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);
+ }
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ protected DateFormat getValueDateFormat()
+ {
+ return _genericDateFormat;
+ }
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ protected DateFormat getValueTimeFormat()
+ {
+ return _genericTimeFormat;
+ }
+
+ /**
+ * [EMAIL PROTECTED]
+ */
+ 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]+) [EMAIL PROTECTED]@([\\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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]+) [EMAIL PROTECTED]@([\\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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]+) [EMAIL PROTECTED]@([\\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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]+) [EMAIL PROTECTED]@([\\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 '[EMAIL PROTECTED]' DROP CONSTRAINT
'[EMAIL PROTECTED])\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);
}
}
