summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-07-04 13:53:36 +0200
committerYorhel <git@yorhel.nl>2009-07-04 13:53:36 +0200
commit1c3769e0bac96979cc1aba0a80081b69a8470528 (patch)
tree95ce961d93f72b3e14c2b2dd71d65f60eae94f13 /util
parent45843fa3b02a69951fe7f4144ccdcbca76609180 (diff)
Implemented support for multilingual releases
The 'language' column in releases_rev has been replaced with a releases_lang table. As this is quite a big change, there may still be bugs floating around somewhere.
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql16
-rw-r--r--util/updates/update_2.5.sql61
2 files changed, 73 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
diff --git a/util/updates/update_2.5.sql b/util/updates/update_2.5.sql
new file mode 100644
index 00000000..dab07608
--- /dev/null
+++ b/util/updates/update_2.5.sql
@@ -0,0 +1,61 @@
+
+
+-- multilingual releases
+
+CREATE TABLE releases_lang (
+ rid integer NOT NULL REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+INSERT INTO releases_lang (rid, lang) SELECT id, language FROM releases_rev;
+ALTER TABLE releases_rev DROP COLUMN language;
+
+CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$
+DECLARE
+ w text := '';
+BEGIN
+ IF id > 0 THEN
+ w := ' WHERE id = '||id;
+ END IF;
+ EXECUTE 'UPDATE vn SET
+ c_released = COALESCE((SELECT
+ MIN(rr1.released)
+ FROM releases_rev rr1
+ JOIN releases r1 ON rr1.id = r1.latest
+ JOIN releases_vn rv1 ON rr1.id = rv1.rid
+ WHERE rv1.vid = vn.id
+ AND rr1.type <> 2
+ AND r1.hidden = FALSE
+ AND rr1.released <> 0
+ GROUP BY rv1.vid
+ ), 0),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ 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 rl2.lang
+ ORDER BY rl2.lang
+ ), ''/''), ''''),
+ c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT rp3.platform
+ FROM releases_platforms rp3
+ JOIN releases_rev rr3 ON rp3.rid = rr3.id
+ JOIN releases r3 ON rp3.rid = r3.latest
+ JOIN releases_vn rv3 ON rp3.rid = rv3.rid
+ WHERE rv3.vid = vn.id
+ AND rr3.type <> 2
+ AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+ AND r3.hidden = FALSE
+ GROUP BY rp3.platform
+ ORDER BY rp3.platform
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+