summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Discussions.pm
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-11-11 15:51:40 +0100
committerYorhel <git@yorhel.nl>2015-11-11 16:00:34 +0100
commit1e97c0405a0cacb05d9d70429d7e7969e231b786 (patch)
tree1d31d74761726bf29868d1d0aebc66e188180dd9 /lib/VNDB/DB/Discussions.pm
parentfd9f224ad7e1d6ebe4f7abba75526b5190c963ba (diff)
Misc poll improvements
- Merged polls table into threads table. Not much of a storage/performance difference, and it's a bit simpler this way. - Merged DB::Polls into DB::Discussions. Mainly because of the above change in DB structure. - Add option to remove an existing poll. - Allow preview and recast to be changed without deleting the votes - Set preview option by default. Because personal preferences. :) - Minor form validation differences
Diffstat (limited to 'lib/VNDB/DB/Discussions.pm')
-rw-r--r--lib/VNDB/DB/Discussions.pm84
1 files changed, 72 insertions, 12 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index c380feeb..b4771adc 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -5,11 +5,11 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount|;
+our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPollStats dbPollVote|;
# Options: id, type, iid, results, page, what, notusers, search, sort, reverse
-# What: boards, boardtitles, firstpost, lastpost
+# What: boards, boardtitles, firstpost, lastpost, poll
# Sort: id lastpost
sub dbThreadGet {
my($self, %o) = @_;
@@ -38,9 +38,9 @@ sub dbThreadGet {
}
my @select = (
- qw|t.id t.title t.count t.locked t.hidden|,
+ qw|t.id t.title t.count t.locked t.hidden|, 't.poll_question IS NOT NULL AS haspoll',
$o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
- 'p.id AS poll',
+ $o{what} =~ /poll/ ? (qw|t.poll_question t.poll_max_options t.poll_preview t.poll_recast|) : (),
);
my @join = (
@@ -50,7 +50,6 @@ sub dbThreadGet {
) : (),
$o{type} && $o{iid} ?
'JOIN threads_boards tb ON tb.tid = t.id' : (),
- 'LEFT JOIN polls p ON p.tid = t.id',
);
my $order = sprintf {
@@ -67,9 +66,10 @@ sub dbThreadGet {
join(', ', @select), join(' ', @join), \@where, $order
);
- if($o{what} =~ /(boards|boardtitles)/ && $#$r >= 0) {
+ if($o{what} =~ /(boards|boardtitles|poll)/ && $#$r >= 0) {
my %r = map {
$r->[$_]{boards} = [];
+ $r->[$_]{poll_options} = [];
($r->[$_]{id}, $_)
} 0..$#$r;
@@ -82,6 +82,15 @@ sub dbThreadGet {
)});
}
+ if($o{what} =~ /poll/) {
+ push(@{$r->[$r{$_->{tid}}]{poll_options}}, [ $_->{id}, $_->{option} ]) for (@{$self->dbAll(q|
+ SELECT tid, id, option
+ FROM threads_poll_options
+ WHERE tid IN(!l)|,
+ [ keys %r ]
+ )});
+ }
+
if($o{what} =~ /firstpost/) {
do { my $x = $r->[$r{$_->{tid}}]; $x->{fuid} = $_->{uid}; $x->{fdate} = $_->{date}; $x->{fusername} = $_->{username} } for (@{$self->dbAll(q|
SELECT tpf.tid, tpf.uid, EXTRACT('epoch' from tpf.date) AS date, uf.username
@@ -109,7 +118,10 @@ sub dbThreadGet {
}
-# id, %options->( title locked hidden boards }
+# id, %options->( title locked hidden boards poll_question poll_max_options poll_preview poll_recast poll_options }
+# The poll_{question,options,max_options} fields should not be set when there
+# are no changes to the poll info. Either all or none of these fields should be
+# set.
sub dbThreadEdit {
my($self, $id, %o) = @_;
@@ -117,6 +129,12 @@ sub dbThreadEdit {
'title = ?' => $o{title},
'locked = ?' => $o{locked}?1:0,
'hidden = ?' => $o{hidden}?1:0,
+ 'poll_preview = ?' => $o{poll_preview}?1:0,
+ 'poll_recast = ?' => $o{poll_recast}?1:0,
+ exists $o{poll_question} ? (
+ 'poll_question = ?' => $o{poll_question}||undef,
+ 'poll_max_options = ?' => $o{poll_max_options}||1,
+ ) : (),
);
$self->dbExec(q|
@@ -133,18 +151,27 @@ sub dbThreadEdit {
$id, $_->[0], $_->[1]||0
) for (@{$o{boards}});
}
+
+ if(exists $o{poll_question}) {
+ $self->dbExec('DELETE FROM threads_poll_options WHERE tid = ?', $id);
+ $self->dbExec(q|
+ INSERT INTO threads_poll_options (tid, option)
+ VALUES (?, ?)|,
+ $id, $_
+ ) for (@{$o{poll_options}});
+ }
}
-# %options->{ title hidden locked boards }
+# %options->{ title hidden locked boards poll_stuff }
sub dbThreadAdd {
my($self, %o) = @_;
my $id = $self->dbRow(q|
- INSERT INTO threads (title, hidden, locked)
- VALUES (?, ?, ?)
+ INSERT INTO threads (title, hidden, locked, poll_question, poll_max_options, poll_preview, poll_recast)
+ VALUES (?, ?, ?, ?, ?, ?, ?)
RETURNING id|,
- $o{title}, $o{hidden}?1:0, $o{locked}?1:0
+ $o{title}, $o{hidden}?1:0, $o{locked}?1:0, $o{poll_question}||undef, $o{poll_max_options}||1, $o{poll_preview}?1:0, $o{poll_recast}?1:0
)->{id};
$self->dbExec(q|
@@ -153,6 +180,12 @@ sub dbThreadAdd {
$id, $_->[0], $_->[1]||0
) for (@{$o{boards}});
+ $self->dbExec(q|
+ INSERT INTO threads_poll_options (tid, option)
+ VALUES (?, ?)|,
+ $id, $_
+ ) for ($o{poll_question} ? @{$o{poll_options}} : ());
+
return $id;
}
@@ -287,5 +320,32 @@ sub dbPostAdd {
}
-1;
+# Args: tid
+# Returns: num_users, poll_stats, user_voted_options
+sub dbPollStats {
+ my($self, $tid) = @_;
+ my $uid = $self->authInfo->{id};
+
+ my $num_users = $self->dbRow('SELECT COUNT(DISTINCT uid) AS votes FROM threads_poll_votes WHERE tid = ?', $tid)->{votes} || 0;
+
+ my $stats = !$num_users ? {} : { map +($_->{optid}, $_->{votes}), @{$self->dbAll(
+ 'SELECT optid, COUNT(optid) AS votes FROM threads_poll_votes WHERE tid = ? GROUP BY optid', $tid
+ )} };
+
+ my $user = !$num_users || !$uid ? [] : [
+ map $_->{optid}, @{$self->dbAll('SELECT optid FROM threads_poll_votes WHERE tid = ? AND uid = ?', $tid, $uid)}
+ ];
+
+ return $num_users, $stats, $user;
+}
+
+sub dbPollVote {
+ my($self, $tid, $uid, @opts) = @_;
+
+ $self->dbExec('DELETE FROM threads_poll_votes WHERE tid = ? AND uid = ?', $tid, $uid);
+ $self->dbExec('INSERT INTO threads_poll_votes (tid, uid, optid) VALUES (?, ?, ?)',
+ $tid, $uid, $_) for @opts;
+}
+
+1;