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/07/02 23:53:39 UTC
svn commit: r418654 - in /db/ddlutils/trunk/src:
java/org/apache/ddlutils/platform/oracle/ test/org/apache/ddlutils/platform/
Author: tomdz
Date: Sun Jul 2 14:53:39 2006
New Revision: 418654
URL: http://svn.apache.org/viewvc?rev=418654&view=rev
Log:
Fixes/enhancements to the Oracle platforms
Modified:
db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle10Builder.java
db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8Builder.java
db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java
db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle8Platform.java
db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle9Platform.java
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle10Builder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle10Builder.java?rev=418654&r1=418653&r2=418654&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle10Builder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle10Builder.java Sun Jul 2 14:53:39 2006
@@ -47,21 +47,17 @@
{
// The only difference to the Oracle 8/9 variant is the purge which prevents the
// table from being moved to the recycle bin (which is new in Oracle 10)
- print("DROP TABLE ");
- printIdentifier(getTableName(table));
- print(" CASCADE CONSTRAINTS PURGE");
- printEndOfStatement();
-
Column[] columns = table.getAutoIncrementColumns();
for (int idx = 0; idx < columns.length; idx++)
{
- print("DROP TRIGGER ");
- printIdentifier(getConstraintName("trg", table, columns[idx].getName(), null));
- printEndOfStatement();
- print("DROP SEQUENCE ");
- printIdentifier(getConstraintName("seq", table, columns[idx].getName(), null));
- printEndOfStatement();
+ dropAutoIncrementTrigger(table, columns[idx]);
+ dropAutoIncrementSequence(table, columns[idx]);
}
+
+ print("DROP TABLE ");
+ printIdentifier(getTableName(table));
+ print(" CASCADE CONSTRAINTS PURGE");
+ printEndOfStatement();
}
}
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8Builder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8Builder.java?rev=418654&r1=418653&r2=418654&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8Builder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8Builder.java Sun Jul 2 14:53:39 2006
@@ -86,91 +86,155 @@
/**
* {@inheritDoc}
*/
+ public void createTable(Database database, Table table, Map parameters) throws IOException
+ {
+ // lets create any sequences
+ Column[] columns = table.getAutoIncrementColumns();
+
+ for (int idx = 0; idx < columns.length; idx++)
+ {
+ createAutoIncrementSequence(table, columns[idx]);
+ }
+
+ super.createTable(database, table, parameters);
+
+ for (int idx = 0; idx < columns.length; idx++)
+ {
+ createAutoIncrementTrigger(table, columns[idx]);
+ }
+ }
+
+ /**
+ * {@inheritDoc}
+ */
public void dropTable(Table table) throws IOException
{
+ Column[] columns = table.getAutoIncrementColumns();
+
+ for (int idx = 0; idx < columns.length; idx++)
+ {
+ dropAutoIncrementTrigger(table, columns[idx]);
+ dropAutoIncrementSequence(table, columns[idx]);
+ }
+
print("DROP TABLE ");
printIdentifier(getTableName(table));
print(" CASCADE CONSTRAINTS");
printEndOfStatement();
+ }
- Column[] columns = table.getAutoIncrementColumns();
+ /**
+ * Creates the sequence necessary for the auto-increment of the given column.
+ *
+ * @param table The table
+ * @param column The column
+ */
+ protected void createAutoIncrementSequence(Table table,
+ Column column) throws IOException
+ {
+ print("CREATE SEQUENCE ");
+ printIdentifier(getConstraintName("seq", table, column.getName(), null));
+ printEndOfStatement();
+ }
- for (int idx = 0; idx < columns.length; idx++)
- {
- print("DROP TRIGGER ");
- printIdentifier(getConstraintName("trg", table, columns[idx].getName(), null));
- printEndOfStatement();
- print("DROP SEQUENCE ");
- printIdentifier(getConstraintName("seq", table, columns[idx].getName(), null));
- printEndOfStatement();
- }
+ /**
+ * Creates the trigger necessary for the auto-increment of the given column.
+ *
+ * @param table The table
+ * @param column The column
+ */
+ protected void createAutoIncrementTrigger(Table table,
+ Column column) throws IOException
+ {
+ String columnName = getColumnName(column);
+ String triggerName = getConstraintName("trg", table, column.getName(), null);
+
+ // note that the BEGIN ... SELECT ... END; is all in one line and does
+ // not contain a semicolon except for the END-one
+ // this way, the tokenizer will not split the statement before the END
+ print("CREATE OR REPLACE TRIGGER ");
+ printIdentifier(triggerName);
+ print(" BEFORE INSERT ON ");
+ printIdentifier(getTableName(table));
+ print(" FOR EACH ROW WHEN (new.");
+ printIdentifier(columnName);
+ println(" IS NULL)");
+ print("BEGIN SELECT ");
+ printIdentifier(getConstraintName("seq", table, column.getName(), null));
+ print(".nextval INTO :new.");
+ printIdentifier(columnName);
+ print(" FROM dual");
+ print(getPlatformInfo().getSqlCommandDelimiter());
+ print(" END");
+ // It is important that there is a semicolon at the end of the statement (or more
+ // precisely, at the end of the PL/SQL block), and thus we put two semicolons here
+ // because the tokenizer will remove the one at the end
+ print(getPlatformInfo().getSqlCommandDelimiter());
+ printEndOfStatement();
}
/**
- * {@inheritDoc}
+ * Drops the sequence used for the auto-increment of the given column.
+ *
+ * @param table The table
+ * @param column The column
*/
- public void dropExternalForeignKeys(Table table) throws IOException
+ protected void dropAutoIncrementSequence(Table table,
+ Column column) throws IOException
{
- // no need to as we drop the table with CASCASE CONSTRAINTS
+ print("DROP SEQUENCE ");
+ printIdentifier(getConstraintName("seq", table, column.getName(), null));
+ printEndOfStatement();
}
/**
- * {@inheritDoc}
+ * Drops the trigger used for the auto-increment of the given column.
+ *
+ * @param table The table
+ * @param column The column
*/
- public void writeExternalIndexDropStmt(Table table, Index index) throws IOException
+ protected void dropAutoIncrementTrigger(Table table,
+ Column column) throws IOException
{
- // Index names in Oracle are unique to a schema and hence Oracle does not
- // use the ON <tablename> clause
- print("DROP INDEX ");
- printIdentifier(getIndexName(index));
+ print("DROP TRIGGER ");
+ printIdentifier(getConstraintName("trg", table, column.getName(), null));
printEndOfStatement();
}
/**
* {@inheritDoc}
*/
- public void createTable(Database database, Table table, Map parameters) throws IOException
+ protected void createTemporaryTable(Database database, Table table, Map parameters) throws IOException
{
- // lets create any sequences
- Column[] columns = table.getAutoIncrementColumns();
-
- for (int idx = 0; idx < columns.length; idx++)
- {
- print("CREATE SEQUENCE ");
- printIdentifier(getConstraintName("seq", table, columns[idx].getName(), null));
- printEndOfStatement();
- }
+ createTable(database, table, parameters);
+ }
- super.createTable(database, table, parameters);
+ /**
+ * {@inheritDoc}
+ */
+ protected void dropTemporaryTable(Database database, Table table) throws IOException
+ {
+ dropTable(table);
+ }
- for (int idx = 0; idx < columns.length; idx++)
- {
- String columnName = getColumnName(columns[idx]);
- String triggerName = getConstraintName("trg", table, columns[idx].getName(), null);
+ /**
+ * {@inheritDoc}
+ */
+ public void dropExternalForeignKeys(Table table) throws IOException
+ {
+ // no need to as we drop the table with CASCASE CONSTRAINTS
+ }
- // note that the BEGIN ... SELECT ... END; is all in one line and does
- // not contain a semicolon except for the END-one
- // this way, the tokenizer will not split the statement before the END
- print("CREATE OR REPLACE TRIGGER ");
- printIdentifier(triggerName);
- print(" BEFORE INSERT ON ");
- printIdentifier(getTableName(table));
- print(" FOR EACH ROW WHEN (new.");
- printIdentifier(columnName);
- println(" IS NULL)");
- print("BEGIN SELECT ");
- printIdentifier(getConstraintName("seq", table, columns[idx].getName(), null));
- print(".nextval INTO :new.");
- printIdentifier(columnName);
- print(" FROM dual");
- print(getPlatformInfo().getSqlCommandDelimiter());
- print(" END");
- // It is important that there is a semicolon at the end of the statement (or more
- // precisely, at the end of the PL/SQL block), and thus we put two semicolons here
- // because the tokenizer will remove the one at the end
- print(getPlatformInfo().getSqlCommandDelimiter());
- printEndOfStatement();
- }
+ /**
+ * {@inheritDoc}
+ */
+ public void writeExternalIndexDropStmt(Table table, Index index) throws IOException
+ {
+ // Index names in Oracle are unique to a schema and hence Oracle does not
+ // use the ON <tablename> clause
+ print("DROP INDEX ");
+ printIdentifier(getIndexName(index));
+ printEndOfStatement();
}
/**
@@ -245,19 +309,31 @@
/**
* {@inheritDoc}
*/
- protected void createTemporaryTable(Database database, Table table, Map parameters) throws IOException
+ public String getSelectLastIdentityValues(Table table)
{
- // we don't want the auto-increment triggers/sequences for the temporary table
- super.createTable(database, table, parameters);
- }
+ Column[] columns = table.getAutoIncrementColumns();
- /**
- * {@inheritDoc}
- */
- protected void dropTemporaryTable(Database database, Table table) throws IOException
- {
- // likewise, we don't need to drop a sequence or trigger
- super.dropTable(table);
+ if (columns.length > 0)
+ {
+ StringBuffer result = new StringBuffer();
+
+ result.append("SELECT ");
+ for (int idx = 0; idx < columns.length; idx++)
+ {
+ if (idx > 0)
+ {
+ result.append(",");
+ }
+ result.append(getConstraintName("seq", table, columns[idx].getName(), null));
+ result.append(".currval");
+ }
+ result.append(" FROM dual");
+ return result.toString();
+ }
+ else
+ {
+ return null;
+ }
}
/**
@@ -304,7 +380,9 @@
AddColumnChange addColumnChange = (AddColumnChange)change;
// Oracle can only add not insert columns
- if (addColumnChange.isAtEnd())
+ // Also, we cannot add NOT NULL columns unless they have a default value
+ if (addColumnChange.isAtEnd() &&
+ (!addColumnChange.getNewColumn().isRequired() || (addColumnChange.getNewColumn().getDefaultValue() != null)))
{
processChange(currentModel, desiredModel, addColumnChange);
change.apply(currentModel, getPlatform().isDelimitedIdentifierModeOn());
@@ -359,6 +437,11 @@
print("ADD ");
writeColumn(change.getChangedTable(), change.getNewColumn());
printEndOfStatement();
+ if (change.getNewColumn().isAutoIncrement())
+ {
+ createAutoIncrementSequence(change.getChangedTable(), change.getNewColumn());
+ createAutoIncrementTrigger(change.getChangedTable(), change.getNewColumn());
+ }
}
/**
@@ -372,6 +455,11 @@
Database desiredModel,
RemoveColumnChange change) throws IOException
{
+ if (change.getColumn().isAutoIncrement())
+ {
+ dropAutoIncrementTrigger(change.getChangedTable(), change.getColumn());
+ dropAutoIncrementSequence(change.getChangedTable(), change.getColumn());
+ }
print("ALTER TABLE ");
printlnIdentifier(getTableName(change.getChangedTable()));
printIndent();
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java?rev=418654&r1=418653&r2=418654&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java Sun Jul 2 14:53:39 2006
@@ -32,6 +32,8 @@
import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.model.Column;
+import org.apache.ddlutils.model.Table;
+import org.apache.ddlutils.model.TypeMap;
import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
import org.apache.ddlutils.platform.JdbcModelReader;
import org.apache.oro.text.regex.MalformedPatternException;
@@ -82,6 +84,21 @@
}
}
+ /**
+ * {@inheritDoc}
+ */
+ protected Table readTable(DatabaseMetaDataWrapper metaData, Map values) throws SQLException
+ {
+ Table table = super.readTable(metaData, values);
+
+ if (table != null)
+ {
+ determineAutoIncrementColumns(table);
+ }
+
+ return table;
+ }
+
/**
* {@inheritDoc}
*/
@@ -189,10 +206,80 @@
}
}
}
+ else if (TypeMap.isTextType(column.getTypeCode()))
+ {
+ column.setDefaultValue(unescape(column.getDefaultValue(), "'", "''"));
+ }
return column;
}
- /**
+ /**
+ * Helper method that determines the auto increment status using Firebird's system tables.
+ *
+ * @param table The table
+ */
+ protected void determineAutoIncrementColumns(Table table) throws SQLException
+ {
+ Column[] columns = table.getColumns();
+
+ for (int idx = 0; idx < columns.length; idx++)
+ {
+ columns[idx].setAutoIncrement(isAutoIncrement(table, columns[idx]));
+ }
+ }
+
+ /**
+ * Tries to determine whether the given column is an identity column.
+ *
+ * @param table The table
+ * @param column The column
+ * @return <code>true</code> if the column is an identity column
+ */
+ protected boolean isAutoIncrement(Table table, Column column) throws SQLException
+ {
+ // TODO: For now, we only check whether there is a sequence & trigger as generated by DdlUtils
+ // But once sequence/trigger support is in place, it might be possible to 'parse' the
+ // trigger body (via SELECT trigger_name, trigger_body FROM user_triggers) in order to
+ // determine whether it fits our auto-increment definition
+ PreparedStatement prepStmt = null;
+ String triggerName = getPlatform().getSqlBuilder().getConstraintName("trg", table, column.getName(), null);
+ String seqName = getPlatform().getSqlBuilder().getConstraintName("seq", table, column.getName(), null);
+
+ if (!getPlatform().isDelimitedIdentifierModeOn())
+ {
+ triggerName = triggerName.toUpperCase();
+ seqName = seqName.toUpperCase();
+ }
+ try
+ {
+ prepStmt = getConnection().prepareStatement("SELECT * FROM user_triggers WHERE trigger_name = ?");
+ prepStmt.setString(1, triggerName);
+
+ ResultSet resultSet = prepStmt.executeQuery();
+
+ if (!resultSet.next())
+ {
+ return false;
+ }
+ // we have a trigger, so lets check the sequence
+ prepStmt.close();
+
+ prepStmt = getConnection().prepareStatement("SELECT * FROM user_sequences WHERE sequence_name = ?");
+ prepStmt.setString(1, seqName);
+
+ resultSet = prepStmt.executeQuery();
+ return resultSet.next();
+ }
+ finally
+ {
+ if (prepStmt != null)
+ {
+ prepStmt.close();
+ }
+ }
+ }
+
+ /**
* {@inheritDoc}
*/
protected Collection readIndices(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException
Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle8Platform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle8Platform.java?rev=418654&r1=418653&r2=418654&view=diff
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle8Platform.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle8Platform.java Sun Jul 2 14:53:39 2006
@@ -95,13 +95,13 @@
public void testColumnConstraints() throws Exception
{
assertEqualsIgnoringWhitespaces(
- "DROP TABLE \"constraints\" CASCADE CONSTRAINTS;\n" +
"DROP TRIGGER \"trg_constraints_L_PK_AUTO_INCR\";\n"+
- "DROP SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n" +
+ "DROP SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n"+
"DROP TRIGGER \"trg_constraints_COL_AUTO_INCR\";\n"+
- "DROP SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n" +
- "CREATE SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n" +
- "CREATE SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n" +
+ "DROP SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n"+
+ "DROP TABLE \"constraints\" CASCADE CONSTRAINTS;\n"+
+ "CREATE SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n"+
+ "CREATE SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n"+
"CREATE TABLE \"constraints\"\n"+
"(\n"+
" \"COL_PK\" VARCHAR2(32),\n"+
Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle9Platform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle9Platform.java?rev=418654&r1=418653&r2=418654&view=diff
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle9Platform.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestOracle9Platform.java Sun Jul 2 14:53:39 2006
@@ -85,13 +85,13 @@
public void testColumnConstraints() throws Exception
{
assertEqualsIgnoringWhitespaces(
- "DROP TABLE \"constraints\" CASCADE CONSTRAINTS;\n" +
"DROP TRIGGER \"trg_constraints_L_PK_AUTO_INCR\";\n"+
- "DROP SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n" +
+ "DROP SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n"+
"DROP TRIGGER \"trg_constraints_COL_AUTO_INCR\";\n"+
- "DROP SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n" +
- "CREATE SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n" +
- "CREATE SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n" +
+ "DROP SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n"+
+ "DROP TABLE \"constraints\" CASCADE CONSTRAINTS;\n"+
+ "CREATE SEQUENCE \"seq_constraints_L_PK_AUTO_INCR\";\n"+
+ "CREATE SEQUENCE \"seq_constraints_COL_AUTO_INCR\";\n"+
"CREATE TABLE \"constraints\"\n"+
"(\n"+
" \"COL_PK\" VARCHAR2(32),\n"+