summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-02-20 18:46:02 +0100
committerYorhel <git@yorhel.nl>2020-03-12 14:07:40 +0100
commit583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 (patch)
treee618a64c418f8e0c83cf02a0229011e77f4c1f91 /lib/VNDB
parentd3b7d3ff7a70e726d221d78f4e991ec76f75909f (diff)
SQL: Use global "images" table for metadata of all images in the DB
In preparation for an image flagging feature. This replaces the 'screenshots' table. I played around with various implementations of a primary key for the images table; A single integer encoding both the type and id is most efficient, but kind of ugly to work with. Two separate columns are also ugly and force the creation of a separate 'image_type' column on all referencing tables. The current composite type isn't all that much better, but at least it makes it easy to keep treating the image id as a single entity. Old VNDB::* code still treats it as an integer with different namespaces, new VNWeb::* code treats it as a proper composite type.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/Chars.pm11
-rw-r--r--lib/VNDB/DB/VN.pm34
2 files changed, 26 insertions, 19 deletions
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index a93ad28c..047f39b5 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -71,7 +71,8 @@ sub dbCharGet {
);
my @select = (qw|c.id c.name c.original c.gender|);
- push @select, qw|c.hidden c.locked c.alias c.desc c.image c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.cup_size c.age c.main c.main_spoil| if $o{what} =~ /extended/;
+ push @select, qw|c.hidden c.locked c.alias c.desc c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.cup_size c.age c.main c.main_spoil|,
+ 'coalesce((c.image).id,0) AS image' if $o{what} =~ /extended/;
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
@@ -94,7 +95,7 @@ sub dbCharGetRev {
my $select = 'c.itemid AS id, ch.name, ch.original, ch.gender';
$select .= ', extract(\'epoch\' from c.added) as added, c.comments, c.rev, c.ihid, c.ilock, '.VNWeb::DB::sql_user();
$select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
- $select .= ', ch.alias, ch.desc, ch.image, ch.b_month, ch.b_day, ch.s_bust, ch.s_waist, ch.s_hip, ch.height, ch.weight, ch.bloodt, ch.cup_size, ch.age, ch.main, ch.main_spoil, co.hidden, co.locked' if $o{what} =~ /extended/;
+ $select .= ', ch.alias, ch.desc, coalesce((ch.image).id, 0) as image, ch.b_month, ch.b_day, ch.s_bust, ch.s_waist, ch.s_hip, ch.height, ch.weight, ch.bloodt, ch.cup_size, ch.age, ch.main, ch.main_spoil, co.hidden, co.locked' if $o{what} =~ /extended/;
my $r = $self->dbAll(q|
SELECT !s
@@ -177,7 +178,9 @@ sub dbCharRevisionInsert {
my($self, $o) = @_;
my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
- qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt cup_size age gender main main_spoil|;
+ qw|name original alias desc b_month b_day s_bust s_waist s_hip height weight bloodt cup_size age gender main main_spoil|;
+ $set{'image = ROW(\'ch\',?)::image_id'} = $o->{image} if $o->{image};
+ $set{'image = NULL'} = 1 if exists $o->{image} && !$o->{image};
$self->dbExec('UPDATE edit_chars !H', \%set) if keys %set;
if($o->{traits}) {
@@ -193,7 +196,7 @@ sub dbCharRevisionInsert {
# fetches an ID for a new image
sub dbCharImageId {
- return shift->dbRow("SELECT nextval('charimg_seq') AS ni")->{ni};
+ return shift->dbRow(q|INSERT INTO images (id) VALUES (ROW('ch', nextval('charimg_seq'))::image_id) RETURNING (id).id|)->{id};
}
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index d099b6ff..3615d918 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -111,7 +111,7 @@ sub dbVNGet {
my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages and c_platforms
qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_olang::text[] v.c_platforms::text[] v.title v.original v.rgraph|,
$o{what} =~ /extended/ ? (
- qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai v.l_wikidata| ) : (),
+ qw|v.alias v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai v.l_wikidata|, 'coalesce((v.image).id,0) as image' ) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$o{what} =~ /ranking/ ? (
@@ -160,7 +160,7 @@ sub dbVNGetRev {
my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_olang::text[], vo.c_platforms::text[], v.title, v.original, vo.rgraph';
$select .= ', extract(\'epoch\' from c.added) as added, c.comments, c.rev, c.ihid, c.ilock, '.VNWeb::DB::sql_user();
$select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
- $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, v.l_wikidata, vo.hidden, vo.locked' if $o{what} =~ /extended/;
+ $select .= ', v.alias, coalesce((v.image).id, 0) as image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, v.l_wikidata, vo.hidden, vo.locked' if $o{what} =~ /extended/;
$select .= ', vo.c_popularity, vo.c_rating, vo.c_votecount' if $o{what} =~ /rating/;
$select .= ', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(vo.c_popularity, 0.0)) AS p_ranking'
.', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(vo.c_rating, 0.0)) AS r_ranking' if $o{what} =~ /ranking/;
@@ -245,9 +245,9 @@ sub _enrich {
if($what =~ /screenshots/) {
push(@{$r->[$r{ delete $_->{xid} }]{screenshots}}, $_) for (@{$self->dbAll("
- SELECT vs.$colname AS xid, s.id, vs.nsfw, vs.rid, s.width, s.height
+ SELECT vs.$colname AS xid, (s.id).id as id, vs.nsfw, vs.rid, s.width, s.height
FROM vn_screenshots$hist vs
- JOIN screenshots s ON vs.scr = s.id
+ JOIN images s ON vs.scr = s.id
WHERE vs.$colname IN(!l)
ORDER BY vs.scr",
[ keys %r ]
@@ -277,12 +277,14 @@ sub dbVNRevisionInsert {
$o->{img_nsfw} = $o->{img_nsfw}?1:0 if exists $o->{img_nsfw};
my %set = map exists($o->{$_}) ? (qq|"$_" = ?| => $o->{$_}) : (),
- qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_wikidata|;
+ qw|title original desc alias img_nsfw length l_wp l_encubed l_renai l_wikidata|;
+ $set{'image = ROW(\'cv\',?)::image_id'} = $o->{image} if $o->{image};
+ $set{'image = NULL'} = 1 if exists $o->{image} && !$o->{image};
$self->dbExec('UPDATE edit_vn !H', \%set) if keys %set;
if($o->{screenshots}) {
$self->dbExec('DELETE FROM edit_vn_screenshots');
- my $q = join ',', map '(?, ?, ?)', @{$o->{screenshots}};
+ my $q = join ',', map '(ROW(\'sf\', ?)::image_id, ?, ?)', @{$o->{screenshots}};
my @val = map +($_->{id}, $_->{nsfw}?1:0, $_->{rid}), @{$o->{screenshots}};
$self->dbExec("INSERT INTO edit_vn_screenshots (scr, nsfw, rid) VALUES $q", @val) if @val;
}
@@ -318,20 +320,20 @@ sub dbVNRevisionInsert {
# fetches an ID for a new image
sub dbVNImageId {
- return shift->dbRow("SELECT nextval('covers_seq') AS ni")->{ni};
+ return shift->dbRow(q|INSERT INTO images (id) VALUES (ROW('cv', nextval('covers_seq'))::image_id) RETURNING (id).id|)->{id};
}
# insert a new screenshot and return it's ID
sub dbScreenshotAdd {
my($s, $width, $height) = @_;
- return $s->dbRow(q|INSERT INTO screenshots (width, height) VALUES (?, ?) RETURNING id|, $width, $height)->{id};
+ return $s->dbRow(q|INSERT INTO images (id, width, height) VALUES (ROW('sf', nextval('screenshots_seq'))::image_id, ?, ?) RETURNING (id).id|, $width, $height)->{id};
}
# arrayref of screenshot IDs as argument
sub dbScreenshotGet {
- return shift->dbAll(q|SELECT * FROM screenshots WHERE id IN(!l)|, shift);
+ return shift->dbAll(q|SELECT (id).id, width, height FROM images WHERE id IN(SELECT ROW('sf', n::integer)::image_id FROM unnest(ARRAY[!l]) x(n))|, shift);
}
@@ -339,29 +341,31 @@ sub dbScreenshotGet {
# if any arguments are given, it will return one random screenshot for each VN
sub dbScreenshotRandom {
my($self, @vids) = @_;
+ # Assumption: id.id for screenshots doesn't have ~too~ many gaps (less than, say, 80%)
return $self->dbAll(q|
- SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title
- FROM screenshots s
+ SELECT (s.id).id AS scr, s.width, s.height, v.id AS vid, v.title
+ FROM images s
JOIN vn_screenshots vs ON vs.scr = s.id
JOIN vn v ON v.id = vs.id
WHERE NOT v.hidden AND NOT vs.nsfw
AND s.id IN(
- SELECT floor(random() * last_value)::integer
- FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM screenshots) s1
+ SELECT ROW('sf', floor(random() * (select last_value from screenshots_seq)))::image_id
+ FROM generate_series(1,20)
LIMIT 20
)
LIMIT 4|
) if !@vids;
+
# this query is faster than it looks
return $self->dbAll(join(' UNION ALL ', map
- q|SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position
+ q|SELECT (s.id).id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position
FROM (
SELECT vs2.id, vs2.scr FROM vn_screenshots vs2
WHERE vs2.id = ? AND NOT vs2.nsfw
ORDER BY RANDOM() LIMIT 1
) vs
JOIN vn v ON v.id = vs.id
- JOIN screenshots s ON s.id = vs.scr
+ JOIN images s ON s.id = vs.scr
|, @vids).' ORDER BY position', @vids);
}