[Koha-patches] [PATCH] Revised Schema
Daniel Sweeney
daniel.sweeney at liblime.com
Tue Feb 3 23:02:06 CET 2009
From: John Beppu <john.beppu at liblime.com>
- 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 <daniel.sweeney at liblime.com>
---
.../mysql/en/mandatory/message_transport_types.sql | 3 +-
.../data/mysql/en/optional/sample_notices.sql | 4 +-
.../optional/sample_notices_message_transports.sql | 10 +++-----
.../1-Obligatoire/message_transport_types.sql | 2 +-
.../data/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 e8c0027..517f5cc 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 ad414b0..52e7eca 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 <<count>> items due soon'),
('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <<borrowers.firstname>> <<borrowers.surname>>,\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 <<branches.branchname>>', 'Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\nLocation: <<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n<<branches.branchaddress3>>'),
-('circulation','CHECKINDGST','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n<<items.content>>'),
-('circulation','CHECKOUTDGST','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n<<items.content>>');
+('circulation','CHECKIN','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you.'),
+('circulation','CHECKOUT','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you for visiting <<branches.branchname>>.');
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 b9d9f90..c2d5d93 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 e8c0027..0c61bb8 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 ad414b0..52e7eca 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 <<count>> items due soon'),
('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <<borrowers.firstname>> <<borrowers.surname>>,\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 <<branches.branchname>>', 'Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\nLocation: <<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n<<branches.branchaddress3>>'),
-('circulation','CHECKINDGST','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n<<items.content>>'),
-('circulation','CHECKOUTDGST','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n<<items.content>>');
+('circulation','CHECKIN','Item Check-in (Digest)','Check-ins','The following items have been checked in:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you.'),
+('circulation','CHECKOUT','Item Check-out (Digest)','Checkouts','The following items have been checked out:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you for visiting <<branches.branchname>>.');
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 b9d9f90..c2d5d93 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 1678aaf..2d4b415 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 87c7283..31971d8 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<<items.content>>');
+ ('circulation','CHECKIN','Item Check-in','Check-ins','The following items have been checked in:\r\n----\r\n<<biblio.title>>\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<<items.content>>');
+ ('circulation','CHECKOUT','Item Checkout','Checkouts','The following items have been checked out:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you for visiting <<branches.branchname>>.');
});
$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);
--
1.5.5.GIT
More information about the Koha-patches
mailing list