[Howto] Getting unread email count from K9

[nanobrain]

If you want to get the unread email count from K9 you first need to locate the K9 database. In my case I set the storage location to External Storage (SD card) under Account Settings > Storage in K9. The database in that case is located in Android/data/com.fsck.k9/files/ and has a long pseudo-random filename ending in .db. Since I am lazy I get the filename with:

List Files [ Dir:Android/data/com.fsck.k9/files/ Match:*db Include Hidden Files:Off Variable:%Filelist ]

 

Since there is only one .db file (at least for me) the filename is in %Filelist1. You can then extract the unread email count with:

 

Run Shell [ Command:sqlite3 %Filelist1 "SELECT unread_count FROM folders WHERE id=1;" Timeout (Seconds):5 Use Root:On Store Result In:%unread ] 

 

with the result stored in the local variable %unread.

 

Hope this helps someone.

[Luis A. Florit]

Unfortunately, this didn't work for me (Rooted ICS galaxy note, tasker beta 4.0.15+):

I have 2 DB files. One of them gives emptystring for unread_count, while the other gives 3. No matter what.

I tried removing " WHERE id=1", but the list of numbers shown is independent of the number of new emails in both files.

Any clue? Did you get an alternative solution to this?

   Thanks!

        L.

[nanobrain]

I don't need an alternate solution, since it still works for me :). My suggestion is to look into the K9 database. I think root explorer can do this, but there is a plethora of apps in the play store that can do it (personally I use SQLite Editor). If you open your K9 database you should see different tables (android_metadata, attachments, folders, headers, messages and pending_commands, YMMV), after opening the folders table search for the id of your INBOX. That's the number you need to use in the " WHERE id=X"
 part of the command. I only have one email account, if you have multiple you might have to activate the universal inbox in K9 or deal with the separate accounts individually.

Good luck

[Luis A. Florit]

This is very strange. I have 3 email accounts, 2 DBs in /sdcard/Android/data/com.fsck.k9/files representing 2 of the accounts, but not the third (that works fine... so where is the DB??). The DB file date/time correspond to now, so these are the ones actually used by k9.

Looking at one of the DBs, the folders table structure is:

CREATE TABLE folders (id INTEGER PRIMARY KEY, name TEXT, last_updated INTEGER, unread_count INTEGER, visible_limit INTEGER, status TEXT, push_state TEXT, last_pushed INTEGER, flagged_count INTEGER default 0, integrate INTEGER, top_group INTEGER, poll_class TEXT, push_class TEXT, display_class TEXT);

So field #4 is the unread_count. ID number 8 is my inbox:

INSERT INTO "folders" VALUES(8,'INBOX',1363448907508,0,50,NULL,'uidNext=355',1354042789581,0,1,1,'INHERITED','NO_CLASS','FIRST_CLASS');

As you can see, the unread_count is 0, despite that there are unread emails according to k9.

I guess K9 is storing stuff in some other place, specially since I don't see my third account. Strange is that k9 updates these DBs too.

Clues?

Thanks!

  L.

[nanobrain]

In the K9 Settings >> Account Settings >> Storage you can specify the storage location (either external or internal location) maybe you are not selecting the same for each of the accounts?  Otherwise I have no idea what's going on.

Sorry 

[Luis A. Florit]

Right, the third account was set differently and now i found it.
Still, all three show 0 for unread_count in all folders. So this doesn't work for me.

Thanks anyway,

L.

nanobrain <nano...@gmail.com> wrote:

>In the K9 Settings >> Account Settings >> Storage you can specify the
>storage location (either external or internal location) maybe you are
>not
>selecting the same for each of the accounts? Otherwise I have no idea
>what's going on.
>
>Sorry
>
>On Saturday, March 16, 2013 12:05:00 PM UTC-4, Luis A. Florit wrote:
>>
>> This is very strange. I have 3 email accounts, 2 DBs in
>> /sdcard/Android/data/com.fsck.k9/files representing 2 of the
>accounts, but
>> not the third (that works fine... so where is the DB??). The DB file
>> date/time correspond to now, so these are the ones actually used by
>k9.
>>
>> Looking at one of the DBs, the folders table structure is:
>>

