[Zope] q: How should I get a guaranteed unique id in Zope?

[Chris Meyers]

On Tue, Jan 15, 2002 at 10:08:49AM -0700, Jason Earl wrote:
> Joe Block <jpb@ApesSeekingKnowledge.net> writes:
> 
> > On Tuesday, January 15, 2002, at 12:36 , Troy Farrell wrote:
> > 
> > > Mmmm. Good point.  Joe, I'd assumed you were storing the data in a
> > > python product/class object in the ZODB.  Grand.  Now I'm second
> > > guessing myself.
> > >
> > > Joe, can you clarify your architecture a bit?  What DB?  Are you
> > > moving the cgi(s) to a python product?  A set of external methods?
> > 
> > I'm writing a job tracking system. It's an extremely simple database
> > - one table with the job specific data, and another for the comments
> > attached to the jobs.  I'm using PostgreSQL as the back end, but I
> > don't have a lot of experience with it yet.
> 
> One of the coolest things about Zope and PostgreSQL is that Zope will
> happily manage your PostgreSQL transactions.  Learning both Zope and
> PostgreSQL at the same time makes things more difficult, but
> PostgreSQL has excellent documentation, and Zope's documentation is
> getting pretty good as well.
> 
> Both have excellent mailing lists.  I would highly suggest at least
> subscribing to pgsql-novice@postgresql.org.  There's very little
> traffic, and you will undoubtedly learn some cool tricks.
> 
> > The current system is a bunch of separate cgis, using a home-brewed
> > framework that frankly, is kind of awkward to deal with in a lot of
> > ways.  I sat down to break the code into a more logical layout
> > before adding some more features, and decided it would be worth some
> > time to look for another framework to use before investing too much
> > more time into my homebrewed cgi framework, and discovered
> > Zope. Zope has made my life easier in a lot of ways, but the one
> > nice thing about running each job or comment creation as a separate
> > cgi invocation was making it easy to create unique IDs to use in my
> > inserts.
> 
> You should let PostgreSQL do this for you.  There are all sorts of
> things that can go wrong when creating unique ids, and PostgreSQL has
> already solved most of them for you.
> 
> Take a look at the documentation for sequences, they are precisely
> what you need.  Basically your table should have a primary key that is
> generated from a sequence.  Something like this:
> 
> CREATE SEQUENCE job_id_seq;
> 
> CREATE TABLE jobs (
>         id              int PRIMARY KEY DEFAULT nextval('job_id_seq'),
>         the rest of your table ...
> );
> 
> When you insert into this table you if you don't specify an 'id' it
> will automatically generate a unique one for you.  Just like magic!
> 
> > The awkward thing is, when I create a job, I have to simultaneously
> > create a comment to attach to it with information about who
> > initially created the job, and some descriptive data, so I need to
> > know what jobid the job gets so I can add the comment to the comment
> > db with the appropriate jobid identifier.
> 
> This is what I have done in similar situations.  I would create a ZSQL
> statement that first inserts the job and then returns the job id.
> Something like this should work.
> 
> INSERT INTO jobs (foo) VALUES (<dtml-sqlvar foo type="int">) --notice no 'id'
> <dtml-var sql_delimiter> -- abstraction for PostgreSQL ';'
> SELECT currval('job_id_seq') -- This returns the new job id.

This works really well, but could cause problems if the db is heavily used. Another transaction could sneak in between your INSERT and the SELECT and throw off your count (unless Zope wraps ZSQL Methods in a transaction block which I am not sure about). You could do something like this if you want to be really sure that no new ids are slipped in:

--ZSQL method(foo) to get job id:

SELECT nextval('job_id_seq');

--ZSQL method(bar) to insert the data into the jobs table:

INSERT INTO jobs (job_id,data)
VALUES(<dtml-sqlvar jobID type="int">,<dtml-var data type="string">);

##python script (or DTML or whatever) to get and use the job id:

jobID=context.foo()[0][0]
context.bar(jobID=jobID)
return jobID

Chris

> 
> > I'm probably missing something in postgresql where I can do an
> > insert and have it return whatever jobid it automatically generates.
> > On the Zope side, I'm using ZPsycoPg as my postgres connection.
> 
> If you have any further questions feel free to ask.
> 
> Jason
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )