Tasker to mark unwanted or irrelevant unread sms as read and auto delete sms message via sqlite command

[Cathy Lansy]

Tasker to mark unwanted or irrelevant unread sms as read and auto
delete sms message via sqlite command


hi, I have been searching for a solution in the Internet regarding
this issue. I believe sl4a script is able to work, however it is sort
of difficult for me to study on it.

finally I found sqlite command is much better than that.

You see? simply use 'update' command, we may modify the SMS States as
read in sms sqlite database.

-------First of all, you should root your phone first, or else, run
shell action would not work properly.


Sqlite knowledge:

Okay, before we move onto tasker work, i wanna explain some specific
knowledge regarding sqlite for you guys.

For sqlite command, “update” means edit and replace something with
something. There is a fomular like below.

And for some fuzzy match keyword, you will have to use the command
“like” and includes the wildcard “%” right after the keyword.

e.g. update target catalog set column id=’new value’ where column
id=’value’ and column id like ’keyward%’;

KKKKK denotes the particular keyward in the sms message body, remember
there is ; in the end

And according to android sms sqlite database analysis, we will see
“address” denotes incoming/outgoing phone number, and “read” denote
the status of being read or unread. 0 means it is unread, 0 means it
is already read. Body denotes to the sms message body.

So that is why we have to set the script like that. To change the read
status from 0 into 1 within the sqlite database.

-------Most of stock ROM, the sms message database is located in data/
dtata/com.android.providers.telephony/databases/mmssms.db

If you would not find the sms database in that folder, please search
and modify the path accordingly.


------There is a thing, this action only affect the android sms
database, but it doesn’t affect the ongoing notification, so after
action took effect, the unread sms notification alert would not
dismiss from the status bar. When you tap into the sms builtin apps,
you will the sms has already been marked as read.

The solution for this, you may try load app—select sms message, wait 1
or 2 seconds, action-Go home, i guess it would dismiss the unread sms
notification.








Okay, now let us get into the real matter on tasker.


*Auto mark unread specific sms as read*

Step 1: you should make sure sqlite3 system file is installed in your
phone system, or else, the script would not work. You may go search in
the play store to find a software named sqlite installer root
(required root access), which would help you get the proper sqlite
file installed in your system.

------(Tips: if you run the sqlite script and get the error code like
“sqlite file not found” or “bad command” something, that means your
phone system lib doesn’t have the proper sqlite 3 file.)

Step 2: create a foler named “sql” , and then create a txt file in
that foler. Afterwards, copy the script below paste in the txt file.
After done, rename the txt file into unreadsms.sql, which is with .sql
as the postfix.

update sms set read='1' where address='XXXXXXX' and body like 'YYYYYYY
%';

------XXXXXX denotes the receiver phone number which you would like to
mark it as read, YYYYY is the particular keyword which to identify the
specific SMS you would like to make it as read.

Step 3: Well, now you may just set up the action part like below in
tasker or test it via Gscript to see if it works. (you may search and
find out Gscript in play store. A very useful software to run shell
command.)

Action: script-run shell-command: sqlite3 /data/data/
com.android.providers.telephony/databases/mmssms.db < /sdcard/sql/
unreadsms.sql
Check “Use Root”

[Cathy Lansy]

*Auto delete unwanted specific sms *

Step 1: make sure sqlite file is well installed in your phone system.

Step 2: create a foler named “sql” if you don’t have, and then create
a new txt file in that foler. Afterwards, copy the script below paste
in the txt file. After done, rename the txt file into delsms.sql,

which is with .sql as the postfix.

delete from sms where address='XXXX' and body like 'YYYYY%';

(please refer to the above-mentioned sqlite knowledge to see more
details)

Step 3: Well, now you may just set up the action part like below in
tasker or test it via Gscript to see if it works.

Action: script-run shell-command: sqlite3 /data/data/
com.android.providers.telephony/databases/mmssms.db < /sdcard/sql/

delsms.sql
Check “Use Root”

[easiuser]

If it is a short script as shown, there is no need to create the text file.  You can enter it all in the shell command by just placing quotes around the argument. ie:

sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "update sms set read='1' where address='XXXXXXX' and body like 'YYYYYYY%';"

For some reason I could not get it to run with the text file, even when I ran it at a root command prompt in a terminal window.  No error, it just didn't delete the messages.  Putting it quotes like above worked fine.

Thanks for pointing me in the right direction.

[Matt R]

Maybe your system partition isn't mounted as writable? There's a shell command to mount it rw, but I don't know it off the top of my head. Google should be able to find it though.

Matt

[TomL]

It's not on system, /data/data should always be writable.

What version Android OS do you have?  What version sqlite3 do you have?  It could be incompatible versions of sqlite3 database and client.  Not enough to break read access, but enough to break write access.

