Changeset 5882

Timestamp:

Nov 22, 2011, 11:47:22 AM (10 years ago)

Author:

Nicklas Nordborg

Message:

References #1630: Migrate from MySQL to PostgreSQL

Use approximate number of rows instead of counting the actual number of rows when exporting. The reason is that counting can take a long time (>1 hour on a big database) and is only used for progress reporting in to the console. The approximate row count is good enough for the progress reporting.

Fixed an issue with '0' characters in some strings when exporting. Importing to PostgreSQL caused the following error:

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
  Where: COPY RawBioAssayHeaders, line 58878

Location:

trunk/src/core/net/sf/basedb/core

Files:

• Migration.java (modified) (7 diffs)
• dbengine/AbstractDbEngine.java (modified) (1 diff)
• dbengine/DbEngine.java (modified) (1 diff)
• dbengine/MySQLEngine.java (modified) (1 diff)
• dbengine/PostgresDbEngine.java (modified) (1 diff)
• dbengine/TableInfo.java (modified) (4 diffs)

trunk/src/core/net/sf/basedb/core/Migration.java

@@ -261 +261 @@
     List<Table> dynamicTables = TableInfo.listTables(metaData, dynamicCatalog, dynamicSchema);
     progress.display(0, dynamicTables.size() + " tables in dynamic database\n");
-    progress.display(0, "Counting rows...");
+    progress.display(0, "Counting rows in all tables...");
     int totalTables = staticTables.size() + dynamicTables.size();
     
@@ -267 +267 @@
     // and count the number of data rows for each table
     List<TableInfo> tables = new ArrayList<TableInfo>();
-    long totalRows = 0;
+    long approxTotal = 0;
     for (Table table : staticTables)
     {
+      TableInfo info = new TableInfo(table, metaData);
+      long approxRows = info.getRowCount(engine, connection, true);
+      approxTotal += approxRows;
+      tables.add(info);
       if (System.currentTimeMillis() > nextTick)
       {
-        //progress.append(".");
         progress.display(0, "Counting rows in all tables (" + tables.size() + "/" + totalTables + ")...");
         nextTick = System.currentTimeMillis()+TICK_INTERVAL;
       }
+    }
+    for (Table table : dynamicTables)
+    {
       TableInfo info = new TableInfo(table, metaData);
-      long tableRows = info.getRowCount(engine, connection);
-      totalRows += tableRows;
+      long approxRows = info.getRowCount(engine, connection, true);
+      approxTotal += approxRows;
       tables.add(info);
-    }
-    for (Table table : dynamicTables)
-    {
-      if (System.currentTimeMillis() > nextTick)
-      {
-        //progress.append(".");
+      if (System.currentTimeMillis() > nextTick || tables.size() == totalTables)
+      {
         progress.display(0, "Counting rows in all tables (" + tables.size() + "/" + totalTables + ")...");
         nextTick = System.currentTimeMillis()+TICK_INTERVAL;
       }
-      TableInfo info = new TableInfo(table, metaData);
-      long tableRows = info.getRowCount(engine, connection);
-      totalRows += tableRows;
-      tables.add(info);
-    }
-
-    progress.display(0, "Found " + Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n");
-    
+    }
+    
+    progress.display(0, "Found approximate " + Values.formatNumber(approxTotal / 1000000f, 2) + " million data rows\n");
     
     // Export data for each table...
-    long totalExported = 0;
+    long actualExported = 0;
     Set<Integer> usedSqlTypes = new HashSet<Integer>();
     for (TableInfo table : tables)
     {
       String tableName = table.getTable().getName();
-      progress.display((int)((totalExported * 100) / totalRows), 
+      progress.display((int)((actualExported * 100) / approxTotal), 
           tableName + ": loading...");
       nextTick = System.currentTimeMillis()+TICK_INTERVAL;
@@ -337 +334 @@
           new OutputStreamWriter(FileUtil.getOutputStream(exportFile), "UTF-8"));
 
-      long tableRows = table.getRowCount(engine, connection);
+      long approxRows = table.getRowCount(engine, connection, true);
       ResultSet rows = st.executeQuery(select.toString());
-      int numRows = 0;
+      int actualRows = 0;
       while (rows.next())
       {
@@ -345 +342 @@
         if (System.currentTimeMillis() > nextTick)
         {
-          progress.display((int)((totalExported * 100) / totalRows), 
-              tableName + ": exporting... (" + numRows + " of " + tableRows + " rows done)");
+          progress.display((int)((actualExported * 100) / approxTotal), 
+              tableName + ": exporting... (" + actualRows + " of " + approxRows + " rows done)");
           nextTick = System.currentTimeMillis() + TICK_INTERVAL;
         }
@@ -361 +358 @@
         
         // Keep track of rows
-        totalExported++;
-        numRows++;
+        actualRows++;
+        actualExported++;
+        if (actualRows > approxRows)
+        {
+          // Increase the approximate row count
+          approxTotal++;
+          approxRows++;
+        }
+        
+      }
+      if (actualRows < approxRows)
+      {
+        // Decrease the approximate total row count
+        approxTotal += actualRows - approxRows;
       }
 
       // Done with this table...
-      progress.display((int)((totalExported * 100) / totalRows), 
-          tableName + ": complete (" + tableRows + " rows)                   \n");
+      progress.display((int)((actualExported * 100) / approxTotal), 
+          tableName + ": complete (" + actualRows + " rows)                   \n");
       writer.flush();
       writer.close();
@@ -616 +625 @@
   }
   
+  private static String zero = new String(new char[] { 0 });
+
   /**
     Format the value to a string that PostgreSQL accepts in the 
@@ -652 +663 @@
           s = s.replace("\r", "\\r");
           s = s.replace("\t", "\\t");
+          s = s.replace(zero, ""); // a zero-character can't be stored in PostgreSQL so we simply remove it
           formatted = s;
           break;

trunk/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

@@ -226 +226 @@
     return false;
   }
+  /**
+    @return null, since this depends on capabilities of underlying database
+  */
+  @Override
+  public String getApproximateRowCountSql(String catalog, String schema, String table)
+  {
+    return null;
+  }
   // -------------------------------------------
 

trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

@@ -380 +380 @@
   public boolean useSavePointToContinueTransactionFromSqlFailure();
   
+  /**
+    Get an SQL statement that returns an approximate count for the number
+    of rows in the given table. The SQL query should return a single row
+    with a single value.
+    
+    @param catalog The name of the catalog (database) where the table is
+      located, or null if it is located in the current catalog
+    @param schema The name of the schema where the table is located, or
+      null if is located in the current schema
+    @param table The name of the table
+    @return An SQL query or null
+    @since 3.1
+  */
+  public String getApproximateRowCountSql(String catalog, String schema, String table);
+  
 }

