Bugzilla – Attachment 853 Details for
Bug 2797
Add Summary records to represent summary holdings
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
patch showing some potential database changes
0001-Summary-Record-Database-update.patch (text/plain), 16.88 KB, created by
Chris Cormack
on 2008-11-17 07:24:00 UTC
(
hide
)
Description:
patch showing some potential database changes
Filename:
MIME Type:
Creator:
Chris Cormack
Created:
2008-11-17 07:24:00 UTC
Size:
16.88 KB
patch
obsolete
>From a42d250a103e99017c4990ebbc87b8bb5a299111 Mon Sep 17 00:00:00 2001 >From: Andrew Moore <andrew.moore@liblime.com> >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 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 2797
: 853