summaryrefslogtreecommitdiff
path: root/util/updates/update_2.3.sql
blob: b06623024ed831690346dfdd8d71f0bee7cde2e3 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

-- some random VN quotes
CREATE TABLE quotes (
  vid integer NOT NULL REFERENCES vn (id),
  quote varchar(250) NOT NULL,
  PRIMARY KEY(vid, quote)
) WITHOUT OIDS;


-- catalog numbers for releases
ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT '';




-- tagging system

CREATE TABLE tags (
  id          SERIAL       NOT NULL PRIMARY KEY,
  name        varchar(250) NOT NULL UNIQUE,
  alias       text         NOT NULL DEFAULT '',
  description text         NOT NULL DEFAULT '',
  meta        boolean      NOT NULL DEFAULT FALSE,
  c_vns       integer      NOT NULL DEFAULT 0
) WITHOUT OIDS;

CREATE TABLE tags_parents (
  tag    integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED,
  parent integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED,
  PRIMARY KEY(tag, parent)
) WITHOUT OIDS;

CREATE TABLE tags_vn (
  tag     integer  NOT NULL REFERENCES tags  (id) DEFERRABLE INITIALLY DEFERRED,
  vid     integer  NOT NULL REFERENCES vn    (id) DEFERRABLE INITIALLY DEFERRED,
  uid     integer  NOT NULL REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED,
  vote    smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
  spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
  PRIMARY KEY(tag, vid, uid)
) WITHOUT OIDS;


CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);

-- tag: tag to start with,
-- lvl: recursion level
-- dir: direction, true = parent->child, false = child->parent
CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$
DECLARE
  r tag_tree_item%rowtype;
  r2 tag_tree_item%rowtype;
BEGIN
  IF dir AND tag = 0 THEN
    FOR r IN
      SELECT lvl, t.id, t.name, t.c_vns
        FROM tags t
        WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
        ORDER BY t.name
    LOOP
      RETURN NEXT r;
      IF lvl-1 <> 0 THEN
        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
          RETURN NEXT r2;
        END LOOP;
      END IF;
    END LOOP;
  ELSIF dir THEN
    FOR r IN
      SELECT lvl, tp.tag, t.name, t.c_vns
        FROM tags_parents tp
        JOIN tags t ON t.id = tp.tag
        WHERE tp.parent = tag
        ORDER BY t.name
    LOOP
      RETURN NEXT r;
      IF lvl-1 <> 0 THEN
        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
          RETURN NEXT r2;
        END LOOP;
      END IF;
    END LOOP;
  ELSE
    FOR r IN
      SELECT lvl, tp.parent, t.name, t.c_vns
        FROM tags_parents tp
        JOIN tags t ON t.id = tp.parent
        WHERE tp.tag = tag
        ORDER BY t.name
    LOOP
      RETURN NEXT r;
      IF lvl-1 <> 0 THEN
        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
          RETURN NEXT r2;
        END LOOP;
      END IF;
    END LOOP;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- returns all votes inherited by childs
-- UNION this with tags_vn and you have all votes for all tags
CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
DECLARE
  r tags_vn%rowtype;
  i RECORD;
  l RECORD;
BEGIN
  FOR l IN SElECT id FROM tags WHERE meta = FALSE AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
    FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
      FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
        RETURN NEXT r;
      END LOOP;
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- all votes for all tags
CREATE OR REPLACE VIEW tags_vn_all AS
    SELECT *
      FROM tags_vn
  UNION
    SELECT *
      FROM tag_vn_childs();

-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
CREATE OR REPLACE VIEW tags_vn_grouped AS
    SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
      FROM tags_vn_all
  GROUP BY tag, vid, uid;

-- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags)
CREATE OR REPLACE VIEW tags_vn_inherited AS
    SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating,
        (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
      FROM tags_vn_grouped
  GROUP BY tag, vid;

-- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN
-- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating
--CREATE OR REPLACE VIEW tags_vn_bayesian AS
--  SELECT tag, vid, users,
--      ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating )
--      / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating,
--      spoiler
--    FROM tags_vn_inherited tvi;


-- creates/updates a table eqvuivalent to tags_vn_bayesian
CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
BEGIN
  DROP TABLE IF EXISTS tags_vn_stored;
  CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
  CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);

  UPDATE tags_vn_stored tvs SET rating =
      ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
    / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;

  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT tag_vn_calc();



-- Cache users tag vote count
ALTER TABLE users ADD COLUMN c_tags integer NOT NULL DEFAULT 0;
UPDATE users SET c_tags = (SELECT COUNT(*) FROM tags_vn WHERE uid = id);

CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
BEGIN
  IF TG_TABLE_NAME = 'votes' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
    ELSE
      UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
    END IF;
  ELSIF TG_TABLE_NAME = 'changes' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
    ELSE
      UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
    END IF;
  ELSIF TG_TABLE_NAME = 'tags_vn' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
    ELSE
      UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();