I've been trying to use Torque to write database independent schema and data files and allow the loading of these to MySQL for development and (soon) Oracle for production.
I've discovered (and fixed) a number of problems:
1. Spaces were sometimes omitted (causing invalid SQL) and usually a
few extra spaces were added too.I noticed that someone's fix a couple of days ago partially addressed this, but I think I've got it a little more right.
I've adjusted the unit tests correspondingly.
2. A default value of an empty string seemed silly.
I changed this to no default and thus null instead. Using TorqueJDBCTransformTask followed by TorqueSQLTask now changes the schema in the database less for me (which I'd guess is better).
I've included a unified diff of my work, partly just in the hope that these changes will be incorporated into the next release of Torque and thus prevent me from needing custom modifications, but also in the hope that it might help someone else.
Thanks.
Index: src/generator/src/java/org/apache/torque/engine/database/model/Column.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/java/org/apache/torque/engine/database/model/Column.java,v
retrieving revision 1.22
diff -U3 -r1.22 Column.java
--- src/generator/src/java/org/apache/torque/engine/database/model/Column.java 27 Jan
2004 01:23:16 -0000 1.22
+++ src/generator/src/java/org/apache/torque/engine/database/model/Column.java 29 Jan
2004 22:29:47 -0000
@@ -426,12 +426,17 @@
/**
* Return NOT NULL String for this column
*
- * @return "NOT NULL" if null values are not allowed or an empty String.
+ * @return " NOT NULL" if null values are not allowed or an empty String.
*/
public String getNotNullString()
{
- return getTable().getDatabase().getPlatform()
+ String notNull = getTable().getDatabase().getPlatform()
.getNullString(this.isNotNull());
+ if(notNull.length() == 0) {
+ return "";
+ } else {
+ return " " + notNull;
+ }
}
/**
@@ -796,7 +801,10 @@
if (isAutoIncrement()
&& IDMethod.NATIVE.equals(getTable().getIdMethod()))
{
- return getPlatform().getAutoIncrement();
+ String autoIncrement = getPlatform().getAutoIncrement();
+ if(autoIncrement.length() > 0) {
+ return " " + autoIncrement;
+ }
}
return "";
}
@@ -991,24 +999,21 @@
if (getPlatform().hasSize(getDomain().getSqlType()))
{
sb.append(getDomain().printSize());
- sb.append(' ');
}
- if (getDomain().getDefaultValue() != null)
+ String defaultValue = getDomain().getDefaultValue();
+ if ((defaultValue != null) && (defaultValue.length() > 0))
{
- sb.append("default ");
if (TypeMap.isTextType(getDomain().getType()))
{
// TODO: Properly SQL-escape the text.
- sb.append('\'').append(getDefaultValue()).append('\'');
+ sb.append(" default ").append('\'').append(defaultValue).append('\'');
}
else
{
- sb.append(getDefaultValue());
+ sb.append(" default ").append(defaultValue);
}
- sb.append(' ');
}
sb.append(getNotNullString());
- sb.append(' ');
sb.append(getAutoIncrementString());
return sb.toString();
}
Index: src/generator/src/test/org/apache/torque/engine/database/model/DomainTest.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/test/org/apache/torque/engine/database/model/DomainTest.java,v
retrieving revision 1.9
diff -U3 -r1.9 DomainTest.java
--- src/generator/src/test/org/apache/torque/engine/database/model/DomainTest.java
17 Jan 2004 01:52:11 -0000 1.9
+++ src/generator/src/test/org/apache/torque/engine/database/model/DomainTest.java
29 Jan 2004 22:29:47 -0000
@@ -112,7 +112,7 @@
assertEquals("VARCHAR", name.getType());
assertEquals("VARCHAR", name.getDomain().getSqlType());
assertEquals("40", name.getSize());
- assertEquals("name VARCHAR(40) ", name.getSqlString());
+ assertEquals("name VARCHAR(40)", name.getSqlString());
Column price = table.getColumn("price"Ex);
assertEquals("NUMERIC", price.getTorqueType());
assertEquals("NUMERIC", price.getType());
@@ -121,7 +121,7 @@
assertEquals("2", price.getScale());
assertEquals("0", price.getDefaultValue());
assertEquals("(10,2)", price.printSize());
- assertEquals("price DECIMAL(10,2) default 0 ", price.getSqlString());
+ assertEquals("price DECIMAL(10,2) default 0", price.getSqlString());
}
/**
@@ -138,7 +138,7 @@
assertEquals("2", price.getScale());
assertEquals("1000", price.getDefaultValue());
assertEquals("(12,2)", price.printSize());
- assertEquals("price DECIMAL(12,2) default 1000 ", price.getSqlString());
+ assertEquals("price DECIMAL(12,2) default 1000", price.getSqlString());
}
public void testDecimalColumn() throws Exception
@@ -151,7 +151,7 @@
assertEquals("10", col.getSize());
assertEquals("3", col.getScale());
assertEquals("(10,3)", col.printSize());
- assertEquals("decimal_col DECIMAL(10,3) ", col.getSqlString());
+ assertEquals("decimal_col DECIMAL(10,3)", col.getSqlString());
}
public void testDateColumn() throws Exception
@@ -162,14 +162,14 @@
assertEquals("DATE", col.getType());
assertEquals("DATETIME", col.getDomain().getSqlType());
assertEquals("", col.printSize());
- assertEquals("date_col DATETIME ", col.getSqlString());
+ assertEquals("date_col DATETIME", col.getSqlString());
}
public void testNativeAutoincrement() throws Exception
{
Table table = db.getTable("native");
Column col = table.getColumn("native_id");
- assertEquals("AUTO_INCREMENT", col.getAutoIncrementString());
+ assertEquals(" AUTO_INCREMENT", col.getAutoIncrementString());
assertEquals("native_id INTEGER NOT NULL AUTO_INCREMENT", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
@@ -180,7 +180,7 @@
Table table = db.getTable("article");
Column col = table.getColumn("article_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("article_id INTEGER NOT NULL ", col.getSqlString());
+ assertEquals("article_id INTEGER NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -193,7 +193,7 @@
assertEquals("BOOLEANINT", col.getTorqueType());
assertEquals("INTEGER", col.getType());
assertEquals("INTEGER", col.getDomain().getSqlType());
- assertEquals("cbooleanint INTEGER ", col.getSqlString());
+ assertEquals("cbooleanint INTEGER", col.getSqlString());
}
public void testBlob() throws Exception
@@ -203,7 +203,7 @@
assertEquals("", col.getAutoIncrementString());
assertEquals("BLOB", col.getTorqueType());
assertEquals("LONGBLOB", col.getDomain().getSqlType());
- assertEquals("cblob LONGBLOB ", col.getSqlString());
+ assertEquals("cblob LONGBLOB", col.getSqlString());
}
}
Index:
src/generator/src/test/org/apache/torque/engine/database/model/MssqlDomainTest.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/test/org/apache/torque/engine/database/model/MssqlDomainTest.java,v
retrieving revision 1.1
diff -U3 -r1.1 MssqlDomainTest.java
---
src/generator/src/test/org/apache/torque/engine/database/model/MssqlDomainTest.java 7
Dec 2003 12:47:37 -0000 1.1
+++
src/generator/src/test/org/apache/torque/engine/database/model/MssqlDomainTest.java 29
Jan 2004 22:29:47 -0000
@@ -97,7 +97,7 @@
Column name = table.getColumn("name");
assertEquals("VARCHAR", name.getDomain().getSqlType());
assertEquals("40", name.getSize());
- assertEquals("name VARCHAR(40) NULL ", name.getSqlString());
+ assertEquals("name VARCHAR(40) NULL", name.getSqlString());
Column price = table.getColumn("price");
assertEquals("NUMERIC", price.getTorqueType());
assertEquals("NUMERIC", price.getDomain().getSqlType());
@@ -105,7 +105,7 @@
assertEquals("2", price.getScale());
assertEquals("0", price.getDefaultValue());
assertEquals("(10,2)", price.printSize());
- assertEquals("price NUMERIC(10,2) default 0 NULL ", price.getSqlString());
+ assertEquals("price NUMERIC(10,2) default 0 NULL", price.getSqlString());
}
/**
@@ -121,7 +121,7 @@
assertEquals("2", price.getScale());
assertEquals("1000", price.getDefaultValue());
assertEquals("(12,2)", price.printSize());
- assertEquals("price NUMERIC(12,2) default 1000 NULL ", price.getSqlString());
+ assertEquals("price NUMERIC(12,2) default 1000 NULL", price.getSqlString());
}
public void testDecimalColumn() throws Exception
@@ -133,7 +133,7 @@
assertEquals("10", col.getSize());
assertEquals("3", col.getScale());
assertEquals("(10,3)", col.printSize());
- assertEquals("decimal_col DECIMAL(10,3) NULL ", col.getSqlString());
+ assertEquals("decimal_col DECIMAL(10,3) NULL", col.getSqlString());
}
public void testDateColumn() throws Exception
@@ -143,14 +143,14 @@
assertEquals("DATE", col.getTorqueType());
assertEquals("DATETIME", col.getDomain().getSqlType());
assertEquals("", col.printSize());
- assertEquals("date_col DATETIME NULL ", col.getSqlString());
+ assertEquals("date_col DATETIME NULL", col.getSqlString());
}
public void testNativeAutoincrement() throws Exception
{
Table table = db.getTable("native");
Column col = table.getColumn("native_id");
- assertEquals("IDENTITY", col.getAutoIncrementString());
+ assertEquals(" IDENTITY", col.getAutoIncrementString());
assertEquals("native_id INT NOT NULL IDENTITY", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
@@ -161,7 +161,7 @@
Table table = db.getTable("article");
Column col = table.getColumn("article_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("article_id INT NOT NULL ", col.getSqlString());
+ assertEquals("article_id INT NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -173,7 +173,7 @@
assertEquals("", col.getAutoIncrementString());
assertEquals("BOOLEANINT", col.getTorqueType());
assertEquals("INT", col.getDomain().getSqlType());
- assertEquals("cbooleanint INT NULL ", col.getSqlString());
+ assertEquals("cbooleanint INT NULL", col.getSqlString());
}
}
Index:
src/generator/src/test/org/apache/torque/engine/database/model/OracleDomainTest.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/test/org/apache/torque/engine/database/model/OracleDomainTest.java,v
retrieving revision 1.2
diff -U3 -r1.2 OracleDomainTest.java
---
src/generator/src/test/org/apache/torque/engine/database/model/OracleDomainTest.java
7 Dec 2003 12:47:37 -0000 1.2
+++
src/generator/src/test/org/apache/torque/engine/database/model/OracleDomainTest.java
29 Jan 2004 22:29:47 -0000
@@ -97,7 +97,7 @@
Column name = table.getColumn("name");
assertEquals("VARCHAR2", name.getDomain().getSqlType());
assertEquals("40", name.getSize());
- assertEquals("name VARCHAR2(40) ", name.getSqlString());
+ assertEquals("name VARCHAR2(40)", name.getSqlString());
Column price = table.getColumn("price");
assertEquals("NUMERIC", price.getTorqueType());
assertEquals("NUMBER", price.getDomain().getSqlType());
@@ -105,7 +105,7 @@
assertEquals("2", price.getScale());
assertEquals("0", price.getDefaultValue());
assertEquals("(10,2)", price.printSize());
- assertEquals("price NUMBER(10,2) default 0 ", price.getSqlString());
+ assertEquals("price NUMBER(10,2) default 0", price.getSqlString());
}
/**
@@ -121,7 +121,7 @@
assertEquals("2", price.getScale());
assertEquals("1000", price.getDefaultValue());
assertEquals("(12,2)", price.printSize());
- assertEquals("price NUMBER(12,2) default 1000 ", price.getSqlString());
+ assertEquals("price NUMBER(12,2) default 1000", price.getSqlString());
}
public void testDecimalColumn() throws Exception
@@ -133,7 +133,7 @@
assertEquals("10", col.getSize());
assertEquals("3", col.getScale());
assertEquals("(10,3)", col.printSize());
- assertEquals("decimal_col NUMBER(10,3) ", col.getSqlString());
+ assertEquals("decimal_col NUMBER(10,3)", col.getSqlString());
}
public void testDateColumn() throws Exception
@@ -143,7 +143,7 @@
assertEquals("DATE", col.getTorqueType());
assertEquals("DATE", col.getDomain().getSqlType());
assertEquals("", col.printSize());
- assertEquals("date_col DATE ", col.getSqlString());
+ assertEquals("date_col DATE", col.getSqlString());
}
public void testNativeAutoincrement() throws Exception
@@ -151,7 +151,7 @@
Table table = db.getTable("native");
Column col = table.getColumn("native_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("native_id NUMBER NOT NULL ", col.getSqlString());
+ assertEquals("native_id NUMBER NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -161,7 +161,7 @@
Table table = db.getTable("article");
Column col = table.getColumn("article_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("article_id NUMBER NOT NULL ", col.getSqlString());
+ assertEquals("article_id NUMBER NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -173,7 +173,7 @@
assertEquals("", col.getAutoIncrementString());
assertEquals("BOOLEANINT", col.getTorqueType());
assertEquals("NUMBER", col.getDomain().getSqlType());
- assertEquals("cbooleanint NUMBER(1,0) ", col.getSqlString());
+ assertEquals("cbooleanint NUMBER(1,0)", col.getSqlString());
}
}
Index:
src/generator/src/test/org/apache/torque/engine/database/model/PostgresqlDomainTest.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/test/org/apache/torque/engine/database/model/PostgresqlDomainTest.java,v
retrieving revision 1.3
diff -U3 -r1.3 PostgresqlDomainTest.java
---
src/generator/src/test/org/apache/torque/engine/database/model/PostgresqlDomainTest.java
21 Jan 2004 17:57:25 -0000 1.3
+++
src/generator/src/test/org/apache/torque/engine/database/model/PostgresqlDomainTest.java
29 Jan 2004 22:29:47 -0000
@@ -97,7 +97,7 @@
Column name = table.getColumn("name");
assertEquals("VARCHAR", name.getDomain().getSqlType());
assertEquals("40", name.getSize());
- assertEquals("name VARCHAR(40) ", name.getSqlString());
+ assertEquals("name VARCHAR(40)", name.getSqlString());
Column price = table.getColumn("price");
assertEquals("NUMERIC", price.getTorqueType());
assertEquals("NUMERIC", price.getDomain().getSqlType());
@@ -105,7 +105,7 @@
assertEquals("2", price.getScale());
assertEquals("0", price.getDefaultValue());
assertEquals("(10,2)", price.printSize());
- assertEquals("price NUMERIC(10,2) default 0 ", price.getSqlString());
+ assertEquals("price NUMERIC(10,2) default 0", price.getSqlString());
}
/**
@@ -121,7 +121,7 @@
assertEquals("2", price.getScale());
assertEquals("1000", price.getDefaultValue());
assertEquals("(12,2)", price.printSize());
- assertEquals("price NUMERIC(12,2) default 1000 ", price.getSqlString());
+ assertEquals("price NUMERIC(12,2) default 1000", price.getSqlString());
}
public void testDecimalColumn() throws Exception
@@ -133,7 +133,7 @@
assertEquals("10", col.getSize());
assertEquals("3", col.getScale());
assertEquals("(10,3)", col.printSize());
- assertEquals("decimal_col DECIMAL(10,3) ", col.getSqlString());
+ assertEquals("decimal_col DECIMAL(10,3)", col.getSqlString());
}
public void testDateColumn() throws Exception
@@ -143,7 +143,7 @@
assertEquals("DATE", col.getTorqueType());
assertEquals("DATE", col.getDomain().getSqlType());
assertEquals("", col.printSize());
- assertEquals("date_col DATE ", col.getSqlString());
+ assertEquals("date_col DATE", col.getSqlString());
}
public void testNativeAutoincrement() throws Exception
@@ -151,7 +151,7 @@
Table table = db.getTable("native");
Column col = table.getColumn("native_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("native_id INTEGER NOT NULL ", col.getSqlString());
+ assertEquals("native_id INTEGER NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -161,7 +161,7 @@
Table table = db.getTable("article");
Column col = table.getColumn("article_id");
assertEquals("", col.getAutoIncrementString());
- assertEquals("article_id INTEGER NOT NULL ", col.getSqlString());
+ assertEquals("article_id INTEGER NOT NULL", col.getSqlString());
col = table.getColumn("name");
assertEquals("", col.getAutoIncrementString());
}
@@ -173,7 +173,7 @@
assertEquals("", col.getAutoIncrementString());
assertEquals("BOOLEANINT", col.getTorqueType());
assertEquals("INT2", col.getDomain().getSqlType());
- assertEquals("cbooleanint INT2 ", col.getSqlString());
+ assertEquals("cbooleanint INT2", col.getSqlString());
}
}--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