>> *CREATE TABLE folders (id INTEGER PRIMARY KEY, name TEXT,

>last_updated
>> INTEGER, unread_count INTEGER, visible_limit INTEGER, status TEXT,
>> push_state TEXT, last_pushed INTEGER, flagged_count INTEGER default
>0,
>> integrate INTEGER, top_group INTEGER, poll_class TEXT, push_class
>TEXT,

>> display_class TEXT);*

>>
>> So field #4 is the unread_count. ID number 8 is my inbox:
>>

>> *INSERT INTO "folders"

>>
>VALUES(8,'INBOX',1363448907508,0,50,NULL,'uidNext=355',1354042789581,0,1,1,'INHERITED','NO_CLASS','FIRST_CLASS');

>> *

--
My gallery: http://luis.impa.br/photo

[nanobrain]

So K9 claims you have unread messages and the database claims there are none in your INBOX. So are there any unread messages in folders you created? IIRC the unread count K9 claims in the notification bar is all messages not just the ones in the INBOX (see e.g. https://code.google.com/p/k9mail/issues/detail?id=3666, its an issue from 2011, but it is still marked "new"). If there are any unread messages in your Inbox and the SQL database says otherwise then I am at my wits end

[Luis A. Florit]

nanobrain <nano...@gmail.com> wrote:

>So K9 claims you have unread messages

Actually, I do, since I sent these mails to myself (speaking about loneliness......) and I see them in k9.

> and the database claims there are
>none in your INBOX. So are there any unread messages in folders you
>created? IIRC the unread count K9 claims in the notification bar is all
>
>messages not just the ones in the INBOX (see e.g.
>https://code.google.com/p/k9mail/issues/detail?id=3666, its an issue
>from
>2011, but it is still marked "new"). If there are any unread messages
>in
>your Inbox and the SQL database says otherwise then I am at my wits end

It is not that. I had 5 new emails in my 3 accounts, 1 in one inbox, and 2 in each of the other 2 inbox. Yet, the 3 DBs showed 0 unread_count at the three inbox.

No idea where k9 stores this information, nor why it works for you.

Go figure...

Thanks,

L.

[easiuser]

I was looking for a good way to get this information because trying to keep track of it with an incoming message event was proving unreliable.  I did a little poking around and it appears K9 does store the count in the folders table.  This SQL will give you the count of unread messages for a particular account, regardless of which folder they are in.

SELECT SUM(unread_count) FROM folders

This SQL will give you the unread messages from the account

SELECT * FROM messages WHERE flags NOT LIKE '%SEEN%'

Thanks nanobrain for pointing me in the right direction.

[Luis A. Florit]

Nope, this didn't work for me either. Despite the fact that I send emails to myself for testing, or that I clean all mails

(seen and not seen, Inbox and trash and everywhere), I keep seeing the same numbers.

I see the db files changing timestamp, but not the size. The emails are still there.

Maybe it has to do with the K9 (4.390) version I use?

Or maybe I'm looking at the wrong directory (/sdcard/Android/data/com.fsck.k9/files)?

Strange.

Thanks anyway,

   L.

[easiuser]

If you haven't used a tool like SQLite Debugger, I suggest you do, its free from the play store.  You can see instantly the results from SQL queries.

[Luis A. Florit]

Sorry, but what do you mean?
It's not just a simple command

sqlite3 (db) (command) ?

[easiuser]

Just to verify you have the correct database and SQL statement.  With SQLite debugger or a terminal session it is easier to see the results and browse the records than trying to do so in tasker.  Whatever works for you.

[nanobrain]

UPDATE:

With the update to K9 version 4.4 this is no longer working as the "unread_count" column in the "folders" table is no longer updated. Instead the Run Shell Command needs to be:

sqlite3 %Filelist1 "SELECT count(*) FROM messages WHERE folder_id=1 AND read = 0 AND subject is not null"

folder_id for my inbox is 1 and can be found in the "folders" table. YMMV

[Luis A. Florit]

Finally, something that worked for me. I only had to change 'folder_id=1' by 'folder_id=6'.

Thanks!

   L.