Attachment #30297 for bug #9303

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




  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
  `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
  `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
  UNIQUE KEY `cardnumber` (`cardnumber`),
  PRIMARY KEY `borrowernumber` (`borrowernumber`),
  KEY `categorycode` (`categorycode`),

  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
  `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history  KEY `borrowernumber` (`borrowernumber`),
  `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
  KEY borrowernumber (borrowernumber),
  KEY `cardnumber` (`cardnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `issues`;
CREATE TABLE `issues` ( -- information related to check outs or issues
  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
  `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
  `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
  `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)

  `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
  `issuedate` datetime default NULL, -- date the item was checked out or issued
  PRIMARY KEY (`issue_id`),
  KEY `issuesborridx` (`borrowernumber`),
  KEY `itemnumber_idx` (`itemnumber`),
  KEY `branchcode_idx` (`branchcode`),


DROP TABLE IF EXISTS `old_issues`;
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
  `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
  `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
  `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)

  `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
  `issuedate` datetime default NULL, -- date the item was checked out or issued
  PRIMARY KEY (`issue_id`),
  KEY `old_issuesborridx` (`borrowernumber`),
  KEY `old_issuesitemidx` (`itemnumber`),
  KEY `branchcode_idx` (`branchcode`),




('AllowNotForLoanOverride','0','','If ON, Koha will allow the librarian to loan a not for loan item.','YesNo'),
('AllowOfflineCirculation','0','','If on, enables HTML5 offline circulation functionality.','YesNo'),
('AllowOnShelfHolds','0','','Allow hold requests to be placed on items that are not on loan','YesNo'),
('AllowPatronToSetCheckoutsVisibilityForGuarantor',  '0', NULL, 'If enabled, the patron can set checkouts to be visible to his or her guarantor',  'YesNo'),
('AllowPKIAuth','None','None|Common Name|emailAddress','Use the field from a client-side SSL certificate to look a user in the Koha database','Choice'),
('AllowPurchaseSuggestionBranchChoice','0','1','Allow user to choose branch when making a purchase suggestion','YesNo'),
('AllowRenewalLimitOverride','0',NULL,'if ON, allows renewal limits to be overridden on the circulation screen','YesNo'),
('AllowReturnToBranch','anywhere','anywhere|homebranch|holdingbranch|homeorholdingbranch','Where an item may be returned','Choice'),
('AllowSelfCheckReturns','0','','If enabled, patrons may return items through the Web-based Self Checkout','YesNo'),
('AllowStaffToSetCheckoutsVisibilityForGuarantor','0',NULL,'If enabled, library staff can set a patron''s checkouts to be visible to linked patrons from the opac.',  'YesNo'),
('AllowTooManyOverride','1','','If on, allow staff to override and check out items when the patron has reached the maximum number of allowed checkouts','YesNo'),
('alphabet','A B C D E F G H I J K L M N O P Q R S T U V W X Y Z',NULL,'Alphabet than can be expanded into browse links, e.g. on Home > Patrons','free'),
('AlternateHoldingsField','',NULL,'The MARC field/subfield that contains alternate holdings information for bibs taht do not have items attached (e.g. 852abchi for libraries converting from MARC Magician).','free'),




use C4::Context;
use C4::Installer;
use C4::Dates;
use Koha::Database;

use MARC::Record;
use MARC::File::XML ( BinaryEncoding => 'utf8' );

    SetVersion ($DBversion);
}

$DBversion = "3.17.00.XXX";
if(CheckVersion($DBversion)) {
    $dbh->do(q{
        ALTER TABLE borrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0'
    });
    $dbh->do(q{
        ALTER TABLE deletedborrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0'
    });
    $dbh->do(q{
        ALTER TABLE old_issues DROP PRIMARY KEY
    });
    $dbh->do(q{
        ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
    });
    $dbh->do(q{
        ALTER TABLE old_issues DROP INDEX itemnumber_idx
    });
    $dbh->do(q{
        ALTER TABLE issues DROP PRIMARY KEY
    });
    $dbh->do(q{
        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
    });
    $dbh->do(q{
        ALTER TABLE issues DROP INDEX itemnumber_idx
    });
    $dbh->do(q{
        ALTER TABLE issues ADD UNIQUE itemnumber_idx (itemnumber)
    });
    $dbh->do(qq{
        UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
    });

    my $schema = Koha::Database->new()->schema();
    my $max_issue_id = $schema->resultset('Issue')->get_column('issue_id')->max();
    $max_issue_id ||= $schema->resultset('OldIssue')->get_column('issue_id')->max();
    $max_issue_id ||= 0;
    $max_issue_id++;
    $dbh->do(qq{
        ALTER TABLE issues AUTO_INCREMENT = $max_issue_id
    });

    $dbh->do(q{
        INSERT INTO systempreferences (variable, value, options, explanation, type )
        VALUES (
            'AllowStaffToSetCheckoutsVisibilityForGuarantor',  '0', NULL,
            'If enabled, library staff can set a patron''s checkouts to be visible to linked patrons from the opac.',  'YesNo'
        )
    });

    $dbh->do(q{
        INSERT INTO systempreferences (variable, value, options, explanation, type )
        VALUES (
            'AllowPatronToSetCheckoutsVisibilityForGuarantor',  '0', NULL,
            'If enabled, the patron can set checkouts to be visible to  his or her guarantor',  'YesNo'
        )
    });

    print "Upgrade to $DBversion done (Bug 9303 - relative's checkouts in the opac)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)
- 

Return to bug 9303

Lines 37-42 use Getopt::Long; Link Here

(-)a/installer/data/mysql/updatedatabase.pl (-1 / +63 lines)
37	use C4::Context;	37	use C4::Context;
38	use C4::Installer;	38	use C4::Installer;
39	use C4::Dates;	39	use C4::Dates;
		40	use Koha::Database;
40		41
41	use MARC::Record;	42	use MARC::Record;
42	use MARC::File::XML ( BinaryEncoding => 'utf8' );	43	use MARC::File::XML ( BinaryEncoding => 'utf8' );
Lines 8589-8594 if ( CheckVersion($DBversion) ) { Link Here
8589	SetVersion ($DBversion);	8590	SetVersion ($DBversion);
8590	}	8591	}
8591		8592
		8593	$DBversion = "3.17.00.XXX";
		8594	if(CheckVersion($DBversion)) {
		8595	$dbh->do(q{
		8596	ALTER TABLE borrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0'
		8597	});
		8598	$dbh->do(q{
		8599	ALTER TABLE deletedborrowers ADD privacy_guarantor_checkouts BOOLEAN NOT NULL DEFAULT '0'
		8600	});
		8601	$dbh->do(q{
		8602	ALTER TABLE old_issues DROP PRIMARY KEY
		8603	});
		8604	$dbh->do(q{
		8605	ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
		8606	});
		8607	$dbh->do(q{
		8608	ALTER TABLE old_issues DROP INDEX itemnumber_idx
		8609	});
		8610	$dbh->do(q{
		8611	ALTER TABLE issues DROP PRIMARY KEY
		8612	});
		8613	$dbh->do(q{
		8614	ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
		8615	});
		8616	$dbh->do(q{
		8617	ALTER TABLE issues DROP INDEX itemnumber_idx
		8618	});
		8619	$dbh->do(q{
		8620	ALTER TABLE issues ADD UNIQUE itemnumber_idx (itemnumber)
		8621	});
		8622	$dbh->do(qq{
		8623	UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC
		8624	});
		8625
		8626	my $schema = Koha::Database->new()->schema();
		8627	my $max_issue_id = $schema->resultset('Issue')->get_column('issue_id')->max();
		8628	$max_issue_id \|\|= $schema->resultset('OldIssue')->get_column('issue_id')->max();
		8629	$max_issue_id \|\|= 0;
		8630	$max_issue_id++;
		8631	$dbh->do(qq{
		8632	ALTER TABLE issues AUTO_INCREMENT = $max_issue_id
		8633	});
		8634
		8635	$dbh->do(q{
		8636	INSERT INTO systempreferences (variable, value, options, explanation, type )
		8637	VALUES (
		8638	'AllowStaffToSetCheckoutsVisibilityForGuarantor', '0', NULL,
		8639	'If enabled, library staff can set a patron''s checkouts to be visible to linked patrons from the opac.', 'YesNo'
		8640	)
		8641	});
		8642
		8643	$dbh->do(q{
		8644	INSERT INTO systempreferences (variable, value, options, explanation, type )
		8645	VALUES (
		8646	'AllowPatronToSetCheckoutsVisibilityForGuarantor', '0', NULL,
		8647	'If enabled, the patron can set checkouts to be visible to his or her guarantor', 'YesNo'
		8648	)
		8649	});
		8650
		8651	print "Upgrade to $DBversion done (Bug 9303 - relative's checkouts in the opac)\n";
		8652	SetVersion($DBversion);
		8653	}
		8654
8592	=head1 FUNCTIONS	8655	=head1 FUNCTIONS
8593		8656
8594	=head2 TableExists($table)	8657	=head2 TableExists($table)
8595	-

Lines 265-270 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here

(-)a/installer/data/mysql/kohastructure.sql (+6 lines)
265	`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower	265	`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
266	`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)	266	`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
267	`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history	267	`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
		268	`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
