summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--Dockerfile2
-rw-r--r--data/style.css15
-rw-r--r--elm/ImageFlagging.elm192
-rw-r--r--elm/Lib/Api.elm1
-rw-r--r--lib/Multi/API.pm8
-rw-r--r--lib/Multi/IRC.pm21
-rw-r--r--lib/Multi/Maintenance.pm3
-rw-r--r--lib/VNDB/DB/Chars.pm17
-rw-r--r--lib/VNDB/DB/Misc.pm10
-rw-r--r--lib/VNDB/DB/VN.pm45
-rw-r--r--lib/VNDB/Handler/Chars.pm2
-rw-r--r--lib/VNDB/Handler/VNEdit.pm4
-rw-r--r--lib/VNWeb/Chars/Page.pm4
-rw-r--r--lib/VNWeb/Elm.pm17
-rw-r--r--lib/VNWeb/Misc/ImageFlagging.pm59
-rwxr-xr-xutil/dbdump.pl10
-rwxr-xr-xutil/devdump.pl27
-rw-r--r--util/sql/func.sql67
-rw-r--r--util/sql/perms.sql10
-rw-r--r--util/sql/schema.sql45
-rw-r--r--util/sql/tableattrs.sql14
-rw-r--r--util/sql/triggers.sql14
-rwxr-xr-xutil/unusedimages.pl31
-rw-r--r--util/updates/2020-03-06-images-table.sql58
-rwxr-xr-xutil/updates/2020-03-12-image-sizes.pl26
-rw-r--r--util/updates/2020-03-13-image-flagging.sql30
-rwxr-xr-xutil/vndb.pl15
27 files changed, 630 insertions, 117 deletions
diff --git a/Dockerfile b/Dockerfile
index 855c9411..2b13eb13 100644
--- a/Dockerfile
+++ b/Dockerfile
@@ -1,7 +1,7 @@
FROM alpine:3.11
MAINTAINER Yoran Heling <contact@vndb.org>
-ENV VNDB_DOCKER_VERSION=1
+ENV VNDB_DOCKER_VERSION=2
CMD /var/www/util/docker-init.sh
RUN apk add --no-cache \
diff --git a/data/style.css b/data/style.css
index ec1addd6..5168515c 100644
--- a/data/style.css
+++ b/data/style.css
@@ -1075,6 +1075,21 @@ p.filselect i { font-style: normal }
+/****** Image flagging *******/
+
+.imageflag { width: 810px; margin: auto }
+.imageflag div:nth-child(1) { display: flex; justify-content: space-between; height: 20px }
+.imageflag div:nth-child(1) span { white-space: nowrap; overflow: hidden; text-overflow: ellipsis; padding: 0px 20px }
+.imageflag div:nth-child(2) { border: 1px solid $border$; padding: 5px; display: flex; justify-content: center; align-items: center; background: #000 }
+.imageflag div:nth-child(2) a { border: none; display: inline-block; width: 100%; height: 100%; background-repeat: no-repeat; background-position: center }
+.imageflag div:nth-child(3) { display: flex; justify-content: space-between; }
+.imageflag ul.imgvoteopt { display: flex; align-items: center; list-style-type: none; margin: 5px 0 }
+.imageflag ul.imgvoteopt span { font-weight: bold; padding-right: 5px }
+.imageflag ul.imgvoteopt li:nth-child(4) { flex-grow: 1 }
+.imageflag ul.imgvoteopt li label { display: inline-block; border: 1px solid $secborder$; padding: 4px 5px; min-width: 90px; white-space: nowrap; margin: 2px }
+.imageflag ul.imgvoteopt li.sel label { background-color: $secbg$ }
+
+
/****** Icons *******/
.icons {
diff --git a/elm/ImageFlagging.elm b/elm/ImageFlagging.elm
new file mode 100644
index 00000000..b45cbde9
--- /dev/null
+++ b/elm/ImageFlagging.elm
@@ -0,0 +1,192 @@
+module ImageFlagging exposing (main)
+
+import Html exposing (..)
+import Html.Attributes exposing (..)
+import Html.Events exposing (..)
+import Array
+import Dict
+import Browser
+import Task
+import Process
+import Json.Decode as JD
+import Lib.Html exposing (..)
+import Lib.Util exposing (..)
+import Lib.Api as Api
+import Lib.Ffi as Ffi
+import Gen.Api as GApi
+import Gen.Images as GI
+import Gen.ImageVote as GIV
+
+
+-- TODO: Keyboard shortcuts
+main : Program () Model Msg
+main = Browser.element
+ { init = \e -> (init e, Cmd.none)
+ , view = view
+ , update = update
+ , subscriptions = always Sub.none
+ }
+
+
+type alias Model =
+ { warn : Bool
+ , images : Array.Array GApi.ApiImageResult
+ , index : Int
+ , changes : Dict.Dict String GIV.SendVotes
+ , saved : Bool
+ , saveTimer : Bool
+ , loadState : Api.State
+ , saveState : Api.State
+ }
+
+init : () -> Model
+init _ =
+ { warn = True
+ , images = Array.empty
+ , index = 0
+ , changes = Dict.empty
+ , saved = False
+ , saveTimer = False
+ , saveState = Api.Normal
+ , loadState = Api.Normal
+ }
+
+
+type Msg
+ = SkipWarn
+ | Load GApi.Response
+ | Vote (Maybe Int) (Maybe Int) Bool
+ | Save
+ | Saved GApi.Response
+ | Prev
+ | Next
+
+
+isLast : Model -> Bool
+isLast model = Array.get model.index model.images |> Maybe.map (\i -> i.my_sexual == Nothing || i.my_violence == Nothing) |> Maybe.withDefault True
+
+
+-- TODO: preload next image
+update : Msg -> Model -> (Model, Cmd Msg)
+update msg model =
+ let -- Load more images if we're about to run out
+ load (m,c) =
+ if m.loadState /= Api.Loading && Array.length m.images - m.index <= 3
+ then ({ m | loadState = Api.Loading }, Cmd.batch [ c, GI.send {} Load ])
+ else (m,c)
+ -- Start a timer to save changes
+ save (m,c) =
+ if not m.saveTimer && not (Dict.isEmpty m.changes) && m.saveState /= Api.Loading
+ then ({ m | saveTimer = True }, Cmd.batch [ c, Task.perform (always Save) (Process.sleep 5000) ])
+ else (m,c)
+ in
+ case msg of
+ SkipWarn -> load ({ model | warn = False }, Cmd.none)
+
+ Load (GApi.ImageResult l) ->
+ let nm = { model | loadState = Api.Normal, images = Array.append model.images (Array.fromList l) }
+ nc = if nm.index < 200 then nm
+ else { nm | index = nm.index - 100, images = Array.slice 100 (Array.length nm.images) nm.images }
+ in (nc, Cmd.none)
+ Load e -> ({ model | loadState = Api.Error e }, Cmd.none)
+
+ Vote s v _ ->
+ case Array.get model.index model.images of
+ Nothing -> (model, Cmd.none)
+ Just i ->
+ let m = { model | saved = False, images = Array.set model.index { i | my_sexual = s, my_violence = v } model.images }
+ in case (s,v) of
+ -- Complete vote, mark it as a change and go to next image
+ (Just xs, Just xv) -> save <| load
+ ({ m | index = m.index + (if isLast model then 1 else 0)
+ , changes = Dict.insert i.id { id = i.id, sexual = xs, violence = xv } m.changes
+ }, Cmd.none)
+ -- Otherwise just save it internally
+ _ -> (m, Cmd.none)
+
+ Save -> ({ model | saveTimer = False, saveState = Api.Loading, changes = Dict.empty }, GIV.send { votes = Dict.values model.changes } Saved)
+ Saved r -> save ({ model | saved = True, saveState = if r == GApi.Success then Api.Normal else Api.Error r }, Cmd.none)
+
+ Prev -> ({ model | saved = False, index = model.index - (if model.index == 0 then 0 else 1) }, Cmd.none)
+ Next -> ({ model | saved = False, index = model.index + (if isLast model then 0 else 1) }, Cmd.none)
+
+
+view : Model -> Html Msg
+view model =
+ let
+ -- TODO: Dynamic box size depending on available space?
+ boxwidth = 800
+ boxheight = 600
+ px n = String.fromInt (floor n) ++ "px"
+ stat avg stddev =
+ case (avg, stddev) of
+ (Just a, Just s) -> Ffi.fmtFloat a 2 ++ " σ " ++ Ffi.fmtFloat s 2
+ _ -> "-"
+
+ imgView i =
+ let entry = i.entry_type ++ String.fromInt i.entry_id
+ in
+ [ div []
+ [ a [ href "#", onClickD Prev, classList [("invisible", model.index == 0)] ] [ text "««" ]
+ , span []
+ [ b [ class "grayedout" ] [ text (entry ++ ":") ]
+ , a [ href ("/" ++ entry) ] [ text i.entry_title ]
+ ]
+ , a [ href "#", onClickD Next, classList [("invisible", isLast model)] ] [ text "»»" ]
+ ]
+ , div [ style "width" (px boxwidth), style "height" (px boxheight) ] <|
+ -- Don't use an <img> here, changing the src= causes the old image to be displayed with the wrong dimensions while the new image is being loaded.
+ [ a [ href i.url, style "background-image" ("url("++i.url++")")
+ , style "background-size" (if i.width > boxwidth || i.height > boxheight then "contain" else "auto")
+ ] [ text "" ] ]
+ , div []
+ [ span [] <|
+ case model.saveState of
+ Api.Error e -> [ b [ class "standout" ] [ text <| "Save failed: " ++ Api.showResponse e ] ]
+ _ ->
+ [ span [ class "spinner", classList [("invisible", model.saveState == Api.Normal)] ] []
+ , b [ class "grayedout" ] [ text <|
+ if not (Dict.isEmpty model.changes)
+ then "Unsaved votes: " ++ String.fromInt (Dict.size model.changes)
+ else if model.saved then "Saved!" else "" ]
+ ]
+ , span []
+ [ text <| String.fromInt i.votecount ++ (if i.votecount == 1 then " vote" else " votes")
+ , b [ class "grayedout" ] [ text " / " ]
+ , text <| "sexual: " ++ stat i.sexual_avg i.sexual_stddev
+ , b [ class "grayedout" ] [ text " / " ]
+ , text <| "violence: " ++ stat i.violence_avg i.violence_stddev
+ , b [ class "grayedout" ] [ text " / " ]
+ , a [ href i.url ] [ text <| String.fromInt i.width ++ "x" ++ String.fromInt i.height ]
+ ]
+ ]
+ -- TODO: Mouse-over quick explanations
+ , ul [ class "imgvoteopt" ]
+ [ li [] [ span [] [ text "Sexual" ] ]
+ , li [ classList [("sel", i.my_sexual == Just 0)] ] [ label [] [ inputRadio "sexual" (i.my_sexual == Just 0) (Vote (Just 0) i.my_violence), text " Safe" ] ]
+ , li [ classList [("sel", i.my_sexual == Just 1)] ] [ label [] [ inputRadio "sexual" (i.my_sexual == Just 1) (Vote (Just 1) i.my_violence), text " Suggestive" ] ]
+ , li [ classList [("sel", i.my_sexual == Just 2)] ] [ label [] [ inputRadio "sexual" (i.my_sexual == Just 2) (Vote (Just 2) i.my_violence), text " Explicit" ] ]
+ , li [] [ span [] [ text "Violence" ] ]
+ , li [ classList [("sel", i.my_violence == Just 0)] ] [ label [] [ inputRadio "violence" (i.my_violence == Just 0) (Vote i.my_sexual (Just 0)), text " Tame" ] ]
+ , li [ classList [("sel", i.my_violence == Just 1)] ] [ label [] [ inputRadio "violence" (i.my_violence == Just 1) (Vote i.my_sexual (Just 1)), text " Violent" ] ]
+ , li [ classList [("sel", i.my_violence == Just 2)] ] [ label [] [ inputRadio "violence" (i.my_violence == Just 2) (Vote i.my_sexual (Just 2)), text " Brutal" ] ]
+ ]
+ -- TODO: list of users who voted on this image
+ ]
+
+ in div [ class "mainbox" ]
+ [ h1 [] [ text "Image flagging" ]
+ , div [ class "imageflag" ] <|
+ if model.warn
+ then [ ul []
+ [ li [] [ text "Make sure you are familiar with the ", a [ href "/d19" ] [ text "image flagging guidelines" ], text "." ]
+ , li [] [ b [ class "standout" ] [ text "WARNING: " ], text "Images shown may be highly offensive and/or depictions of explicit sexual acts." ]
+ ]
+ , inputButton "I understand, continue" SkipWarn []
+ ]
+ else case (Array.get model.index model.images, model.loadState) of
+ (Just i, _) -> imgView i
+ (_, Api.Loading) -> [ span [ class "spinner" ] [] ]
+ (_, Api.Error e) -> [ b [ class "standout" ] [ text <| Api.showResponse e ] ]
+ (_, Api.Normal) -> [ text "No more images to vote on!" ]
+ ]
diff --git a/elm/Lib/Api.elm b/elm/Lib/Api.elm
index d8cb1315..ee2df07f 100644
--- a/elm/Lib/Api.elm
+++ b/elm/Lib/Api.elm
@@ -47,6 +47,7 @@ showResponse res =
TagResult _ -> unexp
VNResult _ -> unexp
ProducerResult _ -> unexp
+ ImageResult _ -> unexp
expectResponse : (Response -> msg) -> Http.Expect msg
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/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index abed87a6..e0f9e08d 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -74,6 +74,9 @@ my %dailies = (
# takes about 4 seconds, OK
vnstats => 'SELECT update_vnvotestats()',
+ # takes a few seconds, need more data and measurements. This query /should/ not be necessary.
+ imagecache => 'SELECT update_images_cache(NULL)',
+
# should be pretty fast
cleangraphs => q|
DELETE FROM relgraphs vg
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index a93ad28c..7ee6d86b 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -5,7 +5,7 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbCharFilters dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|;
+our @EXPORT = qw|dbCharFilters dbCharGet dbCharGetRev dbCharRevisionInsert|;
# Character filters shared by dbCharGet and dbVNGet
@@ -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}) {
@@ -191,11 +194,5 @@ sub dbCharRevisionInsert {
}
-# fetches an ID for a new image
-sub dbCharImageId {
- return shift->dbRow("SELECT nextval('charimg_seq') AS ni")->{ni};
-}
-
-
1;
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index cd290d61..73e3e71f 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbStats dbItemEdit dbRevisionGet dbWikidata
+ dbStats dbItemEdit dbRevisionGet dbWikidata dbImageAdd
|;
@@ -115,5 +115,13 @@ sub dbWikidata {
}
+# insert a new image and return its ID
+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};
+}
+
+
1;
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index d099b6ff..e8f3666e 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -8,7 +8,7 @@ use POSIX 'strftime';
use Exporter 'import';
use VNDB::Func 'normalize_query', 'gtintype';
-our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
+our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbScreenshotGet dbScreenshotRandom|;
# Options: id, char, search, gtin, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
@@ -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;
}
@@ -316,22 +318,9 @@ sub dbVNRevisionInsert {
}
-# fetches an ID for a new image
-sub dbVNImageId {
- return shift->dbRow("SELECT nextval('covers_seq') AS ni")->{ni};
-}
-
-
-# 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};
-}
-
-
# 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 +328,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/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index a7a8d801..d1544981 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -426,7 +426,7 @@ sub _uploadimage {
$im->Set(magick => 'JPEG', quality => 90);
# Get ID and save
- my $imgid = $self->dbCharImageId;
+ my $imgid = $self->dbImageAdd(ch => $nw, $nh);
my $fn = imgpath(ch => $imgid);
$im->Write($fn);
chmod 0666, $fn;
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index 932a07f9..49e383a7 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -244,7 +244,7 @@ sub _uploadimage {
$im->Set(quality => 90);
# Get ID and save
- my $imgid = $self->dbVNImageId;
+ my $imgid = $self->dbImageAdd(cv => $nw, $nh);
my $fn = imgpath(cv => $imgid);
$im->Write($fn);
chmod 0666, $fn;
@@ -517,7 +517,7 @@ sub scrxml {
$im->Set(quality => 90);
($ow, $oh) = ($im->Get('width'), $im->Get('height'));
- $id = $self->dbScreenshotAdd($ow, $oh);
+ $id = $self->dbImageAdd(sf => $ow, $oh);
my $fn = imgpath(sf => $id);
$im->Write($fn);
chmod 0666, $fn;
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/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm
index 106f3d8d..04f03643 100644
--- a/lib/VNWeb/Elm.pm
+++ b/lib/VNWeb/Elm.pm
@@ -80,6 +80,22 @@ my %apis = (
name => {},
original => { required => 0, default => '' },
} } ],
+ ImageResult => [ { aoh => { # Response to 'Images'
+ id => { }, # image id...
+ url => { },
+ width => { uint => 1 },
+ height => { uint => 1 },
+ votecount => { uint => 1 },
+ sexual_avg => { num => 1, required => 0 },
+ sexual_stddev => { num => 1, required => 0 },
+ violence_avg => { num => 1, required => 0 },
+ violence_stddev => { num => 1, required => 0 },
+ entry_type => {},
+ entry_id => { id => 1 },
+ entry_title => {},
+ my_sexual => { uint => 1, required => 0 },
+ my_violence => { uint => 1, required => 0 },
+ } } ],
);
@@ -174,6 +190,7 @@ sub write_module {
-- This file is automatically generated from lib/VNWeb/Elm.pm.
-- Do not edit, your changes will be lost.
module Gen.$module exposing (..)
+ import Dict
import Http
import Html
import Html.Attributes as A
diff --git a/lib/VNWeb/Misc/ImageFlagging.pm b/lib/VNWeb/Misc/ImageFlagging.pm
new file mode 100644
index 00000000..0228b2b1
--- /dev/null
+++ b/lib/VNWeb/Misc/ImageFlagging.pm
@@ -0,0 +1,59 @@
+package Misc::ImageFlagging;
+
+use VNWeb::Prelude;
+
+# TODO: /img/<imageid> endpoint to open the imageflagging UI for a particular image.
+
+TUWF::get qr{/img/vote}, sub {
+ return tuwf->resDenied if !auth->permEdit; # TODO: permImg?
+ framework_ title => 'Image flagging', sub {
+ # TODO: Include recent votes
+ elm_ 'ImageFlagging';
+ };
+};
+
+
+# Fetch a list of images for the user to vote on.
+elm_api Images => undef, {}, sub {
+ return elm_Unauth if !auth->permEdit; # XXX
+
+ # TODO: Return nothing when the user has voted on >90% of the images?
+
+ # This query is kind of slow, but there's a few ways to improve:
+ # - create index .. on images (id) include (c_weight) where c_weight > 0;
+ # - Add a 'images.c_uids integer[]' cache to filter out rows faster.
+ # - Distribute images in a fixed number of buckets and choose a random bucket up front.
+ my $l = tuwf->dbAlli('
+ SELECT id, width, height, c_votecount AS votecount, c_sexual_avg AS sexual_avg, c_sexual_stddev AS sexual_stddev, c_violence_avg AS violence_avg, c_violence_stddev AS violence_stddev
+ FROM images i
+ WHERE c_weight > 0
+ AND NOT EXISTS(SELECT 1 FROM image_votes iv WHERE iv.id = i.id AND iv.uid =', \auth->uid, ')
+ ORDER BY random() ^ (1.0/c_weight) DESC
+ LIMIT 100'
+ );
+ enrich_merge id => q{SELECT image AS id, 'v' AS entry_type, id AS entry_id, title AS entry_title FROM vn WHERE image IN}, grep $_->{id} =~ /cv/, @$l;
+ enrich_merge id => q{SELECT vs.scr AS id, 'v' AS entry_type, v.id AS entry_id, v.title AS entry_title FROM vn_screenshots vs JOIN vn v ON v.id = vs.id AND vs.scr IN}, grep $_->{id} =~ /sf/, @$l;
+ enrich_merge id => q{SELECT image AS id, 'c' AS entry_type, id AS entry_id, name AS entry_title FROM chars WHERE image IN}, grep $_->{id} =~ /ch/, @$l;
+ $_->{url} = tuwf->imgurl($_->{id}) for @$l;
+ $_->{my_sexual} = $_->{my_violence} = undef for @$l;
+ elm_ImageResult $l;
+};
+
+
+# TODO: This permits anyone to vote on any image; Might want to restrict that
+# to images that have been randomly selected for the user to avoid abuse.
+elm_api ImageVote => undef, {
+ votes => { sort_keys => 'id', aoh => {
+ id => { regex => qr/^\((ch|cv|sf),[1-9][0-9]*\)$/ },
+ sexual => { uint => 1, range => [0,2] },
+ violence => { uint => 1, range => [0,2] },
+ } },
+}, sub {
+ my($data) = @_;
+ return elm_Unauth if !auth->permEdit; # XXX
+ $_->{uid} = auth->uid for $data->{votes}->@*;
+ tuwf->dbExeci('INSERT INTO image_votes', $_, 'ON CONFLICT (id, uid) DO UPDATE SET', $_, ', date = now()') for $data->{votes}->@*;
+ elm_Success
+};
+
+1;
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 8f0cc5f8..11cd2046 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -56,6 +56,8 @@ 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 => "c_weight > 0" }, # Only images with a positive weight are referenced.
+ image_votes => { where => "id IN(SELECT id FROM images WHERE c_weight > 0)", order => 'uid, id' },
producers => { where => 'NOT hidden' },
producers_relations => { where => 'id IN(SELECT id FROM producers WHERE NOT hidden)' },
releases => { where => 'NOT hidden' },
@@ -70,7 +72,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 +170,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 +249,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..40ef6848 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,11 @@ sub copy_entry {
# Wikidata (TODO: This could be a lot more selective)
copy 'wikidata';
+ # 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' };
+
# 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 +156,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 +195,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/func.sql b/util/sql/func.sql
index af510e51..77d4451e 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -136,6 +136,62 @@ $$ LANGUAGE SQL;
+-- c_weight = if not_referenced then 0 else lower(c_votecount) -> higher(c_weight) && higher(*_stddev) -> higher(c_weight)
+--
+-- One solution:
+--
+-- 1 # Minimum weight for referenced images
+-- * max(1, 10 - c_votecount) # 0 votes -> 10x more likely to be selected, 9+ votes -> 1x. Something non-linear may be nicer...
+-- * (1+c_sexual_stddev*6) # stddev is 0..1.5, upscaled to 1x..9x more likely to be selected. Any stddev>0.8 (=4.8x more likely) is probably worth looking at
+-- * (1+c_violence_stddev*6)
+--
+-- Extremes: 1 .. 810
+--
+-- Alternative solution (currently implemented):
+--
+-- votes_weight = max(0, 10 - c_votecount)/10 -> linear weight between 0..1, 0 being OK and 1 being BAD
+-- *_stddev_weight = *_stddev/1.5 -> ^
+-- weight = 1 + votes_weight*100 + sexual_stddev_weight*100 + violence_stddev_weight*100
+--
+-- Extremes: 1 .. 301, easier to tune and reason about, but still linear
+--
+-- Neither of those solutions are grounded in theory, I've no clue how
+-- statistics work. I suspect confidence intervals/levels are more appropriate
+-- for this use case.
+CREATE OR REPLACE FUNCTION update_images_cache(image_id) RETURNS void AS $$
+BEGIN
+ -- Have to dynamically construct the query here, a
+ -- WHERE ($1 IS NULL OR s.id = $1)
+ -- causes the planner to miss a bunch of optimizations.
+ EXECUTE $sql$UPDATE images
+ SET c_votecount = votecount, c_sexual_avg = sexual_avg, c_sexual_stddev = sexual_stddev
+ , c_violence_avg = violence_avg, c_violence_stddev = violence_stddev, c_weight = weight
+ FROM (
+ SELECT s.*,
+ CASE WHEN x.id IS NULL THEN 0
+ ELSE 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
+ END AS weight
+ FROM (
+ SELECT i.id, count(iv.id) AS votecount
+ , avg(sexual) AS sexual_avg, stddev_pop(sexual) AS sexual_stddev
+ , avg(violence) AS violence_avg, stddev_pop(violence) AS violence_stddev
+ FROM images i
+ LEFT JOIN image_votes iv ON iv.id = i.id
+ GROUP BY i.id
+ ) s
+ LEFT JOIN (
+ SELECT image FROM vn WHERE NOT hidden AND image IS NOT NULL
+ 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$
+ ) weights
+ WHERE weights.id = images.id
+ $sql$;
+END; $$ LANGUAGE plpgsql;
+
+
+
-- Update users.c_vns, c_votes and c_wish for one user (when given an id) or all users (when given NULL)
CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) RETURNS void AS $$
BEGIN
@@ -468,6 +524,17 @@ BEGIN
SELECT rl.uid, rv.vid FROM rlists rl JOIN releases_vn rv ON rv.id = rl.rid WHERE rl.rid = xedit.itemid
ON CONFLICT (uid, vid) DO NOTHING;
END IF;
+
+ -- Call update_images_cache() where appropriate
+ IF xtype = 'c'
+ THEN
+ PERFORM update_images_cache(image) FROM chars_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
+ END IF;
+ IF xtype = 'v'
+ THEN
+ PERFORM update_images_cache(image) FROM vn_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
+ PERFORM update_images_cache(scr) FROM vn_screenshots_hist WHERE chid IN(xoldchid,xedit.chid);
+ END IF;
END;
$$ LANGUAGE plpgsql;
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index b4833a60..b25235f0 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,8 @@ 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 image_votes 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 +38,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 +95,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 +105,8 @@ 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, UPDATE ON images TO vndb_multi;
+GRANT SELECT ON image_votes 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 +122,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..ae10a3d9 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,28 @@ CREATE TABLE docs_hist (
html text -- cache
);
+-- images
+CREATE TABLE images (
+ id image_id NOT NULL PRIMARY KEY, -- [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...)
+ c_sexual_avg float, -- [pub]
+ c_sexual_stddev float, -- [pub]
+ c_violence_avg float, -- [pub]
+ c_violence_stddev float, -- [pub]
+ c_weight float NOT NULL DEFAULT 0 -- [pub]
+);
+
+-- image_votes
+CREATE TABLE image_votes (
+ id image_id 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]
+ date timestamptz NOT NULL DEFAULT NOW() -- [pub]
+);
+
-- login_throttle
CREATE TABLE login_throttle (
ip inet NOT NULL PRIMARY KEY,
@@ -449,13 +475,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 +819,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 +844,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 +887,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 +896,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..00efbd48 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;
@@ -14,6 +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 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);
@@ -75,8 +78,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 +92,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;
@@ -107,6 +112,9 @@ ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey
CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+
CREATE INDEX chars_vns_vid ON chars_vns (vid);
+CREATE INDEX chars_image ON chars (image);
+CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
+CREATE INDEX image_votes_id ON image_votes (id);
CREATE INDEX notifications_uid ON notifications (uid);
CREATE INDEX releases_producers_pid ON releases_producers (pid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
@@ -121,6 +129,8 @@ CREATE INDEX threads_posts_date ON threads_posts (date);
CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
CREATE INDEX traits_chars_tid ON traits_chars (tid);
+CREATE INDEX vn_image ON vn (image);
+CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
CREATE INDEX vn_staff_aid ON vn_staff (aid);
diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql
index a8ef3bbc..aa9419c2 100644
--- a/util/sql/triggers.sql
+++ b/util/sql/triggers.sql
@@ -329,3 +329,17 @@ END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+
+
+
+
+-- Call update_images_cache() for every change on image_votes
+
+CREATE OR REPLACE FUNCTION update_images_cache() RETURNS trigger AS $$
+BEGIN
+ PERFORM update_images_cache(id) FROM (SELECT OLD.id UNION SELECT NEW.id) AS x(id) WHERE id IS NOT NULL;
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER image_votes_cache AFTER INSERT OR UPDATE OR DELETE ON image_votes FOR EACH ROW EXECUTE PROCEDURE update_images_cache();
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/updates/2020-03-12-image-sizes.pl b/util/updates/2020-03-12-image-sizes.pl
new file mode 100755
index 00000000..2855c581
--- /dev/null
+++ b/util/updates/2020-03-12-image-sizes.pl
@@ -0,0 +1,26 @@
+#!/usr/bin/perl
+
+use v5.26;
+use warnings;
+use DBI;
+use Image::Magick;
+
+my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1, AutoCommit => 0 });
+
+my $upd = $db->prepare('UPDATE images SET width = ?, height = ? WHERE id = ?::image_id');
+
+for my $id ($db->selectcol_arrayref('SELECT id FROM images WHERE width IS NULL')->@*) {
+ my($t,$n) = $id =~ /\(([a-z]+),([0-9]+)\)/;
+ my $f = sprintf 'static/%s/%02d/%d.jpg', $t, $n%100, $n;
+ my $im = Image::Magick->new;
+ my $e = $im->Read($f);
+ warn "$f: $e\n" if $e;
+ $upd->execute($im->Get('width'), $im->Get('height'), $id) if !$e;
+}
+
+# A few images have been permanently deleted, that's alright, not being used anyway.
+$db->do('UPDATE images SET width = 0, height = 0 WHERE width IS NULL');
+
+$db->do('ALTER TABLE images ALTER COLUMN width SET NOT NULL');
+$db->do('ALTER TABLE images ALTER COLUMN height SET NOT NULL');
+$db->commit;
diff --git a/util/updates/2020-03-13-image-flagging.sql b/util/updates/2020-03-13-image-flagging.sql
new file mode 100644
index 00000000..d106af1c
--- /dev/null
+++ b/util/updates/2020-03-13-image-flagging.sql
@@ -0,0 +1,30 @@
+ALTER TABLE images ADD COLUMN c_votecount integer NOT NULL DEFAULT 0;
+ALTER TABLE images ADD COLUMN c_sexual_avg float;
+ALTER TABLE images ADD COLUMN c_sexual_stddev float;
+ALTER TABLE images ADD COLUMN c_violence_avg float;
+ALTER TABLE images ADD COLUMN c_violence_stddev float;
+ALTER TABLE images ADD COLUMN c_weight float NOT NULL DEFAULT 0;
+
+CREATE TABLE image_votes (
+ id image_id NOT NULL,
+ uid integer,
+ sexual smallint NOT NULL CHECK(sexual >= 0 AND sexual <= 2),
+ violence smallint NOT NULL CHECK(violence >= 0 AND violence <= 2),
+ date timestamptz NOT NULL DEFAULT NOW()
+);
+
+CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
+CREATE INDEX image_votes_id ON image_votes (id);
+ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id);
+
+-- These significantly speed up the update_image_cache() and reverse image search on the flagging UI
+CREATE INDEX vn_image ON vn (image);
+CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
+CREATE INDEX chars_image ON chars (image);
+
+\i util/sql/func.sql
+\i util/sql/triggers.sql
+\i util/sql/perms.sql
+
+\timing
+select update_images_cache(NULL);
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 {