Rafi and I have been thinking about standards to apply within the ACS to make such porting efforts described here easier. I read the guidelines page and found it interesting. I'll be interested to follow any thing you guys write about porting DDL, queries, and the like. Any guidelines you want us to follow in writing Oracle SQL would be welcome. You guys know both PostgreSQL and Oracle very well, but we don't know PG too well. Any requirements you pass along to be more SQL92 compliant are appreciated.
For a number of cases, you're doing simple string transformations. It might be worth your investing in constructing a tool to do this automatically. Rafi recently showed me the power of JavaCC for creating parsers easily in Java. You can create an Oracle SQL parser fairly easily, and then write a back end for the parse tree to work properly with PostgreSQL. Actually, it looks like there is already an Oracle SQL grammar at the JavaCC grammar repository.
Once you get the tool working and all of the knowledge you guys have on porting statements encoded programmatically, you can translate all of the Oracle SQL we spit out trivially.
I'm also interested in a tool for specifying object models in a higher-level language than Oracle DDL + PL/SQL than the ACS currently uses. It sounds like you guys are going to take a different approach with PG's object-relational features which probably makes sense (i don't know enough to say). Again, if we worked out a common tool for creating objects, with us maintaining an Oracle back-end and you guys maintaining a PG back-end, we'd both save a lot of time. You could also have it configured to handle stuff like varchar(4000) => text automatically.
Another great benefit is that if you later want to change your transformation rules, you can, and just rerun the tool, rather than ask a bunch of people to grovel over the code again.
What do you think?
