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

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
Lines 1446-1451 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1446
--
1446
--
1447
DROP TABLE IF EXISTS `old_reserves`;
1447
DROP TABLE IF EXISTS `old_reserves`;
1448
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1448
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1449
  `reserve_id` int(11) NOT NULL, -- primary key
1449
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1450
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1450
  `reservedate` date default NULL, -- the date the hold was places
1451
  `reservedate` date default NULL, -- the date the hold was places
1451
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
1452
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1464-1469 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b Link Here
1464
  `lowestPriority` tinyint(1) NOT NULL,
1465
  `lowestPriority` tinyint(1) NOT NULL,
1465
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1466
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1466
  `suspend_until` DATETIME NULL DEFAULT NULL,
1467
  `suspend_until` DATETIME NULL DEFAULT NULL,
1468
  PRIMARY KEY (`reserve_id`),
1467
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1469
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1468
  KEY `old_reserves_biblionumber` (`biblionumber`),
1470
  KEY `old_reserves_biblionumber` (`biblionumber`),
1469
  KEY `old_reserves_itemnumber` (`itemnumber`),
1471
  KEY `old_reserves_itemnumber` (`itemnumber`),
Lines 1642-1647 CREATE TABLE `reserveconstraints` ( Link Here
1642
1644
1643
DROP TABLE IF EXISTS `reserves`;
1645
DROP TABLE IF EXISTS `reserves`;
1644
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1646
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1647
  `reserve_id` int(11) NOT NULL, -- primary key
1645
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1648
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1646
  `reservedate` date default NULL, -- the date the hold was places
1649
  `reservedate` date default NULL, -- the date the hold was places
1647
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
1650
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1660-1665 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha Link Here
1660
  `lowestPriority` tinyint(1) NOT NULL,
1663
  `lowestPriority` tinyint(1) NOT NULL,
1661
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1664
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
1662
  `suspend_until` DATETIME NULL DEFAULT NULL,
1665
  `suspend_until` DATETIME NULL DEFAULT NULL,
1666
  PRIMARY KEY (`reserve_id`),
1663
  KEY priorityfoundidx (priority,found),
1667
  KEY priorityfoundidx (priority,found),
1664
  KEY `borrowernumber` (`borrowernumber`),
1668
  KEY `borrowernumber` (`borrowernumber`),
1665
  KEY `biblionumber` (`biblionumber`),
1669
  KEY `biblionumber` (`biblionumber`),
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +110 lines)
Lines 5212-5217 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5212
    SetVersion($DBversion);
5212
    SetVersion($DBversion);
5213
}
5213
}
5214
5214
5215
$DBversion = "3.07.00.XXX";
5216
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
5217
    my $i = 1;
5218
5219
    my ( $query_update, $sth_update, $query_select, $sth_select );
5220
5221
    $dbh->do("ALTER TABLE reserves ADD reserve_id INT NOT NULL FIRST");
5222
    $dbh->do("ALTER TABLE old_reserves ADD reserve_id INT NOT NULL FIRST");
5223
5224
    $query_select = "SELECT * FROM old_reserves ORDER BY timestamp ASC";
5225
    $sth_select = $dbh->prepare( $query_select );
5226
    $sth_select->execute();
5227
    while( my @r = $sth_select->fetchrow_array() ) {
5228
      my ( $reserve_id, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode,
5229
           $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found,
5230
           $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r;
5231
5232
      $query_update = "UPDATE old_reserves SET reserve_id = $i WHERE ";
5233
      $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL";
5234
      $query_update .= " AND ";
5235
      $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL";
5236
      $query_update .= " AND ";
5237
      $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL";
5238
      $query_update .= " AND ";
5239
      $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL";
5240
      $query_update .= " AND ";
5241
      $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL";
5242
      $query_update .= " AND ";
5243
      $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL";
5244
      $query_update .= " AND ";
5245
      $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL";
5246
      $query_update .= " AND ";
5247
      $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL";
5248
      $query_update .= " AND ";
5249
      $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL";
5250
      $query_update .= " AND ";
5251
      $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL";
5252
      $query_update .= " AND ";
5253
      $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL";
5254
      $query_update .= " AND ";
5255
      $query_update .= "timestamp = '$timestamp'";
5256
      $query_update .= " AND ";
5257
      $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL";
5258
      $query_update .= " AND ";
5259
      $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL";
5260
      $query_update .= " AND ";
5261
      $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL";
5262
      $query_update .= " AND ";
5263
      $query_update .= "lowestPriority = '$lowestPriority'";
5264
      $query_update .= "AND reserve_id = 0  LIMIT 1";
5265
5266
      $dbh->do( $query_update );
5267
      $i++;
5268
    }
5269
5270
    $query_select = "SELECT * FROM reserves ORDER BY timestamp ASC";
5271
    $sth_select = $dbh->prepare( $query_select );
5272
    $sth_select->execute();
5273
    while( my @r = $sth_select->fetchrow_array() ) {
5274
      my ( $reserve_id, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode,
5275
           $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found,
5276
           $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r;
5277
5278
      $query_update = "UPDATE reserves SET reserve_id = $i WHERE ";
5279
      $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL";
5280
      $query_update .= " AND ";
5281
      $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL";
5282
      $query_update .= " AND ";
5283
      $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL";
5284
      $query_update .= " AND ";
5285
      $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL";
5286
      $query_update .= " AND ";
5287
      $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL";
5288
      $query_update .= " AND ";
5289
      $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL";
5290
      $query_update .= " AND ";
5291
      $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL";
5292
      $query_update .= " AND ";
5293
      $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL";
5294
      $query_update .= " AND ";
5295
      $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL";
5296
      $query_update .= " AND ";
5297
      $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL";
5298
      $query_update .= " AND ";
5299
      $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL";
5300
      $query_update .= " AND ";
5301
      $query_update .= "timestamp = '$timestamp'";
5302
      $query_update .= " AND ";
5303
      $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL";
5304
      $query_update .= " AND ";
5305
      $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL";
5306
      $query_update .= " AND ";
5307
      $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL";
5308
      $query_update .= " AND ";
5309
      $query_update .= "lowestPriority = '$lowestPriority'";
5310
      $query_update .= "AND reserve_id = 0 LIMIT 1";
5311
5312
      $dbh->do( $query_update );
5313
      $i++;
5314
    }
5315
5316
    $dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reserve_id )");
5317
    $dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reserve_id )");
5318
5319
    $dbh->do("ALTER TABLE reserves CHANGE reserve_id reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT");
5320
5321
    print "Upgrade to $DBversion done ( add reserve_id to reserves and old_reserves tables )\n";
5322
    SetVersion($DBversion);
5323
}
5324
5215
=head1 FUNCTIONS
5325
=head1 FUNCTIONS
5216
5326
5217
=head2 TableExists($table)
5327
=head2 TableExists($table)
5218
- 

Return to bug 7065