I'm not sure what you were reading, but it seems pretty explicit in Rogue Wave doing
joins.
ex: Inner join
RWDBTable purchases = myDbase.table("purchase"); //1
RWDBTable videos = myDbase.table("videos"); //2
RWDBSelector select = myDbase.selector();
select << purchases["orderNum"] //3
<< purchases["supplierID"] << videos["title"]; //4
select.where(purchases["videoID"] == videos["ID"]);
the myDbase.table seems pretty explicitly definining the tables.
And now for an outer join:
RWDBTable employee = myDbase.table("emp");
RWDBTable dept = myDbase.table("dept");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< employee["deptno"] << dept["deptno"] << dept["dname"];
RWDBJoinExpr join = rwdbRightOuter(employee, dept); //1
join.on(employee["deptno"] == dept["deptno"]); //2
selector.from(join);
Seems pretty explicit there too, no? I don't think it's possible not to be explicit
on how you want to join a table.
The problem with Rogue Wave is that it's database dependent, for example, doing an
Oracle style join, you would not use the above way, you would use:
selector.where(purchase["videoID"].leftOuterJoin(videos["ID"])); //1
I do agree with you on the syntax for creating comparisons, that would be very nice.
But since it can't happen, you have to use something else. In crossdb, we opted for
the LESS_THAN_OR_EQUAL, but this could easily be changed to something like LTE which
would make things look much nicer.
Travis
---- Original Message ----
From: Jeff Schnitzer <[EMAIL PROTECTED]>
Sent: 2002-04-29
To: Turbine Torque Developers List <[EMAIL PROTECTED]>
Subject: RE: Why I don't like Criteria (was Re: Crossdb and Criteria Patch)
Here's my reason for not liking Criteria (and where CrossDB falls down
as well): It doesn't handle joins.
Given tables/objects like this:
department (id, name, location)
employee (id, name, depId)
I want to be able to select all employees who are in San Francisco like
this:
SELECT e.*
FROM employee e, department d
WHERE e.depId = d.id AND d.location = 'San Francisco';
Criteria doesn't try, and CrossDB requires the FROM clause to be
explicitly defined - not good if Torque's doSelect should only take the
equivalent of a WhereClause.
I like the way RogueWave DBTools.h++ handles this. It also provides a
very elegant syntax for constructing the expressions; no annoying
constants like LESS_THAN_OR_EQUAL to work with.
The important characteristic of DBTools is that they have a Column
object which encapsulates the table it comes from and the column
identifier. This Column object is an Expr:
class Column extends Expr {...}
class Expr
{
public BooleanExpr greaterThan(Expr other)
public BooleanExpr equals(Expr other)
public BooleanExpr equals(String constant)
public BooleanExpr and(BooleanExpr other)
public Expr cast(int sqlType)
// etc...
}
Using this system the join query I mentioned above would look something
like this:
Expr emp_depId = EmployeePeer.getColumn("depId");
Expr dep_id = DepartmentPeer.getColumn("id");
Expr dep_location = DepartmentPeer.COL_LOCATION; // generated constant
List results = EmployeePeer.doSelect(
(emp_depId.equals(dep_id)).and(dep_location.equals("San
Francisco"))
);
I'll have to admit that this may be the first time I have actually
wished Java supported operator overloading :-)
To crank up the difficulty a bit more, I would also like to see a
mechanism that supports self-joins. For example, given the basic tree
structure in the table:
node (id, parentId, name)
I would like to be able to get all the grandchildren of a node:
SELECT n2.*
FROM node n1, node n2
WHERE n1.parentId = 5 AND n1.id = n2.parentId
This becomes more complicated. It could be done like this:
Table n2 = NodePeer.getJoinInstance();
// The normal getColumn() on NodePeer always refers to an
// implicit first join instance.
List results = NodePeer.doSelect(
NodePeer.getColumn("parentId").equals(5)
.and(NodePeer.getColumn("id").equals(n2.getColumn("parentId")))
);
One nice thing about this approach is that it's very easy to perform
joins with tables which are not maintained by Torque... a Table can be
created from any named table.
Does this make sense? Am I alone in thinking that joins are a
critically important part of any query system? Or did I just spend too
many years writing ROLAP code? (Actually, I already know the answer to
that last one :-)
If this is at all intriguing, the "cool chapter" from the RogueWave
documentation is this one:
http://www.roguewave.fr/support/docs/dbtug/5-2.cfm
Jeff Schnitzer
[EMAIL PROTECTED]
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
