diff options
Diffstat (limited to 'util/updates/update_1.21.sql')
-rw-r--r-- | util/updates/update_1.21.sql | 97 |
1 files changed, 97 insertions, 0 deletions
diff --git a/util/updates/update_1.21.sql b/util/updates/update_1.21.sql index 176f9056..7ae77c2d 100644 --- a/util/updates/update_1.21.sql +++ b/util/updates/update_1.21.sql @@ -14,3 +14,100 @@ CREATE TABLE vn_screenshots ( PRIMARY KEY(vid, scr) ) WITHOUT OIDS; + + +-- PostgreSQL has a boolean type since 8.1, let's convert our smallints... +-- psql -> perl: +-- No changes required, DBD::Pg automatically converts the boolean type to 1 or 0 +-- perl -> psql: +-- psql doesn't accept the integers 1 and 0 as boolean, +-- so I added a !b conversion for VNDB::Util::DB::sqlprint() + +ALTER TABLE producers ALTER COLUMN locked DROP DEFAULT; +ALTER TABLE producers ALTER COLUMN locked TYPE boolean USING locked::text::boolean; +ALTER TABLE producers ALTER COLUMN locked SET DEFAULT FALSE; +ALTER TABLE producers ALTER COLUMN hidden DROP DEFAULT; +ALTER TABLE producers ALTER COLUMN hidden TYPE boolean USING hidden::text::boolean; +ALTER TABLE producers ALTER COLUMN hidden SET DEFAULT FALSE; + +ALTER TABLE releases ALTER COLUMN locked DROP DEFAULT; +ALTER TABLE releases ALTER COLUMN locked TYPE boolean USING locked::text::boolean; +ALTER TABLE releases ALTER COLUMN locked SET DEFAULT FALSE; +ALTER TABLE releases ALTER COLUMN hidden DROP DEFAULT; +ALTER TABLE releases ALTER COLUMN hidden TYPE boolean USING hidden::text::boolean; +ALTER TABLE releases ALTER COLUMN hidden SET DEFAULT FALSE; + +ALTER TABLE threads ALTER COLUMN locked DROP DEFAULT; +ALTER TABLE threads ALTER COLUMN locked TYPE boolean USING locked::text::boolean; +ALTER TABLE threads ALTER COLUMN locked SET DEFAULT FALSE; +ALTER TABLE threads ALTER COLUMN hidden DROP DEFAULT; +ALTER TABLE threads ALTER COLUMN hidden TYPE boolean USING hidden::text::boolean; +ALTER TABLE threads ALTER COLUMN hidden SET DEFAULT FALSE; + +ALTER TABLE threads_posts ALTER COLUMN hidden DROP DEFAULT; +ALTER TABLE threads_posts ALTER COLUMN hidden TYPE boolean USING hidden::text::boolean; +ALTER TABLE threads_posts ALTER COLUMN hidden SET DEFAULT FALSE; + +ALTER TABLE vn ALTER COLUMN locked DROP DEFAULT; +ALTER TABLE vn ALTER COLUMN locked TYPE boolean USING locked::text::boolean; +ALTER TABLE vn ALTER COLUMN locked SET DEFAULT FALSE; +ALTER TABLE vn ALTER COLUMN hidden DROP DEFAULT; +ALTER TABLE vn ALTER COLUMN hidden TYPE boolean USING hidden::text::boolean; +ALTER TABLE vn ALTER COLUMN hidden SET DEFAULT FALSE; + +ALTER TABLE vn_rev ALTER COLUMN img_nsfw DROP DEFAULT; +ALTER TABLE vn_rev ALTER COLUMN img_nsfw TYPE boolean USING img_nsfw::text::boolean; +ALTER TABLE vn_rev ALTER COLUMN img_nsfw SET DEFAULT FALSE; + +ALTER TABLE vn_screenshots ALTER COLUMN nsfw DROP DEFAULT; +ALTER TABLE vn_screenshots ALTER COLUMN nsfw TYPE boolean USING nsfw::text::boolean; +ALTER TABLE vn_screenshots ALTER COLUMN nsfw SET DEFAULT FALSE; + + +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 language + FROM releases_rev rr2 + 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 + ), ''/''), ''''), + 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; |