Re: EOF & pgSQL join syntax
Re: EOF & pgSQL join syntax
- Subject: Re: EOF & pgSQL join syntax
- From: Arturo Pérez <email@hidden>
- Date: Sat, 1 May 2004 02:00:49 -0400
On Apr 30, 2004, at 1:22 AM, Anjo Krank wrote:
> Folks,
>
> I recently added "PlugIn" section on Project Wonder (current HEAD
> CVS), where there is currently a PG plugin you can check out and
> contribute to again. The plugin supports nifty stuff like the "*~"
> regex operator and fixes a bug when generating NSData selects.
>
> That being said, the problem is that EOF constructs the statement like:
>
> select ... from a, b where a.x <some operator> b.y and ...
>
> whereas Postgres would like
>
> select ... from a <left|right> <inner|outer> join b on (a.x=b.y)
> where ...
>
Hi all,
Since what I was supposed to be working on isn't and I took this as a
challenge to my manhood I hereby present you with a SQL92 compliant
join syntax plugin for pgSQL. I didn't expect it to be this much
trouble but sometimes I feel a certain je ne se quoi that makes me
spend way too much time on this sort of thing. I also reenabled
Giorgio's fix in this copy. The code isn't optimal but since my join
fu is not what it should be I didn't want to go too much further.
Needless to say, I haven't extensively tested this. However, my
applications work with it and even do that left outer join I originally
wanted so many hours ago :-)
Anjo, please let me know if you need further information.
/*
PostgresqlPlugin v1.2
Copyright (C) 2001 Kenny Leung
2004 A. Perez added support for proper join syntax.
This bundle is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License version 2.1 as published by the Free Software Foundation.
This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package com.webobjects.jdbcadaptor;
import com.webobjects.eoaccess.*;
import com.webobjects.jdbcadaptor.*;
import com.webobjects.foundation.NSArray;
import com.webobjects.foundation.NSMutableArray;
import com.webobjects.foundation.NSMutableDictionary;
public class PostgresqlExpression extends JDBCExpression {
static private final char SQL_ESCAPE_CHAR = '|';
private NSMutableArray alreadyJoined = new NSMutableArray();
private class JoinClause {
String table1;
String op;
String table2;
String joinCondition;
}
public PostgresqlExpression(EOEntity entity) {
super(entity);
}
public char sqlEscapeChar() {
return SQL_ESCAPE_CHAR;
}
public String assembleSelectStatementWithAttributes(NSArray attributes,
boolean lock,
com.webobjects.eocontrol.EOQualifier qualifier,
NSArray fetchOrder,
String selectString,
String columnList,
String tableList,
String whereClause,
String joinClause,
String orderByClause,
String lockClause) {
StringBuffer sb = new StringBuffer();
sb.append("SELECT ");
sb.append(columnList);
sb.append(" FROM ");
if (alreadyJoined.count() > 0) {
sb.append(" ");
sb.append(joinClauseString());
} else
sb.append(tableList);
if (whereClause != null && whereClause.length() > 0) {
sb.append(" WHERE ");
sb.append(whereClause);
}
if (orderByClause != null && orderByClause.length() > 0) {
sb.append(" ORDER BY ");
sb.append(orderByClause);
}
if (lockClause != null && lockClause.length() > 0) {
sb.append(" ");
sb.append(lockClause);
}
return sb.toString();
}
public String sqlStringForValue(Object v, String kp) {
EOAttribute attribute = entity().attributeNamed(kp);
String suffix = "";
if(attribute != null) {
//ENHANCEME ak: we should hande key paths
return super.sqlStringForValue(v,kp) + "::" + columnTypeStringForAttribute(attribute);
}
return super.sqlStringForValue(v,kp);
}
public String joinClauseString() {
NSMutableDictionary seenIt = new NSMutableDictionary();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < alreadyJoined.count(); i++) {
JoinClause jc = (JoinClause)alreadyJoined.objectAtIndex(i);
if (seenIt.objectForKey(jc.table1) == null &&
seenIt.objectForKey(jc.table2) == null) {
sb.append(jc.table1);
sb.append(jc.op);
sb.append(jc.table2);
} else if (seenIt.objectForKey(jc.table1) == null) {
sb.append(jc.op);
sb.append(jc.table1);
} else {
sb.append(jc.op);
sb.append(jc.table2);
}
sb.append(jc.joinCondition);
sb.append(" ");
seenIt.setObjectForKey(jc.table1, jc.table1);
seenIt.setObjectForKey(jc.table2, jc.table2);
}
return sb.toString();
}
public void addJoinClause(String leftName,
String rightName,
int semantic) {
assembleJoinClause(leftName, rightName, semantic);
}
private EOEntity entityForKeyPath(String keyPath) {
NSArray keys = NSArray.componentsSeparatedByString(keyPath, ".");
EOEntity ent = entity();
for (int i = 0; i < keys.count(); i++) {
String k = (String)keys.objectAtIndex(i);
EORelationship rel = ent.anyRelationshipNamed(k);
if (rel == null) {
throw new IllegalArgumentException("relationship " + k + " relationship was null");
}
ent = rel.destinationEntity();
}
return ent;
}
public String assembleJoinClause(String leftName,
String rightName,
int semantic) {
StringBuffer sb = new StringBuffer();
String leftAlias = leftName.substring(0, leftName.indexOf("."));
String rightAlias = rightName.substring(0, rightName.indexOf("."));
String leftCol = leftName.substring(leftName.indexOf(".") + 1);
String rightCol = rightName.substring(rightName.indexOf(".") + 1);
NSArray k;
EOEntity rightEntity;
EOEntity leftEntity;
if (leftAlias.equals("t0")) {
leftEntity = entity();
} else {
k = aliasesByRelationshipPath().allKeysForObject(leftAlias);
String leftKey = k.count()>0? (String)k.lastObject() : "";
leftEntity = entityForKeyPath(leftKey);
}
if (rightAlias.equals("t0")) {
rightEntity = entity();
} else {
k = aliasesByRelationshipPath().allKeysForObject(rightAlias);
String rightKey = k.count()>0? (String)k.lastObject() : "";
rightEntity = entityForKeyPath(rightKey);
}
String rightTable = rightEntity.externalName();
String leftTable = leftEntity.externalName();
JoinClause jc = new JoinClause();
sb.append(leftTable);
sb.append(" ");
sb.append(leftAlias);
jc.table1 = sb.toString();
switch (semantic) {
case EORelationship.LeftOuterJoin:
jc.op = " LEFT OUTER JOIN ";
break;
case EORelationship.RightOuterJoin:
jc.op = " RIGHT OUTER JOIN ";
break;
case EORelationship.FullOuterJoin:
jc.op = " FULL OUTER JOIN ";
break;
case EORelationship.InnerJoin:
jc.op = " INNER JOIN ";
break;
}
sb.append(jc.op);
sb.append(rightTable);
sb.append(" ");
sb.append(rightAlias);
jc.table2 = rightTable + " " + rightAlias;
if (leftCol.equals(rightCol)) {
sb.append(" USING (");
sb.append(leftCol);
sb.append(")");
jc.joinCondition = " USING (" + leftCol + ")";
} else {
sb.append(" ON ");
sb.append(leftName);
sb.append(" = ");
sb.append(rightName);
jc.joinCondition = " ON " + leftName + " = " + rightName;
}
alreadyJoined.insertObjectAtIndex(jc, 0);
return sb.toString();
}
}
----
WO in philadelphia - wanna cheesesteak with that?
Please visit webobjects.meetup.com.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.