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