diff options
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 84 | ||||
-rw-r--r-- | lib/VNDB/DB/Polls.pm | 95 |
2 files changed, 72 insertions, 107 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; diff --git a/lib/VNDB/DB/Polls.pm b/lib/VNDB/DB/Polls.pm deleted file mode 100644 index 0b8ff81d..00000000 --- a/lib/VNDB/DB/Polls.pm +++ /dev/null @@ -1,95 +0,0 @@ - -package VNDB::DB::Polls; - -use strict; -use warnings; -use Exporter 'import'; - -our @EXPORT = qw|dbPollGet dbPollVote dbPollAdd dbPollEdit|; - - -# Options: id, tid, uid, what -# What: votes -sub dbPollGet { - my($self, %o) = @_; - $o{what} ||= ''; - $o{uid} ||= $self->authInfo->{id}; - - my %where = ( - $o{id} ? ('p.id = ?' => $o{id}) : - $o{tid} ? ('p.tid = ?' => $o{tid}) : (), - ); - - my @select = ( - qw|p.id p.question p.max_options p.preview p.recast|, - $o{what} =~ /votes/ ? - ('(SELECT COUNT(DISTINCT uid) FROM polls_votes pv WHERE pv.pid = p.id) AS votes') : (), - ); - my $p = $self->dbRow(q| - SELECT !s - FROM polls p - !W|, - join(', ', @select), \%where - ); - return $p unless %$p; - - my $options_query = $o{what} =~ /votes/ ? - q|SELECT id, option, COUNT(pv.optid) AS votes - FROM polls_options po - LEFT JOIN polls_votes pv ON po.id = pv.optid - WHERE po.pid = ? GROUP BY id ORDER BY id| : - q|SELECT id, option - FROM polls_options po - WHERE po.pid = ? ORDER BY id|; - $p->{options} = $self->dbAll($options_query, $p->{id}); - - $p->{user} = $o{uid} ? [ - map $_->{optid}, @{$self->dbAll(q| - SELECT optid FROM polls_votes - WHERE pid = ? AND uid = ?|, $p->{id}, $o{uid})} - ] : []; - - return $p; -} - - -sub dbPollVote { - my($self, $id, %o) = @_; - - $self->dbExec('DELETE FROM polls_votes WHERE pid = ? AND uid = ?', $id, $o{uid}); - $self->dbExec('INSERT INTO polls_votes (pid, uid, optid) VALUES (?, ?, ?)', - $id, $o{uid}, $_) for @{$o{options}}; -} - - -sub dbPollAdd { - my($self, %o) = @_; - - my $id = $self->dbRow(q| - INSERT INTO polls (tid, question, max_options, preview, recast) - VALUES (?, ?, ?, ?, ?) RETURNING id|, - $o{tid}, $o{question}, $o{max_options}, $o{preview}, $o{recast} - )->{id}; - - $self->dbExec('INSERT INTO polls_options (pid, option) VALUES (?, ?)', $id, $_) - for @{$o{options}}; - - return $id; -} - - -sub dbPollEdit { - my($self, $id, %o) = @_; - - my %set = map exists $o{$_} ? ("$_ = ?" => $o{$_}) : (), - qw|question max_options preview recast|; - - $self->dbExec('UPDATE polls !H WHERE id = ?', \%set, $id); - $self->dbExec('DELETE FROM polls_options WHERE pid = ?', $id); - $self->dbExec('INSERT INTO polls_options (pid, option) VALUES (?, ?)', $id, $_) - for @{$o{options}}; -} - - -1; - |