Re: DataObject postgresql problems

Paul Cooper wrote:

On Mon, 2003-06-23 at 14:56, Jeroen Houben wrote:

Hi, My table definition looks like this: CREATE TABLE "users" ( "user_id" SMALLINT DEFAULT nextval('users_id_seq::text') NOT NULL, // I should get rid of ::text, but it was created automatically migrating from MySql "fullname" CHAR(60) DEFAULT '' NOT NULL, "username" VARCHAR(10) DEFAULT '' NOT NULL, "pwd" VARCHAR(40) DEFAULT '' NOT NULL, "email" VARCHAR(150), PRIMARY KEY("user_id"), UNIQUE("username") ) WITH OIDS; CREATE INDEX "idx_pwd_users_index" ON "users" USING btree ("pwd"); Problem is that it tries to to skip the username column on inserts. Possibly because it thinks it's a primary auto incrementing key or something. This is the query that gets send to PG:

Yes it sees the unique key and leaves the field alone. Two things you can do (apart from work out a permanent fix of course) is edit your generated .ini file and edit the [users__keys] section removing the username = 2 entry. Or remove unique from line 284 of Generator.php, e.g.

           if (preg_match("/(primary|unique|nextval\()/i",$t->flags)) {

and regenerate your classes. Beware - I'm not sure if this breaks things for other databases as I'm only using postgres at the moment. I removed the 2 from the .ini file. Seemed the cleanest way to me, but i really think this is a bug in DataObjects.

INSERT INTO users (user_id , fullname , pwd , email ) VALUES ( 4 , 'dsasd' , 'fdsasdasddsa' , 'dsa@dsadas.nl' ) [nativecode=ERROR: Cannot insert a duplicate key into unique index users_username_key ] Another problem is that it takes a wrong number for the sequence. It should be 120. It creates it's own sequence and uses values from that:

in your [DB_DataObject] section of your ini file you can specify the correct sequence, e.g. sequence_users = users_id_seq yes that worked. Well I had to do

sequence_users = users_id because it automatically adds the "_seq" prefix.

this is an effect of the default behaviour of DB with sequences, which is to use tablename_seq and create it if it doesn't exist (unless you tell it otherwise). HTH, It did!

Jeroen