[MMS][SMS] Debugging / Optimisation of mmssms.db

---

Hi all,

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)
- Reboot

=> 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 ------

20120227 - Update - 1.1.zip => Seems that id do not works for ICS... wait for clearance

20120307 - 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

20120311 - Mistake, the ICS script was the Gingerbread one and vice versa ... :) - Corrected
- update.ICS - 1.1 created
- update.GingerBread - 1.3 created

20120318 - Added a debug log in GingerBread script
- update.GingerBread - 1.4 created

20120325 - Added sqlite3 for people who need it, put it in /system/xbin/ (Do not forget eXecute rights)

oh my gosh i just tried this and this works like a charm, have a thread with about 8500 sms and opening it is so much faster!
incredible work, thank you very much!

Is it working for all devices ? With all Roms ?

Envoyé depuis mon HTC Sensation XE with Beats Audio avec Tapatalk

Originally Posted by KcDaRookie

oh my gosh i just tried this and this works like a charm, have a thread with about 8500 sms and opening it is so much faster!
incredible work, thank you very much!

You're welcome, happy that it help you like it does for me..

Originally Posted by seandk

Is it working for all devices ? With all Roms ?

Well, i've not done extensive tests... but it should work with all roms/device as the mmssms.db is an "Android" database, its format does not often change.

The best things to do is :
1 - Backup
2 - Test

Hi, would it be possible to scan for duplicate messages and remove them; that would be an additional useful optimisation

Thanks, will give this a go...

as

---

humm.............................

Originally Posted by iandol

Hi, would it be possible to scan for duplicate messages and remove them; that would be an additional useful optimisation

Hi, this should be pretty easy... but what is for you, a "duplicate" ?
It can be a combination of :

- Same text / same date / same time / same recipient / same thread

What kind of problem do you really have ?

$ export PATH=/data/local/bin:$PATH
$ sh /sdcard/Download/mms/update/Update.sh
mmssms Update - 1.1
- mmssms.db found
- patch file found
.. backuping mmssms.db
.. Applying Update
Error: near line 448: no such table: main.wpm
Error: near line 479: no such table: main.wpm
Error: near line 489: no such table: main.wpm
$

something wrong there?

Originally Posted by UnitedOceanic

$ export PATH=/data/local/bin:$PATH
$ sh /sdcard/Download/mms/update/Update.sh
mmssms Update - 1.1
- mmssms.db found
- patch file found
.. backuping mmssms.db
.. Applying Update
Error: near line 448: no such table: main.wpm
Error: near line 479: no such table: main.wpm
Error: near line 489: no such table: main.wpm
$

something wrong there?

same here with ics mms.apk