diff options
Diffstat (limited to 'lib/VNWeb/ULists/Elm.pm')
-rw-r--r-- | lib/VNWeb/ULists/Elm.pm | 125 |
1 files changed, 79 insertions, 46 deletions
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm index c88156df..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 => { vndbid => 'u' }, - labels => { aoh => { + 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; @@ -58,22 +63,55 @@ elm_api UListManageLabels => undef, $LABELS, sub { # 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, ')') : (), + @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 uvl WHERE uvl.vid = uv.vid AND uid =', \$uid, 'AND lbl NOT IN', [ @delete_lblonly, @delete_empty ], ')' + '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; - # (This will also delete all relevant vn<->label rows from ulist_vns_labels) + $changed += tuwf->dbExeci( + 'UPDATE ulist_vns + SET labels = array_remove(labels,', \$_->{id}, ') + WHERE uid =', \$uid, 'AND labels && ARRAY[', \$_->{id}, '::smallint]' + ) for @delete; + 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 => { @@ -93,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 }; @@ -116,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 }; @@ -138,7 +175,7 @@ elm_api UListLabelEdit => $VNLABELS_OUT, $VNLABELS_IN, sub { our $VNDATE = form_compile any => { uid => { vndbid => 'u' }, vid => { vndbid => 'v' }, - 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 + date => { default => '', caldate => 1 }, start => { anybool => 1 }, # Field selection, started/finished }; @@ -149,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 }; @@ -170,7 +207,7 @@ our $VNOPT = form_compile any => { elm_api UListVNNotes => $VNOPT, { uid => { vndbid => 'u' }, vid => { vndbid => 'v' }, - notes => { required => 0, default => '', maxlength => 2000 }, + notes => { default => '', maxlength => 2000 }, }, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; @@ -203,8 +240,8 @@ elm_api UListDel => undef, { our $RLIST_STATUS = form_compile any => { uid => { vndbid => 'u' }, rid => { vndbid => 'r' }, - status => { required => 0, uint => 1, enum => \%RLIST_STATUS }, # undef meaning delete - empty => { required => 0, default => '' }, # An 'out' field + 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) = @_; @@ -226,7 +263,7 @@ 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 vn WHERE id =', \$data->{vid}); + 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}; }; @@ -235,14 +272,10 @@ elm_api UListWidget => $WIDGET, { uid => { vndbid => 'u' }, vid => { vndbid => ' 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 = { @@ -257,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 }; |