Hello all,
we've been using Torque for a while to generate SQL based on our OJB
repository. We've had to tweak a few things to get them to work though,
and we thought we should contribute them back.
1. When generating DECIMAL columns, we needed to generate precision and
scale.
2. When generating indexes, we needed (at least on Oracle) to be able to
specify the tablespace for the index. It's a huge performance boost to
have the index located on a separate drive from the table.
3. We use non-native auto incrementing heavily, and needed to be able to
turn it off. Although Table has an Id Method, it wasn't being checked in
the velocity macros. We needed to enable that.
4. There were some weird end of file characters in some of the velocity
templates that caused SQL generation to die a horrible death under
Windows.
Attached is a patch that addresses all these issues. Here's a short list
of the affected files:
src/generator/src/dtd/database.dtd
src/generator/src/java/org/apache/torque/engine/database/model/Column.java
src/generator/src/java/org/apache/torque/engine/database/model/Table.java
src/generator/src/templates/sql/base/mssql/columns.vm
src/generator/src/templates/sql/base/mssql/db.props
src/generator/src/templates/sql/base/oracle/columns.vm
src/generator/src/templates/sql/base/oracle/db.props
src/generator/src/templates/sql/base/oracle/index.vm
src/generator/src/templates/sql/base/oracle/primarykey.vm
src/generator/src/templates/sql/base/oracle/unique.vm
src/generator/src/templates/sql/base/postgresql/columns.vm
src/generator/src/templates/sql/base/postgresql/db.props
src/generator/src/templates/sql/base/postgresql/index.vm
src/generator/src/templates/sql/base/postgresql/table.vm
We've only modified Oracle, PostgreSQL, and MS SQL velocity macros because
those are the only databases we've been hitting. As we add more, we'll be
glad to submit those changes back as well.
If you have any questions, I'd be glad to answer them. We're eager to get
the changes into Torque so that we don't have to maintain a separate local
version.
Regards,
Chris Greenlee
Index: src/generator/src/dtd/database.dtd
===================================================================
RCS file: /home/cvspublic/db-torque/src/generator/src/dtd/database.dtd,v
retrieving revision 1.1
diff -u -r1.1 database.dtd
--- src/generator/src/dtd/database.dtd 10 Feb 2003 13:22:37 -0000 1.1
+++ src/generator/src/dtd/database.dtd 19 Mar 2003 02:26:07 -0000
@@ -66,6 +66,7 @@
javaNamingMethod (nochange|underscore|javaname) #IMPLIED
heavyIndexing (true|false) #IMPLIED
description CDATA #IMPLIED
+ indexTablespace CDATA #IMPLIED
>
<!ELEMENT id-method-parameter EMPTY>
@@ -91,6 +92,8 @@
) "VARCHAR"
javaType (object|primitive) #IMPLIED
size CDATA #IMPLIED
+ precision CDATA #IMPLIED
+ scale CDATA #IMPLIED
default CDATA #IMPLIED
autoIncrement (true|false) "false"
inheritance (single|false) "false"
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.1
diff -u -r1.1 Column.java
--- src/generator/src/java/org/apache/torque/engine/database/model/Column.java 10 Feb
2003 13:20:58 -0000 1.1
+++ src/generator/src/java/org/apache/torque/engine/database/model/Column.java 19 Mar
2003 02:26:09 -0000
@@ -81,6 +81,9 @@
private String javaNamingMethod;
private boolean isNotNull = false;
private String size;
+ private String precision;
+ private String scale;
+
/** type as defined in schema.xml */
private String torqueType;
private String javaType;
@@ -198,6 +201,8 @@
defaultValue = attrib.getValue("default");
size = attrib.getValue("size");
+ precision = attrib.getValue("precision");
+ scale = attrib.getValue("scale");
setType(attrib.getValue("type"));
@@ -638,6 +643,42 @@
public void setSize(String newSize)
{
size = newSize;
+ }
+
+ /**
+ * Returns the precision for this column.
+ * @return The precision for this column.
+ */
+ public String getPrecision()
+ {
+ return precision;
+ }
+
+ /**
+ * Sets the precision for this column.
+ * @param precision The precision for this column.
+ */
+ public void setPrecision(String precision)
+ {
+ this.precision = precision;
+ }
+
+ /**
+ * Returns the scale for this column.
+ * @return The scale for this column.
+ */
+ public String getScale()
+ {
+ return scale;
+ }
+
+ /**
+ * Sets the scale for this column.
+ * @param scale The scale for this column.
+ */
+ public void setScale(String scale)
+ {
+ this.scale = scale;
}
/**
Index: src/generator/src/java/org/apache/torque/engine/database/model/Table.java
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/java/org/apache/torque/engine/database/model/Table.java,v
retrieving revision 1.1
diff -u -r1.1 Table.java
--- src/generator/src/java/org/apache/torque/engine/database/model/Table.java 10 Feb
2003 13:20:58 -0000 1.1
+++ src/generator/src/java/org/apache/torque/engine/database/model/Table.java 19 Mar
2003 02:26:11 -0000
@@ -89,6 +89,7 @@
private String javaName;
private String idMethod;
private String javaNamingMethod;
+ private String indexTablespace;
private Database tableParent;
private List referrers;
private List foreignTableNames;
@@ -144,6 +145,11 @@
javaName = attrib.getValue("javaName");
idMethod = attrib.getValue("idMethod");
+ String indexTablespace = attrib.getValue("indexTablespace");
+ if (indexTablespace != null && indexTablespace.trim().length() > 0) {
+ setIndexTablespace(indexTablespace.trim());
+ }
+
// retrieves the method for converting from specified name to
// a java name.
javaNamingMethod = attrib.getValue("javaNamingMethod");
@@ -1029,6 +1035,22 @@
public void setForReferenceOnly(boolean v)
{
this.forReferenceOnly = v;
+ }
+
+ /**
+ * The name of the tablespace to create indexes on, if the database supports them.
+ * @return The name of the tablespace to create the index on.
+ */
+ public String getIndexTablespace() {
+ return indexTablespace;
+ }
+
+ /**
+ * Sets the name of the tablespace to create the index on.
+ * @param indexTablespace The name of the index tablespace.
+ */
+ public void setIndexTablespace(String indexTablespace) {
+ this.indexTablespace = indexTablespace;
}
/**
Index: src/generator/src/templates/sql/base/mssql/columns.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/mssql/columns.vm,v
retrieving revision 1.1
diff -u -r1.1 columns.vm
--- src/generator/src/templates/sql/base/mssql/columns.vm 10 Feb 2003 13:18:49
-0000 1.1
+++ src/generator/src/templates/sql/base/mssql/columns.vm 19 Mar 2003 02:26:11
-0000
@@ -1,14 +1,23 @@
#foreach ($col in $table.Columns)
#set ( $type = $dbprops.get($col.Type) )
- #if ($type == "INT" || $type == "TEXT")
+ #if ($type == "DECIMAL")
+ #set ( $type = "$type ($col.precision,$col.scale)")
+ #end
+ #if ($type == "INT" || $type == "TEXT" || $type == "DECIMAL")
#set ( $size = "" )
- #else
+ #else
#set ( $size = $col.printSize() )
#end
#set ( $default = $col.DefaultSetting )
#set ( $nullString = $strings.select($col.isNotNull(),
$dbprops.get("NOTNULL"),$dbprops.get("NULL")) )
- #set ( $autoIncrement = $strings.select($col.isAutoIncrement(),
$dbprops.get("AUTOINCREMENT"),"") )
- #set ( $entry = "$col.Name $type $size $default $nullString $autoIncrement," )
+ #if ($col.isAutoIncrement() && $table.IdMethod == "native")
+ #set ( $autoIncrement = $strings.select($col.isAutoIncrement(),
$dbprops.get("AUTOINCREMENT"),"") )
+ #else
+ #set ( $autoIncrement = " " )
+ #end
+ #set ( $entry = "$col.Name $type " )
+ #if ($type != "INT") #set ( $entry = "$entry $size" )#end
+ #set ( $entry = "$entry $default $nullString $autoIncrement," )
$strings.sub($strings.collapseSpaces($entry)," ,",",")
#end
Index: src/generator/src/templates/sql/base/mssql/db.props
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/mssql/db.props,v
retrieving revision 1.1
diff -u -r1.1 db.props
--- src/generator/src/templates/sql/base/mssql/db.props 10 Feb 2003 13:18:49 -0000
1.1
+++ src/generator/src/templates/sql/base/mssql/db.props 19 Mar 2003 02:26:11 -0000
@@ -2,19 +2,19 @@
TINYINT = TINYINT
SMALLINT = SMALLINT
INTEGER = INT
-BIGINT = BIGINT
+BIGINT = INT
FLOAT = FLOAT
REAL = REAL
-DOUBLE = FLOAT
+DOUBLE = DECIMAL
NUMERIC = NUMERIC
DECIMAL = DECIMAL
-CHAR = CHAR
+CHAR = VARCHAR
VARCHAR = VARCHAR
LONGVARCHAR = TEXT
DATE = DATETIME
TIME = DATETIME
TIMESTAMP = DATETIME
-BINARY = BINARY(7132)
+BINARY = BINARY
VARBINARY = IMAGE
LONGVARBINARY = IMAGE
NULL = NULL
@@ -31,5 +31,5 @@
NOTNULL = NOT NULL
removeNotNullWithAutoIncrement = false
+
suppressDefaults = false
-maxColumnNameLength = 30
Index: src/generator/src/templates/sql/base/oracle/columns.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/oracle/columns.vm,v
retrieving revision 1.1
diff -u -r1.1 columns.vm
--- src/generator/src/templates/sql/base/oracle/columns.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/oracle/columns.vm 19 Mar 2003 02:26:11
-0000
@@ -1,5 +1,6 @@
#foreach ($col in $table.Columns)
#set ( $type = $dbprops.get($col.Type) )
+ #if ($type == "DECIMAL") #set ( $type = "$type ($col.precision,$col.scale)")#end
#set ( $size = $col.printSize() )
#set ( $default = $col.DefaultSetting )
#set ( $nullString = $strings.select($col.isNotNull(),
$dbprops.get("NOTNULL"),"") )
Index: src/generator/src/templates/sql/base/oracle/db.props
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/oracle/db.props,v
retrieving revision 1.2
diff -u -r1.2 db.props
--- src/generator/src/templates/sql/base/oracle/db.props 23 Feb 2003 20:09:26
-0000 1.2
+++ src/generator/src/templates/sql/base/oracle/db.props 19 Mar 2003 02:26:11
-0000
@@ -12,9 +12,9 @@
BIGINT = NUMBER (20, 0)
FLOAT = FLOAT
REAL = NUMBER
-DOUBLE = FLOAT
+DOUBLE = DECIMAL
NUMERIC = NUMBER
-DECIMAL = NUMBER
+DECIMAL = DECIMAL
CHAR = CHAR
VARCHAR = VARCHAR2
LONGVARCHAR = VARCHAR2 (2000)
@@ -46,4 +46,4 @@
maxColumnNameLength = 30
-idMethod = sequence
+idMethod=sequence
Index: src/generator/src/templates/sql/base/oracle/index.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/oracle/index.vm,v
retrieving revision 1.1
diff -u -r1.1 index.vm
--- src/generator/src/templates/sql/base/oracle/index.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/oracle/index.vm 19 Mar 2003 02:26:11
-0000
@@ -1,3 +1,10 @@
+#if ($table.IndexTablespace == true)
+ #set($useIndexTablespace=true)
+#else
+ #set($useIndexTablespace=false)
+#end
+
+
#foreach ($index in $table.Indices)
-CREATE#if ($index.Unique)UNIQUE#end INDEX $index.Name ON $table.Name
($index.ColumnList);
+CREATE#if ($index.Unique)UNIQUE#end INDEX $index.Name ON $table.Name
($index.ColumnList)#if ($useIndexTablespace) TABLESPACE $table.IndexTablespace#end;
#end
Index: src/generator/src/templates/sql/base/oracle/primarykey.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/oracle/primarykey.vm,v
retrieving revision 1.1
diff -u -r1.1 primarykey.vm
--- src/generator/src/templates/sql/base/oracle/primarykey.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/oracle/primarykey.vm 19 Mar 2003 02:26:11
-0000
@@ -3,5 +3,5 @@
#if ($table.hasPrimaryKey())
ALTER TABLE $table.Name
ADD CONSTRAINT ${table.Name.substring(0,$length)}_PK
-PRIMARY KEY ($table.printPrimaryKey());
+PRIMARY KEY ($table.printPrimaryKey()) #if ($table.IndexTablespace) USING INDEX
TABLESPACE $table.IndexTablespace#else -- YAY!#end;
#end
Index: src/generator/src/templates/sql/base/oracle/unique.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/oracle/unique.vm,v
retrieving revision 1.1
diff -u -r1.1 unique.vm
--- src/generator/src/templates/sql/base/oracle/unique.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/oracle/unique.vm 19 Mar 2003 02:26:11
-0000
@@ -1,3 +1,10 @@
-#foreach ($unique in $table.Unices)
- CONSTRAINT $unique.Name UNIQUE ($unique.ColumnList),
+#if ($table.IndexTablespace == true)
+ #set($useIndexTablespace=true)
+#else
+ #set($useIndexTablespace=false)
#end
+
+
+#foreach ($unique in $table.Unices)
+ CONSTRAINT $unique.Name UNIQUE ($unique.ColumnList)#if ($useIndexTablespace)
USING INDEX TABLESPACE $table.IndexTablespace#end,
+#end
\ No newline at end of file
Index: src/generator/src/templates/sql/base/postgresql/columns.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/postgresql/columns.vm,v
retrieving revision 1.1
diff -u -r1.1 columns.vm
--- src/generator/src/templates/sql/base/postgresql/columns.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/postgresql/columns.vm 19 Mar 2003 02:26:11
-0000
@@ -9,35 +9,35 @@
#else
#set ( $size = "" )
#end
- #if (($table.IdMethod == "native") && ($col.isPrimaryKey()))
- #set ( $default = "DEFAULT nextval('$table.SequenceName')" )
- #else
- #set ( $default = $col.DefaultSetting )
- #end
+ #set ( $default = $col.DefaultSetting )
#set ( $nullString = $strings.select($col.isNotNull(),
$dbprops.get("NOTNULL"),"") )
#set ( $entry = "$col.Name $type $size $default $nullString" )
- #set ( $autoIncrement = $dbprops.get("AUTOINCREMENT") )
+ #if ($col.isAutoIncrement() && $table.IdMethod == "native")
+ #set ( $autoIncrement = $strings.select($col.isAutoIncrement(),
$dbprops.get("AUTOINCREMENT"),"") )
+ #else
+ #set ( $autoIncrement = " int" )
+ #end
#set ( $entry = $strings.select($col.isAutoIncrement(), "$col.Name
$autoIncrement", $entry) )
##
## the following works if you want to inline your foreign keys
## if you turn it on, be sure to comment out foreignkey.vm
#if (false)
- #if ($col.isForeignKey())
- #set ($fk = $col.ForeignKey)
- #set ($entry = "$entry REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)")
- #if ($fk.hasOnUpdate())
- #set ($entry = "$entry ON UPDATE $fk.OnUpdate ")
- #end
- #if ($fk.hasOnDelete())
- #set ($entry = "$entry ON DELETE $fk.OnDelete ")
- #end
- #end
+#if ($col.isForeignKey())
+#set ($fk = $col.ForeignKey)
+#set ($entry = "$entry REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)")
+#if ($fk.hasOnUpdate())
+#set ($entry = "$entry ON UPDATE $fk.OnUpdate ")
#end
+#if ($fk.hasOnDelete())
+#set ($entry = "$entry ON DELETE $fk.OnDelete ")
+#end
+#end
+#end
+#set ( $entry = $strings.collapseSpaces("$entry,") )
+ $strings.sub($entry," ,",",")
## add a nice comment
#if ($col.isForeignKey())
- #set ($fk = $col.ForeignKey)
+#set ($fk = $col.ForeignKey)
-- REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)
#end
-#set ( $entry = $strings.collapseSpaces("$entry,") )
- $strings.sub($entry," ,",",")
#end
Index: src/generator/src/templates/sql/base/postgresql/db.props
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/postgresql/db.props,v
retrieving revision 1.1
diff -u -r1.1 db.props
--- src/generator/src/templates/sql/base/postgresql/db.props 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/postgresql/db.props 19 Mar 2003 02:26:11
-0000
@@ -1,27 +1,23 @@
# Taken from java.sql.Types in the IBM 1.3 JDK
# JDBC to PostgreSQL mappings.
-#
-# See http://www.postgresql.org/idocs/index.php?datatype.html for more
-# information on PostgreSQL data types.
-BIT = boolean
+BIT =
TINYINT = int2
SMALLINT = int2
INTEGER = integer
BIGINT = int8
FLOAT = float
REAL = float
-BOOLEANCHAR = char
+BOOLEANCHAR = boolean
BOOLEANINT = int2
-DOUBLE = double
-# numeric(precision, scale) / decimal(precision, scale)
-NUMERIC = numeric
-DECIMAL = decimal
-CHAR = char
+DOUBLE = double precision
+NUMERIC =
+DECIMAL =
+CHAR = varchar
VARCHAR = varchar
LONGVARCHAR = bytea
-DATE = date
+DATE = date
TIME = time
TIMESTAMP = timestamp
BINARY = bytea
@@ -47,4 +43,3 @@
suppressDefaults = false
idMethod=sequence
-
Index: src/generator/src/templates/sql/base/postgresql/index.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/postgresql/index.vm,v
retrieving revision 1.1
diff -u -r1.1 index.vm
--- src/generator/src/templates/sql/base/postgresql/index.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/postgresql/index.vm 19 Mar 2003 02:26:11
-0000
@@ -1,4 +1,3 @@
#foreach ($index in $table.Indices)
CREATE#if($index.Unique) UNIQUE#end INDEX $index.Name ON $table.Name
($index.ColumnList);
#end
-
Index: src/generator/src/templates/sql/base/postgresql/table.vm
===================================================================
RCS file:
/home/cvspublic/db-torque/src/generator/src/templates/sql/base/postgresql/table.vm,v
retrieving revision 1.1
diff -u -r1.1 table.vm
--- src/generator/src/templates/sql/base/postgresql/table.vm 10 Feb 2003 13:18:47
-0000 1.1
+++ src/generator/src/templates/sql/base/postgresql/table.vm 19 Mar 2003 02:26:12
-0000
@@ -18,4 +18,3 @@
);
#if($index.length() > 0)$strings.chop($index,1)#end
-
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
