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

[Jason Earl]

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.

> 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