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