Attachment #9413 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)
  `reserve_id` 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

  `lowestPriority` tinyint(1) NOT NULL,
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
  `suspend_until` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`reserve_id`),
  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
  `reserve_id` int(11) NOT NULL auto_increment, -- 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

  `lowestPriority` tinyint(1) NOT NULL,
  `suspend` BOOLEAN NOT NULL DEFAULT 0,
  `suspend_until` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`reserve_id`),
  KEY priorityfoundidx (priority,found),
  KEY `borrowernumber` (`borrowernumber`),
  KEY `biblionumber` (`biblionumber`),




    SetVersion($DBversion);
}

$DBversion = "3.08.00.XXX";
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
    $dbh->do('START TRANSACTION');
    $dbh->do('CREATE TABLE tmp_reserves AS SELECT * FROM reserves LIMIT 0');
    $dbh->do('ALTER TABLE tmp_reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
    $dbh->do("
        INSERT INTO tmp_reserves (
          borrowernumber, reservedate, biblionumber,
          constrainttype, branchcode, notificationdate,
          reminderdate, cancellationdate, reservenotes,
          priority, found, timestamp, itemnumber,
          waitingdate, expirationdate, lowestPriority
        ) SELECT
          borrowernumber, reservedate, biblionumber,
          constrainttype, branchcode, notificationdate,
          reminderdate, cancellationdate, reservenotes,
          priority, found, timestamp, itemnumber,
          waitingdate, expirationdate, lowestPriority
        FROM old_reserves ORDER BY reservedate
    ");
    $dbh->do('SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves )');
    $dbh->do('TRUNCATE old_reserves');
    $dbh->do('ALTER TABLE old_reserves ADD reserve_id INT( 11 ) NOT NULL PRIMARY KEY FIRST');
    $dbh->do('INSERT INTO old_reserves SELECT * FROM tmp_reserves WHERE reserve_id <= @ai');
    $dbh->do("
        INSERT INTO tmp_reserves (
          borrowernumber, reservedate, biblionumber,
          constrainttype, branchcode, notificationdate,
          reminderdate, cancellationdate, reservenotes,
          priority, found, timestamp, itemnumber,
          waitingdate, expirationdate, lowestPriority
        ) SELECT
          borrowernumber, reservedate, biblionumber,
          constrainttype, branchcode, notificationdate,
          reminderdate, cancellationdate, reservenotes,
          priority, found, timestamp, itemnumber,
          waitingdate, expirationdate, lowestPriority
        FROM reserves ORDER BY reservedate
    ");
    $dbh->do('TRUNCATE reserves');
    $dbh->do('ALTER TABLE reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
    $dbh->do('INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai');
    $dbh->do('DROP TABLE tmp_reserves');
    $dbh->do('COMMIT');

    my $sth = $dbh->prepare("
        SELECT COUNT( * ) AS count
        FROM information_schema.COLUMNS
        WHERE COLUMN_NAME =  'reserve_id'
        AND (
          TABLE_NAME LIKE  'reserves'
          OR
          TABLE_NAME LIKE  'old_reserves'
        )
    ");
    $sth->execute();
    my $row = $sth->fetchrow_hashref();
    die("Failed to add reserve_id to reserves tables, please refresh the page to try again.") unless ( $row->{'count'} );

    print "Upgrade to $DBversion done (add reserve_id to reserves & old_reserves tables)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)
- 

Return to bug 7065

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

(-)a/installer/data/mysql/updatedatabase.pl (-1 / +63 lines)
5212	SetVersion($DBversion);	5212	SetVersion($DBversion);
5213	}	5213	}
5214		5214
		5215	$DBversion = "3.08.00.XXX";
		5216	if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
		5217	$dbh->do('START TRANSACTION');
		5218	$dbh->do('CREATE TABLE tmp_reserves AS SELECT * FROM reserves LIMIT 0');
		5219	$dbh->do('ALTER TABLE tmp_reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
		5220	$dbh->do("
		5221	INSERT INTO tmp_reserves (
		5222	borrowernumber, reservedate, biblionumber,
		5223	constrainttype, branchcode, notificationdate,
		5224	reminderdate, cancellationdate, reservenotes,
		5225	priority, found, timestamp, itemnumber,
		5226	waitingdate, expirationdate, lowestPriority
		5227	) SELECT
		5228	borrowernumber, reservedate, biblionumber,
		5229	constrainttype, branchcode, notificationdate,
		5230	reminderdate, cancellationdate, reservenotes,
		5231	priority, found, timestamp, itemnumber,
		5232	waitingdate, expirationdate, lowestPriority
		5233	FROM old_reserves ORDER BY reservedate
		5234	");
		5235	$dbh->do('SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves )');
		5236	$dbh->do('TRUNCATE old_reserves');
		5237	$dbh->do('ALTER TABLE old_reserves ADD reserve_id INT( 11 ) NOT NULL PRIMARY KEY FIRST');
		5238	$dbh->do('INSERT INTO old_reserves SELECT * FROM tmp_reserves WHERE reserve_id <= @ai');
		5239	$dbh->do("
		5240	INSERT INTO tmp_reserves (
		5241	borrowernumber, reservedate, biblionumber,
		5242	constrainttype, branchcode, notificationdate,
		5243	reminderdate, cancellationdate, reservenotes,
		5244	priority, found, timestamp, itemnumber,
		5245	waitingdate, expirationdate, lowestPriority
		5246	) SELECT
		5247	borrowernumber, reservedate, biblionumber,
		5248	constrainttype, branchcode, notificationdate,
		5249	reminderdate, cancellationdate, reservenotes,
		5250	priority, found, timestamp, itemnumber,
		5251	waitingdate, expirationdate, lowestPriority
		5252	FROM reserves ORDER BY reservedate
		5253	");
		5254	$dbh->do('TRUNCATE reserves');
		5255	$dbh->do('ALTER TABLE reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST');
		5256	$dbh->do('INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai');
		5257	$dbh->do('DROP TABLE tmp_reserves');
		5258	$dbh->do('COMMIT');
		5259
		5260	my $sth = $dbh->prepare("
		5261	SELECT COUNT( * ) AS count
		5262	FROM information_schema.COLUMNS
		5263	WHERE COLUMN_NAME = 'reserve_id'
		5264	AND (
		5265	TABLE_NAME LIKE 'reserves'
		5266	OR
		5267	TABLE_NAME LIKE 'old_reserves'
		5268	)
		5269	");
		5270	$sth->execute();
		5271	my $row = $sth->fetchrow_hashref();
		5272	die("Failed to add reserve_id to reserves tables, please refresh the page to try again.") unless ( $row->{'count'} );
		5273
		5274	print "Upgrade to $DBversion done (add reserve_id to reserves & old_reserves tables)\n";
		5275	SetVersion($DBversion);
		5276	}
		5277
5215	=head1 FUNCTIONS	5278	=head1 FUNCTIONS
5216		5279
5217	=head2 TableExists($table)	5280	=head2 TableExists($table)
5218	-

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

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
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 auto_increment, -- 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`),