Bugzilla – Attachment 32401 Details for
Bug 13023
Nonpublic note for items should be mapped to a database column like itemnotes
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 13023 - Nonpublic note for items should be mapped to a database column like itemnotes
Bug-13023---Nonpublic-note-for-items-should-be-map.patch (text/plain), 19.87 KB, created by
Kyle M Hall (khall)
on 2014-10-16 11:33:10 UTC
(
hide
)
Description:
Bug 13023 - Nonpublic note for items should be mapped to a database column like itemnotes
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2014-10-16 11:33:10 UTC
Size:
19.87 KB
patch
obsolete
>From f7b4bebe6e87584f0006438eab8b19f470a9a8d0 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >Date: Thu, 2 Oct 2014 10:11:32 -0400 >Subject: [PATCH] Bug 13023 - Nonpublic note for items should be mapped to a database column like itemnotes > >An item's public notes are mapped to a db column, but the librarian only >notes are not. We need to map these so in the future we may display them >in places other than the editor. > >Test Plan: >1) Create an item with a non-public note >2) Apply this patch >3) Run updatedatabase >4) Update your frameworks to map your nonpublic item note to items.itemnotes_nonpublic >5) Run misc/migration_tools/copy_item_xml_field_to_db.pl --subfield x --column itemnotes_nonpublic -c -v >6) Inspect the database, note that your non-public note is now in > items.itemnotes_nonpublic >--- > installer/data/mysql/kohastructure.sql | 2 + > installer/data/mysql/updatedatabase.pl | 74 +++++++++++--------- > misc/migration_tools/copy_item_xml_field_to_db.pl | 58 ++++++++++++++++ > 3 files changed, 101 insertions(+), 33 deletions(-) > create mode 100755 misc/migration_tools/copy_item_xml_field_to_db.pl > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index aeeba17..5db930e 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -904,6 +904,7 @@ CREATE TABLE `deleteditems` ( > `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved > `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5) > `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x) >+ `itemnotes_nonpublic` mediumtext default NULL, > `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b) > `paidfor` mediumtext, > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered >@@ -1202,6 +1203,7 @@ CREATE TABLE `items` ( -- holdings/item information > `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved > `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5) > `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x) >+ `itemnotes_nonpublic` mediumtext default NULL, > `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b) > `paidfor` mediumtext, > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index d6b7d24..49ba837 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -2741,7 +2741,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $DBversion = '3.01.00.066'; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;'); >- >+ > my $maxreserves = C4::Context->preference('maxreserves'); > $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;'); > $sth->execute($maxreserves); >@@ -2948,7 +2948,7 @@ $$maxbudgetid[0] = 0 if !$$maxbudgetid[0]; > $dbh->do(<<BUDGETAUTOINCREMENT); > ALTER TABLE aqbudget AUTO_INCREMENT=$$maxbudgetid[0] > BUDGETAUTOINCREMENT >- >+ > $dbh->do(<<BUDGETNAME); > ALTER TABLE aqbudget RENAME `aqbudgets` > BUDGETNAME >@@ -3326,10 +3326,10 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $DBversion = "3.01.00.101"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do( >- "INSERT INTO systempreferences >+ "INSERT INTO systempreferences > (variable, value, options, explanation, type) > VALUES ( >- 'OverdueNoticeBcc', '', '', >+ 'OverdueNoticeBcc', '', '', > 'Email address to Bcc outgoing notices sent by email', > 'free') > "); >@@ -3364,7 +3364,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > if ($borrnotes_count == 0) { > $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')"); > } >- >+ > $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')"); > $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')"); > >@@ -3384,7 +3384,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > PRIMARY KEY (`colId`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > "); >- >+ > $dbh->do(" > CREATE TABLE `collections_tracking` ( > `ctId` int(11) NOT NULL auto_increment, >@@ -3394,7 +3394,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > "); > $dbh->do(" >- INSERT INTO permissions (module_bit, code, description) >+ INSERT INTO permissions (module_bit, code, description) > VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" ); > print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotating collections functionality)\n"; > SetVersion ($DBversion); >@@ -3419,7 +3419,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do(qq{ > ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` , > ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` , >- ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator` >+ ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator` > }); > print "Upgrade to $DBversion done (added separators for csv export)\n"; > SetVersion ($DBversion); >@@ -3592,7 +3592,7 @@ $DBversion = "3.01.00.126"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI','0','Enable ILS-DI services. See http://your.opac.name/cgi-bin/koha/ilsdi.pl for online documentation.','','YesNo')"); > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI:AuthorizedIPs','127.0.0.1','A comma separated list of IP addresses authorized to access the web services.','','free')"); >- >+ > print "Upgrade to $DBversion done (Adding ILS-DI updates and ILS-DI:AuthorizedIPs)\n"; > SetVersion ($DBversion); > } >@@ -3819,21 +3819,21 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original > $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;"); > $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;"); > $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;"); >- $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist >+ $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist > SELECT s1.routingid FROM subscriptionroutinglist s1 > WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2 > WHERE s2.borrowernumber = s1.borrowernumber >- AND s2.subscriptionid = s1.subscriptionid >+ AND s2.subscriptionid = s1.subscriptionid > AND s2.routingid < s1.routingid);"); > $dbh->do("DELETE FROM subscriptionroutinglist > WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);"); > $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);"); >- $dbh->do("ALTER TABLE subscriptionroutinglist >- ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) >+ $dbh->do("ALTER TABLE subscriptionroutinglist >+ ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) > REFERENCES `borrowers` (`borrowernumber`) > ON DELETE CASCADE ON UPDATE CASCADE"); >- $dbh->do("ALTER TABLE subscriptionroutinglist >- ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) >+ $dbh->do("ALTER TABLE subscriptionroutinglist >+ ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) > REFERENCES `subscription` (`subscriptionid`) > ON DELETE CASCADE ON UPDATE CASCADE"); > print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n"; >@@ -4102,7 +4102,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original > SetVersion ($DBversion); > } > >-$DBversion = '3.03.00.027'; >+$DBversion = '3.03.00.027'; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('displayFacetCount', '0', NULL, NULL, 'YesNo')"); > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('maxRecordsForFacets', '20', NULL, NULL, 'Integer')"); >@@ -4136,7 +4136,7 @@ $DBversion = "3.03.00.031"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('FineNotifyAtCheckin',0,'If ON notify librarians of overdue fines on the items they are checking in.',NULL,'YesNo');"); > print "Upgrade to $DBversion done (Add syspref FineNotifyAtCheckin)\n"; >- SetVersion ($DBversion); >+ SetVersion ($DBversion); > } > > $DBversion = '3.03.00.032'; >@@ -4205,7 +4205,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ShowReviewer',1,'If ON, name of reviewer will be shown above comments in OPAC',NULL,'YesNo');"); > print "Upgrade to $DBversion done (Add syspref ShowReviewer)\n"; > } >- >+ > $DBversion = "3.03.00.040"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseControlNumber',0,'If ON, record control number (w subfields) and control number (001) are used for linking of bibliographic records.','','YesNo');"); >@@ -4325,7 +4325,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("UPDATE message_attributes SET message_name = 'Advance_Notice' WHERE message_name='Advance Notice'"); > $dbh->do("UPDATE message_attributes SET message_name = 'Hold_Filled' WHERE message_name='Hold Filled'"); > $dbh->do("UPDATE message_attributes SET message_name = 'Item_Check_in' WHERE message_name='Item Check-in'"); >- $dbh->do("UPDATE message_attributes SET message_name = 'Item_Checkout' WHERE message_name='Item Checkout'"); >+ $dbh->do("UPDATE message_attributes SET message_name = 'Item_Checkout' WHERE message_name='Item Checkout'"); > SetVersion ($DBversion); > } > >@@ -4372,9 +4372,9 @@ $DBversion = "3.05.00.004"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ShowReviewerPhoto',1,'If ON, photo of reviewer will be shown beside comments in OPAC',NULL,'YesNo');"); > print "Upgrade to $DBversion done (Add syspref ShowReviewerPhoto)\n"; >- SetVersion($DBversion); >+ SetVersion($DBversion); > } >- >+ > $DBversion = "3.05.00.005"; > if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('BasketConfirmations', '1', 'When closing or reopening a basket,', 'always ask for confirmation.|do not ask for confirmation.', 'Choice');"); >@@ -4382,7 +4382,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > SetVersion($DBversion); > } > >-$DBversion = "3.05.00.006"; >+$DBversion = "3.05.00.006"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('MARCAuthorityControlField008', '|| aca||aabn | a|a d', NULL, NULL, 'Textarea')"); > print "Upgrade to $DBversion done (Add syspref MARCAuthorityControlField008)\n"; >@@ -4451,7 +4451,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > print "Upgrade to $DBversion done (add OPACResultsSidebar syspref (enh 6165))\n"; > SetVersion($DBversion); > } >- >+ > $DBversion = "3.05.00.012"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('RecordLocalUseOnReturn',0,'If ON, statistically record returns of unissued items as local use, instead of return',NULL,'YesNo')"); >@@ -4532,11 +4532,11 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $DBversion = "3.05.00.021"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("ALTER TABLE borrower_attribute_types ADD COLUMN display_checkout TINYINT(1) NOT NULL DEFAULT '0';"); >- print "Upgrade to $DBversion done (Added a display_checkout field in borrower_attribute_types table)\n"; >+ print "Upgrade to $DBversion done (Added a display_checkout field in borrower_attribute_types table)\n"; > SetVersion($DBversion); > } > >-$DBversion = "3.05.00.022"; >+$DBversion = "3.05.00.022"; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > $dbh->do("CREATE TABLE need_merge_authorities (id int NOT NULL auto_increment PRIMARY KEY, authid bigint NOT NULL, done tinyint DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8"); > print "Upgrade to $DBversion done (6094: Fixing ModAuthority problems, add a need_merge_authorities table)\n"; >@@ -5349,24 +5349,24 @@ $DBversion ="3.09.00.014"; > if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > # add phone message transport type > $dbh->do("INSERT INTO message_transport_types (message_transport_type) VALUES ('phone')"); >- >+ > # adds HOLD_PHONE and PREDUE_PHONE letters (as placeholders) > $dbh->do("INSERT INTO letter (module, code, name, title, content) VALUES > ('reserves', 'HOLD_PHONE', 'Item Available for Pick-up (phone notice)', 'Item Available for Pick-up (phone notice)', 'Your item is available for pickup'), > ('circulation', 'PREDUE_PHONE', 'Advance Notice of Item Due (phone notice)', 'Advance Notice of Item Due (phone notice)', 'Your item is due soon'), > ('circulation', 'OVERDUE_PHONE', 'Overdue Notice (phone notice)', 'Overdue Notice (phone notice)', 'Your item is overdue') > "); >- >+ > # add phone notifications to patron message preferences options > $dbh->do("INSERT INTO message_transports > (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES > (4, 'phone', 0, 'reserves', 'HOLD_PHONE'), > (2, 'phone', 0, 'circulation', 'PREDUE_PHONE') > "); >- >+ > # add TalkingTechItivaPhoneNotification syspref > $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('TalkingTechItivaPhoneNotification',0,'If ON, enables Talking Tech I-tiva phone notifications',NULL,'YesNo');"); >- >+ > print "Upgrade done (Support for Talking Tech i-tiva phone notification system)\n"; > SetVersion($DBversion); > } >@@ -5948,7 +5948,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > ADD COLUMN record_type enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio'"); > $dbh->do("UPDATE import_batches SET record_type='auth' WHERE import_batch_id IN > (SELECT import_batch_id FROM import_records WHERE record_type='auth')"); >- >+ > print "Upgrade to $DBversion done (Added support for staging authorities)\n"; > SetVersion ($DBversion); > } >@@ -7204,9 +7204,9 @@ if ( CheckVersion($DBversion) ) { > $DBversion = "3.13.00.029"; > if ( CheckVersion($DBversion) ) { > $dbh->do(q{ >- INSERT IGNORE INTO export_format( profile, description, content, csv_separator, type ) >+ INSERT IGNORE INTO export_format( profile, description, content, csv_separator, type ) > VALUES ( "issues to claim", "efault CSV export for serial issue claims", >- "SUPPLIER=aqbooksellers.name|TITLE=subscription.title|ISSUE NUMBER=serial.serialseq|LATE SINCE=serial.planneddate", >+ "SUPPLIER=aqbooksellers.name|TITLE=subscription.title|ISSUE NUMBER=serial.serialseq|LATE SINCE=serial.planneddate", > ",", "sql" ) > }); > print "Upgrade to $DBversion done (Bug 10854: Add the default CSV profile for claiming issues)\n"; >@@ -7317,7 +7317,7 @@ if ( CheckVersion($DBversion) ) { > SELECT DISTINCT(parent_ordernumber) > FROM aqorders > WHERE ordernumber != parent_ordernumber >- ) AS aq >+ ) AS aq > ) > AND basketno IN (SELECT basketno FROM aqbasket WHERE closedate IS NOT NULL) > }); >@@ -8801,6 +8801,14 @@ if ( CheckVersion($DBversion) ) { > } > > >+$DBversion = '3.17.00.XXX'; >+if (C4::Context->preference("Version") < TransformToNum($DBversion)) { >+ $dbh->do("ALTER TABLE items ADD COLUMN itemnotes_nonpublic MEDIUMTEXT AFTER itemnotes"); >+ $dbh->do("ALTER TABLE deleteditems ADD COLUMN itemnotes_nonpublic MEDIUMTEXT AFTER itemnotes"); >+ print "Upgrade to $DBversion done (Bug 4222 - Nonpublic note not appearing in the staff client) <b>Please check each of your frameworks to ensure your non-public item notes are mapped to items.itemnotes_nonpublic. After doing so please have your administrator run misc/maintenance/touch_all_items.pl </b>)\n"; >+ SetVersion($DBversion); >+} >+ > =head1 FUNCTIONS > > =head2 TableExists($table) >diff --git a/misc/migration_tools/copy_item_xml_field_to_db.pl b/misc/migration_tools/copy_item_xml_field_to_db.pl >new file mode 100755 >index 0000000..a5fe955 >--- /dev/null >+++ b/misc/migration_tools/copy_item_xml_field_to_db.pl >@@ -0,0 +1,58 @@ >+#!/usr/bin/perl >+ >+use Modern::Perl; >+ >+use Koha::Database; >+ >+BEGIN { >+ # find Koha's Perl modules >+ # test carefully before changing this >+ use FindBin; >+ eval { require "$FindBin::Bin/kohalib.pl" }; >+} >+ >+use Getopt::Long; >+use MARC::Record; >+use C4::Items; >+ >+my ( $help, $confirm, $test, $verbose, $field, $subfield, $db_column ); >+GetOptions( >+ 's|subfield=s' => \$subfield, >+ 'd|column|db_column=s' => \$db_column, >+ >+ 'c|confirm' => \$confirm, >+ 't|test' => \$test, >+ >+ 'v|verbose' => \$verbose, >+ 'h|help' => \$help, >+); >+ >+unless ( ( $confirm || $test ) && $subfield && $db_column ) { >+ say >+"This script will copy the value stored in an item's more_subfields_xml marc to >+the provided item table column. >+ >+ copy_item_xml_field_to_db.pl.pl --subfield x --column itemnotes_nonpublic [--confirm] [--test] >+"; >+ exit; >+} >+ >+my $items_rs = Koha::Database->new()->schema()->resultset("Item"); >+ >+while ( my $item = $items_rs->next() ) { >+ my $itemnumber = $item->get_column('itemnumber'); >+ my $biblionumber = $item->get_column('biblionumber'); >+ >+ my $marc = C4::Items::GetMarcItem( $biblionumber, $itemnumber ); >+ my $field = $marc->field('952'); >+ my $field_value = $field->subfield($subfield) || q{}; >+ >+ if ($test) { >+ say "Would insert '$field_value' into '$db_column' for item $itemnumber"; >+ } >+ elsif ($confirm) { >+ ModItem( { $db_column => $field_value }, $biblionumber, $itemnumber ); >+ say "Inserting '$field_value' into '$db_column' for item $itemnumber" if $verbose; >+ } >+} >+ >-- >1.7.2.5
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 13023
:
31979
|
31980
|
32400
|
32401
|
32513
|
32663
|
33501
|
34361
|
34366
|
34416
|
34417
|
34418
|
34427
|
34428
|
35611
|
38428