You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Øyvind Harboe <oy...@zylin.com> on 2006/06/21 08:18:08 UTC
Generating primary key locally without extra tables
Problem:
I'm using Cayenne for projects where I can't modify the database
schema on the server.
These databases use server generated primary keys. MSAccess, MS SQL
Server and Oracle.
The number of new records created are very small(max 1/minute), so I'm
rather insensitive to performance of record creation.
Q: Does a strategy for generating keys locally exist?
This strategy should work across databases where Cayenne does not
support server generated primary keys.
Getting a primary key by e.g.:
SELECT MAX(ID)+1 FROM FOO
This would a) be slow b) risk a collision when inserting.
Qs:
a) can I always override a server generated key?
b) would it be sufficient to use exception handling and retry e.g. N times?
--
Øyvind Harboe
http://www.zylin.com
RE: Generating primary key locally without extra tables
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
UUID: I don't care about guessable (I'd never give a client -- such as a web browser -- access to the primary key). Also, it isn't so much a need as a desire. I'm perfectly content with incrementing numbers, but when doing production support, more information could be helpful. If the PK contains the create timestamp, the server IP, and the process number, that would allow me to more easily trouble shoot something (especially finding the log output). In my current environment, we have 20 or so instances (processes) of an application split across multiple servers hitting the same database. If there is an issue, it is painful to track down the logs for when a record was created. If the PK could narrow that down, it would be helpful.
How are the non-standard (as in not supported by Cayenne) PKs generated? I had to roll my own PK generator for Cayenne because our schema (Sybase backend -- which is similar to MS SQL Server) used a stored procedure which returned a 12-byte array as the PK. It is certainly possible to roll your own generator, which might be the best approach.
Thanks,
/dev/mrg
-----Original Message-----
From: oyvindharboe@gmail.com [mailto:oyvindharboe@gmail.com] On Behalf Of Øyvind Harboe
Sent: Wednesday, June 21, 2006 9:40 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Generating primary key locally without extra tables
On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> Hmm, I can look into the UUID thing a bit more, but at first glance I don't think it has the information I'd like encoded in it. But thanks! :-)
Isn't UUID also guaranteed to be unguessable based upon previous
UUID's and have no structure?
It does sound like you have very different needs if you need some
structure to the number.
>
> As to my comments, I have a feeling that changing the value of an existing primary key of an object would have bad consequences on the Cayenne object graph (mess Cayenne up). I've never tried it, but it just doesn't seem like the right thing to do.
>
> The second thing you mentioned, catching exceptions and trying again, seems very hackish, which I'm sure you already know.
>
> Is there any reason you can't use the existing server-side PK generation?
It needs to be supported by both the JDBC driver and the Cayenne
adapter, which is not always the case.
>
> /dev/mrg
>
>
> -----Original Message-----
> From: oyvindharboe@gmail.com [mailto:oyvindharboe@gmail.com] On Behalf Of Øyvind Harboe
> Sent: Wednesday, June 21, 2006 9:16 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Generating primary key locally without extra tables
>
>
> On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> > I actually looked into making a PK generator that required no DB access, but when I looked into it, Java didn't support everything I wanted without resorting to JNI and C code. Of course, I was more ambitious in what I was thinking. I wanted it to be similar to what I could do in EOF (hundreds of keys/sec).
> >
> > Does anyone here know if Java 1.5 supports obtaining the process ID natively? I think that was my stumbling point before. I was trying to make a key that contained server IP, process ID, timestamp, and counter. Maybe it is time to consider this again. If I could produce a unique string all the time like that, it would make a nice PK generator and your PKs would have some meaning (that is me with my production support hat on).
>
> It sounds like you are looking for something like:
> http://java.sun.com/j2se/1.5.0/docs/api/java/util/UUID.html
>
> > As to your questions ...
> >
> > A) My gut feeling tells me that wouldn't be a good idea.
>
> Could you be more specific?
>
> We're replacing a horrid .asp + MS Access application, which
> inevitably leads to some nausea :-)
>
> > B) You could do that, but it seems pretty problematic to me.
>
> More specific?
>
>
> > Maybe some others will have a few good ideas ...
>
> If I can have an MSAccess & Oracle & MS SQL adapter that supports
> server generated keys, then that will also solve my problems. Might be
> a bit quixotic though. :-)
>
> --
> Øyvind Harboe
> http://www.zylin.com
>
--
Øyvind Harboe
http://www.zylin.com
Re: Generating primary key locally without extra tables
Posted by Øyvind Harboe <oy...@zylin.com>.
On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> Hmm, I can look into the UUID thing a bit more, but at first glance I don't think it has the information I'd like encoded in it. But thanks! :-)
Isn't UUID also guaranteed to be unguessable based upon previous
UUID's and have no structure?
It does sound like you have very different needs if you need some
structure to the number.
>
> As to my comments, I have a feeling that changing the value of an existing primary key of an object would have bad consequences on the Cayenne object graph (mess Cayenne up). I've never tried it, but it just doesn't seem like the right thing to do.
>
> The second thing you mentioned, catching exceptions and trying again, seems very hackish, which I'm sure you already know.
>
> Is there any reason you can't use the existing server-side PK generation?
It needs to be supported by both the JDBC driver and the Cayenne
adapter, which is not always the case.
>
> /dev/mrg
>
>
> -----Original Message-----
> From: oyvindharboe@gmail.com [mailto:oyvindharboe@gmail.com] On Behalf Of Øyvind Harboe
> Sent: Wednesday, June 21, 2006 9:16 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Generating primary key locally without extra tables
>
>
> On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> > I actually looked into making a PK generator that required no DB access, but when I looked into it, Java didn't support everything I wanted without resorting to JNI and C code. Of course, I was more ambitious in what I was thinking. I wanted it to be similar to what I could do in EOF (hundreds of keys/sec).
> >
> > Does anyone here know if Java 1.5 supports obtaining the process ID natively? I think that was my stumbling point before. I was trying to make a key that contained server IP, process ID, timestamp, and counter. Maybe it is time to consider this again. If I could produce a unique string all the time like that, it would make a nice PK generator and your PKs would have some meaning (that is me with my production support hat on).
>
> It sounds like you are looking for something like:
> http://java.sun.com/j2se/1.5.0/docs/api/java/util/UUID.html
>
> > As to your questions ...
> >
> > A) My gut feeling tells me that wouldn't be a good idea.
>
> Could you be more specific?
>
> We're replacing a horrid .asp + MS Access application, which
> inevitably leads to some nausea :-)
>
> > B) You could do that, but it seems pretty problematic to me.
>
> More specific?
>
>
> > Maybe some others will have a few good ideas ...
>
> If I can have an MSAccess & Oracle & MS SQL adapter that supports
> server generated keys, then that will also solve my problems. Might be
> a bit quixotic though. :-)
>
> --
> Øyvind Harboe
> http://www.zylin.com
>
--
Øyvind Harboe
http://www.zylin.com
Re: Generating primary key locally without extra tables
Posted by Øyvind Harboe <oy...@zylin.com>.
On 6/21/06, Tore Halset <ha...@pvv.ntnu.no> wrote:
> On Jun 21, 2006, at 15:16, Øyvind Harboe wrote:
>
> > If I can have an MSAccess & Oracle & MS SQL adapter that supports
> > server generated keys, then that will also solve my problems.
>
> So, after your MSAccess-work, it does support generated keys. MS SQL
> Server adapter works with generated keys if you use jtds. I have not
> used the oracle adapter, but it does not look like it supports
> generated keys.
>
> Looks like (googling) the Oracle jdbc driver does not support
> generated keys either. Perhaps you can create a separate PkGenerator
> that uses the max()+1 hack just for oracle?
That does sound like a solution yes. Though I haven't yet googled up
any examples on how to do this. The PkGenerator I found was concerned
with creating/dropping the primary key tables...
--
Øyvind Harboe
http://www.zylin.com
Re: Generating primary key locally without extra tables
Posted by Tore Halset <ha...@pvv.ntnu.no>.
On Jun 21, 2006, at 15:16, Øyvind Harboe wrote:
> If I can have an MSAccess & Oracle & MS SQL adapter that supports
> server generated keys, then that will also solve my problems.
So, after your MSAccess-work, it does support generated keys. MS SQL
Server adapter works with generated keys if you use jtds. I have not
used the oracle adapter, but it does not look like it supports
generated keys.
Looks like (googling) the Oracle jdbc driver does not support
generated keys either. Perhaps you can create a separate PkGenerator
that uses the max()+1 hack just for oracle?
- Tore.
RE: Generating primary key locally without extra tables
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
Hmm, I can look into the UUID thing a bit more, but at first glance I don't think it has the information I'd like encoded in it. But thanks! :-)
As to my comments, I have a feeling that changing the value of an existing primary key of an object would have bad consequences on the Cayenne object graph (mess Cayenne up). I've never tried it, but it just doesn't seem like the right thing to do.
The second thing you mentioned, catching exceptions and trying again, seems very hackish, which I'm sure you already know.
Is there any reason you can't use the existing server-side PK generation?
/dev/mrg
-----Original Message-----
From: oyvindharboe@gmail.com [mailto:oyvindharboe@gmail.com] On Behalf Of Øyvind Harboe
Sent: Wednesday, June 21, 2006 9:16 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Generating primary key locally without extra tables
On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> I actually looked into making a PK generator that required no DB access, but when I looked into it, Java didn't support everything I wanted without resorting to JNI and C code. Of course, I was more ambitious in what I was thinking. I wanted it to be similar to what I could do in EOF (hundreds of keys/sec).
>
> Does anyone here know if Java 1.5 supports obtaining the process ID natively? I think that was my stumbling point before. I was trying to make a key that contained server IP, process ID, timestamp, and counter. Maybe it is time to consider this again. If I could produce a unique string all the time like that, it would make a nice PK generator and your PKs would have some meaning (that is me with my production support hat on).
It sounds like you are looking for something like:
http://java.sun.com/j2se/1.5.0/docs/api/java/util/UUID.html
> As to your questions ...
>
> A) My gut feeling tells me that wouldn't be a good idea.
Could you be more specific?
We're replacing a horrid .asp + MS Access application, which
inevitably leads to some nausea :-)
> B) You could do that, but it seems pretty problematic to me.
More specific?
> Maybe some others will have a few good ideas ...
If I can have an MSAccess & Oracle & MS SQL adapter that supports
server generated keys, then that will also solve my problems. Might be
a bit quixotic though. :-)
--
Øyvind Harboe
http://www.zylin.com
Re: Generating primary key locally without extra tables
Posted by Øyvind Harboe <oy...@zylin.com>.
On 6/21/06, Gentry, Michael (Contractor) <mi...@fanniemae.com> wrote:
> I actually looked into making a PK generator that required no DB access, but when I looked into it, Java didn't support everything I wanted without resorting to JNI and C code. Of course, I was more ambitious in what I was thinking. I wanted it to be similar to what I could do in EOF (hundreds of keys/sec).
>
> Does anyone here know if Java 1.5 supports obtaining the process ID natively? I think that was my stumbling point before. I was trying to make a key that contained server IP, process ID, timestamp, and counter. Maybe it is time to consider this again. If I could produce a unique string all the time like that, it would make a nice PK generator and your PKs would have some meaning (that is me with my production support hat on).
It sounds like you are looking for something like:
http://java.sun.com/j2se/1.5.0/docs/api/java/util/UUID.html
> As to your questions ...
>
> A) My gut feeling tells me that wouldn't be a good idea.
Could you be more specific?
We're replacing a horrid .asp + MS Access application, which
inevitably leads to some nausea :-)
> B) You could do that, but it seems pretty problematic to me.
More specific?
> Maybe some others will have a few good ideas ...
If I can have an MSAccess & Oracle & MS SQL adapter that supports
server generated keys, then that will also solve my problems. Might be
a bit quixotic though. :-)
--
Øyvind Harboe
http://www.zylin.com
RE: Generating primary key locally without extra tables
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
I actually looked into making a PK generator that required no DB access, but when I looked into it, Java didn't support everything I wanted without resorting to JNI and C code. Of course, I was more ambitious in what I was thinking. I wanted it to be similar to what I could do in EOF (hundreds of keys/sec).
Does anyone here know if Java 1.5 supports obtaining the process ID natively? I think that was my stumbling point before. I was trying to make a key that contained server IP, process ID, timestamp, and counter. Maybe it is time to consider this again. If I could produce a unique string all the time like that, it would make a nice PK generator and your PKs would have some meaning (that is me with my production support hat on).
As to your questions ...
A) My gut feeling tells me that wouldn't be a good idea.
B) You could do that, but it seems pretty problematic to me.
Maybe some others will have a few good ideas ...
/dev/mrg
-----Original Message-----
From: oyvindharboe@gmail.com [mailto:oyvindharboe@gmail.com] On Behalf Of Øyvind Harboe
Sent: Wednesday, June 21, 2006 2:18 AM
To: cayenne-user@incubator.apache.org
Subject: Generating primary key locally without extra tables
Problem:
I'm using Cayenne for projects where I can't modify the database
schema on the server.
These databases use server generated primary keys. MSAccess, MS SQL
Server and Oracle.
The number of new records created are very small(max 1/minute), so I'm
rather insensitive to performance of record creation.
Q: Does a strategy for generating keys locally exist?
This strategy should work across databases where Cayenne does not
support server generated primary keys.
Getting a primary key by e.g.:
SELECT MAX(ID)+1 FROM FOO
This would a) be slow b) risk a collision when inserting.
Qs:
a) can I always override a server generated key?
b) would it be sufficient to use exception handling and retry e.g. N times?
--
Øyvind Harboe
http://www.zylin.com