From 718f4d0258049aa92f229c08d5ec7204dae3ffa6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 17 Oct 2015 17:05:50 +0200 Subject: SQL: Fix all browsing queries to use the new schema This basically makes VNDB browsable again, but editing entries is still broken. I split off the get-old-revision functionality from the db*Get() methods into db*GetRev(). This split makes sense even with the old SQL schema: db*Get() had to special-case some joins/filters when fetching an older revision, and none of the other filters would work in that case. This split does cause some code duplication in that all db*GetRev() methods look very much alike, and that the columns they fetch is almost identical to the db*Get() methods. Not sure yet how to avoid the duplication elegantly. I didn't do a whole lot of query optimization yet (most issues require extra indices, I'll investigate later which indices will make a big difference), but I did fix some low hanging fruit whenever I encountered something. I don't think I've worsened anything, performance-wise. --- util/sql/tableattrs.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'util') diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 05770f83..b3ac137f 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -102,6 +102,7 @@ CREATE INDEX tags_vn_date ON tags_vn (date); CREATE INDEX tags_vn_vid ON tags_vn (vid); CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg))); CREATE INDEX vn_staff_aid ON vn_staff (aid); +CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); 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)); -- cgit v1.2.3