App-Dochazka-REST
view release on metacpan or search on metacpan
config/sql/dbinit_Config.pm view on Meta::CPAN
set( 'DBINIT_CONNECT_SUPERAUTH', 'bogus_password_to_be_overrided' );
#
# DBINIT_CREATE
#
# A list of SQL statements that are executed when the database is first
# created, to set up the table structure, etc. -- see the create_tables
# subroutine in REST.pm
#
set( 'DBINIT_CREATE', [
# miscellaneous settings
q/SET client_min_messages=WARNING/,
# generalized (utility) functions used in multiple datamodel classes
q#CREATE OR REPLACE FUNCTION round_time(timestamptz)
RETURNS TIMESTAMPTZ AS $$
SELECT date_trunc('hour', $1) + INTERVAL '5 min' * ROUND(date_part('minute', $1) / 5.0)
$$ LANGUAGE sql IMMUTABLE
#,
q#COMMENT ON FUNCTION round_time(timestamptz) IS
'Round a single timestamp value to the nearest 5 minutes'#,
q#CREATE OR REPLACE FUNCTION parens(tstzrange)
RETURNS RECORD AS $$
DECLARE
left_paren text;
right_paren text;
BEGIN
IF lower_inc($1) THEN
left_paren := '['::text;
ELSE
left_paren := '('::text;
END IF;
IF upper_inc($1) THEN
right_paren := ']'::text;
ELSE
right_paren := ')'::text;
END IF;
RETURN (left_paren, right_paren);
END;
$$ LANGUAGE plpgsql#,
q/CREATE OR REPLACE FUNCTION overlaps(tstzrange, tstzrange)
RETURNS boolean AS $$
BEGIN
IF $1 && $2 THEN
RETURN 't'::boolean;
ELSE
RETURN 'f'::boolean;
END IF;
END;
$$ LANGUAGE plpgsql/,
q/COMMENT ON FUNCTION overlaps(tstzrange, tstzrange) IS
'Tests two tstzranges whether they overlap'/,
q/CREATE OR REPLACE FUNCTION not_before_1892(timestamptz)
RETURNS TIMESTAMPTZ AS $IMM$
BEGIN
IF $1 < '1892-01-01'::timestamptz THEN
RAISE EXCEPTION 'No dates earlier than 1892-01-01 please';
END IF;
RETURN $1;
END;
$IMM$ LANGUAGE plpgsql/,
q/COMMENT ON FUNCTION not_before_1892(timestamptz) IS 'We enforce dates 1892-01-01 or later'/,
q#CREATE OR REPLACE FUNCTION valid_intvl() RETURNS trigger AS $$
BEGIN
IF ( NEW.intvl IS NULL ) OR
( isempty(NEW.intvl) ) OR
( lower(NEW.intvl) = '-infinity' ) OR
( lower(NEW.intvl) = 'infinity' ) OR
( upper(NEW.intvl) = '-infinity' ) OR
( upper(NEW.intvl) = 'infinity' ) OR
( NOT lower_inc(NEW.intvl) ) OR
( upper_inc(NEW.intvl) ) OR
( lower_inf(NEW.intvl) ) OR
( upper_inf(NEW.intvl) ) THEN
RAISE EXCEPTION 'illegal attendance interval %s', NEW.intvl;
END IF;
PERFORM not_before_1892(upper(NEW.intvl));
PERFORM not_before_1892(lower(NEW.intvl));
IF ( upper(NEW.intvl) != round_time(upper(NEW.intvl)) ) OR
( lower(NEW.intvl) != round_time(lower(NEW.intvl)) ) THEN
RAISE EXCEPTION 'upper and lower bounds of interval must be evenly divisible by 5 minutes';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE
#,
q#COMMENT ON FUNCTION valid_intvl() IS $body$
This function runs a battery of validation tests on intervals.
The purpose of these tests is to ensure that the only intervals to make
it into the database are those that make sense in the context of employee
attendance.
$body$
#,
# the 'employees' table
q/CREATE TABLE IF NOT EXISTS employees (
eid serial PRIMARY KEY,
nick varchar(32) UNIQUE NOT NULL,
sec_id varchar(64) UNIQUE,
fullname varchar(96) UNIQUE,
email text UNIQUE,
passhash text,
salt text,
sync boolean DEFAULT FALSE NOT NULL,
supervisor integer REFERENCES employees (eid),
remark text,
CONSTRAINT kosher_nick CHECK (nick ~* '^[[:alnum:]_][[:alnum:]_-]+$')
)/,
q#COMMENT ON TABLE employees IS 'Employee profile associating a real (or imagined) employee with an Employee ID (EID)'#,
# 'employees' triggers
q/CREATE OR REPLACE FUNCTION eid_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.eid <> NEW.eid THEN
RAISE EXCEPTION 'employees.eid field is immutable';
END IF;
RETURN NEW;
END;
$IMM$ LANGUAGE plpgsql/,
q/COMMENT ON FUNCTION eid_immutable() IS 'trigger function to prevent users from modifying the EID field'/,
q/CREATE TRIGGER no_eid_update BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE eid_immutable()/,
q/COMMENT ON TRIGGER no_eid_update ON employees IS 'trigger for eid_immutable()'/,
q/CREATE OR REPLACE FUNCTION employee_supervise_self() RETURNS trigger AS $IMM$
BEGIN
IF NEW.eid = NEW.supervisor THEN
RAISE EXCEPTION 'employees cannot be their own supervisor';
END IF;
RETURN NEW;
END;
config/sql/dbinit_Config.pm view on Meta::CPAN
sid integer REFERENCES schedules (sid) NOT NULL,
effective timestamptz NOT NULL,
remark text,
UNIQUE (eid, effective)
)/,
q/-- trigger function to make 'shid' field immutable
CREATE OR REPLACE FUNCTION shid_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.shid <> NEW.shid THEN
RAISE EXCEPTION 'schedhistory.shid field is immutable';
END IF;
RETURN NEW;
END;
$IMM$ LANGUAGE plpgsql/,
q/-- trigger the trigger
CREATE TRIGGER no_shid_update BEFORE UPDATE ON schedhistory
FOR EACH ROW EXECUTE PROCEDURE shid_immutable()/,
# the 'privilege' type
q/CREATE TYPE privilege AS ENUM ('passerby', 'inactive', 'active', 'admin')/,
# the 'schedhistory' table
q/CREATE TABLE IF NOT EXISTS privhistory (
phid serial PRIMARY KEY,
eid integer REFERENCES employees (eid) NOT NULL,
priv privilege NOT NULL,
effective timestamptz NOT NULL,
remark text,
UNIQUE (eid, effective)
)/,
q/-- trigger function to make 'phid' field immutable
CREATE OR REPLACE FUNCTION phid_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.phid <> NEW.phid THEN
RAISE EXCEPTION 'privhistory.phid field is immutable';
END IF;
RETURN NEW;
END;
$IMM$ LANGUAGE plpgsql/,
q/-- trigger the trigger
CREATE TRIGGER no_phid_update BEFORE UPDATE ON privhistory
FOR EACH ROW EXECUTE PROCEDURE phid_immutable()/,
# triggers shared by 'privhistory' and 'schedhistory'
q/CREATE OR REPLACE FUNCTION round_effective() RETURNS trigger AS $$
BEGIN
NEW.effective = round_time(NEW.effective);
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE/,
q/CREATE OR REPLACE FUNCTION sane_timestamp() RETURNS trigger AS $$
BEGIN
PERFORM not_before_1892(NEW.effective);
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE/,
q/CREATE TRIGGER round_effective BEFORE INSERT OR UPDATE ON schedhistory
FOR EACH ROW EXECUTE PROCEDURE round_effective()/,
q/CREATE TRIGGER round_effective BEFORE INSERT OR UPDATE ON privhistory
FOR EACH ROW EXECUTE PROCEDURE round_effective()/,
q/CREATE TRIGGER enforce_ts_sanity BEFORE INSERT OR UPDATE ON schedhistory
FOR EACH ROW EXECUTE PROCEDURE sane_timestamp()/,
q/CREATE TRIGGER enforce_ts_sanity BEFORE INSERT OR UPDATE ON privhistory
FOR EACH ROW EXECUTE PROCEDURE sane_timestamp()/,
# stored procedures relating to privhistory, schedhistory, and schedule
q#-- generalized function to get privilege level for an employee
-- as of a given timestamp
-- the complicated SELECT is necessary to ensure that the function
-- always returns a valid privilege level -- if the EID given doesn't
-- have a privilege level for the timestamp given, the function
-- returns 'passerby' (for more information, see t/003-current-priv.t)
CREATE OR REPLACE FUNCTION priv_at_timestamp (INTEGER, TIMESTAMP WITH TIME ZONE)
RETURNS privilege AS $$
SELECT priv FROM (
SELECT 'passerby' AS priv, '4713-01-01 BC' AS effective
UNION
SELECT priv, effective FROM privhistory
WHERE eid=$1 AND effective <= $2
) AS something_like_a_virtual_table
ORDER BY effective DESC
FETCH FIRST ROW ONLY
$$ LANGUAGE sql IMMUTABLE#,
q#-- function to get SID for an employee as of timestamp
CREATE OR REPLACE FUNCTION sid_at_timestamp (INTEGER, TIMESTAMP WITH TIME ZONE)
RETURNS integer AS $$
SELECT sid FROM (
SELECT NULL AS sid, '4713-01-01 BC' AS effective
UNION
SELECT schedules.sid, schedhistory.effective
FROM schedules, schedhistory
WHERE schedules.sid = schedhistory.sid AND
schedhistory.eid=$1 AND
schedhistory.effective <= $2
) AS something_like_a_virtual_table
ORDER BY effective DESC
FETCH FIRST ROW ONLY
$$ LANGUAGE sql IMMUTABLE#,
q#-- function to get the privhistory record applicable to an employee
-- as of a timestamp
CREATE OR REPLACE FUNCTION privhistory_at_timestamp (
IN INTEGER,
IN TIMESTAMP WITH TIME ZONE,
OUT phid INTEGER,
OUT eid INTEGER,
OUT priv PRIVILEGE,
config/sql/dbinit_Config.pm view on Meta::CPAN
$$ LANGUAGE plpgsql IMMUTABLE#,
q/-- trigger the trigger
CREATE TRIGGER no_intvl_conflict BEFORE INSERT OR UPDATE ON privhistory
FOR EACH ROW EXECUTE PROCEDURE history_policy()/,
q/-- trigger the trigger
CREATE TRIGGER no_intvl_conflict BEFORE INSERT OR UPDATE ON schedhistory
FOR EACH ROW EXECUTE PROCEDURE history_policy()/,
q/-- trigger function to enforce policy that an interval cannot come into
-- existence unless the employee has only a single privlevel throughout
-- the entire interval and that privlevel is either 'active' or 'admin'
CREATE OR REPLACE FUNCTION priv_policy() RETURNS trigger AS $$
DECLARE
priv text;
pr_count integer;
BEGIN
-- the EID is NEW.eid, interval is NEW.intvl
-- 1. is there a non-passerbu privilege at the beginning of the interval?
SELECT priv_at_timestamp(NEW.eid, lower(NEW.intvl)) INTO priv;
IF priv = 'passerby' OR priv = 'inactive' THEN
RAISE EXCEPTION 'insufficient privileges: check employee privhistory';
END IF;
-- 2. are there any privhistory records during the interval?
SELECT count(*) FROM privhistory INTO pr_count
WHERE eid=NEW.eid AND effective >= lower(NEW.intvl) AND effective <= upper(NEW.intvl);
IF pr_count > 0 THEN
RAISE EXCEPTION 'ambiguous privilege status: check employee privhistory';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE/,
q/-- trigger function to enforce policy that an interval cannot come into
-- existence unless the employee has only a single schedule throughout
-- the entire interval
CREATE OR REPLACE FUNCTION schedule_policy() RETURNS trigger AS $$
DECLARE
test_sid text;
sh_count integer;
BEGIN
-- the EID is NEW.eid, interval is NEW.intvl
-- 1. is there a schedule at the beginning of the interval?
SELECT sid_at_timestamp(NEW.eid, lower(NEW.intvl)) INTO test_sid;
IF test_sid IS NULL THEN
RAISE EXCEPTION 'employee schedule for this interval cannot be determined';
END IF;
-- 2. are there any schedhistory records during the interval?
SELECT count(*) FROM schedhistory INTO sh_count
WHERE eid=NEW.eid AND effective >= lower(NEW.intvl) AND effective <= upper(NEW.intvl);
IF sh_count > 0 THEN
RAISE EXCEPTION 'employee schedule for this interval cannot be determined';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE/,
q/-- trigger function for use in sanity checks on attendance and lock intervals
-- vets an interval to ensure it does not extend too far into the future
CREATE OR REPLACE FUNCTION not_too_future() RETURNS trigger AS $$
DECLARE
limit_ts timestamptz;
BEGIN
--
-- does the interval extend too far into the future?
--
SELECT date_trunc('MONTH', (now() + interval '4 months'))::TIMESTAMPTZ INTO limit_ts;
IF upper(NEW.intvl) >= limit_ts THEN
RAISE EXCEPTION 'interval extends too far into the future';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql IMMUTABLE/,
q/-- trigger function to make 'iid' field immutable
CREATE OR REPLACE FUNCTION iid_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.iid <> NEW.iid THEN
RAISE EXCEPTION 'intervals.iid field is immutable';
END IF;
RETURN NEW;
END;
$IMM$ LANGUAGE plpgsql/,
q/CREATE TRIGGER one_and_only_one_schedule BEFORE INSERT OR UPDATE ON intervals
FOR EACH ROW EXECUTE PROCEDURE schedule_policy()/,
q/CREATE TRIGGER enforce_priv_policy BEFORE INSERT OR UPDATE ON intervals
FOR EACH ROW EXECUTE PROCEDURE priv_policy()/,
q/CREATE TRIGGER a1_interval_valid_intvl BEFORE INSERT OR UPDATE ON intervals
FOR EACH ROW EXECUTE PROCEDURE valid_intvl()/,
q/CREATE TRIGGER a2_interval_not_too_future BEFORE INSERT OR UPDATE ON intervals
FOR EACH ROW EXECUTE PROCEDURE not_too_future()/,
q/CREATE TRIGGER a3_no_iid_update BEFORE UPDATE ON intervals
FOR EACH ROW EXECUTE PROCEDURE iid_immutable()/,
# the 'locks' table
q/-- locks
CREATE TABLE locks (
lid serial PRIMARY KEY,
eid integer REFERENCES Employees (EID),
intvl tstzrange NOT NULL,
remark text,
stamp json,
EXCLUDE USING gist (eid WITH =, intvl WITH &&)
)/,
q/-- trigger function to make 'lid' field immutable
CREATE OR REPLACE FUNCTION lid_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.lid <> NEW.lid THEN
RAISE EXCEPTION 'locks.lid field is immutable';
END IF;
RETURN NEW;
END;
$IMM$ LANGUAGE plpgsql/,
q/CREATE TRIGGER a1_lock_valid_intvl BEFORE INSERT OR UPDATE ON locks
FOR EACH ROW EXECUTE PROCEDURE valid_intvl()/,
q/CREATE TRIGGER a2_lock_not_too_future BEFORE INSERT OR UPDATE ON locks
FOR EACH ROW EXECUTE PROCEDURE not_too_future()/,
q/-- trigger the trigger
CREATE TRIGGER a3_no_lid_update BEFORE UPDATE ON locks
FOR EACH ROW EXECUTE PROCEDURE lid_immutable()/,
q/-- lock lookup trigger for intervals table
CREATE OR REPLACE FUNCTION no_lock_conflict() RETURNS trigger AS $IMM$
DECLARE
this_eid integer;
this_intvl tstzrange;
lock_count integer;
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
-- EID and tsrange are NEW.eid and NEW.intvl, respectively
this_eid := NEW.eid;
this_intvl := NEW.intvl;
ELSE
-- TG_OP = 'DELETE'
this_eid := OLD.eid;
this_intvl := OLD.intvl;
END IF;
SELECT count(*) INTO lock_count FROM locks WHERE eid=this_eid AND intvl && this_intvl;
IF lock_count > 0 THEN
RAISE EXCEPTION 'interval is locked';
END IF;
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$IMM$ LANGUAGE plpgsql/,
q/CREATE TRIGGER intvl_not_locked BEFORE INSERT OR UPDATE OR DELETE ON intervals
FOR EACH ROW EXECUTE PROCEDURE no_lock_conflict()/,
# the 'tempintvls' table and associated plumbing
q/CREATE SEQUENCE temp_intvl_seq/,
q/COMMENT ON SEQUENCE temp_intvl_seq IS 'sequence guaranteeing that each set of temporary intervals will have a unique identifier'/,
q/-- tempintvls
-- for staging fillup intervals
CREATE TABLE IF NOT EXISTS tempintvls (
int_id serial PRIMARY KEY,
tiid integer NOT NULL,
intvl tstzrange NOT NULL
)/,
q/CREATE TRIGGER a2_interval_not_too_future BEFORE INSERT OR UPDATE ON tempintvls
FOR EACH ROW EXECUTE PROCEDURE not_too_future()/,
# create 'root' and 'demo' employees
q/-- insert root employee into employees table and grant admin
-- privilege to the resulting EID
WITH cte AS (
INSERT INTO employees (nick, fullname, email, passhash, salt, remark)
VALUES ('root', 'Root Immutable', 'root@site.org', '82100e9bd4757883b4627b3bafc9389663e7be7f76a1273508a7a617c9dcd917428a7c44c6089477c8e1d13e924343051563d2d426617b695f3a3bff74e7c003', '341755e03e1f163f829785d1d19eab9dee5135c0', 'dbinit')
RETURNING eid
)
INSERT INTO privhistory (eid, priv, effective, remark)
SELECT eid, 'admin', '1892-01-01', 'IMMUTABLE' FROM cte
/,
q/-- insert demo employee into employees table
INSERT INTO employees (nick, fullname, email, passhash, salt, remark)
VALUES ('demo', 'Demo Employee', 'demo@dochazka.site', '4962cc89c646261a887219795083a02b899ea960cd84a234444b7342e2222eb22dc06f5db9c71681074859469fdc0abd53e3f1f47a381617b59f4b31608e24b1', '82702be8d9810d8fba774dcb7c9f68f39d0933e8', 'dbinit')
RETURNING eid
/,
# DEFAULT schedule
q/-- insert DEFAULT schedule into schedules table
INSERT INTO schedules (scode, schedule)
VALUES ('DEFAULT', '[{"high_dow":"MON","high_time":"12:00","low_dow":"MON","low_time":"08:00"},{"high_dow":"MON","high_time":"16:30","low_dow":"MON","low_time":"12:30"},{"high_dow":"TUE","high_time":"12:00","low_dow":"TUE","low_time":"08:00"},{...
/,
]);
# DBINIT_SELECT_EID_OF
# after create_tables (REST.pm) executes the above list of SQL
# statements, it needs to find the EID of the root and demo employees
#
set('DBINIT_SELECT_EID_OF', q/
SELECT eid FROM employees WHERE nick = ?/);
# DBINIT_MAKE_ROOT_IMMUTABLE
# after finding the EID of the root employee, create_tables executes
# another batch of SQL statements to make root immutable
# (for more information, see t/002-root.t)
#
set('DBINIT_MAKE_ROOT_IMMUTABLE', [
q/
-- trigger function to detect attempts to change nick of the
-- root employee
CREATE OR REPLACE FUNCTION root_immutable() RETURNS trigger AS $IMM$
BEGIN
IF OLD.eid = ? THEN
IF NEW.eid <> ? THEN
RAISE EXCEPTION 'root employee is immutable';
END IF;
IF NEW.nick <> 'root' THEN
RAISE EXCEPTION 'root employee is immutable';
END IF;
IF NEW.supervisor IS NOT NULL THEN
RAISE EXCEPTION 'root employee is immutable';
END IF;
END IF;
RETURN NEW;
END;
( run in 0.654 second using v1.01-cache-2.11-cpan-d7f47b0818f )