Get new Gmail sender and subject?
3,130 views
Skip to first unread message
Tye
Jul 22, 2011, 2:14:28 AM7/22/11
to tas...@googlegroups.com
I read on XDA (says brandall) that Tasker might be receiving the ability to get more info out of Gmail really soon. Is this the case? If so, it would be really useful and cleaner than what I'm working on.
(brandall, if you're reading this, yeah I'm the guy that PM'd you)
Mark
Jan 17, 2012, 5:08:25 AM1/17/12
to tas...@googlegroups.com
Did anything happen with this? I've been searching for a way to get Tasker to check if the sender is x,y or z when new emails arrive in gmail and if so make phone sound until acknowledged.
BossMan
Jan 17, 2012, 6:05:17 AM1/17/12
to tas...@googlegroups.com
I wouldn't count on this, unless of course there is some other undocumented way for doing this.
BR,
A.
fubaya
Jan 17, 2012, 8:31:43 PM1/17/12
to Tasker
If you're rooted, you can have Tasker to listen for a statusbar
notification belonging to the gmail app then run this command with
locale execute:
sqlite3 /data/data/com.google.android.gm/databases/
mailstore...@gmail.com.db "select * from messages" | awk '/@/
{FS="|";print $4,$11}' | tail -n1 > /sdcard/Tasker/email
Just replace YOURNAME with your gmail login name.
In the /sdcard/Tasker/email file you should have the sender's name,
email and subject like: "John" <joh...@isp.net> Hey what's up?
(completely untested- i know the notification should work and the
command works (I barely tested it) but I didn't put them all together,
let alone check for bugs)
notification belonging to the gmail app then run this command with
locale execute:
sqlite3 /data/data/com.google.android.gm/databases/
mailstore...@gmail.com.db "select * from messages" | awk '/@/
{FS="|";print $4,$11}' | tail -n1 > /sdcard/Tasker/email
Just replace YOURNAME with your gmail login name.
In the /sdcard/Tasker/email file you should have the sender's name,
email and subject like: "John" <joh...@isp.net> Hey what's up?
(completely untested- i know the notification should work and the
command works (I barely tested it) but I didn't put them all together,
let alone check for bugs)
Mark
Jan 19, 2012, 10:42:17 AM1/19/12
to tas...@googlegroups.com
Thanks fubaya. I've looked at this but I can't even find the folder
/data/data/com.google.android.gm/databases/ so think it's way above where I'm currently at with Android/Tasker knowledge.
Should this folder be in the same place on all Android based systems or does its location change?
fubaya
Jan 19, 2012, 11:33:43 AM1/19/12
to Tasker
It's possible. Im on ICS. Is there anything else with gm in the name
in /data/datadata? It would probably be with the com.android or
com.google apps, no need to look in the apps that start with z. If you
have a terminal on the phone or can use adb shell, this command should
find it: ls /data/data | grep gm. Im assuming it will have "gm" in the
name.
in /data/datadata? It would probably be with the com.android or
com.google apps, no need to look in the apps that start with z. If you
have a terminal on the phone or can use adb shell, this command should
find it: ls /data/data | grep gm. Im assuming it will have "gm" in the
name.
Mark
Jan 19, 2012, 12:13:22 PM1/19/12
to tas...@googlegroups.com
Ok, apologies, I was using a file manager to look for the folder and it wasn't running as su so looked empty. Checked with terminal and can now see
/data/data/com.google.android.gm
I pasted the sqlite3 command and then checked /sdcard/Tasker/email and this does contain e.g. "John" <joh...@isp.net> Hey what's up?
Getting there!
Thought that I could monitor %NITLTE for when it wasn't empty to check for notifications but that's apparently not as easy as it sounded. When I checked the %NTITLE variable but this just says "Alarm"
It must be possible so I'll go away and investigate further how the %NTITLE works. My plan is to monitor this for changes which contain "@gmail.com" then fire your sqlite script and finally ready the contents via TTS if that's possible (not looked yet).
Thanks for your help, I'll try and think a little further before coming back with "I can't find x" :)
TomL
Jan 19, 2012, 12:25:46 PM1/19/12
to tas...@googlegroups.com
Here's an idea. You can bypass the notification trigger completely by setting the profile to fire when the gmail.com.db file is modified.
Tom
fubaya
Jan 19, 2012, 4:19:34 PM1/19/12
to Tasker
I've never had a file modification profile work very well, but I
haven't tried in a long time either. Is there any difference in
battery usage between the two?
I tested this and it did work but I only had it beep and never tested
with the command, and watching the file may be better anyway. create a
new profile> event> ui> notification then select gmail as the owner
application. Then my task just beeped but you'd have a task run the
command, probably wait 1 second, read the file and see if it matches
John Doe and his address and, if so, say "John's emailing you" or
whatever.
I may do this tonight for a couple people and can post details if it
works. Is it me or is posting here on a phone nearly impossible?
haven't tried in a long time either. Is there any difference in
battery usage between the two?
I tested this and it did work but I only had it beep and never tested
with the command, and watching the file may be better anyway. create a
new profile> event> ui> notification then select gmail as the owner
application. Then my task just beeped but you'd have a task run the
command, probably wait 1 second, read the file and see if it matches
John Doe and his address and, if so, say "John's emailing you" or
whatever.
I may do this tonight for a couple people and can post details if it
works. Is it me or is posting here on a phone nearly impossible?
fubaya
Jan 19, 2012, 7:29:46 PM1/19/12
to Tasker
Ok, it works but my TTS isn't working for some reason so I just
flashed a message for success. I didn't have time to try the file
modification method yet.
For the profile, click the + to add a new profile, name it, select
"event", then UI, then notification, then click "owner application"
and select gmail.
For the task:
1- using the Locale Execute Plugin, execute the command (more on this
in a minute)
2- wait 1 second (usually a good idea with commands like this)
3- read line 1 from the output file to variable %BLAH
4- Say "Success" if %BLAH matches whatever
5- say "the boss just emailed" if %BLAH matches your bosses address.
You could have a long list of "say ifs"
Instead of the command I posted earlier, i decided to slap together a
script that can extract the sender's name, address and subject, or any
single one of those alone. That will make it easier to match either of
those fields. I think their email is more reliable. You can make it
say anything if the email matches anyway, so if you know 4 Johns you
can match the email and make it say "John from work just emailed you"
To use it, copy everything below from "#! /system/bin/sh" to "esac"
and put it in a text file. I called the file "mailsender" so let's use
that as as example. Where is says "yourmail=", put your gmail address
to the right of the equal sign and put the file on the sdcard.
First, there are three options to it. name, email, subject. Here's the
commands and what they output"
mailsender name -- outputs the name
mailsender email -- outputs the sender's email address
mailsender subject -- outputs the subject
mailsender -- used with no options, it outputs all three.
Now, there are two ways to run it and the commands are different for
each. If you want to be able to use it system wide (like from a
terminal), put it in /system/xbin and make it executable. From a
terminal that would be done with the two commands:
cp /sdcard/mailsender /system/xbin
chmod 755 /system/xbin/mailsender
Otherwise, you can leave it on the sdcard but to run it you have to
add "sh" and the full path to the script instead of just it's name.
So, if you leave it on the sdcard, the commands would be
sh /sdcard/mailsender name
sh /sdcard/mailsender email
sh /sdcard/mailsender subject
sh /sdcard/mailsender
Don't forget to send the output to a file. That's done with a ">"
symbol followed by the name of the file. Then with the task, select
that file to read.
e.g.
mailsender email > /sdcard/somefile
or
sh /sdcard/mailsender subject > /sdcard/somefile
That should cover it if I didn't make it too confusing. Here's the
script:
#! /system/bin/sh
yourmail=
case "$1" in
name)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $4}' |
tail -n1 | grep -o '"[^"]*"' | tr -d \";;
email)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $4}' |
tail -n1 | grep -o \<.*\> | tr -d '<>';;
subject)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $11}' |
tail -n1;;
*)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print
$4,$11}' | tail -n1;;
esac
flashed a message for success. I didn't have time to try the file
modification method yet.
For the profile, click the + to add a new profile, name it, select
"event", then UI, then notification, then click "owner application"
and select gmail.
For the task:
1- using the Locale Execute Plugin, execute the command (more on this
in a minute)
2- wait 1 second (usually a good idea with commands like this)
3- read line 1 from the output file to variable %BLAH
4- Say "Success" if %BLAH matches whatever
5- say "the boss just emailed" if %BLAH matches your bosses address.
You could have a long list of "say ifs"
Instead of the command I posted earlier, i decided to slap together a
script that can extract the sender's name, address and subject, or any
single one of those alone. That will make it easier to match either of
those fields. I think their email is more reliable. You can make it
say anything if the email matches anyway, so if you know 4 Johns you
can match the email and make it say "John from work just emailed you"
To use it, copy everything below from "#! /system/bin/sh" to "esac"
and put it in a text file. I called the file "mailsender" so let's use
that as as example. Where is says "yourmail=", put your gmail address
to the right of the equal sign and put the file on the sdcard.
First, there are three options to it. name, email, subject. Here's the
commands and what they output"
mailsender name -- outputs the name
mailsender email -- outputs the sender's email address
mailsender subject -- outputs the subject
mailsender -- used with no options, it outputs all three.
Now, there are two ways to run it and the commands are different for
each. If you want to be able to use it system wide (like from a
terminal), put it in /system/xbin and make it executable. From a
terminal that would be done with the two commands:
cp /sdcard/mailsender /system/xbin
chmod 755 /system/xbin/mailsender
Otherwise, you can leave it on the sdcard but to run it you have to
add "sh" and the full path to the script instead of just it's name.
So, if you leave it on the sdcard, the commands would be
sh /sdcard/mailsender name
sh /sdcard/mailsender email
sh /sdcard/mailsender subject
sh /sdcard/mailsender
Don't forget to send the output to a file. That's done with a ">"
symbol followed by the name of the file. Then with the task, select
that file to read.
e.g.
mailsender email > /sdcard/somefile
or
sh /sdcard/mailsender subject > /sdcard/somefile
That should cover it if I didn't make it too confusing. Here's the
script:
#! /system/bin/sh
yourmail=
case "$1" in
name)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $4}' |
tail -n1 | grep -o '"[^"]*"' | tr -d \";;
email)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $4}' |
tail -n1 | grep -o \<.*\> | tr -d '<>';;
subject)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print $11}' |
tail -n1;;
*)
sqlite3 /data/data/com.google.android.gm/databases/mailstore.$
{yourmail}.db "select * from messages" | awk '/@/{FS="|";print
$4,$11}' | tail -n1;;
esac
fubaya
Jan 19, 2012, 7:35:03 PM1/19/12
to Tasker
Ahhh... bad formatting.
If copying and pasting that, please note everything from "sqlite3" to
";;" needs to be on a single line, not 3 like the site formatted it.
There should be four long "sqlite3" to ";;" lines.
If copying and pasting that, please note everything from "sqlite3" to
";;" needs to be on a single line, not 3 like the site formatted it.
There should be four long "sqlite3" to ";;" lines.
Mark
Jan 20, 2012, 6:17:52 PM1/20/12
to tas...@googlegroups.com
wow thanks fubaya, you must have put in some effort to get this working.
I'll have to look at this sunday as all I'm getting right now is Syntax error: ";;" unexpected when I try to just run the name command via shell and when I run the command from a profile I get EOF as it's not writing anything to the file - command was sh /sdcard/mailsender name > /sdcard/mailname
The script all fires when a new mail comes in so I know I have at least half of it working.
Has to be a simple mistake but I can't see it. Will try again on Sunday.
Have a good weekend :)
fubaya
Jan 20, 2012, 7:09:01 PM1/20/12
to Tasker
On Jan 20, 6:17 pm, Mark <mark.darwin.ema...@gmail.com> wrote:
> wow thanks fubaya, you must have put in some effort to get this working.
Not nearly as much as your think, which is probably why it doesn't
> wow thanks fubaya, you must have put in some effort to get this working.
work...
Everything looks right in my earlier post, but since this board
formatted it funny, it could be related to that. It's also possible
that it could get screwed up if transferring from a windows computer
to the phone.
I actually improved it a little (nothing you'd even notice though) and
blogged it here:
http://a-more-common-hades.blogspot.com/2012/01/pulling-sender-and-subject-from-gmail_19.html
It's formatted properly there and I just added a QR code. If you have
Barcode Scanner installed, go into settings and make sure "copy to
clipboard" is checked, then scan the code and, voila, it will be in
your clipboard. You can then paste it to /sdcard/mailsender. Change
YOURNAME to your gmail name and it should be good to go.
Mark
Jan 20, 2012, 7:09:17 PM1/20/12
to tas...@googlegroups.com
ok last attempt tonight still failed. Here's what I've got so far.
Profile:
with owner application set to gmail
Task:
Flash text "important message" ///this works
EXECUTE (locale execute) command sh /sdcard/mailsender subject > /sdcard/mailsubject ///this make a blank file only
wait 2 seconds ///works
Read Line mailsubject Line 1 to var %MAILSUBJECT ///works I think
Flash %MAILSUBJECT subject ///works but displays "EOF subject" because nothing is dumped to the file
Have now tested various scenarios including running this as RunShell command and execute is killing the rest of the task.
Mark
Jan 20, 2012, 7:12:19 PM1/20/12
to tas...@googlegroups.com
hah never mind the post above, looks like we both posted at the same time. Will try you're new version from the blog now. Thanks very much for this. :)
fubaya
Jan 20, 2012, 9:05:29 PM1/20/12
to Tasker
make sure to run the command with locale execute as root with an
exclamation point. E.g. "! sh /sdcard/mailsender" it will flash a
notification when it runs, to get rid of that add a "@". E.g.: @! sh /
sdcard/mailsender
Those are the only options with the plugin and they're rather hidden I
think.
If nothing works I'll upload my profile and task later
exclamation point. E.g. "! sh /sdcard/mailsender" it will flash a
notification when it runs, to get rid of that add a "@". E.g.: @! sh /
sdcard/mailsender
Those are the only options with the plugin and they're rather hidden I
think.
If nothing works I'll upload my profile and task later
Mark
Jan 25, 2012, 6:35:29 PM1/25/12
to tas...@googlegroups.com
Thanks for that. I was missing the !@ at the beginning of the line. Now have it working roughly, just need to fine tune it.
*grins* now a very happy man
Amer
Feb 2, 2012, 3:33:37 AM2/2/12
to tas...@googlegroups.com
Hi there! sorry to bring up the old thread, but I really interesting in this function.
so far, I've manage to pull the trick explain by fubaya (mark plz ignore my pm) :)
but I've noticed from the command and from some testing that the "ORDER BY _id DESC LIMIT 1" is retrieving the info from the latest email only. And as you may know that using the gmail notification function, you may limit those notification only from chosen label.
And if I directly use those command, the problem that may occure will be retrieving the wrong email address, because the notification only notify the chosen label (not the latest email) while the command retrieve the last email being received.
the question is, How should I modified the command so that I only retrieve last email from chosen label? or can I retrieve the last email label?
Any suggestion/idea/thought might give a huge help for it coming so close to perfection :)
Thx in advance...
--
Be first, be better, or cheat.™
so far, I've manage to pull the trick explain by fubaya (mark plz ignore my pm) :)
but I've noticed from the command and from some testing that the "ORDER BY _id DESC LIMIT 1" is retrieving the info from the latest email only. And as you may know that using the gmail notification function, you may limit those notification only from chosen label.
And if I directly use those command, the problem that may occure will be retrieving the wrong email address, because the notification only notify the chosen label (not the latest email) while the command retrieve the last email being received.
the question is, How should I modified the command so that I only retrieve last email from chosen label? or can I retrieve the last email label?
Any suggestion/idea/thought might give a huge help for it coming so close to perfection :)
Thx in advance...
On Thu, Jan 26, 2012 at 6:35 AM, Mark <mark.darw...@gmail.com> wrote:
Thanks for that. I was missing the !@ at the beginning of the line. Now have it working roughly, just need to fine tune it.*grins* now a very happy man
Matthieu Berthomé
Feb 2, 2012, 12:04:10 PM2/2/12
to tas...@googlegroups.com
To get labels, you have to use different tables.
The message_labels table links each messageID to a specific labelID. In order to get the latest unread message in a specific label,
The labels table links each labelID to its name. To find the label id of the label you want, you'll need to explore the mailstore.db file and its labels table with a sqlite explorer, or do this command in a shell
sqlite3 ${dir}mailstore.${yourmail}.db "select * from labels"
to dump its content
Once you have found the id of the label you want (example, in my case, I have 619327313 as the priority inbox id), you have to use the message_labels table to link each message to its label. And, finally, lookup in the messages table the latest (biggest id) message with the labelID.
Tentatively:
sqlite3 path-to-mailstore.db "select fromAddress from messages where messageId (SELECT message_messageId FROM message_labels WHERE labels_id 619327313) ORDER BY _id DESC LIMIT 1"
Does that work ?
The message_labels table links each messageID to a specific labelID. In order to get the latest unread message in a specific label,
The labels table links each labelID to its name. To find the label id of the label you want, you'll need to explore the mailstore.db file and its labels table with a sqlite explorer, or do this command in a shell
sqlite3 ${dir}mailstore.${yourmail}.db "select * from labels"
to dump its content
Once you have found the id of the label you want (example, in my case, I have 619327313 as the priority inbox id), you have to use the message_labels table to link each message to its label. And, finally, lookup in the messages table the latest (biggest id) message with the labelID.
Tentatively:
sqlite3 path-to-mailstore.db "select fromAddress from messages where messageId (SELECT message_messageId FROM message_labels WHERE labels_id 619327313) ORDER BY _id DESC LIMIT 1"
Does that work ?
BossMan
Feb 2, 2012, 1:11:38 PM2/2/12
to tas...@googlegroups.com
Try this:
select fromAddress,subject from messages,message_labels,labels where labels.name = 'HERE PUT YOUR LABEL NAME' and message_labels.labels_id = labels._id and messages.messageId = message_labels.message_messageId order by messages._id desc limit 1;
BR,
A.
A.
Amer
Feb 2, 2012, 8:24:23 PM2/2/12
to tas...@googlegroups.com
I'm playing with sql in my daily life but sqllite3 and linux command is new things to me.
your explanation give me another chance of exploring this much further. Thx mate! :)
your explanation give me another chance of exploring this much further. Thx mate! :)
Amer
Feb 2, 2012, 8:28:58 PM2/2/12
to tas...@googlegroups.com
wow! straight to the point solution! will try this now for sure :)
thx again for all the replies, and thx pent for a great app :)
thx again for all the replies, and thx pent for a great app :)
Amer
Feb 3, 2012, 2:17:26 AM2/3/12
to tas...@googlegroups.com
Well, I manage to get some profiles working. And just to finish it, I need to extract the _id from table messages.
I can modified the SQL script but to put them into the command is not easy for me because I don't really understand, for example, what the "| grep -o '"[^"]*"' | tr -d \" command mean. :(
all I know that, the command retrieve 4 data through 4 SQL script, 1. name, 2. email, 3. subject & 4.*
so my question is, how to modified the command so that it include the "extraction" of the _id from the email? my purpose is to identified whether its the same email or the new one base on the _id? I will only use if the _id is not the same as the last.
the SQL script ""ORDER BY _id DESC LIMIT 1" will give you the latest email, but if I have several labels to extract with, I have to identify every _id so that it only detect new email for each label.
any idea would be very appreciated :)
I can modified the SQL script but to put them into the command is not easy for me because I don't really understand, for example, what the "| grep -o '"[^"]*"' | tr -d \" command mean. :(
all I know that, the command retrieve 4 data through 4 SQL script, 1. name, 2. email, 3. subject & 4.*
so my question is, how to modified the command so that it include the "extraction" of the _id from the email? my purpose is to identified whether its the same email or the new one base on the _id? I will only use if the _id is not the same as the last.
the SQL script ""ORDER BY _id DESC LIMIT 1" will give you the latest email, but if I have several labels to extract with, I have to identify every _id so that it only detect new email for each label.
any idea would be very appreciated :)
Amer
Feb 5, 2012, 8:22:09 PM2/5/12
to tas...@googlegroups.com
up up :)
so my question is, how to modified the command so that it include the "extraction" of the _id from the email? my purpose is to identified whether its the same email or the new one base on the _id? I will only use if the _id is not the same as the last.
so my question is, how to modified the command so that it include the "extraction" of the _id from the email? my purpose is to identified whether its the same email or the new one base on the _id? I will only use if the _id is not the same as the last.
Amer
Feb 5, 2012, 11:55:57 PM2/5/12
to tas...@googlegroups.com
nevermind.
manage to accomplished it via trial and error. :)
not perfect but will do just find :)
manage to accomplished it via trial and error. :)
not perfect but will do just find :)
Dennis
Feb 5, 2012, 11:40:31 PM2/5/12
to Tasker
Not anything big; works only if you sync only one account (didn't need
more) but if that's true for you as well, you won't have to change the
script. Pulled this together with what I found on this board. Thanks
to everyone and thanks to Pent for Tasker! Love This App, won't buy a
phone without this LOT of possibilities ever again!
Mind the funny formatting of this board!
#! /system/bin/sh
yourmail=`sqlite3 /data/data/com.google.android.gsf/databases/talk.db
"select * from accounts;" | cut -f2 -d'|'`
dir="/data/data/com.google.android.gm/databases/"
case "$1" in
name| -name ) sqlite3 ${dir}mailstore.${yourmail}.db "select
fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o
'"[^"]*"' | tr -d \";;
email| -email ) sqlite3 ${dir}mailstore.${yourmail}.db "select
fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\>
| tr -d '<>';;
subject| -subject ) sqlite3 ${dir}mailstore.${yourmail}.db "select
subject from messages ORDER BY _id DESC LIMIT 1";;
snippet| -snippet ) sqlite3 ${dir}mailstore.${yourmail}.db "select
snippet from messages ORDER BY _id DESC LIMIT 1";;
body| -body ) sqlite3 ${dir}mailstore.${yourmail}.db "select body from
messages ORDER BY _id DESC LIMIT 1";;
help| -help| --help| -h| --h| -H| --H ) echo "Type command name for
last mailers name, email for last mailers adress, subject for last
mails subject, snippet for a snippet, body for the mail body and help
for this strange line of what I hope might be a little help, all or
gibberish for name and subject";;
all| allish| * ) sqlite3 ${dir}mailstore.${yourmail}.db "select * from
messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;
esac
Anyone got experience with change file btw?
Didn't check that one so far!
Completely off topic: managed to figure out how to pull messages from
Whats App.
sqlite3 /data/data/com.whatsapp/databases/msgstore.db "select data
from messages WHERE key_from_me='0' ORDER BY _id DESC LIMIT 1;"
Would like to be able to telnet my phone to send stuff; anyone figured
out how to send anything?Just don't like that keyboard while I'm on a
PC.
On Feb 6, 2:22 am, Amer <moamer.khad...@gmail.com> wrote:
> up up :)
>
> so my question is, how to modified the command so that it include the
> "extraction" of the _id from the email? my purpose is to identified whether
> its the same email or the new one base on the _id? I will only use if the
> _id is not the same as the last.
>
>
>
>
>
>
>
>
>
> On Fri, Feb 3, 2012 at 2:17 PM, Amer <moamer.khad...@gmail.com> wrote:
> > Well, I manage to get some profiles working. And just to finish it, I
> > need to extract the *_id* from table messages.
more) but if that's true for you as well, you won't have to change the
script. Pulled this together with what I found on this board. Thanks
to everyone and thanks to Pent for Tasker! Love This App, won't buy a
phone without this LOT of possibilities ever again!
Mind the funny formatting of this board!
#! /system/bin/sh
yourmail=`sqlite3 /data/data/com.google.android.gsf/databases/talk.db
"select * from accounts;" | cut -f2 -d'|'`
dir="/data/data/com.google.android.gm/databases/"
case "$1" in
name| -name ) sqlite3 ${dir}mailstore.${yourmail}.db "select
fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o
'"[^"]*"' | tr -d \";;
email| -email ) sqlite3 ${dir}mailstore.${yourmail}.db "select
fromAddress from messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\>
| tr -d '<>';;
subject| -subject ) sqlite3 ${dir}mailstore.${yourmail}.db "select
subject from messages ORDER BY _id DESC LIMIT 1";;
snippet| -snippet ) sqlite3 ${dir}mailstore.${yourmail}.db "select
snippet from messages ORDER BY _id DESC LIMIT 1";;
body| -body ) sqlite3 ${dir}mailstore.${yourmail}.db "select body from
messages ORDER BY _id DESC LIMIT 1";;
help| -help| --help| -h| --h| -H| --H ) echo "Type command name for
last mailers name, email for last mailers adress, subject for last
mails subject, snippet for a snippet, body for the mail body and help
for this strange line of what I hope might be a little help, all or
gibberish for name and subject";;
all| allish| * ) sqlite3 ${dir}mailstore.${yourmail}.db "select * from
messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;
esac
Anyone got experience with change file btw?
Didn't check that one so far!
Completely off topic: managed to figure out how to pull messages from
Whats App.
sqlite3 /data/data/com.whatsapp/databases/msgstore.db "select data
from messages WHERE key_from_me='0' ORDER BY _id DESC LIMIT 1;"
Would like to be able to telnet my phone to send stuff; anyone figured
out how to send anything?Just don't like that keyboard while I'm on a
PC.
On Feb 6, 2:22 am, Amer <moamer.khad...@gmail.com> wrote:
> up up :)
>
> so my question is, how to modified the command so that it include the
> "extraction" of the _id from the email? my purpose is to identified whether
> its the same email or the new one base on the _id? I will only use if the
> _id is not the same as the last.
>
>
>
>
>
>
>
>
>
> On Fri, Feb 3, 2012 at 2:17 PM, Amer <moamer.khad...@gmail.com> wrote:
> > Well, I manage to get some profiles working. And just to finish it, I
>
> > I can modified the SQL script but to put them into the command is not easy
> > for me because I don't really understand, for example, what the "| grep -o
> > '"[^"]*"' | tr -d \" command mean. :(
>
> > all I know that, the command retrieve 4 data through 4 SQL script, 1.
> > name, 2. email, 3. subject & 4.*
>
> > so my question is, how to modified the command so that it include the
> > "extraction" of the *_id* from the email? my purpose is to identified
> > I can modified the SQL script but to put them into the command is not easy
> > for me because I don't really understand, for example, what the "| grep -o
> > '"[^"]*"' | tr -d \" command mean. :(
>
> > all I know that, the command retrieve 4 data through 4 SQL script, 1.
> > name, 2. email, 3. subject & 4.*
>
> > so my question is, how to modified the command so that it include the
> > whether its the same email or the new one base on the _id? I will only use
> > if the _id is not the same as the last.
>
> > the SQL script ""ORDER BY _id DESC LIMIT 1" will give you the latest
> > email, but if I have several labels to extract with, I have to identify
> > every _id so that it only detect new email for each label.
>
> > any idea would be very appreciated :)
>
> > if the _id is not the same as the last.
>
> > the SQL script ""ORDER BY _id DESC LIMIT 1" will give you the latest
> > email, but if I have several labels to extract with, I have to identify
> > every _id so that it only detect new email for each label.
>
> > any idea would be very appreciated :)
>
> > On Fri, Feb 3, 2012 at 8:28 AM, Amer <moamer.khad...@gmail.com> wrote:
>
> >> wow! straight to the point solution! will try this now for sure :)
>
> >> thx again for all the replies, and thx pent for a great app :)
>
>
> >> wow! straight to the point solution! will try this now for sure :)
>
> >> thx again for all the replies, and thx pent for a great app :)
>
Amer
Feb 6, 2012, 10:06:06 PM2/6/12
to tas...@googlegroups.com
thx for the reply, the whole regex thing is new to me so its kinda frustrating trying to modified it.
I managed to come up an idea with the last command I have by adding these lines into the command
id)
sqlite3 ${dir}mailstore.${yourmail}.db "select * from messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $1,$1}';;
if the _id is 123 then the ouput will be:
123 123
I only need 1 number but this result will do just fine. :)
anyway, the more I test the profile the more I realize that it need more power than my sleep profile have. when display off, I set the CPU to smartass max 268800 and it not enough to run this profile. I'm guessing that the profile need more power to run the sqllite. but when the display is on and the CPU will reach normal, the profile work just fine. :)
I managed to come up an idea with the last command I have by adding these lines into the command
id)
sqlite3 ${dir}mailstore.${yourmail}.db "select * from messages ORDER BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $1,$1}';;
if the _id is 123 then the ouput will be:
123 123
I only need 1 number but this result will do just fine. :)
anyway, the more I test the profile the more I realize that it need more power than my sleep profile have. when display off, I set the CPU to smartass max 268800 and it not enough to run this profile. I'm guessing that the profile need more power to run the sqllite. but when the display is on and the CPU will reach normal, the profile work just fine. :)
fubaya
Feb 6, 2012, 11:10:52 PM2/6/12
to Tasker
I'm not entirely sure this is what you want but try this. BossMan's
post (nice work which gave me a headache just trying to read it) will
give you the name, address and subject of the last email that was
received from the label you choose. Since you already have the first
script working, try this one now which adds the method from his post.
Just change YOURLABEL to the label you want to use and YOURNAME to
your gmail name. Make sure the quotation marks around your label stay
the same as I have them here.
#! /system/bin/sh
label="'YOURLABEL'"
yourmail="YOUR...@gmail.com"
name)
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select subject from
messages,message_labels,labels where labels.name = "$label" and
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select fromAddress,subject from
messages,message_labels,labels where labels.name = "$label" and
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select subject from
messages,message_labels,labels where labels.name = "$label" and
labelall)
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select fromAddress,subject from
messages,message_labels,labels where labels.name = "$label" and
esac
Now if you run it with "sh /sdcard/mailsender lname" it will output
the name of the person who sent the new email for the label you chose,
"lemail" will output their email address, "lsubject" will output the
subject of the email and "labelall" will output all three. Same output
as before but now it only checks the label you chose instead of all
mail.
post (nice work which gave me a headache just trying to read it) will
give you the name, address and subject of the last email that was
received from the label you choose. Since you already have the first
script working, try this one now which adds the method from his post.
Just change YOURLABEL to the label you want to use and YOURNAME to
your gmail name. Make sure the quotation marks around your label stay
the same as I have them here.
#! /system/bin/sh
label="'YOURLABEL'"
yourmail="YOUR...@gmail.com"
name)
sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from
messages ORDER BY _id DESC LIMIT 1" | grep -o '"[^"]*"' | tr -d \";;
email)
messages ORDER BY _id DESC LIMIT 1" | grep -o '"[^"]*"' | tr -d \";;
sqlite3 ${dir}mailstore.${yourmail}.db "select fromAddress from
messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\> | tr -d '<>';;
subject)
messages ORDER BY _id DESC LIMIT 1" | grep -o \<.*\> | tr -d '<>';;
sqlite3 ${dir}mailstore.${yourmail}.db "select subject from messages
ORDER BY _id DESC LIMIT 1";;
*)
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}';;
lname)
BY _id DESC LIMIT 1" | awk '/@/{FS="|";print $4,$11}';;
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select subject from
messages,message_labels,labels where labels.name = "$label" and
message_labels.labels_id = labels._id and messages.messageId =
message_labels.message_messageId order by messages._id desc limit 1" |
message_labels.message_messageId order by messages._id desc limit 1" |
grep -o '"[^"]*"' | tr -d \";;
lemail)
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select fromAddress,subject from
messages,message_labels,labels where labels.name = "$label" and
message_labels.labels_id = labels._id and messages.messageId =
message_labels.message_messageId order by messages._id desc limit 1" |
message_labels.message_messageId order by messages._id desc limit 1" |
grep -o \<.*\> | tr -d '<>';;
lsubject)
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select subject from
messages,message_labels,labels where labels.name = "$label" and
message_labels.labels_id = labels._id and messages.messageId =
message_labels.message_messageId order by messages._id desc limit 1";;
labelall)
sqlite3 -separator " " /data/data/com.google.android.gm/databases/
mailstore.YOURADDRESS.db sqlite3 "select fromAddress,subject from
messages,message_labels,labels where labels.name = "$label" and
message_labels.labels_id = labels._id and messages.messageId =
message_labels.message_messageId order by messages._id desc limit 1"
;;
message_labels.message_messageId order by messages._id desc limit 1"
esac
Now if you run it with "sh /sdcard/mailsender lname" it will output
the name of the person who sent the new email for the label you chose,
"lemail" will output their email address, "lsubject" will output the
subject of the email and "labelall" will output all three. Same output
as before but now it only checks the label you chose instead of all
mail.
fubaya
Feb 6, 2012, 11:18:21 PM2/6/12
to Tasker
hang on, I posted that too hastily plus it got formatted wrong. Let me
find a better way..
find a better way..
fubaya
Feb 6, 2012, 11:27:53 PM2/6/12
to Tasker
Amer
Feb 6, 2012, 11:50:47 PM2/6/12
to tas...@googlegroups.com
i got your attention at last! :) ok, I'll try your script right away, but can you add another command in order to get the _id ?
here's why I need it.
I trigger the profile with the notification from gmail app. and there's no way to know from which label the notification are came from with the notification context, so if I have two labels and both are having new emails then the scripts will run two times.
without knowing the _id, whatever action I have will be inaccurate.
for example, If I have "tasker" & "android" labels.
1. there's new email in "tasker" & "android"
2. the script will generate output both for "tasker" and "android"
3. say "new email in tasker"
4. say "new email in android"
5. there's new email in "tasker" only
6. the script will generate output both for "tasker" and "android"
7. say "new email in tasker" (this is true)
8. say "new email in android" (this is not true it only repeat the last email)
If I have the _id, then I can check whether it's the same email or not. I can accomplish this by validating the email address but _id will give more accurate result.
plz advice... :)
here's why I need it.
I trigger the profile with the notification from gmail app. and there's no way to know from which label the notification are came from with the notification context, so if I have two labels and both are having new emails then the scripts will run two times.
without knowing the _id, whatever action I have will be inaccurate.
for example, If I have "tasker" & "android" labels.
1. there's new email in "tasker" & "android"
2. the script will generate output both for "tasker" and "android"
3. say "new email in tasker"
4. say "new email in android"
5. there's new email in "tasker" only
6. the script will generate output both for "tasker" and "android"
7. say "new email in tasker" (this is true)
8. say "new email in android" (this is not true it only repeat the last email)
If I have the _id, then I can check whether it's the same email or not. I can accomplish this by validating the email address but _id will give more accurate result.
plz advice... :)
On Tue, Feb 7, 2012 at 11:27 AM, fubaya <jdm...@gmail.com> wrote:
Ok, I temporarily put it here:
http://a-more-common-hades.blogspot.com/2011/02/temp-post.html
fubaya
Feb 7, 2012, 1:22:00 AM2/7/12
to Tasker
I actually thought the other guys were better suited to answer it. I
just made it more user friendly.
> can you add another command in order to get the *_id* ?*
I see what you need now. In some tables, the _id is something useless,
but I see that it is useful in others. I don't know how to do it at
the moment but I did just realize something, unread messages have the
label id of -505,4,-506 (but 4 seems kinda useless) in the
conversations table, in addition to other label ids. Once you read the
message, the -505,4,-506 changes to -65533 and all read messages have
the -65533 tag.
I don't even fully understand BossMan's command yet, but the two
labels "tasker" and "android" would be something like 28 and 31 on my
phone, so it should be possible to get the messageId of anything
matching 28/31 and 505 or 506 (or not 65533) from conversations and
use that to grab the sender info from messages.
Can anyone with an unread email check the conversations table to see
if the tags contain -505/-506, then read the message and see if those
disappear and -65533 appears?
You can run this command with an unread message, then again after
reading it:
sqlite3 /data/data/com.google.android.gm/databases/
mailstore.YOURMAIL.db "select labelIds from conversations"
Then, can anyone figure out the sqlite command to take advantage of
this? lol
just made it more user friendly.
> can you add another command in order to get the *_id* ?*
I see what you need now. In some tables, the _id is something useless,
but I see that it is useful in others. I don't know how to do it at
the moment but I did just realize something, unread messages have the
label id of -505,4,-506 (but 4 seems kinda useless) in the
conversations table, in addition to other label ids. Once you read the
message, the -505,4,-506 changes to -65533 and all read messages have
the -65533 tag.
I don't even fully understand BossMan's command yet, but the two
labels "tasker" and "android" would be something like 28 and 31 on my
phone, so it should be possible to get the messageId of anything
matching 28/31 and 505 or 506 (or not 65533) from conversations and
use that to grab the sender info from messages.
Can anyone with an unread email check the conversations table to see
if the tags contain -505/-506, then read the message and see if those
disappear and -65533 appears?
You can run this command with an unread message, then again after
reading it:
sqlite3 /data/data/com.google.android.gm/databases/
mailstore.YOURMAIL.db "select labelIds from conversations"
Then, can anyone figure out the sqlite command to take advantage of
this? lol
Amer
Feb 7, 2012, 3:17:44 AM2/7/12
to tas...@googlegroups.com
On Tue, Feb 7, 2012 at 1:22 PM, fubaya <jdm...@gmail.com> wrote:
I actually thought the other guys were better suited to answer it. I
just made it more user friendly.
any help would be appreciated, especially when dealing with something as complex as this :)
> can you add another command in order to get the *_id* ?*
I see what you need now. In some tables, the _id is something useless,
but I see that it is useful in others. I don't know how to do it at
the moment but I did just realize something, unread messages have the
label id of -505,4,-506 (but 4 seems kinda useless) in the
conversations table, in addition to other label ids. Once you read the
message, the -505,4,-506 changes to -65533 and all read messages have
the -65533 tag.
I don't even fully understand BossMan's command yet, but the two
labels "tasker" and "android" would be something like 28 and 31 on my
phone, so it should be possible to get the messageId of anything
matching 28/31 and 505 or 506 (or not 65533) from conversations and
use that to grab the sender info from messages.
Can anyone with an unread email check the conversations table to see
if the tags contain -505/-506, then read the message and see if those
disappear and -65533 appears?
You can run this command with an unread message, then again after
reading it:
sqlite3 /data/data/com.google.android.gm/databases/
mailstore.YOURMAIL.db "select labelIds from conversations"
Then, can anyone figure out the sqlite command to take advantage of
this? lol
I see the conversations table using sqllite editor app, I tried to send my self several email but there's no different in labelids :(
maybe it has to be more than 1 email?
Amer
Feb 7, 2012, 7:57:02 AM2/7/12
to tas...@googlegroups.com
@fubaya: ok, I tried your script and it returned EOF.
for meantime I stick to the old command ;)
for meantime I stick to the old command ;)
Message has been deleted
fubaya
Feb 7, 2012, 11:36:25 PM2/7/12
to Tasker
On Feb 7, 7:57 am, Amer <moamer.khad...@gmail.com> wrote:
> @fubaya: ok, I tried your script and it returned EOF.
I haven't had time to look at it tonight but did you try it more than
> @fubaya: ok, I tried your script and it returned EOF.
once? Tasker returns EOF every other time even if you have it set to
read a certain line. Also, you may want to put a wait of 1 second in
between the script and reading the file.
I would set up the task something like:
1- run the script
2- wait 1 second
3- read the file to %var
4- go to 3 if %var matches EOF
Amer
Feb 7, 2012, 11:50:38 PM2/7/12
to tas...@googlegroups.com
Actually I wait for two seconds ;)
OK, I'll give another try later on :)
--
[OS] Android Gingerbread 2.3.7
[Phone] HTC Desire S
[ROM] CyanogenMod 7.1
[Kernel] Tiamat Saga 1.1.5+
[Recovery] 4EXTRecovery v2.1.0
Reply all
Reply to author
Forward
0 new messages