db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject svn commit: r462920 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/platform/SqlBuilder.java java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java test/org/apache/ddlutils/TestBase.java test/org/apache/ddlutils/platform/TestMSSqlPlatform.java
Date Wed, 11 Oct 2006 19:52:27 GMT
Author: tomdz
Date: Wed Oct 11 12:52:26 2006
New Revision: 462920

URL: http://svn.apache.org/viewvc?view=rev&rev=462920
Log:
Fixed SQL Server unit tests
Fixed DDLUTILS-40

Modified:
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java
    db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/TestBase.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java?view=diff&rev=462920&r1=462919&r2=462920
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/SqlBuilder.java Wed Oct 11 12:52:26
2006
@@ -264,20 +264,82 @@
                     locale = new Locale(language);
                 }
 
-                _valueLocale       = localeStr;
-                _valueDateFormat   = DateFormat.getDateInstance(DateFormat.SHORT, locale);
-                _valueTimeFormat   = DateFormat.getTimeInstance(DateFormat.SHORT, locale);
-                _valueNumberFormat = NumberFormat.getNumberInstance(locale);
+                _valueLocale = localeStr;
+                setValueDateFormat(DateFormat.getDateInstance(DateFormat.SHORT, locale));
+                setValueTimeFormat(DateFormat.getTimeInstance(DateFormat.SHORT, locale));
+                setValueNumberFormat(NumberFormat.getNumberInstance(locale));
                 return;
             }
         }
