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 .
=== Introduction of the db_revs directory ===
With (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. 
== See also ==
* The section about "Database" in the Coding guidelines
Category:Development