Changeset 5854
- Timestamp:
- Nov 8, 2011, 1:43:27 PM (11 years ago)
- Location:
- trunk
- Files:
-
- 1 added
- 6 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/src/core/net/sf/basedb/core/HibernateUtil.java
r5853 r5854 628 628 { 629 629 SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, mode, progress); 630 schemaGenerator.setCreateMissingUniqueConstraints(true); 630 631 session = HibernateUtil.newSession(); 631 632 tx = HibernateUtil.newTransaction(session); -
trunk/src/core/net/sf/basedb/core/Migration.java
r5853 r5854 43 43 import java.util.SortedSet; 44 44 import java.util.TreeSet; 45 45 import java.util.regex.Matcher; 46 import java.util.regex.Pattern; 47 48 import net.sf.basedb.core.data.VirtualDbData; 46 49 import net.sf.basedb.core.dbengine.DbEngine; 50 import net.sf.basedb.core.dbengine.PostgresDbEngine; 47 51 import net.sf.basedb.core.dbengine.TableInfo; 48 52 import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo; 49 53 import net.sf.basedb.core.hibernate.JdbcWork; 54 import net.sf.basedb.core.hibernate.SchemaGenerator; 50 55 import net.sf.basedb.util.FileUtil; 51 56 import net.sf.basedb.util.RegexpFileFilter; … … 59 64 60 65 /** 61 Class for migrating a database from MySQL to 62 PostgreSQL. The {@link #exportAll(String, ProgressReporter)} method 63 only works on a MySQL database and the {@link #importAll(String, ProgressReporter)} 66 Class for migrating a database to PostgreSQL. 67 The {@link #exportAll(String, ProgressReporter)} method should work on 68 both MySQL and PostgreSQL (but is only tested regularly with MySQL). 69 The export generates data that is suitable for importing into 70 PostgreSQL using the COPY command. The {@link #importAll(String, ProgressReporter)} 64 71 only works on a PostgreSQL database. 65 72 … … 109 116 if (tx != null) HibernateUtil.rollback(tx); 110 117 if (session != null) HibernateUtil.close(session); 118 Application.stop(); 111 119 } 112 120 } … … 145 153 if (tx != null) HibernateUtil.rollback(tx); 146 154 if (session != null) HibernateUtil.close(session); 155 Application.stop(); 156 } 157 } 158 159 public static void createMissingConstraints(ProgressReporter progress) 160 { 161 try 162 { 163 Application.start(false, false, false); 164 progress.display(0, "Creating constraints..."); 165 HibernateUtil.createStaticTables(SchemaGenerator.Mode.UPDATE, progress); 166 } 167 finally 168 { 169 Application.stop(); 147 170 } 148 171 } … … 196 219 // ------------------- 197 220 198 221 /** 222 Perform the export. In principle we support all databases that BASE supports, 223 but it may depend on the actual underlying SQL column types. The export 224 generates files that can be imported into PostgreSQL only. 225 */ 199 226 private void doExport(Connection connection) 200 227 throws SQLException, IOException … … 203 230 DatabaseMetaData metaData = connection.getMetaData(); 204 231 DbEngine engine = HibernateUtil.getDbEngine(); 232 233 String staticCatalog = null; 234 String staticSchema = null; 235 String dynamicCatalog = Application.getDynamicCatalog(); 236 String dynamicSchema = Application.getDynamicSchema(); 237 238 if (engine instanceof PostgresDbEngine) 239 { 240 staticSchema = "public"; 241 } 205 242 206 243 // Load information about all tables in the static and dynamic databases 207 progress.display(0, " Loading tables...\n");244 progress.display(0, "Counting tables...\n"); 208 245 final int TICK_INTERVAL = 1000; 209 246 long nextTick = System.currentTimeMillis() + TICK_INTERVAL; 210 List<Table> staticTables = TableInfo.listTables(metaData, null, null);247 List<Table> staticTables = TableInfo.listTables(metaData, staticCatalog, staticSchema); 211 248 progress.display(0, staticTables.size() + " tables in static database\n"); 212 249 213 List<Table> dynamicTables = TableInfo.listTables(metaData, 214 Application.getDynamicCatalog(), Application.getDynamicSchema());215 progress.display(0, dynamicTables.size() + " tables in static database");250 List<Table> dynamicTables = TableInfo.listTables(metaData, dynamicCatalog, dynamicSchema); 251 progress.display(0, dynamicTables.size() + " tables in dynamic database\n"); 252 progress.display(0, "Counting rows..."); 216 253 217 254 // Get metadata (eg. column names+types) … … 244 281 } 245 282 246 progress.append("\n"); 247 progress.display(0, "Found " + tables.size() + " tables with " + 248 Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n" 249 ); 283 progress.append(" found " + Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n"); 250 284 251 285 … … 315 349 writer.flush(); 316 350 writer.close(); 317 318 } 319 } 320 351 } 352 } 353 354 /** 355 Perform the import. Importing is supported on PostgreSQL only. 356 */ 321 357 private void doImport(Connection connection) 322 358 throws SQLException, IOException … … 325 361 DbEngine engine = HibernateUtil.getDbEngine(); 326 362 363 if (!(engine instanceof PostgresDbEngine)) 364 { 365 throw new UnsupportedOperationException("Importing is supported for PostgreSQL only"); 366 } 367 327 368 // Load information about all tables in the static and dynamic databases 328 progress.display(0, "Loading tables... \n");369 progress.display(0, "Loading tables..."); 329 370 final int TICK_INTERVAL = 1000; 330 371 long nextTick = System.currentTimeMillis() + TICK_INTERVAL; … … 355 396 // And then the dynamic tables 356 397 // Since they don't exists in the database yet, we need to scan the files 398 Pattern dynamicFilePattern = Pattern.compile("d(\\d+)(.+)\\.data"); 357 399 List<File> dynamicDataFiles = FileUtil.findFiles(directory, 358 new RegexpFileFilter( "d\\d+.*", null));400 new RegexpFileFilter(dynamicFilePattern, null)); 359 401 progress.display(0, dynamicDataFiles.size() + " files with dynamic data"); 360 361 402 for (File f : dynamicDataFiles) 362 403 { … … 364 405 } 365 406 366 367 progress.append("\n");368 407 progress.display(0, "Found " + (tables.size()+dynamicDataFiles.size()) + " files with " + 369 408 Values.formatBytes(totalFileSize, 2) + " data\n" … … 382 421 } 383 422 } 423 // Keep track of the max id used in all tables (so that we can set the 'hibernate_sequence') 384 424 int maxGlobalId = 0; 385 425 … … 390 430 { 391 431 File importFile = getDataFile(table); 392 String tableName = table.getTable().getName(); 432 if (importFile.length() > 0) 433 { 434 String tableName = table.getTable().getName(); 435 progress.display((int)((totalImported * 100) / totalFileSize), 436 tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "..."); 437 438 // Get columns in sorted order 439 SortedSet<ColumnInfo> columns = new TreeSet<ColumnInfo>(); 440 columns.addAll(table.getColumns()); 441 442 // Create COPY (<columns>) FROM '<filename>' 443 StringBuilder copy = new StringBuilder("COPY "); 444 copy.append(table.getFullQuotedTableName(engine)); 445 copy.append(" ("); 446 String separator = ""; 447 boolean hasIdColumn = false; 448 for (ColumnInfo c : columns) 449 { 450 copy.append(separator); 451 copy.append(engine.getQuotedName(c.getName())); 452 separator = ","; 453 hasIdColumn |= "id".equals(c.getName()); 454 } 455 copy.append(") FROM '").append(importFile.getAbsolutePath() + "'"); 456 457 // Import the data 458 st.executeUpdate(copy.toString()); 459 460 // Get max id (if 'id' column exists) 461 if (hasIdColumn) 462 { 463 String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine); 464 Query query = HibernateUtil.createSqlQuery(session, sql); 465 Integer maxId = HibernateUtil.loadData(Integer.class, query); 466 if (maxId != null) // empty tables have no max id 467 { 468 if (sequenceGenerators.containsKey(tableName)) 469 { 470 // Set the value for the special sequence generator 471 sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")"; 472 st.execute(sql); 473 } 474 else 475 { 476 // Global 'hibernate_sequence' 477 if (maxGlobalId < maxId) maxGlobalId = maxId; 478 } 479 } 480 } 481 482 // Done with this table... 483 totalImported += importFile.length(); 484 progress.append("done"); 485 } 486 } 487 488 // Set the value for the 'hibernate_sequence' generator 489 String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")"; 490 st.execute(sql); 491 492 // Import data for each table in the dynamic database 493 String dynamicCatalog = HibernateUtil.quote(Application.getDynamicCatalog()); 494 String dynamicSchema = HibernateUtil.quote(Application.getDynamicSchema()); 495 for (File dynamicDataFile : dynamicDataFiles) 496 { 497 Matcher m = dynamicFilePattern.matcher(dynamicDataFile.getName()); 498 if (!m.matches()) 499 { 500 // Should never happen since we use the same filter to match files at the beginning 501 throw new AssertionError("File is not a dynamic data file: " + dynamicDataFile.getAbsolutePath()); 502 } 503 int virtualDbId = Values.getInt(m.group(1)); 504 String tableType = m.group(2).toLowerCase(); 505 506 // Load the VirtualDb object that is managing the dynamic data table 507 VirtualDbData vdbData = HibernateUtil.loadData(session, VirtualDbData.class, virtualDbId); 508 VirtualDb vdb = new VirtualDb(vdbData); 509 510 // Find the VirtualTable for this file 511 VirtualTable vTable = null; 512 String tableName = null; 513 for (VirtualTable vt : VirtualTable.values()) 514 { 515 tableName = vt.getTableName(vdb); 516 if (tableName.toLowerCase().endsWith(tableType)) 517 { 518 vTable = vt; 519 break; 520 } 521 } 522 523 if (vTable == null) 524 { 525 throw new ItemNotFoundException("No virtual table found for file: " + dynamicDataFile.getAbsolutePath()); 526 } 527 393 528 progress.display((int)((totalImported * 100) / totalFileSize), 394 tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "..."); 529 tableName + ": importing " + Values.formatBytes(dynamicDataFile.length(), 2) + "..."); 530 531 // Create the virtual table 532 HibernateUtil.createVirtualTable(vdb, vTable); 395 533 396 534 // Get columns in sorted order 397 SortedSet<ColumnInfo> columns = new TreeSet<ColumnInfo>(); 398 columns.addAll(table.getColumns()); 535 SortedSet<String> columns = new TreeSet<String>(); 536 for (VirtualColumn col : vTable.getColumns(vdb.getRawDataType())) 537 { 538 columns.add(col.getColumn()); 539 } 399 540 400 // Create COPY (<columns>) FROM '<filename>'541 // Create COPY <table> (<columns>) FROM '<filename>' 401 542 StringBuilder copy = new StringBuilder("COPY "); 402 copy.append(table.getFullQuotedTableName(engine)); 543 if (dynamicCatalog != null) 544 { 545 copy.append(dynamicCatalog).append("."); 546 } 547 if (dynamicSchema != null) 548 { 549 copy.append(dynamicSchema).append("."); 550 } 551 copy.append(engine.getQuotedName(tableName)); 403 552 copy.append(" ("); 404 553 String separator = ""; 405 554 boolean hasIdColumn = false; 406 for ( ColumnInfo c: columns)555 for (String col : columns) 407 556 { 408 557 copy.append(separator); 409 copy.append(engine.getQuotedName(c .getName()));558 copy.append(engine.getQuotedName(col)); 410 559 separator = ","; 411 hasIdColumn |= "id".equals(c.getName()); 412 } 413 copy.append(") FROM '").append(importFile.getAbsolutePath() + "'"); 560 } 561 copy.append(") FROM '").append(dynamicDataFile.getAbsolutePath() + "'"); 414 562 415 563 // Import the data 416 564 st.executeUpdate(copy.toString()); 417 565 418 // Get max id (if 'id' column exists)419 if (hasIdColumn)420 {421 String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);422 Query query = HibernateUtil.createSqlQuery(session, sql);423 Integer maxId = HibernateUtil.loadData(Integer.class, query);424 if (maxId != null) // empty tables have no max id425 {426 if (sequenceGenerators.containsKey(tableName))427 {428 // Set the value for the special sequence generator429 sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")";430 st.execute(sql);431 }432 else433 {434 // Global 'hibernate_sequence'435 if (maxGlobalId < maxId) maxGlobalId = maxId;436 }437 }438 }439 440 566 // Done with this table... 441 totalImported += importFile.length(); 442 progress.append("done\n"); 443 } 444 445 // Set the value for the 'hibernate_sequence' generator 446 String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")"; 447 st.execute(sql); 448 449 // Import data for each table in the dynamic database 450 for (File f : dynamicDataFiles) 451 { 452 567 totalImported += dynamicDataFile.length(); 568 progress.append("done"); 453 569 } 454 570 … … 571 687 return new File(directory, table.getTable().getName().toLowerCase()+".data"); 572 688 } 689 690 573 691 } 574 692 -
trunk/src/core/net/sf/basedb/core/VirtualTable.java
r5384 r5854 260 260 public String getTableName(VirtualDb vdb) 261 261 { 262 return nameTemplate.replace("#", Integer.toString(vdb .getId()));262 return nameTemplate.replace("#", Integer.toString(vdb == null ? 0 : vdb.getId())); 263 263 } 264 264 -
trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java
r5853 r5854 85 85 String tableCatalog = result.getString("TABLE_CAT"); 86 86 String tableSchema = result.getString("TABLE_SCHEM"); 87 88 //System.out.println(tableName + ":" + result.getString("TABLE_TYPE"));89 90 87 Table table = new Table(); 91 88 table.setName(tableName); … … 96 93 return tables; 97 94 } 98 99 95 100 96 private final Table table; -
trunk/src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java
r5853 r5854 23 23 24 24 import java.sql.Connection; 25 import java.sql.DatabaseMetaData; 25 26 import java.sql.SQLException; 26 27 import java.sql.Statement; 28 import java.util.ArrayList; 29 import java.util.Arrays; 30 import java.util.Collections; 31 import java.util.Iterator; 32 import java.util.List; 33 import java.util.Set; 27 34 28 35 import net.sf.basedb.core.ProgressReporter; 29 36 import net.sf.basedb.core.StringUtil; 30 37 import net.sf.basedb.core.dbengine.DbEngine; 38 import net.sf.basedb.core.dbengine.TableInfo; 39 import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo; 40 import net.sf.basedb.core.dbengine.TableInfo.IndexInfo; 31 41 32 42 import org.hibernate.cfg.Configuration; 33 43 import org.hibernate.dialect.Dialect; 34 44 import org.hibernate.jdbc.Work; 45 import org.hibernate.mapping.Table; 35 46 import org.hibernate.tool.hbm2ddl.DatabaseMetadata; 36 47 … … 58 69 private final Mode mode; 59 70 private final ProgressReporter progress; 71 private boolean createMissingUniqueConstraints; 60 72 61 73 /** … … 92 104 try 93 105 { 94 String[] allSql = null;106 List<String> allSql = new ArrayList<String>(); 95 107 if (mode == Mode.UPDATE) 96 108 { 97 109 log.info("Fetching database metadata"); 98 110 DatabaseMetadata meta = new DatabaseMetadata(connection, dialect); 99 allSql = cfg.generateSchemaUpdateScript(dialect, meta);111 allSql.addAll(Arrays.asList(cfg.generateSchemaUpdateScript(dialect, meta))); 100 112 } 101 113 else 102 114 { 103 allSql = cfg.generateSchemaCreationScript(dialect); 104 } 105 int numStatements = allSql.length; 115 allSql.addAll(Arrays.asList(cfg.generateSchemaCreationScript(dialect))); 116 } 117 118 if (createMissingUniqueConstraints && mode == Mode.UPDATE) 119 { 120 Iterator<Table> tables = (Iterator<Table>)cfg.getTableMappings(); 121 DatabaseMetaData metaData = connection.getMetaData(); 122 while (tables.hasNext()) 123 { 124 Table table = tables.next(); 125 TableInfo tiDb = new TableInfo(table, metaData); 126 TableInfo tiHib = new TableInfo(table, dialect); 127 128 // Single-column unique constraints are not found in the indexes collection 129 for (ColumnInfo ci : tiHib.getColumns()) 130 { 131 if (!ci.isUnique()) continue; // with next column 132 133 // Check if the constraint exists in the db 134 Set<String> columns = Collections.singleton(ci.getName()); 135 String dbName = tiDb.findIndexName(null, columns); 136 if (dbName != null) continue; // with the next column 137 138 // Generate SQL to create the unique constraint 139 String constraintName = table.getName() +"_" + ci.getName(); 140 String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(), 141 table.getName(), constraintName, columns, ci.isUnique()); 142 allSql.add(createSql); 143 } 144 145 for (IndexInfo ii : tiHib.getIndexes()) 146 { 147 if (!ii.isUnique()) continue; // with the next index 148 149 // Check if the index exists in the db 150 String dbName = tiDb.findIndexName(ii.getName(), ii.getColumns()); 151 if (dbName != null) continue; // with the next index 152 153 // Generate SQL to create the unique constraint 154 String constraintName = table.getName() +"_" + ii.getName(); 155 String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(), 156 table.getName(), constraintName, ii.getColumns(), ii.isUnique()); 157 allSql.add(createSql); 158 } 159 } 160 } 161 162 int numStatements = allSql.size(); 106 163 int numDone = 0; 107 164 int interval = Math.max(1, numStatements / 30); … … 158 215 } 159 216 217 /** 218 Set this option to let the schema generator create unique constraints 219 on columns that doesn't have them already. This is only supported 220 in UPDATE mode. 221 @since 3.1 222 */ 223 public void setCreateMissingUniqueConstraints(boolean createMissingUniqueConstraints) 224 { 225 this.createMissingUniqueConstraints = createMissingUniqueConstraints; 226 } 227 160 228 /** 161 229 The installation mode. -
trunk/src/install/net/sf/basedb/install/InitDB.java
r5853 r5854 146 146 if (export) 147 147 { 148 Migration.exportAll(path, new ConsoleProgressReporter(false)); 148 ProgressReporter progress = new ConsoleProgressReporter(false); 149 progress.display(0, "Starting export...\n"); 150 ChainedProgressReporter chained = new ChainedProgressReporter(progress); 151 chained.setRange(1, 99); 152 Migration.exportAll(path, chained); 153 progress.display(100, "Export completed successfully!\n"); 149 154 } 150 155 else 151 156 { 152 ChainedProgressReporter progress = new ChainedProgressReporter(new ConsoleProgressReporter()); 157 ProgressReporter progress = new ConsoleProgressReporter(); 158 ChainedProgressReporter chained = new ChainedProgressReporter(progress); 159 progress.display(0, "Starting import..."); 153 160 // Create empty database without indexes and foreign keys 154 progress.setRange(0, 5); 155 Install.createTables(SchemaGenerator.Mode.MIGRATE, progress, null, null); 156 progress.append("\n"); 161 chained.setRange(1, 5); 162 Install.createTables(SchemaGenerator.Mode.MIGRATE, chained, null, null); 157 163 158 164 // Import all data 159 progress = new ChainedProgressReporter(new ConsoleProgressReporter(false)); 160 progress.setRange(5, 90); 161 Migration.importAll(path, progress); 165 chained.setRange(6, 90); 166 Migration.importAll(path, chained); 162 167 163 168 // Add indexes, foreign keys, etc. to database 169 chained.setRange(91, 99); 170 Migration.createMissingConstraints(chained); 171 progress.display(100, "Import completed successfully!\n"); 164 172 } 165 173 Application.stop();
Note: See TracChangeset
for help on using the changeset viewer.