From b9492060edbf98326df6ae594f1c8888f422f3e4 Mon Sep 17 00:00:00 2001 From: Paul POULAIN Date: Sat, 20 Oct 2007 18:36:18 +0200 Subject: [PATCH] merging some SQL commands to speed up updatedatabase Signed-off-by: Joshua Ferraro --- updater/updatedatabase | 174 +++++++++++++++++++++-------------------- 1 file changed, 91 insertions(+), 83 deletions(-) diff --git a/updater/updatedatabase b/updater/updatedatabase index 42baaa3e1f..5d635033f7 100755 --- a/updater/updatedatabase +++ b/updater/updatedatabase @@ -163,13 +163,13 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { FROM `biblioitems`"); # biblioitems changes - $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT"); - $dbh->do("ALTER TABLE `biblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`"); - $dbh->do("ALTER TABLE `biblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`"); - $dbh->do("ALTER TABLE `biblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`"); - $dbh->do("ALTER TABLE `biblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`"); - $dbh->do("ALTER TABLE `biblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`"); - $dbh->do("ALTER TABLE `biblioitems` ADD `totalissues` INT(10) AFTER `cn_sort`"); + $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, @@ -184,100 +184,108 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { # 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)"); - $dbh->do("UPDATE `biblioitems` SET cn_item = subclass"); - $dbh->do("UPDATE `biblioitems` SET `cn_sort` = `lcsort`"); + 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`"); - $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `dewey`"); - $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `subclass`"); - $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `lcsort`"); - $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `ccode`"); + $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 ''"); - $dbh->do("ALTER TABLE `deletedbiblio` DROP COLUMN `marc`"); - $dbh->do("ALTER TABLE `deletedbiblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp`"); + $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"); - $dbh->do("ALTER TABLE `deletedbiblioitems` CHANGE `volumeddesc` `volumedesc` TEXT"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `marc` BLOB"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `totalissues` INT(10) AFTER `cn_sort`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `isbn` (`isbn`)"); - $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `publishercode` (`publishercode`)"); + $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)"); - $dbh->do("UPDATE `deletedbiblioitems` SET cn_item = subclass"); - $dbh->do("UPDATE `deletedbiblioitems` SET `cn_sort` = `lcsort`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `classification`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `dewey`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `subclass`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `lcsort`"); - $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `ccode`"); + 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"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `price` DECIMAL(8,2) DEFAULT NULL"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL"); - $dbh->do("ALTER TABLE `deleteditems` DROP `bulk`"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL"); - $dbh->do("ALTER TABLE `deleteditems` DROP `interim`"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`"); - $dbh->do("ALTER TABLE `deleteditems` DROP `cutterextra`"); - $dbh->do("ALTER TABLE `deleteditems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`"); - $dbh->do("ALTER TABLE `deleteditems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source"); - $dbh->do("ALTER TABLE `deleteditems` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`"); - $dbh->do("ALTER TABLE `deleteditems` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`"); - $dbh->do("ALTER TABLE `deleteditems` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`"); - $dbh->do("ALTER TABLE `deleteditems` MODIFY `marc` LONGBLOB AFTER `uri`"); - $dbh->do("ALTER TABLE `deleteditems` DROP KEY `barcode`"); - $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembarcodeidx`"); - $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembinoidx`"); - $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembibnoidx`"); - $dbh->do("ALTER TABLE `deleteditems` ADD UNIQUE KEY `delitembarcodeidx` (`barcode`)"); - $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembinoidx` (`biblioitemnumber`)"); - $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembibnoidx` (`biblionumber`)"); - $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delhomebranch` (`homebranch`)"); - $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delholdingbranch` (`holdingbranch`)"); - $dbh->do("UPDATE `deleteditems` SET `ccode` = `itype`"); - $dbh->do("ALTER TABLE `deleteditems` DROP `itype`"); + $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`"); - $dbh->do("ALTER TABLE `items` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source"); - $dbh->do("ALTER TABLE `items` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`"); - $dbh->do("ALTER TABLE `items` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`"); - $dbh->do("ALTER TABLE `items` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`"); - $dbh->do("ALTER TABLE `items` DROP KEY `itembarcodeidx`"); - $dbh->do("ALTER TABLE `items` ADD UNIQUE KEY `itembarcodeidx` (`barcode`)"); - - # map items.itype to items.ccode - $dbh->do("UPDATE `items` SET `ccode` = `itype`"); - + $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`"); + $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`, + `ccode` = `itype`"); - $dbh->do("ALTER TABLE `items` DROP `cutterextra`"); - $dbh->do("ALTER TABLE `items` DROP `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); -- 2.39.5