view release on metacpan or  search on metacpan

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

      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', [