CreateTableQuery quivalent in JOOQ
Lewis John Mcgibbney
As part of a much larger task of migrating all of our sql-store [1] logic to the JOOQ API, I'm in the process of replacing a small part of our legacy (LGPL licensed) code with the JOOQ equivalent.
Before I start, a reference to the class in question can be found below here [2].
OK, with that said, basically I need to find a replacement for CreateTableQuery [3], I understand that I should be accessing the implementation through o.j.util.Factory, however (due to my unfamiliarity with the JOOQ API at this stage) I can only locate delete, inset, results, selects, simple selects & updates... oh and of course query and query part's :0)
I need to replace/alternative getter and setter implementations which (privately) set a column constraint, which can then used when we createSchema() in our SqlStore.
As I'm picking the JOOQ API up at a reasonably random stage rather than starting at the beginning (which I'll be doing in a couple of weeks), I would really appreciate any help anyone can throw in my direction at this stage. It's early days, but it looks like I'll be around for a while.
Regards and thanks in advance
Lewis
[1] https://svn.apache.org/viewvc/gora/trunk/gora-sql/
[2] https://svn.apache.org/viewvc/gora/trunk/gora-sql/src/main/java/org/apache/gora/sql/store/SqlStore.java?view=markup
[3] http://openhms.sourceforge.net/sqlbuilder/apidocs/com/healthmarketscience/sqlbuilder/CreateTableQuery.html
Lukas Eder
> OK, with that said, basically I need to find a replacement for
> CreateTableQuery [3], I understand that I should be accessing the
> implementation through o.j.util.Factory, however (due to my unfamiliarity
> with the JOOQ API at this stage) I can only locate delete, inset, results,
> selects, simple selects & updates... oh and of course query and query part's
> :0)
As I mentioned to you before on the GORA news groups, jOOQ currently
does not support DDL statements. I found the relevant mail here:
http://mail-archives.apache.org/mod_mbox/gora-dev/201201.mbox/%3CCAB4ELO5y666xTNnBEyQOjYp...@mail.gmail.com%3E
Adding support for DDL statements is on the jOOQ roadmap:
https://sourceforge.net/apps/trac/jooq/ticket/883
I've found a sample OSS project on Google Code using jOOQ for DDL:
http://lukaseder.wordpress.com/2011/10/27/jooq-in-the-wild/
Currently, your only option is to do something like this:
factory.execute(
"CREATE TABLE IF NOT EXISTS backups (" +
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
"run_time DATETIME NOT NULL, " +
"num_entries INT NULL" +
")");
As you can see, this makes use of MySQL syntax (AUTO_INCREMENT).
Unfortunately, I've had no idea yet, how to formalise even simple DDL
features into a common API.
> I need to replace/alternative getter and setter implementations which
> (privately) set a column constraint, which can then used when we
> createSchema() in our SqlStore.
Can you elaborate? I'm not sure what you mean...
> As I'm picking the JOOQ API up at a reasonably random stage rather than
> starting at the beginning (which I'll be doing in a couple of weeks), I
> would really appreciate any help anyone can throw in my direction at this
> stage. It's early days, but it looks like I'll be around for a while.
Any questions welcome!
Cheers
Lukas
Lewis John Mcgibbney
On Feb 19, 9:58 am, Lukas Eder <lukas.e...@gmail.com> wrote:
> Adding support for DDL statements is on the jOOQ roadmap:https://sourceforge.net/apps/trac/jooq/ticket/883
>
> I've found a sample OSS project on Google Code using jOOQ for DDL:http://lukaseder.wordpress.com/2011/10/27/jooq-in-the-wild/
in the blog post, the project is now hosted over @ GitHub [0]
> As you can see, this makes use of MySQL syntax (AUTO_INCREMENT).
> Unfortunately, I've had no idea yet, how to formalise even simple DDL
> features into a common API.
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?
> > I need to replace/alternative getter and setter implementations which
> > (privately) set a column constraint, which can then used when we
> > createSchema() in our SqlStore.
>
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?
> Any questions welcome!
As always a great help,thank you
Lewis
[0] https://github.com/blizzy78/blizzys-backup/
Lukas Eder
> 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