[EN] SQL exception during update

[rakekniven]

app version before update: v1.2.5
app version to update to: v2.0.1

SQL exception:
An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "CHANGE" LINE 1: ALTER TABLE "oc_gestion_client" CHANGE "siret" "legal_one" L... ^

System info:

• NC version 23.0.0
• DB type: PostgreSQL v10.15
• OS: Ubuntu 20.04 LTS
• Webserver: Apache 2.4.41

[baimard]

Hotfix is to change manually in database
Table : oc_gestion_client
column : siret by ==> legal_one

table : oc_gestion_configuration
column : siret by ==> legal_one
column : siren by ==> legal_two

I will fix in next version

[rakekniven]

Worked for me.

[baimard]

Need to check the exact syntax for postgre sql to alter table. It's because I don't want to drop column and create it.

[vitormattos]

Hi @baimard,
Looking the code I see that you stay using the same migration to do anything.
Will be good create new migrations all times that you need change the database.

[baimard]

Hi @vitormattos,

Why you tell me that, I have just the same migration file in version 2.0.0 and 2.0.1 because of big bug in migration file in the version 2.0.0. Except of this last two version, I always create a new migration file.

Actualy, the last migration file are in version 7

[vitormattos]

This is the problem. You create a new and remove the last and put all changes on a new migration file. You have only one migration file. You need maintain the last and create a new.

Look this for example:

https://github.com/nextcloud/spreed/tree/master/lib/Migration

The migrations need be incremental.

Every that you need do a new database change, you need create a new migration.
Example:

occ migrations:generate gestion 20001

This will generate a new migration to version 2.0.1 with the date of migration and you will put on new migration only the news changes. The name of file will be generated automatically: Version20001Date20220131222953.php

If you use new files to run migrations, all executed migrations will be stored on table **prefix**migrations. Look the content of this table to get more examples.

[baimard]

Thank you for all informations !

That really help me !

[baimard]

@vitormattos
I have to keep all migration files in the folder?

[vitormattos]

Yes!

[vitormattos]

The migrations is incremental. Test locally and create a new migration with the occ command that I send on previous comment. Only to test, don't put anything on new migration file. After, run occ migrations:migrate gestion, this will run the migration and put on table migration a new entry flagging that the new migration was executed. Now, to test, create one more migration and run one more time the migrate command and look one more time the content of table migrations.

[vitormattos]

Talking about development scenario...
Don't is possible do rollback, Nextcloud haven't support to rollback migrations. If you need do a rollback, delete the entry on table migrations of last executed migration and revert manually the change that this migration did.

[vitormattos]

For now, don't recreate the old migrations, maintain the actual migration and to new databases updates, create new migrations.

You only will need change old migrations files if the old file stay generating an error and can't run.

[baimard]

The migrations is incremental. Test locally and create a new migration with the occ command that I send on previous comment. Only to test, don't put anything on new migration file. After, run occ migrations:migrate gestion, this will run the migration and put on table migration a new entry flagging that the new migration was executed. Now, to test, create one more migration and run one more time the migrate command and look one more time the content of table migrations.

👍

Talking about development scenario... Don't is possible do rollback, Nextcloud haven't support to rollback migrations. If you need do a rollback, delete the entry on table migrations of last executed migration and revert manually the change that this migration did.

👍 (I know that, unfortunately :))

For now, don't recreate the old migrations, maintain the actual migration and to new databases updates, create new migrations.

You only will need change old migrations files if the old file stay generating an error and can't run.

👍

--

For know the big problem in the migration is that impossible to change column name without :

• delete and add it, but that cause lost information
• ALTER TABLE in SQL, but not the same syntax in POSTGRE SQLITE AND MYSQL/MARIADB

I have read these files :

• ./3rdparty/doctrine/dbal/src/Schema/Column.php
• ./3rdparty/doctrine/dbal/src/Schema/Table.php

But no reference to a function for changing name of the column 😢

[vitormattos]

Suggestion:

• Do a SELECT to get all data of old column and store this
• Create the column with new name
• Delete the old column
• Iterate on stored data of old column and run UPDATE on all to move from memory to new column

Look this for example:

https://github.com/LibreSign/libresign/blob/main/lib/Migration/Version1000Date20210502200600.php

[baimard]

Nice solution I will try.

[baimard]

@vitormattos what do you think about that https://github.com/baimard/gestion/pull/131/files ?

[vitormattos]

I did a review

[baimard]

One more question in one review, all others changes accepted.