summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-04-05 15:18:05 +0200
committerYorhel <git@yorhel.nl>2020-04-05 15:18:27 +0200
commit6d767e48b63c30fd572cff1fdf73bb2e60574848 (patch)
treeb330f7f37b42293fad2579fb7788e2be5ca7ecb6
parenta53e57953a9f4a9912c70901b3841fd30a80bd1d (diff)
SQL: Add C-based "vndbid" type and use it for image IDs
I had already rambled on the current composite type solution in 583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 and I had already explored a few alternatives. This was the one alternative I hadn't yet explored because I wasn't sure the operational complexity was going to be worth it, but after seeing how bad PostgreSQL was at optimizing queries with composite types, I figured I might as well just go with this approach. It improves performance of some queries by a *lot* (especially the image selection query) and it's pretty elegant and convenient to work with. Only downside is the complexity of compiling, installing and maintaining a vndbid.so library for PostgreSQL.
-rw-r--r--.gitignore3
-rw-r--r--Dockerfile2
-rw-r--r--Makefile1
-rw-r--r--README.md13
-rw-r--r--elm/ImageFlagging.elm2
-rw-r--r--lib/Multi/API.pm6
-rw-r--r--lib/VNDB/DB/Chars.pm7
-rw-r--r--lib/VNDB/DB/Misc.pm2
-rw-r--r--lib/VNDB/DB/VN.pm21
-rw-r--r--lib/VNWeb/Graph.pm1
-rw-r--r--lib/VNWeb/Images/List.pm9
-rw-r--r--lib/VNWeb/Images/Vote.pm19
-rw-r--r--sql/c/Makefile4
-rw-r--r--sql/c/test.sql53
-rw-r--r--sql/c/vndbfuncs.c221
-rw-r--r--sql/func.sql6
-rw-r--r--sql/schema.sql19
-rw-r--r--sql/tableattrs.sql2
-rw-r--r--sql/vndbid.sql85
-rwxr-xr-xutil/dbdump.pl15
-rwxr-xr-xutil/devdump.pl5
-rwxr-xr-xutil/docker-init.sh8
-rwxr-xr-xutil/unusedimages.pl4
-rw-r--r--util/updates/2020-04-05-vndbid-for-images.sql51
-rwxr-xr-xutil/vndb.pl2
25 files changed, 497 insertions, 64 deletions
diff --git a/.gitignore b/.gitignore
index c8f9e661..eda3cee9 100644
--- a/.gitignore
+++ b/.gitignore
@@ -29,3 +29,6 @@
/sql/editfunc.sql
/www/
*.swp
+*.o
+*.so
+*.dll
diff --git a/Dockerfile b/Dockerfile
index 2b13eb13..37ebb653 100644
--- a/Dockerfile
+++ b/Dockerfile
@@ -1,7 +1,7 @@
FROM alpine:3.11
MAINTAINER Yoran Heling <contact@vndb.org>
-ENV VNDB_DOCKER_VERSION=2
+ENV VNDB_DOCKER_VERSION=3
CMD /var/www/util/docker-init.sh
RUN apk add --no-cache \
diff --git a/Makefile b/Makefile
index 972fcc8c..e1033b03 100644
--- a/Makefile
+++ b/Makefile
@@ -50,6 +50,7 @@ clean:
rm -f static/f/icons.png
rm -rf elm/Gen/
rm -rf elm/elm-stuff/build-artifacts
+ $(MAKE) -C sql/c clean
cleaner: clean
rm -rf elm/elm-stuff
diff --git a/README.md b/README.md
index 33be6269..18b1a948 100644
--- a/README.md
+++ b/README.md
@@ -45,7 +45,8 @@ can stop the container and run:
Global requirements:
- Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows.
-- PostgreSQL 10 (older versions may work)
+- A standard C build system (make/gcc/etc)
+- PostgreSQL 10+ (including development files)
- Perl 5.26+
- Elm 0.19.1
@@ -88,11 +89,21 @@ util/multi.pl (application server, optional):
```
- Setup a PostgreSQL server and make sure you can login with some admin user
+- Build the *vndbfuncs* PostgreSQL library:
+
+```
+ make -C sql/c
+```
+
+- Copy `sql/c/vndbfuncs.so` to the appropriate directory (either run
+ `sudo make -C sql/c install` or see `pg_config --pkglibdir` or
+ `SHOW dynamic_library_path`)
- Initialize the VNDB database (assuming 'postgres' is a superuser):
```
# Create the database & roles
psql -U postgres -f sql/superuser_init.sql
+ psql -U postgres vndb -f sql/vndbid.sql
# Set a password for each database role:
echo "ALTER ROLE vndb LOGIN PASSWORD 'pwd1'" | psql -U postgres
diff --git a/elm/ImageFlagging.elm b/elm/ImageFlagging.elm
index 81a48a17..4bc4a91f 100644
--- a/elm/ImageFlagging.elm
+++ b/elm/ImageFlagging.elm
@@ -250,7 +250,7 @@ view model =
else if model.saved then "Saved!" else "" ]
]
, span []
- [ a [ href <| "/img/" ++ String.filter Char.isAlphaNum i.id ] [ text <| String.filter Char.isAlphaNum i.id ]
+ [ a [ href <| "/img/" ++ i.id ] [ text i.id ]
, b [ class "grayedout" ] [ text " / " ]
, a [ href i.url ] [ text <| String.fromInt i.width ++ "x" ++ String.fromInt i.height ]
]
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 1ece822b..22152aa0 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -417,7 +417,7 @@ my %GET_VN = (
},
},
details => {
- 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',
+ select => 'vndbid_num(v.image) 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,7 +490,7 @@ my %GET_VN = (
]],
},
screens => {
- fetch => [[ 'id', 'SELECT vs.id AS vid, (vs.scr).id AS image, vs.rid, vs.nsfw, s.width, s.height
+ fetch => [[ 'id', 'SELECT vs.id AS vid, vndbid_num(vs.scr) 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) {
@@ -817,7 +817,7 @@ my %GET_CHARACTER = (
},
},
details => {
- select => 'c.alias AS aliases, (c.image).id as image, c."desc" AS description',
+ select => 'c.alias AS aliases, vndbid_num(c.image) 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/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 7ee6d86b..6f1463c1 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -72,7 +72,7 @@ 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.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/;
+ 'coalesce(vndbid_num(c.image),0) AS image' if $o{what} =~ /extended/;
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
@@ -95,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, 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/;
+ $select .= ', ch.alias, ch.desc, coalesce(vndbid_num(ch.image), 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
@@ -179,8 +179,7 @@ sub dbCharRevisionInsert {
my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
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};
+ $set{'image = vndbid(\'ch\',?)'} = $o->{image}||undef if exists $o->{image};
$self->dbExec('UPDATE edit_chars !H', \%set) if keys %set;
if($o->{traits}) {
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 4f630cbd..74d2b15e 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -118,7 +118,7 @@ sub dbWikidata {
sub dbImageAdd {
my($s, $type, $width, $height) = @_;
my $seq = {qw/sf screenshots_seq cv covers_seq ch charimg_seq/}->{$type}||die;
- return $s->dbRow(q|INSERT INTO images (id, width, height) VALUES (ROW(?, nextval(?))::image_id, ?, ?) RETURNING (id).id|, $type, $seq, $width, $height)->{id};
+ return $s->dbRow(q|INSERT INTO images (id, width, height) VALUES (vndbid(?, nextval(?)::int), ?, ?) RETURNING vndbid_num(id) as id|, $type, $seq, $width, $height)->{id};
}
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index e8f3666e..64fab011 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.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' ) : (),
+ qw|v.alias v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai v.l_wikidata|, 'coalesce(vndbid_num(v.image),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, 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 .= ', v.alias, coalesce(vndbid_num(v.image), 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,7 +245,7 @@ sub _enrich {
if($what =~ /screenshots/) {
push(@{$r->[$r{ delete $_->{xid} }]{screenshots}}, $_) for (@{$self->dbAll("
- SELECT vs.$colname AS xid, (s.id).id as id, vs.nsfw, vs.rid, s.width, s.height
+ SELECT vs.$colname AS xid, vndbid_num(s.id) as id, vs.nsfw, vs.rid, s.width, s.height
FROM vn_screenshots$hist vs
JOIN images s ON vs.scr = s.id
WHERE vs.$colname IN(!l)
@@ -278,13 +278,12 @@ 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 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};
+ $set{'image = vndbid(\'cv\',?)'} = $o->{image}||undef if exists $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 '(ROW(\'sf\', ?)::image_id, ?, ?)', @{$o->{screenshots}};
+ my $q = join ',', map '(vndbid(\'sf\', ?), ?, ?)', @{$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;
}
@@ -320,7 +319,7 @@ sub dbVNRevisionInsert {
# arrayref of screenshot IDs as argument
sub dbScreenshotGet {
- 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);
+ return shift->dbAll(q|SELECT vndbid_num(id) AS id, width, height FROM images WHERE id IN(SELECT vndbid('sf', n::integer) FROM unnest(ARRAY[!l]) x(n))|, shift);
}
@@ -328,15 +327,15 @@ 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%)
+ # Assumption: vndbid_num(id) for screenshots doesn't have ~too~ many gaps (less than, say, 80%)
return $self->dbAll(q|
- SELECT (s.id).id AS scr, s.width, s.height, v.id AS vid, v.title
+ SELECT vndbid_num(s.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 ROW('sf', floor(random() * (select last_value from screenshots_seq)))::image_id
+ SELECT vndbid('sf', floor(random() * (select last_value from screenshots_seq))::int)
FROM generate_series(1,20)
LIMIT 20
)
@@ -345,7 +344,7 @@ sub dbScreenshotRandom {
# this query is faster than it looks
return $self->dbAll(join(' UNION ALL ', map
- q|SELECT (s.id).id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position
+ q|SELECT vndbid_num(s.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
diff --git a/lib/VNWeb/Graph.pm b/lib/VNWeb/Graph.pm
index f3e26678..c7550fc5 100644
--- a/lib/VNWeb/Graph.pm
+++ b/lib/VNWeb/Graph.pm
@@ -44,7 +44,6 @@ sub dot2svg {
my($dot) = @_;
$dot = encode_utf8 $dot;
- local $SIG{CHLD} = undef; # Fixed in TUWF 4d8a59cc1dfb5f919298ee495b8865f7872f6cbb
my $e = run_cmd([config->{graphviz_path},'-Tsvg'], '<', \$dot, '>', \my $out, '2>', \my $err)->recv;
warn "graphviz STDERR: $err\n" if chomp $err;
$e and die "Failed to run graphviz";
diff --git a/lib/VNWeb/Images/List.pm b/lib/VNWeb/Images/List.pm
index db0bc12b..ccaf86e7 100644
--- a/lib/VNWeb/Images/List.pm
+++ b/lib/VNWeb/Images/List.pm
@@ -15,10 +15,9 @@ sub listing_ {
paginate_ $url, $opt->{p}, $np, 't';
div_ class => 'mainbox imagebrowse', sub {
div_ class => 'imagecard', sub {
- my $id = $_->{id} =~ s/[^a-z0-9]+//gr;
- a_ href => "/img/$id", style => 'background-image: url('.tuwf->imgurl($_->{id}, 1).')', '';
+ a_ href => "/img/$_->{id}", style => 'background-image: url('.tuwf->imgurl($_->{id}, 1).')', '';
div_ sub {
- a_ href => "/img/$id", $id;
+ a_ href => "/img/$_->{id}", $_->{id};
txt_ ' / ';
a_ href => tuwf->imgurl($_->{id}), "$_->{width}x$_->{height}";
br_;
@@ -64,7 +63,7 @@ sub opts_ {
};
form_ sub {
- input_ type => 'hidden', class => 'hidden', name => 'u', value => $opt->{u};
+ input_ type => 'hidden', class => 'hidden', name => 'u', value => $opt->{u} if $opt->{u};
input_ type => 'hidden', class => 'hidden', name => 'u2', value => $opt->{u2} if $opt->{u2} != auth->uid;
p_ class => 'center', sub {
span_ class => 'linkradio', sub {
@@ -122,7 +121,7 @@ TUWF::get qr{/img/list}, sub {
return tuwf->resNotFound if $opt->{u} && !$u->{user_id};
my $where = sql_and
- $opt->{t}->@* ? sql('(i.id).itype IN', $opt->{t}) : (),
+ $opt->{t}->@* ? sql_or(map sql('i.id BETWEEN vndbid(',\"$_",',1) AND vndbid_max(',\"$_",')'), $opt->{t}->@*) : (),
$opt->{m} ? sql('i.c_votecount >', \$opt->{m}) : ();
my($lst, $np) = tuwf->dbPagei({ results => 100, page => $opt->{p} }, '
diff --git a/lib/VNWeb/Images/Vote.pm b/lib/VNWeb/Images/Vote.pm
index fe0c6586..f28a7542 100644
--- a/lib/VNWeb/Images/Vote.pm
+++ b/lib/VNWeb/Images/Vote.pm
@@ -25,9 +25,6 @@ sub validate_token {
sub enrich_image {
my($l) = @_;
- # XXX: Can't use "IN($image_ids)" here because of an odd PostgreSQL
- # limitation regarding input of composite types. "IN('(ch,1)')" throws an
- # error, though IN(..) with multiple values works just fine.
enrich_merge id => sub { sql q{
SELECT i.id, i.width, i.height, i.c_votecount AS votecount
, i.c_sexual_avg AS sexual_avg, i.c_sexual_stddev AS sexual_stddev
@@ -37,18 +34,18 @@ sub enrich_image {
, COALESCE(v.title, c.name, vsv.title) AS entry_title
FROM images i
LEFT JOIN image_votes iv ON iv.id = i.id AND iv.uid =}, \auth->uid, q{
- LEFT JOIN vn v ON (i.id).itype = 'cv' AND v.image = i.id
- LEFT JOIN chars c ON (i.id).itype = 'ch' AND c.image = i.id
- LEFT JOIN vn_screenshots vs ON (i.id).itype = 'sf' AND vs.scr = i.id
- LEFT JOIN vn vsv ON (i.id).itype = 'sf' AND vsv.id = vs.id
- WHERE i.id = ANY(ARRAY}, $_, '::image_id[])'
+ LEFT JOIN vn v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id
+ LEFT JOIN chars c ON i.id BETWEEN 'ch1' AND vndbid_max('ch') AND c.image = i.id
+ LEFT JOIN vn_screenshots vs ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vs.scr = i.id
+ LEFT JOIN vn vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id
+ WHERE i.id IN}, $_
}, $l;
enrich votes => id => id => sub { sql '
SELECT iv.id, iv.sexual, iv.violence, ', sql_user(), '
FROM image_votes iv
LEFT JOIN users u ON u.id = iv.uid
- WHERE iv.id = ANY(ARRAY', $_, '::image_id[])',
+ WHERE iv.id IN', $_,
auth ? ('AND iv.uid <> ', \auth->uid) : (), '
ORDER BY u.username'
}, $l;
@@ -125,7 +122,7 @@ elm_api Images => $SEND, {}, sub {
elm_api ImageVote => undef, {
votes => { sort_keys => 'id', aoh => {
- id => { regex => qr/^\((?:ch|cv|sf),[1-9][0-9]*\)$/ },
+ id => { regex => qr/^(?:ch|cv|sf)[1-9][0-9]*$/ },
token => {},
sexual => { uint => 1, range => [0,2] },
violence => { uint => 1, range => [0,2] },
@@ -160,7 +157,7 @@ TUWF::get qr{/img/vote}, sub {
TUWF::get qr{/img/(ch|cv|sf)([1-9][0-9]*)}, sub {
my($itype, $id) = (tuwf->capture(1), tuwf->capture(2));
- my $l = [{ id => "($itype,$id)" }];
+ my $l = [{ id => "$itype$id" }];
enrich_image $l;
return tuwf->resNotFound if !defined $l->[0]{width};
diff --git a/sql/c/Makefile b/sql/c/Makefile
new file mode 100644
index 00000000..6127292a
--- /dev/null
+++ b/sql/c/Makefile
@@ -0,0 +1,4 @@
+MODULES = vndbfuncs
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
diff --git a/sql/c/test.sql b/sql/c/test.sql
new file mode 100644
index 00000000..dffca0b3
--- /dev/null
+++ b/sql/c/test.sql
@@ -0,0 +1,53 @@
+-- should all fail
+select 's+10'::vndbid;
+select 's 10'::vndbid;
+select ' s10'::vndbid;
+select 's10 '::vndbid;
+select 's01'::vndbid;
+select 'x01'::vndbid;
+select 'x1'::vndbid;
+select 'v0'::vndbid;
+select 'v'::vndbid;
+select ''::vndbid;
+select 'cx1'::vndbid;
+select 'chx1'::vndbid;
+select 'v67108864'::vndbid;
+
+-- Should all return their input
+select 'c123456'::vndbid;
+select 'p789000'::vndbid;
+select 'v67108863'::vndbid;
+select 'r10'::vndbid;
+select 'i10'::vndbid;
+select 'g10'::vndbid;
+select 's10'::vndbid;
+select 'ch10'::vndbid;
+select 'cv10'::vndbid;
+select 'sf10'::vndbid;
+
+select 's11'::vndbid = 's11'::vndbid; -- t
+select 's11'::vndbid = 'v11'::vndbid; -- f
+select 's11'::vndbid <> 's11'::vndbid; -- f
+select 's11'::vndbid <> 'v11'::vndbid; -- t
+select 's11'::vndbid > 's11'::vndbid; -- f
+select 's11'::vndbid > 's10'::vndbid; -- t
+select 's11'::vndbid >= 's11'::vndbid; -- t
+select 's11'::vndbid >= 's10'::vndbid; -- t
+select 's11'::vndbid >= 's12'::vndbid; -- f
+
+select vndbid_type('sf1'); -- 'sf'
+select vndbid_type('v1'); -- 'v'
+
+select vndbid_num('sf1'); -- 1
+select vndbid_num('v5'); -- 5
+select vndbid_num('v67108863'); -- large
+
+select vndbid('s', 1); -- 's1'
+select vndbid('sf', 500); -- 'sf500'
+select vndbid('s', 0); -- fail
+select vndbid('x', 1); -- fail
+select vndbid('s', 67108864); -- fail
+
+-- The functions probably aren't even called, so not sure if this is a good test.
+select vndbid_le(NULL, 'sf1'); -- NULL
+select vndbid(NULL, 1); -- NULL
diff --git a/sql/c/vndbfuncs.c b/sql/c/vndbfuncs.c
new file mode 100644
index 00000000..c2dd2084
--- /dev/null
+++ b/sql/c/vndbfuncs.c
@@ -0,0 +1,221 @@
+/* This file contains C support functions for the 'vndbid' type,
+ * see sql/vndbid.sql for more information.
+ */
+
+#include "postgres.h"
+#include "fmgr.h"
+#include "libpq/pqformat.h"
+#include "utils/sortsupport.h"
+
+PG_MODULE_MAGIC;
+
+
+/* Internal representation of the vndbid is an int32,
+ * 6 most significant bits are used for the type,
+ * 26 least significant bits for the numeric identifier.
+ *
+ * Apart from the different formatting and type system considerations, these
+ * identifiers are treated (compared, sorted, etc) exactly as if they were
+ * regular integers.
+ *
+ * The order of different entry types is, uh, implementation-defined. It
+ * doesn't have to make sense, it just has to have a stable order.
+ */
+
+/* List of recognized types: encoded type_id (must be stable!), string, first character, second character.
+ * ASSUMPTION: 0 <= type_id <= 31, so that (vndbid-vndbid) can't overflow.
+ */
+#define VNDBID_TYPES\
+ X( 1, "c" , 'c', 0)\
+ X( 2, "d", 'd', 0)\
+ X( 3, "g" , 'g', 0)\
+ X( 4, "i" , 'i', 0)\
+ X( 5, "p" , 'p', 0)\
+ X( 6, "r" , 'r', 0)\
+ X( 7, "s" , 's', 0)\
+ X( 8, "v" , 'v', 0)\
+ X( 9, "ch", 'c', 'h')\
+ X(10, "cv", 'c', 'v')\
+ X(11, "sf", 's', 'f')
+
+#define VNDBID_TYPE(_x) ((_x) >> 26)
+#define VNDBID_NUM(_x) ((_x) & 0x03FFFFFF)
+#define VNDBID_MAXID ((1<<26)-1)
+#define VNDBID_CREATE(_x, _y) (((_x) << 26) | (_y))
+
+
+static char *vndbid_type2str(int t) {
+ switch(t) {
+#define X(num, str, _a, _b) case num: return str;
+ VNDBID_TYPES
+#undef X
+ }
+ return "";
+}
+
+
+static int vndbid_str2type(char a, char b) {
+#define CHAR2(_x, _y) (((int)(_x)<<8) | (int)(_y))
+ switch(CHAR2(a, b)) {
+#define X(num, _a, first, second) case CHAR2(first, second): return num;
+ VNDBID_TYPES
+#undef X
+ }
+ return -1;
+#undef CHAR2
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_in);
+
+Datum vndbid_in(PG_FUNCTION_ARGS) {
+ char *ostr = PG_GETARG_CSTRING(0);
+ char *str = ostr, a = 0, b = 0;
+ int type, num = 0;
+ if(*str >= 'a' && *str <= 'z') a = *(str++);
+ if(*str >= 'a' && *str <= 'z') b = *(str++);
+ type = vndbid_str2type(a, b);
+
+ if(type < 0 || *str == 0 || *str == '0')
+ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for type %s: \"%s\"", "vndbid", ostr)));
+
+ /* Custom string-to-int function, we don't allow leading zeros or signs */
+ while(*str >= '0' && *str <= '9' && num <= VNDBID_MAXID)
+ num = num*10 + (*(str++)-'0');
+
+ if(num > VNDBID_MAXID || *str != 0)
+ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for type %s: \"%s\"", "vndbid", ostr)));
+
+ PG_RETURN_INT32(VNDBID_CREATE(type, num));
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_out);
+
+Datum vndbid_out(PG_FUNCTION_ARGS) {
+ int32 arg = PG_GETARG_INT32(0);
+ PG_RETURN_CSTRING(psprintf("%s%d", vndbid_type2str(VNDBID_TYPE(arg)), (int)VNDBID_NUM(arg)));
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_recv);
+
+Datum vndbid_recv(PG_FUNCTION_ARGS) {
+ StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
+ int32 val = pq_getmsgint(buf, sizeof(int32));
+ if(!*vndbid_type2str(VNDBID_TYPE(val)))
+ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid data for type vndbid")));
+ PG_RETURN_INT32(val);
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_send);
+
+Datum vndbid_send(PG_FUNCTION_ARGS) {
+ int32 arg1 = PG_GETARG_INT32(0);
+ StringInfoData buf;
+
+ pq_begintypsend(&buf);
+ pq_sendint32(&buf, arg1);
+ PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_cmp);
+PG_FUNCTION_INFO_V1(vndbid_lt);
+PG_FUNCTION_INFO_V1(vndbid_le);
+PG_FUNCTION_INFO_V1(vndbid_eq);
+PG_FUNCTION_INFO_V1(vndbid_ge);
+PG_FUNCTION_INFO_V1(vndbid_gt);
+PG_FUNCTION_INFO_V1(vndbid_ne);
+Datum vndbid_cmp(PG_FUNCTION_ARGS){ PG_RETURN_INT32(PG_GETARG_INT32(0) - PG_GETARG_INT32(1)); }
+Datum vndbid_lt(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) < PG_GETARG_INT32(1)); }
+Datum vndbid_le(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) <= PG_GETARG_INT32(1)); }
+Datum vndbid_eq(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) == PG_GETARG_INT32(1)); }
+Datum vndbid_ge(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) >= PG_GETARG_INT32(1)); }
+Datum vndbid_gt(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) > PG_GETARG_INT32(1)); }
+Datum vndbid_ne(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(PG_GETARG_INT32(0) != PG_GETARG_INT32(1)); }
+
+
+static int vndbid_fastcmp(Datum x, Datum y, SortSupport ssup) {
+ int32 a = DatumGetInt32(x);
+ int32 b = DatumGetInt32(y);
+ return a-b;
+}
+
+PG_FUNCTION_INFO_V1(vndbid_sortsupport);
+
+Datum vndbid_sortsupport(PG_FUNCTION_ARGS) {
+ SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
+ ssup->comparator = vndbid_fastcmp;
+ PG_RETURN_VOID();
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_hash);
+
+Datum vndbid_hash(PG_FUNCTION_ARGS) {
+ uint32 v = PG_GETARG_INT32(0);
+ /* Found in khashl.h, no clue which hash function this is, but it's short and seems to make a good attempt at mixing bits.
+ * PostgresSQL's internal hash functions are not exported. */
+ v += ~(v << 15);
+ v ^= (v >> 10);
+ v += (v << 3);
+ v ^= (v >> 6);
+ v += ~(v << 11);
+ v ^= (v >> 16);
+ PG_RETURN_INT32(v);
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid);
+
+Datum vndbid(PG_FUNCTION_ARGS) {
+ text *type = PG_GETARG_TEXT_PP(0);
+ int32 v = PG_GETARG_INT32(1);
+
+ int itype =
+ VARSIZE(type) == VARHDRSZ + 1 ? vndbid_str2type(*((char *)VARDATA(type)), 0) :
+ VARSIZE(type) == VARHDRSZ + 2 ? vndbid_str2type(*((char *)VARDATA(type)), ((char *)VARDATA(type))[1]) : -1;
+
+ if(itype < 0 || v <= 0 || v > VNDBID_MAXID)
+ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input for type vndbid")));
+
+ PG_RETURN_INT32(VNDBID_CREATE(itype, v));
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_type);
+
+Datum vndbid_type(PG_FUNCTION_ARGS) {
+ uint32 v = PG_GETARG_INT32(0);
+ char *str = vndbid_type2str(VNDBID_TYPE(v));
+ size_t len = strlen(str);
+ text *ret = (text *) palloc(len + VARHDRSZ);
+ SET_VARSIZE(ret, len + VARHDRSZ);
+ memcpy(VARDATA(ret), str, len);
+ PG_RETURN_TEXT_P(ret);
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_num);
+
+Datum vndbid_num(PG_FUNCTION_ARGS) {
+ PG_RETURN_INT32(VNDBID_NUM(PG_GETARG_INT32(0)));
+}
+
+
+PG_FUNCTION_INFO_V1(vndbid_max);
+
+Datum vndbid_max(PG_FUNCTION_ARGS) {
+ text *type = PG_GETARG_TEXT_PP(0);
+
+ int itype =
+ VARSIZE(type) == VARHDRSZ + 1 ? vndbid_str2type(*((char *)VARDATA(type)), 0) :
+ VARSIZE(type) == VARHDRSZ + 2 ? vndbid_str2type(*((char *)VARDATA(type)), ((char *)VARDATA(type))[1]) : -1;
+
+ if(itype < 0)
+ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input for type vndbid")));
+
+ PG_RETURN_INT32(VNDBID_CREATE(itype, VNDBID_MAXID));
+}
diff --git a/sql/func.sql b/sql/func.sql
index f7eeccc1..7701c52d 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -161,7 +161,7 @@ $$ LANGUAGE SQL;
--
-- Non-'ch' image weights are currently reduced to 20% in order to prioritize
-- character images.
-CREATE OR REPLACE FUNCTION update_images_cache(image_id) RETURNS void AS $$
+CREATE OR REPLACE FUNCTION update_images_cache(vndbid) RETURNS void AS $$
BEGIN
-- Have to dynamically construct the query here, a
-- WHERE ($1 IS NULL OR s.id = $1)
@@ -174,7 +174,7 @@ BEGIN
CASE WHEN x.id IS NULL THEN 0
ELSE greatest(1,
((greatest(0, 10.0 - s.votecount)/10)*100 + coalesce(s.sexual_stddev/1.5, 0)*100 + coalesce(s.violence_stddev/1.5, 0)*100)
- * (CASE WHEN (x.id).itype = 'ch' THEN 1 ELSE 0.2 END)
+ * (CASE WHEN vndbid_type(x.id) = 'ch' THEN 1 ELSE 0.2 END)
)
END AS weight
FROM (
@@ -190,7 +190,7 @@ BEGIN
UNION ALL SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden
UNION ALL SELECT image FROM chars WHERE NOT hidden AND image IS NOT NULL
) x(id) ON s.id = x.id
- $sql$ || (CASE WHEN $1 IS NULL THEN '' ELSE 'WHERE s.id = '||quote_literal($1)||'::image_id' END) || $sql$
+ $sql$ || (CASE WHEN $1 IS NULL THEN '' ELSE 'WHERE s.id = '||quote_literal($1) END) || $sql$
) weights
WHERE weights.id = images.id
$sql$;
diff --git a/sql/schema.sql b/sql/schema.sql
index a34bc998..3a2f0f2e 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -68,9 +68,6 @@ 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;
@@ -112,7 +109,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 image_id CONSTRAINT chars_image_check CHECK((image).itype = 'ch'), -- [pub]
+ image vndbid CONSTRAINT chars_image_check CHECK(vndbid_type(image) = 'ch'), -- [pub]
"desc" text NOT NULL DEFAULT '', -- [pub]
gender gender NOT NULL DEFAULT 'unknown', -- [pub]
s_bust smallint NOT NULL DEFAULT 0, -- [pub]
@@ -135,7 +132,7 @@ CREATE TABLE chars_hist (
name varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
alias varchar(500) NOT NULL DEFAULT '',
- image image_id CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch'),
+ image vndbid CONSTRAINT chars_hist_image_check CHECK(vndbid_type(image) = 'ch'),
"desc" text NOT NULL DEFAULT '',
gender gender NOT NULL DEFAULT 'unknown',
s_bust smallint NOT NULL DEFAULT 0,
@@ -206,7 +203,7 @@ CREATE TABLE docs_hist (
-- images
CREATE TABLE images (
- id image_id NOT NULL PRIMARY KEY, -- [pub]
+ id vndbid NOT NULL PRIMARY KEY CONSTRAINT images_id_check CHECK(vndbid_type(id) IN('ch', 'cv', 'sf')), -- [pub]
width smallint NOT NULL, -- [pub]
height smallint NOT NULL, -- [pub]
c_votecount integer NOT NULL DEFAULT 0, -- [pub] (cached columns are marked [pub] for easy querying...)
@@ -219,7 +216,7 @@ CREATE TABLE images (
-- image_votes
CREATE TABLE image_votes (
- id image_id NOT NULL, -- [pub]
+ id vndbid NOT NULL, -- [pub]
uid integer, -- [pub]
sexual smallint NOT NULL CHECK(sexual >= 0 AND sexual <= 2), -- [pub]
violence smallint NOT NULL CHECK(violence >= 0 AND violence <= 2), -- [pub]
@@ -828,7 +825,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 image_id CONSTRAINT vn_image_check CHECK((image).itype = 'cv'), -- [pub]
+ image vndbid CONSTRAINT vn_image_check CHECK(vndbid_type(image) = '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)
@@ -853,7 +850,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 image_id CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv'),
+ image vndbid CONSTRAINT vn_hist_image_check CHECK(vndbid_type(image) = 'cv'),
"desc" text NOT NULL DEFAULT '',
l_wp varchar(150) NOT NULL DEFAULT '',
l_encubed varchar(100) NOT NULL DEFAULT '',
@@ -896,7 +893,7 @@ CREATE TABLE vn_relations_hist (
-- vn_screenshots
CREATE TABLE vn_screenshots (
id integer NOT NULL, -- [pub]
- scr image_id NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf'), -- [pub] images.id
+ scr vndbid NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK(vndbid_type(scr) = '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)
@@ -905,7 +902,7 @@ CREATE TABLE vn_screenshots (
-- vn_screenshots_hist
CREATE TABLE vn_screenshots_hist (
chid integer NOT NULL,
- scr image_id NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf'),
+ scr vndbid NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK(vndbid_type(scr) = 'sf'),
rid integer,
nsfw boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY(chid, scr)
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 00efbd48..653e0dae 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -16,7 +16,7 @@ ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
-ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id);
+ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id) ON DELETE CASCADE;
ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
diff --git a/sql/vndbid.sql b/sql/vndbid.sql
new file mode 100644
index 00000000..85bbdeeb
--- /dev/null
+++ b/sql/vndbid.sql
@@ -0,0 +1,85 @@
+-- This file defines a custom 'vndbid' base type and a bunch of utility functions.
+-- This file must be loaded into the 'vndb' database as a superuser, e.g.:
+--
+-- psql -U postgres vndb -f sql/vndbid.sql
+--
+-- A 'vndbid' represents an identifier used on the site and is essentially a
+-- (type,number) tuple, e.g. 'v17', 'r102', 'sf500'. It is not strictly limited
+-- to database entries with an edit history, any type-prefixed integer could be
+-- added here. 'u' and 't' types are not yet supported but may be added later.
+--
+-- Main advantage of this type is convenience and domain separation. Comparing
+-- vndbids of different types will always return false, so it's less prone to
+-- errors. Values are interally represented as a 32bit integer, so they're
+-- pretty efficient as well.
+--
+-- (This type is only used for image idenfitiers as of writing, but I expect
+-- I'll be converting DB entries as well at some point)
+--
+-- Constructing an ID:
+--
+-- 'v1'::vndbid
+-- vndbid('v', 1)
+--
+-- Extracting info:
+--
+-- vndbid_type('v1') -- 'v'
+-- vndbid_num('v1') -- 1
+--
+-- Efficient filtering on the type:
+--
+-- id BETWEEN 'v1' AND vndbid_max('v')
+--
+-- Is equivalent to, but faster than:
+--
+-- vndbid_type(id) = 'v'
+--
+CREATE TYPE vndbid;
+
+CREATE FUNCTION vndbid_in(cstring) RETURNS vndbid AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_out(vndbid) RETURNS cstring AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_recv(internal) RETURNS vndbid AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_send(vndbid) RETURNS bytea AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_cmp(vndbid, vndbid) RETURNS int AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_lt(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_le(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_eq(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_ge(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_gt(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_ne(vndbid, vndbid) RETURNS boolean AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_sortsupport(internal) RETURNS void AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_hash(vndbid) RETURNS int AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid(text, int) RETURNS vndbid AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_type(vndbid) RETURNS text AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_num(vndbid) RETURNS int AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+CREATE FUNCTION vndbid_max(text) RETURNS vndbid AS 'vndbfuncs' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE TYPE vndbid (
+ internallength = 4,
+ input = vndbid_in,
+ output = vndbid_out,
+ receive = vndbid_recv,
+ send = vndbid_send,
+ alignment = int4,
+ passedbyvalue
+);
+
+CREATE OPERATOR < (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_lt, commutator = > , negator = >=, restrict = scalarltsel, join = scalarltjoinsel);
+CREATE OPERATOR <= (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_le, commutator = >=, negator = > , restrict = scalarlesel, join = scalarlejoinsel);
+CREATE OPERATOR = (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_eq, commutator = = , negator = <>, restrict = eqsel, join = eqjoinsel);
+CREATE OPERATOR <> (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_ne, commutator = <>, negator = =, restrict = neqsel, join = neqjoinsel);
+CREATE OPERATOR >= (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_ge, commutator = <=, negator = < , restrict = scalargesel, join = scalargejoinsel);
+CREATE OPERATOR > (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_gt, commutator = < , negator = <=, restrict = scalargtsel, join = scalargtjoinsel);
+
+CREATE OPERATOR CLASS vndbid_btree_ops DEFAULT FOR TYPE vndbid USING btree AS
+ OPERATOR 1 <,
+ OPERATOR 2 <=,
+ OPERATOR 3 =,
+ OPERATOR 4 >=,
+ OPERATOR 5 >,
+ FUNCTION 1 vndbid_cmp(vndbid, vndbid),
+ FUNCTION 2 vndbid_sortsupport(internal);
+
+CREATE OPERATOR CLASS vndbid_hash_ops DEFAULT FOR TYPE vndbid USING hash AS
+ OPERATOR 1 =,
+ FUNCTION 1 vndbid_hash(vndbid);
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 9946ad9e..51503b44 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -168,10 +168,15 @@ sub export_import_script {
-- Uncomment to import the schema and data into a separate namespace:
--CREATE SCHEMA vndb;
--SET search_path TO vndb;
+
+ -- 'vndbid' is a custom base type used in the VNDB codebase, but it's safe to treat
+ -- it as just text. If you want to use the proper type, load sql/vndbid.sql from
+ -- the VNDB source code into your database and comment out the following line.
+ -- (or ignore the error message about 'vndbid' already existing)
+ CREATE DOMAIN vndbid AS text;
_
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);
@@ -179,7 +184,7 @@ sub export_import_script {
my $schema = $schema->{$table->{name}};
print $F "\n";
print $F "CREATE TABLE \"$table->{name}\" (\n";
- print $F join ",\n", map " $_->{decl}" =~ s/" serial/" integer/ir, grep $_->{pub}, @{$schema->{cols}};
+ print $F join ",\n", map " $_->{decl}" =~ s/" serial/" integer/ir =~ s/ +(?:check|constraint) +.*//ir, grep $_->{pub}, @{$schema->{cols}};
print $F ",\n PRIMARY KEY(".join(', ', map "\"$_\"", @{$schema->{primary}}).")" if $schema->{primary};
print $F "\n);\n";
}
@@ -250,9 +255,9 @@ sub export_img {
my %scr;
my %dir = (ch => {}, cv => {}, sf => \%scr, st => \%scr);
- $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}");
+ $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(scr) FROM vn_screenshots WHERE $tables{vn_screenshots}{where}");
+ $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM vn WHERE image IS NOT NULL AND $tables{vn}{where}");
+ $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) 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 f1529839..9ee1959f 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -134,7 +134,7 @@ sub copy_entry {
# Image metadata
my $image_ids = join ',', map "'$_'", @$images;
copy images => "SELECT * FROM images WHERE id IN($image_ids)";
- copy image_votes => "SELECT * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
+ copy image_votes => "SELECT DISTINCT ON (id,uid%10) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
# Threads (announcements)
my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") };
@@ -181,6 +181,7 @@ sub copy_entry {
print "SELECT update_stats_cache_full();\n";
print "SELECT update_vnvotestats();\n";
print "SELECT update_users_ulist_stats(NULL);\n";
+ print "SELECT update_images_cache(NULL);\n";
print "UPDATE users u SET c_tags = (SELECT COUNT(*) FROM tags_vn v WHERE v.uid = u.id);\n";
print "UPDATE users u SET c_changes = (SELECT COUNT(*) FROM changes c WHERE c.requester = u.id);\n";
@@ -196,7 +197,7 @@ sub copy_entry {
# Now figure out which images we need, and throw everything in a tarball
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;
+my @imgpaths = sort map { my($t,$id) = /([a-z]+)([0-9]+)/; (img($t, $id), $t eq 'sf' ? img('st', $id) : ()) } @$images;
system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths);
unlink 'dump.sql';
diff --git a/util/docker-init.sh b/util/docker-init.sh
index e1cf1b68..42ff99a9 100755
--- a/util/docker-init.sh
+++ b/util/docker-init.sh
@@ -37,6 +37,12 @@ mkdevuser() {
}
+# Should run as root
+installvndbid() {
+ make -C /var/www/sql/c install || exit
+}
+
+
# Should run as devuser
pg_start() {
if [ ! -d /var/www/data/docker-pg/12 ]; then
@@ -67,6 +73,7 @@ pg_start() {
make sql/editfunc.sql
psql postgres -f sql/superuser_init.sql
+ psql -U devuser vndb -f sql/vndbid.sql
echo "ALTER ROLE vndb LOGIN" | psql postgres
echo "ALTER ROLE vndb_site LOGIN" | psql postgres
echo "ALTER ROLE vndb_multi LOGIN" | psql postgres
@@ -102,6 +109,7 @@ devshell() {
case "$1" in
'')
mkdevuser
+ installvndbid
su devuser -c '/var/www/util/docker-init.sh pg_start'
exec su devuser -c '/var/www/util/docker-init.sh devshell'
;;
diff --git a/util/unusedimages.pl b/util/unusedimages.pl
index f1a23377..3b81c7ae 100755
--- a/util/unusedimages.pl
+++ b/util/unusedimages.pl
@@ -36,7 +36,7 @@ sub cleandb {
) x
)
});
- print "# Deleted unreferenced screenshots: $cnt\n";
+ print "# Deleted unreferenced images: $cnt\n";
}
sub addtxt {
@@ -59,7 +59,7 @@ sub addtxtsql {
}
sub addimagessql {
- my $st = $db->prepare('SELECT (id).itype, (id).id FROM images');
+ my $st = $db->prepare('SELECT vndbid_type(id), vndbid_num(id) FROM images');
$st->execute();
$count = 0;
while((my $num = $st->fetch())) {
diff --git a/util/updates/2020-04-05-vndbid-for-images.sql b/util/updates/2020-04-05-vndbid-for-images.sql
new file mode 100644
index 00000000..df85313b
--- /dev/null
+++ b/util/updates/2020-04-05-vndbid-for-images.sql
@@ -0,0 +1,51 @@
+-- Make sure to import sql/vndbid.sql before running this script.
+
+ALTER TABLE chars DROP CONSTRAINT chars_image_fkey;
+ALTER TABLE chars_hist DROP CONSTRAINT chars_hist_image_fkey;
+ALTER TABLE image_votes DROP CONSTRAINT image_votes_id_fkey;
+ALTER TABLE vn DROP CONSTRAINT vn_image_fkey;
+ALTER TABLE vn_hist DROP CONSTRAINT vn_hist_image_fkey;
+ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_fkey;
+ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_scr_fkey;
+
+ALTER TABLE chars DROP CONSTRAINT chars_image_check;
+ALTER TABLE chars_hist DROP CONSTRAINT chars_hist_image_check;
+ALTER TABLE images DROP CONSTRAINT images_id_check;
+ALTER TABLE vn DROP CONSTRAINT vn_image_check;
+ALTER TABLE vn_hist DROP CONSTRAINT vn_hist_image_check;
+ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_check;
+ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_scr_check;
+
+ALTER TABLE chars ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE chars_hist ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE images ALTER COLUMN id TYPE vndbid USING vndbid((id).itype::text, (id).id);
+ALTER TABLE image_votes ALTER COLUMN id TYPE vndbid USING vndbid((id).itype::text, (id).id);
+ALTER TABLE vn ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE vn_hist ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE vn_screenshots ALTER COLUMN scr TYPE vndbid USING vndbid((scr).itype::text, (scr).id);
+ALTER TABLE vn_screenshots_hist ALTER COLUMN scr TYPE vndbid USING vndbid((scr).itype::text, (scr).id);
+
+ALTER TABLE chars ADD CONSTRAINT chars_image_check CHECK(vndbid_type(image) = 'ch');
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_check CHECK(vndbid_type(image) = 'ch');
+ALTER TABLE images ADD CONSTRAINT images_id_check CHECK(vndbid_type(id) IN('ch', 'cv', 'sf'));
+ALTER TABLE vn ADD CONSTRAINT vn_image_check CHECK(vndbid_type(image) = 'cv');
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_check CHECK(vndbid_type(image) = 'cv');
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_check CHECK(vndbid_type(scr) = 'sf');
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_check CHECK(vndbid_type(scr) = 'sf');
+
+ALTER TABLE chars ADD CONSTRAINT chars_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id) ON DELETE CASCADE;
+ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
+
+DROP FUNCTION update_images_cache(image_id);
+
+\i sql/func.sql
+
+DROP TYPE image_id;
+DROP TYPE image_type;
+
+ANALYZE images, image_votes;
diff --git a/util/vndb.pl b/util/vndb.pl
index b7aedb27..d926a04e 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -39,7 +39,7 @@ tuwf->{elmgen} = $ARGV[0] && $ARGV[0] eq 'elmgen';
sub _path {
- my($t, $id) = $_[1] =~ /\(([a-z]+),([0-9]+)\)/;
+ 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;
}