Attachment #8036 for bug #7065

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




--
DROP TABLE IF EXISTS `old_reserves`;
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
  `reservenumber` int(11) NOT NULL, -- primary key
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
  `reservedate` date default NULL, -- the date the hold was places
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on

  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
  `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)
  `lowestPriority` tinyint(1) NOT NULL,
  PRIMARY KEY (`reservenumber`),
  KEY `old_reserves_borrowernumber` (`borrowernumber`),
  KEY `old_reserves_biblionumber` (`biblionumber`),
  KEY `old_reserves_itemnumber` (`itemnumber`),


DROP TABLE IF EXISTS `reserves`;
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
  `reservenumber` int(11) NOT NULL, -- primary key
  `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
  `reservedate` date default NULL, -- the date the hold was places
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on

  `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
  `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)
  `lowestPriority` tinyint(1) NOT NULL,
  PRIMARY KEY (`reservenumber`),
  KEY priorityfoundidx (priority,found),
  KEY `borrowernumber` (`borrowernumber`),
  KEY `biblionumber` (`biblionumber`),




    SetVersion($DBversion);
}

$DBversion = "3.07.00.XXX";
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
    my $i = 1;
    
    my ( $query_update, $sth_update, $query_select, $sth_select );
    
    $dbh->do("ALTER TABLE reserves ADD reservenumber INT NOT NULL FIRST");
    $dbh->do("ALTER TABLE old_reserves ADD reservenumber INT NOT NULL FIRST");
    
    $query_update = "UPDATE old_reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1";
    $sth_update = $dbh->prepare( $query_update );
    
    $query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM old_reserves ORDER BY timestamp ASC";
    $sth_select = $dbh->prepare( $query_select );
    $sth_select->execute();
    while( my @r = $sth_select->fetchrow_array() ) {
      print "Updating old_reserves row with key $i\n";
      $sth_update->execute( $i, @r );
      $i++;
    }

    $query_update = "UPDATE reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1";
    $sth_update = $dbh->prepare( $query_update );
    
    $query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM reserves ORDER BY timestamp ASC";
    $sth_select = $dbh->prepare( $query_select );
    $sth_select->execute();
    while( my @r = $sth_select->fetchrow_array() ) {
      print "Updating reserves row with key $i\n";
      $sth_update->execute( $i, @r );
      $i++;
    }

    $dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reservenumber )");
    $dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reservenumber )");

    $dbh->do("ALTER TABLE reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT");
    
    print "Upgrade to $DBversion done ( add reservenumber to reserves and old_reserves tables )\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 DropAllForeignKeys($table)
- 

Return to bug 7065

Lines 4734-4739 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (-1 / +42 lines)
4734	SetVersion($DBversion);	4734	SetVersion($DBversion);
4735	}	4735	}
4736		4736
		4737	$DBversion = "3.07.00.XXX";
		4738	if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
		4739	my $i = 1;
		4740
		4741	my ( $query_update, $sth_update, $query_select, $sth_select );
		4742
		4743	$dbh->do("ALTER TABLE reserves ADD reservenumber INT NOT NULL FIRST");
		4744	$dbh->do("ALTER TABLE old_reserves ADD reservenumber INT NOT NULL FIRST");
		4745
		4746	$query_update = "UPDATE old_reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1";
		4747	$sth_update = $dbh->prepare( $query_update );
		4748
		4749	$query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM old_reserves ORDER BY timestamp ASC";
		4750	$sth_select = $dbh->prepare( $query_select );
		4751	$sth_select->execute();
		4752	while( my @r = $sth_select->fetchrow_array() ) {
		4753	print "Updating old_reserves row with key $i\n";
		4754	$sth_update->execute( $i, @r );
		4755	$i++;
		4756	}
		4757
		4758	$query_update = "UPDATE reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1";
		4759	$sth_update = $dbh->prepare( $query_update );
		4760
		4761	$query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM reserves ORDER BY timestamp ASC";
		4762	$sth_select = $dbh->prepare( $query_select );
		4763	$sth_select->execute();
		4764	while( my @r = $sth_select->fetchrow_array() ) {
		4765	print "Updating reserves row with key $i\n";
		4766	$sth_update->execute( $i, @r );
		4767	$i++;
		4768	}
		4769
		4770	$dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reservenumber )");
		4771	$dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reservenumber )");
		4772
		4773	$dbh->do("ALTER TABLE reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT");
		4774
		4775	print "Upgrade to $DBversion done ( add reservenumber to reserves and old_reserves tables )\n";
		4776	SetVersion($DBversion);
		4777	}
		4778
4737	=head1 FUNCTIONS	4779	=head1 FUNCTIONS
4738		4780
4739	=head2 DropAllForeignKeys($table)	4781	=head2 DropAllForeignKeys($table)
4740	-

Lines 1384-1389 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
1384	--	1384	--
1385	DROP TABLE IF EXISTS `old_reserves`;	1385	DROP TABLE IF EXISTS `old_reserves`;
1386	CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)	1386	CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
		1387	`reservenumber` int(11) NOT NULL, -- primary key
1387	`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for	1388	`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
1388	`reservedate` date default NULL, -- the date the hold was places	1389	`reservedate` date default NULL, -- the date the hold was places
1389	`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on	1390	`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1400-1405 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b Link Here
1400	`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library	1401	`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1401	`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)	1402	`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)
1402	`lowestPriority` tinyint(1) NOT NULL,	1403	`lowestPriority` tinyint(1) NOT NULL,
		1404	PRIMARY KEY (`reservenumber`),
1403	KEY `old_reserves_borrowernumber` (`borrowernumber`),	1405	KEY `old_reserves_borrowernumber` (`borrowernumber`),
1404	KEY `old_reserves_biblionumber` (`biblionumber`),	1406	KEY `old_reserves_biblionumber` (`biblionumber`),
1405	KEY `old_reserves_itemnumber` (`itemnumber`),	1407	KEY `old_reserves_itemnumber` (`itemnumber`),
Lines 1577-1582 CREATE TABLE `reserveconstraints` ( Link Here
1577		1579
1578	DROP TABLE IF EXISTS `reserves`;	1580	DROP TABLE IF EXISTS `reserves`;
1579	CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha	1581	CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
		1582	`reservenumber` int(11) NOT NULL, -- primary key
1580	`borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for	1583	`borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
1581	`reservedate` date default NULL, -- the date the hold was places	1584	`reservedate` date default NULL, -- the date the hold was places
1582	`biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on	1585	`biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
Lines 1593-1598 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha Link Here
1593	`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library	1596	`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
1594	`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	`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)
1595	`lowestPriority` tinyint(1) NOT NULL,	1598	`lowestPriority` tinyint(1) NOT NULL,
		1599	PRIMARY KEY (`reservenumber`),
1596	KEY priorityfoundidx (priority,found),	1600	KEY priorityfoundidx (priority,found),
1597	KEY `borrowernumber` (`borrowernumber`),	1601	KEY `borrowernumber` (`borrowernumber`),
1598	KEY `biblionumber` (`biblionumber`),	1602	KEY `biblionumber` (`biblionumber`),