diff options
author | Yorhel <git@yorhel.nl> | 2009-07-04 13:53:36 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-07-04 13:53:36 +0200 |
commit | 1c3769e0bac96979cc1aba0a80081b69a8470528 (patch) | |
tree | 95ce961d93f72b3e14c2b2dd71d65f60eae94f13 /util | |
parent | 45843fa3b02a69951fe7f4144ccdcbca76609180 (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.sql | 16 | ||||
-rw-r--r-- | util/updates/update_2.5.sql | 61 |
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; + |