summaryrefslogtreecommitdiff
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
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
-rw-r--r--lib/Multi/RG.pm2
-rw-r--r--lib/VNDB/Util/DB.pm52
-rw-r--r--util/dump.sql28
-rw-r--r--util/updates/update_1.21.sql97
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;