db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject svn commit: r408489 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/platform/sybase/ test/org/apache/ddlutils/platform/
Date Sun, 21 May 2006 20:42:19 GMT
Author: tomdz
Date: Sun May 21 13:42:19 2006
New Revision: 408489

URL: http://svn.apache.org/viewvc?rev=408489&view=rev
Log:
Fixed/enhanced Sybase platform

Modified:
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseBuilder.java
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseModelReader.java
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybasePlatform.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestSybasePlatform.java

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseBuilder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseBuilder.java?rev=408489&r1=408488&r2=408489&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseBuilder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseBuilder.java Sun
May 21 13:42:19 2006
@@ -20,13 +20,28 @@
 import java.io.StringWriter;
 import java.io.Writer;
 import java.sql.Types;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.List;
 import java.util.Map;
 
 import org.apache.ddlutils.Platform;
+import org.apache.ddlutils.alteration.AddColumnChange;
+import org.apache.ddlutils.alteration.AddPrimaryKeyChange;
+import org.apache.ddlutils.alteration.ColumnAutoIncrementChange;
+import org.apache.ddlutils.alteration.ColumnChange;
+import org.apache.ddlutils.alteration.ColumnDefaultValueChange;
+import org.apache.ddlutils.alteration.PrimaryKeyChange;
+import org.apache.ddlutils.alteration.RemoveColumnChange;
+import org.apache.ddlutils.alteration.RemovePrimaryKeyChange;
+import org.apache.ddlutils.alteration.TableChange;
 import org.apache.ddlutils.model.Column;
 import org.apache.ddlutils.model.Database;
 import org.apache.ddlutils.model.ForeignKey;
+import org.apache.ddlutils.model.Index;
 import org.apache.ddlutils.model.Table;
+import org.apache.ddlutils.platform.CreationParameters;
 import org.apache.ddlutils.platform.SqlBuilder;
 import org.apache.ddlutils.util.Jdbc3Utils;
 
@@ -68,16 +83,24 @@
         print(" ");
         print(getSqlType(column));
         writeColumnDefaultValueStmt(table, column);
-        // Sybase does not like NOT NULL and IDENTITY together
-        if (column.isRequired() && !column.isAutoIncrement())
+        // Sybase does not like NULL/NOT NULL and IDENTITY together
+        if (column.isAutoIncrement())
         {
             print(" ");
-            writeColumnNotNullableStmt();
+            writeColumnAutoIncrementStmt(table, column);
         }
-        if (column.isAutoIncrement())
+        else
         {
             print(" ");
-            writeColumnAutoIncrementStmt(table, column);
+            if (column.isRequired())
+            {
+                writeColumnNotNullableStmt();
+            }
+            else
+            {
+                // we'll write a NULL for all columns that are not required 
+                writeColumnNullableStmt();
+            }
         }
 	}
 
@@ -160,6 +183,18 @@
     /**
      * {@inheritDoc}
      */
