diff options
-rw-r--r-- | css/v2.css | 3 | ||||
-rw-r--r-- | elm/ReleaseEdit.elm | 110 | ||||
-rw-r--r-- | elm/UList/Opt.elm | 2 | ||||
-rw-r--r-- | elm/UList/Widget.elm | 2 | ||||
-rw-r--r-- | elm/VNEdit.elm | 3 | ||||
-rw-r--r-- | lib/VNWeb/AdvSearch.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Elm.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/LangPref.pm | 11 | ||||
-rw-r--r-- | lib/VNWeb/Misc/HomePage.pm | 11 | ||||
-rw-r--r-- | lib/VNWeb/Releases/Edit.pm | 10 | ||||
-rw-r--r-- | lib/VNWeb/Releases/Lib.pm | 24 | ||||
-rw-r--r-- | lib/VNWeb/Releases/List.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/Releases/Page.pm | 57 | ||||
-rw-r--r-- | lib/VNWeb/Releases/VNTab.pm | 9 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Page.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/VN/Edit.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/VN/Length.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 4 | ||||
-rw-r--r-- | sql/func.sql | 32 | ||||
-rw-r--r-- | sql/perms.sql | 8 | ||||
-rw-r--r-- | sql/schema.sql | 51 | ||||
-rw-r--r-- | sql/tableattrs.sql | 6 | ||||
-rwxr-xr-x | util/dbdump.pl | 2 | ||||
-rwxr-xr-x | util/devdump.pl | 2 |
24 files changed, 202 insertions, 165 deletions
@@ -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"; |