summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql16
1 files changed, 12 insertions, 4 deletions
diff --git a/util/dump.sql b/util/dump.sql
index 5d13e027..6fe4bc08 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -79,6 +79,13 @@ CREATE TABLE releases (
hidden boolean NOT NULL DEFAULT FALSE
);
+-- releases_lang
+CREATE TABLE releases_lang (
+ rid integer NOT NULL,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+
-- releases_media
CREATE TABLE releases_media (
rid integer NOT NULL DEFAULT 0,
@@ -108,7 +115,6 @@ CREATE TABLE releases_rev (
title varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
type smallint NOT NULL DEFAULT 0,
- language varchar NOT NULL DEFAULT 'ja',
website varchar(250) NOT NULL DEFAULT '',
released integer NOT NULL,
notes text NOT NULL DEFAULT '',
@@ -358,6 +364,7 @@ ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
@@ -453,16 +460,17 @@ BEGIN
GROUP BY rv1.vid
), 0),
c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
- SELECT language
+ SELECT rl2.lang
FROM releases_rev rr2
+ JOIN releases_lang rl2 ON rl2.rid = rr2.id
JOIN releases r2 ON rr2.id = r2.latest
JOIN releases_vn rv2 ON rr2.id = rv2.rid
WHERE rv2.vid = vn.id
AND rr2.type <> 2
AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
AND r2.hidden = FALSE
- GROUP BY rr2.language
- ORDER BY rr2.language
+ GROUP BY rl2.lang
+ ORDER BY rl2.lang
), ''/''), ''''),
c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
SELECT rp3.platform