From 5ef508bddbd32d7ec39971d7f2178f3e024ad475 Mon Sep 17 00:00:00 2001 From: John Beppu Date: Tue, 3 Feb 2009 16:02:06 -0600 Subject: [PATCH] Revised Schema - removed 'rss' transport - made the messages non-digest - updated the default text of the templates in the letters table - [table] item_circulation_alert_preferences - added notification column - removed is_enabled column - [table] message_queue - added metadata column - added letter_code column Signed-off-by: Daniel Sweeney Signed-off-by: Galen Charlton --- .../en/mandatory/message_transport_types.sql | 3 +-- .../data/mysql/en/optional/sample_notices.sql | 4 ++-- .../sample_notices_message_transports.sql | 10 ++++---- .../1-Obligatoire/message_transport_types.sql | 2 +- .../mysql/fr-FR/2-Optionel/sample_notices.sql | 4 ++-- .../sample_notices_message_transports.sql | 10 ++++---- installer/data/mysql/kohastructure.sql | 8 ++++--- installer/data/mysql/updatedatabase.pl | 24 +++++++++---------- 8 files changed, 31 insertions(+), 34 deletions(-) diff --git a/installer/data/mysql/en/mandatory/message_transport_types.sql b/installer/data/mysql/en/mandatory/message_transport_types.sql index e8c0027354..517f5cc96c 100644 --- a/installer/data/mysql/en/mandatory/message_transport_types.sql +++ b/installer/data/mysql/en/mandatory/message_transport_types.sql @@ -2,5 +2,4 @@ INSERT INTO message_transport_types (message_transport_type) values ('email'), -('sms') -('feed'); +('sms'); diff --git a/installer/data/mysql/en/optional/sample_notices.sql b/installer/data/mysql/en/optional/sample_notices.sql index ad414b080d..52e7ecaa8e 100644 --- a/installer/data/mysql/en/optional/sample_notices.sql +++ b/installer/data/mysql/en/optional/sample_notices.sql @@ -11,5 +11,5 @@ VALUES ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <> items due soon'), ('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <> <>,\r\n\r\nThis is a reminder of an upcoming library event in which you have expressed interest.'), ('reserves', 'HOLD', 'Hold Available for Pickup', 'Hold Available for Pickup at <>', 'Dear <> <>,\r\n\r\nYou have a hold available for pickup as of <>:\r\n\r\nTitle: <>\r\nAuthor: <>\r\nCopy: <>\r\nLocation: <>\r\n<>\r\n<>\r\n<>'), -('circulation','CHECKINDGST','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n<>'), -('circulation','CHECKOUTDGST','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n<>'); +('circulation','CHECKIN','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n----\r\n<>\r\n----\r\nThank you.'), +('circulation','CHECKOUT','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n----\r\n<>\r\n----\r\nThank you for visiting <>.'); diff --git a/installer/data/mysql/en/optional/sample_notices_message_transports.sql b/installer/data/mysql/en/optional/sample_notices_message_transports.sql index b9d9f90439..c2d5d93117 100644 --- a/installer/data/mysql/en/optional/sample_notices_message_transports.sql +++ b/installer/data/mysql/en/optional/sample_notices_message_transports.sql @@ -13,9 +13,7 @@ values (2, 'sms', 1, 'circulation', 'PREDUEDGST'), (3, 'sms', 0, 'circulation', 'EVENT'), (4, 'sms', 0, 'reserves', 'HOLD'), -(5, 'email', 1, 'circulation', 'CHECKINDGST'), -(5, 'sms', 1, 'circulation', 'CHECKINDGST'), -(5, 'feed', 1, 'circulation', 'CHECKINDGST'), -(6, 'email', 1, 'circulation', 'CHECKOUTDGST'), -(6, 'sms', 1, 'circulation', 'CHECKOUTDGST'), -(6, 'feed', 1, 'circulation', 'CHECKOUTDGST'); +(5, 'email', 0, 'circulation', 'CHECKIN'), +(5, 'sms', 0, 'circulation', 'CHECKIN'), +(6, 'email', 0, 'circulation', 'CHECKOUT'), +(6, 'sms', 0, 'circulation', 'CHECKOUT'), diff --git a/installer/data/mysql/fr-FR/1-Obligatoire/message_transport_types.sql b/installer/data/mysql/fr-FR/1-Obligatoire/message_transport_types.sql index e8c0027354..0c61bb8dea 100644 --- a/installer/data/mysql/fr-FR/1-Obligatoire/message_transport_types.sql +++ b/installer/data/mysql/fr-FR/1-Obligatoire/message_transport_types.sql @@ -2,5 +2,5 @@ INSERT INTO message_transport_types (message_transport_type) values ('email'), -('sms') +('sms'), ('feed'); diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_notices.sql b/installer/data/mysql/fr-FR/2-Optionel/sample_notices.sql index ad414b080d..52e7ecaa8e 100644 --- a/installer/data/mysql/fr-FR/2-Optionel/sample_notices.sql +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_notices.sql @@ -11,5 +11,5 @@ VALUES ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <> items due soon'), ('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <> <>,\r\n\r\nThis is a reminder of an upcoming library event in which you have expressed interest.'), ('reserves', 'HOLD', 'Hold Available for Pickup', 'Hold Available for Pickup at <>', 'Dear <> <>,\r\n\r\nYou have a hold available for pickup as of <>:\r\n\r\nTitle: <>\r\nAuthor: <>\r\nCopy: <>\r\nLocation: <>\r\n<>\r\n<>\r\n<>'), -('circulation','CHECKINDGST','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n<>'), -('circulation','CHECKOUTDGST','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n<>'); +('circulation','CHECKIN','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n----\r\n<>\r\n----\r\nThank you.'), +('circulation','CHECKOUT','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n----\r\n<>\r\n----\r\nThank you for visiting <>.'); diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_notices_message_transports.sql b/installer/data/mysql/fr-FR/2-Optionel/sample_notices_message_transports.sql index b9d9f90439..c2d5d93117 100644 --- a/installer/data/mysql/fr-FR/2-Optionel/sample_notices_message_transports.sql +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_notices_message_transports.sql @@ -13,9 +13,7 @@ values (2, 'sms', 1, 'circulation', 'PREDUEDGST'), (3, 'sms', 0, 'circulation', 'EVENT'), (4, 'sms', 0, 'reserves', 'HOLD'), -(5, 'email', 1, 'circulation', 'CHECKINDGST'), -(5, 'sms', 1, 'circulation', 'CHECKINDGST'), -(5, 'feed', 1, 'circulation', 'CHECKINDGST'), -(6, 'email', 1, 'circulation', 'CHECKOUTDGST'), -(6, 'sms', 1, 'circulation', 'CHECKOUTDGST'), -(6, 'feed', 1, 'circulation', 'CHECKOUTDGST'); +(5, 'email', 0, 'circulation', 'CHECKIN'), +(5, 'sms', 0, 'circulation', 'CHECKIN'), +(6, 'email', 0, 'circulation', 'CHECKOUT'), +(6, 'sms', 0, 'circulation', 'CHECKOUT'), diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 1678aaf006..2d4b41567a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2239,6 +2239,8 @@ CREATE TABLE `message_queue` ( `borrowernumber` int(11) default NULL, `subject` text, `content` text, + `metadata` text DEFAULT NULL, + `letter_code` varchar(64) DEFAULT NULL, `message_transport_type` varchar(20) NOT NULL, `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending', `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, @@ -2349,10 +2351,10 @@ CREATE TABLE `item_circulation_alert_preferences` ( `branchcode` varchar(10) NOT NULL, `categorycode` varchar(10) NOT NULL, `item_type` varchar(10) NOT NULL, - `is_enabled` tinyint(1) NOT NULL default '0', + `notification` varchar(16) NOT NULL, PRIMARY KEY (`id`), - KEY `branchcode` (`branchcode`,`categorycode`,`item_type`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 + KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 87c7283885..31971d8faa 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -2130,31 +2130,31 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { branchcode varchar(10) NOT NULL, categorycode varchar(10) NOT NULL, item_type varchar(10) NOT NULL, - is_enabled tinyint(1) NOT NULL DEFAULT 0, + notification varchar(16) NOT NULL, PRIMARY KEY (id), - KEY (branchcode, categorycode, item_type) + KEY (branchcode, categorycode, item_type, notification) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; "); + + $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; }); + $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; }); + $dbh->do(q{ INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES - ('circulation','CHECKINDGST','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n<>'); + ('circulation','CHECKIN','Item Check-in','Check-ins','The following items have been checked in:\r\n----\r\n<>\r\n----\r\nThank you.'); }); $dbh->do(q{ INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES - ('circulation','CHECKOUTDGST','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n<>'); + ('circulation','CHECKOUT','Item Checkout','Checkouts','The following items have been checked out:\r\n----\r\n<>\r\n----\r\nThank you for visiting <>.'); }); $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);}); $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);}); - $dbh->do(q{INSERT INTO message_transport_types (message_transport_type) VALUES ('feed');}); - - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'email', 1, 'circulation', 'CHECKINDGST');}); - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'sms', 1, 'circulation', 'CHECKINDGST');}); - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'feed', 1, 'circulation', 'CHECKINDGST');}); - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'email', 1, 'circulation', 'CHECKOUTDGST');}); - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'sms', 1, 'circulation', 'CHECKOUTDGST');}); - $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'feed', 1, 'circulation', 'CHECKOUTDGST');}); + $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'email', 0, 'circulation', 'CHECKIN');}); + $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'sms', 0, 'circulation', 'CHECKIN');}); + $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'email', 0, 'circulation', 'CHECKOUT');}); + $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'sms', 0, 'circulation', 'CHECKOUT');}); print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n"; SetVersion ($DBversion); -- 2.39.5