Revision: 3993
Author: [email protected]
Date: Wed Nov 24 14:04:42 2010
Log: Fix for the first part of bug 1827. Data types for sql server 2008 are
now compared correctly in terms of date types. The root cause was all date
types are forward engineered to a datetime type in SQL Server and the JDBC
driver returns datetime fields as a timestamp field.
For the two new tests to run they need to be able to access a virtual
machine on gargantubrain, the SQL Server testing virtual machine.
http://code.google.com/p/power-architect/source/detail?r=3993
Modified:
/trunk/pl.regression.ini
/trunk/regress/ca/sqlpower/architect/ddl/SQLServerDDLGeneratorTest.java
/trunk/src/main/java/ca/sqlpower/architect/swingui/CompareDMFormatter.java
=======================================
--- /trunk/pl.regression.ini Thu Jul 22 08:19:18 2010
+++ /trunk/pl.regression.ini Wed Nov 24 14:04:42 2010
@@ -42,6 +42,116 @@
ca.sqlpower.sqlobject.SQLTypePhysicalProperties_6=83c27f1a-f56c-4e2c-ae53-18e02ff499af,LONGVARBINARY,NOT_APPLICABLE,NOT_APPLICABLE
ca.sqlpower.sqlobject.SQLTypePhysicalProperties_7=9a814859-ab26-4acd-9415-ea82291bcac1,LONGVARCHAR,NOT_APPLICABLE,NOT_APPLICABLE
ca.sqlpower.sqlobject.SQLTypePhysicalProperties_8=f332925d-2b1a-4a8a-b22d-90b05d4f7ebb,VARBINARY,NOT_APPLICABLE,NOT_APPLICABLE
+[Database Types_2]
+JDBC Driver Class=com.microsoft.sqlserver.jdbc.SQLServerDriver
+JDBC JAR_0=builtin:sqljdbc4-2.0.jar
+JDBC JAR Count=1
+JDBC URL=jdbc:sqlserver://<Hostname>:<Port:1433>
+Name=SQL Server 2005
+Supports Updatable Result Sets=true
+DDL Generator=ca.sqlpower.architect.ddl.SQLServer2005DDLGenerator
+ca.sqlpower.architect.etl.kettle.connectionType=MS SQL Server
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_0=bit,bit,-7,true,false,false,false,false,false,false,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_1=tinyint,INTEGER,-6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_2=tinyint
identity,INTEGER,-6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_3=bigint,INTEGER,-5,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_4=bigint
identity,INTEGER,-5,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_5=numeric,numeric,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_6=numeric(),numeric,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_7=decimal,decimal,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_8=decimal(),decimal,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_9=int,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_10=int
identity,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_11=smallint,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_12=smallint
identity,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_13=float,float,6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_14=real,real,7,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_15=money,money,2,true,true,true,true,false,false,false,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_16=smallmoney,smallmoney,7,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_17=text,text,2005,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_18=ntext,ntext,2005,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_19=image,image,2004,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_20=datetime,datetime,93,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_21=smalldatetime,smalldatetime,93,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_22=varbinary,varbinary,-3,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_23=binary,binary,-2,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_24=char,char,1,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_25=nchar,nchar,1,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_26=varchar,varchar,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_27=nvarchar,nvarchar,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_28=sysname,sysname,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_29=timestamp,timestamp,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_30=uniqueidentifier,uniqueidentifier,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_31=sql_variant,sql_variant,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ColumnProfileResult$StringLengthSQLFunction=LEN(:)
+ca.sqlpower.architect.profile.ColumnProfileResult$AverageSQLFunction=AVG(CONVERT(DECIMAL,:))
+ca.sqlpower.architect.profile.ColumnProfileResult$CaseWhenNullSQLFunction=CASE
WHEN :
IS NULL THEN : END
+ca.sqlpower.sqlobject.SQLIndex.IndexType_0=HASH
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_0=5c50e616-a448-40e1-abcd-5bfc04dcc607,IMAGE,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_1=9c22ed40-2868-4d9e-96ca-789234a53c8f,TEXT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_2=ee349be3-6af2-46e1-b305-975f5354b009,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_3=4fc90970-b2e5-41dd-911c-cc605d87e7a8,REAL,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_4=63b011c7-c81f-4c75-8271-632b3c87f727,INT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_5=83c27f1a-f56c-4e2c-ae53-18e02ff499af,IMAGE,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_6=9a814859-ab26-4acd-9415-ea82291bcac1,TEXT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_7=0b8db85e-251b-4be0-8205-4d74362105cb,NCLOB,VARIABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_8=10d6960f-169d-47c2-860a-37341342b1d5,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_9=6497d8de-0e00-40d6-a42e-e2adca83be64,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
+[Database Types_3]
+JDBC Driver Class=com.microsoft.sqlserver.jdbc.SQLServerDriver
+JDBC JAR_0=builtin:sqljdbc4-2.0.jar
+JDBC JAR Count=1
+JDBC URL=jdbc:sqlserver://<Hostname>:<Port:1433>
+Name=SQL Server 2008
+Supports Updatable Result Sets=true
+DDL Generator=ca.sqlpower.architect.ddl.SQLServer2005DDLGenerator
+ca.sqlpower.architect.etl.kettle.connectionType=MS SQL Server
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_0=bit,bit,-7,true,false,false,false,false,false,false,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_1=tinyint,INTEGER,-6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_2=tinyint
identity,INTEGER,-6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_3=bigint,INTEGER,-5,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_4=bigint
identity,INTEGER,-5,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_5=numeric,numeric,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_6=numeric(),numeric,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_7=decimal,decimal,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_8=decimal(),decimal,2,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_9=int,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_10=int
identity,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_11=smallint,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_12=smallint
identity,INTEGER,4,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_13=float,float,6,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_14=real,real,7,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_15=money,money,2,true,true,true,true,false,false,false,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_16=smallmoney,smallmoney,7,true,true,true,true,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_17=text,text,2005,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_18=ntext,ntext,2005,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_19=image,image,2004,false,false,false,false,false,false,false,false
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_20=datetime,datetime,93,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_21=smalldatetime,smalldatetime,93,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_22=varbinary,varbinary,-3,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_23=binary,binary,-2,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_24=char,char,1,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_25=nchar,nchar,1,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_26=varchar,varchar,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_27=nvarchar,nvarchar,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_28=sysname,sysname,12,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_29=timestamp,timestamp,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_30=uniqueidentifier,uniqueidentifier,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ProfileFunctionDescriptor_31=sql_variant,sql_variant,2006,true,true,true,false,true,true,true,true
+ca.sqlpower.architect.profile.ColumnProfileResult$StringLengthSQLFunction=LEN(:)
+ca.sqlpower.architect.profile.ColumnProfileResult$AverageSQLFunction=AVG(CONVERT(DECIMAL,:))
+ca.sqlpower.architect.profile.ColumnProfileResult$CaseWhenNullSQLFunction=CASE
WHEN :
IS NULL THEN : END
+ca.sqlpower.sqlobject.SQLIndex.IndexType_0=HASH
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_0=5c50e616-a448-40e1-abcd-5bfc04dcc607,IMAGE,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_1=9c22ed40-2868-4d9e-96ca-789234a53c8f,TEXT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_2=ee349be3-6af2-46e1-b305-975f5354b009,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_3=4fc90970-b2e5-41dd-911c-cc605d87e7a8,REAL,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_4=63b011c7-c81f-4c75-8271-632b3c87f727,INT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_5=83c27f1a-f56c-4e2c-ae53-18e02ff499af,IMAGE,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_6=9a814859-ab26-4acd-9415-ea82291bcac1,TEXT,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_7=0b8db85e-251b-4be0-8205-4d74362105cb,NCLOB,VARIABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_8=10d6960f-169d-47c2-860a-37341342b1d5,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
+ca.sqlpower.sqlobject.SQLTypePhysicalProperties_9=6497d8de-0e00-40d6-a42e-e2adca83be64,DATETIME,NOT_APPLICABLE,NOT_APPLICABLE
[Databases_1]
Logical=regression_test
Connection Type=HSQLDB
@@ -53,6 +163,17 @@
[Databases_2]
Logical=Testing data source
Connection Type
+[Databases_3]
+Logical=sql server 2008
+Connection Type=SQL Server 2008
+JDBC URL=jdbc:sqlserver://dhcp-110:1433
+UID=arch_test
+PWD=khm^ijok|
+ca.sqlpower.architect.etl.kettle.database=
+ca.sqlpower.architect.etl.kettle.port=
+ca.sqlpower.architect.etl.kettle.hostname=
+ca.sqlpower.architect.etl.kettle.repos.login=
+ca.sqlpower.architect.etl.kettle.repos.password=
[Data Types_1]
Name=ARRAY
UUID=5a4374b2-131e-46dd-b7f1-5eb2aaf25f8b
=======================================
--- /trunk/regress/ca/sqlpower/architect/ddl/SQLServerDDLGeneratorTest.java
Fri May 22 06:08:44 2009
+++ /trunk/regress/ca/sqlpower/architect/ddl/SQLServerDDLGeneratorTest.java
Wed Nov 24 14:04:42 2010
@@ -18,13 +18,37 @@
*/
package ca.sqlpower.architect.ddl;
-import ca.sqlpower.sqlobject.SQLColumn;
-import junit.framework.TestCase;
-import ca.sqlpower.sqlobject.SQLTable;
+import java.sql.Connection;
+import java.sql.Statement;
import java.sql.Types;
+import java.util.Collections;
import java.util.List;
+import junit.framework.TestCase;
+
+import org.apache.log4j.Logger;
+
+import ca.sqlpower.architect.ArchitectSessionContext;
+import ca.sqlpower.architect.TestingArchitectSessionContext;
+import ca.sqlpower.architect.diff.CompareSQL;
+import ca.sqlpower.architect.swingui.ArchitectSwingSession;
+import ca.sqlpower.architect.swingui.CompareDMFormatter;
+import ca.sqlpower.architect.swingui.CompareDMPanel;
+import ca.sqlpower.architect.swingui.CompareDMSettings;
+import ca.sqlpower.architect.swingui.TestingArchitectSwingSessionContext;
+import ca.sqlpower.architect.swingui.CompareDMSettings.DatastoreType;
+import ca.sqlpower.architect.swingui.CompareDMSettings.OutputFormat;
+import ca.sqlpower.diff.DiffChunk;
+import ca.sqlpower.sql.DataSourceCollection;
+import ca.sqlpower.sql.JDBCDataSource;
+import ca.sqlpower.sqlobject.SQLColumn;
+import ca.sqlpower.sqlobject.SQLDatabase;
+import ca.sqlpower.sqlobject.SQLObject;
+import ca.sqlpower.sqlobject.SQLTable;
+
public class SQLServerDDLGeneratorTest extends TestCase {
+
+ private static final Logger logger =
Logger.getLogger(SQLServerDDLGenerator.class);
public void testGenerateComment() throws Exception {
// it shouldn't matter which of the two (2000, 2005) are
instantiated
@@ -52,4 +76,205 @@
assertEquals("-- Comment for column [id]: The row's primary key",
stmts.get(2).getSQLText().trim());
assertEquals("-- Comment for column [name]: The person's name",
stmts.get(3).getSQLText().trim());
}
-}
+
+ /**
+ * Test for the first part of bug 1827. Previously with SQL Server
2008 if
+ * you forward engineered a table and then compared the table with the
play
+ * pen that was just used to forward engineer it you would find
differences.
+ * However there should be none.
+ */
+ public void testCompareDMClean() throws Exception {
+ ArchitectSessionContext context = new
TestingArchitectSessionContext();
+ final DataSourceCollection<JDBCDataSource> plIni =
context.getPlDotIni();
+ JDBCDataSource ds = plIni.getDataSource("sql server 2008",
JDBCDataSource.class);
+
+ if (ds == null)
+ fail("No server named 'sql server 2008' is defined in the ini
file so we cannot connect to the database.");
+
+ String tableName = "TestCompareDMClean";
+ final SQLDatabase db = new SQLDatabase(ds);
+ Connection con = null;
+ try {
+ con = db.getConnection();
+ Statement stmt = con.createStatement();
+ stmt.execute("drop table " + tableName);
+ } catch (Exception e) {
+ logger.error(e);
+ } finally {
+ con.close();
+ con = null;
+ }
+
+
+ //create a table with the test columns in the play pen
+ SQLDatabase ppdb = new SQLDatabase();
+ ppdb.setPlayPenDatabase(true);
+ ppdb.setPopulated(true);
+ ppdb.setName("Play pen DB");
+ SQLTable t = new SQLTable(ppdb, true);
+ t.setName(tableName);
+ SQLColumn bigIntCol = new SQLColumn();
+ bigIntCol.setName("bigIntCol");
+ bigIntCol.setType(plIni.getSQLType("BIGINT"));
+ assertNotNull(bigIntCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(bigIntCol);
+ SQLColumn tinyIntCol = new SQLColumn();
+ tinyIntCol.setName("tinyIntCol");
+ tinyIntCol.setType(plIni.getSQLType("TINYINT"));
+
assertNotNull(tinyIntCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(tinyIntCol);
+ SQLColumn dateTimeCol = new SQLColumn();
+ dateTimeCol.setName("dateTimeCol");
+ dateTimeCol.setType(plIni.getSQLType("DATE"));
+
assertNotNull(dateTimeCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(dateTimeCol);
+
+ try {
+ //forward engineer the play pen to the connection
+ List<DDLStatement> statements =
DDLUtils.createDDLGenerator(ds).generateDDLStatements(Collections.singletonList(t));
+ logger.info("Running script " + statements);
+ con = db.getConnection();
+ Statement stmt = con.createStatement();
+ for (DDLStatement statement : statements) {
+ stmt.execute(statement.getSQLText());
+ }
+
+ //create a compareSQL object with the play pen objects in one
side and the database in the other
+ SQLTable dbTable = db.getTableByName(tableName);
+ CompareSQL comparator = new
CompareSQL(Collections.singletonList(dbTable),
Collections.singletonList(t), true);
+ //run the compareDM
+ List<DiffChunk<SQLObject>> diffs =
comparator.generateTableDiffs(context.createSession());
+ CompareDMSettings dmSettings = new CompareDMSettings();
+ dmSettings.setOutputFormat(OutputFormat.SQL);
+ dmSettings.setDdlGenerator(SQLServer2005DDLGenerator.class);
+ dmSettings.setSuppressSimilarities(true);
+
dmSettings.getSourceSettings().setDatastoreType(DatastoreType.DATABASE);
+ ArchitectSwingSession swingSession = new
TestingArchitectSwingSessionContext().createSession();
+ CompareDMPanel panel = new CompareDMPanel(swingSession, null);
+ dmSettings.setSourceStuff(panel.new SourceOrTargetStuff() {
+ @Override
+ public synchronized SQLDatabase getDatabase() {
+ return db;
+ }
+ });
+
dmSettings.getTargetSettings().setDatastoreType(DatastoreType.PROJECT);
+ CompareDMFormatter formatter = new
CompareDMFormatter(swingSession, null, dmSettings);
+ DDLGenerator gen = formatter.formatForSQLOutput(diffs, diffs,
dbTable, t);
+ assertTrue(gen.getDdlStatements().isEmpty());
+ } finally {
+ if (con != null) {
+ try {
+ Statement stmt = con.createStatement();
+ stmt.execute("drop table " + tableName);
+ } catch (Exception e) {
+ logger.error(e);
+ } finally {
+ try {
+ con.close();
+ } catch (Exception e) {
+ logger.error(e);
+ }
+ }
+ }
+ }
+ }
+
+ /**
+ * Test relating to bug 1827. While the first test checks forward
+ * engineering a table will match what is in the database if the user
+ * actually creates a table elsewhere with a date or time column they
will
+ * compare as nvarchars in the compare DM feature.
+ */
+ public void testCompareDMDates() throws Exception {
+ ArchitectSessionContext context = new
TestingArchitectSessionContext();
+ final DataSourceCollection<JDBCDataSource> plIni =
context.getPlDotIni();
+ JDBCDataSource ds = plIni.getDataSource("sql server 2008",
JDBCDataSource.class);
+
+ if (ds == null)
+ fail("No server named 'sql server 2008' is defined in the ini
file so we cannot connect to the database.");
+
+ String tableName = "TestCompareDMDates";
+ final SQLDatabase db = new SQLDatabase(ds);
+ Connection con = null;
+ try {
+ con = db.getConnection();
+ Statement stmt = con.createStatement();
+ stmt.execute("drop table " + tableName);
+ } catch (Exception e) {
+ logger.error(e);
+ } finally {
+ con.close();
+ con = null;
+ }
+
+
+ //create a table with the test columns in the play pen
+ SQLDatabase ppdb = new SQLDatabase();
+ ppdb.setPlayPenDatabase(true);
+ ppdb.setPopulated(true);
+ ppdb.setName("Play pen DB");
+ SQLTable t = new SQLTable(ppdb, true);
+ t.setName(tableName);
+ SQLColumn dateCol = new SQLColumn();
+ dateCol.setName("dateCol");
+ dateCol.setType(plIni.getSQLType("DATE"));
+ assertNotNull(dateCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(dateCol);
+ SQLColumn timeCol = new SQLColumn();
+ timeCol.setName("timeCol");
+ timeCol.setType(plIni.getSQLType("TIME"));
+ assertNotNull(timeCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(timeCol);
+ SQLColumn timestampCol = new SQLColumn();
+ timestampCol.setName("timestampCol");
+ timestampCol.setType(plIni.getSQLType("TIMESTAMP"));
+
assertNotNull(timestampCol.getUserDefinedSQLType().getUpstreamType());
+ t.addColumn(timestampCol);
+
+ try {
+ //forward engineer the play pen to the connection
+ con = db.getConnection();
+ Statement stmt = con.createStatement();
+ stmt.execute("Create table " + tableName + "(dateCol DATE NOT
NULL, timeCol TIME NOT NULL, timestampCol DATETIME NOT NULL)");
+
+ //create a compareSQL object with the play pen objects in one
side and the database in the other
+ SQLTable dbTable = db.getTableByName(tableName);
+ CompareSQL comparator = new
CompareSQL(Collections.singletonList(dbTable),
Collections.singletonList(t), true);
+ //run the compareDM
+ List<DiffChunk<SQLObject>> diffs =
comparator.generateTableDiffs(context.createSession());
+ CompareDMSettings dmSettings = new CompareDMSettings();
+ dmSettings.setOutputFormat(OutputFormat.SQL);
+ dmSettings.setDdlGenerator(SQLServer2005DDLGenerator.class);
+ dmSettings.setSuppressSimilarities(true);
+
dmSettings.getSourceSettings().setDatastoreType(DatastoreType.DATABASE);
+ ArchitectSwingSession swingSession = new
TestingArchitectSwingSessionContext().createSession();
+ CompareDMPanel panel = new CompareDMPanel(swingSession, null);
+ dmSettings.setSourceStuff(panel.new SourceOrTargetStuff() {
+ @Override
+ public synchronized SQLDatabase getDatabase() {
+ return db;
+ }
+ });
+
dmSettings.getTargetSettings().setDatastoreType(DatastoreType.PROJECT);
+ CompareDMFormatter formatter = new
CompareDMFormatter(swingSession, null, dmSettings);
+ DDLGenerator gen = formatter.formatForSQLOutput(diffs, diffs,
dbTable, t);
+ assertTrue(gen.getDdlStatements().isEmpty());
+ } finally {
+ if (con != null) {
+ try {
+ Statement stmt = con.createStatement();
+ stmt.execute("drop table " + tableName);
+ } catch (Exception e) {
+ logger.error(e);
+ } finally {
+ try {
+ con.close();
+ } catch (Exception e) {
+ logger.error(e);
+ }
+ }
+ }
+ }
+ }
+
+}
=======================================
---
/trunk/src/main/java/ca/sqlpower/architect/swingui/CompareDMFormatter.java
Wed Nov 24 11:57:00 2010
+++
/trunk/src/main/java/ca/sqlpower/architect/swingui/CompareDMFormatter.java
Wed Nov 24 14:04:42 2010
@@ -40,6 +40,7 @@
import ca.sqlpower.architect.ddl.DDLGenerator;
import ca.sqlpower.architect.ddl.LiquibaseDDLGenerator;
import ca.sqlpower.architect.ddl.OracleDDLGenerator;
+import ca.sqlpower.architect.ddl.SQLServer2005DDLGenerator;
import ca.sqlpower.architect.diff.ArchitectDiffException;
import ca.sqlpower.architect.swingui.CompareDMPanel.SourceOrTargetStuff;
import
ca.sqlpower.architect.swingui.CompareDMSettings.SourceOrTargetSettings;
@@ -151,12 +152,12 @@
}
}
- public void formatForSQLOutput(List<DiffChunk<SQLObject>> diff,
+ public DDLGenerator formatForSQLOutput(List<DiffChunk<SQLObject>> diff,
List<DiffChunk<SQLObject>> diff1, SQLObject left, SQLObject
right) {
+ DDLGenerator gen = null;
try {
SourceOrTargetStuff source = dmSetting.getSourceStuff();
- DDLGenerator gen = null;
if
(dmSetting.getOutputFormat().equals(CompareDMSettings.OutputFormat.SQL)) {
gen = dmSetting.getDdlGenerator().newInstance();
SQLCatalog cat = (SQLCatalog)
dmSetting.getSourceSettings().getCatalogObject();
@@ -223,6 +224,7 @@
ASUtils.showExceptionDialog(session, "Unxepected Exception!",
ex);
logger.error("Unxepected Exception!", ex);
}
+ return gen;
}
@@ -294,6 +296,30 @@
}
}
((OracleDDLGenerator)gen).modifyColumn(c,
changeNull);
+ } else if
(SQLServer2005DDLGenerator.class.isAssignableFrom(gen.getClass())) {
+ //Fix for the first part of bug 1827. All time
data types in sql server
+ //collapse to Datetime which then gets converted
to a timestamp.
+ if (chunk.getPropertyChanges().size() == 1) {
+ PropertyChange propertyChange =
chunk.getPropertyChanges().get(0);
+ String newVal = propertyChange.getNewValue();
+ String oldVal = propertyChange.getOldValue();
+ String dateType = "DATE";
+ String timeType = "TIME";
+ String timestampType = "TIMESTAMP";
+ if
(propertyChange.getPropertyName().equals("type") &&
+ (newVal.equalsIgnoreCase(dateType) ||
newVal.equalsIgnoreCase(timeType) ||
+
newVal.equalsIgnoreCase(timestampType)) &&
+ (oldVal.equalsIgnoreCase(dateType) ||
oldVal.equalsIgnoreCase(timeType) ||
+
oldVal.equalsIgnoreCase(timestampType))) {
+ //skip becuase they are actually of the
same type for SQL Server.
+ } else {
+ gen.modifyColumn(c);
+ }
+
+ } else {
+ gen.modifyColumn(c);
+ }
+
} else
gen.modifyColumn(c);
}
