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