#!/usr/bin/perl
-# $Id$
# Database Updater
# This script checks for required updates to the database.
# Koha modules
use C4::Context;
+use MARC::Record;
+use MARC::File::XML ( BinaryEncoding => 'utf8' );
+
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.
my $silent;
GetOptions(
- 's' =>\$silent
- );
+ 's' =>\$silent
+ );
my $dbh = C4::Context->dbh;
-print "connected to your DB. Checking & modifying it\n" unless $silent;
-
-#-------------------
-# Defines
-
-# Tables to add if they don't exist
-my %requiretables = (
- categorytable => "(categorycode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (categorycode)
- )",
- subcategorytable => "(subcategorycode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (subcategorycode)
- )",
- mediatypetable => "(mediatypecode char(5) NOT NULL default '',
- description text default '',
- itemtypecodes text default '',
- PRIMARY KEY (mediatypecode)
- )",
- action_logs => "(
- `timestamp` TIMESTAMP NOT NULL ,
- `user` INT( 11 ) NOT NULL ,
- `module` TEXT default '',
- `action` TEXT default '' ,
- `object` INT(11) default '' ,
- `info` TEXT default '' ,
- PRIMARY KEY ( `timestamp` , `user` )
- )",
-);
+$|=1; # flushes output
+
+=item
+ Deal with virtualshelves
+=cut
+
+my $DBversion = "3.00.00.001";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ # update virtualshelves table to
+ #
+ $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
+ $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
+ $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
+ # drop all foreign keys : otherwise, we can't drop itemnumber field.
+ DropAllForeignKeys('virtualshelfcontents');
+ # create the new foreign keys (on biblionumber)
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
+ # re-create the foreign key on virtualshelf
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
+ # now we can drop the itemnumber column
+ $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
+ print "Upgrade to $DBversion done (virtualshelves)\n";
+ SetVersion ($DBversion);
+}
-my %requirefields = (
-# tablename => { 'field' => 'fieldtype' },
-);
-my %dropable_table = (
-# tablename => 'tablename',
-);
+$DBversion = "3.00.00.002";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("DROP TABLE sessions");
+ $dbh->do("CREATE TABLE `sessions` (
+ `id` char(32) NOT NULL,
+ `a_session` text NOT NULL,
+ UNIQUE KEY `id` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
+ print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
+ SetVersion ($DBversion);
+}
-my %uselessfields = (
-# tablename => "field1,field2",
- );
-# the other hash contains other actions that can't be done elsewhere. they are done
-# either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
-
-# The tabledata hash contains data that should be in the tables.
-# The uniquefieldrequired hash entry is used to determine which (if any) fields
-# must not exist in the table for this row to be inserted. If the
-# uniquefieldrequired entry is already in the table, the existing data is not
-# modified, unless the forceupdate hash entry is also set. Fields in the
-# anonymous "forceupdate" hash will be forced to be updated to the default
-# values given in the %tabledata hash.
-
-my %tabledata = (
-# tablename => [
-# { uniquefielrequired => 'fieldname', # the primary key in the table
-# fieldname => fieldvalue,
-# fieldname2 => fieldvalue2,
-# },
-# ],
- systempreferences => [
- {
- uniquefieldrequired => 'variable',
- variable => 'Activate_Log',
- value => 'On',
- forceupdate => { 'explanation' => 1,
- 'type' => 1},
- explanation => 'Turn Log Actions on DB On an Off',
- type => 'YesNo',
- },
- ],
-);
+$DBversion = "3.00.00.003";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ if (C4::Context->preference("opaclanguage") eq "fr") {
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
+ } else {
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
+ }
+ print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
+ SetVersion ($DBversion);
+}
-my %fielddefinitions = (
-# fieldname => [
-# { field => 'fieldname',
-# type => 'fieldtype',
-# null => '',
-# key => '',
-# default => ''
-# },
-# ],
-);
-#-------------------
-# Initialize
+$DBversion = "3.00.00.004";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");
+ print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
+ SetVersion ($DBversion);
+}
-# Start checking
+$DBversion = "3.00.00.005";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE `tags` (
+ `entry` varchar(255) NOT NULL default '',
+ `weight` bigint(20) NOT NULL default 0,
+ PRIMARY KEY (`entry`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ ");
+ $dbh->do("CREATE TABLE `nozebra` (
+ `server` varchar(20) NOT NULL,
+ `indexname` varchar(40) NOT NULL,
+ `value` varchar(250) NOT NULL,
+ `biblionumbers` longtext NOT NULL,
+ KEY `indexname` (`server`,`indexname`),
+ KEY `value` (`server`,`value`))
+ ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ ");
+ print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
+ SetVersion ($DBversion);
+}
-# Get version of MySQL database engine.
-my $mysqlversion = `mysqld --version`;
-$mysqlversion =~ /Ver (\S*) /;
-$mysqlversion = $1;
-if ( $mysqlversion ge '3.23' ) {
- print "Could convert to MyISAM database tables...\n" unless $silent;
+$DBversion = "3.00.00.006";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
+ print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
+ SetVersion ($DBversion);
}
-#---------------------------------
-# Tables
+$DBversion = "3.00.00.007";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
+ print "Upgrade to $DBversion done (set SessionStorage variable)\n";
+ SetVersion ($DBversion);
+}
-# Collect all tables into a list
-$sth = $dbh->prepare("show tables");
-$sth->execute;
-while ( my ($table) = $sth->fetchrow ) {
- $existingtables{$table} = 1;
+$DBversion = "3.00.00.008";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
+ $dbh->do("UPDATE biblio SET datecreated=timestamp");
+ print "Upgrade to $DBversion done (biblio creation date)\n";
+ SetVersion ($DBversion);
}
+$DBversion = "3.00.00.009";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+
+ # Create backups of call number columns
+ # in case default migration needs to be customized
+ #
+ # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
+ # after call numbers have been transformed to the new structure
+ #
+ # Not bothering to do the same with deletedbiblioitems -- assume
+ # default is good enough.
+ $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
+ SELECT `biblioitemnumber`, `biblionumber`,
+ `classification`, `dewey`, `subclass`,
+ `lcsort`, `ccode`
+ FROM `biblioitems`");
+
+ # biblioitems changes
+ $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
+ ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
+ ADD `totalissues` INT(10) AFTER `cn_sort`");
+
+ # default mapping of call number columns:
+ # cn_class = concatentation of classification + dewey,
+ # trimmed to fit -- assumes that most users do not
+ # populate both classification and dewey in a single record
+ # cn_item = subclass
+ # cn_source = left null
+ # cn_sort = lcsort
+ #
+ # After upgrade, cn_sort will have to be set based on whatever
+ # default call number scheme user sets as a preference. Misc
+ # script will be added at some point to do that.
+ #
+ $dbh->do("UPDATE `biblioitems`
+ SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
+ cn_item = subclass,
+ `cn_sort` = `lcsort`
+ ");
+
+ # Now drop the old call number columns
+ $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
+ DROP COLUMN `dewey`,
+ DROP COLUMN `subclass`,
+ DROP COLUMN `lcsort`,
+ DROP COLUMN `ccode`");
+
+ # deletedbiblio changes
+ $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
+ DROP COLUMN `marc`,
+ ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
+ $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
+
+ # deletedbiblioitems changes
+ $dbh->do("ALTER TABLE `deletedbiblioitems`
+ MODIFY `publicationyear` TEXT,
+ CHANGE `volumeddesc` `volumedesc` TEXT,
+ MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
+ MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
+ MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
+ MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
+ MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
+ MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
+ MODIFY `marc` BLOB,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
+ ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
+ ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
+ ADD `totalissues` INT(10) AFTER `cn_sort`,
+ ADD KEY `isbn` (`isbn`),
+ ADD KEY `publishercode` (`publishercode`)
+ ");
+
+ $dbh->do("UPDATE `deletedbiblioitems`
+ SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
+ `cn_item` = `subclass`,
+ `cn_sort` = `lcsort`
+ ");
+ $dbh->do("ALTER TABLE `deletedbiblioitems`
+ DROP COLUMN `classification`,
+ DROP COLUMN `dewey`,
+ DROP COLUMN `subclass`,
+ DROP COLUMN `lcsort`,
+ DROP COLUMN `ccode`
+ ");
+
+ # deleteditems changes
+ $dbh->do("ALTER TABLE `deleteditems`
+ MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
+ MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
+ MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
+ DROP `bulk`,
+ MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
+ MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
+ DROP `interim`,
+ MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
+ DROP `cutterextra`,
+ ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
+ ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
+ MODIFY `marc` LONGBLOB AFTER `uri`,
+ DROP KEY `barcode`,
+ DROP KEY `itembarcodeidx`,
+ DROP KEY `itembinoidx`,
+ DROP KEY `itembibnoidx`,
+ ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
+ ADD KEY `delitembinoidx` (`biblioitemnumber`),
+ ADD KEY `delitembibnoidx` (`biblionumber`),
+ ADD KEY `delhomebranch` (`homebranch`),
+ ADD KEY `delholdingbranch` (`holdingbranch`)");
+ $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
+ $dbh->do("ALTER TABLE deleteditems DROP `itype`");
+ $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
+
+ # items changes
+ $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
+ ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
+ ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
+ ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
+ ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
+ ");
+ $dbh->do("ALTER TABLE `items`
+ DROP KEY `itembarcodeidx`,
+ ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
+
+ # map items.itype to items.ccode and
+ # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
+ # will have to be subsequently updated per user's default
+ # classification scheme
+ $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
+ `ccode` = `itype`");
+
+ $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
+ DROP `itype`");
+
+ print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
+ SetVersion ($DBversion);
+}
-# Now add any missing tables
-foreach $table ( keys %requiretables ) {
- unless ( $existingtables{$table} ) {
- print "Adding $table table...\n" unless $silent;
- my $sth = $dbh->prepare("create table $table $requiretables{$table}");
- $sth->execute;
- if ( $sth->err ) {
- print "Error : $sth->errstr \n";
- $sth->finish;
- } # if error
- } # unless exists
-} # foreach
-
-# now drop useless tables
-foreach $table ( keys %dropable_table ) {
- if ( $existingtables{$table} ) {
- print "Dropping unused table $table\n" if $debug and not $silent;
- $dbh->do("drop table $table");
- if ( $dbh->err ) {
- print "Error : $dbh->errstr \n";
- }
- }
+$DBversion = "3.00.00.010";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
+ print "Upgrade to $DBversion done (userid index added)\n";
+ SetVersion ($DBversion);
}
-#---------------------------------
-# Columns
-
-foreach $table ( keys %requirefields ) {
- print "Check table $table\n" if $debug and not $silent;
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute();
- undef %types;
- while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
- {
- $types{$column} = $type;
- } # while
- foreach $column ( keys %{ $requirefields{$table} } ) {
- print " Check column $column [$types{$column}]\n" if $debug and not $silent;
- if ( !$types{$column} ) {
-
- # column doesn't exist
- print "Adding $column field to $table table...\n" unless $silent;
- $query = "alter table $table
- add column $column " . $requirefields{$table}->{$column};
- print "Execute: $query\n" if $debug;
- my $sti = $dbh->prepare($query);
- $sti->execute;
- if ( $sti->err ) {
- print "**Error : $sti->errstr \n";
- $sti->finish;
- } # if error
- } # if column
- } # foreach column
-} # foreach table
-
-foreach $table ( keys %fielddefinitions ) {
- print "Check table $table\n" if $debug;
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute();
- my $definitions;
- while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
- {
- $definitions->{$column}->{type} = $type;
- $definitions->{$column}->{null} = $null;
- $definitions->{$column}->{key} = $key;
- $definitions->{$column}->{default} = $default;
- $definitions->{$column}->{extra} = $extra;
- } # while
- my $fieldrow = $fielddefinitions{$table};
- foreach my $row (@$fieldrow) {
- my $field = $row->{field};
- my $type = $row->{type};
- my $null = $row->{null};
- my $key = $row->{key};
- my $default = $row->{default};
- $default="''" unless $default;
- my $extra = $row->{extra};
- my $def = $definitions->{$field};
- unless ( $type eq $def->{type}
- && $null eq $def->{null}
- && $key eq $def->{key}
- && $default eq $def->{default}
- && $extra eq $def->{extra} )
- {
-
- if ( $null eq '' ) {
- $null = 'NOT NULL';
- }
- if ( $key eq 'PRI' ) {
- $key = 'PRIMARY KEY';
- }
- unless ( $extra eq 'auto_increment' ) {
- $extra = '';
- }
- # if it's a new column use "add", if it's an old one, use "change".
- my $action;
- if ($definitions->{$field}->{type}) {
- $action="change $field"
- } else {
- $action="add";
- }
-# if it's a primary key, drop the previous pk, before altering the table
- my $sth;
- if ($key ne 'PRIMARY KEY') {
- $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
- } else {
- $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
- }
- $sth->execute($default);
- print " Alter $field in $table\n" unless $silent;
- }
- }
+$DBversion = "3.00.00.011";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
+ $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
+ $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
+ $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
+ $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
+ print "Upgrade to $DBversion done (added branchcategory type)\n";
+ SetVersion ($DBversion);
}
+$DBversion = "3.00.00.012";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE `class_sort_rules` (
+ `class_sort_rule` varchar(10) NOT NULL default '',
+ `description` mediumtext,
+ `sort_routine` varchar(30) NOT NULL default '',
+ PRIMARY KEY (`class_sort_rule`),
+ UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `class_sources` (
+ `cn_source` varchar(10) NOT NULL default '',
+ `description` mediumtext,
+ `used` tinyint(4) NOT NULL default 0,
+ `class_sort_rule` varchar(10) NOT NULL default '',
+ PRIMARY KEY (`cn_source`),
+ UNIQUE KEY `cn_source_idx` (`cn_source`),
+ KEY `used_idx` (`used`),
+ CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
+ REFERENCES `class_sort_rules` (`class_sort_rule`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
+ VALUES('DefaultClassificationSource','ddc',
+ 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
+ $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
+ ('dewey', 'Default filing rules for DDC', 'Dewey'),
+ ('lcc', 'Default filing rules for LCC', 'LCC'),
+ ('generic', 'Generic call number filing rules', 'Generic')");
+ $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
+ ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
+ ('lcc', 'Library of Congress Classification', 1, 'lcc'),
+ ('udc', 'Universal Decimal Classification', 0, 'generic'),
+ ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
+ ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
+ print "Upgrade to $DBversion done (classification sources added)\n";
+ SetVersion ($DBversion);
+}
-# Populate tables with required data
-foreach my $table ( keys %tabledata ) {
- print "Checking for data required in table $table...\n" unless $silent;
- my $tablerows = $tabledata{$table};
- foreach my $row (@$tablerows) {
- my $uniquefieldrequired = $row->{uniquefieldrequired};
- my $uniquevalue = $row->{$uniquefieldrequired};
- my $forceupdate = $row->{forceupdate};
- my $sth =
- $dbh->prepare(
-"select $uniquefieldrequired from $table where $uniquefieldrequired=?"
- );
- $sth->execute($uniquevalue);
- if ($sth->rows) {
- foreach my $field (keys %$forceupdate) {
- if ($forceupdate->{$field}) {
- my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
- $sth->execute($row->{$field}, $uniquevalue);
- }
- }
- } else {
- print "Adding row to $table: " unless $silent;
- my @values;
- my $fieldlist;
- my $placeholders;
- foreach my $field ( keys %$row ) {
- next if $field eq 'uniquefieldrequired';
- next if $field eq 'forceupdate';
- my $value = $row->{$field};
- push @values, $value;
- print " $field => $value" unless $silent;
- $fieldlist .= "$field,";
- $placeholders .= "?,";
- }
- print "\n" unless $silent;
- $fieldlist =~ s/,$//;
- $placeholders =~ s/,$//;
- my $sth =
- $dbh->prepare(
- "insert into $table ($fieldlist) values ($placeholders)");
- $sth->execute(@values);
+$DBversion = "3.00.00.013";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("CREATE TABLE `import_batches` (
+ `import_batch_id` int(11) NOT NULL auto_increment,
+ `template_id` int(11) default NULL,
+ `branchcode` varchar(10) default NULL,
+ `num_biblios` int(11) NOT NULL default 0,
+ `num_items` int(11) NOT NULL default 0,
+ `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
+ `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
+ `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
+ `file_name` varchar(100),
+ `comments` mediumtext,
+ PRIMARY KEY (`import_batch_id`),
+ KEY `branchcode` (`branchcode`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_records` (
+ `import_record_id` int(11) NOT NULL auto_increment,
+ `import_batch_id` int(11) NOT NULL,
+ `branchcode` varchar(10) default NULL,
+ `record_sequence` int(11) NOT NULL default 0,
+ `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `import_date` DATE default NULL,
+ `marc` longblob NOT NULL,
+ `marcxml` longtext NOT NULL,
+ `marcxml_old` longtext NOT NULL,
+ `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
+ `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
+ `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
+ `import_error` mediumtext,
+ `encoding` varchar(40) NOT NULL default '',
+ `z3950random` varchar(40) default NULL,
+ PRIMARY KEY (`import_record_id`),
+ CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
+ REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `branchcode` (`branchcode`),
+ KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_record_matches` (
+ `import_record_id` int(11) NOT NULL,
+ `candidate_match_id` int(11) NOT NULL,
+ `score` int(11) NOT NULL default 0,
+ CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `record_score` (`import_record_id`, `score`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_biblios` (
+ `import_record_id` int(11) NOT NULL,
+ `matched_biblionumber` int(11) default NULL,
+ `control_number` varchar(25) default NULL,
+ `original_source` varchar(25) default NULL,
+ `title` varchar(128) default NULL,
+ `author` varchar(80) default NULL,
+ `isbn` varchar(14) default NULL,
+ `issn` varchar(9) default NULL,
+ `has_items` tinyint(1) NOT NULL default 0,
+ CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `matched_biblionumber` (`matched_biblionumber`),
+ KEY `title` (`title`),
+ KEY `isbn` (`isbn`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("CREATE TABLE `import_items` (
+ `import_items_id` int(11) NOT NULL auto_increment,
+ `import_record_id` int(11) NOT NULL,
+ `itemnumber` int(11) default NULL,
+ `branchcode` varchar(10) default NULL,
+ `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
+ `marcxml` longtext NOT NULL,
+ `import_error` mediumtext,
+ PRIMARY KEY (`import_items_id`),
+ CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `itemnumber` (`itemnumber`),
+ KEY `branchcode` (`branchcode`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ SetVersion ($DBversion);
+}
+
+$DBversion = "3.00.00.014";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
+ print "Upgrade to $DBversion done (userid index added)\n";
+ SetVersion ($DBversion);
+}
+
+=item DropAllForeignKeys($table)
+
+ Drop all foreign keys of the table $table
+
+=cut
+
+sub DropAllForeignKeys {
+ my ($table) = @_;
+ # get the table description
+ my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
+ $sth->execute;
+ my $vsc_structure = $sth->fetchrow;
+ # split on CONSTRAINT keyword
+ my @fks = split /CONSTRAINT /,$vsc_structure;
+ # parse each entry
+ foreach (@fks) {
+ # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
+ $_ = /(.*) FOREIGN KEY.*/;
+ my $id = $1;
+ if ($id) {
+ # we have found 1 foreign, drop it
+ $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
+ $id="";
}
}
}
-# at last, remove useless fields
-foreach $table ( keys %uselessfields ) {
- my @fields = split /,/,$uselessfields{$table};
- my $fields;
- my $exists;
- foreach my $fieldtodrop (@fields) {
- $fieldtodrop =~ s/\t//g;
- $fieldtodrop =~ s/\n//g;
- $exists =0;
- $sth = $dbh->prepare("show columns from $table");
- $sth->execute;
- while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
- {
- $exists =1 if ($column eq $fieldtodrop);
- }
- if ($exists) {
- print "deleting $fieldtodrop field in $table...\n" unless $silent;
- my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
- $sth->execute;
- }
- }
-} # foreach
-
-
-$sth->finish;
+
+
+
+
+
+=item TransformToNum
+
+ Transform the Koha version from a 4 parts string
+ to a number, with just 1 .
+
+=cut
+
+sub TransformToNum {
+ my $version = shift;
+ # remove the 3 last . to have a Perl number
+ $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
+ return $version;
+}
+
+=item SetVersion
+ set the DBversion in the systempreferences
+=cut
+
+sub SetVersion {
+ my $kohaversion = TransformToNum(shift);
+ if (C4::Context->preference('Version')) {
+ my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
+ $finish->execute($kohaversion);
+ } else {
+ my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. Don t change this value manually, it s holded by the webinstaller')");
+ $finish->execute($kohaversion);
+ }
+}
exit;
-# $Log$
-# Revision 1.111 2005/07/25 15:35:38 tipaul
-# we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
-# So, the updatedatabase script can highly be cleaned (90% removed).
-# Let's play with the new Koha DB structure now ;-)
-#
+# Revision 1.172 2007/07/19 10:21:22 hdl
projects / koha_fer / blobdiff