diff options
author | Yorhel <git@yorhel.nl> | 2020-02-20 18:46:02 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-03-12 14:07:40 +0100 |
commit | 583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 (patch) | |
tree | e618a64c418f8e0c83cf02a0229011e77f4c1f91 /lib/VNDB | |
parent | d3b7d3ff7a70e726d221d78f4e991ec76f75909f (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.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 34 |
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); } |