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/perluse 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.comwith protocol IMAPuser "steve.markham@gmail.com" there is steve herepassword "password"folder 'testDB'with sslkeep
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-paramiko1839 sudo dpkg -i /home/steve/Downloads/mysql-workbench-gpl-5.2.38-1ubu1004-i386.deb1840 sudo apt-get install mysql-server
1844 sudo apt-get install fetchmail1845 which fetchmail1846 vim .forward1847 which procmail1848 sudo apt-get install procmail1849 cat /etc/fetchmail.rc1850 which fetchmail1851 fetchmail1852 vim .fetchmailrc1853 fetchmail1854 chmod 700 .fetchmailrc1855 fetchmail1856 vim .fetchmailrc
1870 which sendmail1871 sudo apt-get install postfix1872 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://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.