Add early test implementation of VN length voting

There's no way to list or aggregate the votes yet, and I'm not fully
sure the data model is what it should be. Hence testing.

18 files changed, 215 insertions, 26 deletions

diff --git a/elm/CharEdit.elm b/elm/CharEdit.elm
index 031c2488..54160aad 100644
--- a/elm/CharEdit.elm
+++ b/elm/CharEdit.elm
@@ -432,7 +432,6 @@ view model =
           case lst of
             (x::xs) -> if Set.member x set then uniq xs set else x :: uniq xs (Set.insert x set)
             [] -> []
-        showrel r = "[" ++ (RDate.format (RDate.expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
         vn vid lst rels =
           let title = Maybe.withDefault "<unknown>" <| Maybe.map (\(_,v) -> v.title) <| List.head lst
           in
@@ -447,7 +446,7 @@ view model =
             , tr []
               [ td [] [ inputSelect "" item.rid (VnRel idx) [ style "width" "400px", style "margin" "0 15px" ] <|
                   (Nothing, if List.length lst == 1 then "All (full) releases" else "Other releases")
-                  :: List.map (\r -> (Just r.id, showrel r)) rels
+                  :: List.map (\r -> (Just r.id, RDate.showrel r)) rels
                   ++ if isJust item.rid && List.isEmpty (List.filter (\r -> Just r.id == item.rid) rels)
                      then [(item.rid, "Deleted release: " ++ Maybe.withDefault "" item.rid)] else []
                 ]
diff --git a/elm/Lib/Html.elm b/elm/Lib/Html.elm
index fdd2e627..3f13dbee 100644
--- a/elm/Lib/Html.elm
+++ b/elm/Lib/Html.elm
@@ -49,7 +49,7 @@ inputButton val onch attrs =
 
 -- Submit button with loading indicator and error message display
 submitButton : String -> Api.State -> Bool -> Html m
-submitButton val state valid = div []
+submitButton val state valid = span []
    [ input [ type_ "submit", class "submit", tabindex 10, value val, disabled (state == Api.Loading || not valid) ] []
    , case state of
        Api.Error r -> p [] [ b [class "standout" ] [ text <| Api.showResponse r ] ]
diff --git a/elm/Lib/RDate.elm b/elm/Lib/RDate.elm
index b0ff4604..3eca4cfa 100644
--- a/elm/Lib/RDate.elm
+++ b/elm/Lib/RDate.elm
@@ -15,6 +15,7 @@ import Html.Events exposing (..)
 import Date
 import Lib.Html exposing (..)
 import Gen.Types as GT
+import Gen.Api as GApi
 
 
 type alias RDate = Int
@@ -111,3 +112,10 @@ view ro permitUnknown permitToday msg =
     , if r.y == 0 || r.y == 9999 then text "" else inputSelect "" ro msg [ style "width" "90px" ] ml
     , if r.m == 0 || r.m ==   99 then text "" else inputSelect "" ro msg [ style "width" "90px" ] dl
     ]
+
+
+-- Handy function for formatting release info as a string
+-- (Typically used in selection boxes)
+-- (Why is that in this module, you ask? Well, where else do I put it?)
+showrel : GApi.ApiReleases -> String
+showrel r = "[" ++ (format (expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
diff --git a/elm/Lib/Util.elm b/elm/Lib/Util.elm
index 86d82bf5..85cc5404 100644
--- a/elm/Lib/Util.elm
+++ b/elm/Lib/Util.elm
@@ -4,6 +4,7 @@ import Dict
 import Task
 import Regex
 import Lib.Ffi as Ffi
+import Gen.Api as GApi
 
 -- Delete an element from a List
 delidx : Int -> List a -> List a
diff --git a/elm/Reviews/Edit.elm b/elm/Reviews/Edit.elm
index 35e8b1d7..5a917c3b 100644
--- a/elm/Reviews/Edit.elm
+++ b/elm/Reviews/Edit.elm
@@ -107,8 +107,6 @@ update msg model =
     Deleted r -> ({ model | delState = Api.Error r }, Cmd.none)
 
 
-showrel r = "[" ++ (RDate.format (RDate.expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
-
 view : Model -> Html Msg
 view model =
   let minChars = if model.isfull then   1000 else 200
@@ -134,7 +132,7 @@ view model =
       , formField ""
         [ inputSelect "" model.rid Release [style "width" "500px" ] <|
           (Nothing, "No release selected")
-          :: List.map (\r -> (Just r.id, showrel r)) model.releases
+          :: List.map (\r -> (Just r.id, RDate.showrel r)) model.releases
           ++ if model.rid == Nothing || List.any (\r -> Just r.id == model.rid) model.releases then [] else [(model.rid, "Deleted or moved release: r"++Maybe.withDefault "" model.rid)]
         , br [] []
         , text "You do not have to select a release, but indicating which release your review is based on gives more context."
diff --git a/elm/UList/Opt.elm b/elm/UList/Opt.elm
index 9812a725..c1ac70ab 100644
--- a/elm/UList/Opt.elm
+++ b/elm/UList/Opt.elm
@@ -75,10 +75,6 @@ type Msg
   | RelAdd String
 
 
-showrel : GApi.ApiReleases -> String
-showrel r = "[" ++ (RDate.format (RDate.expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
-
-
 update : Msg -> Model -> (Model, Cmd Msg)
 update msg model =
   case msg of
@@ -128,7 +124,7 @@ update msg model =
       ( { model
         | relState = Api.Normal
         , relNfo = Dict.union (Dict.fromList <| List.map (\r -> (r.id, r)) rels) model.relNfo
-        , relOptions = Just <| List.map (\r -> (r.id, showrel r)) rels
+        , relOptions = Just <| List.map (\r -> (r.id, RDate.showrel r)) rels
         }, Cmd.none)
     RelLoaded e -> ({ model | relState = Api.Error e }, Cmd.none)
     RelAdd rid ->
diff --git a/elm/UList/Widget.elm b/elm/UList/Widget.elm
index 105653b9..ec4375a5 100644
--- a/elm/UList/Widget.elm
+++ b/elm/UList/Widget.elm
@@ -102,7 +102,7 @@ init f =
   , finished   = let m = DE.init { uid = f.uid, vid = f.vid, date = Maybe.map (\full -> full.finished) f.full |> Maybe.withDefault "", start = False } in { m | visible = True }
   , rels       = List.map (\st -> RE.init ("widget-" ++ f.vid) { uid = f.uid, rid = st.id, status = Just st.status, empty = "" }) <| Maybe.withDefault [] <| Maybe.map (\full -> full.rlist) f.full
   , relNfo     = Dict.fromList <| List.map (\r -> (r.id, r)) <| Maybe.withDefault [] <| Maybe.map (\full -> full.releases) f.full
-  , relOptions = Maybe.withDefault [] <| Maybe.map (\full -> List.map (\r -> (r.id, showrel r)) full.releases) f.full
+  , relOptions = Maybe.withDefault [] <| Maybe.map (\full -> List.map (\r -> (r.id, RDate.showrel r)) full.releases) f.full
   }
 
 reset : Model -> Model
@@ -164,10 +164,6 @@ isPublic model =
   || (isJust model.vote.vote && List.any (\l -> l.id == 7 && not l.private) model.labels.labels)
 
 
-showrel : GApi.ApiReleases -> String
-showrel r = "[" ++ (RDate.format (RDate.expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
-
-
 update : Msg -> Model -> (Model, Cmd Msg)
 update msg model =
   case msg of
diff --git a/elm/User/Edit.elm b/elm/User/Edit.elm
index 74e946ba..78598aee 100644
--- a/elm/User/Edit.elm
+++ b/elm/User/Edit.elm
@@ -65,6 +65,7 @@ type AdminMsg
   | PermBoardmod Bool
   | PermEdit Bool
   | PermImgvote Bool
+  | PermLengthvote Bool
   | PermTag Bool
   | PermDbmod Bool
   | PermTagmod Bool
@@ -114,6 +115,7 @@ updateAdmin msg model =
     PermBoardmod b -> { model | perm_boardmod = b }
     PermEdit b     -> { model | perm_edit     = b }
     PermImgvote b  -> { model | perm_imgvote  = b }
+    PermLengthvote b->{ model | perm_lengthvote=b }
     PermTag b      -> { model | perm_tag      = b }
     PermDbmod b    -> { model | perm_dbmod    = b }
     PermTagmod b   -> { model | perm_tagmod   = b }
@@ -125,6 +127,7 @@ updateAdmin msg model =
       , perm_boardmod = False
       , perm_edit     = False
       , perm_imgvote  = False
+      , perm_lengthvote=False
       , perm_tag      = False
       , perm_dbmod    = False
       , perm_tagmod   = False
@@ -137,6 +140,7 @@ updateAdmin msg model =
       , perm_boardmod = False
       , perm_edit     = True
       , perm_imgvote  = True
+      , perm_lengthvote=True
       , perm_tag      = True
       , perm_dbmod    = False
       , perm_tagmod   = False
@@ -220,6 +224,7 @@ view model =
         , perm False              <| label [] [ inputCheck "" m.perm_boardmod (Admin << PermBoardmod), text " boardmod", br_ 1 ]
         , perm opts.perm_dbmod    <| label [] [ inputCheck "" m.perm_edit     (Admin << PermEdit),     text " edit*", br_ 1 ]
         , perm opts.perm_dbmod    <| label [] [ inputCheck "" m.perm_imgvote  (Admin << PermImgvote),  text " imgvote* (existing votes will stop counting when unset)", br_ 1 ]
+        , perm opts.perm_dbmod    <| label [] [ inputCheck "" m.perm_lengthvote(Admin<< PermLengthvote),text " lengthvote* (existing votes will stop counting when unset)", br_ 1 ]
         , perm opts.perm_tagmod   <| label [] [ inputCheck "" m.perm_tag      (Admin << PermTag),      text " tag* (existing tag votes will stop counting when unset)", br_ 1 ]
         , perm False              <| label [] [ inputCheck "" m.perm_dbmod    (Admin << PermDbmod),    text " dbmod", br_ 1 ]
         , perm False              <| label [] [ inputCheck "" m.perm_tagmod   (Admin << PermTagmod),   text " tagmod", br_ 1 ]
diff --git a/elm/VNEdit.elm b/elm/VNEdit.elm
index 68aa4c79..7f1fed98 100644
--- a/elm/VNEdit.elm
+++ b/elm/VNEdit.elm
@@ -514,8 +514,7 @@ view model =
 
     screenshots =
       let
-        showrel r = "[" ++ (RDate.format (RDate.expand r.released)) ++ " " ++ (String.join "," r.lang) ++ "] " ++ r.title ++ " (" ++ r.id ++ ")"
-        rellist = List.map (\r -> (Just r.id, showrel r)) model.releases
+        rellist = List.map (\r -> (Just r.id, RDate.showrel r)) model.releases
         scr n (id, i, rel) = (String.fromInt id, tr [] <|
           let getdim img = Maybe.map (\nfo -> (nfo.width, nfo.height)) img |> Maybe.withDefault (0,0)
               imgdim = getdim i.img
diff --git a/elm/VNLengthVote.elm b/elm/VNLengthVote.elm
new file mode 100644
index 00000000..0c7bc0d7
--- /dev/null
+++ b/elm/VNLengthVote.elm
@@ -0,0 +1,120 @@
+module VNLengthVote exposing (main)
+
+import Html exposing (..)
+import Html.Attributes exposing (..)
+import Html.Events exposing (..)
+import Browser
+import Lib.Html exposing (..)
+import Lib.Api as Api
+import Lib.RDate as RDate
+import Gen.Api as GApi
+import Gen.VNLengthVote as GV
+import Gen.Release as GR
+
+
+main : Program GV.Send Model Msg
+main = Browser.element
+  { init   = \e -> (init e, Cmd.none)
+  , view   = view
+  , update = update
+  , subscriptions = always Sub.none
+  }
+
+type alias Model =
+  { state   : Api.State
+  , open    : Bool
+  , uid     : String
+  , vid     : String
+  , rid     : String
+  , defrid  : String
+  , length  : Int
+  , slength : Int
+  , notes   : String
+  , rels    : Maybe (List (String, String))
+  }
+
+init : GV.Send -> Model
+init f =
+  { state   = Api.Normal
+  , open    = False
+  , uid     = f.uid
+  , vid     = f.vid
+  , rid     = Maybe.map (\v -> v.rid)    f.vote |> Maybe.withDefault ""
+  , defrid  = ""
+  , length  = Maybe.map (\v -> v.length) f.vote |> Maybe.withDefault 0
+  , slength = Maybe.map (\v -> v.length) f.vote |> Maybe.withDefault 0
+  , notes   = Maybe.map (\v -> v.notes)  f.vote |> Maybe.withDefault ""
+  , rels    = Nothing
+  }
+
+encode : Model -> GV.Send
+encode m =
+  { uid = m.uid
+  , vid = m.vid
+  , vote = if m.length == 0 then Nothing else Just { rid = m.rid, length = m.length, notes = m.notes }
+  }
+
+type Msg
+  = Open Bool
+  | Length (Maybe Int)
+  | Release String
+  | Notes String
+  | RelLoaded GApi.Response
+  | Delete
+  | Submit
+  | Submitted GApi.Response
+
+
+update : Msg -> Model -> (Model, Cmd Msg)
+update msg model =
+  case msg of
+    Open b ->
+      if b && model.rels == Nothing
+      then ({ model | open = b, state = Api.Loading }, GR.send { vid = model.vid } RelLoaded)
+      else ({ model | open = b }, Cmd.none)
+    Length n  -> ({ model | length = 60 * Maybe.withDefault 0 n }, Cmd.none)
+    Release s -> ({ model | rid = s }, Cmd.none)
+    Notes s   -> ({ model | notes  = s }, Cmd.none)
+    RelLoaded (GApi.Releases rels) ->
+      let def = case rels of
+                  [r] -> r.id
+                  _ -> ""
+      in ({ model | state = Api.Normal
+          , rels = Just <| List.map (\r -> (r.id, RDate.showrel r)) rels
+          , rid = if model.rid == "" then def else model.rid
+         }, Cmd.none)
+    RelLoaded e -> ({ model | state = Api.Error e }, Cmd.none)
+    Delete      -> let m = { model | length = 0, rid = model.defrid, notes = "", state = Api.Loading } in (m, GV.send (encode m) Submitted)
+    Submit      -> ({ model | state = Api.Loading }, GV.send (encode model) Submitted)
+    Submitted (GApi.Success) -> ({ model | open = False, state = Api.Normal, slength = model.length }, Cmd.none)
+    Submitted r -> ({ model | state = Api.Error r }, Cmd.none)
+
+
+view : Model -> Html Msg
+view model = span [] <|
+  let
+    frm = [ form_ "" (if model.rid == "" then Open True else Submit) False
+      [ text "My play time: "
+      , inputNumber "" (if model.length == 0 then Nothing else Just (model.length//60)) Length <|
+        [ Html.Attributes.min "1", Html.Attributes.max "500", required True ]
+      , text " hours"
+      , br [] []
+      , if model.defrid /= "" then text "" else
+        inputSelect "" model.rid Release [style "width" "100%"] <|
+          ("", "-- select release --") :: (Maybe.withDefault [] model.rels)
+      , inputTextArea "" model.notes Notes
+        [rows 2, cols 30, style "width" "100%", placeholder "(Optional) comments that may be helpful. For example, did you complete all routes, did you use auto mode? etc." ]
+      , if model.slength == 0 then text "" else inputButton "Delete my vote" Delete [style "float" "right"]
+      , if model.length == 0 || model.rid == "" then text "" else submitButton "Save" model.state True
+      , inputButton "Cancel" (Open False) []
+      ] ]
+  in
+    [ text " "
+    , a [ onClickD (Open (not model.open)), href "#" ]
+      [ text <| if model.slength == 0 then "Vote »"
+        else "My vote: " ++ String.fromInt (model.slength // 60) ++ "h" ] -- TODO minute
+    ] ++ case (model.open, model.state) of
+          (False, _) -> []
+          (_, Api.Normal) -> frm
+          (_, Api.Error e) -> [ br [] [], b [ class "standout" ] [ text ("Error: " ++ Api.showResponse e) ] ]
+          (_, Api.Loading) -> [ span [ class "spinner" ] [] ]
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 3408db77..06ca88ec 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -67,7 +67,7 @@ sub isMod { auth->permUsermod || auth->permDbmod || auth->permBoardmod || auth->
 
 
 
-my @perms = qw/board boardmod edit imgvote tag dbmod tagmod usermod review/;
+my @perms = qw/board boardmod edit imgvote tag dbmod tagmod usermod review lengthvote/;
 
 sub listPerms { @perms }
 
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index d04647ad..ad74088f 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -128,11 +128,12 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
         tuwf->dbExeci(select => sql_func user_setperm_usermod => \$data->{id}, \auth->uid, sql_fromhex(auth->token), \$data->{admin}{perm_usermod});
         $set{"perm_$_"} = $data->{admin}{"perm_$_"} for grep $_ ne 'usermod', auth->listPerms;
     }
-    $set{perm_board}   = $data->{admin}{perm_board}   if auth->permBoardmod;
-    $set{perm_review}  = $data->{admin}{perm_review}  if auth->permBoardmod;
-    $set{perm_edit}    = $data->{admin}{perm_edit}    if auth->permDbmod;
-    $set{perm_imgvote} = $data->{admin}{perm_imgvote} if auth->permDbmod;
-    $set{perm_tag}     = $data->{admin}{perm_tag}     if auth->permTagmod;
+    $set{perm_board}      = $data->{admin}{perm_board}      if auth->permBoardmod;
+    $set{perm_review}     = $data->{admin}{perm_review}     if auth->permBoardmod;
+    $set{perm_edit}       = $data->{admin}{perm_edit}       if auth->permDbmod;
+    $set{perm_imgvote}    = $data->{admin}{perm_imgvote}    if auth->permDbmod;
+    $set{perm_lengthvote} = $data->{admin}{perm_lengthvote} if auth->permDbmod;
+    $set{perm_tag}        = $data->{admin}{perm_tag}        if auth->permTagmod;
 
     if($own && $data->{password}) {
         return elm_InsecurePass if is_insecurepass $data->{password}{new};
diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm
index 3bf02d59..fb452a43 100644
--- a/lib/VNWeb/VN/Elm.pm
+++ b/lib/VNWeb/VN/Elm.pm
@@ -30,4 +30,27 @@ elm_api VN => undef, {
     ');
 };
 
+
+our $LENGTHVOTE = form_compile any => {
+    uid    => { vndbid => 'u' },
+    vid    => { vndbid => 'v' },
+    vote   => { type => 'hash', required => 0, keys => {
+        rid    => { vndbid => 'r' },
+        length => { uint => 1, range => [1,32767] },
+        notes  => { required => 0, default => '' },
+    } },
+};
+
+elm_api VNLengthVote => undef, $LENGTHVOTE, sub {
+    my($data) = @_;
+    return elm_Unauth if !auth->permLengthvote || $data->{uid} ne auth->uid;
+    my %where = ( uid => $data->{uid}, vid => $data->{vid} );
+    tuwf->dbExeci('DELETE FROM vn_length_votes WHERE', \%where) if !$data->{vote};
+    tuwf->dbExeci(
+        'INSERT INTO vn_length_votes', { %where, $data->{vote}->%* },
+        'ON CONFLICT (uid, vid) DO UPDATE SET', $data->{vote}
+    ) if $data->{vote};
+    return elm_Success;
+};
+
 1;
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 6a45502c..10ac10ba 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -355,7 +355,16 @@ sub infobox_ {
 
                 tr_ sub {
                     td_ 'Length';
-                    td_ "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})";
+                    td_ sub {
+                        txt_ "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})";
+                        if (auth->permLengthvote && canvote $v) {
+                            my $vote = tuwf->dbRowi('SELECT rid, length, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
+                            elm_ VNLengthVote => $VNWeb::VN::Elm::LENGTHVOTE, {
+                                uid => auth->uid, vid => $v->{id},
+                                vote => $vote->{rid}?$vote:undef,
+                            }, sub { span_ @_, ''};
+                        }
+                    };
                 } if $v->{length};
 
                 infobox_producers_ $v;
diff --git a/sql/perms.sql b/sql/perms.sql
index 538b8f46..d966d801 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -82,6 +82,7 @@ GRANT SELECT, INSERT, UPDATE         ON vn                       TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_anime                 TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_anime_hist            TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_hist                  TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON vn_length_votes          TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_relations             TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_relations_hist        TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_screenshots           TO vndb_site;
@@ -164,6 +165,7 @@ GRANT SELECT (id),            DELETE ON users_shadow             TO vndb_multi;
 GRANT SELECT,         UPDATE         ON vn                       TO vndb_multi;
 GRANT SELECT                         ON vn_anime                 TO vndb_multi;
 GRANT SELECT                         ON vn_hist                  TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON vn_length_votes          TO vndb_multi;
 GRANT SELECT                         ON vn_relations             TO vndb_multi;
 GRANT SELECT                         ON vn_screenshots           TO vndb_multi;
 GRANT SELECT                         ON vn_screenshots_hist      TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index 78d8eb23..ecc7910d 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -996,7 +996,8 @@ CREATE TABLE users (
   ulist_wish          jsonb,
   vnlang              jsonb, -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
   tableopts_v         integer,
-  tableopts_vt        integer -- VN listing on tag pages
+  tableopts_vt        integer, -- VN listing on tag pages
+  perm_lengthvote     boolean NOT NULL DEFAULT false
 );
 
 -- Additional fields for the 'users' table, but with some protected columns.
@@ -1150,6 +1151,16 @@ CREATE TABLE vn_staff_hist (
   PRIMARY KEY (chid, aid, role)
 );
 
+-- vn_length_votes
+CREATE TABLE vn_length_votes (
+  vid        vndbid NOT NULL,
+  rid        vndbid NOT NULL,
+  date       timestamptz NOT NULL DEFAULT NOW(),
+  uid        vndbid,
+  length     smallint NOT NULL, -- minutes
+  notes      text NOT NULL DEFAULT ''
+);
+
 -- wikidata
 CREATE TABLE wikidata (
   lastfetch          timestamptz,
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 9cb4f061..3842057c 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -114,6 +114,9 @@ ALTER TABLE vn_seiyuu_hist           ADD CONSTRAINT vn_seiyuu_hist_cid_fkey
 ALTER TABLE vn_staff                 ADD CONSTRAINT vn_staff_id_fkey                   FOREIGN KEY (id)        REFERENCES vn            (id);
 ALTER TABLE vn_staff                 ADD CONSTRAINT vn_staff_aid_fkey                  FOREIGN KEY (aid)       REFERENCES staff_alias   (aid) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn_staff_hist            ADD CONSTRAINT vn_staff_hist_chid_fkey            FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_vid_fkey           FOREIGN KEY (vid)       REFERENCES vn            (id);
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_rid_fkey           FOREIGN KEY (rid)       REFERENCES releases      (id);
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_uid_fkey           FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
 
 
 
@@ -152,6 +155,7 @@ 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);
+CREATE UNIQUE INDEX vn_length_votes_pkey   ON vn_length_votes (vid, uid);
 CREATE UNIQUE INDEX changes_itemrev        ON changes (itemid, rev);
 CREATE UNIQUE INDEX chars_vns_pkey         ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier.
 CREATE UNIQUE INDEX chars_vns_hist_pkey    ON chars_vns_hist (chid, vid, COALESCE(rid, 'v1'));
diff --git a/util/updates/2021-07-30-vn-length-voting.sql b/util/updates/2021-07-30-vn-length-voting.sql
new file mode 100644
index 00000000..48dedb52
--- /dev/null
+++ b/util/updates/2021-07-30-vn-length-voting.sql
@@ -0,0 +1,17 @@
+CREATE TABLE vn_length_votes (
+  vid        vndbid NOT NULL,
+  rid        vndbid NOT NULL,
+  date       timestamptz NOT NULL DEFAULT NOW(),
+  uid        vndbid,
+  length     smallint NOT NULL, -- minutes
+  notes      text NOT NULL DEFAULT ''
+);
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_vid_fkey           FOREIGN KEY (vid)       REFERENCES vn            (id);
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_rid_fkey           FOREIGN KEY (rid)       REFERENCES releases      (id);
+ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_uid_fkey           FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
+CREATE UNIQUE INDEX vn_length_votes_pkey   ON vn_length_votes (vid, uid);
+
+-- DEFAULT false while it's in development.
+ALTER TABLE users ADD COLUMN perm_lengthvote boolean NOT NULL DEFAULT false;
+
+\i sql/perms.sql