diff options
| author | Yorhel <git@yorhel.nl> | 2021-03-18 12:24:56 +0100 |
|---|---|---|
| committer | Yorhel <git@yorhel.nl> | 2021-03-23 11:16:22 +0100 |
| commit | a286672505ee3f6dfd9fb5d62cef460e7807176d (patch) | |
| tree | fae538cff1f1b6e1e1af67082dc633639fae854c | |
| parent | 7ffe7fa485c49b3d44150ed7b63d9062ae654ac8 (diff) | |
Traits: Convert to vndbids + add edit history
Much the same as the previous conversion of tags.
33 files changed, 339 insertions, 274 deletions
diff --git a/elm/AdvSearch/Lib.elm b/elm/AdvSearch/Lib.elm index 1a419744..fa94692f 100644 --- a/elm/AdvSearch/Lib.elm +++ b/elm/AdvSearch/Lib.elm @@ -179,6 +179,6 @@ type alias Data = , producers : Dict.Dict String GApi.ApiProducerResult , staff : Dict.Dict String GApi.ApiStaffResult , tags : Dict.Dict String GApi.ApiTagResult - , traits : Dict.Dict Int GApi.ApiTraitResult + , traits : Dict.Dict String GApi.ApiTraitResult , anime : Dict.Dict Int GApi.ApiAnimeResult } diff --git a/elm/AdvSearch/Traits.elm b/elm/AdvSearch/Traits.elm index 712acdff..03071975 100644 --- a/elm/AdvSearch/Traits.elm +++ b/elm/AdvSearch/Traits.elm @@ -49,7 +49,7 @@ update dat msg model = Nothing -> (dat, { model | search = nm }, c) Just t -> ( { dat | traits = Dict.insert t.id t dat.traits } - , { model | search = A.clear nm "", sel = S.update (S.Sel t.id True) model.sel } + , { model | search = A.clear nm "", sel = S.update (S.Sel (vndbidNum t.id) True) model.sel } , c ) @@ -78,7 +78,7 @@ view dat model = [s] -> span [ class "nowrap" ] [ S.lblPrefix model.sel , b [ class "grayedout" ] [ text <| "i" ++ String.fromInt s ++ ":" ] - , Dict.get s dat.traits |> Maybe.map (\t -> t.name) |> Maybe.withDefault "" |> text + , Dict.get (vndbid 'i' s) dat.traits |> Maybe.map (\t -> t.name) |> Maybe.withDefault "" |> text ] l -> span [] [ S.lblPrefix model.sel, text <| "Traits (" ++ String.fromInt (List.length l) ++ ")" ] , \() -> @@ -95,9 +95,9 @@ view dat model = li [ style "overflow" "hidden", style "text-overflow" "ellipsis" ] [ inputButton "X" (Sel (S.Sel t False)) [] , b [ class "grayedout" ] [ text <| " i" ++ String.fromInt t ++ ": " ] - , Dict.get t dat.traits |> Maybe.map (\e -> span [] + , Dict.get (vndbid 'i' t) dat.traits |> Maybe.map (\e -> span [] [ Maybe.withDefault (text "") <| Maybe.map (\g -> b [ class "grayedout" ] [ text (g ++ " / ") ]) e.group_name - , a [ href ("/i" ++ String.fromInt t), target "_blank", style "display" "inline" ] [ text e.name ] ]) |> Maybe.withDefault (text "") + , a [ href ("/" ++ e.id), target "_blank", style "display" "inline" ] [ text e.name ] ]) |> Maybe.withDefault (text "") ] ) (Set.toList model.sel.sel) , A.view model.conf model.search [ placeholder "Search..." ] diff --git a/elm/CharEdit.elm b/elm/CharEdit.elm index 63abffe4..60517c72 100644 --- a/elm/CharEdit.elm +++ b/elm/CharEdit.elm @@ -73,7 +73,7 @@ type alias Model = , image : Img.Image , traits : List GCE.RecvTraits , traitSearch : A.Model GApi.ApiTraitResult - , traitSelId : Int + , traitSelId : String , traitSelSpl : Int , vns : List GCE.RecvVns , vnSearch : A.Model GApi.ApiVNResult @@ -113,7 +113,7 @@ init d = , image = Img.info d.image_info , traits = d.traits , traitSearch = A.init "" - , traitSelId = 0 + , traitSelId = "" , traitSelSpl = 0 , vns = d.vns , vnSearch = A.init "" @@ -191,7 +191,7 @@ type Msg | ImageSelected File | ImageMsg Img.Msg | TraitDel Int - | TraitSel Int Int + | TraitSel String Int | TraitSpoil Int Int | TraitSearch (A.Msg GApi.ApiTraitResult) | VnRel Int (Maybe String) @@ -252,9 +252,9 @@ update msg model = in case res of Nothing -> ({ model | traitSearch = nm }, c) Just t -> - if not t.applicable || t.state /= 2 || List.any (\l -> l.tid == t.id) model.traits + if not t.applicable || t.hidden || List.any (\l -> l.tid == t.id) model.traits then ({ model | traitSearch = A.clear nm "" }, c) - else ({ model | traitSearch = A.clear nm "", traits = model.traits ++ [{ tid = t.id, spoil = t.defaultspoil, name = t.name, group = t.group_name, state = t.state, applicable = t.applicable, new = True }] }, c) + else ({ model | traitSearch = A.clear nm "", traits = model.traits ++ [{ tid = t.id, spoil = t.defaultspoil, name = t.name, group = t.group_name, hidden = t.hidden, locked = t.locked, applicable = t.applicable, new = True }] }, c) VnRel idx r -> ({ model | vns = modidx idx (\v -> { v | rid = r }) model.vns }, Cmd.none) VnRole idx s -> ({ model | vns = modidx idx (\v -> { v | role = s }) model.vns }, Cmd.none) @@ -407,20 +407,20 @@ view model = old = List.filter (\(_,t) -> not t.new) <| List.indexedMap (\i t -> (i,t)) model.traits new = List.filter (\(_,t) -> t.new) <| List.indexedMap (\i t -> (i,t)) model.traits spoil t = if t.tid == model.traitSelId then model.traitSelSpl else t.spoil - trait (i,t) = (String.fromInt t.tid, + trait (i,t) = (t.tid, tr [] - [ td [ style "padding" "0 0 0 10px", style "text-decoration" (if t.applicable && t.state == 2 then "none" else "line-through") ] + [ td [ style "padding" "0 0 0 10px", style "text-decoration" (if t.applicable && not t.hidden then "none" else "line-through") ] [ Maybe.withDefault (text "") <| Maybe.map (\g -> b [ class "grayedout" ] [ text <| g ++ " / " ]) t.group - , a [ href <| "/i" ++ String.fromInt t.tid ] [ text t.name ] - , if t.state == 0 then b [ class "standout" ] [ text " (awaiting moderation)" ] - else if t.state == 1 then b [ class "standout" ] [ text " (deleted)" ] + , a [ href <| "/" ++ t.tid ] [ text t.name ] + , if t.hidden && t.locked then b [ class "standout" ] [ text " (awaiting moderation)" ] + else if t.hidden then b [ class "standout" ] [ text " (deleted)" ] else if not t.applicable then b [ class "standout" ] [ text " (not applicable)" ] else text "" ] , td [ class "buts" ] - [ a [ href "#", onMouseOver (TraitSel t.tid 0), onMouseOut (TraitSel 0 0), onClickD (TraitSpoil i 0), classList [("s0", spoil t == 0 )], title "Not a spoiler" ] [] - , a [ href "#", onMouseOver (TraitSel t.tid 1), onMouseOut (TraitSel 0 0), onClickD (TraitSpoil i 1), classList [("s1", spoil t == 1 )], title "Minor spoiler" ] [] - , a [ href "#", onMouseOver (TraitSel t.tid 2), onMouseOut (TraitSel 0 0), onClickD (TraitSpoil i 2), classList [("s2", spoil t == 2 )], title "Major spoiler" ] [] + [ a [ href "#", onMouseOver (TraitSel t.tid 0), onMouseOut (TraitSel "" 0), onClickD (TraitSpoil i 0), classList [("s0", spoil t == 0 )], title "Not a spoiler" ] [] + , a [ href "#", onMouseOver (TraitSel t.tid 1), onMouseOut (TraitSel "" 0), onClickD (TraitSpoil i 1), classList [("s1", spoil t == 1 )], title "Minor spoiler" ] [] + , a [ href "#", onMouseOver (TraitSel t.tid 2), onMouseOut (TraitSel "" 0), onClickD (TraitSpoil i 2), classList [("s2", spoil t == 2 )], title "Major spoiler" ] [] ] , td [] [ case (t.tid == model.traitSelId, lookup model.traitSelSpl spoilOpts) of diff --git a/elm/Lib/Autocomplete.elm b/elm/Lib/Autocomplete.elm index b52095ad..eaa80ff0 100644 --- a/elm/Lib/Autocomplete.elm +++ b/elm/Lib/Autocomplete.elm @@ -94,7 +94,7 @@ boardSource = } -tagStatus i = +ttStatus i = case ((i.hidden, i.locked), i.searchable, i.applicable) of ((True, False), _, _ ) -> b [ class "grayedout" ] [ text " (awaiting approval)" ] ((True, True ), _, _ ) -> b [ class "grayedout" ] [ text " (deleted)" ] -- (not returned by the API for now) @@ -110,21 +110,11 @@ tagSource = <| \x -> case x of GApi.TagResult e -> Just e _ -> Nothing - , view = \i -> [ text i.name, tagStatus i ] + , view = \i -> [ text i.name, ttStatus i ] , key = \i -> i.id } -traitStatus i = - case (i.searchable, i.applicable, i.state) of - (_, _, 0) -> b [ class "grayedout" ] [ text " (awaiting approval)" ] - (_, _, 1) -> b [ class "grayedout" ] [ text " (deleted)" ] -- (not returned by the API for now) - (False, False, _) -> b [ class "grayedout" ] [ text " (meta)" ] - (True, False, _) -> b [ class "grayedout" ] [ text " (not applicable)" ] - (False, True, _) -> b [ class "grayedout" ] [ text " (not searchable)" ] - _ -> text "" - - traitSource : SourceConfig m GApi.ApiTraitResult traitSource = { source = Endpoint (\s -> GTR.send { search = s }) @@ -136,9 +126,9 @@ traitSource = Nothing -> text "" Just g -> b [ class "grayedout" ] [ text <| g ++ " / " ] , text i.name - , traitStatus i + , ttStatus i ] - , key = \i -> String.fromInt i.id + , key = \i -> i.id } diff --git a/elm/Subscribe.elm b/elm/Subscribe.elm index ca70a675..61edc49c 100644 --- a/elm/Subscribe.elm +++ b/elm/Subscribe.elm @@ -80,7 +80,7 @@ view model = "w" -> text " Disable notifications only for this review." _ -> text " Disable edit notifications only for this entry." ] - , if t == "i" then text "" else label [] + , label [] [ inputCheck "" (dat.subnum == Just True) (SubNum True) , case t of "t" -> text " Enable notifications for new replies" diff --git a/elm/TraitEdit.elm b/elm/TraitEdit.elm index af0b4763..d8866f25 100644 --- a/elm/TraitEdit.elm +++ b/elm/TraitEdit.elm @@ -10,6 +10,7 @@ import Lib.Api as Api import Lib.Util exposing (..) import Lib.Autocomplete as A import Lib.Ffi as Ffi +import Lib.Editsum as Editsum import Gen.Api as GApi import Gen.TraitEdit as GTE @@ -24,11 +25,11 @@ main = Browser.element type alias Model = - { formstate : Api.State - , id : Maybe Int + { state : Api.State + , editsum : Editsum.Model + , id : Maybe String , name : String , alias : String - , state : Int , sexual : Bool , description : TP.Model , searchable : Bool @@ -37,19 +38,17 @@ type alias Model = , parents : List GTE.RecvParents , parentAdd : A.Model GApi.ApiTraitResult , order : Int - , addedby : String - , canMod : Bool , dupNames : List GApi.ApiDupNames } init : GTE.Recv -> Model init d = - { formstate = Api.Normal + { state = Api.Normal + , editsum = { authmod = d.authmod, editsum = TP.bbcode d.editsum, locked = d.locked, hidden = d.hidden, hasawait = True } , id = d.id , name = d.name , alias = d.alias - , state = d.state , sexual = d.sexual , description = TP.bbcode d.description , searchable = d.searchable @@ -58,8 +57,6 @@ init d = , parents = d.parents , parentAdd = A.init "" , order = d.order - , addedby = d.addedby - , canMod = d.can_mod , dupNames = [] } @@ -80,15 +77,17 @@ parentConfig = { wrap = ParentSearch, id = "parentadd", source = A.traitSource } encode : Model -> GTE.Send encode m = { id = m.id + , editsum = m.editsum.editsum.data + , hidden = m.editsum.hidden + , locked = m.editsum.locked , name = m.name , alias = m.alias - , state = m.state , sexual = m.sexual , description = m.description.data , searchable = m.searchable , applicable = m.applicable , defaultspoil = m.defaultspoil - , parents = List.map (\l -> {id=l.id}) m.parents + , parents = List.map (\l -> {parent=l.parent}) m.parents , order = m.order } @@ -96,12 +95,12 @@ encode m = type Msg = Name String | Alias String - | State Int | Searchable Bool | Applicable Bool | Sexual Bool | DefaultSpoil Int | Description TP.Msg + | Editsum Editsum.Msg | ParentDel Int | ParentSearch (A.Msg GApi.ApiTraitResult) | Order String @@ -114,13 +113,13 @@ update msg model = case msg of Name s -> ({ model | name = s }, Cmd.none) Alias s -> ({ model | alias = String.replace "," "\n" s }, Cmd.none) - State n -> ({ model | state = n }, Cmd.none) Searchable b -> ({ model | searchable = b }, Cmd.none) Applicable b -> ({ model | applicable = b }, Cmd.none) Sexual b -> ({ model | sexual = b }, Cmd.none) DefaultSpoil n-> ({ model | defaultspoil = n }, Cmd.none) Order s -> ({ model | order = Maybe.withDefault 0 (String.toInt s) }, Cmd.none) 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) ParentSearch m -> @@ -128,25 +127,23 @@ update msg model = in case res of Nothing -> ({ model | parentAdd = nm }, c) Just p -> - if List.any (\e -> e.id == p.id) model.parents + if List.any (\e -> e.parent == p.id) model.parents then ({ model | parentAdd = nm }, c) - else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ id = p.id, name = p.name, group = p.group_name }] }, c) + else ({ model | parentAdd = A.clear nm "", parents = model.parents ++ [{ parent = p.id, name = p.name, group = p.group_name }] }, c) - Submit -> ({ model | formstate = Api.Loading }, GTE.send (encode model) Submitted) - Submitted (GApi.DupNames l) -> ({ model | dupNames = l, formstate = Api.Normal }, Cmd.none) + Submit -> ({ model | state = Api.Loading }, GTE.send (encode model) Submitted) + Submitted (GApi.DupNames l) -> ({ model | dupNames = l, state = Api.Normal }, Cmd.none) Submitted (GApi.Redirect s) -> (model, load s) - Submitted r -> ({ model | formstate = Api.Error r }, Cmd.none) + Submitted r -> ({ model | state = Api.Error r }, Cmd.none) view : Model -> Html Msg view model = - form_ "" Submit (model.formstate == Api.Loading) + form_ "" Submit (model.state == Api.Loading) [ div [ class "mainbox" ] [ h1 [] [ text <| if model.id == Nothing then "Submit new trait" else "Edit trait" ] , table [ class "formtable" ] - [ if model.id == Nothing then text "" else - formField "Added by" [ span [ Ffi.innerHtml model.addedby ] [], br_ 2 ] - , formField "name::Primary name" [ inputText "name" model.name Name GTE.valName ] + [ formField "name::Primary name" [ inputText "name" model.name Name GTE.valName ] , formField "alias::Aliases" -- BUG: Textarea doesn't validate the maxlength and patterns for aliases, we don't have a client-side fallback check either. [ inputTextArea "alias" model.alias Alias [] @@ -161,17 +158,8 @@ view model = ] ] , tr [ class "newpart" ] [ td [ colspan 2 ] [ text "" ] ] - , if not model.canMod then text "" else - formField "state::State" [ inputSelect "state" model.state State GTE.valState - [ (0, "Awaiting Moderation") - , (1, "Deleted/hidden") - , (2, "Approved") - ] - ] - , if not model.canMod then text "" else - formField "" [ label [] [ inputCheck "" model.searchable Searchable, text " Searchable (people can use this trait to find characters)" ] ] - , if not model.canMod then text "" else - formField "" [ label [] [ inputCheck "" model.applicable Applicable, text " Applicable (people can apply this trait to characters)" ] ] + , 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 [ (0, "No spoiler") @@ -186,10 +174,10 @@ view model = , tr [ class "newpart" ] [ td [ colspan 2 ] [ text "" ] ] , formField "Parent traits" [ table [ class "compact" ] <| List.indexedMap (\i p -> tr [] - [ td [ style "text-align" "right" ] [ b [ class "grayedout" ] [ text <| "i" ++ String.fromInt p.id ++ ":" ] ] + [ td [ style "text-align" "right" ] [ b [ class "grayedout" ] [ text <| p.parent ++ ":" ] ] , td [] [ Maybe.withDefault (text "") <| Maybe.map (\g -> b [ class "grayedout" ] [ text (g ++ " / ") ]) p.group - , a [ href <| "/i" ++ String.fromInt p.id ] [ text p.name ] + , a [ href <| "/" ++ p.parent ] [ text p.name ] ] , td [] [ inputButton "remove" (ParentDel i) [] ] ] @@ -204,6 +192,9 @@ view model = ] ] ] - , div [ class "mainbox" ] - [ fieldset [ class "submit" ] [ submitButton "Submit" model.formstate (isValid model) ] ] + , div [ class "mainbox" ] [ fieldset [ class "submit" ] + [ Html.map Editsum (Editsum.view model.editsum) + , submitButton "Submit" model.state (isValid model) + ] + ] ] diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index c7110585..b64319a1 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -859,7 +859,7 @@ my %GET_CHARACTER = ( fetch => [[ 'id', 'SELECT id, tid, spoil FROM chars_traits WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{id} eq $_->{id}, @$r ]; + $i->{traits} = [ map [ idnum($_->{tid}), $_->{spoil}*1 ], grep $i->{id} eq $_->{id}, @$r ]; } }, ]], @@ -930,8 +930,8 @@ my %GET_CHARACTER = ( [ inta => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid IN(:value:))', {'=',1}, process => \'v', join => ',' ], ], traits => [ - [ int => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, range => [1,1e6] ], - [ inta => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', range => [1,1e6] ], + [ int => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'i' ], + [ inta => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'i' ], ], }, ); diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index aab05223..9809ea35 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -199,13 +199,12 @@ sub set_logger { sub set_notify { pg_cmd q{SELECT (SELECT id FROM changes ORDER BY id DESC LIMIT 1) AS rev, - (SELECT id FROM traits ORDER BY id DESC LIMIT 1) AS trait, (SELECT date FROM threads_posts ORDER BY date DESC LIMIT 1) AS post, (SELECT id FROM reviews ORDER BY id DESC LIMIT 1) AS review }, undef, sub { return if pg_expect $_[0], 1; %lastnotify = %{($_[0]->rowsAsHashes())[0]}; - push_watcher pg->listen($_, on_notify => \¬ify) for qw{newrevision newpost newtag newtrait newreview}; + push_watcher pg->listen($_, on_notify => \¬ify) for qw{newrevision newpost newreview}; }; } @@ -298,7 +297,7 @@ sub handleid { $id =~ /^s/ ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $1' : $id =~ /^t/ ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = $1' : $id =~ /^g/ ? 'name AS title FROM tags WHERE id = $1' : - $id =~ /^i/ ? 'name AS title FROM traits WHERE id = vndbid_num($1)' : + $id =~ /^i/ ? 'name AS title FROM traits WHERE id = $1' : $id =~ /^d/ ? 'title FROM docs WHERE id = $1' : $id =~ /^w/ ? 'v.title, u.username FROM reviews w JOIN vn v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid WHERE w.id = $1' : 'r.title FROM releases r WHERE r.id = $1'), @@ -313,9 +312,10 @@ sub handleid { $id =~ /^s/ ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid LEFT JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.itemid = $1 AND c.rev = $2' : $id =~ /^d/ ? 'dh.title, u.username, c.comments FROM changes c JOIN docs_hist dh ON c.id = dh.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' : $id =~ /^g/ ? 'th.name AS title, u.username, c.comments FROM changes c JOIN tags_hist th ON c.id = th.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' : + $id =~ /^i/ ? 'th.name AS title, u.username, c.comments FROM changes c JOIN traits_hist th ON c.id = th.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' : $id =~ /^w/ ? 'v.title, u.username FROM reviews_posts wp JOIN reviews w ON w.id = wp.id JOIN vn v ON v.id = w.vid LEFT JOIN users u ON u.id = wp.uid WHERE wp.id = $1 AND wp.num = $2' : 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id LEFT JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = $1 AND tp.num = $2'), - [ $id, $rev], $c if $rev && $id =~ /^[dvprtcsgw]/; + [ $id, $rev], $c if $rev && $id =~ /^[dvprtcsgiw]/; } @@ -327,8 +327,8 @@ sub vndbid { my @id; # [ type, id, ref ] for (split /[, ]/, $msg) { next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things - push @id, /^(?:.*[^\w]|)([wdvprtcsg][1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2 ] # x+.+ - : /^(?:.*[^\w]|)([wdvprtugics][1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, '' ] : (); # x+ + push @id, /^(?:.*[^\w]|)([wdvprtcsgi][1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2 ] # x+.+ + : /^(?:.*[^\w]|)([wdvprtcsgiu][1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, '' ] : (); # x+ } handleid($chan, @$_) for @id; } @@ -338,13 +338,13 @@ sub vndbid { sub notify { my(undef, $sel) = @_; - my $k = {qw|newrevision rev newpost post newtrait trait newreview review|}->{$sel}; + my $k = {qw|newrevision rev newpost post newreview review|}->{$sel}; return if !$k || !$lastnotify{$k}; my $q = { rev => q{ SELECT c.rev, c.comments, c.id AS lastid, c.itemid AS id, - COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name, dh.title, th.name) AS title, u.username + COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name, dh.title, th.name, ih.name) AS title, u.username FROM changes c LEFT JOIN vn_hist vh ON vndbid_type(c.itemid) = 'v' AND c.id = vh.chid LEFT JOIN releases_hist rh ON vndbid_type(c.itemid) = 'r' AND c.id = rh.chid @@ -354,6 +354,7 @@ sub notify { LEFT JOIN staff_alias_hist sah ON vndbid_type(c.itemid) = 's' AND sah.aid = sh.aid AND sah.chid = c.id LEFT JOIN docs_hist dh ON vndbid_type(c.itemid) = 'd' AND c.id = dh.chid LEFT JOIN tags_hist th ON vndbid_type(c.itemid) = 'g' AND c.id = th.chid + LEFT JOIN traits_hist ih ON vndbid_type(c.itemid) = 'i' AND c.id = ih.chid JOIN users u ON u.id = c.requester WHERE c.id > $1 AND c.requester <> 'u1' ORDER BY c.id}, @@ -364,12 +365,6 @@ sub notify { LEFT JOIN users u ON u.id = tp.uid WHERE tp.date > $1 AND tp.num = 1 AND NOT t.hidden AND NOT t.private ORDER BY tp.date}, - trait => q{ - SELECT 'i'||t.id AS id, t.name AS title, u.username, t.id AS lastid - FROM traits t - JOIN users u ON u.id = t.addedby - WHERE t.id > $1 - ORDER BY t.id}, review => q{ SELECT w.id, v.title, u.username, w.id AS lastid FROM reviews w diff --git a/lib/VNDB/BBCode.pm b/lib/VNDB/BBCode.pm index c3289784..4f7b5503 100644 --- a/lib/VNDB/BBCode.pm +++ b/lib/VNDB/BBCode.pm @@ -139,7 +139,7 @@ sub parse { while($raw =~ m{(?: \[ \/? (?i: b|i|u|s|spoiler|quote|code|url|raw ) [^\s\]]* \] | # tag d[1-9][0-9]* \# [1-9][0-9]* (?: \.[1-9][0-9]* )? | # d+#+[.+] - [tdvprcswg][1-9][0-9]*\.[1-9][0-9]* | # v+.+ + [tdvprcswgi][1-9][0-9]*\.[1-9][0-9]* | # v+.+ [tdvprcsugiw][1-9][0-9]* | # v+ (?:https?|ftp)://[^><"\n\s\]\[]+[\d\w=/-] # link )}xg) { @@ -295,7 +295,7 @@ sub bb_subst_links { my %lookup; parse $msg, sub { my($code, $tag) = @_; - $lookup{$2}{ $2 eq 'i' ? $3 : $1 } = 1 if $tag eq 'dblink' && $code =~ /^((.)(\d+))/; + $lookup{$2}{$1} = 1 if $tag eq 'dblink' && $code =~ /^((.)\d+)/; 1; }; return $msg unless %lookup; @@ -306,7 +306,7 @@ sub bb_subst_links { c => 'SELECT id, name FROM chars WHERE id IN', p => 'SELECT id, name FROM producers WHERE id IN', g => 'SELECT id, name FROM tags WHERE id IN', - i => 'SELECT \'i\'||id AS id, name FROM traits WHERE id IN', + i => 'SELECT id, name FROM traits WHERE id IN', s => 'SELECT s.id, sa.name FROM staff_alias sa JOIN staff s ON s.aid = sa.aid WHERE s.id IN', }; my %links; diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm index f325b634..cc76c6cf 100644 --- a/lib/VNWeb/AdvSearch.pm +++ b/lib/VNWeb/AdvSearch.pm @@ -601,7 +601,7 @@ sub _sql_where_trait { for my $s (keys %f) { push @l, sql_and $s < 2 ? sql('spoil <=', \$s) : (), - sql('tid IN', [ map s/^.//r, $f{$s}->@* ]); + sql('tid IN', $f{$s}); } sql 'c.id', $neg ? 'NOT' : (), 'IN(SELECT cid FROM traits_chars WHERE', sql_or(@l), $all && @$val > 1 ? ('GROUP BY cid HAVING COUNT(tid) =', \scalar @$val) : (), ')' } @@ -740,8 +740,8 @@ sub elm_search_query { $o{tags} = [ map +{id => $_}, grep /^g/, keys %ids ]; enrich_merge id => 'SELECT id, name, searchable, applicable, hidden, locked FROM tags WHERE id IN', $o{tags}; - $o{traits} = [ map +{id => $_=~s/^i//rg}, grep /^i/, keys %ids ]; - enrich_merge id => 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.state, g.id AS group_id, g.name AS group_name + $o{traits} = [ map +{id => $_}, grep /^i/, keys %ids ]; + enrich_merge id => 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.hidden, t.locked, g.id AS group_id, g.name AS group_name FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $o{traits}; $o{anime} = [ map +{id => $_=~s/^anime//rg}, grep /^anime/, keys %ids ]; diff --git a/lib/VNWeb/Chars/Edit.pm b/lib/VNWeb/Chars/Edit.pm index b08f1330..0bc00d5c 100644 --- a/lib/VNWeb/Chars/Edit.pm +++ b/lib/VNWeb/Chars/Edit.pm @@ -30,11 +30,12 @@ my $FORM = { image => { required => 0, vndbid => 'ch' }, image_info => { _when => 'out', required => 0, type => 'hash', keys => $VNWeb::Elm::apis{ImageResult}[0]{aoh} }, traits => { sort_keys => 'id', aoh => { - tid => { id => 1 }, + tid => { vndbid => 'i' }, spoil => { uint => 1, range => [0,2] }, name => { _when => 'out' }, group => { _when => 'out', required => 0 }, - state => { _when => 'out', uint => 1 }, + hidden => { _when => 'out', anybool => 1 }, + locked => { _when => 'out', anybool => 1 }, applicable => { _when => 'out', anybool => 1 }, new => { _when => 'out', anybool => 1 }, } }, @@ -69,7 +70,7 @@ TUWF::get qr{/$RE{crev}/(?<action>edit|copy)} => sub { $e->{main_name} = $e->{main} ? tuwf->dbVali('SELECT name FROM chars WHERE id =', \$e->{main}) : ''; $e->{main_ref} = tuwf->dbVali('SELECT 1 FROM chars WHERE main =', \$e->{id})||0; - enrich_merge tid => 'SELECT t.id AS tid, t.name, t.state, t.applicable, g.name AS group, g.order AS order, false AS new FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $e->{traits}; + enrich_merge tid => 'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, g.name AS group, g.order AS order, false AS new FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $e->{traits}; $e->{traits} = [ sort { ($a->{order}//99) <=> ($b->{order}//99) || $a->{name} cmp $b->{name} } grep !$copy || $_->{applicable}, $e->{traits}->@* ]; enrich_merge vid => 'SELECT id AS vid, title FROM vn WHERE id IN', $e->{vns}; @@ -138,7 +139,7 @@ elm_api CharEdit => $FORM_OUT, $FORM_IN, sub { # Allow non-applicable or non-approved traits only when they were already applied to this character. validate_dbid - sql('SELECT id FROM traits t WHERE ((state = 1+1 AND applicable) OR EXISTS(SELECT 1 FROM chars_traits ct WHERE ct.tid = t.id AND ct.id =', \$e->{id}, ')) AND id IN'), + sql('SELECT id FROM traits t WHERE ((NOT hidden AND applicable) OR EXISTS(SELECT 1 FROM chars_traits ct WHERE ct.tid = t.id AND ct.id =', \$e->{id}, ')) AND id IN'), map $_->{tid}, $data->{traits}->@*; validate_dbid 'SELECT id FROM vn WHERE id IN', map $_->{vid}, $data->{vns}->@*; diff --git a/lib/VNWeb/Chars/Page.pm b/lib/VNWeb/Chars/Page.pm index ca18a2f8..e2d10068 100644 --- a/lib/VNWeb/Chars/Page.pm +++ b/lib/VNWeb/Chars/Page.pm @@ -23,7 +23,7 @@ sub enrich_item { enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $c->{vns}; enrich_merge rid => 'SELECT id AS rid, title AS rtitle, original AS roriginal FROM releases WHERE id IN', grep $_->{rid}, $c->{vns}->@*; enrich_merge tid => - 'SELECT t.id AS tid, t.name, t.state, t.applicable, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order + 'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order FROM traits t LEFT JOIN traits g ON t.group = g.id WHERE t.id IN', $c->{traits}; $c->{vns} = [ sort { $a->{title} cmp $b->{title} || idcmp($a->{vid}, $b->{vid}) || idcmp($a->{rid}||'r999999', $b->{rid}||'r999999') } $c->{vns}->@* ]; @@ -51,7 +51,7 @@ sub fetch_chars { }, $l; enrich traits => id => id => sub { sql ' - SELECT ct.id, ct.tid, ct.spoil, t.name, t.state, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order + SELECT ct.id, ct.tid, ct.spoil, t.name, t.hidden, t.locked, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order FROM chars_traits ct JOIN traits t ON t.id = ct.tid LEFT JOIN traits g ON t.group = g.id @@ -98,11 +98,11 @@ sub _rev_ { txt_ " $CHAR_ROLE{$_->{role}}{txt} (".fmtspoil($_->{spoil}).')'; } ], [ traits => 'Traits', fmt => sub { - b_ class => 'grayedout', "$_->{groupname} / " if $_->{group} != $_->{tid}; - a_ href => "/i$_->{tid}", $_->{name}; + b_ class => 'grayedout', "$_->{groupname} / " if $_->{group} ne $_->{tid}; + a_ href => "/$_->{tid}", $_->{name}; txt_ ' ('.fmtspoil($_->{spoil}).')'; - b_ class => 'standout', ' (awaiting moderation)' if $_->{state} == 0; - b_ class => 'standout', ' (trait deleted)' if $_->{state} == 1; + b_ class => 'standout', ' (awaiting moderation)' if $_->{hidden} && !$_->{locked}; + b_ class => 'standout', ' (trait deleted)' if $_->{hidden} && $_->{locked}; b_ class => 'standout', ' (not applicable)' if !$_->{applicable}; } ], } @@ -158,13 +158,13 @@ sub chartable_ { } if defined $c->{age}; my @groups; - for(grep $_->{state} == 2 && $_->{spoil} <= $view->{spoilers} && (!$_->{sexual} || $view->{traits_sexual}), $c->{traits}->@*) { - push @groups, $_ if !@groups || $groups[$#groups]{group} != $_->{group}; + for(grep !$_->{hidden} && $_->{spoil} <= $view->{spoilers} && (!$_->{sexual} || $view->{traits_sexual}), $c->{traits}->@*) { + push @groups, $_ if !@groups || $groups[$#groups]{group} ne $_->{group}; push $groups[$#groups]{traits}->@*, $_; } - tr_ class => "trait_group_i$_->{group}", sub { - td_ class => 'key', sub { a_ href => "/i$_->{group}", $_->{groupname} }; - td_ sub { join_ ', ', sub { a_ href => "/i$_->{tid}", $_->{name}; spoil_ $_->{spoil} }, $_->{traits}->@* }; + tr_ class => "trait_group_$_->{group}", sub { + td_ class => 'key', sub { a_ href => "/$_->{group}", $_->{groupname} }; + td_ sub { join_ ', ', sub { a_ href => "/$_->{tid}", $_->{name}; spoil_ $_->{spoil} }, $_->{traits}->@* }; } for @groups; my @visvns = grep $_->{spoil} <= $view->{spoilers}, $c->{vns}->@*; @@ -244,13 +244,13 @@ TUWF::get qr{/$RE{crev}} => sub { my $max_spoil = max( $inst_maxspoil||0, - (map $_->{spoil}, grep $_->{state} == 2, $c->{traits}->@*), + (map $_->{spoil}, grep !$_->{hidden}, $c->{traits}->@*), (map $_->{spoil}, $c->{vns}->@*), defined $c->{spoil_gender} ? 2 : 0, $c->{desc} =~ /\[spoiler\]/i ? 2 : 0, # crude ); # Only display the sexual traits toggle when there are sexual traits within the current spoiler level. - my $has_sex = grep $_->{state} == 2 && $_->{spoil} <= $view->{spoilers} && $_->{sexual}, map $_->{traits}->@*, $c, @$inst; + my $has_sex = grep !$_->{hidden} && $_->{spoil} <= $view->{spoilers} && $_->{sexual}, map $_->{traits}->@*, $c, @$inst; framework_ title => $c->{name}, index => !tuwf->capture('rev'), dbobj => $c, hiddenmsg => 1, og => { diff --git a/lib/VNWeb/Chars/VNTab.pm b/lib/VNWeb/Chars/VNTab.pm index 6dd9836a..e74a9144 100644 --- a/lib/VNWeb/Chars/VNTab.pm +++ b/lib/VNWeb/Chars/VNTab.pm @@ -10,14 +10,14 @@ sub chars_ { my $max_spoil = max( map max( - (map $_->{spoil}, $_->{traits}->@*), + (map $_->{spoil}, grep !$_->{hidden}, $_->{traits}->@*), (map $_->{spoil}, $_->{vns}->@*), defined $_->{spoil_gender} ? 2 : 0, $_->{desc} =~ /\[spoiler\]/i ? 2 : 0, ), @$chars ); $chars = [ grep +grep($_->{spoil} <= $view->{spoilers}, $_->{vns}->@*), @$chars ]; - my $has_sex = grep $_->{spoil} <= $view->{spoilers} && $_->{sexual}, map $_->{traits}->@*, @$chars; + my $has_sex = grep !$_->{hidden} && $_->{spoil} <= $view->{spoilers} && $_->{sexual}, map $_->{traits}->@*, @$chars; my %done; my $first = 0; diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm index 953fc9b9..68b244c7 100644 --- a/lib/VNWeb/Elm.pm +++ b/lib/VNWeb/Elm.pm @@ -90,13 +90,14 @@ our %apis = ( locked => { anybool => 1 }, } } ], TraitResult => [ { aoh => { # Response to 'Traits' - id => { id => 1 }, + id => { vndbid => 'i' }, name => {}, searchable => { anybool => 1 }, applicable => { anybool => 1 }, - state => { int => 1 }, defaultspoil => { uint => 1 }, - group_id => { required => 0, uint => 1 }, + hidden => { anybool => 1 }, + locked => { anybool => 1 }, + group_id => { required => 0, vndbid => 'i' }, group_name => { required => 0 }, } } ], VNResult => [ { aoh => { # Response to 'VN' diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm index d6283c43..ae615d02 100644 --- a/lib/VNWeb/HTML.pm +++ b/lib/VNWeb/HTML.pm @@ -356,7 +356,7 @@ sub _maintabs_subscribe_ { UNION SELECT 1+1 FROM reviews w, users u WHERE u.id =', \auth->uid, 'AND w.uid =', \auth->uid, 'AND w.id =', \$id, 'AND u.notify_comment ) x(x)') - : $id =~ /^[vrpcsdg]/ && auth->pref('notify_dbedit') && tuwf->dbVali(' + : $id =~ /^[vrpcsdgi]/ && auth->pref('notify_dbedit') && tuwf->dbVali(' SELECT 1 FROM changes WHERE itemid =', \$id, 'AND requester =', \auth->uid); my $sub = tuwf->dbRowi('SELECT subnum, subreview, subapply FROM notification_subs WHERE uid =', \auth->uid, 'AND iid =', \$id); @@ -377,10 +377,11 @@ sub _maintabs_subscribe_ { sub _maintabs_ { my $opt = shift; - my($t, $o, $sel) = @{$opt}{qw/type dbobj tab/}; - return if !$t || !$o; + my($o, $sel) = @{$opt}{qw/dbobj tab/}; + return if !$o; - my $id = $o->{id} =~ /^[0-9]*$/ ? $t.$o->{id} : $o->{id}; + my $id = $o->{id}; + my($t) = $id =~ /^(.)/; my sub t { my($tabname, $url, $text) = @_; @@ -418,7 +419,7 @@ sub _maintabs_ { t disc => "/t/$id", "discussions ($cnt)"; }; - t hist => "/$id/hist", 'history' if $t =~ /[uvrpcsdg]/; + t hist => "/$id/hist", 'history' if $t =~ /[uvrpcsdgi]/; _maintabs_subscribe_ $o, $id; } } diff --git a/lib/VNWeb/Misc/History.pm b/lib/VNWeb/Misc/History.pm index 0ba25232..87156883 100644 --- a/lib/VNWeb/Misc/History.pm +++ b/lib/VNWeb/Misc/History.pm @@ -42,6 +42,7 @@ sub fetch { UNION ALL SELECT chid, title, '' AS original FROM docs_hist UNION ALL SELECT sh.chid, name, original FROM staff_hist sh JOIN staff_alias_hist sah ON sah.chid = sh.chid AND sah.aid = sh.aid UNION ALL SELECT chid, name, '' AS original FROM tags_hist + UNION ALL SELECT chid, name, '' AS original FROM traits_hist ) t(id, title, original) WHERE id IN}), $lst; ($lst, $np) @@ -96,6 +97,7 @@ sub filters_ { [ p => 'Producers' ], [ s => 'Staff' ], [ c => 'Characters' ], + [ i => 'Traits' ], [ d => 'Docs' ], ); @@ -163,7 +165,7 @@ sub filters_ { } -TUWF::get qr{/(?:([upvrcsdg][1-9][0-9]{0,6})/)?hist} => sub { +TUWF::get qr{/(?:([upvrcsdgi][1-9][0-9]{0,6})/)?hist} => sub { my $id = tuwf->capture(1)||''; my $obj = dbobj $id; diff --git a/lib/VNWeb/Prelude.pm b/lib/VNWeb/Prelude.pm index 2c7ede52..bdd54e02 100644 --- a/lib/VNWeb/Prelude.pm +++ b/lib/VNWeb/Prelude.pm @@ -86,7 +86,7 @@ our %RE = ( sid => qr{(?<id>s$num)}, cid => qr{(?<id>c$num)}, pid => qr{(?<id>p$num)}, - iid => qr{i(?<id>$num)}, + iid => qr{(?<id>i$num)}, did => qr{(?<id>d$num)}, tid => qr{(?<id>t$num)}, gid => qr{(?<id>g$num)}, @@ -99,6 +99,7 @@ our %RE = ( crev => qr{(?<id>c$num)$rev?}, drev => qr{(?<id>d$num)$rev?}, grev => qr{(?<id>g$num)$rev?}, + irev => qr{(?<id>i$num)$rev?}, postid => qr{(?<id>t$num)\.(?<num>$num)}, ); @@ -123,6 +124,7 @@ sub dbobj { $id =~ /^c/ ? item chars => 'name' : $id =~ /^s/ ? item staff => '(SELECT name FROM staff_alias WHERE aid = staff.aid)' : $id =~ /^g/ ? item tags => 'name' : + $id =~ /^i/ ? item traits => 'name' : $id =~ /^d/ ? item docs => 'title' : die; $o->{title} = VNWeb::HTML::user_displayname $o if $id =~ /^u/; diff --git a/lib/VNWeb/TT/Elm.pm b/lib/VNWeb/TT/Elm.pm index d47bb223..4ecc0a0a 100644 --- a/lib/VNWeb/TT/Elm.pm +++ b/lib/VNWeb/TT/Elm.pm @@ -27,7 +27,7 @@ elm_api Traits => undef, { search => {} }, sub { my $qs = sql_like $q; elm_TraitResult tuwf->dbPagei({ results => 15, page => 1 }, - 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.state, g.id AS group_id, g.name AS group_name + 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.hidden, t.locked, g.id AS group_id, g.name AS group_name FROM (SELECT MIN(prio), id FROM (', sql_join('UNION ALL', $q =~ /^$RE{iid}$/ ? sql('SELECT 1, id FROM traits WHERE id =', \"$+{id}") : (), @@ -36,7 +36,7 @@ elm_api Traits => undef, { search => {} }, sub { ), ') x(prio, id) GROUP BY id) x(prio,id) JOIN traits t ON t.id = x.id LEFT JOIN traits g ON g.id = t.group - WHERE t.state <> 1 + WHERE NOT (t.hidden AND t.locked) ORDER BY x.prio, t.name ') }; diff --git a/lib/VNWeb/TT/Index.pm b/lib/VNWeb/TT/Index.pm index dd6b6e47..cca74fe7 100644 --- a/lib/VNWeb/TT/Index.pm +++ b/lib/VNWeb/TT/Index.pm @@ -6,10 +6,7 @@ use VNWeb::TT::Lib 'enrich_group', 'tree_'; sub recent_ { my($type) = @_; - my $lst = tuwf->dbAlli( - $type eq 'g' ? sql 'SELECT vndbid_num(id) AS id, name, ', sql_totime('added'), 'AS added FROM tags WHERE NOT hidden ORDER BY id DESC LIMIT 10' - : sql 'SELECT id, name, ', sql_totime('added'), 'AS added FROM traits WHERE state = 1+1 ORDER BY id DESC LIMIT 10' - ); + my $lst = tuwf->dbAlli('SELECT id, name, ', sql_totime('added'), 'AS added FROM', $type eq 'g' ? 'tags' : 'traits', 'WHERE NOT hidden ORDER BY id DESC LIMIT 10'); enrich_group $type, $lst; p_ class => 'mainopts', sub { a_ href => "/$type/list", 'Browse all '.($type eq 'g' ? 'tags' : 'traits'); @@ -20,7 +17,7 @@ sub recent_ { txt_ fmtage $_->{added}; txt_ ' '; b_ class => 'grayedout', "$_->{group} / " if $_->{group}; - a_ href => "/$type$_->{id}", $_->{name}; + a_ href => "/$_->{id}", $_->{name}; } for @$lst; }; } @@ -28,10 +25,7 @@ sub recent_ { sub popular_ { my($type) = @_; - my $lst = tuwf->dbAlli( - $type eq 'g' ? 'SELECT vndbid_num(id) AS id, name, c_items FROM tags WHERE NOT hidden AND c_items > 0 AND applicable ORDER BY c_items DESC LIMIT 10' - : 'SELECT id, name, c_items FROM traits WHERE state = 1+1 AND c_items > 0 AND applicable ORDER BY c_items DESC LIMIT 10' - ); + my $lst = tuwf->dbAlli('SELECT id, name, c_items FROM', $type eq 'g' ? 'tags' : 'traits', 'WHERE NOT hidden AND c_items > 0 AND applicable ORDER BY c_items DESC LIMIT 10'); enrich_group $type, $lst; p_ class => 'mainopts', sub { a_ href => '/g/links', 'Recently tagged'; @@ -40,7 +34,7 @@ sub popular_ { ul_ sub { li_ sub { b_ class => 'grayedout', "$_->{group} / " if $_->{group}; - a_ href => "/$type$_->{id}", $_->{name}; + a_ href => "/$_->{id}", $_->{name}; txt_ " ($_->{c_items})"; } for @$lst; }; @@ -49,10 +43,7 @@ sub popular_ { sub moderation_ { my($type) = @_; - my $lst = tuwf->dbAlli( - $type eq 'g' ? sql 'SELECT vndbid_num(id) AS id, name, ', sql_totime('added'), 'AS added FROM tags t WHERE hidden AND NOT locked ORDER BY added DESC LIMIT 10' - : sql 'SELECT id, name, ', sql_totime('added'), 'AS added FROM traits WHERE state = 0 ORDER BY added DESC LIMIT 10' - ); + my $lst = tuwf->dbAlli('SELECT id, name, ', sql_totime('added'), 'AS added FROM', $type eq 'g' ? 'tags' : 'traits', 'WHERE hidden AND NOT locked ORDER BY added DESC LIMIT 10'); enrich_group $type, $lst; h1_ 'Awaiting moderation'; ul_ sub { @@ -61,7 +52,7 @@ sub moderation_ { txt_ fmtage $_->{added}; txt_ ' '; b_ class => 'grayedout', "$_->{group} / " if $_->{group}; - a_ href => "/$type$_->{id}", $_->{name}; + a_ href => "/$_->{id}", $_->{name}; } for @$lst; li_ sub { br_; diff --git a/lib/VNWeb/TT/Lib.pm b/lib/VNWeb/TT/Lib.pm index aa0f2f9e..ab904e6d 100644 --- a/lib/VNWeb/TT/Lib.pm +++ b/lib/VNWeb/TT/Lib.pm @@ -24,24 +24,22 @@ sub enrich_group { sub tree_ { my($type, $id) = @_; my $table = $type eq 'g' ? 'tags' : 'traits'; - my $joincol = $type eq 'g' ? 'id' : 'trait'; - my $visible = $type eq 'g' ? 'NOT hidden' : 'state = 1+1'; my $top = tuwf->dbAlli( "SELECT id, name, c_items FROM $table t - WHERE $visible - AND", $id ? sql "id IN(SELECT $joincol FROM ${table}_parents WHERE parent = ", \$id, ')' - : "NOT EXISTS(SELECT 1 FROM ${table}_parents tp WHERE tp.$joincol = t.id)", " + WHERE NOT hidden + AND", $id ? sql "id IN(SELECT id FROM ${table}_parents WHERE parent = ", \$id, ')' + : "NOT EXISTS(SELECT 1 FROM ${table}_parents tp WHERE tp.id = t.id)", " ORDER BY ", $type eq 'g' || $id ? 'name' : '"order"' ); return if !@$top; enrich childs => id => parent => sub { sql - "SELECT tp.parent, t.id, t.name, t.c_items FROM $table t JOIN ${table}_parents tp ON tp.$joincol = t.id WHERE $visible AND tp.parent IN", $_, 'ORDER BY name' + "SELECT tp.parent, t.id, t.name, t.c_items FROM $table t JOIN ${table}_parents tp ON tp.id = t.id WHERE NOT hidden AND tp.parent IN", $_, 'ORDER BY name' }, $top; $top = [ sort { $b->{childs}->@* <=> $a->{childs}->@* } @$top ] if $type eq 'g' || $id; my sub lnk_ { - a_ href => $type eq 'i' ? "/$type$_[0]{id}" : "/$_[0]{id}", $_[0]{name}; + a_ href => "/$_[0]{id}", $_[0]{name}; b_ class => 'grayedout', " ($_[0]{c_items})" if $_[0]{c_items}; } div_ class => 'mainbox', sub { @@ -58,7 +56,7 @@ sub tree_ { li_ sub { my $num = @$sub-5; txt_ '> '; - a_ href => $type eq 'i' ? "/$type$_->{id}" : "/$_->{id}", style => 'font-style: italic', sprintf '%d more %s%s', $num, $type eq 'g' ? 'tag' : 'trait', $num == 1 ? '' : 's'; + a_ href => "/$_->{id}", style => 'font-style: italic', sprintf '%d more %s%s', $num, $type eq 'g' ? 'tag' : 'trait', $num == 1 ? '' : 's'; } if @$sub > 6; } if @$sub; } for @$top; @@ -75,13 +73,11 @@ sub parents_ { my %t; my $table = $type eq 'g' ? 'tags' : 'traits'; - my $joincol = $type eq 'g' ? 'id' : 'trait'; - my $idtype = $type eq 'g' ? 'vndbid' : 'int'; push $t{$_->{child}}->@*, $_ for tuwf->dbAlli(' WITH RECURSIVE p(id,child,name) AS ( - SELECT ', \$t->{id}, "::$idtype, NULL::$idtype, NULL::text + SELECT ', \$t->{id}, "::vndbid, NULL::vndbid, NULL::text UNION - SELECT t.id, p.id, t.name FROM p JOIN ${table}_parents tp ON tp.$joincol = p.id JOIN $table t ON t.id = tp.parent + 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 WHERE child IS NOT NULL ORDER BY name ")->@*; @@ -94,7 +90,7 @@ sub parents_ { a_ href => "/$type", $type eq 'g' ? 'Tags' : 'Traits'; for (@$_) { txt_ ' > '; - a_ href => $type eq 'i' ? "/$type$_->{id}" : "/$_->{id}", $_->{name}; + a_ href => "/$_->{id}", $_->{name}; } txt_ ' > '; txt_ $t->{name}; diff --git a/lib/VNWeb/TT/List.pm b/lib/VNWeb/TT/List.pm index b7c65ca3..7c546ed4 100644 --- a/lib/VNWeb/TT/List.pm +++ b/lib/VNWeb/TT/List.pm @@ -22,9 +22,9 @@ sub listing_ { td_ class => 'tc2', $_->{c_items}||'-'; td_ class => 'tc3', sub { b_ class => 'grayedout', "$_->{group} / " if $_->{group}; - a_ href => "/$type$_->{id}", $_->{name}; + a_ href => "/$_->{id}", $_->{name}; join_ ',', sub { b_ class => 'grayedout', ' '.$_ }, - $_->{state} == 0 ? 'awaiting moderation' : $_->{state} == 1 ? 'deleted' : (), + !$_->{hidden} ? () : $_->{locked} ? 'deleted' : 'awaiting moderation', !$_->{applicable} ? 'not applicable' : (), !$_->{searchable} ? 'not searchable' : (); }; @@ -51,10 +51,9 @@ TUWF::get qr{/(?<type>[gi])/list}, sub { my $qs = $opt->{q} && '%'.sql_like($opt->{q}).'%'; my $where = sql_and - defined $opt->{t} ? ( - $type eq 'i' ? sql 'state =', \$opt->{t} : - $opt->{t} == 0 ? 'hidden AND NOT locked' : $opt->{t} == 1 ? 'hidden AND locked' : 'NOT hidden' - ) : (), + !defined $opt->{t} ? () : + $opt->{t} == 0 ? 'hidden AND NOT locked' : + $opt->{t} == 1 ? 'hidden AND locked' : 'NOT hidden', defined $opt->{a} ? sql 'applicable =', \$opt->{a} : (), defined $opt->{b} ? sql 'searchable =', \$opt->{b} : (), $opt->{q} ? sql 'name ILIKE', \$qs, 'OR alias ILIKE', \$qs : (); @@ -62,9 +61,7 @@ TUWF::get qr{/(?<type>[gi])/list}, sub { my $table = $type eq 'g' ? 'tags' : 'traits'; my $count = tuwf->dbVali("SELECT COUNT(*) FROM $table t WHERE", $where); my $list = tuwf->dbPagei({ results => 50, page => $opt->{p} },' - SELECT name, searchable, applicable, c_items,', sql_totime('added'), 'as added - , ', $type eq 'g' ? 'vndbid_num(id) AS id, CASE WHEN NOT hidden THEN 1+1 WHEN locked THEN 1 ELSE 0 END AS state' - : 'id, state', " + SELECT id, name, hidden, locked, searchable, applicable, c_items,', sql_totime('added'), "as added FROM $table WHERE ", $where, ' ORDER BY', {qw|added id name name items c_items|}->{$opt->{s}}, {qw|a ASC d DESC|}->{$opt->{o}}, ', id' diff --git a/lib/VNWeb/TT/TraitEdit.pm b/lib/VNWeb/TT/TraitEdit.pm index 04c4cd51..7744e181 100644 --- a/lib/VNWeb/TT/TraitEdit.pm +++ b/lib/VNWeb/TT/TraitEdit.pm @@ -3,50 +3,45 @@ package VNWeb::TT::TraitEdit; use VNWeb::Prelude; my $FORM = { - id => { required => 0, id => 1 }, + id => { required => 0, vndbid => 'i' }, name => { maxlength => 250, regex => qr/^[^,\r\n]+$/ }, alias => { maxlength => 1024, regex => qr/^[^,]+$/, required => 0, default => '' }, - state => { uint => 1, range => [0,2] }, sexual => { anybool => 1 }, description => { maxlength => 10240 }, searchable => { anybool => 1, default => 1 }, applicable => { anybool => 1, default => 1 }, defaultspoil => { uint => 1, range => [0,2] }, parents => { aoh => { - id => { id => 1 }, + parent => { vndbid => 'i' }, name => { _when => 'out' }, group => { _when => 'out', required => 0 }, } }, order => { uint => 1 }, + hidden => { anybool => 1 }, + locked => { anybool => 1 }, - addedby => { _when => 'out' }, - can_mod => { _when => 'out', anybool => 1 }, + authmod => { _when => 'out', anybool => 1 }, + editsum => { _when => 'in out', editsum => 1 }, }; my $FORM_OUT = form_compile out => $FORM; my $FORM_IN = form_compile in => $FORM; +my $FORM_CMP = form_compile cmp => $FORM; -TUWF::get qr{/$RE{iid}/edit}, sub { - my $e = tuwf->dbRowi(' - SELECT i.id, i.name, i.alias, i.description, i.state, i.sexual, i.defaultspoil, i.searchable, i.applicable, i.order - , ', sql_user('u', 'addedby_'), ' - FROM traits i - LEFT JOIN users u ON i.addedby = u.id - WHERE i.id =', \tuwf->capture('id') - ); +TUWF::get qr{/$RE{irev}/edit}, sub { + my $e = db_entry tuwf->captures('id','rev'); return tuwf->resNotFound if !$e->{id}; - - enrich parents => id => trait => ' - SELECT ip.trait, i.id, i.name, g.name AS group - FROM traits_parents ip JOIN traits i ON i.id = ip.parent LEFT JOIN traits g ON g.id = i.group WHERE ip.trait IN', $e; - return tuwf->resDenied if !can_edit i => $e; - $e->{addedby} = xml_string sub { user_ $e, 'addedby_'; }; - $e->{can_mod} = auth->permTagmod; + enrich_merge parent => ' + SELECT i.id AS parent, i.name, g.name AS group + FROM traits i LEFT JOIN traits g ON g.id = i.group WHERE i.id IN', $e->{parents}; - framework_ title => "Edit $e->{name}", type => 'i', dbobj => $e, tab => 'edit', sub { + $e->{authmod} = auth->permTagmod; + $e->{editsum} = $e->{chrev} == $e->{maxrev} ? '' : "Reverted to revision $e->{id}.$e->{chrev}"; + + framework_ title => "Edit $e->{name}", dbobj => $e, tab => 'edit', sub { elm_ TraitEdit => $FORM_OUT, $e; }; }; @@ -54,12 +49,12 @@ TUWF::get qr{/$RE{iid}/edit}, sub { TUWF::get qr{/(?:$RE{iid}/add|i/new)}, sub { my $id = tuwf->capture('id'); - my $i = tuwf->dbRowi('SELECT i.id, i.name, g.name AS "group", i.sexual FROM traits i LEFT JOIN traits g ON g.id = i."group" WHERE i.id =', \$id); + my $i = tuwf->dbRowi('SELECT i.id AS parent, i.name, g.name AS "group", i.sexual FROM traits i LEFT JOIN traits g ON g.id = i."group" WHERE i.id =', \$id); return tuwf->resDenied if !can_edit i => {}; - return tuwf->resNotFound if $id && !$i->{id}; + return tuwf->resNotFound if $id && !$i->{parent}; my $e = elm_empty($FORM_OUT); - $e->{can_mod} = auth->permTagmod; + $e->{authmod} = auth->permTagmod; if($id) { $e->{parents} = [$i]; $e->{sexual} = $i->{sexual}; @@ -85,58 +80,48 @@ TUWF::get qr{/(?:$RE{iid}/add|i/new)}, sub { elm_api TraitEdit => $FORM_OUT, $FORM_IN, sub { my($data) = @_; - my $id = delete $data->{id}; - my $e = !$id ? {} : tuwf->dbRowi('SELECT id, addedby, state FROM traits WHERE id =', \$id); - return tuwf->resNotFound if $id && !$e->{id}; + my $new = !$data->{id}; + my $e = $new ? {} : db_entry $data->{id} or return tuwf->resNotFound; + return tuwf->resNotFound if !$new && !$e->{id}; return elm_Unauth if !can_edit i => $e; - - $data->{addedby} = $id ? $e->{addedby} : auth->uid; if(!auth->permTagmod) { - $data->{state} = 0; - $data->{applicable} = $data->{searchable} = 1; + $data->{hidden} = $e->{hidden}//1; + $data->{locked} = $e->{locked}//0; } $data->{order} = 0 if $data->{parents}->@*; # Make sure parent IDs exists and are not a child trait of the current trait (i.e. don't allow cycles) - my @parents = map $_->{id}, $data->{parents}->@*; + my @parents = map $_->{parent}, $data->{parents}->@*; validate_dbid sub { 'SELECT id FROM traits WHERE', sql_and - $id ? sql 'id NOT IN(WITH RECURSIVE t(id) AS (SELECT', \$id, '::int UNION SELECT trait FROM traits_parents tp JOIN t ON t.id = tp.parent) SELECT id FROM t)' : (), + $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; # 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). - $data->{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 IN', \@parents, 'ORDER BY id LIMIT 1'); + + $data->{description} = bb_subst_links($data->{description}); # (Ideally this checks all groups that this trait applies in, but that's more annoying to implement) my $re = '[\t\s]*\n[\t\s]*'; my $dups = tuwf->dbAlli(' - SELECT \'i\'||n.id, n.name + SELECT n.id, n.name FROM (SELECT id, name FROM traits UNION ALL SELECT id, s FROM traits, regexp_split_to_table(alias, ', \$re, ') a(s) WHERE s <> \'\') n(id,name) JOIN traits t ON n.id = t.id WHERE ', sql_and( - $id ? sql 'n.id <>', \$id : (), - sql('t."group" IS NOT DISTINCT FROM', \$data->{group}), + $new ? () : sql('n.id <>', \$e->{id}), + sql('t."group" IS NOT DISTINCT FROM', \$group), sql 'lower(n.name) IN', [ map lc($_), $data->{name}, grep length($_), split /$re/, $data->{alias} ] ) ); return elm_DupNames $dups if @$dups; - $data->{description} = bb_subst_links($data->{description}); - - my %set = map +($_,$data->{$_}), qw/name alias description state addedby sexual defaultspoil searchable applicable/; - $set{'"group"'} = $data->{group}; - $set{'"order"'} = $data->{order}; - $set{added} = sql 'NOW()' if $id && $data->{state} == 2 && $e->{state} != 2; - tuwf->dbExeci('UPDATE traits SET', \%set, 'WHERE id =', \$id) if $id; - $id = tuwf->dbVali('INSERT INTO traits', \%set, 'RETURNING id') if !$id; - - tuwf->dbExeci('DELETE FROM traits_parents WHERE trait =', \$id); - tuwf->dbExeci('INSERT INTO traits_parents (trait,parent) VALUES(', \$id, ',', \$_->{id}, ')') for $data->{parents}->@*; - - auth->audit(undef, 'trait edit', "i$id") if $id; # Since we don't have edit histories for traits yet. - elm_Redirect "/i$id"; + 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}); + elm_Redirect "/$ch->{nitemid}.$ch->{nrev}"; }; 1; diff --git a/lib/VNWeb/TT/TraitPage.pm b/lib/VNWeb/TT/TraitPage.pm index 8679ed91..bb72b755 100644 --- a/lib/VNWeb/TT/TraitPage.pm +++ b/lib/VNWeb/TT/TraitPage.pm @@ -7,12 +7,31 @@ use VNWeb::Images::Lib; use VNWeb::TT::Lib 'tree_', 'parents_'; +sub rev_ { + my($t) = @_; + sub enrich_item { + enrich_merge parent => 'SELECT id AS parent, name FROM traits WHERE id IN', $_[0]{parents}; + } + enrich_item $t; + revision_ $t, \&enrich_item, + [ name => 'Name' ], + [ alias => 'Aliases' ], + [ description => 'Description' ], + [ sexual => 'Sexual content',fmt => 'bool' ], + [ searchable => 'Searchable', fmt => 'bool' ], + [ applicable => 'Applicable', fmt => 'bool' ], + [ defaultspoil => 'Default spoiler level' ], + [ order => 'Sort order' ], + [ parents => 'Parent traits', fmt => sub { a_ href => "/$_->{parent}", $_->{name}; } ]; +} + + sub infobox_ { my($t) = @_; p_ class => 'mainopts', sub { - a_ href => "/i$t->{id}/add", 'Create child trait'; - } if $t->{state} != 1 && can_edit i => {}; + a_ href => "/$t->{id}/add", 'Create child trait'; + } if !$t->{hidden} && can_edit i => {}; h1_ "Trait: $t->{name}"; debug_ $t; @@ -25,12 +44,12 @@ sub infobox_ { a_ href => '/t/db', 'discussion board'; txt_ ' if you disagree with this.'; } - } if $t->{state} == 1; + } if $t->{hidden} && $t->{locked}; div_ class => 'notice', sub { h2_ 'Waiting for approval'; p_ 'This trait is waiting for a moderator to approve it.'; - } if $t->{state} == 0; + } if $t->{hidden} && !$t->{locked}; parents_ i => $t; @@ -74,7 +93,7 @@ sub chars_ { my $q = eval { my $f = filter_parse c => $opt->{fil}; # Old URLs often had the trait ID as part of the filter, let's remove that. - $f->{trait_inc} = [ grep $_ != $t->{id}, $f->{trait_inc}->@* ] if $f->{trait_inc}; + $f->{trait_inc} = [ grep "i$_" ne $t->{id}, $f->{trait_inc}->@* ] if $f->{trait_inc}; delete $f->{trait_inc} if $f->{trait_inc} && !$f->{trait_inc}->@*; $f = filter_char_adv $f; tuwf->compile({ advsearch => 'c' })->validate(@$f > 1 ? $f : undef)->data; @@ -103,7 +122,7 @@ sub chars_ { enrich_image_obj image => $list if !$opt->{s}->rows; $time = time - $time; - form_ action => "/i$t->{id}", method => 'get', sub { + form_ action => "/$t->{id}", method => 'get', sub { div_ class => 'mainbox', sub { h1_ 'Characters'; p_ class => 'browseopts', sub { @@ -120,14 +139,15 @@ sub chars_ { } -TUWF::get qr{/$RE{iid}}, sub { - my $t = tuwf->dbRowi('SELECT id, name, alias, description, state, c_items, sexual, searchable, applicable FROM traits WHERE id =', \tuwf->capture('id')); +TUWF::get qr{/$RE{irev}}, sub { + my $t = db_entry tuwf->captures('id', 'rev'); return tuwf->resNotFound if !$t->{id}; - framework_ index => $t->{state} == 2, title => "Trait: $t->{name}", type => 'i', dbobj => $t, sub { + framework_ index => !$t->{hidden}, title => "Trait: $t->{name}", dbobj => $t, sub { + rev_ $t if tuwf->capture('rev'); div_ class => 'mainbox', sub { infobox_ $t; }; tree_ i => $t->{id}; - chars_ $t if $t->{searchable} && $t->{state} == 2; + chars_ $t if $t->{searchable} && !$t->{hidden}; }; }; diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm index d0ee6e32..9bdffba9 100644 --- a/lib/VNWeb/User/Notifications.pm +++ b/lib/VNWeb/User/Notifications.pm @@ -212,7 +212,7 @@ TUWF::get qr{/$RE{uid}/notify/$RE{num}/(?<lid>[a-z0-9\.]+)}, sub { # It's a bit annoying to add auth->notiRead() to each revision page, so do that in bulk with a simple hook. TUWF::hook before => sub { - auth->notiRead($+{vndbid}, $+{rev}) if auth && tuwf->reqPath() =~ qr{^/(?<vndbid>[vrpcsdg]$RE{num})\.(?<rev>$RE{num})$}; + auth->notiRead($+{vndbid}, $+{rev}) if auth && tuwf->reqPath() =~ qr{^/(?<vndbid>[vrpcsdgi]$RE{num})\.(?<rev>$RE{num})$}; }; diff --git a/sql/func.sql b/sql/func.sql index 8b58caa3..d8cceb47 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -287,9 +287,9 @@ BEGIN UNION ALL SELECT lvl-1, tp.parent, tc.cid, tc.spoiler FROM traits_chars_all tc - JOIN traits_parents tp ON tp.trait = tc.tid + JOIN traits_parents tp ON tp.id = tc.tid JOIN traits t ON t.id = tp.parent - WHERE t.state = 2 + WHERE NOT t.hidden AND tc.lvl > 0 ) -- now grouped by (tid, cid), with non-searchable traits filtered out @@ -308,6 +308,7 @@ END; $$ LANGUAGE plpgsql SECURITY DEFINER; + -- Fully recalculate all rows in stats_cache CREATE OR REPLACE FUNCTION update_stats_cache_full() RETURNS void AS $$ BEGIN @@ -317,7 +318,7 @@ BEGIN UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars'; UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff'; UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE hidden = FALSE) WHERE section = 'tags'; - UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits'; + UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE hidden = FALSE) WHERE section = 'traits'; END; $$ LANGUAGE plpgsql; @@ -341,7 +342,7 @@ $$ LANGUAGE SQL; -- A VIEW that can be joined would offer much better optimization possibilities, but I've not managed to write that in a performant way yet. -- A MATERIALIZED VIEW would likely be the fastest approach, but keeping that up-to-date seems like a pain. -- --- Not currently supported: i#, u#, ch#, cv#, sf# +-- Not currently supported: u#, ch#, cv#, sf# CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title text, uid vndbid) AS $$ -- x#.# SELECT v.title, h.requester FROM changes h JOIN vn_hist v ON h.id = v.chid WHERE vndbid_type($1) = 'v' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 @@ -350,6 +351,7 @@ CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title tex UNION ALL SELECT c.name, h.requester FROM changes h JOIN chars_hist c ON h.id = c.chid WHERE vndbid_type($1) = 'c' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 UNION ALL SELECT d.title, h.requester FROM changes h JOIN docs_hist d ON h.id = d.chid WHERE vndbid_type($1) = 'd' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 UNION ALL SELECT g.name, h.requester FROM changes h JOIN tags_hist g ON h.id = g.chid WHERE vndbid_type($1) = 'g' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT i.name, h.requester FROM changes h JOIN traits_hist i ON h.id = i.chid WHERE vndbid_type($1) = 'i' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 UNION ALL SELECT sa.name, h.requester FROM changes h JOIN staff_hist s ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.aid WHERE vndbid_type($1) = 's' AND h.itemid = $1 AND $2 IS NOT NULL AND h.rev = $2 -- x# UNION ALL SELECT title, NULL FROM vn WHERE vndbid_type($1) = 'v' AND id = $1 AND $2 IS NULL @@ -358,6 +360,7 @@ CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title tex UNION ALL SELECT name, NULL FROM chars WHERE vndbid_type($1) = 'c' AND id = $1 AND $2 IS NULL UNION ALL SELECT title, NULL FROM docs WHERE vndbid_type($1) = 'd' AND id = $1 AND $2 IS NULL UNION ALL SELECT name, NULL FROM tags WHERE vndbid_type($1) = 'g' AND id = $1 AND $2 IS NULL + UNION ALL SELECT name, NULL FROM traits WHERE vndbid_type($1) = 'i' AND id = $1 AND $2 IS NULL UNION ALL SELECT sa.name, NULL FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE vndbid_type($1) = 's' AND s.id = $1 AND $2 IS NOT NULL AND $2 IS NULL -- t# UNION ALL SELECT title, NULL FROM threads WHERE vndbid_type($1) = 't' AND id = $1 AND $2 IS NULL @@ -514,7 +517,7 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid vndbid) RETURNS T AND c_pre.itemid = $1 AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed AND c_all.itemid = $1 -- All edits on this entry, to see whom to notify AND c_cur.ihid AND NOT c_pre.ihid - AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g') + AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g', 'i') -- listdel UNION @@ -535,7 +538,7 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid vndbid) RETURNS T FROM changes c JOIN users u ON u.id = c.requester WHERE c.itemid = $1 - AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g') + AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g', 'i') AND $3 <> 'u1' -- Exclude edits by Multi AND u.notify_dbedit AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = c.requester AND ns.subnum = false) @@ -544,7 +547,7 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid vndbid) RETURNS T UNION SELECT 'subedit', ns.uid FROM notification_subs ns - WHERE $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g') + WHERE $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd', 'g', 'i') AND $3 <> 'u1' -- Exclude edits by Multi AND ns.iid = $1 AND ns.subnum @@ -604,8 +607,8 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid vndbid) RETURNS T FROM notification_subs WHERE subapply AND vndbid_type($1) = 'c' AND $2 IS NOT NULL AND iid IN( - WITH new(tid) AS (SELECT vndbid('i', tid) FROM chars_traits_hist WHERE chid = (SELECT id FROM changes WHERE itemid = $1 AND rev = $2)), - old(tid) AS (SELECT vndbid('i', tid) FROM chars_traits_hist WHERE chid = (SELECT id FROM changes WHERE itemid = $1 AND $2 > 1 AND rev = $2-1)) + WITH new(tid) AS (SELECT tid FROM chars_traits_hist WHERE chid = (SELECT id FROM changes WHERE itemid = $1 AND rev = $2)), + old(tid) AS (SELECT tid FROM chars_traits_hist WHERE chid = (SELECT id FROM changes WHERE itemid = $1 AND $2 > 1 AND rev = $2-1)) (SELECT tid FROM old EXCEPT SELECT tid FROM new) UNION (SELECT tid FROM new EXCEPT SELECT tid FROM old) ) diff --git a/sql/perms.sql b/sql/perms.sql index 0d293bbc..1d2e89ec 100644 --- a/sql/perms.sql +++ b/sql/perms.sql @@ -67,9 +67,11 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_options TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_votes TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site; GRANT INSERT ON trace_log TO vndb_site; -GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON traits TO vndb_site; +GRANT SELECT, INSERT ON traits_hist TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_site; -GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site; +GRANT SELECT, INSERT, DELETE ON traits_parents TO vndb_site; +GRANT SELECT, INSERT ON traits_parents_hist TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site; @@ -167,6 +169,7 @@ GRANT SELECT ON threads TO vndb_multi; GRANT SELECT ON threads_boards TO vndb_multi; GRANT SELECT ON threads_posts TO vndb_multi; GRANT SELECT, UPDATE ON traits TO vndb_multi; +GRANT SELECT ON traits_hist TO vndb_multi; GRANT SELECT ON traits_chars TO vndb_multi; -- traits_chars_calc() is SECURITY DEFINER GRANT SELECT ON traits_parents TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi; diff --git a/sql/schema.sql b/sql/schema.sql index 1e67c867..e053eafc 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -84,6 +84,7 @@ CREATE SEQUENCE reviews_seq; CREATE SEQUENCE screenshots_seq; CREATE SEQUENCE staff_id_seq; CREATE SEQUENCE tags_id_seq; +CREATE SEQUENCE traits_id_seq; CREATE SEQUENCE threads_id_seq; CREATE SEQUENCE vn_id_seq; CREATE SEQUENCE users_id_seq; @@ -180,7 +181,7 @@ CREATE TABLE chars_hist ( -- chars_traits CREATE TABLE chars_traits ( id vndbid NOT NULL, -- [pub] - tid integer NOT NULL, -- [pub] traits.id + tid vndbid NOT NULL, -- [pub] traits.id spoil smallint NOT NULL DEFAULT 0, -- [pub] PRIMARY KEY(id, tid) ); @@ -188,7 +189,7 @@ CREATE TABLE chars_traits ( -- chars_traits_hist CREATE TABLE chars_traits_hist ( chid integer NOT NULL, - tid integer NOT NULL, -- traits.id + tid vndbid NOT NULL, -- traits.id spoil smallint NOT NULL DEFAULT 0, PRIMARY KEY(chid, tid) ); @@ -841,9 +842,9 @@ CREATE TABLE trace_log ( ); -- traits -CREATE TABLE traits ( - id SERIAL PRIMARY KEY, -- [pub] - "group" integer, -- [pub] +CREATE TABLE traits ( -- dbentry_type=i + id vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('i', nextval('traits_id_seq')::int) CONSTRAINT traits_id_check CHECK(vndbid_type(id) = 'i'), -- [pub] + "group" vndbid, -- [pub] added timestamptz NOT NULL DEFAULT NOW(), addedby vndbid, c_items integer NOT NULL DEFAULT 0, @@ -853,9 +854,24 @@ CREATE TABLE traits ( sexual boolean NOT NULL DEFAULT false, -- [pub] searchable boolean NOT NULL DEFAULT true, -- [pub] applicable boolean NOT NULL DEFAULT true, -- [pub] - name varchar(250) NOT NULL, -- [pub] + name varchar(250) NOT NULL DEFAULT '', -- [pub] alias varchar(500) NOT NULL DEFAULT '', -- [pub] - description text NOT NULL DEFAULT '' -- [pub] + description text NOT NULL DEFAULT '', -- [pub] + hidden boolean NOT NULL DEFAULT TRUE, + locked boolean NOT NULL DEFAULT FALSE +); + +-- traits_hist +CREATE TABLE traits_hist ( + chid integer NOT NULL, + "order" smallint NOT NULL DEFAULT 0, + defaultspoil smallint NOT NULL DEFAULT 0, + sexual boolean NOT NULL DEFAULT false, + searchable boolean NOT NULL DEFAULT true, + applicable boolean NOT NULL DEFAULT true, + name varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + description text NOT NULL DEFAULT '' ); -- traits_chars @@ -864,15 +880,22 @@ CREATE TABLE traits ( -- key constraints on this table. CREATE TABLE traits_chars ( cid vndbid NOT NULL, -- chars (id) - tid integer NOT NULL, -- traits (id) + tid vndbid NOT NULL, -- traits (id) spoil smallint NOT NULL DEFAULT 0 ); -- traits_parents CREATE TABLE traits_parents ( - trait integer NOT NULL, -- [pub] - parent integer NOT NULL, -- [pub] - PRIMARY KEY(trait, parent) + id vndbid NOT NULL, -- [pub] + parent vndbid NOT NULL, -- [pub] + PRIMARY KEY(id, parent) +); + +-- traits_parents_hist +CREATE TABLE traits_parents_hist ( + chid integer NOT NULL, + parent vndbid NOT NULL, + PRIMARY KEY(chid, parent) ); -- ulist_labels diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 33003a3b..d561c77a 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -59,6 +59,7 @@ ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id); ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE tags_hist ADD CONSTRAINT tags_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_id_fkey FOREIGN KEY (id) REFERENCES tags (id); ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id); ALTER TABLE tags_parents_hist ADD CONSTRAINT tags_parents_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); @@ -72,10 +73,12 @@ ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fke ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE; -ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); -ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); +ALTER TABLE traits_hist ADD CONSTRAINT traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_id_fkey FOREIGN KEY (id) REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); +ALTER TABLE traits_parents_hist ADD CONSTRAINT traits_parents_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE traits_parents_hist ADD CONSTRAINT traits_parents_hist_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); ALTER TABLE ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/sql/triggers.sql b/sql/triggers.sql index 13e681fb..74a6b413 100644 --- a/sql/triggers.sql +++ b/sql/triggers.sql @@ -36,24 +36,14 @@ CREATE TRIGGER users_imgvotes_update AFTER INSERT OR DELETE ON image_votes FOR E -- the stats_cache table CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ -DECLARE - unhidden boolean; - hidden boolean; BEGIN IF TG_OP = 'INSERT' THEN UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; ELSIF TG_OP = 'UPDATE' THEN - IF TG_TABLE_NAME = 'traits' THEN - unhidden := OLD.state <> 2 AND NEW.state = 2; - hidden := OLD.state = 2 AND NEW.state <> 2; - ELSE - unhidden := OLD.hidden AND NOT NEW.hidden; - hidden := NEW.hidden AND NOT OLD.hidden; - END IF; - IF unhidden THEN + IF OLD.hidden AND NOT NEW.hidden THEN UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSIF hidden THEN + ELSIF NEW.hidden AND NOT OLD.hidden THEN UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; END IF; END IF; @@ -73,8 +63,8 @@ CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EAC CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); @@ -191,7 +181,7 @@ CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH RO --- NOTIFY on insert into changes/posts/tags/trait/reviews +-- NOTIFY on insert into changes/posts/reviews CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ BEGIN @@ -199,8 +189,6 @@ BEGIN NOTIFY newrevision; ELSIF TG_TABLE_NAME = 'threads_posts' THEN NOTIFY newpost; - ELSIF TG_TABLE_NAME = 'traits' THEN - NOTIFY newtrait; ELSIF TG_TABLE_NAME = 'reviews' THEN NOTIFY newreview; END IF; @@ -210,7 +198,6 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); CREATE TRIGGER insert_notify AFTER INSERT ON reviews FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); diff --git a/util/dbdump.pl b/util/dbdump.pl index 3a764df7..5777b168 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -58,7 +58,7 @@ use VNDB::Schema; my %tables = ( anime => { where => 'id IN(SELECT va.aid FROM vn_anime va JOIN vn v ON v.id = va.id WHERE NOT v.hidden)' }, chars => { where => 'NOT hidden' }, - chars_traits => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE state = 2)' }, + chars_traits => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE NOT hidden)' }, chars_vns => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden)' .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)' .' AND (rid IS NULL OR rid IN(SELECT id FROM releases WHERE NOT hidden))' @@ -86,8 +86,8 @@ my %tables = ( tags => { where => 'NOT hidden' }, tags_parents => { where => 'id IN(SELECT id FROM tags WHERE NOT hidden)' }, tags_vn => { where => 'tag IN(SELECT id FROM tags WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'tag, vid, uid, date' }, - traits => { where => 'state = 2' }, - traits_parents => { where => 'trait IN(SELECT id FROM traits WHERE state = 2)' }, + traits => { where => 'NOT hidden' }, + traits_parents => { where => 'trait IN(SELECT id FROM traits WHERE NOT hidden)' }, ulist_labels => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' }, ulist_vns => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' .' AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl' @@ -377,9 +377,9 @@ sub export_traits { require PerlIO::gzip; my $lst = $db->selectall_arrayref(q{ - SELECT id, name, alias AS aliases, description, searchable, applicable, c_items AS chars, - (SELECT string_agg(parent::text, ',') FROM traits_parents WHERE trait = id) AS parents - FROM traits WHERE state = 2 ORDER BY id + 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 + FROM traits t WHERE NOT hidden ORDER BY id }, { Slice => {} }); for(@$lst) { $_->{id} *= 1; diff --git a/util/devdump.pl b/util/devdump.pl index f43b717e..e90ac335 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -48,7 +48,6 @@ my $images = $db->selectcol_arrayref(q{ sub copy { my($dest, $sql, $specials) = @_; - warn $dest; $sql ||= "SELECT * FROM $dest"; $specials ||= {}; @@ -125,8 +124,7 @@ sub copy_entry { # Tags & traits copy_entry [qw/tags tags_parents/], $db->selectcol_arrayref('SELECT id FROM tags'); - copy traits => undef, {addedby => 'user'}; - copy 'traits_parents'; + copy_entry [qw/traits traits_parents/], $db->selectcol_arrayref('SELECT id FROM traits'); # Wikidata (TODO: This could be a lot more selective) copy 'wikidata'; diff --git a/util/unusedimages.pl b/util/unusedimages.pl index 019d3c9d..d3050ea6 100755 --- a/util/unusedimages.pl +++ b/util/unusedimages.pl @@ -59,7 +59,9 @@ sub cleandb { UNION ALL SELECT "desc" FROM staff UNION ALL SELECT "desc" FROM staff_hist UNION ALL SELECT description FROM tags + UNION ALL SELECT description FROM tags_hist UNION ALL SELECT description FROM traits + UNION ALL SELECT description FROM traits_hist UNION ALL SELECT comments FROM changes UNION ALL SELECT msg FROM threads_posts UNION ALL SELECT msg FROM reviews_posts diff --git a/util/updates/wip-trait-history.sql b/util/updates/wip-trait-history.sql new file mode 100644 index 00000000..a940799f --- /dev/null +++ b/util/updates/wip-trait-history.sql @@ -0,0 +1,74 @@ +BEGIN; + +ALTER TABLE chars_traits DROP CONSTRAINT chars_traits_tid_fkey; +ALTER TABLE chars_traits_hist DROP CONSTRAINT chars_traits_hist_tid_fkey; +ALTER TABLE traits DROP CONSTRAINT traits_group_fkey; +ALTER TABLE traits_parents DROP CONSTRAINT traits_parents_trait_fkey; +ALTER TABLE traits_parents DROP CONSTRAINT traits_parents_parent_fkey; + +DROP TRIGGER insert_notify ON traits; +DROP TRIGGER stats_cache_new ON traits; +DROP TRIGGER stats_cache_edit ON traits; + +ALTER TABLE traits ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE; +ALTER TABLE traits ADD COLUMN locked boolean NOT NULL DEFAULT TRUE; +UPDATE traits SET hidden = (state <> 2), locked = (state = 1); +ALTER TABLE traits DROP COLUMN state; + +ALTER TABLE traits ALTER COLUMN id DROP DEFAULT; +ALTER TABLE traits ALTER COLUMN id TYPE vndbid USING vndbid('i', id); +ALTER TABLE traits ALTER COLUMN id SET DEFAULT vndbid('i', nextval('traits_id_seq')::int); +ALTER TABLE traits ADD CONSTRAINT traits_id_check CHECK(vndbid_type(id) = 'i'); + +ALTER TABLE traits ALTER COLUMN "group" TYPE vndbid USING vndbid('i', "group"); +ALTER TABLE traits ALTER COLUMN name SET DEFAULT ''; + +ALTER TABLE traits_parents RENAME COLUMN trait TO id; +ALTER TABLE traits_parents ALTER COLUMN id TYPE vndbid USING vndbid('i', id); +ALTER TABLE traits_parents ALTER COLUMN parent TYPE vndbid USING vndbid('i', parent); + +ALTER TABLE traits_chars ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); +ALTER TABLE chars_traits ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); +ALTER TABLE chars_traits_hist ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); + +CREATE TABLE traits_hist ( + chid integer NOT NULL, + "order" smallint NOT NULL DEFAULT 0, + defaultspoil smallint NOT NULL DEFAULT 0, + sexual boolean NOT NULL DEFAULT false, + searchable boolean NOT NULL DEFAULT true, + applicable boolean NOT NULL DEFAULT true, + name varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + description text NOT NULL DEFAULT '' +); + +CREATE TABLE traits_parents_hist ( + chid integer NOT NULL, + parent vndbid NOT NULL, + PRIMARY KEY(chid, parent) +); + + +INSERT INTO changes (requester,itemid,rev,ihid,ilock,comments) + SELECT 'u1', id, 1, hidden, locked, +'Automated import from when the trait database did not keep track of change histories. +This trait was initially submitted by '||coalesce(nullif(addedby::text, 'u1'), 'an anonymous user')||' on '||added::date||', but has no doubt been updated over time by moderators.' + FROM traits; + +INSERT INTO traits_hist (chid, "order", defaultspoil, sexual, searchable, applicable, name, description, alias) + SELECT c.id, t."order", t.defaultspoil, t.sexual, t.searchable, t.applicable, t.name, t.description, t.alias + FROM traits t JOIN changes c ON c.itemid = t.id; + +INSERT INTO traits_parents_hist (chid, parent) SELECT c.id, t.parent FROM traits_parents t JOIN changes c ON c.itemid = t.id; + +ALTER TABLE traits DROP COLUMN addedby; + +\i sql/func.sql +\i sql/editfunc.sql + +COMMIT; + +\i sql/tableattrs.sql +\i sql/triggers.sql +\i sql/perms.sql |