Merge branch 'poll' of https://github.com/morkt/vndb into polls

10 files changed, 386 insertions, 1 deletions

diff --git a/data/global.pl b/data/global.pl
index 1e3f90dc..29f1a6c9 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -114,6 +114,7 @@ our %S;
     posts         => [ 25, 'VNDB Recent Posts', '/t' ],
   },
   staff_roles     => [qw|scenario chardesign art music songs director staff|],
+  poll_options    => 20, # max number of options in discussion board polls
 );
 
 
diff --git a/data/js/main.js b/data/js/main.js
index dc1d3b27..79a280c1 100644
--- a/data/js/main.js
+++ b/data/js/main.js
@@ -48,6 +48,7 @@ VARS = /*VARS*/;
 //include charops.js
 //include filter.js
 //include misc.js
+//include polls.js
 
 // VN editing (/v+/edit)
 //include vnrel.js
diff --git a/data/js/polls.js b/data/js/polls.js
new file mode 100644
index 00000000..b34f0f7b
--- /dev/null
+++ b/data/js/polls.js
@@ -0,0 +1,26 @@
+function addPoll() {
+  var a = byId('poll_add');
+  setClass(a, 'hidden', false);
+  var parentNode = function(n, tag) {
+    while(n && n.nodeName.toLowerCase() != tag)
+      n = n.parentNode;
+    return n;
+  };
+  var show = function(v) {
+    setClass(parentNode(byId('poll_q'),      'tr'), 'hidden', !v);
+    setClass(parentNode(byId('poll_opt'),    'tr'), 'hidden', !v);
+    setClass(parentNode(byId('poll_max'),    'tr'), 'hidden', !v);
+    setClass(parentNode(byId('poll_preview'),'tr'), 'hidden', !v);
+    setClass(parentNode(byId('poll_recast'), 'tr'), 'hidden', !v);
+    setClass(parentNode(a, 'tr'), 'hidden', v);
+  };
+  a.onclick = function() {
+    show(true);
+    return true;
+  };
+  show(false);
+}
+
+// Discussion board polls
+if(byId('poll_add'))
+  addPoll();
diff --git a/data/lang.txt b/data/lang.txt
index 72328fee..ce63f751 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -4556,6 +4556,27 @@ es : Avanzado...
 tr : Gelişmiş...
 uk : Розширений режим
 it : Avanzate...
+ 
+:_poll_novote_login
+en : You must be logged in to be able to vote.
+
+:_poll_choose
+en : You may choose up to [_1] options
+
+:_poll_vote
+en : Vote
+
+:_poll_no_votes
+en : Nobody voted yet.
+
+:_poll_results
+en : View results
+
+:_poll_total_votes
+en : [_1] [quant,_1,vote,votes] total
+
+:_poll_error
+en : Poll error
 
 
 # Post edit/reply/new thread form
@@ -4715,6 +4736,30 @@ es : Mirar [url,/d9.3,d9.3] para ver los formatos permitidos
 tr : Biçimlendirme kodları için [url,/d9.3,d9.3]'e bakınız.
 uk : Правила розмітки читай у [url,/d9.3,d9.3].
 it : Vedi [url,/d9.3,d9.3] per i codici di formattazione
+ 
+:_postedit_form_poll_add
+en : Add poll
+
+:_postedit_form_poll_q
+en : Poll question
+
+:_postedit_form_poll_warning
+en : All votes will be reset if any changes to the poll fields are made!
+
+:_postedit_form_poll_opt
+en : Poll options
+
+:_postedit_form_poll_optmax
+en : one per line,[br][_1] max
+
+:_postedit_form_poll_max
+en : Number of options voter is allowed to choose
+
+:_postedit_form_poll_view
+en : Allow users to view poll results before voting
+
+:_postedit_form_poll_recast
+en : Allow users to change their vote
 
 
 # Browsing threads by board (/t/{board_id})
@@ -4968,6 +5013,9 @@ es : Último mensaje
 tr : Son gönderi
 uk : Останнє повідомлення
 it : Ultimo messaggio
+ 
+:_threadlist_poll
+en : poll
 
 
 
