From 6d3c98ce63028f1f3249a9426d7a0e6e40b31715 Mon Sep 17 00:00:00 2001 From: Kevin Day Date: Tue, 2 May 2017 14:12:14 -0500 Subject: [PATCH] Progress: log tables and related code out of sync and other fixes The log settings became noticably out of date between the code and the database. Significant changes are made, dealing with the database and its usage. Switched to timezone that supports timezone by default and then set standard behavior to use UTC timezone. --- common/base/classes/base_database.php | 10 +- common/base/classes/base_defaults_global.php | 3 + common/base/classes/base_log.php | 297 +++++++++++---------- common/base/classes/base_return.php | 117 ++++++++ common/standard/classes/standard_database.php | 67 +++-- common/standard/classes/standard_index.php | 7 +- common/standard/paths/u/login.php | 7 +- common/standard/paths/u/logout.php | 2 +- .../sql/reservation/reservation-associations.sql | 19 +- database/sql/reservation/reservation-dates.sql | 9 +- database/sql/reservation/reservation-fields.sql | 17 +- database/sql/reservation/reservation-files.sql | 9 +- database/sql/reservation/reservation-first.sql | 1 + database/sql/reservation/reservation-groups.sql | 27 +- database/sql/reservation/reservation-last.sql | 164 +++++++----- database/sql/reservation/reservation-legal.sql | 13 +- .../sql/reservation/reservation-log_groups.sql | 29 +- .../sql/reservation/reservation-log_problems.sql | 9 +- database/sql/reservation/reservation-log_types.sql | 84 ++++-- database/sql/reservation/reservation-log_users.sql | 71 ++--- database/sql/reservation/reservation-main.sql | 13 +- database/sql/reservation/reservation-paths.sql | 9 +- database/sql/reservation/reservation-requests.sql | 29 +- .../sql/reservation/reservation-statistics.sql | 13 +- database/sql/reservation/reservation-types.sql | 25 +- database/sql/reservation/reservation-users.sql | 11 +- database/sql/reservation/reservation-workflow.sql | 1 + database/sql/standard/standard-files.sql | 12 +- database/sql/standard/standard-first.sql | 3 +- database/sql/standard/standard-groups.sql | 29 +- database/sql/standard/standard-last.sql | 167 +++++++----- database/sql/standard/standard-log_groups.sql | 31 ++- database/sql/standard/standard-log_problems.sql | 11 +- database/sql/standard/standard-log_types.sql | 86 ++++-- database/sql/standard/standard-log_users.sql | 73 ++--- database/sql/standard/standard-main.sql | 15 +- database/sql/standard/standard-paths.sql | 11 +- database/sql/standard/standard-statistics.sql | 15 +- database/sql/standard/standard-types.sql | 27 +- database/sql/standard/standard-users.sql | 15 +- documentation/database.txt | 7 + .../reservation/reservation_defaults_global.php | 3 + 42 files changed, 938 insertions(+), 630 deletions(-) diff --git a/common/base/classes/base_database.php b/common/base/classes/base_database.php index 6fb7c0a..ed90693 100644 --- a/common/base/classes/base_database.php +++ b/common/base/classes/base_database.php @@ -1504,7 +1504,6 @@ class c_base_database extends c_base_return { return $result; } - if ($this->asynchronous) { if (empty($parameters)) { $result = pg_send_query($this->database, $query); @@ -2266,6 +2265,15 @@ class c_base_database extends c_base_return { * It is recommended that only the c_base_return_value::get_value() call be used when accessing the result value. */ class c_base_database_result extends c_base_return_resource { + // postgresql pg_result_status() codes. + const STATUS_EMPTY_QUERY = 0; + const STATUS_COMMAND_OK = 1; + const STATUS_TUPLES_OK = 2; + const STATUS_COPY_TO = 3; + const STATUS_COPY_FROM = 4; + const STATUS_BAD_RESPONSE = 5; + const STATUS_NON_FATAL_ERROR = 6; + const STATUS_FATAL_ERROR = 7; /** * Class constructor. diff --git a/common/base/classes/base_defaults_global.php b/common/base/classes/base_defaults_global.php index ee4d87f..48bb399 100644 --- a/common/base/classes/base_defaults_global.php +++ b/common/base/classes/base_defaults_global.php @@ -39,6 +39,9 @@ class c_base_defaults_global { // reserved path groups: array(97, 99, 100, 102, 109, 115, 116, 120, 121). const RESERVED_PATH_GROUP = array(c_base_ascii::LOWER_A, c_base_ascii::LOWER_C, c_base_ascii::LOWER_D, c_base_ascii::LOWER_F, c_base_ascii::LOWER_M, c_base_ascii::LOWER_S, c_base_ascii::LOWER_T, c_base_ascii::LOWER_U, c_base_ascii::LOWER_X); + // default log facility (17 = c_base_error::FACILITY_LOCAL_0). + const LOG_FACILITY = 17; + // Represents the current timestamp of this PHP process/session, see: self::s_get_timestamp_session(). private static $s_timestamp_session = NULL; diff --git a/common/base/classes/base_log.php b/common/base/classes/base_log.php index dab9195..e2d3420 100644 --- a/common/base/classes/base_log.php +++ b/common/base/classes/base_log.php @@ -9,68 +9,66 @@ require_once('common/base/classes/base_return.php'); /** * A generic class for managing the logs. */ -class c_base_log extends c_base_return { - const TYPE_NONE = 0; - const TYPE_BASE = 1; // for low-level entries. - const TYPE_REQUEST = 2; // accessing the site (generally page requests). - const TYPE_INTERPET = 3; // interpretting (such as a PHP-related). - const TYPE_DATABASE = 4; // the database. - const TYPE_USER = 5; // related to users. - const TYPE_PROXY = 6; // proxying as some other user. - const TYPE_ACCESS = 7; // access control. - const TYPE_CONTENT = 8; // content itself. - const TYPE_THEME = 9; // theme (such as renderring a theme). - const TYPE_RESPONSE = 10; // response to requests. - const TYPE_CONNECT = 11; // relating connecting and disconnecting from the site. - const TYPE_CLIENT = 12; // client information. - const TYPE_SERVER = 13; // server information. - const TYPE_LEGAL = 14; // legal or law-based information. - const TYPE_AUDIT = 15; // legal or law-based information. - const TYPE_CACHE = 16; // caching. - const TYPE_SYSTEM = 17; // system. - const TYPE_FILE = 18; // files. - const TYPE_TIME = 19; // time-related matters (such as cron jobs). - const TYPE_EVENT = 20; // time and place related matters. - const TYPE_SESSION = 21; // sessions. - const TYPE_MAIL = 22; // e-mails. - const TYPE_SIGN = 23; // signatures, such as PGP/GPG. - const TYPE_SYNC = 24; // synchronization of information. - const TYPE_WORKFLOW = 25; // workflow. - const TYPE_REQUEST = 26; // workflow: requesting. - const TYPE_COMMENT = 27; // workflow: commenting. - const TYPE_DRAFT = 28; // workflow: drafting. - const TYPE_REVIEW = 29; // workflow: reviewing. - const TYPE_EDIT = 30; // workflow: editting. - const TYPE_AMEND = 31; // workflow: ammending. - const TYPE_UNDO = 32; // workflow: undoing an edit. - const TYPE_APPROVE = 33; // workflow: approving. - const TYPE_DISPROVE = 34; // workflow: disproving. - const TYPE_PUBLISH = 35; // workflow: publushing. - const TYPE_UNPUBLISH = 36; // workflow: publushing. - const TYPE_ACCEPT = 37; // workflow: accepting. - const TYPE_DENY = 38; // workflow: denying. - const TYPE_CANCEL = 39; // workflow: cancelling. - const TYPE_UNCANCEL = 40; // workflow: cancelling. - const TYPE_AUDIT = 41; // workflow: auditing. - const TYPE_TRANSITION = 42; // workflow: transitioning. - const TYPE_REVERT = 43; // workflow: revert. - const TYPE_DELETE = 44; // workflow: delete. - const TYPE_RESTORE = 45; // workflow: restore (undelete). - const TYPE_UPGRADE = 46; // upgrade. - const TYPE_DOWNGRADE = 47; // downgrade. - - // severity defines how important or the context of the log entry. - const SEVERITY_NONE = 0; - const SEVERITY_DEBUG = 1; - const SEVERITY_INFORMATION = 2; // regular logging information. - const SEVERITY_NOTICE = 3; // information worth noting. - const SEVERITY_WARNING = 4; // this could be a problem. - const SEVERITY_ERROR = 5; // this is a problem. - const SEVERITY_CRITICAL = 6; // this is a big problem. - const SEVERITY_EMERGENCY = 7; // this is the most serious type of problem. +class c_base_log extends c_base_return_array { + const TYPE_NONE = 0; + const TYPE_ACCESS = 1; // access control. + const TYPE_ACCEPT = 2; // accept something, workflow: accepting. + const TYPE_AMEND = 3; // amend something, workflow: ammending. + const TYPE_APPROVE = 4; // approve something, workflow: approving. + const TYPE_AUDIT = 5; // audit something, legal or law-based information, workflow: auditing. + const TYPE_BASE = 6; // for low-level entries. + const TYPE_CACHE = 7; // caching. + const TYPE_CANCEL = 8; // cancel something, workflow: cancelling. + const TYPE_CREATE = 9; // create something. + const TYPE_CLIENT = 10; // client information. + const TYPE_CONNECT = 11; // connect to something. + const TYPE_CONTENT = 12; // content. + const TYPE_COMMENT = 13; // workflow: commenting. + const TYPE_DATABASE = 14; // the database, sql. + const TYPE_DELETE = 15; // delete something, workflow: delete. + const TYPE_DENY = 16; // deny something, workflow: denying. + const TYPE_DRAFT = 17; // draft of something, workflow: drafting. + const TYPE_DISPROVE = 18; // disapprove something, workflow: disproving. + const TYPE_DISCONNECT = 19; // disconnect something. + const TYPE_DOWNGRADE = 20; // downgrade. + const TYPE_EDIT = 21; // edit something, workflow: editting. + const TYPE_EVENT = 22; // time and place related matters. + const TYPE_FAILURE = 23; // failure. + const TYPE_FILE = 24; // files. + const TYPE_INTERPETOR = 25; // interpretting (such as a PHP-related). + const TYPE_LEGAL = 26; // legal or law-based information. + const TYPE_LOCK = 27; // lock something. + const TYPE_MAIL = 28; // e-mails. + const TYPE_PROXY = 29; // proxying as some other user. + const TYPE_PUBLISH = 30; // publish something, workflow: publushing. + const TYPE_RESPONSE = 31; // response to requests. + const TYPE_RESTORE = 32; // restore something, workflow: restore (undelete). + const TYPE_REQUEST = 33; // accessing the site (generally page requests), workflow: requesting.. + const TYPE_REVERT = 34; // revert something, workflow: revert. + const TYPE_REVIEW = 35; // review something, workflow: reviewing. + const TYPE_SCHEDULE = 36; // schedule something. + const TYPE_SEARCH = 37; // search. + const TYPE_SESSION = 38; // sessions. + const TYPE_SIGN = 39; // signatures, such as PGP/GPG. + const TYPE_SYNCHRONIZE = 40; // synchronization of information. + const TYPE_SYSTEM = 41; // system/server. + const TYPE_THEME = 42; // theme (such as renderring a theme). + const TYPE_TIME = 43; // time-related matters (such as cron jobs). + const TYPE_TRANSITION = 44; // transition, workflow: transitioning. + const TYPE_UNCANCEL = 45; // uncancel something, workflow: cancelling. + const TYPE_UNDO = 46; // undo somethingm workflow: undoing an edit. + const TYPE_UNPUBLISH = 47; // unpublish something, workflow: publushing. + const TYPE_UPDATE = 48; // update something. + const TYPE_UPGRADE = 49; // upgrade something. + const TYPE_USER = 50; // related to users. + const TYPE_VOID = 51; // void something, such as a signature. + const TYPE_WORKFLOW = 52; // workflow. protected $type; - protected $data; + protected $type_sub; + + protected $severity; + protected $facility; /** @@ -79,8 +77,11 @@ class c_base_log extends c_base_return { public function __construct() { parent::__construct(); - $this->type = self::TYPE_NONE; - $this->data = array(); + $this->type = self::TYPE_NONE; + $this->type_sub = self::TYPE_NONE; + + $this->severity = c_base_error::SEVERITY_NONE; + $this->facility = c_base_error::FACILITY_NONE; } /** @@ -88,7 +89,10 @@ class c_base_log extends c_base_return { */ public function __destruct() { unset($this->type); - unset($this->data); + unset($this->type_sub); + + unset($this->severity); + unset($this->facility); parent::__destruct(); } @@ -117,7 +121,7 @@ class c_base_log extends c_base_return { /** * Assigns the type code for this log entry. * - * @param int $tyoe + * @param int $type * The type code. * * @return c_base_return_status @@ -135,127 +139,126 @@ class c_base_log extends c_base_return { } /** - * Returns the type code for this log entry. + * Assigns the sub-type code for this log entry. * - * @return c_base_return_status|c_base_return_int - * The type integer on success. - * FALSE with error bit set on error. - */ - public function get_type() { - return c_base_return_int::s_new($this->type); - } - - /** - * Returns the data as a serialized array string. + * @param int $type_sub + * The sub-type code. * - * @return c_base_return_status|c_base_return_array - * An array representing the data array on success. + * @return c_base_return_status + * TRUE on success. * FALSE with error bit set is returned on error. */ - public function get_data_serialized() { - return c_base_return_array::s_new($this->data); + public function set_type_sub($type_sub) { + if (!is_int($type_sub) || $type_sub < 0) { + $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'type_sub', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); + return c_base_return_error::s_false($error); + } + + $this->type_sub = $type_sub; + return new c_base_return_true(); } /** - * Returns the data as a serialized array string. + * Assigns the severity code for this log entry. + * + * @param int $type + * The severity code. * - * @return c_base_return_status|c_base_return_string - * A serialized string representing the data array on success. + * @return c_base_return_status + * TRUE on success. * FALSE with error bit set is returned on error. */ - public function get_data_serialized() { - return c_base_return_string::s_new(serialize($this->data)); + public function set_severity($severity) { + if (!is_int($severity) || $severity < 0) { + $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'severity', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); + return c_base_return_error::s_false($error); + } + + $this->severity = $severity; + return new c_base_return_true(); } /** - * Returns the data as a json-serialized array string. + * Assigns the facility code for this log entry. * - * @return c_base_return_status|c_base_return_string - * A json-serialized string representing the data array on success. + * This is generally used for syslog compatibility. + * + * @param int $facility + * The facility code. + * + * @return c_base_return_status + * TRUE on success. * FALSE with error bit set is returned on error. */ - public function get_data_jsonized($options = 0, $depth = 512) { - if (!is_int($options)) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'options', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); + public function set_facility($facility) { + if (!is_int($facility) || $facility < 0) { + $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'facility', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); return c_base_return_error::s_false($error); } - if (!is_int($depth) || $depth < 1) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'depth', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); - return c_base_return_error::s_false($error); - } - - $encoded = json_encode($this->data, $options, $depth) - if ($encoded === FALSE) { - unset($encoded); + $this->facility = $facility; + return new c_base_return_true(); + } - $error = c_base_error::s_log(NULL, array('arguments' => array(':{operation_name}' => 'json_encode', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::OPERATION_FAILURE); - return c_base_return_error::s_false($error); + /** + * Returns the type code for this log entry. + * + * @return c_base_return_status|c_base_return_int + * The type integer on success. + * FALSE with error bit set on error. + */ + public function get_type() { + if (!is_int($this->type)) { + $this->type = self::TYPE_NONE; } - return c_base_return_string::s_new($encoded); + return c_base_return_int::s_new($this->type); } /** - * Assigns data to a specific key in the data array. - * - * @param string|int $key - * The key name string or integer. - * @param $value - * The value to assign. - * There is no enforcement on the data type. + * Returns the sub-type code for this log entry. * - * @return c_base_return_status - * TRUE on success, FALSE otherwise. + * @return c_base_return_status|c_base_return_int + * The type integer on success. + * FALSE with error bit set on error. */ - protected function pr_set_data($key, $value) { - if (is_int($key)) { - if ($key < 0) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'key', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); - return c_base_return_error::s_false($error); - } - } - elseif (!is_string($key)) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'value', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); - return c_base_return_error::s_false($error); + public function get_type_sub() { + if (!is_int($this->type_sub)) { + $this->type_sub = self::TYPE_NONE; } - $this->data[$key] = $value; - return new c_base_return_true(); + return c_base_return_int::s_new($this->type_sub); } /** - * Returns the data assigned at the specified key. - * - * @param string|int $key - * The key name string or integer. + * Returns the severity code for this log entry. * - * @return c_base_return_status|c_base_return_value - * The array value is returned on success. - * FALSE with error bit set is returned on invalid key. - * FALSE with error bit set is returned on error. + * @return c_base_return_status|c_base_return_int + * The severity integer on success. + * FALSE with error bit set on error. */ - protected function pr_get_data($key) { - if (is_int($key)) { - if ($key < 0) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'key', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); - return c_base_return_error::s_false($error); - } - } - elseif (!is_string($key)) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':{argument_name}' => 'key', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::INVALID_ARGUMENT); - return c_base_return_error::s_false($error); + public function get_severity() { + if (!is_int($this->severity)) { + $this->severity = c_base_error::SEVERITY_NONE; } - if (!array_key_exists($key, $this->data)) { - $error = c_base_error::s_log(NULL, array('arguments' => array(':index_name' => $key, ':{array_name}' => 'this->data', ':{function_name}' => __CLASS__ . '->' . __FUNCTION__)), i_base_error_messages::NOT_FOUND_ARRAY_INDEX); - return c_base_return_error::s_false($error); - } + return c_base_return_int::s_new($this->severity); + } - if ($this->data[$key] instanceof c_base_return) { - return $this->data[$key]; + /** + * Returns the facility code for this log entry. + * + * This is generally used for syslog compatibility. + * + * @return c_base_return_status|c_base_return_int + * The facility integer on success. + * FALSE with error bit set on error. + */ + public function get_facility() { + if (!is_int($this->facility)) { + $this->facility = c_base_error::FACILITY_NONE; } - return c_base_return_value::s_new($this->data[$key]); + return c_base_return_int::s_new($this->facility); } } diff --git a/common/base/classes/base_return.php b/common/base/classes/base_return.php index c6a0886..df0eb1a 100644 --- a/common/base/classes/base_return.php +++ b/common/base/classes/base_return.php @@ -1148,6 +1148,84 @@ class c_base_return_array extends c_base_return_value { } /** + * Returns the data as a serialized array string. + * + * @param string + * A serialized string to convert to an array. + * + * @return bool + * TRUE on success, FALSE otherwise. + * if converted string does not produce an array, FALSE is returned and value is set to an empty array. + * + * @see: unserialize() + */ + public function set_value_serialized($string) { + if (!is_string($string)) { + return FALSE; + } + + $unserialized = unserialize($this->value); + if (is_array($unserialized)) { + $this->value = $unserialized; + unset($unserialized); + + return TRUE; + } + unset($unserialized); + + $this->value = array(); + return FALSE; + } + + /** + * Returns the data as a json-serialized array string. + * + * @param string + * A serialized string to convert to an array. + * @param bool $associative + * (optional) When TRUE array is return as an associative array. + * @param int $options + * (optional) bitmask of json constants. + * @param int $depth + * (optional) Maximum array depth. + * + * @return bool + * TRUE on success, FALSE otherwise. + * if converted string does not produce an array, FALSE is returned and value is set to an empty array. + * + * @see: json_decode() + */ + public function set_value_jsonized($string, $associative = TRUE, $options = 0, $depth = 512) { + if (!is_string($string)) { + return FALSE; + } + + if (!is_bool($associative)) { + $associative = TRUE; + } + + if (!is_int($options)) { + $options = 0; + } + + if (!is_int($depth) || $depth < 1) { + $depth = 512; + } + + $decoded = json_decode($this->data, $associative, $options, $depth); + if (is_array($decoded)) { + $this->value = $decoded; + unset($decoded); + + return TRUE; + } + unset($decoded); + + $this->value = array(); + return FALSE; + } + + /** * Return the value. * * @return array|null $value @@ -1317,6 +1395,45 @@ class c_base_return_array extends c_base_return_value { return array_keys($this->value); } + + /** + * Returns the data as a serialized array string. + * + * @return string|bool + * A serialized string representing the value array on success. + * FALSE on failure. + * + * @see: serialize() + */ + public function get_value_serialized() { + return serialize($this->value); + } + + /** + * Returns the data as a json-serialized array string. + * + * @param int $options + * (optional) bitmask of json constants. + * @param int $depth + * (optional) Maximum array depth. + * + * @return string|bool + * A json-serialized string representing the value array on success. + * FALSE on failure. + * + * @see: json_encode() + */ + public function get_value_jsonized($options = 0, $depth = 512) { + if (!is_int($options)) { + $options = 0; + } + + if (!is_int($depth) || $depth < 1) { + $depth = 512; + } + + return json_encode($this->data, $options, $depth); + } } /** diff --git a/common/standard/classes/standard_database.php b/common/standard/classes/standard_database.php index 6feb20b..67647fb 100644 --- a/common/standard/classes/standard_database.php +++ b/common/standard/classes/standard_database.php @@ -11,12 +11,6 @@ require_once('common/base/classes/base_database.php'); * Standard implementation of c_base_database(). */ class c_standard_database extends c_base_database { - const LOG_TYPE_NONE = 0; - const LOG_TYPE_LOGIN = 1; - const LOG_TYPE_LOGOUT = 2; - const LOG_TYPE_CREATE_USER = 3; - const LOG_TYPE_LOGIN_FAILIRE = 4; - /** * Write a log to the database, associated with the current user. */ @@ -31,50 +25,58 @@ class c_standard_database extends c_base_database { return c_base_return_error::s_false($error); } - $query_string = 'insert into v_log_users_self_insert (log_title, log_type, log_severity, request_client, response_code, log_details)'; - $query_string .= ' values ($1, $2, $3, ($4, $5, $6), $7, $8); '; + $query_string = 'insert into v_log_users_self_insert (log_title, log_type, log_type_sub, log_severity, log_facility, request_client, response_code, log_details)'; + $query_string .= ' values ($1, $2, $3, $4, $5, ($6, $7, $8), $9, $10)'; $query_parameters = array(); - $query_parameters[3] = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : '0.0.0.0'; - $query_parameters[4] = isset($_SERVER['REMOTE_PORT']) ? $_SERVER['REMOTE_PORT'] : 0; - $query_parameters[5] = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '' ; - $query_parameters[6] = $response_code; + $query_parameters[5] = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : '0.0.0.0'; + $query_parameters[6] = isset($_SERVER['REMOTE_PORT']) && is_numeric($_SERVER['REMOTE_PORT']) ? (int) $_SERVER['REMOTE_PORT'] : 0; + $query_parameters[7] = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '' ; + $query_parameters[8] = $response_code; - if ($log_type === self::LOG_TYPE_LOGIN) { + if ($log_type === c_base_log::TYPE_CONNECT) { $expires = NULL; if (isset($data['expires']) && is_int($data['expires'])) { $expires = $data['expires']; } $query_parameters[0] = "Logging in to the system."; - $query_parameters[1] = 17; - $query_parameters[2] = 1; - $query_parameters[7] = json_encode(array('expires' => $expires)); + $query_parameters[1] = c_base_log::TYPE_SESSION; + $query_parameters[2] = c_base_log::TYPE_CONNECT; + $query_parameters[3] = c_base_error::SEVERITY_INFORMATIONAL; + $query_parameters[4] = c_base_defaults_global::LOG_FACILITY; + $query_parameters[9] = json_encode(array('expires' => $expires)); unset($expires); } - elseif ($log_type === self::LOG_TYPE_LOGOUT) { + elseif ($log_type === c_base_log::TYPE_DISCONNECT) { $query_parameters[0] = "Logging out of the system."; - $query_parameters[1] = 18; - $query_parameters[2] = 1; - $query_parameters[7] = NULL; + $query_parameters[1] = c_base_log::TYPE_SESSION; + $query_parameters[2] = c_base_log::TYPE_DISCONNECT; + $query_parameters[3] = c_base_error::SEVERITY_INFORMATIONAL; + $query_parameters[4] = c_base_defaults_global::LOG_FACILITY; + $query_parameters[9] = NULL; } - elseif ($log_type === self::LOG_TYPE_CREATE) { + elseif ($log_type === c_base_log::TYPE_CREATE) { $query_parameters[0] = "Created the user account."; - $query_parameters[1] = 27; - $query_parameters[2] = 1; - $query_parameters[7] = NULL; + $query_parameters[1] = c_base_log::TYPE_CREATE; + $query_parameters[2] = c_base_log::TYPE_NONE; + $query_parameters[3] = c_base_error::SEVERITY_INFORMATIONAL; + $query_parameters[4] = c_base_defaults_global::LOG_FACILITY; + $query_parameters[9] = NULL; } - elseif ($log_type === self::LOG_TYPE_LOGIN_FAILURE) { + elseif ($log_type === c_base_log::TYPE_FAILURE) { $user_name = NULL; if (isset($data['user_name']) && is_string($data['user_name'])) { $user_name = $data['user_name']; } - $query_parameters[0] = "Failed to login as the user '" . $user_name . "'."; - $query_parameters[1] = 17; - $query_parameters[2] = 2; - $query_parameters[7] = json_encode(array('user_name' => $user_name)); + $query_parameters[0] = "Failed to login as the user ':{user_name}'."; + $query_parameters[1] = c_base_log::TYPE_CONNECT; + $query_parameters[2] = c_base_log::TYPE_FAILURE; + $query_parameters[3] = c_base_error::SEVERITY_NOTICE; + $query_parameters[4] = c_base_defaults_global::LOG_FACILITY; + $query_parameters[9] = json_encode(array('user_name' => $user_name)); unset($user_name); } @@ -85,8 +87,15 @@ class c_standard_database extends c_base_database { ksort($query_parameters); $query_result = $this->do_query($query_string, $query_parameters); + var_dump($query_string); + print("
"); + var_dump($query_parameters); + print("

"); + var_dump($query_result->get_value()); + print("
"); unset($query_string); unset($query_parameters); + exit(); if (c_base_return::s_has_error($query_result)) { $last_error = $this->get_last_error()->get_value_exact(); diff --git a/common/standard/classes/standard_index.php b/common/standard/classes/standard_index.php index 586222c..26473f0 100644 --- a/common/standard/classes/standard_index.php +++ b/common/standard/classes/standard_index.php @@ -170,9 +170,10 @@ class c_standard_index extends c_base_return { return c_base_return_error::s_false($error); } - $database->do_query('set bytea_output to hex;'); - $database->do_query('set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public;'); - $database->do_query('set datestyle to us;'); + $database->do_query('set bytea_output to hex'); + $database->do_query('set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public'); + $database->do_query('set datestyle to us'); + $database->do_query('set timezone to UTC'); return new c_base_return_true(); } diff --git a/common/standard/paths/u/login.php b/common/standard/paths/u/login.php index 75186c7..5ebab10 100644 --- a/common/standard/paths/u/login.php +++ b/common/standard/paths/u/login.php @@ -10,6 +10,7 @@ require_once('common/base/classes/base_path.php'); require_once('common/base/classes/base_http_status.php'); require_once('common/base/classes/base_cookie.php'); require_once('common/base/classes/base_ldap.php'); +require_once('common/base/classes/base_log.php'); require_once('common/standard/classes/standard_index.php'); require_once('common/standard/classes/standard_path.php'); @@ -394,8 +395,8 @@ class c_standard_path_user_login extends c_standard_path { c_standard_index::s_do_initialize_database($database); if ($database instanceof c_standard_database) { - $database->do_log_user(c_standard_database::LOG_TYPE_CREATE_USER, c_base_http_status::OK, array('user_name' => $_POST['login_form-username'])); - $database->do_log_user(c_standard_database::LOG_TYPE_LOGIN, c_base_http_status::OK, array('expires' => $session->get_timeout_expire()->get_value_exact())); + $database->do_log_user(c_base_log::TYPE_CREATE, c_base_http_status::OK, array('user_name' => $_POST['login_form-username'])); + $database->do_log_user(c_base_log::TYPE_CONNECT, c_base_http_status::OK, array('expires' => $session->get_timeout_expire()->get_value_exact())); } } } @@ -440,7 +441,7 @@ class c_standard_path_user_login extends c_standard_path { c_standard_index::s_do_initialize_database($database); if ($database instanceof c_standard_database) { - $database->do_log_user(c_standard_database::LOG_TYPE_LOGIN, c_base_http_status::OK, array('expires' => $session->get_timeout_expire()->get_value_exact())); + $database->do_log_user(c_base_log::TYPE_CONNECT, c_base_http_status::OK, array('expires' => $session->get_timeout_expire()->get_value_exact())); } } diff --git a/common/standard/paths/u/logout.php b/common/standard/paths/u/logout.php index 6a9cf62..25d3cfe 100644 --- a/common/standard/paths/u/logout.php +++ b/common/standard/paths/u/logout.php @@ -76,7 +76,7 @@ class c_standard_path_user_logout extends c_standard_path { // disconnect from the database. if ($database->is_connected() instanceof c_base_return_true) { if ($database instanceof c_standard_database) { - $database->do_log_user(c_standard_database::LOG_TYPE_LOGOUT, $http_status); + $database->do_log_user(c_base_log::TYPE_DISCONNECT, $http_status); } $database->do_disconnect(); diff --git a/database/sql/reservation/reservation-associations.sql b/database/sql/reservation/reservation-associations.sql index a646c81..4c6c461 100644 --- a/database/sql/reservation/reservation-associations.sql +++ b/database/sql/reservation/reservation-associations.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -31,15 +32,15 @@ create table s_tables.t_associations ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_approved timestamp, - date_cancelled timestamp, - date_denied timestamp, - date_troubled timestamp, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_approved timestamp with time zone, + date_cancelled timestamp with time zone, + date_denied timestamp with time zone, + date_troubled timestamp with time zone, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_affiliation bigint, field_classification bigint, diff --git a/database/sql/reservation/reservation-dates.sql b/database/sql/reservation/reservation-dates.sql index b185150..d47b878 100644 --- a/database/sql/reservation/reservation-dates.sql +++ b/database/sql/reservation/reservation-dates.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -22,10 +23,10 @@ create table s_tables.t_date_contexts ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cu_date_contexts_id unique (id), constraint cu_date_contexts_name_machine unique (name_machine), diff --git a/database/sql/reservation/reservation-fields.sql b/database/sql/reservation/reservation-fields.sql index 5791fc7..bc9a73c 100644 --- a/database/sql/reservation/reservation-fields.sql +++ b/database/sql/reservation/reservation-fields.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -22,10 +23,10 @@ create table s_tables.t_field_affiliations ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cu_field_affiliations_id unique (id), constraint cu_field_affiliations_name_machine unique (name_machine), @@ -71,10 +72,10 @@ create table s_tables.t_field_classifications ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cu_field_classifications_id unique (id), constraint cu_field_classifications_name_machine unique (name_machine), diff --git a/database/sql/reservation/reservation-files.sql b/database/sql/reservation/reservation-files.sql index 76c6a1b..604a38a 100644 --- a/database/sql/reservation/reservation-files.sql +++ b/database/sql/reservation/reservation-files.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -29,10 +30,10 @@ create table s_tables.t_files ( field_data bytea not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_files primary key (id), diff --git a/database/sql/reservation/reservation-first.sql b/database/sql/reservation/reservation-first.sql index 2cf171b..5a3eaee 100644 --- a/database/sql/reservation/reservation-first.sql +++ b/database/sql/reservation/reservation-first.sql @@ -77,6 +77,7 @@ create database reservation; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; /* Make sure public is never allowed to create tables! */ diff --git a/database/sql/reservation/reservation-groups.sql b/database/sql/reservation/reservation-groups.sql index 978adad..42a0a87 100644 --- a/database/sql/reservation/reservation-groups.sql +++ b/database/sql/reservation/reservation-groups.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; /* Note about composite groups (is_composite) @@ -37,11 +38,11 @@ create table s_tables.t_groups ( can_manage_paths boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, settings json, @@ -159,10 +160,10 @@ create table s_tables.t_group_users ( id_user bigint not null, id_group bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, is_locked boolean default false not null, is_deleted boolean default false not null, @@ -221,10 +222,10 @@ create table s_tables.t_group_composites ( id_composite bigint not null, id_group bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, is_locked boolean default false not null, is_deleted boolean default false not null, diff --git a/database/sql/reservation/reservation-last.sql b/database/sql/reservation/reservation-last.sql index 280d09b..df385e3 100644 --- a/database/sql/reservation/reservation-last.sql +++ b/database/sql/reservation/reservation-last.sql @@ -1,11 +1,17 @@ /** Standardized SQL Structure - Last */ /** This depends on: everything (run this absolutely last) **/ - /** The purpose of this is to add all initial data after all appropriate triggers are defined. ***/ start transaction; +/** Custom database specific settings (do this on every connection made) **/ +set bytea_output to hex; +set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; +set datestyle to us; +set timezone to UTC; + + insert into s_tables.t_date_contexts (id, name_machine, name_human) values (0, 'none', 'None'); insert into s_tables.t_date_contexts (name_machine, name_human) values ('rehearsal', 'Rehearsal / Setup'); insert into s_tables.t_date_contexts (name_machine, name_human) values ('event', 'Event / Meeting'); @@ -22,55 +28,59 @@ alter sequence s_tables.se_log_types_id start 1000; alter sequence s_tables.se_log_types_id restart; -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ insert into s_tables.t_log_types (id, name_machine, name_human) values (0, 'none', 'None'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (1, 'php', 'PHP'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (2, 'theme', 'Theme'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (3, 'cache', 'Cache'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (4, 'javascript', 'Javascript'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (5, 'ajax', 'AJAX'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (6, 'sql', 'SQL'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (7, 'form', 'Form'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (8, 'form_validate', 'Form Validation'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (9, 'form_submit', 'Form Submit'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (10, 'load', 'Load'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (11, 'save', 'Save'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (12, 'render', 'Render'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (13, 'client', 'Client'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (14, 'add', 'Add'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (15, 'create', 'Create'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (16, 'change', 'Change'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (17, 'delete', 'Delete'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (18, 'redirect', 'Redirect'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (19, 'login', 'Login'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (20, 'logout', 'Logout'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (21, 'session', 'Session'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (22, 'database', 'Database'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (23, 'not_found', 'Not Found'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (24, 'access_denied', 'Access Denied'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (25, 'removed', 'Removed'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (26, 'locked', 'Locked'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (27, 'timeout', 'Timeout'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (28, 'expire', 'Expiration'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (29, 'user', 'User'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (30, 'error', 'Error'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (31, 'content', 'Content'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (32, 'workflow', 'Workflow'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (33, 'draft', 'Draft'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (34, 'clone', 'Clone'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (35, 'publish', 'Publish'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (36, 'revert', 'Revert'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (37, 'validate', 'Validate'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (38, 'approve', 'Approve'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (39, 'password', 'Password'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (40, 'revision', 'Revision'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (41, 'search', 'Search'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (42, 'access', 'Access'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (43, 'unknown', 'Unknown'); - - - -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ +insert into s_tables.t_log_types (id, name_machine, name_human) values (1, 'access', 'Access'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (2, 'accept', 'Accept'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (3, 'amend', 'Amend'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (4, 'approve', 'Approve'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (5, 'audit', 'Audit'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (6, 'base', 'Base'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (7, 'cache', 'Cache'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (8, 'cancel', 'Cancel'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (9, 'create', 'Create'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (10, 'client', 'Client'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (11, 'connect', 'Connect'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (12, 'content', 'Content'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (13, 'comment', 'Comment'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (14, 'databae', 'Database'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (15, 'delete', 'Delete'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (16, 'deny', 'Deny'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (17, 'draft', 'Draft'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (18, 'disprove', 'Disprove'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (19, 'downgrade', 'Downgrade'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (20, 'edit', 'Edit'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (21, 'event', 'Event'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (22, 'file', 'File'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (23, 'interpretor', 'Interpretor'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (24, 'legal', 'Legal'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (25, 'lock', 'Lock'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (26, 'mail', 'Mail'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (27, 'proxy', 'Proxy'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (28, 'publish', 'Publish'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (29, 'response', 'Response'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (30, 'restore', 'Restore'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (31, 'request', 'Request'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (32, 'revert', 'Revert'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (33, 'review', 'Review'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (34, 'schedule', 'Schedule'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (35, 'search', 'Search'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (36, 'session', 'Session'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (37, 'sign', 'Sign'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (38, 'synchronize', 'Synchronize'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (39, 'system', 'Systen'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (40, 'theme', 'Theme'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (41, 'time', 'Time'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (42, 'transition', 'Transition'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (43, 'uncancel', 'Uncancel'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (44, 'undo', 'Undo'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (45, 'unpublish', 'Unpublish'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (46, 'update', 'Update'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (47, 'upgrade', 'upgrade'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (48, 'user', 'User'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (49, 'void', 'Void'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (50, 'workflow', 'Workflow'); + + insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (0, '0', 'Undefined'); insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (1, '1', 'Invalid'); insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (2, '2', 'Unknown'); @@ -141,21 +151,47 @@ insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) val /*** start the sequence count at 1000 to allow for < 1000 to be reserved for special uses ***/ -alter sequence s_tables.se_log_type_severity_levels_id start 1000; -alter sequence s_tables.se_log_type_severity_levels_id restart; - - -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (0, 'none', 'None'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (1, 'information', 'Information'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (2, 'notice', 'Notice'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (3, 'debug', 'Debug'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (4, 'warning', 'Warning'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (5, 'error', 'Error'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (6, 'alert', 'Alert'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (7, 'critical', 'Critical'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (8, 'emergency', 'Emergency'); - +alter sequence s_tables.se_log_type_severitys_id start 1000; +alter sequence s_tables.se_log_type_severitys_id restart; + + +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (0, 'none', 'None'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (1, 'emergency', 'Emergency'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (2, 'alert', 'Alert'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (3, 'critical', 'Critical'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (4, 'error', 'Error'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (5, 'warning', 'Warning'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (6, 'notice', 'Notice'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (7, 'information', 'Information'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (8, 'debug', 'Debug'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (9, 'unknown', 'Unknown'); + + +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (0, 'none', 'None'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (1, 'kernel', 'Kernel'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (2, 'user', 'User'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (3, 'mail', 'Mail'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (4, 'daemin', 'Daemon'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (5, 'security', 'Security'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (6, 'messages', 'Messages'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (7, 'printer', 'Printer'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (8, 'network', 'Network'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (9, 'uucp', 'UUCP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (10, 'clock', 'Clock'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (11, 'authorization', 'Authorization'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (12, 'ftp', 'FTP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (13, 'ntp', 'NTP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (14, 'audit', 'Audit'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (15, 'alert', 'Alert'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (16, 'cron', 'Cron'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (17, 'local_0', 'Local 0'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (18, 'local_1', 'Local 1'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (19, 'local_2', 'Local 2'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (20, 'local_3', 'Local 3'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (21, 'local_4', 'Local 4'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (22, 'local_5', 'Local 5'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (23, 'local_6', 'Local 6'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (24, 'local_7', 'Local 7'); insert into s_tables.t_type_mime_categorys (id, name_machine, name_human) values (0, 'none', 'None'); diff --git a/database/sql/reservation/reservation-legal.sql b/database/sql/reservation/reservation-legal.sql index 0897788..08ca990 100644 --- a/database/sql/reservation/reservation-legal.sql +++ b/database/sql/reservation/reservation-legal.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -22,10 +23,10 @@ create table s_tables.t_legal_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cu_legal_types_id unique (id), constraint cu_legal_types_name_machine unique (name_machine), @@ -71,8 +72,8 @@ create table s_tables.t_signatures ( is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, /* @todo: PGP/GPG based signatures are planned but not currently implemented, the columns (fingerprint and signature) are created but are subject to major change. */ field_fingerprint varchar(64), diff --git a/database/sql/reservation/reservation-log_groups.sql b/database/sql/reservation/reservation-log_groups.sql index 6bedd36..d7e4fb1 100644 --- a/database/sql/reservation/reservation-log_groups.sql +++ b/database/sql/reservation/reservation-log_groups.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,8 +20,11 @@ create table s_tables.t_log_groups ( id_group bigint not null, log_type bigint not null, + log_type_sub bigint not null, + log_severity bigint not null, + log_facility bigint not null, log_details json, - log_date timestamp default localtimestamp not null, + log_date timestamp with time zone default current_timestamp not null, constraint cp_log_groups primary key (id), @@ -29,7 +33,10 @@ create table s_tables.t_log_groups ( constraint cf_log_groups_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_groups_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_groups_id_group foreign key (id_group) references s_tables.t_groups (id) on delete restrict on update cascade, - constraint cf_log_groups_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade + constraint cf_log_groups_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_groups_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_groups_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_groups_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade ); create sequence s_tables.se_log_groups_id owned by s_tables.t_log_groups.id; @@ -42,13 +49,13 @@ grant usage on s_tables.se_log_groups_id to r_reservation, r_reservation_system; /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_groups_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not) - select id, id_user, id_group, log_type, log_details, log_date from s_tables.t_log_groups + select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_details, log_date from s_tables.t_log_groups where id_user in (select * from this_user); grant select on s_users.v_log_groups_self to r_reservation, r_reservation_system; create view s_users.v_log_groups_self_insert with (security_barrier=true) as - select id_group, log_type, log_details from s_tables.t_log_groups + select id_group, log_type, log_type_sub, log_severity, log_facility, log_details from s_tables.t_log_groups where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; @@ -73,7 +80,10 @@ create table s_tables.t_log_group_users ( id_group bigint not null, log_type bigint not null, - log_date timestamp default localtimestamp not null, + log_type_sub bigint not null, + log_severity bigint not null, + log_facility bigint not null, + log_date timestamp with time zone default current_timestamp not null, constraint cp_log_group_users primary key (id), @@ -82,7 +92,10 @@ create table s_tables.t_log_group_users ( constraint cf_log_group_users_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_group_users_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_group_users_id_group foreign key (id_group) references s_tables.t_groups (id) on delete restrict on update cascade, - constraint cf_log_group_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade + constraint cf_log_group_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade ); create sequence s_tables.se_log_group_users_id owned by s_tables.t_log_group_users.id; @@ -96,13 +109,13 @@ grant usage on s_tables.se_log_group_users_id to r_reservation, r_reservation_sy create view s_users.v_log_group_users_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not), allowed_groups as (select id from s_users.v_groups_self where not is_locked) - select id, id_user, id_group, log_type, log_date from s_tables.t_log_group_users + select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_date from s_tables.t_log_group_users where id_user in (select * from this_user) or id_group in (select * from allowed_groups); grant select on s_users.v_log_group_users_self to r_reservation, r_reservation_system; create view s_users.v_log_group_users_self_insert with (security_barrier=true) as - select id_group, log_type from s_tables.t_log_group_users + select id_group, log_type, log_type_sub, log_severity, log_facility from s_tables.t_log_group_users where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; diff --git a/database/sql/reservation/reservation-log_problems.sql b/database/sql/reservation/reservation-log_problems.sql index b6d8afb..57458f8 100644 --- a/database/sql/reservation/reservation-log_problems.sql +++ b/database/sql/reservation/reservation-log_problems.sql @@ -9,6 +9,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,8 +20,8 @@ create table s_tables.t_log_problems ( name_machine varchar(128) not null, name_human varchar(256) not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, constraint cp_log_problems primary key (id), @@ -45,8 +46,8 @@ create table s_tables.t_log_problems_users ( id_user bigint not null, id_user_session bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, log_details json, diff --git a/database/sql/reservation/reservation-log_types.sql b/database/sql/reservation/reservation-log_types.sql index 9bc2461..72d38de 100644 --- a/database/sql/reservation/reservation-log_types.sql +++ b/database/sql/reservation/reservation-log_types.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -21,9 +22,9 @@ create table s_tables.t_log_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_log_types primary key (id), @@ -59,7 +60,7 @@ create trigger tr_log_types_date_changed_deleted_or_locked /*** provide log severity level id and names ***/ -create table s_tables.t_log_type_severity_levels ( +create table s_tables.t_log_type_severitys ( id bigint not null, name_machine varchar(128) not null, name_human varchar(256) not null, @@ -67,34 +68,75 @@ create table s_tables.t_log_type_severity_levels ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, - constraint cp_log_type_severity_levels primary key (id), + constraint cp_log_type_severitys primary key (id), - constraint cu_log_type_severity_levels_user unique (name_machine), + constraint cu_log_type_severitys_user unique (name_machine), - constraint cc_log_type_severity_levels_id check (id >= 0) + constraint cc_log_type_severitys_id check (id >= 0) ); -create sequence s_tables.se_log_type_severity_levels_id owned by s_tables.t_log_type_severity_levels.id; -alter table s_tables.t_log_type_severity_levels alter column id set default nextval('s_tables.se_log_type_severity_levels_id'::regclass); +create sequence s_tables.se_log_type_severitys_id owned by s_tables.t_log_type_severitys.id; +alter table s_tables.t_log_type_severitys alter column id set default nextval('s_tables.se_log_type_severitys_id'::regclass); -grant select,insert,update on s_tables.t_log_type_severity_levels to r_reservation_administer; -grant select on s_tables.t_log_type_severity_levels to r_reservation_manager, r_reservation_auditor; -grant select,usage on s_tables.se_log_type_severity_levels_id to r_reservation_administer; +grant select,insert,update on s_tables.t_log_type_severitys to r_reservation_administer; +grant select on s_tables.t_log_type_severitys to r_reservation_manager, r_reservation_auditor; +grant select,usage on s_tables.se_log_type_severitys_id to r_reservation_administer; -create view s_users.v_log_type_severity_levels with (security_barrier=true) as - select id, name_machine, name_human from s_tables.t_log_type_severity_levels +create view s_users.v_log_type_severitys with (security_barrier=true) as + select id, name_machine, name_human from s_tables.t_log_type_severitys where not is_deleted; -grant select on s_users.v_log_type_severity_levels to r_reservation, r_reservation_public, r_reservation_system; +grant select on s_users.v_log_type_severitys to r_reservation, r_reservation_public, r_reservation_system; -create trigger tr_log_type_severity_levels_date_changed_deleted_or_locked - before update on s_tables.t_log_type_severity_levels +create trigger tr_log_type_severitys_date_changed_deleted_or_locked + before update on s_tables.t_log_type_severitys + for each row execute procedure s_administers.f_common_update_date_changed_deleted_or_locked(); + + + +/*** provide log facility level id and names ***/ +create table s_tables.t_log_type_facilitys ( + id bigint not null, + name_machine varchar(128) not null, + name_human varchar(256) not null, + + is_locked boolean default false not null, + is_deleted boolean default false not null, + + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, + + constraint cp_log_type_facilitys primary key (id), + + constraint cu_log_type_facilitys_user unique (name_machine), + + constraint cc_log_type_facilitys_id check (id >= 0) +); + +create sequence s_tables.se_log_type_facilitys_id owned by s_tables.t_log_type_facilitys.id; +alter table s_tables.t_log_type_facilitys alter column id set default nextval('s_tables.se_log_type_facilitys_id'::regclass); + +grant select,insert,update on s_tables.t_log_type_facilitys to r_reservation_administer; +grant select on s_tables.t_log_type_facilitys to r_reservation_manager, r_reservation_auditor; +grant select,usage on s_tables.se_log_type_facilitys_id to r_reservation_administer; + +create view s_users.v_log_type_facilitys with (security_barrier=true) as + select id, name_machine, name_human from s_tables.t_log_type_facilitys + where not is_deleted; + +grant select on s_users.v_log_type_facilitys to r_reservation, r_reservation_public, r_reservation_system; + + +create trigger tr_log_type_facilitys_date_changed_deleted_or_locked + before update on s_tables.t_log_type_facilitys for each row execute procedure s_administers.f_common_update_date_changed_deleted_or_locked(); diff --git a/database/sql/reservation/reservation-log_users.sql b/database/sql/reservation/reservation-log_users.sql index 351cd93..d11dad8 100644 --- a/database/sql/reservation/reservation-log_users.sql +++ b/database/sql/reservation/reservation-log_users.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,9 +20,11 @@ create table s_tables.t_log_users ( log_title varchar(512) not null, log_type bigint not null, + log_type_sub bigint not null, log_severity bigint not null, + log_facility bigint not null, log_details json, - log_date timestamp default localtimestamp not null, + log_date timestamp with time zone default current_timestamp not null, request_client public.ct_client not null, response_code smallint not null default 0, @@ -34,7 +37,9 @@ create table s_tables.t_log_users ( constraint cf_log_users_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_users_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, - constraint cf_log_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severity_levels (id) on delete restrict on update cascade, + constraint cf_log_users_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_users_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade, constraint cf_log_users_response_code foreign key (response_code) references s_tables.t_type_http_status_codes (id) on delete restrict on update cascade ); @@ -45,51 +50,13 @@ grant select on s_tables.t_log_users to r_reservation_manager, r_reservation_aud grant select,usage on s_tables.se_log_users_id to r_reservation_administer; grant usage on s_tables.se_log_users_id to r_reservation, r_reservation_public, r_reservation_system; -create index i_log_users_type_php on s_tables.t_log_users (id) - where log_type = 1; - -create index i_log_users_type_theme on s_tables.t_log_users (id) - where log_type = 2; - -create index i_log_users_type_cache on s_tables.t_log_users (id) - where log_type = 3; - -create index i_log_users_type_javascript on s_tables.t_log_users (id) - where log_type = 4; - -create index i_log_users_type_ajax on s_tables.t_log_users (id) - where log_type = 5; - -create index i_log_users_type_sql on s_tables.t_log_users (id) - where log_type = 6; - -create index i_log_users_type_redirect on s_tables.t_log_users (id) - where log_type = 16; - -create index i_log_users_type_login on s_tables.t_log_users (id) - where log_type = 17; - -create index i_log_users_type_logout on s_tables.t_log_users (id) - where log_type = 18; - -create index i_log_users_type_user on s_tables.t_log_users (id) - where log_type = 27; - -create index i_log_users_type_error on s_tables.t_log_users (id) - where log_type = 28; - -create index i_log_users_type_content on s_tables.t_log_users (id) - where log_type = 29; - -create index i_log_users_type_workflow on s_tables.t_log_users (id) - where log_type = 30; - -create index i_log_users_type_search on s_tables.t_log_users (id) - where log_type = 39; create index i_log_users_response_code_200 on s_tables.t_log_users (id) where response_code = 200; +create index i_log_users_response_code_400 on s_tables.t_log_users (id) + where response_code = 400; + create index i_log_users_response_code_403 on s_tables.t_log_users (id) where response_code = 403; @@ -99,6 +66,9 @@ create index i_log_users_response_code_404 on s_tables.t_log_users (id) create index i_log_users_response_code_410 on s_tables.t_log_users (id) where response_code = 410; +create index i_log_users_response_code_451 on s_tables.t_log_users (id) + where response_code = 451; + create index i_log_users_response_code_500 on s_tables.t_log_users (id) where response_code = 500; @@ -112,19 +82,19 @@ create index i_log_users_response_code_redirects on s_tables.t_log_users (id) where response_code in (301, 302, 303, 307, 308); create index i_log_users_response_code_notable on s_tables.t_log_users (id) - where response_code in (400, 403, 404, 410, 500, 503); + where response_code in (400, 403, 404, 410, 451, 500, 503); /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_users_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not) - select id, id_user, log_title, log_type, log_severity, log_details, log_date, request_client, response_code from s_tables.t_log_users + select id, id_user, log_title, log_type, log_type_sub, log_severity, log_facility, log_details, log_date, request_client, response_code from s_tables.t_log_users where id_user in (select * from this_user); grant select on s_users.v_log_users_self to r_reservation, r_reservation_system; create view s_users.v_log_users_self_insert with (security_barrier=true) as - select log_title, log_type, log_severity, log_details, request_client, response_code from s_tables.t_log_users + select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users where id_user in (select id from public.v_users_self_locked_not) with check option; @@ -133,7 +103,7 @@ grant insert on s_users.v_log_users_self_insert to r_reservation, r_reservation_ /** public users should be able to insert, but should never be able to view the logs that they insert. **/ create view public.v_log_users_self_insert with (security_barrier=true) as - select log_title, log_type, log_severity, log_details, request_client, response_code from s_tables.t_log_users + select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users where 'r_reservation_public' in (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_reservation_public') with check option; @@ -154,7 +124,7 @@ create table s_tables.t_log_user_activity ( request_path varchar(512) not null, request_arguments varchar(512) not null, - request_date timestamp default localtimestamp not null, + request_date timestamp with time zone default current_timestamp not null, request_client public.ct_client not null, request_headers json, @@ -189,6 +159,9 @@ create index i_log_user_activity_response_code_404 on s_tables.t_log_user_activi create index i_log_user_activity_response_code_410 on s_tables.t_log_user_activity (id) where response_code = 410; +create index i_log_user_activity_response_code_451 on s_tables.t_log_user_activity (id) + where response_code = 451; + create index i_log_user_activity_response_code_5xx on s_tables.t_log_user_activity (id) where response_code >= 500 and response_code < 600; @@ -199,7 +172,7 @@ create index i_log_user_activity_response_code_503 on s_tables.t_log_user_activi where response_code = 503; create index i_log_user_activity_response_code_notable on s_tables.t_log_user_activity (id) - where response_code in (403, 404, 410, 500, 503); + where response_code in (403, 404, 410, 451, 500, 503); diff --git a/database/sql/reservation/reservation-main.sql b/database/sql/reservation/reservation-main.sql index d1fded8..f4b843a 100644 --- a/database/sql/reservation/reservation-main.sql +++ b/database/sql/reservation/reservation-main.sql @@ -12,6 +12,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -77,15 +78,15 @@ create type public.ct_location as ( ); create type public.ct_date as ( - date timestamp, - time_start timestamp, - time_stop timestamp + date timestamp with time zone, + time_start timestamp with time zone, + time_stop timestamp with time zone ); create type public.ct_date_context as ( - date timestamp, - time_start timestamp, - time_stop timestamp, + date timestamp with time zone, + time_start timestamp with time zone, + time_stop timestamp with time zone, context bigint ); diff --git a/database/sql/reservation/reservation-paths.sql b/database/sql/reservation/reservation-paths.sql index c005ad6..529cbfb 100644 --- a/database/sql/reservation/reservation-paths.sql +++ b/database/sql/reservation/reservation-paths.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -37,10 +38,10 @@ create table s_tables.t_paths ( field_destination varchar(256), field_response_code smallint, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_paths primary key (id), diff --git a/database/sql/reservation/reservation-requests.sql b/database/sql/reservation/reservation-requests.sql index fbe4fec..b82f730 100644 --- a/database/sql/reservation/reservation-requests.sql +++ b/database/sql/reservation/reservation-requests.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -22,10 +23,10 @@ create table s_tables.t_request_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cu_request_types_id unique (id), constraint cu_request_types_name_machine unique (name_machine), @@ -80,15 +81,15 @@ create table s_tables.t_requests ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_approved timestamp, - date_denied timestamp, - date_troubled timestamp, - date_cancelled timestamp, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_approved timestamp with time zone, + date_denied timestamp with time zone, + date_troubled timestamp with time zone, + date_cancelled timestamp with time zone, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_additional text not null, field_dates public.ct_date_context[] not null, @@ -445,7 +446,7 @@ create table s_tables.t_request_revisions ( is_locked boolean, is_deleted boolean, - date_changed timestamp default localtimestamp not null, + date_changed timestamp with time zone default current_timestamp not null, field_additional text, field_dates public.ct_date_context[], diff --git a/database/sql/reservation/reservation-statistics.sql b/database/sql/reservation/reservation-statistics.sql index 1abab6a..1d27554 100644 --- a/database/sql/reservation/reservation-statistics.sql +++ b/database/sql/reservation/reservation-statistics.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -18,9 +19,9 @@ create table s_tables.t_statistics_http_status_codes ( is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_statistics_http_status_codes primary key (code), @@ -73,9 +74,9 @@ create table s_tables.t_statistics_request_path ( is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_statistics_request_path primary key (path), diff --git a/database/sql/reservation/reservation-types.sql b/database/sql/reservation/reservation-types.sql index a763875..83d4df9 100644 --- a/database/sql/reservation/reservation-types.sql +++ b/database/sql/reservation/reservation-types.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -21,10 +22,10 @@ create table s_tables.t_type_http_status_codes ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_log_type_http_status_codes primary key (id), @@ -62,10 +63,10 @@ create table s_tables.t_type_mime_categorys ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_category varchar(64), @@ -112,10 +113,10 @@ create table s_tables.t_type_mime_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_extension varchar(64), field_mime varchar(128), diff --git a/database/sql/reservation/reservation-users.sql b/database/sql/reservation/reservation-users.sql index b1c027b..7ec5db5 100644 --- a/database/sql/reservation/reservation-users.sql +++ b/database/sql/reservation/reservation-users.sql @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -42,11 +43,11 @@ create table s_tables.t_users ( can_manage_roles boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, settings json, diff --git a/database/sql/reservation/reservation-workflow.sql b/database/sql/reservation/reservation-workflow.sql index 00ac7cc..53eb1db 100644 --- a/database/sql/reservation/reservation-workflow.sql +++ b/database/sql/reservation/reservation-workflow.sql @@ -8,5 +8,6 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; /* @todo: implement this and add appropriate foreign keys to the t_requests and related tables. */ diff --git a/database/sql/standard/standard-files.sql b/database/sql/standard/standard-files.sql index 74bb480..0ab9ab2 100644 --- a/database/sql/standard/standard-files.sql +++ b/database/sql/standard/standard-files.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Files **/ -/** This depends on: reservation-users.sql, reservation-types.sql **/ +/** This depends on: standard-users.sql, standard-types.sql **/ start transaction; @@ -8,7 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; - +set timezone to UTC; create table s_tables.t_files ( @@ -29,10 +29,10 @@ create table s_tables.t_files ( field_data bytea not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_files primary key (id), diff --git a/database/sql/standard/standard-first.sql b/database/sql/standard/standard-first.sql index 9b762ca..0b699f2 100644 --- a/database/sql/standard/standard-first.sql +++ b/database/sql/standard/standard-first.sql @@ -1,7 +1,7 @@ /** First time or one time execution stuff **/ /** Things here must be run first and cannot be run a second time due to their nature. **/ /** For example, tablespaces need only be created 1x and then any database on the system can use them **/ -/** Be sure to replace reservation_ and reservation- with the prefix that is specific to your database. **/ +/** Be sure to replace standard_ and standard- with the prefix that is specific to your database. **/ /** This script creates the database (at or near the end of the script). **/ @@ -77,6 +77,7 @@ create database standard; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; /* Make sure public is never allowed to create tables! */ diff --git a/database/sql/standard/standard-groups.sql b/database/sql/standard/standard-groups.sql index 9dc837b..2efb960 100644 --- a/database/sql/standard/standard-groups.sql +++ b/database/sql/standard/standard-groups.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Groups */ -/** This depends on: reservation-users.sql **/ +/** This depends on: standard-users.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; /* Note about composite groups (is_composite) @@ -37,11 +38,11 @@ create table s_tables.t_groups ( can_manage_paths boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, settings json, @@ -159,10 +160,10 @@ create table s_tables.t_group_users ( id_user bigint not null, id_group bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, is_locked boolean default false not null, is_deleted boolean default false not null, @@ -221,10 +222,10 @@ create table s_tables.t_group_composites ( id_composite bigint not null, id_group bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, is_locked boolean default false not null, is_deleted boolean default false not null, diff --git a/database/sql/standard/standard-last.sql b/database/sql/standard/standard-last.sql index 3a2fb9c..9538bcf 100644 --- a/database/sql/standard/standard-last.sql +++ b/database/sql/standard/standard-last.sql @@ -1,65 +1,75 @@ /** Standardized SQL Structure - Last */ /** This depends on: everything (run this absolutely last) **/ - /** The purpose of this is to add all initial data after all appropriate triggers are defined. ***/ start transaction; +/** Custom database specific settings (do this on every connection made) **/ +set bytea_output to hex; +set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; +set datestyle to us; +set timezone to UTC; + + /*** start the sequence count at 1000 to allow for < 1000 to be reserved for special uses ***/ alter sequence s_tables.se_log_types_id start 1000; alter sequence s_tables.se_log_types_id restart; -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ insert into s_tables.t_log_types (id, name_machine, name_human) values (0, 'none', 'None'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (1, 'php', 'PHP'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (2, 'theme', 'Theme'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (3, 'cache', 'Cache'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (4, 'javascript', 'Javascript'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (5, 'ajax', 'AJAX'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (6, 'sql', 'SQL'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (7, 'form', 'Form'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (8, 'form_validate', 'Form Validation'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (9, 'form_submit', 'Form Submit'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (10, 'load', 'Load'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (11, 'save', 'Save'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (12, 'render', 'Render'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (13, 'client', 'Client'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (14, 'add', 'Add'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (15, 'create', 'Create'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (16, 'change', 'Change'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (17, 'delete', 'Delete'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (18, 'redirect', 'Redirect'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (19, 'login', 'Login'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (20, 'logout', 'Logout'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (21, 'session', 'Session'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (22, 'database', 'Database'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (23, 'not_found', 'Not Found'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (24, 'access_denied', 'Access Denied'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (25, 'removed', 'Removed'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (26, 'locked', 'Locked'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (27, 'timeout', 'Timeout'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (28, 'expire', 'Expiration'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (29, 'user', 'User'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (30, 'error', 'Error'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (31, 'content', 'Content'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (32, 'workflow', 'Workflow'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (33, 'draft', 'Draft'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (34, 'clone', 'Clone'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (35, 'publish', 'Publish'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (36, 'revert', 'Revert'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (37, 'validate', 'Validate'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (38, 'approve', 'Approve'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (39, 'password', 'Password'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (40, 'revision', 'Revision'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (41, 'search', 'Search'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (42, 'access', 'Access'); -insert into s_tables.t_log_types (id, name_machine, name_human) values (43, 'unknown', 'Unknown'); - - - -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ +insert into s_tables.t_log_types (id, name_machine, name_human) values (1, 'access', 'Access'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (2, 'accept', 'Accept'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (3, 'amend', 'Amend'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (4, 'approve', 'Approve'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (5, 'audit', 'Audit'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (6, 'base', 'Base'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (7, 'cache', 'Cache'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (8, 'cancel', 'Cancel'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (9, 'create', 'Create'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (10, 'client', 'Client'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (11, 'connect', 'Connect'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (12, 'content', 'Content'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (13, 'comment', 'Comment'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (14, 'databae', 'Database'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (15, 'delete', 'Delete'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (16, 'deny', 'Deny'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (17, 'draft', 'Draft'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (18, 'disprove', 'Disprove'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (19, 'downgrade', 'Downgrade'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (20, 'edit', 'Edit'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (21, 'event', 'Event'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (22, 'file', 'File'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (23, 'interpretor', 'Interpretor'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (24, 'legal', 'Legal'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (25, 'lock', 'Lock'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (26, 'mail', 'Mail'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (27, 'proxy', 'Proxy'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (28, 'publish', 'Publish'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (29, 'response', 'Response'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (30, 'restore', 'Restore'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (31, 'request', 'Request'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (32, 'revert', 'Revert'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (33, 'review', 'Review'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (34, 'schedule', 'Schedule'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (35, 'search', 'Search'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (36, 'session', 'Session'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (37, 'sign', 'Sign'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (38, 'synchronize', 'Synchronize'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (39, 'system', 'Systen'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (40, 'theme', 'Theme'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (41, 'time', 'Time'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (42, 'transition', 'Transition'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (43, 'uncancel', 'Uncancel'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (44, 'undo', 'Undo'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (45, 'unpublish', 'Unpublish'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (46, 'update', 'Update'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (47, 'upgrade', 'upgrade'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (48, 'user', 'User'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (49, 'void', 'Void'); +insert into s_tables.t_log_types (id, name_machine, name_human) values (50, 'workflow', 'Workflow'); + + insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (0, '0', 'Undefined'); insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (1, '1', 'Invalid'); insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) values (2, '2', 'Unknown'); @@ -130,20 +140,51 @@ insert into s_tables.t_type_http_status_codes (id, name_machine, name_human) val /*** start the sequence count at 1000 to allow for < 1000 to be reserved for special uses ***/ -alter sequence s_tables.se_log_type_severity_levels_id start 1000; -alter sequence s_tables.se_log_type_severity_levels_id restart; - - -/** create well known types that can then be user for indexes (all new types added should be considered for custom indexing). **/ -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (0, 'none', 'None'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (1, 'information', 'Information'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (2, 'notice', 'Notice'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (3, 'debug', 'Debug'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (4, 'warning', 'Warning'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (5, 'error', 'Error'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (6, 'alert', 'Alert'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (7, 'critical', 'Critical'); -insert into s_tables.t_log_type_severity_levels (id, name_machine, name_human) values (8, 'emergency', 'Emergency'); +alter sequence s_tables.se_log_type_severitys_id start 1000; +alter sequence s_tables.se_log_type_severitys_id restart; + + +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (0, 'none', 'None'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (1, 'emergency', 'Emergency'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (2, 'alert', 'Alert'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (3, 'critical', 'Critical'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (4, 'error', 'Error'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (5, 'warning', 'Warning'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (6, 'notice', 'Notice'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (7, 'information', 'Information'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (8, 'debug', 'Debug'); +insert into s_tables.t_log_type_severitys (id, name_machine, name_human) values (9, 'unknown', 'Unknown'); + + +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (0, 'none', 'None'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (1, 'kernel', 'Kernel'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (2, 'user', 'User'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (3, 'mail', 'Mail'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (4, 'daemin', 'Daemon'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (5, 'security', 'Security'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (6, 'messages', 'Messages'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (7, 'printer', 'Printer'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (8, 'network', 'Network'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (9, 'uucp', 'UUCP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (10, 'clock', 'Clock'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (11, 'authorization', 'Authorization'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (12, 'ftp', 'FTP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (13, 'ntp', 'NTP'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (14, 'audit', 'Audit'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (15, 'alert', 'Alert'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (16, 'cron', 'Cron'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (17, 'local_0', 'Local 0'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (18, 'local_1', 'Local 1'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (19, 'local_2', 'Local 2'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (20, 'local_3', 'Local 3'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (21, 'local_4', 'Local 4'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (22, 'local_5', 'Local 5'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (23, 'local_6', 'Local 6'); +insert into s_tables.t_log_type_facilitys (id, name_machine, name_human) values (24, 'local_7', 'Local 7'); + +alter table s_tables.t_log_users alter column log_facility set default 17; +alter table s_tables.t_log_groups alter column log_facility set default 17; +alter table s_tables.t_log_group_users alter column log_facility set default 17; diff --git a/database/sql/standard/standard-log_groups.sql b/database/sql/standard/standard-log_groups.sql index 929d58a..12811f9 100644 --- a/database/sql/standard/standard-log_groups.sql +++ b/database/sql/standard/standard-log_groups.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Logs - Groups */ -/** This depends on: reservation-groups.sql **/ +/** This depends on: standard-groups.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,8 +20,11 @@ create table s_tables.t_log_groups ( id_group bigint not null, log_type bigint not null, + log_type_sub bigint not null, + log_severity bigint not null, + log_facility bigint not null, log_details json, - log_date timestamp default localtimestamp not null, + log_date timestamp with time zone default current_timestamp not null, constraint cp_log_groups primary key (id), @@ -29,7 +33,10 @@ create table s_tables.t_log_groups ( constraint cf_log_groups_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_groups_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_groups_id_group foreign key (id_group) references s_tables.t_groups (id) on delete restrict on update cascade, - constraint cf_log_groups_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade + constraint cf_log_groups_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_groups_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_groups_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_groups_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade ); create sequence s_tables.se_log_groups_id owned by s_tables.t_log_groups.id; @@ -42,13 +49,13 @@ grant usage on s_tables.se_log_groups_id to r_standard, r_standard_system; /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_groups_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not) - select id, id_user, id_group, log_type, log_details, log_date from s_tables.t_log_groups + select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_details, log_date from s_tables.t_log_groups where id_user in (select * from this_user); grant select on s_users.v_log_groups_self to r_standard, r_standard_system; create view s_users.v_log_groups_self_insert with (security_barrier=true) as - select id_group, log_type, log_details from s_tables.t_log_groups + select id_group, log_type, log_type_sub, log_severity, log_facility, log_details from s_tables.t_log_groups where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; @@ -73,7 +80,10 @@ create table s_tables.t_log_group_users ( id_group bigint not null, log_type bigint not null, - log_date timestamp default localtimestamp not null, + log_type_sub bigint not null, + log_severity bigint not null, + log_facility bigint not null, + log_date timestamp with time zone default current_timestamp not null, constraint cp_log_group_users primary key (id), @@ -82,7 +92,10 @@ create table s_tables.t_log_group_users ( constraint cf_log_group_users_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_group_users_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_group_users_id_group foreign key (id_group) references s_tables.t_groups (id) on delete restrict on update cascade, - constraint cf_log_group_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade + constraint cf_log_group_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_group_users_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade ); create sequence s_tables.se_log_group_users_id owned by s_tables.t_log_group_users.id; @@ -96,13 +109,13 @@ grant usage on s_tables.se_log_group_users_id to r_standard, r_standard_system; create view s_users.v_log_group_users_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not), allowed_groups as (select id from s_users.v_groups_self where not is_locked) - select id, id_user, id_group, log_type, log_date from s_tables.t_log_group_users + select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_date from s_tables.t_log_group_users where id_user in (select * from this_user) or id_group in (select * from allowed_groups); grant select on s_users.v_log_group_users_self to r_standard, r_standard_system; create view s_users.v_log_group_users_self_insert with (security_barrier=true) as - select id_group, log_type from s_tables.t_log_group_users + select id_group, log_type, log_type_sub, log_severity, log_facility from s_tables.t_log_group_users where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; diff --git a/database/sql/standard/standard-log_problems.sql b/database/sql/standard/standard-log_problems.sql index c91dcb2..ca728c8 100644 --- a/database/sql/standard/standard-log_problems.sql +++ b/database/sql/standard/standard-log_problems.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Logs - Problems */ -/** This depends on: reservation-users.sql **/ +/** This depends on: standard-users.sql **/ /* The problem logs are intended for temporary reporting of problems and are meant to allow permanent deletion. */ start transaction; @@ -9,6 +9,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,8 +20,8 @@ create table s_tables.t_log_problems ( name_machine varchar(128) not null, name_human varchar(256) not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, constraint cp_log_problems primary key (id), @@ -45,8 +46,8 @@ create table s_tables.t_log_problems_users ( id_user bigint not null, id_user_session bigint not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, log_details json, diff --git a/database/sql/standard/standard-log_types.sql b/database/sql/standard/standard-log_types.sql index 557693d..8590b8a 100644 --- a/database/sql/standard/standard-log_types.sql +++ b/database/sql/standard/standard-log_types.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Logs - Types */ -/** This depends on: reservation-users.sql **/ +/** This depends on: standard-users.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -21,9 +22,9 @@ create table s_tables.t_log_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_log_types primary key (id), @@ -59,7 +60,7 @@ create trigger tr_log_types_date_changed_deleted_or_locked /*** provide log severity level id and names ***/ -create table s_tables.t_log_type_severity_levels ( +create table s_tables.t_log_type_severitys ( id bigint not null, name_machine varchar(128) not null, name_human varchar(256) not null, @@ -67,34 +68,75 @@ create table s_tables.t_log_type_severity_levels ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, - constraint cp_log_type_severity_levels primary key (id), + constraint cp_log_type_severitys primary key (id), - constraint cu_log_type_severity_levels_user unique (name_machine), + constraint cu_log_type_severitys_user unique (name_machine), - constraint cc_log_type_severity_levels_id check (id >= 0) + constraint cc_log_type_severitys_id check (id >= 0) ); -create sequence s_tables.se_log_type_severity_levels_id owned by s_tables.t_log_type_severity_levels.id; -alter table s_tables.t_log_type_severity_levels alter column id set default nextval('s_tables.se_log_type_severity_levels_id'::regclass); +create sequence s_tables.se_log_type_severitys_id owned by s_tables.t_log_type_severitys.id; +alter table s_tables.t_log_type_severitys alter column id set default nextval('s_tables.se_log_type_severitys_id'::regclass); -grant select,insert,update on s_tables.t_log_type_severity_levels to r_standard_administer; -grant select on s_tables.t_log_type_severity_levels to r_standard_manager, r_standard_auditor; -grant select,usage on s_tables.se_log_type_severity_levels_id to r_standard_administer; +grant select,insert,update on s_tables.t_log_type_severitys to r_standard_administer; +grant select on s_tables.t_log_type_severitys to r_standard_manager, r_standard_auditor; +grant select,usage on s_tables.se_log_type_severitys_id to r_standard_administer; -create view s_users.v_log_type_severity_levels with (security_barrier=true) as - select id, name_machine, name_human from s_tables.t_log_type_severity_levels +create view s_users.v_log_type_severitys with (security_barrier=true) as + select id, name_machine, name_human from s_tables.t_log_type_severitys where not is_deleted; -grant select on s_users.v_log_type_severity_levels to r_standard, r_standard_public, r_standard_system; +grant select on s_users.v_log_type_severitys to r_standard, r_standard_public, r_standard_system; -create trigger tr_log_type_severity_levels_date_changed_deleted_or_locked - before update on s_tables.t_log_type_severity_levels +create trigger tr_log_type_severitys_date_changed_deleted_or_locked + before update on s_tables.t_log_type_severitys + for each row execute procedure s_administers.f_common_update_date_changed_deleted_or_locked(); + + + +/*** provide log facility level id and names ***/ +create table s_tables.t_log_type_facilitys ( + id bigint not null, + name_machine varchar(128) not null, + name_human varchar(256) not null, + + is_locked boolean default false not null, + is_deleted boolean default false not null, + + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, + + constraint cp_log_type_facilitys primary key (id), + + constraint cu_log_type_facilitys_user unique (name_machine), + + constraint cc_log_type_facilitys_id check (id >= 0) +); + +create sequence s_tables.se_log_type_facilitys_id owned by s_tables.t_log_type_facilitys.id; +alter table s_tables.t_log_type_facilitys alter column id set default nextval('s_tables.se_log_type_facilitys_id'::regclass); + +grant select,insert,update on s_tables.t_log_type_facilitys to r_standard_administer; +grant select on s_tables.t_log_type_facilitys to r_standard_manager, r_standard_auditor; +grant select,usage on s_tables.se_log_type_facilitys_id to r_standard_administer; + +create view s_users.v_log_type_facilitys with (security_barrier=true) as + select id, name_machine, name_human from s_tables.t_log_type_facilitys + where not is_deleted; + +grant select on s_users.v_log_type_facilitys to r_standard, r_standard_public, r_standard_system; + + +create trigger tr_log_type_facilitys_date_changed_deleted_or_locked + before update on s_tables.t_log_type_facilitys for each row execute procedure s_administers.f_common_update_date_changed_deleted_or_locked(); diff --git a/database/sql/standard/standard-log_users.sql b/database/sql/standard/standard-log_users.sql index 2d2bf91..daff46c 100644 --- a/database/sql/standard/standard-log_users.sql +++ b/database/sql/standard/standard-log_users.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Logs */ -/** This depends on: reservation-users.sql, reservation-types.sql **/ +/** This depends on: standard-users.sql, standard-types.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -19,9 +20,11 @@ create table s_tables.t_log_users ( log_title varchar(512) not null, log_type bigint not null, + log_type_sub bigint not null, log_severity bigint not null, + log_facility bigint not null, log_details json, - log_date timestamp default localtimestamp not null, + log_date timestamp with time zone default current_timestamp not null, request_client public.ct_client not null, response_code smallint not null default 0, @@ -34,7 +37,9 @@ create table s_tables.t_log_users ( constraint cf_log_users_id_user foreign key (id_user) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_users_id_user_session foreign key (id_user_session) references s_tables.t_users (id) on delete restrict on update cascade, constraint cf_log_users_log_type foreign key (log_type) references s_tables.t_log_types (id) on delete restrict on update cascade, - constraint cf_log_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severity_levels (id) on delete restrict on update cascade, + constraint cf_log_users_log_type_sub foreign key (log_type_sub) references s_tables.t_log_types (id) on delete restrict on update cascade, + constraint cf_log_users_log_severity foreign key (log_severity) references s_tables.t_log_type_severitys (id) on delete restrict on update cascade, + constraint cf_log_users_log_facility foreign key (log_facility) references s_tables.t_log_type_facilitys (id) on delete restrict on update cascade, constraint cf_log_users_response_code foreign key (response_code) references s_tables.t_type_http_status_codes (id) on delete restrict on update cascade ); @@ -45,51 +50,13 @@ grant select on s_tables.t_log_users to r_standard_manager, r_standard_auditor; grant select,usage on s_tables.se_log_users_id to r_standard_administer; grant usage on s_tables.se_log_users_id to r_standard, r_standard_public, r_standard_system; -create index i_log_users_type_php on s_tables.t_log_users (id) - where log_type = 1; - -create index i_log_users_type_theme on s_tables.t_log_users (id) - where log_type = 2; - -create index i_log_users_type_cache on s_tables.t_log_users (id) - where log_type = 3; - -create index i_log_users_type_javascript on s_tables.t_log_users (id) - where log_type = 4; - -create index i_log_users_type_ajax on s_tables.t_log_users (id) - where log_type = 5; - -create index i_log_users_type_sql on s_tables.t_log_users (id) - where log_type = 6; - -create index i_log_users_type_redirect on s_tables.t_log_users (id) - where log_type = 16; - -create index i_log_users_type_login on s_tables.t_log_users (id) - where log_type = 17; - -create index i_log_users_type_logout on s_tables.t_log_users (id) - where log_type = 18; - -create index i_log_users_type_user on s_tables.t_log_users (id) - where log_type = 27; - -create index i_log_users_type_error on s_tables.t_log_users (id) - where log_type = 28; - -create index i_log_users_type_content on s_tables.t_log_users (id) - where log_type = 29; - -create index i_log_users_type_workflow on s_tables.t_log_users (id) - where log_type = 30; - -create index i_log_users_type_search on s_tables.t_log_users (id) - where log_type = 39; create index i_log_users_response_code_200 on s_tables.t_log_users (id) where response_code = 200; +create index i_log_users_response_code_400 on s_tables.t_log_users (id) + where response_code = 400; + create index i_log_users_response_code_403 on s_tables.t_log_users (id) where response_code = 403; @@ -99,6 +66,9 @@ create index i_log_users_response_code_404 on s_tables.t_log_users (id) create index i_log_users_response_code_410 on s_tables.t_log_users (id) where response_code = 410; +create index i_log_users_response_code_451 on s_tables.t_log_users (id) + where response_code = 451; + create index i_log_users_response_code_500 on s_tables.t_log_users (id) where response_code = 500; @@ -112,19 +82,19 @@ create index i_log_users_response_code_redirects on s_tables.t_log_users (id) where response_code in (301, 302, 303, 307, 308); create index i_log_users_response_code_notable on s_tables.t_log_users (id) - where response_code in (400, 403, 404, 410, 500, 503); + where response_code in (400, 403, 404, 410, 451, 500, 503); /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_users_self with (security_barrier=true) as with this_user as (select id from public.v_users_self_locked_not) - select id, id_user, log_title, log_type, log_severity, log_details, log_date, request_client, response_code from s_tables.t_log_users + select id, id_user, log_title, log_type, log_type_sub, log_severity, log_facility, log_details, log_date, request_client, response_code from s_tables.t_log_users where id_user in (select * from this_user); grant select on s_users.v_log_users_self to r_standard, r_standard_system; create view s_users.v_log_users_self_insert with (security_barrier=true) as - select log_title, log_type, log_severity, log_details, request_client, response_code from s_tables.t_log_users + select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users where id_user in (select id from public.v_users_self_locked_not) with check option; @@ -133,7 +103,7 @@ grant insert on s_users.v_log_users_self_insert to r_standard, r_standard_system /** public users should be able to insert, but should never be able to view the logs that they insert. **/ create view public.v_log_users_self_insert with (security_barrier=true) as - select log_title, log_type, log_severity, log_details, request_client, response_code from s_tables.t_log_users + select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users where 'r_standard_public' in (select pr.rolname from pg_auth_members pam inner join pg_roles pr on (pam.roleid = pr.oid) inner join pg_roles pr_u on (pam.member = pr_u.oid) where pr_u.rolname = current_user and pr.rolname = 'r_standard_public') with check option; @@ -154,7 +124,7 @@ create table s_tables.t_log_user_activity ( request_path varchar(512) not null, request_arguments varchar(512) not null, - request_date timestamp default localtimestamp not null, + request_date timestamp with time zone default current_timestamp not null, request_client public.ct_client not null, request_headers json, @@ -189,6 +159,9 @@ create index i_log_user_activity_response_code_404 on s_tables.t_log_user_activi create index i_log_user_activity_response_code_410 on s_tables.t_log_user_activity (id) where response_code = 410; +create index i_log_user_activity_response_code_451 on s_tables.t_log_user_activity (id) + where response_code = 451; + create index i_log_user_activity_response_code_5xx on s_tables.t_log_user_activity (id) where response_code >= 500 and response_code < 600; @@ -199,7 +172,7 @@ create index i_log_user_activity_response_code_503 on s_tables.t_log_user_activi where response_code = 503; create index i_log_user_activity_response_code_notable on s_tables.t_log_user_activity (id) - where response_code in (403, 404, 410, 500, 503); + where response_code in (403, 404, 410, 451, 500, 503); diff --git a/database/sql/standard/standard-main.sql b/database/sql/standard/standard-main.sql index d29dae9..54df364 100644 --- a/database/sql/standard/standard-main.sql +++ b/database/sql/standard/standard-main.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Main */ -/** This depends on: reservation-first.sql **/ +/** This depends on: standard-first.sql **/ /* @todo: add restrictions to even managers and administers so that ALL users access via views to allow for disabling any account (even an admin). only the postgresql/root account may access tables directly. This requires changing permissions and adding the appropriate s_administers and s_managers tables. @@ -12,6 +12,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -77,15 +78,15 @@ create type public.ct_location as ( ); create type public.ct_date as ( - date timestamp, - time_start timestamp, - time_stop timestamp + date timestamp with time zone, + time_start timestamp with time zone, + time_stop timestamp with time zone ); create type public.ct_date_context as ( - date timestamp, - time_start timestamp, - time_stop timestamp, + date timestamp with time zone, + time_start timestamp with time zone, + time_stop timestamp with time zone, context bigint ); diff --git a/database/sql/standard/standard-paths.sql b/database/sql/standard/standard-paths.sql index 89ce7e7..37b8afb 100644 --- a/database/sql/standard/standard-paths.sql +++ b/database/sql/standard/standard-paths.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Content **/ -/** This depends on: reservation-groups.sql, reservation-types.sql **/ +/** This depends on: standard-groups.sql, standard-types.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -37,10 +38,10 @@ create table s_tables.t_paths ( field_destination varchar(256), field_response_code smallint, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_paths primary key (id), diff --git a/database/sql/standard/standard-statistics.sql b/database/sql/standard/standard-statistics.sql index e6fa698..2ef9788 100644 --- a/database/sql/standard/standard-statistics.sql +++ b/database/sql/standard/standard-statistics.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Statistics **/ -/** This depends on: reservation-log_users.sql **/ +/** This depends on: standard-log_users.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -18,9 +19,9 @@ create table s_tables.t_statistics_http_status_codes ( is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_statistics_http_status_codes primary key (code), @@ -73,9 +74,9 @@ create table s_tables.t_statistics_request_path ( is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_deleted timestamp with time zone, constraint cp_statistics_request_path primary key (path), diff --git a/database/sql/standard/standard-types.sql b/database/sql/standard/standard-types.sql index 9e3659f..d7e4fa2 100644 --- a/database/sql/standard/standard-types.sql +++ b/database/sql/standard/standard-types.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Logs - Types */ -/** This depends on: reservation-main.sql **/ +/** This depends on: standard-main.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -21,10 +22,10 @@ create table s_tables.t_type_http_status_codes ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, constraint cp_log_type_http_status_codes primary key (id), @@ -62,10 +63,10 @@ create table s_tables.t_type_mime_categorys ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_category varchar(64), @@ -112,10 +113,10 @@ create table s_tables.t_type_mime_types ( is_locked boolean default false not null, is_deleted boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, field_extension varchar(64), field_mime varchar(128), diff --git a/database/sql/standard/standard-users.sql b/database/sql/standard/standard-users.sql index 1704ec8..827142a 100644 --- a/database/sql/standard/standard-users.sql +++ b/database/sql/standard/standard-users.sql @@ -1,5 +1,5 @@ /** Standardized SQL Structure - Users */ -/** This depends on: reservation-main.sql **/ +/** This depends on: standard-main.sql **/ start transaction; @@ -8,6 +8,7 @@ start transaction; set bytea_output to hex; set search_path to s_administers,s_managers,s_auditors,s_publishers,s_insurers,s_financers,s_reviewers,s_editors,s_drafters,s_requesters,s_users,public; set datestyle to us; +set timezone to UTC; @@ -42,11 +43,11 @@ create table s_tables.t_users ( can_manage_roles boolean default false not null, - date_created timestamp default localtimestamp not null, - date_changed timestamp default localtimestamp not null, - date_synced timestamp default localtimestamp not null, - date_locked timestamp, - date_deleted timestamp, + date_created timestamp with time zone default current_timestamp not null, + date_changed timestamp with time zone default current_timestamp not null, + date_synced timestamp with time zone default current_timestamp not null, + date_locked timestamp with time zone, + date_deleted timestamp with time zone, settings json, @@ -284,7 +285,7 @@ create function s_administers.f_users_update_actions() returns trigger as $$ $$ language plpgsql; -/* attempt to auto-manage postgresql reservation roles with the reservation database user roles. */ +/* attempt to auto-manage postgresql standard roles with the standard database user roles. */ /* user ids 1 and 2 are explicitly reserved for anonymous/public and the database postgresql accounts. */ /* postgresql does not seem to support variables for the user with grant and revoke, therefore the execute statement is used to perform the query. */ /* @fixme: the name_machine must be forcibly sanitized to be alphanumeric, -, or _ in all cases. */ diff --git a/documentation/database.txt b/documentation/database.txt index fbdd867..a43aabd 100644 --- a/documentation/database.txt +++ b/documentation/database.txt @@ -63,3 +63,10 @@ For database names, the following naming scheme is used: - r_: This represents a role. - u_: This represents a user (except for ldap users, whose names are exactly as defined in ldap, and the root postgresql user, usually postgres). - se_: This represents a sequence. + + +Timezones. +The database should have its timezones always set to UTC, for consistency and integrity reasons. +If at any point in time, a specific timezone is needed, then "at time zone" should be added. +Example of selecting the created date with timezone set to America/Chicago.: + select id, created_date at time zone 'America/Chicago' from s_tables.t_some_table; diff --git a/program/reservation/reservation_defaults_global.php b/program/reservation/reservation_defaults_global.php index 01f8ba1..1334bf9 100644 --- a/program/reservation/reservation_defaults_global.php +++ b/program/reservation/reservation_defaults_global.php @@ -39,6 +39,9 @@ class c_base_defaults_global { // reserved path groups: array(97, 99, 100, 102, 109, 115, 116, 120, 121). const RESERVED_PATH_GROUP = array(c_base_ascii::LOWER_A, c_base_ascii::LOWER_C, c_base_ascii::LOWER_D, c_base_ascii::LOWER_F, c_base_ascii::LOWER_M, c_base_ascii::LOWER_S, c_base_ascii::LOWER_T, c_base_ascii::LOWER_U, c_base_ascii::LOWER_X); + // default log facility (17 = c_base_error::FACILITY_LOCAL_0). + const LOG_FACILITY = 17; + // Represents the current timestamp of this PHP process/session, see: self::s_get_timestamp_session(). private static $s_timestamp_session = NULL; -- 1.8.3.1