diff options
author | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-15 13:47:18 +0000 |
---|---|---|
committer | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-15 13:47:18 +0000 |
commit | 9a72f22e7500624666f4e1e2ba635b6bd9a6ca3f (patch) | |
tree | e71c949392fe7f50d226822c08c619e140b6d36e /util | |
parent | 69925240734c9e7d0a562e695b3690c2960b96d6 (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.sql | 28 | ||||
-rw-r--r-- | util/updates/update_1.21.sql | 97 |
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; |