add: implement proxy handling

[motion.git] / sql / schema.sql

diff --git a/sql/schema.sql b/sql/schema.sql
old mode 100755 (executable)
new mode 100644 (file)
index 116e76f..be4b3d2
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -4,8 +4,10 @@ CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY
 
 DROP TABLE IF EXISTS motion;
 CREATE TABLE motion (id serial NOT NULL,
+                   identifier VARCHAR(20) NOT NULL,
                    name VARCHAR(250) NOT NULL,
                    type VARCHAR(250) NOT NULL,
+                   host VARCHAR(500) NOT NULL,
                    content text NOT NULL,
                    posed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    posed_by int NOT NULL,
@@ -14,7 +16,7 @@ CREATE TABLE motion (id serial NOT NULL,
                    cancelation_reason text NULL DEFAULT NULL,
                    canceled_by int NULL DEFAULT NULL,
                    PRIMARY KEY(id));
-
+CREATE UNIQUE INDEX motion_ident ON motion (identifier);
 
 DROP TABLE IF EXISTS vote;
 DROP TYPE IF EXISTS "vote_type";
@@ -25,6 +27,18 @@ CREATE TABLE vote (motion_id INTEGER NOT NULL,
                  entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY(motion_id, voter_id));
 
+DROP TABLE IF EXISTS proxy;
+CREATE TABLE proxy (id serial NOT NULL,
+                   voter_id INTEGER NOT NULL,
+                   proxy_id INTEGER NOT NULL,
+                   granted timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                   granted_by int NOT NULL,
+                   revoked timestamp NULL DEFAULT NULL,
+                   revoked_by int NULL DEFAULT NULL,
+                   PRIMARY KEY(id));
+CREATE INDEX proxy_voter ON proxy (voter_id);
+CREATE INDEX proxy_proxy ON proxy (proxy_id);
+
 DROP TABLE IF EXISTS schema_version;
 CREATE TABLE schema_version (version INTEGER NOT NULL);
-INSERT INTO schema_version(version) VALUES(1);
+INSERT INTO schema_version(version) VALUES(4);