Tutorial for Updating Database Files

This article is obsolete

WARNING! This page is obsolete. This page is no longer correct and exists for historical reasons only.

The following are instructions from Chris Cormack to Nicole Engard for fixing Bug #2761 which required database editing:

• When making db changes you need to do a few things
  • First is make the change locally and check it works
  • Log into MySQL
    • On Debian: mysql -uUser -pPass
  • Connect to your DB
    • On Debian: use DBNAME;
  • Write the SQL to edit the table in question
    • For BUG #2761: ALTER TABLE items MODIFY itemcallnumber varchar(255);
  • Make sure that your edit doesn't break anything and does what you expected
  • Next step is to edit the appropriate SQL files
    • In the case of this patch, installer/data/mysql/kohastructure.sql
      • On Debian: vim installer/data/mysql/kohastructure.sql
    • Other SQL files can be found in installer/data/mysql/
  • Find the line where the field you changed is
    • For BUG #2761: type /itemcallnumber
  • Make the same change here that you made earlier
    • For BUG #2761: change the varchar(30) to varchar(255)
  • Save your changes
  • Next step is to edit installer/data/mysql/updatedatabase.pl and add a new database version
    • On Debian: vim installer/data/mysql/updatedatabase.pl
  • Come down in the file to just before sub DropAllForeignKeys
    • On Debian: /sub DropAllForeignKeys
  • Scroll up until you see $DBversion= 'some number'; followed by an IF block
  • Copy the following lines (remember the version number will be different in your file)

      $DBversion = '3.01.00.041';
      if (C4::Context->preference("Version") < TransformToNum($DBversion)) {

• • Paste them below the IF block
  • Change the version to the next one on the DB Revisions Table
    • For BUG #2761: '3.01.00.042'
  • Next enter your SQL into the $dbh->do statement below the 2 lines you copied

      $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");

• • Next enter your update message and close the IF block.

      SetVersion ($DBversion);
      print " Upgrade to $DBversion done (change max length of itemcallnumber to 255 from 30.)\n";
      }

• • In the end you should have a block that looks like this to define your new db version:

      $DBversion = '3.01.00.041';
      if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
      $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
      SetVersion ($DBversion);
      print " Upgrade to $DBversion done (change max length of itemcallnumber to 255 from 30.)\n";
      }

• • Save your changes

It is best to leave this final step to the Release Manager, but it is included here for education purposes:

• • Lastly you need to edit kohaversion.pl in the base directory of your git checkout
    • On Debian: vim kohaversion.pl
  • Update the version here to match the one you entered in updatedatabase.pl
  • Save your changes

And that is how we make a patch that changes the database!!