268	UNIQUE KEY `cardnumber` (`cardnumber`),	269	UNIQUE KEY `cardnumber` (`cardnumber`),
269	PRIMARY KEY `borrowernumber` (`borrowernumber`),	270	PRIMARY KEY `borrowernumber` (`borrowernumber`),
270	KEY `categorycode` (`categorycode`),	271	KEY `categorycode` (`categorycode`),
Lines 868-873 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower Link Here
868	`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower	869	`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
869	`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)	870	`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
870	`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),	871	`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),
		872	`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
871	KEY borrowernumber (borrowernumber),	873	KEY borrowernumber (borrowernumber),
872	KEY `cardnumber` (`cardnumber`)	874	KEY `cardnumber` (`cardnumber`)
873	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	875	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Lines 1114-1119 CREATE TABLE `import_items` ( Link Here
1114		1116
1115	DROP TABLE IF EXISTS `issues`;	1117	DROP TABLE IF EXISTS `issues`;
1116	CREATE TABLE `issues` ( -- information related to check outs or issues	1118	CREATE TABLE `issues` ( -- information related to check outs or issues
		1119	`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
1117	`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to	1120	`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1118	`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out	1121	`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
1119	`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)	1122	`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
Lines 1125-1130 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1125	`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed	1128	`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
1126	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched	1129	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1127	`issuedate` datetime default NULL, -- date the item was checked out or issued	1130	`issuedate` datetime default NULL, -- date the item was checked out or issued
		1131	PRIMARY KEY (`issue_id`),
