I need to alter the auto-increment ID of the next entry in a table on the DBMS I use as backend for Nextcloud.
One use is to change the invoice numbering at the beginnging of the year (i.e. starting with 202300000 this year). Another use is after a migration. I need to keep the IDs of source entries so the next-ID counter is wrong. Upon the next insert, the counter is incremented from the old value and an error is thrown because the new ID already exists.
On mySQL I would do a
ALTER TABLE oc_myapp_mytable AUTO_INCREMENT = 20;
On Postgresql (my DB of choice for now) I would do the same with
ALTER SEQUENCE oc_myapp_mytable_id_seq RESTART WITH 20;
However, I am reluctant to use direct DB manipulation and seek for an abstracted Doctrine / QueryBuilder approach.
Is there a way to alter the next increment value through Doctrine DB-agnostically?
I know that TYPO3 offers a GeneralUtility for such manipulation, maybe Nextcloud has also a helper.
If not, how can I issue a direct command to the DB through QueryBuilder? Can QueryBuilder figure out the DB type of the backend? Come to that, how can the DB type be determined over a mysqli adapter from PHP?
I can only second that statement. The database id should be just a “random” number. You can always add a column with a key that you keep track and that is styled as you need it.
Enforcing a certain numbering schema is in fact no good idea. Let the database do it’s work, it was created to do it. Do not try to be smarter than the devs of the database, non-contiguous numbers are in general no issue but you will have to write your own database storage database.
FWIW I too would not use the auto-increment ID in the database for the invoice number.
Sure, it is the way it would be done back in the days when people were actually using databases the way they were intended to be used, but I’d rather decouple that and generate the invoice ID separately (of course still having unique indexes etc on the corresponding column).
But both ways work of course, depending on your needs, requirements and what you want to plan for and potential problems you want to avoid. But why not just generate it separately