PlexLuthor

I have been keeping a data collection spreadsheet as part of my New Year's resolutions. I'm tracking my weight, mostly, but also the family's health, gas mileage, water usage, and maybe a few other things. The spreadsheet is getting long and unwieldy, and it's a pain to update it from the iPod, so I thought it would be fun to set up a database on my home computer and figure out a way to make entries in the database by sending myself an email. I spent one late night working on it, and have the proof-of-concept down. It's not quite to the point where I'm using it instead of the spreadsheet, but it's close.

The basic flow is Gmail Filter->Fetchmail->Postfix->Procmail->Perl->MySQL. I haven't made something that reads the data and makes a graph, which is why I'm still using the spreadsheet. For future reference (in case my hard drive crashes or something) here's the fetchmail rule:

:0:
* ^Subject:.*testDB
{
:0 B c
* ^db\/.*$
| $HOME/myscripts/testdbinsert.pl $MATCH
}

# EOF

Here's testdbinsert.pl:

#!/usr/bin/perl

use CGI;
use DBI;

my $host = 'localhost';
my $db = 'datacollection';
my $db_user = 'data';
my $db_password = 'password';

my $dbh;

my $dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password");

for ($varnum = 0; $varnum < $#ARGV; $varnum=$varnum+2) {

my $name = $ARGV[$varnum];
my $value = $ARGV[$varnum+1];

$sql = "insert into generic_data (data_name, data_value) values ('$name',$value);";

$sth = $dbh->prepare($sql);
$sth->execute();

}

exit(0);

Here's the fetchmail config file:

poll imap.gmail.com
with protocol IMAP
user "steve.markham@gmail.com" there is steve here
password "password"
folder 'testDB'
with ssl
keep

The Gmail filter applies the testDB label to any email from me, me at work, or Janet if the subject is test (or DB, or others, I tried a few things). Everything else was a standard install, I think. Here are some snippets from the output of "history":

1838 sudo apt-get install libctemplate0 libzip1 python-pysqlite2 mysql-client python-crypto python-paramiko
1839 sudo dpkg -i /home/steve/Downloads/mysql-workbench-gpl-5.2.38-1ubu1004-i386.deb
1840 sudo apt-get install mysql-server

1844 sudo apt-get install fetchmail
1845 which fetchmail
1846 vim .forward
1847 which procmail
1848 sudo apt-get install procmail
1849 cat /etc/fetchmail.rc
1850 which fetchmail
1851 fetchmail
1852 vim .fetchmailrc
1853 fetchmail
1854 chmod 700 .fetchmailrc
1855 fetchmail
1856 vim .fetchmailrc