*_seq number is not correct after a non p4a hast inserted a new record into a table

[zukunft]

*_seq number is not correct after a non p4a hast inserted a new record into a table

[fballiano]

Is there the possibility that someone inserted some records in the table without using p4a sequences management functions?

[zukunft]

Exactly that. Sure it is a solution to use the p4a management functions also for all non p4a inserts. But I think with a few changes p4a can also "recover" the _seq id correctly. Probably something like "SELECT max($PK) FROM $table;" could work.

[croaker000]

This mask will allow you to manually reset the ID tables. It's possible to add elements of this code to the construct section of a mask to fix any issues between a manual insertion and the mask opening. If data is incoming simultaneous to the mask being open, I guess you'd need to check on an onSave event.

It's a clunky solution to a real problem I've come across in about 3 projects i the last 12 months or so. It would be great if this whole method of indexing was dumped for something more robust.

build("p4a_db_source", "s1") ->setTable("rota") ->setPk("id"); $this->build("p4a_db_source", "s2") ->setTable("service_users") ->setPk("id"); $this->build("p4a_db_source", "s3") ->setTable("staff") ->setPk("id"); $this->build("p4a_db_source", "s4") ->setTable("worktypes") ->setPk("id"); $this->build("p4a_db_source", "s5") ->setTable("leave") ->setPk("id"); $this->build("p4a_button", "btn_resync") ->setLabel("resync") ->implement("onclick", $this, "resync_seq_table"); $this->frame->anchor($this->btn_resync); $this->display("menu", P4A::singleton()->menu); $this->setTitle(P4A::singleton()->storename." :: Tables Repairer"); } public function resync_seq_table() { $res = $this->s1->resync_seq(); $res = $this->s2->resync_seq(); $res = $this->s3->resync_seq(); $res = $this->s4->resync_seq(); $res = $this->s5->resync_seq(); } ``` } ?>

[croaker000]

... and the library file to go with it

./p4a/libraries/p4a_db_source_resync_seq.php

getTable(); $pKField = $source->getPk(); $seqTable = $table."_".$pKField."_seq"; $lastId = P4A_DB::singleton()->fetchOne("SELECT MAX($pKField) FROM `$table`;"); $query = "DROP TABLE IF EXISTS `$seqTable`"; $resQ1 = P4A_DB::singleton()->query($query); $query = "CREATE TABLE `$seqTable` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=$lastId"; $resQ2 = P4A_DB::singleton()->query($query); $query = "INSERT INTO `$seqTable` VALUES ($lastId)"; $resQ3 = P4A_DB::singleton()->query($query); return ($resQ1 && $resQ2 && $resQ3); } ?>

[fballiano]

actually you should use p4a's functions to get a new sequence number when you're using those table from outside.

btw with p4a3 it should be able to use autoincrement primary keys instead of p4a's sequences (with were made strictly for data portability between different db engines), you could try disabling P4A_AUTO_DB_SEQUENCES, what do you think?

[zukunft]

Thanks. Yes, I think this is the best solution and I set P4A_AUTO_DB_SEQUENCES in /p4a/p4a/constants.php to false. It seems to work fine. Probably the default setting should be "false", if the database engine supports it. Do you know if there is any database engine, where P4A_AUTO_DB_SEQUENCES should be true?

[fballiano]

it's true for historical reasons and now we cannot change this value cause it would break existing data. :)

it should be true for a specific database engine, the problem is data portability between engines.