Re: EOF & pgSQL join syntax
Re: EOF & pgSQL join syntax
- Subject: Re: EOF & pgSQL join syntax
- From: Giorgio Valoti <email@hidden>
- Date: Sat, 1 May 2004 13:53:48 +0200
On 01/mag/04, at 08:00, Arturo Pirez wrote:
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 :-)
[I'm resending the message because it was rejected by the list server
(too long). Sorry for the inconvenience.]
Well done, Arturo!
I was working on the same issue and I've come up with a different patch
that just overrides assembleSelectStatementWithAttributes.
I don't know which one is to be preferred, though... So here it is:
public String assembleSelectStatementWithAttributes(NSArray
attributes, boolean lock, EOQualifier q, NSArray fetchOrder,
String
selectString, String columnList, String tableList,
String
whereClause, String joinClause, String orderByClause,
String
lockClause ) {
try {
NSMutableArray tables = sortTablesStatement( tableList );
NSArray joins = NSArray.componentsSeparatedByString
( joinClause, " AND "
).sortedArrayUsingComparator(NSComparator.AscendingCaseInsensitiveString
Comparator);
int tablesCount = tables.count();
for( int i = 1; i < tablesCount; i++ ) {
String currentJoin = (String) joins.objectAtIndex( i-1
);
if( currentJoin.matches( ".+ [*]{1,1}=[*]{1,1} .+" ) ) {
tables.replaceObjectAtIndex
( "FULL OUTER JOIN "+ tables.objectAtIndex( i ) +"
ON "+ currentJoin.replaceAll("[*]", ""), i );
} else if( currentJoin.matches( ".+ [*]{1,1}= .+" ) ) {
tables.replaceObjectAtIndex
( "LEFT OUTER JOIN "+ tables.objectAtIndex( i ) +"
ON "+ currentJoin.replaceAll("[*]", ""), i );
} else if( currentJoin.matches( ".+ =[*]{1,1} .+" ) ) {
tables.replaceObjectAtIndex
( "RIGHT OUTER JOIN "+ tables.objectAtIndex( i ) +"
ON "+ currentJoin.replaceAll("[*]", ""), i );
} else {
tables.replaceObjectAtIndex( "INNER JOIN "+
tables.objectAtIndex( i ) +" ON "+ currentJoin, i );
}
}
StringBuffer sb = new StringBuffer();
sb.append( selectString +" "+ columnList +" FROM "+
tables.componentsJoinedByString( " " ) );
if( whereClause != null && whereClause != "" )
sb.append( " WHERE "+ whereClause );
if( orderByClause != null && orderByClause != "" )
sb.append( " ORDER BY "+ orderByClause );
if( lockClause != null && lockClause != "")
sb.append( " "+ lockClause );
return sb.toString();
} catch( NSComparator.ComparisonException e ) {
throw new NSForwardException( e );
}
}
//....
private NSMutableArray sortTablesStatement(String tableList) throws
NSComparator.ComparisonException {
NSArray tokens = NSArray.componentsSeparatedByString(
tableList, ", " );
int count = tokens.count();
NSMutableDictionary tmpDict = new NSMutableDictionary(count);
NSMutableArray result = new NSMutableArray(count);
for( int i = 0; i < count; i++ ) {
String currentToken = (String) tokens.objectAtIndex( i );
tmpDict.setObjectForKey( currentToken,
currentToken.substring(currentToken.length()-2) );
}
NSArray sortedKeys =
tmpDict.allKeys().sortedArrayUsingComparator(NSComparator.AscendingCaseI
nsensitiveStringComparator);
for( int i = 0; i < count; i++ ) {
result.addObject( tmpDict.objectForKey(
sortedKeys.objectAtIndex(i) ) );
}
return result;
}
--
Giorgio Valoti
--------------
_______________________________________________
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.