Changeset 4099

Timestamp:

Jan 23, 2008, 4:37:23 PM (15 years ago)

Author:

Nicklas Nordborg

Message:

References #888: Make dynamic table creation code more stable

Now works for MySQL. Need to investigate if it is possible to solve for Postgres as well.

Location:

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

Files:

• HibernateUtil.java (modified) (2 diffs)
• dbengine/AbstractDbEngine.java (modified) (1 diff)
• dbengine/DbEngine.java (modified) (1 diff)
• dbengine/MySQLEngine.java (modified) (2 diffs)

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

@@ -575 +575 @@
       tx = HibernateUtil.newTransaction(session);
       Connection connection = HibernateUtil.getConnection(session);
-
-      if (!tableExists(table.getTableName(db), connection))
+      String tableName = table.getTableName(db);
+
+      if (!tableExists(tableName, connection))
       {
         Table hibernateTable = newDynamicMapping(db, table);
         Dialect dialect = getDialect();
-        String dynamicCatalog = quote(Application.getDynamicCatalog());
-        String dynamicSchema = quote(Application.getDynamicSchema());
-
-        String sql = hibernateTable.sqlCreateString(dialect, null, dynamicCatalog, dynamicSchema);
+        String dynamicCatalog = Application.getDynamicCatalog();
+        String quotedCatalog = quote(dynamicCatalog);
+        String dynamicSchema = Application.getDynamicSchema();
+        String quotedSchema = quote(dynamicSchema);
+
+        String sql = hibernateTable.sqlCreateString(dialect, null, quotedCatalog, quotedSchema);
+        sql = dbEngine.makeSafeCreateTable(sql, dynamicCatalog, dynamicSchema, tableName);
+        System.out.println(sql);
         logSql.debug(sql);
         
@@ -593 +598 @@
         {
           Index ii = (Index)i.next();
-          sql = ii.sqlCreateString(dialect, null, dynamicCatalog, dynamicSchema);
+          sql = ii.sqlCreateString(dialect, null, quotedCatalog, quotedSchema);
           logSql.debug(sql);
           s.executeUpdate(sql);

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

@@ -53 +53 @@
     -------------------------------------------
   */
+  /**
+    Return the SQL unmodified.
+    @since 2.6
+  */
+  public String makeSafeCreateTable(String sql, String catalog, String schema, String table)
+  {
+    return sql;
+  }
   /**
     Most databases doesn't. Any more than Oracle?

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

@@ -135 +135 @@
   
   /**
+    Create a "safe" CREATE TABLE query string that will not fail if the table already
+    exists. Implementors should inject proper SQL code in a way that is supported in the
+    underlying database. The MySQL engine, for example, replaces CREATE TABLE with
+    CREATE TABLE IF NOT EXISTS. If the underlying database doesn't support this kind
+    of check the original SQL should be returned umodified.
+    
+    @param sql The original SQL
+    @param catalog
+    @param catalog The name of the catalog (database) where the table is
+      being create, or null if it is located in the current catalog
+    @param schema The name of the schema where the table is being created, or
+      null if is located in the current schema
+    @param table The name of the table that is being created
+    @return The modified or unmodified SQL statement
+    @since 2.6
+  */
+  public String makeSafeCreateTable(String sql, String catalog, String schema, String table);
+  
+  /**
     If the database does case sensitive or case insensitive string
     comparison in expressions.

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

@@ -25 +25 @@
 
 import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
 
 /**
@@ -123 +125 @@
 
   /**
+    Replace CREATE TABLE with CREATE TABLE IF NOT EXISTS...
+    @since 2.6
+  */
+  public String makeSafeCreateTable(String sql, String catalog, String schema, String table)
+  {
+    Pattern p = Pattern.compile("CREATE TABLE", Pattern.CASE_INSENSITIVE);
+    Matcher m = p.matcher(sql);
+    sql = m.replaceFirst("CREATE TABLE IF NOT EXISTS");
+    return sql;
+  }
+  
+  /**
     Returns TRUE.
     @since 2.4