[MMS][SMS] Debugging / Optimisation of mmssms.db
SMS and MMS are stored in an Sqlite database named mmssms.db, this database contain tables, triggers (34) and some indexes.
I was having "Ghost" threads in my MMS application, threads that can't be removed while they were empty, sometimes dated in 1970... so i dig in the mmssms.db. I found mistakes, bugs, errors in some triggers ... !
Here the result of two days work, this script will update the original build in MMS/SMS database, gaining speed and avoid some bugs.
I've recreated all trigger, corrected some big mistakes and optimise queries for speed. I've also recreated some indexes (added fields).
Some importants queries are now five times faster than before !
=> I've tried it in my phone with no side effect
- Do a titanium backup on "[SMS/MMS/APN] Dialer storage"
- Download the "update.zip" file
- Uncompress it on your sdcard => this will create an "update folder"
- copy your mmssms.db from /data/data/com.android.providers.telephony/databases/ to the "update folder"
- in terminal navigate to the "update folder"
- run the script : "sh update.sh"
=> There are now two files :
- a backup : mmssms.db.bck
- the updated db : mmssms.db
- Copy the mmssms.db back to your /data/data/com.android.providers.telephony/databases/ folder, erasing the old one
- Put the rights back on the mmssms.db (copying to the fat32 sd card should have removed the initial rights)
=> You can now delete "Ghost" threads, they should never come back again... depending if you've got a lot of messages or not you will experience performance gain, faster display .. etc. (got more than 8000 sms/mms)
------ Updates ------
- Update - 1.1.zip => Seems that id do not works for ICS... wait for clearance
- Here are the differences i have detected between Gingerbread / ICS in the mmssms.db
- Bad Queries that where here... are always here...
- Two tables have dissapeared : mychannels and wpm
- Some triggers has been reorganised but not completely
- On 7 indexes ... only 2 remains .. !!!
- update.ICS - 1.0 Added
- update.GingerBread - 1.2 Added (mistake corrected) => can be launched on top of 1.1
- Mistake, the ICS script was the Gingerbread one and vice versa ... :) - Corrected
- update.ICS - 1.1 created
- update.GingerBread - 1.3 created
- Added a debug log in GingerBread script
- update.GingerBread - 1.4 created
- Added sqlite3 for people who need it, put it in /system/xbin/ (Do not forget eXecute rights)