Database updates

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/update_dbix_class_files.pl or dbic on koha-testing-docker (ktd). Currently the RM will run the script. If you want to include those changes with your patches, do so in a separate patch, marked as [DO NOT PUSH]. This will allow the changes to be redone easily if necessary.

Additional hints

System preferences: Any new system preferences must be added to both installer/data/mysql/mandatory/sysprefs.sql and C4/UsageStats.pm. Please note that the system preferences are listed in alphabetic order. Please don't add to UsageStats.pm if the system preference can contain sensitive data.
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/permissions.inc
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 Koha.pm, the RM will increment the version there, when assigning a version number to your patch.
Don't add files to installer/data/mysql/dbrevs, the RM will take care of moving your database update there later.
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
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ZEh31EKBmURMKxDxI+H3EA

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, add an atomicupdate file with a '.pl' file extension and execute file permission to the installer/data/mysql/atomicupdate directory.

It will be a perl script - take a look at installer/data/mysql/atomicupdate/skeleton.pl for an example.

By convention, it should be named something like bug_14242-mynewpref_syspref.pl.

Example 1: Adding a new system preference

use Modern::Perl;
use Koha::Installer::Output qw(say_warning say_success say_info);

return {
    bug_number  => "<Bug number>",
    description => "<Bug description>",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        $dbh->do(q{INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('MyNewPref', 'value', NULL, 'explanation', 'Free')});

        say_success( $out, "Added new system preference 'MyNewPref'" );
    },
};

To install updates, run

% perl installer/data/mysql/updatedatabase.pl

or if using KTD:

updatedatabase

It will produce the following output:

% perl installer/data/mysql/updatedatabase.pl
DEV atomic update /kohadevbox/koha/installer/data/mysql/atomicupdate/bug_xxxxx-add_MyNewPref_syspref.pl  [08:30:36]: Bug BUG_NUMBER - Add new system preference MyNewPref

If an error occurs (for example, if you have a syntax error), you will get:

DEV atomic update /kohadevbox/koha/installer/data/mysql/atomicupdate/bug_xxxxx-add_MyNewPref_syspref.pl  [08:31:42]: Bug BUG_NUMBER - Add new system preference MyNewPref
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: 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 ''MyNewPref', 'value', NULL, 'explanation', 'Free')' at line 1 [for Statement "INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES 'MyNewPref', 'value', NULL, 'explanation', 'Free') "] at /kohadevbox/koha/C4/Installer.pm line 737

Example 2: Adding a new column

use Modern::Perl;
use Koha::Installer::Output qw(say_warning say_success say_info);

return {
    bug_number  => "BUG_NUMBER",
    description => "Enable users to ...",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        if ( !column_exists( 'table_name', 'column_name' ) ) {

            $dbh->do(q{
                ALTER TABLE table_name
                ADD COLUMN `column_name` varchar(100) DEFAULT NULL AFTER `other_column_name`
            });

            say_success( $out, "Added column 'table_name.column_name'" );

        } else {
            say_info( $out, "Column 'table_name.column_name' already exists!" );
        }
    },
};

Printing additional text

If there is more to tell than the description, use $out :

say_success( $out, "Update is going well so far" );

# tables
say_success( $out, "Added new table 'XXX'" );
say $out "Added column 'XXX.YYY'" );

# system preferences
say_success( $out, "Added new system preference 'XXX'" );
say_success( $out, "Updated system preference 'XXX'" );
say_success( $out, "Removed system preference 'XXX'" );

# permissions
say_success( $out, "Added new permission 'XXX'");
say_info( $out, "Added permission 'XXX' to $count users with 'XXX' permissions" );

# letters
say_success( $out, "Added new letter 'XXX' (TRANSPORT)" );

# HTML custmizations
say_success( $out, "Added 'XXX' HTML customization );

Requirements

Idempotent

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 primary_key_exists, unique_key_exists, foreign_key_exists, index_exists, column_exists or TableExists subroutines.

DBIx::Class

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).

Pushing to a branch

This only applies to the Release manager or Release maintainers of Koha >= 21.05:

The installer/data/mysql/atomicupdate directory has to be cleared and its content moved to the installer/data/mysql/db_revs directory.

The content of the file will be the same, but the filename will be replaced with the version number.

git mv installer/data/mysql/atomicupdate/bug_xxxxx-add_MyNewPref_syspref.pl installer/data/mysql/db_revs/210600017.pl

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

misc/devel/update_dbix_class_files.pl --db_name=koha_instance --db_user=koha_instance --db_passwd=thepassword

Increase the $VERSION in Koha.pm, then commit the result.

History

Introduction of the atomicupdate directory

Before the introduction of atomicupdate files the changes to updatedatabase.pl 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.

Introduction of the db_revs directory

With Bug 25078 (Starting from 21.06), the atomicupdate files are no longer appended to the monolithic updatedatabase.pl file but split in individual files in installer/data/mysql/db_revs.

Each entry is now executed in a transaction, if an error occurred it's rollbacked and the upgrade process is stopped.

It brings us better error handling and make the upgrade process easier.