summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--css/v2.css3
-rw-r--r--elm/ReleaseEdit.elm110
-rw-r--r--elm/UList/Opt.elm2
-rw-r--r--elm/UList/Widget.elm2
-rw-r--r--elm/VNEdit.elm3
-rw-r--r--lib/VNWeb/AdvSearch.pm2
-rw-r--r--lib/VNWeb/Elm.pm2
-rw-r--r--lib/VNWeb/LangPref.pm11
-rw-r--r--lib/VNWeb/Misc/HomePage.pm11
-rw-r--r--lib/VNWeb/Releases/Edit.pm10
-rw-r--r--lib/VNWeb/Releases/Lib.pm24
-rw-r--r--lib/VNWeb/Releases/List.pm4
-rw-r--r--lib/VNWeb/Releases/Page.pm57
-rw-r--r--lib/VNWeb/Releases/VNTab.pm9
-rw-r--r--lib/VNWeb/Reviews/Page.pm8
-rw-r--r--lib/VNWeb/VN/Edit.pm2
-rw-r--r--lib/VNWeb/VN/Length.pm2
-rw-r--r--lib/VNWeb/VN/Page.pm4
-rw-r--r--sql/func.sql32
-rw-r--r--sql/perms.sql8
-rw-r--r--sql/schema.sql51
-rw-r--r--sql/tableattrs.sql6
-rwxr-xr-xutil/dbdump.pl2
-rwxr-xr-xutil/devdump.pl2
24 files changed, 202 insertions, 165 deletions
diff --git a/css/v2.css b/css/v2.css
index d4b6f4d1..40e55fc6 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -732,6 +732,9 @@ div#vntags { margin: 0 30px 0 30px; border-top: 1px solid $bo
/***** Release page *****/
.release table { width: 500px; margin: 0 auto; }
+.release td.titles table { margin-left: 96px }
+.release tr.title td { padding: 0 0px }
+.release tr.title td:first-child { width: 20px }
.release .key { width: 110px; }
.release dt { float: none; font-style: normal; }
.release dd { margin-left: 15px; }
diff --git a/elm/ReleaseEdit.elm b/elm/ReleaseEdit.elm
index aa30a06f..00fe7820 100644
--- a/elm/ReleaseEdit.elm
+++ b/elm/ReleaseEdit.elm
@@ -5,8 +5,11 @@ import Html.Events exposing (..)
import Html.Attributes exposing (..)
import Browser
import Browser.Navigation exposing (load)
+import Browser.Dom as Dom
import Bitwise as B
import Set
+import Task
+import Process
import Lib.Util exposing (..)
import Lib.Html exposing (..)
import Lib.TextPreview as TP
@@ -33,14 +36,13 @@ main = Browser.element
type alias Model =
{ state : Api.State
- , title : String
- , original : String
+ , titles : List GRE.RecvTitles
+ , olang : String
, official : Bool
, patch : Bool
, freeware : Bool
, hasEro : Bool
, doujin : Bool
- , lang : List GRE.RecvLang
, plat : Set.Set String
, platDd : DD.Config Msg
, media : List GRE.RecvMedia
@@ -79,14 +81,13 @@ type alias Model =
init : GRE.Recv -> Model
init d =
{ state = Api.Normal
- , title = d.title
- , original = d.original
+ , titles = d.titles
+ , olang = d.olang
, official = d.official
, patch = d.patch
, freeware = d.freeware
, hasEro = d.has_ero
, doujin = d.doujin
- , lang = d.lang
, plat = Set.fromList <| List.map (\e -> e.platform) d.platforms
, platDd = DD.init "platforms" PlatOpen
, media = List.map (\m -> { m | qty = if m.qty == 0 then 1 else m.qty }) d.media
@@ -128,14 +129,13 @@ encode model =
, editsum = model.editsum.editsum.data
, hidden = model.editsum.hidden
, locked = model.editsum.locked
- , title = model.title
- , original = model.original
+ , titles = model.titles
+ , olang = model.olang
, official = model.official
, patch = model.patch
, freeware = model.freeware
, has_ero = model.hasEro
, doujin = model.doujin
- , lang = model.lang
, platforms = List.map (\l -> {platform=l}) <| Set.toList model.plat
, media = model.media
, gtin = model.gtin
@@ -177,15 +177,18 @@ engineConfig = { wrap = Engine, id = "engine", source = A.engineSource }
type Msg
- = Title String
- | Original String
+ = Noop
+ | TitleAdd String
+ | TitleDel Int
+ | TitleLang Int String
+ | TitleTitle Int String
+ | TitleLatin Int String
+ | TitleMtl Int Bool
+ | TitleMain String
| Official Bool
| Patch Bool
| Freeware Bool
| HasEro Bool
- | Lang Int String
- | LangMtl Int Bool
- | LangDel Int
| Plat String Bool
| PlatOpen Bool
| MediaType Int String
@@ -227,15 +230,21 @@ type Msg
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
case msg of
- Title s -> ({ model | title = s }, Cmd.none)
- Original s -> ({ model | original = s }, Cmd.none)
+ Noop -> (model, Cmd.none)
+ TitleAdd s ->
+ ({ model | titles = model.titles ++ [{ lang = s, title = "", latin = Nothing, mtl = False }], 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)
+ TitleMtl i s -> ({ model | titles = modidx i (\e -> { e | mtl = s }) model.titles }, Cmd.none)
+ TitleMain s -> ({ model | olang = s }, Cmd.none)
+
Official b -> ({ model | official = b }, Cmd.none)
Patch b -> ({ model | patch = b }, Cmd.none)
Freeware b -> ({ model | freeware = b }, Cmd.none)
HasEro b -> ({ model | hasEro = b }, Cmd.none)
- Lang n s -> ({ model | lang = if s /= "" && n == List.length model.lang then model.lang ++ [{lang=s, mtl=False}] else modidx n (\l -> { l | lang = s }) model.lang }, Cmd.none)
- LangMtl n b-> ({ model | lang = modidx n (\l -> { l | mtl = b }) model.lang }, Cmd.none)
- LangDel n -> ({ model | lang = delidx n model.lang }, Cmd.none)
Plat s b -> ({ model | plat = if b then Set.insert s model.plat else Set.remove s model.plat }, Cmd.none)
PlatOpen b -> ({ model | platDd = DD.toggle model.platDd b }, Cmd.none)
MediaType n s -> ({ model | media = if s /= "unk" && n == List.length model.media then model.media ++ [{medium = s, qty = 1}] else modidx n (\m -> { m | medium = s }) model.media }, Cmd.none)
@@ -311,9 +320,8 @@ update msg model =
isValid : Model -> Bool
isValid model = not
- ( model.title == model.original
- || List.isEmpty model.lang
- || hasDuplicates (List.map (\l -> l.lang) model.lang)
+ ( List.any (\e -> e.title /= "" && Just e.title == e.latin) model.titles
+ || List.isEmpty model.titles
|| hasDuplicates (List.map (\m -> (m.medium, m.qty)) model.media)
|| not model.gtinValid
|| List.isEmpty model.vn
@@ -349,24 +357,36 @@ viewAnimation cut na m v =
]
]
+viewTitle : Model -> Int -> GRE.RecvTitles -> Html Msg
+viewTitle model i e = tr []
+ [ td [] [ langIcon e.lang ]
+ , td []
+ [ inputText ("title_"++e.lang) e.title (TitleTitle i) (style "width" "500px" :: placeholder "Title (in the original script)" :: GRE.valTitlesTitle)
+ , if not (e.latin /= Nothing || containsNonLatin e.title) then text "" else span []
+ [ br [] []
+ , inputText "" (Maybe.withDefault "" e.latin) (TitleLatin i) (style "width" "500px" :: placeholder "Romanization" :: GRE.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 e.lang), text " main title" ]
+ ]
+ , br [] []
+ , label [] [ inputCheck "" e.mtl (TitleMtl i), text " Machine translation" ]
+ , if e.lang == model.olang then text "" else span []
+ [ br [] [], inputButton "remove" (TitleDel i) [] ]
+ , br_ 2
+ ]
+ ]
+
viewGen : Model -> Html Msg
viewGen model =
table [ class "formtable" ] <|
- [ formField "title::Title (romaji)"
- [ inputText "title" model.title Title (style "width" "500px" :: GRE.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 ""
- ]
- , formField "original::Original title"
- [ inputText "original" model.original Original (style "width" "500px" :: GRE.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 /= "" && 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 if containsJapanese model.original && not (List.isEmpty model.lang) && not (List.any (\l -> l.lang == "ja" || l.lang == "zh" || l.lang == "zh-Hans" || l.lang == "zh-Hant") model.lang)
- then b [ class "standout" ] [ br [] [], text "Non-Japanese releases should (probably) not have a Japanese original title." ]
- else text ""
+ [ formField "Languages & titles"
+ [ table [] <| List.indexedMap (viewTitle model) model.titles
+ , inputSelect "" "" TitleAdd [] <| ("", "- Add language -") :: List.filter (\(l,_) -> l /= "zh" && not (List.any (\e -> e.lang == l) model.titles)) GT.languages
]
, tr [ class "newpart" ] [ td [] [] ]
@@ -378,22 +398,6 @@ viewGen model =
, formField "Release date" [ D.view model.released False False Released, text " Leave month or day blank if they are unknown." ]
, tr [ class "newpart" ] [ td [ colspan 2 ] [ text "Format" ] ]
- , formField "Language(s)"
- [ table [] <| List.indexedMap (\i l ->
- tr []
- [ td [] [ inputSelect "" l.lang (Lang i) [] <| if l.lang == ""
- then ("", "- Add language -") :: List.filter (\(e,_) -> e /= "zh") GT.languages
- else GT.languages ]
- , td [] [ if l.lang == "" then text "" else label [] [ inputCheck "" l.mtl (LangMtl i), text " machine translation" ] ]
- , td [] [ if l.lang == "" || List.length model.lang == 1 then text "" else inputButton "remove" (LangDel i) [] ]
- ]
- ) <| model.lang ++ [{lang = "", mtl = False}]
- , if hasDuplicates (List.map (\l -> l.lang) model.lang)
- then b [ class "standout" ] [ text "List contains duplicates", br [] [] ]
- else if List.any (\e -> e.lang == "zh") model.lang
- then b [ class "standout" ] [ text "The \"Chinese\" language option should not be used anymore, please indicate whether it is Simplified or Traditional (or both).", br [] [] ]
- else text ""
- ]
, formField "Platform(s)"
[ div [ class "elm_dd_input", style "width" "500px" ] [ DD.view model.platDd Api.Normal
(if Set.isEmpty model.plat
diff --git a/elm/UList/Opt.elm b/elm/UList/Opt.elm
index c1ac70ab..5a0ca91d 100644
--- a/elm/UList/Opt.elm
+++ b/elm/UList/Opt.elm
@@ -187,7 +187,7 @@ view model =
<| List.map platformIcon nfo.platforms
++ List.map langIcon nfo.lang
++ [ releaseTypeIcon nfo.rtype ]
- , td [ class "tco4" ] [ a [ href ("/"++nfo.id), title nfo.original ] [ text nfo.title ] ]
+ , td [ class "tco4" ] [ a [ href ("/"++nfo.id), title nfo.alttitle ] [ text nfo.title ] ]
]
confirm =
diff --git a/elm/UList/Widget.elm b/elm/UList/Widget.elm
index 0b25fd24..d41c83ef 100644
--- a/elm/UList/Widget.elm
+++ b/elm/UList/Widget.elm
@@ -270,7 +270,7 @@ view model =
<| List.map platformIcon nfo.platforms
++ List.map langIcon nfo.lang
++ [ releaseTypeIcon nfo.rtype ]
- , td [ class "tco4" ] [ a [ href ("/"++nfo.id), title nfo.original ] [ text nfo.title ] ]
+ , td [ class "tco4" ] [ a [ href ("/"++nfo.id), title nfo.alttitle ] [ text nfo.title ] ]
]
box () =
diff --git a/elm/VNEdit.elm b/elm/VNEdit.elm
index e2af02f3..1cf1a520 100644
--- a/elm/VNEdit.elm
+++ b/elm/VNEdit.elm
@@ -362,10 +362,11 @@ update msg model =
-- TODO: Fuzzier matching? Exclude stuff like 'x Edition', etc.
+-- TODO: Match on all titles associated with a release, not just the 'title' and 'alttitle'.
relAlias : Model -> Maybe GVE.RecvReleases
relAlias model =
let a = String.toLower model.alias |> String.lines |> List.filter (\l -> l /= "") |> Set.fromList
- in List.filter (\r -> Set.member (String.toLower r.title) a || Set.member (String.toLower r.original) a) model.releases |> List.head
+ in List.filter (\r -> Set.member (String.toLower r.title) a || Set.member (String.toLower r.alttitle) a) model.releases |> List.head
isValid : Model -> Bool
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index 440393fe..4b2dde3f 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -358,7 +358,7 @@ f v => 6 => 'developer-id', { vndbid => 'p' }, '=' => sub { sql 'v.c_developers
f r => 2 => 'lang', { enum => \%LANGUAGE },
sql_list => sub {
my($neg, $all, $val) = @_;
- sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM releases_lang WHERE NOT mtl AND lang IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(lang) =', \scalar @$val) : (), ')';
+ sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM releases_titles WHERE NOT mtl AND lang IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(lang) =', \scalar @$val) : (), ')';
};
f r => 4 => 'platform', { required => 0, default => undef, enum => \%PLATFORM },
diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm
index 82d2991f..5981138d 100644
--- a/lib/VNWeb/Elm.pm
+++ b/lib/VNWeb/Elm.pm
@@ -62,7 +62,7 @@ our %apis = (
Releases => [ { aoh => { # Response to 'Release'
id => { vndbid => 'r' },
title => {},
- original => { required => 0, default => '' },
+ alttitle => { required => 0, default => '' },
released => { uint => 1 },
rtype => {},
reso_x => { uint => 1 },
diff --git a/lib/VNWeb/LangPref.pm b/lib/VNWeb/LangPref.pm
index a100c268..e5e34256 100644
--- a/lib/VNWeb/LangPref.pm
+++ b/lib/VNWeb/LangPref.pm
@@ -14,6 +14,7 @@ our @EXPORT = qw/
$DEFAULT_TITLE_LANGS
$DEFAULT_ALTTITLE_LANGS
sql_vn_hist
+ sql_releases_hist
/;
TUWF::set('custom_validations')->{langpref} = { type => 'array', maxlength => 5, values => { type => 'hash', keys => {
@@ -59,7 +60,7 @@ sub pref {
sub gen_sql {
- my($tbl_main, $tbl_titles, $join_col) = @_;
+ my($has_official, $tbl_main, $tbl_titles, $join_col) = @_;
my $p = pref;
sub id { ($_[0]{original}?'r':$_[0]{official}?'o':'u').($_[0]{lang}//'') }
@@ -72,7 +73,7 @@ sub gen_sql {
"$joins{$_}.$join_col = x.$join_col",
$_ =~ /^r/ ? "$joins{$_}.lang = x.olang" : (),
length($_) > 1 ? sql("$joins{$_}.lang =", \(''.substr($_,1))) : (),
- $_ =~ /^o./ ? "$joins{$_}.official" : (),
+ $has_official && $_ =~ /^o./ ? "$joins{$_}.official" : (),
), sort keys %joins;
my $title = 'COALESCE('.join(',',
@@ -90,7 +91,8 @@ sub gen_sql {
# Similar to the 'vnt' VIEW, except for vn_hist and it generates a SELECT query for inline use.
-sub sql_vn_hist { gen_sql 'vn_hist', 'vn_titles_hist', 'chid' }
+sub sql_vn_hist { gen_sql 1, 'vn_hist', 'vn_titles_hist', 'chid' }
+sub sql_releases_hist { gen_sql 0, 'releases_hist', 'releases_titles_hist', 'chid' }
# Run the given subroutine with the default language preferences, by
@@ -114,7 +116,8 @@ TUWF::hook before => sub {
my $p = pref;
return if $p->[2] eq $CURRENT_SESSION;
$CURRENT_SESSION = $p->[2];
- tuwf->dbExeci('CREATE OR REPLACE TEMPORARY VIEW vnt AS', gen_sql('vn', 'vn_titles', 'id'));
+ tuwf->dbExeci('CREATE OR REPLACE TEMPORARY VIEW vnt AS', gen_sql(1, 'vn', 'vn_titles', 'id'));
+ tuwf->dbExeci('CREATE OR REPLACE TEMPORARY VIEW releasest AS', gen_sql(0, 'releases', 'releases_titles', 'id'));
};
1;
diff --git a/lib/VNWeb/Misc/HomePage.pm b/lib/VNWeb/Misc/HomePage.pm
index 72645768..81dbaf5a 100644
--- a/lib/VNWeb/Misc/HomePage.pm
+++ b/lib/VNWeb/Misc/HomePage.pm
@@ -173,16 +173,15 @@ sub releases_ {
# Add the release date as filter, we need to construct a filter for the header link anyway
$filt->{query} = [ 'and', [ released => $released ? '<=' : '>', 1 ], $filt->{query} || () ];
- # XXX This query is kinda slow, an index on releases.released would probably help.
my $lst = tuwf->dbAlli('
- SELECT id, title, original, released
- FROM releases r
+ SELECT id, title, alttitle, released
+ FROM releasest r
WHERE NOT hidden AND ', $filt->sql_where(), '
- AND NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.mtl)
+ AND NOT EXISTS(SELECT 1 FROM releases_titles rt WHERE rt.id = r.id AND rt.mtl)
ORDER BY released', $released ? 'DESC' : '', ', id LIMIT 10'
);
enrich_flatten plat => id => id => 'SELECT id, platform FROM releases_platforms WHERE id IN', $lst;
- enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_lang WHERE id IN', $lst;
+ enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_titles WHERE id IN', $lst;
h1_ sub {
a_ href => '/r?f='.$filt->query_encode().';o=a;s=released', 'Upcoming Releases' if !$released;
@@ -196,7 +195,7 @@ sub releases_ {
platform_ $_ for $_->{plat}->@*;
abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for $_->{lang}->@*;
txt_ ' ';
- a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title};
+ a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title};
}
} for @$lst;
};
diff --git a/lib/VNWeb/Releases/Edit.pm b/lib/VNWeb/Releases/Edit.pm
index 30005150..c2ee566b 100644
--- a/lib/VNWeb/Releases/Edit.pm
+++ b/lib/VNWeb/Releases/Edit.pm
@@ -5,17 +5,18 @@ use VNWeb::Prelude;
my $FORM = {
id => { required => 0, vndbid => 'r' },
- title => { maxlength => 300 },
- original => { required => 0, default => '', maxlength => 250 },
official => { anybool => 1 },
patch => { anybool => 1 },
freeware => { anybool => 1 },
doujin => { anybool => 1 },
has_ero => { anybool => 1 },
- lang => { minlength => 1, sort_keys => 'lang', aoh => {
+ titles => { minlength => 1, sort_keys => 'lang', aoh => {
lang => { enum => \%LANGUAGE },
mtl => { anybool => 1 },
+ title => { maxlength => 300 },
+ latin => { required => 0, default => undef, maxlength => 300 },
} },
+ olang => { enum => \%LANGUAGE, default => 'ja' },
platforms => { aoh => { platform => { enum => \%PLATFORM } } },
media => { aoh => {
medium => { enum => \%MEDIUM },
@@ -75,6 +76,7 @@ TUWF::get qr{/$RE{rrev}/(?<action>edit|copy)} => sub {
$e->{editsum} = $copy ? "Copied from $e->{id}.$e->{chrev}" : $e->{chrev} == $e->{maxrev} ? '' : "Reverted to revision $e->{id}.$e->{chrev}";
$e->{authmod} = auth->permDbmod;
+ $e->{titles} = [ sort { $a->{lang} cmp $b->{lang} } $e->{titles}->@* ];
to_extlinks $e;
enrich_merge vid => 'SELECT id AS vid, title FROM vnt WHERE id IN', $e->{vn};
@@ -162,6 +164,8 @@ elm_api ReleaseEdit => $FORM_OUT, $FORM_IN, sub {
}
ani_compat($data, $e);
+ die "No title in main language" if !grep $_->{lang} eq $data->{olang}, $data->{titles}->@*;
+
$_->{qty} = $MEDIUM{$_->{medium}}{qty} ? $_->{qty}||1 : 0 for $data->{media}->@*;
$data->{notes} = bb_subst_links $data->{notes};
die "No VNs selected" if !$data->{vn}->@*;
diff --git a/lib/VNWeb/Releases/Lib.pm b/lib/VNWeb/Releases/Lib.pm
index 20432c42..ecf643c9 100644
--- a/lib/VNWeb/Releases/Lib.pm
+++ b/lib/VNWeb/Releases/Lib.pm
@@ -9,8 +9,8 @@ our @EXPORT = qw/enrich_release_elm releases_by_vn enrich_release sort_releases
# Enrich a list of releases so that it's suitable as 'Releases' Elm response.
# Given objects must have 'id' and 'rtype' fields (appropriate for the VN in context).
sub enrich_release_elm {
- enrich_merge id => 'SELECT id, title, original, released, reso_x, reso_y FROM releases WHERE id IN', @_;
- enrich_flatten lang => id => id => sub { sql('SELECT id, lang FROM releases_lang WHERE id IN', $_, 'ORDER BY lang') }, @_;
+ enrich_merge id => 'SELECT id, title, alttitle, released, reso_x, reso_y FROM releasest WHERE id IN', @_;
+ enrich_flatten lang => id => id => sub { sql('SELECT id, lang FROM releases_titles WHERE id IN', $_, 'ORDER BY lang') }, @_;
enrich_flatten platforms => id => id => sub { sql('SELECT id, platform FROM releases_platforms WHERE id IN', $_, 'ORDER BY platform') }, @_;
}
@@ -28,13 +28,13 @@ sub releases_by_vn {
sub enrich_release {
my($r) = @_;
enrich_merge id =>
- 'SELECT id, title, original, notes, minage, official, freeware, has_ero, reso_x, reso_y, voiced, uncensored
+ 'SELECT id, title, alttitle, notes, minage, official, freeware, has_ero, reso_x, reso_y, voiced, uncensored
, ani_story, ani_ero, ani_story_sp, ani_story_cg, ani_cutscene, ani_ero_sp, ani_ero_cg, ani_face, ani_bg
- FROM releases WHERE id IN', $r;
+ FROM releasest WHERE id IN', $r;
enrich_merge id => sub { sql 'SELECT id, MAX(rtype) AS rtype FROM releases_vn WHERE id IN', $_, 'GROUP BY id' }, grep !$_->{rtype}, ref $r ? @$r : $r;
enrich_merge id => sql('SELECT rid as id, status as rlist_status FROM rlists WHERE uid =', \auth->uid, 'AND rid IN'), $r if auth;
enrich_flatten platforms => id => id => sub { sql 'SELECT id, platform FROM releases_platforms WHERE id IN', $_, 'ORDER BY id, platform' }, $r;
- enrich lang => id => id => sub { 'SELECT id, lang, mtl FROM releases_lang WHERE id IN', $_, 'ORDER BY id, mtl, lang' }, $r;
+ enrich titles => id => id => sub { 'SELECT id, lang, mtl, title, latin FROM releases_titles WHERE id IN', $_, 'ORDER BY id, mtl, lang' }, $r;
enrich media => id => id => sub { 'SELECT id, medium, qty FROM releases_media WHERE id IN', $_, 'ORDER BY id, medium' }, $r;
}
@@ -90,14 +90,13 @@ sub release_extlinks_ {
# Options
# id: unique identifier if the same release may be listed on a page twice.
-# lang: $lang, whether to display language icons and which language to use for the MTL flag.
+# lang: $lang, whether to display language icons and which language to use for the title and MTL flag.
# prod: 0/1 whether to display Pub/Dev indication
sub release_row_ {
my($r, $opt) = @_;
- my $mtl = $opt->{lang}
- ? [grep $_->{lang} eq $opt->{lang}, $r->{lang}->@*]->[0]{mtl}
- : (grep $_->{mtl}, $r->{lang}->@*) == $r->{lang}->@*;
+ my $lang = $opt->{lang} && (grep $_->{lang} eq $opt->{lang}, $r->{titles}->@*)[0];
+ my $mtl = $lang ? $lang->{mtl} : (grep $_->{mtl}, $r->{titles}->@*) == $r->{titles}->@*;
my $storyani = join "\n", map "$_.",
$r->{ani_story} == 1 ? 'Not animated' :
@@ -141,7 +140,7 @@ sub release_row_ {
}
tr_ $mtl ? (class => 'mtl') : (), sub {
- td_ class => 'tc1', sub { rdate_ [grep $_->{lang} eq $opt->{lang}, $opt->{lang}?$r->{lang}->@*:()]->[0]{released}//$r->{released} };
+ td_ class => 'tc1', sub { rdate_ $r->{released} };
td_ class => 'tc2', sub {
span_ class => 'releaseero releaseero_'.(!$r->{has_ero} ? 'no' : $r->{uncensored} ? 'unc' : defined $r->{uncensored} ? 'cen' : 'yes'),
title => !$r->{has_ero} ? 'No erotic scenes' :
@@ -152,12 +151,13 @@ sub release_row_ {
td_ class => 'tc3', sub {
platform_ $_ for $r->{platforms}->@*;
if(!$opt->{lang}) {
- abbr_ class => "icons lang $_->{lang}".($_->{mtl}?' mtl':''), title => $LANGUAGE{$_->{lang}}, '' for $r->{lang}->@*;
+ abbr_ class => "icons lang $_->{lang}".($_->{mtl}?' mtl':''), title => $LANGUAGE{$_->{lang}}, '' for $r->{titles}->@*;
}
abbr_ class => "icons rt$r->{rtype}", title => $r->{rtype}, '';
};
td_ class => 'tc4', sub {
- a_ href => "/$r->{id}", title => $r->{original}||$r->{title}, $r->{title};
+ # TODO: Read user preferences to see whether to display the 'title' or 'latin' here.
+ a_ href => "/$r->{id}", title => $lang ? $lang->{latin} : $r->{alttitle}||$r->{title}, $lang ? $lang->{title} : $r->{title};
my $note = join ' ', $r->{official} ? () : 'unofficial', $mtl ? 'machine translation' : (), $r->{patch} ? 'patch' : ();
b_ class => 'grayedout', " ($note)" if $note;
};
diff --git a/lib/VNWeb/Releases/List.pm b/lib/VNWeb/Releases/List.pm
index 178fd220..2e25a04d 100644
--- a/lib/VNWeb/Releases/List.pm
+++ b/lib/VNWeb/Releases/List.pm
@@ -62,10 +62,10 @@ TUWF::get qr{/r}, sub {
$count = tuwf->dbVali('SELECT count(*) FROM releases r WHERE', $where);
$list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
SELECT r.id, r.patch, r.released, r.gtin, ', sql_extlinks(r => 'r.'), '
- FROM releases r
+ FROM releasest r
WHERE', $where, '
ORDER BY', sprintf {
- title => 'r.title %s, r.released %1$s',
+ title => 'r.sorttitle %s, r.released %1$s',
minage => 'r.minage %s, r.title %1$s, r.released %1$s',
released => 'r.released %s, r.title %1$s, r.id %1$s',
}->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC'
diff --git a/lib/VNWeb/Releases/Page.pm b/lib/VNWeb/Releases/Page.pm
index 27b9d4ef..cbbfd4c1 100644
--- a/lib/VNWeb/Releases/Page.pm
+++ b/lib/VNWeb/Releases/Page.pm
@@ -2,6 +2,7 @@ package VNWeb::Releases::Page;
use VNWeb::Prelude;
use VNWeb::Releases::Lib;
+use VNWeb::LangPref 'sql_releases_hist';
sub enrich_item {
@@ -10,7 +11,7 @@ sub enrich_item {
enrich_merge pid => 'SELECT id AS pid, name, original FROM producers WHERE id IN', $r->{producers};
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->{titles} = [ sort { ($a->{mtl}?1:0) <=> ($b->{mtl}?1:0) || $a->{lang} cmp $b->{lang} } $r->{titles}->@* ];
$r->{platforms} = [ sort map $_->{platform}, $r->{platforms}->@* ];
$r->{vn} = [ sort { $a->{title} cmp $b->{title} || idcmp($a->{vid}, $b->{vid}) } $r->{vn}->@* ];
$r->{producers} = [ sort { $a->{name} cmp $b->{name} || idcmp($a->{pid}, $b->{pid}) } $r->{producers}->@* ];
@@ -38,11 +39,12 @@ sub _rev_ {
[ has_ero => 'Has ero', fmt => 'bool' ],
[ doujin => 'Doujin', fmt => 'bool' ],
[ uncensored => 'Uncensored', fmt => 'bool' ],
- [ title => 'Title (Romaji)' ],
- [ original => 'Original title' ],
[ gtin => 'JAN/EAN/UPC', empty => 0 ],
[ catalog => 'Catalog number' ],
- [ lang => 'Languages', fmt => sub { txt_ $LANGUAGE{$_->{lang}}; txt_ ' (machine translation)' if $_->{mtl} } ],
+ [ titles => 'Languages', txt => sub {
+ '['.$_->{lang}.($_->{mtl} ? ' machine translation' : '').'] '.$_->{title}.($_->{latin} ? " / $_->{latin}" : '')
+ }],
+ [ olang => 'Main title', fmt => \%LANGUAGE ],
[ released => 'Release date', fmt => sub { rdate_ $_ } ],
[ minage => 'Age rating', fmt => sub { txt_ minage $_ } ],
[ notes => 'Notes' ],
@@ -137,39 +139,34 @@ sub _infotable_ {
}
};
- tr_ sub {
- td_ 'Title';
- td_ $r->{title};
+ tr_ class => 'titles', sub {
+ td_ $r->{titles}->@* == 1 ? 'Title' : 'Titles';
+ td_ sub {
+ table_ sub {
+ tr_ class => 'nostripe title', sub {
+ td_ sub {
+ abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, '';
+ };
+ td_ sub {
+ span_ lang_attr($_->{lang}), $_->{title};
+ b_ class => 'grayedout', ' (machine translation)' if $_->{mtl};
+ if($_->{latin}) {
+ br_;
+ txt_ $_->{latin};
+ }
+ }
+ } for $r->{titles}->@*;
+ };
+ };
};
tr_ sub {
- td_ 'Original title';
- td_ lang_attr($r->{lang}), $r->{original};
- } if $r->{original};
-
- tr_ sub {
td_ 'Type';
td_ !$r->{official} && $r->{patch} ? 'Unofficial patch' :
!$r->{official} ? 'Unofficial' : 'Patch';
} if !$r->{official} || $r->{patch};
tr_ sub {
- td_ 'Language';
- td_ sub {
- join_ \&br_, sub {
- abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, ' ';
- txt_ ' ';
- if($_->{mtl}) {
- b_ class => 'grayedout', $LANGUAGE{$_->{lang}};
- txt_ ' (machine translation)';
- } else {
- txt_ $LANGUAGE{$_->{lang}};
- }
- }, $r->{lang}->@*;
- }
- };
-
- tr_ sub {
td_ 'Publication';
td_ $r->{freeware} ? 'Freeware' : 'Non-free';
};
@@ -271,6 +268,8 @@ TUWF::get qr{/$RE{rrev}} => sub {
my $r = db_entry tuwf->captures('id','rev');
return tuwf->resNotFound if !$r;
+ enrich_merge chid => sql('SELECT chid, x.title, x.alttitle FROM (', sql_releases_hist(), ') x WHERE chid IN'), $r if $r->{chrev} != $r->{maxrev};
+ enrich_merge id => sql('SELECT id, title, alttitle FROM releasest WHERE id IN'), $r if $r->{chrev} == $r->{maxrev};
enrich_item $r;
enrich_extlinks r => $r;
@@ -283,7 +282,7 @@ TUWF::get qr{/$RE{rrev}} => sub {
div_ class => 'mainbox release', sub {
itemmsg_ $r;
h1_ sub { txt_ $r->{title}; debug_ $r };
- h2_ class => 'alttitle', lang_attr($r->{lang}), $r->{original} if length $r->{original};
+ h2_ class => 'alttitle', lang_attr($r->{olang}), $r->{alttitle} if length $r->{alttitle} && $r->{alttitle} ne $r->{title};
_infotable_ $r;
div_ class => 'description', sub { lit_ bb_format $r->{notes} } if $r->{notes};
};
diff --git a/lib/VNWeb/Releases/VNTab.pm b/lib/VNWeb/Releases/VNTab.pm
index dabad6dd..c7408def 100644
--- a/lib/VNWeb/Releases/VNTab.pm
+++ b/lib/VNWeb/Releases/VNTab.pm
@@ -42,8 +42,7 @@ my @rel_cols = (
id => 'lan',
button_string => 'Language',
default => 1,
- has_data => sub { !!@{$_[0]{lang}} },
- draw => sub { join_ \&br_, sub { abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, ''; }, $_[0]{lang}->@* },
+ draw => sub { join_ \&br_, sub { abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, ''; }, $_[0]{titles}->@* },
}, { # Publication
id => 'pub',
sort_field => 'publication',
@@ -168,7 +167,7 @@ sub buttons_ {
}
};
pl 'os', \&platform_, map $_->{platforms}->@*, @$r if $opt->{pla};
- pl 'lang', sub { abbr_ class => "icons lang $_[0]", title => $LANGUAGE{$_[0]}, '' }, map $_->{lang}, map $_->{lang}->@*, @$r if $opt->{lan};
+ pl 'lang', sub { abbr_ class => "icons lang $_[0]", title => $LANGUAGE{$_[0]}, '' }, map $_->{lang}, map $_->{titles}->@*, @$r if $opt->{lan};
}
@@ -178,7 +177,7 @@ sub listing_ {
# Apply language and platform filters
my @r = grep +
($opt->{os} eq 'all' || ($_->{platforms} && grep $_ eq $opt->{os}, $_->{platforms}->@*)) &&
- ($opt->{lang} eq 'all' || ($_->{lang} && grep $_ eq $opt->{lang}, map $_->{lang}, $_->{lang}->@*)), @$r;
+ ($opt->{lang} eq 'all' || ($_->{titles} && grep $_ eq $opt->{lang}, map $_->{lang}, $_->{titles}->@*)), @$r;
# Figure out which columns to display
my @col;
@@ -238,7 +237,7 @@ TUWF::get qr{/$RE{vid}/releases} => sub {
my $r = tuwf->dbAlli('
SELECT r.id, rv.rtype, r.patch, r.released, r.gtin
- FROM releases r
+ FROM releasest r
JOIN releases_vn rv ON rv.id = r.id
WHERE NOT hidden AND rv.vid =', \$v->{id}, '
ORDER BY', sprintf(+(grep $opt->{s} eq ($_->{sort_field}//''), @rel_cols)[0]{sort_sql}, $opt->{o} eq 'a' ? 'ASC' : 'DESC')
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index f5a5f837..769c01d9 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -39,7 +39,7 @@ sub review_ {
platform_ $_ for $w->{platforms}->@*;
abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for $w->{lang}->@*;
abbr_ class => "icons rt$w->{rtype}", title => $w->{rtype}, '';
- a_ href => "/$w->{rid}", title => $w->{roriginal}||$w->{rtitle}, $w->{rtitle};
+ a_ href => "/$w->{rid}", title => $w->{ralttitle}||$w->{rtitle}, $w->{rtitle};
}
};
};
@@ -98,11 +98,11 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
my($id, $sep, $num) = (tuwf->capture('id'), tuwf->capture('sep')||'', tuwf->capture('num'));
my $w = tuwf->dbRowi(
'SELECT r.id, r.vid, r.rid, r.isfull, r.modnote, r.text, r.spoiler, r.locked, COALESCE(c.count,0) AS count, r.c_flagged, r.c_up, r.c_down, uv.vote, rm.id IS NULL AS can
- , v.title, v.alttitle, rel.title AS rtitle, rel.original AS roriginal, relv.rtype, rv.vote AS my, COALESCE(rv.overrule,false) AS overrule
+ , v.title, v.alttitle, rel.title AS rtitle, rel.alttitle AS ralttitle, 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 vnt v ON v.id = r.vid
- LEFT JOIN releases rel ON rel.id = r.rid
+ LEFT JOIN releasest 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
LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid
@@ -113,7 +113,7 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
);
return tuwf->resNotFound if !$w->{id};
- enrich_flatten lang => rid => id => sub { sql 'SELECT id, lang FROM releases_lang WHERE id IN', $_, 'ORDER BY id, lang' }, $w;
+ enrich_flatten lang => rid => id => sub { sql 'SELECT id, lang FROM releases_titles WHERE id IN', $_, 'ORDER BY id, lang' }, $w;
enrich_flatten platforms => rid => id => sub { sql 'SELECT id, platform FROM releases_platforms WHERE id IN', $_, 'ORDER BY id, platform' }, $w;
my $page = $sep eq '/' ? $num||1 : $sep ne '.' ? 1
diff --git a/lib/VNWeb/VN/Edit.pm b/lib/VNWeb/VN/Edit.pm
index 09608ef7..834c8ac8 100644
--- a/lib/VNWeb/VN/Edit.pm
+++ b/lib/VNWeb/VN/Edit.pm
@@ -7,7 +7,7 @@ use VNWeb::Releases::Lib;
my $FORM = {
id => { required => 0, vndbid => 'v' },
- titles => { sort_keys => 'lang', aoh => {
+ titles => { minlength => 1, sort_keys => 'lang', aoh => {
lang => { enum => \%LANGUAGE },
title => { maxlength => 250 },
latin => { required => 0, default => undef, maxlength => 250 },
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index 7f461578..66d76c04 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -141,7 +141,7 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
'ORDER BY', $opt->{s}->sql_order(),
);
$_->{rel} = [ map +{ id => $_ }, $_->{rel}->@* ] for @$lst;
- enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_lang WHERE id IN', map $_->{rel}, @$lst;
+ enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_titles WHERE id IN', map $_->{rel}, @$lst;
my $title = 'Length votes'.($mode ? ($mode eq 'v' ? ' for ' : ' by ').$o->{title} : '');
framework_ title => $title, dbobj => $o, sub {
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index a58a742c..c08ef170 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -249,7 +249,7 @@ sub infobox_producers_ {
SELECT p.id, p.name, p.original, rl.lang, bool_or(rp.developer) as developer, bool_or(rp.publisher) as publisher, min(rv.rtype) as rtype, bool_or(r.official) as official
FROM releases_vn rv
JOIN releases r ON r.id = rv.id
- JOIN releases_lang rl ON rl.id = rv.id
+ JOIN releases_titles rl ON rl.id = rv.id
JOIN releases_producers rp ON rp.id = rv.id
JOIN producers p ON p.id = rp.pid
WHERE NOT r.hidden AND (r.official OR NOT rl.mtl) AND rv.vid =', \$v->{id}, '
@@ -550,7 +550,7 @@ sub releases_ {
my(%lang, %langrel, %langmtl);
for my $r ($v->{releases}->@*) {
- for ($r->{lang}->@*) {
+ for ($r->{titles}->@*) {
push $lang{$_->{lang}}->@*, $r;
$langmtl{$_->{lang}} = ($langmtl{$_->{lang}}//1) && $_->{mtl};
}
diff --git a/sql/func.sql b/sql/func.sql
index af51303a..1b694211 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -21,7 +21,6 @@ CREATE OR REPLACE FUNCTION fmtip(n ipinfo) RETURNS text AS $$
$$ LANGUAGE SQL IMMUTABLE;
-
CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
SELECT coalesce(string_agg(t, ' '), '') FROM (
SELECT t FROM (
@@ -43,15 +42,25 @@ CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
|(?:parts?|vol|volumes?|chapters?|v|ver|versions?)(?:[0-9]+)
|editions?|version|production|thebest|append|scenario|dlc)+$', '', 'xg')
FROM (
- SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
+ SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
UNION ALL
- SELECT original FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
+ SELECT latin FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
) r(t)
) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
) x(t);
$$ LANGUAGE SQL;
+CREATE OR REPLACE FUNCTION search_gen_release(relid vndbid) RETURNS text AS $$
+ SELECT coalesce(string_agg(t, ' '), '') FROM (
+ SELECT t FROM (
+ SELECT search_norm_term(title) FROM releases_titles WHERE id = relid
+ UNION ALL SELECT search_norm_term(latin) FROM releases_titles WHERE id = relid
+ ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
+ ) x(t);
+$$ LANGUAGE SQL;
+
+
-- update_vncache(id) - updates some c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$
UPDATE vn SET
@@ -68,7 +77,7 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$
), 0),
c_languages = ARRAY(
SELECT rl.lang
- FROM releases_lang rl
+ FROM releases_titles rl
JOIN releases r ON r.id = rl.id
JOIN releases_vn rv ON r.id = rv.id
WHERE rv.vid = $1
@@ -409,7 +418,7 @@ BEGIN
--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 'r' THEN RETURN QUERY SELECT r.title ::text, r.alttitle::text, NULL::vndbid, r.hidden, r.locked FROM releasest 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;
WHEN 'd' THEN RETURN QUERY SELECT d.title ::text, NULL, NULL::vndbid, d.hidden, d.locked FROM docs d WHERE d.id = $1;
@@ -424,7 +433,8 @@ BEGIN
ELSE CASE vndbid_type($1)
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 'r' THEN RETURN QUERY SELECT COALESCE(ro.latin, ro.title), CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END, h.requester, h.ihid, h.ilock
+ FROM changes h JOIN releases_hist r ON h.id = r.chid JOIN releases_titles_hist ro ON h.id = ro.chid AND ro.lang = r.olang 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;
WHEN 'd' THEN RETURN QUERY SELECT d.title::text, NULL, h.requester, h.ihid, h.ilock FROM changes h JOIN docs_hist d ON h.id = d.chid WHERE h.itemid = $1 AND h.rev = $2;
@@ -485,7 +495,7 @@ BEGIN
-- Update vn.c_search when
-- 1. A new release is created
-- 2. A release has been hidden or unhidden
- -- 3. The release title/original has changed
+ -- 3. The releases_titles have changed
-- 4. The releases_vn table differs from a previous revision
IF vndbid_type(nitemid) = 'r' THEN
IF -- 1.
@@ -493,7 +503,8 @@ BEGIN
-- 2.
EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = nchid AND c2.id = xoldchid) OR
-- 3.
- EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = nchid) OR
+ EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = nchid) OR
+ EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = nchid EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid) OR
-- 4.
EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = nchid) OR
EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = nchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
@@ -502,6 +513,11 @@ BEGIN
END IF;
END IF;
+ -- Update releases.c_search
+ IF vndbid_type(nitemid) = 'r' THEN
+ UPDATE releases SET c_search = search_gen_release(id) WHERE id = nitemid;
+ END IF;
+
-- Call update_vncache() for related VNs when a release has been created or edited
-- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity)
IF vndbid_type(nitemid) = 'r' THEN
diff --git a/sql/perms.sql b/sql/perms.sql
index f13e9d54..be9c531d 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -31,9 +31,10 @@ GRANT SELECT, INSERT ON producers_relations_hist TO vndb_site;
GRANT SELECT ON quotes TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON registration_throttle TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON releases TO vndb_site;
+GRANT SELECT ON releasest TO vndb_site;
GRANT SELECT, INSERT ON releases_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_lang TO vndb_site;
-GRANT SELECT, INSERT ON releases_lang_hist TO vndb_site;
+GRANT SELECT, INSERT, DELETE ON releases_titles TO vndb_site;
+GRANT SELECT, INSERT ON releases_titles_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON releases_media TO vndb_site;
GRANT SELECT, INSERT ON releases_media_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON releases_platforms TO vndb_site;
@@ -137,8 +138,9 @@ GRANT SELECT ON producers_relations TO vndb_multi;
GRANT SELECT ON quotes TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON registration_throttle TO vndb_multi;
GRANT SELECT ON releases TO vndb_multi;
+GRANT SELECT ON releasest TO vndb_multi;
GRANT SELECT ON releases_hist TO vndb_multi;
-GRANT SELECT ON releases_lang TO vndb_multi;
+GRANT SELECT ON releases_titles TO vndb_multi;
GRANT SELECT ON releases_media TO vndb_multi;
GRANT SELECT ON releases_platforms TO vndb_multi;
GRANT SELECT ON releases_producers TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index 47cda09b..cc988749 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -425,8 +425,6 @@ CREATE TABLE releases ( -- dbentry_type=r
official boolean NOT NULL DEFAULT TRUE, -- [pub]
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
- title varchar(300) NOT NULL DEFAULT '', -- [pub]
- original varchar(250) NOT NULL DEFAULT '', -- [pub]
website varchar(1024) NOT NULL DEFAULT '', -- [pub]
catalog varchar(50) NOT NULL DEFAULT '', -- [pub]
engine varchar(50) NOT NULL DEFAULT '', -- [pub]
@@ -444,7 +442,7 @@ CREATE TABLE releases ( -- dbentry_type=r
l_gyutto integer[] NOT NULL DEFAULT '{}', -- [pub]
l_dmm text[] NOT NULL DEFAULT '{}', -- [pub]
l_freegame text NOT NULL DEFAULT '', -- [pub]
- c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[title, original])) STORED,
+ c_search text,
l_playstation_jp text NOT NULL DEFAULT '', -- [pub]
l_playstation_na text NOT NULL DEFAULT '', -- [pub]
l_playstation_eu text NOT NULL DEFAULT '', -- [pub]
@@ -457,7 +455,8 @@ CREATE TABLE releases ( -- dbentry_type=r
ani_ero_cg animation, -- [pub]
ani_bg boolean, -- [pub]
ani_face boolean, -- [pub]
- has_ero boolean NOT NULL DEFAULT FALSE -- [pub]
+ has_ero boolean NOT NULL DEFAULT FALSE, -- [pub]
+ olang language NOT NULL DEFAULT 'ja' -- [pub] Refers to the main title to use for display purposes, not necessarily the original language.
);
-- releases_hist
@@ -491,8 +490,6 @@ CREATE TABLE releases_hist (
doujin boolean NOT NULL DEFAULT FALSE,
uncensored boolean,
official boolean NOT NULL DEFAULT TRUE,
- title varchar(300) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
website varchar(1024) NOT NULL DEFAULT '',
catalog varchar(50) NOT NULL DEFAULT '',
engine varchar(50) NOT NULL DEFAULT '',
@@ -520,23 +517,8 @@ CREATE TABLE releases_hist (
ani_ero_cg animation,
ani_bg boolean,
ani_face boolean,
- has_ero boolean NOT NULL DEFAULT FALSE
-);
-
--- releases_lang
-CREATE TABLE releases_lang (
- id vndbid NOT NULL, -- [pub]
- lang language NOT NULL, -- [pub]
- mtl boolean NOT NULL DEFAULT false, -- [pub]
- PRIMARY KEY(id, lang)
-);
-
--- releases_lang_hist
-CREATE TABLE releases_lang_hist (
- chid integer NOT NULL,
- lang language NOT NULL,
- mtl boolean NOT NULL DEFAULT false, -- [pub]
- PRIMARY KEY(chid, lang)
+ has_ero boolean NOT NULL DEFAULT FALSE,
+ olang language NOT NULL DEFAULT 'ja'
);
-- releases_media
@@ -589,6 +571,26 @@ CREATE TABLE releases_producers_hist (
PRIMARY KEY(chid, pid)
);
+-- releases_titles
+CREATE TABLE releases_titles (
+ id vndbid NOT NULL, -- [pub]
+ lang language NOT NULL, -- [pub]
+ mtl boolean NOT NULL DEFAULT false, -- [pub]
+ title text NOT NULL DEFAULT '', -- [pub]
+ latin text, -- [pub]
+ PRIMARY KEY(id, lang)
+);
+
+-- releases_titles_hist
+CREATE TABLE releases_titles_hist (
+ chid integer NOT NULL,
+ lang language NOT NULL,
+ mtl boolean NOT NULL DEFAULT false,
+ title text NOT NULL DEFAULT '',
+ latin text,
+ PRIMARY KEY(chid, lang)
+);
+
-- releases_vn
CREATE TABLE releases_vn (
id vndbid NOT NULL, -- [pub]
@@ -1386,3 +1388,6 @@ CREATE TABLE wikidata (
-- 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, COALESCE(vo.latin, vo.title) AS sorttitle, 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;
+
+-- Same for releases
+CREATE VIEW releasest AS SELECT r.*, COALESCE(ro.latin, ro.title) AS title, COALESCE(ro.latin, ro.title) AS sorttitle, CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END AS alttitle FROM releases r JOIN releases_titles ro ON ro.id = r.id AND ro.lang = r.olang;
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 1ce39b30..96cab630 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -82,9 +82,11 @@ ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey
ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases ADD CONSTRAINT releases_olang_fkey FOREIGN KEY (id,olang) REFERENCES releases_titles(id,lang) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES releases_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_titles ADD CONSTRAINT releases_titles_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_titles_hist ADD CONSTRAINT releases_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 01c1ba98..fb51bc41 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -70,10 +70,10 @@ my %tables = (
producers_relations => { where => 'id IN(SELECT id FROM producers WHERE NOT hidden)' },
quotes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' },
releases => { where => 'NOT hidden' },
- releases_lang => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' },
releases_media => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' },
releases_platforms => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' },
releases_producers => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND pid IN(SELECT id FROM producers WHERE NOT hidden)' },
+ releases_titles => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' },
releases_vn => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
rlists => { where => 'EXISTS(SELECT 1 FROM releases r'
.' JOIN releases_vn rv ON rv.id = r.id'
diff --git a/util/devdump.pl b/util/devdump.pl
index 43dff1ec..ae19a6e8 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -171,7 +171,7 @@ sub copy_entry {
copy ulist_vns_labels => "SELECT vid, uid, 7 AS lbl FROM ($votes) x", {uid => 'user'};
# Releases
- copy_entry [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
+ copy_entry [qw/releases releases_media releases_platforms releases_producers releases_titles releases_vn/], $releases;
print "\\i sql/tableattrs.sql\n";
print "\\i sql/triggers.sql\n";