fix(DB): update tables_row_sleeves' sequence after migration

[blizzz]

fixes #1029

On (at least) Postgres the migration logic from 0.6.6 to 0.7 inserts rows into oc_tables_row_sleeves with the id parameter set. It is an autoincrement column, but when the ids are specified manually, the backing sequence is not updated automatically. It then leads to constraint violation when adding new rows.

Doctrine does not offer much to interact with sequences, so here is a fall back a manual query that follows SQL standard.

[blizzz]

ftr: further read

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-SEQUENCE.html
• https://www.postgresql.org/docs/current/sql-altersequence.html

[Sylvain303]

I would say no, it wont fix #1029 given my last comment and rows that are left in the oc_tables_rows or would it suffice?

I mean given I deleted the Tables (from UI interface), but the rows are kept in oc_tables_rows, but the select max(id) from oc_tables_row_sleeves; may have caught the correct max(id) , if I wouldn't have deleted the Tables before... 🤔

[blizzz]

I would say no, it wont fix #1029 given my last comment and rows that are left in the oc_tables_rows or would it suffice?

I mean given I deleted the Tables (from UI interface), but the rows are kept in oc_tables_rows, but the select max(id) from oc_tables_row_sleeves; may have caught the correct max(id) , if I wouldn't have deleted the Tables before... 🤔

That's great input! I'd read then both table's IDs and take the max value.

[Sylvain303]

That's great input! I'd read then both table's IDs and take the max value.

May you should also figure out why rows of deleted Tables are left in oc_tables_rows too? If it's reproducible...

may occ maintenance:repair or occ tables:repair (⬅️ command doesn't exist actually) or such should cleanup something here. 🤷‍♀️

[blizzz]

May you should also figure out why rows of deleted Tables are left in oc_tables_rows too? If it's reproducible...

Definitely, but would separate it from this context.

[blizzz]

May you should also figure out why rows of deleted Tables are left in oc_tables_rows too? If it's reproducible...

Definitely, but would separate it from this context.

So that is, because oc_tables_rows is a legacy table and not in further use. It is kept only as a safety measure around the migration, for instance to be able to re-trigger the transfer to the modernized DB structure.

[blizzz]

Repair steps takes both IDs now into account.

[blizzz]

/backport to stable0.7