From 0eacb23ccc312a534bd7018e8ebdd006371ebdd9 Mon Sep 17 00:00:00 2001 From: moodler Date: Tue, 28 Dec 2004 07:40:26 +0000 Subject: [PATCH] Updated message databases with better time storage, and also some logging for message reading and writing --- lib/db/mysql.php | 25 +++++++++++++++++++++---- lib/db/mysql.sql | 21 +++++++++++++++++++-- lib/db/postgres7.php | 31 +++++++++++++++++++++++++++++-- lib/db/postgres7.sql | 16 ++++++++++++++-- version.php | 2 +- 5 files changed, 84 insertions(+), 11 deletions(-) diff --git a/lib/db/mysql.php b/lib/db/mysql.php index 9675ea3075..0879b42699 100644 --- a/lib/db/mysql.php +++ b/lib/db/mysql.php @@ -1037,13 +1037,17 @@ function main_upgrade($oldversion=0) { PRIMARY KEY (id))"); } - if ($oldversion < 2004121600) { + if ($oldversion < 2004122800) { + execute_sql("DROP TABLE {$CFG->prefix}message", false); + execute_sql("DROP TABLE {$CFG->prefix}message_read", false); + execute_sql("DROP TABLE {$CFG->prefix}message_contacts", false); + modify_database('',"CREATE TABLE `prefix_message` ( `id` int(10) unsigned NOT NULL auto_increment, `useridfrom` int(10) NOT NULL default '0', `useridto` int(10) NOT NULL default '0', `message` text NOT NULL, - `timemodified` int(10) NOT NULL default '0', + `timecreated` int(10) NOT NULL default '0', `messagetype` varchar(50) NOT NULL default '', PRIMARY KEY (`id`), KEY `useridfrom` (`useridfrom`), @@ -1055,15 +1059,28 @@ function main_upgrade($oldversion=0) { `useridfrom` int(10) NOT NULL default '0', `useridto` int(10) NOT NULL default '0', `message` text NOT NULL, - `timemodified` int(10) NOT NULL default '0', + `timecreated` int(10) NOT NULL default '0', + `timeread` int(10) NOT NULL default '0', `messagetype` varchar(50) NOT NULL default '', `mailed` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `useridfrom` (`useridfrom`), KEY `useridto` (`useridto`) ) TYPE=MyISAM COMMENT='Stores all messages that have been read';"); + + modify_database('',"CREATE TABLE `prefix_message_contacts` ( + `id` int(10) unsigned NOT NULL auto_increment, + `userid` int(10) unsigned NOT NULL default '0', + `contactid` int(10) unsigned NOT NULL default '0', + `blocked` tinyint(1) unsigned NOT NULL default '0', + PRIMARY KEY (`id`), + UNIQUE KEY `usercontact` (`userid`,`contactid`) + ) TYPE=MyISAM COMMENT='Maintains lists of relationships between users';"); + + modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'CONCAT(firstname,\" \",lastname)'); "); + modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'CONCAT(firstname,\" \",lastname)'); "); } - + return $result; } diff --git a/lib/db/mysql.sql b/lib/db/mysql.sql index 7fefb5714f..3bc9254cb7 100644 --- a/lib/db/mysql.sql +++ b/lib/db/mysql.sql @@ -309,7 +309,7 @@ CREATE TABLE `prefix_message` ( `useridfrom` int(10) NOT NULL default '0', `useridto` int(10) NOT NULL default '0', `message` text NOT NULL, - `timemodified` int(10) NOT NULL default '0', + `timecreated` int(10) NOT NULL default '0', `messagetype` varchar(50) NOT NULL default '', PRIMARY KEY (`id`), KEY `useridfrom` (`useridfrom`), @@ -326,7 +326,8 @@ CREATE TABLE `prefix_message_read` ( `useridfrom` int(10) NOT NULL default '0', `useridto` int(10) NOT NULL default '0', `message` text NOT NULL, - `timemodified` int(10) NOT NULL default '0', + `timecreated` int(10) NOT NULL default '0', + `timeread` int(10) NOT NULL default '0', `messagetype` varchar(50) NOT NULL default '', `mailed` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), @@ -335,6 +336,20 @@ CREATE TABLE `prefix_message_read` ( ) TYPE=MyISAM COMMENT='Stores all messages that have been read'; # -------------------------------------------------------- +# +# Table structure for table `message_contacts` +# + +CREATE TABLE `prefix_message_contacts` ( + `id` int(10) unsigned NOT NULL auto_increment, + `userid` int(10) unsigned NOT NULL default '0', + `contactid` int(10) unsigned NOT NULL default '0', + `blocked` tinyint(1) unsigned NOT NULL default '0', + PRIMARY KEY (`id`), + UNIQUE KEY `usercontact` (`userid`,`contactid`) +) TYPE=MyISAM COMMENT='Maintains lists of relationships between users'; +# -------------------------------------------------------- + # # Table structure for table `modules` # @@ -537,3 +552,5 @@ INSERT INTO prefix_log_display VALUES ('course', 'user report', 'user', 'CONCAT( INSERT INTO prefix_log_display VALUES ('course', 'view', 'course', 'fullname'); INSERT INTO prefix_log_display VALUES ('course', 'update', 'course', 'fullname'); INSERT INTO prefix_log_display VALUES ('course', 'enrol', 'course', 'fullname'); +INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'CONCAT(firstname," ",lastname)'); +INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'CONCAT(firstname," ",lastname)'); diff --git a/lib/db/postgres7.php b/lib/db/postgres7.php index d2f6cae5df..f023853372 100644 --- a/lib/db/postgres7.php +++ b/lib/db/postgres7.php @@ -799,14 +799,25 @@ function main_upgrade($oldversion=0) { next_change integer NOT NULL default '0', current_offset integer NOT NULL default '0' );"); + } + + if ($oldversion < 2004122800) { + execute_sql("DROP TABLE {$CFG->prefix}message", false); + execute_sql("DROP TABLE {$CFG->prefix}message_read", false); + execute_sql("DROP TABLE {$CFG->prefix}message_contacts", false); + execute_sql("DROP INDEX {$CFG->prefix}message_useridfrom_idx", false); + execute_sql("DROP INDEX {$CFG->prefix}message_useridto_idx", false); + execute_sql("DROP INDEX {$CFG->prefix}message_read_useridfrom_idx", false); + execute_sql("DROP INDEX {$CFG->prefix}message_read_useridto_idx", false); + execute_sql("DROP INDEX {$CFG->prefix}message_contacts_useridcontactid_idx", false); modify_database('',"CREATE TABLE prefix_message ( id SERIAL PRIMARY KEY, useridfrom integer NOT NULL default '0', useridto integer NOT NULL default '0', message text, - timemodified integer NOT NULL default '0', + timecreated integer NOT NULL default '0', messagetype varchar(50) NOT NULL default '' ); @@ -818,7 +829,8 @@ function main_upgrade($oldversion=0) { useridfrom integer NOT NULL default '0', useridto integer NOT NULL default '0', message text, - timemodified integer NOT NULL default '0', + timecreated integer NOT NULL default '0', + timeread integer NOT NULL default '0', messagetype varchar(50) NOT NULL default '', mailed integer NOT NULL default '0' ); @@ -826,7 +838,22 @@ function main_upgrade($oldversion=0) { CREATE INDEX prefix_message_read_useridfrom_idx ON prefix_message_read (useridfrom); CREATE INDEX prefix_message_read_useridto_idx ON prefix_message_read (useridto); "); + + modify_database('',"CREATE TABLE prefix_message_contacts ( + id SERIAL PRIMARY KEY, + userid integer NOT NULL default '0', + contactid integer NOT NULL default '0', + blocked integer NOT NULL default '0' + ); + + CREATE INDEX prefix_message_contacts_useridcontactid_idx ON prefix_message_contacts (userid,contactid); + "); + + modify_database('',"INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'firstname||\' \'||lastname'); + INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'firstname||\' \'||lastname'); + "); } + return $result; } diff --git a/lib/db/postgres7.sql b/lib/db/postgres7.sql index e176e9dd4b..22de7fb9e0 100644 --- a/lib/db/postgres7.sql +++ b/lib/db/postgres7.sql @@ -187,7 +187,7 @@ CREATE TABLE prefix_message ( useridfrom integer NOT NULL default '0', useridto integer NOT NULL default '0', message text, - timemodified integer NOT NULL default '0', + timecreated integer NOT NULL default '0', messagetype varchar(50) NOT NULL default '' ); @@ -199,7 +199,8 @@ CREATE TABLE prefix_message_read ( useridfrom integer NOT NULL default '0', useridto integer NOT NULL default '0', message text, - timemodified integer NOT NULL default '0', + timecreated integer NOT NULL default '0', + timeread integer NOT NULL default '0', messagetype varchar(50) NOT NULL default '', mailed integer NOT NULL default '0' ); @@ -207,6 +208,15 @@ CREATE TABLE prefix_message_read ( CREATE INDEX prefix_message_read_useridfrom_idx ON prefix_message_read (useridfrom); CREATE INDEX prefix_message_read_useridto_idx ON prefix_message_read (useridto); +CREATE TABLE prefix_message_contacts ( + id SERIAL PRIMARY KEY, + userid integer NOT NULL default '0', + contactid integer NOT NULL default '0', + blocked integer NOT NULL default '0' +); + +CREATE INDEX prefix_message_contacts_useridcontactid_idx ON prefix_message_contacts (userid,contactid); + CREATE TABLE prefix_modules ( id SERIAL PRIMARY KEY, name varchar(20) NOT NULL default '', @@ -373,3 +383,5 @@ INSERT INTO prefix_log_display VALUES ('course', 'user report', 'user', 'firstna INSERT INTO prefix_log_display VALUES ('course', 'view', 'course', 'fullname'); INSERT INTO prefix_log_display VALUES ('course', 'update', 'course', 'fullname'); INSERT INTO prefix_log_display VALUES ('course', 'enrol', 'course', 'fullname'); +INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'firstname||\' \'||lastname'); +INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'firstname||\' \'||lastname'); diff --git a/version.php b/version.php index 4628f5bedd..f1a3dd0609 100644 --- a/version.php +++ b/version.php @@ -6,7 +6,7 @@ // This is compared against the values stored in the database to determine // whether upgrades should be performed (see lib/db/*.php) - $version = 2004121600; // YYYYMMDD = date of first major branch release 1.4 + $version = 2004122800; // YYYYMMDD = date of first major branch release 1.4 // XY = increments within a single day $release = '1.5 UNSTABLE DEVELOPMENT'; // Human-friendly version name -- 2.39.5