diff options
-rw-r--r-- | Makefile | 7 | ||||
-rw-r--r-- | data/style.css | 18 | ||||
-rw-r--r-- | elm/Lib/Html.elm | 6 | ||||
-rw-r--r-- | elm/ULists/ManageLabels.elm | 116 | ||||
-rw-r--r-- | elm/ULists/ManageLabels.js | 6 | ||||
-rw-r--r-- | lib/VNWeb/User/Lists.pm | 119 | ||||
-rw-r--r-- | util/sql/perms.sql | 3 | ||||
-rw-r--r-- | util/updates/update_wip_lists.sql | 132 |
8 files changed, 403 insertions, 4 deletions
@@ -116,6 +116,7 @@ static/f/vndb.min.js: static/f/vndb.js # v2-rw +JS_FILES=elm/*.js elm/*/*.js ELM_FILES=elm/*.elm elm/*/*.elm ELM_MODULES=$(shell grep -l '^main =' ${ELM_FILES} | sed 's/^elm\///') @@ -127,7 +128,7 @@ define fix-js sed -i 's/var \$$author\$$project\$$Lib\$$Ffi\$$/var __unused__/g' $@ sed -Ei 's/\$$author\$$project\$$Lib\$$Ffi\$$([a-zA-Z0-9_]+)/window.elmFfi_\1(_Json_wrap)/g' $@ sed -Ei "s/([^ ]+) !== 'checked'/\\1 !== 'checked' \&\& \\1 !== 'selected'/g" $@ - for fn in elm/*.js; do \ + for fn in ${JS_FILES}; do \ echo "(function(){'use strict';"; \ cat $$fn; \ echo "})();"; \ @@ -137,11 +138,11 @@ endef elm/Gen/.generated: lib/VNWeb/*.pm lib/VNWeb/*/*.pm lib/VNDB/Types.pm lib/VNDB/Config.pm data/conf.pl util/vndb.pl elmgen -static/f/v2rw.js: ${ELM_FILES} elm/*.js elm/Gen/.generated | static/f +static/f/v2rw.js: ${ELM_FILES} ${JS_FILES} elm/Gen/.generated | static/f cd elm && ELM_HOME=elm-stuff elm make ${ELM_MODULES} --output ../$@ ${fix-js} -static/f/v2rw.min.js: ${ELM_FILES} elm/*.js elm/Gen/.generated | static/f +static/f/v2rw.min.js: ${ELM_FILES} ${JS_FILES} elm/Gen/.generated | static/f cd elm && ELM_HOME=elm-stuff elm make --optimize ${ELM_MODULES} --output ../$@ ${fix-js} uglifyjs $@ --compress 'pure_funcs="F2,F3,F4,F5,F6,F7,F8,F9,A2,A3,A4,A5,A6,A7,A8,A9",pure_getters,keep_fargs=false,unsafe_comps,unsafe' | uglifyjs --mangle -o $@~ diff --git a/data/style.css b/data/style.css index f3e2366f..b48188aa 100644 --- a/data/style.css +++ b/data/style.css @@ -138,7 +138,7 @@ input.text, input.submit, select, textarea { form, fieldset { border: 0; display: block } legend { display: none; } optgroup option { padding-left: 10px; font-style: normal; } -input.submit { background: $boxbg$; padding: 1px; } +input.submit { background: $boxbg$; padding: 1px 5px; } input.text, select { width: 200px; } fieldset.submit { width: 100%; text-align: center; margin: 5px; } fieldset.submit input[type=submit] { width: 150px; } @@ -168,6 +168,10 @@ div.spinner { content: ''; border: 3px solid #9eaebd; border-bottom-color: trans .textpreview .preview { width: 100%; box-sizing: border-box; border: 1px solid $secborder$; margin: 1px; padding: 5px } fieldset.submit .textpreview { margin: -15px auto 0 auto } +.compact input.text, .compact select { margin: -2px -1px; padding: 1px 0 } +.stealth input, .stealth select { font: inherit; background: none; border: 1px solid transparent; -moz-appearance: none; -webkit-appearance: none; appearance: none } +.stealth input:hover, .stealth input:focus, +.stealth select:hover, .stealth select:focus { border: 1px solid $secborder$; background: $secbg$ } /***** menu *****/ @@ -773,6 +777,18 @@ div.votelist td.tc2 { width: 50px; text-align: right; padding-right: 10px } .wishlist tfoot td { padding: 0 0 0 25px } +/***** New User's VN list *****/ + +.labelfilters { text-align: center } +.labelfilters input.submit { margin-top: 5px } + +.labeledit > div { width: 600px; margin: 10px auto } +.labeledit table { margin: 0 auto } +.labeledit tbody td:nth-child(1) { text-align: right } +.labeledit tbody td:nth-child(4) { padding-left: 10px; width: 300px} +.labeledit select { width: 100% } +.labeledit tfoot div { float: right; text-align: right } + /***** User VN list browser ******/ diff --git a/elm/Lib/Html.elm b/elm/Lib/Html.elm index 3aae21c7..edf0a0c5 100644 --- a/elm/Lib/Html.elm +++ b/elm/Lib/Html.elm @@ -17,6 +17,12 @@ onClickN action = custom "click" (JD.succeed { message = action, stopPropagation br_ : Int -> Html m br_ n = if n == 1 then br [] [] else span [] <| List.repeat n <| br [] [] + +inputButton : String -> m -> List (Attribute m) -> Html m +inputButton val onch attrs = + input ([ type_ "button", class "submit", tabindex 10, value val, onClick onch] ++ attrs) [] + + -- Submit button with loading indicator and error message display submitButton : String -> Api.State -> Bool -> Bool -> Html m submitButton val state valid load = div [] diff --git a/elm/ULists/ManageLabels.elm b/elm/ULists/ManageLabels.elm new file mode 100644 index 00000000..f8f8bb6d --- /dev/null +++ b/elm/ULists/ManageLabels.elm @@ -0,0 +1,116 @@ +module ULists.ManageLabels exposing (main) + +import Html exposing (..) +import Html.Attributes exposing (..) +import Html.Events exposing (..) +import Browser +import Browser.Navigation exposing (reload) +import Json.Encode as JE +import Lib.Html exposing (..) +import Lib.Util exposing (..) +import Lib.Api as Api +import Gen.Api as GApi +import Gen.ManageLabels as GML + + +main : Program GML.Send Model Msg +main = Browser.element + { init = \e -> (init e, Cmd.none) + , view = view + , update = update + , subscriptions = always Sub.none + } + +type alias Model = + { uid : Int + , state : Api.State + , labels : List GML.SendLabels + , editing : Maybe Int + } + +init : GML.Send -> Model +init d = + { uid = d.uid + , state = Api.Normal + , labels = d.labels + , editing = Nothing + } + +type Msg + = Noop + | Private Int Bool + | Label Int String + | Delete Int (Maybe Int) + | Add + | Submit + | Submitted GApi.Response + + +update : Msg -> Model -> (Model, Cmd Msg) +update msg model = + case msg of + Noop -> (model, Cmd.none) + Private n b -> ({ model | labels = modidx n (\l -> { l | private = b }) model.labels }, Cmd.none) + Label n s -> ({ model | labels = modidx n (\l -> { l | label = s }) model.labels }, Cmd.none) + Delete n o -> ({ model | labels = List.filter (\l -> l.count > 0 || l.delete == Nothing) <| modidx n (\l -> { l | delete = o }) model.labels }, Cmd.none) + Add -> ({ model | labels = model.labels ++ [{ id = -1, label = "New label", private = List.all (\il -> il.private) model.labels, count = 0, delete = Nothing }] }, Cmd.none) + + Submit -> ({ model | state = Api.Loading }, Api.post "/u/ulist/labels.json" (GML.encode { uid = model.uid, labels = model.labels }) Submitted) + + Submitted GApi.Success -> (model, reload) + Submitted r -> ({ model | state = Api.Error r }, Cmd.none) + + +view : Model -> Html Msg +view model = + let + item n l = + let strid = "form_pr" ++ String.fromInt n + in tr [ class "compact" ] + [ td [] [ text <| if l.count == 0 then "" else String.fromInt l.count ] + , td [ class "stealth" ] + [ if l.id > 0 && l.id < 1000 then text l.label + else inputText "" l.label (Label n) GML.valLabelsLabel + ] + , td [ class "linkradio" ] [ inputCheck strid l.private (Private n), label [ for strid ] [ text "private" ] ] + , td [ class "stealth" ] + [ if l.id == 7 then b [ class "grayedout" ] [ text "applied when you vote" ] + else if l.id > 0 && l.id < 1000 then b [ class "grayedout" ] [ text "built-in" ] + else if l.delete == Nothing then a [ onClick (Delete n (Just 1)) ] [ text "remove" ] + else inputSelect "" l.delete (Delete n) [] + [ (Nothing, "Keep label") + , (Just 1, "Delete label but keep VNs in my list") + , (Just 2, "Delete label and VNs with only this label") + , (Just 3, "Delete label and all VNs with this label") + ] + ] + ] + in + Html.form [ onSubmit Submit, class "labeledit hidden" ] + [ div [ ] + [ b [] [ text "How to use labels" ] + , ul [] + [ li [] [ text "You can assign multiple labels to a visual novel" ] + , li [] [ text "You can create custom labels or just use the built-in labels" ] + , li [] [ text "Private labels will not be visible to other users" ] + , li [] [ text "Your vote and notes will be public when at least one non-private label has been assigned to the visual novel" ] + ] + ] + , table [ class "stripe" ] <| + [ thead [] [ tr [] + [ td [] [ text "VNs" ] + , td [] [ text "Label" ] + , td [] [ text "Private" ] + , td [] [ ] + ] ] + , tfoot [] [ tr [] + [ td [] [] + , td [ colspan 3 ] + [ a [ onClick Add ] [ text "New label" ] + --, inputButton "Save changes" Noop [] + , submitButton "Save changes" model.state True False + ] + ] ] + , tbody [] <| List.indexedMap item model.labels + ] + ] diff --git a/elm/ULists/ManageLabels.js b/elm/ULists/ManageLabels.js new file mode 100644 index 00000000..85312d21 --- /dev/null +++ b/elm/ULists/ManageLabels.js @@ -0,0 +1,6 @@ +document.querySelectorAll('#labeledit').forEach(function(b) { + b.onclick = function() { + document.querySelectorAll('.labeledit').forEach(function(e) { e.classList.toggle('hidden') }) + }; + return false; +}) diff --git a/lib/VNWeb/User/Lists.pm b/lib/VNWeb/User/Lists.pm new file mode 100644 index 00000000..9ce5cf10 --- /dev/null +++ b/lib/VNWeb/User/Lists.pm @@ -0,0 +1,119 @@ +package VNWeb::User::Lists; + +use VNWeb::Prelude; + +my $LABELS = form_compile any => { + uid => { id => 1 }, + labels => { aoh => { + id => { int => 1 }, + label => { maxlength => 50 }, + private => { anybool => 1 }, + count => { uint => 1 }, + delete => { required => 0, default => undef, uint => 1, range => [1, 3] }, # 1=keep vns, 2=delete when no other label, 3=delete all + } } +}; + + +elm_form 'ManageLabels', undef, $LABELS; + + +# TODO: Keep this URL? Steal /u+/list when that one's gone? +TUWF::get qr{/$RE{uid}/ulist}, sub { + my $u = tuwf->dbRowi('SELECT id,', sql_user(), 'FROM users u WHERE id =', \tuwf->capture('id')); + return tuwf->resNotFound if !$u->{id}; + + my $own = auth && $u->{id} == auth->uid; + my $labels = tuwf->dbAlli( + 'SELECT l.id, l.label, l.private, count(vl.vid) as count, null as delete + FROM ulists_labels l LEFT JOIN ulists_vn_labels vl ON vl.uid = l.uid AND vl.lbl = l.id + WHERE', { 'l.uid' => $u->{id}, $own ? () : ('l.private' => 0) }, + 'GROUP BY l.id, l.label, l.private + ORDER BY CASE WHEN l.id < 1000 THEN l.id ELSE 1000 END, l.label' + ); + + my sub lblfilt_ { + input_ type => 'checkbox', name => 'l', value => $_->{id}, id => "form_l$_->{id}", 0 ? (checked => 'checked') : (); + label_ for => "form_l$_->{id}", "$_->{label} "; + txt_ " ($_->{count})" if !$_->{private}; + b_ class => 'grayedout', " ($_->{count})" if $_->{private}; + } + + my $title = $own ? 'My list' : user_displayname($u)."'s list"; + framework_ title => $title, type => 'u', dbobj => $u, tab => 'list', + sub { + div_ class => 'mainbox', sub { + h1_ $title; + form_ method => 'get', sub { + p_ class => 'labelfilters', sub { + span_ class => 'linkradio', sub { + join_ sub { em_ ' / ' }, \&lblfilt_, grep $_->{id} < 1000, @$labels; + em_ ' | '; + input_ type => 'checkbox', name => 'l', class => 'checkall', value => 0, id => 'form_l_all'; + label_ for => 'form_l_all', 'Select all'; + debug_ $labels; + }; + my @cust = grep $_->{id} >= 1000, @$labels; + if(@cust) { + br_; + span_ class => 'linkradio', sub { + join_ sub { em_ ' / ' }, \&lblfilt_, @cust; + } + } + br_; + input_ type => 'submit', class => 'submit', value => 'Update filters'; + input_ type => 'button', class => 'submit', id => 'labeledit', value => 'Manage labels' if $own; + }; + }; + elm_ 'ULists.ManageLabels', $LABELS, { uid => $u->{id}, labels => $labels } if $own; + } + }; +}; + + +json_api qr{/u/ulist/labels.json}, $LABELS, sub { + my($uid, $labels) = ($_[0]{uid}, $_[0]{labels}); + return elm_Unauth if !auth || auth->uid != $uid; + + # Insert new labels + my @new = grep $_->{id} < 0 && !$_->{delete}, @$labels; + tuwf->dbExeci( + 'INSERT INTO ulists_labels (uid, label, private)', + 'VALUES ', sql_comma( + map sql('(', sql_comma(\$uid, \$_->{label}, \$_->{private}), ')'), @new + ) + ) if @new; + + # Update private flag + tuwf->dbExeci( + 'UPDATE ulists_labels SET private =', \$_->{private}, + 'WHERE uid =', \$uid, 'AND id =', \$_->{id}, 'AND private <>', \$_->{private} + ) for grep $_->{id} > 0 && !$_->{delete}, @$labels; + + # Update label + tuwf->dbExeci( + 'UPDATE ulists_labels SET label =', \$_->{label}, + 'WHERE uid =', \$uid, 'AND id =', \$_->{id}, 'AND label <>', \$_->{label} + ) for grep $_->{id} >= 1000 && !$_->{delete}, @$labels; + + # Delete labels + my @delete = grep $_->{id} >= 1000 && $_->{delete}, @$labels; + my @delete_lblonly = map $_->{id}, grep $_->{delete} == 1, @delete; + my @delete_empty = map $_->{id}, grep $_->{delete} == 2, @delete; + my @delete_all = map $_->{id}, grep $_->{delete} == 3, @delete; + + # delete vns with: (a label in option 3) OR ((a label in option 2) AND (no labels other than in option 1 or 2)) + my @where = + @delete_all ? sql('vid IN(SELECT vid FROM ulists_vn_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_all, ')') : (), + @delete_empty ? sql( + 'vid IN(SELECT vid FROM ulists_vn_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_empty, ')', + 'AND NOT EXISTS(SELECT 1 FROM ulists_vn_labels WHERE uid =', \$uid, 'AND lbl NOT IN(', [ @delete_lblonly, @delete_empty ], '))' + ) : (); + tuwf->dbExeci('DELETE FROM ulists WHERE uid =', \$uid, 'AND (', sql_or(@where), ')') if @where; + + # (This will also delete all relevant vn<->label rows from ulists_vn_labels) + tuwf->dbExeci('DELETE FROM ulists_labels WHERE uid =', \$uid, 'AND id IN', [ map $_->{id}, @delete ]) if @delete; + + elm_Success +}; + +1; diff --git a/util/sql/perms.sql b/util/sql/perms.sql index 5e0f5df3..a038103e 100644 --- a/util/sql/perms.sql +++ b/util/sql/perms.sql @@ -61,6 +61,9 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON traits 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, UPDATE, DELETE ON ulists TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_labels TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_vn_labels TO vndb_site; -- users table is special; The 'perm', 'passwd' and 'mail' columns are -- protected and can only be accessed through the user_* functions. diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql new file mode 100644 index 00000000..04e3b8ee --- /dev/null +++ b/util/updates/update_wip_lists.sql @@ -0,0 +1,132 @@ +-- Replaces the current vnlists, votes and wlists tables +CREATE TABLE ulists ( + uid integer NOT NULL, -- users.id + vid integer NOT NULL, -- vn.id + added timestamptz NOT NULL DEFAULT NOW(), + lastmod timestamptz NOT NULL DEFAULT NOW(), -- updated when anything in this row has changed? + vote_date timestamptz, -- Used for "recent votes" - also updated when vote has changed? + vote smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), + started date, + finished date, + notes text NOT NULL DEFAULT '', + PRIMARY KEY(uid, vid) +); + +-- Automatically created for each user: +-- +-- Wishlist (with -Low/-Medium/-High for converted wishlists, otherwise not created by default) +-- Blacklist +-- Playing +-- Finished +-- Stalled +-- Dropped +-- +-- Should these be user-editable, apart from the 'private' flag? +-- I'd say no, because then it'd be impossible use the lists for stats and automated suggestions. +CREATE TABLE ulists_labels ( + uid integer NOT NULL, -- user.id + id SERIAL NOT NULL, + label text NOT NULL, + private boolean NOT NULL, + PRIMARY KEY(uid, id) + -- Technically 'id' is already unique because of the SERIAL type, but we want labels to be local to users. + -- Assuming we don't need 'id' to be globally unique, we can reserve fixed numbers for automatically created labels + -- (this would allow e.g. an "exclude blacklisted VNs" filter to use the same label id for everyone). +); + +CREATE TABLE ulists_vn_labels ( + uid integer NOT NULL, -- user.id + lbl integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(uid, lbl, vid) + -- (uid, lbl) REFERENCES ulist_labels (uid, id) ON DELETE CASCADE + -- (uid, vid) REFERENCES ulist (uid, vid) ON DELETE CASCADE + -- Do we want a 'when has this label been applied' timestamp? +); + +-- First 1000 numbers are reserved for built-in labels, first 10 non-built-in labels are for conversion. +SELECT setval('ulists_labels_id_seq', 1010); + +-- When is a row in ulist 'public'? i.e. When it is visible in a VNs recent votes and in the user's VN list? +-- +-- EXISTS(SELECT 1 FROM ulist_vn_label uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uid = ulist.uid AND vid = ulist.vid AND NOT ul.private) +-- +-- That is: It is public when it has been assigned at least one non-private label. +-- +-- This means that, during the conversion of old lists to this new format, all +-- vns with an 'unknown' status (= old 'unknown' status or voted but not in +-- vnlist/wlist) from users who have not hidden their list should be assigned +-- to a new non-private label. +-- +-- The "Don't allow others to see my [..] list" profile option becomes obsolete +-- with this label-based private flag. + + + +\timing + +INSERT INTO ulists_labels (uid, id, label, private) + SELECT id, 1, 'Playing', hide_list FROM users + UNION ALL SELECT id, 2, 'Finished', hide_list FROM users + UNION ALL SELECT id, 3, 'Stalled', hide_list FROM users + UNION ALL SELECT id, 4, 'Dropped', hide_list FROM users + UNION ALL SELECT id, 5, 'Wishlist', hide_list FROM users + UNION ALL SELECT id, 6, 'Blacklist', hide_list FROM users + UNION ALL SELECT id, 7, 'Voted', hide_list FROM users + UNION ALL SELECT id, 1000,'Wishlist-High', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 0) + UNION ALL SELECT id, 1001,'Wishlist-Medium', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 1) + UNION ALL SELECT id, 1002,'Wishlist-Low', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 2); + +-- WAY TOO SLOW. No, really, this will likely bring down the server for a day. +--INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote, notes) +-- SELECT u.id, v.id, LEAST(wl.added, vl.added, vo.date), GREATEST(wl.added, vl.added, vo.date), vo.date, vo.vote, COALESCE(vl.notes, '') +-- FROM users u +-- JOIN vn v ON true +-- LEFT JOIN wlists wl ON wl.uid = u.id AND wl.vid = v.id +-- LEFT JOIN vnlists vl ON vl.uid = u.id AND vl.vid = v.id +-- LEFT JOIN votes vo ON vo.uid = u.id AND vo.vid = v.id +-- WHERE (wl.uid IS NOT NULL OR vl.uid IS NOT NULL OR vo.uid IS NOT NULL); + +-- Same thing as above, but in 3 smaller steps. +--INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote) SELECT uid, vid, date, date, date, vote FROM votes; +--INSERT INTO ulists (uid, vid, added, lastmod, notes) +-- SELECT uid, vid, added, added, notes FROM vnlists ON CONFLICT (uid, vid) DO +-- UPDATE SET notes = excluded.notes, added = LEAST(ulists.added, excluded.added), lastmod = GREATEST(ulists.lastmod, excluded.added); +--INSERT INTO ulists (uid, vid, added, lastmod) +-- SELECT uid, vid, added, added FROM wlists ON CONFLICT (uid, vid) DO +-- UPDATE SET added = LEAST(ulists.added, excluded.added), lastmod = GREATEST(ulists.lastmod, excluded.added); + +-- Same thing again, I realized I just needed FULL OUTER JOINs. +INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote, notes) + SELECT COALESCE(wl.uid, vl.uid, vo.uid) + , COALESCE(wl.vid, vl.vid, vo.vid) + , LEAST(wl.added, vl.added, vo.date) + , GREATEST(wl.added, vl.added, vo.date) + , vo.date, vo.vote + , COALESCE(vl.notes, '') + FROM wlists wl + FULL JOIN vnlists vl ON vl.uid = wl.uid AND vl.vid = wl.vid + FULL JOIN votes vo ON vo.uid = COALESCE(wl.uid, vl.uid) AND vo.vid = COALESCE(wl.vid, vl.vid); + +INSERT INTO ulists_vn_labels (uid, vid, lbl) + SELECT uid, vid, 5 FROM wlists WHERE wstat <> 3 -- All wishlisted items except the blacklist + UNION ALL SELECT uid, vid,1000 FROM wlists WHERE wstat = 0 -- Wishlist-High + UNION ALL SELECT uid, vid,1001 FROM wlists WHERE wstat = 1 -- Wishlist-Medium + UNION ALL SELECT uid, vid,1002 FROM wlists WHERE wstat = 2 -- Wishlist-Low + UNION ALL SELECT uid, vid, 6 FROM wlists WHERE wstat = 3 -- Blacklist + UNION ALL SELECT uid, vid, status FROM vnlists WHERE status <> 0 -- Playing/Finished/Stalled/Dropped + UNION ALL SELECT uid, vid, 7 FROM votes; + + + +ALTER TABLE ulists ADD CONSTRAINT ulists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE ulists ADD CONSTRAINT ulists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE ulists_labels ADD CONSTRAINT ulists_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE ulists_vn_labels ADD CONSTRAINT ulists_vn_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE ulists_vn_labels ADD CONSTRAINT ulists_vn_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE ulists_vn_labels ADD CONSTRAINT ulists_vn_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulists_labels (uid,id) ON DELETE CASCADE; +ALTER TABLE ulists_vn_labels ADD CONSTRAINT ulists_vn_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulists (uid,vid) ON DELETE CASCADE; + +GRANT SELECT, INSERT, UPDATE, DELETE ON ulists TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_labels TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_vn_labels TO vndb_site; |