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 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!!