Allow photo upload on updates.

2 files changed, 76 insertions, 41 deletions

diff --git a/db/schema.sql b/db/schema.sql
index 4078421e8..a6609229b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -4,7 +4,7 @@
 -- Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 -- Email: matthew@mysociety.org; WWW: http://www.mysociety.org/
 --
--- $Id: schema.sql,v 1.37 2007-08-24 00:24:14 matthew Exp $
+-- $Id: schema.sql,v 1.38 2008-03-28 15:11:47 matthew Exp $
 --
 
 -- secret
@@ -222,6 +222,7 @@ create table comment (
     website text,
     created timestamp not null default ms_current_timestamp(),
     text text not null,                     -- as entered by comment author
+    photo bytea,
     state text not null check (
         state = 'unconfirmed'
         or state = 'confirmed'
diff --git a/web/index.cgi b/web/index.cgi
index f97d0846d..69cb598f7 100755
--- a/web/index.cgi
+++ b/web/index.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: index.cgi,v 1.182 2008-03-26 23:10:53 matthew Exp $
+# $Id: index.cgi,v 1.183 2008-03-28 15:11:48 matthew Exp $
 
 use strict;
 use Standard;
@@ -158,6 +158,13 @@ sub submit_update {
     my @vars = qw(id name email update fixed);
     my %input = map { $_ => $q->param($_) || '' } @vars;
     my @errors;
+
+    my $fh = $q->upload('photo');
+    if ($fh) {
+        my $err = check_photo($q, $fh);
+        push @errors, $err if $err;
+    }
+
     push(@errors, 'Please enter a message') unless $input{update} =~ /\S/;
     $input{name} = undef unless $input{name} =~ /\S/;
     if ($input{email} !~ /\S/) {
@@ -165,14 +172,26 @@ sub submit_update {
     } elsif (!mySociety::EmailUtil::is_valid_email($input{email})) {
         push(@errors, 'Please enter a valid email');
     }
+
+    my $image;
+    if ($fh) {
+        try {
+            $image = process_photo($fh);
+        } catch Error::Simple with {
+            my $e = shift;
+            push(@errors, "That image doesn't appear to have uploaded correctly ($e), please try again.");
+        };
+    }
+
     return display_problem($q, @errors) if (@errors);
 
     my $id = dbh()->selectrow_array("select nextval('comment_id_seq');");
-    dbh()->do("insert into comment
-        (id, problem_id, name, email, website, text, state, mark_fixed)
-        values (?, ?, ?, ?, ?, ?, 'unconfirmed', ?)", {},
-        $id, $input{id}, $input{name}, $input{email}, '', $input{update},
-        $input{fixed}?'t':'f');
+    workaround_pg_bytea("insert into comment
+        (id, problem_id, name, email, website, text, state, mark_fixed, photo)
+        values (?, ?, ?, ?, '', ?, 'unconfirmed', ?, ?)", 7,
+        $id, $input{id}, $input{name}, $input{email}, $input{update},
+        $input{fixed} ? 't' : 'f', $image);
+
     my %h = ();
     $h{update} = $input{update};
     $h{name} = $input{name} ? $input{name} : "Anonymous";
@@ -183,6 +202,19 @@ sub submit_update {
     return $out;
 }
 
+sub workaround_pg_bytea {
+    my ($st, $img_idx, @elements) = @_;
+    my $s = dbh()->prepare($st);
+    for ($i=1; $i<=@elements; $i++) {
+        if ($i == $img_idx) {
+            $s->bind_param($i, $elements[$i-1], { pg_type => DBD::Pg::PG_BYTEA });
+        } else {
+            $s->bind_param($i, $elements[$i-1]);
+        }
+    }
+    $s->execute();
+}
+
 sub submit_problem {
     my $q = shift;
     my @vars = qw(council title detail name email phone pc easting northing skipped anonymous category flickr);
@@ -195,12 +227,8 @@ sub submit_problem {
 
     my $fh = $q->upload('photo');
     if ($fh) {
-        my $ct = $q->uploadInfo($fh)->{'Content-Type'};
-        my $cd = $q->uploadInfo($fh)->{'Content-Disposition'};
-        # Must delete photo param, otherwise display functions get confused
-        $q->delete('photo');
-        push (@errors, 'Please upload a JPEG image only') unless
-            ($ct eq 'image/jpeg' || $ct eq 'image/pjpeg');
+        my $err = check_photo($q, $fh);
+        push @errors, $err if $err;
     }
 
     push(@errors, 'No council selected') unless ($input{council} && $input{council} =~ /^(?:-1|[\d,]+(?:\|[\d,]+)?)$/);
@@ -276,15 +304,7 @@ sub submit_problem {
     my $image;
     if ($fh) {
         try {
-            $image = Image::Magick->new;
-            my $err = $image->Read(file => \*$fh); # Mustn't be stringified
-            close $fh;
-            throw Error::Simple("read failed: $err") if "$err";
-            $err = $image->Scale(geometry => "250x250>");
-            throw Error::Simple("resize failed: $err") if "$err";
-            my @blobs = $image->ImageToBlob();
-            undef $image;
-            $image = $blobs[0];
+            $image = process_photo($fh);
         } catch Error::Simple with {
             my $e = shift;
             push(@errors, "That image doesn't appear to have uploaded correctly ($e), please try again.");
@@ -315,28 +335,15 @@ sub submit_problem {
         }
     } else {
         $id = dbh()->selectrow_array("select nextval('problem_id_seq');");
-        # This is horrid
-        my $s = dbh()->prepare("insert into problem
+        workaround_pg_bytea("insert into problem
             (id, postcode, easting, northing, title, detail, name,
              email, phone, photo, state, council, used_map, anonymous, category, areas)
             values
-            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'unconfirmed', ?, ?, ?, ?, ?)");
-        $s->bind_param(1, $id);
-        $s->bind_param(2, $input{pc});
-        $s->bind_param(3, $input{easting});
-        $s->bind_param(4, $input{northing});
-        $s->bind_param(5, $input{title});
-        $s->bind_param(6, $input{detail});
-        $s->bind_param(7, $input{name});
-        $s->bind_param(8, $input{email});
-        $s->bind_param(9, $input{phone});
-        $s->bind_param(10, $image, { pg_type => DBD::Pg::PG_BYTEA });
-        $s->bind_param(11, $input{council});
-        $s->bind_param(12, $used_map);
-        $s->bind_param(13, $input{anonymous} ? 'f': 't');
-        $s->bind_param(14, $input{category});
-        $s->bind_param(15, $areas);
-        $s->execute();
+            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'unconfirmed', ?, ?, ?, ?, ?)", 10,
+            $id, $input{pc}, $input{easting}, $input{northing}, $input{title},
+            $input{detail}, $input{name}, $input{email}, $input{phone}, $image,
+            $input{council}, $used_map, $input{anonymous} ? 'f': 't', $input{category},
+            $areas);
         my %h = ();
         $h{title} = $input{title};
         $h{detail} = $input{detail};
@@ -754,6 +761,8 @@ EOF
 <div><label for="form_update">Update:</label>
 <textarea name="update" id="form_update" rows="7" cols="30">$input_h{update}</textarea></div>
 $fixedline
+<div><label for="form_photo">Photo:</label>
+<input type="file" name="photo" id="form_photo"></div>
 <div class="checkbox"><input type="submit" value="Post"></div>
 </form>
 EOF
@@ -825,3 +834,28 @@ sub map_pins {
     return ($pins, $current_map, $current, $fixed, $dist);
 }
 
+sub check_photo {
+    my ($q, $fh) = @_;
+    my $ct = $q->uploadInfo($fh)->{'Content-Type'};
+    my $cd = $q->uploadInfo($fh)->{'Content-Disposition'};
+    # Must delete photo param, otherwise display functions get confused
+    $q->delete('photo');
+    return 'Please upload a JPEG image only' unless
+        ($ct eq 'image/jpeg' || $ct eq 'image/pjpeg');
+    return '';
+}
+
+sub process_photo {
+    my $fh = shift;
+    my $photo = Image::Magick->new;
+    my $err = $photo->Read(file => \*$fh); # Mustn't be stringified
+    close $fh;
+    throw Error::Simple("read failed: $err") if "$err";
+    $err = $photo->Scale(geometry => "250x250>");
+    throw Error::Simple("resize failed: $err") if "$err";
+    my @blobs = $photo->ImageToBlob();
+    undef $photo;
+    $photo = $blobs[0];
+    return $photo;
+}
+