diff options
Diffstat (limited to 'lib/VNWeb/ULists/Elm.pm')
-rw-r--r-- | lib/VNWeb/ULists/Elm.pm | 192 |
1 files changed, 112 insertions, 80 deletions
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm index 4fd032dc..bcc22de1 100644 --- a/lib/VNWeb/ULists/Elm.pm +++ b/lib/VNWeb/ULists/Elm.pm @@ -4,21 +4,33 @@ use VNWeb::Prelude; use VNWeb::ULists::Lib; -# Should be called after any change to the ulist_* tables. +# Should be called after any label/vote/private change to the ulist_vns table. # (Normally I'd do this with triggers, but that seemed like a more complex and less efficient solution in this case) sub updcache { - tuwf->dbExeci(SELECT => sql_func update_users_ulist_stats => \shift); + my($uid,$vid) = @_; + tuwf->dbExeci(SELECT => sql_func update_users_ulist_private => \$uid, \$vid) if @_ == 2; + tuwf->dbExeci(SELECT => sql_func update_users_ulist_stats => \$uid); +} + + +sub sql_labelid { + my($uid) = @_; + sql '(SELECT min(x.n) + FROM generate_series(10, + greatest((SELECT max(id)+1 from ulist_labels ul WHERE ul.uid =', \$uid, '), 10) + ) x(n) + WHERE NOT EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid =', \$uid, 'AND ul.id = x.n))'; } our $LABELS = form_compile any => { - uid => { id => 1 }, - labels => { aoh => { + uid => { vndbid => 'u' }, + labels => { maxlength => 1500, aoh => { id => { int => 1 }, - label => { maxlength => 50 }, + label => { sl => 1, 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 + delete => { default => undef, uint => 1, range => [1, 3] }, # 1=keep vns, 2=delete when no other label, 3=delete all } } }; @@ -28,18 +40,11 @@ elm_api UListManageLabels => undef, $LABELS, sub { # Insert new labels my @new = grep $_->{id} < 0 && !$_->{delete}, @$labels; - # Subquery to get the lowest unused id - my $newid = sql '( - SELECT min(x.n) - FROM generate_series(10, - greatest((SELECT max(id)+1 from ulist_labels ul WHERE ul.uid =', \$uid, '), 10) - ) x(n) - WHERE NOT EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid =', \$uid, 'AND ul.id = x.n) - )'; - tuwf->dbExeci('INSERT INTO ulist_labels', { id => $newid, uid => $uid, label => $_->{label}, private => $_->{private} }) for @new; + tuwf->dbExeci('INSERT INTO ulist_labels', { id => sql_labelid($uid), uid => $uid, label => $_->{label}, private => $_->{private} }) for @new; # Update private flag - tuwf->dbExeci( + my $changed = 0; + $changed += tuwf->dbExeci( 'UPDATE ulist_labels SET private =', \$_->{private}, 'WHERE uid =', \$uid, 'AND id =', \$_->{id}, 'AND private <>', \$_->{private} ) for grep $_->{id} > 0 && !$_->{delete}, @$labels; @@ -57,27 +62,61 @@ elm_api UListManageLabels => undef, $LABELS, sub { 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 ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_all, ')') : (), + my @where = ( + @delete_all ? sql('labels &&', sql_array(@delete_all), '::smallint[]') : (), @delete_empty ? sql( - 'vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_empty, ')', - 'AND NOT EXISTS(SELECT 1 FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl NOT IN(', [ @delete_lblonly, @delete_empty ], '))' - ) : (); - tuwf->dbExeci('DELETE FROM ulist_vns WHERE uid =', \$uid, 'AND (', sql_or(@where), ')') if @where; + 'labels &&', sql_array(@delete_empty), '::smallint[] + AND labels <@', sql_array(@delete_lblonly, @delete_empty), '::smallint[]' + ) : () + ); + tuwf->dbExeci('DELETE FROM ulist_vns uv WHERE uid =', \$uid, 'AND (', sql_or(@where), ')') if @where; + + $changed += tuwf->dbExeci( + 'UPDATE ulist_vns + SET labels = array_remove(labels,', \$_->{id}, ') + WHERE uid =', \$uid, 'AND labels && ARRAY[', \$_->{id}, '::smallint]' + ) for @delete; - # (This will also delete all relevant vn<->label rows from ulist_vns_labels) tuwf->dbExeci('DELETE FROM ulist_labels WHERE uid =', \$uid, 'AND id IN', [ map $_->{id}, @delete ]) if @delete; - updcache $uid; + updcache $uid, $changed ? undef : (); elm_Success }; +# Create a new label and add it to a VN +elm_api UListLabelAdd => undef, { + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, + label => { sl => 1, maxlength => 50 }, +}, sub { + my($data) = @_; + return elm_Unauth if !ulists_own $data->{uid}; + + my $id = tuwf->dbVali(' + WITH x(id) AS (SELECT id FROM ulist_labels WHERE', { uid => $data->{uid}, label => $data->{label} }, '), + y(id) AS (INSERT INTO ulist_labels (id, uid, label, private) SELECT', sql_join(',', + sql_labelid($data->{uid}), \$data->{uid}, \$data->{label}, + # Let's copy the private flag from the Voted label, seems like a sane default + sql('(SELECT private FROM ulist_labels WHERE', {uid => $data->{uid}, id => 7}, ')') + ), 'WHERE NOT EXISTS(SELECT 1 FROM x) RETURNING id) + SELECT id FROM x UNION SELECT id FROM y' + ); + die "Attempt to set vote label" if $id == 7; + + tuwf->dbExeci( + 'INSERT INTO ulist_vns', {uid => $data->{uid}, vid => $data->{vid}, labels => "{$id}"}, + 'ON CONFLICT (uid, vid) DO UPDATE SET labels = array_set(ulist_vns.labels,', \$id, ')' + ); + updcache $data->{uid}, $data->{vid}; + elm_LabelId $id +}; + our $VNVOTE = form_compile any => { - uid => { id => 1 }, - vid => { id => 1 }, + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, vote => { vnvote => 1 }, }; @@ -92,7 +131,7 @@ elm_api UListVoteEdit => undef, $VNVOTE, sub { vote_date => sql $data->{vote} ? 'CASE WHEN ulist_vns.vote IS NULL THEN NOW() ELSE ulist_vns.vote_date END' : 'NULL' } ); - updcache $data->{uid}; + updcache $data->{uid}, $data->{vid}; elm_Success }; @@ -100,8 +139,8 @@ elm_api UListVoteEdit => undef, $VNVOTE, sub { my $VNLABELS = { - uid => { id => 1 }, - vid => { id => 1 }, + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, label => { _when => 'in', id => 1 }, applied => { _when => 'in', anybool => 1 }, labels => { _when => 'out', aoh => { id => { int => 1 }, label => {}, private => { anybool => 1 } } }, @@ -115,19 +154,18 @@ elm_api UListLabelEdit => $VNLABELS_OUT, $VNLABELS_IN, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; die "Attempt to set vote label" if $data->{label} == 7; + die "Attempt to set invalid label" if $data->{applied} + && !tuwf->dbVali('SELECT 1 FROM ulist_labels WHERE uid =', \$data->{uid}, 'AND id =', \$data->{label}); - tuwf->dbExeci('INSERT INTO ulist_vns', {uid => $data->{uid}, vid => $data->{vid}}, 'ON CONFLICT (uid, vid) DO NOTHING'); tuwf->dbExeci( - 'DELETE FROM ulist_vns_labels - WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}, 'AND lbl =', \$data->{label} - ) if !$data->{applied}; - tuwf->dbExeci( - 'INSERT INTO ulist_vns_labels', { uid => $data->{uid}, vid => $data->{vid}, lbl => $data->{label} }, - 'ON CONFLICT (uid, vid, lbl) DO NOTHING' - ) if $data->{applied}; - tuwf->dbExeci('UPDATE ulist_vns SET lastmod = NOW() WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}); - - updcache $data->{uid}; + 'INSERT INTO ulist_vns', { + uid => $data->{uid}, + vid => $data->{vid}, + labels => $data->{applied}?"{$data->{label}}":'{}' + }, 'ON CONFLICT (uid, vid) DO UPDATE SET lastmod = NOW(), + labels =', sql_func $data->{applied} ? 'array_set' : 'array_remove', 'ulist_vns.labels', \$data->{label} + ); + updcache $data->{uid}, $data->{vid}; elm_Success }; @@ -135,9 +173,9 @@ elm_api UListLabelEdit => $VNLABELS_OUT, $VNLABELS_IN, sub { our $VNDATE = form_compile any => { - uid => { id => 1 }, - vid => { id => 1 }, - date => { required => 0, default => '', regex => qr/^(?:19[7-9][0-9]|20[0-9][0-9])-(?:0[1-9]|1[0-2])-(?:0[1-9]|[12][0-9]|3[01])$/ }, # 1970 - 2099 for sanity + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, + date => { default => '', caldate => 1 }, start => { anybool => 1 }, # Field selection, started/finished }; @@ -148,7 +186,7 @@ elm_api UListDateEdit => undef, $VNDATE, sub { 'UPDATE ulist_vns SET lastmod = NOW(), ', $data->{start} ? 'started' : 'finished', '=', \($data->{date}||undef), 'WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid} ); - updcache $data->{uid}; + # Doesn't need `updcache()` elm_Success }; @@ -157,33 +195,19 @@ elm_api UListDateEdit => undef, $VNDATE, sub { our $VNOPT = form_compile any => { own => { anybool => 1 }, - uid => { id => 1 }, - vid => { id => 1 }, + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, notes => {}, rels => $VNWeb::Elm::apis{Releases}[0], relstatus => { type => 'array', values => { uint => 1 } }, # List of release statuses, same order as rels }; -our $VNPAGE = form_compile any => { - uid => { id => 1 }, - vid => { id => 1 }, - onlist => { anybool => 1 }, - canvote => { anybool => 1 }, - vote => { vnvote => 1 }, - notes => { required => 0, default => '' }, - review => { required => 0, vndbid => 'w' }, - canreview=> { anybool => 1 }, - labels => { aoh => { id => { int => 1 }, label => {}, private => { anybool => 1 } } }, - selected => { type => 'array', values => { id => 1 } }, -}; - - -# UListVNNotes module is abused for the UList.Opts and UList.VNPage flag definition +# UListVNNotes module is abused for the UList.Opts flag definition elm_api UListVNNotes => $VNOPT, { - uid => { id => 1 }, - vid => { id => 1 }, - notes => { required => 0, default => '', maxlength => 2000 }, + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, + notes => { default => '', maxlength => 2000 }, }, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; @@ -192,14 +216,14 @@ elm_api UListVNNotes => $VNOPT, { ); # Doesn't need `updcache()` elm_Success -}, VNPage => $VNPAGE; +}; elm_api UListDel => undef, { - uid => { id => 1 }, - vid => { id => 1 }, + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, }, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; @@ -214,10 +238,10 @@ elm_api UListDel => undef, { # Adds the release when not in the list. # $RLIST_STATUS is also referenced from VNWeb::Releases::Page. our $RLIST_STATUS = form_compile any => { - uid => { id => 1 }, - rid => { id => 1 }, - status => { required => 0, uint => 1, enum => \%RLIST_STATUS }, # undef meaning delete - empty => { required => 0, default => '' }, # An 'out' field + uid => { vndbid => 'u' }, + rid => { vndbid => 'r' }, + status => { default => undef, uint => 1, enum => \%RLIST_STATUS }, # undef meaning delete + empty => { default => '' }, # An 'out' field }; elm_api UListRStatus => undef, $RLIST_STATUS, sub { my($data) = @_; @@ -234,20 +258,28 @@ elm_api UListRStatus => undef, $RLIST_STATUS, sub { +our $WIDGET = form_compile out => $VNWeb::Elm::apis{UListWidget}[0]{keys}; + +elm_api UListWidget => $WIDGET, { uid => { vndbid => 'u' }, vid => { vndbid => 'v' } }, sub { + my($data) = @_; + return elm_Unauth if !ulists_own $data->{uid}; + my $v = tuwf->dbRowi('SELECT id, title, c_released FROM', vnt, 'v WHERE id =', \$data->{vid}); + return elm_Invalid if !defined $v->{title}; + elm_UListWidget ulists_widget_full_data $v, $data->{uid}; +}; + + + our %SAVED_OPTS = ( - # Labels - l => { onerror => [], type => 'array', scalar => 1, values => { int => 1 } }, + l => { onerror => [], type => 'array', scalar => 1, values => { int => 1, range => [-1,1600] } }, mul => { anybool => 1 }, - # Sort column & order - s => { onerror => 'title', enum => [qw[ title label vote voted added modified started finished rel rating ]] }, - o => { onerror => 'a', enum => ['a', 'd'] }, - # Visible columns - c => { onerror => [], type => 'array', scalar => 1, values => { enum => [qw[ label vote voted added modified started finished rel rating ]] } }, + s => { onerror => '' }, # TableOpts query string + f => { onerror => '' }, # AdvSearch ); my $SAVED_OPTS = { - uid => { id => 1 }, + uid => { vndbid => 'u' }, opts => { type => 'hash', keys => \%SAVED_OPTS }, field => { _when => 'in', enum => [qw/ vnlist votes wish /] }, }; @@ -258,7 +290,7 @@ our $SAVED_OPTS_OUT = form_compile out => $SAVED_OPTS; elm_api UListSaveDefault => $SAVED_OPTS_OUT, $SAVED_OPTS_IN, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; - tuwf->dbExeci('UPDATE users SET ulist_'.$data->{field}, '=', \JSON::XS->new->encode($data->{opts}), 'WHERE id =', \$data->{uid}); + tuwf->dbExeci('UPDATE users_prefs SET ulist_'.$data->{field}, '=', \JSON::XS->new->encode($data->{opts}), 'WHERE id =', \$data->{uid}); elm_Success }; |