6 DROP TABLE IF EXISTS users CASCADE;
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,
13 creation_time TIMESTAMP NOT NULL DEFAULT NOW()
17 --- pending_actions table
19 CREATE OR REPLACE FUNCTION randomchars(reslength INTEGER, letters TEXT = 'abcdefghijklmnopqrstuvwxyz0123456789')
24 choices_length INTEGER;
27 choices := regexp_split_to_array(letters, '');
28 choices_length = array_length(choices, 1);
30 WHILE length(result) < reslength LOOP
31 result := result || choices[ceil(random() * choices_length)];
38 DROP TYPE IF EXISTS ACTION CASCADE;
39 CREATE TYPE ACTION AS ENUM ('validate_creation', 'reset_password');
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)
52 CREATE OR REPLACE FUNCTION tg_user_ai() RETURNS TRIGGER AS $$
54 INSERT INTO pending_actions (userid, action, creation_time) VALUES
55 (NEW.id, 'validate_creation', NEW.creation_time);
60 CREATE OR REPLACE FUNCTION tg_pending_bi() RETURNS TRIGGER AS $$
62 IF NEW.action = 'reset_password' THEN
63 DELETE FROM pending_actions WHERE action = 'reset_password' AND userid=NEW.userid;
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();
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();
78 DROP SEQUENCE IF EXISTS paths_id_seq CASCADE;
79 CREATE SEQUENCE paths_id_seq;
81 CREATE OR REPLACE FUNCTION seq_attained_value(seqname TEXT, idx INTEGER)
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)
95 return (((idx - rec.start_value) * abs(rec.increment_by) / rec.increment_by) % rec.increment_by) = 0;
99 DROP TABLE IF EXISTS paths CASCADE;
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,
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
110 CREATE OR REPLACE FUNCTION tg_paths_bu() RETURNS TRIGGER AS $$
113 SELECT INTO res COUNT(*) FROM paths WHERE ST_AsBinary(geom) = ST_AsBinary(NEW.geom) AND creator = NEW.creator AND id != NEW.id;
114 -- unique (geom, creator) constraint will not work: first because of postgis
115 -- #541; then because equality is checked by comparing bbox instead of real
116 -- geometries. So, we implement that constraint in a trigger
118 RAISE 'duplicate key paths_geom_key' using ERRCODE = 'unique_violation';
120 -- update last_update TIMESTAMP
121 NEW.last_update = NOW();
127 DROP TRIGGER IF EXISTS tg_paths_bu ON paths;
128 CREATE TRIGGER tg_paths_bu BEFORE INSERT OR UPDATE ON paths FOR EACH ROW EXECUTE PROCEDURE tg_paths_bu();