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

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
Lines 1386-1391 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1386
--
1386
--
1387
DROP TABLE IF EXISTS `old_reserves`;
1387
DROP TABLE IF EXISTS `old_reserves`;
1388
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1388
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
1389
  `reservenumber` int(11) NOT NULL, -- primary key
1389
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1390
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1390
  `reservedate` date default NULL, -- the date the hold was places
1391
  `reservedate` date default NULL, -- the date the hold was places
1391
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
1392
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1402-1407 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b Link Here
1402
  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1403
  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1403
  `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
1404
  `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
1404
  `lowestPriority` tinyint(1) NOT NULL,
1405
  `lowestPriority` tinyint(1) NOT NULL,
1406
  PRIMARY KEY (`reservenumber`),
1405
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1407
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
1406
  KEY `old_reserves_biblionumber` (`biblionumber`),
1408
  KEY `old_reserves_biblionumber` (`biblionumber`),
1407
  KEY `old_reserves_itemnumber` (`itemnumber`),
1409
  KEY `old_reserves_itemnumber` (`itemnumber`),
Lines 1579-1584 CREATE TABLE `reserveconstraints` ( Link Here
1579
1581
1580
DROP TABLE IF EXISTS `reserves`;
1582
DROP TABLE IF EXISTS `reserves`;
1581
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1583
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
1584
  `reservenumber` int(11) NOT NULL, -- primary key
1582
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1585
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1583
  `reservedate` date default NULL, -- the date the hold was places
1586
  `reservedate` date default NULL, -- the date the hold was places
1584
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
1587
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1595-1600 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha Link Here
1595
  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1598
  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1596
  `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
1599
  `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
1597
  `lowestPriority` tinyint(1) NOT NULL,
1600
  `lowestPriority` tinyint(1) NOT NULL,
1601
  PRIMARY KEY (`reservenumber`),
1598
  KEY priorityfoundidx (priority,found),
1602
  KEY priorityfoundidx (priority,found),
1599
  KEY `borrowernumber` (`borrowernumber`),
1603
  KEY `borrowernumber` (`borrowernumber`),
1600
  KEY `biblionumber` (`biblionumber`),
1604
  KEY `biblionumber` (`biblionumber`),
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +109 lines)
Lines 4923-4928 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
4923
    SetVersion($DBversion);
4923
    SetVersion($DBversion);
4924
}
4924
}
4925
4925
4926
$DBversion = "3.07.00.XXX";
4927
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4928
    my $i = 1;
4929
    
4930
    my ( $query_update, $sth_update, $query_select, $sth_select );
4931
    
4932
    $dbh->do("ALTER TABLE reserves ADD reservenumber INT NOT NULL FIRST");
4933
    $dbh->do("ALTER TABLE old_reserves ADD reservenumber INT NOT NULL FIRST");    
4934
    
4935
    $query_select = "SELECT * FROM old_reserves ORDER BY timestamp ASC";
4936
    $sth_select = $dbh->prepare( $query_select );
4937
    $sth_select->execute();
4938
    while( my @r = $sth_select->fetchrow_array() ) {
4939
      my ( $reservenumber, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode, 
4940
           $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found, 
4941
           $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r;
4942
           
4943
      $query_update = "UPDATE old_reserves SET reservenumber = $i WHERE ";
4944
      $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL";
4945
      $query_update .= " AND ";
4946
      $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL"; 
4947
      $query_update .= " AND ";
4948
      $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL";
4949
      $query_update .= " AND ";
4950
      $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL";
4951
      $query_update .= " AND ";
4952
      $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL";
4953
      $query_update .= " AND ";
4954
      $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL";
4955
      $query_update .= " AND ";
4956
      $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL";
4957
      $query_update .= " AND ";
4958
      $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL";
4959
      $query_update .= " AND ";
4960
      $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL";
4961
      $query_update .= " AND ";
4962
      $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL";
4963
      $query_update .= " AND ";
4964
      $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL";
4965
      $query_update .= " AND ";
4966
      $query_update .= "timestamp = '$timestamp'"; 
4967
      $query_update .= " AND ";
4968
      $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL";
4969
      $query_update .= " AND ";
4970
      $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL";
4971
      $query_update .= " AND ";
4972
      $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL";
4973
      $query_update .= " AND ";
4974
      $query_update .= "lowestPriority = '$lowestPriority'"; 
4975
      $query_update .= "AND reservenumber = 0  LIMIT 1";
4976
4977
      $dbh->do( $query_update );
4978
      $i++;
4979
    }
4980
4981
    $query_select = "SELECT * FROM reserves ORDER BY timestamp ASC";
4982
    $sth_select = $dbh->prepare( $query_select );
4983
    $sth_select->execute();
4984
    while( my @r = $sth_select->fetchrow_array() ) {
4985
      my ( $reservenumber, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode, 
4986
           $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found, 
4987
           $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r;
4988
           
4989
      $query_update = "UPDATE reserves SET reservenumber = $i WHERE ";
4990
      $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL";
4991
      $query_update .= " AND ";
4992
      $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL"; 
4993
      $query_update .= " AND ";
4994
      $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL";
4995
      $query_update .= " AND ";
4996
      $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL";
4997
      $query_update .= " AND ";
4998
      $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL";
4999
      $query_update .= " AND ";
5000
      $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL";
5001
      $query_update .= " AND ";
5002
      $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL";
5003
      $query_update .= " AND ";
5004
      $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL";
5005
      $query_update .= " AND ";
5006
      $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL";
5007
      $query_update .= " AND ";
5008
      $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL";
5009
      $query_update .= " AND ";
5010
      $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL";
5011
      $query_update .= " AND ";
5012
      $query_update .= "timestamp = '$timestamp'"; 
5013
      $query_update .= " AND ";
5014
      $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL";
5015
      $query_update .= " AND ";
5016
      $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL";
5017
      $query_update .= " AND ";
5018
      $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL";
5019
      $query_update .= " AND ";
5020
      $query_update .= "lowestPriority = '$lowestPriority'"; 
5021
      $query_update .= "AND reservenumber = 0 LIMIT 1";
5022
5023
      $dbh->do( $query_update );
5024
      $i++;
5025
    }
5026
5027
    $dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reservenumber )");
5028
    $dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reservenumber )");
5029
5030
    $dbh->do("ALTER TABLE reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT");
5031
    
5032
    print "Upgrade to $DBversion done ( add reservenumber to reserves and old_reserves tables )\n";
5033
    SetVersion($DBversion);
5034
}
4926
5035
4927
=head1 FUNCTIONS
5036
=head1 FUNCTIONS
4928
5037
4929
- 

Return to bug 7065