1128	KEY `issuesborridx` (`borrowernumber`),	1132	KEY `issuesborridx` (`borrowernumber`),
1129	KEY `itemnumber_idx` (`itemnumber`),	1133	KEY `itemnumber_idx` (`itemnumber`),
1130	KEY `branchcode_idx` (`branchcode`),	1134	KEY `branchcode_idx` (`branchcode`),
Lines 1585-1590 CREATE TABLE `oai_sets_biblios` ( Link Here
1585		1589
1586	DROP TABLE IF EXISTS `old_issues`;	1590	DROP TABLE IF EXISTS `old_issues`;
1587	CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned	1591	CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
		1592	`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
1588	`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to	1593	`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1589	`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out	1594	`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
1590	`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)	1595	`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
Lines 1596-1601 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1596	`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed	1601	`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
1597	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched	1602	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1598	`issuedate` datetime default NULL, -- date the item was checked out or issued	1603	`issuedate` datetime default NULL, -- date the item was checked out or issued
		1604	PRIMARY KEY (`issue_id`),
1599	KEY `old_issuesborridx` (`borrowernumber`),	1605	KEY `old_issuesborridx` (`borrowernumber`),
1600	KEY `old_issuesitemidx` (`itemnumber`),	1606	KEY `old_issuesitemidx` (`itemnumber`),
1601	KEY `branchcode_idx` (`branchcode`),	1607	KEY `branchcode_idx` (`branchcode`),

Lines 25-35 INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` Link Here

