Hi,

We are using CDO with DBStore for our project.

The sql handler for DBStore looked to not handle join queries. As in the first object from the join result was returned back.

Does dbstore support joins? If so how do I formulate queries to return join results. If not is there some work in progress in this regard. [ Hibernate store looked to support joins but the lack of support for branching and hql queries on audit data made us chose db store].

A slightly off track question relating to dbstore is, for sql queries if two different packages have the same name for an eclass how would one formulate a sql query using this eclass.

Thanks,
Ashish

The DBStore executes the SQL statement by delegating to the underlying backend.
This is comparable to Hibernate's/JPA's native queries.

CDO generates the tables such that they are unique, for example, for an EClass mypackage.A, the table name may be mypackage_ns_uri_A (where mypackage_ns_uri = the EPackage's namespace URI prefix).
The best (at the moment) is to have a look at the database table names that are generated.

Ashish Shinde wrote on Wed, 01 January 2014 07:44

Hi,

We are using CDO with DBStore for our project.

The sql handler for DBStore looked to not handle join queries. As in the first object from the join result was returned back.

Does dbstore support joins? If so how do I formulate queries to return join results. If not is there some work in progress in this regard. [ Hibernate store looked to support joins but the lack of support for branching and hql queries on audit data made us chose db store].

A slightly off track question relating to dbstore is, for sql queries if two different packages have the same name for an eclass how would one formulate a sql query using this eclass.

Thanks,
Ashish

Hi Erdal,

Yes the DBStore executes the join SQL query by delegating it to the backend, however the problem I am facing is that the SQLQueryHandler returns, only the first object for each query result instead of all components of the join.

e.g. for the query select table1.*, table2.* from table1,table2 , the result one would get from the db would be the
table1_object1 (as columns), table2_object1 (as columns)
table1_object2 (as columns), table2_object2 (as columns)
table1_object3 (as columns), table2_object3 (as columns)

however the dbstore query returns
table1_object1,
table1_object2,
table1_object3

ignoring all objects from table2.

Hence the question is does/will dbstore support join queries where the exact join result is returned to the caller instead of only objects from first table in the join.

Thanks and regards,
- Ashish

Erdal Karaca wrote on Wed, 01 January 2014 05:55

The DBStore executes the SQL statement by delegating to the underlying backend.
This is comparable to Hibernate's/JPA's native queries.

CDO generates the tables such that they are unique, for example, for an EClass mypackage.A, the table name may be mypackage_ns_uri_A (where mypackage_ns_uri = the EPackage's namespace URI prefix).
The best (at the moment) is to have a look at the database table names that are generated.

Ashish Shinde wrote on Wed, 01 January 2014 07:44

Hi,

We are using CDO with DBStore for our project.

The sql handler for DBStore looked to not handle join queries. As in the first object from the join result was returned back.

Does dbstore support joins? If so how do I formulate queries to return join results. If not is there some work in progress in this regard. [ Hibernate store looked to support joins but the lack of support for branching and hql queries on audit data made us chose db store].

A slightly off track question relating to dbstore is, for sql queries if two different packages have the same name for an eclass how would one formulate a sql query using this eclass.

Thanks,
Ashish

Am 02.01.2014 12:16, schrieb Erdal Karaca:
> The SQL handler only checks the first column if the query is an "object query" (which is the default if you have not
> set that paramter).
> I guess you have to file an enhancement request.
You can explicitely tell the SQLQueryHandler to return all column values (instead of using the first column to identify
a CDOObject):

CDOQuery query = transaction.createQuery("sql", "SELECT street, city, name FROM model1_customer ORDER BY street");
*query.setParameter("cdoObjectQuery", false);*

List<Object[]> results = query.getResult(Object[].class);

Or return each row as a map:

CDOQuery query = transaction.createQuery("sql", "SELECT street, city, name FROM model1_customer ORDER BY street");
query.setParameter("cdoObjectQuery", false);
*query.setParameter("mapQuery", true);*

List<Map<String, Object>> results = query.getResult();

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper


>
> Ashish Shinde wrote on Thu, 02 January 2014 10:44
>> Hi Erdal,
>>
>> Yes the DBStore executes the join SQL query by delegating it to the backend, however the problem I am facing is that
>> the SQLQueryHandler returns, only the first object for each query result instead of all components of the join.
>>
>> e.g. for the query select table1.*, table2.* from table1,table2 , the result one would get from the db would be the
>> table1_object1 (as columns), table2_object1 (as columns)
>> table1_object2 (as columns), table2_object2 (as columns)
>> table1_object3 (as columns), table2_object3 (as columns)
>>
>> however the dbstore query returns table1_object1,
>> table1_object2, table1_object3
>>
>> ignoring all objects from table2.
>> Hence the question is does/will dbstore support join queries where the exact join result is returned to the caller
>> instead of only objects from first table in the join.
>>
>> Thanks and regards,
>> - Ashish
>>
>> Erdal Karaca wrote on Wed, 01 January 2014 05:55
>> > The DBStore executes the SQL statement by delegating to the underlying backend.
>> > This is comparable to Hibernate's/JPA's native queries.
>> > > CDO generates the tables such that they are unique, for example, for an EClass mypackage.A, the table name may be
>> mypackage_ns_uri_A (where mypackage_ns_uri = the EPackage's namespace URI prefix).
>> > The best (at the moment) is to have a look at the database table names that are generated.
>> > > Ashish Shinde wrote on Wed, 01 January 2014 07:44
>> > > Hi,
>> > > > > We are using CDO with DBStore for our project. > > > > The sql handler for DBStore looked to not handle join
>> queries. As in the first object from the join result was returned back. > > > > Does dbstore support joins? If so how
>> do I formulate queries to return join results. If not is there some work in progress in this regard. [ Hibernate
>> store looked to support joins but the lack of support for branching and hql queries on audit data made us chose db
>> store].
>> > > > > A slightly off track question relating to dbstore is, for sql queries if two different packages have the same
>> name for an eclass how would one formulate a sql query using this eclass.
>> > > > > Thanks,
>> > > Ashish
>
>

Am 04.01.2014 09:21, schrieb Ashish Shinde:
> Hi Eike,
>
> Thanks for the reply. Is there an easy / relatively easy way to convert these column back into CDOObjects.
If the ID of the object is part of the result row you can pass it into view.getObject() to load the CDOObject.

> Also a bit off track but will these results correspond to the view/transaction timestamp if one is set. This might be
> important for our use case.
No. In order to respect the view target, i.e., branch and timestamp, you need to specify your SQL query accordingly.

>
> I glanced a bit at the sql query handler code and it seemed to me that I might get results for all timestamps. Could
> not locate additional timestamp contraint being applied here.
See above.

> I will give this a spin though, however do you know offhand how timestamps might work with a mapquery.
I don't think that these aspects are related. The mapQuery=true parameter influences the representation of the returned
query results. The timestamps would only be used/considered in the WHERE clause of the query.

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper


> Thanks and regards,
> - Ashish
>
> Eike Stepper wrote on Fri, 03 January 2014 02:41
>> Am 02.01.2014 12:16, schrieb Erdal Karaca:
>> > The SQL handler only checks the first column if the query is an "object query" (which is the default if you have
>> not > set that paramter).
>> > I guess you have to file an enhancement request.
>> You can explicitely tell the SQLQueryHandler to return all column values (instead of using the first column to
>> identify a CDOObject):
>>
>> CDOQuery query = transaction.createQuery("sql", "SELECT street, city, name FROM model1_customer ORDER BY
>> street");
>> *query.setParameter("cdoObjectQuery", false);*
>>
>> List<Object[]> results = query.getResult(Object[].class);
>>
>> Or return each row as a map:
>>
>> CDOQuery query = transaction.createQuery("sql", "SELECT street, city, name FROM model1_customer ORDER BY street");
>> query.setParameter("cdoObjectQuery", false);
>> *query.setParameter("mapQuery", true);*
>>
>> List<Map<String, Object>> results = query.getResult();
>>
>> Cheers
>> /Eike
>>
>> ----
>> http://www.esc-net.de
>> http://thegordian.blogspot.com
>> http://twitter.com/eikestepper
>>
>>
>> >
>> > Ashish Shinde wrote on Thu, 02 January 2014 10:44
>> >> Hi Erdal,
>> >>
>> >> Yes the DBStore executes the join SQL query by delegating it to the backend, however the problem I am facing is
>> that >> the SQLQueryHandler returns, only the first object for each query result instead of all components of the join.
>> >>
>> >> e.g. for the query select table1.*, table2.* from table1,table2 , the result one would get from the db would be the
>> >> table1_object1 (as columns), table2_object1 (as columns)
>> >> table1_object2 (as columns), table2_object2 (as columns)
>> >> table1_object3 (as columns), table2_object3 (as columns)
>> >>
>> >> however the dbstore query returns table1_object1,
>> >> table1_object2, table1_object3
>> >>
>> >> ignoring all objects from table2.
>> >> Hence the question is does/will dbstore support join queries where the exact join result is returned to the caller
>> >> instead of only objects from first table in the join.
>> >>
>> >> Thanks and regards,
>> >> - Ashish
>> >>
>> >> Erdal Karaca wrote on Wed, 01 January 2014 05:55
>> >> > The DBStore executes the SQL statement by delegating to the underlying backend.
>> >> > This is comparable to Hibernate's/JPA's native queries.
>> >> > > CDO generates the tables such that they are unique, for example, for an EClass mypackage.A, the table name may
>> be >> mypackage_ns_uri_A (where mypackage_ns_uri = the EPackage's namespace URI prefix).
>> >> > The best (at the moment) is to have a look at the database table names that are generated.
>> >> > > Ashish Shinde wrote on Wed, 01 January 2014 07:44
>> >> > > Hi,
>> >> > > > > We are using CDO with DBStore for our project. > > > > The sql handler for DBStore looked to not handle
>> join >> queries. As in the first object from the join result was returned back. > > > > Does dbstore support joins?
>> If so how >> do I formulate queries to return join results. If not is there some work in progress in this regard. [
>> Hibernate >> store looked to support joins but the lack of support for branching and hql queries on audit data made
>> us chose db >> store].
>> >> > > > > A slightly off track question relating to dbstore is, for sql queries if two different packages have the
>> same >> name for an eclass how would one formulate a sql query using this eclass.
>> >> > > > > Thanks,
>> >> > > Ashish
>> >
>> >
>
>

Am 04.01.2014 15:59, schrieb Ashish Shinde:
> Thank you for the reply.
>
> Would view.getObject() result in an extra database query ? If so is this avoidable since the query might have fetched
> all the attributes for the corresponding CDOObject.

view.getObject(id) leads to a server round trip if the object is not in the view cache and the object data (i.e. the
CDORevision) is not in the session cache. The server round trip only leads to a database query if the object data is not
in the repository cache.

Nothing in CDO creates a CDOObject from a number of arbitrary query result columns, whether they represent that object's
attributes or not.

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper