View | Details | Raw Unified | Return to bug 7065
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
Lines 1448-1453 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1448
--
1448
--
1449
DROP TABLE IF EXISTS `old_reserves`;
1449
DROP TABLE IF EXISTS `old_reserves`;
1450
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1450
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1451
  `reserve_id` int(11) NOT NULL, -- primary key
1451
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1452
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1452
  `reservedate` date default NULL, -- the date the hold was places
1453
  `reservedate` date default NULL, -- the date the hold was places
1453
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
1454
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1466-1471 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b Link Here
1466
  `lowestPriority` tinyint(1) NOT NULL,
1467
  `lowestPriority` tinyint(1) NOT NULL,
1467
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1468
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1468
  `suspend_until` DATETIME NULL DEFAULT NULL,
1469
  `suspend_until` DATETIME NULL DEFAULT NULL,
1470
  PRIMARY KEY (`reserve_id`),
1469
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1471
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1470
  KEY `old_reserves_biblionumber` (`biblionumber`),
1472
  KEY `old_reserves_biblionumber` (`biblionumber`),
1471
  KEY `old_reserves_itemnumber` (`itemnumber`),
1473
  KEY `old_reserves_itemnumber` (`itemnumber`),
Lines 1644-1649 CREATE TABLE `reserveconstraints` ( Link Here
1644
1646
1645
DROP TABLE IF EXISTS `reserves`;
1647
DROP TABLE IF EXISTS `reserves`;
1646
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1648
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1649
  `reserve_id` int(11) NOT NULL auto_increment, -- primary key
1647
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1650
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1648
  `reservedate` date default NULL, -- the date the hold was places
1651
  `reservedate` date default NULL, -- the date the hold was places
1649
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
1652
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1662-1667 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha Link Here
1662
  `lowestPriority` tinyint(1) NOT NULL,
1665
  `lowestPriority` tinyint(1) NOT NULL,
1663
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1666
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1664
  `suspend_until` DATETIME NULL DEFAULT NULL,
1667
  `suspend_until` DATETIME NULL DEFAULT NULL,
1668
  PRIMARY KEY (`reserve_id`),
1665
  KEY priorityfoundidx (priority,found),
1669
  KEY priorityfoundidx (priority,found),
1666
  KEY `borrowernumber` (`borrowernumber`),
1670
  KEY `borrowernumber` (`borrowernumber`),
1667
  KEY `biblionumber` (`biblionumber`),
1671
  KEY `biblionumber` (`biblionumber`),
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +63 lines)
Lines 5343-5348 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5343
    SetVersion($DBversion);
5343
    SetVersion($DBversion);
5344
}
5344
}
5345
5345
5346
$DBversion = "3.09.00.XXX";
5347
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
5348
    $dbh->do('START TRANSACTION');
5349
    $dbh->do('CREATE TABLE tmp_reserves AS SELECT * FROM old_reserves LIMIT 0');
5350
    $dbh->do('ALTER TABLE tmp_reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
5351
    $dbh->do("
5352
        INSERT INTO tmp_reserves (
5353
          borrowernumber, reservedate, biblionumber,
5354
          constrainttype, branchcode, notificationdate,
5355
          reminderdate, cancellationdate, reservenotes,
5356
          priority, found, timestamp, itemnumber,
5357
          waitingdate, expirationdate, lowestPriority
5358
        ) SELECT
5359
          borrowernumber, reservedate, biblionumber,
5360
          constrainttype, branchcode, notificationdate,
5361
          reminderdate, cancellationdate, reservenotes,
5362
          priority, found, timestamp, itemnumber,
5363
          waitingdate, expirationdate, lowestPriority
5364
        FROM old_reserves ORDER BY reservedate
5365
    ");
5366
    $dbh->do('SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves )');
5367
    $dbh->do('TRUNCATE old_reserves');
5368
    $dbh->do('ALTER TABLE old_reserves ADD reserve_id INT( 11 ) NOT NULL PRIMARY KEY FIRST');
5369
    $dbh->do('INSERT INTO old_reserves SELECT * FROM tmp_reserves WHERE reserve_id <= @ai');
5370
    $dbh->do("
5371
        INSERT INTO tmp_reserves (
5372
          borrowernumber, reservedate, biblionumber,
5373
          constrainttype, branchcode, notificationdate,
5374
          reminderdate, cancellationdate, reservenotes,
5375
          priority, found, timestamp, itemnumber,
5376
          waitingdate, expirationdate, lowestPriority
5377
        ) SELECT
5378
          borrowernumber, reservedate, biblionumber,
5379
          constrainttype, branchcode, notificationdate,
5380
          reminderdate, cancellationdate, reservenotes,
5381
          priority, found, timestamp, itemnumber,
5382
          waitingdate, expirationdate, lowestPriority
5383
        FROM reserves ORDER BY reservedate
5384
    ");
5385
    $dbh->do('TRUNCATE reserves');
5386
    $dbh->do('ALTER TABLE reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
5387
    $dbh->do('INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai');
5388
    $dbh->do('DROP TABLE tmp_reserves');
5389
    $dbh->do('COMMIT');
5390
5391
    my $sth = $dbh->prepare("
5392
        SELECT COUNT( * ) AS count
5393
        FROM information_schema.COLUMNS
5394
        WHERE COLUMN_NAME =  'reserve_id'
5395
        AND (
5396
          TABLE_NAME LIKE  'reserves'
5397
          OR
5398
          TABLE_NAME LIKE  'old_reserves'
5399
        )
5400
    ");
5401
    $sth->execute();
5402
    my $row = $sth->fetchrow_hashref();
5403
    die("Failed to add reserve_id to reserves tables, please refresh the page to try again.") unless ( $row->{'count'} );
5404
5405
    print "Upgrade to $DBversion done (add reserve_id to reserves & old_reserves tables)\n";
5406
    SetVersion($DBversion);
5407
}
5408
5346
=head1 FUNCTIONS
5409
=head1 FUNCTIONS
5347
5410
5348
=head2 TableExists($table)
5411
=head2 TableExists($table)
5349
- 

Return to bug 7065