summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-30 09:29:03 +0100
committerYorhel <git@yorhel.nl>2019-12-30 09:29:03 +0100
commite146ed7bda12d369532e485ca0c2e3d823811854 (patch)
treef5e86b31f0255c89397fcb201af5c38a62bc9d4b /util
parent40ef00116cc12aef4ce08fc1136e329c58abda4b (diff)
ulist: Remove remnants of old votes/vnlists/wlists tables
Diffstat (limited to 'util')
-rw-r--r--util/sql/perms.sql6
-rw-r--r--util/sql/schema.sql28
-rw-r--r--util/sql/tableattrs.sql8
-rw-r--r--util/updates/update_wip_lists.sql10
4 files changed, 7 insertions, 45 deletions
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index 358d8bc0..e649526f 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -83,9 +83,6 @@ GRANT SELECT, INSERT, DELETE ON vn_seiyuu TO vndb_site;
GRANT SELECT, INSERT ON vn_seiyuu_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_staff TO vndb_site;
GRANT SELECT, INSERT ON vn_staff_hist TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_site;
GRANT SELECT, INSERT ON wikidata TO vndb_site;
@@ -164,7 +161,4 @@ GRANT SELECT ON vn_screenshots_hist TO vndb_multi;
GRANT SELECT ON vn_seiyuu TO vndb_multi;
GRANT SELECT ON vn_staff TO vndb_multi;
GRANT SELECT ON vn_staff_hist TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_multi;
GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index a3a94a49..ed5315c6 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -913,25 +913,6 @@ CREATE TABLE vn_staff_hist (
PRIMARY KEY (chid, aid, role)
);
--- vnlists
-CREATE TABLE vnlists (
- uid integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub]
- status smallint NOT NULL DEFAULT 0, -- [pub]
- added TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- [pub]
- notes varchar NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY(uid, vid)
-);
-
--- votes
-CREATE TABLE votes (
- vid integer NOT NULL DEFAULT 0, -- [pub]
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vote integer NOT NULL DEFAULT 0, -- [pub]
- date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(vid, uid)
-);
-
-- wikidata
CREATE TABLE wikidata (
id integer NOT NULL PRIMARY KEY, -- [pub]
@@ -966,12 +947,3 @@ CREATE TABLE wikidata (
pixiv_user integer[], -- [pub] P5435
doujinshi_author integer[] -- [pub] P7511
);
-
--- wlists
-CREATE TABLE wlists (
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vid integer NOT NULL DEFAULT 0, -- [pub]
- wstat smallint NOT NULL DEFAULT 0, -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(uid, vid)
-);
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 54361c0a..c9b598b1 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -100,12 +100,6 @@ ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
@@ -129,8 +123,6 @@ CREATE INDEX traits_chars_tid ON traits_chars (tid);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
CREATE INDEX vn_staff_aid ON vn_staff (aid);
-CREATE INDEX votes_date ON votes (date desc); -- Mainly used on /v+ pages, other pages don't really need it
-CREATE INDEX votes_uid ON votes (uid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index c07e4ba1..0ff7a452 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -115,6 +115,9 @@ COMMIT;
DROP FUNCTION update_vnpopularity();
+ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;
+
\i util/sql/func.sql
\i util/sql/perms.sql
@@ -124,10 +127,11 @@ CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PR
CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0;
-ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;
-
\timing
SELECT update_users_ulist_stats(NULL);
CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL;
CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
+
+
+-- Can be done later:
+-- DROP TABLE wlists, vnlists, votes;