Pulling Sender and Subject from Gmail part 2

I'm not a database guy. I've used sqlite3 several times on Android to pull information out of db files, but I usually just dump everything and filter it with tools I'm familiar with and never sat down and tried to learn much about it until tonight. What I learned was just enough basics to make a database admin say "Aww, that's cute... he's twying to wearn something" but it's already useful.

My problem was that I had never actually viewed a db file before. I had dumped them and looked at the output and wondered why there was no way to identify and extract a certain column. It wasn't until I actually tapped on a file and opened it to see that the columns were indeed named and sought out a way to utilize that.

It's actually so simple I could kick myself for not looking sooner. I was toying with pulling data from the gmail file again and found that you can pull data from other columns and limit the output to only show one record (like head -1), but that gives you the oldest record at the top of the file. Turns out, the first column in the gmail file it named _id and it's just line numbers, and you can sort the output by the _id column in reverse order and grab that top line to get the newest. It's as simple as "ORDER BY _id DESC LIMIT 1"

Anyway, I came up with a cleaner way to grab the sender, sender's email address and the subject from the most recent email, or all three together like I had previously posted. This script will do all four.

Here is what it looks like. I just signed up for an account at RootzWiki, so that's my most recent email.

# mailsender email
staff@rootzwiki.com
# mailsender name
RootzWiki
# mailsender subject
New Registration at RootzWiki
# mailsender
"RootzWiki" <staff@rootzwiki.com> New Registration at RootzWiki
#

Using this, it's easy to trigger a Tasker action based on any of those criteria.

First you set up an event profile based on a ui notification owned by gmail, and use it to trigger a task like this:

1- using the Locale Execute Plugin, execute one of the commands and redirect it to a file
2- wait 1 second (usually a good idea with commands like this)
3- read line 1 from the output file to %var
4- Do something if %var matches whatever

The email address is probably the best thing to match, but you could also use it to for a name or a certain subject, just adjust the command accordingly.

All you need to do is replace YOURNAME with your google login name on the second like because the database file is named after your gmail name.

#! /system/bin/sh

yourmail="YOURNAME@gmail.com"

dir="/data/data/com.google.android.gm/databases/"
case "$1" in

name)
sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o '"[^"]*"' | tr -d \";;

email)
sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\> | tr -d '<>';;

subject)
sqlite3 ${dir}mailstore.${yourmail}.db "select subject from messages ORDER BY _id DESC LIMIT 1";;

*)
sqlite3 ${dir}mailstore.${yourmail}.db "select * from messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;

esac

If you want to play around, you can also extract a preview to the email, or even the entire body with these sqlite3 commands:

"select snippet from messages"
"select body from messages"

I think I'm done playing with it, but your gmail messages are completely accessible from the command line on Android. Might be useful for something.

Edit: Jan 20. Here's a QR code to copy the script to your phone's clipboard.