Tom

[Jenny Pink]

My Android is 4.0.4, Build number 4.1.B.0.587, rooted phone (obviously), Xperia Arc S. I have tried a few kernel copy to flash my phone when rooting, so my final kernal should be either LT18i_4.1.B.0.431_(1254-2184).ftf or LT18i_4.1.B.0.431_ONLY KERNEL.ftf

I used sqlite installer for Root link: sqlite installer for root at google market to install sqlite3 onto my android.

 How do I check the version of my sqlite? The file is located in /system/xbin, I couldn't use the application setting page to view the file version as it is not listed there.

[Jenny Pink]

After some checking, I think my SQLite is 3.7.15.1, downloaded from ptSoft.org

[easiuser]

Have you tried running it from a terminal screen as root?  You may get an error message that is more descriptive.

On Monday, February 25, 2013 9:39:23 AM UTC-6, Jenny Pink wrote:

Hi,

  I can't get sqlite3 delete function to work. 

  To test it I used to following command to run Tasker shell command. I check "use root" and "continue if error" and error pass to %err.

   sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "select * from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

   and Tasker Alert pop to display the return value. I got the selected sms correctly. and there is no error. (error code %err return = 0)

  But when I changed the command line to 

   sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "delete from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

  I got nothing and the tasker shell command return error (error code %err return = 1)

What did I do wrong? I have searched other sqlite3 example, my line seems to be correct.

Thanks

On Wednesday, January 23, 2013 3:18:42 PM UTC+8, Cathy Lansy wrote:

[Jenny Pink]

No, have not tried terminal before. Is it just install something like this to try out? and then sqlite emulation follows format/language like this?

[easiuser]

Correct.

[Cathy Lansy]

Hi,

 

Sorry about my late reply.

 

Regarding your questions, would you mind answering my little questions first?

 

1. did you test exactly the same following my instructions before? i mean make a sql file combining the sqlite3 command. did it work?

 

2. if it worked, that means you have successfully installed the sqlite 3 and it already is working. if it didn't work or you didn't actually try this way yet, never mind, please check in google play store to get a software named "Gscript", after installed it, create a new script within Gscript, copy paste the below script command to it and check to run as Root.

 

sqlite3 /data/data/
com.android.providers.telephony/databases/mmssms.db < /sdcard/sql/
delsms.sql

 

Of course, if you don't feel like to make the sql file, then modify it with your way.

 

by using Gscript, we are able to see if the sqlite 3 command is working or not, if it doesn't work, we will see the error information pop-up. studying on the error information, we will eventually sort this out.

 

 

On Monday, February 25, 2013 11:39:23 PM UTC+8, Jenny Pink wrote:

Hi,

  I can't get sqlite3 delete function to work. 

  To test it I used to following command to run Tasker shell command. I check "use root" and "continue if error" and error pass to %err.

   sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "select * from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

   and Tasker Alert pop to display the return value. I got the selected sms correctly. and there is no error. (error code %err return = 0)

  But when I changed the command line to 

   sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "delete from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

  I got nothing and the tasker shell command return error (error code %err return = 1)

What did I do wrong? I have searched other sqlite3 example, my line seems to be correct.

Thanks

On Wednesday, January 23, 2013 3:18:42 PM UTC+8, Cathy Lansy wrote:

[Jenny Pink]

Hi Cathy,

  I believe your suggestion is same as  easiuser's suggestion. 

I installed Android terminal emulator and run from there. I managed to make some progress:

(1)$ su (to get root access)

(2)# sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db

(3)sqlite3> select * from sms

This command return all my sms to me correctly

(4)sqlite3> delete from sms where address line "1223%"

The error return: sqlite error "no such module: FTS3"

So seems like my sqlite3 do not have FTS3 module. And I am not sure why we don't need this module to read stuffs from sms table (my (3) command runs ok). 

Any idea how can I include this module onto my phone?

Thanks

[Jenny Pink]

Hi Cathy,

  I installed Gscript and repeat the above commands, I got the same error sqlite error "no such module: FTS3"

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/kn9r4I2CPQ8/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[TomL]

fts is the free text search module. It may be easier to find a copy of sqlite3 that already has that module baked in.

Tom

[TomL]

Correction, it's the *full* text search module.  Here's more info about it:

http://www.sqlite.org/fts3.html

You'd have to recompile sqlite3 from source in order to add in the module.

The strange thing is that your SQL statement (delete from blah where blah like 'blah') shouldn't require the use of the full text search mechanisms.  Maybe it's the LIKE term that's triggering the usage of the fts3.

Can you try these sql statements?

sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "select * from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

