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