JOOQ for Gora

[Lewis John Mcgibbney <le...@gmail.com>]

Hi Lukas,

This is great that you contacted us, thank you. I'm Cc in gora-dev@ because
I think integrating JOOQ API into Gora is something that will inevitably
affect the entire gora-sql module and subsequently we will make better
decisions for the project as a community.

WRT the SQL module, this is not the primary focus for Apache Gora, that
being said it is extremely important for us to use implementations that do
the best job available. Basically, our original reasoning behind removal of
the sqlbuilder library is described within the Jira issue you replied to,
with this in mind I discovered JOOQ and am personally drawn towards it due
to it:
-released on ASL 2.0
- supporting a myriad of SQL stores

I wonder if you can explain a bit more (either on this thread or on the
issue) about what benefits JOOQ would provide if we were to use it within
Apache Gora?

Thanks again for getting in touch.

Best

Lewis

-- 
*Lewis*

Re: JOOQ for Gora

[Lukas Eder <lu...@gmail.com>]

Hello Lewis,

> WRT the SQL module, this is not the primary focus for Apache Gora, that
> being said it is extremely important for us to use implementations that do
> the best job available.

I can perfectly understand that.

> Basically, our original reasoning behind removal of
> the sqlbuilder library is described within the Jira issue you replied to,
> with this in mind I discovered JOOQ and am personally drawn towards it due
> to it:
> - released on ASL 2.0
> - supporting a myriad of SQL stores
>
> I wonder if you can explain a bit more (either on this thread or on the
> issue) about what benefits JOOQ would provide if we were to use it within
> Apache Gora?

I am now looking at various gora artefacts that I have downloaded from
Maven and from SVN.

Here are some findings:
----------------------------------
- You're currently using SqlBuilder for simple DDL statements (CREATE
TABLE..., DROP TABLE...).
- Apart from that, you're hardly using SqlBuilder, except DbTable as a
table name and primary key store. Most SQL statements are implemented
outside of SqlBuilder in "gora-sql", which contains a couple of
home-grown wrapper objects for various types of statements: SELECT,
DELETE, MERGE

In essence, removing SqlBuilder from Gora is a task that can be done
independently from adding jOOQ (or any other library), as you have
little dependencies on SqlBuilder. This seems to be your primary goal.
Your secondary goal is having broad support for a variety of SQL
stores.

How can jOOQ fit in?
----------------------------------

- You need rather dynamic SQL statements, as you're building a general
solution for persisting any type of object in a table. Standard CRUD
(e.g. as in JPA / Hibernate) cannot be used easily for that.
- jOOQ aims at creating a standardised SQL dialect implemented as a
domain specific language in Java that looks like this:
http://www.jooq.org/sql-as-seen-by-jooq.svg. This jOOQ-SQL dialect
takes the best of what RDBMS offer (e.g. MySQL's INSERT .. ON
DUPLICATE KEY UPDATE, SQL:2003's MERGE statements), and simulates such
statements in other dialects. You have started doing the same thing
with "org.apache.gora.sql.statement.InsertUpdateStatementFactory", but
getting this right is very time-consuming if you want to support many
databases. jOOQ will increase this compatibility in the future, and
thus takes the SQL work away from you.
- jOOQ currently supports 12 databases and runs an odd 130 with 1000+
queries against all of them in integration tests.
- jOOQ does not yet support DDL, but it's on the roadmap:
https://sourceforge.net/apps/trac/jooq/ticket/883
- Compared to SqlBuilder or your current SQL wrappers, jOOQ can also
be used to execute queries.
- jOOQ has only optional dependencies

A usage example:
----------------------------------
Let's look at the INSERT / UPDATE statement that you're executing.
With jOOQ, you'd write:

org.jooq.Factory create = // .. a new jOOQ Factory
org.jooq.Table table = // .. a custom table
org.jooq.Field[] fields = // .. from the column map
Object[] values = // .. from the column map
Map<org.jooq.Field<?>, ?> updateMap = // .. from the column map

create
  .insertInto(table, fields)
  .values(values)
  .onDuplicateKeyUpdate()
  .set(updateMap)
  .execute();

With jOOQ 2.0.2, the above statement will execute on DB2, HSQLDB,
MySQL, Oracle, SQL Server, Sybase SQL Anywhere

I hope this helps! Should you have any other questions, feel free to ask!

Cheers
Lukas