@@ -16261,6 +16309,9 @@ es : Personaje principal inválido. Asgúrate que la ID es correcta, que el pers
 tr : Geçersiz ana karakter. ID'nin doğruluğundan, ana karakterin bir başka karakterin örneği olmadığından, ve bu girdinin başka bir yerde ana karakter olarak kullanılmadığından emin olun.
 uk : Неправильний головний герой. Переконайтеся у правильності ID, головний персонаж — це не втілення іншого персонажа, і що цей запис не використовується в якості головного персонажа десь ще.
 it : Personaggio principale non valido. Assicurati che ID sia corretto, che il personaggio non sia un'instanza di un altro personaggio, e che questa pagina non è utilizzata come personaggio principale da un'altra parte.
+ 
+:_formerr_e_poll
+en : Inappropriate number of options in a poll.
 
 :_formerr_e_usedalias
 en : Can't remove an alias that is still linked to a VN.
@@ -16309,6 +16360,12 @@ es : ¡[_1] es un campo requerido!
 tr : [_1] alanı gereklidir!
 uk : [_1] — обов’язкове поле!
 it : [_1] è un campo obbligatorio!
+ 
+:_formerr_min
+en : [_1]: minimum number is [_2]
+
+:_formerr_max
+en : [_1]: maximum number is [_2]
 
 :_formerr_minlength
 en : [_1]: should have at least [_2] characters
diff --git a/data/style.css b/data/style.css
index 3daffbed..f42d2a3a 100644
--- a/data/style.css
+++ b/data/style.css
@@ -314,6 +314,7 @@ div.mainbox.discussions b.boards a { color: $grayedout$; }
 div.discussions td.tc2             { width: 50px;  }
 div.discussions td.tc3             { width: 90px;  }
 div.discussions td.tc4             { width: 210px; }
+div.discussions .pollflag          { color: $grayedout$; padding-right: 6px; }
 div.postsearch td.tc1_1           { width: 60px; padding-left: 0; padding-right: 0; text-align: right }
 div.postsearch td.tc1_2           { width: 25px; padding-left: 0 }
 div.postsearch td.tc2             { width: 65px;  }
@@ -401,6 +402,23 @@ div#vntags                    { margin: 15px 30px 0 30px; border-top: 1px solid
 
 
 
