From a42d250a103e99017c4990ebbc87b8bb5a299111 Mon Sep 17 00:00:00 2001 From: Andrew Moore Date: Thu, 30 Oct 2008 12:22:26 -0500 Subject: [PATCH 1/8] Summary Record: Database update updates to kohastructure.sql to add several tables to hold summary records and to hold summary record information during batch importing updates to updatedatabase.pl to add these new tables and constraints during upgrade new additions to marc21_framework_DEFAULT.sql to indicate where some summary information is held in MARC --- .../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(-) diff --git a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql b/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql index a5e7d77..b6fb0d8 100644 --- a/installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.sql +++ b/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), diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 09aaf97..57ccfc6 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/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; + diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 03e13c3..7144615 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/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 -- 1.5.6