trunk/src/core/net/sf/basedb/core/dbengine/MySQLEngine.java

@@ -229 +229 @@
     return "`" + name + "`";
   }
+  
+  @Override
+  public String getApproximateRowCountSql(String catalog, String schema, String table)
+  {
+    return "select TABLE_ROWS from INFORMATION_SCHEMA.TABLES where TABLE_NAME='" + table + "' and TABLE_SCHEMA='" + catalog + "'";
+  }
+
   // -------------------------------------------
 

trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

@@ -285 +285 @@
     return true;
   }
+  
+  @Override
+  public String getApproximateRowCountSql(String catalog, String schema, String table)
+  {
+    return "select reltuples from pg_class where relname = '" + table + "'";
+  }
   // -------------------------------------------
 
+
   
 

trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

@@ -103 +103 @@
   private Set<IndexInfo> indexes;
   private long numberOfRows = -1;
+  private long approximateNumberOfRows = -1;
   
   
@@ -481 +482 @@
     @since 3.1
   */
-  public long getRowCount(DbEngine engine, Connection connection)
+  public long getRowCount(DbEngine engine, Connection connection, boolean approximate)
     throws SQLException
   {
-    if (numberOfRows < 0)
+    if ((numberOfRows < 0 && !approximate) || (approximateNumberOfRows < 0 && approximate))
     {
       
@@ -492 +493 @@
       {
         st = connection.createStatement();
-        StringBuilder sql = new StringBuilder("SELECT count(*) FROM ");
-        sql.append(getFullQuotedTableName(engine));
-        result = st.executeQuery(sql.toString());
+        String sql = null;
+        if (approximate)
+        {
+          sql = engine.getApproximateRowCountSql(table.getCatalog(), table.getSchema(), table.getName());
+        }
+        if (sql == null)
+        {
+          sql = "SELECT count(*) FROM " + getFullQuotedTableName(engine);
+        }
+        result = st.executeQuery(sql);
         result.next();
-        numberOfRows = result.getLong(1);
+        long count = result.getLong(1);
+        if (approximate)
+        {
+          approximateNumberOfRows = count;
+        }
+        else 
+        {
+          numberOfRows = count;
+        }
       }
       finally
@@ -504 +520 @@
       }
     }
-    return numberOfRows;
+    return approximate ? approximateNumberOfRows : numberOfRows;
   }