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