HsqlDbBuilder Bug (?)

[Wilfred Springer]

Hi all,

I'm not entirely sure if you would call this a bug, but according to the HSQLDB documentation http://www.hsqldb.org/doc/guide/ch09.html#create_index-section , creating unique indexes is deprecated. If you *do* create unique indexes, then you will out that HSQLDB does not support foreign keys to tables with unique indexes spanning multiple columns. However, if you use the alter table statement to add a unique constraint, then all works out fine.

This has been haunting me for a day, so I created a fix for it. (Find the patch file in the attachment.) It would be great if this could go into the main trunk.

Thanks,

Wilfred

Wilfred Springer | Software Architect | TomTom | [EMAIL PROTECTED]

Index: src/java/org/apache/ddlutils/platform/hsqldb/HsqlDbBuilder.java
===================================================================
--- src/java/org/apache/ddlutils/platform/hsqldb/HsqlDbBuilder.java	(revision 387801)
+++ src/java/org/apache/ddlutils/platform/hsqldb/HsqlDbBuilder.java	(working copy)
@@ -21,6 +21,10 @@
 import org.apache.ddlutils.Platform;
 import org.apache.ddlutils.model.Table;
 import org.apache.ddlutils.platform.SqlBuilder;
+import org.apache.ddlutils.DynaSqlException;
+import org.apache.ddlutils.model.Index;
+import org.apache.ddlutils.model.IndexColumn;
+import org.apache.ddlutils.model.Column;
 
 /**
  * The SQL Builder for the HsqlDb database.
@@ -60,4 +64,39 @@
     {
         return "CALL IDENTITY()";
     }
+
+    /**
+     * [EMAIL PROTECTED]
+     *
+     * In case of a unique index, this implementation will generate an
+     * ALTER TABLE ADD CONSTRAINT statement instead, in line with <a
+     * href="http://www.hsqldb.org/doc/guide/ch09.html#create_index-section";>the
+     * HSQLDB documentation</a>.
+     *
+     * @see http://www.hsqldb.org/doc/guide/ch09.html#create_index-section
+     */
+    protected void writeExternalIndexCreateStmt(Table table, Index index) throws IOException {
+        if (index.isUnique()) {
+            print("ALTER TABLE ");
+            printIdentifier(getTableName(table));
+            print(" ADD CONSTRAINT ");
+            printIdentifier(getIndexName(index));
+            print(" UNIQUE (");
+            for (int idx = 0; idx < index.getColumnCount(); idx++) {
+                IndexColumn idxColumn = index.getColumn(idx);
+                Column col = table.findColumn(idxColumn.getName());
+                if (col == null) {
+                    //would get null pointer on next line anyway, so throw exception
+                    throw new DynaSqlException("Invalid column '" + idxColumn.getName() + "' on index " + index.getName() + " for table " + table.getName());
+                }
+                if (idx > 0) {
+                    print(", ");
+                }
+                printIdentifier(getColumnName(col));
+            }
+            print(")");
+            printEndOfStatement();
+        }
+    }
+
 }