Database updates

From Koha Wiki

Jump to: navigation, search
Koha > Technical > Development


How to write a database update

Pre DBIx::Class and for a transition period

  • Whenever you change the database structure, you must edit installer/data/mysql/kohastructure.sql.
  • Any change to the database structure must match the update done in
  • Any added sysprefs must be added to installer/data/mysql/sysprefs.sql. Please note that in this file the sysprefs are listed in alphabetic order.
  • If your patch adds new sample data to the installer, make sure you also update the translated installer files. It's ok to just copy your English sample data, you don't need to translate into each language.
  • Don't make changes to, the RM will increment the version there, when assigning a version number to your patch.

Changes with DBIx::Class

  • Updating the Schema can be done by running misc/devel/
  • Currently the RM will run the script. If you want to include those changes with your patches, do it in a separate patch. This will allow the changes to be redone easily by the RM if necessary.
  • Don't manually change files under Koha/Schema/Result. If you do make a change, it should just be for supplement to the code generated by DBIx::Class::Schema::Loader, and should be placed after the following lines in a schema class file:
# Created by DBIx::Class::Schema::Loader v0.07025 @ 2013-10-14 20:56:21

See: Email to Koha-Devel: QA: Patches with database changes (DBix::Class)

New workflow since bug 13068 has been pushed

The new workflow described below will get rid of the repeated conflicts on the file. On top of that sandboxes won't longer complain on this conflict and patches will be testable instead of fail on applying the patch set.

Note that this will add few more work for the RM.

If you are a developer

If you need to add changes to the database (update the DB structure or add/delete/update data), you need to create a new file in the installer/data/mysql/atomicupdate/ directory.

Whatever its name it will be took into account when the script will be executed.

By convention, it would be something like bug_14242-add_mynewpref_syspref.sql or bug_14242-add_new_db_field_table.field.sql.

Example: Adding a new system preference:

% $EDITOR installer/data/mysql/atomicupdate/bug_14242-add_mynewpref_syspref.sql
INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('MyNewPref', 'value', 'explanation', NULL, 'Free');
In order to install updates, run
% perl installer/data/mysql/
It will produce the following output:
% perl installer/data/mysql/
DEV atomic update : bug_14242-add_mynewpref_syspref.sql
If an error occurs (for instance if you have a syntax error), you will get:
DEV atomic update : bug_14242-add_mynewpref_syspref.sql 
C4::Installer::load_sql returned the following errors while attempting to load /home/koha/src/installer/data/mysql/atomicupdate/bug_14242-add_mynewpref_syspref.sql:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''my_pref','value', 'explanation'' at line 1

If you are Release Manager

The installer/data/mysql/atomicupdate directory has to be cleared and its content moved to the file. The entries will have the same syntax as before:
$DBversion = "3.XX.00.YYY"; # XX and YYY must be replaced
if ( CheckVersion($DBversion) ) {
        # This is the contain of the atomic update file
        # Be care, the do method accepts only 1 query
        # If more than one is executed, create as many statements as needed
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('MyNewPref', 'value', 'explanation', NULL, 'Free');
    print "Upgrade to $DBversion done (Bug 14242: Add MyNewPref system preference\n";
    SetVersion ($DBversion);

To update the DBIx schema files, run this from the root of your kohaclone:

misc/devel/ --db_name=koha_instance --db_user=koha_instance --db_passwd=thepassword

Then commit the result. You are done!

See also

Developer handbook

Personal tools