PaulPoulain
commented
7 years ago The way we do it in Koha: The SQL table systempreferences contains many parameters and preferences (more than 500 today !!!!) One of them is "KohaVersion". This syspref contain the database number. The script updatedatabase check again the DB number, and applies new changes. Example here : http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/updatedatabase.pl;h=134280678e8b46db3cc5a2a8267f8dafb17728d9;hb=HEAD
Retrieve the cuttent version :
81 my $original_version = C4::Context->preference("Version");
Applies the changes for version 3.00.00.001 if needed
83 # Deal with virtualshelves
84 my $DBversion = "3.00.00.001";
85 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
86 # update virtualshelves table to
87 #
88 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
89 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
90 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
91 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
92 # drop all foreign keys : otherwise, we can't drop itemnumber field.
93 DropAllForeignKeys('virtualshelfcontents');
94 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
95 # create the new foreign keys (on biblionumber)
96 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
97 # re-create the foreign key on virtualshelf
98 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
99 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
Print on the console, reporting the update to the sysadmin
100 print "Upgrade to $DBversion done (virtualshelves)\n";Set the version. Thus, if there's a ctrl-c before the end, we've stored that we've already bumped to 3.00.00.001
101 SetVersion ($DBversion);
102 }Now apply 3.00.00.002
105 $DBversion = "3.00.00.002";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 $dbh->do("DROP TABLE sessions");
108 $dbh->do("CREATE TABLE `sessions` (
109 `id` varchar(32) NOT NULL,
110 `a_session` text NOT NULL,
111 UNIQUE KEY `id` (`id`)
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
113 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
114 SetVersion ($DBversion);
115 }116 We're using this for more than 10 years now.
Before version 3.0, we had another script that was checking each table/index/FK/ ... to see if it was here and/or has to be updated. It became a nightmare because checking everything is VERY hard when the software evolves a lot.
See http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mysql/update22to30.pl;h=f10ace95c5ab624adb9490304e7cb30a67434ee7;hb=HEAD if you're curious
t4k
jeffnm
jcuenod
fondrenlibrary
I was chatting to @jeffnm about the Installer modifier and I'm thinking about how to gather information about what's installed. One of the issues is that knowing what version we're sitting on for the DB (and specific tables within the DB) is pretty important. Apparently @t4k has been thinking about this as well - please weigh in if you have thoughts!
Right now one thing I'm thinking is to store details in the config file but I'm reluctant to do that because it's not "configuration." I don't know how rails does it but it's pretty awesome (their migrations thing). If anyone has ideas about versioning the db, that would be great. Also, I don't think I've completely problematised the issue developers face as sql files are built to provide for upgrades so please weigh in on that score as well.