Hi Lewis, > Thanks for this resource. I don't know if you wish to update the links > in the blog post, the project is now hosted over @ GitHub [0]
Updated, thanks for the hint! > So now that we are in discussion, what technical problems/hurdles are > you experiencing when trying to represent DDL within JOOQ? As you > mention, the example above uses MySQL syntax, in your experience does > this differ significantly from SqlStore to SqlStore? >From what I've seen, GORA will not run into most of the challenges I was facing when analysing DDL (e.g, table partitioning, index-organised tables, temporary/object/xml tables, etc). However, you should be aware of how you want to define your identity columns and primary key constraints. I just now published a blog post elaborating on the issue: http://lukaseder.wordpress.com/2012/02/19/subtle-sql-differences-identity-columns/ Other than that, most databases use the same names for data types. Some differences: - Oracle prefers using VARCHAR2 over VARCHAR - Oracle doesn't know TINYINT, SMALLINT, INT, BIGINT. It just knows NUMBER and DECIMAL - Oracle doesn't know a TIME data type - Oracle's DATE data type is in fact also a TIMESTAMP - Oracle doesn't know a BOOLEAN data type. Use NUMBER(1) instead - Sybase ASE and MySQL call their TIMESTAMP data types DATETIME - Some databases call it BOOLEAN, others call it BIT If you run into concrete issues, feel free to ask! Since you're trying to avoid the depths of SQL with GORA, I think these will be the only problems you might be facing, before jOOQ implements DDL. > What I was attempting to explain here was that when we are trying to > create a schema (if one doesn't already exist), we want to execute the > CREATE TABLE query then specify various column constraints describing > the nature of the table. We currently have private methods for getting > and setting Column Constraints which also need to be adapted to drop > the offending code... Does this make sense? >From what I have seen in SqlStore, I'm guessing that by "specifying column constraints" you mean setting a primary key on your tables? jOOQ-generated code maintains schema-meta information. For instance, if a table has a primary key (or at least one unique key), then this is indicated by the UpdatableTable.getMainKey() method: http://www.jooq.org/javadoc/latest/org/jooq/UpdatableTable.html#getMainKey%28%29 Similarly, you can introspect IDENTITY columns, as described before: http://www.jooq.org/javadoc/latest/org/jooq/Table.html#getIdentity%28%29 And foreign key constraints: http://www.jooq.org/javadoc/latest/org/jooq/Table.html#getReferences%28%29 I can see the following workflow for you when developing: 1. Create a development database, locally 2. Use jooq-codegen to generate source code from it 3. Re-write your SqlStore to introspect jOOQ-generated source code and generate DDL from it when needed 4. Query your database using the generated source code Hope this helps. Cheers Lukas
