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. 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. 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. 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. jpb -- Joe Block <jpb@ApesSeekingKnowledge.net> Benchmarks are like bikinis -- what they reveal may be enticing, but the details that they conceal are crucial.

On Tue, Jan 15, 2002 at 02:33:22AM -0500, Joe Block wrote:

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.

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.

jpb -- Joe Block <jpb@ApesSeekingKnowledge.net>

You can create a function in PostgreSQL which will do the insert in the jobs table, and return the id it created. Something like this works for us in a similar situation: DROP FUNCTION insert_person(text); CREATE FUNCTION insert_person(text) RETURNS int4 AS 'DECLARE id INT4; BEGIN id := nextval(\'tbl_People_idPeople_seq\'); INSERT INTO "tbl_People (id,iduser) VALUES ( id, $1); RETURN id; END;' LANGUAGE 'plpgsql'; Then in your ZSQLMethod, you would do something like this: select insert_person(<dtml-sqlvar iduser type="string">); HTH, Chris

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 )

Chris Kratz <chris.kratz@vistashare.com> writes:

On Tuesday 15 January 2002 01:28 pm, Chris Meyers wrote:

...

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:

We've tested this pretty extensively in our situation and with Psycopg the entire request is in a single transaction so there is no problem with another transaction "sneaking in". Zope handles this automatically for you with psycopg and postgres as long as you don't try to do the transactional work yourself. You can verify this by turning on logging in postgres and watching the logs.

-Chris

I am glad that I am not the only one that has notice this "correct" behavior. I especially like the fact that Zope will happily roll back PostgreSQL transactions on failure. That makes combining Zope and PostgreSQL so much easier. Of course asking for a unique id from the sequence in one ZSQL method (or Python Script or whatever) with nextval and then inserting it works fine too. Using currval simply saves a step, you only have to write the one ZSQL method (Hey, I'm lazy).

On Wed, 16 Jan 2002 10:11, Chris Kratz wrote:

The only caveat I have found with this behavior was again "by accident". If you are in a python script or dtml and use a try-except clause to catch an error within zope, the rollback won't happen. "Catching" the exception keeps zope from triggering the rollback machinery on the database. One solution is to rethrow the exception after you have done your own processing.

get_transaction().abort() will also roll the transaction back. Similarly, get_transaction().commit() will commit. Richard

On Tuesday 15 January 2002 02:33 am, you wrote:

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.

jpb

Hello Joe, I don't know if you have found it yet, but in postgres if you create a table with the primary key of type serial, you are guarranteed a unique id. This creates a sequence behind the scenes which the table pulls the next id from. You can then retrieve the inserted value by querying the sequence for the current value. Since everything within a single request in zope is within a transaction (given that you don't try to roll your own transaction code which is a very bad thing unless you know what your doing...), you will always get back the id of the record you just inserted. For simplicity, I usually put them in separate ZSQL statements (one does the insert and the second pulls the key for the inserted record), but you could include them within the same ZSQL statement if you would like. It makes no difference in their execution if they are separate or together. For example, if you create a table like this... create table simpletable ( idnum serial primary key, comment text); psql will return... NOTICE: CREATE TABLE will create implicit sequence 'simpletable_idnum_seq' for SERIAL column 'simpletable.idnum' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'simpletable_pkey' for table 'simpletable' CREATE or if you already created your table with a serial type, you can inspect the sequence name with \d simpletable Table "simpletable" Attribute | Type | Modifier -----------+---------+----------------------------------------------------------- idnum | integer | not null default nextval('"simpletable_idnum_seq"'::text) comment | text | Index: simpletable_pkey Notice it uses the nextval function as a trigger to get the next available key value. The sequence name again is in the double quotes. If you want to create and add a sequence, you can do that, but it is a little more complicated. See the postgres doc for instructions. Now, since we know the sequence name 'simpletable_idnum_seq', we can place the following two statements into zope ZSQL objects: insert into simpletable(comment) values('Some comment here'); select currval('simpletable_idnum_seq'); The second select calls a function which will always return the value of the last record you inserted. The magic is that this will work across multiple threads (Zope and postgres keep things straight) regardless of the number of transactions, zope threads, or postgres threads that are currently running. Hope that helps, -Chris -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com

Actually to add this I wrote on new how-to and found an old one: - using guid's on Windows: http://www.zope.org/Members/andym/GUID - using FSCounter: http://www.zope.org/Members/andym/FSCounter/unique_ids HTH. ----- Original Message ----- From: "Andy" <andy@agmweb.ca> To: "Joe Block" <jpb@ApesSeekingKnowledge.net>; "Zope List" <zope@zope.org> Sent: Monday, January 14, 2002 10:09 PM Subject: Re: [Zope] q: How should I get a guaranteed unique id in Zope?

Along with all the other suggestion the FSCounter product is a counter in the file system. I use this to happily generate incrementing values.

----- Original Message ----- From: "Joe Block" <jpb@ApesSeekingKnowledge.net> To: "Zope List" <zope@zope.org> Sent: Monday, January 14, 2002 6:34 PM Subject: [Zope] q: How should I get a guaranteed unique id in Zope?

...

I just discovered Zope a couple of weeks ago, and have started migrating a cgi based web application that I was writing in straight python to Zope both as a learning exercise and to take advantage of Zope's feature set.

My problem is, I have several sql tables that I'm storing data in that I need to be able to have the same jobid in because I'm using the jobid to match which comments are attached to which jobs. When I was using cgis, this was easy - I made a class that wrapped the following function

def makeID(self): idstring = "%s-%s.%s-%s" % (self.prefix, os.getpid(), time.time(), self.counter) self.counter = self.counter + 1 return idstring

and I could be guaranteed their uniqueness because prefix was the hostname of the server the cgi was running on, and the combination of the other 3 parts was unique per call to makeID.

Now that I'm using Zope, the pid isn't unique any more, of course. I don't want to store a counter in the database, because from some of the postings I saw in the last few days here I think it'll cause my db to grow for no reason as it stores unwanted undo information. I also haven't figured out how to lock an object while I update properties yet. I don't want to use an external file either, because the constant locking & unlocking seems likely to cause performance problems eventually.

I have both _The Zope Book_ and _Zope Web Application Development and Content Management_ , and haven't found an answer to my problem in either book as yet.

I'm running Zope 2.5.0b3 with Python 2.1.1 on Mac OS X 10.1.2

Thanks,

jpb -- Joe Block <jpb@ApesSeekingKnowledge.net>

Madness takes its toll. Please have exact change ready.

_______________________________________________ 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 )

_______________________________________________ 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 )