Ciao!
In attach there is a patch to support Left/Right/Full outer joins for
both SQL92 and pre standard (EOF style).
For SQL92 it builds the join constructor in the from clause, for pre
standard it uses the classic where classic with =, *= and =* operators.
I've tested it with postgres 7.3 with only _one_ join and it works. The
same should be true for db2 (I'll test next week), oracle 9i and firebird.
Problems arises with non standard implementation (ie Oracle pre 9).
The SQL92 implementation of this patch fails with complex qualifiers (ie
if you have more than one relationship of the same entity inside the
join clause).
The patch is against v1.0.2 (no modeler patch)
Any comments are welcome.
Ciao
Mirko
diff -c ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java Sat Nov 1 10:26:03 2003
***************
*** 176,200 ****
// append from clause
queryBuf.append(" FROM ");
! // append table list (unroll loop's 1st element)
! int tableCount = tableList.size();
! appendTable(queryBuf, 0); // assume there is at least 1 table
! for (int i = 1; i < tableCount; i++) {
! queryBuf.append(", ");
! appendTable(queryBuf, i);
}
! // append db relationship joins if any
! boolean hasWhere = false;
! int dbRelCount = dbRelList.size();
! if (dbRelCount > 0) {
! hasWhere = true;
! queryBuf.append(" WHERE ");
!
! appendDbRelJoins(queryBuf, 0);
! for (int i = 1; i < dbRelCount; i++) {
! queryBuf.append(" AND ");
! appendDbRelJoins(queryBuf, i);
}
}
--- 176,219 ----
// append from clause
queryBuf.append(" FROM ");
! boolean hasWhere = false;
!
! if (adapter.supportsSQL92()) {
! // append table list (unroll loop's 1st element)
! int tableCount = tableList.size();
! appendTable(queryBuf, 0); // assume there is at least 1 table
!
! // append db relationship joins if any
! int dbRelCount = dbRelList.size();
!
! if (dbRelCount > 0) {
! appendDbRelJoins(queryBuf, 0, true);
!
! for (int i = 1; i < dbRelCount; i++) {
! appendDbRelJoins(queryBuf, i, true);
! }
! }
}
+ else {
+ // append table list (unroll loop's 1st element)
+ int tableCount = tableList.size();
+ appendTable(queryBuf, 0); // assume there is at least 1 table
+ for (int i = 1; i < tableCount; i++) {
+ queryBuf.append(", ");
+ appendTable(queryBuf, i);
+ }
! // append db relationship joins if any
! int dbRelCount = dbRelList.size();
! if (dbRelCount > 0) {
! hasWhere = true;
! queryBuf.append(" WHERE ");
!
! appendDbRelJoins(queryBuf, 0, false);
! for (int i = 1; i < dbRelCount; i++) {
! queryBuf.append(" AND ");
! appendDbRelJoins(queryBuf, i, false);
! }
}
}
***************
*** 395,426 ****
queryBuf.append(' ').append((String) aliasList.get(index));
}
! private void appendDbRelJoins(StringBuffer queryBuf, int index) {
DbRelationship rel = (DbRelationship) dbRelList.get(index);
String srcAlias = aliasForTable((DbEntity) rel.getSourceEntity());
String targetAlias = (String) aliasLookup.get(rel);
boolean andFlag = false;
! List joins = rel.getJoins();
! int len = joins.size();
! for (int i = 0; i < len; i++) {
! DbAttributePair join = (DbAttributePair) joins.get(i);
! if (andFlag) {
! queryBuf.append(" AND ");
! } else {
! andFlag = true;
}
! queryBuf
! .append(srcAlias)
! .append('.')
! .append(join.getSource().getName())
! .append(" = ")
! .append(targetAlias)
! .append('.')
! .append(join.getTarget().getName());
}
}
--- 414,494 ----
queryBuf.append(' ').append((String) aliasList.get(index));
}
! private void appendDbRelJoins(StringBuffer queryBuf, int index, boolean sql92) {
DbRelationship rel = (DbRelationship) dbRelList.get(index);
String srcAlias = aliasForTable((DbEntity) rel.getSourceEntity());
String targetAlias = (String) aliasLookup.get(rel);
boolean andFlag = false;
+ int semantic = rel.getJoinSemantic();
+ String joinType = null, op = null;
! if (sql92 == true) {
! op = " = ";
! switch (semantic) {
! default:
! case DbRelationship.INNER_JOIN:
! joinType = " INNER JOIN ";
! break;
! case DbRelationship.LEFT_OUTER_JOIN:
! joinType = " LEFT OUTER JOIN ";
! break;
! case DbRelationship.RIGHT_OUTER_JOIN:
! joinType = " RIGHT OUTER JOIN ";
! break;
! case DbRelationship.FULL_OUTER_JOIN:
! joinType = " FULL OUTER JOIN ";
! break;
! }
! }
! else {
! switch (semantic) {
! default:
! case DbRelationship.INNER_JOIN:
! op = " = ";
! break;
! case DbRelationship.LEFT_OUTER_JOIN:
! op = " *= ";
! break;
! case DbRelationship.RIGHT_OUTER_JOIN:
! op = " =* ";
! break;
! case DbRelationship.FULL_OUTER_JOIN:
! break;
}
+ }
+
+ if (op != null) {
+ List joins = rel.getJoins();
+ int len = joins.size();
+ for (int i = 0; i < len; i++) {
+ DbAttributePair join = (DbAttributePair) joins.get(i);
! if (andFlag) {
! queryBuf.append(" AND ");
! } else {
! if (sql92) {
! queryBuf
! .append(joinType)
! .append(((DbEntity) rel.getTargetEntity()).getFullyQualifiedName())
! .append(" ")
! .append(aliasForTable((DbEntity) rel.getTargetEntity()))
! .append(" ON ");
! }
!
! andFlag = true;
! }
!
! queryBuf
! .append(srcAlias)
! .append('.')
! .append(join.getSource().getName())
! .append(op)
! .append(targetAlias)
! .append('.')
! .append(join.getTarget().getName());
! }
}
}
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/access/trans/SelectTranslator.java Sat Nov 1 10:26:03 2003
***************
*** 176,200 ****
// append from clause
queryBuf.append(" FROM ");
! // append table list (unroll loop's 1st element)
! int tableCount = tableList.size();
! appendTable(queryBuf, 0); // assume there is at least 1 table
! for (int i = 1; i < tableCount; i++) {
! queryBuf.append(", ");
! appendTable(queryBuf, i);
}
! // append db relationship joins if any
! boolean hasWhere = false;
! int dbRelCount = dbRelList.size();
! if (dbRelCount > 0) {
! hasWhere = true;
! queryBuf.append(" WHERE ");
!
! appendDbRelJoins(queryBuf, 0);
! for (int i = 1; i < dbRelCount; i++) {
! queryBuf.append(" AND ");
! appendDbRelJoins(queryBuf, i);
}
}
--- 176,219 ----
// append from clause
queryBuf.append(" FROM ");
! boolean hasWhere = false;
!
! if (adapter.supportsSQL92()) {
! // append table list (unroll loop's 1st element)
! int tableCount = tableList.size();
! appendTable(queryBuf, 0); // assume there is at least 1 table
!
! // append db relationship joins if any
! int dbRelCount = dbRelList.size();
!
! if (dbRelCount > 0) {
! appendDbRelJoins(queryBuf, 0, true);
!
! for (int i = 1; i < dbRelCount; i++) {
! appendDbRelJoins(queryBuf, i, true);
! }
! }
}
+ else {
+ // append table list (unroll loop's 1st element)
+ int tableCount = tableList.size();
+ appendTable(queryBuf, 0); // assume there is at least 1 table
+ for (int i = 1; i < tableCount; i++) {
+ queryBuf.append(", ");
+ appendTable(queryBuf, i);
+ }
! // append db relationship joins if any
! int dbRelCount = dbRelList.size();
! if (dbRelCount > 0) {
! hasWhere = true;
! queryBuf.append(" WHERE ");
!
! appendDbRelJoins(queryBuf, 0, false);
! for (int i = 1; i < dbRelCount; i++) {
! queryBuf.append(" AND ");
! appendDbRelJoins(queryBuf, i, false);
! }
}
}
***************
*** 395,426 ****
queryBuf.append(' ').append((String) aliasList.get(index));
}
! private void appendDbRelJoins(StringBuffer queryBuf, int index) {
DbRelationship rel = (DbRelationship) dbRelList.get(index);
String srcAlias = aliasForTable((DbEntity) rel.getSourceEntity());
String targetAlias = (String) aliasLookup.get(rel);
boolean andFlag = false;
! List joins = rel.getJoins();
! int len = joins.size();
! for (int i = 0; i < len; i++) {
! DbAttributePair join = (DbAttributePair) joins.get(i);
! if (andFlag) {
! queryBuf.append(" AND ");
! } else {
! andFlag = true;
}
! queryBuf
! .append(srcAlias)
! .append('.')
! .append(join.getSource().getName())
! .append(" = ")
! .append(targetAlias)
! .append('.')
! .append(join.getTarget().getName());
}
}
--- 414,494 ----
queryBuf.append(' ').append((String) aliasList.get(index));
}
! private void appendDbRelJoins(StringBuffer queryBuf, int index, boolean sql92) {
DbRelationship rel = (DbRelationship) dbRelList.get(index);
String srcAlias = aliasForTable((DbEntity) rel.getSourceEntity());
String targetAlias = (String) aliasLookup.get(rel);
boolean andFlag = false;
+ int semantic = rel.getJoinSemantic();
+ String joinType = null, op = null;
! if (sql92 == true) {
! op = " = ";
! switch (semantic) {
! default:
! case DbRelationship.INNER_JOIN:
! joinType = " INNER JOIN ";
! break;
! case DbRelationship.LEFT_OUTER_JOIN:
! joinType = " LEFT OUTER JOIN ";
! break;
! case DbRelationship.RIGHT_OUTER_JOIN:
! joinType = " RIGHT OUTER JOIN ";
! break;
! case DbRelationship.FULL_OUTER_JOIN:
! joinType = " FULL OUTER JOIN ";
! break;
! }
! }
! else {
! switch (semantic) {
! default:
! case DbRelationship.INNER_JOIN:
! op = " = ";
! break;
! case DbRelationship.LEFT_OUTER_JOIN:
! op = " *= ";
! break;
! case DbRelationship.RIGHT_OUTER_JOIN:
! op = " =* ";
! break;
! case DbRelationship.FULL_OUTER_JOIN:
! break;
}
+ }
+
+ if (op != null) {
+ List joins = rel.getJoins();
+ int len = joins.size();
+ for (int i = 0; i < len; i++) {
+ DbAttributePair join = (DbAttributePair) joins.get(i);
! if (andFlag) {
! queryBuf.append(" AND ");
! } else {
! if (sql92) {
! queryBuf
! .append(joinType)
! .append(((DbEntity) rel.getTargetEntity()).getFullyQualifiedName())
! .append(" ")
! .append(aliasForTable((DbEntity) rel.getTargetEntity()))
! .append(" ON ");
! }
!
! andFlag = true;
! }
!
! queryBuf
! .append(srcAlias)
! .append('.')
! .append(join.getSource().getName())
! .append(op)
! .append(targetAlias)
! .append('.')
! .append(join.getTarget().getName());
! }
}
}
diff -c ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/dba/DbAdapter.java cayenne/org/objectstyle/cayenne/dba/DbAdapter.java
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/dba/DbAdapter.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/dba/DbAdapter.java Sat Nov 1 10:24:26 2003
***************
*** 134,140 ****
*/
public boolean supportsBatchUpdates();
! /**
* Returns a SQL string that can be used to drop
* a database table corresponding to <code>ent</code>
* parameter.
--- 134,146 ----
*/
public boolean supportsBatchUpdates();
! /**
! * Returns <code>true</code> if the target database supports
! * SQL92 for ie join notation.
! */
! public boolean supportsSQL92();
!
! /**
* Returns a SQL string that can be used to drop
* a database table corresponding to <code>ent</code>
* parameter.
diff -c ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/dba/JdbcAdapter.java cayenne/org/objectstyle/cayenne/dba/JdbcAdapter.java
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/dba/JdbcAdapter.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/dba/JdbcAdapter.java Sat Nov 1 10:24:59 2003
***************
*** 214,219 ****
--- 214,224 ----
return true;
}
+ /** Returns true. */
+ public boolean supportsSQL92() {
+ return true;
+ }
+
/**
* Returns a SQL string to drop a table corresponding
* to <code>ent</code> DbEntity.
diff -c ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/map/DbRelationship.java cayenne/org/objectstyle/cayenne/map/DbRelationship.java
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/map/DbRelationship.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/map/DbRelationship.java Sat Nov 1 10:28:06 2003
***************
*** 78,87 ****
public static final EventSubject PROPERTY_DID_CHANGE =
EventSubject.getSubject(DbRelationship.class, "PropertyDidChange");
! // The columns through which the join is implemented.
protected List joins = new ArrayList();
!
! // Is relationship from source to target points to dependent primary
// key (primary key column of destination table that is also a FK to the source column)
protected boolean toDependentPK;
--- 78,93 ----
public static final EventSubject PROPERTY_DID_CHANGE =
EventSubject.getSubject(DbRelationship.class, "PropertyDidChange");
! public static final int INNER_JOIN = 0;
! public static final int FULL_OUTER_JOIN = 1;
! public static final int LEFT_OUTER_JOIN = 2;
! public static final int RIGHT_OUTER_JOIN = 3;
!
! // The columns through which the join is implemented.
protected List joins = new ArrayList();
! protected int joinSemantic;
!
! // Is relationship from source to target points to dependent primary
// key (primary key column of destination table that is also a FK to the source column)
protected boolean toDependentPK;
***************
*** 210,216 ****
}
}
! /** Creates a snapshot of primary key attributes of a target
* object of this relationship based on a snapshot of a source.
* Only "to-one" relationships are supported.
* Returns null if relationship does not point to an object.
--- 216,263 ----
}
}
! public int getJoinSemantic() {
! return joinSemantic;
! }
!
! public void setJoinSemantic(int joinSemantic) {
! this.joinSemantic = joinSemantic;
! }
!
! public String getJoinSemanticName() {
! switch (joinSemantic) {
! case INNER_JOIN:
! return "InnerJoin";
! case FULL_OUTER_JOIN:
! return "FullOuterJoin";
! case LEFT_OUTER_JOIN:
! return "LeftOuterJoin";
! case RIGHT_OUTER_JOIN:
! return "RightOuterJoin";
! }
!
! return null;
! }
!
! public void setJoinSemanticName(String joinSemantic) {
! if (joinSemantic == null || joinSemantic.equalsIgnoreCase("InnerJoin")) {
! this.joinSemantic = INNER_JOIN;
! }
! else if (joinSemantic.equalsIgnoreCase("LeftOuterJoin")) {
! this.joinSemantic = LEFT_OUTER_JOIN;
! }
! else if (joinSemantic.equalsIgnoreCase("RightOuterJoin")) {
! this.joinSemantic = RIGHT_OUTER_JOIN;
! }
! else if (joinSemantic.equalsIgnoreCase("FullOuterJoin")) {
! this.joinSemantic = FULL_OUTER_JOIN;
! }
! else {
! this.joinSemantic = INNER_JOIN;
! }
! }
!
! /** Creates a snapshot of primary key attributes of a target
* object of this relationship based on a snapshot of a source.
* Only "to-one" relationships are supported.
* Returns null if relationship does not point to an object.
diff -c ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/map/MapLoader.java cayenne/org/objectstyle/cayenne/map/MapLoader.java
*** ../../cayenne-1.0.2/src/cayenne/org/objectstyle/cayenne/map/MapLoader.java Mon Oct 27 22:38:41 2003
--- cayenne/org/objectstyle/cayenne/map/MapLoader.java Fri Oct 31 23:14:01 2003
***************
*** 833,838 ****
--- 833,840 ----
out.print(temp.isToDependentPK() ? TRUE : FALSE);
out.print("\" toMany=\"");
out.print(temp.isToMany() ? TRUE : FALSE);
+ out.print("\" joinSemantic=\"");
+ out.print(temp.getJoinSemanticName());
out.println("\">");
this.storeDbAttributePair(out, temp);
out.print("\t</");
***************
*** 1040,1045 ****
--- 1042,1049 ----
temp = atts.getValue("", "toDependentPK");
boolean toDependentPK = temp != null && temp.equalsIgnoreCase(TRUE);
+ String semantic = atts.getValue("", "joinSemantic");
+
String name = atts.getValue("", "name");
if (null == name) {
throw new SAXException(
***************
*** 1053,1058 ****
--- 1057,1063 ----
dbRelationship.setTargetEntity(target);
dbRelationship.setToMany(toMany);
dbRelationship.setName(name);
+ dbRelationship.setJoinSemanticName(semantic);
dbRelationship.setToDependentPK(toDependentPK);
// Save the reference to this db relationship for later resolution
// in the ObjRelationship
This archive was generated by hypermail 2.0.0 : Sat Nov 01 2003 - 05:29:23 EST