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 | |
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
-rw-r--r-- | lib/Multi/RG.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Util/DB.pm | 52 | ||||
-rw-r--r-- | util/dump.sql | 28 | ||||
-rw-r--r-- | util/updates/update_1.21.sql | 97 |
4 files changed, 139 insertions, 40 deletions
diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index 8aace991..d94eb484 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -51,7 +51,7 @@ sub cmd_relgraph { if($_[ARG1] ne 'all') { $_[HEAP]{todo} = [ split /\s/, $_[ARG1] ]; } else { - my $q = $Multi::SQL->prepare('SELECT id FROM vn WHERE hidden = 0'); + my $q = $Multi::SQL->prepare('SELECT id FROM vn WHERE hidden = FALSE'); $q->execute; $_[HEAP]{todo} = [ map { $_->[0] } @{$q->fetchall_arrayref([])} ]; } diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index 683caec0..4063eb2a 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -111,7 +111,7 @@ sub DBCategoryCount { SELECT cat, COUNT(vid) AS cnt FROM vn_categories vc JOIN vn v ON v.latest = vc.vid - WHERE v.hidden = 0 + WHERE v.hidden = FALSE GROUP BY cat ORDER BY cnt| )} @@ -128,8 +128,8 @@ sub DBLanguageCount { JOIN releases r ON r.latest = rr.id JOIN releases_vn rv ON rv.rid = rr.id JOIN vn v ON v.id = rv.vid - WHERE r.hidden = 0 - AND v.hidden = 0 + WHERE r.hidden = FALSE + AND v.hidden = FALSE AND rr.type <> 2 AND rr.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer GROUP BY rr.language|)} }; @@ -142,7 +142,7 @@ sub DBTableCount { # table (users, producers, vn, releases, votes) FROM %s %s|, $_[1], - $_[1] =~ /producers|vn|releases/ ? 'WHERE hidden = 0' : '', + $_[1] =~ /producers|vn|releases/ ? 'WHERE hidden = FALSE' : '', )->{cnt} - ($_[1] eq 'users' ? 1 : 0); } @@ -185,9 +185,9 @@ sub DBGetHist { # %options->{ type, id, cid, caused, next, page, results, ip, ed # get rid of 'hidden' items !$o{showhid} ? ( - '(v.hidden IS NOT NULL AND v.hidden = 0 OR r.hidden IS NOT NULL AND r.hidden = 0 OR p.hidden IS NOT NULL AND p.hidden = 0)' => 1, + '(v.hidden IS NOT NULL AND v.hidden = FALSE OR r.hidden IS NOT NULL AND r.hidden = FALSE OR p.hidden IS NOT NULL AND p.hidden = FALSE)' => 1, ) : $o{showhid} == 2 ? ( - '(v.hidden IS NOT NULL AND v.hidden = 1 OR r.hidden IS NOT NULL AND r.hidden = 1 OR p.hidden IS NOT NULL AND p.hidden = 1)' => 1, + '(v.hidden IS NOT NULL AND v.hidden = TRUE OR r.hidden IS NOT NULL AND r.hidden = TRUE OR p.hidden IS NOT NULL AND p.hidden = TRUE)' => 1, ) : (), ); @@ -234,7 +234,7 @@ sub DBLockItem { # table, id, locked my($s, $tbl, $id, $l) = @_; $s->DBExec(q| UPDATE %s - SET locked = %d + SET locked = !b WHERE id = %d|, $tbl, $l, $id); } @@ -714,7 +714,7 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate my %where = ( !$o{id} && !$o{rev} ? ( # don't fetch hidden items unless we ask for an ID - 'v.hidden = 0' => 1 ) : (), + 'v.hidden = FALSE' => 1 ) : (), $o{id} && !ref($o{id}) ? ( 'v.id = %d' => $o{id} ) : (), $o{id} && ref($o{id}) ? ( @@ -904,7 +904,7 @@ sub _insert_vn_rev { $s->DBExec(q| INSERT INTO vn_rev (id, vid, title, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn) - VALUES (%d, %d, !s, !s, !s, %d, %d, %d, !s, !s, !s, %d)|, + VALUES (%d, %d, !s, !s, !s, %d, !b, %d, !s, !s, !s, %d)|, $cid, $vid, @$o{qw|title desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|}); $s->DBExec(q| @@ -915,8 +915,8 @@ sub _insert_vn_rev { $s->DBExec(q| INSERT INTO vn_screenshots (vid, scr, nsfw) - VALUES (%d, %d, %d)|, - $cid, $_->[0], $_->[1]?1:0 + VALUES (%d, %d, !b)|, + $cid, $_->[0], $_->[1] ) for (@{$o->{screenshots}}); $s->DBExec(q| @@ -950,7 +950,7 @@ sub DBHideVN { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE vn - SET hidden = %d + SET hidden = !b WHERE id = %d|, $h, $id); @@ -994,7 +994,7 @@ sub DBGetRelease { # %options->{ id vid results page rev } $o{order} ||= 'rr.released ASC'; my %where = ( !$o{id} && !$o{rev} ? ( - 'r.hidden = 0' => 1 ) : (), + 'r.hidden = FALSE' => 1 ) : (), $o{id} ? ( 'r.id = %d' => $o{id} ) : (), $o{rev} ? ( @@ -1165,7 +1165,7 @@ sub DBHideRelease { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE releases - SET hidden = %d + SET hidden = !b WHERE id = %d|, $h, $id); } @@ -1186,7 +1186,7 @@ sub DBGetProducer { # %options->{ id search char results page rev } $o{what} ||= ''; my %where = ( !$o{id} && !$o{rev} ? ( - 'p.hidden = 0' => 1 ) : (), + 'p.hidden = FALSE' => 1 ) : (), $o{id} ? ( 'p.id = %d' => $o{id} ) : (), $o{search} ? ( @@ -1238,7 +1238,7 @@ sub DBGetProducerVN { # pid JOIN vn v ON v.id = rv.vid JOIN vn_rev vr ON vr.id = v.latest WHERE vp.pid = %d - AND v.hidden = 0 + AND v.hidden = FALSE GROUP BY v.id ORDER BY date|, $_[1]); @@ -1303,7 +1303,7 @@ sub DBHideProducer { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE producers - SET hidden = %d + SET hidden = !b WHERE id = %d|, $h, $id); } @@ -1329,7 +1329,7 @@ sub DBGetThreads { # %options->{ id type iid results page what } $o{id} ? ( 't.id = %d' => $o{id} ) : (), !$o{id} ? ( - 't.hidden = 0' => 1 ) : (), + 't.hidden = FALSE' => 1 ) : (), $o{type} && !$o{iid} ? ( 't.id IN(SELECT tid FROM threads_tags WHERE type = !s)' => $o{type} ) : (), $o{type} && $o{iid} ? ( @@ -1450,7 +1450,7 @@ sub DBEditPost { # %options->{ tid num msg hidden } my %set = ( 'msg = !s' => $o{msg}, 'edited = %d' => time, - 'hidden = %d' => $o{hidden}?1:0, + 'hidden = !b' => $o{hidden}, ); $s->DBExec(q| @@ -1468,8 +1468,8 @@ sub DBEditThread { # %options->{ id title locked hidden tags } my %set = ( 'title = !s' => $o{title}, - 'locked = %d' => $o{locked}?1:0, - 'hidden = %d' => $o{hidden}?1:0, + 'locked = !b' => $o{locked}, + 'hidden = !b' => $o{hidden}, ); $s->DBExec(q| @@ -1494,9 +1494,9 @@ sub DBAddThread { # %options->{ title hidden locked tags } my $id = $s->DBRow(q| INSERT INTO threads (title, hidden, locked) - VALUES (!s, %d, %d) + VALUES (!s, !b, !b) RETURNING id|, - $o{title}, $o{hidden}?1:0, $o{locked}?1:0 + $o{title}, $o{hidden}, $o{locked} )->{id}; $s->DBExec(q| @@ -1559,6 +1559,7 @@ sub sqlhelper { # type, query, @list # Added features: # !s SQL-quote +# !b boolean (anything perl considers true = 'TRUE', otherwise 'FALSE') # !l listify # !L SQL-quote-and-listify # !H list of SET-items: key = format, value = replacement @@ -1577,10 +1578,11 @@ sub sqlprint { $arg[$i] = $_[$i]; next; } - if($d !~ /[slLHW]/) { + if($d !~ /[sblLHW]/) { $i--; next } $arg[$i] = qs($_[$i]) if $d eq 's'; + $arg[$i] = $_[$i] ? 'TRUE' : 'FALSE' if $d eq 'b'; $arg[$i] = join(',', @{$_[$i]}) if $d eq 'l'; $arg[$i] = join(',', (qs(@{$_[$i]}))) if $d eq 'L'; if($d eq 'H' || $d eq 'W') { @@ -1589,7 +1591,7 @@ sub sqlprint { $arg[$i] = join($d eq 'H' ? ', ' : ' AND ', @i); } } - $s =~ s/![sSlLHW]/%s/g; + $s =~ s/![sblLHW]/%s/g; $s =~ s/!!/!/g; return sprintf($s, @arg); } 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; |