Changeset:
9b872faec92c
https://sourceforge.net/p/mrbs/hg-code/ci/9b872faec92c712aaef66ef430624044364f4a3c
Author:
John Beranek <[email protected]>
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
