How to modify database when my module is updated?

Developing for Dolibarr
1

After more than one hour checking official wiki & forum i’m not able to find where is explained to developers of modules where to put the actions to be executed by Dolibarr when you need that the user update the module.

Please can someone tell me if is there any published guide. The official wiki doesn’t tell nothing about “how to update your module”.

For example, in my case i need now to add 2 new fields to one table at database. So, how get it done? I would suppose that i can include in my new module version a script to be called when the module is “enabled” from Settings panel by the admin user. But i unknow how to do it.

I took a look on hooks “tiny” doc, but it doesn’t tell nothing about this.

Please, any help will be very appreciated. Thanks in advance!

2

Hi caos30,

As a general rule, modules should be disabled and enabled again following update, meaning the init function of your module descriptor should be run. This loads all sql files present in the sql directory, so something like an ‘upgrade.sql’ file should make it. If you need deeper version management, you can handle that in the init function by using test statements.

Regards,
Marc

1 Like
3

Thanks a lot! I get it work. I would like to share now some other details i’ve discovered today and i hope will be useful for other developers:

  • The recommendation for the name of those “update” SQL files is that they follow this format: update_XXXX.sql (i decided to use update_01.14.sql).
  • Each time that the admin user enable the module on Setup>Modules Dolibarr will load and try to execute all the SQL files contained on the /sql directory contained in your module path, taking in account that it will run first the llx_XXX.sql after that the XXXX.key.sql, after that the data_XXXX.sql and finally the update_XXX.sql.
  • And always will sort them by their name. So update_01.14.sql will be executed before update_02.3.sql, so take this in account when naming these SQL files.
  • When executing those SQL files, if the Mysql/MariaDB returns an error, Dolibarr continue with the next SQL on that file or with the next SQL file. So, for example, if you have an SQL file of a previous update that try to add a new column (field) on an existing table, when the SQL try to be executed more than one time on your database, mysql will return an error related to “an already existing column”. But don’t worry, Dolibarr will continue with the other SQL files/commands.
  • So, in other words: ALL YOUR SQL COMMANDS WILL BE EXECUTED EACH TIME the admin user enable your module on Dolibarr Setup section! If you ask me it’s a bit strange. But it seems that run “fine” until now.

Finally, i would like to share 3 useful Mysql commands for modify tables:

ALTER TABLE `llx_stocktransfers_transfers` ADD `private_note` text COLLATE utf8_unicode_ci NULL;
ALTER TABLE `llx_stocktransfers_transfers` CHANGE `private_note` `note` text COLLATE utf8_unicode_ci NULL;
ALTER TABLE `llx_stocktransfers_transfers` DROP `note`;
1 Like