diff options
author | Yorhel <git@yorhel.nl> | 2020-12-31 09:35:07 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-01-03 10:39:57 +0100 |
commit | 65e36750ec6ed95d82529146887dd82b2e9dde1c (patch) | |
tree | e28d26909bf2e0f94cebbca54ca9d96306b53253 | |
parent | 61cb7cd185fbf9203041ca366a2eb915e746d6f4 (diff) |
AdvSearch: Add feature to save/load/delete queries
-rw-r--r-- | data/style.css | 7 | ||||
-rw-r--r-- | elm/AdvSearch/Lib.elm | 8 | ||||
-rw-r--r-- | elm/AdvSearch/Main.elm | 212 | ||||
-rw-r--r-- | elm/Lib/Api.elm | 1 | ||||
-rw-r--r-- | lib/VNWeb/AdvSearch.pm | 34 | ||||
-rw-r--r-- | lib/VNWeb/Elm.pm | 13 | ||||
-rw-r--r-- | lib/VNWeb/Misc/AdvSearch.pm | 40 | ||||
-rw-r--r-- | sql/perms.sql | 1 | ||||
-rw-r--r-- | sql/schema.sql | 9 | ||||
-rw-r--r-- | sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | util/updates/2021-01-03-advsearch-saved-queries.sql | 10 |
11 files changed, 271 insertions, 65 deletions
diff --git a/data/style.css b/data/style.css index bfe5d754..8303a4ae 100644 --- a/data/style.css +++ b/data/style.css @@ -88,7 +88,8 @@ div.warning h2, div.notice h2 { font-size: 13px; font-weight: bold; margin: 0; } .maintabs .elm_dd > a { box-sizing: border-box; height: 21px; padding: 1px 15px 0 7px; border: 1px solid $border$; border-bottom: none; background-color: $tabbg$; color: $maintext$ } .elm_votedd .elm_dd ul li { text-align: left } -.elm_dd_input .elm_dd > a { background-color: $secbg$; color: $maintext$; border: 1px solid $secborder$; font: 14px "Tahoma", "Arial", sans-serif; padding: 1px 15px 1px 2px; margin: -1px } +.elm_dd_input .elm_dd > a { background-color: $secbg$; color: $maintext$; border: 1px solid $secborder$; font: 14px "Tahoma", "Arial", sans-serif; padding: 1px 15px 1px 2px; margin: -1px } +.elm_dd_button .elm_dd > a { background-color: $boxbg$; color: $maintext$; border: 1px solid $secborder$; font: 14px "Tahoma", "Arial", sans-serif; padding: 1px 15px 1px 5px; margin: -1px } .elm_dd_input.elm_dd_noarrow .elm_dd > a { padding-right: 2px } .elm_dd_noarrow .elm_dd > a { padding-right: 0 } .elm_dd_noarrow .elm_dd > a > span:last-child { display: none } @@ -1168,7 +1169,9 @@ p.filselect i { font-style: normal } .advsearch .advheader .opts { display: flex; justify-content: space-between; align-items: flex-end; min-width: 170px } .advsearch .advheader .opts > * { margin: 0; white-space: nowrap } .advsearch .advheader .opselect > * { display: inline-block; font-size: 18px; padding: 0 5px } -.advsearch input.submit { margin-top: 5px } + +.advsearch .optbuttons { margin-top: 5px } +.advsearch .optbuttons > .elm_dd_button { margin-top: 5px; margin-right: 10px; width: 120px; display: inline-block; } diff --git a/elm/AdvSearch/Lib.elm b/elm/AdvSearch/Lib.elm index 0820eeb6..36373ede 100644 --- a/elm/AdvSearch/Lib.elm +++ b/elm/AdvSearch/Lib.elm @@ -140,6 +140,14 @@ encQuery query = QTuple n o a b -> fint n ++ encTypeOp o 5 ++ fint a ++ fint b +showQType : QType -> String +showQType q = + case q of + V -> "v" + R -> "r" + C -> "c" + S -> "s" + showOp : Op -> String showOp op = case op of diff --git a/elm/AdvSearch/Main.elm b/elm/AdvSearch/Main.elm index 3565be81..1d1a03b8 100644 --- a/elm/AdvSearch/Main.elm +++ b/elm/AdvSearch/Main.elm @@ -6,10 +6,19 @@ import Html.Events exposing (..) import Browser import Set import Dict -import Array as A +import Task +import Browser.Dom as Dom +import Array as Array import Json.Encode as JE import Json.Decode as JD import Gen.Api as GApi +import Gen.AdvSearchSave as GASS +import Gen.AdvSearchDel as GASD +import Gen.AdvSearchLoad as GASL +import Lib.Html exposing (..) +import Lib.Api as Api +import Lib.DropDown as DD +import Lib.Autocomplete as A import AdvSearch.Lib exposing (..) import AdvSearch.Fields exposing (..) @@ -19,109 +28,214 @@ main = Browser.element { init = \e -> (init e, Cmd.none) , view = view , update = update - , subscriptions = \m -> Sub.map Field (fieldSub m.query) + , subscriptions = \m -> Sub.batch [ DD.sub m.saveDd, Sub.map Field (fieldSub m.query) ] } +type alias SQuery = { name: String, query: String } type alias Recv = - { query : JE.Value - , qtype : String - , uid : Maybe Int + { uid : Maybe Int , labels : List { id: Int, label: String } , defaultSpoil : Int - , producers : List GApi.ApiProducerResult - , staff : List GApi.ApiStaffResult - , tags : List GApi.ApiTagResult - , traits : List GApi.ApiTraitResult - , anime : List GApi.ApiAnimeResult + , saved : List SQuery + , query : GApi.ApiAdvSearchQuery } type alias Model = - { query : Field - , qtype : QType - , data : Data + { query : Field + , qtype : QType + , data : Data + , saved : List SQuery + , saveState : Api.State + , saveDd : DD.Config Msg + , saveAct : Int + , saveName : String + , saveDel : Set.Set String } type Msg - = Field FieldMsg + = Noop + | Field FieldMsg + | SaveToggle Bool + | SaveAct Int + | SaveName String + | SaveSave + | SaveSaved SQuery GApi.Response + | SaveLoad String + | SaveLoaded GApi.Response + | SaveDelSel String + | SaveDel + | SaveDeleted (Set.Set String) GApi.Response -- Add default set of fields (if they aren't present yet) and sort the list -normalize : Model -> Model -normalize model = +normalize : QType -> Field -> Data -> (Field, Data) +normalize qtype query odat = let present = List.foldl (\(n,_,_) a -> Set.insert n a) Set.empty - defaults pres = A.foldl (\f (al,dat,an) -> - if f.qtype == model.qtype && f.quick /= 0 && not (Set.member an pres) + defaults pres = Array.foldl (\f (al,dat,an) -> + if f.qtype == qtype && f.quick /= 0 && not (Set.member an pres) then let (ndat, nf) = fieldInit an dat in (nf::al, ndat, an+1) else (al,dat,an+1) - ) ([],model.data,0) fields + ) ([],odat,0) fields cmp (an,add,am) (bn,bdd,bm) = -- Sort active filters before empty ones, then order by 'quick', fallback to title - let aq = fieldToQuery model.data (an,add,am) /= Nothing - bq = fieldToQuery model.data (bn,bdd,bm) /= Nothing - af = A.get an fields - bf = A.get bn fields + let aq = fieldToQuery odat (an,add,am) /= Nothing + bq = fieldToQuery odat (bn,bdd,bm) /= Nothing + af = Array.get an fields + bf = Array.get bn fields ao = Maybe.andThen (\d -> if d.quick == 0 then Nothing else Just d.quick) af |> Maybe.withDefault 9999 bo = Maybe.andThen (\d -> if d.quick == 0 then Nothing else Just d.quick) bf |> Maybe.withDefault 9999 at = Maybe.map (\d -> d.title) af |> Maybe.withDefault "" bt = Maybe.map (\d -> d.title) bf |> Maybe.withDefault "" in if aq && not bq then LT else if not aq && bq then GT else if ao /= bo then compare ao bo else compare at bt - in case model.query of + in case query of (qid, qdd, FMNest qm) -> - let (nl, dat, _) = defaults (present qm.fields) + let (nl, ndat, _) = defaults (present qm.fields) nqm = { qm | fields = List.sortWith cmp (nl++qm.fields) } - in { model | query = (qid, qdd, FMNest nqm), data = dat } - _ -> model + in ((qid, qdd, FMNest nqm), ndat) + _ -> (query, odat) -init : Recv -> Model -init arg = +loadQuery : Data -> GApi.ApiAdvSearchQuery -> (QType, Field, Data) +loadQuery odat arg = let dat = { objid = 0 , level = 0 - , uid = arg.uid - , labels = (0, "Unlabeled") :: List.map (\e -> (e.id, e.label)) arg.labels - , defaultSpoil = arg.defaultSpoil - , producers = Dict.fromList <| List.map (\p -> (p.id,p)) <| arg.producers - , staff = Dict.fromList <| List.map (\s -> (s.id,s)) <| arg.staff - , tags = Dict.fromList <| List.map (\t -> (t.id,t)) <| arg.tags - , traits = Dict.fromList <| List.map (\t -> (t.id,t)) <| arg.traits - , anime = Dict.fromList <| List.map (\a -> (a.id,a)) <| arg.anime + , uid = odat.uid + , labels = odat.labels + , defaultSpoil = odat.defaultSpoil + , producers = Dict.union (Dict.fromList <| List.map (\p -> (p.id,p)) <| arg.producers) odat.producers + , staff = Dict.union (Dict.fromList <| List.map (\s -> (s.id,s)) <| arg.staff ) odat.staff + , tags = Dict.union (Dict.fromList <| List.map (\t -> (t.id,t)) <| arg.tags ) odat.tags + , traits = Dict.union (Dict.fromList <| List.map (\t -> (t.id,t)) <| arg.traits ) odat.traits + , anime = Dict.union (Dict.fromList <| List.map (\a -> (a.id,a)) <| arg.anime ) odat.anime } qtype = if arg.qtype == "v" then V else R - (ndat, query) = JD.decodeValue decodeQuery arg.query |> Result.toMaybe |> Maybe.withDefault (QAnd []) |> fieldFromQuery qtype dat + (dat2, query) = JD.decodeValue decodeQuery arg.query |> Result.toMaybe |> Maybe.withDefault (QAnd []) |> fieldFromQuery qtype dat -- We always want the top-level query to be a Nest type. - addtoplvl = let (_,m) = fieldCreate -1 (Tuple.mapSecond FMNest (nestInit True qtype qtype [query] ndat)) in m - nquery = case query of + addtoplvl = let (_,m) = fieldCreate -1 (Tuple.mapSecond FMNest (nestInit True qtype qtype [query] dat2)) in m + query2 = case query of (_,_,FMNest m) -> if m.qtype == qtype then query else addtoplvl _ -> addtoplvl + dat3 = { dat2 | objid = dat2.objid + 5 } -- +5 for the creation of query2 -- Is this a "simple" query? i.e. one that consists of at most a single level of nesting - isSimple = case nquery of + isSimple = case query2 of (_,_,FMNest m) -> List.all (\f -> case f of (_,_,FMNest _) -> False _ -> True) m.fields _ -> True - model = { query = nquery - , qtype = qtype - , data = { ndat | objid = ndat.objid + 5 } -- +5 for the creation of nQuery - } - in if isSimple then normalize model else model + (query3, dat4) = if isSimple then normalize qtype query2 dat3 else (query2, dat3) + in (qtype, query3, dat4) + + +init : Recv -> Model +init arg = + let dat = { objid = 0 + , level = 0 + , uid = arg.uid + , labels = (0, "Unlabeled") :: List.map (\e -> (e.id, e.label)) arg.labels + , defaultSpoil = arg.defaultSpoil + , producers = Dict.empty + , staff = Dict.empty + , tags = Dict.empty + , traits = Dict.empty + , anime = Dict.empty + } + (qtype, query, ndat) = loadQuery dat arg.query + in { query = query + , qtype = qtype + , data = ndat + , saved = arg.saved + , saveState = Api.Normal + , saveDd = DD.init "advsearch_save" SaveToggle + , saveAct = 0 + , saveName = "" + , saveDel = Set.empty + } update : Msg -> Model -> (Model, Cmd Msg) update msg model = case msg of + Noop -> (model, Cmd.none) Field m -> let (ndat, nm, nc) = fieldUpdate model.data m model.query in ({ model | data = ndat, query = nm }, Cmd.map Field nc) + SaveToggle b -> + let act = if model.saveAct == 0 && not (List.isEmpty model.saved) && fieldToQuery model.data model.query == Nothing then 1 else model.saveAct + in ( { model | saveDd = DD.toggle model.saveDd b, saveAct = act, saveDel = Set.empty } + , if b && act == 0 then Task.attempt (always Noop) (Dom.focus "advsearch_saveinput") else Cmd.none) + SaveAct n -> ({ model | saveAct = n, saveDel = Set.empty }, Cmd.none) + SaveName n -> ({ model | saveName = n }, Cmd.none) + SaveSave -> + case Maybe.map encQuery (fieldToQuery model.data model.query) of + Just q -> ({ model | saveState = Api.Loading }, GASS.send { name = model.saveName, qtype = showQType model.qtype, query = q } (SaveSaved { name = model.saveName, query = q }) ) + Nothing -> (model, Cmd.none) + SaveSaved q GApi.Success -> + let f rep lst = case lst of + (x::xs) -> + if x.name == q.name then q :: f True xs + else if not rep && x.name > q.name then q :: x :: f True xs + else x :: f rep xs + [] -> if rep then [] else [q] + in ({ model | saveState = Api.Normal, saveDd = DD.toggle model.saveDd False, saved = f False model.saved }, Cmd.none) + SaveSaved _ e -> ({ model | saveState = Api.Error e }, Cmd.none) + SaveLoad q -> ({ model | saveState = Api.Loading, saveDd = DD.toggle model.saveDd False }, GASL.send { qtype = showQType model.qtype, query = q } SaveLoaded) + SaveLoaded (GApi.AdvSearchQuery q) -> + let (_, query, dat) = loadQuery model.data q + in ({ model | saveState = Api.Normal, query = query, data = dat }, Cmd.none) + SaveLoaded e -> ({ model | saveState = Api.Error e }, Cmd.none) + SaveDelSel s -> ({ model | saveDel = (if Set.member s model.saveDel then Set.remove else Set.insert) s model.saveDel }, Cmd.none) + SaveDel -> ({ model | saveState = Api.Loading }, GASD.send { qtype = showQType model.qtype, name = Set.toList model.saveDel } (SaveDeleted model.saveDel)) + SaveDeleted d GApi.Success -> ({ model | saveState = Api.Normal, saveDel = Set.empty, saved = List.filter (\e -> not (Set.member e.name d)) model.saved }, Cmd.none) + SaveDeleted _ e -> ({ model | saveState = Api.Error e }, Cmd.none) + view : Model -> Html Msg -view model = div [ class "advsearch" ] - [ input [ type_ "hidden", id "f", name "f", value <| Maybe.withDefault "" <| Maybe.map encQuery (fieldToQuery model.data model.query) ] [] +view model = div [ class "advsearch" ] <| + let encQ = Maybe.withDefault "" <| Maybe.map encQuery (fieldToQuery model.data model.query) + in + [ input [ type_ "hidden", id "f", name "f", value encQ ] [] , Html.map Field (fieldView model.data model.query) - , input [ type_ "submit", class "submit", value "Search" ] [] + , div [ class "optbuttons" ] + [ if model.data.uid == Nothing then text "" else div [ class "elm_dd_button" ] + [ DD.view model.saveDd model.saveState (text "Save/Load") <| \() -> + [ div [ class "advheader", style "min-width" "250px" ] + [ div [ class "opts", style "margin-bottom" "5px" ] + [ if model.saveAct == 0 then b [] [ text "Save" ] else a [ href "#", onClickD (SaveAct 0) ] [ text "Save" ] + , if model.saveAct == 1 then b [] [ text "Load" ] else a [ href "#", onClickD (SaveAct 1) ] [ text "Load" ] + , if model.saveAct == 2 then b [] [ text "Delete" ] else a [ href "#", onClickD (SaveAct 2) ] [ text "Delete" ] + ] + , h3 [] [ text <| if model.saveAct == 0 then "Save current filter" else if model.saveAct == 1 then "Load filter" else "Delete saved filter" ] + ] + , case (model.saved, model.saveAct) of + (_, 0) -> + if encQ == "" then text "Nothing to save." else + form_ "" SaveSave False + [ inputText "advsearch_saveinput" model.saveName SaveName [ required True, maxlength 50, placeholder "Name...", style "width" "245px" ] + , if List.any (\e -> e.name == model.saveName) model.saved + then text "You already have a filter by that name, click save to overwrite it." + else text "" + , submitButton "Save" model.saveState True + ] + ([], _) -> text "You don't have any saved queries." + (l, 1) -> + div [] + [ if encQ == "" || List.any (\e -> encQ == e.query) l + then text "" else text "Unsaved changes will be lost when loading a saved filter." + , ul [] <| List.map (\e -> li [ style "overflow" "hidden", style "text-overflow" "ellipsis" ] [ a [ href "#", onClickD (SaveLoad e.query) ] [ text e.name ] ]) l + ] + (l, _) -> + div [] + [ ul [] <| List.map (\e -> li [ style "overflow" "hidden", style "text-overflow" "ellipsis" ] [ linkRadio (Set.member e.name model.saveDel) (always (SaveDelSel e.name)) [ text e.name ] ]) model.saved + , inputButton "Delete selected" SaveDel [ disabled (Set.isEmpty model.saveDel) ] + ] + ] + ] + , input [ type_ "submit", class "submit", value "Search" ] [] + ] ] diff --git a/elm/Lib/Api.elm b/elm/Lib/Api.elm index 3e69a4e1..514ac6d2 100644 --- a/elm/Lib/Api.elm +++ b/elm/Lib/Api.elm @@ -58,6 +58,7 @@ showResponse res = CharResult _ -> unexp AnimeResult _ -> unexp ImageResult _ -> unexp + AdvSearchQuery _ -> unexp expectResponse : (Response -> msg) -> Http.Expect msg diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm index 5ff0a5ed..26e06561 100644 --- a/lib/VNWeb/AdvSearch.pm +++ b/lib/VNWeb/AdvSearch.pm @@ -282,7 +282,7 @@ sub _enc_query { # # An implementation for the '!=' operator will be supplied automatically if it's not explicitely defined. # NOTE: That implementation does NOT work for NULL values. -my(%FIELDS, %NUMFIELDS); +our(%FIELDS, %NUMFIELDS); sub f { my($t, $num, $n, $v, @opts) = @_; my %f = ( @@ -641,6 +641,9 @@ sub sql_where { } +sub json { shift->{query} } + + sub _compact_json { my($t, $q) = @_; return [ $q->[0] eq 'and' ? 0 : 1, map _compact_json($t, $_), @$q[1..$#$q] ] if $q->[0] eq 'and' || $q->[0] eq 'or'; @@ -678,7 +681,8 @@ sub _extract_ids { } -sub elm_ { +# Returns a JSON object suitable for the AdvSearchQuery API response. +sub elm_search_query { my($self) = @_; my(%o,%ids); @@ -702,23 +706,27 @@ sub elm_ { $o{qtype} = $self->{type}; $o{query} = $self->compact_json; - $o{uid} = auth->uid; - $o{labels} = auth ? tuwf->dbAlli('SELECT id, label FROM ulist_labels WHERE uid =', \auth->uid, 'ORDER BY CASE WHEN id < 10 THEN id ELSE 10 END, label') : []; - $o{defaultSpoil} = auth->pref('spoilers')||0; + \%o +} + + +sub elm_ { + my($self) = @_; state $schema ||= tuwf->compile({ type => 'hash', keys => { - qtype => {}, - query => { type => 'array', required => 0 }, uid => { uint => 1, required => 0 }, labels => { aoh => { id => { uint => 1 }, label => {} } }, defaultSpoil => { uint => 1 }, - producers => $VNWeb::Elm::apis{ProducerResult}[0], - staff => $VNWeb::Elm::apis{StaffResult}[0], - tags => $VNWeb::Elm::apis{TagResult}[0], - traits => $VNWeb::Elm::apis{TraitResult}[0], - anime => $VNWeb::Elm::apis{AnimeResult}[0], + saved => { aoh => { name => {}, query => {} } }, + query => $VNWeb::Elm::apis{AdvSearchQuery}[0], }}); - VNWeb::HTML::elm_ 'AdvSearch.Main', $schema, \%o; + VNWeb::HTML::elm_ 'AdvSearch.Main', $schema, { + uid => auth->uid, + labels => auth ? tuwf->dbAlli('SELECT id, label FROM ulist_labels WHERE uid =', \auth->uid, 'ORDER BY CASE WHEN id < 10 THEN id ELSE 10 END, label') : [], + defaultSpoil => auth->pref('spoilers')||0, + saved => auth ? tuwf->dbAlli('SELECT name, query FROM saved_queries WHERE uid =', \auth->uid, ' AND qtype =', \$self->{type}, 'ORDER BY name') : [], + query => $self->elm_search_query(), + }; } diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm index 0fd24d36..96ccc583 100644 --- a/lib/VNWeb/Elm.pm +++ b/lib/VNWeb/Elm.pm @@ -155,6 +155,16 @@ our %apis = ( } }, } } ], ); +# (These references to other API results cause redundant Elm code - can be deduplicated) +$apis{AdvSearchQuery} = [ { type => 'hash', keys => { # Response to 'AdvSearchLoad' + qtype => {}, + query => { type => 'any' }, + producers => $apis{ProducerResult}[0], + staff => $apis{StaffResult}[0], + tags => $apis{TagResult}[0], + traits => $apis{TraitResult}[0], + anime => $apis{AnimeResult}[0], +} } ], # Compile %apis into a %schema and generate the elm_Response() functions @@ -196,6 +206,7 @@ sub def_type { $data .= def_type($name . to_camel($_), $obj->{keys}{$_}{values} || bless { $obj->{keys}{$_}->%*, required => 1 }, ref $obj->{keys}{$_} ) for @keys; $data .= sprintf "\ntype alias %s = %s\n\n", $name, $obj->elm_type( + any => 'JE.Value', keys => +{ map { my $t = $obj->{keys}{$_}; my $n = $name . to_camel($_); @@ -235,7 +246,7 @@ sub def_validation { # Generate an Elm JSON encoder taking a corresponding def_type() as input sub encoder { my($name, $type, $obj) = @_; - def $name, "$type -> JE.Value", $obj->elm_encoder(json_encode => 'JE.'); + def $name, "$type -> JE.Value", $obj->elm_encoder(any => ' ', json_encode => 'JE.'); } diff --git a/lib/VNWeb/Misc/AdvSearch.pm b/lib/VNWeb/Misc/AdvSearch.pm new file mode 100644 index 00000000..61b9af48 --- /dev/null +++ b/lib/VNWeb/Misc/AdvSearch.pm @@ -0,0 +1,40 @@ +package VNWeb::Misc::AdvSearch; + +use VNWeb::Prelude; +use VNWeb::AdvSearch; + + +elm_api 'AdvSearchSave' => undef, { + name => { length => [1,50] }, + qtype => { enum => \%VNWeb::AdvSearch::FIELDS }, + query => {}, +}, sub { + my($d) = @_; + my $q = tuwf->compile({ advsearch => $d->{qtype} })->validate($d->{query})->data->query_encode; + tuwf->dbExeci( + 'INSERT INTO saved_queries', { uid => auth->uid, qtype => $d->{qtype}, name => $d->{name}, query => $q }, + 'ON CONFLICT (uid, qtype, name) DO UPDATE SET query =', \$q + ); + elm_Success +}; + + +elm_api 'AdvSearchDel' => undef, { + name => { type => 'array', minlength => 1, values => { length => [1,50] } }, + qtype => { enum => \%VNWeb::AdvSearch::FIELDS }, +}, sub { + my($d) = @_; + tuwf->dbExeci('DELETE FROM saved_queries WHERE uid =', \auth->uid, 'AND qtype =', \$d->{qtype}, 'AND name IN', $d->{name}); + elm_Success +}; + + +elm_api 'AdvSearchLoad' => undef, { + qtype => { enum => \%VNWeb::AdvSearch::FIELDS }, + query => {}, +}, sub { + my($d) = @_; + elm_AdvSearchQuery tuwf->compile({ advsearch => $d->{qtype} })->validate($d->{query})->data->elm_search_query; +}; + +1; diff --git a/sql/perms.sql b/sql/perms.sql index dc23aeba..b0f17172 100644 --- a/sql/perms.sql +++ b/sql/perms.sql @@ -43,6 +43,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_posts TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_votes TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON saved_queries TO vndb_site; -- No access to the 'sessions' table, managed by the user_* functions. GRANT SELECT ON shop_denpa TO vndb_site; GRANT SELECT ON shop_dlsite TO vndb_site; diff --git a/sql/schema.sql b/sql/schema.sql index af6b240c..9eaada73 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -574,6 +574,15 @@ CREATE TABLE rlists ( PRIMARY KEY(uid, rid) ); +-- saved_queries +CREATE TABLE saved_queries ( + uid integer NOT NULL, + name text NOT NULL, + qtype dbentry_type NOT NULL, + query text NOT NULL, -- compact encoded form + PRIMARY KEY(uid, qtype, name) +); + -- sessions CREATE TABLE sessions ( uid integer NOT NULL, diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 829640d8..4ff8c43c 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -51,6 +51,7 @@ ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_id_fkey ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE saved_queries ADD CONSTRAINT saved_queries_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE staff ADD CONSTRAINT staff_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); diff --git a/util/updates/2021-01-03-advsearch-saved-queries.sql b/util/updates/2021-01-03-advsearch-saved-queries.sql new file mode 100644 index 00000000..89a6f844 --- /dev/null +++ b/util/updates/2021-01-03-advsearch-saved-queries.sql @@ -0,0 +1,10 @@ +CREATE TABLE saved_queries ( + uid integer NOT NULL, + name text NOT NULL, + qtype dbentry_type NOT NULL, + query text NOT NULL, -- compact encoded form + PRIMARY KEY(uid, qtype, name) +); + +ALTER TABLE saved_queries ADD CONSTRAINT saved_queries_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +GRANT SELECT, INSERT, UPDATE, DELETE ON saved_queries TO vndb_site; |