How does the WindowsCE system databases (WinCE DB's for short) store mail-related information?

http://smartphonemag.com/forum/topic.asp?TOPIC_ID=19093

This is another, previously, in public, uncharted (Google for 'pmailFolders') area of the Windows Mobile operating system – so, this is the first time extensive information is published on this subject. The Windows Mobile operating system (WM for short) handles the mail headers in the WinCE DB's and the actual mail bodies in the file system (under \Windows\Messaging) completely independently. (Note that the bodies of POP3/IMAP-downloaded mail include the full RFC 822 (and additional, for example, extended) headers. On the other hand, the mail bodies synchronized by ActiveSync (AS) don't. This is why you can't even see for example the sender/date in mail files that have been synchronized from your desktop Outlook if you peek into these files inside \Windows\Messaging. Otherwise, AS-synchronized and POP3/IMAP-downloaded mail is stored using exactly the same way on the PDA). The fact that Messaging stores information in completely different places may cause problems if somebody/thing (for example, a misbehaving AS) deletes the contents of \Windows\Messaging. Then, as the references in the related WinCE DB remain intact, Messaging will still think the files (the mail bodies) are there. This means if the contents of \Windows\Messaging is deleted, WM will still list their headers in the (Pocket) Inbox (the name of the built-in e-mail application in pre-WM2003SE WM's) /Messaging (the name of the same app in WM2003SE+ WM's; I only use the latter name to refer to the built-in mailer application in WM). The same stands for compatible (for example, WebIS Mail http://www.pocketinformant.com/p_webismail.php ) apps that directly access these databases/files – they use exactly the same databases and behave the same way in this respect. So, if you only use WebIS Mail instead of Messaging, you may still need this information. The case is the same in the opposite direction – the records in the related WinCE DB may be deleted without \Windows\Messaging being accordingly cleaned up. This means a lot of never-to-be-accessed-again mail bodies will remain in \Windows\Messaging, resulting in sluggishness and degraded overall WM performance. Why do you need to know anything about this subject, anyway? There're a lot of cases you will need to dive into this question. For example, the deletion of mail. Why would one delete the contents of \Windows\Messaging, you may ask. For performance reasons, I do recommend this if there're hundreds or even thousands of them in there with a twist – that is, archiving instead of deleting (compressing the mail body files and putting the resulting archive file(s) on a storage card so that it can be accessed any time without any access to your desktop). I may elaborate on the latter subject much more thoroughly some time in the future; for now, please read this thread for my tips on how such a strictly PDA-based archiving algorithm can be written. In this case, the developer must know the connection between the WinCE DB's and the mail files. (If you don't want to archive so that you can access the mail any time, deleting mail from inside Messaging is the way to go. It guarantees everything is kept in synchron.) In addition, ActiveSync often messes up the system while it synchronizes mail, which sometimes even results in Inbox's inability to start. In these cases, you can clean your system without doing a hard reset if you devote some time to analyze the problem and clean up the related databases by hand because, unfortunately, current versions of generic system cleaning tools (not even the almighty SKTools) aren't able to resolve these problems. (See this (alternatives: iPAQ HQ, AximSite, PPC Magazine, FirstLoox, BrightHand) for more info on these tools.) Therefore, you must do the cleaning by hand if you, for example, having problems with, for example, starting Messaging. (Corrupted/desynchronized mail-related WinCE DB's often cause Messaging to be unable to start, as has already been pointed out.) OK, now for the subject. The central WinCE DB that stores references to separate WinCE DB folders is 'pmailFolders'. It's here that you must look up what the name of a specific WinCE DB folder is. (Note that you can cut corners by peeking into the fldr* DB's – sooner or later, you will run into the one that has the mail for the specific POP3 account (or for the ActiveSync-synched mail). If you, however, have several accounts and don't want to do this by hand, knowing the structure of pmailFolders can be very handy.) First, pmailFolders has a string field with ID 12289 (in hexa, it's 0x3001) that contains the pop3 name you've given to Messaging. Once you find it in a record, look up the field of ID 32769 (0x8001) or 32773 (0x8005) in the same record to know its ID. Now, use this value the following way. The record ID you've just looked up is referenced by several records in their field of ID 32773 (0x8005). To decide which of the many records is the right one (the one that, for example, references the Inbox folder of one of your POP3 accounts), you must also check their 12289 (0x3001) field. This field can have the following values: Deleted Items/Drafts/Inbox/IPM.Root/Outbox or Sent Items. Once you've found the record whose value of ID 32773 (0x8005) equals to the above-found ID and has the right folder name in 12289 (0x3001), you must look up the contents of their ID 32771 (0x8003) field. It's a string that has the name of the physical WinCE DB containing the folder contents. To show an example of all this, I show you an example of both the on-PDA hunting with the free Kenny Soft dbView (see this blog entry for more info on this app) and the offline, SKTools (read the above-linked article on my system cleaner app roundup for info on SKTools) backup-based way of looking up the needed info. First, the on-PDA search with Kenny Soft dbView. Go to pmailFolders and scroll to the right until you see the column with the header 0x3001. It's in this column that you must look for the POP3 account name you've entered to Messaging right at starting to create your account. In the following screenshot, I've highlighted the one that has 'winm' (standing for Windows Mobile Technology) in here. click here for the screenshot Now, you must look up the contents of the 32769 (0x8001) or 32773 (0x8005) columns for the same record. As can clearly be seen, it's 7414. Now, you must look up the one and only one record in the same pmailFolders that has the previously-found 7414 in its 32773 (0x8005) field and has the right folder name in field (column) 12289 (0x3001) (I assume we look for all incoming mail here, therefore, we look for 'Inbox' in here). This record can be seen highlighted in the following screenshot (check out the contents of the two above-mentioned 0x8005 and 0x3001 fields): click here for the screenshot Then, you only need to find the contents of the 32771 (0x8003) field. To do this, I had to scroll to the right: click here for the screenshot The value standing here is 'fldr3001a50'. This is what the WinCE DB holding the headers of the Inbox of my 'winm' POP3 has. Congrats, you've found the physical WinCE DB that stores the mail headers using Kenny Soft dbView. Now comes the offline, SKTools backup-based solution, if you prefer it to dbView. Go to Databases, highlight pmailFolders and choose Action/Export: click here for the screenshot Now, grab the \My Documents\db_xml\pmailFolders.sk.xml file, transfer it to the desktop, open it for viewing with for example Total Commander (F3/View) and do the following: 1, look for the POP3 name; in this case, 'winm'. There will be only one occurrence; jot down the record number (the oid attribute) that starts this XML <record> record (7414 in this case). 2, now, look for the same 7414 in the same XML file. It'll be found in several <record> records. Look for the one that has 'Inbox' in it (and not, say, Outbox). Jot down the value of the value of the tag that has the ID attribute 32771 – that is, the physical WinCE DB name. In the following XML snippet, I've emphasized with bold all the four values that you must check: <record oid="7402"> <field id="13834" type="19" htype="CEVT_UI4">0</field> <field id="12289" type="31" htype="CEVT_LPWSTR" size="5" > Inbox</field> <field id="32772" type="19" htype="CEVT_UI4">268435457</field> <field id="32771" type="31" htype="CEVT_LPWSTR" size="11" >fldr3001a50</field> <field id="32769" type="19" htype="CEVT_UI4">7402</field> <field id="3593" type="19" htype="CEVT_UI4">7405</field> <field id="32773" type="19" htype="CEVT_UI4"> 7414</field> <field id="13827" type="19" htype="CEVT_UI4">0</field> </record> Now, independent of whether we've used a SKTools export or Kenny Soft dbView to find it, we have have the physical WinCE DB name. This WinCE DB contains all the mail headers in the given folder. An example (we'll work with the highlighted record further): click here for the screenshot These records contain all the (for normal usage where only the subject/sender/date and some other headers are needed) relevant mail headers of the mail bodies in \Windows\Messaging. Let's scrutinize these records a bit more! Let's start with the field that has the ID 32773 (0x8005). It (with the highlighted record) contains the value 16805078, which is 0x01006cd6 in hexa. Why do we need this particular field? It's pretty simple: it's by the value that we'll be able to find the related mail bodies in the file system. The file names in \Windows\Messaging are composed of two parts. The last eight hexadecimal digits can be ignored. The hexa digits before that, however, are the value given in this very ID 32773 (0x8005) column. Assume we have the following files in \Windows\Messaging: 11/04/2005 08:06 AM 1,866 01006cd681030102.mpb 11/04/2005 03:10 AM 1,707 020064c481030102.mpb 11/04/2005 03:10 AM 1,163 08006cf081030102.mpb 11/04/2005 08:06 AM 1,172 0a006db881030102.mpb 11/04/2005 08:06 AM 1,172 0b006db481030102.mpb and a particular mail record has the above-mentioned 16805078 (0x01006cd6) in the field of ID 32773 (0x8005). The referenced mail body can very easily be found if you look at the above file name list: yes, it's the first file because it starts with the hexa value 01006cd6: 01006cd681030102.mpb Now you know a lot about WinCE mail DB's; programmers, based on the information I've provided, can even write a PDA mail backup/restore application I've outlined above to combat the problem of WinCE's inability to store mail bodies on storage cards.