summaryrefslogtreecommitdiff
path: root/lib/VNWeb/ULists/Elm.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNWeb/ULists/Elm.pm')
-rw-r--r--lib/VNWeb/ULists/Elm.pm125
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
};