summaryrefslogtreecommitdiff
path: root/util/updates/update_2.10.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.10.sql')
-rw-r--r--util/updates/update_2.10.sql63
1 files changed, 63 insertions, 0 deletions
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
new file mode 100644
index 00000000..e54e028c
--- /dev/null
+++ b/util/updates/update_2.10.sql
@@ -0,0 +1,63 @@
+
+-- no more bayesian rating for VN list on tag pages, just plain averages
+DROP TABLE tags_vn_bayesian;
+CREATE TABLE tags_vn_inherit (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
+
+-- remove unused functions
+DROP FUNCTION tag_vn_childs() CASCADE;
+DROP FUNCTION tag_tree(integer, integer, boolean);
+DROP TYPE tag_tree_item;
+
+
+-- remove changes.causedby and give the affected changes to Multi
+UPDATE changes SET requester = 1 WHERE causedby IS NOT NULL;
+ALTER TABLE changes DROP COLUMN causedby;
+UPDATE users SET
+ c_changes = COALESCE((
+ SELECT COUNT(id)
+ FROM changes
+ WHERE requester = users.id
+ GROUP BY requester
+ ), 0);
+
+
+-- set default on releases_rev.released, required for the revision insertion abstraction
+ALTER TABLE releases_rev ALTER COLUMN released SET DEFAULT 0;
+
+
+-- type used for the revision inserting functions
+CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
+
+
+-- import the new and updated functions
+\i util/sql/func.sql
+
+
+-- call update_vncache() when a release is added, edited, hidden or unhidden
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+
+
+-- improved relgraph notify triggers
+DROP TRIGGER vn_relgraph_notify ON vn;
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+DROP TRIGGER vn_relgraph_notify ON producers;
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+
+-- more efficient version of tag_vn_calc()
+SELECT tag_vn_calc();
+
+
+-- regenerate the relation graphs so that they contain IDs for highlighting
+UPDATE vn SET rgraph = NULL;
+UPDATE producers SET rgraph = NULL;
+DELETE FROM relgraphs;
+
+