summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-02-21 08:30:46 +0100
committerYorhel <git@yorhel.nl>2020-03-16 12:45:42 +0100
commitbe6aa6cc7e8034cfc064acb22f44e66aa527e06f (patch)
treef5e27345a3bec3d4d516a7c986d34b23e37c2d28
parent8fe95ae3c2119e5a5219ad072d441bac406ea547 (diff)
imgflag: Initial schema + UI for image flagging
Lots of TODO's left to work on, but you have to start somewhere. I've bumped the Docker image version because this change requires TUWF commit 74aad378d49592df4359ea8a9f6f36d4a0013c04 (Elm decoder for structs with more than 8 fields)
-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/Maintenance.pm3
-rw-r--r--lib/VNWeb/Elm.pm17
-rw-r--r--lib/VNWeb/Misc/ImageFlagging.pm59
-rwxr-xr-xutil/dbdump.pl5
-rwxr-xr-xutil/devdump.pl4
-rw-r--r--util/sql/func.sql67
-rw-r--r--util/sql/perms.sql4
-rw-r--r--util/sql/schema.sql21
-rw-r--r--util/sql/tableattrs.sql6
-rw-r--r--util/sql/triggers.sql14
-rw-r--r--util/updates/2020-03-13-image-flagging.sql30
15 files changed, 431 insertions, 9 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/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/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 f2f5d4ca..11cd2046 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -56,9 +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 => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden'
- .' UNION SELECT image FROM chars WHERE image IS NOT NULL AND NOT hidden'
- .' UNION SELECT image from vn WHERE image IS NOT NULL AND NOT hidden)' },
+ 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' },
diff --git a/util/devdump.pl b/util/devdump.pl
index 5fb4a916..40ef6848 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -132,7 +132,9 @@ sub copy_entry {
copy 'wikidata';
# Image metadata
- copy images => 'SELECT * FROM images WHERE id IN('.join(',',map "'$_'", @$images).')';
+ 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'") };
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 7410a688..b25235f0 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -16,6 +16,7 @@ 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;
@@ -104,7 +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 ON images 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;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 89d8431e..ae10a3d9 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -206,9 +206,24 @@ CREATE TABLE docs_hist (
-- images
CREATE TABLE images (
- id image_id NOT NULL PRIMARY KEY, -- [pub]
- width smallint NOT NULL, -- [pub]
- height smallint NOT NULL -- [pub]
+ 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
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 191c78e6..00efbd48 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -16,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);
@@ -111,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);
@@ -125,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/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);