Getting K9 unread mail count for Zooper Widget from Tasker

I decided to revert back to my stock ROM backup, and OMG the nandroid backup actually worked sort of flawlessly. It was as simple as going to TWRP recovery mode and choosing to restore the backup file I'd made before installing CM 10.2.

I think that my phone might like a clean-up from all the crap, but for now I'm just content with how I don't have to setup everything from scratch.

Anyways, to the topic. I wanted to have an accurate e-mail unread count for my university e-mail, like I have for Gmail. So that the unread count actually reflects the number of unread emails I have (in a specific folder), rather than how many notifications I've gotten and clearing the count by opening the app.

Here, are, my, references. The tools to do this: rooted phone, SQLite, a file explorer (I use MiXplorer), SQLite Debugger, Tasker, Zooper Widget Pro, K9 mail.

Disclaimer: I don't know if the detailed steps work for anything other than my Samsung Galaxy S3 mini, Android version 4.1.2. 

For example folder structuring might be different in other android versions. So that's why it's better to actually explore every step in your phone rather than trying to copy-paste something, also I thought it was interesting to see how the SQLite script/query was built in SQLite Debugger. 

Ok let's get going.

0. Have a rooted phone. 

I don't know how to root phones, sorry. Google "how to root [my phone]", XDA developers probably have all different scenarios covered.

1. Install SQLite on your phone and reboot*

I used SQLite Installer for Root. *I don't know if reboot is necessary at this stage but it's better to do it than not do it.

2. Install SQLite Debugger on your phone and reboot*

Here's the link to SQLite Debugger. It asked me to also install BusyBox so I did (Stericson version). I actually first thought I already had SQLite on my phone from all the stuff I'd done previously, and SQlite debugger didn't complain about me not having it, and Titanium Backup always checks for sqlite support or something like that, so that's why I had no idea why my Zooper Widget's Tasker variable wasn't working! So I actually did step 1 last. No harm done! *I don't know if reboot is necessary at this stage, but I know I had to reboot at one point. 

3. Set your K-9 mail to use external storage

Open your K-9 mail and push menu -> Settings -> Account Settings -> Storage -> External storage (SD card)
Note that it doesn't actually mean your external SD if you have one, but the part of your internal memory that's acting as the sdcard where a user can store their stuff.

4. Find your k-9 mail database (file path)

Now you need to search for your db file. Once you've found it. type it either on paper or on a text file in your computer, somewhere where you can see it outside your phone! (I have a bad memory)

Short answer: for me it was in /storage/sdcard0/Android/data/com.fsck.k0/files/9032etcetc-etc-etc.db 

Long answer: It's inside a com.fsck.k9 folder, and for me it was in com.fsck.k9/files/ where there was 1 folder and 3 files (.nomedia, *.db and *.db-journal).

So open miXplorer and check bookmarks for SD card (mine was /storage/sdcard0).

I think the absolute path may vary, and I had several locations where I found com.fsck.k9, probably from 1) when it was in internal storage and 2) as a result from my switching ROMs.  For example I found it in
/storage/sdcard0/0/Android/data/com.fsck.k0/files/9032etcetc-etc-etc.db             AND
/storage/sdcard0/Android/data/com.fsck.k0/files/9032etcetc-etc-etc.db 

The first one was not the correct one. Send yourself an e-mail and in miXplorer switch the list to "detailed list" and then you can see when the file was last modified. If it wasn't modified a couple of minutes a go (when you sent the email to yourself) then it's not the correct folder. If it was updated when you sent the mail, then you have the correct location.

Note. I tried going to the root folder in miXplorer and searching for com.fsck.k9, but after it was finished it actually only found one of the folders and it was the wrong one, so I think manually searching for it might be better, especially since the search takes a long time at least on my phone.

5. Copy the file path to clipboard

Long press the file in miXplorer and pick the menu from bottom right of the screen and choose properties. Click the path text and it should say "copied to clipboard".

6. Open your SQLite Debugger

Choose to open file (browse sd-card for databases) and navigate to the folder where your .db file is. This is why I typed the path on paper, because I have the memory of a goldfish. It might ask for what you want to do with the file, I don't remember anymore. But if it does, choose SQLite debugger and it opens the file inside the app. 

6.1 Find your folder ID

You should have your db file open on your SQLite Debugger screen. Click on "folders". Then click on the right pointing arrow below your statusbar (next to the "bookmark" looking icon). Now you should have a page open where it says SELECT * FROM folders, and a list of folders in your e-mail. Search for your "INBOX" or whatever folder you want to show the unread count to. I had loads of them, so I had to go to the 2nd page (bottom right curvy arrow) and found a line that said 52 INBOX. So that's my folder_id! I typed that on paper as well, 52.

6.2 Find the correct SQLite command to see your unread mail count

Navigate back to the main page of your .db file (where you first selected "folders"). Now open "messages" instead and then scroll down and click on "read (21, integer, default=0)" and choose 0.  Now it opens a list of unread messages you have in your mailbox (it actually doesn't show all folders, as I redirect some newsletters and ad spam on a certain folder and it doesn't show those).

