Core:Database schema updates

From Habari Project

Jump to: navigation, search

This page describes how to upgrade the database schema that Habari uses.


Simple updates

For simple updates, such as adding a column to a table:

  • make the schema change in the schema file for each of the supported databases (/system/schema/{db}/schema.sql).
  • update the database version (DB_VERSION in /system/classes/version.php) to the version of your commit.

Pre- and post-upgrade SQL and PHP

You can specify both SQL and PHP that should run before and after an upgrade.

  • Pre-upgrade SQL files should be placed in /system/schema/{db}/upgrades/pre/{new_version}.sql.
  • Post-upgrade SQL files should be placed in /system/schema/{db}/upgrades/post/{new_version}.sql.
  • Pre-upgrade PHP should be placed in a function called /upgrade_db_pre_{new_version}() in /system/classes/installhandler.php.
  • Post-upgrade PHP should be placed in a function called /upgrade_db_post_{new_version}() in /system/classes/installhandler.php.

Non-destructive upgrade

The code that applies the differences between the old and new schemas does so in a way so as to minimize the risk of data loss. This means that, for example, it won't drop tables or delete columns. To perform actions that might lose data, you need to provide pre- or post-upgrade SQL.

An example of this is renaming a column, which would be done as follows:

  • Rename the column in the database schema files (/system/schema/{db}/schema.sql).
  • The upgrade will add the new column name, but will not drop the old one.
  • In a post-upgrade SQL file, copy the data from the old column to the new column, and drop the old column.

Upgrade version number

The requirement to name your upgrade files and functions after the revision you're committing, while recommended in most cases, is actually a small lie. As long as the new DB_VERSION is set to a higher number than the existing DB_VERSION on a given Habari installation, all upgrades between the existing version and the new version will run.

Upgrade details

// Gets the old version from Options, gets new database schema, saves the new version in Options
-InstallHandler->upgrade_db_pre($old_version) // Finds InstallHandler pre-upgrade methods with version > current version
--InstallHandler->upgrade_db_pre_{rev} // Runs revision-specific pre-upgrade code
--{db}Connection->upgrade_pre($old_version) // Works out where the pre-upgrade SQL files are
---DatabaseConnection::upgrade($old_version, $upgrade_path) // Executes the pre-upgrade SQL
-DB::dbdelta($queries) // Sends new schema to database-specific dbdelta
--{db}Connection->dbdelta($queries) // Compares new schema with old schema and applies changes. See #Non-destructive upgrade.
-InstallHandler->upgrade_db_post($old_version) // Finds InstallHandler post-upgrade methods with version > current version
--InstallHandler->upgrade_db_post_{rev} // Runs revision-specific post-upgrade code
Personal tools