In the response that you get, do you have a column called "_id"?  If so, then look at the id number (let's say it's 58 ), and run this query:

sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "select * from sms where _id=58 ; "

That should return the same line as the first sql statement you ran.  If so, then try this statement:

sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "delete from sms where _id=58 ; "

If that still doesn't work, you may need to dump the schema of the database for us to look at.  Run this command:

sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db ".schema;"

and paste the output in a reply.

There's going to be a lot of lines of output, so you might instead try this:

sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db ".schema;" > /sdcard/sms_schema.txt

to have it write the schema to a file that you can upload in a reply.

Tom

[pinki...@gmail.com]

Hi TomL,

Thanks for the detailed suggestions.

I tried this:

sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "select * from sms where _id=58 ; "

I can get back my selected sms,

but this:

sqlite3 -header /data/data/com.android.providers.telephony/databases/mmssms.db "delete from sms where _id=58 ; "

resulted the same thing (no such module: FTS3)

I then tried this: 

sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db ".schema;" > /sdcard/sms_schema.txt

It turns out that .schema command cannot be called in this way (not inside "" and with ;), so I just issued .schema at the terminal and copied the whole dump out content to a text file, as attached.

Thanks

--

[TomL]

Your best bet is to find a compiled version of sqlite3 that has the fts3 module included.  You can try emailing the developers of the sqlite3 app you have (ptSoft.org ?) if they would kindly compile such a version of sqlite3 for you.

Other than that, you can try this.  It looks like there's an SQL trigger that fires when you try to delete an sms entry.  The action that gets triggered updates the FTS tables.  What we're gonna try to do it temporarily remove this trigger, try to do your delete of the sms, and then recreate the removed trigger.  Make sure you are comfortable with all the steps before you start.  

1. Start a shell session as a root user. Make a backup of your sms database file:

cd /data/data/com.android.providers.telephony/databases/

cp mmssms.db mmssms.db.bak

2. Create a text file that contains the schema command that was used to create the trigger.  We'll need this file to restore the trigger we're going to remove.

sqlite3 mmssms.db ".schema sms" | grep sms_words_delete > sms_words_delete.txt

Confirm that the sms_words_delete.txt file contains something like:

CREATE TRIGGER sms_words_delete AFTER DELETE ON sms [blah blah blah]; END;

3. Remove that trigger with the command:

sqlite3 mmssms.db "drop trigger sms_words_delete"

4. Now try your delete command.

sqlite3 -header mmssms.db "select * from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

sqlite3 mmssms.db "delete from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

5. Errors?  None?  Good.  Confirm that the entry got deleted.

sqlite3 -header mmssms.db "select * from sms where address='XXXXXXX' and body like 'YYYYYYY%';"

6. Restore the removed trigger.

sqlite3 mmssms.db < sms_words_delete.txt

Try this out.  If this works for you (and I did for me on Android 2.3.4), then we can look into how to streamline all this down into something more compact.

[TomL]

If it works, then you can do future sms deletes from a Tasker RunShell action using this single command:

sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "begin transaction; DROP TRIGGER sms_words_delete; DELETE from sms where address='XXXXXXX' and body like 'YYYYYYY%'; CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; END; commit;"

You'll need to verify that the trigger definition in this command is indeed the trigger defition on your specific device.  Note that this is one long single command, no line breaks.  If you want to break the single command into more readable chunks, you can do it this way:

sqlite3 \

/data/data/com.android.providers.telephony/databases/mmssms.db " \

begin transaction; \

DROP TRIGGER sms_words_delete; \

DELETE from sms where address='XXXXXXX' and body like 'YYYYYYY%'; \

CREATE TRIGGER sms_words_delete AFTER DELETE ON sms \

BEGIN \

DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; \

END; \

commit; "

Note the backslash \ at the end of each line.  After each backslash is a single carriage return.  Note too that this should still be entered as one long shell command, and not separate shell commands.

I hope that helps.

Tom

[pinki...@gmail.com]

This method is painful to type on my phone keyboard, but it works! Thanks a million! So can I delete the sms_words_delete.txt now? Or I have to keep a copy of that for this method to work?

Now my problem left is how to incorporate the tasker variable %SMSRB and %SMSRF into this sqlite sentence. My original plan is, when incoming sms, use tasker pop to display %SMSRT,%SMSRB and %SMSRF, then a delete button will run shell command to sqlite3 delete from sms where address = %SMSRF and body = %SMSRB. Obviously I cannot just type "delete from sms where address = %SMSRF and body = %SMSRB" as % is a special operator in sqlite3. How to insert these tasker variable correctly?

By the way, before sqlite3, I tried to use SL4A and python command to delete sms, something describe here, You need to install SL4A and Pythod for android beforehand. This method works too. Just that I find having Tasker invoking SL4A to do this command is very slow. I prefer using sqlite3 method.

