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 .= "  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 .= "  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