-        _valueLocale       = null;
-        _valueDateFormat   = null;
-        _valueTimeFormat   = null;
-        _valueNumberFormat = null;
+        _valueLocale = null;
+        setValueDateFormat(null);
+        setValueTimeFormat(null);
+        setValueNumberFormat(null);
     }
 
     /**
+     * Returns the format object for formatting dates in the specified locale.
+     * 
+     * @return The date format object or null if no locale is set
+     */
+    protected DateFormat getValueDateFormat()
+    {
+        return _valueDateFormat;
+    }
+    
+    /**
+     * Sets the format object for formatting dates in the specified locale.
+     * 
+     * @param format The date format object
+     */
+    protected void setValueDateFormat(DateFormat format)
+    {
+        _valueDateFormat = format;
+    }
+
+    /**
+     * Returns the format object for formatting times in the specified locale.
+     * 
+     * @return The time format object or null if no locale is set
+     */
+    protected DateFormat getValueTimeFormat()
+    {
+        return _valueTimeFormat;
+    }
+
+    /**
+     * Sets the date format object for formatting times in the specified locale.
+     * 
+     * @param format The time format object
+     */
+    protected void setValueTimeFormat(DateFormat format)
+    {
+        _valueTimeFormat = format;
+    }
+
+    /**
+     * Returns the format object for formatting numbers in the specified locale.
+     * 
+     * @return The number format object or null if no locale is set
+     */
+    protected NumberFormat getValueNumberFormat()
+    {
+        return _valueNumberFormat;
+    }
+
+    /**
+     * Returns a new date format object for formatting numbers in the specified locale.
+     * Platforms can override this if necessary.
+     * 
+     * @param locale The locale
+     * @return The number format object
+     */
+    protected void setValueNumberFormat(NumberFormat format)
+    {
+        _valueNumberFormat = format;
+    }
+    
+    /**
      * Adds a char sequence that needs escaping, and its escaped version.
      * 
      * @param charSequence   The char sequence
@@ -1506,13 +1568,12 @@
         // TODO: Handle binary types (BINARY, VARBINARY, LONGVARBINARY, BLOB)
         switch (column.getTypeCode())
         {
-            // Note: TIMESTAMP (java.sql.Timestamp) is properly handled by its toString method
             case Types.DATE:
                 result.append(getPlatformInfo().getValueQuoteToken());
-                if (!(value instanceof String) && (_valueDateFormat != null))
+                if (!(value instanceof String) && (getValueDateFormat() != null))
                 {
                     // TODO: Can the format method handle java.sql.Date properly ?
-                    result.append(_valueDateFormat.format(value));
+                    result.append(getValueDateFormat().format(value));
                 }
                 else
                 {
@@ -1522,10 +1583,10 @@
                 break;
             case Types.TIME:
                 result.append(getPlatformInfo().getValueQuoteToken());
-                if (!(value instanceof String) && (_valueTimeFormat != null))
+                if (!(value instanceof String) && (getValueTimeFormat() != null))
                 {
                     // TODO: Can the format method handle java.sql.Date properly ?
-                    result.append(_valueTimeFormat.format(value));
+                    result.append(getValueTimeFormat().format(value));
                 }
                 else
                 {
@@ -1533,15 +1594,22 @@
                 }
                 result.append(getPlatformInfo().getValueQuoteToken());
                 break;
+            case Types.TIMESTAMP:
+                result.append(getPlatformInfo().getValueQuoteToken());
+                // TODO: SimpleDateFormat does not support nano seconds so we would
+                //       need a custom date formatter for timestamps
+                result.append(value.toString());
+                result.append(getPlatformInfo().getValueQuoteToken());
+                break;
             case Types.REAL:
             case Types.NUMERIC:
             case Types.FLOAT:
             case Types.DOUBLE:
             case Types.DECIMAL:
                 result.append(getPlatformInfo().getValueQuoteToken());
-                if (!(value instanceof String) && (_valueNumberFormat != null))
+                if (!(value instanceof String) && (getValueNumberFormat() != null))
                 {
-                    result.append(_valueNumberFormat.format(value));
+                    result.append(getValueNumberFormat().format(value));
                 }
                 else
                 {

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java?view=diff&rev=462920&r1=462919&r2=462920
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java Wed Oct
11 12:52:26 2006
@@ -18,6 +18,8 @@
 
 import java.io.IOException;
 import java.sql.Types;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.HashSet;
 import java.util.Iterator;
@@ -51,6 +53,11 @@
  */
 public class MSSqlBuilder extends SqlBuilder
 {
+    /** We use a generic date format. */
+    private DateFormat _genericDateFormat = new SimpleDateFormat("yyyy-MM-dd");
+    /** We use a generic date format. */
+    private DateFormat _genericTimeFormat = new SimpleDateFormat("HH:mm:ss");
+
     /**
      * Creates a new builder instance.
      * 
@@ -113,6 +120,76 @@
     {
         writeQuotationOnStatement();
         super.dropExternalForeignKeys(table);
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected DateFormat getValueDateFormat()
+    {
+        return _genericDateFormat;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected DateFormat getValueTimeFormat()
+    {
+        return _genericTimeFormat;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    protected String getValueAsString(Column column, Object value)
+    {
+        if (value == null)
+        {
+            return "NULL";
+        }
+
+        StringBuffer result = new StringBuffer();
+
+        switch (column.getTypeCode())
+        {
+            case Types.REAL:
+            case Types.NUMERIC:
+            case Types.FLOAT:
+            case Types.DOUBLE:
+            case Types.DECIMAL:
+                // SQL Server does not want quotes around the value
+                if (!(value instanceof String) && (getValueNumberFormat() != null))
+                {
+                    result.append(getValueNumberFormat().format(value));
+                }
+                else
+                {
+                    result.append(value.toString());
+                }
+                break;
+            case Types.DATE:
+                result.append("CAST(");
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(value instanceof String ? (String)value : getValueDateFormat().format(value));
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(" AS datetime)");
+                break;
+            case Types.TIME:
+                result.append("CAST(");
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(value instanceof String ? (String)value : getValueTimeFormat().format(value));
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(" AS datetime)");
+                break;
+            case Types.TIMESTAMP:
+                result.append("CAST(");
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(value.toString());
+                result.append(getPlatformInfo().getValueQuoteToken());
+                result.append(" AS datetime)");
+                break;
+        }
+        return super.getValueAsString(column, value);
     }
 
     /**

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/TestBase.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/TestBase.java?view=diff&rev=462920&r1=462919&r2=462920
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/TestBase.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/TestBase.java Wed Oct 11 12:52:26 2006
@@ -49,8 +49,7 @@
     /**
      * Parses the database defined in the given XML definition.
      * 
-     * @param dbDef
-     *            The database XML definition
+     * @param dbDef The database XML definition
      * @return The database model
      */
     protected Database parseDatabaseFromString(String dbDef)
@@ -66,22 +65,22 @@
      * Compares the two strings but ignores any whitespace differences. It also
      * recognizes special delimiter chars.
      * 
-     * @param expected
-     *            The expected string
-     * @param actual
-     *            The actual string
+     * @param expected The expected string
+     * @param actual   The actual string
      */
     protected void assertEqualsIgnoringWhitespaces(String expected, String actual)
     {
-        assertEquals(compressWhitespaces(expected), compressWhitespaces(actual));
+        String processedExpected = compressWhitespaces(expected);
+        String processedActual   = compressWhitespaces(actual);
+
+        assertEquals(processedExpected, processedActual);
     }
 
     /**
      * Compresses the whitespaces in the given string to a single space. Also
      * recognizes special delimiter chars and removes whitespaces before them.
      * 
-     * @param original
-     *            The original string
+     * @param original The original string
      * @return The resulting string
      */
     private String compressWhitespaces(String original)

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java?view=diff&rev=462920&r1=462919&r2=462920
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/platform/TestMSSqlPlatform.java Wed Oct
11 12:52:26 2006
@@ -18,6 +18,12 @@
 
 import org.apache.ddlutils.TestPlatformBase;
 import org.apache.ddlutils.platform.mssql.MSSqlPlatform;
+import org.apache.oro.text.regex.MatchResult;
+import org.apache.oro.text.regex.Pattern;
+import org.apache.oro.text.regex.PatternMatcher;
+import org.apache.oro.text.regex.PatternMatcherInput;
+import org.apache.oro.text.regex.Perl5Compiler;
+import org.apache.oro.text.regex.Perl5Matcher;
 
 /**
  * Tests the Microsoft SQL Server platform.
@@ -50,21 +56,35 @@
      */
     public void testColumnTypes() throws Exception
     {
+        String sql = createTestDatabase(COLUMN_TEST_SCHEMA);
+
+        // Since we have no way of knowing the auto-generated variables in the SQL,
+        // we simply try to extract it from the SQL
+        Pattern        declarePattern    = new Perl5Compiler().compile("DECLARE @([\\S]+)
[^@]+@([\\S]+)");
+        PatternMatcher matcher           = new Perl5Matcher();
+        String         tableNameVar      = "tablename";
+        String         constraintNameVar = "constraintname";
+
+        if (matcher.contains(sql, declarePattern))
+        {
+            tableNameVar      = matcher.getMatch().group(1);
+            constraintNameVar = matcher.getMatch().group(2);
+        }
         assertEqualsIgnoringWhitespaces(
             "SET quoted_identifier on;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'coltype')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVar + " nvarchar(256), @" + constraintNameVar + " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'coltype'  OPEN
refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar
+ ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -105,7 +125,7 @@
             "    \"COL_VARBINARY\"       VARBINARY(15),\n"+
             "    \"COL_VARCHAR\"         VARCHAR(15)\n"+
             ");\n",
-            createTestDatabase(COLUMN_TEST_SCHEMA));
+            sql);
     }
 
 
@@ -114,22 +134,36 @@
      */
     public void testColumnConstraints() throws Exception
     {
-        // this is not valid sql as a table can have only one identity column at most 
+        String sql = createTestDatabase(COLUMN_CONSTRAINT_TEST_SCHEMA);
+
+        // Since we have no way of knowing the auto-generated variables in the SQL,
+        // we simply try to extract it from the SQL
+        Pattern        declarePattern    = new Perl5Compiler().compile("DECLARE @([\\S]+)
[^@]+@([\\S]+)");
+        PatternMatcher matcher           = new Perl5Matcher();
+        String         tableNameVar      = "tablename";
+        String         constraintNameVar = "constraintname";
+
+        if (matcher.contains(sql, declarePattern))
+        {
+            tableNameVar      = matcher.getMatch().group(1);
+            constraintNameVar = matcher.getMatch().group(2);
+        }
+        // Note that this is not valid SQL as a table can have only one identity column at
most 
         assertEqualsIgnoringWhitespaces(
             "SET quoted_identifier on;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'constraints')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVar + " nvarchar(256), @" + constraintNameVar + " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'constraints'
 OPEN refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar
+ ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -146,7 +180,7 @@
             "    \"COL_AUTO_INCR\"        DECIMAL(19,0) IDENTITY(1,1),\n"+
             "    PRIMARY KEY (\"COL_PK\", \"COL_PK_AUTO_INCR\")\n"+
             ");\n",
-            createTestDatabase(COLUMN_CONSTRAINT_TEST_SCHEMA));
+            sql);
     }
 
     /**
@@ -154,6 +188,24 @@
      */
     public void testTableConstraints() throws Exception
     {
+        String sql = createTestDatabase(TABLE_CONSTRAINT_TEST_SCHEMA);
+
+        // Since we have no way of knowing the auto-generated variables in the SQL,
+        // we simply try to extract it from the SQL
+        Pattern             declarePattern     = new Perl5Compiler().compile("DECLARE @([\\S]+)
[^@]+@([\\S]+)");
+        PatternMatcherInput input              = new PatternMatcherInput(sql);
+        PatternMatcher      matcher            = new Perl5Matcher();
+        String[]            tableNameVars      = { "tablename", "tablename", "tablename"
};
+        String[]            constraintNameVars = { "constraintname", "constraintname", "constraintname"
};
+
+        for (int idx = 0; (idx < 3) && matcher.contains(input, declarePattern);
idx++)
+        {
+            MatchResult result = matcher.getMatch();
+
+            tableNameVars[idx]      = result.group(1);
+            constraintNameVars[idx] = result.group(2);
+            input.setCurrentOffset(result.endOffset(2));
+        }
         assertEqualsIgnoringWhitespaces(
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = 'testfk')\n"+
@@ -165,16 +217,16 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table3')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVars[0] + " nvarchar(256), @" + constraintNameVars[0]
+ " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table3'  OPEN
refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVars[0] + ", @" + constraintNameVars[0]
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVars[0] + "+' DROP CONSTRAINT '+@"
+ constraintNameVars[0] + ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVars[0] + ", @" + constraintNameVars[0]
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -183,16 +235,16 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table2')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVars[1] + " nvarchar(256), @" + constraintNameVars[1]
+ " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table2'  OPEN
refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVars[1] + ", @" + constraintNameVars[1]
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVars[1] + "+' DROP CONSTRAINT '+@"
+ constraintNameVars[1] + ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVars[1] + ", @" + constraintNameVars[1]
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -201,16 +253,16 @@
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'table1')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVars[2] + " nvarchar(256), @" + constraintNameVars[2]
+ " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'table1'  OPEN
refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVars[2] + ", @" + constraintNameVars[2]
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVars[2] + "+' DROP CONSTRAINT '+@"
+ constraintNameVars[2] + ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVars[2] + ", @" + constraintNameVars[2]
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -245,7 +297,7 @@
             ");\n"+
             "ALTER TABLE \"table2\" ADD CONSTRAINT \"table2_FK_COL_FK_1_COL_FK_2_table1\"
FOREIGN KEY (\"COL_FK_1\", \"COL_FK_2\") REFERENCES \"table1\" (\"COL_PK_2\", \"COL_PK_1\");\n"+
             "ALTER TABLE \"table3\" ADD CONSTRAINT \"testfk\" FOREIGN KEY (\"COL_FK\") REFERENCES
\"table2\" (\"COL_PK\");\n",
-            createTestDatabase(TABLE_CONSTRAINT_TEST_SCHEMA));
+            sql);
     }
 
     /**
@@ -253,21 +305,35 @@
      */
     public void testCharacterEscaping() throws Exception
     {
+        String sql = createTestDatabase(COLUMN_CHAR_SEQUENCES_TO_ESCAPE);
+
+        // Since we have no way of knowing the auto-generated variables in the SQL,
+        // we simply try to extract it from the SQL
+        Pattern        declarePattern    = new Perl5Compiler().compile("DECLARE @([\\S]+)
[^@]+@([\\S]+)");
+        PatternMatcher matcher           = new Perl5Matcher();
+        String         tableNameVar      = "tablename";
+        String         constraintNameVar = "constraintname";
+
+        if (matcher.contains(sql, declarePattern))
+        {
+            tableNameVar      = matcher.getMatch().group(1);
+            constraintNameVar = matcher.getMatch().group(2);
+        }
         assertEqualsIgnoringWhitespaces(
             "SET quoted_identifier on;\n"+
             "SET quoted_identifier on;\n"+
             "IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'escapedcharacters')\n"+
             "BEGIN\n"+
-            "  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)\n"+
+            "  DECLARE @" + tableNameVar + " nvarchar(256), @" + constraintNameVar + " nvarchar(256)\n"+
             "  DECLARE refcursor CURSOR FOR\n"+
             "  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname\n"+
             "    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid\n"+
             "    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'escapedcharacters'
 OPEN refcursor\n"+
-            "  FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "  FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "  WHILE @@FETCH_STATUS = 0\n"+
             "    BEGIN\n"+
-            "      EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)\n"+
-            "      FETCH NEXT FROM refcursor INTO @tablename, @constraintname\n"+
+            "      EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar
+ ")\n"+
+            "      FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar
+ "\n"+
             "    END\n"+
             "  CLOSE refcursor\n"+
             "  DEALLOCATE refcursor\n"+
@@ -280,6 +346,6 @@
             "    \"COL_TEXT\" VARCHAR(128) DEFAULT '\'\'',\n"+
             "    PRIMARY KEY (\"COL_PK\")\n"+
             ");\n",
-            createTestDatabase(COLUMN_CHAR_SEQUENCES_TO_ESCAPE));
+            sql);
     }
 }



Mime
View raw message