@@ -, +, @@ --- .../marc21/mandatory/marc21_framework_DEFAULT.sql | 4 +- installer/data/mysql/kohastructure.sql | 100 ++++++++++++++- installer/data/mysql/updatedatabase.pl | 139 ++++++++++++++++++++ 3 files changed, 240 insertions(+), 3 deletions(-) --- a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql +++ a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql @@ -2829,12 +2829,12 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('852', '6', 'Linkage', 'Linkage', 0, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', '8', 'Sequence number', 'Sequence number', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'a', 'Location', 'Location', 0, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), - ('852', 'b', 'Sublocation or collection', 'Sublocation or collection', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), + ('852', 'b', 'Sublocation or collection', 'Sublocation or collection', 1, 0, 'summaries.homebranch', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'c', 'Shelving location', 'Shelving location', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'e', 'Address', 'Address', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'f', 'Coded location qualifier', 'Coded location qualifier', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'g', 'Non-coded location qualifier', 'Non-coded location qualifier', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), - ('852', 'h', 'Classification part', 'Classification part', 0, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), + ('852', 'h', 'Classification part', 'Classification part', 0, 0, 'summaries.callnumber', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'i', 'Item part', 'Item part', 1, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'j', 'Shelving control number', 'Shelving control number', 0, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), ('852', 'k', 'Call number prefix', 'Call number prefix', 0, 0, '', 8, '', '', '', NULL, 5, '', '', '', NULL), --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1158,15 +1158,18 @@ CREATE TABLE `items` ( `more_subfields_xml` longtext default NULL, `enumchron` varchar(80) default NULL, `copynumber` varchar(32) default NULL, + `summary_id` int(11) default NULL, PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), KEY `itembinoidx` (`biblioitemnumber`), KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), KEY `holdingbranch` (`holdingbranch`), + KEY `items_ibfk_4` (`summary_id`), CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, - CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE + CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, + CONSTRAINT `items_ibfk_4` FOREIGN KEY (`summary_id`) REFERENCES `summaries` (`summary_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2303,3 +2306,98 @@ CREATE TABLE `borrower_message_transport_preferences` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; +DROP TABLE IF EXISTS `summaries`; +CREATE TABLE `summaries` ( +`summary_id` INTEGER auto_increment , +`biblionumber` int(11) DEFAULT NULL, +`homebranch` VARCHAR(10) default NULL, +`holdingbranch` VARCHAR(10) default NULL, +`callnumber` char (30) default '', +`shelvinglocation` int (11) default NULL, +`call_number_source` char (10) default '', +`collection_code` int (11) default NULL, +`URI` char (255) default '', +`itemtype` varchar(10) default NULL, +`copy_number` INTEGER default '0', +`last_modified_by` INTEGER default '0', +`last_modified_timestamp` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +`created_by` INTEGER default '0', +`created_timestamp` TIMESTAMP, +PRIMARY KEY (`summary_id`), +CONSTRAINT `summaries_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_4` FOREIGN KEY (`shelvinglocation`) REFERENCES `authorised_values` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_5` FOREIGN KEY (`call_number_source`) REFERENCES `class_sources` (`cn_source`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_6` FOREIGN KEY (`collection_code`) REFERENCES `authorised_values` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_7` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_8` FOREIGN KEY (`last_modified_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_9` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `unstructured_summary_holdings_statements`; +CREATE TABLE `unstructured_summary_holdings_statements` ( +`unstructured_summary_holdings_statement_id` INTEGER auto_increment , +`summary_id` INTEGER default '0', +`sequence_number` INTEGER NOT NULL default '0', +`public_note` char (100) NOT NULL default '', +`staff_note` char (100) NOT NULL default '', +`statement` MEDIUMTEXT NOT NULL default '', +`display_template` MEDIUMTEXT NOT NULL default '', +PRIMARY KEY (`unstructured_summary_holdings_statement_id`), +CONSTRAINT `unstructured_summary_holdings_statements_ibfk_1` FOREIGN KEY (`summary_id`) REFERENCES `summaries` (`summary_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `structured_summary_holdings_statements`; +CREATE TABLE `structured_summary_holdings_statements` ( +`structured_summary_holdings_statement_id` INTEGER auto_increment , +`summary_id` INTEGER default '0', +`sequence_number` INTEGER NOT NULL default '0', +`public_note` char (100) NOT NULL default '', +`staff_note` char (100) NOT NULL default '', +`display_template` MEDIUMTEXT NOT NULL default '', +PRIMARY KEY (`structured_summary_holdings_statement_id`), +CONSTRAINT `structured_summary_holdings_statements` FOREIGN KEY (`summary_id`) REFERENCES `summaries` (`summary_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +DROP TABLE IF EXISTS `structured_summary_holding_statement_levels`; +CREATE TABLE `structured_summary_holding_statement_levels` ( +`id` INTEGER NOT NULL auto_increment , +`structured_summary_holdings_statement_id` INTEGER default '0', +`level` INTEGER NOT NULL default '0', +`beginning_label` char (32) NOT NULL default '', +`beginning_value` char (32) NOT NULL default '', +`ending_label` char (32) NOT NULL default '', +`ending_value` char (32) NOT NULL default '', +PRIMARY KEY (`id`), +CONSTRAINT `structured_summary_holding_statement_levels_ibfk_1` FOREIGN KEY (`structured_summary_holdings_statement_id`) REFERENCES `structured_summary_holdings_statements` (`structured_summary_holdings_statement_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `summary_record_templates`; +CREATE TABLE `summary_record_templates` ( +`id` INTEGER auto_increment , +`homebranch` varchar(10) NOT NULL default '', +`holdingbranch` varchar(10) NOT NULL default '', +`itemtype` varchar(10) NOT NULL default '', +`shelvinglocation` char (80) NOT NULL default '', +`call_number_source` char (10) NOT NULL default '', +`collection_code` char (10) NOT NULL default '', +`URI` char (255) NOT NULL default '', +PRIMARY KEY (`id`), +CONSTRAINT `summary_record_templates_ibfk_1` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summary_record_templates_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summary_record_templates_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `import_summaries`; +CREATE TABLE `import_summaries` ( + `import_record_id` int(11) NOT NULL, + `control_number` varchar(25) default NULL, + `biblio_control_number` varchar(25) default NULL, + `original_source` varchar(25) default NULL, + KEY `import_record_ibfk_1` (`import_record_id`), + KEY `biblio_control_number` (`biblio_control_number`), + CONSTRAINT `import_summaries_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -2040,6 +2040,145 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = '3.01.00.004'; +if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) { + + $dbh->do(<<'END_SQL'); +CREATE TABLE `summaries` ( +`summary_id` INTEGER auto_increment , +`biblionumber` int(11) DEFAULT NULL, +`homebranch` VARCHAR(10) default NULL, +`holdingbranch` VARCHAR(10) default NULL, +`callnumber` char (30) default '', +`shelvinglocation` int (11) default NULL, +`call_number_source` char (10) default '', +`collection_code` int (11) default NULL, +`URI` char (255) default '', +`itemtype` varchar(10) default NULL, +`copy_number` INTEGER default '0', +`last_modified_by` INTEGER default '0', +`last_modified_timestamp` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +`created_by` INTEGER default '0', +`created_timestamp` TIMESTAMP, +PRIMARY KEY (`summary_id`), +CONSTRAINT `summaries_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_4` FOREIGN KEY (`shelvinglocation`) REFERENCES `authorised_values` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_5` FOREIGN KEY (`call_number_source`) REFERENCES `class_sources` (`cn_source`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_6` FOREIGN KEY (`collection_code`) REFERENCES `authorised_values` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_7` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_8` FOREIGN KEY (`last_modified_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summaries_ibfk_9` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `unstructured_summary_holdings_statements` ( +`unstructured_summary_holdings_statement_id` INTEGER auto_increment , +`summary_id` INTEGER default '0', +`sequence_number` INTEGER NOT NULL default '0', +`public_note` char (100) NOT NULL default '', +`staff_note` char (100) NOT NULL default '', +`statement` MEDIUMTEXT NOT NULL default '', +`display_template` MEDIUMTEXT NOT NULL default '', +PRIMARY KEY (`unstructured_summary_holdings_statement_id`), +CONSTRAINT `unstructured_summary_holdings_statements_ibfk_1` FOREIGN KEY (`summary_id`) REFERENCES `summaries` (`summary_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `structured_summary_holdings_statements` ( +`structured_summary_holdings_statement_id` INTEGER auto_increment , +`summary_id` INTEGER default '0', +`sequence_number` INTEGER NOT NULL default '0', +`public_note` char (100) NOT NULL default '', +`staff_note` char (100) NOT NULL default '', +`display_template` MEDIUMTEXT NOT NULL default '', +PRIMARY KEY (`structured_summary_holdings_statement_id`), +CONSTRAINT `structured_summary_holdings_statements` FOREIGN KEY (`summary_id`) REFERENCES `summaries` (`summary_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `structured_summary_holding_statement_levels` ( +`id` INTEGER NOT NULL auto_increment , +`structured_summary_holdings_statement_id` INTEGER default '0', +`level` INTEGER NOT NULL default '0', +`beginning_label` char (32) NOT NULL default '', +`beginning_value` char (32) NOT NULL default '', +`ending_label` char (32) NOT NULL default '', +`ending_value` char (32) NOT NULL default '', +PRIMARY KEY (`id`), +CONSTRAINT `structured_summary_holding_statement_levels_ibfk_1` FOREIGN KEY (`structured_summary_holdings_statement_id`) REFERENCES `structured_summary_holdings_statements` (`structured_summary_holdings_statement_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `summary_record_templates` ( +`id` INTEGER auto_increment , +`homebranch` varchar(10) NOT NULL default '', +`holdingbranch` varchar(10) NOT NULL default '', +`itemtype` varchar(10) NOT NULL default '', +`shelvinglocation` char (80) NOT NULL default '', +`call_number_source` char (10) NOT NULL default '', +`collection_code` char (10) NOT NULL default '', +`URI` char (255) NOT NULL default '', +PRIMARY KEY (`id`), +CONSTRAINT `summary_record_templates_ibfk_1` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summary_record_templates_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `summary_record_templates_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `import_summaries` ( + `import_record_id` int(11) NOT NULL, + `control_number` varchar(25) default NULL, + `biblio_control_number` varchar(25) default NULL, + `original_source` varchar(25) default NULL, + KEY `import_record_ibfk_1` (`import_record_id`), + KEY `biblio_control_number` (`biblio_control_number`), + CONSTRAINT `import_summaries_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + + $dbh->do(<<'END_SQL'); +alter table items + add column summary_id int +END_SQL + + $dbh->do(<<'END_SQL'); +alter table items + add constraint `items_ibfk_4` + foreign key (`summary_id`) + references `summaries` (`summary_id`) +END_SQL + + $dbh->do(<<'END_SQL'); +UPDATE marc_subfield_structure + SET kohafield = 'summaries.homebranch' + WHERE tagfield = '852' + AND tagsubfield = 'b' +END_SQL + + $dbh->do(<<'END_SQL'); +UPDATE marc_subfield_structure + SET kohafield = 'summaries.callnumber' + WHERE tagfield = '852' + AND tagsubfield = 'h' +END_SQL + + $dbh->do(<<'END_SQL'); +ALTER TABLE import_batches + ADD COLUMN `num_summaries` int(11) NOT NULL default '0' +END_SQL + + print "Upgrade to $DBversion done (DDL for summary records)\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table --