Re: JOOQ for CMIS (Content Management)

[Tim Webster]

Hi - sorry I forgot to replace 'QueryDSL' with 'JOOQ' in the message text - copy/paste error!  The question should read like below:

On Friday, 7 June 2013 11:15:39 UTC+1, Tim Webster wrote:

Hi,

I posted this over at the QueryDSL group and didn't get much of a response - was wondering what everyone here thinks of it...?:

I'm currently working on a CMIS implementation.  For those not familiar with CMIS, it is an open standard for Content Management (documents, files, media, etc).  Content is stored in a repository which is CMIS compliant.  There are several big-name vendors that provide CMIS systems (IBM, Microsoft, etc).

One of CMIS main features is the ability to query the repository using SQL (SQL 92 compliant).  Currently there is no abstraction layer for generation of this SQL, the queries must be built 'by hand'.  It would be nice to have a QueryDSL-like implementation to be able to write these queries.

However, there are some differences between CMIS and databases:

- A CMIS repository is not a database, and querying from a client point of view does not require JDBC.  All we need is the SQL.

- Will JOOQ only support back-ends that can be interfaced by a JDBC driver?

- Type-safety.  CMIS has object types, and these have property definitions.  These could be analogous to database tables and types could theoretically be generated against them.  

- I'm trying to work out if this belongs here or in the OpenCMIS project (Apache Chemistry).   If it isn't suited to JOOQ because of the lack of database or JDBC, perhaps it would be more appropriate there.

So, this is just something I'm thinking about and would greatly value any thoughts or feedback from the experts..:-) 

Thanks

[Durchholz, Joachim]

> - A CMIS repository is not a database, and querying
> from a client point of view does not require JDBC.
> All we need is the SQL.
> - Will JOOQ only support back-ends that can be
> interfaced by a JDBC driver?

See http://www.jooq.org/doc/3.0/manual/getting-started/use-cases/jooq-as-a-standalone-sql-builder/

Money quote #1: "The SQL string that you can generate as such can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools."

> - Type-safety. CMIS has object types, and these have
> property definitions. These could be analogous to
> database tables and types could theoretically be
> generated against them.

Money quote #2: "Plain SQL: This section contains information useful in particular to those that want to supply table expressions, column expressions, etc. as plain SQL to jOOQ, rather than through generated artefacts."

Disclaimer: That's just from reading the manual. I'm more a bystander than an actual Jooq user.
But I think the link says that, yes, you can do these things, assuming there are no pitfalls hidden in the details of your use case (or in the details of Jooq).

[Lukas Eder]

Hi Tim,

2013/6/7 <tim.w...@gmail.com>

Hi,

I posted this over at the QueryDSL group and didn't get much of a response - was wondering what everyone here thinks of it...?:

I'm currently working on a CMIS implementation.  For those not familiar with CMIS, it is an open standard for Content Management (documents, files, media, etc).  Content is stored in a repository which is CMIS compliant.  There are several big-name vendors that provide CMIS systems (IBM, Microsoft, etc).

One of CMIS main features is the ability to query the repository using SQL (SQL 92 compliant).  Currently there is no abstraction layer for generation of this SQL, the queries must be built 'by hand'.  It would be nice to have a QueryDSL-like implementation to be able to write these queries.

However, there are some differences between CMIS and databases:

- A CMIS repository is not a database, and querying from a client point of view does not require JDBC.  All we need is the SQL.

- Will QueryDSL only support back-ends that can be interfaced by a JDBC driver?

Yes, jOOQ relies on JDBC for SQL execution. You can still use jOOQ for SQL rendering only, if deviating execution APIs are provided by CMIS implementations. I could imagine, though, that it shouldn't be too hard to implement JDBC for CMIS, wrapping existing non-standard APIs inside.

 

- Type-safety.  CMIS has object types, and these have property definitions.  These could be analogous to database tables and types could theoretically be generated against them.  

I'd have to dig further into CMIS details to take a stand on that.

 

- I'm trying to work out if this belongs here or in the OpenCMIS project (Apache Chemistry).   If it isn't suited to QueryDSL because of the lack of database or JDBC, perhaps it would be more appropriate there.

Clearly, jOOQ wouldn't implement JDBC for CMIS, but rely on a possibly not-yet-existing JDBC driver. So the JDBC driver should be implemented in Apache Chemistry. If you would need such an implementation any time soon, you could P.M. me for an implementation effort, and possible contribution to Apache.

 

