1 DROP TABLE IF EXISTS voter;
2 CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id));
5 DROP TABLE IF EXISTS motion;
6 CREATE TABLE motion (id serial NOT NULL,
7 identifier VARCHAR(20) NOT NULL,
8 name VARCHAR(250) NOT NULL,
9 type VARCHAR(250) NOT NULL,
10 host VARCHAR(500) NOT NULL,
11 content text NOT NULL,
12 posed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
13 posed_by int NOT NULL,
14 deadline timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP + interval '3 days'),
15 canceled timestamp NULL DEFAULT NULL,
16 cancelation_reason text NULL DEFAULT NULL,
17 canceled_by int NULL DEFAULT NULL,
19 CREATE UNIQUE INDEX motion_ident ON motion (identifier);
21 DROP TABLE IF EXISTS vote;
22 DROP TYPE IF EXISTS "vote_type";
23 CREATE TYPE "vote_type" AS ENUM ('yes', 'no', 'abstain');
24 CREATE TABLE vote (motion_id INTEGER NOT NULL,
25 voter_id INTEGER NOT NULL,
26 result vote_type NOT NULL,
27 entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 proxy_id INTEGER NOT NULL,
29 PRIMARY KEY(motion_id, voter_id));
31 DROP TABLE IF EXISTS proxy;
32 CREATE TABLE proxy (id serial NOT NULL,
33 voter_id INTEGER NOT NULL,
34 proxy_id INTEGER NOT NULL,
35 granted timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
36 granted_by int NOT NULL,
37 revoked timestamp NULL DEFAULT NULL,
38 revoked_by int NULL DEFAULT NULL,
40 CREATE INDEX proxy_voter ON proxy (voter_id);
41 CREATE INDEX proxy_proxy ON proxy (proxy_id);
43 DROP TABLE IF EXISTS schema_version;
44 CREATE TABLE schema_version (version INTEGER NOT NULL);
45 INSERT INTO schema_version(version) VALUES(5);