]> dev.renevier.net Git - syj.git/blob - scripts/schema.postgres.sql
allow opening terms of use box with space keyboard
[syj.git] / 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(160),
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;