Best practice to update the db schema

[xavier]

Hi,

on sepa, we'll need to alter one table to add a new column. Is this possible to use the update hook and what's the best practice we are only adding the column if it doesn't exist (ie. need to know from which version we update?

do you have an example of an extension doing it already?

[Erik Hommel]

I have done something like that in the upgrader Class of an extension?

[totten]

I can't name any examples -- generally, I'd tend to do a separate table with a 1-1 relationship rather than modify core schema. However, I agree with Erik that you should be able to do that with an upgrader class or hook_civicrm_upgrade. (Clarification: an upgrader class is civix's more convenient way to use hook_civicrm_upgrade.)

[Erik Hommel]

Agree with Tim that I also tend to create a new table rather than add fields to core schema. And I deal with that in the upgrader class. In my example I added some fields to an extension specific file (civicrm_contribution_recur_offline)

[xavier]

I wasn't clear, the initial question was about modifying the schema of one of the extra table created by the extension.

right now, we just have the schema now and the one we want for the next release, so the upgrade is hopefully going to be easy, but I was wondering how it would work later, when you have several versions with different modifications to apply and was wondering if there is something for the extensions like there is for the core and that we should use already.

(and Dave's best practice suggestion to add ON DUPLICATE KEY UPDATE...)

[totten]

Follow a pattern that's a bit like Drupal hook_update_N. Whenever you decide to (e.g.) add a new column, you need to make two changes:

 1. Update the main SQL used for new installations
 2. Add an upgrade function, e.g. CRM_Myextension_Upgrader::upgrade_1000() (Example: https://github.com/civicrm/civihr/blob/master/hrjob/CRM/HRJob/Upgrader.php#L155 )

When an admin downloads the latest version of myextension, he needs to run the DB upgrade (like in Drupal). There are two ways to do this:

 a. (v4.2+) In the web UI, the "Manage Extensions" screen will show an alert if there are pending DB upgrades. Click the link and let them run.
 b. (v4.5+) Using the API/CLI, run civicrm_api("Extension","upgrade",...) -- e.g. "drush cvapi Extension.upgrade"

When one goes through either of those, the internal machinations are a bit like this:

 - Core invokes hook_civicrm_upgrade
 - The hook is predefined by civix with myextension_civicrm_upgrade()
 - myextension_civicrm_upgrade() instantiates CRM_Myextension_Upgrader
 - CRM_Myextension_Upgrader scans itself for new functions that look like upgrade_1000() and enqueues them to run as part of the upgrade

(Note: Comments above assume the extension is based on civix. If it's not based on civix, then you would need to define hook_civicrm_upgrade and enqueue your own upgrade tasks.)