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