Database updates

From Koha Wiki

Jump to: navigation, search
Koha > Technical > Development


How to write a database update

Files to change

  • Whenever you change the database structure, you must edit installer/data/mysql/kohastructure.sql. This will be used to create the database schema for a new installation.
  • Create a new atomicupdate file with your change that matches the result of the change to kohastructure.sql. This will be used to update the database for existing installations. Please use AFTER/BEFORE column in your atomicupdate to keep the order of columns identical.
  • Updating the Schema can be done by running misc/devel/ or dbic on KohaDevBox. Currently the RM will run the script. If you want to include those changes with your patches, do so in a separate patch. This will allow the changes to be redone easily if necessary.

Additional hints

  • Sysprefs: 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.
  • Installer: 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.
  • Permissions: userflags.sql, permissions.sql and don't forget koha-tmpl/intranet-tmpl/prog/en/includes/
  • If you change borrowers, make sure you also change deletedborrowers, borrower_modifications, same goes for issues and old_issues and other similar tables.
  • Don't make changes to, the RM will increment the version there, when assigning a version number to your patch.
  • Don't make changes to
  • Don't manually change files under Koha/Schema/Result. If you do make a change, it should just be a 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)

How to write an atomicupdate file

To modify the data or the database structure of an existing installation, an atomicupdate file with a .sql or .perl file extension has to be added to the installer/data/mysql/atomicupdate directory.

A .sql will contain SQL queries only, and a .perl will be a perl script (take a look at installer/data/mysql/atomicupdate/skeleton.perl for an example). Providing a .pl file will make it a bit easier on the RM to move it to and is the preferred way.

By convention, it should be named something like bug_14242-add_mynewpref_syspref.sql or bug_14242-add_mynewpref_syspref.perl.

Example 1: Adding a new system preference (.perl)

Important File ending has to be .perl not .pl for it to work!

$DBversion = 'XXX';  # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    # you can use $dbh here like:
    $dbh->do(q{INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('MyNewPref', 'value', 'explanation', NULL, 'Free') });
    # or perform some test and warn
    # if( !column_exists( 'biblio', 'biblionumber' ) ) {
    #    warn "There is something wrong";
    # }
    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug XXXXX - description)\n";

Example 2: Adding a new system preference (.sql)

% $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



You must keep in mind that a new update version must be idempotent (the statements can be run multiple times without causing harm or errors/warnings)

This can be achieved by INSERT IGNORE and use of the column_exists, foreign_key_exists or index_exists subroutines.


When you write such update database entry, you must not use the Koha API or DBIx::Class schema.

So do not call Koha:: or C4:: methods/subroutines or use of Koha::Database->schema.

If you do so it may break in future versions if the schema does no longer match the table structure (see bug 19789).

This only applies to the 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.


Before the introduction of atomicupdate files the changes to were done by the developers. This caused the a lot of conflicts, required a lot of rebases and made testing on sandboxes nearly impossible. The workflow was changed with the push of Bug 13068.

See also

Developer handbook

Personal tools