Posted by
Adam Heath-2 on
Sep 04, 2005; 10:13pm
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Dev-entity-engine-sql-using-javacc-tp165963.html
So, I've been working on something I think you guys will find most
interesting. At the user conference back in July, it was discussed that
something was needed to parse sql, into some form of object graph. Well, I've
gotten something that is very close, and I've only spent a day on it.
This is based on the oracle sql floating around, extended to support LEFT
JOIN, and a few AS fixes in various places.
It currently enforces only AND operators in the ON clauses(which is all that
ModelViewEntity supports). It only handles field comparisons in ON clauses as
well(not enforced yet). It also doesn't handle nested joins: ie, something
like this snippet:
JOIN ProductCategory pc ON ... LEFT JOIN (ProductCategoryMember pcm JOIN
Product p ON pcm.productId = p.productId) ON pc.productCategoryId =
pcm.productCategoryId
The sql parser itself can handle all these constructs. I have to detect these
extended uses in my visitor code, however, and abort if found. I'm wondering
of some of these deficiencies could be fixed, either by modifying the entity
engine, or just using this code I've written to directly output sql(I can make
it query the tablename/columnname from the model).
Personally, I find this form much easier to deal with, then with the obtuse
xml format. I could even extend this to support CREATE TABLE and CREATE
INDEX.
I'm thinking I may end up bypassing the sql generator in the entity engine,
and instead output and query directly. Then, modifying the cache code to
still work with this more advanced query language.
ps: I haven't implemented WHERE, ORDER BY, or GROUP BY, nor functions on
select columns. None of these are all that hard, however.
SELECT
b.fromDate AS pscFromDate,
b.thruDate AS pscThruDate,
b.sequenceNum AS pscSequenceNum,
c.fromDate AS pccFromDate,
c.thruDate AS pccThruDate,
c.sequenceNum AS pccSequenceNum,
d.productCategoryId,
d.description AS categoryDescription,
d.longDescription AS categoryLongDescription,
e.fromDate AS pcmFromDate,
e.thruDate AS pcmThruDate,
e.sequenceNum AS pcmSequenceNum,
f.productId,
f.productName,
f.description AS productDescription,
f.longDescription AS productLongDescription
FROM
WebSite a JOIN ProductStoreCatalog b ON
a.productStoreId = b.productStoreId
JOIN ProdCatalogCategory c ON
b.prodCatalogId = c.prodCatalogId
JOIN ProductCategory d ON
c.productCategoryId = d.productCategoryId
LEFT JOIN ProductCategoryMember e ON
d.productCategoryId = e.productCategoryId
LEFT JOIN Product f ON
e.productId = f.productId
DynamicViewEntity dve = new DynamicViewEntity();
dve.addAlias("b", "fromDate", "pscFromDate", null, null, null, null);
dve.addAlias("b", "thruDate", "pscThruDate", null, null, null, null);
dve.addAlias("b", "sequenceNum", "pscSequenceNum", null, null, null, null);
dve.addAlias("c", "fromDate", "pccFromDate", null, null, null, null);
dve.addAlias("c", "thruDate", "pccThruDate", null, null, null, null);
dve.addAlias("c", "sequenceNum", "pccSequenceNum", null, null, null, null);
dve.addAlias("d", "productCategoryId", null, null, null, null, null);
dve.addAlias("d", "description", "categoryDescription", null, null, null, null);
dve.addAlias("d", "longDescription", "categoryLongDescription", null, null, null, null);
dve.addAlias("e", "fromDate", "pcmFromDate", null, null, null, null);
dve.addAlias("e", "thruDate", "pcmThruDate", null, null, null, null);
dve.addAlias("e", "sequenceNum", "pcmSequenceNum", null, null, null, null);
dve.addAlias("f", "productId", null, null, null, null, null);
dve.addAlias("f", "productName", null, null, null, null, null);
dve.addAlias("f", "description", "productDescription", null, null, null, null);
dve.addAlias("f", "longDescription", "productLongDescription", null, null, null, null);
dve.addMemberEntity("b", "ProductStoreCatalog");
{
ArrayList mkmap = new ArrayList();
mkmap.add(new ModelKeyMap("productStoreId", "productStoreId"));
dve.addViewLink("a", "b", false, mkmap);
}
dve.addMemberEntity("c", "ProdCatalogCategory");
{
ArrayList mkmap = new ArrayList();
mkmap.add(new ModelKeyMap("prodCatalogId", "prodCatalogId"));
dve.addViewLink("b", "c", false, mkmap);
}
dve.addMemberEntity("d", "ProductCategory");
{
ArrayList mkmap = new ArrayList();
mkmap.add(new ModelKeyMap("productCategoryId", "productCategoryId"));
dve.addViewLink("c", "d", false, mkmap);
}
dve.addMemberEntity("e", "ProductCategoryMember");
{
ArrayList mkmap = new ArrayList();
mkmap.add(new ModelKeyMap("productCategoryId", "productCategoryId"));
dve.addViewLink("d", "e", true, mkmap);
}
dve.addMemberEntity("f", "Product");
{
ArrayList mkmap = new ArrayList();
mkmap.add(new ModelKeyMap("productId", "productId"));
dve.addViewLink("e", "f", true, mkmap);
}
dve.addMemberEntity("a", "WebSite");
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev