Tutorial for Updating Database Files

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 Database Revision Tracking
  • 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!!