Changeset: 9b872faec92c https://sourceforge.net/p/mrbs/hg-code/ci/9b872faec92c712aaef66ef430624044364f4a3c Author: John Beranek <jbera...@users.sourceforge.net> Date: Thu Jun 01 23:08:27 2017 +0100 Log message:
Added a timestamp column to the users table, and support for viewing it. Added function and triggers for updating the timestamp columns when using PostgreSQL. diffstat: tables.my.sql | 3 ++- tables.pg.sql | 19 ++++++++++++++++--- web/dbsys.inc | 2 +- web/edit_users.php | 14 +++++++++++++- web/lang/lang.en | 1 + web/upgrade/52/mysql.sql | 5 +++++ web/upgrade/52/pgsql.sql | 10 ++++++++++ web/upgrade/52/post.inc | 28 ++++++++++++++++++++++++++++ 8 files changed, 76 insertions(+), 6 deletions(-) diffs (189 lines): diff -r d15543350b4a -r 9b872faec92c tables.my.sql --- a/tables.my.sql Tue May 30 20:52:25 2017 +0100 +++ b/tables.my.sql Thu Jun 01 23:08:27 2017 +0100 @@ -184,12 +184,13 @@ name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci, password_hash varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci, email varchar(75) CHARACTER SET utf8 COLLATE utf8_general_ci, + timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO mrbs_variables (variable_name, variable_content) - VALUES ( 'db_version', '51'); + VALUES ( 'db_version', '52'); INSERT INTO mrbs_variables (variable_name, variable_content) VALUES ( 'local_db_version', '1'); diff -r d15543350b4a -r 9b872faec92c tables.pg.sql --- a/tables.pg.sql Tue May 30 20:52:25 2017 +0100 +++ b/tables.pg.sql Thu Jun 01 23:08:27 2017 +0100 @@ -99,7 +99,7 @@ REFERENCES mrbs_room(id) ON UPDATE CASCADE ON DELETE RESTRICT, - timestamp timestamp DEFAULT current_timestamp, + timestamp timestamptz DEFAULT current_timestamp, create_by varchar(80) NOT NULL, modified_by varchar(80) NOT NULL, name varchar(80) NOT NULL, @@ -131,7 +131,7 @@ REFERENCES mrbs_room(id) ON UPDATE CASCADE ON DELETE RESTRICT, - timestamp timestamp DEFAULT current_timestamp, + timestamp timestamptz DEFAULT current_timestamp, create_by varchar(80) NOT NULL, modified_by varchar(80) NOT NULL, name varchar(80) NOT NULL, @@ -177,11 +177,24 @@ name varchar(30), password_hash varchar(255), email varchar(75), + timestamp timestamptz DEFAULT current_timestamp, CONSTRAINT mrbs_uq_name UNIQUE (name) ); +CREATE OR REPLACE FUNCTION update_timestamp_column() +RETURNS TRIGGER AS $$ +BEGIN + NEW.timestamp = NOW(); + RETURN NEW; +END; +$$ language 'plpgsql'; + +CREATE TRIGGER update_mrbs_entry_timestamp BEFORE UPDATE ON mrbs_entry FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column(); +CREATE TRIGGER update_mrbs_repeat_timestamp BEFORE UPDATE ON mrbs_repeat FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column(); +CREATE TRIGGER update_mrbs_timestamp BEFORE UPDATE ON mrbs_users FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column(); + INSERT INTO mrbs_variables (variable_name, variable_content) - VALUES ('db_version', '51'); + VALUES ('db_version', '52'); INSERT INTO mrbs_variables (variable_name, variable_content) VALUES ('local_db_version', '1'); diff -r d15543350b4a -r 9b872faec92c web/dbsys.inc --- a/web/dbsys.inc Tue May 30 20:52:25 2017 +0100 +++ b/web/dbsys.inc Thu Jun 01 23:08:27 2017 +0100 @@ -22,7 +22,7 @@ } -$db_schema_version = 51; +$db_schema_version = 52; $local_db_schema_version = 1; // Convenience wrapper function to provide access to a DB object for diff -r d15543350b4a -r 9b872faec92c web/edit_users.php --- a/web/edit_users.php Tue May 30 20:52:25 2017 +0100 +++ b/web/edit_users.php Thu Jun 01 23:08:27 2017 +0100 @@ -171,6 +171,10 @@ "<a href=\"mailto:$escaped_email\">$escaped_email</a>\n" . "</div>\n"; break; + case 'timestamp': + // Convert the SQL timestamp into a time value and back into a localised string + $values[] = time_date_string(strtotime($col_value)); + break; default: // Where there's an associative array of options, display // the value rather than the key @@ -348,6 +352,9 @@ case 'password_hash': echo "<input type=\"hidden\" name=\"" . $params['name'] ."\" value=\"". htmlspecialchars($params['value']) . "\">\n"; break; + case 'timestamp': + // Don't show timestamp in the form at all + break; default: echo "<div>\n"; $params['disabled'] = ($level < $min_user_editing_level) && in_array($key, $auth['db']['protected_fields']); @@ -534,7 +541,7 @@ { $values = array(); $q_string = ($Id >= 0) ? "Action=Edit" : "Action=Add"; - foreach ($fields as $field) + foreach ($fields as $index => $field) { $fieldname = $field['name']; $type = get_form_var_type($field); @@ -603,6 +610,11 @@ exit; } break; + case 'timestamp': + // Don't update this field ourselves at all + unset($fields[$index]); + unset($values[$fieldname]); + break; default: $q_string .= "&$fieldname=" . urlencode($values[$fieldname]); break; diff -r d15543350b4a -r 9b872faec92c web/lang/lang.en --- a/web/lang/lang.en Tue May 30 20:52:25 2017 +0100 +++ b/web/lang/lang.en Thu Jun 01 23:08:27 2017 +0100 @@ -216,6 +216,7 @@ $vocab["users.name"] = "Name"; $vocab["users.password"] = "Password"; $vocab["users.level"] = "Rights"; +$vocab["users.timestamp"] = "Last updated"; $vocab["unknown_user"] = "Unknown user"; $vocab["you_are"] = "You are"; $vocab["login"] = "Log in"; diff -r d15543350b4a -r 9b872faec92c web/upgrade/52/mysql.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/web/upgrade/52/mysql.sql Thu Jun 01 23:08:27 2017 +0100 @@ -0,0 +1,5 @@ +-- Add a timestamp field for the users table + +ALTER TABLE %DB_TBL_PREFIX%users + ADD COLUMN `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `email`; + \ No newline at end of file diff -r d15543350b4a -r 9b872faec92c web/upgrade/52/pgsql.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/web/upgrade/52/pgsql.sql Thu Jun 01 23:08:27 2017 +0100 @@ -0,0 +1,10 @@ +-- Correct type of earlier timestamp fields +ALTER TABLE %DB_TBL_PREFIX%entry + ALTER COLUMN timestamp TYPE timestamptz; +ALTER TABLE %DB_TBL_PREFIX%repeat + ALTER COLUMN timestamp TYPE timestamptz; + +-- Add a timestamp field for the users table + +ALTER TABLE %DB_TBL_PREFIX%users + ADD COLUMN timestamp timestamptz DEFAULT current_timestamp; diff -r d15543350b4a -r 9b872faec92c web/upgrade/52/post.inc --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/web/upgrade/52/post.inc Thu Jun 01 23:08:27 2017 +0100 @@ -0,0 +1,28 @@ +<?php +// -*- mode: php; -*- +namespace MRBS; + +global $dbsys, $tbl_entry, $tbl_repeat, $tbl_users, $admin_handle; + +// Special case for PostgreSQL as attempting to create a function +// with the pgsql.sql file doesn't work due to the way the file +// is split by semi-colons. +if ($dbsys == "pgsql") +{ + // Add function to update timestamp column + $sql = <<<END_OF_SQL +CREATE OR REPLACE FUNCTION update_timestamp_column() +RETURNS TRIGGER AS \$\$ +BEGIN + NEW.timestamp = NOW(); + RETURN NEW; +END; +\$\$ language 'plpgsql'; +END_OF_SQL; + $admin_handle->command($sql); + + // Add triggers for tables with timestamp columns + $admin_handle->command("CREATE TRIGGER update_${tbl_entry}_timestamp BEFORE UPDATE ON ${tbl_entry} FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column()"); + $admin_handle->command("CREATE TRIGGER update_${tbl_repeat}_timestamp BEFORE UPDATE ON ${tbl_repeat} FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column()"); + $admin_handle->command("CREATE TRIGGER update_${tbl_users}_timestamp BEFORE UPDATE ON ${tbl_users} FOR EACH ROW EXECUTE PROCEDURE update_timestamp_column()"); +} ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Mrbs-commits mailing list Mrbs-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/mrbs-commits