installer/data/mysql/labels_upgrade.pl

   1 #!/usr/bin/perl
   2 #
   3 # Copyright 2009 Foundations Bible College.
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 #use strict;
  21 #use warnings; FIXME - Bug 2505
  22
  23 use C4::Context;
  24
  25 my $sth = C4::Context->dbh;
  26
  27 # NOTE: As long as we die on error *before* the DROP TABLE instructions are executed, the script may simply be rerun after addressing whatever errors occur; If we get past the data conversion without error, the DROPs and ALTERs could be executed manually if need be.
  28
  29 # Turn off key checks for duration of script...
  30 $sth->do("
  31     SET UNIQUE_CHECKS = 0;
  32 ") or die "DB ERROR: " . $sth->errstr . "\n";
  33
  34 $sth->do("
  35     SET FOREIGN_KEY_CHECKS = 0;
  36 ") or die "DB ERROR: " . $sth->errstr . "\n";
  37
  38 # Create new tables with temporary names...
  39 $sth->do("
  40     DROP TABLE IF EXISTS labels_batches_tmp;");
  41 $sth->do("
  42     CREATE TABLE `labels_batches_tmp` (
  43       `label_id` int(11) NOT NULL auto_increment,
  44       `batch_id` int(10) NOT NULL default '1',
  45       `item_number` int(11) NOT NULL default '0',
  46       `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  47       `branch_code` varchar(10) NOT NULL default 'NB',
  48       PRIMARY KEY USING BTREE (`label_id`),
  49       KEY `branch_fk_constraint` (`branch_code`),
  50       KEY `item_fk_constraint` (`item_number`),
  51       FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
  52       FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
  53     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  54 ") or die "DB ERROR: " . $sth->errstr . "\n";
  55
  56 $sth->do("
  57     DROP TABLE IF EXISTS labels_layouts_tmp;");
  58 $sth->do("
  59     CREATE TABLE `labels_layouts_tmp` (
  60       `layout_id` int(4) NOT NULL auto_increment,
  61       `barcode_type` char(100) NOT NULL default 'CODE39',
  62       `printing_type` char(32) NOT NULL default 'BAR',
  63       `layout_name` char(20) NOT NULL default 'DEFAULT',
  64       `guidebox` int(1) default '0',
  65       `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
  66       `font_size` int(4) NOT NULL default '10',
  67       `callnum_split` int(1) default '0',
  68       `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
  69       `format_string` varchar(210) NOT NULL default 'barcode',
  70       PRIMARY KEY  USING BTREE (`layout_id`)
  71     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  72 ") or die "DB ERROR: " . $sth->errstr . "\n";
  73
  74 $sth->do("
  75     DROP TABLE IF EXISTS labels_templates_tmp;");
  76 $sth->do("
  77     CREATE TABLE `labels_templates_tmp` (
  78       `template_id` int(4) NOT NULL auto_increment,
  79       `profile_id` int(4) default NULL,
  80       `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
  81       `template_desc` char(100) NOT NULL default 'Default description',
  82       `page_width` float NOT NULL default '0',
  83       `page_height` float NOT NULL default '0',
  84       `label_width` float NOT NULL default '0',
  85       `label_height` float NOT NULL default '0',
  86       `top_text_margin` float NOT NULL default '0',
  87       `left_text_margin` float NOT NULL default '0',
  88       `top_margin` float NOT NULL default '0',
  89       `left_margin` float NOT NULL default '0',
  90       `cols` int(2) NOT NULL default '0',
  91       `rows` int(2) NOT NULL default '0',
  92       `col_gap` float NOT NULL default '0',
  93       `row_gap` float NOT NULL default '0',
  94       `units` char(20) NOT NULL default 'POINT',
  95       PRIMARY KEY  (`template_id`),
  96       KEY `template_profile_fk_constraint` (`profile_id`)
  97     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  98 ") or die "DB ERROR: " . $sth->errstr . "\n";
  99
 100 $sth->do("
 101     DROP TABLE IF EXISTS printers_profile_tmp;");
 102 $sth->do("
 103     CREATE TABLE `printers_profile_tmp` (
 104       `profile_id` int(4) NOT NULL auto_increment,
 105       `printer_name` varchar(40) NOT NULL default 'Default Printer',
 106       `template_id` int(4) NOT NULL default '0',
 107       `paper_bin` varchar(20) NOT NULL default 'Bypass',
 108       `offset_horz` float NOT NULL default '0',
 109       `offset_vert` float NOT NULL default '0',
 110       `creep_horz` float NOT NULL default '0',
 111       `creep_vert` float NOT NULL default '0',
 112       `units` char(20) NOT NULL default 'POINT',
 113       PRIMARY KEY  (`profile_id`),
 114       UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
 115     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 116 ") or die "DB ERROR: " . $sth->errstr . "\n";
 117
 118 # Migrate data from existing tables to new tables...
 119
 120 $sth->do("INSERT INTO `labels_batches_tmp` (label_id, batch_id, item_number) SELECT labelid, batch_id, itemnumber FROM labels;") or die "DB ERROR: " . $sth->errstr . "\n";
 121 # Since the new label creator keys batches on branch code we must add a branch code during the conversion; the simplest solution appears to be to grab the top branch code from the branches table...
 122 $sth->do("UPDATE `labels_batches_tmp` SET branch_code=(SELECT branchcode FROM branches LIMIT 0,1);") or die "DB ERROR: " . $sth->errstr . "\n";
 123
 124
 125 $sth->do("INSERT INTO `labels_layouts_tmp` (layout_id, barcode_type, printing_type, layout_name, guidebox, callnum_split, text_justify, format_string) SELECT lc.id, lc.barcodetype, lc.printingtype, lc.layoutname, lc.guidebox, lc.callnum_split, lc.text_justify, lc.formatstring FROM labels_conf AS lc;") or die "DB ERROR: " . $sth->errstr . "\n";
 126
 127 $sth->do("INSERT INTO `labels_templates_tmp` (template_id, template_code, template_desc, page_width, page_height, label_width, label_height, top_margin, left_margin, cols, rows, col_gap, row_gap, units) SELECT lt.tmpl_id, lt.tmpl_code, lt.tmpl_desc, lt.page_width, lt.page_height, lt.label_width, lt.label_height, lt.topmargin, lt.leftmargin, lt.cols, lt.rows, lt.colgap, lt.rowgap, lt.units FROM labels_templates AS lt;") or die "DB ERROR: " . $sth->errstr . "\n";
 128
 129 $sth->do("INSERT INTO `printers_profile_tmp` (profile_id, printer_name, template_id, paper_bin, offset_horz, offset_vert, creep_horz, creep_vert, units) SELECT prof_id, printername, tmpl_id, paper_bin, offset_horz, offset_vert, creep_horz, creep_vert, unit FROM printers_profile;") or die "DB ERROR: " . $sth->errstr . "\n";
 130
 131
 132 my $sth1 = C4::Context->dbh->prepare("SELECT layout_id, format_string FROM labels_layouts_tmp;");
 133 #$sth1->{'TraceLevel'} = 3;
 134 $sth1->execute or die "DB ERROR: " . $sth1->errstr . "\n";
 135 while (my $layout = $sth1->fetchrow_hashref()) {
 136     if (!$layout->{'format_string'}) {
 137         my $sth2 = C4::Context->dbh->prepare("SELECT id, title, subtitle, itemtype, barcode, dewey, classification, subclass, itemcallnumber, author, issn, isbn, ccode FROM labels_conf WHERE id = " . $layout->{'layout_id'});
 138         $sth2->execute or die "DB ERROR: " . $sth2->errstr . "\n";
 139         my $record = $sth2->fetchrow_hashref();
 140         my @label_fields = ();
 141         RECORD:
 142         foreach (keys(%$record)) {
 143             next RECORD if $record->{$_} eq '' or $_ eq 'id';
 144             $label_fields[$record->{$_}] = $_;
 145         }
 146         shift @label_fields;
 147         my $format_string = join (",", @label_fields);
 148 #        my $format_string = s/^,//i;
 149         $sth->do("UPDATE `labels_layouts_tmp` SET format_string=\'$format_string\' WHERE layout_id = " . $record->{'id'}) or die "DB ERROR: " . $sth->errstr . "\n";
 150     }
 151 }
 152
 153 my $sth3 = C4::Context->dbh->prepare("SELECT template_id FROM labels_templates_tmp;");
 154 $sth3->execute or die "DB ERROR: " . $sth3->errstr . "\n";
 155 RECORD:
 156 while (my $template = $sth3->fetchrow_hashref()) {
 157         my $sth4 = C4::Context->dbh->prepare("SELECT profile_id FROM printers_profile_tmp WHERE template_id = " . $template->{'template_id'});
 158         $sth4->execute or die "DB ERROR: " . $sth4->errstr . "\n";
 159         my $profile_id = $sth4->fetchrow_hashref();
 160         next RECORD if $profile_id->{'profile_id'} eq '';
 161         $sth->do("UPDATE `labels_templates_tmp` SET profile_id=\'" . $profile_id->{'profile_id'} . "\' WHERE template_id = " . $template->{'template_id'}) or die "DB ERROR: " . $sth->errstr . "\n";
 162 }
 163
 164 # Drop old tables....
 165
 166 $sth->do("DROP TABLE IF EXISTS labels;") or die "DB ERROR: " . $sth->errstr . "\n";
 167 $sth->do("DROP TABLE IF EXISTS labels_batches;") or die "DB ERROR: " . $sth->errstr . "\n";
 168 $sth->do("DROP TABLE IF EXISTS labels_conf;") or die "DB ERROR: " . $sth->errstr . "\n";
 169 $sth->do("DROP TABLE IF EXISTS labels_layouts;") or die "DB ERROR: " . $sth->errstr . "\n";
 170 $sth->do("DROP TABLE IF EXISTS labels_profile;") or die "DB ERROR: " . $sth->errstr . "\n";
 171 $sth->do("DROP TABLE IF EXISTS labels_templates;") or die "DB ERROR: " . $sth->errstr . "\n";
 172 $sth->do("DROP TABLE IF EXISTS printers_profile;") or die "DB ERROR: " . $sth->errstr . "\n";
 173
 174 # Rename temporary tables to permenant names...
 175
 176 $sth->do("ALTER TABLE labels_batches_tmp RENAME TO labels_batches;") or die "DB ERROR: " . $sth->errstr . "\n";
 177 $sth->do("ALTER TABLE labels_layouts_tmp RENAME TO labels_layouts;") or die "DB ERROR: " . $sth->errstr . "\n";
 178 $sth->do("ALTER TABLE labels_templates_tmp RENAME TO labels_templates;") or die "DB ERROR: " . $sth->errstr . "\n";
 179 $sth->do("ALTER TABLE printers_profile_tmp RENAME TO printers_profile;") or die "DB ERROR: " . $sth->errstr . "\n";
 180
 181
 182 # Re-enable key checks...
 183 $sth->do("
 184     SET UNIQUE_CHECKS = 1;
 185 ") or die "DB ERROR: " . $sth->errstr . "\n";
 186
 187 $sth->do("
 188     SET  FOREIGN_KEY_CHECKS = 1;
 189 ") or die "DB ERROR: " . $sth->errstr . "\n";