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 );
  15
  16 ---
  17 --- pending_actions table
  18 ---
  19 CREATE OR REPLACE FUNCTION randomchars(reslength INTEGER, letters TEXT = 'abcdefghijklmnopqrstuvwxyz0123456789')
  20     RETURNS TEXT AS
  21 $$
  22 DECLARE
  23     choices CHAR ARRAY;
  24     choices_length INTEGER;
  25     result TEXT := '';
  26 BEGIN
  27     choices := regexp_split_to_array(letters, '');
  28     choices_length = array_length(choices, 1);
  29
  30     WHILE length(result) < reslength LOOP
  31         result := result || choices[ceil(random() * choices_length)];
  32     END LOOP;
  33
  34     return result;
  35 END;
  36 $$ LANGUAGE plpgsql;
  37
  38 DROP TYPE IF EXISTS ACTION CASCADE;
  39 CREATE TYPE ACTION AS ENUM ('validate_creation', 'reset_password');
  40
  41 DROP TABLE IF EXISTS pending_actions CASCADE;
  42 CREATE TABLE pending_actions (
  43     id SERIAL PRIMARY KEY,
  44     userid INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
  45     action ACTION NOT NULL,
  46     hash CHAR(40) NOT NULL UNIQUE CHECK (hash ~ '^[a-z0-9]+$') DEFAULT randomchars(40),
  47     notifications_number INTEGER DEFAULT 0,
  48     creation_time TIMESTAMP NOT NULL DEFAULT NOW(),
  49     UNIQUE (userid, action)
  50 );
  51
  52 CREATE OR REPLACE FUNCTION tg_user_ai() RETURNS TRIGGER AS $$
  53 BEGIN
  54     INSERT INTO pending_actions (userid, action, creation_time) VALUES
  55                     (NEW.id, 'validate_creation', NEW.creation_time);
  56     RETURN NEW;
  57 END;
  58 $$ LANGUAGE PLPGSQL;
  59
  60 CREATE OR REPLACE FUNCTION tg_pending_bi() RETURNS TRIGGER AS $$
  61 BEGIN
  62     IF NEW.action = 'reset_password' THEN
  63         DELETE FROM pending_actions WHERE action = 'reset_password' AND userid=NEW.userid;
  64     END IF;
  65     RETURN NEW;
  66 END;
  67 $$ LANGUAGE PLPGSQL;
  68
  69 DROP TRIGGER IF EXISTS tg_user_ai ON users;
  70 CREATE TRIGGER tg_user_ai AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE tg_user_ai();
  71
  72 DROP TRIGGER IF EXISTS tg_pending_bi ON pending_actions;
  73 CREATE TRIGGER tg_pending_bi BEFORE INSERT ON pending_actions FOR EACH ROW EXECUTE PROCEDURE tg_pending_bi();
  74
  75 ---
  76 --- paths table
  77 ---
  78 DROP SEQUENCE IF EXISTS paths_id_seq CASCADE;
  79 CREATE SEQUENCE paths_id_seq;
  80
  81 CREATE OR REPLACE FUNCTION seq_attained_value(seqname TEXT, idx INTEGER)
  82     RETURNS BOOLEAN AS
  83 $$
  84 DECLARE
  85     rec RECORD;
  86 BEGIN
  87     EXECUTE 'SELECT * FROM ' || quote_ident (seqname) INTO rec;
  88     IF rec.is_called = FALSE
  89             OR idx < rec.min_value OR idx > rec.max_value
  90             OR (idx > rec.last_value AND rec.increment_by > 0)
  91             OR (idx < rec.last_value AND rec.increment_by < 0)
  92             THEN
  93         RETURN FALSE;
  94     END IF;
  95     return (((idx - rec.start_value) * abs(rec.increment_by) / rec.increment_by) % rec.increment_by) = 0;
  96 END;
  97 $$ LANGUAGE plpgsql;
  98
  99 DROP TABLE IF EXISTS paths CASCADE;
 100 CREATE TABLE paths (
 101     id INTEGER PRIMARY KEY DEFAULT nextval('paths_id_seq'),
 102     geom GEOGRAPHY (LINESTRING, 4326) NOT NULL,
 103     creator INTEGER REFERENCES users ON DELETE SET NULL,
 104     creator_ip INET NOT NULL,
 105     title VARCHAR(40),
 106     last_update TIMESTAMP NOT NULL DEFAULT NOW(),
 107     urlcomp VARCHAR(20) UNIQUE CHECK (urlcomp ~ '^[a-z][a-zA-Z0-9_]*$') -- ~: matches regular expression; case sensitive
 108 );
 109
 110 COMMIT;