Hello Gerhard,
> A few weeks ago, I tried to make a PostgreSQL backend for MiddleKit
> using pyPgSQL. Unfortunately, it couldn't pursue this any further
> because of a (IMO) bad design decision of MiddleKit:
>
> It depends on there being something like MySQL's last_insert_id
> available.
I'm also interested in working on PostgreSQL support for MiddleKit
(I'm currently developing an app using MiddleKit/MySQL, but I prefer PostgreSQL
and would like to migrate to PostgreSQL before deployment).
I've been thinking about the problem of the last_insert_id from the point
of view of the application developer, instead of the MiddleKit developer.
The expected behaviour from MiddleKit is that after adding an object
to the store using
store.addObject( myobject )
the object should have been assigned a unique identifier. A quick grep
throuh my code looking for occurences of addObject followed by "sqlObjRef"
or "serialNum" came up with about 9 hits.
I think it is a common pattern in applications to create an object and then
want to save the reference to it (i.e. in the session) so that further
modifications can be made to the object.
The alternative (if we were to change the semantics so that last_insert_id is
not required) would be to create the object, throw the instance away,
and then query the store to get a fresh instance of the object with
the id. One problem with this is that it's inconvenient, but the
bigger problem is how to write the query so that we do indeed get back
the same object which we created. Since we have no unique identifier,
it's not possible.
(0) I strongly suggest removing this limitation, because it
> simply cannot be done with PostgreSQL.
As I've been arguing, I think it is a natural in application development
to want a unique identifier for an object after adding it to the store.
As you've pointed out, since PostgreSQL has no last_insert_id, it's necessary
to do things a little differently to achieve the same effect.
> 1) You select nextval() from a sequence, then use the id in your INSERT
> statement; this means you must know the name of the id column and the
> name of the sequence in MiddleKit (possible, but suboptimal)
This gets my vote. Since sequences increment atomically, after doing
the "select nextval()" you are guaranteed that no one else will get
the same number. I've used the same technique with PostgreSQL from
PHP applications.
I'm reasonably familiar with MiddleKit, and I think
it would be fairly straightforward to modify it to support this, so that
it's not necessary to override too much SQLObjectStore functionality in
PgSQLObjectStore. In MiddleKit the name of the id column is derived
from the name of the class, so this information is easy to get. I would
write the PostgreSQL code generator to generate a sequence corresponding
to each non-abstract class in the model (i.e. one sequence per table), and
would use a naming scheme such that the sequence name can be derived
from the class name.
> 2) You make an INSERT statement on the table, which has an id primary
> key "default nextval ..." on the sequence; then make another select
> currval() on the sequence to get the last inserted id; this means you
> must know the name of the id column and the name of the primary column
> in Middlekit (possible, but suboptimal)
The problem with this is that it's possible for the sequence to be
incremented by a different database connection between doing the
"insert" and "select currval" statements, so you're not guaranteed
to read the same value from the sequence that was used in the insert.
> 3) You make an INSERT statement on the table, don't use any id columns
> at all, but abuse PostgreSQL's object ids. So next, you just ask
> cursor.oidvalue in pyPgSQL to get the object id of the last inserted
> row; which you could in turn use to get the value of the id column
> (ugh!)
I don't know enough about PostgreSQL object ids to know why this
is ugly, but I'm sure accessing sequences is quicker than doing
a select, so performance-wise this solution wouldn't fare as well
as (1).
I would be very happy to work together with you to get this working,
since, as I've said, I have a need for PostgreSQL support in MiddleKit.
I don't have any experience using PostgreSQL from Python (yet), but I am
familiar with MiddleKit and I've done quite a lot using PostgreSQL from PHP.
In any case, I'd be interested in taking a look at your code, if you'd
like to send me a copy.
peace,
Jason
_______________________________________________________________
Hundreds of nodes, one monster rendering program.
Now that's a super model! Visit http://clustering.foundries.sf.net/
_______________________________________________
Webware-discuss mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/webware-discuss
