Set auto-increment counter through QueryBuilder

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?

Can QueryBuilder figure out the DB type of the backend?

One use is to change the invoice numbering at the beginnging of the year (i.e. starting with 202300000 this year).

My approach would be to solve the “invoice numbering” issue on the application level. The database looks like the wrong place. Especially if you want to support multiple database backends.

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 :slight_smile:

I hear you. Alas, I have data to import that has its own dependencies and re-assigning amended indices means a lot of work. Especially as testing the legacy data for consistency would be very hard.

Thanks for the advice and the pointer to getDatabasePlatform !