VN: Add support for multiple titles in different languages

This implements the main database model part of custom title languages (https://vndb.org/t12465). Selecting the right title for display is done in SQL through the 'vnt' VIEW, which can be overridden in each session with a TEMPORARY VIEW in order to support user title preferences, but that part has not been implemented yet. I had started out using an sql_vn() function that returned a subquery instead of using a VIEW, but then ran into trouble with the item_info() SQL function. This VIEW approach also happened to simplify much of the code. I did have to get rid of the Discusssions::Lib::sql_boards() function, as Postgres was unable to optimize the subquery inside a UNION inside a subquery for some reason. Haven't run into any other noticeable performance regressions yet. TODO: - Implement actual user title preferences - Add the correct 'lang' HTML attributes everywhere a title is displayed (we do have the information now, though it still isn't trivial) - Add title fetching support to API
author: Yorhel <git@yorhel.nl> 2022-01-20 09:02:15 +0100
committer: Yorhel <git@yorhel.nl> 2022-02-08 11:36:07 +0100
commit: 727a4d84255f13badffe16c5858fdb036ddcdbce (patch)
tree: 6c77f72e2423a7c7425caea6aebc6ba8eaf001f8
parent: 9d651b8d138349b3853f23117cc3802fcfe0ba6a (diff)
44 files changed, 322 insertions, 187 deletions
diff --git a/css/v2.css b/css/v2.css
index 51a489fe..5d1c2bba 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -484,7 +484,7 @@ div.vndetails > table         { float: left; width: 500px; }
 div.vndetails > table td.key  { width: 90px; }
 div.vndetails > table dt      { float: left; font-style: italic; }
 div.vndetails > table dd      { margin-left: 90px; }
-div.vndetails td.title abbr   { float: right }
+div.vndetails td.title td     { padding: 0 0px }
 div.vndetails td.relations dt { float: none; font-style: normal; }
 div.vndetails td.relations dd { margin-left: 15px; }
 div.vndetails td.anime b      { font-size: 10px; font-weight: normal; padding-right: 4px; }
diff --git a/elm/Lib/Util.elm b/elm/Lib/Util.elm
index 85cc5404..c4d51e50 100644
--- a/elm/Lib/Util.elm
+++ b/elm/Lib/Util.elm
@@ -1,6 +1,6 @@
 module Lib.Util exposing (..)
 
-import Dict
+import Set
 import Task
 import Regex
 import Lib.Ffi as Ffi
@@ -29,13 +29,20 @@ hasDuplicates l =
     step e acc =
       case acc of
         Nothing -> Nothing
-        Just m -> if Dict.member e m then Nothing else Just (Dict.insert e True m)
+        Just m -> if Set.member e m then Nothing else Just (Set.insert e m)
   in
-    case List.foldr step (Just Dict.empty) l of
+    case List.foldr step (Just Set.empty) l of
       Nothing -> True
       Just _  -> False
 
 
+-- Returns true if list a contains elements also in list b
+contains : List comparable -> List comparable -> Bool
+contains a b =
+  let d = Set.fromList b
+  in List.any (\e -> Set.member e d) a
+
+
 -- Haskell's 'lookup' - find an entry in an association list
 lookup : a -> List (a,b) -> Maybe b
 lookup n l = List.filter (\(a,_) -> a == n) l |> List.head |> Maybe.map Tuple.second
@@ -82,7 +89,7 @@ jap_ = Maybe.withDefault Regex.never (Regex.fromString "[\\u3000-\\u9fff\\uff00-
 
 -- Not even close to comprehensive, just excludes a few scripts commonly found on VNDB.
 nonlatin_ : Regex.Regex
-nonlatin_ = Maybe.withDefault Regex.never (Regex.fromString "[\\u3000-\\u9fff\\uff00-\\uff9f\\u0400-\\u04ff\\u1100-\\u11ff\\uac00-\\ud7af\\u0600-\\u06ff]")
+nonlatin_ = Maybe.withDefault Regex.never (Regex.fromString "[\\u3000-\\u9fff\\uff00-\\uff9f\\u0400-\\u04ff\\u1100-\\u11ff\\uac00-\\ud7af\\u0600-\\u06ff\\u0e00-\\u0e7f]")
 
 -- This regex can't differentiate between Japanese and Chinese, so has a good chance of returning true for Chinese as well.
 containsJapanese : String -> Bool
diff --git a/elm/VNEdit.elm b/elm/VNEdit.elm
index 269a2e14..da88be7d 100644
--- a/elm/VNEdit.elm
+++ b/elm/VNEdit.elm
@@ -6,6 +6,7 @@ import Html.Keyed as K
 import Html.Attributes exposing (..)
 import Browser
 import Browser.Navigation exposing (load)
+import Browser.Dom as Dom
 import Dict
 import Set
 import Task
@@ -51,8 +52,7 @@ type alias Model =
   , tab         : Tab
   , invalidDis  : Bool
   , editsum     : Editsum.Model
-  , title       : String
-  , original    : String
+  , titles      : List GVE.RecvTitles
   , alias       : String
   , desc        : TP.Model
   , olang       : String
@@ -88,8 +88,7 @@ init d =
   , tab         = General
   , invalidDis  = False
   , editsum     = { authmod = d.authmod, editsum = TP.bbcode d.editsum, locked = d.locked, hidden = d.hidden, hasawait = False }
-  , title       = d.title
-  , original    = d.original
+  , titles      = d.titles
   , alias       = d.alias
   , desc        = TP.bbcode d.desc
   , olang       = d.olang
@@ -125,8 +124,7 @@ encode model =
   , editsum     = model.editsum.editsum.data
   , hidden      = model.editsum.hidden
   , locked      = model.editsum.locked
-  , title       = model.title
-  , original    = model.original
+  , titles      = model.titles
   , alias       = model.alias
   , desc        = model.desc.data
   , olang       = model.olang
@@ -154,20 +152,25 @@ seiyuuConfig : A.Config Msg GApi.ApiStaffResult
 seiyuuConfig = { wrap = SeiyuuSearch, id = "seiyuuadd", source = A.staffSource }
 
 type Msg
-  = Editsum Editsum.Msg
+  = Noop
+  | Editsum Editsum.Msg
   | Tab Tab
   | Invalid Tab
   | InvalidEnable
   | Submit
   | Submitted GApi.Response
-  | Title String
-  | Original String
   | Alias String
   | Desc TP.Msg
-  | OLang String
   | Length Int
   | LWikidata (Maybe Int)
   | LRenai String
+  | TitleAdd String
+  | TitleDel Int
+  | TitleLang Int String
+  | TitleTitle Int String
+  | TitleLatin Int String
+  | TitleOfficial Int Bool
+  | TitleMain Int String
   | VNDel Int
   | VNRel Int String
   | VNOfficial Int Bool
@@ -213,20 +216,28 @@ scrProcessQueue (model, msg) =
 update : Msg -> Model -> (Model, Cmd Msg)
 update msg model =
   case msg of
+    Noop       -> (model, Cmd.none)
     Editsum m  -> let (nm,nc) = Editsum.update m model.editsum in ({ model | editsum = nm }, Cmd.map Editsum nc)
     Tab t      -> ({ model | tab = t }, Cmd.none)
     Invalid t  -> if model.invalidDis || model.tab == All || model.tab == t then (model, Cmd.none) else
                   ({ model | tab = t, invalidDis = True }, Task.attempt (always InvalidEnable) (Ffi.elemCall "reportValidity" "mainform" |> Task.andThen (\_ -> Process.sleep 100)))
     InvalidEnable -> ({ model | invalidDis = False }, Cmd.none)
-    Title s    -> ({ model | title    = s, dupVNs = [] }, Cmd.none)
-    Original s -> ({ model | original = s, dupVNs = [] }, Cmd.none)
     Alias s    -> ({ model | alias    = s, dupVNs = [] }, Cmd.none)
     Desc m     -> let (nm,nc) = TP.update m model.desc in ({ model | desc = nm }, Cmd.map Desc nc)
-    OLang s    -> ({ model | olang    = s }, Cmd.none)
     Length n   -> ({ model | length = n }, Cmd.none)
     LWikidata n-> ({ model | lWikidata = n }, Cmd.none)
     LRenai s   -> ({ model | lRenai = s }, Cmd.none)
 
+    TitleAdd s ->
+      ({ model | titles = model.titles ++ [{ lang = s, title = "", latin = Nothing, official = True }], olang = if List.isEmpty model.titles then s else model.olang }
+      , Task.attempt (always Noop) (Dom.focus ("title_" ++ s)))
+    TitleDel i        -> ({ model | titles = delidx i model.titles }, Cmd.none)
+    TitleLang i s     -> ({ model | titles = modidx i (\e -> { e | lang = s }) model.titles }, Cmd.none)
+    TitleTitle i s    -> ({ model | titles = modidx i (\e -> { e | title = s }) model.titles }, Cmd.none)
+    TitleLatin i s    -> ({ model | titles = modidx i (\e -> { e | latin = if s == "" then Nothing else Just s }) model.titles }, Cmd.none)
+    TitleOfficial i s -> ({ model | titles = modidx i (\e -> { e | official = s }) model.titles }, Cmd.none)
+    TitleMain i s     -> ({ model | olang = s, titles = modidx i (\e -> { e | official = True }) model.titles }, Cmd.none)
+
     VNDel idx        -> ({ model | vns = delidx idx model.vns }, Cmd.none)
     VNRel idx rel    -> ({ model | vns = modidx idx (\v -> { v | relation = rel }) model.vns }, Cmd.none)
     VNOfficial idx o -> ({ model | vns = modidx idx (\v -> { v | official = o   }) model.vns }, Cmd.none)
@@ -237,7 +248,7 @@ update msg model =
         Just v ->
           if List.any (\l -> l.vid == v.id) model.vns
           then ({ model | vnSearch = A.clear nm "" }, c)
-          else ({ model | vnSearch = A.clear nm "", vns = model.vns ++ [{ vid = v.id, title = v.title, original = v.original, relation = "seq", official = True }] }, c)
+          else ({ model | vnSearch = A.clear nm "", vns = model.vns ++ [{ vid = v.id, title = v.title, relation = "seq", official = True }] }, c)
 
     AnimeDel i -> ({ model | anime = delidx i model.anime }, Cmd.none)
     AnimeSearch m ->
@@ -290,7 +301,7 @@ update msg model =
 
     DupSubmit ->
       if List.isEmpty model.dupVNs
-      then ({ model | state = Api.Loading }, GV.send { hidden = True, search = model.title :: model.original :: String.lines model.alias } DupResults)
+      then ({ model | state = Api.Loading }, GV.send { hidden = True, search = (List.concatMap (\e -> [e.title, Maybe.withDefault "" e.latin]) model.titles) ++ String.lines model.alias } DupResults)
       else ({ model | dupCheck = True, dupVNs = [] }, Cmd.none)
     DupResults (GApi.VNResult vns) ->
       if List.isEmpty vns
@@ -312,7 +323,8 @@ relAlias model =
 
 isValid : Model -> Bool
 isValid model = not
-  (  (model.title /= "" && model.title == model.original)
+  (  List.any (\e -> e.title /= "" && Just e.title == e.latin) model.titles
+  || List.isEmpty model.titles
   || relAlias model /= Nothing
   || not (Img.isValid model.image)
   || List.any (\(_,i,r) -> r == Nothing || not (Img.isValid i)) model.screenshots
@@ -325,26 +337,46 @@ isValid model = not
 view : Model -> Html Msg
 view model =
   let
-    titles =
-      [ formField "title::Title (romaji)"
-        [ inputText "title" model.title Title (style "width" "500px" :: onInvalid (Invalid General) :: GVE.valTitle)
-        , if containsNonLatin model.title
-          then b [ class "standout" ] [ br [] [], text "This title field should only contain latin-alphabet characters, please put the \"actual\" title in the field below and the romanization above." ]
-          else text ""
+    title i e = tr []
+      [ td [] [ langIcon e.lang ]
+      , td []
+        [ inputText ("title_"++e.lang) e.title (TitleTitle i) (style "width" "500px" :: onInvalid (Invalid General) :: placeholder "Title (in the original script)" :: GVE.valTitlesTitle)
+        , if not (e.latin /= Nothing || containsNonLatin e.title) then text "" else span []
+          [ br [] []
+          , inputText "" (Maybe.withDefault "" e.latin) (TitleLatin i) (style "width" "500px" :: onInvalid (Invalid General) :: placeholder "Romanization" :: GVE.valTitlesLatin)
+          , case e.latin of
+              Just s -> if containsNonLatin s then b [ class "standout" ] [ br [] [], text "Romanization should only consist of characters in the latin alphabet." ] else text ""
+              Nothing -> text ""
+          ]
+        , if List.length model.titles == 1 then text "" else span []
+          [ br [] []
+          , label [] [ inputRadio "olang" (e.lang == model.olang) (\_ -> TitleMain i e.lang), text " main title (the language the VN was originally written in)" ]
+          ]
+        , if e.lang == model.olang then text "" else span []
+          [ br [] []
+          , label [] [ inputCheck "" e.official (TitleOfficial i), text " official title (from the developer or licensed localization; not from a fan translation)" ]
+          , br [] []
+          , inputButton "remove" (TitleDel i) []
+          ]
+        , br_ 2
         ]
-      , formField "original::Original title"
-        [ inputText "original" model.original Original (style "width" "500px" :: onInvalid (Invalid General) :: GVE.valOriginal)
-        , if model.title /= "" && model.title == model.original
-          then b [ class "standout" ] [ br [] [], text "Should not be the same as the Title (romaji). Leave blank if the original title is already in the latin alphabet" ]
-          else if model.original /= "" && String.toLower model.title /= String.toLower model.original && not (containsNonLatin model.original)
-          then b [ class "standout" ] [ br [] [], text "Original title does not seem to contain any non-latin characters. Leave this field empty if the title is already in the latin alphabet" ]
-          else text ""
+      ]
+
+    titles =
+      let lines = List.filter (\e -> e /= "") <| String.lines <| String.toLower model.alias
+      in
+      [ formField "Title(s)"
+        [ table [] <| List.indexedMap title model.titles
+        , inputSelect "" "" TitleAdd [] <| ("", "- Add title -") :: List.filter (\(l,_) -> not <| List.any (\e -> e.lang == l) model.titles) GT.languages
+        , br_ 2
         ]
       , formField "alias::Aliases"
         [ inputTextArea "alias" model.alias Alias (rows 3 :: onInvalid (Invalid General) :: GVE.valAlias)
         , br [] []
-        , if hasDuplicates <| String.lines <| String.toLower model.alias
+        , if hasDuplicates lines
           then b [ class "standout" ] [ text "List contains duplicate aliases.", br [] [] ]
+          else if contains lines <| List.map String.toLower <| List.concatMap (\e -> [e.title, Maybe.withDefault "" e.latin]) model.titles
+          then b [ class "standout" ] [ text "Titles listed above should not also be added as alias.", br [] [] ]
           else
             case relAlias model of
               Nothing -> text ""
@@ -355,7 +387,7 @@ view model =
                 , a [ href <| "/"++r.id ] [ text r.title ]
                 , br [] [], br [] []
                 ]
-        , text "List of alternative titles or abbreviations. One line for each alias. Can include both official (japanese/english) titles and unofficial titles used around net."
+        , text "List of additional titles or abbreviations. One line for each alias. Can include both official (japanese/english) titles and unofficial titles used around net."
         , br [] []
         , text "Titles that are listed in the releases should not be added here!"
         ]
@@ -366,7 +398,6 @@ view model =
         [ TP.view "desc" model.desc Desc 600 (style "height" "180px" :: onInvalid (Invalid General) :: GVE.valDesc) [ b [ class "standout" ] [ text "English please!" ] ]
         , text "Short description of the main story. Please do not include spoilers, and don't forget to list the source in case you didn't write the description yourself."
         ]
-      , formField "olang::Original language" [ inputSelect "olang" model.olang OLang [] GT.languages ]
       , formField "length::Length"
         [ inputSelect "length" model.length Length [] GT.vnLengths
         , text " (only displayed if there are no length votes)" ]
diff --git a/lib/VNDB/Schema.pm b/lib/VNDB/Schema.pm
index 654a08b9..127d5384 100644
--- a/lib/VNDB/Schema.pm
+++ b/lib/VNDB/Schema.pm
@@ -35,9 +35,7 @@ sub schema {
     while(<$F>) {
         chomp;
         next if /^\s*--/ || /^\s*$/;
-        next if /^\s*CREATE\s+TYPE/;
-        next if /^\s*CREATE\s+SEQUENCE/;
-        next if /^\s*CREATE\s+FUNCTION/;
+        next if /^\s*CREATE\s+(?:TYPE|SEQUENCE|FUNCTION|VIEW)/;
 
         if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) {
             die "Unexpected 'CREATE TABLE $1'\n" if $table;
diff --git a/lib/VNWeb/Chars/Edit.pm b/lib/VNWeb/Chars/Edit.pm
index 0bc00d5c..4beefaa5 100644
--- a/lib/VNWeb/Chars/Edit.pm
+++ b/lib/VNWeb/Chars/Edit.pm
@@ -73,7 +73,7 @@ TUWF::get qr{/$RE{crev}/(?<action>edit|copy)} => sub {
     enrich_merge tid => 'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, g.name AS group, g.order AS order, false AS new FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $e->{traits};
     $e->{traits} = [ sort { ($a->{order}//99) <=> ($b->{order}//99) || $a->{name} cmp $b->{name} } grep !$copy || $_->{applicable}, $e->{traits}->@* ];
 
-    enrich_merge vid => 'SELECT id AS vid, title FROM vn WHERE id IN', $e->{vns};
+    enrich_merge vid => 'SELECT id AS vid, title FROM vnt WHERE id IN', $e->{vns};
     $e->{vns} = [ sort { $a->{title} cmp $b->{title} || idcmp($a->{vid}, $b->{vid}) || idcmp($a->{rid}||'r0', $b->{rid}||'r0') } $e->{vns}->@* ];
 
     my %vns;
@@ -100,7 +100,7 @@ TUWF::get qr{/$RE{crev}/(?<action>edit|copy)} => sub {
 
 TUWF::get qr{/$RE{vid}/addchar}, sub {
     return tuwf->resDenied if !can_edit c => undef;
-    my $v = tuwf->dbRowi('SELECT id, title FROM vn WHERE NOT hidden AND id =', \tuwf->capture('id'));
+    my $v = tuwf->dbRowi('SELECT id, title FROM vnt WHERE NOT hidden AND id =', \tuwf->capture('id'));
     return tuwf->resNotFound if !$v->{id};
 
     my $e = elm_empty($FORM_OUT);
diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm
index 5e44a606..81d2dc19 100644
--- a/lib/VNWeb/Chars/List.pm
+++ b/lib/VNWeb/Chars/List.pm
@@ -26,7 +26,7 @@ sub listing_ {
                 td_ class => 'tc2', sub {
                     a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name};
                     b_ class => 'grayedout', sub {
-                        join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }, $_->{vn}->@*;
+                        join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} }, $_->{vn}->@*;
                     };
                 };
             } for @$list;
@@ -49,7 +49,7 @@ sub listing_ {
                 a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name};
                 br_;
                 b_ class => 'grayedout', sub {
-                    join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }, $_->{vn}->@*;
+                    join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} }, $_->{vn}->@*;
                 };
             };
         } for @$list;
@@ -71,9 +71,9 @@ sub listing_ {
 # Also used by VNWeb::TT::TraitPage
 sub enrich_listing {
     enrich vn => id => cid => sub { sql '
-        SELECT DISTINCT cv.id AS cid, v.id, v.title, v.original
+        SELECT DISTINCT cv.id AS cid, v.id, v.title, v.alttitle
           FROM chars_vns cv
-          JOIN vn v ON v.id = cv.vid
+          JOIN vnt v ON v.id = cv.vid
          WHERE NOT v.hidden AND cv.spoil = 0 AND cv.id IN', $_, '
          ORDER BY v.title'
     }, @_;
diff --git a/lib/VNWeb/Chars/Page.pm b/lib/VNWeb/Chars/Page.pm
index e2d10068..c16b1cd1 100644
--- a/lib/VNWeb/Chars/Page.pm
+++ b/lib/VNWeb/Chars/Page.pm
@@ -20,7 +20,7 @@ sub enrich_item {
     my($c) = @_;
 
     enrich_image_obj image => $c;
-    enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $c->{vns};
+    enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $c->{vns};
     enrich_merge rid => 'SELECT id AS rid, title AS rtitle, original AS roriginal FROM releases WHERE id IN', grep $_->{rid}, $c->{vns}->@*;
     enrich_merge tid =>
      'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order
@@ -42,9 +42,9 @@ sub fetch_chars {
     ');
 
     enrich vns => id => id => sub { sql '
-        SELECT cv.id, cv.vid, cv.rid, cv.spoil, cv.role, v.title, v.original, r.title AS rtitle, r.original AS roriginal
+        SELECT cv.id, cv.vid, cv.rid, cv.spoil, cv.role, v.title, v.alttitle, r.title AS rtitle, r.original AS roriginal
           FROM chars_vns cv
-          JOIN vn v ON v.id = cv.vid
+          JOIN vnt v ON v.id = cv.vid
           LEFT JOIN releases r ON r.id = cv.rid
          WHERE cv.id IN', $_, $vid ? ('AND cv.vid =', \$vid) : (), '
          ORDER BY v.title, cv.vid, cv.rid NULLS LAST'
@@ -91,7 +91,7 @@ sub _rev_ {
         [ main_spoil => 'Spoiler',       fmt => sub { txt_ fmtspoil $_ } ],
         [ image      => 'Image',         fmt => sub { image_ $_ } ],
         [ vns        => 'Visual novels', fmt => sub {
-            a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{vid};
+            a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{vid};
             if($_->{rid}) {
                 txt_ ' ['; a_ href => "/$_->{rid}", $_->{rid}; txt_ ']';
             }
@@ -181,11 +181,11 @@ sub chartable_ {
                         # Just a VN link, no releases
                         if(!$vn && $v->{rels}->@* == 1 && !$v->{rels}[0]{rid}) {
                             txt_ $CHAR_ROLE{$v->{role}}{txt}.' - ';
-                            a_ href => "/$v->{vid}", title => $v->{original}||$v->{title}, $v->{title};
+                            a_ href => "/$v->{vid}", title => $v->{alttitle}||$v->{title}, $v->{title};
                             spoil_ $v->{spoil};
                         # With releases
                         } else {
-                            a_ href => "/$v->{vid}", title => $v->{original}||$v->{title}, $v->{title} if !$vn;
+                            a_ href => "/$v->{vid}", title => $v->{alttitle}||$v->{title}, $v->{title} if !$vn;
                             br_ if !$vn;
                             join_ \&br_, sub {
                                 b_ class => 'grayedout', '> ';
diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm
index 321de780..b68ef432 100644
--- a/lib/VNWeb/Discussions/Edit.pm
+++ b/lib/VNWeb/Discussions/Edit.pm
@@ -112,9 +112,11 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub {
 TUWF::get qr{(?:/t/(?<board>$BOARD_RE)/new|/$RE{tid}\.1/edit)}, sub {
     my $board_id = tuwf->capture('board')||'';
     my($board_type) = $board_id =~ /^([^0-9]+)/;
-    $board_id = undef if $board_id !~ /[0-9]$/;
+    $board_id = $board_id =~ /[0-9]$/ ? dbobj $board_id : undef;
     my $tid = tuwf->capture('id');
 
+    return tuwf->resNotFound if $board_id && !$board_id->{id};
+
     $board_type = 'ge' if $board_type && $board_type eq 'an' && !auth->permBoardmod;
 
     my $t = !$tid ? {} : tuwf->dbRowi('
@@ -136,13 +138,11 @@ TUWF::get qr{(?:/t/(?<board>$BOARD_RE)/new|/$RE{tid}\.1/edit)}, sub {
     } else {
         $t->{boards} = [ {
             btype => $board_type,
-            iid   => $board_id||undef,
-            title => !$board_id ? undef :
-                tuwf->dbVali('SELECT title FROM', sql_boards(), 'x WHERE btype =', \$board_type, 'AND iid =', \$board_id)
+            iid   => $board_id ? $board_id->{id} : undef,
+            title => $board_id ? $board_id->{title} : undef,
         } ];
-        return tuwf->resNotFound if $board_id && !length $t->{boards}[0]{title};
         push $t->{boards}->@*, { btype => 'u', iid => auth->uid, title => auth->user->{user_name} }
-            if $board_type eq 'u' && $board_id ne auth->uid;
+            if $board_type eq 'u' && $board_id->{id} ne auth->uid;
     }
 
     $t->{can_mod}     = auth->permBoardmod;
diff --git a/lib/VNWeb/Discussions/Elm.pm b/lib/VNWeb/Discussions/Elm.pm
index 27bd12f0..08329763 100644
--- a/lib/VNWeb/Discussions/Elm.pm
+++ b/lib/VNWeb/Discussions/Elm.pm
@@ -27,11 +27,11 @@ elm_api Boards => undef, {
         'SELECT btype, iid, title
            FROM (',
              sql_join('UNION ALL',
-                 (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'),#, \$BOARD_TYPE{$_}{txt}),
+                 (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'),
                      grep $q eq $_ || $BOARD_TYPE{$_}{txt} =~ /\Q$q/i,
                      grep !$BOARD_TYPE{$_}{dbitem} && ($BOARD_TYPE{$_}{post_perm} eq 'board' || auth->permBoardmod),
                      keys %BOARD_TYPE),
-                 item('vn', 'v', 'title', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
+                 item('vnt', 'v', 'title', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
                  item('producers', 'p', 'name', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
                  item('users', 'u', 'username', 'true', sql 'lower(username) LIKE', \lc "%$qs%"),
              ), ') x(prio, btype, iid, title)
diff --git a/lib/VNWeb/Discussions/Lib.pm b/lib/VNWeb/Discussions/Lib.pm
index 7e9465b2..e5131934 100644
--- a/lib/VNWeb/Discussions/Lib.pm
+++ b/lib/VNWeb/Discussions/Lib.pm
@@ -3,7 +3,7 @@ package VNWeb::Discussions::Lib;
 use VNWeb::Prelude;
 use Exporter 'import';
 
-our @EXPORT = qw/$BOARD_RE sql_visible_threads sql_boards enrich_boards threadlist_ boardsearch_ boardtypes_/;
+our @EXPORT = qw/$BOARD_RE sql_visible_threads enrich_boards threadlist_ boardsearch_ boardtypes_/;
 
 
 our $BOARD_RE = join '|', map $_.($BOARD_TYPE{$_}{dbitem}?'(?:[1-9][0-9]{0,5})?':''), keys %BOARD_TYPE;
@@ -18,25 +18,20 @@ sub sql_visible_threads {
 }
 
 
-# Returns a SELECT subquery with all board IDs
-sub sql_boards {
-    sql q{(   SELECT 'v'::board_type AS btype, id AS iid, title,    original, hidden FROM vn
-    UNION ALL SELECT 'p'::board_type AS btype, id AS iid, name,     original, hidden FROM producers
-    UNION ALL SELECT 'u'::board_type AS btype, id AS iid, username, NULL,     false  FROM users
-    )}
-}
-
-
 # Adds a 'boards' array to threads.
 sub enrich_boards {
     my($filt, @lst) = @_;
-    enrich boards => id => tid => sub { sql q{
-        SELECT tb.tid, tb.type AS btype, tb.iid, b.title, b.original
+    enrich boards => id => tid => sub { sql '
+        SELECT tb.tid, tb.type AS btype, tb.iid
+              , COALESCE(v.title, p.name, u.username) AS title
+              , COALESCE(v.alttitle, p.original) AS alttitle
           FROM threads_boards tb
-          LEFT JOIN }, sql_boards(), q{b ON b.btype = tb.type AND b.iid = tb.iid
-         WHERE }, sql_and(sql('tb.tid IN', $_[0]), $filt||()), q{
+          LEFT JOIN vnt v ON tb.type = \'v\' AND v.id = tb.iid
+          LEFT JOIN producers p ON tb.type = \'p\' AND p.id = tb.iid
+          LEFT JOIN users u ON tb.type = \'u\' AND u.id = tb.iid
+         WHERE ', sql_and(sql('tb.tid IN', $_[0]), $filt||()), '
          ORDER BY tb.type, tb.iid
-    }}, @lst;
+    '}, @lst;
 }
 
 
@@ -94,7 +89,7 @@ sub threadlist_ {
                     b_ class => 'boards', sub {
                         join_ ', ', sub {
                             a_ href => '/t/'.($_->{iid}||$_->{btype}),
-                                title => $_->{original}||$BOARD_TYPE{$_->{btype}}{txt},
+                                title => $_->{alttitle}||$BOARD_TYPE{$_->{btype}}{txt},
                                 shorten $_->{title}||$BOARD_TYPE{$_->{btype}}{txt}, 30;
                         }, $l->{boards}->@[0 .. min 4, $#{$l->{boards}}];
                         txt_ ', ...' if $l->{boards}->@* > 4;
diff --git a/lib/VNWeb/Discussions/Search.pm b/lib/VNWeb/Discussions/Search.pm
index cb7a31b7..6a3babc0 100644
--- a/lib/VNWeb/Discussions/Search.pm
+++ b/lib/VNWeb/Discussions/Search.pm
@@ -86,12 +86,12 @@ sub posts_ {
              ) : (), $reviews ? (
                  sql('SELECT w.id, 0, v.title, w.uid, w.date, w.text
                         FROM reviews w
-                        JOIN vn v ON v.id = w.vid
+                        JOIN vnt v ON v.id = w.vid
                        WHERE NOT w.c_flagged AND bb_tsvector(w.text) @@ to_tsquery(', \$ts, ')'),
                  sql('SELECT wp.id, wp.num, v.title, wp.uid, wp.date, wp.msg
                         FROM reviews_posts wp
                         JOIN reviews w ON w.id = wp.id
-                        JOIN vn v ON v.id = w.vid
+                        JOIN vnt v ON v.id = w.vid
                        WHERE NOT w.c_flagged AND wp.hidden IS NULL AND bb_tsvector(wp.msg) @@ to_tsquery(', \$ts, ')'),
              ) : ()), ') m (id, num, title, uid, date, msg)
           LEFT JOIN users u ON u.id = m.uid
diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm
index 84ce9977..669fa33b 100644
--- a/lib/VNWeb/Discussions/Thread.pm
+++ b/lib/VNWeb/Discussions/Thread.pm
@@ -83,7 +83,7 @@ sub metabox_ {
                     a_ style => 'font-weight: bold', href => "/t/$_->{iid}", $_->{iid};
                     txt_ ':';
                     if($_->{title}) {
-                        a_ href => "/$_->{iid}", title => $_->{original}||$_->{title}, $_->{title};
+                        a_ href => "/$_->{iid}", title => $_->{alttitle}||$_->{title}, $_->{title};
                     } else {
                         b_ '[deleted]';
                     }
diff --git a/lib/VNWeb/Discussions/UPosts.pm b/lib/VNWeb/Discussions/UPosts.pm
index a8cb437c..afe7c256 100644
--- a/lib/VNWeb/Discussions/UPosts.pm
+++ b/lib/VNWeb/Discussions/UPosts.pm
@@ -49,7 +49,7 @@ TUWF::get qr{/$RE{uid}/posts}, sub {
         SELECT rp.id, rp.num, rp.msg, v.title, rp.date, rp.hidden IS NOT NULL
           FROM reviews_posts rp
           JOIN reviews r ON r.id = rp.id
-          JOIN vn v ON v.id = r.vid
+          JOIN vnt v ON v.id = r.vid
          WHERE rp.uid =', \$u->{id}, auth->permBoardmod ? () : 'AND rp.hidden IS NULL', '
        ) p(id,num,msg,title,date,hidden)';
 
diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm
index da98ae2b..0d90d0f8 100644
--- a/lib/VNWeb/Elm.pm
+++ b/lib/VNWeb/Elm.pm
@@ -104,7 +104,7 @@ our %apis = (
     VNResult       => [ { aoh => { # Response to 'VN'
         id       => { vndbid => 'v' },
         title    => {},
-        original => { required => 0, default => '' },
+        alttitle => { required => 0, default => '' },
         hidden   => { anybool => 1 },
     } } ],
     ProducerResult => [ { aoh => { # Response to 'Producers'
diff --git a/lib/VNWeb/Images/Lib.pm b/lib/VNWeb/Images/Lib.pm
index 3adb142d..fb5c57f5 100644
--- a/lib/VNWeb/Images/Lib.pm
+++ b/lib/VNWeb/Images/Lib.pm
@@ -29,10 +29,10 @@ sub enrich_image {
            , COALESCE(v.title, c.name, vsv.title) AS entry_title
         FROM images i
         LEFT JOIN image_votes iv ON iv.id = i.id AND iv.uid =}, \auth->uid, q{
-        LEFT JOIN vn v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id
+        LEFT JOIN vnt v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id
         LEFT JOIN chars c ON i.id BETWEEN 'ch1' AND vndbid_max('ch') AND c.image = i.id
         LEFT JOIN vn_screenshots vs ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vs.scr = i.id
-        LEFT JOIN vn vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id
+        LEFT JOIN vnt vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id
        WHERE i.id IN}, $_
     }, $l;
 
diff --git a/lib/VNWeb/Misc/HomePage.pm b/lib/VNWeb/Misc/HomePage.pm
index 36b241c6..6dcac4a0 100644
--- a/lib/VNWeb/Misc/HomePage.pm
+++ b/lib/VNWeb/Misc/HomePage.pm
@@ -16,7 +16,7 @@ sub screens_ {
         # (As of Sep 2020, over half of the VNs in the database have screenshots, so that assumption usually works)
         'SELECT * FROM (
             SELECT DISTINCT ON (v.id) i.id, i.width, i,height, v.id AS vid, v.title
-              FROM (SELECT id, title FROM vn v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v
+              FROM (SELECT id, title FROM vnt v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v
               JOIN vn_screenshots vs ON v.id = vs.id
               JOIN images i ON i.id = vs.scr
              WHERE ', $where, '
@@ -26,7 +26,7 @@ sub screens_ {
         SELECT i.id, i.width, i.height, v.id AS vid, v.title
           FROM (SELECT id, width, height FROM images i TABLESAMPLE SYSTEM (', \$sample, ') WHERE', $where, ' ORDER BY random() LIMIT', \4, ') i(id)
           JOIN vn_screenshots vs ON vs.scr = i.id
-          JOIN vn v ON v.id = vs.id
+          JOIN vnt v ON v.id = vs.id
          WHERE NOT v.hidden
          ORDER BY random()
          LIMIT', \4
@@ -125,7 +125,7 @@ sub recent_vn_posts_ {
             SELECT w.id, v.title, wp.num, wp.date, wp.uid
               FROM reviews w
               JOIN reviews_posts wp ON wp.id = w.id AND wp.num = w.c_lastnum
-              JOIN vn v ON v.id = w.vid
+              JOIN vnt v ON v.id = w.vid
               LEFT JOIN users u ON wp.uid = u.id
              WHERE NOT w.c_flagged AND wp.hidden IS NULL
              ORDER BY wp.date DESC LIMIT 10
@@ -207,7 +207,7 @@ sub reviews_ {
     my $lst = tuwf->dbAlli('
         SELECT w.id, v.title, w.isfull, ', sql_user(), ',', sql_totime('w.date'), 'AS date
           FROM reviews w
-          JOIN vn v ON v.id = w.vid
+          JOIN vnt v ON v.id = w.vid
           LEFT JOIN users u ON u.id = w.uid
          WHERE NOT w.c_flagged
          ORDER BY w.id DESC LIMIT 10'
diff --git a/lib/VNWeb/Producers/Page.pm b/lib/VNWeb/Producers/Page.pm
index c5a2b2e5..ac722145 100644
--- a/lib/VNWeb/Producers/Page.pm
+++ b/lib/VNWeb/Producers/Page.pm
@@ -78,8 +78,8 @@ sub rel_ {
     enrich_extlinks r => $r;
     enrich_release $r;
     enrich vn => id => rid => sub { sql '
-        SELECT rv.id as rid, rv.rtype, v.id, v.title, v.original
-          FROM vn v
+        SELECT rv.id as rid, rv.rtype, v.id, v.title, v.alttitle
+          FROM vnt v
           JOIN releases_vn rv ON rv.vid = v.id
          WHERE NOT v.hidden AND rv.id IN', $_, '
          ORDER BY v.title
@@ -101,7 +101,7 @@ sub rel_ {
             tr_ class => 'vn', sub {
                 td_ colspan => 8, sub {
                     ulists_widget_ $v;
-                    a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, $v->{title};
+                    a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, $v->{title};
                 };
                 my $ropt = { id => $v->{id}, prod => 1 };
                 for my $rel ($vn{$v->{id}}->@*) {
@@ -118,8 +118,8 @@ sub rel_ {
 sub vns_ {
     my($p) = @_;
     my $v = tuwf->dbAlli(q{
-        SELECT v.id, v.title, v.original, rels.developer, rels.publisher, rels.released
-          FROM vn v
+        SELECT v.id, v.title, v.alttitle, rels.developer, rels.publisher, rels.released
+          FROM vnt v
           JOIN (
                SELECT rv.vid, bool_or(rp.developer), bool_or(rp.publisher)
                     , COALESCE(MIN(r.released) FILTER(WHERE rv.rtype <> 'trial'), MIN(r.released))
@@ -141,7 +141,7 @@ sub vns_ {
         li_ sub {
             span_ sub { rdate_ $_->{released} };
             ulists_widget_ $_;
-            a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title};
+            a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title};
             span_ join ' & ',
                 $_->{publisher} ? 'Publisher' : (),
                 $_->{developer} ? 'Developer' : ();
diff --git a/lib/VNWeb/Releases/Edit.pm b/lib/VNWeb/Releases/Edit.pm
index 1c44ffb3..c4abf8f0 100644
--- a/lib/VNWeb/Releases/Edit.pm
+++ b/lib/VNWeb/Releases/Edit.pm
@@ -69,7 +69,7 @@ TUWF::get qr{/$RE{rrev}/(?<action>edit|copy)} => sub {
 
     to_extlinks $e;
 
-    enrich_merge vid => 'SELECT id AS vid, title FROM vn WHERE id IN', $e->{vn};
+    enrich_merge vid => 'SELECT id AS vid, title FROM vnt WHERE id IN', $e->{vn};
     enrich_merge pid => 'SELECT id AS pid, name FROM producers WHERE id IN', $e->{producers};
 
     $e->@{qw/gtin catalog extlinks/} = elm_empty($FORM_OUT)->@{qw/gtin catalog extlinks/} if $copy;
@@ -85,7 +85,12 @@ TUWF::get qr{/$RE{rrev}/(?<action>edit|copy)} => sub {
 
 TUWF::get qr{/$RE{vid}/add}, sub {
     return tuwf->resDenied if !can_edit r => undef;
-    my $v = tuwf->dbRowi('SELECT id, title, original FROM vn WHERE id =', \tuwf->capture('id'));
+    my $v = tuwf->dbRowi('
+        SELECT v.id, v.title AS displaytitle, vo.title, vo.latin
+          FROM vnt v
+          JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang
+         WHERE v.id =', \tuwf->capture('id')
+    );
     return tuwf->resNotFound if !$v->{id};
 
     my $delrel = tuwf->dbAlli('SELECT r.id, r.title, r.original FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE r.hidden AND rv.vid =', \$v->{id}, 'ORDER BY id');
@@ -93,16 +98,16 @@ TUWF::get qr{/$RE{vid}/add}, sub {
 
     my $e = {
         elm_empty($FORM_OUT)->%*,
-        title    => $v->{title},
-        original => $v->{original},
-        vn       => [{vid => $v->{id}, title => $v->{title}, rtype => 'complete'}],
+        title    => $v->{latin}//$v->{title},
+        original => $v->{latin} ? $v->{title} : '',
+        vn       => [{vid => $v->{id}, title => $v->{displaytitle}, rtype => 'complete'}],
         official => 1,
     };
     $e->{authmod} = auth->permDbmod;
 
-    framework_ title => "Add release to $v->{title}",
+    framework_ title => "Add release to $v->{displaytitle}",
     sub {
-        editmsg_ r => undef, "Add release to $v->{title}";
+        editmsg_ r => undef, "Add release to $v->{displaytitle}";
 
         div_ class => 'mainbox', sub {
             h1_ 'Deleted releases';
diff --git a/lib/VNWeb/Releases/Page.pm b/lib/VNWeb/Releases/Page.pm
index 5078376a..0cf2e4a9 100644
--- a/lib/VNWeb/Releases/Page.pm
+++ b/lib/VNWeb/Releases/Page.pm
@@ -7,7 +7,7 @@ sub enrich_item {
     my($r) = @_;
 
     enrich_merge pid => 'SELECT id AS pid, name, original FROM producers WHERE id IN', $r->{producers};
-    enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $r->{vn};
+    enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $r->{vn};
 
     $r->{lang}      = [ sort { ($a->{mtl}?1:0) <=> ($b->{mtl}?1:0) || $a->{lang} cmp $b->{lang} } $r->{lang}->@* ];
     $r->{platforms} = [ sort map $_->{platform}, $r->{platforms}->@* ];
@@ -24,7 +24,7 @@ sub _rev_ {
     revision_ $r, \&enrich_item,
         [ vn         => 'Relations',       fmt => sub {
             abbr_ class => "icons rt$_->{rtype}", title => $_->{rtype}, ' ';
-            a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title};
+            a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title};
             txt_ " ($_->{rtype})" if $_->{rtype} ne 'complete';
         } ],
         [ official   => 'Official',        fmt => 'bool' ],
@@ -66,7 +66,7 @@ sub _infotable_ {
             td_ sub {
                 join_ \&br_, sub {
                     abbr_ class => "icons rt$_->{rtype}", title => $_->{rtype}, ' ';
-                    a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title};
+                    a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title};
                     txt_ " ($_->{rtype})" if $_->{rtype} ne 'complete';
                 }, $r->{vn}->@*
             }
diff --git a/lib/VNWeb/Reviews/Edit.pm b/lib/VNWeb/Reviews/Edit.pm
index 9c76f5fb..befc93b5 100644
--- a/lib/VNWeb/Reviews/Edit.pm
+++ b/lib/VNWeb/Reviews/Edit.pm
@@ -33,7 +33,7 @@ sub releases {
 
 
 TUWF::get qr{/$RE{vid}/addreview}, sub {
-    my $v = tuwf->dbRowi('SELECT id, title FROM vn WHERE NOT hidden AND id =', \tuwf->capture('id'));
+    my $v = tuwf->dbRowi('SELECT id, title FROM vnt WHERE NOT hidden AND id =', \tuwf->capture('id'));
     return tuwf->resNotFound if !$v->{id};
 
     my $id = tuwf->dbVali('SELECT id FROM reviews WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
@@ -58,7 +58,7 @@ TUWF::get qr{/$RE{vid}/addreview}, sub {
 TUWF::get qr{/$RE{wid}/edit}, sub {
     my $e = tuwf->dbRowi(
         'SELECT r.id, r.uid AS user_id, r.vid, r.rid, r.isfull, r.modnote, r.text, r.spoiler, r.locked, v.title AS vntitle
-          FROM reviews r JOIN vn v ON v.id = r.vid WHERE r.id =', \tuwf->capture('id')
+          FROM reviews r JOIN vnt v ON v.id = r.vid WHERE r.id =', \tuwf->capture('id')
     );
     return tuwf->resNotFound if !$e->{id};
     return tuwf->resDenied if !can_edit w => $e;
diff --git a/lib/VNWeb/Reviews/List.pm b/lib/VNWeb/Reviews/List.pm
index 5d33e134..33b2e0ac 100644
--- a/lib/VNWeb/Reviews/List.pm
+++ b/lib/VNWeb/Reviews/List.pm
@@ -62,7 +62,7 @@ TUWF::get qr{/w}, sub {
              , ', sql_user(), ',', sql_totime('w.date'), 'as date
              , ', sql_user('wpu','lu_'), ',', sql_totime('wp.date'), 'as ldate
           FROM reviews w
-          JOIN vn v ON v.id = w.vid
+          JOIN vnt v ON v.id = w.vid
           LEFT JOIN users u ON u.id = w.uid
           LEFT JOIN reviews_posts wp ON w.id = wp.id AND w.c_lastnum = wp.num
           LEFT JOIN users wpu ON wpu.id = wp.uid
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index 4a270553..16bb0f56 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -97,7 +97,7 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
               , v.title, rel.title AS rtitle, rel.original AS roriginal, relv.rtype, rv.vote AS my, COALESCE(rv.overrule,false) AS overrule
               , ', sql_user(), ',', sql_totime('r.date'), 'AS date,', sql_totime('r.lastmod'), 'AS lastmod
            FROM reviews r
-           JOIN vn v ON v.id = r.vid
+           JOIN vnt v ON v.id = r.vid
            LEFT JOIN releases rel ON rel.id = r.rid
            LEFT JOIN releases_vn relv ON relv.id = r.rid AND relv.vid = r.vid
            LEFT JOIN users u ON u.id = r.uid
diff --git a/lib/VNWeb/Staff/Page.pm b/lib/VNWeb/Staff/Page.pm
index 81746048..8d143fcc 100644
--- a/lib/VNWeb/Staff/Page.pm
+++ b/lib/VNWeb/Staff/Page.pm
@@ -74,9 +74,9 @@ sub _roles_ {
     my %alias = map +($_->{aid}, $_), $s->{alias}->@*;
 
     my $roles = tuwf->dbAlli(q{
-        SELECT v.id, vs.aid, vs.role, vs.note, v.c_released, v.title, v.original
+        SELECT v.id, vs.aid, vs.role, vs.note, v.c_released, v.title, v.alttitle
           FROM vn_staff vs
-          JOIN vn v ON v.id = vs.id
+          JOIN vnt v ON v.id = vs.id
          WHERE vs.aid IN}, [ keys %alias ], q{
            AND NOT v.hidden
          ORDER BY v.c_released ASC, v.title ASC, vs.role ASC
@@ -100,7 +100,7 @@ sub _roles_ {
                 my($v, $a) = ($_, $alias{$_->{aid}});
                 td_ class => 'tc_ulist', sub { ulists_widget_ $v if !$vns{$v->{id}}++ } if auth;
                 td_ class => 'tc1', sub {
-                    a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 60;
+                    a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 60;
                 };
                 td_ class => 'tc2', sub { rdate_ $v->{c_released} };
                 td_ class => 'tc3', $CREDIT_TYPE{$v->{role}};
@@ -117,10 +117,10 @@ sub _cast_ {
     my %alias = map +($_->{aid}, $_), $s->{alias}->@*;
 
     my $cast = tuwf->dbAlli(q{
-        SELECT vs.aid, v.id, v.c_released, v.title, v.original, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note,
+        SELECT vs.aid, v.id, v.c_released, v.title, v.alttitle, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note,
                (SELECT MIN(cv.spoil) FROM chars_vns cv WHERE cv.id = c.id AND cv.vid = v.id) AS spoil
           FROM vn_seiyuu vs
-          JOIN vn v ON v.id = vs.id
+          JOIN vnt v ON v.id = vs.id
           JOIN chars c ON c.id = vs.cid
          WHERE vs.aid IN}, [ keys %alias ], q{
            AND NOT v.hidden
@@ -156,7 +156,7 @@ sub _cast_ {
                 my($v, $a) = ($_, $alias{$_->{aid}});
                 td_ class => 'tc_ulist', sub { ulists_widget_ $v if !$vns{$v->{id}}++ } if auth;
                 td_ class => 'tc1', sub {
-                    a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 60;
+                    a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 60;
                 };
                 td_ class => 'tc2', sub { rdate_ $v->{c_released} };
                 td_ class => 'tc3', sub {
diff --git a/lib/VNWeb/TT/TagLinks.pm b/lib/VNWeb/TT/TagLinks.pm
index 874b3cf9..d953a96e 100644
--- a/lib/VNWeb/TT/TagLinks.pm
+++ b/lib/VNWeb/TT/TagLinks.pm
@@ -69,7 +69,7 @@ TUWF::get qr{/g/links}, sub {
     my($lst, $np) = tuwf->dbPagei({ page => $opt->{p}, results => 50 }, '
         SELECT tv.vid, tv.uid, tv.tag, tv.vote, tv.spoiler,', sql_totime('tv.date'), 'as date, tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) AS ignore, tv.notes, v.title,', sql_user(), ', t.name
           FROM tags_vn tv
-          JOIN vn v ON v.id = tv.vid
+          JOIN vnt v ON v.id = tv.vid
           LEFT JOIN users u ON u.id = tv.uid
           JOIN tags t ON t.id = tv.tag
          WHERE', $where, '
@@ -98,7 +98,7 @@ TUWF::get qr{/g/links}, sub {
                     li_ sub {
                         txt_ '['; a_ href => url(v=>undef, p=>undef), 'remove'; txt_ '] ';
                         txt_ 'Visual novel'; txt_ ' ';
-                        a_ href => "/$opt->{v}", tuwf->dbVali('SELECT title FROM vn WHERE id=', \$opt->{v})||'Unknown VN';
+                        a_ href => "/$opt->{v}", tuwf->dbVali('SELECT title FROM vnt WHERE id=', \$opt->{v})||'Unknown VN';
                     } if defined $opt->{v};
                 }
             }
diff --git a/lib/VNWeb/TT/TagPage.pm b/lib/VNWeb/TT/TagPage.pm
index 0a5fa903..7863ac12 100644
--- a/lib/VNWeb/TT/TagPage.pm
+++ b/lib/VNWeb/TT/TagPage.pm
@@ -102,9 +102,9 @@ sub vns_ {
     db_maytimeout {
         $count = tuwf->dbVali('SELECT count(*) FROM vn v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where);
         $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
-            SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
+            SELECT tvi.rating AS tagscore, v.id, v.title, v.alttitle, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
                  , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
-              FROM vn v
+              FROM vnt v
               JOIN tags_vn_inherit tvi ON tvi.vid = v.id
              WHERE', $where, '
              ORDER BY', $opt->{s}->sql_order(),
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm
index ab2839e0..ceaf6eb5 100644
--- a/lib/VNWeb/ULists/Elm.pm
+++ b/lib/VNWeb/ULists/Elm.pm
@@ -226,7 +226,7 @@ our $WIDGET = form_compile out => $VNWeb::Elm::apis{UListWidget}[0]{keys};
 elm_api UListWidget => $WIDGET, { uid => { vndbid => 'u' }, vid => { vndbid => 'v' } }, sub {
     my($data) = @_;
     return elm_Unauth if !ulists_own $data->{uid};
-    my $v = tuwf->dbRowi('SELECT id, title, c_released FROM vn WHERE id =', \$data->{vid});
+    my $v = tuwf->dbRowi('SELECT id, title, c_released FROM vnt WHERE id =', \$data->{vid});
     return elm_Invalid if !defined $v->{title};
     elm_UListWidget ulists_widget_full_data $v, $data->{uid};
 };
diff --git a/lib/VNWeb/ULists/Export.pm b/lib/VNWeb/ULists/Export.pm
index 655bbd54..50ab3b9e 100644
--- a/lib/VNWeb/ULists/Export.pm
+++ b/lib/VNWeb/ULists/Export.pm
@@ -15,17 +15,21 @@ sub data {
     # We'd like ISO7601/RFC3339 timestamps in UTC with accuracy to the second.
     my sub tz { sql 'to_char(', $_[0], ' at time zone \'utc\',', \'YYYY-MM-DD"T"HH24:MM:SS"Z"', ') as', $_[1] }
 
+    # XXX: This keeps the old "title"/"original" fields for compatibility, but
+    # should the export take user title preferences into account instead? Or
+    # export all known titles?
     my $d = {
         'export-date' => tuwf->dbVali(select => tz('NOW()', 'now')),
         user   => tuwf->dbRowi('SELECT id, username as name FROM users WHERE id =', \$uid),
         labels => tuwf->dbAlli('SELECT id, label, private FROM ulist_labels WHERE uid =', \$uid, 'ORDER BY id'),
         vns    => tuwf->dbAlli('
-            SELECT v.id, v.title, v.original, uv.vote, uv.started, uv.finished, uv.notes
+            SELECT v.id, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN \'\' ELSE vo.title END AS original, uv.vote, uv.started, uv.finished, uv.notes
                  , ', sql_comma(tz('uv.added', 'added'), tz('uv.lastmod', 'lastmod'), tz('uv.vote_date', 'vote_date')), '
               FROM ulist_vns uv
               JOIN vn v ON v.id = uv.vid
+              JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang
              WHERE uv.uid =', \$uid, '
-             ORDER BY v.title')
+             ORDER BY title')
     };
     enrich labels => id => vid => sub { sql '
         SELECT uvl.vid, ul.id, ul.label, ul.private
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index 0df4ac41..62040de0 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -219,7 +219,7 @@ sub vn_ {
         } if $opt->{s}->vis('label');
 
         td_ class => 'tc_title', sub {
-            a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 70;
+            a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 70;
             b_ class => 'grayedout', id => 'ulist_notes_'.$v->{id}, $v->{notes} if $v->{notes} || $own;
         };
 
@@ -272,15 +272,15 @@ sub listing_ {
         $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : ();
 
-    my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vn v ON v.id = uv.vid WHERE', $where);
+    my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vnt v ON v.id = uv.vid WHERE', $where);
 
     my $lst = tuwf->dbPagei({ page => $opt->{p}, results => $opt->{s}->results },
-        'SELECT v.id, v.title, v.original, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released
+        'SELECT v.id, v.title, v.alttitle, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released
               ,', sql_totime('uv.added'), ' as added
               ,', sql_totime('uv.lastmod'), ' as lastmod
               ,', sql_totime('uv.vote_date'), ' as vote_date
            FROM ulist_vns uv
-           JOIN vn v ON v.id = uv.vid
+           JOIN vnt v ON v.id = uv.vid
           WHERE', $where, '
           ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.title'
     );
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index 803fff65..43d4dd8e 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -142,9 +142,9 @@ sub _votestats_ {
     };
 
     my $recent = tuwf->dbAlli('
-        SELECT vn.id, vn.title, vn.original, uv.vote,', sql_totime('uv.vote_date'), 'AS date
+        SELECT v.id, v.title, v.alttitle, uv.vote,', sql_totime('uv.vote_date'), 'AS date
           FROM ulist_vns uv
-          JOIN vn ON vn.id = uv.vid
+          JOIN vnt v ON v.id = uv.vid
          WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id},
           $own ? () : (
               'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)'
@@ -159,7 +159,7 @@ sub _votestats_ {
         } } };
         tr_ sub {
             my $v = $_;
-            td_ sub { a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 30 };
+            td_ sub { a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 30 };
             td_ fmtvote $v->{vote};
             td_ fmtdate $v->{date};
         } for @$recent;
diff --git a/lib/VNWeb/VN/Edit.pm b/lib/VNWeb/VN/Edit.pm
index 9decd145..ee14a789 100644
--- a/lib/VNWeb/VN/Edit.pm
+++ b/lib/VNWeb/VN/Edit.pm
@@ -7,8 +7,12 @@ use VNWeb::Releases::Lib;
 
 my $FORM = {
     id         => { required => 0, vndbid => 'v' },
-    title      => { maxlength => 250 },
-    original   => { required => 0, default => '', maxlength => 250 },
+    titles     => { sort_keys => 'lang', aoh => {
+        lang     => { enum => \%LANGUAGE },
+        title    => { maxlength => 250 },
+        latin    => { required => 0, default => undef, maxlength => 250 },
+        official => { anybool => 1 },
+    } },
     alias      => { required => 0, default => '', maxlength => 500 },
     desc       => { required => 0, default => '', maxlength => 10240 },
     olang      => { enum => \%LANGUAGE, default => 'ja' },
@@ -20,7 +24,6 @@ my $FORM = {
         relation => { enum => \%VN_RELATION },
         official => { anybool => 1 },
         title    => { _when => 'out' },
-        original => { _when => 'out', required => 0, default => '' },
     } },
     anime      => { sort_keys => 'aid', aoh => {
         aid      => { id => 1 },
@@ -76,6 +79,7 @@ TUWF::get qr{/$RE{vrev}/edit} => sub {
     $e->{authmod} = auth->permDbmod;
     $e->{editsum} = $e->{chrev} == $e->{maxrev} ? '' : "Reverted to revision $e->{id}.$e->{chrev}";
 
+    $e->{titles} = [ sort { $a->{lang} cmp $b->{lang} } $e->{titles}->@* ];
     if($e->{image}) {
         $e->{image_info} = { id => $e->{image} };
         enrich_image 0, [$e->{image_info}];
@@ -85,7 +89,7 @@ TUWF::get qr{/$RE{vrev}/edit} => sub {
     $_->{info} = {id=>$_->{scr}} for $e->{screenshots}->@*;
     enrich_image 0, [map $_->{info}, $e->{screenshots}->@*];
 
-    enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $e->{relations};
+    enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $e->{relations};
     enrich_merge aid => 'SELECT id AS aid, title_romaji AS title, COALESCE(title_kanji, \'\') AS original FROM anime WHERE id IN', $e->{anime};
 
     enrich_merge aid => 'SELECT id, aid, name, original FROM staff_alias WHERE aid IN', $e->{staff}, $e->{seiyuu};
@@ -134,6 +138,7 @@ elm_api VNEdit => $FORM_OUT, $FORM_IN, sub {
     }
     $data->{desc} = bb_subst_links $data->{desc};
     $data->{alias} =~ s/\n\n+/\n/;
+    die "No title in original language" if !grep $_->{lang} eq $data->{olang}, $data->{titles}->@*;
 
     validate_dbid 'SELECT id FROM anime WHERE id IN', map $_->{aid}, $data->{anime}->@*;
     validate_dbid 'SELECT id FROM images WHERE id IN', $data->{image} if $data->{image};
diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm
index 0f6ca5d4..0a018b4d 100644
--- a/lib/VNWeb/VN/Elm.pm
+++ b/lib/VNWeb/VN/Elm.pm
@@ -11,16 +11,16 @@ elm_api VN => undef, {
     die "No query" if !@q;
 
     elm_VNResult tuwf->dbPagei({ results => $data->{hidden}?50:15, page => 1 },
-        'SELECT v.id, v.title, v.original, v.hidden
+        'SELECT v.id, v.title, v.alttitle, v.hidden
            FROM (',
             sql_join('UNION ALL', map +(
-                /^$RE{vid}$/ ? sql('SELECT 1, id FROM vn WHERE id =', \"$+{id}") : (),
-                sql('SELECT 1+substr_score(lower(title),', \sql_like($_), '), id FROM vn WHERE c_search LIKE ALL (search_query(', \"$_", '))'),
+                /^$RE{vid}$/ ? sql('SELECT 1, id FROM vnt WHERE id =', \"$+{id}") : (),
+                sql('SELECT 1+substr_score(lower(title),', \sql_like($_), '), id FROM vnt WHERE c_search LIKE ALL (search_query(', \"$_", '))'),
             ), @q),
             ') x(prio, id)
-           JOIN vn v ON v.id = x.id
+           JOIN vnt v ON v.id = x.id
           WHERE', sql_and($data->{hidden} ? () : 'NOT v.hidden'), '
-          GROUP BY v.id, v.title, v.original, v.hidden
+          GROUP BY v.id, v.title, v.alttitle, v.hidden
           ORDER BY MIN(x.prio), v.title
     ');
 };
diff --git a/lib/VNWeb/VN/Graph.pm b/lib/VNWeb/VN/Graph.pm
index a9227108..940c3a39 100644
--- a/lib/VNWeb/VN/Graph.pm
+++ b/lib/VNWeb/VN/Graph.pm
@@ -8,7 +8,7 @@ TUWF::get qr{/$RE{vid}/rg}, sub {
     my $id = tuwf->capture(1);
     my $num = tuwf->validate(get => num => { uint => 1, onerror => 15 })->data;
     my $unoff = tuwf->validate(get => unoff => { default => 1, anybool => 1 })->data;
-    my $v = tuwf->dbRowi('SELECT id, title, original, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \$id);
+    my $v = tuwf->dbRowi('SELECT id, title, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \$id);
 
     my $has = tuwf->dbRowi('SELECT bool_or(official) AS official, bool_or(not official) AS unofficial FROM vn_relations WHERE id =', \$id, 'GROUP BY id');
     $unoff = 1 if !$has->{official};
@@ -27,7 +27,7 @@ TUWF::get qr{/$RE{vid}/rg}, sub {
 
     # Fetch the nodes
     my $nodes = gen_nodes $id, $rel, $num;
-    enrich_merge id => "SELECT id, title, c_released, array_to_string(c_languages, '/') AS lang FROM vn WHERE id IN", values %$nodes;
+    enrich_merge id => "SELECT id, title, c_released, array_to_string(c_languages, '/') AS lang FROM vnt WHERE id IN", values %$nodes;
 
     my $total_nodes = keys { map +($_->{id0},1), @$rel }->%*;
     my $visible_nodes = keys %$nodes;
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index f1a2635e..338e5edb 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -46,7 +46,7 @@ sub listing_ {
                 td_ class => 'tc1', fmtdate $_->{date};
                 td_ class => 'tc2', sub { user_ $_ } if $mode ne 'u';
                 td_ class => 'tc2', sub {
-                    a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title};
+                    a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title};
                 } if $mode ne 'v';
                 td_ class => 'tc3'.($_->{ignore}?' grayedout':''), sub { vnlength_ $_->{length} };
                 td_ class => 'tc4'.($_->{ignore}?' grayedout':''), ['Slow','Normal','Fast']->[$_->{speed}];
@@ -129,10 +129,10 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
       'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, '
             , sql_totime('l.date'), 'AS date, l.ignore OR u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore',
               $mode ne 'u' ? (', ', sql_user()) : (),
-              $mode ne 'v' ? ', v.title, v.original' : (), '
+              $mode ne 'v' ? ', v.title, v.alttitle' : (), '
          FROM vn_length_votes l
          LEFT JOIN users u ON u.id = l.uid',
-         $mode ne 'v' ? 'JOIN vn v ON v.id = l.vid' : (),
+         $mode ne 'v' ? 'JOIN vnt v ON v.id = l.vid' : (),
        'WHERE', $where,
        'ORDER BY', $opt->{s}->sql_order(),
     );
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 40916777..29a76bd0 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -107,7 +107,7 @@ sub listing_ {
             tr_ sub {
                 td_ class => 'tc_score', sub { tagscore_ $_->{tagscore} } if $tagscore;
                 td_ class => 'tc_ulist', sub { ulists_widget_ $_ } if auth;
-                td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} };
+                td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} };
                 td_ class => 'tc_dev',   sub {
                     join_ ' & ', sub {
                         a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name};
@@ -138,7 +138,7 @@ sub listing_ {
             a_ href => $url, title => $title, $label if $canlink;
             span_ $label if !$canlink;
         }
-        lnk_ "/$_->{id}", $_->{original}||$_->{title}, $_->{title};
+        lnk_ "/$_->{id}", $_->{alttitle}||$_->{title}, $_->{title};
         br_;
         join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@*;
         join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@*;
@@ -200,7 +200,7 @@ sub listing_ {
     div_ class => 'mainbox vngrid', sub {
         div_ !$_->{image} || image_hidden($_->{image}) ? (class => 'noimage') : (style => 'background-image: url("'.imgurl($_->{image}{id}).'")'), sub {
             ulists_widget_ $_;
-            a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, sub { infoblock_ 0 };
+            a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, sub { infoblock_ 0 };
         } for @$list;
     } if $opt->{s}->grid;
 
@@ -269,12 +269,12 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where);
+        $count = tuwf->dbVali('SELECT count(*) FROM vnt v WHERE', $where);
         $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
-            SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
+            SELECT v.id, v.title, v.alttitle, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
                  , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang',
                    $opt->{s}->vis('length') ? ', v.length, v.c_length, v.c_lengthnum' : (), '
-              FROM vn v
+              FROM vnt v
              WHERE', $where, '
              ORDER BY', $opt->{s}->sql_order(),
         ) : [];
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 4cfb86dd..0551d237 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -11,8 +11,8 @@ use VNDB::Func 'fmtrating';
 # Also used by Chars::VNTab & Reviews::VNTab
 sub enrich_vn {
     my($v, $revonly) = @_;
-    enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum FROM vn WHERE id IN', $v;
-    enrich_merge vid => 'SELECT id AS vid, title, original, c_released FROM vn WHERE id IN', $v->{relations};
+    enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum, title, alttitle FROM vnt WHERE id IN', $v;
+    enrich_merge vid => 'SELECT id AS vid, title, alttitle, c_released FROM vnt WHERE id IN', $v->{relations};
     enrich_merge aid => 'SELECT id AS aid, title_romaji, title_kanji, year, type, ann_id, lastfetch FROM anime WHERE id IN', $v->{anime};
     enrich_extlinks v => $v;
     enrich_image_obj image => $v;
@@ -87,8 +87,15 @@ sub canvote {
 sub rev_ {
     my($v) = @_;
     revision_ $v, \&enrich_item,
-        [ title       => 'Title (romaji)' ],
-        [ original    => 'Original title' ],
+        [ titles      => 'Title(s)',      fmt => sub {
+            abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, '';
+            txt_ $_->{title};
+            if($_->{latin}) {
+                b_ class => 'grayedout', ' / ';
+                txt_ $_->{latin};
+            }
+            b_ class => 'grayedout', ' (unofficial)' if !$_->{official};
+        }],
         [ alias       => 'Alias'          ],
         [ olang       => 'Original language', fmt => \%LANGUAGE ],
         [ desc        => 'Description'    ],
@@ -108,7 +115,7 @@ sub rev_ {
         }],
         [ relations   => 'Relations',     fmt => sub {
             txt_ sprintf '[%s] %s: ', $_->{official} ? 'official' : 'unofficial', $VN_RELATION{$_->{relation}}{txt};
-            a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title};
+            a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title};
         }],
         [ anime       => 'Anime',         fmt => sub { a_ href => "https://anidb.net/anime/$_->{aid}", "a$_->{aid}" }],
         [ screenshots => 'Screenshots',   fmt => sub {
@@ -145,7 +152,7 @@ sub infobox_relations_ {
                 dd_ sub {
                     join_ \&br_, sub {
                         b_ class => 'grayedout', '[unofficial] ' if !$_->{official};
-                        a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 40;
+                        a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, shorten $_->{title}, 40;
                     }, $rel{$_}->@*;
                 }
             }
@@ -366,27 +373,37 @@ sub infobox_ {
     div_ class => 'mainbox', sub {
         itemmsg_ $v;
         h1_ $v->{title};
-        h2_ class => 'alttitle', lang_attr($v->{olang}), $v->{original} if $v->{original};
+        h2_ class => 'alttitle', lang_attr($v->{olang}), $v->{alttitle} if $v->{alttitle};
 
         div_ class => 'vndetails', sub {
             div_ class => 'vnimg', sub { image_ $v->{image}, alt => $v->{title}; };
 
             table_ class => 'stripe', sub {
                 tr_ sub {
-                    td_ class => 'key', 'Title';
-                    td_ class => 'title', sub {
-                        txt_ $v->{title};
+                    td_ class => 'key', sub{
+                        txt_ $v->{titles}->@* > 1 ? 'Titles' : 'Title';
                         debug_ $v;
-                        abbr_ class => "icons lang $v->{olang}", title => "Original language: $LANGUAGE{$v->{olang}}", '';
+                    };
+                    td_ class => 'title', sub {
+                        table_ sub {
+                            tr_ class => $_->{official}?undef:'grayedout', sub {
+                                td_ sub {
+                                    abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, '';
+                                };
+                                td_ sub {
+                                    span_ lang_attr($_->{lang}), $_->{title};
+                                    if($_->{latin}) {
+                                        br_;
+                                        txt_ $_->{latin};
+                                    }
+                                }
+                            } for (grep $_->{lang} eq $v->{olang}, $v->{titles}->@*),
+                                grep $_->{lang} ne $v->{olang}, sort { $a->{official} cmp $b->{official} || $a->{lang} cmp $b->{lang} } $v->{titles}->@*
+                        };
                     };
                 };
 
                 tr_ sub {
-                    td_ 'Original title';
-                    td_ lang_attr($v->{olang}), $v->{original};
-                } if $v->{original};
-
-                tr_ sub {
                     td_ 'Aliases';
                     td_ $v->{alias} =~ s/\n/, /gr;
                 } if $v->{alias};
diff --git a/lib/VNWeb/VN/Tagmod.pm b/lib/VNWeb/VN/Tagmod.pm
index 7a35846d..767f9ab5 100644
--- a/lib/VNWeb/VN/Tagmod.pm
+++ b/lib/VNWeb/VN/Tagmod.pm
@@ -72,7 +72,7 @@ elm_api Tagmod => $FORM_OUT, $FORM_IN, sub {
 
 
 TUWF::get qr{/$RE{vid}/tagmod}, sub {
-    my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \tuwf->capture('id'));
+    my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \tuwf->capture('id'));
     return tuwf->resNotFound if !$v->{id} || (!auth->permDbmod && $v->{entry_hidden});
     return tuwf->resDenied if !can_tag;
 
diff --git a/lib/VNWeb/VN/Votes.pm b/lib/VNWeb/VN/Votes.pm
index a5bce3f7..b70ca342 100644
--- a/lib/VNWeb/VN/Votes.pm
+++ b/lib/VNWeb/VN/Votes.pm
@@ -31,7 +31,7 @@ sub listing_ {
 
 TUWF::get qr{/$RE{vid}/votes}, sub {
     my $id = tuwf->capture('id');
-    my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \$id);
+    my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \$id);
     return tuwf->resNotFound if !$v->{id} || $v->{hidden};
 
     my $opt = tuwf->validate(get =>
diff --git a/sql/func.sql b/sql/func.sql
index e0468221..c8dbc864 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -82,8 +82,8 @@ $$ LANGUAGE SQL IMMUTABLE;
 CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
   SELECT coalesce(string_agg(t, ' '), '') FROM (
     SELECT t FROM (
-                SELECT search_norm_term(title) FROM vn WHERE id = vnid
-      UNION ALL SELECT search_norm_term(original) FROM vn WHERE vnid = id
+                SELECT search_norm_term(title) FROM vn_titles WHERE id = vnid
+      UNION ALL SELECT search_norm_term(latin) FROM vn_titles WHERE id = vnid
       UNION ALL SELECT search_norm_term(a) FROM vn, regexp_split_to_table(alias, E'\n') a(a) WHERE vnid = id
       -- Remove the various editions/version strings from release titles,
       -- this reduces the index size and makes VN search more relevant.
@@ -464,11 +464,12 @@ $$ LANGUAGE SQL;
 
 -- Returns generic information for almost every supported vndbid + num.
 -- Not currently supported: ch#, cv#, sf#
+-- XXX: user title preferences (through the 'vnt' VIEW) are not used for explicit revisions.
 --
 -- Returned fields:
 --   * title    - Main/romanized title.
 --                For users this is their username, not displayname.
---   * original - Original title (if applicable). Used in edit histories
+--   * original - Original/alternative title (if applicable). Used in edit histories
 --   * uid      - User who created/initiated this entry. Used in notification listings and reports
 --   * hidden   - Whether this entry is 'hidden' or private. Used for the reporting function & framework_ object.
 --                For edits this info comes from the revision itself, not the final entry.
@@ -480,7 +481,9 @@ CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title tex
 BEGIN
   -- x#
   IF $2 IS NULL THEN CASE vndbid_type($1)
-    WHEN 'v' THEN RETURN QUERY SELECT v.title   ::text, v.original::text,  NULL::vndbid,  v.hidden, v.locked FROM vn v        WHERE v.id = $1;
+    --WHEN 'v' THEN RETURN QUERY SELECT COALESCE(vo.latin, vo.title), CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END, NULL::vndbid, v.hidden, v.locked
+    --                      FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang WHERE v.id = $1;
+    WHEN 'v' THEN RETURN QUERY SELECT v.title   ::text, v.alttitle::text,  NULL::vndbid,  v.hidden, v.locked FROM vnt v       WHERE v.id = $1;
     WHEN 'r' THEN RETURN QUERY SELECT r.title   ::text, r.original::text,  NULL::vndbid,  r.hidden, r.locked FROM releases r  WHERE r.id = $1;
     WHEN 'p' THEN RETURN QUERY SELECT p.name    ::text, p.original::text,  NULL::vndbid,  p.hidden, p.locked FROM producers p WHERE p.id = $1;
     WHEN 'c' THEN RETURN QUERY SELECT c.name    ::text, c.original::text,  NULL::vndbid,  c.hidden, c.locked FROM chars c     WHERE c.id = $1;
@@ -489,12 +492,13 @@ BEGIN
     WHEN 'i' THEN RETURN QUERY SELECT i.name    ::text, NULL,              NULL::vndbid,  i.hidden, i.locked FROM traits i    WHERE i.id = $1;
     WHEN 's' THEN RETURN QUERY SELECT sa.name   ::text, sa.original::text, NULL::vndbid,  s.hidden, s.locked FROM staff s   JOIN staff_alias sa ON sa.aid = s.aid WHERE s.id = $1;
     WHEN 't' THEN RETURN QUERY SELECT t.title   ::text, NULL,              NULL::vndbid,  t.hidden OR t.private, t.locked FROM threads t WHERE t.id = $1;
-    WHEN 'w' THEN RETURN QUERY SELECT v.title   ::text, v.original::text,  w.uid, w.c_flagged, w.locked FROM reviews w JOIN vn v ON v.id = w.vid WHERE w.id = $1;
+    WHEN 'w' THEN RETURN QUERY SELECT v.title   ::text, v.alttitle::text,  w.uid, w.c_flagged, w.locked FROM reviews w JOIN vnt v ON v.id = w.vid WHERE w.id = $1;
     WHEN 'u' THEN RETURN QUERY SELECT u.username::text, NULL,              NULL::vndbid,  FALSE,  FALSE  FROM users u WHERE u.id = $1;
   END CASE;
   -- x#.#
   ELSE CASE vndbid_type($1)
-    WHEN 'v' THEN RETURN QUERY SELECT v.title::text, v.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN vn_hist v        ON h.id = v.chid WHERE h.itemid = $1 AND h.rev = $2;
+    WHEN 'v' THEN RETURN QUERY SELECT COALESCE(vo.latin, vo.title), CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END, h.requester, h.ihid, h.ilock
+                          FROM changes h JOIN vn_hist v ON h.id = v.chid JOIN vn_titles_hist vo ON h.id = vo.chid AND vo.lang = v.olang WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'r' THEN RETURN QUERY SELECT r.title::text, r.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN releases_hist r  ON h.id = r.chid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'p' THEN RETURN QUERY SELECT p.name ::text, p.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'c' THEN RETURN QUERY SELECT c.name ::text, c.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN chars_hist c     ON h.id = c.chid WHERE h.itemid = $1 AND h.rev = $2;
@@ -503,7 +507,7 @@ BEGIN
     WHEN 'i' THEN RETURN QUERY SELECT i.name ::text, NULL,              h.requester, h.ihid, h.ilock FROM changes h JOIN traits_hist i    ON h.id = i.chid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 's' THEN RETURN QUERY SELECT sa.name::text, sa.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN staff_hist s     ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.aid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 't' THEN RETURN QUERY SELECT t.title::text, NULL,              tp.uid, t.hidden OR t.private OR tp.hidden IS NOT NULL, t.locked FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE t.id = $1 AND tp.num = $2;
-    WHEN 'w' THEN RETURN QUERY SELECT v.title::text, v.original::text,  wp.uid, w.c_flagged OR wp.hidden IS NOT NULL, w.locked FROM reviews w JOIN vn v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE w.id = $1 AND wp.num = $2;
+    WHEN 'w' THEN RETURN QUERY SELECT v.title::text, v.alttitle::text,  wp.uid, w.c_flagged OR wp.hidden IS NOT NULL, w.locked FROM reviews w JOIN vnt v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE w.id = $1 AND wp.num = $2;
   END CASE;
   END IF;
 END;
@@ -548,17 +552,9 @@ DECLARE
 BEGIN
   SELECT id INTO xoldchid FROM changes WHERE itemid = nitemid AND rev = nrev-1;
 
-  -- Update c_search when
-  -- 1. A new VN entry is created
-  -- 2. The vn title/original/alias has changed
+  -- Update c_search
   IF vndbid_type(nitemid) = 'v' THEN
-    IF -- 1.
-       xoldchid IS NULL OR
-       -- 2.
-       EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = nchid)
-    THEN
-      UPDATE vn SET c_search = search_gen_vn(id) WHERE id = nitemid;
-    END IF;
+    UPDATE vn SET c_search = search_gen_vn(id) WHERE id = nitemid;
   END IF;
 
   -- Update vn.c_search when
diff --git a/sql/perms.sql b/sql/perms.sql
index de6da264..d01023f5 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -92,6 +92,9 @@ GRANT SELECT, INSERT,         DELETE ON vn_seiyuu                TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_seiyuu_hist           TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_staff                 TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_staff_hist            TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_titles                TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_titles_hist           TO vndb_site;
+GRANT SELECT                         ON vnt                      TO vndb_site;
 GRANT SELECT, INSERT                 ON wikidata                 TO vndb_site;
 
 
@@ -174,4 +177,7 @@ GRANT SELECT                         ON vn_screenshots_hist      TO vndb_multi;
 GRANT SELECT                         ON vn_seiyuu                TO vndb_multi;
 GRANT SELECT                         ON vn_staff                 TO vndb_multi;
 GRANT SELECT                         ON vn_staff_hist            TO vndb_multi;
+GRANT SELECT                         ON vn_titles                TO vndb_multi;
+GRANT SELECT                         ON vn_titles_hist           TO vndb_multi;
+GRANT SELECT                         ON vnt                      TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE         ON wikidata                 TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index 86d13cb6..f012f520 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1060,8 +1060,6 @@ CREATE TABLE vn ( -- dbentry_type=v
   img_nsfw      boolean NOT NULL DEFAULT FALSE, -- (deprecated)
   locked        boolean NOT NULL DEFAULT FALSE,
   hidden        boolean NOT NULL DEFAULT FALSE,
-  title         varchar(250) NOT NULL DEFAULT '', -- [pub]
-  original      varchar(250) NOT NULL DEFAULT '', -- [pub]
   alias         varchar(500) NOT NULL DEFAULT '', -- [pub]
   l_wp          varchar(150) NOT NULL DEFAULT '', -- (deprecated)
   l_encubed     varchar(100) NOT NULL DEFAULT '', -- (deprecated)
@@ -1084,8 +1082,6 @@ CREATE TABLE vn_hist (
   l_wikidata   integer,
   length       smallint NOT NULL DEFAULT 0,
   img_nsfw     boolean NOT NULL DEFAULT FALSE,
-  title        varchar(250) NOT NULL DEFAULT '',
-  original     varchar(250) NOT NULL DEFAULT '',
   alias        varchar(500) NOT NULL DEFAULT '',
   l_wp         varchar(150) NOT NULL DEFAULT '',
   l_encubed    varchar(100) NOT NULL DEFAULT '',
@@ -1179,6 +1175,26 @@ CREATE TABLE vn_staff_hist (
   PRIMARY KEY (chid, aid, role)
 );
 
+-- vn_titles
+CREATE TABLE vn_titles (
+  id         vndbid NOT NULL, -- [pub]
+  lang       language NOT NULL, -- [pub]
+  title      text NOT NULL, -- [pub]
+  latin      text, -- [pub]
+  official   boolean NOT NULL, -- [pub]
+  PRIMARY KEY(id, lang)
+);
+
+-- vn_titles_hist
+CREATE TABLE vn_titles_hist (
+  chid       integer NOT NULL,
+  lang       language NOT NULL,
+  title      text NOT NULL,
+  latin      text,
+  official   boolean NOT NULL,
+  PRIMARY KEY(chid, lang)
+);
+
 -- vn_length_votes
 CREATE TABLE vn_length_votes (
   id         serial PRIMARY KEY,
@@ -1232,3 +1248,12 @@ CREATE TABLE wikidata (
   playstation_na     text[],    -- [pub] P5944
   playstation_eu     text[]     -- [pub] P5971
 );
+
+
+-- The 'vnt' view is equivalent to the 'vn' table with two additional columns:
+-- 'title' and 'alttitle', which represent the display title and the
+-- alternative (mouse-hover) title. The view defined here displays the
+-- latin/title name of the original language as main title and the title in the
+-- original script as alttitle, but this view can be redefined as a TEMPORARY
+-- VIEW in sessions to override the default behavior.
+CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 6de668e5..83a12264 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -138,9 +138,11 @@ ALTER TABLE users_shadow             ADD CONSTRAINT users_shadow_id_fkey
 ALTER TABLE users_username_hist      ADD CONSTRAINT users_username_hist_id_fkey        FOREIGN KEY (id)        REFERENCES users         (id) ON DELETE CASCADE;
 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                       ADD CONSTRAINT vn_olang_fkey                      FOREIGN KEY (id,olang)  REFERENCES vn_titles     (id,lang)   DEFERRABLE INITIALLY DEFERRED;
 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_hist                  ADD CONSTRAINT vn_hist_olang_fkey                 FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
 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);
 ALTER TABLE vn_anime_hist            ADD CONSTRAINT vn_anime_hist_chid_fkey            FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
@@ -163,5 +165,7 @@ 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_titles                ADD CONSTRAINT vn_titles_id_fkey                  FOREIGN KEY (id)        REFERENCES vn            (id);
+ALTER TABLE vn_titles_hist           ADD CONSTRAINT vn_titles_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_uid_fkey           FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
diff --git a/util/dbdump.pl b/util/dbdump.pl
index f71b9598..5488d6e4 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -107,6 +107,7 @@ my %tables = (
                                 .' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
                                 .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
     vn_staff            => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
+    vn_titles           => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
     vn_length_votes     => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
                            , order => 'vid, uid' },
     wikidata            => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden
diff --git a/util/devdump.pl b/util/devdump.pl
index 2a20544c..9d801912 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -157,7 +157,7 @@ sub copy_entry {
 
     # Visual novels
     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.itemid IN($vids)";
-    copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
+    copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], \@vids;
 
     # VN-related niceties
     copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'};
diff --git a/util/updates/wip-vn-titles.sql b/util/updates/wip-vn-titles.sql
new file mode 100644
index 00000000..b8162ea7
--- /dev/null
+++ b/util/updates/wip-vn-titles.sql
@@ -0,0 +1,41 @@
+BEGIN;
+
+CREATE TABLE vn_titles (
+  id         vndbid NOT NULL,
+  lang       language NOT NULL,
+  title      text NOT NULL,
+  latin      text,
+  official   boolean NOT NULL,
+  PRIMARY KEY(id, lang)
+);
+
+CREATE TABLE vn_titles_hist (
+  chid       integer NOT NULL,
+  lang       language NOT NULL,
+  title      text NOT NULL,
+  latin      text,
+  official   boolean NOT NULL,
+  PRIMARY KEY(chid, lang)
+);
+
+INSERT INTO vn_titles      SELECT id,   olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn;
+INSERT INTO vn_titles_hist SELECT chid, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn_hist;
+
+ALTER TABLE vn_titles                ADD CONSTRAINT vn_titles_id_fkey                  FOREIGN KEY (id)        REFERENCES vn            (id);
+ALTER TABLE vn_titles_hist           ADD CONSTRAINT vn_titles_hist_chid_fkey           FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
+ALTER TABLE vn                       ADD CONSTRAINT vn_olang_fkey                      FOREIGN KEY (id,olang)  REFERENCES vn_titles     (id,lang)   DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_olang_fkey                 FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
+
+-- TODO: actually drop
+ALTER TABLE vn RENAME COLUMN original TO old_original;
+ALTER TABLE vn RENAME COLUMN title TO old_title;
+--ALTER TABLE vn RENAME COLUMN old_original TO original;
+--ALTER TABLE vn RENAME COLUMN old_title TO title;
+--ALTER TABLE vn DROP COLUMN original, DROP COLUMN title;
+
+CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;
+
+COMMIT;
+\i sql/func.sql
+\i sql/editfunc.sql
+\i sql/perms.sql
author	Yorhel <git@yorhel.nl>	2022-01-20 09:02:15 +0100
committer	Yorhel <git@yorhel.nl>	2022-02-08 11:36:07 +0100
commit	727a4d84255f13badffe16c5858fdb036ddcdbce (patch)
tree	6c77f72e2423a7c7425caea6aebc6ba8eaf001f8
parent	9d651b8d138349b3853f23117cc3802fcfe0ba6a (diff)