objectbridge-developers Mailing List for ObJectRelationalBridge (Page 11)
Brought to you by:
thma
Menu
▾
▴
objectbridge-developers — public list for developer issues
You can subscribe to this list here.
| 2000 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
(14) |
Dec
(20) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2001 |
Jan
(33) |
Feb
(8) |
Mar
(3) |
Apr
(1) |
May
(18) |
Jun
(6) |
Jul
(15) |
Aug
(71) |
Sep
(29) |
Oct
(43) |
Nov
(77) |
Dec
(54) |
| 2002 |
Jan
(54) |
Feb
(147) |
Mar
(144) |
Apr
(163) |
May
(307) |
Jun
(240) |
Jul
|
Aug
|
Sep
(1) |
Oct
|
Nov
|
Dec
|
|
From: Oleg N. <on...@uk...> - 2002-05-30 21:56:35
|
On Friday 31 May 2002 00:53, Oleg Nitz wrote: > I propose to return to the original DBMS names with mixed case and to > original Platform class name algorithm. > Thus I am going to write > dbmsName=MySQL > dbmsName=Hsqldb > in profile files. > Any objections? > And BTW the correct DBMS names should be listed in the documentation. Another option is to change names of Platform classes and to leave profile files unchanged There are 2 classes to rename: ProfileMySQLImpl -> ProfileMssqlImpl ProfileMsAccessImpl -> ProfileMsaccessImpl I don't know which way is better. Oleg |
|
From: Oleg N. <on...@uk...> - 2002-05-30 21:51:00
|
Hi, Some time ago DBMS settings where in repository.xml and their names where in mixed case: MySQL, Hsqldb, and so on. Platform class name where defined as "Platform" + dbms_name + "Impl", e.g. PlatformMySQLImpl, PlatformHsqldbImpl, and so on. Then DBMS settings have moved to profile/*.profile and for some reason their names became lowercase: dbmsName=mysql dbmsName=hsqldb At some point I changed the algoritm for Platform names to "Platform" + pf.substring(0, 1).toUpperCase() + pf.substring(1) + "Impl"; so that it handled Hsqldb correctly. But now I see that this is not enough. I guess that the recently reported problem with MySQL may be due to this. I propose to return to the original DBMS names with mixed case and to original Platform class name algorithm. Thus I am going to write dbmsName=MySQL dbmsName=Hsqldb in profile files. Any objections? And BTW the correct DBMS names should be listed in the documentation. Oleg |
|
From: Oleg N. <on...@uk...> - 2002-05-30 21:27:02
|
On Thursday 30 May 2002 23:47, tr...@th... wrote:
> Because you are adding extra levels of complexity to OJB by trying to
> handle all databases internally. Abstracting this out would make it much
> more modular and easier to maintain and implement new databases. Not to
> mention better performance because crossdb uses a factory get the
> appropriate crossdb implementation, so there's not a bunch of if(db is
> mysql) else if(db is oracle), etc, etc.
Travis, the same thing is done in OJB: see Platform interface,
PlatformFactory, and so on. And database handling is completely contained in
ojb.broker.platforms module. The similar factory architecture is used in
Castor. But I decided to introduce a classification of outer join syntax of
*all* existing RDBMS (at least all that I know). At first I thought that
there are 3 types of syntax: SQL-92, Sybase and Oracle. Than it turned out
that SQL-92 syntax has 2 flavours: with braces and without braces.
And you are right, SqlStatement.java contains some number of if/else, which
handles these 4 types of syntax. I don't believe that this is noticable for
performance. But I suppose that the amount of code that handles database
differences in OJB is less than in Castor (ojb.broker.platforms = 21K,
org.exolab.castor.jdo.drivers = 217K), while OJB's SqlStatement.java is much
more powerful than Castor's QueryExpression.
> And it can support everything but unions (only because unions aren't
> supported in all dbs). Joins are easily supported and Where statements can
> be infinitely nested.
And what about aliases?
Anyway we need to use unions with all databases that support them, thus
CrossDB doesn't fit our needs. And supports less databases.
Not to mention better performance of SqlStatement because it does directly
stringBuffer.append("X.col1");
stringBuffer.append("=");
stringBuffer.append("Y.col2");
instead of
SelectQuery sq = factory.getSelectQuery();
...
sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2");
sq.execute();
I guess some number of additional objects are created inside the called
methods. And how many if/else handle constants like WhereCondition.EQUAL_TO?
Don't tell me that with CrossDB we will increase performance ;o)
Regards,
Oleg
|
|
From: <tr...@th...> - 2002-05-30 20:52:53
|
Because you are adding extra levels of complexity to OJB by trying to handle all databases internally. Abstracting this out would make it much more modular and easier to maintain and implement new databases. Not to mention better performance because crossdb uses a factory get the appropriate crossdb implementation, so there's not a bunch of if(db is mysql) else if(db is oracle), etc, etc. And it can support everything but unions (only because unions aren't supported in all dbs). Joins are easily supported and Where statements can be infinitely nested. Travis ---- Original Message ---- From: Oleg Nitz <on...@uk...> Sent: 2002-05-30 To: obj...@li... Subject: Re: [OJB-developers] bug in sql generation Hi Travis, OJB is database abstraction level itself, why introduce one more level? Actually it is not so difficult to support multiple databases in OJB, but SqlStatement.java code was added recently and need some time to stabilize. And I suppose that OJB already supports more than 6 databases. Also note that the generated queries may be very complicated: multiple inner and outer joins, aliases, unions. Does CrossDB support all these things? Thanks, Oleg On Thursday 30 May 2002 19:12, tr...@th... wrote: > You guys might want to consider using CrossDB at www.crossdb.com, then you > wouldn't have to deal with the intracacies of each db. You could cut out > half your if/elses, etc and just use one common set of methods. I'd be > happy to help in this area if need be. > > And it will support all databases very easily (5 or 6 dbs are supported > now), very easy to support more and the OJB code would not have to change > to support others. > > An example of how simple a join query would be is: > > SelectQuery sq = factory.getSelectQuery(); > sq.addTable("X"); > sq.addTable("Y"); > sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2"); > // add more conditions, order bys, group bys, etc if needed > > that's it > then call sq.execute when ready. > > Travis > > ---- Original Message ---- > From: Oleg Nitz <on...@ib...> > Sent: 2002-05-30 > To: obj...@li... > Subject: Re: [OJB-developers] bug in sql generation > > Hi Matthew, > > I also don't see error in the generated SQL. > Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server. > I propose that we change join syntax for MS SQL Server to > SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem. > Also this will make OJB support earlier versions MS SQL Server, which > didn't support SQL-92 syntax. > > Oleg > > Matthew baird wrote: > > I think the appendTableWithJoins routine has a bug with parenthesis. > > the routine is recursive to build out all the joins (with parens) upon > > entry, the stmtFromPos ( the position of the FROM keyword in the SQL > > being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and > > the table is the root. So let's say it's set to 150. > > > > since I have a complex path, with lots of joins, I end up calling > > appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos > > is 0 because table is not root. Near the bottom of the function, you see > > a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has > > next, so the ( is inserted at stmtFromPos which is 0, so I end up with > > SQL that looks like this: > > > > (((SELECT .... > > > > obviously those parens should go after the FROM clause. > > > > the solution is to make the stmtFromPos a parameter and pass it along. I > > wanted to get opinions on this fix, as the SQL being generated for our > > really complex case is still wrong (parens aren't working right) and I > > wanted to make sure this wasn't a symptom of another problem. > > > > protected void appendTableWithJoins(TableAlias table, StringBuffer where, > > StringBuffer buf) { > > int stmtFromPos = 0; > > > > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root) > > { > > stmtFromPos = buf.length(); // BRJ : point after FROM > > } > > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != > > root)) { > > buf.append(table.table); > > buf.append(" "); > > buf.append(table.alias); > > } > > if (!table.hasJoins()) > > { > > return; > > } > > for (Iterator it = table.iterateJoins(); it.hasNext();) > > { > > Join join = (Join) it.next(); > > if (joinSyntaxType == SQL92_JOIN_SYNTAX) > > { > > if (join.isOuter) > > { > > buf.append(" LEFT OUTER JOIN "); > > } > > else > > { > > buf.append(" INNER JOIN "); > > } > > if (join.right.hasJoins()) > > { > > buf.append("("); > > appendTableWithJoins(join.right, where, buf); > > buf.append(")"); > > } > > else > > { > > appendTableWithJoins(join.right, where, buf); > > } > > buf.append(" ON "); > > join.appendJoinEqualities(buf); > > if (it.hasNext()) > > { > > buf.insert(stmtFromPos, "("); > > buf.append(")"); > > } > > } > > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX) > > { > > if (join.isOuter) > > { > > buf.append(" LEFT OUTER JOIN "); > > } > > else > > { > > buf.append(" INNER JOIN "); > > } > > buf.append(join.right.table); > > buf.append(" "); > > buf.append(join.right.alias); > > buf.append(" ON "); > > join.appendJoinEqualities(buf); > > appendTableWithJoins(join.right, where, buf); > > } > > else > > { > > buf.append(","); > > appendTableWithJoins(join.right, where, buf); > > if (where.length() > 0) > > { > > where.append(" AND "); > > } > > join.appendJoinEqualities(where); > > } > > } > > } > > The SQL being generated for my complex case is: > > SELECT > > A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0 > >.activation_date, A0.deactivation_date,A0.matrix_calc_version_uuid FROM > > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN > > (statement_VAR A2 INNER JOIN PARTICIPANT A3 ON > > A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 > > ON A2.organization_uuid=A5.hierarchy_object_uuid) INNER JOIN JOB A4 ON > > A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON > > A6.unit_uuid=A7.unit_uuid) ON A2.statement_uuid=A6.statement_uuid) ON > > A1.statement_var_uuid=A2.statement_var_uuid) ON > > A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid > > > > WHERE (((( ( ( A0.activation_date <= ? > > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) > > AND A0.begin_effective_date <= ?) > > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) > > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) > > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ? > > which if you drop it into Query Analyzer or try to execute it against MS > > SQL Server, you get an error with not a lot of information, but it looks > > like it's right after the FROM. thoughts? > > > > > > > > --------------------------------- > > Do You Yahoo!? > > Yahoo! - Official partner of 2002 FIFA World Cup > > _______________________________________________________________ > > Don't miss the 2002 Sprint PCS Application Developer's Conference > August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers > > > > _______________________________________________________________ > > Don't miss the 2002 Sprint PCS Application Developer's Conference > August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ Objectbridge-developers mailing list Obj...@li... https://lists.sourceforge.net/lists/listinfo/objectbridge-developers |
|
From: Oleg N. <on...@uk...> - 2002-05-30 20:33:23
|
Hi Travis, OJB is database abstraction level itself, why introduce one more level? Actually it is not so difficult to support multiple databases in OJB, but SqlStatement.java code was added recently and need some time to stabilize. And I suppose that OJB already supports more than 6 databases. Also note that the generated queries may be very complicated: multiple inner and outer joins, aliases, unions. Does CrossDB support all these things? Thanks, Oleg On Thursday 30 May 2002 19:12, tr...@th... wrote: > You guys might want to consider using CrossDB at www.crossdb.com, then you > wouldn't have to deal with the intracacies of each db. You could cut out > half your if/elses, etc and just use one common set of methods. I'd be > happy to help in this area if need be. > > And it will support all databases very easily (5 or 6 dbs are supported > now), very easy to support more and the OJB code would not have to change > to support others. > > An example of how simple a join query would be is: > > SelectQuery sq = factory.getSelectQuery(); > sq.addTable("X"); > sq.addTable("Y"); > sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2"); > // add more conditions, order bys, group bys, etc if needed > > that's it > then call sq.execute when ready. > > Travis > > ---- Original Message ---- > From: Oleg Nitz <on...@ib...> > Sent: 2002-05-30 > To: obj...@li... > Subject: Re: [OJB-developers] bug in sql generation > > Hi Matthew, > > I also don't see error in the generated SQL. > Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server. > I propose that we change join syntax for MS SQL Server to > SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem. > Also this will make OJB support earlier versions MS SQL Server, which > didn't support SQL-92 syntax. > > Oleg > > Matthew baird wrote: > > I think the appendTableWithJoins routine has a bug with parenthesis. > > the routine is recursive to build out all the joins (with parens) upon > > entry, the stmtFromPos ( the position of the FROM keyword in the SQL > > being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and > > the table is the root. So let's say it's set to 150. > > > > since I have a complex path, with lots of joins, I end up calling > > appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos > > is 0 because table is not root. Near the bottom of the function, you see > > a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has > > next, so the ( is inserted at stmtFromPos which is 0, so I end up with > > SQL that looks like this: > > > > (((SELECT .... > > > > obviously those parens should go after the FROM clause. > > > > the solution is to make the stmtFromPos a parameter and pass it along. I > > wanted to get opinions on this fix, as the SQL being generated for our > > really complex case is still wrong (parens aren't working right) and I > > wanted to make sure this wasn't a symptom of another problem. > > > > protected void appendTableWithJoins(TableAlias table, StringBuffer where, > > StringBuffer buf) { > > int stmtFromPos = 0; > > > > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root) > > { > > stmtFromPos = buf.length(); // BRJ : point after FROM > > } > > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != > > root)) { > > buf.append(table.table); > > buf.append(" "); > > buf.append(table.alias); > > } > > if (!table.hasJoins()) > > { > > return; > > } > > for (Iterator it = table.iterateJoins(); it.hasNext();) > > { > > Join join = (Join) it.next(); > > if (joinSyntaxType == SQL92_JOIN_SYNTAX) > > { > > if (join.isOuter) > > { > > buf.append(" LEFT OUTER JOIN "); > > } > > else > > { > > buf.append(" INNER JOIN "); > > } > > if (join.right.hasJoins()) > > { > > buf.append("("); > > appendTableWithJoins(join.right, where, buf); > > buf.append(")"); > > } > > else > > { > > appendTableWithJoins(join.right, where, buf); > > } > > buf.append(" ON "); > > join.appendJoinEqualities(buf); > > if (it.hasNext()) > > { > > buf.insert(stmtFromPos, "("); > > buf.append(")"); > > } > > } > > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX) > > { > > if (join.isOuter) > > { > > buf.append(" LEFT OUTER JOIN "); > > } > > else > > { > > buf.append(" INNER JOIN "); > > } > > buf.append(join.right.table); > > buf.append(" "); > > buf.append(join.right.alias); > > buf.append(" ON "); > > join.appendJoinEqualities(buf); > > appendTableWithJoins(join.right, where, buf); > > } > > else > > { > > buf.append(","); > > appendTableWithJoins(join.right, where, buf); > > if (where.length() > 0) > > { > > where.append(" AND "); > > } > > join.appendJoinEqualities(where); > > } > > } > > } > > The SQL being generated for my complex case is: > > SELECT > > A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0 > >.activation_date, A0.deactivation_date,A0.matrix_calc_version_uuid FROM > > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN > > (statement_VAR A2 INNER JOIN PARTICIPANT A3 ON > > A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 > > ON A2.organization_uuid=A5.hierarchy_object_uuid) INNER JOIN JOB A4 ON > > A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON > > A6.unit_uuid=A7.unit_uuid) ON A2.statement_uuid=A6.statement_uuid) ON > > A1.statement_var_uuid=A2.statement_var_uuid) ON > > A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid > > > > WHERE (((( ( ( A0.activation_date <= ? > > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) > > AND A0.begin_effective_date <= ?) > > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) > > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) > > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ? > > which if you drop it into Query Analyzer or try to execute it against MS > > SQL Server, you get an error with not a lot of information, but it looks > > like it's right after the FROM. thoughts? > > > > > > > > --------------------------------- > > Do You Yahoo!? > > Yahoo! - Official partner of 2002 FIFA World Cup > > _______________________________________________________________ > > Don't miss the 2002 Sprint PCS Application Developer's Conference > August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers > > > > _______________________________________________________________ > > Don't miss the 2002 Sprint PCS Application Developer's Conference > August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers |
|
From: Jakob B. <jbr...@ho...> - 2002-05-30 20:01:45
|
hi galvin,
oleg and me wanted to get rid of MtoNQuery, so we decided to use inverse
relationships there.
afaik m:n relationships work with this solution (at least the test cases
do). i see there's a chance of a npe if no inverse relationship is found,
but i think this should not happen.
jakob
----- Original Message -----
From: "Galvin Hsiu" <gk...@ya...>
To: <obj...@li...>
Sent: Thursday, May 30, 2002 7:31 PM
Subject: [OJB-developers] Bug/Broken feature in CVS in
PersistenceBrokerImpl.java
> For the m:n relationships (looking at the CVS -
> version 1.19 of PersistentBrokerImpl.java)
>
> There is a null pointer exception that can be thrown
> if the inverseCod is null.
>
> This happens when I have a non-decomposed m:n
> relationship between 2 tables - it seems like version
> 1.19 patch addressed the m:n extends, but completely
> broke functionality with nondecomposed m:n
> relationships w/ indirection tables (unless someone
> could maybe shed some light on this?). Furthermore,
> what happens if the inverse relationship cannot be
> determined? (i.e. the relationship joining two tables
> are defined in the indirection table)?
>
> private Query getMtoNQuery(Object obj,
> ClassDescriptor cld, CollectionDescriptor cod)
> {
> Object[] values = cld.getKeyValues(obj);
> ClassDescriptor refCld =
> getClassDescriptor(cod.getItemClass());
> FieldDescriptor[] pks = cld.getPkFields();
> CollectionDescriptor inverseCod = null;
> CollectionDescriptor icod;
> Class itemClass = cld.getClassOfObject();
> Class baseClass = getExtentClass(itemClass);
> Criteria criteria = new Criteria();
> Query query;
>
> //
> // find the inverse relationship of m:n
> //
> Vector v = refCld.getCollectionDescriptors();
>
> for (int i= 0;i < v.size();i++)
> {
> icod =
> (CollectionDescriptor)v.elementAt(i);
> if (icod.getItemClass() == baseClass)
> {
> inverseCod = icod;
> break;
> }
> }
>
> /* BEGIN NEW CODE */
> /* if (inverseCod == null)
> handle gracefully, or maybe try to
> detect this as an indirect table with
> 1.18 m:n logic.
> */
> /* END NEW CODE */
>
> //
> // build the criteria using inverse
> relationship
> //
> for (int i = 0; i < pks.length; i++)
> {
>
> criteria.addEqualTo(inverseCod.getAttributeName() +
> "." + pks[i].getAttributeName(), values[i]);
> }
>
> criteria.orderBy(refCld.getOrderby());
> query = new
> QueryByCriteria(refCld.getClassOfObject(), criteria);
> if (baseClass != itemClass)
> {
> ((QueryByCriteria)
> query).addPathClass(inverseCod.getAttributeName(),
> itemClass); // Set the hint
> }
>
> return query;
> }
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
>
> _______________________________________________________________
>
> Don't miss the 2002 Sprint PCS Application Developer's Conference
> August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm
>
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
>
|
|
From: <ll...@li...> - 2002-05-30 18:21:53
|
Hi, I have done quite a lot of XSLT and I think that I might be able to write a migration stylesheet. However I am not into the finer details of the mapping files, so what I can offer is a stylesheet that generates files that complies to the new DTD. Not a throughly tested migration path. If you just send me a bunch of working mapping files (all entries are welcome) I'll try to write the stylesheet over the weekend. /Lasse Thomas Mahler wrote: > A request: Are there any XSLT experts on this list? > I'd like to provide OJB users with a stylesheet that can transform > their old repository.xml into the new format. > Any volunteers? > > cheers, > > Thomas > > > |
|
From: <tr...@th...> - 2002-05-30 18:17:43
|
Yes it really works. I use it for everything and it's under apache li=
cense so it can be used.
<br>
<br>And since OJB is looking to go towards apache license it would be a nic=
e fit.
<br>
<br>Travis
<br>
<br>---- Original Message ----
<br>From: Matthew baird <<a href=3D"mailto:mat...@ya...">mattbair=
d...@ya...</a>>
<br>Sent: 2002-05-30
<br>To: <a href=3D"mailto:tr...@th...">tr...@th...<=
/a>, Oleg Nitz <<a href=3D"mailto:on...@ib...">on...@ib...</=
a>>
<br>Subject: RE: Re: [OJB-developers] bug in sql generation
<br>
<br><P> ooooo that thing looks cool, but does it really work? If It did, th=
at would be a great time saver. Any chance the licenses would not be happy =
together?
<P> <B><I>tr...@th...</I></B> wrote:=20
<BLOCKQUOTE style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #101=
0ff 2px solid">You guys might want to consider using CrossDB at www.crossdb=
.com, then you wouldn't have to deal with the intracacies of each db. You c=
ould cut out half your if/elses, etc and just use one common set of methods=
. I'd be happy to help in this area if need be.<BR><BR>And it will support =
all databases very easily (5 or 6 dbs are supported now), very easy to supp=
ort more and the OJB code would not have to change to support others.<BR><B=
R>An example of how simple a join query would be is:<BR><BR>SelectQuery sq =
=3D factory.getSelectQuery();<BR>sq.addTable("X");<BR>sq.addTable("Y");<BR>=
sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2");<BR>// ad=
d more conditions, order bys, group bys, etc if needed<BR><BR>that's it<BR>=
then call sq.execute when ready.<BR><BR>Travis<BR><BR>---- Original Message=
----<BR>From: Oleg Nitz <ON...@IB...><BR>Sent: 2002-05-30<BR>To: obj=
ect...@li...<BR>Subject: Re: [OJB-developers]=
bug in sql generation<BR><BR>Hi Matthew,<BR><BR>I also don't see error in =
the generated SQL.<BR>Probably it's a problem of SQL-92 JOIN syntax support=
in MS SQL Server.<BR>I propose that we change join syntax for MS SQL Serve=
r to<BR>SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the proble=
m. <BR>Also this will make OJB support earlier versions MS SQL Server, whic=
h<BR>didn't support SQL-92 syntax.<BR><BR>Oleg<BR><BR>Matthew baird wrote:<=
BR><BR>> I think the appendTableWithJoins routine has a bug with parenth=
esis. the routine is recursive to build out all the joins (with parens) upo=
n entry, the stmtFromPos ( the position of the FROM<BR>> keyword in the =
SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and=
the table is the root. So let's say it's set to 150.<BR><BR>> since I h=
ave a complex path, with lots of joins, I end up calling appendTableWithJoi=
ns from within appendTableWithJoins. Now, stmtFromPos is 0 because table is=
not root. Near the bottom of the<BR>> function, you see a buf.insert(st=
mtFromPos, "("); if iterator has next. In my case it has next, so the ( is =
inserted at stmtFromPos which is 0, so I end up with SQL that looks like th=
is:<BR><BR>> (((SELECT ....<BR><BR>> obviously those parens should go=
after the FROM clause. <BR><BR>> the solution is to make the stmtFromPo=
s a parameter and pass it along. I wanted to get opinions on this fix, as t=
he SQL being generated for our really complex case is still wrong (parens a=
ren't<BR>> working right) and I wanted to make sure this wasn't a sympto=
m of another problem.<BR><BR>> protected void appendTableWithJoins(Table=
Alias table, StringBuffer where, StringBuffer buf)<BR>> {<BR>> int st=
mtFromPos =3D 0;<BR><BR>> if (joinSyntaxType =3D=3D SQL92_JOIN_SYNTAX &a=
mp;& table =3D=3D root)<BR>> {<BR>> stmtFromPos =3D buf.length();=
// BRJ : point after FROM<BR>> }<BR>> if (!(joinSyntaxType =3D=3D SQ=
L92_NOPAREN_JOIN_SYNTAX && table !=3D root))<BR>> {<BR>> buf.=
append(table.table);<BR>> buf.append(" ");<BR>> buf.append(table.alia=
s);<BR>> }<BR>> if (!table.hasJoins())<BR>> {<BR>> return;<BR>&=
gt; }<BR>> for (Iterator it =3D table.iterateJoins(); it.hasNext();)<BR>=
> {<BR>> Join join =3D (Join) it.next();<BR>> if (joinSyntaxType =
=3D=3D SQL92_JOIN_SYNTAX)<BR>> {<BR>> if (join.isOuter)<BR>> {<BR>=
> buf.append(" LEFT OUTER JOIN ");<BR>> }<BR>> else<BR>> {<BR>&=
gt; buf.append(" INNER JOIN ");<BR>> }<BR>> if (join.right.hasJoins()=
)<BR>> {<BR>> buf.append("(");<BR>> appendTableWithJoins(join.righ=
t, where, buf);<BR>> buf.append(")");<BR>> }<BR>> else<BR>> {<B=
R>> appendTableWithJoins(join.right, where, buf);<BR>> }<BR>> buf.=
append(" ON ");<BR>> join.appendJoinEqualities(buf);<BR>> if (it.hasN=
ext())<BR>> {<BR>> buf.insert(stmtFromPos, "(");<BR>> buf.append("=
)");<BR>> }<BR>> }<BR>> else if (joinSyntaxType =3D=3D SQL92_NOPAR=
EN_JOIN_SYNTAX)<BR>> {<BR>> if (join.isOuter)<BR>> {<BR>> buf.a=
ppend(" LEFT OUTER JOIN ");<BR>> }<BR>> else<BR>> {<BR>> buf.ap=
pend(" INNER JOIN ");<BR>> }<BR>> buf.append(join.right.table);<BR>&g=
t; buf.append(" ");<BR>> buf.append(join.right.alias);<BR>> buf.appen=
d(" ON ");<BR>> join.appendJoinEqualities(buf);<BR>> appendTableWithJ=
oins(join.right, where, buf);<BR>> }<BR>> else<BR>> {<BR>> buf.=
append(",");<BR>> appendTableWithJoins(join.right, where, buf);<BR>> =
if (where.length() > 0)<BR>> {<BR>> where.append(" AND ");<BR>>=
}<BR>> join.appendJoinEqualities(where);<BR>> }<BR>> }<BR>> }<=
BR>> The SQL being generated for my complex case is:<BR>> SELECT A0.m=
atrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activ=
ation_date,<BR>> A0.deactivation_date,A0.matrix_calc_version_uuid FROM <=
BR>> (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOI=
N (statement_VAR A2 INNER JOIN PARTICIPANT A3 <BR>> ON A2.participant_uu=
id=3DA3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization=
_uuid=3DA5.hierarchy_object_uuid) <BR>> INNER JOIN JOB A4 ON A2.job_uuid=
=3DA4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=
=3DA7.unit_uuid) <BR>> ON A2.statement_uuid=3DA6.statement_uuid) ON A1.s=
tatement_var_uuid=3DA2.statement_var_uuid) <BR>> ON A0.matrix_calc_versi=
on_uuid=3DA1.matrix_calc_version_uuid<BR><BR>> WHERE (((( ( ( A0.activat=
ion_date <=3D ? <BR>> AND A0.deactivation_date > ? OR A0.deactivat=
ion_date IS NULL ) <BR>> AND A0.begin_effective_date <=3D ?) <BR>>=
AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) <BR>&g=
t; AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) <BR>> AND=
A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid =3D ?<BR>> which if=
you drop it into Query Analyzer or try to execute it against MS SQL Server=
, you get an error with not a lot of information, but it looks like it's ri=
ght after the FROM.<BR>> thoughts?<BR><BR><BR><BR>> -----------------=
----------------<BR>> Do You Yahoo!?<BR>> Yahoo! - Official partner o=
f 2002 FIFA World Cup<BR><BR><BR><BR>______________________________________=
_________________________<BR><BR>Don't miss the 2002 Sprint PCS Application=
Developer's Conference<BR>August 25-28 in Las Vegas -- http://devcon.sprin=
tpcs.com/adp/index.cfm<BR><BR>_____________________________________________=
__<BR>Objectbridge-developers mailing list<BR>Objectbridge-developers@lists=
.sourceforge.net<BR>https://lists.sourceforge.net/lists/listinfo/objectbrid=
ge-developers<BR><BR><BR><BR>______________________________________________=
_________________<BR><BR>Don't miss the 2002 Sprint PCS Application Develop=
er's Conference<BR>August 25-28 in Las Vegas -- http://devcon.sprintpcs.com=
/adp/index.cfm<BR><BR>_______________________________________________<BR>Ob=
jectbridge-developers mailing list<BR>Obj...@li...urcef=
orge.net<BR>https://lists.sourceforge.net/lists/listinfo/objectbridge-devel=
opers</BLOCKQUOTE><p><br><hr size=3D1><b>Do You Yahoo!?</b><br>
<a href=3D"http://rd.yahoo.com/welcome/*http://fifaworldcup.yahoo.com">Yaho=
o!</a> - Official partner of 2002 FIFA World Cup
|
|
From: Galvin H. <gk...@ya...> - 2002-05-30 17:31:15
|
For the m:n relationships (looking at the CVS -
version 1.19 of PersistentBrokerImpl.java)
There is a null pointer exception that can be thrown
if the inverseCod is null.
This happens when I have a non-decomposed m:n
relationship between 2 tables - it seems like version
1.19 patch addressed the m:n extends, but completely
broke functionality with nondecomposed m:n
relationships w/ indirection tables (unless someone
could maybe shed some light on this?). Furthermore,
what happens if the inverse relationship cannot be
determined? (i.e. the relationship joining two tables
are defined in the indirection table)?
private Query getMtoNQuery(Object obj,
ClassDescriptor cld, CollectionDescriptor cod)
{
Object[] values = cld.getKeyValues(obj);
ClassDescriptor refCld =
getClassDescriptor(cod.getItemClass());
FieldDescriptor[] pks = cld.getPkFields();
CollectionDescriptor inverseCod = null;
CollectionDescriptor icod;
Class itemClass = cld.getClassOfObject();
Class baseClass = getExtentClass(itemClass);
Criteria criteria = new Criteria();
Query query;
//
// find the inverse relationship of m:n
//
Vector v = refCld.getCollectionDescriptors();
for (int i= 0;i < v.size();i++)
{
icod =
(CollectionDescriptor)v.elementAt(i);
if (icod.getItemClass() == baseClass)
{
inverseCod = icod;
break;
}
}
/* BEGIN NEW CODE */
/* if (inverseCod == null)
handle gracefully, or maybe try to
detect this as an indirect table with
1.18 m:n logic.
*/
/* END NEW CODE */
//
// build the criteria using inverse
relationship
//
for (int i = 0; i < pks.length; i++)
{
criteria.addEqualTo(inverseCod.getAttributeName() +
"." + pks[i].getAttributeName(), values[i]);
}
criteria.orderBy(refCld.getOrderby());
query = new
QueryByCriteria(refCld.getClassOfObject(), criteria);
if (baseClass != itemClass)
{
((QueryByCriteria)
query).addPathClass(inverseCod.getAttributeName(),
itemClass); // Set the hint
}
return query;
}
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
|
|
From: Matthew b. <mat...@ya...> - 2002-05-30 17:10:12
|
ooooo that thing looks cool, but does it really work? If It did, that would be a great time saver. Any chance the licenses would not be happy together? tr...@th... wrote: You guys might want to consider using CrossDB at www.crossdb.com, then you wouldn't have to deal with the intracacies of each db. You could cut out half your if/elses, etc and just use one common set of methods. I'd be happy to help in this area if need be. And it will support all databases very easily (5 or 6 dbs are supported now), very easy to support more and the OJB code would not have to change to support others. An example of how simple a join query would be is: SelectQuery sq = factory.getSelectQuery(); sq.addTable("X"); sq.addTable("Y"); sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2"); // add more conditions, order bys, group bys, etc if needed that's it then call sq.execute when ready. Travis ---- Original Message ---- From: Oleg Nitz Sent: 2002-05-30 To: obj...@li... Subject: Re: [OJB-developers] bug in sql generation Hi Matthew, I also don't see error in the generated SQL. Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server. I propose that we change join syntax for MS SQL Server to SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem. Also this will make OJB support earlier versions MS SQL Server, which didn't support SQL-92 syntax. Oleg Matthew baird wrote: > I think the appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM > keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150. > since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the > function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this: > (((SELECT .... > obviously those parens should go after the FROM clause. > the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't > working right) and I wanted to make sure this wasn't a symptom of another problem. > protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf) > { > int stmtFromPos = 0; > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root) > { > stmtFromPos = buf.length(); // BRJ : point after FROM > } > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root)) > { > buf.append(table.table); > buf.append(" "); > buf.append(table.alias); > } > if (!table.hasJoins()) > { > return; > } > for (Iterator it = table.iterateJoins(); it.hasNext();) > { > Join join = (Join) it.next(); > if (joinSyntaxType == SQL92_JOIN_SYNTAX) > { > if (join.isOuter) > { > buf.append(" LEFT OUTER JOIN "); > } > else > { > buf.append(" INNER JOIN "); > } > if (join.right.hasJoins()) > { > buf.append("("); > appendTableWithJoins(join.right, where, buf); > buf.append(")"); > } > else > { > appendTableWithJoins(join.right, where, buf); > } > buf.append(" ON "); > join.appendJoinEqualities(buf); > if (it.hasNext()) > { > buf.insert(stmtFromPos, "("); > buf.append(")"); > } > } > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX) > { > if (join.isOuter) > { > buf.append(" LEFT OUTER JOIN "); > } > else > { > buf.append(" INNER JOIN "); > } > buf.append(join.right.table); > buf.append(" "); > buf.append(join.right.alias); > buf.append(" ON "); > join.appendJoinEqualities(buf); > appendTableWithJoins(join.right, where, buf); > } > else > { > buf.append(","); > appendTableWithJoins(join.right, where, buf); > if (where.length() > 0) > { > where.append(" AND "); > } > join.appendJoinEqualities(where); > } > } > } > The SQL being generated for my complex case is: > SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date, > A0.deactivation_date,A0.matrix_calc_version_uuid FROM > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3 > ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid) > INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid) > ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid) > ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid > WHERE (((( ( ( A0.activation_date <= ? > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) > AND A0.begin_effective_date <= ?) > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ? > which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM. > thoughts? > --------------------------------- > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ Objectbridge-developers mailing list Obj...@li... https://lists.sourceforge.net/lists/listinfo/objectbridge-developers _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ Objectbridge-developers mailing list Obj...@li... https://lists.sourceforge.net/lists/listinfo/objectbridge-developers --------------------------------- Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup |
|
From: Oleg N. <on...@ib...> - 2002-05-30 16:29:18
|
Hi Jakob,
Jakob Braeuchi wrote:
> i made stmtFromPos an instvar again, otherwise it can be 0 in some cases and
> the result will be (((SELECT as matthew described.
Now I see. Thanks to Matthew!
Oleg
> jakob
> ----- Original Message -----
> From: "Oleg Nitz" <on...@ib...>
> To: <obj...@li...>
> Sent: Thursday, May 30, 2002 6:08 PM
> Subject: Re: [OJB-developers] bug in sql generation
>> Hi Matthew,
>>
>> I also don't see error in the generated SQL.
>> Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server.
>> I propose that we change join syntax for MS SQL Server to
>> SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem.
>> Also this will make OJB support earlier versions MS SQL Server, which
>> didn't support SQL-92 syntax.
>>
>> Oleg
>>
>> Matthew baird wrote:
>>
>> > I think the appendTableWithJoins routine has a bug with parenthesis.
> the routine is recursive to build out all the joins (with parens) upon
> entry, the stmtFromPos ( the position of the FROM
>> > keyword in the SQL being generated) is set because joinSyntaxType is
> SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.
>>
>> > since I have a complex path, with lots of joins, I end up calling
> appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0
> because table is not root. Near the bottom of the
>> > function, you see a buf.insert(stmtFromPos, "("); if iterator has next.
> In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I
> end up with SQL that looks like this:
>>
>> > (((SELECT ....
>>
>> > obviously those parens should go after the FROM clause.
>>
>> > the solution is to make the stmtFromPos a parameter and pass it along. I
> wanted to get opinions on this fix, as the SQL being generated for our
> really complex case is still wrong (parens aren't
>> > working right) and I wanted to make sure this wasn't a symptom of
> another problem.
>>
>> > protected void appendTableWithJoins(TableAlias table, StringBuffer
> where, StringBuffer buf)
>> > {
>> > int stmtFromPos = 0;
>>
>> > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
>> > {
>> > stmtFromPos = buf.length(); // BRJ : point after FROM
>> > }
>> > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table !=
> root))
>> > {
>> > buf.append(table.table);
>> > buf.append(" ");
>> > buf.append(table.alias);
>> > }
>> > if (!table.hasJoins())
>> > {
>> > return;
>> > }
>> > for (Iterator it = table.iterateJoins(); it.hasNext();)
>> > {
>> > Join join = (Join) it.next();
>> > if (joinSyntaxType == SQL92_JOIN_SYNTAX)
>> > {
>> > if (join.isOuter)
>> > {
>> > buf.append(" LEFT OUTER JOIN ");
>> > }
>> > else
>> > {
>> > buf.append(" INNER JOIN ");
>> > }
>> > if (join.right.hasJoins())
>> > {
>> > buf.append("(");
>> > appendTableWithJoins(join.right, where, buf);
>> > buf.append(")");
>> > }
>> > else
>> > {
>> > appendTableWithJoins(join.right, where, buf);
>> > }
>> > buf.append(" ON ");
>> > join.appendJoinEqualities(buf);
>> > if (it.hasNext())
>> > {
>> > buf.insert(stmtFromPos, "(");
>> > buf.append(")");
>> > }
>> > }
>> > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
>> > {
>> > if (join.isOuter)
>> > {
>> > buf.append(" LEFT OUTER JOIN ");
>> > }
>> > else
>> > {
>> > buf.append(" INNER JOIN ");
>> > }
>> > buf.append(join.right.table);
>> > buf.append(" ");
>> > buf.append(join.right.alias);
>> > buf.append(" ON ");
>> > join.appendJoinEqualities(buf);
>> > appendTableWithJoins(join.right, where, buf);
>> > }
>> > else
>> > {
>> > buf.append(",");
>> > appendTableWithJoins(join.right, where, buf);
>> > if (where.length() > 0)
>> > {
>> > where.append(" AND ");
>> > }
>> > join.appendJoinEqualities(where);
>> > }
>> > }
>> > }
>> > The SQL being generated for my complex case is:
>> > SELECT
> A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.ac
> tivation_date,
>> > A0.deactivation_date,A0.matrix_calc_version_uuid FROM
>> > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN
> (statement_VAR A2 INNER JOIN PARTICIPANT A3
>> > ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT
> A5 ON A2.organization_uuid=A5.hierarchy_object_uuid)
>> > INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6
> INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid)
>> > ON A2.statement_uuid=A6.statement_uuid) ON
> A1.statement_var_uuid=A2.statement_var_uuid)
>> > ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid
>>
>> > WHERE (((( ( ( A0.activation_date <= ?
>> > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL )
>> > AND A0.begin_effective_date <= ?)
>> > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL )
>> > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL )
>> > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ?
>> > which if you drop it into Query Analyzer or try to execute it against MS
> SQL Server, you get an error with not a lot of information, but it looks
> like it's right after the FROM.
>> > thoughts?
>>
>>
>>
>> > ---------------------------------
>> > Do You Yahoo!?
>> > Yahoo! - Official partner of 2002 FIFA World Cup
>>
>>
>>
>> _______________________________________________________________
>>
>> Don't miss the 2002 Sprint PCS Application Developer's Conference
>> August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm
>>
>> _______________________________________________
>> Objectbridge-developers mailing list
>> Obj...@li...
>> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
>>
|
|
From: Jakob B. <jbr...@ho...> - 2002-05-30 16:21:21
|
hi oleg,
i made stmtFromPos an instvar again, otherwise it can be 0 in some cases and
the result will be (((SELECT as matthew described.
jakob
----- Original Message -----
From: "Oleg Nitz" <on...@ib...>
To: <obj...@li...>
Sent: Thursday, May 30, 2002 6:08 PM
Subject: Re: [OJB-developers] bug in sql generation
> Hi Matthew,
>
> I also don't see error in the generated SQL.
> Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server.
> I propose that we change join syntax for MS SQL Server to
> SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem.
> Also this will make OJB support earlier versions MS SQL Server, which
> didn't support SQL-92 syntax.
>
> Oleg
>
> Matthew baird wrote:
>
> > I think the appendTableWithJoins routine has a bug with parenthesis.
the routine is recursive to build out all the joins (with parens) upon
entry, the stmtFromPos ( the position of the FROM
> > keyword in the SQL being generated) is set because joinSyntaxType is
SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.
>
> > since I have a complex path, with lots of joins, I end up calling
appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0
because table is not root. Near the bottom of the
> > function, you see a buf.insert(stmtFromPos, "("); if iterator has next.
In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I
end up with SQL that looks like this:
>
> > (((SELECT ....
>
> > obviously those parens should go after the FROM clause.
>
> > the solution is to make the stmtFromPos a parameter and pass it along. I
wanted to get opinions on this fix, as the SQL being generated for our
really complex case is still wrong (parens aren't
> > working right) and I wanted to make sure this wasn't a symptom of
another problem.
>
> > protected void appendTableWithJoins(TableAlias table, StringBuffer
where, StringBuffer buf)
> > {
> > int stmtFromPos = 0;
>
> > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
> > {
> > stmtFromPos = buf.length(); // BRJ : point after FROM
> > }
> > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table !=
root))
> > {
> > buf.append(table.table);
> > buf.append(" ");
> > buf.append(table.alias);
> > }
> > if (!table.hasJoins())
> > {
> > return;
> > }
> > for (Iterator it = table.iterateJoins(); it.hasNext();)
> > {
> > Join join = (Join) it.next();
> > if (joinSyntaxType == SQL92_JOIN_SYNTAX)
> > {
> > if (join.isOuter)
> > {
> > buf.append(" LEFT OUTER JOIN ");
> > }
> > else
> > {
> > buf.append(" INNER JOIN ");
> > }
> > if (join.right.hasJoins())
> > {
> > buf.append("(");
> > appendTableWithJoins(join.right, where, buf);
> > buf.append(")");
> > }
> > else
> > {
> > appendTableWithJoins(join.right, where, buf);
> > }
> > buf.append(" ON ");
> > join.appendJoinEqualities(buf);
> > if (it.hasNext())
> > {
> > buf.insert(stmtFromPos, "(");
> > buf.append(")");
> > }
> > }
> > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
> > {
> > if (join.isOuter)
> > {
> > buf.append(" LEFT OUTER JOIN ");
> > }
> > else
> > {
> > buf.append(" INNER JOIN ");
> > }
> > buf.append(join.right.table);
> > buf.append(" ");
> > buf.append(join.right.alias);
> > buf.append(" ON ");
> > join.appendJoinEqualities(buf);
> > appendTableWithJoins(join.right, where, buf);
> > }
> > else
> > {
> > buf.append(",");
> > appendTableWithJoins(join.right, where, buf);
> > if (where.length() > 0)
> > {
> > where.append(" AND ");
> > }
> > join.appendJoinEqualities(where);
> > }
> > }
> > }
> > The SQL being generated for my complex case is:
> > SELECT
A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.ac
tivation_date,
> > A0.deactivation_date,A0.matrix_calc_version_uuid FROM
> > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN
(statement_VAR A2 INNER JOIN PARTICIPANT A3
> > ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT
A5 ON A2.organization_uuid=A5.hierarchy_object_uuid)
> > INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6
INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid)
> > ON A2.statement_uuid=A6.statement_uuid) ON
A1.statement_var_uuid=A2.statement_var_uuid)
> > ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid
>
> > WHERE (((( ( ( A0.activation_date <= ?
> > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL )
> > AND A0.begin_effective_date <= ?)
> > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL )
> > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL )
> > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ?
> > which if you drop it into Query Analyzer or try to execute it against MS
SQL Server, you get an error with not a lot of information, but it looks
like it's right after the FROM.
> > thoughts?
>
>
>
> > ---------------------------------
> > Do You Yahoo!?
> > Yahoo! - Official partner of 2002 FIFA World Cup
>
>
>
> _______________________________________________________________
>
> Don't miss the 2002 Sprint PCS Application Developer's Conference
> August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm
>
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
>
|
|
From: <tr...@th...> - 2002-05-30 16:18:08
|
You guys might want to consider using CrossDB at www.crossdb.com, then you wouldn't have to deal with the intracacies of each db. You could cut out half your if/elses, etc and just use one common set of methods. I'd be happy to help in this area if need be. And it will support all databases very easily (5 or 6 dbs are supported now), very easy to support more and the OJB code would not have to change to support others. An example of how simple a join query would be is: SelectQuery sq = factory.getSelectQuery(); sq.addTable("X"); sq.addTable("Y"); sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2"); // add more conditions, order bys, group bys, etc if needed that's it then call sq.execute when ready. Travis ---- Original Message ---- From: Oleg Nitz <on...@ib...> Sent: 2002-05-30 To: obj...@li... Subject: Re: [OJB-developers] bug in sql generation Hi Matthew, I also don't see error in the generated SQL. Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server. I propose that we change join syntax for MS SQL Server to SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem. Also this will make OJB support earlier versions MS SQL Server, which didn't support SQL-92 syntax. Oleg Matthew baird wrote: > I think the appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM > keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150. > since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the > function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this: > (((SELECT .... > obviously those parens should go after the FROM clause. > the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't > working right) and I wanted to make sure this wasn't a symptom of another problem. > protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf) > { > int stmtFromPos = 0; > if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root) > { > stmtFromPos = buf.length(); // BRJ : point after FROM > } > if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root)) > { > buf.append(table.table); > buf.append(" "); > buf.append(table.alias); > } > if (!table.hasJoins()) > { > return; > } > for (Iterator it = table.iterateJoins(); it.hasNext();) > { > Join join = (Join) it.next(); > if (joinSyntaxType == SQL92_JOIN_SYNTAX) > { > if (join.isOuter) > { > buf.append(" LEFT OUTER JOIN "); > } > else > { > buf.append(" INNER JOIN "); > } > if (join.right.hasJoins()) > { > buf.append("("); > appendTableWithJoins(join.right, where, buf); > buf.append(")"); > } > else > { > appendTableWithJoins(join.right, where, buf); > } > buf.append(" ON "); > join.appendJoinEqualities(buf); > if (it.hasNext()) > { > buf.insert(stmtFromPos, "("); > buf.append(")"); > } > } > else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX) > { > if (join.isOuter) > { > buf.append(" LEFT OUTER JOIN "); > } > else > { > buf.append(" INNER JOIN "); > } > buf.append(join.right.table); > buf.append(" "); > buf.append(join.right.alias); > buf.append(" ON "); > join.appendJoinEqualities(buf); > appendTableWithJoins(join.right, where, buf); > } > else > { > buf.append(","); > appendTableWithJoins(join.right, where, buf); > if (where.length() > 0) > { > where.append(" AND "); > } > join.appendJoinEqualities(where); > } > } > } > The SQL being generated for my complex case is: > SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date, > A0.deactivation_date,A0.matrix_calc_version_uuid FROM > (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3 > ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid) > INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid) > ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid) > ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid > WHERE (((( ( ( A0.activation_date <= ? > AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) > AND A0.begin_effective_date <= ?) > AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) > AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) > AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ? > which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM. > thoughts? > --------------------------------- > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ Objectbridge-developers mailing list Obj...@li... https://lists.sourceforge.net/lists/listinfo/objectbridge-developers |
|
From: Oleg N. <on...@ib...> - 2002-05-30 16:05:31
|
Hi Matthew,
I also don't see error in the generated SQL.
Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server.
I propose that we change join syntax for MS SQL Server to
SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem.
Also this will make OJB support earlier versions MS SQL Server, which
didn't support SQL-92 syntax.
Oleg
Matthew baird wrote:
> I think the appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM
> keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.
> since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the
> function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this:
> (((SELECT ....
> obviously those parens should go after the FROM clause.
> the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't
> working right) and I wanted to make sure this wasn't a symptom of another problem.
> protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf)
> {
> int stmtFromPos = 0;
> if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
> {
> stmtFromPos = buf.length(); // BRJ : point after FROM
> }
> if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root))
> {
> buf.append(table.table);
> buf.append(" ");
> buf.append(table.alias);
> }
> if (!table.hasJoins())
> {
> return;
> }
> for (Iterator it = table.iterateJoins(); it.hasNext();)
> {
> Join join = (Join) it.next();
> if (joinSyntaxType == SQL92_JOIN_SYNTAX)
> {
> if (join.isOuter)
> {
> buf.append(" LEFT OUTER JOIN ");
> }
> else
> {
> buf.append(" INNER JOIN ");
> }
> if (join.right.hasJoins())
> {
> buf.append("(");
> appendTableWithJoins(join.right, where, buf);
> buf.append(")");
> }
> else
> {
> appendTableWithJoins(join.right, where, buf);
> }
> buf.append(" ON ");
> join.appendJoinEqualities(buf);
> if (it.hasNext())
> {
> buf.insert(stmtFromPos, "(");
> buf.append(")");
> }
> }
> else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
> {
> if (join.isOuter)
> {
> buf.append(" LEFT OUTER JOIN ");
> }
> else
> {
> buf.append(" INNER JOIN ");
> }
> buf.append(join.right.table);
> buf.append(" ");
> buf.append(join.right.alias);
> buf.append(" ON ");
> join.appendJoinEqualities(buf);
> appendTableWithJoins(join.right, where, buf);
> }
> else
> {
> buf.append(",");
> appendTableWithJoins(join.right, where, buf);
> if (where.length() > 0)
> {
> where.append(" AND ");
> }
> join.appendJoinEqualities(where);
> }
> }
> }
> The SQL being generated for my complex case is:
> SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date,
> A0.deactivation_date,A0.matrix_calc_version_uuid FROM
> (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3
> ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid)
> INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid)
> ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid)
> ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid
> WHERE (((( ( ( A0.activation_date <= ?
> AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL )
> AND A0.begin_effective_date <= ?)
> AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL )
> AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL )
> AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ?
> which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM.
> thoughts?
> ---------------------------------
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
|
|
From: Jakob B. <jbr...@ho...> - 2002-05-30 07:21:09
|
hi matthew,
the sql looks quite ok.=20
i first introduced stmtFromPos as an instance variable and i think oleg =
made it a local variable.=20
jakob
----- Original Message -----=20
From: Matthew baird=20
To: obj...@li...=20
Sent: Thursday, May 30, 2002 8:19 AM
Subject: [OJB-developers] bug in sql generation
I think the appendTableWithJoins routine has a bug with parenthesis. =
the routine is recursive to build out all the joins (with parens) upon =
entry, the stmtFromPos ( the position of the FROM keyword in the SQL =
being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and =
the table is the root. So let's say it's set to 150.
since I have a complex path, with lots of joins, I end up calling =
appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos =
is 0 because table is not root. Near the bottom of the function, you see =
a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has =
next, so the ( is inserted at stmtFromPos which is 0, so I end up with =
SQL that looks like this:
(((SELECT ....
obviously those parens should go after the FROM clause.=20
the solution is to make the stmtFromPos a parameter and pass it along. =
I wanted to get opinions on this fix, as the SQL being generated for our =
really complex case is still wrong (parens aren't working right) and I =
wanted to make sure this wasn't a symptom of another problem.
protected void appendTableWithJoins(TableAlias table, StringBuffer =
where, StringBuffer buf)
{
int stmtFromPos =3D 0;
if (joinSyntaxType =3D=3D SQL92_JOIN_SYNTAX && table =3D=3D =
root)
{
stmtFromPos =3D buf.length(); // BRJ : point after FROM
}=20
if (!(joinSyntaxType =3D=3D SQL92_NOPAREN_JOIN_SYNTAX && table =
!=3D root))
{
buf.append(table.table);
buf.append(" ");
buf.append(table.alias);
}=20
if (!table.hasJoins())
{
return;
}=20
for (Iterator it =3D table.iterateJoins(); it.hasNext();)
{
Join join =3D (Join) it.next();
if (joinSyntaxType =3D=3D SQL92_JOIN_SYNTAX)
{
if (join.isOuter)
{
buf.append(" LEFT OUTER JOIN ");
}
else
{
buf.append(" INNER JOIN ");
}
if (join.right.hasJoins())
{
buf.append("(");
appendTableWithJoins(join.right, where, buf);
buf.append(")");
}
else
{
appendTableWithJoins(join.right, where, buf);
}
buf.append(" ON ");
join.appendJoinEqualities(buf);=20
if (it.hasNext())
{
buf.insert(stmtFromPos, "(");
buf.append(")");
}
}
else if (joinSyntaxType =3D=3D SQL92_NOPAREN_JOIN_SYNTAX)
{
if (join.isOuter)
{
buf.append(" LEFT OUTER JOIN ");
}
else
{
buf.append(" INNER JOIN ");
}
buf.append(join.right.table);
buf.append(" ");
buf.append(join.right.alias);
buf.append(" ON ");
join.appendJoinEqualities(buf);
appendTableWithJoins(join.right, where, buf);
}
else
{
buf.append(",");
appendTableWithJoins(join.right, where, buf);
if (where.length() > 0)
{
where.append(" AND ");
}
join.appendJoinEqualities(where);
}
}
}=20
The SQL being generated for my complex case is:=20
SELECT =
A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0=
.activation_date,
A0.deactivation_date,A0.matrix_calc_version_uuid FROM=20
(((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN =
(statement_VAR A2 INNER JOIN PARTICIPANT A3=20
ON A2.participant_uuid=3DA3.participant_uuid) INNER JOIN =
HIERARCHY_OBJECT A5 ON A2.organization_uuid=3DA5.hierarchy_object_uuid)=20
INNER JOIN JOB A4 ON A2.job_uuid=3DA4.job_uuid) INNER JOIN (statement =
A6 INNER JOIN UNIT A7 ON A6.unit_uuid=3DA7.unit_uuid)=20
ON A2.statement_uuid=3DA6.statement_uuid) ON =
A1.statement_var_uuid=3DA2.statement_var_uuid)=20
ON A0.matrix_calc_version_uuid=3DA1.matrix_calc_version_uuid
=20
WHERE (((( ( ( A0.activation_date <=3D ?=20
AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL )=20
AND A0.begin_effective_date <=3D ?)=20
AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL )=20
AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL )=20
AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid =3D ?=20
which if you drop it into Query Analyzer or try to execute it against =
MS SQL Server, you get an error with not a lot of information, but it =
looks like it's right after the FROM.=20
thoughts?
-------------------------------------------------------------------------=
-----
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup |
|
From: Matthew b. <mat...@ya...> - 2002-05-30 06:46:26
|
I'm looking through the code trying to thoroughly grok how extents are supported, and I have to admit I'm a little bit puzzled :) I was wondering if someone out there could comment on my understanding of how things work:
Consider having two objects mapped to the same table, but they one doesn't inherit from the other. To be concrete, we have OperatingUnit and EnterpriseUnit.
If I execute an OQL query "select unit from com.somecompany.OperatingUnit" I would expect to get back all the operatingunits and none of the enterpriseunits. To support that, I've included an ojbConcreteClass mapping for each of the unit types.
OJB does it's thing and finally ends up calling the .next() on the RsIterator, during which OJB will call getObjectFromResultSet which will eventually call readObjectFrom
readObjectFrom then checks the ojbConcreteClass attribute, but can't really use this as a discriminator since it doesn't know what you asked for (for instance, the first row in the UNIT table is an enterprise unit, and I asked for operatingunits) so it tries to build the EnterpriseUnit using the query results of the OperatingUnit.
If EnterpriseUnit doesn't have the same fields mapped in the same order as OperatingUnit, building the new instance of the object will throw an exception. the exception makes its way back to the .next() routine where it is caught as an Exception and rethrown as a NoSuchElementException which is NOT caught in getCollectionByQuery
getCollectionByQuery is extent aware, and is ready to deal with having object materialized that don't match what was asked for
if ((itemClass.equals(candidate.getClass()))
|| (Proxy.isProxyClass(candidate.getClass()))
|| (candidate instanceof VirtualProxy))
is the important peice of code however, this code is unlikely to actually be called because Object candidate = i.next(); is probably going to throw a NoSuchElementException exception.
There are a couple other places where we load the object, check the type and try to materialize one of those objects. Using the new row[] array instead of the resultset means we have to be aware of the ordering of results (we can't get them by name) which causes a problem with 2 objects mapped to the same table with different order of mappings. I can live with that, but the materialization of an extent object with extra attributes failing because row[] doesn't contain all those values is not good.
If you grokked all that, here's a potential solution:
when creating the select SQL, if more than one object is mapped to the table, either load the union of all the fields that *could* be used dependent on object type, then store them by name in a hashmap (or come up with a lighter solution to get the columns by name). Then you would be guaranteed that the actual object could be created if need be. This solution would allow for people to query on base class and still get back base and sub classes all mapped to same table.
Other solution would be to somehow append the objConcreteClass discriminator to the where clause, but that would not allow for loading subclasses as base objects, so I don't think it's a good solution.
Comments?
---------------------------------
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup |
|
From: Matthew b. <mat...@ya...> - 2002-05-30 06:19:35
|
I think the appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.
since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this:
(((SELECT ....
obviously those parens should go after the FROM clause.
the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't working right) and I wanted to make sure this wasn't a symptom of another problem.
protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf)
{
int stmtFromPos = 0;
if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
{
stmtFromPos = buf.length(); // BRJ : point after FROM
}
if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root))
{
buf.append(table.table);
buf.append(" ");
buf.append(table.alias);
}
if (!table.hasJoins())
{
return;
}
for (Iterator it = table.iterateJoins(); it.hasNext();)
{
Join join = (Join) it.next();
if (joinSyntaxType == SQL92_JOIN_SYNTAX)
{
if (join.isOuter)
{
buf.append(" LEFT OUTER JOIN ");
}
else
{
buf.append(" INNER JOIN ");
}
if (join.right.hasJoins())
{
buf.append("(");
appendTableWithJoins(join.right, where, buf);
buf.append(")");
}
else
{
appendTableWithJoins(join.right, where, buf);
}
buf.append(" ON ");
join.appendJoinEqualities(buf);
if (it.hasNext())
{
buf.insert(stmtFromPos, "(");
buf.append(")");
}
}
else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
{
if (join.isOuter)
{
buf.append(" LEFT OUTER JOIN ");
}
else
{
buf.append(" INNER JOIN ");
}
buf.append(join.right.table);
buf.append(" ");
buf.append(join.right.alias);
buf.append(" ON ");
join.appendJoinEqualities(buf);
appendTableWithJoins(join.right, where, buf);
}
else
{
buf.append(",");
appendTableWithJoins(join.right, where, buf);
if (where.length() > 0)
{
where.append(" AND ");
}
join.appendJoinEqualities(where);
}
}
}
The SQL being generated for my complex case is:
SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date,
A0.deactivation_date,A0.matrix_calc_version_uuid FROM
(((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3
ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid)
INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid)
ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid)
ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid
WHERE (((( ( ( A0.activation_date <= ?
AND A0.deactivation_date > ? OR A0.deactivation_date IS NULL )
AND A0.begin_effective_date <= ?)
AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL )
AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL )
AND A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid = ?
which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM.
thoughts?
---------------------------------
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup |
|
From: Hoang, H. <Hai...@co...> - 2002-05-30 04:54:31
|
I've some code that read the repository.xml and generate java code for the following pattern from previous project (ATG) and now I want to port over to OJB and donate it to the ojb community. For example, if I have a class descriptor and these are classes will be generated: User (interface) UserImpl (class) UserManager (interface) UserManagerImpl (class) 1. I would like to know, which class parses the repository.xml for information purposes only (without validation), so I can use the information in the repository to generate code. 2. Can we possibly add a <interface.name>com.xyz.Permission</interface.name> tag to the class descriptor along with <class.name>com.xyz.PermissionImpl</class.name>? 3. Can I reuse the metadata package to store the parsed information from the repository.xml without required runtime classes? for example, if I have a <class.name>com.xyz.PermissionImpl</class.name> in one of my class descriptor and the file is not yet genereated, will this causes me problem? Thank you, Hai |
|
From: Chris G. <CGr...@de...> - 2002-05-30 00:52:46
|
Thomas, I'll finish that by the end of the weekend. Regards, Chris Greenlee > Hi all, > > I just checked in a new repository.dtd and all things related: > RepositoryXmlHandler, repository*.xml files, Descriptor classes, etc. > > some things are still missing: > > I did not yet touch the xml generation code. That is > RepositoryPersistore.writeToFile(...) does generate invalid xml! > Of course I will fix this soon. > > I did not rewrite the new ContentHandler classes yet. (Chris, if you > have time you may start now with patching them.) > > I did not yet provide documentation on the new grammar, but I hope to > work on this soon. > > during my changes today I overwrote some changes to metadata-related > stuff checked in by others (namely the RepositoryXmlHandler and the > repository_user.xml). we'll have to check this! > > A request: Are there any XSLT experts on this list? > I'd like to provide OJB users with a stylesheet that can transform > their old repository.xml into the new format. > Any volunteers? > > cheers, > > Thomas |
|
From: Thomas M. <tho...@ho...> - 2002-05-29 23:17:46
|
Hi all, I just checked in a new repository.dtd and all things related: RepositoryXmlHandler, repository*.xml files, Descriptor classes, etc. some things are still missing: I did not yet touch the xml generation code. That is RepositoryPersistore.writeToFile(...) does generate invalid xml! Of course I will fix this soon. I did not rewrite the new ContentHandler classes yet. (Chris, if you have time you may start now with patching them.) I did not yet provide documentation on the new grammar, but I hope to work on this soon. during my changes today I overwrote some changes to metadata-related stuff checked in by others (namely the RepositoryXmlHandler and the repository_user.xml). we'll have to check this! A request: Are there any XSLT experts on this list? I'd like to provide OJB users with a stylesheet that can transform their old repository.xml into the new format. Any volunteers? cheers, Thomas |
|
From: Thomas M. <tho...@ho...> - 2002-05-29 17:38:03
|
Hi again Jason, I hope you enjoyed your vacation! Jason van Zyl wrote: > Hi, > > I just got back from vacation and was wondering how people were feeling > about making the move to Jakarta? > In the meantime all developers that contributed major portions of the code consented on moving to Jakarta and to accept the apache licence on their code. There is only one minor issue with a few classes under GPL that we took from a different opensource project. I will talk to them to get these files under apache license as well. > At the very least I can start adding people to the system on the Jakarta > machine, get your accounts setup and get CVS working for each of you. > ok! > I have been talking with Geir (another Jakarta fellow) about the > creation of db.apache.org but I honestly don't want to wait three months > for it to be formed. Additionally the package names won't change the OJB > is moved within Jakarta so we don't really have to wait. > I agree! > Whatever you guys think just let me know and I'll get the ball rolling. > I'll prepare a 0.9 release of OJB this weekend. IMO we should start with renaming the package structure to org.apache.ojb and to provide the apache licence in all files just after this release. cheers, Thomas |
|
From: Mahler T. <tho...@it...> - 2002-05-29 15:04:36
|
Hi Joachim, There is already an implementation of the ODMG DMap interface. You can = use it to store arbitrary maps. As it's part of the ODMG implementation it's only save to use within = ODMG transactions. But it would be very easy to reuse the existing code to write a = persistent Map that works with the PersistenceBroker only. In ojb.odmg.collection you'll also find implementations for persistent collections. HTH, Thomas > -----Urspr=FCngliche Nachricht----- > Von: Joa...@tp... [mailto:Joa...@tp...] > Gesendet: Mittwoch, 29. Mai 2002 16:52 > An: obj...@li... > Betreff: [OJB-developers] Storing Maps >=20 >=20 > Hy, >=20 > we'd need to store a map in the Database. It seems that this is not=20 > implemented natively. Is there any support planned for this? It seems = > easily possible to make a work-arround by using a custom=20 > map-work-alike=20 > that is mapped like any other m:n association, but it would be more=20 > comfortable if we could just use the default Map implementations. >=20 > regards > Joachim Sauer >=20 > _______________________________________________________________ >=20 > Don't miss the 2002 Sprint PCS Application Developer's Conference > August 25-28 in Las Vegas -- = http://devcon.sprintpcs.com/adp/index.cfm >=20 > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers >=20 |
|
From: <Joa...@tp...> - 2002-05-29 14:52:23
|
Hy, we'd need to store a map in the Database. It seems that this is not implemented natively. Is there any support planned for this? It seems easily possible to make a work-arround by using a custom map-work-alike that is mapped like any other m:n association, but it would be more comfortable if we could just use the default Map implementations. regards Joachim Sauer |
|
From: Mahler T. <tho...@it...> - 2002-05-29 11:09:54
|
Hi Andrew, This is very easy to fix! If you are using OJB in ODMG mode just set all <auto.update> and <auto.delete> entries to false. This avoids the double insertion that you noticed already. This is documented in Tutorial3.html: http://objectbridge.sourceforge.net/tutorial3.html#cascading hth, Th > -----Urspr=FCngliche Nachricht----- > Von: Andrew Pietsch [mailto:api...@op...] > Gesendet: Dienstag, 28. Mai 2002 11:58 > An: OJB Developers mailing list > Betreff: [OJB-developers] Collections problem.. >=20 >=20 > Howdy, >=20 > I seem to be having a bit of trouble storing collections. =20 > I've got a Site object that contains > an ArrayList of CollectionPoint objects. Each=20 > CollectionPoint also has reference back to it's > parent Site object. When I create a Site and add a single=20 > CollectionPoint to it and persist the=20 > Site, it seems that the CollectionPoint object is being=20 > inserted twice into the database resulting > in a primary key violation. I've narrowed it down a little=20 > and it seems that the ObjectEnvelopeTable > iterates over both the Site and CollectionPoint calling=20 > commit on both. The problem seems to be that=20 > while persisting the Site, the CollectionPoint is persisted=20 > via storeCollections(..), and=20 > is then persisted again by the ObjectEvelopeTable. >=20 > This is my first OJB project so I'm not sure if I've screwed=20 > things up. I checked out of cvs just a=20 > few minutes ago so I should be the latest source. I've=20 > attached the output trace and my respositry.xml > just in case I've done something crazy.. >=20 > Also, I was just wondering if references could be implemented=20 > (theoretically speaking) without using a > distinct ID field in the referring object, ie My=20 > CollectionPoint must have a Site attribute and a siteId=20 > attribute, I'd love to ditch the siteId. Please feel free to=20 > call me an idiot if I've overlooked the > obvious.. I just thought it would be a nice to have.. (c: >=20 > Keep up the excellent work!! >=20 > Thanks > Andrew >=20 |
|
From: Jason v. Z. <jv...@ze...> - 2002-05-29 10:47:14
|
Hi, I just got back from vacation and was wondering how people were feeling about making the move to Jakarta? At the very least I can start adding people to the system on the Jakarta machine, get your accounts setup and get CVS working for each of you. I have been talking with Geir (another Jakarta fellow) about the creation of db.apache.org but I honestly don't want to wait three months for it to be formed. Additionally the package names won't change the OJB is moved within Jakarta so we don't really have to wait. Whatever you guys think just let me know and I'll get the ball rolling. -- jvz. Jason van Zyl jv...@ap... http://tambora.zenplex.org |
9 messages has been excluded from this view by a project administrator.
