Index: cpa/src/main/java/org/castor/cpa/persistence/sql/driver/SQLServerQueryExpression.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/driver/SQLServerQueryExpression.java (revision 8135) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/driver/SQLServerQueryExpression.java (working copy) @@ -45,6 +45,8 @@ package org.castor.cpa.persistence.sql.driver; import java.util.Enumeration; +import java.util.Hashtable; +import java.util.Vector; import org.exolab.castor.jdo.engine.JDBCSyntax; import org.exolab.castor.persist.spi.PersistenceFactory; @@ -61,30 +63,124 @@ } public String getStatement(final boolean lock) { - StringBuffer sql; - boolean first; - - sql = new StringBuffer(); + StringBuffer sql = new StringBuffer(); sql.append(JDBCSyntax.SELECT); if (_distinct) { sql.append(JDBCSyntax.DISTINCT); } - if (_limit != null) { if (!_limit.equals("")) { - sql.append("TOP ").append(_limit).append(" "); + sql.append("TOP (").append(_limit).append(") "); } } + if (_select == null) { + sql.append(getColumnList()); + } else { + sql.append(_select).append(" "); + } - sql.append(getColumnList()); - sql.append(JDBCSyntax.FROM); - // Use HOLDLOCK to lock selected tables. - Enumeration enumeration = _tables.keys(); + // Use outer join syntax for all outer joins. Inner joins come later. + Hashtable tables = new Hashtable(_tables); + Vector done = new Vector(); + boolean first = true; + // gather all outer joins with the same left part + for (int i = 0; i < _joins.size(); ++i) { + Join join = _joins.elementAt(i); + + if (!join._outer || done.contains(join._leftTable)) { + continue; + } + if (first) { + first = false; + } else { + sql.append(JDBCSyntax.TABLE_SEPARATOR); + } + + sql.append(_factory.quoteName(join._leftTable)); + + if (lock) { + sql.append(" WITH (HOLDLOCK) "); + } + + sql.append(JDBCSyntax.LEFT_JOIN); + + String tableName = tables.get(join._rightTable); + + if (join._rightTable.equals(tableName)) { + sql.append(_factory.quoteName(tableName)); + } else { + sql.append(_factory.quoteName(tableName) + " " + + _factory.quoteName(join._rightTable)); + } + + if (lock) { + sql.append(" WITH (HOLDLOCK) "); + } + + sql.append(JDBCSyntax.ON); + for (int j = 0; j < join._leftColumns.length; ++j) { + if (j > 0) { + sql.append(JDBCSyntax.AND); + } + sql.append(_factory.quoteName(join._leftTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join._leftColumns[j])).append(OP_EQUALS); + sql.append(_factory.quoteName(join._rightTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join._rightColumns[j])); + } + + tables.remove(join._leftTable); + tables.remove(join._rightTable); + for (int k = i + 1; k < _joins.size(); ++k) { + Join join2; + + join2 = _joins.elementAt(k); + if (!join2._outer || !join._leftTable.equals(join2._leftTable)) { + continue; + } + sql.append(JDBCSyntax.LEFT_JOIN); + tableName = tables.get(join2._rightTable); + + if (join2._rightTable.equals(tableName)) { + sql.append(_factory.quoteName(tableName)); + } else { + sql.append(_factory.quoteName(tableName) + " " + + _factory.quoteName(join2._rightTable)); + } + + if (lock) { + sql.append(" WITH (HOLDLOCK) "); + } + + sql.append(JDBCSyntax.ON); + for (int j = 0; j < join2._leftColumns.length; ++j) { + if (j > 0) { + sql.append(JDBCSyntax.AND); + } + sql.append(_factory.quoteName(join2._leftTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join2._leftColumns[j])).append(OP_EQUALS); + sql.append(_factory.quoteName(join2._rightTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join2._rightColumns[j])); + } + tables.remove(join2._rightTable); + } + + done.addElement(join._leftTable); + } + Enumeration enumeration = tables.keys(); while (enumeration.hasMoreElements()) { + if (first) { + first = false; + } else { + sql.append(JDBCSyntax.TABLE_SEPARATOR); + } String tableAlias = enumeration.nextElement(); - String tableName = _tables.get(tableAlias); + String tableName = tables.get(tableAlias); if (tableAlias.equals(tableName)) { sql.append(_factory.quoteName(tableName)); } else { @@ -92,42 +188,34 @@ + _factory.quoteName(tableAlias)); } if (lock) { - sql.append(" HOLDLOCK "); + sql.append(" WITH (HOLDLOCK) "); } - if (enumeration.hasMoreElements()) { - sql.append(JDBCSyntax.TABLE_SEPARATOR); - } } + // Use standard join syntax for all inner joins first = true; - // Use asterisk notation to denote a left outer join - // and equals to denote an inner join for (int i = 0; i < _joins.size(); ++i) { Join join; - if (first) { - sql.append(JDBCSyntax.WHERE); - first = false; - } else { - sql.append(JDBCSyntax.AND); - } - join = _joins.elementAt(i); - for (int j = 0; j < join._leftColumns.length; ++j) { - if (j > 0) { + if (!join._outer) { + if (first) { + sql.append(JDBCSyntax.WHERE); + first = false; + } else { sql.append(JDBCSyntax.AND); } - sql.append(_factory.quoteName(join._leftTable - + JDBCSyntax.TABLE_COLUMN_SEPARATOR - + join._leftColumns[j])); - if (join._outer) { - sql.append("*="); - } else { - sql.append(OP_EQUALS); + for (int j = 0; j < join._leftColumns.length; ++j) { + if (j > 0) { + sql.append(JDBCSyntax.AND); + } + sql.append(_factory.quoteName(join._leftTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join._leftColumns[j])).append(OP_EQUALS); + sql.append(_factory.quoteName(join._rightTable + + JDBCSyntax.TABLE_COLUMN_SEPARATOR + + join._rightColumns[j])); } - sql.append(_factory.quoteName(join._rightTable - + JDBCSyntax.TABLE_COLUMN_SEPARATOR - + join._rightColumns[j])); } } first = addWhereClause(sql, first); @@ -136,12 +224,11 @@ sql.append(JDBCSyntax.ORDER_BY).append(_order); } + // There is no standard way to lock selected tables. return sql.toString(); } public boolean isLimitClauseSupported() { - return true; + return true; } -} - - +} \ No newline at end of file Index: cpactf/src/test/java/org/castor/cpa/test/test1379/Test1379.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test1379/Test1379.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test1379/Test1379.java (working copy) @@ -19,15 +19,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - // SQL_SERVER is excluded until issue CASTOR-2221 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } - public void test() throws Exception { Computer computer = null; Product product; Index: cpactf/src/test/java/org/castor/cpa/test/test30/TestOQLCondition.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test30/TestOQLCondition.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test30/TestOQLCondition.java (working copy) @@ -51,14 +51,9 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - - // SQL_SERVER is excluded until issue CASTOR-2634 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } public void setUp() throws Exception { _db = getJDOManager(DBNAME, MAPPING).getDatabase(); Index: cpactf/src/test/java/org/castor/cpa/test/test972/Test972.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test972/Test972.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test972/Test972.java (working copy) @@ -36,15 +36,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - // SQL_SERVER is excluded until issue CASTOR-2221 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } - public void testLongTransaction() throws Exception { Product product = null; ProductGroup group; Index: cpactf/src/test/java/org/castor/cpa/test/test1196/TestLongTransaction.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test1196/TestLongTransaction.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test1196/TestLongTransaction.java (working copy) @@ -45,15 +45,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - // SQL_SERVER is excluded until issue CASTOR-2634 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } - public void deleteUnidirectional() throws Exception { JDOManager jdo = getJDOManager(DBNAME, MAPPING); Index: cpactf/src/test/java/org/castor/cpa/test/test31/TestPersistenceWithExtends.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test31/TestPersistenceWithExtends.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test31/TestPersistenceWithExtends.java (working copy) @@ -48,15 +48,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - // SQL_SERVER is excluded until issue CASTOR-2634 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } - public void setUp() throws Exception { _db = getJDOManager(DBNAME, MAPPING).getDatabase(); Index: cpactf/src/test/java/org/castor/cpa/test/test2527/Test2527.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test2527/Test2527.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test2527/Test2527.java (working copy) @@ -34,15 +34,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.DERBY); } - // SQL_SERVER is excluded until issue CASTOR-2634 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } - public void test() throws Exception { Database db = getJDOManager(DBNAME, MAPPING).getDatabase(); Index: cpactf/src/test/java/org/castor/cpa/test/test87/TestExtended.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test87/TestExtended.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test87/TestExtended.java (working copy) @@ -52,15 +52,10 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) - || (engine == DatabaseEngineType.ORACLE); + || (engine == DatabaseEngineType.ORACLE) + || (engine == DatabaseEngineType.SQL_SERVER); // || (engine == DatabaseEngineType.DERBY); } - - // SQL_SERVER is excluded until issue CASTOR-2634 is resolved - - public boolean exclude(final DatabaseEngineType engine) { - return (engine == DatabaseEngineType.SQL_SERVER); - } public void delete() throws Exception { JDOManager jdo = getJDOManager(DBNAME, MAPPING); Index: cpactf/src/test/java/org/castor/cpa/test/test1073/TestDuration.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test1073/TestDuration.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test1073/TestDuration.java (working copy) @@ -36,6 +36,7 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.DERBY) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.ORACLE); } Index: cpactf/src/test/java/org/castor/cpa/test/test1073/TestTime.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test1073/TestTime.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test1073/TestTime.java (working copy) @@ -36,6 +36,7 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.DERBY) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.ORACLE); } Index: cpactf/src/test/java/org/castor/cpa/test/test1217/TestTimestampChange.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test1217/TestTimestampChange.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test1217/TestTimestampChange.java (working copy) @@ -37,6 +37,7 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.DERBY) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.ORACLE); } Index: cpactf/src/test/java/org/castor/cpa/test/test241/TestObjectNotFound.java =================================================================== --- cpactf/src/test/java/org/castor/cpa/test/test241/TestObjectNotFound.java (revision 8135) +++ cpactf/src/test/java/org/castor/cpa/test/test241/TestObjectNotFound.java (working copy) @@ -34,6 +34,7 @@ public boolean include(final DatabaseEngineType engine) { return (engine == DatabaseEngineType.MYSQL) || (engine == DatabaseEngineType.DERBY) + || (engine == DatabaseEngineType.SQL_SERVER) || (engine == DatabaseEngineType.ORACLE); } Index: cpactf/src/test/ddl/org/castor/cpa/test/test1073/mssql.sql =================================================================== --- cpactf/src/test/ddl/org/castor/cpa/test/test1073/mssql.sql (revision 0) +++ cpactf/src/test/ddl/org/castor/cpa/test/test1073/mssql.sql (revision 0) @@ -0,0 +1,17 @@ +drop table test1073_duration; + +create table test1073_duration ( + id int not null, + long_duration bigint, + string_duration varchar(100) +); + +drop table test1073_time; + +create table test1073_time ( + id int not null, + long_time_local bigint, + long_time_utc bigint, + string_time_local varchar(100), + string_time_utc varchar(100) +); Index: cpactf/src/test/ddl/org/castor/cpa/test/test1217/mssql.sql =================================================================== --- cpactf/src/test/ddl/org/castor/cpa/test/test1217/mssql.sql (revision 0) +++ cpactf/src/test/ddl/org/castor/cpa/test/test1217/mssql.sql (revision 0) @@ -0,0 +1,43 @@ +DROP TABLE TEST1217_PRODUCT; +DROP TABLE TEST1217_PERSON; +DROP TABLE TEST1217_EXTENDED; +DROP TABLE TEST1217_BASE; + +CREATE TABLE TEST1217_PERSON ( + OID VARCHAR(8) NOT NULL, + NAME VARCHAR(60) NOT NULL, + PRIMARY KEY (OID) +); + +CREATE TABLE TEST1217_BASE ( + OID VARCHAR(8) NOT NULL, + NAME VARCHAR(60) NOT NULL, + PRIMARY KEY (OID) +); + +CREATE TABLE TEST1217_EXTENDED ( + OID VARCHAR(8) NOT NULL, + NUM VARCHAR(20) NOT NULL, + PRIMARY KEY (OID) +); + +ALTER TABLE TEST1217_EXTENDED +ADD FOREIGN KEY (OID) +REFERENCES TEST1217_BASE (OID); + +CREATE TABLE TEST1217_PRODUCT ( + OID VARCHAR(8) NOT NULL, + CODE VARCHAR(12) NOT NULL, + VALUE NUMERIC(12,2) NOT NULL, + COMPANY VARCHAR(8) NOT NULL, + PART VARCHAR(8) NOT NULL, + PRIMARY KEY (OID) +); + +ALTER TABLE TEST1217_PRODUCT +ADD FOREIGN KEY (COMPANY) +REFERENCES TEST1217_PERSON (OID); + +ALTER TABLE TEST1217_PRODUCT +ADD FOREIGN KEY (PART) +REFERENCES TEST1217_BASE (OID);