001package ca.uhn.fhir.jpa.migrate; 002 003/*- 004 * #%L 005 * HAPI FHIR Server - SQL Migration 006 * %% 007 * Copyright (C) 2014 - 2022 Smile CDR, Inc. 008 * %% 009 * Licensed under the Apache License, Version 2.0 (the "License"); 010 * you may not use this file except in compliance with the License. 011 * You may obtain a copy of the License at 012 * 013 * http://www.apache.org/licenses/LICENSE-2.0 014 * 015 * Unless required by applicable law or agreed to in writing, software 016 * distributed under the License is distributed on an "AS IS" BASIS, 017 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 018 * See the License for the specific language governing permissions and 019 * limitations under the License. 020 * #L% 021 */ 022 023import ca.uhn.fhir.jpa.migrate.taskdef.ColumnTypeEnum; 024import ca.uhn.fhir.rest.server.exceptions.InternalErrorException; 025import org.apache.commons.lang3.builder.EqualsBuilder; 026import org.apache.commons.lang3.builder.HashCodeBuilder; 027import org.apache.commons.lang3.builder.ToStringBuilder; 028import org.hibernate.boot.model.naming.Identifier; 029import org.hibernate.dialect.Dialect; 030import org.hibernate.engine.jdbc.dialect.internal.StandardDialectResolver; 031import org.hibernate.engine.jdbc.dialect.spi.DatabaseMetaDataDialectResolutionInfoAdapter; 032import org.hibernate.engine.jdbc.dialect.spi.DialectResolver; 033import org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl; 034import org.hibernate.engine.jdbc.env.spi.ExtractedDatabaseMetaData; 035import org.hibernate.engine.jdbc.env.spi.IdentifierHelper; 036import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment; 037import org.hibernate.engine.jdbc.env.spi.LobCreatorBuilder; 038import org.hibernate.engine.jdbc.env.spi.NameQualifierSupport; 039import org.hibernate.engine.jdbc.env.spi.QualifiedObjectNameFormatter; 040import org.hibernate.engine.jdbc.spi.SqlExceptionHelper; 041import org.hibernate.engine.jdbc.spi.TypeInfo; 042import org.hibernate.service.ServiceRegistry; 043import org.hibernate.tool.schema.extract.spi.ExtractionContext; 044import org.hibernate.tool.schema.extract.spi.SequenceInformation; 045import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor; 046import org.slf4j.Logger; 047import org.slf4j.LoggerFactory; 048import org.springframework.jdbc.core.ColumnMapRowMapper; 049 050import javax.annotation.Nullable; 051import javax.sql.DataSource; 052import java.sql.Connection; 053import java.sql.DatabaseMetaData; 054import java.sql.ResultSet; 055import java.sql.SQLException; 056import java.sql.Types; 057import java.util.ArrayList; 058import java.util.Collections; 059import java.util.HashSet; 060import java.util.List; 061import java.util.Locale; 062import java.util.Objects; 063import java.util.Set; 064import java.util.stream.Collectors; 065 066public class JdbcUtils { 067 private static final Logger ourLog = LoggerFactory.getLogger(JdbcUtils.class); 068 069 /** 070 * Retrieve all index names 071 */ 072 public static Set<String> getIndexNames(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName) throws SQLException { 073 074 if (!getTableNames(theConnectionProperties).contains(theTableName)) { 075 return Collections.emptySet(); 076 } 077 078 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 079 try (Connection connection = dataSource.getConnection()) { 080 return theConnectionProperties.getTxTemplate().execute(t -> { 081 DatabaseMetaData metadata; 082 try { 083 metadata = connection.getMetaData(); 084 085 ResultSet indexes = getIndexInfo(theTableName, connection, metadata, false); 086 Set<String> indexNames = new HashSet<>(); 087 while (indexes.next()) { 088 ourLog.debug("*** Next index: {}", new ColumnMapRowMapper().mapRow(indexes, 0)); 089 String indexName = indexes.getString("INDEX_NAME"); 090 indexNames.add(indexName); 091 } 092 093 indexes = getIndexInfo(theTableName, connection, metadata, true); 094 while (indexes.next()) { 095 ourLog.debug("*** Next index: {}", new ColumnMapRowMapper().mapRow(indexes, 0)); 096 String indexName = indexes.getString("INDEX_NAME"); 097 indexNames.add(indexName); 098 } 099 100 indexNames = indexNames 101 .stream() 102 .filter(Objects::nonNull) // filter out the nulls first 103 .map(s -> s.toUpperCase(Locale.US)) // then convert the non-null entries to upper case 104 .collect(Collectors.toSet()); 105 106 return indexNames; 107 108 } catch (SQLException e) { 109 throw new InternalErrorException(e); 110 } 111 }); 112 } 113 } 114 115 @SuppressWarnings("ConstantConditions") 116 public static boolean isIndexUnique(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName, String theIndexName) throws SQLException { 117 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 118 try (Connection connection = dataSource.getConnection()) { 119 return theConnectionProperties.getTxTemplate().execute(t -> { 120 DatabaseMetaData metadata; 121 try { 122 metadata = connection.getMetaData(); 123 ResultSet indexes = getIndexInfo(theTableName, connection, metadata, false); 124 125 while (indexes.next()) { 126 String indexName = indexes.getString("INDEX_NAME"); 127 if (theIndexName.equalsIgnoreCase(indexName)) { 128 boolean nonUnique = indexes.getBoolean("NON_UNIQUE"); 129 return !nonUnique; 130 } 131 } 132 133 } catch (SQLException e) { 134 throw new InternalErrorException(e); 135 } 136 137 throw new InternalErrorException("Can't find index: " + theIndexName + " on table " + theTableName); 138 }); 139 } 140 } 141 142 private static ResultSet getIndexInfo(String theTableName, Connection theConnection, DatabaseMetaData theMetadata, boolean theUnique) throws SQLException { 143 // FYI Using approximate=false causes a very slow table scan on Oracle 144 boolean approximate = true; 145 return theMetadata.getIndexInfo(theConnection.getCatalog(), theConnection.getSchema(), massageIdentifier(theMetadata, theTableName), theUnique, approximate); 146 } 147 148 /** 149 * Retrieve all index names 150 */ 151 public static ColumnType getColumnType(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName, String theColumnName) throws SQLException { 152 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 153 try (Connection connection = dataSource.getConnection()) { 154 return theConnectionProperties.getTxTemplate().execute(t -> { 155 DatabaseMetaData metadata; 156 try { 157 metadata = connection.getMetaData(); 158 String catalog = connection.getCatalog(); 159 String schema = connection.getSchema(); 160 ResultSet indexes = metadata.getColumns(catalog, schema, massageIdentifier(metadata, theTableName), null); 161 162 while (indexes.next()) { 163 164 String tableName = indexes.getString("TABLE_NAME").toUpperCase(Locale.US); 165 if (!theTableName.equalsIgnoreCase(tableName)) { 166 continue; 167 } 168 String columnName = indexes.getString("COLUMN_NAME").toUpperCase(Locale.US); 169 if (!theColumnName.equalsIgnoreCase(columnName)) { 170 continue; 171 } 172 173 int dataType = indexes.getInt("DATA_TYPE"); 174 Long length = indexes.getLong("COLUMN_SIZE"); 175 switch (dataType) { 176 case Types.BIT: 177 case Types.BOOLEAN: 178 return new ColumnType(ColumnTypeEnum.BOOLEAN, length); 179 case Types.VARCHAR: 180 return new ColumnType(ColumnTypeEnum.STRING, length); 181 case Types.NUMERIC: 182 case Types.BIGINT: 183 case Types.DECIMAL: 184 return new ColumnType(ColumnTypeEnum.LONG, length); 185 case Types.INTEGER: 186 return new ColumnType(ColumnTypeEnum.INT, length); 187 case Types.TIMESTAMP: 188 case Types.TIMESTAMP_WITH_TIMEZONE: 189 return new ColumnType(ColumnTypeEnum.DATE_TIMESTAMP, length); 190 case Types.BLOB: 191 return new ColumnType(ColumnTypeEnum.BLOB, length); 192 case Types.LONGVARBINARY: 193 if (DriverTypeEnum.MYSQL_5_7.equals(theConnectionProperties.getDriverType())) { 194 //See git 195 return new ColumnType(ColumnTypeEnum.BLOB, length); 196 } else { 197 throw new IllegalArgumentException("Don't know how to handle datatype " + dataType + " for column " + theColumnName + " on table " + theTableName); 198 } 199 case Types.VARBINARY: 200 if (DriverTypeEnum.MSSQL_2012.equals(theConnectionProperties.getDriverType())) { 201 // MS SQLServer seems to be mapping BLOB to VARBINARY under the covers, so we need to reverse that mapping 202 return new ColumnType(ColumnTypeEnum.BLOB, length); 203 204 } else { 205 throw new IllegalArgumentException("Don't know how to handle datatype " + dataType + " for column " + theColumnName + " on table " + theTableName); 206 } 207 case Types.CLOB: 208 return new ColumnType(ColumnTypeEnum.CLOB, length); 209 case Types.DOUBLE: 210 return new ColumnType(ColumnTypeEnum.DOUBLE, length); 211 case Types.FLOAT: 212 return new ColumnType(ColumnTypeEnum.FLOAT, length); 213 default: 214 throw new IllegalArgumentException("Don't know how to handle datatype " + dataType + " for column " + theColumnName + " on table " + theTableName); 215 } 216 217 } 218 219 ourLog.debug("Unable to find column {} in table {}.", theColumnName, theTableName); 220 return null; 221 222 } catch (SQLException e) { 223 throw new InternalErrorException(e); 224 } 225 226 }); 227 } 228 } 229 230 /** 231 * Retrieve all index names 232 */ 233 public static Set<String> getForeignKeys(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName, @Nullable String theForeignTable) throws SQLException { 234 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 235 236 try (Connection connection = dataSource.getConnection()) { 237 return theConnectionProperties.getTxTemplate().execute(t -> { 238 DatabaseMetaData metadata; 239 try { 240 metadata = connection.getMetaData(); 241 String catalog = connection.getCatalog(); 242 String schema = connection.getSchema(); 243 244 245 List<String> parentTables = new ArrayList<>(); 246 if (theTableName != null) { 247 parentTables.add(massageIdentifier(metadata, theTableName)); 248 } else { 249 // If no foreign table is specified, we'll try all of them 250 parentTables.addAll(JdbcUtils.getTableNames(theConnectionProperties)); 251 } 252 253 String foreignTable = massageIdentifier(metadata, theForeignTable); 254 255 Set<String> fkNames = new HashSet<>(); 256 for (String nextParentTable : parentTables) { 257 ResultSet indexes = metadata.getCrossReference(catalog, schema, nextParentTable, catalog, schema, foreignTable); 258 259 while (indexes.next()) { 260 String fkName = indexes.getString("FK_NAME"); 261 fkName = fkName.toUpperCase(Locale.US); 262 fkNames.add(fkName); 263 } 264 } 265 266 return fkNames; 267 } catch (SQLException e) { 268 throw new InternalErrorException(e); 269 } 270 }); 271 } 272 } 273 274 /** 275 * Retrieve names of foreign keys that reference a specified foreign key column. 276 */ 277 public static Set<String> getForeignKeysForColumn(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theForeignKeyColumn, String theForeignTable) throws SQLException { 278 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 279 280 try (Connection connection = dataSource.getConnection()) { 281 return theConnectionProperties.getTxTemplate().execute(t -> { 282 DatabaseMetaData metadata; 283 try { 284 metadata = connection.getMetaData(); 285 String catalog = connection.getCatalog(); 286 String schema = connection.getSchema(); 287 288 289 List<String> parentTables = new ArrayList<>(); 290 parentTables.addAll(JdbcUtils.getTableNames(theConnectionProperties)); 291 292 String foreignTable = massageIdentifier(metadata, theForeignTable); 293 294 Set<String> fkNames = new HashSet<>(); 295 for (String nextParentTable : parentTables) { 296 ResultSet indexes = metadata.getCrossReference(catalog, schema, nextParentTable, catalog, schema, foreignTable); 297 298 while (indexes.next()) { 299 if (theForeignKeyColumn.equals(indexes.getString("FKCOLUMN_NAME"))) { 300 String fkName = indexes.getString("FK_NAME"); 301 fkName = fkName.toUpperCase(Locale.US); 302 fkNames.add(fkName); 303 } 304 } 305 } 306 307 return fkNames; 308 } catch (SQLException e) { 309 throw new InternalErrorException(e); 310 } 311 }); 312 } 313 } 314 315 /** 316 * Retrieve all index names 317 */ 318 public static Set<String> getColumnNames(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName) throws SQLException { 319 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 320 try (Connection connection = dataSource.getConnection()) { 321 return theConnectionProperties.getTxTemplate().execute(t -> { 322 DatabaseMetaData metadata; 323 try { 324 metadata = connection.getMetaData(); 325 ResultSet indexes = metadata.getColumns(connection.getCatalog(), connection.getSchema(), massageIdentifier(metadata, theTableName), null); 326 327 Set<String> columnNames = new HashSet<>(); 328 while (indexes.next()) { 329 String tableName = indexes.getString("TABLE_NAME").toUpperCase(Locale.US); 330 if (!theTableName.equalsIgnoreCase(tableName)) { 331 continue; 332 } 333 334 String columnName = indexes.getString("COLUMN_NAME"); 335 columnName = columnName.toUpperCase(Locale.US); 336 columnNames.add(columnName); 337 } 338 339 return columnNames; 340 } catch (SQLException e) { 341 throw new InternalErrorException(e); 342 } 343 }); 344 } 345 } 346 347 public static Set<String> getSequenceNames(DriverTypeEnum.ConnectionProperties theConnectionProperties) throws SQLException { 348 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 349 try (Connection connection = dataSource.getConnection()) { 350 return theConnectionProperties.getTxTemplate().execute(t -> { 351 try { 352 DialectResolver dialectResolver = new StandardDialectResolver(); 353 Dialect dialect = dialectResolver.resolveDialect(new DatabaseMetaDataDialectResolutionInfoAdapter(connection.getMetaData())); 354 355 Set<String> sequenceNames = new HashSet<>(); 356 if (dialect.supportsSequences()) { 357 358 // Use Hibernate to get a list of current sequences 359 SequenceInformationExtractor sequenceInformationExtractor = dialect.getSequenceInformationExtractor(); 360 ExtractionContext extractionContext = new ExtractionContext.EmptyExtractionContext() { 361 @Override 362 public Connection getJdbcConnection() { 363 return connection; 364 } 365 366 @Override 367 public ServiceRegistry getServiceRegistry() { 368 return super.getServiceRegistry(); 369 } 370 371 @Override 372 public JdbcEnvironment getJdbcEnvironment() { 373 return new JdbcEnvironment() { 374 @Override 375 public Dialect getDialect() { 376 return dialect; 377 } 378 379 @Override 380 public ExtractedDatabaseMetaData getExtractedDatabaseMetaData() { 381 return null; 382 } 383 384 @Override 385 public Identifier getCurrentCatalog() { 386 return null; 387 } 388 389 @Override 390 public Identifier getCurrentSchema() { 391 return null; 392 } 393 394 @Override 395 public QualifiedObjectNameFormatter getQualifiedObjectNameFormatter() { 396 return null; 397 } 398 399 @Override 400 public IdentifierHelper getIdentifierHelper() { 401 return new NormalizingIdentifierHelperImpl(this, null, true, true, true, null, null, null); 402 } 403 404 @Override 405 public NameQualifierSupport getNameQualifierSupport() { 406 return null; 407 } 408 409 @Override 410 public SqlExceptionHelper getSqlExceptionHelper() { 411 return null; 412 } 413 414 @Override 415 public LobCreatorBuilder getLobCreatorBuilder() { 416 return null; 417 } 418 419 @Override 420 public TypeInfo getTypeInfoForJdbcCode(int jdbcTypeCode) { 421 return null; 422 } 423 }; 424 } 425 }; 426 Iterable<SequenceInformation> sequences = sequenceInformationExtractor.extractMetadata(extractionContext); 427 for (SequenceInformation next : sequences) { 428 sequenceNames.add(next.getSequenceName().getSequenceName().getText()); 429 } 430 431 } 432 return sequenceNames; 433 } catch (SQLException e) { 434 throw new InternalErrorException(e); 435 } 436 }); 437 } 438 } 439 440 public static Set<String> getTableNames(DriverTypeEnum.ConnectionProperties theConnectionProperties) throws SQLException { 441 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 442 try (Connection connection = dataSource.getConnection()) { 443 return theConnectionProperties.getTxTemplate().execute(t -> { 444 DatabaseMetaData metadata; 445 try { 446 metadata = connection.getMetaData(); 447 ResultSet tables = metadata.getTables(connection.getCatalog(), connection.getSchema(), null, null); 448 449 Set<String> columnNames = new HashSet<>(); 450 while (tables.next()) { 451 String tableName = tables.getString("TABLE_NAME"); 452 tableName = tableName.toUpperCase(Locale.US); 453 454 String tableType = tables.getString("TABLE_TYPE"); 455 if ("SYSTEM TABLE".equalsIgnoreCase(tableType)) { 456 continue; 457 } 458 if (SchemaMigrator.HAPI_FHIR_MIGRATION_TABLENAME.equalsIgnoreCase(tableName)) { 459 continue; 460 } 461 462 columnNames.add(tableName); 463 } 464 465 return columnNames; 466 } catch (SQLException e) { 467 throw new InternalErrorException(e); 468 } 469 }); 470 } 471 } 472 473 public static boolean isColumnNullable(DriverTypeEnum.ConnectionProperties theConnectionProperties, String theTableName, String theColumnName) throws SQLException { 474 DataSource dataSource = Objects.requireNonNull(theConnectionProperties.getDataSource()); 475 try (Connection connection = dataSource.getConnection()) { 476 //noinspection ConstantConditions 477 return theConnectionProperties.getTxTemplate().execute(t -> { 478 DatabaseMetaData metadata; 479 try { 480 metadata = connection.getMetaData(); 481 ResultSet tables = metadata.getColumns(connection.getCatalog(), connection.getSchema(), massageIdentifier(metadata, theTableName), null); 482 483 while (tables.next()) { 484 String tableName = tables.getString("TABLE_NAME").toUpperCase(Locale.US); 485 if (!theTableName.equalsIgnoreCase(tableName)) { 486 continue; 487 } 488 489 if (theColumnName.equalsIgnoreCase(tables.getString("COLUMN_NAME"))) { 490 String nullable = tables.getString("IS_NULLABLE"); 491 if ("YES".equalsIgnoreCase(nullable)) { 492 return true; 493 } else if ("NO".equalsIgnoreCase(nullable)) { 494 return false; 495 } else { 496 throw new IllegalStateException("Unknown nullable: " + nullable); 497 } 498 } 499 } 500 501 throw new IllegalStateException("Did not find column " + theColumnName); 502 } catch (SQLException e) { 503 throw new InternalErrorException(e); 504 } 505 }); 506 } 507 } 508 509 private static String massageIdentifier(DatabaseMetaData theMetadata, String theCatalog) throws SQLException { 510 String retVal = theCatalog; 511 if (theCatalog == null) { 512 return null; 513 } else if (theMetadata.storesLowerCaseIdentifiers()) { 514 retVal = retVal.toLowerCase(); 515 } else { 516 retVal = retVal.toUpperCase(); 517 } 518 return retVal; 519 } 520 521 public static class ColumnType { 522 private final ColumnTypeEnum myColumnTypeEnum; 523 private final Long myLength; 524 525 public ColumnType(ColumnTypeEnum theColumnType, Long theLength) { 526 myColumnTypeEnum = theColumnType; 527 myLength = theLength; 528 } 529 530 public ColumnType(ColumnTypeEnum theColumnType, int theLength) { 531 this(theColumnType, (long) theLength); 532 } 533 534 public ColumnType(ColumnTypeEnum theColumnType) { 535 this(theColumnType, null); 536 } 537 538 @Override 539 public boolean equals(Object theO) { 540 if (this == theO) { 541 return true; 542 } 543 544 if (theO == null || getClass() != theO.getClass()) { 545 return false; 546 } 547 548 ColumnType that = (ColumnType) theO; 549 550 return new EqualsBuilder() 551 .append(myColumnTypeEnum, that.myColumnTypeEnum) 552 .append(myLength, that.myLength) 553 .isEquals(); 554 } 555 556 @Override 557 public int hashCode() { 558 return new HashCodeBuilder(17, 37) 559 .append(myColumnTypeEnum) 560 .append(myLength) 561 .toHashCode(); 562 } 563 564 @Override 565 public String toString() { 566 ToStringBuilder b = new ToStringBuilder(this); 567 b.append("type", myColumnTypeEnum); 568 if (myLength != null) { 569 b.append("length", myLength); 570 } 571 return b.toString(); 572 } 573 574 public ColumnTypeEnum getColumnTypeEnum() { 575 return myColumnTypeEnum; 576 } 577 578 public Long getLength() { 579 return myLength; 580 } 581 582 public boolean equals(ColumnTypeEnum theTaskColumnType, Long theTaskColumnLength) { 583 ourLog.debug("Comparing existing {} {} to new {} {}", myColumnTypeEnum, myLength, theTaskColumnType, theTaskColumnLength); 584 return myColumnTypeEnum == theTaskColumnType && (theTaskColumnLength == null || theTaskColumnLength.equals(myLength)); 585 } 586 } 587}