slashcode-development

[Slashcode-development] PgSql no LAST_INSERT_ID()

[James E. M. <jm...@tr...>]

Hi,

seems like Slash/DB/MySql.pm, line 3068, is calling a sql_select
for LAST_INSERT_ID(), a MySQL extension that I can't find an 
analog for in PostgreSQL.  But I don't have much experience with 
either database.  

This causes lots! of errors on install_slashsite.  Any advice?

And sorry if this has been covered before.  I've been reading this 
mail list for a while, and I've seen pgsql comments, but I couldn't
find any archive of the older email messages, just what seemed like
March.  And a search on slashcode for PostgreSQL wasn't very 
enlightening either.

james

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Alessio B. <al...@al...>]

James Edward Marca wrote:

> seems like Slash/DB/MySql.pm, line 3068, is calling a sql_select
> for LAST_INSERT_ID(), a MySQL extension that I can't find an
> analog for in PostgreSQL.  But I don't have much experience with
> either database.

It's SELECT currval('templates_tpid_seq'), i.e. the latest sequence
number assigned to templates.tpid, which is a SERIAL (auto-increments).

In the createTemplate subroutine has to be used as

return $self->sqlSelect('currval(\'templates_tpid_seq\')';

I would provide a patch, but PostgreSQL.pm has much less code than
MySql.pm! :-( Means most of the routines are still to be written?

-- 
Alessio F. Bragadini		al...@al...
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"
		-- The Eighth Networking Truth from RFC 1925

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Chris N. <pu...@po...>]

At 10:56 +0300 2001.03.28, Alessio Bragadini wrote:
>James Edward Marca wrote:
>
>> seems like Slash/DB/MySql.pm, line 3068, is calling a sql_select
>> for LAST_INSERT_ID(), a MySQL extension that I can't find an
>> analog for in PostgreSQL.  But I don't have much experience with
>> either database.
>
>It's SELECT currval('templates_tpid_seq'), i.e. the latest sequence
>number assigned to templates.tpid, which is a SERIAL (auto-increments).

Is this per-connection, like last_insert_id is in MySQL?  So if two
separate httpds do inserts at the same time, would they both get back the
same value if they then asked for currval('templates_tpid_seq')?  If so,
that's a problem.

>I would provide a patch, but PostgreSQL.pm has much less code than
>MySql.pm! :-( Means most of the routines are still to be written?

No, not at all.  PostgreSQL inherits from MySQL.  So where the code does
not need to be changed for PostgreSQL (which is most places), there is no
need to write that code in the PostgreSQL module.  It will just pick it up
from MySQL.pm if it is not in PostgreSQL.pm.  That said, there are places,
like this one, that do need porting, and haven't yet been ported.

I think the best course of action is for PostgreSQL users to work with us
(especially Brian, when he has the time) to do the porting, if they can.
It is, unfortunately, not a high priority to get PostgreSQL support in 2.0
finished; however, the code is all there, and that's what Open Source is
about.  :-)  Again, we'll work with developers who want to get this done,
we just can't spend much time on it ourselves.

-- 
Chris Nandor                      pu...@po...    http://pudge.net/
Open Source Development Network    pu...@os...     http://osdn.com/

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Alessio B. <al...@al...>]

Chris Nandor wrote:

> Is this per-connection, like last_insert_id is in MySQL?

Yes.

> So if two
> separate httpds do inserts at the same time, would they both get back the
> same value if they then asked for currval('templates_tpid_seq')? 

Nope, it's safe and backend-isolated.

> No, not at all.  PostgreSQL inherits from MySQL.

Ahh, sorry. I thought they were at the same level (didn't check
carefully the module).

> places,
> like this one, that do need porting, and haven't yet been ported.

Ok, I will check if they are MySql-isms :-) that haven't been ported.

-- 
Alessio F. Bragadini		al...@al...
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"
		-- The Eighth Networking Truth from RFC 1925

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Brian A. <br...@ta...>]

Alessio Bragadini wrote:
> > places,
> > like this one, that do need porting, and haven't yet been ported.
> 
> Ok, I will check if they are MySql-isms :-) that haven't been ported.
Thats great. I would be happy to accept patches from you for PostgreSQL.
There have been queries from people about using it, but its not
really fully functional yet.

How many cases of the LAST_INSERT_ID's are you finding? It might
be easier to just wrap this call in a method and have each module
make its own call. 

	-Brian

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Alessio B. <al...@se...>]

Brian Aker:

> How many cases of the LAST_INSERT_ID's are you finding?

A quick grep found just 3 of them.

> It might be easier to just wrap this call in a method and have each
> module make its own call.

Maybe. What I'd like to see wrapped in a method is (UN)LOCK TABLES: a
generic method that on MySql really locks and on PostgreSQL and Oracle
just does a No-op. :-)

This would be better than copying the whole subroutine.

-- 
Alessio F. Bragadini		al...@se...

http://www.citinv.it

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Brian A. <br...@ta...>]

Alessio Bragadini wrote:
> > It might be easier to just wrap this call in a method and have each
> > module make its own call.
> 
> Maybe. What I'd like to see wrapped in a method is (UN)LOCK TABLES: a
> generic method that on MySql really locks and on PostgreSQL and Oracle
> just does a No-op. :-)
Send patches :)

	-Brian

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Alessio B. <al...@al...>]

Brian Aker wrote:

> Send patches :)

I will do it probably later today or tomorrow. I think it's wise to
change as little code as follow, but use Zach Beane's "mini-library"
<http://www.xach.com/aolserver/mysql-to-postgresql.html>. 

Once we have from_unixtime, to_days etc., there is only need for an
handful of changes.

-- 
Alessio F. Bragadini		al...@al...
APL Financial Services		http://village.albourne.com
Nicosia, Cyprus		 	phone: +357-2-755750

"It is more complicated than you think"
		-- The Eighth Networking Truth from RFC 1925

Re: [Slashcode-development] PgSql no LAST_INSERT_ID()

[Brian A. <br...@ta...>]

Alessio Bragadini wrote:
> It's SELECT currval('templates_tpid_seq'), i.e. the latest sequence
> number assigned to templates.tpid, which is a SERIAL (auto-increments).
> 
> In the createTemplate subroutine has to be used as
> 
> return $self->sqlSelect('currval(\'templates_tpid_seq\')';
> 
> I would provide a patch, but PostgreSQL.pm has much less code than
> MySql.pm! :-( Means most of the routines are still to be written?
Pg inherits from MySQL. The patch would be to copy the method from MySQL
to the Pg file and fix it to work with PostgreSQL.
	-Brian