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(),
14 creation_addr INET NOT NULL
18 --- pending_actions table
20 CREATE OR REPLACE FUNCTION randomchars(reslength INTEGER, letters TEXT = 'abcdefghijklmnopqrstuvwxyz0123456789')
25 choices_length INTEGER;
28 choices := regexp_split_to_array(letters, '');
29 choices_length = array_length(choices, 1);
31 WHILE length(result) < reslength LOOP
32 result := result || choices[ceil(random() * choices_length)];
39 DROP TYPE IF EXISTS ACTION CASCADE;
40 CREATE TYPE ACTION AS ENUM ('validate_creation', 'reset_password');
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)
53 CREATE OR REPLACE FUNCTION tg_user_ai() RETURNS TRIGGER AS $$
55 INSERT INTO pending_actions (userid, action, creation_time) VALUES
56 (NEW.id, 'validate_creation', NEW.creation_time);
61 CREATE OR REPLACE FUNCTION tg_pending_bi() RETURNS TRIGGER AS $$
63 IF NEW.action = 'reset_password' THEN
64 DELETE FROM pending_actions WHERE action = 'reset_password' AND userid=NEW.userid;
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();
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();
79 DROP SEQUENCE IF EXISTS paths_id_seq CASCADE;
80 CREATE SEQUENCE paths_id_seq;
82 CREATE OR REPLACE FUNCTION seq_attained_value(seqname TEXT, idx INTEGER)
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)
96 return (((idx - rec.start_value) * abs(rec.increment_by) / rec.increment_by) % rec.increment_by) = 0;
100 DROP TABLE IF EXISTS paths CASCADE;
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,
106 urlcomp VARCHAR(20) UNIQUE CHECK (urlcomp ~ '^[a-z][a-zA-Z0-9_]*$') -- ~: matches regular expression; case sensitive
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 $$
115 SELECT INTO res COUNT(*) FROM paths WHERE ST_AsBinary(geom) = ST_AsBinary(NEW.geom) AND owner = NEW.owner AND id != NEW.id;
117 RAISE 'duplicate key paths_geom_key' using ERRCODE = 'unique_violation';
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();