[OFBiz] Dev - ofbiz sql(whee!) (very long)
Locked
[OFBiz] Dev - ofbiz sql(whee!) (very long)
 
|
So, as talked about at the last users' conference, here's a teaser.
I have code that can parse SQL-like strings and files, and product EntityCondition objects. For example: == InputStream in = StringBufferInputStream( "CONDITION foo field1 = 'bar' OR field2 = 'baz' AND field3 " + "IN (1, 2, 3); CONDITION bar id = ?id;"; SqlScript sqlScript = new SqlScript(inputStream); SqlScriptVisitor visitor = MakeCondition(); SimpleNode node = sqlScript.NamedConditions(); Map namedConditions = (Map) node.jjtAccept(visitor, null); == That returns a map of name -> condition. There is support for named parameters, which make use of the freeze support I added last year, and a ThreadLocal, to convert the condition to a real instance. The above pseudo-code(which is very close to the real code I have) can cache the parsed EntityCondition object, and only needs to call freeze() if parameters are being expanded. Here's the api I'd like to see: == condition = EntityConditionUtil.getCondition(string); condition = EntityConditionUtil.getCondition(string, map); condition = EntityConditionUtil.get(url, name); condition = EntityConditionUtil.get(url, name, map); == The url forms could use UtilCache to store the parsed file. The variants that take a map will call freeze() to create a real condition. Here's what I have done. * jjtree/javacc grammar for conditions(actually, full sql, see below) * implementation of SqlScriptVisitor that produces EntityCondition objects * new EntityConditionValue classes: * NamedParameter: placeholder for ?name in condition strings * ConstantValue: NamedParameter replaces itself with this during freeze. All that's needed is a url->inputstream mapper(trivial), storage in UtilCache, and the above wrapping code in EntityConditionUtil. Ok, now on to the next big feature. The jjtree grammar I have is not just for conditions. It can do full sql. And by full script, I mean CREATE TABLE, SELECT, INSERT, etc. To test this system out, I wrote an xsl stylesheet, that converts entitymodel.xml files into sql. I then did it over all such files in ofbiz. I then wrote a visitor that walks the parsed tree, and produces standard ModelEntity/ModelViewEntity objects. With counting in place, I get identical entity/view counts as the standard ModelReader stuff. Initially, this may look like a bunch of busy work. Why should entitymodel be done in sql, instead of the current working form? Well, if you were only concerned with plain tables, and not views, you would be correct. However, views are much more powerful when you can make use of the full sql syntax. This includes nested subselects in from items, complex nested inner/outer joins, and mathematical operations everywhere. The existing ModelViewEntity and sql generating code in in SqlJdbcUtil do not support such advanced features. This system can also be a replacement for DynamicViewEntities, which are rather cumbersome to work with. You already need to understand sql to write a view, and you also need to understand how to use the DynamicViewEntities api. With this new system, you just need to know sql. This last viewpoint also applies to ModelViewEntity xml. Why bother learning a second xml language for defining views, when sql will do just fine? So now you may be wondering why I wrote all this. You may ask, isn't the current system just fine? Well, while the current system does work, and is fairly well tested, it has several shortcomings, which have now permeated themselves thruout the code base, and the ofbiz community. As already mentioned, existing view entities are limited in their features. Because of the limit syntax, java code gets written that ends up doing several round trips to the database(or the entity-cache stuff I wrote), and recombing data in memory. However, it's been my experience, that a properly written query, with proper indexing, can be extremely fast. Let me give you an example. When I wrote the entitycache stuff and submitted it over a year ago, it was due to realized performance issues with the existing condition/caching stuff in ofbiz. This was due to the job I was working on at the time having some rather large databases, and some complex data arrangements. The reverse-dependency cache clearing code was an attempt to solve this, by clearing views when a normal entity was updated. It mostly works, with some very small border conditions(some have been fixed since then). The views that I was using that drove this development, have 10 members, but only 8 tables. 2 of those tables are joined twice. Additionally, a feature I wrote, but never submitted, was the ability to restrict views with conditions in the xml. I never liked my implemetation, so never sent it. After I got the cache clearing stuff done, and before I sent it to ofbiz, I realized that the complex view I had done was not performing well in the database. This was due to my initial lack of proper indexing. The existing deployed site now doesn't even cache that query *at all*, and the page it's one is the one of the main pages for the site(gives users a transcript of all certification tests they have taken, grouped into categories, with red/yellow/green lights signifying whether they have passed, or their cert is about to expire). It was at this point that I decided to trust the database, and let it do all the work. As much as I could, I tried to create a view, and not have any loop code at all. Once you have to do multiple queries in a loop(even with caching), performance falls about. So, now jump forward a year. The current projects we(brainfood) are working on are based on a new wiki system we've developed. One of the goals for this system is to simplify things as much as possible; to lower the barrier for entry when creating websites. Wiki markup is one of the features we implemented to support this end goal. Ean(my boss) wrote a very simple, but very easy to use, SQLUtil tool. Give it an ofbiz connection name(ie, "localpostgres"), a raw sql query string, and it will return you a list of maps, one map for each row in the database. Writing sql is much simpler than writing the xml stuff, and this tool let him change the sql easily at runtime. However, using this system requires one to know the entityname/fieldname mapping syntax, which is yet another thing to learn. It also had no caching whatsoever. One of the tools we integrated for this wiki system, is velocity. This is simpler(main design goal) than freemarker, and faster(freemarker copies object graphs when placing them into the template context). It uses jjtree to parse it's templates, and uses the AST(abstract syntax tree) at runtime to evaluate everything. This allowed me to see a working jjtree/javacc setup, and helped me to learn that tool. So, now that we(brainfood) have a need for a sql parser(to speed up SQLUtil, and to make things easier to work with), and that I know how to use jjtree/javacc, this feature can actually be implemented. Ok, enough rambling. Let me quickly explain the attachments. SqlScript.jtt: This is the sql grammar file. Fairly standard set of options needed to parse that into the SqlScript.jj file, then produce the SqlScript.java file from that. There are 2 top-level entry points: SQLScript, and NamedConditions. Both expect to parse a full file. The former will handle CREATE TABLE, CREATE VIEW, and CREATE INDEX. The latter handles the CONDITION name <condition> stuff. Both output an AST of the parsed data. The AST produced is meanted to be accessed by using an implementation of SqlScriptVisitor. I have versions for parsing CREATE TABLE, CREATE VIEW, and CONDITION trees; however, they require some minor changes to existing ofbiz code, so are not yet included here. entitymodel.xslt: XSL stylesheet that can convert entitymodel.xml files into sql files. Used to automatically convert everything, for testing. model.sql.gz: Example file containing all existing ofbiz entity models converted to sql. My development plan for integrating this, is to not replace anything, at first. I want this new code to exist alongside the existing sql/entitymodel/ModelEntity stuff. This way, no unknown problems occur. I'll be modifying ModelReader to be an interface, then having different implementations, one for the xml, and one for the sql forms. However, even at this step, no new features will exist. I'll then make a third ModelReader, that will not restrict what you can do for CREATE VIEW. This will replace the existing ModelViewEntity, and SqlJdbcUtil. It'll most likely just keep the post-processed AST around at runtime, for generating the actual sql. I don't have a time table for completly any of these steps. I can, however, probably get the condition parsing stuff integrated fairly quick, as that is non-invasive, and non-disrupting. ps: sorry for the length, but it's hard to shrink this down. |
SqlScript.jjt (46K)
Re: Dev - ofbiz sql(whee!) (very long)
|
|
Re: Dev - ofbiz sql(whee!) (very long)
|
|
Re: Dev - ofbiz sql(whee!) (very long)
|
|
| Free forum by Nabble | Edit this page |