summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--Makefile7
-rw-r--r--data/style.css18
-rw-r--r--elm/Lib/Html.elm6
-rw-r--r--elm/ULists/ManageLabels.elm116
-rw-r--r--elm/ULists/ManageLabels.js6
-rw-r--r--lib/VNWeb/User/Lists.pm119
-rw-r--r--util/sql/perms.sql3
-rw-r--r--util/updates/update_wip_lists.sql132
8 files changed, 403 insertions, 4 deletions
diff --git a/Makefile b/Makefile
index cf067f4e..a0474a73 100644
--- a/Makefile
+++ b/Makefile
@@ -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;