Changeset 5882
- Timestamp:
- Nov 22, 2011, 11:47:22 AM (11 years ago)
- Location:
- trunk/src/core/net/sf/basedb/core
- Files:
-
- 6 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/src/core/net/sf/basedb/core/Migration.java
r5880 r5882 261 261 List<Table> dynamicTables = TableInfo.listTables(metaData, dynamicCatalog, dynamicSchema); 262 262 progress.display(0, dynamicTables.size() + " tables in dynamic database\n"); 263 progress.display(0, "Counting rows ...");263 progress.display(0, "Counting rows in all tables..."); 264 264 int totalTables = staticTables.size() + dynamicTables.size(); 265 265 … … 267 267 // and count the number of data rows for each table 268 268 List<TableInfo> tables = new ArrayList<TableInfo>(); 269 long totalRows= 0;269 long approxTotal = 0; 270 270 for (Table table : staticTables) 271 271 { 272 TableInfo info = new TableInfo(table, metaData); 273 long approxRows = info.getRowCount(engine, connection, true); 274 approxTotal += approxRows; 275 tables.add(info); 272 276 if (System.currentTimeMillis() > nextTick) 273 277 { 274 //progress.append(".");275 278 progress.display(0, "Counting rows in all tables (" + tables.size() + "/" + totalTables + ")..."); 276 279 nextTick = System.currentTimeMillis()+TICK_INTERVAL; 277 280 } 281 } 282 for (Table table : dynamicTables) 283 { 278 284 TableInfo info = new TableInfo(table, metaData); 279 long tableRows = info.getRowCount(engine, connection);280 totalRows += tableRows;285 long approxRows = info.getRowCount(engine, connection, true); 286 approxTotal += approxRows; 281 287 tables.add(info); 282 } 283 for (Table table : dynamicTables) 284 { 285 if (System.currentTimeMillis() > nextTick) 286 { 287 //progress.append("."); 288 if (System.currentTimeMillis() > nextTick || tables.size() == totalTables) 289 { 288 290 progress.display(0, "Counting rows in all tables (" + tables.size() + "/" + totalTables + ")..."); 289 291 nextTick = System.currentTimeMillis()+TICK_INTERVAL; 290 292 } 291 TableInfo info = new TableInfo(table, metaData); 292 long tableRows = info.getRowCount(engine, connection); 293 totalRows += tableRows; 294 tables.add(info); 295 } 296 297 progress.display(0, "Found " + Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n"); 298 293 } 294 295 progress.display(0, "Found approximate " + Values.formatNumber(approxTotal / 1000000f, 2) + " million data rows\n"); 299 296 300 297 // Export data for each table... 301 long totalExported = 0;298 long actualExported = 0; 302 299 Set<Integer> usedSqlTypes = new HashSet<Integer>(); 303 300 for (TableInfo table : tables) 304 301 { 305 302 String tableName = table.getTable().getName(); 306 progress.display((int)(( totalExported * 100) / totalRows),303 progress.display((int)((actualExported * 100) / approxTotal), 307 304 tableName + ": loading..."); 308 305 nextTick = System.currentTimeMillis()+TICK_INTERVAL; … … 337 334 new OutputStreamWriter(FileUtil.getOutputStream(exportFile), "UTF-8")); 338 335 339 long tableRows = table.getRowCount(engine, connection);336 long approxRows = table.getRowCount(engine, connection, true); 340 337 ResultSet rows = st.executeQuery(select.toString()); 341 int numRows = 0;338 int actualRows = 0; 342 339 while (rows.next()) 343 340 { … … 345 342 if (System.currentTimeMillis() > nextTick) 346 343 { 347 progress.display((int)(( totalExported * 100) / totalRows),348 tableName + ": exporting... (" + numRows + " of " + tableRows + " rows done)");344 progress.display((int)((actualExported * 100) / approxTotal), 345 tableName + ": exporting... (" + actualRows + " of " + approxRows + " rows done)"); 349 346 nextTick = System.currentTimeMillis() + TICK_INTERVAL; 350 347 } … … 361 358 362 359 // Keep track of rows 363 totalExported++; 364 numRows++; 360 actualRows++; 361 actualExported++; 362 if (actualRows > approxRows) 363 { 364 // Increase the approximate row count 365 approxTotal++; 366 approxRows++; 367 } 368 369 } 370 if (actualRows < approxRows) 371 { 372 // Decrease the approximate total row count 373 approxTotal += actualRows - approxRows; 365 374 } 366 375 367 376 // Done with this table... 368 progress.display((int)(( totalExported * 100) / totalRows),369 tableName + ": complete (" + tableRows + " rows) \n");377 progress.display((int)((actualExported * 100) / approxTotal), 378 tableName + ": complete (" + actualRows + " rows) \n"); 370 379 writer.flush(); 371 380 writer.close(); … … 616 625 } 617 626 627 private static String zero = new String(new char[] { 0 }); 628 618 629 /** 619 630 Format the value to a string that PostgreSQL accepts in the … … 652 663 s = s.replace("\r", "\\r"); 653 664 s = s.replace("\t", "\\t"); 665 s = s.replace(zero, ""); // a zero-character can't be stored in PostgreSQL so we simply remove it 654 666 formatted = s; 655 667 break; -
trunk/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java
r5881 r5882 226 226 return false; 227 227 } 228 /** 229 @return null, since this depends on capabilities of underlying database 230 */ 231 @Override 232 public String getApproximateRowCountSql(String catalog, String schema, String table) 233 { 234 return null; 235 } 228 236 // ------------------------------------------- 229 237 -
trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java
r5881 r5882 380 380 public boolean useSavePointToContinueTransactionFromSqlFailure(); 381 381 382 /** 383 Get an SQL statement that returns an approximate count for the number 384 of rows in the given table. The SQL query should return a single row 385 with a single value. 386 387 @param catalog The name of the catalog (database) where the table is 388 located, or null if it is located in the current catalog 389 @param schema The name of the schema where the table is located, or 390 null if is located in the current schema 391 @param table The name of the table 392 @return An SQL query or null 393 @since 3.1 394 */ 395 public String getApproximateRowCountSql(String catalog, String schema, String table); 396 382 397 } -
trunk/src/core/net/sf/basedb/core/dbengine/MySQLEngine.java
r5852 r5882 229 229 return "`" + name + "`"; 230 230 } 231 232 @Override 233 public String getApproximateRowCountSql(String catalog, String schema, String table) 234 { 235 return "select TABLE_ROWS from INFORMATION_SCHEMA.TABLES where TABLE_NAME='" + table + "' and TABLE_SCHEMA='" + catalog + "'"; 236 } 237 231 238 // ------------------------------------------- 232 239 -
trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java
r5881 r5882 285 285 return true; 286 286 } 287 288 @Override 289 public String getApproximateRowCountSql(String catalog, String schema, String table) 290 { 291 return "select reltuples from pg_class where relname = '" + table + "'"; 292 } 287 293 // ------------------------------------------- 288 294 295 289 296 290 297 -
trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java
r5857 r5882 103 103 private Set<IndexInfo> indexes; 104 104 private long numberOfRows = -1; 105 private long approximateNumberOfRows = -1; 105 106 106 107 … … 481 482 @since 3.1 482 483 */ 483 public long getRowCount(DbEngine engine, Connection connection )484 public long getRowCount(DbEngine engine, Connection connection, boolean approximate) 484 485 throws SQLException 485 486 { 486 if ( numberOfRows < 0)487 if ((numberOfRows < 0 && !approximate) || (approximateNumberOfRows < 0 && approximate)) 487 488 { 488 489 … … 492 493 { 493 494 st = connection.createStatement(); 494 StringBuilder sql = new StringBuilder("SELECT count(*) FROM "); 495 sql.append(getFullQuotedTableName(engine)); 496 result = st.executeQuery(sql.toString()); 495 String sql = null; 496 if (approximate) 497 { 498 sql = engine.getApproximateRowCountSql(table.getCatalog(), table.getSchema(), table.getName()); 499 } 500 if (sql == null) 501 { 502 sql = "SELECT count(*) FROM " + getFullQuotedTableName(engine); 503 } 504 result = st.executeQuery(sql); 497 505 result.next(); 498 numberOfRows = result.getLong(1); 506 long count = result.getLong(1); 507 if (approximate) 508 { 509 approximateNumberOfRows = count; 510 } 511 else 512 { 513 numberOfRows = count; 514 } 499 515 } 500 516 finally … … 504 520 } 505 521 } 506 return numberOfRows;522 return approximate ? approximateNumberOfRows : numberOfRows; 507 523 } 508 524
Note: See TracChangeset
for help on using the changeset viewer.