Data Collection

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

1870 which sendmail

1871 sudo apt-get install postfix

1872 fetchmail -v > testfetch.txt

Lastly, here are some of the websites that I used as references:

http://www.ii.com/internet/robots/procmail/qs/ - Procmail quick start tutorial

http://www.cyberciti.biz/faq/howto-pass-perl-command-line-arguments/ -

http://support.verio.com/documents/view_article.cfm?doc_id=1212 - lots of procmail examples

http://www.syntheticzero.com/howto/vmail.php - has the procmail/MySQL chain

http://forums.whirlpool.net.au/archive/360121 - has the perl/MySQL example

I'll try to post more if/when I update it to make the chart and be a little more flexible.