Changeset 5852

Timestamp:

Nov 7, 2011, 9:16:54 AM (10 years ago)

Author:

Nicklas Nordborg

Message:

References #1630: Migrate from MySQL to PostgreSQL

Implemented the export functionality. It will currently only work on a MySQL database and generates a data file for each table. Hopefully, the files can be read by PostgreSQL, but we need to be able to create an empty database without foreign keys first...

Location:

trunk/src

Files:

• core/net/sf/basedb/core/dbengine/AbstractDbEngine.java (modified) (1 diff)
• core/net/sf/basedb/core/dbengine/DbEngine.java (modified) (1 diff)
• core/net/sf/basedb/core/dbengine/DefaultDbEngine.java (modified) (1 diff)
• core/net/sf/basedb/core/dbengine/MySQLEngine.java (modified) (1 diff)
• core/net/sf/basedb/core/dbengine/PostgresDbEngine.java (modified) (1 diff)
• core/net/sf/basedb/core/dbengine/TableInfo.java (modified) (8 diffs)
• install/net/sf/basedb/install/InitDB.java (modified) (3 diffs)

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

@@ -207 +207 @@
     return sql;
   }
-
+  /**
+    Put quotes (") around the name.
+  */
+  @Override
+  public String getQuotedName(String name)
+  {
+    return "\"" + name + "\"";
+  }
   // -------------------------------------------
 

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

@@ -42 +42 @@
 public interface DbEngine
 {
+  /**
+    Quote the name with the database specific quote character.
+    
+    @param name The name of an object in the database
+    @return A quoted name
+    @since 3.1
+  */
+  public String getQuotedName(String name);
   
   /**

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

@@ -150 +150 @@
   // -------------------------------------------
 
-  /**
-    Put quotes (") around the name.
-  */
-  private String getQuotedName(String name)
-  {
-    return "\"" + name + "\"";
-  }
+
 }

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

@@ -221 +221 @@
     return "varchar(" + length + ") CHARACTER SET utf8 COLLATE utf8_bin";
   }
+  /**
+    Put quotes (`) around the name.
+  */
+  @Override
+  public String getQuotedName(String name)
+  {
+    return "`" + name + "`";
+  }
   // -------------------------------------------
-  
-  /**
-    Put quotes (`) around the name.
-  */
-  private String getQuotedName(String name)
-  {
-    return "`" + name + "`";
-  }
+
 }

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

@@ -258 +258 @@
   // -------------------------------------------
   
-  /**
-    Put quotes (") around the name.
-  */
-  private String getQuotedName(String name)
-  {
-    return "\"" + name + "\"";
-  }
 
 }

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

@@ -22 +22 @@
 package net.sf.basedb.core.dbengine;
 
+import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.Types;
 import java.util.Arrays;
@@ -32 +34 @@
 import java.util.Iterator;
 import java.util.LinkedHashSet;
+import java.util.LinkedList;
 import java.util.List;
 import java.util.Map;
@@ -63 +66 @@
 {
 
-  private Table table;
+  /**
+    List all tables that are present in the given catalog and/or schema.
+    @param metaData A metadata object that is connected to a database
+    @param catalog The name of the catalog, or null
+    @param schema The name of the schema, or null
+    @return A list of tables
+    @throws SQLException
+    @since 3.1
+  */
+  public static List<Table> listTables(DatabaseMetaData metaData, String catalog, String schema)
+    throws SQLException
+  {
+    List<Table> tables = new LinkedList<Table>();
+    ResultSet result = metaData.getTables(catalog, schema, null, null);
+    while (result.next())
+    {
+      String tableName = result.getString("TABLE_NAME");
+      String tableCatalog = result.getString("TABLE_CAT");
+      String tableSchema = result.getString("TABLE_SCHEM");
+      Table table = new Table();
+      table.setName(tableName);
+      table.setCatalog(tableCatalog);
+      table.setSchema(tableSchema);
+      tables.add(table);
+    }
+    return tables;
+  }
+
+  
+  private final Table table;
   private Dialect dialect;
   
@@ -70 +102 @@
   private Set<ForeignKeyInfo> foreignKeys;
   private Set<IndexInfo> indexes;
+  private long numberOfRows = -1;
+  
   
   
@@ -260 +294 @@
     }
 
