From ceb1b2243199d983a80ba9c8556f25e37d238d9a Mon Sep 17 00:00:00 2001 From: Kevin Day Date: Tue, 25 Jul 2017 16:30:32 -0500 Subject: [PATCH] Cleanup: rename and relocate request path statistics database table The context of the statistics is misleading, so rename the database and all related content to have HTTP in the name. Move the code into the standard statistics sql file. --- .../sql/reservation/reservation-permissions.sql | 13 ++--- .../sql/reservation/reservation-statistics.sql | 56 ---------------------- database/sql/standard/standard-permissions.sql | 7 +-- database/sql/standard/standard-statistics.sql | 56 ++++++++++++++++++++++ 4 files changed, 67 insertions(+), 65 deletions(-) diff --git a/database/sql/reservation/reservation-permissions.sql b/database/sql/reservation/reservation-permissions.sql index 97ef023..239129d 100644 --- a/database/sql/reservation/reservation-permissions.sql +++ b/database/sql/reservation/reservation-permissions.sql @@ -559,12 +559,13 @@ grant insert on public.v_log_user_activity_self_insert to r_reservation_public; /* standard-statistics.sql permissions */ grant select,insert,update on s_tables.t_statistics_http_status_codes to r_reservation_manager, u_reservation_statistics_update; grant select on s_tables.t_statistics_http_status_codes to r_reservation_auditor; -grant select on s_tables.t_statistics_request_path to r_reservation_manager, r_reservation_auditor; +grant select on s_tables.t_statistics_http_request_path to r_reservation_manager, r_reservation_auditor; -grant select,insert,update on s_users.v_statistics_request_path to r_reservation, r_reservation_system; -grant select,insert,update on public.v_statistics_request_path to r_reservation_public; +grant select,insert,update on s_users.v_statistics_http_request_path to r_reservation, r_reservation_system; +grant select,insert,update on public.v_statistics_http_request_path to r_reservation_public; alter function s_tables.f_statistics_http_status_codes_insert () owner to u_reservation_statistics_update; +alter function s_tables.f_statistics_http_request_path_insert () owner to u_reservation_statistics_update; /* reservation-dates permissions */ @@ -638,9 +639,9 @@ alter function s_tables.f_request_revisions_record_revision () owner to u_reserv /* reservation-statistics permissions */ -grant select on s_tables.t_statistics_request_path to r_reservation_manager, r_reservation_auditor; -grant select,insert,update on s_users.v_statistics_request_path to r_reservation, r_reservation_system; -grant select,insert,update on public.v_statistics_request_path to r_reservation_public; +grant select on s_tables.t_statistics_http_request_path to r_reservation_manager, r_reservation_auditor; +grant select,insert,update on s_users.v_statistics_http_request_path to r_reservation, r_reservation_system; +grant select,insert,update on public.v_statistics_http_request_path to r_reservation_public; /* reservation-legal permissions */ diff --git a/database/sql/reservation/reservation-statistics.sql b/database/sql/reservation/reservation-statistics.sql index 2c08f53..9cc6931 100644 --- a/database/sql/reservation/reservation-statistics.sql +++ b/database/sql/reservation/reservation-statistics.sql @@ -12,60 +12,4 @@ set timezone to UTC; -/** Provide request path statistics **/ -create table s_tables.t_statistics_request_path ( - path varchar(512) not null, - count bigint not null default 0, - - 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_deleted timestamp with time zone, - - constraint cp_statistics_request_path primary key (path), - - constraint cc_statistics_request_path_count check (count >= 0) -); - - -/** permissions prevent this from working as desired, so for now open up these stats to the following users (via a view) **/ -/* @todo: review this and try to restrict what accounts can access and set request_path in the same way s_tables.f_statistics_http_status_codes_insert() is handled. */ -create view s_users.v_statistics_request_path with (security_barrier=true) as - select path, count from s_tables.t_statistics_request_path - with check option; - -create view public.v_statistics_request_path with (security_barrier=true) as - select path, count from s_tables.t_statistics_request_path - with check option; - - -/** create an auto-update trigger **/ -create function s_tables.f_statistics_request_path_insert() returns trigger as $$ - begin - if (tg_op = 'INSERT') then - update v_statistics_request_path set count = (select count + 1 as count from v_statistics_request_path where path = new.request_path) where path = new.request_path; - if not found then - insert into v_statistics_request_path (path, count) values (new.request_path, 1); - if not found then return null; end if; - end if; - - return new; - end if; - - return null; - end; -$$ language plpgsql; - - -create trigger tr_statistics_request_path_insert - after insert on s_tables.t_statistics_request_path - for each row execute procedure s_tables.f_statistics_request_path_insert(); - -create trigger tr_statistics_request_path_date_deleted - before update on s_tables.t_statistics_request_path - for each row execute procedure s_administers.f_common_update_date_deleted(); - - - commit transaction; diff --git a/database/sql/standard/standard-permissions.sql b/database/sql/standard/standard-permissions.sql index b32ddd2..75ecdcc 100644 --- a/database/sql/standard/standard-permissions.sql +++ b/database/sql/standard/standard-permissions.sql @@ -559,12 +559,13 @@ grant insert on public.v_log_user_activity_self_insert to r_standard_public; /* standard-statistics.sql permissions */ grant select,insert,update on s_tables.t_statistics_http_status_codes to r_standard_manager, u_standard_statistics_update; grant select on s_tables.t_statistics_http_status_codes to r_standard_auditor; -grant select on s_tables.t_statistics_request_path to r_standard_manager, r_standard_auditor; +grant select on s_tables.t_statistics_http_request_path to r_standard_manager, r_standard_auditor; -grant select,insert,update on s_users.v_statistics_request_path to r_standard, r_standard_system; -grant select,insert,update on public.v_statistics_request_path to r_standard_public; +grant select,insert,update on s_users.v_statistics_http_request_path to r_standard, r_standard_system; +grant select,insert,update on public.v_statistics_http_request_path to r_standard_public; alter function s_tables.f_statistics_http_status_codes_insert () owner to u_standard_statistics_update; +alter function s_tables.f_statistics_http_request_path_insert () owner to u_standard_statistics_update; diff --git a/database/sql/standard/standard-statistics.sql b/database/sql/standard/standard-statistics.sql index afa9748..9bd250d 100644 --- a/database/sql/standard/standard-statistics.sql +++ b/database/sql/standard/standard-statistics.sql @@ -63,4 +63,60 @@ create trigger tr_statistics_http_status_codes_date_deleted +/** Provide request path statistics **/ +create table s_tables.t_statistics_http_request_path ( + path varchar(512) not null, + count bigint not null default 0, + + 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_deleted timestamp with time zone, + + constraint cp_statistics_http_request_path primary key (path), + + constraint cc_statistics_http_request_path_count check (count >= 0) +); + + +/** permissions prevent this from working as desired, so for now open up these stats to the following users (via a view) **/ +/* @todo: review this and try to restrict what accounts can access and set request_path in the same way s_tables.f_statistics_http_status_codes_insert() is handled. */ +create view s_users.v_statistics_http_request_path with (security_barrier=true) as + select path, count from s_tables.t_statistics_http_request_path + with check option; + +create view public.v_statistics_http_request_path with (security_barrier=true) as + select path, count from s_tables.t_statistics_http_request_path + with check option; + + +/** create an auto-update trigger **/ +create function s_tables.f_statistics_http_request_path_insert() returns trigger as $$ + begin + if (tg_op = 'INSERT') then + update v_statistics_http_request_path set count = (select count + 1 as count from v_statistics_http_request_path where path = new.request_path) where path = new.request_path; + if not found then + insert into v_statistics_http_request_path (path, count) values (new.request_path, 1); + if not found then return null; end if; + end if; + + return new; + end if; + + return null; + end; +$$ language plpgsql; + + +create trigger tr_statistics_http_request_path_insert + after insert on s_tables.t_statistics_http_request_path + for each row execute procedure s_tables.f_statistics_http_request_path_insert(); + +create trigger tr_statistics_http_request_path_date_deleted + before update on s_tables.t_statistics_http_request_path + for each row execute procedure s_administers.f_common_update_date_deleted(); + + + commit transaction; -- 1.8.3.1