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