+/***** Polls ****/
+
+.votebooth thead td   { font-weight: normal; background: transparent; padding-bottom: 5px; }
+.votebooth tfoot td   { padding-top: 5px }
+.votebooth td         { vertical-align: middle; padding: 0 8px; }
+.votebooth            { margin: 0 30px }
+.votebooth td.tc1     { padding-right: 20px }
+.votebooth td.tc2     { min-width: 220px }
+.votebooth td.tc2 div { margin: 2px; }
+.votebooth td.tc2 div.graph { float: left; height: 14px; background-color: $border$; padding: 0; }
+.votebooth td.tc3     { text-align: right; padding-right: 16px; }
+.votebooth .submit    { width: 100px }
+.votebooth .option    { margin-left: 8px }
+.votebooth .option.own { font-weight: bold }
+
+
+
 /***** VN edit *****/
 
 #jt_box_vn_rel table  { margin-bottom: 10px; }
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 48859611..c380feeb 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -40,6 +40,7 @@ sub dbThreadGet {
   my @select = (
     qw|t.id t.title t.count t.locked t.hidden|,
     $o{what} =~ /lastpost/  ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
+    'p.id AS poll',
   );
 
   my @join = (
@@ -49,6 +50,7 @@ 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 {
diff --git a/lib/VNDB/DB/Polls.pm b/lib/VNDB/DB/Polls.pm
new file mode 100644
index 00000000..0b8ff81d
--- /dev/null
+++ b/lib/VNDB/DB/Polls.pm
@@ -0,0 +1,95 @@
+
+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;
+
diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm
index 421a998d..4542a5fb 100644
--- a/lib/VNDB/Handler/Discussions.pm
+++ b/lib/VNDB/Handler/Discussions.pm
@@ -6,10 +6,12 @@ use warnings;
 use TUWF ':html', 'xml_escape', 'uri_escape';
 use POSIX 'ceil';
 use VNDB::Func;
+use List::Util qw(first max);
 
 
 TUWF::register(
   qr{t([1-9]\d*)(?:/([1-9]\d*))?}    => \&thread,
+  qr{t([1-9]\d*)(/[1-9]\d*)?/vote}   => \&vote,
   qr{t([1-9]\d*)\.([1-9]\d*)}        => \&redirect,
   qr{t/(all|db|an|ge|[vpu])([1-9]\d*)?}  => \&board,
   qr{t([1-9]\d*)/reply}              => \&edit,
@@ -37,6 +39,8 @@ sub thread {
   my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page, what => 'user');
   return $self->resNotFound if !$p->[0];
 
+  my $poll = $self->dbPollGet(id => $t->{poll}, what => 'votes') if $t->{poll};
+
   $self->htmlHeader(title => $t->{title}, noindex => 1);
   div class => 'mainbox';
    h1 $t->{title};
@@ -56,6 +60,8 @@ sub thread {
    end;
   end 'div';
 
+  _poll($self, "/t$tid".($page > 1 ? "/$page" : ''), $poll) if $poll;
+
   $self->htmlBrowseNavigate("/t$tid/", $page, [ $t->{count}, 25 ], 't', 1);
   div class => 'mainbox thread';
    table class => 'stripe';
@@ -154,6 +160,8 @@ sub edit {
   my $p = $num && $self->dbPostGet(tid => $tid, num => $num, what => 'user')->[0];
   return $self->resNotFound if $num && !$p->{num};
 
+  $t->{poll} = $self->dbPollGet(id => $t->{poll}) if $tid && $t->{poll};
+
   # are we allowed to perform this action?
   return $self->htmlDenied if !$self->authCan('board')
     || ($tid && ($t->{locked} || $t->{hidden}) && !$self->authCan('boardmod'))
@@ -167,6 +175,11 @@ sub edit {
       !$tid || $num == 1 ? (
         { post => 'title', maxlength => 50 },
         { post => 'boards', maxlength => 50 },
+        { post => 'poll_q', required => 0, maxlength => 100 },
+        { post => 'poll_opt', required => 0, maxlength => 100*$self->{poll_options} },
+        { post => 'poll_max', required => 0, default => 1, template => 'uint', min => 1, max => $self->{poll_options} },
+        { post => 'poll_preview', required => 0 },
+        { post => 'poll_recast', required => 0 },
       ) : (),
       $self->authCan('boardmod') ? (
         { post => 'locked', required => 0 },
@@ -205,6 +218,22 @@ sub edit {
       }
     }
 
+    # validate poll options
+    my %poll;
+    if(!$frm->{_err} && $frm->{poll_opt}) {
+      # split by lines, trimming whitespace
+      my @options = split /\s*\n\s*/, $frm->{poll_opt};
+      %poll = (
+        question  => $frm->{poll_q},
+        preview   => $frm->{poll_preview}?1:0,
+        recast    => $frm->{poll_recast}?1:0,
+        options   => \@options,
+        max_options => $frm->{poll_max},
+      );
+      push @{$frm->{_err}}, [ 'poll_max', 'max', scalar @options ] if @options > 1 && @options < $poll{max_options};
+      push @{$frm->{_err}}, 'poll' if @options > $self->{poll_options} || @options < 2;
+    }
+
     if(!$frm->{_err}) {
       my($ntid, $nnum) = ($tid, $num);
 
@@ -218,6 +247,16 @@ sub edit {
         );
         $self->dbThreadEdit($tid, %thread)  if $tid;
         $ntid = $self->dbThreadAdd(%thread) if !$tid;
+        if(%poll) {
+          $poll{tid} = $ntid;
+          if($tid && $t->{poll}) {
+            my $same = (!first { !($t->{poll}{$_} ~~ $poll{$_}) } qw|question preview recast max_options|)
+                    && [ map $_->{option}, @{$t->{poll}{options}} ] ~~ $poll{options};
+            $self->dbPollEdit($t->{poll}{id}, %poll) unless $same;
+          } else {
+            $self->dbPollAdd(%poll);
+          }
+        }
       }
 
       # create/edit post
@@ -242,10 +281,18 @@ sub edit {
       $frm->{title} ||= $t->{title};
       $frm->{locked}  = $t->{locked} if !exists $frm->{locked};
       $frm->{hidden}  = $t->{hidden} if !exists $frm->{hidden};
+      if($t->{poll}) {
+        $frm->{poll_q}   ||= $t->{poll}{question};
+        $frm->{poll_max} ||= $t->{poll}{max_options};
+        $frm->{poll_preview} = $t->{poll}{preview} if !exists $frm->{poll_preview};
+        $frm->{poll_recast}  = $t->{poll}{recast}  if !exists $frm->{poll_recast};
+        $frm->{poll_opt} ||= join "\n", map $_->{option}, @{$t->{poll}{options}};
+      }
     }
   }
   delete $frm->{_err} unless ref $frm->{_err};
   $frm->{boards} ||= $board;
+  $frm->{poll_max} ||= 1;
 
   # generate html
   my $url = !$tid ? "/t/$board/new" : !$num ? "/t$tid/reply" : "/t$tid.$num/edit";
@@ -273,11 +320,54 @@ sub edit {
     ) : (),
     [ text   => name => mt('_postedit_form_msg').'<br /><b class="standout">'.mt('_inenglish').'</b>', short => 'msg', rows => 25, cols => 75 ],
     [ static => content => mt('_postedit_form_msg_format') ],
+    (!$tid || $num == 1) ? (
+      [ input => short => 'poll_q', name => mt('_postedit_form_poll_q'), width => 250 ],
+      $num && $frm->{poll_opt} ? (
+        [ static => content => '<b class="standout">'.mt('_postedit_form_poll_warning').'</b>' ]
+      ) : (),
+      [ text  => short => 'poll_opt', name => mt('_postedit_form_poll_opt').'<br /><i>'.mt('_postedit_form_poll_optmax', $self->{poll_options}).'</i>', rows => 8, cols => 35 ],
+      [ input => short => 'poll_max', width => 16, post => ' '.mt('_postedit_form_poll_max') ],
+      [ check => short => 'poll_preview', name => mt('_postedit_form_poll_view') ],
+      [ check => short => 'poll_recast',  name => mt('_postedit_form_poll_recast') ],
+      !$frm->{poll_opt} ? (
+        [ static => content => '<br /><a id="poll_add" class="hidden" href="#poll_q">'.mt('_postedit_form_poll_add').'</a>' ]
+      ) : (),
+    ) : (),
   ]);
   $self->htmlFooter;
 }
 
 
+sub vote {
+  my($self, $tid, $page) = @_;
+  return $self->htmlDenied if !$self->authCan('board');
+  return if !$self->authCheckCode;
+
+  my $url = '/t'.$tid.($page ? "/$page" : '');
+  my $poll = $self->dbPollGet(tid => $tid);
+  return $self->resNotFound if !%$poll;
+
+  # user has already voted and poll doesn't allow to change a vote.
+  return $self->resRedirect($url, 'post') if @{$poll->{user}} && !$poll->{recast};
+
+  my $f = $self->formValidate({
+    post => 'option', multi => 1, enum => [ map $_->{id}, @{$poll->{options}} ],
+  });
+  if(!$f->{_err} && (!@{$f->{option}} || @{$f->{option}} > $poll->{max_options})) {
+    push @{$f->{_err}}, 'poll';
+  }
+  if($f->{_err}) {
+    $self->htmlHeader(title => mt '_poll_error');
+    $self->htmlFormError($f, 1);
+    $self->htmlFooter;
+    return;
+  }
+
+  $self->dbPollVote($poll->{id}, uid => $self->authInfo->{id}, options => $f->{option});
+  $self->resRedirect($url, 'post');
+}
+
+
 sub board {
   my($self, $type, $iid) = @_;
   $iid ||= '';
@@ -510,7 +600,10 @@ sub _threadlist {
       my($self, $n, $o) = @_;
       Tr;
        td class => 'tc1';
-        a $o->{locked} ? ( class => 'locked' ) : (), href => "/t$o->{id}", shorten $o->{title}, 50;
+        a $o->{locked} ? ( class => 'locked' ) : (), href => "/t$o->{id}";
+         span class => 'pollflag', '['.mt('_threadlist_poll').']' if $o->{poll};
+         txt shorten $o->{title}, 50;
+        end;
         b class => 'boards';
          my $i = 1;
          my @boards = sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } grep $_->{type}.($_->{iid}||'') ne $board, @{$o->{boards}};
@@ -541,5 +634,71 @@ sub _threadlist {
 }
 
 
+sub _poll {
+  my($self, $url, $poll) = @_;
+  my %own_votes = map +($_ => 1), @{$poll->{user}} if @{$poll->{user}};
+  my $preview = !%own_votes && $self->reqGet('pollview') && $poll->{preview};
+
+  div class => 'mainbox poll';
+   form action => $url.'/vote', method => 'post';
+    h1 class => 'question', $poll->{question} if $poll->{question};
+    input type => 'hidden', name => 'formcode', value => $self->authGetCode($url.'/vote');
+    table class => 'votebooth';
+      my $allow_vote = $self->authCan('board') && (!%own_votes || $poll->{recast});
+      if($allow_vote && $poll->{max_options} > 1) {
+        thead; Tr; td colspan => 3;
+         i mt('_poll_choose', $poll->{max_options});
+        end; end; end;
+      }
+      tfoot; Tr;
+       td class => 'tc1';
+        input type => 'submit', class => 'submit', value => mt('_poll_vote') if $allow_vote;
+        if(!$self->authCan('board')) {
+          b class => 'standout', mt('_poll_novote_login');
+        }
+       end;
+       td class => 'tc2', colspan => 2;
+        if($poll->{preview} || %own_votes) {
+          if(!$poll->{votes}) {
+            i mt('_poll_no_votes');
+          } elsif(!$preview && !%own_votes) {
+            a href => $url.'?pollview=1', id => 'pollpreview', mt('_poll_results');
+          } else {
+            txt mt('_poll_total_votes', $poll->{votes});
+          }
+        }
+       end;
+      end; end;
+     tbody;
+      my $max = max map $_->{votes}, @{$poll->{options}};
+      my $show_graph = $max && (%own_votes || $preview);
+      my $graph_width = 200;
+      for my $opt (@{$poll->{options}}) {
+        my $own = exists $own_votes{$opt->{id}} ? ' own' : '';
+        Tr $own ? (class => 'odd') : ();
+         td class => 'tc1';
+          label;
+           input type => $poll->{max_options} > 1 ? 'checkbox' : 'radio', name => 'option', class => 'option', value => $opt->{id}, $own ? (checked => '') : () if $allow_vote;
+           span class => 'option'.$own, $opt->{option};
+          end;
+         end;
+         if($show_graph) {
+           td class => 'tc2';
+            div class => 'graph', style => sprintf('width: %dpx', $opt->{votes}/$max*$graph_width), ' ';
+            div class => 'number', $opt->{votes};
+           end;
+           td class => 'tc3', sprintf('%.3g%%', $poll->{votes} ? $opt->{votes}/$poll->{votes}*100 : 0);
+         } else {
+           td class => 'tc2', colspan => 2, '';
+         }
+        end;
+      }
+     end;
+    end 'table';
+   end 'form';
+  end 'div';
+}
+
+
 1;
 
diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm
index afecbe08..e1f3bdfc 100644
--- a/lib/VNDB/Util/FormHTML.pm
+++ b/lib/VNDB/Util/FormHTML.pm
@@ -34,6 +34,8 @@ sub htmlFormError {
       if($type eq 'required') {
         li; lit mt $field eq 'editsum' ?'_formerr_tpl_editsum' : '_formerr_required', $field; end;
       }
+      li mt '_formerr_min', $field, $rule if $type eq 'min';
+      li mt '_formerr_max', $field, $rule if $type eq 'max';
       li mt '_formerr_minlength', $field, $rule if $type eq 'minlength';
       li mt '_formerr_maxlength', $field, $rule if $type eq 'maxlength';
       li mt '_formerr_enum', $field, join ', ', @$rule if $type eq 'enum';
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index ffc20cc2..d2d11b64 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -730,3 +730,27 @@ CREATE TABLE wlists (
   added timestamptz NOT NULL DEFAULT NOW(),
   PRIMARY KEY(uid, vid)
 );
+
+CREATE TABLE polls (
+  id SERIAL PRIMARY KEY,
+  tid integer UNIQUE NOT NULL DEFAULT 0, -- references threads
+  question varchar(100) NOT NULL DEFAULT '',
+  max_options smallint NOT NULL DEFAULT 1,
+  preview boolean NOT NULL DEFAULT FALSE,
+  recast boolean NOT NULL DEFAULT FALSE
+);
+
+CREATE TABLE polls_options (
+  id SERIAL PRIMARY KEY,
+  pid integer NOT NULL REFERENCES polls (id) ON DELETE CASCADE,
+  option varchar(100) NOT NULL
+);
+
+CREATE TABLE polls_votes (
+  pid integer NOT NULL REFERENCES polls (id) ON DELETE CASCADE,
+  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
+  optid integer NOT NULL REFERENCES polls_options (id) ON DELETE CASCADE,
+  PRIMARY KEY (pid, uid, optid)
+);
+
+ALTER TABLE polls ADD FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;