From 1e97c0405a0cacb05d9d70429d7e7969e231b786 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 11 Nov 2015 15:51:40 +0100 Subject: 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 --- util/sql/schema.sql | 45 ++++++++++++++++++++------------------------ util/sql/tableattrs.sql | 4 ++++ util/updates/update_2.26.sql | 22 ++++++++++++++++++++++ 3 files changed, 46 insertions(+), 25 deletions(-) (limited to 'util') 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; -- cgit v1.2.3