You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by to...@apache.org on 2006/05/21 22:42:19 UTC
svn commit: r408489 - in /db/ddlutils/trunk/src:
java/org/apache/ddlutils/platform/sybase/ test/org/apache/ddlutils/platform/
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));