Click on the column (not column header) that is under FOLDER_ID and  choose ... = X (whatever number there is). Then go to the SQLite script line where it says SELECT * FROM etc and change folder_id = YOURID. So I had to change 32 to 52 and press the right pointing triangle arrow on the top right to update your command.

You now have a list that shows unread mail from the folder you specified, but you also might see entries which are just <NULL> in almost every field. I don't really know where those come from, but you don't want to see those in your unread count. So to prevent that from happening, I chose to click on the SENDER_LIST column (not the header) and choose "is not null". The examples I read had something like "subject is not null", but I'm not sure if that also blocks mails with no subject, so I didn't want to use that. I thought it would be safe to assume that if there's no sender then it's not a valid e-mail. Click on the right pointing triangular arrow again to update the command, and now those <NULL>s should've disappeared.

If you made a mistake, hit "back" or clear the newly added text and update with the arrow.

Now your command line should read something like
SELECT * FROM messages WHERE read = 0 AND folder_id = 52 AND sender_list is not null
and see a list of entries that match that query.

Now all you need to do is to wrap count() around *, so that it reads
SELECT count(*) FROM messages WHERE read = 0 AND folder_id = 52 AND sender_list is not null
update with the arrow and now what you should see is just a number.

Select that command/script/query whatever it is actually called and copy it to clipboard.

Note that I use Samsung Keyboard which allows me to see the clipboard, not just paste the most recently copied item. I'm sure all other keyboards support that as well.

7. Let the Tasker fun begin

7.1 Create the task

Open Tasker and create a new task and add new action Script -> Run Shell
Now type or copy-paste to command:
sqlite3 [paste the file path from clipboard] "[paste the command you copied from SQLite Debugger];"
(note the quotes and semicolon)

So this is my full working command (except for the db file name, as I cba to type that and it will be different for everyone):

sqlite3 /storage/sdcard0/Android/data/com.fsck.k0/files/9032etcetc-etc-etc.db  "SELECT count(*) FROM messages WHERE read = 0 AND folder_id = 52 AND sender_list is not null;" 

Then set Timeout (Seconds) to 5 and Store Output in %K9UNREAD, also you might want to Store Errors In %K9ERROR.

Go back to the task and add a new action: Plugin -> Zooper Widget Pro...
Then in Configuration set
ZW Variable: K9UNREAD (or whatever you want to use in ZW)
ZW Text: %K9UNREAD
press save icon.

Now you have a task with 2 actions: 1. Run Shell, 2. Zooper Widget Pro Variable

Press the "play" icon on the bottom left of the screen to run the task (just in case). You can go check the variable list in Tasker VARS and see if %K9RESULT displays a number (if you have unread mail) and if %K9ERROR says anything. Hopefully it doesn't, but it helped me to realize I don't actually have SQLite installed after I saw that there's an error, and just Googling that error helped me figure out what it was!

7.2  Create the profile
Add a new profile: Event -> File ->  File Modified -> [paste the full file path again] and then assign the task you just created to it.
Note that there's a search icon for looking for the file in Tasker, but it doesn't include the whole path and I'm not sure if that's required (I first had it like that but I changed it to full path when I was trying to see why it doesn't work, and I didn't change it back after actually installing SQLite, but no harm done anyways if the full path is pasted).

8. Create/Edit your Zooper Widget to show unread e-mail count

Now all you need to do is to create a Zooper Widget where you want it and insert a (rich) text module. Go to "edit text manually" and type whatever you want to show there. Use #TK9UNREAD# (or whatever variable you chose to use, just note the "T" that means it's a Tasker variable) where you want to insert unread count.

For example, my unread notifications string looks like this:

$#SMCALLS#>=1?MISSED CALLS\: #SMCALLS#

$$#SUSMS#>0?NEW SMS\: #SUSMS#

$$#TWA#>0?WhatsApp\: #TWA#

$$#TLMSG#>0?LINE MESSAGES\: #TLMSG#

$$#TK9UNREAD#>0?UTU\: #TK9UNREAD#

$$#SUG#>0?Gmailº1\: #SUG#$$#SUG#>0&&#SUG1#>0? | $$#SUG1#>0?Gmailº2\: #SUG1#$

Note that ZW conditionals are in the form of $#VARIABLe#=X?Y$ and I have entered a line break before the last $ for each notification. That's why each line starts with $$. This makes it so that every notification is in it's own row, but there won't be any gaps if I for example have one missed call and one missed LINE message, but no new SMS/WhatsApp messages. #TWA# and #TLMSG# for WhatsApp/LINE notifications are created in Tasker (Tasker variables, which you can see from the #T beginning) and the count goes up when WA/LINE push notifications, and the count is cleared when I open those apps (so no real unread control like for GMail/K9 mail).

9. That's it!

Go back to your home screen and admire your work! It actually updates better than the ZW native #SUG# variables, I might integrate my Gmail accounts to K-9 mail too.