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