root/feedmelinks/bin/add-link.pl

#!/usr/local/bin/perl
use strict;

use DBD::mysql;
use FeedMeLinks::Environment;
use LWP;
use POSIX qw(strftime);

# takes an email as stdin, "authenticates" the sender against 
# the FML db, uses the subject as the link name, the first 
# line as the link URL, and the rest of the body as comments 
# to the link

my $verbose = 0;
my $now = strftime "%a %b %e %H:%M:%S %Y", localtime;

# flags
my $headersDone = 0;
my $messageDone = 0;

# fields
my $title;
my $submitter;
my $link;
my $body = "";

my $line;
my $tagName = "new";

open(LOG, ">>" . FeedMeLinks::Environment::get_webserver_root() . "/_logs/mail.log") or die "can't open file";
print LOG "--- starting run ---\n" if( $verbose );

while( <> ) {
        my $line = $_;
        chomp( $line );
        if( !$messageDone ) {
                if( !$headersDone ) {
                        if( $line eq "" || m/^\s*$/ ) {
                                $headersDone = 1;
                        } elsif( m/^Subject:/ ) {
                                print LOG "THIS IS THE LINK TITLE! $line\n" if( $verbose );
                                $title = $line;
                                $title =~ s/Subject: //;
                        } elsif( m/^From:/ ) {
                                if( ! $submitter ) {
                                        $submitter = $line;
                                        $submitter =~ s/From: //;
                                        $submitter =~ s/^.*<//;
                                        $submitter =~ s/>.*$//;
                                        print LOG "THIS IS WHO FROM! $submitter\n" if( $verbose );
                                }
                        } 
                } else {
                        if( m/^\s*(http|ftp)/ ) {
                                if( ! $link ) {
                                        print LOG "THIS IS THE LINK: $line\n" if( $verbose );
                                        $link = $line;
                                        $link =~ s/^\s*//;
                                        $link =~ s/\s*$//;
                                } else {
                                        # treat other LOG as text
                                        $body = "$body\n$line";
                                        $body =~ s/Sent via BlackBerry from Cingular Wireless//g;
                                        print LOG "$body\n" if( $verbose );
                                }
                        } elsif( m/^\s*--\s*$/ ) {
                                print LOG "message is done\n" if( $verbose );
                                $messageDone = 1
                        } else {
                                next if( m/^$/ );
                                $body = "$body\n$line";
                                print LOG "added line to body\n" if( $verbose );
                        }
                }
        }
}

if( $headersDone && $title && $submitter && $link ) {
        print LOG "adding link to db\n" if( $verbose );
        my $dsn = "DBI:mysql:database=" . FeedMeLinks::Environment::get_database_name() . ";host=localhost"; # FIXME: need to retrieve this from our config arrays...
        my $dbh = DBI->connect($dsn, FeedMeLinks::Environment::get_database_user(), FeedMeLinks::Environment::get_database_password() );

        if( ! $dbh ) {
                print LOG "$now [link-by-mail error] cannot connect to DB]\n";
                die( "can't connect to DB, fatal error\n" );
        }

        my $qs = "SELECT userid, password FROM users WHERE email = ? OR email = ? LIMIT 1";
        my $sth = $dbh->prepare( $qs );

        # because the DB emails are URL-encoded
        my $encoded_submitter = $submitter;
        $encoded_submitter =~ s/@/%40/;

        $sth->execute( $submitter, $encoded_submitter );
        my @ary = $sth->fetchrow_array;
        my $dbUser = $ary[0];
        my $password = $ary[1];
        if( $dbUser ) {
                print LOG "we found you and you are $dbUser\n" if( $verbose );

                $qs = "INSERT INTO links VALUES( NULL, ?, ?, NULL, NULL, ?, NULL, NULL)";
                $sth = $dbh->prepare( $qs );
                $sth->execute( $link, $title, $dbUser );

                $qs = "SELECT LAST_INSERT_ID() AS last";
                $sth = $dbh->prepare( $qs );
                $sth->execute();
                my $linkID = $sth->fetchrow_array;
                if( $linkID ) {
                        print LOG "$now [link-by-mail] $dbUser added $linkID\n";

                        $qs = "SELECT ID FROM tags WHERE name = ? AND userid = ? AND isPrivate IS NULL";
                        $sth = $dbh->prepare( $qs );
                        $sth->execute( $tagName, $dbUser );
                        @ary = $sth->fetchrow_array;
                        my $tagID = @ary[0];
                        if( ! $tagID ) {
                                print LOG "couldn't find a tag named $tagName; trying to insert it\n" if( $verbose );
                                $qs = "INSERT INTO tags VALUES( NULL, ?, ?, NULL, NULL)";
                                print LOG "SQL: $qs with $dbUser, $tagName\n" if( $verbose );
                                $sth = $dbh->prepare( $qs );
                                $sth->execute( $dbUser, $tagName );

                                $qs = "SELECT LAST_INSERT_ID() AS last";
                                $sth = $dbh->prepare( $qs );
                                $sth->execute();
                                @ary = $sth->fetchrow_array;
                                $tagID = @ary[0];
                                if( $tagID ) {
                                        print LOG "created new tag '$tagName' with ID $tagID\n" if( $verbose );
                                } else {
                                        print LOG "creating new tag '$tagName' failed!\n" if( $verbose );
                                }
                        }
                        if( $tagID ) {
                                print LOG "found existing tag as ID $tagID\n" if( $verbose );
                                $qs = "INSERT INTO links_tags_xref VALUES( ?, ?)";
                                $sth = $dbh->prepare( $qs );
                                $sth->execute( $linkID, $tagID );

                                my $ua = LWP::UserAgent->new;
                                my $url = "http://feedmelinks.com/xml/set/comment";
                                my $response = $ua->post( $url,
                                        [ 'id' => $linkID, 'comments' => $body, 'user' => $dbUser, 'password' => $password ]
                                );
                                print LOG "response: " . $response->content . "\n" if( $verbose );
                                die "$url error: ", $response->status_line
                                unless $response->is_success;

                        } else {
                                print LOG "$now [link-by-mail error] no tag found: $dbUser, tag: '$tagName'\n";
                                die( "cant find a tag for this link, searching for '$tagName' failed, and adding a new one failed" );
                        }
                }
        } else {
                print LOG "$now [link-by-mail error] unregistered user $submitter\n";
                die( "The email address $submitter isn't registered with any Feed Me Links user. Perhaps you registered under another name?" );
        }
}

print LOG "body!: $body\n" if( $verbose );
print LOG "--- ending run ---\n\n" if( $verbose );
close(LOG);