+    public void writeExternalIndexDropStmt(Table table, Index index) throws IOException
+    {
+        print("DROP INDEX ");
+        printIdentifier(getTableName(table));
+        print(".");
+        printIdentifier(getIndexName(index));
+        printEndOfStatement();
+    }
+
+    /**
+     * {@inheritDoc}
+     */
     public void dropExternalForeignKeys(Table table) throws IOException
     {
         writeQuotationOnStatement();
@@ -189,5 +224,341 @@
         print("'");
         print(identifier);
         print("'");
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void writeCastExpression(Column sourceColumn, Column targetColumn) throws IOException
+    {
+        String sourceNativeType = getBareNativeType(sourceColumn);
+        String targetNativeType = getBareNativeType(targetColumn);
+
+        if (sourceNativeType.equals(targetNativeType))
+        {
+            printIdentifier(getColumnName(sourceColumn));
+        }
+        else
+        {
+            print("CONVERT(");
+            print(getNativeType(targetColumn));
+            print(",");
+            printIdentifier(getColumnName(sourceColumn));
+            print(")");
+        }
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void processChanges(Database currentModel, Database desiredModel, List changes,
CreationParameters params) throws IOException
+    {
+        if (!changes.isEmpty())
+        {
+            writeQuotationOnStatement();
+        }
+        super.processChanges(currentModel, desiredModel, changes, params);
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected void processTableStructureChanges(Database currentModel,
+                                                Database desiredModel,
+                                                Table    sourceTable,
+                                                Table    targetTable,
+                                                Map      parameters,
+                                                List     changes) throws IOException
+    {
+        // First we drop primary keys as necessary
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof RemovePrimaryKeyChange)
+            {
+                processChange(currentModel, desiredModel, (RemovePrimaryKeyChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof PrimaryKeyChange)
+            {
+                PrimaryKeyChange       pkChange       = (PrimaryKeyChange)change;
+                RemovePrimaryKeyChange removePkChange = new RemovePrimaryKeyChange(pkChange.getChangedTable(),
+                                                                                   pkChange.getOldPrimaryKeyColumns());
+
+                processChange(currentModel, desiredModel, removePkChange);
+                removePkChange.apply(currentModel);
+            }
+        }
+
+
+        HashMap columnChanges = new HashMap();
+
+        // Next we add/remove columns
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof AddColumnChange)
+            {
+                AddColumnChange addColumnChange = (AddColumnChange)change;
+
+                // Oracle can only add not insert columns
+                if (addColumnChange.isAtEnd())
+                {
+                    processChange(currentModel, desiredModel, addColumnChange);
+                    change.apply(currentModel);
+                    changeIt.remove();
+                }
+            }
+            else if (change instanceof RemoveColumnChange)
+            {
+                processChange(currentModel, desiredModel, (RemoveColumnChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof ColumnAutoIncrementChange)
+            {
+                // Sybase has no way of adding or removing an IDENTITY constraint
+                // Thus we have to rebuild the table anyway and can ignore all the other

+                // column changes
+                columnChanges = null;
+            }
+            else if ((change instanceof ColumnChange) && (columnChanges != null))
+            {
+                // we gather all changed columns because we can use the ALTER TABLE ALTER
COLUMN
+                // statement for them
+                ColumnChange columnChange     = (ColumnChange)change;
+                ArrayList    changesPerColumn = (ArrayList)columnChanges.get(columnChange.getChangedColumn());
+
+                if (changesPerColumn == null)
+                {
+                    changesPerColumn = new ArrayList();
+                    columnChanges.put(columnChange.getChangedColumn(), changesPerColumn);
+                }
+                changesPerColumn.add(change);
+            }
+        }
+        if (columnChanges != null)
+        {
+            for (Iterator changesPerColumnIt = columnChanges.entrySet().iterator(); changesPerColumnIt.hasNext();)
+            {
+                Map.Entry entry            = (Map.Entry)changesPerColumnIt.next();
+                Column    sourceColumn     = (Column)entry.getKey();
+                ArrayList changesPerColumn = (ArrayList)entry.getValue();
+
+                // Sybase does not like us to use the ALTER TABLE ALTER statement if we don't
actually
+                // change the datatype or the required constraint but only the default value
+                // Thus, if we only have to change the default, we use a different handler
+                if ((changesPerColumn.size() == 1) && (changesPerColumn.get(0) instanceof
ColumnDefaultValueChange))
+                {
+                    processChange(currentModel,
+                                  desiredModel,
+                                  (ColumnDefaultValueChange)changesPerColumn.get(0));
+                }
+                else
+                {
+                    Column targetColumn = targetTable.findColumn(sourceColumn.getName(),
+                                                                 getPlatform().isDelimitedIdentifierModeOn());
+
+                    processColumnChange(sourceTable, targetTable, sourceColumn, targetColumn);
+                }
+                for (Iterator changeIt = changesPerColumn.iterator(); changeIt.hasNext();)
+                {
+                    ((ColumnChange)changeIt.next()).apply(currentModel);
+                }
+            }
+        }
+        // Finally we add primary keys
+        for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
+        {
+            TableChange change = (TableChange)changeIt.next();
+
+            if (change instanceof AddPrimaryKeyChange)
+            {
+                processChange(currentModel, desiredModel, (AddPrimaryKeyChange)change);
+                change.apply(currentModel);
+                changeIt.remove();
+            }
+            else if (change instanceof PrimaryKeyChange)
+            {
+                PrimaryKeyChange    pkChange    = (PrimaryKeyChange)change;
+                AddPrimaryKeyChange addPkChange = new AddPrimaryKeyChange(pkChange.getChangedTable(),
+                                                                          pkChange.getNewPrimaryKeyColumns());
+
+                processChange(currentModel, desiredModel, addPkChange);
+                addPkChange.apply(currentModel);
+                changeIt.remove();
+            }
+        }
+    }
+
+
+    /**
+     * Processes the addition of a column to a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database        currentModel,
+                                 Database        desiredModel,
+                                 AddColumnChange change) throws IOException
+    {
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(change.getChangedTable()));
+        printIndent();
+        print("ADD ");
+        writeColumn(change.getChangedTable(), change.getNewColumn());
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes the removal of a column from a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database           currentModel,
+                                 Database           desiredModel,
+                                 RemoveColumnChange change) throws IOException
+    {
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(change.getChangedTable()));
+        printIndent();
+        print("DROP ");
+        printIdentifier(getColumnName(change.getColumn()));
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes the removal of a primary key from a table.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database               currentModel,
+                                 Database               desiredModel,
+                                 RemovePrimaryKeyChange change) throws IOException
+    {
+        // TODO: this would be easier when named primary keys are supported
+        //       because then we can use ALTER TABLE DROP
+        String tableName = getTableName(change.getChangedTable());
+
+        println("BEGIN");
+        println("  DECLARE @tablename nvarchar(60), @constraintname nvarchar(60)");
+        println("  WHILE EXISTS(SELECT sysindexes.name");
+        println("                 FROM sysindexes, sysobjects");
+        print("                 WHERE sysobjects.name = ");
+        printAlwaysSingleQuotedIdentifier(tableName);
+        println(" AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048) >
0)");
+        println("  BEGIN");
+        println("    SELECT @tablename = sysobjects.name, @constraintname = sysindexes.name");
+        println("      FROM sysindexes, sysobjects");
+        print("      WHERE sysobjects.name = ");
+        printAlwaysSingleQuotedIdentifier(tableName);
+        print(" AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048) >
0");
+        println("    EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)");
+        println("  END");
+        print("END");
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes the change of the default value of a column. Note that this method is only
+     * used if it is the only change to that column.
+     * 
+     * @param currentModel The current database schema
+     * @param desiredModel The desired database schema
+     * @param change       The change object
+     */
+    protected void processChange(Database                 currentModel,
+                                 Database                 desiredModel,
+                                 ColumnDefaultValueChange change) throws IOException
+    {
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(change.getChangedTable()));
+        printIndent();
+        print("REPLACE ");
+        printIdentifier(getColumnName(change.getChangedColumn()));
+
+        Table  curTable  = currentModel.findTable(change.getChangedTable().getName(), getPlatform().isDelimitedIdentifierModeOn());
+        Column curColumn = curTable.findColumn(change.getChangedColumn().getName(), getPlatform().isDelimitedIdentifierModeOn());
+
+        print(" DEFAULT ");
+        if (isValidDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode()))
+        {
+            printDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode());
+        }
+        else
+        {
+            print("NULL");
+        }
+        printEndOfStatement();
+    }
+
+    /**
+     * Processes a change to a column.
+     * 
+     * @param sourceTable  The current table
+     * @param targetTable  The desired table
+     * @param sourceColumn The current column
+     * @param targetColumn The desired column
+     */
+    protected void processColumnChange(Table  sourceTable,
+                                       Table  targetTable,
+                                       Column sourceColumn,
+                                       Column targetColumn) throws IOException
+    {
+        Object oldParsedDefault = sourceColumn.getParsedDefaultValue();
+        Object newParsedDefault = targetColumn.getParsedDefaultValue();
+        String newDefault       = targetColumn.getDefaultValue();
+        boolean defaultChanges  = ((oldParsedDefault == null) && (newParsedDefault
!= null)) ||
+                                  ((oldParsedDefault != null) && !oldParsedDefault.equals(newParsedDefault));
+
+        // Sybase does not like it if there is a default spec in the ALTER TABLE ALTER
+        // statement; thus we have to change the default afterwards
+        if (newDefault != null)
+        {
+            targetColumn.setDefaultValue(null);
+        }
+        if (defaultChanges)
+        {
+            // we're first removing the default as it might make problems when the
+            // datatype changes
+            print("ALTER TABLE ");
+            printlnIdentifier(getTableName(sourceTable));
+            printIndent();
+            print("REPLACE ");
+            printIdentifier(getColumnName(sourceColumn));
+            print(" DEFAULT NULL");
+            printEndOfStatement();
+        }
+        print("ALTER TABLE ");
+        printlnIdentifier(getTableName(sourceTable));
+        printIndent();
+        print("MODIFY ");
+        writeColumn(sourceTable, targetColumn);
+        printEndOfStatement();
+        if (defaultChanges)
+        {
+            print("ALTER TABLE ");
+            printlnIdentifier(getTableName(sourceTable));
+            printIndent();
+            print("REPLACE ");
+            printIdentifier(getColumnName(sourceColumn));
+            if (newDefault != null)
+            {
+                writeColumnDefaultValueStmt(sourceTable, targetColumn);
+            }
+            else
+            {
+                print(" DEFAULT NULL");
+            }
+            printEndOfStatement();
+        }
     }
 }

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseModelReader.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseModelReader.java?rev=408489&r1=408488&r2=408489&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseModelReader.java
(original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybaseModelReader.java
Sun May 21 13:42:19 2006
@@ -17,16 +17,23 @@
  */
 
 import java.sql.Date;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.Time;
 import java.sql.Timestamp;
 import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Collection;
 import java.util.Map;
 
 import org.apache.ddlutils.DdlUtilsException;
 import org.apache.ddlutils.Platform;
 import org.apache.ddlutils.model.Column;
+import org.apache.ddlutils.model.ForeignKey;
 import org.apache.ddlutils.model.Index;
+import org.apache.ddlutils.model.Reference;
 import org.apache.ddlutils.model.Table;
 import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
 import org.apache.ddlutils.platform.JdbcModelReader;
@@ -153,32 +160,117 @@
 	/**
      * {@inheritDoc}
      */
-    protected boolean isInternalPrimaryKeyIndex(DatabaseMetaDataWrapper metaData, Table table,
Index index)
+    protected Collection readForeignKeys(DatabaseMetaDataWrapper metaData, String tableName)
throws SQLException
     {
-        // Sybase defines a unique index "<table name>_<integer numer>" for primary
keys
-    	if (index.isUnique() && (index.getName() != null))
-    	{
-	    	int underscorePos = index.getName().lastIndexOf('_');
-	
-	    	if (underscorePos > 0)
-	    	{
-	    		String tableName = index.getName().substring(0, underscorePos);
-	    		String id        = index.getName().substring(underscorePos + 1);
-	
-	    		if (table.getName().startsWith(tableName))
-	    		{
-		    		try
-		    		{
-		    			Long.parseLong(id);
-		    			return true;
-		    		}
-		    		catch (NumberFormatException ex)
-		    		{
-		    			// we ignore it
-		    		}
-	    		}
-	    	}
-    	}
-    	return false;
+        // Sybase (or jConnect) does not return the foreign key names, thus we have to
+        // read the foreign keys manually from the system tables
+        StringBuffer query = new StringBuffer();
+
+        query.append("SELECT refobjs.name, localtables.id, remotetables.name, remotetables.id");
+        for (int idx = 1; idx <= 16; idx++)
+        {
+            query.append(", refs.fokey");
+            query.append(idx);
+            query.append(", refs.refkey");
+            query.append(idx);
+        }
+        query.append(" FROM sysreferences refs, sysobjects refobjs, sysobjects localtables,
sysobjects remotetables");
+        query.append(" WHERE refobjs.type = 'RI' AND refs.constrid = refobjs.id AND");
+        query.append(" localtables.type = 'U' AND refs.tableid = localtables.id AND localtables.name
= '");
+        query.append(tableName);
+        query.append("' AND remotetables.type = 'U' AND refs.reftabid = remotetables.id");
+
+        Statement         stmt     = getConnection().createStatement();
+        PreparedStatement prepStmt = getConnection().prepareStatement("SELECT name FROM syscolumns
WHERE id = ? AND colid = ?");
+        ArrayList         result   = new ArrayList();
+
+        try
+        {
+            ResultSet fkRs = stmt.executeQuery(query.toString());
+
+            while (fkRs.next())
+            {
+                ForeignKey fk            = new ForeignKey(fkRs.getString(1));
+                int        localTableId  = fkRs.getInt(2);
+                int        remoteTableId = fkRs.getInt(4);
+
+                fk.setForeignTableName(fkRs.getString(3));
+                for (int idx = 0; idx < 16; idx++)
+                {
+                    short     fkColIdx = fkRs.getShort(5 + idx + idx);
+                    short     pkColIdx = fkRs.getShort(6 + idx + idx);
+                    Reference ref      = new Reference();
+
+                    if (fkColIdx == 0)
+                    {
+                        break;
+                    }
+
+                    prepStmt.setInt(1, localTableId);
+                    prepStmt.setShort(2, fkColIdx);
+
+                    ResultSet colRs = prepStmt.executeQuery();
+
+                    if (colRs.next())
+                    {
+                        ref.setLocalColumnName(colRs.getString(1));
+                    }
+                    colRs.close();
+
+                    prepStmt.setInt(1, remoteTableId);
+                    prepStmt.setShort(2, pkColIdx);
+
+                    colRs = prepStmt.executeQuery();
+
+                    if (colRs.next())
+                    {
+                        ref.setForeignColumnName(colRs.getString(1));
+                    }
+                    colRs.close();
+
+                    fk.addReference(ref);
+                }
+                result.add(fk);
+            }
+
+            fkRs.close();
+        }
+        finally
+        {
+            stmt.close();
+            prepStmt.close();
+        }
+
+        return result;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected boolean isInternalPrimaryKeyIndex(DatabaseMetaDataWrapper metaData, Table table,
Index index) throws SQLException
+    {
+        // We can simply check the sysindexes table where a specific flag is set for pk indexes
+        StringBuffer query = new StringBuffer();
+
+        query.append("SELECT name = sysindexes.name FROM sysindexes, sysobjects WHERE sysobjects.name
= '");
+        query.append(table.getName());
+        query.append("' AND sysindexes.name = '");
+        query.append(index.getName());
+        query.append("' AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048)
> 0");
+        
+        Statement stmt = getConnection().createStatement();
+
+        try
+        {
+            ResultSet rs     = stmt.executeQuery(query.toString());
+            boolean   result = rs.next();
+
+            rs.close();
+            return result;
+        }
+        finally
+        {
+            stmt.close();
+        }
     }
 }

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybasePlatform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybasePlatform.java?rev=408489&r1=408488&r2=408489&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybasePlatform.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sybase/SybasePlatform.java Sun
May 21 13:42:19 2006
@@ -64,7 +64,7 @@
         PlatformInfo info = getPlatformInfo();
 
         info.setMaxIdentifierLength(28);
-        info.setNullAsDefaultValueRequired(false);
+        info.setNullAsDefaultValueRequired(true);
         info.setPrimaryKeyEmbedded(true);
         info.setForeignKeysEmbedded(false);
         info.setIndicesEmbedded(false);

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestSybasePlatform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestSybasePlatform.java?rev=408489&r1=408488&r2=408489&view=diff
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestSybasePlatform.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestSybasePlatform.java Sun May
21 13:42:19 2006
@@ -60,37 +60,37 @@
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"coltype\"\n"+
             "(\n"+
-            "    \"COL_ARRAY\"           IMAGE,\n"+
-            "    \"COL_BIGINT\"          DECIMAL(19,0),\n"+
-            "    \"COL_BINARY\"          BINARY(254),\n"+
-            "    \"COL_BIT\"             SMALLINT,\n"+
-            "    \"COL_BLOB\"            IMAGE,\n"+
-            "    \"COL_BOOLEAN\"         SMALLINT,\n"+
-            "    \"COL_CHAR\"            CHAR(15),\n"+
-            "    \"COL_CLOB\"            TEXT,\n"+
-            "    \"COL_DATALINK\"        IMAGE,\n"+
-            "    \"COL_DATE\"            DATETIME,\n"+
-            "    \"COL_DECIMAL\"         DECIMAL(15,3),\n"+
-            "    \"COL_DECIMAL_NOSCALE\" DECIMAL(15,0),\n"+
-            "    \"COL_DISTINCT\"        IMAGE,\n"+
-            "    \"COL_DOUBLE\"          DOUBLE PRECISION,\n"+
-            "    \"COL_FLOAT\"           DOUBLE PRECISION,\n"+
-            "    \"COL_INTEGER\"         INT,\n"+
-            "    \"COL_JAVA_OBJECT\"     IMAGE,\n"+
-            "    \"COL_LONGVARBINARY\"   IMAGE,\n"+
-            "    \"COL_LONGVARCHAR\"     TEXT,\n"+
-            "    \"COL_NULL\"            IMAGE,\n"+
-            "    \"COL_NUMERIC\"         NUMERIC(15,0),\n"+
-            "    \"COL_OTHER\"           IMAGE,\n"+
-            "    \"COL_REAL\"            REAL,\n"+
-            "    \"COL_REF\"             IMAGE,\n"+
-            "    \"COL_SMALLINT\"        SMALLINT,\n"+
-            "    \"COL_STRUCT\"          IMAGE,\n"+
-            "    \"COL_TIME\"            DATETIME,\n"+
-            "    \"COL_TIMESTAMP\"       DATETIME,\n"+
-            "    \"COL_TINYINT\"         SMALLINT,\n"+
-            "    \"COL_VARBINARY\"       VARBINARY(15),\n"+
-            "    \"COL_VARCHAR\"         VARCHAR(15)\n"+
+            "    \"COL_ARRAY\"           IMAGE NULL,\n"+
+            "    \"COL_BIGINT\"          DECIMAL(19,0) NULL,\n"+
+            "    \"COL_BINARY\"          BINARY(254) NULL,\n"+
+            "    \"COL_BIT\"             SMALLINT NULL,\n"+
+            "    \"COL_BLOB\"            IMAGE NULL,\n"+
+            "    \"COL_BOOLEAN\"         SMALLINT NULL,\n"+
+            "    \"COL_CHAR\"            CHAR(15) NULL,\n"+
+            "    \"COL_CLOB\"            TEXT NULL,\n"+
+            "    \"COL_DATALINK\"        IMAGE NULL,\n"+
+            "    \"COL_DATE\"            DATETIME NULL,\n"+
+            "    \"COL_DECIMAL\"         DECIMAL(15,3) NULL,\n"+
+            "    \"COL_DECIMAL_NOSCALE\" DECIMAL(15,0) NULL,\n"+
+            "    \"COL_DISTINCT\"        IMAGE NULL,\n"+
+            "    \"COL_DOUBLE\"          DOUBLE PRECISION NULL,\n"+
+            "    \"COL_FLOAT\"           DOUBLE PRECISION NULL,\n"+
+            "    \"COL_INTEGER\"         INT NULL,\n"+
+            "    \"COL_JAVA_OBJECT\"     IMAGE NULL,\n"+
+            "    \"COL_LONGVARBINARY\"   IMAGE NULL,\n"+
+            "    \"COL_LONGVARCHAR\"     TEXT NULL,\n"+
+            "    \"COL_NULL\"            IMAGE NULL,\n"+
+            "    \"COL_NUMERIC\"         NUMERIC(15,0) NULL,\n"+
+            "    \"COL_OTHER\"           IMAGE NULL,\n"+
+            "    \"COL_REAL\"            REAL NULL,\n"+
+            "    \"COL_REF\"             IMAGE NULL,\n"+
+            "    \"COL_SMALLINT\"        SMALLINT NULL,\n"+
+            "    \"COL_STRUCT\"          IMAGE NULL,\n"+
+            "    \"COL_TIME\"            DATETIME NULL,\n"+
+            "    \"COL_TIMESTAMP\"       DATETIME NULL,\n"+
+            "    \"COL_TINYINT\"         SMALLINT NULL,\n"+
+            "    \"COL_VARBINARY\"       VARBINARY(15) NULL,\n"+
+            "    \"COL_VARCHAR\"         VARCHAR(15) NULL\n"+
             ");\n",
             createTestDatabase(COLUMN_TEST_SCHEMA));
     }
@@ -111,11 +111,11 @@
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"constraints\"\n"+
             "(\n"+
-            "    \"COL_PK\"               VARCHAR(32),\n"+
+            "    \"COL_PK\"               VARCHAR(32) NULL,\n"+
             "    \"COL_PK_AUTO_INCR\"     INT IDENTITY,\n"+
             "    \"COL_NOT_NULL\"         BINARY(100) NOT NULL,\n"+
             "    \"COL_NOT_NULL_DEFAULT\" DOUBLE PRECISION DEFAULT -2.0 NOT NULL,\n"+
-            "    \"COL_DEFAULT\"          CHAR(4) DEFAULT 'test',\n"+
+            "    \"COL_DEFAULT\"          CHAR(4) DEFAULT 'test' NULL,\n"+
             "    \"COL_AUTO_INCR\"        DECIMAL(19,0) IDENTITY,\n"+
             "    PRIMARY KEY (\"COL_PK\", \"COL_PK_AUTO_INCR\")\n"+
             ");\n",
@@ -154,10 +154,10 @@
             "CREATE TABLE \"table1\"\n"+
             "(\n"+
             "    \"COL_PK_1\"    VARCHAR(32) NOT NULL,\n"+
-            "    \"COL_PK_2\"    INT,\n"+
+            "    \"COL_PK_2\"    INT NULL,\n"+
             "    \"COL_INDEX_1\" BINARY(100) NOT NULL,\n"+
             "    \"COL_INDEX_2\" DOUBLE PRECISION NOT NULL,\n"+
-            "    \"COL_INDEX_3\" CHAR(4),\n"+
+            "    \"COL_INDEX_3\" CHAR(4) NULL,\n"+
             "    PRIMARY KEY (\"COL_PK_1\", \"COL_PK_2\")\n"+
             ");\n"+
             "CREATE INDEX \"testindex1\" ON \"table1\" (\"COL_INDEX_2\");\n"+
@@ -165,15 +165,15 @@
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"table2\"\n"+
             "(\n"+
-            "    \"COL_PK\"   INT,\n"+
-            "    \"COL_FK_1\" INT,\n"+
+            "    \"COL_PK\"   INT NULL,\n"+
+            "    \"COL_FK_1\" INT NULL,\n"+
             "    \"COL_FK_2\" VARCHAR(32) NOT NULL,\n"+
             "    PRIMARY KEY (\"COL_PK\")\n"+
             ");\n"+
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"table3\"\n"+
             "(\n"+
-            "    \"COL_PK\" VARCHAR(16),\n"+
+            "    \"COL_PK\" VARCHAR(16) NULL,\n"+
             "    \"COL_FK\" INT NOT NULL,\n"+
             "    PRIMARY KEY (\"COL_PK\")\n"+
             ");\n"+
@@ -197,8 +197,8 @@
             "SET quoted_identifier on;\n"+
             "CREATE TABLE \"escapedcharacters\"\n"+
             "(\n"+
-            "    \"COL_PK\"   INT,\n"+
-            "    \"COL_TEXT\" VARCHAR(128) DEFAULT '\'\'',\n"+
+            "    \"COL_PK\"   INT NULL,\n"+
+            "    \"COL_TEXT\" VARCHAR(128) DEFAULT '\'\'' NULL,\n"+
             "    PRIMARY KEY (\"COL_PK\")\n"+
             ");\n",
             createTestDatabase(COLUMN_CHAR_SEQUENCES_TO_ESCAPE));



Mime
View raw message