So, this is just something I'm thinking about and would greatly value any thoughts or feedback from the experts..:-) 

What I can tell you is that I had recently evaluated feasibility of supporting JCR-SQL2 in jOOQ as a personal interest that evolved from my current contracting position at Adobe for the Adobe Experience Manager. I have come to the conclusion that JCR's promise of providing a SQLesque querying language is not a good match for jOOQ.

Looking at CMIS's SQL dialect, however, I feel that there is some potential of integrating, as the promise to implement SQL-92 is a bit more "trustworthy". I have registered a feature request for this evaluation:

https://github.com/jOOQ/jOOQ/issues/2507

Given my (rather short) JCR and content management experience, I think that your suggestion could be a real value addition to jOOQ!

Cheers

Lukas

[Lukas Eder]

2013/6/7 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

Absolutely! 

[Tim Webster]

Hi - great feedback thanks.  Slightly kicking myself for not going deeper into the manual now - as the SQL builder functionality is exactly what I was looking for (and I've just finished writing my own implementation!)  Oh well, like Lukas said perhaps there is scope for more integrated support in the future...:-)

Thanks again,

Tim

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/af-5m56Y3dI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Check out my wine blog: http://timswineblog.blogspot.com/

[Tim Webster]

Hi Lukas - any addition would obviously have to implement the CMIS SQL extensions.

I wouldn't mind being involved in this (contributing, etc) - not sure where to start though - any suggestions?

Tim

[Lukas Eder]

Hi Tim,

I've had a closer look at CMIS-SQL. I'm not sure if I got the whole picture, but I feel that this is a very limited SQL dialect with almost no functionality, or am I missing some important aspects?

Also, how well is CMIS adopted?

Before thinking about adding a contribution, I would like to assess its true value. Currently, new SQL dialects are not very easy to integrate in jOOQ, as jOOQ performs a lot of SQL syntax transformation to simulate functions and SQL clauses to standardise dialects. This effort is only worth the pain if I can get most of the integration tests to run.

Cheers

Lukas

2013/6/7 Tim Webster <tim.w...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Tim Webster]

Hi Lukas,

You're right in that it is a limited SQL dialect (with a few extensions).  There's nothing like stored procedures or that sort of thing.  SQL was chosen as an interface language because it is so well adopted and it makes it ideal for a standard like CMIS.  I'm assuming you looked at the CMIS standard?

CMIS itself from what I know is very well adopted.  For the company I'm working for (financial software - we're interesting in document management) the fact that we now support it is a big selling point, as the lack of standards for content management was always an issue for both us and our clients (having to support different systems, lack of compatibility,etc). I'm probably not the best person to ask this as I'm 'just' a techie though. The implementation I currently work with is IBM FileNet P8, but some of our clients use Microsoft Sharepoint (a big one) and Alfresco is a leader also in the development of the CMIS standard and OpenCMIS framework (see Apache Chemistry).

Also your question makes sense to me as one of things I was wondering about is where something like this would belong - as part of OpenCMIS itself or some other ORM solution.  However I would have thought that anyone developing a Java/SQL framework would be interested in supporting as many back-end products as possible (even non-RDBMS ones).  But yeah, since I don't know really much about the JOOQ internals I don't know how well it would suit.  One thing I thought would be interesting is the type-safety, which should be possible even though it's implementation would be a departure from what you normally do.  Funnily enough it suits me at the moment NOT to use use typesafe code, as the queries we generate are all dynamic.

Anyway, I think a solution belongs somewhere.  In the meantime I've written my own...not a full CMIS SQL implementation but it supports enough for our own requirements.  It would be good to have something out there in the public domain though..:-)

Thanks,

Tim

[Lukas Eder]

Some feedback on this thread.

While I generally like the idea of CMIS SQL (and also other SQL dialects, such as JCR-SQL2), I tend to think that this will not be a winning horse in the near future, for jOOQ to adopt. The current efforts and immediate roadmap will set more focus on very interesting SQL features, such as the recent feature additions of Oracle 12c and SQL Server 2012.

I am also thinking about providing niche SQL vendors with a partnership plan where they can pay for the maintenance (and publicity) they can get through a jOOQ integration. CMIS SQL might be one of them.

Best Regards,

Lukas

2013/6/9 Tim Webster <tim.w...@gmail.com>