+  }
+  
+  /**
+    Get the table this information is representing.
+    @return The table object
+    @since 3.1
+  */
+  public Table getTable()
+  {
+    return table;
   }
   
@@ -371 +415 @@
   }
 
+  /**
+    Get the fully quialified and quoted table name using
+    the quoting rules from the given DBEngine
+    @param engine A DbEngine
+    @return The fully quoted table name
+    @since 3.1
+  */
+  public String getFullQuotedTableName(DbEngine engine)
+  {
+    StringBuilder name = new StringBuilder();
+    if (table.getCatalog() != null) 
+    {
+      name.append(engine.getQuotedName(table.getCatalog())).append(".");
+    }
+    if (table.getSchema() != null) 
+    {
+      name.append(engine.getQuotedName(table.getSchema())).append(".");
+    }
+    name.append(engine.getQuotedName(table.getName()));
+    return name.toString();
+  }
+  
+  /**
+    Count the number of rows in the table.
+    @param connection The connection to user for counting
+    @since 3.1
+  */
+  public long getRowCount(DbEngine engine, Connection connection)
+    throws SQLException
+  {
+    if (numberOfRows < 0)
+    {
+      
+      Statement st = null;
+      ResultSet result = null;
+      try
+      {
+        st = connection.createStatement();
+        StringBuilder sql = new StringBuilder("SELECT count(*) FROM ");
+        sql.append(getFullQuotedTableName(engine));
+        result = st.executeQuery(sql.toString());
+        result.next();
+        numberOfRows = result.getLong(1);
+      }
+      finally
+      {
+        if (result != null) result.close();
+        if (st != null) st.close();
+      }
+    }
+    return numberOfRows;
+  }
+  
   private static final Set<Integer> STRING_TYPE = new HashSet<Integer>(
       Arrays.asList(
@@ -381 +478 @@
   */
   public static class ColumnInfo
+    implements Comparable<ColumnInfo>
   {
     private final String name;
@@ -449 +547 @@
         (isUnique ? " UNIQUE" : "");
     }
+
+    @Override
+    public int compareTo(ColumnInfo o)
+    {
+      return name.compareTo(o.name);
+    }
   }
   

trunk/src/install/net/sf/basedb/install/InitDB.java

@@ -32 +32 @@
 import net.sf.basedb.core.HibernateUtil;
 import net.sf.basedb.core.Install;
+import net.sf.basedb.core.Migration;
 import net.sf.basedb.core.ProgressReporter;
 import net.sf.basedb.core.Update;
@@ -125 +126 @@
         Application.stop();
       }
+      else if ("migrate".equals(cmd))
+      {
+        // args[1] = 'import' or 'export'
+        // args[2] = path to output dir
+        if (args.length != 3)
+        {
+          showUsage(cmd);
+          return;
+        }
+        boolean export = "export".equals(args[1]);
+        
+        showDbInfo(null);
+        String path = args[2];
+        if (export)
+        {
+          Application.start(false);
+          Migration.exportAll(path, new ConsoleProgressReporter(false));
+        }
+        else
+        {
+          // Create empty database without indexes and foreign keys
+          // Import all data
+          // Add indexes, foreign keys, etc. to database
+        }
+        Application.stop();
+      }
       else 
       {
@@ -151 +178 @@
       System.out.println("       root password: The password to give to the root user account");
     }
-    
+    else if ("migrate".equals(cmd))
+    {
+      System.out.println("Usage: migrate.sh <cmd> <path>");
+      System.out.println("       cmd    : export | import");
+      System.out.println("       path   : The path to a directory where data files should be saved/read");
+    }
   }