summaryrefslogtreecommitdiff
path: root/util/updates/update_1.21.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_1.21.sql')
-rw-r--r--util/updates/update_1.21.sql97
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;