Return-Path: Delivered-To: apmail-db-ddlutils-dev-archive@www.apache.org Received: (qmail 16069 invoked from network); 14 May 2006 18:09:00 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 14 May 2006 18:09:00 -0000 Received: (qmail 99084 invoked by uid 500); 14 May 2006 18:09:00 -0000 Delivered-To: apmail-db-ddlutils-dev-archive@db.apache.org Received: (qmail 99043 invoked by uid 500); 14 May 2006 18:08:59 -0000 Mailing-List: contact ddlutils-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ddlutils-dev@db.apache.org Delivered-To: mailing list ddlutils-dev@db.apache.org Received: (qmail 99031 invoked by uid 500); 14 May 2006 18:08:59 -0000 Delivered-To: apmail-db-ddlutils-commits@db.apache.org Received: (qmail 99028 invoked by uid 99); 14 May 2006 18:08:59 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 14 May 2006 11:08:59 -0700 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Sun, 14 May 2006 11:08:57 -0700 Received: (qmail 15875 invoked by uid 65534); 14 May 2006 18:08:37 -0000 Message-ID: <20060514180837.15874.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r406392 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/alteration/ java/org/apache/ddlutils/model/ java/org/apache/ddlutils/platform/ java/org/apache/ddlutils/platform/mysql/ java/org/apache/ddlutils/platform/sapdb/ test/org/apache/d... Date: Sun, 14 May 2006 18:08:35 -0000 To: ddlutils-commits@db.apache.org From: tomdz@apache.org X-Mailer: svnmailer-1.0.8 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: tomdz Date: Sun May 14 11:08:35 2006 New Revision: 406392 URL: http://svn.apache.org/viewcvs?rev=406392&view=rev Log: Enhanced alteration algorithm for SapDB/MaxDB Added test for column ordering change Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/AddColumnChange.java db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/ModelComparator.java db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mysql/MySqlBuilder.java db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/AddColumnChange.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/AddColumnChange.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/AddColumnChange.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/AddColumnChange.java Sun May 14 11:08:35 2006 @@ -34,6 +34,8 @@ private Column _previousColumn; /** The column before which the new column should be added. */ private Column _nextColumn; + /** Whether the column is added at the end. */ + private boolean _atEnd; /** * Creates a new change object. @@ -82,6 +84,28 @@ } /** + * Determines whether the column is added at the end (when applied in the order + * of creation of the changes). + * + * @return true if the column is added at the end + */ + public boolean isAtEnd() + { + return _atEnd; + } + + /** + * Specifies whether the column is added at the end (when applied in the order + * of creation of the changes). + * + * @param atEnd true if the column is added at the end + */ + public void setAtEnd(boolean atEnd) + { + _atEnd = atEnd; + } + + /** * {@inheritDoc} */ public void apply(Database database) @@ -96,6 +120,18 @@ { throw new DdlUtilsException(ex); } - database.findTable(getChangedTable().getName()).addColumn(newColumn); + + Table table = database.findTable(getChangedTable().getName()); + + if ((_previousColumn != null) && (_nextColumn != null)) + { + int idx = table.getColumnIndex(_previousColumn) + 1; + + table.addColumn(idx, newColumn); + } + else + { + table.addColumn(newColumn); + } } } Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/ModelComparator.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/ModelComparator.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/ModelComparator.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/alteration/ModelComparator.java Sun May 14 11:08:35 2006 @@ -17,6 +17,7 @@ */ import java.util.ArrayList; +import java.util.HashMap; import java.util.List; import org.apache.commons.lang.StringUtils; @@ -201,6 +202,8 @@ } } + HashMap addColumnChanges = new HashMap(); + for (int columnIdx = 0; columnIdx < targetTable.getColumnCount(); columnIdx++) { Column targetColumn = targetTable.getColumn(columnIdx); @@ -212,14 +215,34 @@ { _log.info("Column " + targetColumn.getName() + " needs to be created for table " + sourceTable.getName()); } - changes.add(new AddColumnChange(sourceTable, - targetColumn, - columnIdx > 0 ? targetTable.getColumn(columnIdx - 1) : null, - columnIdx < targetTable.getColumnCount() - 1 ? targetTable.getColumn(columnIdx + 1) :null)); + + AddColumnChange change = new AddColumnChange(sourceTable, + targetColumn, + columnIdx > 0 ? targetTable.getColumn(columnIdx - 1) : null, + columnIdx < targetTable.getColumnCount() - 1 ? targetTable.getColumn(columnIdx + 1) : null); + + changes.add(change); + addColumnChanges.put(targetColumn, change); } else { changes.addAll(compareColumns(sourceTable, sourceColumn, targetTable, targetColumn)); + } + } + // if the last columns in the target table are added, then we note this at the changes + for (int columnIdx = targetTable.getColumnCount() - 1; columnIdx >= 0; columnIdx--) + { + Column targetColumn = targetTable.getColumn(columnIdx); + AddColumnChange change = (AddColumnChange)addColumnChanges.get(targetColumn); + + if (change == null) + { + // column was not added, so we can ignore any columns before it that were added + break; + } + else + { + change.setAtEnd(true); } } Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java Sun May 14 11:08:35 2006 @@ -560,6 +560,26 @@ } /** + * Determines the index of the given column. + * + * @param column The column + * @return The index or -1 if it is no column of this table + */ + public int getColumnIndex(Column column) + { + int idx = 0; + + for (Iterator it = _columns.iterator(); it.hasNext(); idx++) + { + if (column == it.next()) + { + return idx; + } + } + return -1; + } + + /** * Finds the index with the specified name, using case insensitive matching. * Note that this method is not called getIndex to avoid introspection * problems. Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java Sun May 14 11:08:35 2006 @@ -2111,6 +2111,22 @@ } /** + * Determines whether the given default spec is a non-empty spec that shall be used in a DEFAULT + * expression. E.g. if the spec is an empty string and the type is a numeric type, then it is + * no valid default value whereas if it is a string type, then it is valid. + * + * @param defaultSpec The default value spec + * @param typeCode The JDBC type code + * @return true if the default value spec is valid + */ + protected boolean isValidDefaultValue(String defaultSpec, int typeCode) + { + return (defaultSpec != null) && + ((defaultSpec.length() > 0) || + (!TypeMap.isNumericType(typeCode) && !TypeMap.isDateTimeType(typeCode))); + } + + /** * Prints the default value stmt part for the column. * * @param table The table @@ -2128,8 +2144,7 @@ throw new DynaSqlException("The platform does not support default values for LONGVARCHAR or LONGVARBINARY columns"); } // we write empty default value strings only if the type is not a numeric or date/time type - if ((column.getDefaultValue().length() > 0) || - (!TypeMap.isNumericType(column.getTypeCode()) && !TypeMap.isDateTimeType(column.getTypeCode()))) + if (isValidDefaultValue(column.getDefaultValue(), column.getTypeCode())) { print(" DEFAULT "); writeColumnDefaultValue(table, column); @@ -2150,23 +2165,32 @@ */ protected void writeColumnDefaultValue(Table table, Column column) throws IOException { - boolean shouldUseQuotes = !TypeMap.isNumericType(column.getTypeCode()); + printDefaultValue(getNativeDefaultValue(column), column.getTypeCode()); + } - if (shouldUseQuotes) - { - // characters are only escaped when within a string literal - print(getPlatformInfo().getValueQuoteToken()); - print(escapeStringValue(getNativeDefaultValue(column).toString())); - print(getPlatformInfo().getValueQuoteToken()); - } - else + /** + * Prints the default value of the column. + * + * @param defaultValue The default value + * @param typeCode The type code to write the default value for + */ + protected void printDefaultValue(Object defaultValue, int typeCode) throws IOException + { + if (defaultValue != null) { - Object nativeDefault = getNativeDefaultValue(column); - - if (nativeDefault != null) - { - print(nativeDefault.toString()); - } + boolean shouldUseQuotes = !TypeMap.isNumericType(typeCode); + + if (shouldUseQuotes && (defaultValue != null)) + { + // characters are only escaped when within a string literal + print(getPlatformInfo().getValueQuoteToken()); + print(escapeStringValue(defaultValue.toString())); + print(getPlatformInfo().getValueQuoteToken()); + } + else + { + print(defaultValue.toString()); + } } } Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mysql/MySqlBuilder.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mysql/MySqlBuilder.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mysql/MySqlBuilder.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mysql/MySqlBuilder.java Sun May 14 11:08:35 2006 @@ -202,29 +202,33 @@ { processChange(currentModel, desiredModel, (RemoveColumnChange)change); change.apply(currentModel); + changeIt.remove(); } else if (change instanceof AddPrimaryKeyChange) { processChange(currentModel, desiredModel, (AddPrimaryKeyChange)change); change.apply(currentModel); + changeIt.remove(); } else if (change instanceof PrimaryKeyChange) { processChange(currentModel, desiredModel, (PrimaryKeyChange)change); change.apply(currentModel); + changeIt.remove(); } else if (change instanceof RemovePrimaryKeyChange) { processChange(currentModel, desiredModel, (RemovePrimaryKeyChange)change); change.apply(currentModel); + changeIt.remove(); } else { // we gather all changed columns because we can use the ALTER TABLE MODIFY COLUMN // statement for them changedColumns.add(((ColumnChange)change).getChangedColumn()); + changeIt.remove(); } - changeIt.remove(); } for (Iterator columnIt = changedColumns.iterator(); columnIt.hasNext();) { Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java Sun May 14 11:08:35 2006 @@ -17,9 +17,21 @@ */ import java.io.IOException; +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.ColumnDefaultValueChange; +import org.apache.ddlutils.alteration.ColumnRequiredChange; +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.Table; import org.apache.ddlutils.platform.SqlBuilder; @@ -60,5 +72,241 @@ protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException { print("DEFAULT SERIAL(1)"); + } + + /** + * {@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); + } + } + // Next we add/change/remove columns + // SapDB has a ALTER TABLE MODIFY COLUMN but it is limited regarding the type conversions + // it can perform, so we don't use it here but rather rebuild the table + for (Iterator changeIt = changes.iterator(); changeIt.hasNext();) + { + TableChange change = (TableChange)changeIt.next(); + + if (change instanceof AddColumnChange) + { + AddColumnChange addColumnChange = (AddColumnChange)change; + + // SapDB can only add not insert columns + if (addColumnChange.isAtEnd()) + { + processChange(currentModel, desiredModel, addColumnChange); + change.apply(currentModel); + changeIt.remove(); + } + } + else if (change instanceof ColumnDefaultValueChange) + { + processChange(currentModel, desiredModel, (ColumnDefaultValueChange)change); + change.apply(currentModel); + changeIt.remove(); + } + else if (change instanceof ColumnRequiredChange) + { + processChange(currentModel, desiredModel, (ColumnRequiredChange)change); + change.apply(currentModel); + changeIt.remove(); + } + else if (change instanceof RemoveColumnChange) + { + processChange(currentModel, desiredModel, (RemoveColumnChange)change); + change.apply(currentModel); + changeIt.remove(); + } + } + // 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())); + print(" RELEASE SPACE"); + 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 + { + print("ALTER TABLE "); + printlnIdentifier(getTableName(change.getChangedTable())); + printIndent(); + print("DROP PRIMARY KEY"); + printEndOfStatement(); + } + + /** + * Processes the change of the primary key of 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, + PrimaryKeyChange change) throws IOException + { + print("ALTER TABLE "); + printlnIdentifier(getTableName(change.getChangedTable())); + printIndent(); + print("DROP PRIMARY KEY"); + printEndOfStatement(); + writeExternalPrimaryKeysCreateStmt(change.getChangedTable(), change.getNewPrimaryKeyColumns()); + } + + /** + * Processes the change of the required constraint of a 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, + ColumnRequiredChange change) throws IOException + { + print("ALTER TABLE "); + printlnIdentifier(getTableName(change.getChangedTable())); + printIndent(); + print("COLUMN "); + printIdentifier(getColumnName(change.getChangedColumn())); + if (change.getChangedColumn().isRequired()) + { + print(" DEFAULT NULL"); + } + else + { + print(" NOT NULL"); + } + printEndOfStatement(); + } + + /** + * Processes the change of the default value of a 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("COLUMN "); + printIdentifier(getColumnName(change.getChangedColumn())); + + Table curTable = currentModel.findTable(change.getChangedTable().getName(), getPlatform().isDelimitedIdentifierModeOn()); + Column curColumn = curTable.findColumn(change.getChangedColumn().getName(), getPlatform().isDelimitedIdentifierModeOn()); + boolean hasDefault = curColumn.getParsedDefaultValue() != null; + + if (isValidDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode())) + { + if (hasDefault) + { + print(" ALTER DEFAULT "); + } + else + { + print(" ADD DEFAULT "); + } + printDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode()); + } + else if (hasDefault) + { + print(" DROP DEFAULT"); + } + printEndOfStatement(); } } Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java?rev=406392&r1=406391&r2=406392&view=diff ============================================================================== --- db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java (original) +++ db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestAlteration.java Sun May 14 11:08:35 2006 @@ -486,6 +486,53 @@ assertEquals((Object)null, beans.get(0), "avalue1"); assertEquals((Object)null, beans.get(0), "avalue2"); + assertEquals(new Double(3.0), beans.get(0), "avalue3"); + assertEquals((Object)null, beans.get(0), "avalue4"); + } + + /** + * Tests the addition of several columns at the end of the table. This test + * is known to fail on MaxDB where a DEFAULT specification is applied to existing + * rows even if they are not defined as NOT NULL (column 'avalue3' in the + * target schema). + */ + public void testAddColumnsAtTheEnd() + { + final String model1Xml = + "\n"+ + "\n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + "
\n"+ + ""; + final String model2Xml = + "\n"+ + "\n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + "
\n"+ + ""; + + createDatabase(model1Xml); + + insertRow("roundtrip", new Object[] { new Integer(1), "test", new Integer(3) }); + + alterDatabase(model2Xml); + + assertEquals(getAdjustedModel(), + readModelFromDatabase("roundtriptest")); + + List beans = getRows("roundtrip"); + + assertEquals((Object)"test", beans.get(0), "avalue1"); + assertEquals(new Integer(3), beans.get(0), "avalue2"); + assertEquals((Object)null, beans.get(0), "avalue3"); assertEquals((Object)null, beans.get(0), "avalue4"); } @@ -557,6 +604,51 @@ List beans = getRows("roundtrip"); assertEquals(new Integer(2), beans.get(0), "avalue"); + } + + /** + * Tests the change of the order of the columns of a table. + */ + public void testChangeColumnOrder() + { + final String model1Xml = + "\n"+ + "\n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + "
\n"+ + ""; + final String model2Xml = + "\n"+ + "\n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + " \n"+ + "
\n"+ + ""; + + createDatabase(model1Xml); + + insertRow("roundtrip", new Object[] { new Integer(1), "test", "value", null, null }); + + alterDatabase(model2Xml); + + assertEquals(getAdjustedModel(), + readModelFromDatabase("roundtriptest")); + + List beans = getRows("roundtrip"); + + assertEquals((Object)"test", beans.get(0), "avalue1"); + assertEquals((Object)null, beans.get(0), "avalue2"); + assertEquals(new Double(1.0), beans.get(0), "avalue3"); + assertEquals((Object)"value", beans.get(0), "avalue4"); } /**