summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--css/v2.css11
-rw-r--r--elm/User/Edit.elm14
-rw-r--r--elm/VNEdit.elm131
-rw-r--r--lib/VNDB/Types.pm5
-rw-r--r--lib/VNWeb/Staff/Page.pm7
-rw-r--r--lib/VNWeb/User/Edit.pm11
-rw-r--r--lib/VNWeb/VN/Edit.pm15
-rw-r--r--lib/VNWeb/VN/Page.pm94
-rw-r--r--sql/perms.sql2
-rw-r--r--sql/schema.sql31
-rw-r--r--sql/tableattrs.sql6
-rwxr-xr-xutil/dbdump.pl4
-rw-r--r--util/updates/2022-08-25-staff-editions.sql43
13 files changed, 304 insertions, 70 deletions
diff --git a/css/v2.css b/css/v2.css
index 08407c5f..a199114e 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -846,14 +846,17 @@ table.aliases td.key { padding: 0 5px 0 0; width: auto }
/***** Staff display on VN pages *****/
-.vnstaff { width: 97%; margin: -15px auto 5px auto; justify-content: space-between }
+.vnstaff div { width: 97%; margin: 0 auto 5px auto; justify-content: space-between }
.vnstaff ul { list-style: none; margin: 0 }
.vnstaff-2 ul { width: 100% } .vnstaff-2 { flex-wrap: wrap }
.vnstaff-3 ul { width: 32% }
.vnstaff-4 ul { width: 24% }
-.vnstaff li { white-space: nowrap; overflow-x: hidden; text-overflow: ellipsis; padding-bottom: 1px }
-.vnstaff li.vnstaff_head { font-weight: bold; margin-top: 15px }
-.vnstaff li b.grayedout { margin-left: 10px }
+.vnstaff li { padding-bottom: 1px; padding-left: 10px; }
+.vnstaff li a { display: inline-block; margin-left: -10px }
+.vnstaff li b { padding-left: 10px }
+.vnstaff li.vnstaff_head { font-weight: bold; padding-left: 0 }
+.vnstaff li:not(:first-child).vnstaff_head { margin-top: 15px }
+.vnstaff summary { background: $boxbg; font-weight: bold; width: 100% }
@media(min-width: 0px) { .vnstaff-2{display:flex} .vnstaff-3{display:none} .vnstaff-4{display:none} }
@media(min-width: 850px) { .vnstaff-2{display:flex} .vnstaff-3{display:none} .vnstaff-4{display:none} .vnstaff-2 ul {width:49%} .vnstaff-2{flex-wrap:nowrap} }
@media(min-width:1000px) { .vnstaff-2{display:none} .vnstaff-3{display:flex} .vnstaff-4{display:none} }
diff --git a/elm/User/Edit.elm b/elm/User/Edit.elm
index 447691c6..5fafa7c7 100644
--- a/elm/User/Edit.elm
+++ b/elm/User/Edit.elm
@@ -109,6 +109,9 @@ type PrefMsg
| VNRelLangs (List String)
| VNRelOLang Bool
| VNRelMTL Bool
+ | StaffEdLangs (List String)
+ | StaffEdOLang Bool
+ | StaffEdUnoff Bool
| ProdRel Bool
| Skin String
| Css String
@@ -213,6 +216,9 @@ updatePrefs msg model =
VNRelLangs l->{ model | vnrel_langs = l }
VNRelOLang b->{ model | vnrel_olang = b }
VNRelMTL b -> { model | vnrel_mtl = b }
+ StaffEdLangs l->{ model | staffed_langs = l }
+ StaffEdOLang b->{ model | staffed_olang = b }
+ StaffEdUnoff b->{ model | staffed_unoff = b }
ProdRel b -> { model | prodrelexpand = b }
Skin n -> { model | skin = n }
Css n -> { model | customcss = n }
@@ -425,6 +431,14 @@ view model =
, label [] [ inputCheck "" m.vnrel_olang (Prefs << VNRelOLang), text " Always expand original language" ], br_ 1
, label [] [ inputCheck "" m.vnrel_mtl (Prefs << VNRelMTL ), text " Expand machine translations" ]
]
+ , formField "Staff"
+ [ text "Expand editions for the following languages by default", br_ 1
+ , select [ tabindex 10, multiple True, onInputMultiple (Prefs << StaffEdLangs), style "height" "200px" ]
+ <| List.map (\(k,v) -> option [ value k, selected (List.member k m.staffed_langs) ] [ text v ]) GT.languages
+ , br_ 1
+ , label [] [ inputCheck "" m.staffed_olang (Prefs << StaffEdOLang), text " Always expand original edition" ], br_ 1
+ , label [] [ inputCheck "" m.staffed_unoff (Prefs << StaffEdUnoff), text " Expand unofficial editions" ]
+ ]
, tr [ class "newpart" ] [ td [ colspan 2 ] [ text "Theme" ] ]
, formField "skin::Skin" [ inputSelect "skin" m.skin (Prefs << Skin) [ style "width" "300px" ] GT.skins ]
, formField "css::Custom CSS" [ inputTextArea "css" m.customcss (Prefs << Css) ([ rows 5, cols 60 ] ++ GUE.valPrefsCustomcss) ]
diff --git a/elm/VNEdit.elm b/elm/VNEdit.elm
index 18d1faf2..d711601d 100644
--- a/elm/VNEdit.elm
+++ b/elm/VNEdit.elm
@@ -65,8 +65,10 @@ type alias Model =
, anime : List GVE.RecvAnime
, animeSearch : A.Model GApi.ApiAnimeResult
, image : Img.Image
+ , editions : List GVE.RecvEditions
, staff : List GVE.RecvStaff
- , staffSearch : A.Model GApi.ApiStaffResult
+ -- Search boxes matching the list of editions (n+1), first entry is for the NULL edition.
+ , staffSearch : List (A.Config Msg GApi.ApiStaffResult, A.Model GApi.ApiStaffResult)
, seiyuu : List GVE.RecvSeiyuu
, seiyuuSearch: A.Model GApi.ApiStaffResult
, seiyuuDef : String -- character id for newly added seiyuu
@@ -102,8 +104,9 @@ init d =
, anime = d.anime
, animeSearch = A.init ""
, image = Img.info d.image_info
+ , editions = d.editions
, staff = d.staff
- , staffSearch = A.init ""
+ , staffSearch = (staffConfig Nothing, A.init "") :: List.map (\e -> (staffConfig (Just e.eid), A.init "")) d.editions
, seiyuu = d.seiyuu
, seiyuuSearch= A.init ""
, seiyuuDef = Maybe.withDefault "" <| List.head <| List.map (\c -> c.id) d.chars
@@ -137,8 +140,9 @@ encode model =
, relations = List.map (\v -> { vid = v.vid, relation = v.relation, official = v.official }) model.vns
, anime = List.map (\a -> { aid = a.aid }) model.anime
, image = model.image.id
- , staff = List.map (\s -> { aid = s.aid, role = s.role, note = s.note }) model.staff
- , seiyuu = List.map (\s -> { aid = s.aid, cid = s.cid, note = s.note }) model.seiyuu
+ , editions = model.editions
+ , staff = List.map (\s -> { aid = s.aid, eid = s.eid, note = s.note, role = s.role }) model.staff
+ , seiyuu = List.map (\s -> { aid = s.aid, cid = s.cid, note = s.note }) model.seiyuu
, screenshots = List.map (\(_,i,r) -> { scr = Maybe.withDefault "" i.id, rid = r }) model.screenshots
}
@@ -148,8 +152,12 @@ vnConfig = { wrap = VNSearch, id = "relationadd", source = A.vnSource }
animeConfig : A.Config Msg GApi.ApiAnimeResult
animeConfig = { wrap = AnimeSearch, id = "animeadd", source = A.animeSource False }
-staffConfig : A.Config Msg GApi.ApiStaffResult
-staffConfig = { wrap = StaffSearch, id = "staffadd", source = A.staffSource }
+staffConfig : Maybe Int -> A.Config Msg GApi.ApiStaffResult
+staffConfig eid =
+ { wrap = (StaffSearch eid)
+ , id = "staffadd-" ++ Maybe.withDefault "" (Maybe.map String.fromInt eid)
+ , source = A.staffSource
+ }
seiyuuConfig : A.Config Msg GApi.ApiStaffResult
seiyuuConfig = { wrap = SeiyuuSearch, id = "seiyuuadd", source = A.staffSource }
@@ -185,10 +193,15 @@ type Msg
| ImageSelect
| ImageSelected File
| ImageMsg Img.Msg
+ | EditionAdd
+ | EditionLang Int (Maybe String)
+ | EditionName Int String
+ | EditionOfficial Int Bool
+ | EditionDel Int Int
| StaffDel Int
| StaffRole Int String
| StaffNote Int String
- | StaffSearch (A.Msg GApi.ApiStaffResult)
+ | StaffSearch (Maybe Int) (A.Msg GApi.ApiStaffResult)
| SeiyuuDef String
| SeiyuuDel Int
| SeiyuuChar Int String
@@ -270,14 +283,43 @@ update msg model =
ImageSelected f -> let (nm, nc) = Img.upload Api.Cv f in ({ model | image = nm }, Cmd.map ImageMsg nc)
ImageMsg m -> let (nm, nc) = Img.update m model.image in ({ model | image = nm }, Cmd.map ImageMsg nc)
+ EditionAdd ->
+ let f n acc =
+ case acc of
+ Just x -> Just x
+ Nothing -> if not (List.isEmpty (List.filter (\i -> i.eid == n) model.editions)) then Nothing else Just n
+ newid = List.range 0 500 |> List.foldl f Nothing |> Maybe.withDefault 0
+ in ({ model
+ | editions = model.editions ++ [{ eid = newid, lang = Nothing, name = "", official = True }]
+ , staffSearch = model.staffSearch ++ [(staffConfig (Just newid), A.init "")]
+ }, Cmd.none)
+ EditionDel idx eid ->
+ ({ model
+ | editions = delidx idx model.editions
+ , staffSearch = delidx (idx + 1) model.staffSearch
+ , staff = List.filter (\s -> s.eid /= Just eid) model.staff
+ }, Cmd.none)
+ EditionLang idx v -> ({ model | editions = modidx idx (\s -> { s | lang = v }) model.editions }, Cmd.none)
+ EditionName idx v -> ({ model | editions = modidx idx (\s -> { s | name = v }) model.editions }, Cmd.none)
+ EditionOfficial idx v -> ({ model | editions = modidx idx (\s -> { s | official = v }) model.editions }, Cmd.none)
+
StaffDel idx -> ({ model | staff = delidx idx model.staff }, Cmd.none)
StaffRole idx v -> ({ model | staff = modidx idx (\s -> { s | role = v }) model.staff }, Cmd.none)
StaffNote idx v -> ({ model | staff = modidx idx (\s -> { s | note = v }) model.staff }, Cmd.none)
- StaffSearch m ->
- let (nm, c, res) = A.update staffConfig m model.staffSearch
- in case res of
- Nothing -> ({ model | staffSearch = nm }, c)
- Just s -> ({ model | staffSearch = A.clear nm "", staff = model.staff ++ [{ id = s.id, aid = s.aid, name = s.name, original = s.original, role = "staff", note = "" }] }, c)
+ StaffSearch eid m ->
+ let idx = List.indexedMap Tuple.pair model.editions
+ |> List.filterMap (\(n,e) -> if Just e.eid == eid then Just (n+1) else Nothing)
+ |> List.head |> Maybe.withDefault 0
+ in case List.drop idx model.staffSearch |> List.head of
+ Nothing -> (model, Cmd.none)
+ Just (sconfig, smodel) ->
+ let (nm, c, res) = A.update sconfig m smodel
+ nnm = if res == Nothing then nm else A.clear nm ""
+ nsearch = modidx idx (\(oc,om) -> (oc,nnm)) model.staffSearch
+ nstaff s = [{ id = s.id, aid = s.aid, eid = eid, name = s.name, original = s.original, role = "staff", note = "" }]
+ in case res of
+ Nothing -> ({ model | staffSearch = nsearch }, c)
+ Just s -> ({ model | staffSearch = nsearch, staff = model.staff ++ nstaff s }, c)
SeiyuuDef c -> ({ model | seiyuuDef = c }, Cmd.none)
SeiyuuDel idx -> ({ model | seiyuu = delidx idx model.seiyuu }, Cmd.none)
@@ -334,7 +376,8 @@ isValid model = not
|| not (Img.isValid model.image)
|| List.any (\(_,i,r) -> r == Nothing || not (Img.isValid i)) model.screenshots
|| not (List.isEmpty model.scrQueue)
- || hasDuplicates (List.map (\s -> (s.aid, s.role)) model.staff)
+ || hasDuplicates (List.map (\e -> e.name) model.editions)
+ || hasDuplicates (List.map (\s -> (s.aid, Maybe.withDefault -1 s.eid, s.role)) model.staff)
|| hasDuplicates (List.map (\s -> (s.aid, s.cid)) model.seiyuu)
)
@@ -467,41 +510,65 @@ view model =
staff =
let
- head =
- if List.isEmpty model.staff then [] else [
+ head lst =
+ if List.isEmpty lst then text "" else
thead [] [ tr []
[ td [] []
, td [] [ text "Staff" ]
, td [] [ text "Role" ]
, td [] [ text "Note" ]
, td [] []
- ] ] ]
- foot =
+ ] ]
+ foot searchn lst (sconfig, smodel) =
tfoot [] [ tr [] [ td [] [], td [ colspan 4 ]
- [ br [] []
- , if hasDuplicates (List.map (\s -> (s.aid, s.role)) model.staff)
+ [ text ""
+ , if hasDuplicates (List.map (\(_,s) -> (s.aid, s.role)) lst)
then b [ class "standout" ] [ text "List contains duplicate staff roles.", br [] [] ]
else text ""
- , A.view staffConfig model.staffSearch [placeholder "Add staff..."]
- , text "Can't find the person you're looking for? You can "
- , a [ href "/s/new" ] [ text "create a new entry" ]
- , text ", but "
- , a [ href "/s/all" ] [ text "please check for aliasses first." ]
- , br_ 2
- , text "Some guidelines:"
- , ul []
- [ li [] [ text "Please add major staff only, i.e. people who had a significant and noticable impact on the work." ]
- , li [] [ text "If one person performed several roles, you can add multiple entries with different major roles." ]
+ , A.view sconfig smodel [placeholder "Add staff..."]
+ , if searchn > 0 then text "" else span []
+ [ text "Can't find the person you're looking for? You can "
+ , a [ href "/s/new" ] [ text "create a new entry" ]
+ , text ", but "
+ , a [ href "/s/all" ] [ text "please check for aliasses first." ]
+ , br [] []
+ , text "If one person performed several roles, you can add multiple entries with different major roles."
]
] ] ]
- item n s = tr []
+ item (n,s) = tr []
[ td [ style "text-align" "right" ] [ b [ class "grayedout" ] [ text <| s.id ++ ":" ] ]
, td [] [ a [ href <| "/" ++ s.id ] [ text s.name ] ]
, td [] [ inputSelect "" s.role (StaffRole n) [style "width" "150px" ] GT.creditTypes ]
, td [] [ inputText "" s.note (StaffNote n) (style "width" "300px" :: onInvalid (Invalid Staff) :: GVE.valStaffNote) ]
, td [] [ inputButton "remove" (StaffDel n) [] ]
]
- in table [] <| head ++ [ foot ] ++ List.indexedMap item model.staff
+ edition searchn edi =
+ let eid = Maybe.map (\e -> e.eid) edi
+ lst = List.indexedMap Tuple.pair model.staff |> List.filter (\(_,s) -> s.eid == eid)
+ sch = List.drop searchn model.staffSearch |> List.head
+ in div [style "margin" "0 0 30px 0"]
+ [ Maybe.withDefault (if List.isEmpty model.editions then text "" else h2 [] [ text "Original edition" ])
+ <| Maybe.map (\e -> h2 [] [ text (if e.name == "" then "New edition" else e.name) ]) edi
+ , case edi of
+ Nothing -> text ""
+ Just e ->
+ div [style "margin" "5px 0 0 15px"]
+ [ inputText "" e.name (EditionName (searchn-1)) (placeholder "Edition title" :: style "width" "300px" :: onInvalid (Invalid Staff) :: GVE.valEditionsName)
+ , inputSelect "" e.lang (EditionLang (searchn-1)) [style "width" "150px"]
+ ((Nothing, "Original language") :: List.map (\(i,l) -> (Just i, l)) GT.languages)
+ , text " ", label [] [ inputCheck "" e.official (EditionOfficial (searchn-1)), text " official" ]
+ , inputButton "remove edition" (EditionDel (searchn-1) e.eid) [style "margin-left" "30px"]
+ ]
+ , table [style "margin" "5px 0 0 15px"]
+ <| head lst
+ :: Maybe.withDefault (text "") (Maybe.map (foot searchn lst) sch)
+ :: List.map item lst
+ ]
+ in edition 0 Nothing
+ :: List.indexedMap (\n e -> edition (n+1) (Just e)) model.editions
+ ++ [ br [] [], inputButton "Add edition" EditionAdd [] ]
+
+
cast =
let
@@ -679,7 +746,7 @@ view model =
]
, div [ class "mainbox", classList [("hidden", model.tab /= General && model.tab /= All)] ] [ h1 [] [ text "General info" ], table [ class "formtable" ] geninfo ]
, div [ class "mainbox", classList [("hidden", model.tab /= Image && model.tab /= All)] ] [ h1 [] [ text "Image" ], image ]
- , div [ class "mainbox", classList [("hidden", model.tab /= Staff && model.tab /= All)] ] [ h1 [] [ text "Staff" ], staff ]
+ , div [ class "mainbox", classList [("hidden", model.tab /= Staff && model.tab /= All)] ] ( h1 [] [ text "Staff" ] :: staff )
, div [ class "mainbox", classList [("hidden", model.tab /= Cast && model.tab /= All)] ] [ h1 [] [ text "Cast" ], cast ]
, div [ class "mainbox", classList [("hidden", model.tab /= Screenshots && model.tab /= All)] ] [ h1 [] [ text "Screenshots" ], screenshots ]
, div [ class "mainbox" ] [ fieldset [ class "submit" ]
diff --git a/lib/VNDB/Types.pm b/lib/VNDB/Types.pm
index 684c3602..9f61fe2c 100644
--- a/lib/VNDB/Types.pm
+++ b/lib/VNDB/Types.pm
@@ -165,11 +165,14 @@ hash PRODUCER_TYPE =>
# SQL: ENUM credit_type
hash CREDIT_TYPE =>
scenario => 'Scenario',
+ director => 'Director',
chardesign => 'Character design',
art => 'Artist',
music => 'Composer',
songs => 'Vocals',
- director => 'Director',
+ translator => 'Translator',
+ editor => 'Editor',
+ qa => 'Quality assurance',
staff => 'Staff';
diff --git a/lib/VNWeb/Staff/Page.pm b/lib/VNWeb/Staff/Page.pm
index 8d143fcc..d1ee5bf3 100644
--- a/lib/VNWeb/Staff/Page.pm
+++ b/lib/VNWeb/Staff/Page.pm
@@ -74,12 +74,13 @@ sub _roles_ {
my %alias = map +($_->{aid}, $_), $s->{alias}->@*;
my $roles = tuwf->dbAlli(q{
- SELECT v.id, vs.aid, vs.role, vs.note, v.c_released, v.title, v.alttitle
+ SELECT v.id, vs.aid, vs.role, vs.note, ve.name, ve.official, v.c_released, v.title, v.alttitle
FROM vn_staff vs
JOIN vnt v ON v.id = vs.id
+ LEFT JOIN vn_editions ve ON ve.id = vs.id AND ve.eid = vs.eid
WHERE vs.aid IN}, [ keys %alias ], q{
AND NOT v.hidden
- ORDER BY v.c_released ASC, v.title ASC, vs.role ASC
+ ORDER BY v.c_released ASC, v.title ASC, ve.lang NULLS FIRST, ve.name NULLS FIRST, vs.role ASC
});
return if !@$roles;
enrich_ulists_widget $roles;
@@ -101,6 +102,8 @@ sub _roles_ {
td_ class => 'tc_ulist', sub { ulists_widget_ $v if !$vns{$v->{id}}++ } if auth;
td_ class => 'tc1', sub {
a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 60;
+ txt_ " $v->{name}" if $v->{name} && $v->{official};
+ b_ class => 'grayedout', " $v->{name}" if $v->{name} && !$v->{official};
};
td_ class => 'tc2', sub { rdate_ $v->{c_released} };
td_ class => 'tc3', $CREDIT_TYPE{$v->{role}};
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index cf544179..c33f04e7 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -44,6 +44,9 @@ my $FORM = {
vnrel_langs => { type => 'array', values => { enum => \%LANGUAGE }, sort => 'str', unique => 1 },
vnrel_olang => { anybool => 1 },
vnrel_mtl => { anybool => 1 },
+ staffed_langs => { type => 'array', values => { enum => \%LANGUAGE }, sort => 'str', unique => 1 },
+ staffed_olang => { anybool => 1 },
+ staffed_unoff => { anybool => 1 },
skin => { enum => skins },
customcss => { required => 0, default => '', maxlength => 2000 },
@@ -93,7 +96,8 @@ TUWF::get qr{/$RE{uid}/edit}, sub {
$u->{prefs} = $u->{id} eq auth->uid || auth->permUsermod ?
tuwf->dbRowi(
'SELECT max_sexual, max_violence, traits_sexual, tags_all, tags_cont, tags_ero, tags_tech, prodrelexpand
- , spoilers, vnrel_langs::text[], vnrel_olang, vnrel_mtl, skin, customcss, title_langs, alttitle_langs
+ , vnrel_langs::text[], vnrel_olang, vnrel_mtl, staffed_langs::text[], staffed_olang, staffed_unoff
+ , spoilers, skin, customcss, title_langs, alttitle_langs
, nodistract_noads, nodistract_nofancy, support_enabled, uniname, pubskin_enabled
FROM users u JOIN users_prefs up ON up.id = u.id WHERE u.id =', \$u->{id}
) : undef;
@@ -101,6 +105,7 @@ TUWF::get qr{/$RE{uid}/edit}, sub {
$u->{prefs}{email} = _getmail $u->{id};
$u->{prefs}{skin} ||= config->{skin_default};
$u->{prefs}{vnrel_langs} ||= [ keys %LANGUAGE ];
+ $u->{prefs}{staffed_langs} ||= [ keys %LANGUAGE ];
$u->{prefs}{title_langs} = langpref_parse($u->{prefs}{title_langs}) // $DEFAULT_TITLE_LANGS;
$u->{prefs}{alttitle_langs} = langpref_parse($u->{prefs}{alttitle_langs}) // $DEFAULT_ALTTITLE_LANGS;
$u->{prefs}{traits} = tuwf->dbAlli('SELECT u.tid, t.name, g.name AS "group" FROM users_traits u JOIN traits t ON t.id = u.tid LEFT JOIN traits g ON g.id = t.group WHERE u.id =', \$u->{id}, 'ORDER BY g.order, t.name');
@@ -143,10 +148,12 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
$p->{title_langs} = undef if $p->{title_langs} && ($p->{title_langs} eq langpref_fmt($DEFAULT_TITLE_LANGS) || $p->{title_langs} eq '[]');
$p->{alttitle_langs} = undef if $p->{alttitle_langs} && $p->{alttitle_langs} eq langpref_fmt $DEFAULT_ALTTITLE_LANGS;
$p->{vnrel_langs} = $p->{vnrel_langs}->@* == keys %LANGUAGE ? undef : '{'.join(',',$p->{vnrel_langs}->@*).'}';
+ $p->{staffed_langs} = $p->{staffed_langs}->@* == keys %LANGUAGE ? undef : '{'.join(',',$p->{staffed_langs}->@*).'}';
$set{$_} = $p->{$_} for qw/nodistract_noads nodistract_nofancy support_enabled uniname pubskin_enabled/;
$setp{$_} = $p->{$_} for qw/
max_sexual max_violence traits_sexual tags_all tags_cont tags_ero tags_tech prodrelexpand
- vnrel_langs vnrel_olang vnrel_mtl spoilers skin customcss title_langs alttitle_langs
+ vnrel_langs vnrel_olang vnrel_mtl staffed_langs staffed_olang staffed_unoff
+ spoilers skin customcss title_langs alttitle_langs
/;
tuwf->dbExeci('DELETE FROM users_traits WHERE id =', \$data->{id});
tuwf->dbExeci('INSERT INTO users_traits', { id => $data->{id}, tid => $_->{tid} }) for $p->{traits}->@*;
diff --git a/lib/VNWeb/VN/Edit.pm b/lib/VNWeb/VN/Edit.pm
index 0cacbb0f..09608ef7 100644
--- a/lib/VNWeb/VN/Edit.pm
+++ b/lib/VNWeb/VN/Edit.pm
@@ -33,8 +33,15 @@ my $FORM = {
} },
image => { required => 0, vndbid => 'cv' },
image_info => { _when => 'out', required => 0, type => 'hash', keys => $VNWeb::Elm::apis{ImageResult}[0]{aoh} },
- staff => { sort_keys => ['aid','role'], aoh => {
+ editions => { sort_keys => 'eid', aoh => {
+ eid => { uint => 1, max => 500 },
+ lang => { required => 0, language => 1 },
+ name => {},
+ official => { anybool => 1 },
+ } },
+ staff => { sort_keys => ['aid','eid','role'], aoh => {
aid => { id => 1 },
+ eid => { required => 0, uint => 1 },
role => { enum => \%CREDIT_TYPE },
note => { required => 0, default => '', maxlength => 250 },
id => { _when => 'out', vndbid => 's' },
@@ -100,6 +107,8 @@ TUWF::get qr{/$RE{vrev}/edit} => sub {
$e->{staff} = [ grep $_->{id}, $e->{staff}->@* ];
$e->{seiyuu} = [ grep $_->{id}, $e->{seiyuu}->@* ];
+ $e->{editions} = [ sort { ($a->{lang}||'') cmp ($b->{lang}||'') || $b->{official} cmp $a->{official} || $a->{name} cmp $b->{name} } $e->{editions}->@* ];
+
$e->{releases} = releases_by_vn $e->{id};
$e->{chars} = tuwf->dbAlli('
@@ -148,6 +157,10 @@ elm_api VNEdit => $FORM_OUT, $FORM_IN, sub {
validate_dbid 'SELECT aid FROM staff_alias WHERE aid IN', map $_->{aid}, $data->{staff}->@*;
validate_dbid 'SELECT aid FROM staff_alias WHERE aid IN', map $_->{aid}, $data->{seiyuu}->@*;
+ # Drop unused staff editions
+ my %editions = map defined $_->{eid} ? +($_->{eid},1) : (), $data->{staff}->@*;
+ $data->{editions} = [ grep $editions{$_->{eid}}, $data->{editions}->@* ];
+
$data->{relations} = [] if $data->{hidden};
validate_dbid 'SELECT id FROM vn WHERE id IN', map $_->{vid}, $data->{relations}->@*;
die "Relation with self" if grep $_->{vid} eq $e->{id}, $data->{relations}->@*;
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 77e4883b..e140cb59 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -59,7 +59,8 @@ sub enrich_item {
$v->{relations} = [ sort { idcmp($a->{vid}, $b->{vid}) } $v->{relations}->@* ];
$v->{anime} = [ sort { $a->{aid} <=> $b->{aid} } $v->{anime}->@* ];
- $v->{staff} = [ sort { $a->{aid} <=> $b->{aid} || $a->{role} cmp $b->{role} } $v->{staff}->@* ];
+ $v->{editions} = [ sort { ($a->{lang}||'') cmp ($b->{lang}||'') || $b->{official} cmp $a->{official} || $a->{name} cmp $b->{name} } $v->{editions}->@* ];
+ $v->{staff} = [ sort { ($a->{eid}//-1) <=> ($b->{eid}//-1) || $a->{aid} <=> $b->{aid} || $a->{role} cmp $b->{role} } $v->{staff}->@* ];
$v->{seiyuu} = [ sort { $a->{aid} <=> $b->{aid} || idcmp($a->{cid}, $b->{cid}) || $a->{note} cmp $b->{note} } $v->{seiyuu}->@* ];
$v->{screenshots} = [ sort { idcmp($a->{scr}{id}, $b->{scr}{id}) } $v->{screenshots}->@* ];
}
@@ -75,6 +76,25 @@ sub og {
}
+sub prefs {
+ state $default = {
+ vnrel_langs => \%LANGUAGE, vnrel_olang => 1, vnrel_mtl => 0,
+ staffed_langs => \%LANGUAGE, staffed_olang => 1, staffed_unoff => 0,
+ };
+ tuwf->req->{vnpage_prefs} //= auth ? do {
+ my $v = tuwf->dbRowi('
+ SELECT vnrel_langs::text[], vnrel_olang, vnrel_mtl
+ , staffed_langs::text[], staffed_olang, staffed_unoff
+ FROM users_prefs
+ WHERE id =', \auth->uid
+ );
+ $v->{vnrel_langs} = $v->{vnrel_langs} ? { map +($_,1), $v->{langs}->@* } : \%LANGUAGE;
+ $v->{staffed_langs} = $v->{staffed_langs} ? { map +($_,1), $v->{langs}->@* } : \%LANGUAGE;
+ $v
+ } : $default;
+}
+
+
# The voting and review options are hidden if nothing has been released yet.
sub canvote {
my($v) = @_;
@@ -96,7 +116,15 @@ sub rev_ {
[ desc => 'Description' ],
[ devstatus => 'Development status',fmt => \%DEVSTATUS ],
[ length => 'Length', fmt => \%VN_LENGTH ],
+ [ editions => 'Editions', fmt => sub {
+ abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, '' if $_->{lang};
+ txt_ $_->{name};
+ b_ class => 'grayedout', ' (unofficial)' if !$_->{official};
+ }],
[ staff => 'Credits', fmt => sub {
+ my $eid = $_->{eid};
+ my $e = defined $eid && (grep $eid == $_->{eid}, $_[0]{editions}->@*)[0];
+ txt_ "[$e->{name}] " if $e;
a_ href => "/$_->{sid}", title => $_->{original}||$_->{name}, $_->{name} if $_->{sid};
b_ class => 'grayedout', '[removed alias]' if !$_->{sid};
txt_ " [$CREDIT_TYPE{$_->{role}}]";
@@ -498,8 +526,6 @@ sub tabs_ {
sub releases_ {
my($v) = @_;
- # TODO: Organize a long list of releases a bit better somehow? Collapsable language sections?
-
enrich_release $v->{releases};
$v->{releases} = sort_releases $v->{releases};
@@ -512,18 +538,13 @@ sub releases_ {
}
$langrel{$_} = min map $_->{released}, $lang{$_}->@* for keys %lang;
my @lang = sort { $langrel{$a} <=> $langrel{$b} || ($b eq $v->{olang}) cmp ($a eq $v->{olang}) || $a cmp $b } keys %lang;
-
- my $pref = auth ? do {
- my $v = tuwf->dbRowi('SELECT vnrel_langs::text[] AS langs, vnrel_olang AS olang, vnrel_mtl AS mtl FROM users_prefs WHERE id =', \auth->uid);
- $v->{langs} = $v->{langs} ? { map +($_,1), $v->{langs}->@* } : \%LANGUAGE;
- $v
- } : { langs => \%LANGUAGE, olang => 1, mtl => 0 };
+ my $pref = prefs;
my sub lang_ {
my($lang) = @_;
my $ropt = { id => $lang, lang => $lang };
my $mtl = $langmtl{$lang};
- my $open = ($pref->{olang} && $lang eq $v->{olang} && !$mtl) || ($pref->{langs}{$lang} && (!$mtl || $pref->{mtl}));
+ my $open = ($pref->{vnrel_olang} && $lang eq $v->{olang} && !$mtl) || ($pref->{vnrel_langs}{$lang} && (!$mtl || $pref->{vnrel_mtl}));
details_ open => $open?'open':undef, sub {
summary_ $mtl ? (class => 'mtl') : (), sub {
abbr_ class => "icons lang $lang".($mtl?' mtl':''), title => $LANGUAGE{$lang}, '';
@@ -547,8 +568,8 @@ sub releases_ {
}
-sub staff_ {
- my($v) = @_;
+sub staff_cols_ {
+ my($lst) = @_;
# XXX: The staff listing is included in the page 3 times, for 3 different
# layouts. A better approach to get the same layout is to add the boxes to
@@ -560,7 +581,7 @@ sub staff_ {
# Step 1: Get a list of 'boxes'; Each 'box' represents a role with a list of staff entries.
# @boxes = [ $height, $roleimp, $html ]
my %roles;
- push $roles{$_->{role}}->@*, $_ for grep $_->{sid}, $v->{staff}->@*;
+ push $roles{$_->{role}}->@*, $_ for grep $_->{sid}, @$lst;
my $i=0;
my @boxes =
sort { $b->[0] <=> $a->[0] || $a->[1] <=> $b->[1] }
@@ -569,7 +590,7 @@ sub staff_ {
li_ class => 'vnstaff_head', $CREDIT_TYPE{$_};
li_ sub {
a_ href => "/$_->{sid}", title => $_->{original}||$_->{name}, $_->{name};
- b_ title => $_->{note}, class => 'grayedout', $_->{note} if $_->{note};
+ b_ class => 'grayedout', $_->{note} if $_->{note};
} for sort { $a->{name} cmp $b->{name} } $roles{$_}->@*;
}
], grep $roles{$_}, keys %CREDIT_TYPE;
@@ -591,14 +612,45 @@ sub staff_ {
@$c = sort { $a->[1] <=> $b->[1] } @$c;
}
- div_ class => 'mainbox', id => 'staff', 'data-mainbox-summarize' => 200, sub {
+ div_ class => sprintf('vnstaff-%d', scalar @$_), sub {
+ ul_ sub {
+ lit_ $_->[2] for $_->[2]->@*;
+ } for @$_
+ } for @cols;
+}
+
+
+sub staff_ {
+ my($v) = @_;
+ return if !$v->{staff}->@*;
+
+ my %staff;
+ push $staff{ $_->{eid} // '' }->@*, $_ for $v->{staff}->@*;
+ my $pref = prefs;
+
+ div_ class => 'mainbox vnstaff', id => 'staff', sub {
h1_ 'Staff';
- div_ class => sprintf('vnstaff vnstaff-%d', scalar @$_), sub {
- ul_ sub {
- lit_ $_->[2] for $_->[2]->@*;
- } for @$_
- } for @cols;
- } if $v->{staff}->@*;
+ if (!$v->{editions}->@*) {
+ staff_cols_ $v->{staff};
+ return;
+ }
+ for my $e (undef, $v->{editions}->@*) {
+ my $lst = $staff{ $e ? $e->{eid} : '' };
+ next if !$lst;
+ my $lang = ($e && $e->{lang}) || $v->{olang};
+ my $unoff = $e && !$e->{official};
+ my $open = ($pref->{staffed_olang} && !$e) || ($pref->{staffed_langs}{$lang} && (!$unoff || $pref->{staffed_mtl}));
+ details_ open => $open?'open':undef, sub {
+ summary_ sub {
+ abbr_ class => "icons lang $e->{lang}", title => $LANGUAGE{$e->{lang}}, '' if $e && $e->{lang};
+ txt_ 'Original edition' if !$e;
+ txt_ $e->{name} if $e;
+ b_ class => 'grayedout', ' (unofficial)' if $unoff;
+ };
+ staff_cols_ $lst;
+ };
+ }
+ };
}
diff --git a/sql/perms.sql b/sql/perms.sql
index 816a45c6..786c0ebc 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -88,6 +88,8 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON users_traits TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site;
GRANT SELECT, INSERT ON vn_anime_hist TO vndb_site;
+GRANT SELECT, INSERT, DELETE ON vn_editions TO vndb_site;
+GRANT SELECT, INSERT ON vn_editions_hist TO vndb_site;
GRANT SELECT, INSERT ON vn_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON vn_length_votes TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_relations TO vndb_site;
diff --git a/sql/schema.sql b/sql/schema.sql
index 155a2881..e6fc7874 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -57,7 +57,7 @@ CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe',
CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
-CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
+CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'translator', 'editor', 'qa', 'staff');
CREATE TYPE cup_size AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
@@ -1091,7 +1091,10 @@ CREATE TABLE users_prefs (
prodrelexpand boolean NOT NULL DEFAULT true,
vnrel_langs language[], -- NULL meaning "show all languages"
vnrel_olang boolean NOT NULL DEFAULT true,
- vnrel_mtl boolean NOT NULL DEFAULT false
+ vnrel_mtl boolean NOT NULL DEFAULT false,
+ staffed_langs language[],
+ staffed_olang boolean NOT NULL DEFAULT true,
+ staffed_unoff boolean NOT NULL DEFAULT false
);
-- Additional fields for the 'users' table, but with some protected columns.
@@ -1190,6 +1193,26 @@ CREATE TABLE vn_anime_hist (
PRIMARY KEY(chid, aid)
);
+-- vn_editions
+CREATE TABLE vn_editions (
+ id vndbid NOT NULL, -- [pub]
+ lang language, -- [pub]
+ eid smallint NOT NULL, -- [pub] (not stable across revisions)
+ official boolean NOT NULL DEFAULT TRUE, -- [pub]
+ name text NOT NULL, -- [pub]
+ PRIMARY KEY(id, eid)
+);
+
+-- vn_editions_hist
+CREATE TABLE vn_editions_hist (
+ chid integer NOT NULL,
+ lang language,
+ eid smallint NOT NULL,
+ official boolean NOT NULL DEFAULT TRUE,
+ name text NOT NULL,
+ PRIMARY KEY(chid, eid)
+);
+
-- vn_relations
CREATE TABLE vn_relations (
id vndbid NOT NULL, -- [pub]
@@ -1250,7 +1273,7 @@ CREATE TABLE vn_staff (
aid integer NOT NULL, -- [pub] staff_alias.aid
role credit_type NOT NULL DEFAULT 'staff', -- [pub]
note varchar(250) NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY (id, aid, role)
+ eid smallint -- [pub]
);
-- vn_staff_hist
@@ -1259,7 +1282,7 @@ CREATE TABLE vn_staff_hist (
aid integer NOT NULL, -- See note at vn_seiyuu_hist.aid
role credit_type NOT NULL DEFAULT 'staff',
note varchar(250) NOT NULL DEFAULT '',
- PRIMARY KEY (chid, aid, role)
+ eid smallint
);
-- vn_titles
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 176657e8..c4cd3462 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -37,6 +37,8 @@ CREATE INDEX vn_image ON vn (image);
CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
+CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, COALESCE(eid,-1::smallint), aid, role);
+CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, COALESCE(eid,-1::smallint), aid, role);
CREATE INDEX vn_staff_aid ON vn_staff (aid);
CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid);
CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid);
@@ -170,9 +172,9 @@ ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_eid_fkey FOREIGN KEY (id,eid) REFERENCES vn_editions (id,eid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_eid_fkey FOREIGN KEY (chid,eid) REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_titles ADD CONSTRAINT vn_titles_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_titles_hist ADD CONSTRAINT vn_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
diff --git a/util/dbdump.pl b/util/dbdump.pl
index f749ed72..01c1ba98 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -101,12 +101,14 @@ my %tables = (
.' OR id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private)' },
vn => { where => 'NOT hidden' },
vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
+ vn_editions => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_screenshots => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_seiyuu => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)'
.' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
.' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
- vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
+ vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
+ , order => 'id, eid, aid, role' },
vn_titles => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden) AND NOT private'
, order => 'vid, uid' },
diff --git a/util/updates/2022-08-25-staff-editions.sql b/util/updates/2022-08-25-staff-editions.sql
new file mode 100644
index 00000000..d5a731e5
--- /dev/null
+++ b/util/updates/2022-08-25-staff-editions.sql
@@ -0,0 +1,43 @@
+ALTER TYPE credit_type ADD VALUE 'translator' AFTER 'director';
+ALTER TYPE credit_type ADD VALUE 'editor' AFTER 'translator';
+ALTER TYPE credit_type ADD VALUE 'qa' AFTER 'editor';
+
+CREATE TABLE vn_editions (
+ id vndbid NOT NULL, -- [pub]
+ lang language, -- [pub]
+ eid smallint NOT NULL, -- [pub] (not stable across entry revisions)
+ official boolean NOT NULL DEFAULT TRUE, -- [pub]
+ name text NOT NULL, -- [pub]
+ PRIMARY KEY(id, eid)
+);
+
+CREATE TABLE vn_editions_hist (
+ chid integer NOT NULL,
+ lang language,
+ eid smallint NOT NULL,
+ official boolean NOT NULL DEFAULT TRUE,
+ name text NOT NULL,
+ PRIMARY KEY(chid, eid)
+);
+
+ALTER TABLE vn_staff ADD COLUMN eid smallint;
+ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_pkey;
+CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, COALESCE(eid,-1::smallint), aid, role);
+
+ALTER TABLE vn_staff_hist ADD COLUMN eid smallint;
+ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_pkey;
+CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, COALESCE(eid,-1::smallint), aid, role);
+
+ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_id_fkey;
+ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_chid_fkey;
+
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_eid_fkey FOREIGN KEY (id,eid) REFERENCES vn_editions (id,eid) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_eid_fkey FOREIGN KEY (chid,eid) REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE users_prefs
+ ADD COLUMN staffed_langs language[],
+ ADD COLUMN staffed_olang boolean NOT NULL DEFAULT true,
+ ADD COLUMN staffed_unoff boolean NOT NULL DEFAULT false;
+
+\i sql/editfunc.sql
+\i sql/perms.sql