Hi Clemens, Thanks for your contribution to the project. The best way to handle the contribution is to open up a bugzilla report at http://bugzilla.exolab.org. This way it can be tracked easier and the appropriate individuals will be assigned to the report.
Also read the simple guidelines for contribution: http://castor.exolab.org/cvs.html#Guidelines-For-Code-Contribution The primary guideline is to make sure you are the copyright holder of the code you are contributing or that you have written permission from the copyright holder to contribute the code. Thanks again, --Keith Clemens Kadura wrote: > > Hello, > > last week I raised an issue regarding finding objects not only by their simple > fields but also by related objects. > e.g. "SELECT a FROM jdo.address WHERE a.person=$1 and a.zip=$2" > and there is a many-to-one-relation between adress and person. > > Bruce Snyder replied that it is only planned to investigate in this in the future > but no activities currently. > > I walked through the source code a little bit (thanks Open Source) and found that it > not that big deal to implement this function. > > There are mainly 2 point to be changed: > 1. in OQLQueryImpl.bind(...) we need to bind the identifier values of the objects > instead of the objects themselves. > > 2. for multiple PKs we need to extend the SQL query so that it asks for all foreign > keys. > e.g. "... WHERE address.personFk1=$ AND address.personFk2=$ and address.zip=$" > > I prepared a short change description, 2 test cases (one for singlePKs and one for > multiple) and the changed classes. > > If you like, you can integrate this in your next version of castor. > > (Is it the official way to deliver patches or is there a better channel in case I'd > have another one) > > Regards Clemens > > ______________________________________________________________________________ > Horoskop, Comics, VIPs, Wetter, Sport und Lotto im WEB.DE Screensaver1.2 > Kostenlos downloaden: http://screensaver.web.de/?mc=021110 > > ------------------------------------------------------------------------ > Name: findByRelatedObject.zip > findByRelatedObject.zip Type: Zip Compressed Data > (application/x-zip-compressed) > Encoding: base64 > > ------------------------------------------------------------------------ > Patch description for "Find by related Object" > ============================================== > Author: Clemens Kadura > e-Mail: [EMAIL PROTECTED] > > patch is based on Castor release 0.9.5.2 > > 0. Content > ========== > I. Problem description > II. Considerations > III. necessary changes > 1. create a suitable SQL expression > 2. change the binding process > IV. Source Code of Changes > V. other optional changes > > I. Problem description > ======================= > It is not possible to handle OQL query statement that ask for a specific > referenced object. > > Assume the followinmg case: > There is a Person that has Addresses (one-to-many relation, lazy loaded). > Persons have more than 1000 addresses. > For performance reasons it seems worth not to load all addresses, if you only > need those for a specific zip code. > > One approach would be to run a query: > ... > aPerson = _db.load( TestLazyPerson.class, complexPK ); > query = _db.getOQLQuery( > "SELECT a FROM jdo.TestLazyAddress a WHERE Person = $1 AND zip = $2"); > query.bind( aPerson ); > query.bind( "10000" ); > > You see: $1 should be bound with a person object or at least with the identity > of a person object. > > In the current release you get an error message, especially when the person's > identity is a multi-key one. > > II. Considerations > =================== > For such a multi-key case, the SQL query statement should look like: > > SELECT id,street,city,state,zip,fname,lname > FROM test_pks_address > WHERE (test_pks_address.fname = ? AND test_pks_address.lname = ? > AND test_pks_address.zip = ?) > > You can see that for each identity field of the related object > an entry in the WHERE clause is necessary ! > > Today there are 2 problems: > - the ParseTreeWalker only maps one identifier to one entry in the where clause > - it is not possible to bind Complex keys to object parameters > > III. necessary changes > ====================== > 1. create a suitable SQL expression > =================================== > > org.exolab.castor.jdo.oql.ParseTreeWalker > ----------------------------------------- > The best place to create a WHERE clause entry for each identifier field > is directly during the creation of the SQL expression > > The suggested solution is to replace in the parse tree > the corresponding EQUAL token > EQUAL > IDENTIFIER or DOT > DOLLAR > > with > > AND > EQUAL > IDENTIFIER > DOLLAR > EQUAL > IDENTIFIER > DOLLAR > > During createQueryExpression() we will test > for each EQUAL token, if the included field has multiple SQLNames. > In this case we change the parse tree as described above. > > In the further processing of this adapted parse tree the correct SQL expression > will be created (as in the current version). > Doing this also in the ParamMap the correct links between OQL parameters > and SQL parameters will be set (without any code change). > > --- > The following dependent changes are necessary to gain this: > > org.exolab.castor.jdo.oql.TokenTypes > ------------------------------------ > Implement a SIMPLE_EQUAL token type to mark EQUAL tokens that were already > processed. > > org.exolab.castor.jdo.oql.ParseTreeNode > --------------------------------------- > add a public method > public void replaceChild(ParseTreeNode child, int index) > > 2. change the binding process > ============================= > org.exolab.castor.jdo.engine.OQLQueryImpl > ----------------------------------------- > 1st change: > When the customer tries to bind an persistable object, already the cuurent code > converts this object to their (Complex) primary key. (line 170) > But this is not consequently continued: > We get an error at least when the database driver tries to serialize the object > > The change is: > before we put the value into _bindValues we test, if the value is a Complex > object. If yes, we put the content of Complex instead of the object itself > > 2nd change: > specific for multiple Key handling > in ParseTreeWalker there were the additional links in the ParamMap already added > with the change described above. We can use this here. > If there are multiple key fields in the Complex object we add always the next > value for each loop through the ParamMap. > In the (improbable) case that such objects are bound multiple times, we reset > the field pointer of the Complex object for the next usage. > > IV: Source Code of Changes > =========================== > > org.exolab.castor.jdo.oql.ParseTreeWalker > ----------------------------------------- > Line 92 add: > private Hashtable _multiPKCntr; > > Line 128 add: > _multiPKCntr = new Hashtable(); > > Line 499 add: > _multiPKCntr.put( projection, new Integer(0) ); > > Line 646 add: > _multiPKCntr.put( fieldTree, new Integer(0) ); > > Line 1136 replace: > case EQUAL: case NOT_EQUAL: case CONCAT: > with > case SIMPLE_EQUAL: case CONCAT: > > Line 1140 replace: > return getSQLExpr( exprTree.getChild(0) ) + " " > + exprTree.getToken().getTokenValue() + " " > + getSQLExpr( exprTree.getChild(1) ); > with > return getSQLExprForBinaryOp( exprTree ); > > Line 1142 add: > case EQUAL: case NOT_EQUAL: > return getSQLExprForEqual( exprTree ); > > Line 1251 replace: > return _queryExpr.encodeColumn(clsTableAlias, field.getSQLName()[0]); > with > int index = ((Integer)_multiPKCntr.get( exprTree )).intValue(); > _multiPKCntr.put( exprTree, new Integer( ++index ) ); > > return _queryExpr.encodeColumn(clsTableAlias, > field.getSQLName()[index-1]); > > Line 1323 add: (this is the main new code) > private String getSQLExprForBinaryOp(ParseTreeNode equalTree) { > return getSQLExpr( equalTree.getChild(0) ) + " " > + equalTree.getToken().getTokenValue() + " " > + getSQLExpr( equalTree.getChild(1) ); > } > > private String getSQLExprForEqual(ParseTreeNode equalTree) { > > // find the IDENTIFIER or DOT among the children of the EQUAL node > // supports both "a.person=$2" and "$2=a.person" > ParseTreeNode identOrDot = null; > Enumeration e = equalTree.children(); > for (; e.hasMoreElements(); ) { > identOrDot = (ParseTreeNode)e.nextElement(); > int tokenType = identOrDot.getToken().getTokenType(); > if (tokenType == IDENTIFIER || tokenType == DOT) > break; > else > identOrDot = null; > } > if (identOrDot == null) > return getSQLExprForBinaryOp(equalTree); > > // get FieldDesc => SQLNames > JDOFieldDescriptor field = (JDOFieldDescriptor) _fieldInfo.get(identOrDot); > if ( field == null ) > return getSQLExprForBinaryOp(equalTree); > > String[] sqlNames = field.getSQLName(); > if (sqlNames.length <= 1) > return getSQLExprForBinaryOp(equalTree); > > // The same simpleEqual object is used in multilple places of the new tree. > // When creating the SQL code snippet for the contained field we will > // use this to count for the index of the sqlNames array > ParseTreeNode simpleEqual = new ParseTreeNode( > new Token( SIMPLE_EQUAL,equalTree.getToken().getTokenValue() ) ); > for ( e = equalTree.children(); e.hasMoreElements(); ) > simpleEqual.addChild( (ParseTreeNode) e.nextElement() ); > > ParseTreeNode parentNode = equalTree.getParent(); > ParseTreeNode oldNode = simpleEqual; > ParseTreeNode newNode = null; > for ( int i = 1 ; i < sqlNames.length ; ++i ) { > newNode = new ParseTreeNode(new Token(KEYWORD_AND, "and")); > newNode.addChild( oldNode ); > newNode.addChild( simpleEqual ); > oldNode = newNode; > } > parentNode.replaceChild( newNode, 0 ); > // and now execute the adapted tree as usual > return getSQLExpr( newNode ); > } > > org.exolab.castor.jdo.oql.TokenTypes > ------------------------------------ > Line 112 add: > int SIMPLE_EQUAL = 56; > > org.exolab.castor.jdo.oql.ParseTreeNode > --------------------------------------- > Line 193 (and following) add: > /** > * Replace a node child. > * > * @param child the new child > * @param index the index of the child to replace. > */ > public void replaceChild(ParseTreeNode child, int index) { > child.setParent(this); > _children.setElementAt(child, index); > } > > org.exolab.castor.jdo.engine.OQLQueryImpl > ----------------------------------------- > Line 206 was: > for (Enumeration e = info.getParamMap().elements(); e.hasMoreElements(); > ) > { > int fieldNum = ( (Integer) e.nextElement() ).intValue(); > _bindValues[ fieldNum - 1 ] = value; > } > > changed to: > int valCntr = 0; > for (Enumeration e = info.getParamMap().elements(); e.hasMoreElements(); > ) > { > int fieldNum = ( (Integer) e.nextElement() ).intValue(); > if (value.getClass().isAssignableFrom( Complex.class )){ > _bindValues[ fieldNum - 1 ] = ((Complex)value).get(valCntr++); > if (((Complex)value).size() == valCntr) valCntr = 0; > } else > _bindValues[ fieldNum - 1 ] = value; > } > > V. (by the way) other optional changes > ========================== > change that is not directly necessary for this patch > > org.exolab.castor.jdo.engine.JDOFieldDescriptor > ----------------------------------------------- > change toString() method to handle Multiple Keys correctly ----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to [EMAIL PROTECTED] with a subject of: unsubscribe castor-dev
