You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@chemistry.apache.org by "Huebel, Jens" <j....@sap.com> on 2013/06/03 09:31:28 UTC
Re: CMIS SQL abstraction
Hi Tim,
as far as I know nothing like this is currently available or planned. If
you have something to share please do (not necessarily code, can even be
ideas, pseudo-code, proof of concept, etc). I am sure we will have some
interesting discussions on the list.
JPA is around persisting Java objects. Do you have a use case in mind
doing this via CMIS?
Jens
On 30.05.13 10:37, "Tim Webster" <ti...@gmail.com> wrote:
>Hi,
>
>I was wondering if anyone uses an abstraction layer for generating CMIS
>SQL? Something like a Java API like JPA. Is there an equivalent of a
>JDBC
>driver for CMIS so we could do this?
>
>I basically need to write my own, as our queries are getting more complex,
>and cobbling together the SQL is getting messy.
>
>Any advice?
>
>Thanks,
>
>Tim
>
>
>--
>Check out my wine blog: http://timswineblog.blogspot.com/
Re: CMIS SQL abstraction
Posted by Tim Webster <ti...@gmail.com>.
Thanks Florian,
Yes I looked at the QueryStatement interface and came to the same
conclusion you did.
I may open an improvement issue once I get my code at little more polished
- it's just barely suitable for my own project at the moment. It's my own
API (i.e. not QueryDSL or some other existing API).
Also, I wasn't sure if this actually should be part of Chemistry or
OpenCMIS since it's not part of the spec...
Here is an example of the code used to build the SQL, and the resulting SQL
statement. It shows use of date/time properties, multi-valued properties,
and groups (groups of predicates separated by logical AND or OR clauses):
final Group whenGroup = new Group();
whenGroup.add(PROP1).eq(VALUE_1)
.and(DATE_PROP1).loe(DATE_UPPER_LIMIT);
final Group orGroup = new Group();
orGroup.add(PROP1).eq(VALUE_1)
.and(DATE_PROP1).goe(DATE_LOWER_LIMIT);
this.classUnderTest = new Query();
this.classUnderTest.select(COLUMN_1)
.select(COLUMN_2)
.from(BASE_OBJECT_TYPE_1)
.from(BASE_OBJECT_TYPE_2)
.whereGroup(whenGroup)
.orGroup(orGroup)
.and(MULTI_STR_PROP1).eq(VALUE_3)
.or(BOOL_PROP1).eq(true)
.orderBy(PROP2.getQueryName(),
ESqlOrderByModifier.DESC)
.orderBy(PROP3.getQueryName());
SELECT COLUMN_1,
COLUMN_2
FROM baseObject1,
baseObject2
WHERE ((property1 = 'value1') AND (dateProperty1 <= TIMESTAMP
'1973-12-31T11:12:13:123Z'))
OR ((property1 = 'value1') AND (dateProperty1 >= TIMESTAMP
'1973-01-01T11:12:13:123Z'))
AND ('value3' = ANY multiStrproperty1)
OR (boolProperty1 = true)
ORDER BY property2query DESC,
property3query ASC
Tim
On Mon, Jun 3, 2013 at 1:32 PM, Florian Müller <fm...@apache.org> wrote:
> Hi Tim,
>
> OpenCMIS provides a QueryStatement interface [1][2] which is comparable to
> the JDBC PreparedStatement. But that is not the abstraction level you are
> talking about.
> Something like QueryDSL should be doable. If you have concrete ideas,
> please open an improvement issue [1]. You can also attach source code to
> this issue, if you want to contribute something.
>
> Thanks,
>
> Florian
>
>
> [1] http://chemistry.apache.org/**java/0.8.0/maven/apidocs/org/**
> apache/chemistry/opencmis/**client/api/Session.html#**
> createQueryStatement(java.**lang.String)<http://chemistry.apache.org/java/0.8.0/maven/apidocs/org/apache/chemistry/opencmis/client/api/Session.html#createQueryStatement(java.lang.String)>
> [2] http://chemistry.apache.**org/java/0.8.0/maven/apidocs/**
> org/apache/chemistry/opencmis/**client/api/QueryStatement.html<http://chemistry.apache.org/java/0.8.0/maven/apidocs/org/apache/chemistry/opencmis/client/api/QueryStatement.html>
> [3] https://issues.apache.org/**jira/browse/CMIS<https://issues.apache.org/jira/browse/CMIS>
>
>
>
> Hi,
>>
>> I was thinking of just a small API/implementation that would generate
>> CMIS-compliant SQL for querying the repository (not persistence).
>>
>> I thought I might be able to reuse existing ORM tools like Spring Data,
>> Query DSL, JPA, etc and just 'lift' the generated SQL to use against the
>> CMIS repository, but these are all statically typed and basically need to
>> execute against a 'real' database. They need type code generated by a
>> database scheme and are abstractions over JDBC. This won't work. We just
>> need the SQL.
>>
>> One option is to implement something like QueryDSL for CMIS and maybe
>> generate types against CMIS property definitions (instead of scheme
>> definition)?
>>
>> Or if we don't care about type safety just something to generate SQL? I
>> ended up using this option and wrote my own library - it's simple and just
>> supports the main SQL clauses and operators and will work for my project
>> (happy to share it). It would be good to have our own option for the CMIS
>> community or else everyone might have to do this....
>>
>> Tim
>>
>>
>> On Mon, Jun 3, 2013 at 8:31 AM, Huebel, Jens <j....@sap.com> wrote:
>>
>> Hi Tim,
>>>
>>> as far as I know nothing like this is currently available or planned. If
>>> you have something to share please do (not necessarily code, can even be
>>> ideas, pseudo-code, proof of concept, etc). I am sure we will have some
>>> interesting discussions on the list.
>>>
>>> JPA is around persisting Java objects. Do you have a use case in mind
>>> doing this via CMIS?
>>>
>>> Jens
>>>
>>>
>>> On 30.05.13 10:37, "Tim Webster" <ti...@gmail.com> wrote:
>>>
>>> >Hi,
>>> >
>>> >I was wondering if anyone uses an abstraction layer for generating CMIS
>>> >SQL? Something like a Java API like JPA. Is there an equivalent of a
>>> >JDBC
>>> >driver for CMIS so we could do this?
>>> >
>>> >I basically need to write my own, as our queries are getting more
>>> complex,
>>> >and cobbling together the SQL is getting messy.
>>> >
>>> >Any advice?
>>> >
>>> >Thanks,
>>> >
>>> >Tim
>>> >
>>> >
>>> >--
>>> >Check out my wine blog: http://timswineblog.blogspot.**com/<http://timswineblog.blogspot.com/>
>>>
>>>
>>>
>
--
Check out my wine blog: http://timswineblog.blogspot.com/
Re: CMIS SQL abstraction
Posted by Florian Müller <fm...@apache.org>.
Hi Tim,
OpenCMIS provides a QueryStatement interface [1][2] which is comparable
to the JDBC PreparedStatement. But that is not the abstraction level you
are talking about.
Something like QueryDSL should be doable. If you have concrete ideas,
please open an improvement issue [1]. You can also attach source code to
this issue, if you want to contribute something.
Thanks,
Florian
[1]
http://chemistry.apache.org/java/0.8.0/maven/apidocs/org/apache/chemistry/opencmis/client/api/Session.html#createQueryStatement(java.lang.String)
[2] http://chemistry.apache.org/java/0.8.0/maven/apidocs/org/apache/chemistry/opencmis/client/api/QueryStatement.html
[3] https://issues.apache.org/jira/browse/CMIS
> Hi,
>
> I was thinking of just a small API/implementation that would generate
> CMIS-compliant SQL for querying the repository (not persistence).
>
> I thought I might be able to reuse existing ORM tools like Spring
> Data,
> Query DSL, JPA, etc and just 'lift' the generated SQL to use against
> the
> CMIS repository, but these are all statically typed and basically
> need to
> execute against a 'real' database. They need type code generated by
> a
> database scheme and are abstractions over JDBC. This won't work. We
> just
> need the SQL.
>
> One option is to implement something like QueryDSL for CMIS and maybe
> generate types against CMIS property definitions (instead of scheme
> definition)?
>
> Or if we don't care about type safety just something to generate SQL?
> I
> ended up using this option and wrote my own library - it's simple and
> just
> supports the main SQL clauses and operators and will work for my
> project
> (happy to share it). It would be good to have our own option for the
> CMIS
> community or else everyone might have to do this....
>
> Tim
>
>
> On Mon, Jun 3, 2013 at 8:31 AM, Huebel, Jens <j....@sap.com>
> wrote:
>
>> Hi Tim,
>>
>> as far as I know nothing like this is currently available or
>> planned. If
>> you have something to share please do (not necessarily code, can
>> even be
>> ideas, pseudo-code, proof of concept, etc). I am sure we will have
>> some
>> interesting discussions on the list.
>>
>> JPA is around persisting Java objects. Do you have a use case in
>> mind
>> doing this via CMIS?
>>
>> Jens
>>
>>
>> On 30.05.13 10:37, "Tim Webster" <ti...@gmail.com> wrote:
>>
>> >Hi,
>> >
>> >I was wondering if anyone uses an abstraction layer for generating
>> CMIS
>> >SQL? Something like a Java API like JPA. Is there an equivalent
>> of a
>> >JDBC
>> >driver for CMIS so we could do this?
>> >
>> >I basically need to write my own, as our queries are getting more
>> complex,
>> >and cobbling together the SQL is getting messy.
>> >
>> >Any advice?
>> >
>> >Thanks,
>> >
>> >Tim
>> >
>> >
>> >--
>> >Check out my wine blog: http://timswineblog.blogspot.com/
>>
>>
Re: CMIS SQL abstraction
Posted by Tim Webster <ti...@gmail.com>.
Hi,
I was thinking of just a small API/implementation that would generate
CMIS-compliant SQL for querying the repository (not persistence).
I thought I might be able to reuse existing ORM tools like Spring Data,
Query DSL, JPA, etc and just 'lift' the generated SQL to use against the
CMIS repository, but these are all statically typed and basically need to
execute against a 'real' database. They need type code generated by a
database scheme and are abstractions over JDBC. This won't work. We just
need the SQL.
One option is to implement something like QueryDSL for CMIS and maybe
generate types against CMIS property definitions (instead of scheme
definition)?
Or if we don't care about type safety just something to generate SQL? I
ended up using this option and wrote my own library - it's simple and just
supports the main SQL clauses and operators and will work for my project
(happy to share it). It would be good to have our own option for the CMIS
community or else everyone might have to do this....
Tim
On Mon, Jun 3, 2013 at 8:31 AM, Huebel, Jens <j....@sap.com> wrote:
> Hi Tim,
>
> as far as I know nothing like this is currently available or planned. If
> you have something to share please do (not necessarily code, can even be
> ideas, pseudo-code, proof of concept, etc). I am sure we will have some
> interesting discussions on the list.
>
> JPA is around persisting Java objects. Do you have a use case in mind
> doing this via CMIS?
>
> Jens
>
>
> On 30.05.13 10:37, "Tim Webster" <ti...@gmail.com> wrote:
>
> >Hi,
> >
> >I was wondering if anyone uses an abstraction layer for generating CMIS
> >SQL? Something like a Java API like JPA. Is there an equivalent of a
> >JDBC
> >driver for CMIS so we could do this?
> >
> >I basically need to write my own, as our queries are getting more complex,
> >and cobbling together the SQL is getting messy.
> >
> >Any advice?
> >
> >Thanks,
> >
> >Tim
> >
> >
> >--
> >Check out my wine blog: http://timswineblog.blogspot.com/
>
>
--
Check out my wine blog: http://timswineblog.blogspot.com/