diff options
Diffstat (limited to 'lib/VNDB/DB/Discussions.pm')
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 84 |
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; |