scripts/schema.postgres.sql

   1 BEGIN;
   2
   3 ---
   4 --- users table
   5 ---
   6 DROP TABLE IF EXISTS users CASCADE;
   7 CREATE TABLE users (
   8     id SERIAL PRIMARY KEY,
   9     password CHAR(40) NOT NULL,
  10     pseudo VARCHAR(20) UNIQUE NOT NULL CHECK (pseudo ~ '^[a-zA-Z0-9_.]+$'),
  11     email VARCHAR(320) UNIQUE NOT NULL,
  12     lang VARCHAR(3),
  13     creation_time TIMESTAMP NOT NULL DEFAULT NOW(),
  14     creation_addr INET NOT NULL
  15 );
  16
  17 ---
  18 --- pending_actions table
  19 ---
  20 CREATE OR REPLACE FUNCTION randomchars(reslength INTEGER, letters TEXT = 'abcdefghijklmnopqrstuvwxyz0123456789')
  21     RETURNS TEXT AS
  22 $$
  23 DECLARE
  24     choices CHAR ARRAY;
  25     choices_length INTEGER;
  26     result TEXT := '';
  27 BEGIN
  28     choices := regexp_split_to_array(letters, '');
  29     choices_length = array_length(choices, 1);
  30
  31     WHILE length(result) < reslength LOOP
  32         result := result || choices[ceil(random() * choices_length)];
  33     END LOOP;
  34
  35     return result;
  36 END;
  37 $$ LANGUAGE plpgsql;
  38
  39 DROP TYPE IF EXISTS ACTION CASCADE;
  40 CREATE TYPE ACTION AS ENUM ('validate_creation', 'reset_password');
  41
  42 DROP TABLE IF EXISTS pending_actions CASCADE;
  43 CREATE TABLE pending_actions (
  44     id SERIAL PRIMARY KEY,
  45     userid INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
  46     action ACTION NOT NULL,
  47     hash CHAR(40) NOT NULL UNIQUE CHECK (hash ~ '^[a-z0-9]+$') DEFAULT randomchars(40),
  48     notifications_number INTEGER DEFAULT 0,
  49     creation_time TIMESTAMP NOT NULL DEFAULT NOW(),
  50     UNIQUE (userid, action)
  51 );
  52
  53 CREATE OR REPLACE FUNCTION tg_user_ai() RETURNS TRIGGER AS $$
  54 BEGIN
  55     INSERT INTO pending_actions (userid, action, creation_time) VALUES
  56                     (NEW.id, 'validate_creation', NEW.creation_time);
  57     RETURN NEW;
  58 END;
  59 $$ LANGUAGE PLPGSQL;
  60
  61 CREATE OR REPLACE FUNCTION tg_pending_bi() RETURNS TRIGGER AS $$
  62 BEGIN
  63     IF NEW.action = 'reset_password' THEN
  64         DELETE FROM pending_actions WHERE action = 'reset_password' AND userid=NEW.userid;
  65     END IF;
  66     RETURN NEW;
  67 END;
  68 $$ LANGUAGE PLPGSQL;
  69
  70 DROP TRIGGER IF EXISTS tg_user_ai ON users;
  71 CREATE TRIGGER tg_user_ai AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE tg_user_ai();
  72
  73 DROP TRIGGER IF EXISTS tg_pending_bi ON pending_actions;
  74 CREATE TRIGGER tg_pending_bi BEFORE INSERT ON pending_actions FOR EACH ROW EXECUTE PROCEDURE tg_pending_bi();
  75
  76 ---
  77 --- paths table
  78 ---
  79 DROP SEQUENCE IF EXISTS paths_id_seq CASCADE;
  80 CREATE SEQUENCE paths_id_seq;
  81
  82 CREATE OR REPLACE FUNCTION seq_attained_value(seqname TEXT, idx INTEGER)
  83     RETURNS BOOLEAN AS
  84 $$
  85 DECLARE
  86     rec RECORD;
  87 BEGIN
  88     EXECUTE 'SELECT * FROM ' || quote_ident (seqname) INTO rec;
  89     IF rec.is_called = FALSE
  90             OR idx < rec.min_value OR idx > rec.max_value
  91             OR (idx > rec.last_value AND rec.increment_by > 0)
  92             OR (idx < rec.last_value AND rec.increment_by < 0)
  93             THEN
  94         RETURN FALSE;
  95     END IF;
  96     return (((idx - rec.start_value) * abs(rec.increment_by) / rec.increment_by) % rec.increment_by) = 0;
  97 END;
  98 $$ LANGUAGE plpgsql;
  99
 100 DROP TABLE IF EXISTS paths CASCADE;
 101 CREATE TABLE paths (
 102     id INTEGER PRIMARY KEY DEFAULT nextval('paths_id_seq'),
 103     geom GEOGRAPHY (LINESTRING, 4326) NOT NULL,
 104     owner INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
 105     title VARCHAR(40),
 106     urlcomp VARCHAR(20) UNIQUE CHECK (urlcomp ~ '^[a-z][a-zA-Z0-9_]*$') -- ~: matches regular expression; case sensitive
 107 );
 108
 109 -- unique (geom, owner) constraint will not work: first because of postgis
 110 -- #541; then because equality is checked by comparing bbox instead of real
 111 -- geometries. So, we implement that constraint in a trigger
 112 CREATE OR REPLACE FUNCTION tg_paths_bu() RETURNS TRIGGER AS $$
 113 DECLARE res INTEGER;
 114 BEGIN
 115     SELECT INTO res COUNT(*) FROM paths WHERE ST_AsBinary(geom) = ST_AsBinary(NEW.geom) AND owner = NEW.owner AND id != NEW.id;
 116     IF res >= 1 THEN
 117         RAISE 'duplicate key paths_geom_key' using ERRCODE = 'unique_violation';
 118     ELSE
 119         RETURN NEW;
 120     END IF;
 121 END;
 122 $$ LANGUAGE PLPGSQL;
 123
 124 DROP TRIGGER IF EXISTS tg_paths_bu ON paths;
 125 CREATE TRIGGER tg_paths_bu BEFORE INSERT OR UPDATE ON paths FOR EACH ROW EXECUTE PROCEDURE tg_paths_bu();
 126
 127 COMMIT;