summaryrefslogtreecommitdiff
path: root/util/updates/update_2.20.sql
blob: 51b9252b3018a223f2451d44ebcc649fa1c49715 (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

ALTER TYPE notification_ltype RENAME TO tmp;
CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't');
ALTER TABLE notifications ALTER COLUMN ltype TYPE notification_ltype USING ltype::text::notification_ltype;
DROP TYPE tmp;

\i util/sql/func.sql

CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON chars         FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON chars         FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();


INSERT INTO stats_cache VALUES
  ('chars',  (SELECT COUNT(*) FROM chars WHERE NOT hidden)),
  ('tags',   (SELECT COUNT(*) FROM tags WHERE state = 2)),
  ('traits', (SELECT COUNT(*) FROM traits WHERE state = 2));

CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON chars         FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON chars         FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON tags          FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON tags          FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON traits        FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON traits        FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();



CREATE TABLE affiliate_links (
  id SERIAL PRIMARY KEY,
  rid integer NOT NULL REFERENCES releases (id),
  hidden boolean NOT NULL DEFAULT false, -- to hide a link for some reason
  priority smallint NOT NULL DEFAULT 0,  -- manual ordering when competing on a VN page, usually not necessary
  affiliate smallint NOT NULL DEFAULT 0, -- index to a semi-static array in data/config.pl
  url varchar NOT NULL,
  version varchar NOT NULL DEFAULT '', -- "x edition" or "x version", default used is "<language> version"
  lastfetch timestamptz, -- last update of price
  price varchar NOT NULL DEFAULT '' -- formatted, including currency, e.g. "$50" or "€34.95 / $50.46"
);

CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;

--INSERT INTO affiliate_links (rid, priority, affiliate, url, version, lastfetch, price) VALUES
--  (175, 1, 0, 'http://www.jbox.com/product/PCG4776', 'Original all-ages edition (Japanese)', NOW(), '$110.00'),
--  (8124, 0, 0, 'http://www.jbox.com/product/PSPLB001', 'Converted edition (PSP, Japanese)', NOW(), '$78.00');