You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Dave Merrin <dm...@ipasystems.co.uk> on 2007/06/06 16:12:10 UTC
Select Query on Table with no Primary Key
Hi,
I'm trying to run a SelectQuery on a table with no primary key.
Unfortunately it's not working. Can anybody help? I have no control over
the database so I can't add in primary keys.
Cheers,
Dave
INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
MPS.STATETABLE_CPT t0
INFO QueryLogger: === returned 949 rows. - took 203 ms.
INFO QueryLogger: +++ transaction committed.
Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
[v.2.0.3 May 6 2007] Won't be able to create ObjectId for
'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
defined.
at
org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
at
org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
at
org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(DataDomainQueryAction.java:319)
at
org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:116)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
at
org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:217)
at
org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:54)
at
org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1376)
at
ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java:158)
at
ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
Re: Remove Foreign Keys Mapped as Object Attributes
Posted by Mike Kienenberger <mk...@gmail.com>.
Yes, you generally want to do this.
It means that you are going to let Cayenne manage the foreign and
primary keys for the entities involved. Then, instead of having to
deal with
table2.setFk2pk1Field(Table1.somehowGetPrimaryKeyWhichMightNotBeComputedYet())
all you need to do is
table2.setFk2pk1Relationship(table1);
On 6/6/07, Fredrik Liden <fl...@translate.com> wrote:
> When I synch the dbentity with the object I get the following prompt:
>
> "Remove foreign keys mapped as Object Attributes."
>
> Could someone explain what this one does? What are the pros and cons of
> removing the foreign keys?
>
> This is a 1-many relationship.
>
> Table 1
> Pk1
> Field1
>
> Table 2
> Pk2
> Field2
> Fk2_pk1
>
> Thanks,
>
> Fredrik
>
>
Remove Foreign Keys Mapped as Object Attributes
Posted by Fredrik Liden <fl...@translate.com>.
When I synch the dbentity with the object I get the following prompt:
"Remove foreign keys mapped as Object Attributes."
Could someone explain what this one does? What are the pros and cons of
removing the foreign keys?
This is a 1-many relationship.
Table 1
Pk1
Field1
Table 2
Pk2
Field2
Fk2_pk1
Thanks,
Fredrik
Re: Select Query on Table with no Primary Key
Posted by Craig L Russell <Cr...@Sun.COM>.
On Jun 7, 2007, at 3:32 AM, Andrus Adamchik wrote:
>> I'm kind of curious if Cayenne supports PK-less for read-only data
>> now, though. Seems to me that could be a useful thing.
>
> It doesn't - otherwise you won't be able to build a valid ObjectId
> and hence - ensure uniqueness across the context.
>
> The only way we can potentially support it is by supporting
> "disconnected" objects, i.e. objects that are fetched from DB, but
> not registered in the ObjectContext. Such objects will only be
> marginally more useful than DataRows though. (IIRC JPA supports
> disconnected objects, produced in some other unrelated scenarios).
JPA supports disconnected objects, that were once retrieved from (or
persisted in) the database. It doesn't support disconnected objects
without a database identity.
Craig
>
> Andrus
>
>
> On Jun 6, 2007, at 7:42 PM, Michael Gentry wrote:
>> It's not a hijacking. It is interesting to point out that JPA
>> does not
>> support tables without PKs -- I really should learn more about JPA
>> soon. I
>> can understand why, too (need to know what to update and how to
>> make joins,
>> etc). I'm kind of curious if Cayenne supports PK-less for read-
>> only data
>> now, though. Seems to me that could be a useful thing.
>>
>> /dev/mrg
>>
>>
>> On 6/6/07, Craig L Russell <Cr...@sun.com> wrote:
>>>
>>> I don't want to hijack this thread, but just point out that JPA does
>>> not support tables without primary key; JDO does (including updates)
>>> as an optional feature.
>>>
>>> Craig
>>>
>>>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
> I'm kind of curious if Cayenne supports PK-less for read-only data
> now, though. Seems to me that could be a useful thing.
It doesn't - otherwise you won't be able to build a valid ObjectId
and hence - ensure uniqueness across the context.
The only way we can potentially support it is by supporting
"disconnected" objects, i.e. objects that are fetched from DB, but
not registered in the ObjectContext. Such objects will only be
marginally more useful than DataRows though. (IIRC JPA supports
disconnected objects, produced in some other unrelated scenarios).
Andrus
On Jun 6, 2007, at 7:42 PM, Michael Gentry wrote:
> It's not a hijacking. It is interesting to point out that JPA does
> not
> support tables without PKs -- I really should learn more about JPA
> soon. I
> can understand why, too (need to know what to update and how to
> make joins,
> etc). I'm kind of curious if Cayenne supports PK-less for read-
> only data
> now, though. Seems to me that could be a useful thing.
>
> /dev/mrg
>
>
> On 6/6/07, Craig L Russell <Cr...@sun.com> wrote:
>>
>> I don't want to hijack this thread, but just point out that JPA does
>> not support tables without primary key; JDO does (including updates)
>> as an optional feature.
>>
>> Craig
>>
>>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
From a brief try this morning I don't think this works. I'm going to
try again and also see if there is anything else I can do.
Cheers,
Dave
Michael Gentry wrote:
> It's not a hijacking. It is interesting to point out that JPA does not
> support tables without PKs -- I really should learn more about JPA
> soon. I
> can understand why, too (need to know what to update and how to make
> joins,
> etc). I'm kind of curious if Cayenne supports PK-less for read-only data
> now, though. Seems to me that could be a useful thing.
>
> /dev/mrg
>
>
> On 6/6/07, Craig L Russell <Cr...@sun.com> wrote:
>>
>> I don't want to hijack this thread, but just point out that JPA does
>> not support tables without primary key; JDO does (including updates)
>> as an optional feature.
>>
>> Craig
>>
>>
>
Re: Select Query on Table with no Primary Key
Posted by Michael Gentry <bl...@gmail.com>.
It's not a hijacking. It is interesting to point out that JPA does not
support tables without PKs -- I really should learn more about JPA soon. I
can understand why, too (need to know what to update and how to make joins,
etc). I'm kind of curious if Cayenne supports PK-less for read-only data
now, though. Seems to me that could be a useful thing.
/dev/mrg
On 6/6/07, Craig L Russell <Cr...@sun.com> wrote:
>
> I don't want to hijack this thread, but just point out that JPA does
> not support tables without primary key; JDO does (including updates)
> as an optional feature.
>
> Craig
>
>
Re: Select Query on Table with no Primary Key
Posted by Michael Gentry <bl...@gmail.com>.
We have a group here doing updates through views (with Oracle, at least), so
that is still a valid practice. Also, Cayenne doesn't know if it is a view
or an actual table.
/dev/mrg
On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>
> Sorry, I forgot to mention again....these are views, not tables.
>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Sorry, I forgot to mention again....these are views, not tables.
Dave
Craig L Russell wrote:
> I don't want to hijack this thread, but just point out that JPA does
> not support tables without primary key; JDO does (including updates)
> as an optional feature.
>
> Craig
>
> On Jun 6, 2007, at 8:41 AM, Dave Merrin wrote:
>
>> Sorry, I forgot to mention that I won't be updating the data. I would
>> like to move round the database using relationships though.
>>
>> Apart from changing the database are there any hacks I could try to
>> get round this problem.
>>
>> Cheers,
>>
>> Dave
>>
>> Michael Gentry wrote:
>>> Cayenne needs PKs in order to do UPDATEs. If you aren't modifying
>>> the data,
>>> you might try fetching as data rows instead of CayenneDataObjects.
>>> I'm not
>>> certain if that would work, but has a much higher chance. Of
>>> course, you
>>> still won't be able to make a CayenneDataObject out of it.
>>>
>>> /dev/mrg
>>>
>>>
>>> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>> Unfortunately it's not working. Can anybody help? I have no control
>>>> over
>>>> the database so I can't add in primary keys.
>>>>
>>>> Cheers,
>>>>
>>>> Dave
>>>>
>>>> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>>>> MPS.STATETABLE_CPT t0
>>>> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>>>> INFO QueryLogger: +++ transaction committed.
>>>> Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
>>>> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>>>> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
>>>> defined.
>>>> at
>>>> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
>>>> at
>>>> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
>>>>
>>>> at
>>>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
>>>>
>>>> DataDomainQueryAction.java:319)
>>>> at
>>>> org.apache.cayenne.access.DataDomainQueryAction.execute(
>>>> DataDomainQueryAction.java:116)
>>>> at
>>>> org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
>>>> :746)
>>>> at
>>>> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>>>> ObjectContextQueryAction.java:217)
>>>> at
>>>> org.apache.cayenne.access.DataContextQueryAction.execute(
>>>> DataContextQueryAction.java:54)
>>>> at
>>>> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
>>>> at
>>>> org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1376)
>>>>
>>>> at
>>>> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
>>>>
>>>> :158)
>>>> at
>>>> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>>>>
>>>
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Re: Select Query on Table with no Primary Key
Posted by Craig L Russell <Cr...@Sun.COM>.
I don't want to hijack this thread, but just point out that JPA does
not support tables without primary key; JDO does (including updates)
as an optional feature.
Craig
On Jun 6, 2007, at 8:41 AM, Dave Merrin wrote:
> Sorry, I forgot to mention that I won't be updating the data. I
> would like to move round the database using relationships though.
>
> Apart from changing the database are there any hacks I could try to
> get round this problem.
>
> Cheers,
>
> Dave
>
> Michael Gentry wrote:
>> Cayenne needs PKs in order to do UPDATEs. If you aren't modifying
>> the data,
>> you might try fetching as data rows instead of
>> CayenneDataObjects. I'm not
>> certain if that would work, but has a much higher chance. Of
>> course, you
>> still won't be able to make a CayenneDataObject out of it.
>>
>> /dev/mrg
>>
>>
>> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>>
>>> Hi,
>>>
>>> I'm trying to run a SelectQuery on a table with no primary key.
>>> Unfortunately it's not working. Can anybody help? I have no
>>> control over
>>> the database so I can't add in primary keys.
>>>
>>> Cheers,
>>>
>>> Dave
>>>
>>> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>>> MPS.STATETABLE_CPT t0
>>> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>>> INFO QueryLogger: +++ transaction committed.
>>> Exception in thread "main"
>>> org.apache.cayenne.CayenneRuntimeException:
>>> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>>> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary
>>> Key
>>> defined.
>>> at
>>> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:
>>> 75)
>>> at
>>> org.apache.cayenne.access.ObjectResolver.<init>
>>> (ObjectResolver.java:57)
>>> at
>>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConve
>>> rsion(
>>> DataDomainQueryAction.java:319)
>>> at
>>> org.apache.cayenne.access.DataDomainQueryAction.execute(
>>> DataDomainQueryAction.java:116)
>>> at org.apache.cayenne.access.DataDomain.onQuery
>>> (DataDomain.java
>>> :746)
>>> at
>>> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>>> ObjectContextQueryAction.java:217)
>>> at
>>> org.apache.cayenne.access.DataContextQueryAction.execute(
>>> DataContextQueryAction.java:54)
>>> at
>>> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
>>> at
>>> org.apache.cayenne.access.DataContext.performQuery
>>> (DataContext.java:1376)
>>> at
>>> ipa.printexpress.datahelpers.Session.fetchEntityCollection
>>> (Session.java
>>> :158)
>>> at
>>> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>>>
>>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
I am +0 on this feature. Maybe you are right and the users should
have an opportunity to mess up the object graph in this case.... :-)
Would you mind logging a Jira issue? And let's see what other Cayenne
developers say on that.
Thanks
Andrus
On Jun 8, 2007, at 10:25 AM, Dave Merrin wrote:
> Hi Andrus,
>
> I had seen this as being a problem but not one that I really cared
> about. The main functionality I was after was being able to
> generate classes and have them mapped through to database tables.
> I've got a fair bit of code using cayenne already which I wanted to
> use again. It would be good if Cayenne had some options which
> allowed for no primary keys but made you aware you would lose some
> functionality.
>
> As mentioned before I've got round the problem for this project but
> next time it might not be so easy.
>
> Cheers,
>
> Dave
>
> Andrus Adamchik wrote:
>>
>> Hi Craig,
>>
>> I can probably implement this in Cayenne in about 30 minutes, as
>> Cayenne has a notion of "temporary id" (normally used for new
>> uncommitted objects). The problem of course is uniquing. So say if
>> a DB row is fetched from a table via a query, resulting in object
>> A, and then later the same row is navigated to via a relationship
>> from another object, resulting in a second object B, distinct from
>> A. This breaks the fundamental assumption about object identity.
>> So we chose not to go this way.
>>
>> Andrus
>>
>>
>> On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
>>
>>> Just FYI, when JDO reads data from tables without PK, it
>>> internally creates a unique id, similar to a generated PK, for
>>> the objects that it reads and these ids are discarded when no
>>> longer needed. The fact that the mapping is for tables without PK
>>> is known by the code that creates the temporary ids.
>>>
>>> Craig
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Hi Andrus,
I had seen this as being a problem but not one that I really cared
about. The main functionality I was after was being able to generate
classes and have them mapped through to database tables. I've got a fair
bit of code using cayenne already which I wanted to use again. It would
be good if Cayenne had some options which allowed for no primary keys
but made you aware you would lose some functionality.
As mentioned before I've got round the problem for this project but next
time it might not be so easy.
Cheers,
Dave
Andrus Adamchik wrote:
>
> Hi Craig,
>
> I can probably implement this in Cayenne in about 30 minutes, as
> Cayenne has a notion of "temporary id" (normally used for new
> uncommitted objects). The problem of course is uniquing. So say if a
> DB row is fetched from a table via a query, resulting in object A, and
> then later the same row is navigated to via a relationship from
> another object, resulting in a second object B, distinct from A. This
> breaks the fundamental assumption about object identity. So we chose
> not to go this way.
>
> Andrus
>
>
> On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
>
>> Just FYI, when JDO reads data from tables without PK, it internally
>> creates a unique id, similar to a generated PK, for the objects that
>> it reads and these ids are discarded when no longer needed. The fact
>> that the mapping is for tables without PK is known by the code that
>> creates the temporary ids.
>>
>> Craig
>
Re: Select Query on Table with no Primary Key
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Andrus,
On Jun 10, 2007, at 4:42 AM, Andrus Adamchik wrote:
>> If you can navigate to a single A via a relationship, to me that
>> implies that you have some unique constraint by which a single A
>> is associated with another row. And if you have a unique
>> constraint, then you can use that instead of a primary key.
>
> True, and that constraint makes it possible to map a "virtual" PK
> in Cayenne, so having a no-pk entity doesn't buy us anything in
> this case.
>
> But in the remaining cases uniquing is clearly broken. This
> includes to-many relationships and running multiple queries with
> overlapping result sets.
With to-many relationships you map the relationship to a field in the
non-pk table and all the entities that are related can be counted and
operated on. It's not clear to me that uniquing is of value when you
don't define a unique behavior in the database.
Given that there is a database schema that makes sense for the
application, I don't see any problem with mapping that to Java
objects. You just cannot assign stronger identity inside the VM than
exists in the database.
Craig
>
> Andrus
>
>
>
>
> On Jun 9, 2007, at 11:27 PM, Craig L Russell wrote:
>
>> Hi Andrus,
>>
>> On Jun 7, 2007, at 11:38 PM, Andrus Adamchik wrote:
>>
>>>
>>> On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
>>>
>>>> Just FYI, when JDO reads data from tables without PK, it
>>>> internally creates a unique id, similar to a generated PK, for
>>>> the objects that it reads and these ids are discarded when no
>>>> longer needed. The fact that the mapping is for tables without
>>>> PK is known by the code that creates the temporary ids.
>>>>
>>>> Craig
>>>
>>> Hi Craig,
>>>
>>> I can probably implement this in Cayenne in about 30 minutes, as
>>> Cayenne has a notion of "temporary id" (normally used for new
>>> uncommitted objects). The problem of course is uniquing. So say
>>> if a DB row is fetched from a table via a query, resulting in
>>> object A, and then later the same row is navigated to via a
>>> relationship from another object, resulting in a second object B,
>>> distinct from A. This breaks the fundamental assumption about
>>> object identity. So we chose not to go this way.
>>
>> If you can navigate to a single A via a relationship, to me that
>> implies that you have some unique constraint by which a single A
>> is associated with another row. And if you have a unique
>> constraint, then you can use that instead of a primary key.
>>
>> Craig
>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
> If you can navigate to a single A via a relationship, to me that
> implies that you have some unique constraint by which a single A is
> associated with another row. And if you have a unique constraint,
> then you can use that instead of a primary key.
True, and that constraint makes it possible to map a "virtual" PK in
Cayenne, so having a no-pk entity doesn't buy us anything in this case.
But in the remaining cases uniquing is clearly broken. This includes
to-many relationships and running multiple queries with overlapping
result sets.
Andrus
On Jun 9, 2007, at 11:27 PM, Craig L Russell wrote:
> Hi Andrus,
>
> On Jun 7, 2007, at 11:38 PM, Andrus Adamchik wrote:
>
>>
>> On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
>>
>>> Just FYI, when JDO reads data from tables without PK, it
>>> internally creates a unique id, similar to a generated PK, for
>>> the objects that it reads and these ids are discarded when no
>>> longer needed. The fact that the mapping is for tables without PK
>>> is known by the code that creates the temporary ids.
>>>
>>> Craig
>>
>> Hi Craig,
>>
>> I can probably implement this in Cayenne in about 30 minutes, as
>> Cayenne has a notion of "temporary id" (normally used for new
>> uncommitted objects). The problem of course is uniquing. So say if
>> a DB row is fetched from a table via a query, resulting in object
>> A, and then later the same row is navigated to via a relationship
>> from another object, resulting in a second object B, distinct from
>> A. This breaks the fundamental assumption about object identity.
>> So we chose not to go this way.
>
> If you can navigate to a single A via a relationship, to me that
> implies that you have some unique constraint by which a single A is
> associated with another row. And if you have a unique constraint,
> then you can use that instead of a primary key.
>
> Craig
Re: Select Query on Table with no Primary Key
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Andrus,
On Jun 7, 2007, at 11:38 PM, Andrus Adamchik wrote:
>
> On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
>
>> Just FYI, when JDO reads data from tables without PK, it
>> internally creates a unique id, similar to a generated PK, for the
>> objects that it reads and these ids are discarded when no longer
>> needed. The fact that the mapping is for tables without PK is
>> known by the code that creates the temporary ids.
>>
>> Craig
>
> Hi Craig,
>
> I can probably implement this in Cayenne in about 30 minutes, as
> Cayenne has a notion of "temporary id" (normally used for new
> uncommitted objects). The problem of course is uniquing. So say if
> a DB row is fetched from a table via a query, resulting in object
> A, and then later the same row is navigated to via a relationship
> from another object, resulting in a second object B, distinct from
> A. This breaks the fundamental assumption about object identity. So
> we chose not to go this way.
If you can navigate to a single A via a relationship, to me that
implies that you have some unique constraint by which a single A is
associated with another row. And if you have a unique constraint,
then you can use that instead of a primary key.
Craig
>
> Andrus
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jun 8, 2007, at 7:01 AM, Craig L Russell wrote:
> Just FYI, when JDO reads data from tables without PK, it internally
> creates a unique id, similar to a generated PK, for the objects
> that it reads and these ids are discarded when no longer needed.
> The fact that the mapping is for tables without PK is known by the
> code that creates the temporary ids.
>
> Craig
Hi Craig,
I can probably implement this in Cayenne in about 30 minutes, as
Cayenne has a notion of "temporary id" (normally used for new
uncommitted objects). The problem of course is uniquing. So say if a
DB row is fetched from a table via a query, resulting in object A,
and then later the same row is navigated to via a relationship from
another object, resulting in a second object B, distinct from A. This
breaks the fundamental assumption about object identity. So we chose
not to go this way.
Andrus
Re: Select Query on Table with no Primary Key
Posted by Craig L Russell <Cr...@Sun.COM>.
Just FYI, when JDO reads data from tables without PK, it internally
creates a unique id, similar to a generated PK, for the objects that
it reads and these ids are discarded when no longer needed. The fact
that the mapping is for tables without PK is known by the code that
creates the temporary ids.
Craig
On Jun 7, 2007, at 2:33 AM, Dave Merrin wrote:
> OK, I've stepped through the code and got to the ObjectResolver. In
> there it checks for primary keys and throws an exception if there
> aren't any. Is there any way I can override this code easily to
> removed the primary key check?
>
>
> ObjectResolver(DataContext context, ObjEntity entity, boolean
> refresh,
> boolean resolveInheritanceHierarchy) {
> init(context, entity, refresh, resolveInheritanceHierarchy);
> }
>
> void init(
> DataContext context,
> ObjEntity entity,
> boolean refresh,
> boolean resolveInheritanceHierarchy) {
> // sanity check
> DbEntity dbEntity = entity.getDbEntity();
> if (dbEntity == null) {
> throw new CayenneRuntimeException("ObjEntity '"
> + entity.getName()
> + "' has no DbEntity.");
> }
>
> this.primaryKey = dbEntity.getPrimaryKey();
> if (primaryKey.size() == 0) {
> throw new CayenneRuntimeException("Won't be able to
> create ObjectId for '"
> + entity.getName()
> + "'. Reason: DbEntity '"
> + dbEntity.getName()
> + "' has no Primary Key defined.");
> }
>
> this.context = context;
> this.cache = context.getObjectStore().getDataRowCache();
> this.refreshObjects = refresh;
> this.entity = entity;
> this.inheritanceTree = context.getEntityResolver
> ().lookupInheritanceTree(entity);
> this.resolveInheritance = (inheritanceTree != null)
> ? resolveInheritanceHierarchy
> : false;
> }
> Michael Gentry wrote:
>> The only thing I can think of trying (and maybe Andrus/Tore/Mike/
>> etc would
>> have better ideas) is to model that ObjEntity (and perhaps others
>> it relates
>> to?) as read-only and see if Cayenne stops caring about missing a
>> PK. Just
>> a thought -- I've not tried it.
>>
>> /dev/mrg
>>
>>
>> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>>
>>> Sorry, I forgot to mention that I won't be updating the data. I
>>> would
>>> like to move round the database using relationships though.
>>>
>>> Apart from changing the database are there any hacks I could try
>>> to get
>>> round this problem.
>>>
>>> Cheers,
>>>
>>> Dave
>>>
>>> Michael Gentry wrote:
>>> > Cayenne needs PKs in order to do UPDATEs. If you aren't
>>> modifying the
>>> > data,
>>> > you might try fetching as data rows instead of CayenneDataObjects.
>>> > I'm not
>>> > certain if that would work, but has a much higher chance. Of
>>> course,
>>> you
>>> > still won't be able to make a CayenneDataObject out of it.
>>> >
>>> > /dev/mrg
>>> >
>>> >
>>> > On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>> >>
>>> >> Hi,
>>> >>
>>> >> I'm trying to run a SelectQuery on a table with no primary key.
>>> >> Unfortunately it's not working. Can anybody help? I have no
>>> control
>>> over
>>> >> the database so I can't add in primary keys.
>>> >>
>>> >> Cheers,
>>> >>
>>> >> Dave
>>> >>
>>> >> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>>> >> MPS.STATETABLE_CPT t0
>>> >> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>>> >> INFO QueryLogger: +++ transaction committed.
>>> >> Exception in thread "main"
>>> org.apache.cayenne.CayenneRuntimeException:
>>> >> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>>> >> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no
>>> Primary Key
>>> >> defined.
>>> >> at
>>> >> org.apache.cayenne.access.ObjectResolver.init
>>> (ObjectResolver.java:75)
>>> >> at
>>> >> org.apache.cayenne.access.ObjectResolver.<init>
>>> (ObjectResolver.java:57)
>>> >> at
>>> >>
>>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConve
>>> rsion(
>>> >>
>>> >> DataDomainQueryAction.java:319)
>>> >> at
>>> >> org.apache.cayenne.access.DataDomainQueryAction.execute(
>>> >> DataDomainQueryAction.java:116)
>>> >> at org.apache.cayenne.access.DataDomain.onQuery
>>> (DataDomain.java
>>> >> :746)
>>> >> at
>>> >> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>>> >> ObjectContextQueryAction.java:217)
>>> >> at
>>> >> org.apache.cayenne.access.DataContextQueryAction.execute(
>>> >> DataContextQueryAction.java:54)
>>> >> at
>>> >> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:
>>> 1387)
>>> >> at
>>> >> org.apache.cayenne.access.DataContext.performQuery
>>> (DataContext.java
>>> :1376)
>>> >>
>>> >> at
>>> >> ipa.printexpress.datahelpers.Session.fetchEntityCollection
>>> (Session.java
>>> >> :158)
>>> >> at
>>> >> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:
>>> 109)
>>> >>
>>> >
>>>
>>>
>>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
OK, I've stepped through the code and got to the ObjectResolver. In
there it checks for primary keys and throws an exception if there aren't
any. Is there any way I can override this code easily to removed the
primary key check?
ObjectResolver(DataContext context, ObjEntity entity, boolean refresh,
boolean resolveInheritanceHierarchy) {
init(context, entity, refresh, resolveInheritanceHierarchy);
}
void init(
DataContext context,
ObjEntity entity,
boolean refresh,
boolean resolveInheritanceHierarchy) {
// sanity check
DbEntity dbEntity = entity.getDbEntity();
if (dbEntity == null) {
throw new CayenneRuntimeException("ObjEntity '"
+ entity.getName()
+ "' has no DbEntity.");
}
this.primaryKey = dbEntity.getPrimaryKey();
if (primaryKey.size() == 0) {
throw new CayenneRuntimeException("Won't be able to create
ObjectId for '"
+ entity.getName()
+ "'. Reason: DbEntity '"
+ dbEntity.getName()
+ "' has no Primary Key defined.");
}
this.context = context;
this.cache = context.getObjectStore().getDataRowCache();
this.refreshObjects = refresh;
this.entity = entity;
this.inheritanceTree =
context.getEntityResolver().lookupInheritanceTree(entity);
this.resolveInheritance = (inheritanceTree != null)
? resolveInheritanceHierarchy
: false;
}
Michael Gentry wrote:
> The only thing I can think of trying (and maybe Andrus/Tore/Mike/etc
> would
> have better ideas) is to model that ObjEntity (and perhaps others it
> relates
> to?) as read-only and see if Cayenne stops caring about missing a PK.
> Just
> a thought -- I've not tried it.
>
> /dev/mrg
>
>
> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>
>> Sorry, I forgot to mention that I won't be updating the data. I would
>> like to move round the database using relationships though.
>>
>> Apart from changing the database are there any hacks I could try to get
>> round this problem.
>>
>> Cheers,
>>
>> Dave
>>
>> Michael Gentry wrote:
>> > Cayenne needs PKs in order to do UPDATEs. If you aren't modifying the
>> > data,
>> > you might try fetching as data rows instead of CayenneDataObjects.
>> > I'm not
>> > certain if that would work, but has a much higher chance. Of course,
>> you
>> > still won't be able to make a CayenneDataObject out of it.
>> >
>> > /dev/mrg
>> >
>> >
>> > On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>> >>
>> >> Hi,
>> >>
>> >> I'm trying to run a SelectQuery on a table with no primary key.
>> >> Unfortunately it's not working. Can anybody help? I have no control
>> over
>> >> the database so I can't add in primary keys.
>> >>
>> >> Cheers,
>> >>
>> >> Dave
>> >>
>> >> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>> >> MPS.STATETABLE_CPT t0
>> >> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>> >> INFO QueryLogger: +++ transaction committed.
>> >> Exception in thread "main"
>> org.apache.cayenne.CayenneRuntimeException:
>> >> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>> >> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
>> >> defined.
>> >> at
>> >> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
>> >> at
>> >>
>> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
>> >> at
>> >>
>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
>>
>> >>
>> >> DataDomainQueryAction.java:319)
>> >> at
>> >> org.apache.cayenne.access.DataDomainQueryAction.execute(
>> >> DataDomainQueryAction.java:116)
>> >> at
>> org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
>> >> :746)
>> >> at
>> >> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>> >> ObjectContextQueryAction.java:217)
>> >> at
>> >> org.apache.cayenne.access.DataContextQueryAction.execute(
>> >> DataContextQueryAction.java:54)
>> >> at
>> >> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
>> >> at
>> >> org.apache.cayenne.access.DataContext.performQuery(DataContext.java
>> :1376)
>> >>
>> >> at
>> >>
>> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
>> >> :158)
>> >> at
>> >> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>> >>
>> >
>>
>>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Cheers, I'll give it a try.
Dave
Michael Gentry wrote:
> The only thing I can think of trying (and maybe Andrus/Tore/Mike/etc
> would
> have better ideas) is to model that ObjEntity (and perhaps others it
> relates
> to?) as read-only and see if Cayenne stops caring about missing a PK.
> Just
> a thought -- I've not tried it.
>
> /dev/mrg
>
>
> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>
>> Sorry, I forgot to mention that I won't be updating the data. I would
>> like to move round the database using relationships though.
>>
>> Apart from changing the database are there any hacks I could try to get
>> round this problem.
>>
>> Cheers,
>>
>> Dave
>>
>> Michael Gentry wrote:
>> > Cayenne needs PKs in order to do UPDATEs. If you aren't modifying the
>> > data,
>> > you might try fetching as data rows instead of CayenneDataObjects.
>> > I'm not
>> > certain if that would work, but has a much higher chance. Of course,
>> you
>> > still won't be able to make a CayenneDataObject out of it.
>> >
>> > /dev/mrg
>> >
>> >
>> > On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>> >>
>> >> Hi,
>> >>
>> >> I'm trying to run a SelectQuery on a table with no primary key.
>> >> Unfortunately it's not working. Can anybody help? I have no control
>> over
>> >> the database so I can't add in primary keys.
>> >>
>> >> Cheers,
>> >>
>> >> Dave
>> >>
>> >> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>> >> MPS.STATETABLE_CPT t0
>> >> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>> >> INFO QueryLogger: +++ transaction committed.
>> >> Exception in thread "main"
>> org.apache.cayenne.CayenneRuntimeException:
>> >> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>> >> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
>> >> defined.
>> >> at
>> >> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
>> >> at
>> >>
>> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
>> >> at
>> >>
>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
>>
>> >>
>> >> DataDomainQueryAction.java:319)
>> >> at
>> >> org.apache.cayenne.access.DataDomainQueryAction.execute(
>> >> DataDomainQueryAction.java:116)
>> >> at
>> org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
>> >> :746)
>> >> at
>> >> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>> >> ObjectContextQueryAction.java:217)
>> >> at
>> >> org.apache.cayenne.access.DataContextQueryAction.execute(
>> >> DataContextQueryAction.java:54)
>> >> at
>> >> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
>> >> at
>> >> org.apache.cayenne.access.DataContext.performQuery(DataContext.java
>> :1376)
>> >>
>> >> at
>> >>
>> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
>> >> :158)
>> >> at
>> >> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>> >>
>> >
>>
>>
>
Re: Select Query on Table with no Primary Key
Posted by Michael Gentry <bl...@gmail.com>.
The only thing I can think of trying (and maybe Andrus/Tore/Mike/etc would
have better ideas) is to model that ObjEntity (and perhaps others it relates
to?) as read-only and see if Cayenne stops caring about missing a PK. Just
a thought -- I've not tried it.
/dev/mrg
On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>
> Sorry, I forgot to mention that I won't be updating the data. I would
> like to move round the database using relationships though.
>
> Apart from changing the database are there any hacks I could try to get
> round this problem.
>
> Cheers,
>
> Dave
>
> Michael Gentry wrote:
> > Cayenne needs PKs in order to do UPDATEs. If you aren't modifying the
> > data,
> > you might try fetching as data rows instead of CayenneDataObjects.
> > I'm not
> > certain if that would work, but has a much higher chance. Of course,
> you
> > still won't be able to make a CayenneDataObject out of it.
> >
> > /dev/mrg
> >
> >
> > On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
> >>
> >> Hi,
> >>
> >> I'm trying to run a SelectQuery on a table with no primary key.
> >> Unfortunately it's not working. Can anybody help? I have no control
> over
> >> the database so I can't add in primary keys.
> >>
> >> Cheers,
> >>
> >> Dave
> >>
> >> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
> >> MPS.STATETABLE_CPT t0
> >> INFO QueryLogger: === returned 949 rows. - took 203 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
> >> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
> >> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
> >> defined.
> >> at
> >> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
> >> at
> >> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
> >>
> >> DataDomainQueryAction.java:319)
> >> at
> >> org.apache.cayenne.access.DataDomainQueryAction.execute(
> >> DataDomainQueryAction.java:116)
> >> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
> >> :746)
> >> at
> >> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
> >> ObjectContextQueryAction.java:217)
> >> at
> >> org.apache.cayenne.access.DataContextQueryAction.execute(
> >> DataContextQueryAction.java:54)
> >> at
> >> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
> >> at
> >> org.apache.cayenne.access.DataContext.performQuery(DataContext.java
> :1376)
> >>
> >> at
> >> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
> >> :158)
> >> at
> >> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
> >>
> >
>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Sorry, I forgot to mention that I won't be updating the data. I would
like to move round the database using relationships though.
Apart from changing the database are there any hacks I could try to get
round this problem.
Cheers,
Dave
Michael Gentry wrote:
> Cayenne needs PKs in order to do UPDATEs. If you aren't modifying the
> data,
> you might try fetching as data rows instead of CayenneDataObjects.
> I'm not
> certain if that would work, but has a much higher chance. Of course, you
> still won't be able to make a CayenneDataObject out of it.
>
> /dev/mrg
>
>
> On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>>
>> Hi,
>>
>> I'm trying to run a SelectQuery on a table with no primary key.
>> Unfortunately it's not working. Can anybody help? I have no control over
>> the database so I can't add in primary keys.
>>
>> Cheers,
>>
>> Dave
>>
>> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
>> MPS.STATETABLE_CPT t0
>> INFO QueryLogger: === returned 949 rows. - took 203 ms.
>> INFO QueryLogger: +++ transaction committed.
>> Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
>> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
>> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
>> defined.
>> at
>> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
>> at
>> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
>>
>> DataDomainQueryAction.java:319)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.execute(
>> DataDomainQueryAction.java:116)
>> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
>> :746)
>> at
>> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
>> ObjectContextQueryAction.java:217)
>> at
>> org.apache.cayenne.access.DataContextQueryAction.execute(
>> DataContextQueryAction.java:54)
>> at
>> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
>> at
>> org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1376)
>>
>> at
>> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
>> :158)
>> at
>> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>>
>
Re: Select Query on Table with no Primary Key
Posted by Michael Gentry <bl...@gmail.com>.
Cayenne needs PKs in order to do UPDATEs. If you aren't modifying the data,
you might try fetching as data rows instead of CayenneDataObjects. I'm not
certain if that would work, but has a much higher chance. Of course, you
still won't be able to make a CayenneDataObject out of it.
/dev/mrg
On 6/6/07, Dave Merrin <dm...@ipasystems.co.uk> wrote:
>
> Hi,
>
> I'm trying to run a SelectQuery on a table with no primary key.
> Unfortunately it's not working. Can anybody help? I have no control over
> the database so I can't add in primary keys.
>
> Cheers,
>
> Dave
>
> INFO QueryLogger: SELECT t0.ID, t0.KIND, t0.TIMESTAMP FROM
> MPS.STATETABLE_CPT t0
> INFO QueryLogger: === returned 949 rows. - took 203 ms.
> INFO QueryLogger: +++ transaction committed.
> Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
> [v.2.0.3 May 6 2007] Won't be able to create ObjectId for
> 'StatetableCpt'. Reason: DbEntity 'STATETABLE_CPT' has no Primary Key
> defined.
> at
> org.apache.cayenne.access.ObjectResolver.init(ObjectResolver.java:75)
> at
> org.apache.cayenne.access.ObjectResolver.<init>(ObjectResolver.java:57)
> at
> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(
> DataDomainQueryAction.java:319)
> at
> org.apache.cayenne.access.DataDomainQueryAction.execute(
> DataDomainQueryAction.java:116)
> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java
> :746)
> at
> org.apache.cayenne.util.ObjectContextQueryAction.runQuery(
> ObjectContextQueryAction.java:217)
> at
> org.apache.cayenne.access.DataContextQueryAction.execute(
> DataContextQueryAction.java:54)
> at
> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1387)
> at
> org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1376)
> at
> ipa.printexpress.datahelpers.Session.fetchEntityCollection(Session.java
> :158)
> at
> ipa.px.importer.abb.ABBImporterMain.main(ABBImporterMain.java:109)
>
Re: Select Query on Table with no Primary Key
Posted by John <mm...@web.de>.
Hello Andrus,
I can get data from a table or a view, what about a StoredProcedure? can i
get data from it?
cos this could be the solution for Dave's problem
Sako.
----- Original Message -----
From: "Andrus Adamchik" <an...@objectstyle.org>
To: <us...@cayenne.apache.org>
Sent: Thursday, June 07, 2007 12:44 PM
Subject: Re: Select Query on Table with no Primary Key
> To give an example - if you have an EMPLOYEE table, you can make an
> assumption that it is highly unlikely that there are two people with the
> same name, born on the same date, and working in the same department. On
> that assumption you can mark these 4 columns as PK in the Modeler:
> FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>
> Works well with views or tables (updateable or read-only) on any DB.
>
> Andrus
>
>
> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>
>> Tore is right - for Cayenne to handle an object (whether read-only on
>> read/write), it needs to know which column or columns uniquely identify
>> each row. Now... you can fake a PK in your model, even if there's none
>> in the db - just select a really unique combination of columns, and mark
>> those columns as the PK in the modeler. I've mapped tables with such
>> "imaginary PK" a lot.
>>
>> If it is not possible (i.e. duplicate rows are expected to be fetched),
>> you will have to use DataRows.
>>
>> Andrus
>>
>>
>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>
>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>
>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>> Unfortunately it's not working. Can anybody help? I have no control
>>>> over the database so I can't add in primary keys.
>>>
>>> As you know the PK are essential not only to update a row, but to make
>>> sure a single row maps to a single DataObject in your context.
>>>
>>> Some database engines do have a unique invisible column. If your
>>> database does this, then perhaps you could map that column as your
>>> primary key? What database engine are you using?
>>>
>>> Regards,
>>> - Tore.
>>>
>>
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Hi John,
as mentioned to Andrus I'm going with the setting primary keys on
columns. I actually found some documentation for the database which said
which columns make the rows unique. This keeps things relatively simple.
BTW. also tried some prefetching today....cool stuff. I got some code
down from 8 seconds down to 3 seconds.
Cheers for the help... support is the reason I'm using Cayenne rather
than Hibernate.
Dave
John wrote:
> Hello Dave,
>
>
> You said you use a view and you will NOT modify the data, then try this:
>
> Your View name : MyView.
>
> --------------------------------------------------------------------------------------------------------------------------
>
> create another view call MyViewWithPK
> -------------------------------------------------
>
>
> SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name )
> AS rownumber
> FROM MyView e
>
> // remember that name must be included in the view MyView
> // remember that this technique is slow, but i suppose this is your
> last problem :)
> ---------------------------------------------------------------------------------------------------------------------------
>
> let me know if this work :)
>
>
> Sako
>
> Signature IT-Consult Armainak Sarkis
> ----- Original Message ----- From: "Dave Merrin"
> <dm...@ipasystems.co.uk>
> To: <us...@cayenne.apache.org>
> Sent: Thursday, June 07, 2007 3:36 PM
> Subject: Re: Select Query on Table with no Primary Key
>
>
>> Hi Andrus,
>>
>> I was playing about with putting in my own EntityResolver. For now
>> I'm going to take your approach and see how I get on.
>>
>> Cheers,
>>
>> Dave
>>
>> Andrus Adamchik wrote:
>>> To give an example - if you have an EMPLOYEE table, you can make an
>>> assumption that it is highly unlikely that there are two people with
>>> the same name, born on the same date, and working in the same
>>> department. On that assumption you can mark these 4 columns as PK in
>>> the Modeler: FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>>>
>>> Works well with views or tables (updateable or read-only) on any DB.
>>>
>>> Andrus
>>>
>>>
>>> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>>>
>>>> Tore is right - for Cayenne to handle an object (whether read-only
>>>> on read/write), it needs to know which column or columns uniquely
>>>> identify each row. Now... you can fake a PK in your model, even if
>>>> there's none in the db - just select a really unique combination of
>>>> columns, and mark those columns as the PK in the modeler. I've
>>>> mapped tables with such "imaginary PK" a lot.
>>>>
>>>> If it is not possible (i.e. duplicate rows are expected to be
>>>> fetched), you will have to use DataRows.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>>>
>>>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>>>
>>>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>>>> Unfortunately it's not working. Can anybody help? I have no
>>>>>> control over the database so I can't add in primary keys.
>>>>>
>>>>> As you know the PK are essential not only to update a row, but to
>>>>> make sure a single row maps to a single DataObject in your context.
>>>>>
>>>>> Some database engines do have a unique invisible column. If your
>>>>> database does this, then perhaps you could map that column as your
>>>>> primary key? What database engine are you using?
>>>>>
>>>>> Regards,
>>>>> - Tore.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by John <mm...@web.de>.
opps forgot a part from the SQL
SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
rownumber
FROM MyView e
ORDER BY name
Order by must be added
Sako.
Signature IT-Consult Armainak Sarkis
----- Original Message -----
From: "John" <mm...@web.de>
To: <us...@cayenne.apache.org>
Sent: Thursday, June 07, 2007 5:38 PM
Subject: Re: Select Query on Table with no Primary Key
> Hello Dave,
>
>
> You said you use a view and you will NOT modify the data, then try this:
>
> Your View name : MyView.
>
> --------------------------------------------------------------------------------------------------------------------------
> create another view call MyViewWithPK
> -------------------------------------------------
>
>
> SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
> rownumber
> FROM MyView e
>
> // remember that name must be included in the view MyView
> // remember that this technique is slow, but i suppose this is your last
> problem :)
> ---------------------------------------------------------------------------------------------------------------------------
> let me know if this work :)
>
>
> Sako
>
> Signature IT-Consult Armainak Sarkis
> ----- Original Message -----
> From: "Dave Merrin" <dm...@ipasystems.co.uk>
> To: <us...@cayenne.apache.org>
> Sent: Thursday, June 07, 2007 3:36 PM
> Subject: Re: Select Query on Table with no Primary Key
>
>
>> Hi Andrus,
>>
>> I was playing about with putting in my own EntityResolver. For now I'm
>> going to take your approach and see how I get on.
>>
>> Cheers,
>>
>> Dave
>>
>> Andrus Adamchik wrote:
>>> To give an example - if you have an EMPLOYEE table, you can make an
>>> assumption that it is highly unlikely that there are two people with the
>>> same name, born on the same date, and working in the same department. On
>>> that assumption you can mark these 4 columns as PK in the Modeler:
>>> FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>>>
>>> Works well with views or tables (updateable or read-only) on any DB.
>>>
>>> Andrus
>>>
>>>
>>> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>>>
>>>> Tore is right - for Cayenne to handle an object (whether read-only on
>>>> read/write), it needs to know which column or columns uniquely identify
>>>> each row. Now... you can fake a PK in your model, even if there's none
>>>> in the db - just select a really unique combination of columns, and
>>>> mark those columns as the PK in the modeler. I've mapped tables with
>>>> such "imaginary PK" a lot.
>>>>
>>>> If it is not possible (i.e. duplicate rows are expected to be fetched),
>>>> you will have to use DataRows.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>>>
>>>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>>>
>>>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>>>> Unfortunately it's not working. Can anybody help? I have no control
>>>>>> over the database so I can't add in primary keys.
>>>>>
>>>>> As you know the PK are essential not only to update a row, but to make
>>>>> sure a single row maps to a single DataObject in your context.
>>>>>
>>>>> Some database engines do have a unique invisible column. If your
>>>>> database does this, then perhaps you could map that column as your
>>>>> primary key? What database engine are you using?
>>>>>
>>>>> Regards,
>>>>> - Tore.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by John <mm...@web.de>.
Hello Dave,
You said you use a view and you will NOT modify the data, then try this:
Your View name : MyView.
--------------------------------------------------------------------------------------------------------------------------
create another view call MyViewWithPK
-------------------------------------------------
SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
rownumber
FROM MyView e
// remember that name must be included in the view MyView
// remember that this technique is slow, but i suppose this is your last
problem :)
---------------------------------------------------------------------------------------------------------------------------
let me know if this work :)
Sako
Signature IT-Consult Armainak Sarkis
----- Original Message -----
From: "Dave Merrin" <dm...@ipasystems.co.uk>
To: <us...@cayenne.apache.org>
Sent: Thursday, June 07, 2007 3:36 PM
Subject: Re: Select Query on Table with no Primary Key
> Hi Andrus,
>
> I was playing about with putting in my own EntityResolver. For now I'm
> going to take your approach and see how I get on.
>
> Cheers,
>
> Dave
>
> Andrus Adamchik wrote:
>> To give an example - if you have an EMPLOYEE table, you can make an
>> assumption that it is highly unlikely that there are two people with the
>> same name, born on the same date, and working in the same department. On
>> that assumption you can mark these 4 columns as PK in the Modeler:
>> FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>>
>> Works well with views or tables (updateable or read-only) on any DB.
>>
>> Andrus
>>
>>
>> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>>
>>> Tore is right - for Cayenne to handle an object (whether read-only on
>>> read/write), it needs to know which column or columns uniquely identify
>>> each row. Now... you can fake a PK in your model, even if there's none
>>> in the db - just select a really unique combination of columns, and mark
>>> those columns as the PK in the modeler. I've mapped tables with such
>>> "imaginary PK" a lot.
>>>
>>> If it is not possible (i.e. duplicate rows are expected to be fetched),
>>> you will have to use DataRows.
>>>
>>> Andrus
>>>
>>>
>>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>>
>>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>>
>>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>>> Unfortunately it's not working. Can anybody help? I have no control
>>>>> over the database so I can't add in primary keys.
>>>>
>>>> As you know the PK are essential not only to update a row, but to make
>>>> sure a single row maps to a single DataObject in your context.
>>>>
>>>> Some database engines do have a unique invisible column. If your
>>>> database does this, then perhaps you could map that column as your
>>>> primary key? What database engine are you using?
>>>>
>>>> Regards,
>>>> - Tore.
>>>>
>>>
>>>
>>
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Hi Andrus,
I was playing about with putting in my own EntityResolver. For now I'm
going to take your approach and see how I get on.
Cheers,
Dave
Andrus Adamchik wrote:
> To give an example - if you have an EMPLOYEE table, you can make an
> assumption that it is highly unlikely that there are two people with
> the same name, born on the same date, and working in the same
> department. On that assumption you can mark these 4 columns as PK in
> the Modeler: FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>
> Works well with views or tables (updateable or read-only) on any DB.
>
> Andrus
>
>
> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>
>> Tore is right - for Cayenne to handle an object (whether read-only on
>> read/write), it needs to know which column or columns uniquely
>> identify each row. Now... you can fake a PK in your model, even if
>> there's none in the db - just select a really unique combination of
>> columns, and mark those columns as the PK in the modeler. I've mapped
>> tables with such "imaginary PK" a lot.
>>
>> If it is not possible (i.e. duplicate rows are expected to be
>> fetched), you will have to use DataRows.
>>
>> Andrus
>>
>>
>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>
>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>
>>>> I'm trying to run a SelectQuery on a table with no primary key.
>>>> Unfortunately it's not working. Can anybody help? I have no control
>>>> over the database so I can't add in primary keys.
>>>
>>> As you know the PK are essential not only to update a row, but to
>>> make sure a single row maps to a single DataObject in your context.
>>>
>>> Some database engines do have a unique invisible column. If your
>>> database does this, then perhaps you could map that column as your
>>> primary key? What database engine are you using?
>>>
>>> Regards,
>>> - Tore.
>>>
>>
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
To give an example - if you have an EMPLOYEE table, you can make an
assumption that it is highly unlikely that there are two people with
the same name, born on the same date, and working in the same
department. On that assumption you can mark these 4 columns as PK in
the Modeler: FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
Works well with views or tables (updateable or read-only) on any DB.
Andrus
On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
> Tore is right - for Cayenne to handle an object (whether read-only
> on read/write), it needs to know which column or columns uniquely
> identify each row. Now... you can fake a PK in your model, even if
> there's none in the db - just select a really unique combination of
> columns, and mark those columns as the PK in the modeler. I've
> mapped tables with such "imaginary PK" a lot.
>
> If it is not possible (i.e. duplicate rows are expected to be
> fetched), you will have to use DataRows.
>
> Andrus
>
>
> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>
>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>
>>> I'm trying to run a SelectQuery on a table with no primary key.
>>> Unfortunately it's not working. Can anybody help? I have no
>>> control over the database so I can't add in primary keys.
>>
>> As you know the PK are essential not only to update a row, but to
>> make sure a single row maps to a single DataObject in your context.
>>
>> Some database engines do have a unique invisible column. If your
>> database does this, then perhaps you could map that column as your
>> primary key? What database engine are you using?
>>
>> Regards,
>> - Tore.
>>
>
>
Re: Select Query on Table with no Primary Key
Posted by Andrus Adamchik <an...@objectstyle.org>.
Tore is right - for Cayenne to handle an object (whether read-only on
read/write), it needs to know which column or columns uniquely
identify each row. Now... you can fake a PK in your model, even if
there's none in the db - just select a really unique combination of
columns, and mark those columns as the PK in the modeler. I've mapped
tables with such "imaginary PK" a lot.
If it is not possible (i.e. duplicate rows are expected to be
fetched), you will have to use DataRows.
Andrus
On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>
>> I'm trying to run a SelectQuery on a table with no primary key.
>> Unfortunately it's not working. Can anybody help? I have no
>> control over the database so I can't add in primary keys.
>
> As you know the PK are essential not only to update a row, but to
> make sure a single row maps to a single DataObject in your context.
>
> Some database engines do have a unique invisible column. If your
> database does this, then perhaps you could map that column as your
> primary key? What database engine are you using?
>
> Regards,
> - Tore.
>
Re: Select Query on Table with no Primary Key
Posted by Dave Merrin <dm...@ipasystems.co.uk>.
Hi Tore,
I understand there will be limitations in the use of cayenne if there is
no primary key; I can work with these.
I'm using an Oracle database. The views were created to give restricted
access to the data. The tables may have primary keys but I can't get
these added to the views.
We already use Cayenne on a different project so we have a lot of code
to make things easier for us. This is the main reason I'm not looking at
alternatives to solve the problem.
Cheers,
Dave
Tore Halset wrote:
> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>
>> I'm trying to run a SelectQuery on a table with no primary key.
>> Unfortunately it's not working. Can anybody help? I have no control
>> over the database so I can't add in primary keys.
>
> As you know the PK are essential not only to update a row, but to make
> sure a single row maps to a single DataObject in your context.
>
> Some database engines do have a unique invisible column. If your
> database does this, then perhaps you could map that column as your
> primary key? What database engine are you using?
>
> Regards,
> - Tore.
>
>
Re: Select Query on Table with no Primary Key
Posted by Tore Halset <ha...@pvv.ntnu.no>.
On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
> I'm trying to run a SelectQuery on a table with no primary key.
> Unfortunately it's not working. Can anybody help? I have no control
> over the database so I can't add in primary keys.
As you know the PK are essential not only to update a row, but to
make sure a single row maps to a single DataObject in your context.
Some database engines do have a unique invisible column. If your
database does this, then perhaps you could map that column as your
primary key? What database engine are you using?
Regards,
- Tore.