view release on metacpan or  search on metacpan

config/sql/dbinit_Config.pm  view on Meta::CPAN

              ) AS tblalias
          WHERE schedule_changed = 't'
      $$ LANGUAGE sql IMMUTABLE#,

    q#-- wrapper function to get priv as of current timestamp
      CREATE OR REPLACE FUNCTION current_priv (INTEGER)
      RETURNS privilege AS $$
          SELECT priv_at_timestamp($1, current_timestamp)
      $$ LANGUAGE sql IMMUTABLE#,

    q#-- wrapper function to get schedule as of current timestamp
      CREATE OR REPLACE FUNCTION current_schedule (INTEGER)
      RETURNS integer AS $$
          SELECT sid_at_timestamp($1, current_timestamp)
      $$ LANGUAGE sql IMMUTABLE#,

    # the 'activities' table

    q/-- activities
      CREATE TABLE activities (
          aid        serial PRIMARY KEY,
          code       varchar(32) UNIQUE NOT NULL,
          long_desc  text,
          remark     text,
          disabled   boolean NOT NULL,
          stamp      json,
          CONSTRAINT kosher_code CHECK (code ~* '^[[:alnum:]_][[:alnum:]_-]+$')
      )/,
  
    q/-- trigger function to make 'aid' field immutable
    CREATE OR REPLACE FUNCTION aid_immutable() RETURNS trigger AS $IMM$
      BEGIN
          IF OLD.aid <> NEW.aid THEN
              RAISE EXCEPTION 'activities.aid field is immutable'; 
          END IF;
          RETURN NEW;
      END;
    $IMM$ LANGUAGE plpgsql/,
    
    q/-- trigger the trigger
    CREATE TRIGGER no_aid_update BEFORE UPDATE ON activities
      FOR EACH ROW EXECUTE PROCEDURE aid_immutable()/,
    
    q/CREATE OR REPLACE FUNCTION code_to_upper() RETURNS trigger AS $$
        BEGIN
            NEW.code = upper(NEW.code);
            RETURN NEW;
        END;
    $$ LANGUAGE plpgsql IMMUTABLE/,

    q/CREATE TRIGGER code_to_upper BEFORE INSERT OR UPDATE ON activities
        FOR EACH ROW EXECUTE PROCEDURE code_to_upper()/,

    q/CREATE TRIGGER disabled_to_zero BEFORE INSERT OR UPDATE ON activities
        FOR EACH ROW EXECUTE PROCEDURE disabled_to_zero()/,

    # the 'components' table

    q#-- components
      CREATE TABLE components (
          cid         serial PRIMARY KEY,
          path        varchar(2048) UNIQUE NOT NULL,
          source      text NOT NULL,
          acl         varchar(16) NOT NULL,
          validations text,
          CONSTRAINT kosher_path CHECK (path ~* '^[[:alnum:]_.][[:alnum:]_/.-]+$')
      )#,
  
    q/-- trigger function to make 'cid' field immutable
    CREATE OR REPLACE FUNCTION cid_immutable() RETURNS trigger AS $IMM$
      BEGIN
          IF OLD.cid <> NEW.cid THEN
              RAISE EXCEPTION 'components.cid field is immutable'; 
          END IF;
          RETURN NEW;
      END;
    $IMM$ LANGUAGE plpgsql/,
    
    q/-- trigger the trigger
    CREATE TRIGGER no_cid_update BEFORE UPDATE ON components
      FOR EACH ROW EXECUTE PROCEDURE cid_immutable()/,
    
    # the 'intervals' table

    q/-- intervals
      CREATE TABLE IF NOT EXISTS intervals (
          iid        serial PRIMARY KEY,
          eid        integer REFERENCES employees (eid) NOT NULL,
          aid        integer REFERENCES activities (aid) NOT NULL,
          intvl      tstzrange NOT NULL,
          long_desc  text,
          remark     text,
          stamp      json,
          EXCLUDE USING gist (eid WITH =, intvl WITH &&)
      )/,

    q#-- trigger function to ensure that a privhistory/schedhistory record
      -- does not fall within an existing attendance interval
    CREATE OR REPLACE FUNCTION history_policy() RETURNS trigger AS $$
        DECLARE
            intvl_count integer;
        BEGIN
            -- the EID is NEW.eid, effective timestamptz is NEW.effective
            SELECT count(*) FROM intervals INTO intvl_count
            WHERE eid=NEW.eid AND intvl @> NEW.effective;
            IF intvl_count > 0 THEN
                RAISE EXCEPTION 'effective timestamp conflicts with existing attendance interval';
            END IF;
            RETURN NEW;
        END;
    $$ 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;