diff options
author | Yorhel <git@yorhel.nl> | 2021-05-21 13:07:06 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-05-21 13:07:08 +0200 |
commit | 09062eaba0297123eaf298827c803db5a5fe0e2e (patch) | |
tree | c19d133b0f05f80ca586f4196e4e8153fd2bb195 | |
parent | 892af2bbbc9c7b6675708bf01f0ebd304014fa0f (diff) |
Tags/Traits: Add main/primary flag to parents list
This way there is always a single canonical path for each tag/trait,
which fixes the problem with traits that could belong to multiple groups
yet you couldn't control which one was selected, and this also removes
duplication in the VN->tags tab, which now doesn't have to non-canonical
tag paths.
-rw-r--r-- | elm/TagEdit.elm | 14 | ||||
-rw-r--r-- | elm/TraitEdit.elm | 14 | ||||
-rw-r--r-- | lib/VNWeb/TT/Lib.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/TT/TagEdit.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/TT/TagPage.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/TT/TraitEdit.pm | 13 | ||||
-rw-r--r-- | lib/VNWeb/TT/TraitPage.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 2 | ||||
-rw-r--r-- | sql/schema.sql | 4 | ||||
-rwxr-xr-x | util/dbdump.pl | 4 | ||||
-rw-r--r-- | util/updates/2021-05-21-tt-primary-parent.sql | 17 |
11 files changed, 65 insertions, 21 deletions
diff --git a/elm/TagEdit.elm b/elm/TagEdit.elm index 9cf44890..7a342a6f 100644 --- a/elm/TagEdit.elm +++ b/elm/TagEdit.elm @@ -95,7 +95,7 @@ encode m = , searchable = m.searchable , applicable = m.applicable , defaultspoil = m.defaultspoil - , parents = List.map (\l -> {parent=l.parent}) m.parents + , parents = List.map (\l -> {parent=l.parent, main=l.main}) m.parents , wipevotes = m.wipevotes , merge = List.map (\l -> {id=l.id}) m.merge } @@ -110,6 +110,7 @@ type Msg | DefaultSpoil Int | Description TP.Msg | Editsum Editsum.Msg + | ParentMain Int Bool | ParentDel Int | ParentSearch (A.Msg GApi.ApiTagResult) | WipeVotes Bool @@ -132,7 +133,11 @@ update msg model = Description m -> let (nm,nc) = TP.update m model.description in ({ model | description = nm }, Cmd.map Description nc) Editsum m -> let (nm,nc) = Editsum.update m model.editsum in ({ model | editsum = nm }, Cmd.map Editsum nc) - ParentDel i -> ({ model | parents = delidx i model.parents }, Cmd.none) + ParentMain i _-> ({ model | parents = List.indexedMap (\n p -> { p | main = i == n }) model.parents }, Cmd.none) + ParentDel i -> + let np = delidx i model.parents + nnp = if List.any (\p -> p.main) np then np else List.indexedMap (\n p -> { p | main = n == 0 }) np + in ({ model | parents = nnp }, Cmd.none) ParentSearch m -> let (nm, c, res) = A.update parentConfig m model.parentAdd in case res of @@ -140,7 +145,7 @@ update msg model = Just p -> if List.any (\e -> e.parent == p.id) model.parents then ({ model | parentAdd = nm }, c) - else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ parent = p.id, name = p.name}] }, c) + else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ parent = p.id, main = List.isEmpty model.parents, name = p.name}] }, c) MergeDel i -> ({ model | merge = delidx i model.merge }, Cmd.none) MergeSearch m -> @@ -179,7 +184,7 @@ view model = , formField "" [ label [] [ inputCheck "" model.searchable Searchable, text " Searchable (people can use this tag to find VNs)" ] ] , formField "" [ label [] [ inputCheck "" model.applicable Applicable, text " Applicable (people can apply this tag to VNs)" ] ] , formField "cat::Category" [ inputSelect "cat" model.cat Cat GTE.valCat tagCategories ] - , formField "defaultspoil::Default spoiler level" [ inputSelect "defaultspoil" model.defaultspoil DefaultSpoil GTE.valDefaultspoil + , formField "defaultspoil::Default spoiler level" [ inputSelect "defaultspoil" model.defaultspoil DefaultSpoil GTE.valDefaultspoil [ (0, "No spoiler") , (1, "Minor spoiler") , (2, "Major spoiler") @@ -194,6 +199,7 @@ view model = [ table [ class "compact" ] <| List.indexedMap (\i p -> tr [] [ td [ style "text-align" "right" ] [ b [ class "grayedout" ] [ text <| p.parent ++ ":" ] ] , td [] [ a [ href <| "/" ++ p.parent ] [ text p.name ] ] + , td [] [ label [] [ inputRadio "parentprimary" p.main (ParentMain i), text " primary" ] ] , td [] [ inputButton "remove" (ParentDel i) [] ] ] ) model.parents diff --git a/elm/TraitEdit.elm b/elm/TraitEdit.elm index d8866f25..f19c4b48 100644 --- a/elm/TraitEdit.elm +++ b/elm/TraitEdit.elm @@ -87,7 +87,7 @@ encode m = , searchable = m.searchable , applicable = m.applicable , defaultspoil = m.defaultspoil - , parents = List.map (\l -> {parent=l.parent}) m.parents + , parents = List.map (\l -> {parent=l.parent, main=l.main}) m.parents , order = m.order } @@ -101,6 +101,7 @@ type Msg | DefaultSpoil Int | Description TP.Msg | Editsum Editsum.Msg + | ParentMain Int Bool | ParentDel Int | ParentSearch (A.Msg GApi.ApiTraitResult) | Order String @@ -121,7 +122,11 @@ update msg model = Description m -> let (nm,nc) = TP.update m model.description in ({ model | description = nm }, Cmd.map Description nc) Editsum m -> let (nm,nc) = Editsum.update m model.editsum in ({ model | editsum = nm }, Cmd.map Editsum nc) - ParentDel i -> ({ model | parents = delidx i model.parents }, Cmd.none) + ParentMain i _-> ({ model | parents = List.indexedMap (\n p -> { p | main = i == n }) model.parents }, Cmd.none) + ParentDel i -> + let np = delidx i model.parents + nnp = if List.any (\p -> p.main) np then np else List.indexedMap (\n p -> { p | main = n == 0 }) np + in ({ model | parents = nnp }, Cmd.none) ParentSearch m -> let (nm, c, res) = A.update parentConfig m model.parentAdd in case res of @@ -129,7 +134,7 @@ update msg model = Just p -> if List.any (\e -> e.parent == p.id) model.parents then ({ model | parentAdd = nm }, c) - else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ parent = p.id, name = p.name, group = p.group_name }] }, c) + else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ parent = p.id, main = List.isEmpty model.parents, name = p.name, group = p.group_name }] }, c) Submit -> ({ model | state = Api.Loading }, GTE.send (encode model) Submitted) Submitted (GApi.DupNames l) -> ({ model | dupNames = l, state = Api.Normal }, Cmd.none) @@ -161,7 +166,7 @@ view model = , formField "" [ label [] [ inputCheck "" model.searchable Searchable, text " Searchable (people can use this trait to find characters)" ] ] , formField "" [ label [] [ inputCheck "" model.applicable Applicable, text " Applicable (people can apply this trait to characters)" ] ] , formField "" [ label [] [ inputCheck "" model.sexual Sexual, text " Indicates sexual content" ] ] - , formField "defaultspoil::Default spoiler level" [ inputSelect "defaultspoil" model.defaultspoil DefaultSpoil GTE.valDefaultspoil + , formField "defaultspoil::Default spoiler level" [ inputSelect "defaultspoil" model.defaultspoil DefaultSpoil GTE.valDefaultspoil [ (0, "No spoiler") , (1, "Minor spoiler") , (2, "Major spoiler") @@ -179,6 +184,7 @@ view model = [ Maybe.withDefault (text "") <| Maybe.map (\g -> b [ class "grayedout" ] [ text (g ++ " / ") ]) p.group , a [ href <| "/" ++ p.parent ] [ text p.name ] ] + , td [] [ label [] [ inputRadio "parentprimary" p.main (ParentMain i), text " primary" ] ] , td [] [ inputButton "remove" (ParentDel i) [] ] ] ) model.parents diff --git a/lib/VNWeb/TT/Lib.pm b/lib/VNWeb/TT/Lib.pm index cb0cc074..d40f5d46 100644 --- a/lib/VNWeb/TT/Lib.pm +++ b/lib/VNWeb/TT/Lib.pm @@ -74,11 +74,11 @@ sub parents_ { my %t; my $table = $type eq 'g' ? 'tags' : 'traits'; push $t{$_->{child}}->@*, $_ for tuwf->dbAlli(" - WITH RECURSIVE p(id,child,name) AS ( - SELECT id, ", \$t->{id}, "::vndbid, name FROM $table WHERE id IN", [ map $_->{parent}, $t->{parents}->@* ], " + WITH RECURSIVE p(id,child,name,main) AS ( + SELECT t.id, tp.id, t.name, tp.main FROM ${table}_parents tp JOIN $table t ON t.id = tp.parent WHERE tp.id =", \$t->{id}, " UNION - SELECT t.id, p.id, t.name FROM p JOIN ${table}_parents tp ON tp.id = p.id JOIN $table t ON t.id = tp.parent - ) SELECT * FROM p ORDER BY name + SELECT t.id, p.id, t.name, (p.main and tp.main) FROM p JOIN ${table}_parents tp ON tp.id = p.id JOIN $table t ON t.id = tp.parent + ) SELECT * FROM p ORDER BY main DESC, name ")->@*; my sub rec { diff --git a/lib/VNWeb/TT/TagEdit.pm b/lib/VNWeb/TT/TagEdit.pm index 1b4a86df..be4b9964 100644 --- a/lib/VNWeb/TT/TagEdit.pm +++ b/lib/VNWeb/TT/TagEdit.pm @@ -15,6 +15,7 @@ my $FORM = { defaultspoil => { uint => 1, range => [0,2] }, parents => { aoh => { parent => { vndbid => 'g' }, + main => { anybool => 1 }, name => { _when => 'out' }, } }, wipevotes => { _when => 'in', anybool => 1 }, @@ -60,7 +61,7 @@ TUWF::get qr{/(?:$RE{gid}/add|g/new)}, sub { my $e = elm_empty($FORM_OUT); $e->{authmod} = auth->permTagmod; if($id) { - $e->{parents} = [{ parent => $g->{id}, name => $g->{name} }]; + $e->{parents} = [{ parent => $g->{id}, main => 1, name => $g->{name} }]; $e->{cat} = $g->{cat}; } @@ -112,6 +113,7 @@ elm_api TagEdit => $FORM_OUT, $FORM_IN, sub { $new ? () : sql('id NOT IN(WITH RECURSIVE t(id) AS (SELECT', \$data->{id}, '::vndbid UNION SELECT tp.id FROM tags_parents tp JOIN t ON t.id = tp.parent) SELECT id FROM t)'), sql 'id IN', $_[0] }, map $_->{parent}, $data->{parents}->@*; + die "No or multiple primary parents" if $data->{parents}->@* && 1 != grep $_->{main}, $data->{parents}->@*; $data->{description} = bb_subst_links($data->{description}); diff --git a/lib/VNWeb/TT/TagPage.pm b/lib/VNWeb/TT/TagPage.pm index 819a6bd1..e7b63f31 100644 --- a/lib/VNWeb/TT/TagPage.pm +++ b/lib/VNWeb/TT/TagPage.pm @@ -11,6 +11,7 @@ sub rev_ { my($t) = @_; sub enrich_item { enrich_merge parent => 'SELECT id AS parent, name FROM tags WHERE id IN', $_[0]{parents}; + $_[0]{parents} = [ sort { $a->{name} cmp $b->{name} || $a->{parent} <=> $b->{parent} } $_[0]{parents}->@* ]; } enrich_item $t; revision_ $t, \&enrich_item, @@ -21,7 +22,7 @@ sub rev_ { [ searchable => 'Searchable', fmt => 'bool' ], [ applicable => 'Applicable', fmt => 'bool' ], [ defaultspoil => 'Default spoiler level' ], - [ parents => 'Parent tags', fmt => sub { a_ href => "/$_->{parent}", $_->{name}; } ]; + [ parents => 'Parent tags', fmt => sub { a_ href => "/$_->{parent}", $_->{name}; txt_ ' (primary)' if $_->{main} } ]; } diff --git a/lib/VNWeb/TT/TraitEdit.pm b/lib/VNWeb/TT/TraitEdit.pm index 7744e181..2c3a43ae 100644 --- a/lib/VNWeb/TT/TraitEdit.pm +++ b/lib/VNWeb/TT/TraitEdit.pm @@ -13,6 +13,7 @@ my $FORM = { defaultspoil => { uint => 1, range => [0,2] }, parents => { aoh => { parent => { vndbid => 'i' }, + main => { anybool => 1 }, name => { _when => 'out' }, group => { _when => 'out', required => 0 }, } }, @@ -56,6 +57,7 @@ TUWF::get qr{/(?:$RE{iid}/add|i/new)}, sub { my $e = elm_empty($FORM_OUT); $e->{authmod} = auth->permTagmod; if($id) { + $i->{main} = 1; $e->{parents} = [$i]; $e->{sexual} = $i->{sexual}; } @@ -98,9 +100,9 @@ elm_api TraitEdit => $FORM_OUT, $FORM_IN, sub { $new ? () : sql('id NOT IN(WITH RECURSIVE t(id) AS (SELECT', \$e->{id}, '::vndbid UNION SELECT tp.id FROM traits_parents tp JOIN t ON t.id = tp.parent) SELECT id FROM t)'), sql 'id IN', $_[0] }, @parents; + die "No or multiple primary parents" if $data->{parents}->@* && 1 != grep $_->{main}, $data->{parents}->@*; - # It's technically possible for a trait to be in multiple groups, but the DB schema doesn't support that so let's get the group from the first parent (sorted by id). - my $group = tuwf->dbVali('SELECT coalesce("group",id) FROM traits WHERE id IN', \@parents, 'ORDER BY id LIMIT 1'); + my $group = tuwf->dbVali('SELECT coalesce("group",id) FROM traits WHERE id =', \[grep $_->{main}, $data->{parents}->@*]->[0]{parent}); $data->{description} = bb_subst_links($data->{description}); @@ -120,7 +122,12 @@ elm_api TraitEdit => $FORM_OUT, $FORM_IN, sub { return elm_Unchanged if !$new && !form_changed $FORM_CMP, $data, $e; my $ch = db_edit i => $e->{id}, $data; - tuwf->dbExeci('UPDATE traits SET "group" =', \$group, 'WHERE id =', \$ch->{nitemid}); + tuwf->dbExeci('UPDATE traits SET "group" = null WHERE id =', \$ch->{nitemid}) if !$group; + tuwf->dbExeci(' + WITH RECURSIVE childs (id) AS ( + SELECT ', \$ch->{nitemid}, '::vndbid UNION ALL SELECT tp.id FROM childs JOIN traits_parents tp ON tp.parent = childs.id AND tp.main + ) UPDATE traits SET "group" =', \$group, 'WHERE id IN(SELECT id FROM childs) AND "group" IS DISTINCT FROM', \$group + ) if $group; elm_Redirect "/$ch->{nitemid}.$ch->{nrev}"; }; diff --git a/lib/VNWeb/TT/TraitPage.pm b/lib/VNWeb/TT/TraitPage.pm index a759eb6b..8ccb629d 100644 --- a/lib/VNWeb/TT/TraitPage.pm +++ b/lib/VNWeb/TT/TraitPage.pm @@ -11,6 +11,7 @@ sub rev_ { my($t) = @_; sub enrich_item { enrich_merge parent => 'SELECT id AS parent, name FROM traits WHERE id IN', $_[0]{parents}; + $_[0]{parents} = [ sort { $a->{name} cmp $b->{name} || $a->{parent} <=> $b->{parent} } $_[0]{parents}->@* ]; } enrich_item $t; revision_ $t, \&enrich_item, @@ -22,7 +23,7 @@ sub rev_ { [ applicable => 'Applicable', fmt => 'bool' ], [ defaultspoil => 'Default spoiler level' ], [ order => 'Sort order' ], - [ parents => 'Parent traits', fmt => sub { a_ href => "/$_->{parent}", $_->{name}; } ]; + [ parents => 'Parent traits', fmt => sub { a_ href => "/$_->{parent}", $_->{name}; txt_ ' (primary)' if $_->{main} } ]; } diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index 6cfc0230..29d8e905 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -760,7 +760,7 @@ sub tags_ { WITH RECURSIVE parents (tag, child) AS ( SELECT tag::vndbid, NULL::vndbid FROM (VALUES", sql_join(',', map sql('(',\$_,')'), keys %tags), ") AS x(tag) UNION - SELECT tp.parent, tp.id FROM tags_parents tp, parents a WHERE a.tag = tp.id + SELECT tp.parent, tp.id FROM tags_parents tp, parents a WHERE a.tag = tp.id AND tp.main ) SELECT * FROM parents WHERE child IS NOT NULL" ); diff --git a/sql/schema.sql b/sql/schema.sql index e889c814..cceeea9e 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -749,6 +749,7 @@ CREATE TABLE tags_hist ( CREATE TABLE tags_parents ( id vndbid NOT NULL, -- [pub] parent vndbid NOT NULL, -- [pub] + main boolean NOT NULL DEFAULT false, -- [pub] PRIMARY KEY(id, parent) ); @@ -756,6 +757,7 @@ CREATE TABLE tags_parents ( CREATE TABLE tags_parents_hist ( chid integer NOT NULL, parent vndbid NOT NULL, + main boolean NOT NULL DEFAULT false, PRIMARY KEY(chid, parent) ); @@ -888,6 +890,7 @@ CREATE TABLE traits_chars ( CREATE TABLE traits_parents ( id vndbid NOT NULL, -- [pub] parent vndbid NOT NULL, -- [pub] + main boolean NOT NULL DEFAULT false, -- [pub] PRIMARY KEY(id, parent) ); @@ -895,6 +898,7 @@ CREATE TABLE traits_parents ( CREATE TABLE traits_parents_hist ( chid integer NOT NULL, parent vndbid NOT NULL, + main boolean NOT NULL DEFAULT false, PRIMARY KEY(chid, parent) ); diff --git a/util/dbdump.pl b/util/dbdump.pl index 237709a8..3d10ec4d 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -353,7 +353,7 @@ sub export_tags { my $lst = $db->selectall_arrayref(q{ SELECT vndbid_num(id) AS id, name, description, searchable, applicable, c_items AS vns, cat, alias, - (SELECT string_agg(vndbid_num(parent)::text, ',') FROM tags_parents tp WHERE tp.id = t.id) AS parents + (SELECT string_agg(vndbid_num(parent)::text, ',' ORDER BY main desc, parent) FROM tags_parents tp WHERE tp.id = t.id) AS parents FROM tags t WHERE NOT hidden ORDER BY id }, { Slice => {} }); for(@$lst) { @@ -378,7 +378,7 @@ sub export_traits { my $lst = $db->selectall_arrayref(q{ SELECT vndbid_num(id) AS id, name, alias AS aliases, description, searchable, applicable, c_items AS chars, - (SELECT string_agg(vndbid_num(parent)::text, ',') FROM traits_parents tp WHERE tp.id = t.id) AS parents + (SELECT string_agg(vndbid_num(parent)::text, ',' ORDER BY main desc, parent) FROM traits_parents tp WHERE tp.id = t.id) AS parents FROM traits t WHERE NOT hidden ORDER BY id }, { Slice => {} }); for(@$lst) { diff --git a/util/updates/2021-05-21-tt-primary-parent.sql b/util/updates/2021-05-21-tt-primary-parent.sql new file mode 100644 index 00000000..00f513a4 --- /dev/null +++ b/util/updates/2021-05-21-tt-primary-parent.sql @@ -0,0 +1,17 @@ +ALTER TABLE tags_parents ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE tags_parents_hist ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE traits_parents ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE traits_parents_hist ADD COLUMN main boolean NOT NULL DEFAULT false; +\i sql/editfunc.sql + +UPDATE tags_parents tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp2 WHERE tp2.id = tp.id AND tp2.parent < tp.parent); +UPDATE tags_parents_hist tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM tags_parents_hist tp2 WHERE tp2.chid = tp.chid AND tp2.parent < tp.parent); +UPDATE traits_parents tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM traits_parents tp2 WHERE tp2.id = tp.id AND tp2.parent < tp.parent); +UPDATE traits_parents_hist tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM traits_parents_hist tp2 WHERE tp2.chid = tp.chid AND tp2.parent < tp.parent); + +-- Update the traits.group cache for consistency with the above selected 'main' flags. +WITH RECURSIVE childs (id, grp) AS ( + SELECT id, id FROM traits t WHERE NOT EXISTS(SELECT 1 FROM traits_parents tp WHERE tp.id = t.id) + UNION ALL + SELECT tp.id, childs.grp FROM childs JOIN traits_parents tp ON tp.parent = childs.id AND tp.main +) UPDATE traits SET "group" = grp FROM childs WHERE childs.id = traits.id AND "group" IS DISTINCT FROM grp AND grp <> childs.id; |