Bug #605

when sequence_$table used insert returns 1

Details

Submitted	2004-01-21 19:41 UTC
From	fredrick dot leitz at sjrb dot ca
Status	No Feedback
Package	DB_DataObject
PHP Version	4.3.4
OS	FreeBSD 5.2
Roadmaps	(Not assigned)

Comments

[2004-01-21 19:41 UTC] fredrick dot leitz at sjrb dot ca

Description:
------------
When I enable sequence_response in my ini file insert() always returns one.

Reproduce code:
---------------
$response = DB_DataObject::factory("response");
$response->account = $_SESSION['LIB_QUESTION_ACCOUNT'];
$response->employee = $_SESSION['LIB_QUESTION_EMPLOYEE'];
$responseid = $response->insert();
echo "SEQUENCE: $responseid";

Expected result:
----------------
I expect the value returned to be the value inserted into the database.

Actual result:
--------------
Debug info below....

With out definining sequence_response

DB Error: unknown error
SELECT NEXTVAL('response_seq') [nativecode=ERROR: relation "response_seq" does not exist ]
dataobjects_response: QUERY: INSERT INTO response (response_id , employee , account ) VALUES ( 1 , 0234 , 01000000000 )
DB Error: unknown error
INSERT INTO response (response_id , employee , account ) VALUES ( 1 , 0234 , 01000000000 ) [nativecode=ERROR: duplicate key violates unique constraint "response_pkey" ]

When defining sequence_response
[DB_DataObject]
....
sequence_response = response_response_id_seq

dataobjects_response: QUERY: INSERT INTO response (employee , account ) VALUES ( 0234 , 01000000000 )
dataobjects_response: query: QUERY DONE IN 0.467092990875 seconds
dataobjects_response: 1: Clearing Cache for dataobjects_response
SEQUENCE: 1

[2004-01-21 19:44 UTC] fredrick dot leitz at sjrb dot ca

Oh, also when postgres creates a table the default naming convention for sequences is table_name_column_name_seq
so...
CREATE TABLE "response" ( response_id SERIAL );
produces a sequence
response_response_id_seq in PGSQL > 7.3
I don't know how hard it is to have DataObject generate the same default sequence for PGSQL

[2004-01-22 17:37 UTC] fredrick dot leitz at sjrb dot ca

The fix only fixes the problem when the ini file reads:
sequence_response = response_id:response_response_id_seq
when the ini file is set to:
sequence_response = response_response_id_seq
the problem still exists.