summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-11-11 15:51:40 +0100
committerYorhel <git@yorhel.nl>2015-11-11 16:00:34 +0100
commit1e97c0405a0cacb05d9d70429d7e7969e231b786 (patch)
tree1d31d74761726bf29868d1d0aebc66e188180dd9 /util
parentfd9f224ad7e1d6ebe4f7abba75526b5190c963ba (diff)
Misc poll improvements
- Merged polls table into threads table. Not much of a storage/performance difference, and it's a bit simpler this way. - Merged DB::Polls into DB::Discussions. Mainly because of the above change in DB structure. - Add option to remove an existing poll. - Allow preview and recast to be changed without deleting the votes - Set preview option by default. Because personal preferences. :) - Minor form validation differences
Diffstat (limited to 'util')
-rw-r--r--util/sql/schema.sql45
-rw-r--r--util/sql/tableattrs.sql4
-rw-r--r--util/updates/update_2.26.sql22
3 files changed, 46 insertions, 25 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index d2d11b64..9d71d7b8 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -486,7 +486,26 @@ CREATE TABLE threads (
title varchar(50) NOT NULL DEFAULT '',
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
- count smallint NOT NULL DEFAULT 0
+ count smallint NOT NULL DEFAULT 0,
+ poll_question varchar(100),
+ poll_max_options smallint NOT NULL DEFAULT 1,
+ poll_preview boolean NOT NULL DEFAULT FALSE,
+ poll_recast boolean NOT NULL DEFAULT FALSE
+);
+
+-- threads_poll_options
+CREATE TABLE threads_poll_options (
+ id SERIAL PRIMARY KEY,
+ tid integer NOT NULL,
+ option varchar(100) NOT NULL
+);
+
+-- threads_poll_votes
+CREATE TABLE threads_poll_votes (
+ tid integer NOT NULL,
+ uid integer NOT NULL,
+ optid integer NOT NULL,
+ PRIMARY KEY (tid, uid, optid)
);
-- threads_posts
@@ -730,27 +749,3 @@ CREATE TABLE wlists (
added timestamptz NOT NULL DEFAULT NOW(),
PRIMARY KEY(uid, vid)
);
-
-CREATE TABLE polls (
- id SERIAL PRIMARY KEY,
- tid integer UNIQUE NOT NULL DEFAULT 0, -- references threads
- question varchar(100) NOT NULL DEFAULT '',
- max_options smallint NOT NULL DEFAULT 1,
- preview boolean NOT NULL DEFAULT FALSE,
- recast boolean NOT NULL DEFAULT FALSE
-);
-
-CREATE TABLE polls_options (
- id SERIAL PRIMARY KEY,
- pid integer NOT NULL REFERENCES polls (id) ON DELETE CASCADE,
- option varchar(100) NOT NULL
-);
-
-CREATE TABLE polls_votes (
- pid integer NOT NULL REFERENCES polls (id) ON DELETE CASCADE,
- uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
- optid integer NOT NULL REFERENCES polls_options (id) ON DELETE CASCADE,
- PRIMARY KEY (pid, uid, optid)
-);
-
-ALTER TABLE polls ADD FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 7a144e1f..55f91742 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -51,6 +51,10 @@ ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey
ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fkey FOREIGN KEY (optid) REFERENCES threads_poll_options (id) ON DELETE CASCADE;
ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
diff --git a/util/updates/update_2.26.sql b/util/updates/update_2.26.sql
index 6900d080..691fdca7 100644
--- a/util/updates/update_2.26.sql
+++ b/util/updates/update_2.26.sql
@@ -7,3 +7,25 @@ UPDATE users SET passwd = '' WHERE length(passwd) = 41;
-- Need to regenerate all relation graphs in the switch to HTML5
UPDATE vn SET rgraph = NULL;
UPDATE producers SET rgraph = NULL;
+
+
+-- Polls
+ALTER TABLE threads ADD COLUMN poll_question varchar(100);
+ALTER TABLE threads ADD COLUMN poll_max_options smallint NOT NULL DEFAULT 1;
+ALTER TABLE threads ADD COLUMN poll_preview boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE threads ADD COLUMN poll_recast boolean NOT NULL DEFAULT FALSE;
+CREATE TABLE threads_poll_options (
+ id SERIAL PRIMARY KEY,
+ tid integer NOT NULL,
+ option varchar(100) NOT NULL
+);
+CREATE TABLE threads_poll_votes (
+ tid integer NOT NULL,
+ uid integer NOT NULL,
+ optid integer NOT NULL,
+ PRIMARY KEY (tid, uid, optid)
+);
+ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fkey FOREIGN KEY (optid) REFERENCES threads_poll_options (id) ON DELETE CASCADE;