summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/Multi/API.pm8
-rw-r--r--lib/Multi/IRC.pm21
-rw-r--r--lib/VNDB/DB/Chars.pm11
-rw-r--r--lib/VNDB/DB/VN.pm34
-rw-r--r--lib/VNWeb/Chars/Page.pm4
-rwxr-xr-xutil/dbdump.pl11
-rwxr-xr-xutil/devdump.pl25
-rw-r--r--util/sql/perms.sql8
-rw-r--r--util/sql/schema.sql30
-rw-r--r--util/sql/tableattrs.sql8
-rwxr-xr-xutil/unusedimages.pl31
-rw-r--r--util/updates/2020-03-06-images-table.sql58
-rwxr-xr-xutil/vndb.pl15
13 files changed, 170 insertions, 94 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index d7c59378..1ece822b 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -417,7 +417,7 @@ my %GET_VN = (
},
},
details => {
- select => 'v.image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai, l_wikidata',
+ select => '(v.image).id as image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai, l_wikidata',
proc => sub {
$_[0]{aliases} ||= undef;
$_[0]{length} *= 1;
@@ -490,8 +490,8 @@ my %GET_VN = (
]],
},
screens => {
- fetch => [[ 'id', 'SELECT vs.id AS vid, vs.scr AS image, vs.rid, vs.nsfw, s.width, s.height
- FROM vn_screenshots vs JOIN screenshots s ON s.id = vs.scr WHERE vs.id IN(%s)',
+ fetch => [[ 'id', 'SELECT vs.id AS vid, (vs.scr).id AS image, vs.rid, vs.nsfw, s.width, s.height
+ FROM vn_screenshots vs JOIN images s ON s.id = vs.scr WHERE vs.id IN(%s)',
sub { my($r, $n) = @_;
for my $i (@$r) {
$i->{screens} = [ grep $i->{id} == $_->{vid}, @$n ];
@@ -817,7 +817,7 @@ my %GET_CHARACTER = (
},
},
details => {
- select => 'c.alias AS aliases, c.image, c."desc" AS description',
+ select => 'c.alias AS aliases, (c.image).id as image, c."desc" AS description',
proc => sub {
$_[0]{aliases} ||= undef;
$_[0]{image} = $_[0]{image} ? sprintf '%s/ch/%02d/%d.jpg', config->{url_static}, $_[0]{image}%100, $_[0]{image} : undef;
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 503a1543..6c86d2f9 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -442,27 +442,6 @@ p => [ 0, 0, sub {
};
}],
-scr => [ 0, 0, sub {
- my($nick, $chan, $q) = @_;
- return $irc->send_msg(PRIVMSG => $chan,
- q|Sorry, I failed to comprehend which screenshot you'd like me to lookup for you,|
- .q| please understand that Yorhel was not willing to supply me with mind reading capabilities.|)
- if !$q || $q !~ /([0-9]+)\.jpg/;
- $q = $1;
- pg_cmd q{
- SELECT 'v'::text AS type, v.id, v.title
- FROM changes c
- JOIN vn_screenshots_hist vsh ON vsh.chid = c.id
- JOIN vn v ON v.id = c.itemid
- WHERE vsh.scr = $1 LIMIT 1
- }, [ $q ], sub {
- my $res = shift;
- return if pg_expect $res, 1;
- return $irc->send_msg(PRIVMSG => $chan, "Couldn't find a VN with that screenshot ID.") if !$res->nRows;
- formatid([$res->rowsAsHashes()], $chan, 0);
- };
-}],
-
die => [ 1, 1, sub {
kill 'TERM', 0;
}],
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);
}
diff --git a/lib/VNWeb/Chars/Page.pm b/lib/VNWeb/Chars/Page.pm
index 49acf20e..6e759d0b 100644
--- a/lib/VNWeb/Chars/Page.pm
+++ b/lib/VNWeb/Chars/Page.pm
@@ -83,7 +83,7 @@ sub _rev_ {
a_ href => "/c$c->{id}", title => $c->{name}, "c$c->{id}"
} ],
[ main_spoil => 'Spoiler', fmt => sub { txt_ fmtspoil $_ } ],
- [ image => 'Image', empty => 0, fmt => sub { img_ src => tuwf->imgurl(ch => $_) } ],
+ [ image => 'Image', empty => 0, fmt => sub { img_ src => tuwf->imgurl($_) } ],
[ vns => 'Visual novels', fmt => sub {
a_ href => "/v$_->{vid}", title => $_->{original}||$_->{title}, "v$_->{vid}";
if($_->{rid}) {
@@ -109,7 +109,7 @@ sub chartable_ {
div_ mkclass(chardetails => 1, charsep => $sep), sub {
div_ class => 'charimg', sub {
p_ 'No image uploaded yet' if !$c->{image};
- img_ src => tuwf->imgurl(ch => $c->{image}), alt => $c->{name} if $c->{image};
+ img_ src => tuwf->imgurl($c->{image}), alt => $c->{name} if $c->{image};
};
table_ class => 'stripe', sub {
thead_ sub { tr_ sub { td_ colspan => 2, sub {
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 8f0cc5f8..f2f5d4ca 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -56,6 +56,9 @@ my %tables = (
.' AND (rid IS NULL OR rid IN(SELECT id FROM releases WHERE NOT hidden))'
, order => 'id, vid, rid' },
docs => { where => 'NOT hidden' },
+ images => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden'
+ .' UNION SELECT image FROM chars WHERE image IS NOT NULL AND NOT hidden'
+ .' UNION SELECT image from vn WHERE image IS NOT NULL AND NOT hidden)' },
producers => { where => 'NOT hidden' },
producers_relations => { where => 'id IN(SELECT id FROM producers WHERE NOT hidden)' },
releases => { where => 'NOT hidden' },
@@ -70,7 +73,6 @@ my %tables = (
.' JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid'
.' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
.' WHERE r.id = rlists.rid AND uvl.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT ul.private)' },
- screenshots => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden)' },
staff => { where => 'NOT hidden' },
staff_alias => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' },
tags => { where => 'state = 2' },
@@ -169,6 +171,7 @@ sub export_import_script {
_
print $F "\n\n";
+ print $F "$types->{image_type}{decl}\n";
my %types = map +($_->{type}, 1), grep $_->{pub}, map @{$schema->{$_->{name}}{cols}}, @tables;
print $F "$types->{$_}{decl}\n" for (sort grep $types->{$_}, keys %types);
@@ -247,9 +250,9 @@ sub export_img {
my %scr;
my %dir = (ch => {}, cv => {}, sf => \%scr, st => \%scr);
- $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT id FROM screenshots WHERE $tables{screenshots}{where} ORDER BY id");
- $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT image FROM vn WHERE image <> 0 AND $tables{vn}{where} ORDER BY image");
- $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT image FROM chars WHERE image <> 0 AND $tables{chars}{where} ORDER BY image");
+ $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT (scr).id FROM vn_screenshots WHERE $tables{vn_screenshots}{where}");
+ $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT (image).id FROM vn WHERE image IS NOT NULL AND $tables{vn}{where}");
+ $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT (image).id FROM chars WHERE image IS NOT NULL AND $tables{chars}{where}");
$db->rollback;
undef $db;
diff --git a/util/devdump.pl b/util/devdump.pl
index 1134e7b7..5fb4a916 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -8,6 +8,12 @@ use warnings;
use autodie;
use DBI;
use DBD::Pg;
+use Cwd 'abs_path';
+
+my $ROOT;
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/devdump\.pl$}{}; }
+
+use lib $ROOT.'/lib';
my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 });
@@ -29,7 +35,11 @@ my $characters = $db->selectcol_arrayref(
."UNION "
."SELECT DISTINCT h.main FROM chars_vns_hist e JOIN changes c ON c.id = e.chid JOIN chars_hist h ON h.chid = e.chid WHERE e.vid IN($vids) AND h.main IS NOT NULL"
);
-
+my $images = $db->selectcol_arrayref(q{
+ SELECT image FROM chars_hist ch JOIN changes c ON c.id = ch.chid WHERE c.type = 'c' AND c.itemid IN(}.join(',',@$characters).qq{) AND ch.image IS NOT NULL
+ UNION SELECT image FROM vn_hist vh JOIN changes c ON c.id = vh.chid WHERE c.type = 'v' AND c.itemid IN($vids) AND vh.image IS NOT NULL
+ UNION SELECT scr FROM vn_screenshots_hist vs JOIN changes c ON c.id = vs.chid WHERE c.type = 'v' AND c.itemid IN($vids)
+});
# Helper function to copy a table or SQL statement. Can do modifications on a
@@ -121,6 +131,9 @@ sub copy_entry {
# Wikidata (TODO: This could be a lot more selective)
copy 'wikidata';
+ # Image metadata
+ copy images => 'SELECT * FROM images WHERE id IN('.join(',',map "'$_'", @$images).')';
+
# Threads (announcements)
my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") };
copy threads => "SELECT * FROM threads WHERE id IN($threads)";
@@ -141,7 +154,6 @@ sub copy_entry {
copy_entry c => [qw/chars chars_traits chars_vns/], $characters;
# Visual novels
- copy screenshots => "SELECT DISTINCT s.* FROM screenshots s JOIN vn_screenshots_hist v ON v.scr = s.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($vids)";
copy anime => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($vids)";
copy relgraphs => "SELECT DISTINCT ON (r.id) r.* FROM relgraphs r JOIN vn v ON v.rgraph = r.id WHERE v.id IN($vids)", {};
copy_entry v => [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
@@ -181,11 +193,8 @@ sub copy_entry {
# Now figure out which images we need, and throw everything in a tarball
-sub imgs { map sprintf('static/%s/%02d/%d.jpg', $_[0], $_%100, $_), @{$_[1]} }
-
-my $ch = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM chars_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'c' AND e.image <> 0 AND c.itemid IN(".join(',', @$characters).")");
-my $cv = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM vn_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND e.image <> 0 AND c.itemid IN($vids)");
-my $sf = $db->selectcol_arrayref("SELECT DISTINCT e.scr FROM vn_screenshots_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND c.itemid IN($vids)");
+sub img { sprintf 'static/%s/%02d/%d.jpg', $_[0], $_[1]%100, $_[1] }
+my @imgpaths = sort map { my($t,$id) = /\((.+),(.+)\)/; (img($t, $id), $t eq 'sf' ? img('st', $id) : ()) } @$images;
-system("tar -czf devdump.tar.gz dump.sql ".join ' ', imgs(ch => $ch), imgs(cv => $cv), imgs(sf => $sf), imgs(st => $sf));
+system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths);
unlink 'dump.sql';
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index b4833a60..7410a688 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -3,7 +3,7 @@
DROP OWNED BY vndb_site;
GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site;
-GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
GRANT SELECT, INSERT ON anime TO vndb_site;
GRANT SELECT, INSERT ON changes TO vndb_site;
@@ -15,6 +15,7 @@ GRANT SELECT, INSERT, DELETE ON chars_vns TO vndb_site;
GRANT SELECT, INSERT ON chars_vns_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON docs TO vndb_site;
GRANT SELECT, INSERT ON docs_hist TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON images TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site;
@@ -36,7 +37,6 @@ GRANT SELECT, INSERT, DELETE ON releases_vn TO vndb_site;
GRANT SELECT, INSERT ON releases_vn_hist TO vndb_site;
GRANT SELECT ON relgraphs TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON screenshots TO vndb_site;
-- No access to the 'sessions' table, managed by the user_* functions.
GRANT SELECT ON shop_denpa TO vndb_site;
GRANT SELECT ON shop_dlsite TO vndb_site;
@@ -94,7 +94,7 @@ GRANT SELECT, INSERT ON wikidata TO vndb_site;
DROP OWNED BY vndb_multi;
GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_multi;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_multi;
-GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
GRANT SELECT, UPDATE ON anime TO vndb_multi;
GRANT SELECT ON changes TO vndb_multi;
@@ -104,6 +104,7 @@ GRANT SELECT ON chars_traits TO vndb_multi;
GRANT SELECT ON chars_vns TO vndb_multi;
GRANT SELECT ON docs TO vndb_multi;
GRANT SELECT ON docs_hist TO vndb_multi;
+GRANT SELECT ON images TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_multi;
GRANT SELECT, UPDATE ON producers TO vndb_multi;
@@ -119,7 +120,6 @@ GRANT SELECT ON releases_producers TO vndb_multi;
GRANT SELECT ON releases_vn TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON relgraphs TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_multi;
-GRANT SELECT ON screenshots TO vndb_multi;
GRANT SELECT (expires) ON sessions TO vndb_multi;
GRANT DELETE ON sessions TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 28048f5e..9f86b9be 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -68,9 +68,13 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
CREATE TYPE session_type AS ENUM ('web', 'pass', 'mail');
+CREATE TYPE image_type AS ENUM ('ch', 'cv', 'sf');
+CREATE TYPE image_id AS (itype image_type, id int);
+
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
CREATE SEQUENCE charimg_seq;
+CREATE SEQUENCE screenshots_seq;
@@ -108,7 +112,7 @@ CREATE TABLE chars ( -- dbentry_type=c
name varchar(250) NOT NULL DEFAULT '', -- [pub]
original varchar(250) NOT NULL DEFAULT '', -- [pub]
alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- image integer NOT NULL DEFAULT 0, -- [pub]
+ image image_id CONSTRAINT chars_image_check CHECK((image).itype = 'ch'), -- [pub]
"desc" text NOT NULL DEFAULT '', -- [pub]
gender gender NOT NULL DEFAULT 'unknown', -- [pub]
s_bust smallint NOT NULL DEFAULT 0, -- [pub]
@@ -131,7 +135,7 @@ CREATE TABLE chars_hist (
name varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
alias varchar(500) NOT NULL DEFAULT '',
- image integer NOT NULL DEFAULT 0,
+ image image_id CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch'),
"desc" text NOT NULL DEFAULT '',
gender gender NOT NULL DEFAULT 'unknown',
s_bust smallint NOT NULL DEFAULT 0,
@@ -200,6 +204,13 @@ CREATE TABLE docs_hist (
html text -- cache
);
+-- images
+CREATE TABLE images (
+ id image_id NOT NULL PRIMARY KEY, -- [pub]
+ width smallint, -- [pub] dimensions are only set for the 'sf' type (for now)
+ height smallint -- [pub]
+);
+
-- login_throttle
CREATE TABLE login_throttle (
ip inet NOT NULL PRIMARY KEY,
@@ -449,13 +460,6 @@ CREATE TABLE rlists (
PRIMARY KEY(uid, rid)
);
--- screenshots
-CREATE TABLE screenshots (
- id SERIAL NOT NULL PRIMARY KEY, -- [pub]
- width smallint NOT NULL DEFAULT 0, -- [pub]
- height smallint NOT NULL DEFAULT 0 -- [pub]
-);
-
-- sessions
CREATE TABLE sessions (
uid integer NOT NULL,
@@ -800,7 +804,7 @@ CREATE TABLE vn ( -- dbentry_type=v
alias varchar(500) NOT NULL DEFAULT '', -- [pub]
length smallint NOT NULL DEFAULT 0, -- [pub]
img_nsfw boolean NOT NULL DEFAULT FALSE, -- [pub]
- image integer NOT NULL DEFAULT 0, -- [pub]
+ image image_id CONSTRAINT vn_image_check CHECK((image).itype = 'cv'), -- [pub]
"desc" text NOT NULL DEFAULT '', -- [pub]
l_wp varchar(150) NOT NULL DEFAULT '', -- [pub] (deprecated)
l_encubed varchar(100) NOT NULL DEFAULT '', -- [pub] (deprecated)
@@ -825,7 +829,7 @@ CREATE TABLE vn_hist (
alias varchar(500) NOT NULL DEFAULT '',
length smallint NOT NULL DEFAULT 0,
img_nsfw boolean NOT NULL DEFAULT FALSE,
- image integer NOT NULL DEFAULT 0,
+ image image_id CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv'),
"desc" text NOT NULL DEFAULT '',
l_wp varchar(150) NOT NULL DEFAULT '',
l_encubed varchar(100) NOT NULL DEFAULT '',
@@ -868,7 +872,7 @@ CREATE TABLE vn_relations_hist (
-- vn_screenshots
CREATE TABLE vn_screenshots (
id integer NOT NULL, -- [pub]
- scr integer NOT NULL, -- [pub] screenshots.id
+ scr image_id NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf'), -- [pub] images.id
rid integer, -- [pub] releases.id (only NULL for old revisions, nowadays not allowed anymore)
nsfw boolean NOT NULL DEFAULT FALSE, -- [pub]
PRIMARY KEY(id, scr)
@@ -877,7 +881,7 @@ CREATE TABLE vn_screenshots (
-- vn_screenshots_hist
CREATE TABLE vn_screenshots_hist (
chid integer NOT NULL,
- scr integer NOT NULL,
+ scr image_id NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf'),
rid integer,
nsfw boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY(chid, scr)
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 5c894a85..191c78e6 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -2,8 +2,10 @@
ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE chars ADD CONSTRAINT chars_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
+ALTER TABLE chars ADD CONSTRAINT chars_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
@@ -75,8 +77,10 @@ ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
@@ -87,10 +91,10 @@ ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid_fkey
ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
diff --git a/util/unusedimages.pl b/util/unusedimages.pl
index b5eb3989..f1a23377 100755
--- a/util/unusedimages.pl
+++ b/util/unusedimages.pl
@@ -24,9 +24,17 @@ my %dir = (cv => \%cv, ch => \%ch, sf => \%scr, st => \%scr);
sub cleandb {
my $cnt = $db->do(q{
- DELETE FROM screenshots s
- WHERE NOT EXISTS(SELECT 1 FROM vn_screenshots_hist WHERE scr = s.id)
- AND NOT EXISTS(SELECT 1 FROM vn_screenshots WHERE scr = s.id)
+ DELETE FROM images WHERE id IN(
+ SELECT id FROM images EXCEPT
+ SELECT * FROM (
+ SELECT scr FROM vn_screenshots
+ UNION SELECT scr FROM vn_screenshots_hist
+ UNION SELECT image FROM vn WHERE image IS NOT NULL
+ UNION SELECT image FROM vn_hist WHERE image IS NOT NULL
+ UNION SELECT image FROM chars WHERE image IS NOT NULL
+ UNION SELECT image FROM chars_hist WHERE image IS NOT NULL
+ ) x
+ )
});
print "# Deleted unreferenced screenshots: $cnt\n";
}
@@ -50,17 +58,16 @@ sub addtxtsql {
print "# References in $name... $count\n";
}
-sub addnumsql {
- my($name, $tbl, $query) = @_;
- $count = 0;
- my $st = $db->prepare($query);
+sub addimagessql {
+ my $st = $db->prepare('SELECT (id).itype, (id).id FROM images');
$st->execute();
+ $count = 0;
while((my $num = $st->fetch())) {
- $tbl->{$num->[0]} = 1;
+ $dir{$num->[0]}{$num->[1]} = 1;
$count++;
}
- print "# Items in $name... $count\n";
-}
+ print "# Items in `images'... $count\n";
+};
sub findunused {
my $size = 0;
@@ -100,7 +107,5 @@ addtxtsql 'Tag descriptions', 'SELECT description FROM tags';
addtxtsql 'Trait descriptions', 'SELECT description FROM traits';
addtxtsql 'Change summaries', 'SELECT comments FROM changes';
addtxtsql 'Posts', 'SELECT msg FROM threads_posts';
-addnumsql 'Screenshots', \%scr, 'SELECT id FROM screenshots';
-addnumsql 'VN images', \%cv, 'SELECT image FROM vn UNION ALL SELECT image from vn_hist';
-addnumsql 'Character images', \%ch, 'SELECT image FROM chars UNION ALL SELECT image from chars_hist';
+addimagessql;
findunused;
diff --git a/util/updates/2020-03-06-images-table.sql b/util/updates/2020-03-06-images-table.sql
new file mode 100644
index 00000000..f3db7975
--- /dev/null
+++ b/util/updates/2020-03-06-images-table.sql
@@ -0,0 +1,58 @@
+CREATE TYPE image_type AS ENUM ('ch', 'cv', 'sf');
+CREATE TYPE image_id AS (itype image_type, id int);
+
+CREATE TABLE images (
+ id image_id NOT NULL PRIMARY KEY CHECK((id).id IS NOT NULL AND (id).itype IS NOT NULL),
+ width smallint, -- dimensions are only set for the 'sf' type (for now)
+ height smallint
+);
+
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
+INSERT INTO images (id, width, height)
+ SELECT ROW('sf', id)::image_id, width, height FROM screenshots
+UNION ALL
+ SELECT ROW('cv', image)::image_id, null, null FROM vn_hist WHERE image <> 0 GROUP BY image
+UNION ALL
+ SELECT ROW('ch', image)::image_id, null, null FROM chars_hist WHERE image <> 0 GROUP BY image;
+
+
+ALTER TABLE vn ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE vn ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE vn ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('cv', image)::image_id END;
+ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn ADD CONSTRAINT vn_image_check CHECK((image).itype = 'cv');
+ALTER TABLE vn_hist ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE vn_hist ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE vn_hist ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('cv', image)::image_id END;
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv');
+
+ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_fkey;
+ALTER TABLE vn_screenshots ALTER COLUMN scr TYPE image_id USING CASE WHEN scr = 0 THEN NULL ELSE ROW('sf', scr)::image_id END;
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf');
+ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_scr_fkey;
+ALTER TABLE vn_screenshots_hist ALTER COLUMN scr TYPE image_id USING CASE WHEN scr = 0 THEN NULL ELSE ROW('sf', scr)::image_id END;
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf');
+
+ALTER TABLE chars ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE chars ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE chars ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('ch', image)::image_id END;
+ALTER TABLE chars ADD CONSTRAINT chars_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE chars ADD CONSTRAINT chars_image_check CHECK((image).itype = 'ch');
+ALTER TABLE chars_hist ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE chars_hist ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE chars_hist ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('ch', image)::image_id END;
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch');
+
+COMMIT;
+
+CREATE SEQUENCE screenshots_seq;
+SELECT setval('screenshots_seq', nextval('screenshots_id_seq'));
+DROP TABLE screenshots;
+
+\i util/sql/perms.sql
diff --git a/util/vndb.pl b/util/vndb.pl
index 405b32a9..0741bfaf 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -12,6 +12,7 @@ BEGIN { ($ROOT = abs_path $0) =~ s{/util/vndb\.pl$}{}; }
use lib $ROOT.'/lib';
use SkinFile;
+use VNDB::Func ();
use VNDB::Config;
use VNWeb::Auth;
use VNWeb::HTML ();
@@ -37,11 +38,17 @@ TUWF::set %{ config->{tuwf} };
tuwf->{elmgen} = $ARGV[0] && $ARGV[0] eq 'elmgen';
-# tuwf->imgpath(cg => $image_id)
-sub TUWF::Object::imgpath { sprintf '%s/%s/%02d/%d.jpg', $ROOT, $_[1], $_[2]%100, $_[2] }
+sub _path {
+ my($t, $id) = $_[1] =~ /\(([a-z]+),([0-9]+)\)/;
+ $t = 'st' if $t eq 'sf' && $_[2];
+ sprintf '%s/%s/%02d/%d.jpg', $_[0], $t, $id%100, $id;
+}
+
+# tuwf->imgpath($image_id, $thumb)
+sub TUWF::Object::imgpath { _path $ROOT, $_[1], $_[2] }
-# tuwf->imgurl(cv => $image_id)
-sub TUWF::Object::imgurl { sprintf '%s/%s/%02d/%d.jpg', $_[0]->{url_static}, $_[1], $_[2]%100, $_[2] }
+# tuwf->imgurl($image_id, $thumb)
+sub TUWF::Object::imgurl { _path $_[0]{url_static}, $_[1], $_[2] }
TUWF::hook before => sub {
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 04:07:21 +0000