summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authoryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-15 13:47:18 +0000
committeryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-15 13:47:18 +0000
commit9a72f22e7500624666f4e1e2ba635b6bd9a6ca3f (patch)
treee71c949392fe7f50d226822c08c619e140b6d36e /util
parent69925240734c9e7d0a562e695b3690c2960b96d6 (diff)
Converted smallints used as boolean to PostgreSQL's relatively new (8.1) boolean type.1.21
git-svn-id: svn://vndb.org/vndb@90 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql28
-rw-r--r--util/updates/update_1.21.sql97
2 files changed, 111 insertions, 14 deletions
diff --git a/util/dump.sql b/util/dump.sql
index a96365ac..3294179e 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -45,8 +45,8 @@ CREATE TABLE changes (
CREATE TABLE producers (
id SERIAL NOT NULL PRIMARY KEY,
latest integer NOT NULL DEFAULT 0,
- locked smallint NOT NULL DEFAULT 0,
- hidden smallint NOT NULL DEFAULT 0
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
);
-- producers_rev
@@ -65,8 +65,8 @@ CREATE TABLE producers_rev (
CREATE TABLE releases (
id SERIAL NOT NULL PRIMARY KEY,
latest integer NOT NULL DEFAULT 0,
- locked smallint NOT NULL DEFAULT 0,
- hidden smallint NOT NULL DEFAULT 0
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
);
-- releases_media
@@ -135,8 +135,8 @@ CREATE TABLE screenshots (
CREATE TABLE threads (
id SERIAL NOT NULL PRIMARY KEY,
title varchar(50) NOT NULL DEFAULT '',
- locked smallint NOT NULL DEFAULT 0,
- hidden smallint NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
count smallint NOT NULL DEFAULT 0
);
@@ -148,7 +148,7 @@ CREATE TABLE threads_posts (
date bigint NOT NULL DEFAULT DATE_PART('epoch', NOW()),
edited bigint NOT NULL DEFAULT 0,
msg text NOT NULL DEFAULT '',
- hidden smallint NOT NULL DEFAULT 0,
+ hidden boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY(tid, num)
);
@@ -175,8 +175,8 @@ CREATE TABLE users (
CREATE TABLE vn (
id SERIAL NOT NULL PRIMARY KEY,
latest integer NOT NULL DEFAULT 0,
- locked smallint NOT NULL DEFAULT 0,
- hidden smallint NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
rgraph integer NOT NULL DEFAULT 0,
c_released integer NOT NULL DEFAULT 0,
c_languages varchar(32) NOT NULL DEFAULT '',
@@ -212,7 +212,7 @@ CREATE TABLE vn_rev (
vid integer NOT NULL DEFAULT 0,
title varchar(250) NOT NULL DEFAULT '',
alias varchar(500) NOT NULL DEFAULT '',
- img_nsfw smallint NOT NULL DEFAULT 0,
+ img_nsfw boolean NOT NULL DEFAULT FALSE,
length smallint NOT NULL DEFAULT 0,
"desc" text NOT NULL DEFAULT '',
l_wp varchar(150) NOT NULL DEFAULT '',
@@ -226,7 +226,7 @@ CREATE TABLE vn_rev (
CREATE TABLE vn_screenshots (
vid integer NOT NULL DEFAULT 0,
scr integer NOT NULL DEFAULT 0,
- nsfw smallint NOT NULL DEFAULT 0,
+ nsfw boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY(vid, scr)
);
@@ -354,7 +354,7 @@ BEGIN
JOIN releases_vn rv1 ON rr1.id = rv1.rid
WHERE rv1.vid = vn.id
AND rr1.type <> 2
- AND r1.hidden = 0
+ AND r1.hidden = FALSE
AND rr1.released <> 0
GROUP BY rv1.vid
), 0),
@@ -366,7 +366,7 @@ BEGIN
WHERE rv2.vid = vn.id
AND rr2.type <> 2
AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
- AND r2.hidden = 0
+ AND r2.hidden = FALSE
GROUP BY rr2.language
ORDER BY rr2.language
), ''/''), ''''),
@@ -379,7 +379,7 @@ BEGIN
WHERE rv3.vid = vn.id
AND rr3.type <> 2
AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
- AND r3.hidden = 0
+ AND r3.hidden = FALSE
GROUP BY rp3.platform
ORDER BY rp3.platform
), ''/''), '''')
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;