(-)a/installer/data/mysql/sysprefs.sql (+2 lines)
25	('AllowNotForLoanOverride','0','','If ON, Koha will allow the librarian to loan a not for loan item.','YesNo'),	25	('AllowNotForLoanOverride','0','','If ON, Koha will allow the librarian to loan a not for loan item.','YesNo'),
26	('AllowOfflineCirculation','0','','If on, enables HTML5 offline circulation functionality.','YesNo'),	26	('AllowOfflineCirculation','0','','If on, enables HTML5 offline circulation functionality.','YesNo'),
27	('AllowOnShelfHolds','0','','Allow hold requests to be placed on items that are not on loan','YesNo'),	27	('AllowOnShelfHolds','0','','Allow hold requests to be placed on items that are not on loan','YesNo'),
		28	('AllowPatronToSetCheckoutsVisibilityForGuarantor', '0', NULL, 'If enabled, the patron can set checkouts to be visible to his or her guarantor', 'YesNo'),
28	('AllowPKIAuth','None','None\|Common Name\|emailAddress','Use the field from a client-side SSL certificate to look a user in the Koha database','Choice'),	29	('AllowPKIAuth','None','None\|Common Name\|emailAddress','Use the field from a client-side SSL certificate to look a user in the Koha database','Choice'),
29	('AllowPurchaseSuggestionBranchChoice','0','1','Allow user to choose branch when making a purchase suggestion','YesNo'),	30	('AllowPurchaseSuggestionBranchChoice','0','1','Allow user to choose branch when making a purchase suggestion','YesNo'),
30	('AllowRenewalLimitOverride','0',NULL,'if ON, allows renewal limits to be overridden on the circulation screen','YesNo'),	31	('AllowRenewalLimitOverride','0',NULL,'if ON, allows renewal limits to be overridden on the circulation screen','YesNo'),
31	('AllowReturnToBranch','anywhere','anywhere\|homebranch\|holdingbranch\|homeorholdingbranch','Where an item may be returned','Choice'),	32	('AllowReturnToBranch','anywhere','anywhere\|homebranch\|holdingbranch\|homeorholdingbranch','Where an item may be returned','Choice'),
32	('AllowSelfCheckReturns','0','','If enabled, patrons may return items through the Web-based Self Checkout','YesNo'),	33	('AllowSelfCheckReturns','0','','If enabled, patrons may return items through the Web-based Self Checkout','YesNo'),
		34	('AllowStaffToSetCheckoutsVisibilityForGuarantor','0',NULL,'If enabled, library staff can set a patron''s checkouts to be visible to linked patrons from the opac.', 'YesNo'),
33	('AllowTooManyOverride','1','','If on, allow staff to override and check out items when the patron has reached the maximum number of allowed checkouts','YesNo'),	35	('AllowTooManyOverride','1','','If on, allow staff to override and check out items when the patron has reached the maximum number of allowed checkouts','YesNo'),
34	('alphabet','A B C D E F G H I J K L M N O P Q R S T U V W X Y Z',NULL,'Alphabet than can be expanded into browse links, e.g. on Home > Patrons','free'),	36	('alphabet','A B C D E F G H I J K L M N O P Q R S T U V W X Y Z',NULL,'Alphabet than can be expanded into browse links, e.g. on Home > Patrons','free'),
35	('AlternateHoldingsField','',NULL,'The MARC field/subfield that contains alternate holdings information for bibs taht do not have items attached (e.g. 852abchi for libraries converting from MARC Magician).','free'),	37	('AlternateHoldingsField','',NULL,'The MARC field/subfield that contains alternate holdings information for bibs taht do not have items attached (e.g. 852abchi for libraries converting from MARC Magician).','free'),