Re: Re: FormBuilder and QuickForm_Controller

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Justin, > Yes, looks like DB and/or DB_DataObject should fix this. Adding a bug > for DB_DO is a good idea if one doesn't exist for this yet. However, > there is a workaround, see below. I think I've cracked it, See section [THE FIX]), however for the sake of completenes.. Overloading the DO sequenceKey() to function sequenceKey() { return array('id',true,false); } OR function sequenceKey() { return array('id',true,true); } Causes the ->insert() method to return bool(false) and nothing to be inserted. > should. Please try this and tell me if it works or not. Doesn't work. > > The other (less than fashionable) way, is to avoid using SERIAL (or > > indeed specifying it as auto-incrementing) when creating the table. That > > way the INSERT command must explicitly specify the key. Ugly as hell, > > but may have to do for now. > > I don't see how this is any different from using SERIAL...if it's > SERIAL the DB auto-gen's the id. If it's AUTO INCREMENT the DB auot > generates the id. I don't see the difference. Sorry, I wasn't clear. My point was to give up on consistency between - ->insert() and INSERT and avoid the use of autogenerated values, however I've worked out the fix. THE FIX ======= Ok. I think I have cracked it, the logic to my conclusion is as followe. If one specifies the table as follows: CREATE TABLE idclash ( id BIGINT, PRIMARY KEY (id) ); In other words the primary key is NOT flagged as automatically incremented by the DB then with a command line INSERT you have to specify the key (obviously). When you use ->insert() however, it magically generates a sequence table called: idclash_seq Which (surprise surprise) contains the sequence information used by - ->insert(). In fact, what is happening is the default DO behaviour is to look for a SEQUENCE table called [TABLENAME]_seq, rather than the one generated by the SERIAL specifier which is called [TABLENAME]_[COLUMNNAME]_seq. Hence the confusion! I think this comes down to the ->keys() member function in the DO not returning the key field name correctly. The result of this is that it's not available to be used to generate the sequence table name, hence the missing _[SERIALFIELD] in [TABLENAME]_[SERIALFIELD]_seq. - From the PostgreSQL manual, SERIAL is short hand for http://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-SERIAL integer DEFAULT nextval('tablename_colname_seq') NOT NULL So dropping the (sequence) tables idclash idclash_seq idclash_id_seq if they exist to start from scratch and creating the table using CREATE TABLE idclash ( id BIGINT DEFAULT nextval('idclash_seq') NOT NULL, dat VARCHAR(255) DEFAULT '', PRIMARY KEY (id) ); Means that the DB will look for sequence values in the SEQUENCE idclash_seq, the same SEQUENCE table that ->insert() will use! Problem solved! It now works. So now doing - ->insert() - ->insert() INSERT INTO idclash (id) VALUES (default); - ->insert(); INSERT INTO idclash (id) VALUES (default); works as expected and gives us ojdb=# SELECT * FROM idclash; id | dat - ----+----- 1 | 2 | 3 | 4 | 5 | (5 rows) Phew! That was all rather involved! Of course, with the benifit of hindsight I should have spotted something fishy as I have SEQUENCE tables with _[field]_seq and _seq sufficies for all my tables! So. In conclusion. The default behaviour for the DO when encountering a SERIAL type is wrong. Rather than looking for the key sequence in the sequence table [TABLENAME]_[FIELDNAME]_seq it is currently looking in [TABLENAME]_seq and generating it if it doesn't exist. The consequence being that there are now two (potentially conflicting) sources of sequence information. Does this make sense? I find it a bit worrying that for a mature (non Beta) library such as DB::DataObject, which is supposed to be DB agnostic, getting autoincrementing primary keys is 1) such a hastle 2) not DB agnostic (MySQL ok, PostgreSQL not) - -ed -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCMZbK+i4TdNhCMrYRAi0HAJ9AbRaB6a7T0s4A6am6QVFBw9xDbgCfbTCX OMScXwRiFQUf8L49wLbB/aY= =mNFh -----END PGP SIGNATURE-----