add: adjust user handling per host

[motion.git] / sql / schema.sql

diff --git a/sql/schema.sql b/sql/schema.sql
index 290b9b32deb0b16e4f9fbfff8431b5eb14998d67..690425bdbcf697509ac116210c89fd62181190ee 100644 (file)
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1,5 +1,8 @@
 DROP TABLE IF EXISTS voter;
 DROP TABLE IF EXISTS voter;

-CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id));
+CREATE TABLE voter (id serial NOT NULL,
+                   email VARCHAR(255) NOT NULL,
+                   host VARCHAR(500) NOT NULL,
+                   PRIMARY KEY(id));

 
 
 DROP TABLE IF EXISTS motion;
 
 
 DROP TABLE IF EXISTS motion;


@@ -7,6 +10,7 @@ CREATE TABLE motion (id serial NOT NULL,
                    identifier VARCHAR(20) NOT NULL,
                    name VARCHAR(250) NOT NULL,
                    type VARCHAR(250) 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,
                    content text NOT NULL,
                    posed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    posed_by int NOT NULL,


@@ -24,8 +28,21 @@ CREATE TABLE vote (motion_id INTEGER NOT NULL,
                  voter_id INTEGER NOT NULL,
                  result vote_type NOT NULL,
                  entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  voter_id INTEGER NOT NULL,
                  result vote_type NOT NULL,
                  entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

+                 proxy_id INTEGER NOT NULL,

                  PRIMARY KEY(motion_id, voter_id));
 
                  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);
 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(6);