FAQ
Edit report at http://pear.php.net/bugs/bug.php?id=3773&edit=1

ID: 3773
Updated by: alan@akbkhome.com
Reported By: ej dot grace at imperial dot ac dot uk
Summary: Re: [PEAR-BUG] Bug #3773 [Ctl->Fbk]: PostgreSQL
SERIAL type not supported
-Status: Verified
+Status: Suspended
Type: Feature/Change Request
Package: DB_DataObject
Operating System: Linux
PHP Version: 4.3.10
Roadmap Versions:
New Comment:

-Status: Verified
+Status: Suspended
No patch available


Previous Comments:
------------------------------------------------------------------------

[2007-08-23 17:22:20] gabrielengel

IS this still NOT FIXED?!?!?!?

Here goes a script to fix the created DataObjects after the generator
creates them.

// Read the necessary variables from the command line arguments.
// This script is executed in technical/dbobj_class_generator.sh
// and you should not need to run it elsewhere.
list($command, $database, $databaseuser, $password,
$path_to_dataobjects) = $argv;

// Associative array mapping tables to primary key fields.
$pkeys = array();

// Connect to the database using the command line supplied
// parameters. Exit on failure.
if (!$connection = pg_connect("host=localhost port=5432
dbname=$database user=$databaseuser password=$password")) {
die("Couldn't connect\n");
}

// Select a list of primary key index names for each table.
$result = pg_query($connection, "
SELECT tablename,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
AND indexname LIKE '%_pkey'
");
while (($row = pg_fetch_assoc($result)) !== false) {
$pkeys[$row["tablename"]] = preg_replace("#^.*\(([^(]+)\)$#", "$1",
$row["indexdef"]);
}

// Open each DataObject file in turn in the user's sandbox.
foreach (glob("{$path_to_dataobjects}/*.php") as $dataobject) {

// Read all the contents into a string and look for a
// sequenceKey() method.
$code = file_get_contents($dataobject);
if (!preg_match("#function sequenceKey\s*\(\)#", $code)) {

// If the sequenceKey() method was missing, read out the
// table name and the sequence name.
if (preg_match("#var \\$\_\_table = '([^']+)';#", $code, $matches))
{
$table = $matches[1];
preg_match("#default_nextval%28public\.([^%]+)%29#", $code,
$matches);
@$sequence = $matches[1];

// If the table is in our list of primary keys, determine
// the field type of the primary key. If it's an integer,
// go ahead and create the sequenceKey() method.
if (array_key_exists($table, $pkeys)) {
$result = pg_query("SELECT $pkeys[$table] FROM $table LIMIT 1");
if (pg_field_type($result, 0) == "int4") {
$code = preg_replace("#(}\s*(\?>\s*)?)$#", "\n\tfunction
sequenceKey() { return array('$pkeys[$table]', true, '$sequence');
}\n\n$1", $code);

// Write the changed file back to the user's sandbox.
$file = fopen($dataobject, "w");
fwrite($file, $code);
fclose($file);
}
}
}
}
}

------------------------------------------------------------------------

[2006-10-15 22:12:46] alan_k

Changed to this (as per discussion on pear-dev)
$pgsql_key = $DB->getOne("SELECT currval('".$seq . "')");

This probably does not fix the SERIAL issue.. - but fixes the race
condition that is mentioned below.

------------------------------------------------------------------------

[2006-10-13 08:21:19] mcraig at leadehealth dot com

I've been working with sequences in PostgreSQL >=7.4.7 and a modified
DataObject for about 2 years without problems. The whole problem is in
DB with the sequence code. (Sorry I never got the fix in. I submitted
a change once but the version had bumped up a few times and my patch was
no longer usable.)

Regardless of using "SERIAL" or "nextval(...)" in the create table
command, Postgres keeps the string "DEFAULT nextval(...)" in the schema
definition and it is easy for DataObject to use it correctly.

I will submit a patch for this against the most current version of
DataObject because there is also a fix in there for a race condition
against selecting nextval from the sequence.

------------------------------------------------------------------------

[2005-07-06 02:35:35] alan_k

changing to a feature request. - still need to fix it though..

------------------------------------------------------------------------

[2005-06-27 04:40:21] ej dot grace at imperial dot ac dot uk
postgres has 2 ways to define sequences/serial etc.

id SERIAL, -which doesnt work

id default nextval('my_sequence') not null; - which works.
Indeed. SERIAL is in fact an alias for

DEFAULT nextval('tablename_colname_seq') not null
If we support SERIAL, how do you get the last inserted id back from the
insert() call?
I imagine in the same manner as it is done at the moment.

$pgsql_key = $DB->getOne("SELECT last_value FROM ".$seq);

I think the point is that it is getting the last_value from the wrong
SEQUENCE!


A more detailed (and possibly redundant) explanation follows..

=======================================

Ok, I should begin by saying I don't thoroughly know the inner workings
of DataObject.php or its design philosophy, however I think the
following stands to reason without this knowledge.

What I think is going wrong is that the case for pgsql is not
functioning correctly in ->insert() where the


// nativeSequences or Sequences..

// big check for using sequences

section occurs. For this reason I think it picks up the wrong sequence
name, e.g. from :


case 'pgsql':
if (!$seq) {
$seq = $DB->getSequenceName($this->__table
);
}
$pgsql_key = $DB->getOne("SELECT last_value
FROM ".$seq);
if (PEAR::isError($pgsql_key)) {
$this->raiseError($r);
return false;
}

My reason for this line of thought is the following:

If I define a table key as SERIAL the DataObject code (incorrectly)
creates a sequence table named:

table_seq

e.g. person_seq

it if does not exist which DataObject uses to keep track of sequences,
whereas for SERIAL postgres defines the sequence:

table_column_seq

e.g. person_id_seq

which *it* uses to keep track of IDs. The fact that there can be two
separate sequences relating to the same table and key is what causes the
mismatch between ->insert() and INSERT.

If one can make the DataObject use the correct sequence, i.e.
table_column_seq instead of table_seq then it will behave in a
consistent manner with the SQL INSERT.

This is why defining the automatically incrementing key with:

CREATE TABLE tablename (
id BIGINT DEFAULT nextval('tablename_seq') NOT NULL,
..
..
..
);

works. Since it forces Postgres to generate a sequence called
tablename_seq, which is the same sequence DataObjects will use to keep
track of keys. Rather than tablename_id_seq which is what would be
generated using SERIAL as the type.

After all SERIAL is just an alias for

BIGINT DEFAULT nextval('tablename_colname_seq') NOT NULL

in postgres.

-ed

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://pear.php.net/bugs/bug.php?id=3773

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedApr 25, '10 at 11:03p
activeApr 25, '10 at 11:03p
posts1
users1
websitepear.php.net

1 user in discussion

Alan: 1 post

People

Translate

site design / logo © 2022 Grokbase