[OFBiz] Dev - entity engine sql(using javacc)

[Adam Heath-2]

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

[Adam Heath-2]

On Sun, 4 Sep 2005, Adam Heath wrote:

ps: that was only 12 hours of work(if that).

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Adam Heath-2]

On Sun, 4 Sep 2005, Adam Heath wrote:

> [snip]

Well, I have condition parsing working now.  A snippet of a full sql query is
included at the end.

The code will parse any sql condition, and produce a real EntityCondition
object.  Positional parameters are a new type of EntityConditionValue.

I am planning on making a cache of this, so that the reparse doesn't have to
happen all the time.

Here's how I think this might be used:

==
EntityCondition condition = EntityCondition.parse("((webSiteId = ?1 OR
(webSiteId = ?2 AND hostName LIKE '%.com')) OR foo BETWEEN ( 'a', 'b'
) OR foo IS NULL OR foo IS NOT NULL OR foo < '1' OR foo <= '1' OR foo > '1' OR
foo >= '1' OR foo = '1' OR foo <> '1' OR foo LIKE '%.com' OR L
OWER(foo) = LOWER('bar'))");
ArrayList params = new ArrayList(2);
params.add("first");
params.add("second");
PositionalParameter.setParams(params);
List values = delegator.findByConditionCache(entityName, condition, null,
null);
PositionalParameter.clearParams();
==

This makes use of a ThreadLocal in PositionalParameter, and the freeze feature
when the entity engine creates an actual sql query, to convert the positional
params into real values.  You can set a positional parameter to null as well,
and that'll work fine.

At this point, I have all that I need to support a sql definition language,
but still have it play nice with the rest of the entity model.  I may not get
it done tomorrow, but I don't forsee any hurdles at this point.

ps: the sql parser doesn't like the IN operator, and the output likes to quote
plain numbers.  The latter shouldn't be hard to fixed(they are parsed as
strings or numbers, I'm just not checking).  The former a bit more difficult,
but still easy.  Also, I see that no one actually is using the BETWEEN
operator anywhere in ofbiz.  The sql string it produces is buggy, as evident
by the code above.


==
WHERE
        (
                a.webSiteId = ?1
                OR
                a.webSiteId = ?2
                AND
                a.hostName LIKE '%.com'
        )
        OR
        a.foo BETWEEN 'a' AND 'b'
        OR
        a.foo IS NULL
        OR
        a.foo IS NOT NULL
        OR
        a.foo < 1
        OR
        a.foo <= 1
        OR
        a.foo > 1
        OR
        a.foo >= 1
        OR
        a.foo = 1
        OR
        a.foo != 1
--      OR
--      a.foo IN ( 1, 2, 3 )
--      OR
--      a.foo NOT IN ( 1, 2, 3 )
        OR
        a.foo NOT LIKE '%.com'
        OR
        LOWER(a.foo) = LOWER('bar')

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev