From d8654344fa806d4a8ce7d675ae396b4016877ec2 Mon Sep 17 00:00:00 2001 From: Andrew Moore Date: Fri, 20 Jun 2008 10:58:31 -0500 Subject: [PATCH] Bug 2176 (1/5): database update: adding message_queue table and tables to hold patron messaging preferences Updates to kohastructure.sql and updatedatabase.pl: - add new message_queue table - add message_transport_types table - add message_attributes table - add message_transports table - add borrower_message_preferences table - add borrower_message_transport_preferences table - adding EnhancedMessagingPreferenes to sysprefs SQL. Added column to borrowers table to hold SMS Alert Number. Added some more sample notices (letters) that will be sent for patron alerts added some sample SQL to configure messaging. Signed-off-by: Joshua Ferraro --- .../en/mandatory/message_transport_types.sql | 6 ++ .../en/mandatory/message_transport_types.txt | 2 + .../data/mysql/en/mandatory/sysprefs.sql | 1 + .../data/mysql/en/optional/sample_notices.sql | 11 ++- .../sample_notices_message_attributes.sql | 7 ++ .../sample_notices_message_attributes.txt | 2 + .../sample_notices_message_transports.sql | 19 ++++ .../sample_notices_message_transports.txt | 3 + installer/data/mysql/kohastructure.sql | 95 ++++++++++++++++++ installer/data/mysql/updatedatabase.pl | 98 ++++++++++++++++++- kohaversion.pl | 2 +- 11 files changed, 242 insertions(+), 4 deletions(-) create mode 100644 installer/data/mysql/en/mandatory/message_transport_types.sql create mode 100644 installer/data/mysql/en/mandatory/message_transport_types.txt create mode 100644 installer/data/mysql/en/optional/sample_notices_message_attributes.sql create mode 100644 installer/data/mysql/en/optional/sample_notices_message_attributes.txt create mode 100644 installer/data/mysql/en/optional/sample_notices_message_transports.sql create mode 100644 installer/data/mysql/en/optional/sample_notices_message_transports.txt diff --git a/installer/data/mysql/en/mandatory/message_transport_types.sql b/installer/data/mysql/en/mandatory/message_transport_types.sql new file mode 100644 index 0000000000..57d73e2112 --- /dev/null +++ b/installer/data/mysql/en/mandatory/message_transport_types.sql @@ -0,0 +1,6 @@ +INSERT INTO message_transport_types +(message_transport_type) +values +('email'), +('rss'), +('sms'); diff --git a/installer/data/mysql/en/mandatory/message_transport_types.txt b/installer/data/mysql/en/mandatory/message_transport_types.txt new file mode 100644 index 0000000000..49851e5f26 --- /dev/null +++ b/installer/data/mysql/en/mandatory/message_transport_types.txt @@ -0,0 +1,2 @@ +defines default message tranports for email, rss, and sms. + diff --git a/installer/data/mysql/en/mandatory/sysprefs.sql b/installer/data/mysql/en/mandatory/sysprefs.sql index 9282a7f850..5fb9c6a665 100755 --- a/installer/data/mysql/en/mandatory/sysprefs.sql +++ b/installer/data/mysql/en/mandatory/sysprefs.sql @@ -22,6 +22,7 @@ INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('checkdigit','none','If ON, enable checks on patron cardnumber: none or \"Katipo\" style checks','none|katipo','Choice'); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('delimiter',';','Define the default separator character for exporting reports',';|tabulation|,|/|\\|#|\|','Choice'); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('Disable_Dictionary',1,'If ON, disable Authority Searching and OPAC Dictionary','','YesNo'); +INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo'); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('expandedSearchOption',0,'If ON, set advanced search to be expanded by default',NULL,'YesNo'); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('FinesLog',1,'If ON, log fines',NULL,'YesNo'); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('hidelostitems',0,'If ON, disables display of\"lost\" items in OPAC.','','YesNo'); diff --git a/installer/data/mysql/en/optional/sample_notices.sql b/installer/data/mysql/en/optional/sample_notices.sql index 04da5e14d4..5928366ed7 100644 --- a/installer/data/mysql/en/optional/sample_notices.sql +++ b/installer/data/mysql/en/optional/sample_notices.sql @@ -1,5 +1,12 @@ -INSERT INTO `letter` VALUES +INSERT INTO `letter` +(module, code, name, title, content) +VALUES ('circulation','ODUE','Overdue Notice','Item Overdue','Dear <> <>,\r\n\r\nAccording to our current records, you have items that are overdue.Your library does not charge late fines, but please return orrenew them as soon as possible.\r\n\r\n<><><><><><><>If you have registered a password with the library, you may use it withyour library card number to renew online. If an item becomes more than 30 days overdue, you will be unable to use your library card until the item is returned. The following item is currently overdue:\r\n\r\n<> by <>'), ('claimacquisition','ACQCLAIM','Acquisition Claim','Item Not Received','<>\r\n<>\r\n<>\r\n<>\r\n<>\r\n<>\r\n\r\nOrdernumber <> (<>) (<> ordered) ($<> each) has not been received.'), ('serial','RLIST','Routing List','Serial is now available','<> <>,\r\n\r\nThe following issue is now available:\r\n\r\n<>\r\n\r\nPlease pick it up at your convenience.'), -('members','ACCTDETAILS','Account Details Template - DEFAULT','Your new Koha account details.','Hello <> <> <>.\r\n\r\nYour new Koha account details are:\r\n\r\nUser: <>\r\nPassword: <>\r\n\r\nIf you have any problems or questions regarding your account, please contact your Koha Administrator.\r\n\r\nThank you,\r\nKoha Administrator\r\nkohaadmin@yoursite.org'); +('members','ACCTDETAILS','Account Details Template - DEFAULT','Your new Koha account details.','Hello <> <> <>.\r\n\r\nYour new Koha account details are:\r\n\r\nUser: <>\r\nPassword: <>\r\n\r\nIf you have any problems or questions regarding your account, please contact your Koha Administrator.\r\n\r\nThank you,\r\nKoha Administrator\r\nkohaadmin@yoursite.org'), +('circulation','DUE','Item Due Reminder','Item Due Reminder','Dear <> <>,\r\n\r\nThe following item is now due:\r\n\r\n<> by <>'), +('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <> items due'), +('circulation','PREDUE','Advance Notice of Item Due','Advance Notice of Item Due','Dear <> <>,\r\n\r\nThe following item will be due soon:\r\n\r\n<> by <>'), +('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.'); diff --git a/installer/data/mysql/en/optional/sample_notices_message_attributes.sql b/installer/data/mysql/en/optional/sample_notices_message_attributes.sql new file mode 100644 index 0000000000..3d74c43696 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_notices_message_attributes.sql @@ -0,0 +1,7 @@ +insert into `message_attributes` +(`message_attribute_id`, message_name, `takes_days`) +values +(1, 'Item DUE', 0), +(2, 'Advance Notice', 1), +(3, 'Upcoming Events', 1); + diff --git a/installer/data/mysql/en/optional/sample_notices_message_attributes.txt b/installer/data/mysql/en/optional/sample_notices_message_attributes.txt new file mode 100644 index 0000000000..770bcac2b5 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_notices_message_attributes.txt @@ -0,0 +1,2 @@ +defines default messages for items due, advance notices, and upcoming library events. + diff --git a/installer/data/mysql/en/optional/sample_notices_message_transports.sql b/installer/data/mysql/en/optional/sample_notices_message_transports.sql new file mode 100644 index 0000000000..9c4d0ee0c9 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_notices_message_transports.sql @@ -0,0 +1,19 @@ +insert into `message_transports` +(`message_attribute_id`, `message_transport_type`, `is_digest`, `letter_module`, `letter_code`) +values +(1, 'email', 0, 'circulation', 'DUE'), +(1, 'email', 1, 'circulation', 'DUEDGST'), +(2, 'email', 0, 'circulation', 'PREDUE'), +(2, 'email', 1, 'circulation', 'PREDUEDGST'), +(3, 'email', 0, 'circulation', 'EVENT'), +(1, 'rss', 0, 'circulation', 'DUE'), +(1, 'rss', 1, 'circulation', 'DUEDGST'), +(2, 'rss', 0, 'circulation', 'PREDUE'), +(2, 'rss', 1, 'circulation', 'PREDUEDGST'), +(3, 'rss', 0, 'circulation', 'EVENT'), +(1, 'sms', 0, 'circulation', 'DUE'), +(1, 'sms', 1, 'circulation', 'DUEDGST'), +(2, 'sms', 0, 'circulation', 'PREDUE'), +(2, 'sms', 1, 'circulation', 'PREDUEDGST'), +(3, 'sms', 0, 'circulation', 'EVENT'); + diff --git a/installer/data/mysql/en/optional/sample_notices_message_transports.txt b/installer/data/mysql/en/optional/sample_notices_message_transports.txt new file mode 100644 index 0000000000..5b80ed3005 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_notices_message_transports.txt @@ -0,0 +1,3 @@ +Defines default message transports for sending item due messages, +advance notice messages, and library event messages through email, +rss, and SMS. diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 1d973a32f9..4785667dd7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -486,6 +486,7 @@ CREATE TABLE `borrowers` ( `altcontactaddress3` varchar(255) default NULL, `altcontactzipcode` varchar(50) default NULL, `altcontactphone` varchar(50) default NULL, + `smsalertnumber` varchar(50) default NULL, UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -2171,6 +2172,100 @@ CREATE TABLE `tmp_holdsqueue` ( `notes` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `message_queue` +-- + +DROP TABLE if EXISTS `message_queue`; +CREATE TABLE `message_queue` ( + `message_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `subject` text, + `content` text, + `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, + KEY `message_id` (`message_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transport_types` +-- + +DROP TABLE IF EXISTS `message_transport_types`; +CREATE TABLE `message_transport_types` ( + `message_transport_type` varchar(20) NOT NULL, + PRIMARY KEY (`message_transport_type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_attributes` +-- + +DROP TABLE IF EXISTS `message_attributes`; +CREATE TABLE `message_attributes` ( + `message_attribute_id` int(11) NOT NULL auto_increment, + `message_name` varchar(20) NOT NULL default '', + `takes_days` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`message_attribute_id`), + UNIQUE KEY `message_name` (`message_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transports` +-- + +DROP TABLE IF EXISTS `message_transports`; +CREATE TABLE `message_transports` ( + `message_attribute_id` int(11) NOT NULL, + `message_transport_type` varchar(20) NOT NULL, + `is_digest` tinyint(1) NOT NULL default '0', + `letter_module` varchar(20) NOT NULL default '', + `letter_code` varchar(20) NOT NULL default '', + PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`), + KEY `message_transport_type` (`message_transport_type`), + KEY `letter_module` (`letter_module`,`letter_code`), + CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_preferences` +-- + +DROP TABLE IF EXISTS `borrower_message_preferences`; +CREATE TABLE `borrower_message_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL default '0', + `message_attribute_id` int(11) default '0', + `days_in_advance` int(11) default '0', + `wants_digest` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `message_attribute_id` (`message_attribute_id`), + CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_transport_preferences` +-- + +DROP TABLE IF EXISTS `borrower_message_transport_preferences`; +CREATE TABLE `borrower_message_transport_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL default '0', + `message_transport_type` varchar(20) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 8297a757ae..ae591056ea 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -12,9 +12,10 @@ # NOTE: If you do something more than once in here, make it table driven. -# NOTE: Please keep the version in C4/Context.pm up-to-date! +# NOTE: Please keep the version in kohaversion.pl up-to-date! use strict; +# use warnings; # CPAN modules use DBI; @@ -1671,6 +1672,101 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } + +$DBversion = "3.00.00.091"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(<<'END_SQL'); +CREATE TABLE `message_queue` ( + `message_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `subject` text, + `content` text, + `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, + KEY `message_id` (`message_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +END_SQL + + $dbh->do(<<'END_SQL'); +ALTER TABLE borrowers +ADD `smsalertnumber` varchar(50) default NULL +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `message_transport_types` ( + `message_transport_type` varchar(20) NOT NULL, + PRIMARY KEY (`message_transport_type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `message_attributes` ( + `message_attribute_id` int(11) NOT NULL auto_increment, + `message_name` varchar(20) NOT NULL default '', + `takes_days` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`message_attribute_id`), + UNIQUE KEY `message_name` (`message_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `message_transports` ( + `message_attribute_id` int(11) NOT NULL, + `message_transport_type` varchar(20) NOT NULL, + `is_digest` tinyint(1) NOT NULL default '0', + `letter_module` varchar(20) NOT NULL default '', + `letter_code` varchar(20) NOT NULL default '', + PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`), + KEY `message_transport_type` (`message_transport_type`), + KEY `letter_module` (`letter_module`,`letter_code`), + CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `borrower_message_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL default '0', + `message_attribute_id` int(11) default '0', + `days_in_advance` int(11) default '0', + `wants_digets` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `message_attribute_id` (`message_attribute_id`), + CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +END_SQL + + $dbh->do(<<'END_SQL'); +CREATE TABLE `borrower_message_transport_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL default '0', + `message_transport_type` varchar(20) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +END_SQL + + $dbh->do(<<'END_SQL'); +INSERT INTO `systempreferences` + (variable,value,explanation,options,type) +VALUES +('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo') +END_SQL + + print "Upgrade to $DBversion done (Table structure for table `message_queue`, `message_transport_types`, `message_attributes`, `message_transports`, `borrower_message_preferences`, and `borrower_message_transport_preferences`. Alter `borrowers` table,\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table diff --git a/kohaversion.pl b/kohaversion.pl index 10835c17dd..956817d6d2 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -10,7 +10,7 @@ use strict; sub kohaversion { - our $VERSION = "3.00.00.090"; + our $VERSION = "3.00.00.091"; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install -- 2.39.5