Thanks

[GermainZ]

Actually you can. Tasker will replace all variables before running the shell command.

[pinki...@gmail.com]

GermainZ is right, my final command becomes:

tasker run shell command: sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "begin transaction; DROP TRIGGER sms_words_delete; DELETE from sms where address='%SMSRF' and body ='%SMSRB'; CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; END; commit;"

This works perfectly and much faster than the SL4A way.

Thank you all so much!

[TomL]

No, you do not have to keep the file sms_words_delete.txt around.  We just needed it when we were trying out the technique.  The huge single SQL command has the trigger definition hard coded in it, and is self contained.

One caution for anyone using this trick of dropping a trigger and then recreating the trigger.  In looking at the schema for the sms database, it looks like the words virtual table is a full text search index so that clients can quickly search for all sms entries that have a particular word using full text search table.  The trigger that we're temporarily removing acts to remove index entries for words found in the sms entry being deleted.  

On my stock SMS client, if I start typing into the search field, the app quickly finds entries that has the letters I've typed so far.  These results seem to come from the FTS table.  If I then click Search after I finish typing my word, then the results I get back are from the app actually looking through all SMS entries.  I just tried on my phone these steps to prove this behavior:

Open SMS client, go to the search field and start typing in "cupcake".  Before I even finish typing in all the letters of the word cupcake, two matching SMS entries show up.  After I finish typing cupcake and click on the Search button, the app shows me these two matching SMS entries, let's call them cupcake1, cupcake2.  So far so, good.

I use our technique to delete the cupcake1 sms entry.  Going back into the sms app, I repeat my cupcake search.  As before, as I type in the letters of the word, I see two matching sms entries: cupcake1, cupcake2.  When I click on the search button, only one entry is returned, cupcake2. 

Long story short: if you do a search for sms entries after using this deletion technique, you may see matches for deleted entries.  

Tom

[pinki...@gmail.com]

Just the update of my project. I recently changed the OS of my phone to custom ROM CM10. I did not install back sqlite3 binary (3.7.16) from ptsoft.org. I just try the command without disabling/re-enabling the trigger. It works well, I also notice that the speed of this sms deleting task is faster without disabling/re-enabling the trigger. 

On Fri, Mar 29, 2013 at 12:56 AM, Travis Krumsick <travis....@gmail.com> wrote:

David Marchbanks of ptsoft.org has graciously updated the sqlite3 binary (3.7.16) to include the FTS3 module.    Disabling/re-enabling the trigger should no longer be necessary for us Galaxy S3 users.

[Cathy Lansy]

Hi,

via sqlite 3 command has a flaw, which i have explained very well in my another entry.

you may go take a look. https://groups.google.com/forum/#!searchin/tasker/sl4a$20script$20mark$20sms$20as$20read/tasker/aeamefOvuko/Pqydrksno6oJ

sl4a python script should be a better option for you.

well, you may go visit my personal blog to get more information about tasker profiles.

http://taskerfans.blogspot.com

Thanks!

Cathy

 

 

On Wednesday, January 23, 2013 3:18:42 PM UTC+8, Cathy Lansy wrote:

[Minos]

Sorry for reviving an old post but I was wondering if the new restrictions imposed on google on KitKat has actually made those methods obsolete.I have successfully managed to delete and update sms read status on a Galaxy S2 and S3, running both CM10,stock and  SlimRom. I have just bought a moto G as a secondary phone and I have been unsuccessfully trying to make tasker delete an sms.What I have tried is:

• Use the .sql script and run it as root both from tasker and terminal emulator.
• Used  sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "delete from sms where address='XXXXXXX%' and body like 'YYYYYY%';" as tasker shell command.
• Tried using Cathy's SL4A script.(I do not think they can be run as root).
• I dumped the .schema of the smsmms database, checked for the triggers and issued that command:sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db "begin transaction; DROP TRIGGER sms_words_delete; DELETE from sms where address='XXXXXX' and body like YYYYYY%'; CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM words WHERE source_id = OLD._id AND table_to_use = 1; END; commit;"
• Tried logging into an sql shell and issue the commands to check for errors.Sql does not report any errors and accepts every command but in the end nothing happens.I am using latest version of SQLite installed by sql for root application.

I am attaching the schema just in case I have missed something there.I would appreciate any feedback on weather someone has made this work on kitkat.

[Daniel Mazur]

How have you gotten it to delete the msgs from the galaxy s3?

[James Davis]

I am curious about this as well. I can confirm that something somewhere has changed regarding this topic. I had this setup for a while in an sms popup profile I made with Tasker. I haven't changed my phone or ROM and one day it just no longer works. Does anyone have any info?