Project

General

Profile

Forums » Replicant development »

[solved] sms database format

Added by Fil Lupin over 9 years ago

Hi, I would like to to import a sms set from an iphone.
I tried using https://github.com/t413/SMS-Tools/ but it seems theses scripts does not work (https://github.com/t413/SMS-Tools/issues/12) and made my SMS app on replicant crashing. I think it could be a problem of db format change (mmssms.db or mmssms.db-journal) and would like to check.
Do you have any pointer with docs on this subject?
Thanks


Replies (21)

RE: sms database format - Added by Paul Kocialkowski over 9 years ago

You can probably write a script to insert the messages to the database. It is in sqlite3 format, so it's possible. I am not really aware of any application that would automate this process.

RE: sms database format - Added by Fil Lupin over 9 years ago

Thank you for the answer. In fact, I think I wrote "format" instead of "database structure". To write a script to do that, I need the internal structure of the database, and possibly the format of mmssms.db-journal file. This mmssms.db structure is the point I'm looking for some info.
Do you know where I can find info on this structure ? I found methods to access to the SMS but not the structure used actually in replicant.

RE: sms database format - Added by Paul Kocialkowski over 9 years ago

Do you know where I can find info on this structure ?

Using sqlite3 as I already mentioned.

RE: sms database format - Added by Fil Lupin over 9 years ago

Using SQLite allow me to write my data in the adapted format, but it seems to me I need to have the tables structure to make them understandable from android when it will try to read them.

For instance, I can write a table named "anything" with rows "a", "b", and "c", while android is looking for rows "_id", "thread_id", "address", "person", and other ones. Then, it seems to me any SQLlite request on this structure (something like "select a,b,c in anything") will be replied there is no row with these names (a,b and c), or did I miss something ?

RE: sms database format - Added by Paul Kocialkowski over 9 years ago

sqlite3 can give you the structure too! I suggest you run sqlite3 mmssms.db .dump and see.

RE: sms database format - Added by Fil Lupin over 9 years ago

Ok, I understand from your proposition that mmssms.db-journal is not mandatory so knowing its structure is not important (it is not readable by sqlite3). I'll try this, thank you

RE: sms database format - Added by Fil Lupin about 9 years ago

I tried several modifications, and it seems the generated mmssms.db has the same structure than the empty db.
However, it seems there is still an issue, since I am notified that "table pdu already exists (code 1)".

I tried to delete this table and to insert again the db, but a similar message appears with another table. And it continues until a message suggest that table sms already exists. But deleting this table will loose all sms for sure, so it is not a solution.

Here is the output I get by adb log, do you have any idea for this ? If not, do you know where I could find any help ?

--------- beginning of /dev/log/main
    D/audio_hw_primary( 1897): select_output_device: AUDIO_DEVICE_OUT_SPEAKER
    --------- beginning of /dev/log/system
    I/ActivityManager( 2248): START u0 {act=android.intent.action.MAIN cat=[android.intent.category.LAUNCHER] flg=0x10200000 cmp=com.android.mms/.ui.ConversationList bnds=[424,1154][552,1282]} from pid 2492
    W/WindowManager( 2248): Failure taking screenshot for (328x583) to layer 21010
    I/ActivityManager( 2248): Start proc com.android.mms for activity com.android.mms/.ui.ConversationList: pid=4039 uid=10024 gids={50024, 3003, 1015, 1028}
    W/Trace ( 4039): error opening trace file: No such file or directory (2)
    D/CountryDetector( 2248): The first listener is added
    V/MmsConfig( 4039): mnc/mcc:
    V/MmsConfig( 4039): tag: bool value: enabledMMS - true
    V/MmsConfig( 4039): tag: int value: maxMessageSize - 307200
    V/MmsConfig( 4039): tag: int value: maxImageHeight - 480
    V/MmsConfig( 4039): tag: int value: maxImageWidth - 640
    V/MmsConfig( 4039): tag: int value: defaultSMSMessagesPerThread - 500
    V/MmsConfig( 4039): tag: int value: defaultMMSMessagesPerThread - 50
    V/MmsConfig( 4039): tag: int value: minMessageCountPerThread - 10
    V/MmsConfig( 4039): tag: int value: maxMessageCountPerThread - 5000
    V/MmsConfig( 4039): tag: string value: uaProfUrl - http://www.google.com/oha/rdf/ua-profile-kila.xml
    V/MmsConfig( 4039): tag: int value: recipientLimit - -1
    V/MmsConfig( 4039): tag: bool value: enableMultipartSMS - true
    V/MmsConfig( 4039): tag: bool value: enableSplitSMS - false
    V/MmsConfig( 4039): tag: int value: smsToMmsTextThreshold - -1
    V/MmsConfig( 4039): tag: bool value: enableSlideDuration - true
    V/MmsConfig( 4039): tag: int value: maxMessageTextSize - -1
    D/Mms:app ( 4039): cancelNotification
    E/SQLiteLog( 2431): (1) table sms already exists
    E/DatabaseUtils( 2431): Writing exception to parcel
    E/DatabaseUtils( 2431): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.createSmsTables(MmsSmsDatabaseHelper.java:819)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.onCreate(MmsSmsDatabaseHelper.java:447)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.queryInternal(MmsSmsProvider.java:317)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.query(MmsSmsProvider.java:297)
    E/DatabaseUtils( 2431): at android.content.ContentProvider.query(ContentProvider.java:652)
    E/DatabaseUtils( 2431): at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
    E/DatabaseUtils( 2431): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113)
    E/DatabaseUtils( 2431): at android.os.Binder.execTransact(Binder.java:351)
    E/DatabaseUtils( 2431): at dalvik.system.NativeStart.run(Native Method)
    E/SqliteWrapper( 4039): Catch a SQLiteException when query:
    E/SqliteWrapper( 4039): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/SqliteWrapper( 4039): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:181)
    E/SqliteWrapper( 4039): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:137)
    E/SqliteWrapper( 4039): at android.content.ContentProviderProxy.query(ContentProviderNative.java:366)
    E/SqliteWrapper( 4039): at android.content.ContentResolver.query(ContentResolver.java:372)
    E/SqliteWrapper( 4039): at android.content.ContentResolver.query(ContentResolver.java:315)
    E/SqliteWrapper( 4039): at android.database.sqlite.SqliteWrapper.query(SqliteWrapper.java:59)
    E/SqliteWrapper( 4039): at com.android.mms.data.RecipientIdCache.fill(RecipientIdCache.java:73)
    E/SqliteWrapper( 4039): at com.android.mms.data.RecipientIdCache$1.run(RecipientIdCache.java:57)
    E/SqliteWrapper( 4039): at java.lang.Thread.run(Thread.java:856)
    W/dalvikvm( 4039): threadid=14: thread exiting with uncaught exception (group=0x41e2d930)
    E/AndroidRuntime( 4039): FATAL EXCEPTION: RecipientIdCache.init
    E/AndroidRuntime( 4039): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/AndroidRuntime( 4039): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:181)
    E/AndroidRuntime( 4039): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:137)
    E/AndroidRuntime( 4039): at android.content.ContentProviderProxy.query(ContentProviderNative.java:366)
    E/AndroidRuntime( 4039): at android.content.ContentResolver.query(ContentResolver.java:372)
    E/AndroidRuntime( 4039): at android.content.ContentResolver.query(ContentResolver.java:315)
    E/AndroidRuntime( 4039): at android.database.sqlite.SqliteWrapper.query(SqliteWrapper.java:59)
    E/AndroidRuntime( 4039): at com.android.mms.data.RecipientIdCache.fill(RecipientIdCache.java:73)
    E/AndroidRuntime( 4039): at com.android.mms.data.RecipientIdCache$1.run(RecipientIdCache.java:57)
    E/AndroidRuntime( 4039): at java.lang.Thread.run(Thread.java:856)
    W/ActivityManager( 2248): Force finishing activity com.android.mms/.ui.ConversationList
    W/WindowManager( 2248): Failure taking screenshot for (328x583) to layer 21020
    E/SQLiteLog( 2431): (1) table sms already exists
    E/DatabaseUtils( 2431): Writing exception to parcel
    E/DatabaseUtils( 2431): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.createSmsTables(MmsSmsDatabaseHelper.java:819)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.onCreate(MmsSmsDatabaseHelper.java:447)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.queryInternal(MmsSmsProvider.java:317)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.query(MmsSmsProvider.java:297)
    E/DatabaseUtils( 2431): at android.content.ContentProvider.query(ContentProvider.java:652)
    E/DatabaseUtils( 2431): at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
    E/DatabaseUtils( 2431): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113)
    E/DatabaseUtils( 2431): at android.os.Binder.execTransact(Binder.java:351)
    E/DatabaseUtils( 2431): at dalvik.system.NativeStart.run(Native Method)
    W/dalvikvm( 4039): threadid=16: thread exiting with uncaught exception (group=0x41e2d930)
    I/Process ( 4039): Sending signal. PID: 4039 SIG: 9
    D/CountryDetector( 2248): No listener is left
    I/ActivityManager( 2248): Process com.android.mms (pid 4039) has died.
    W/InputDispatcher( 2248): channel '421ed678 com.android.mms/com.android.mms.ui.ConversationList (server)' ~ Consumer closed input channel or an error occurred. events=0x9
    E/InputDispatcher( 2248): channel '421ed678 com.android.mms/com.android.mms.ui.ConversationList (server)' ~ Channel is unrecoverably broken and will be disposed!
    E/SQLiteLog( 2431): (1) table sms already exists
    E/DatabaseUtils( 2431): Writing exception to parcel
    E/DatabaseUtils( 2431): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.createSmsTables(MmsSmsDatabaseHelper.java:819)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.onCreate(MmsSmsDatabaseHelper.java:447)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.queryInternal(MmsSmsProvider.java:317)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.query(MmsSmsProvider.java:297)
    E/DatabaseUtils( 2431): at android.content.ContentProvider.query(ContentProvider.java:652)
    E/DatabaseUtils( 2431): at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
    E/DatabaseUtils( 2431): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113)
    E/DatabaseUtils( 2431): at android.os.Binder.execTransact(Binder.java:351)
    E/DatabaseUtils( 2431): at dalvik.system.NativeStart.run(Native Method)
    E/SQLiteLog( 2431): (1) table sms already exists
    E/DatabaseUtils( 2431): Writing exception to parcel
    E/DatabaseUtils( 2431): android.database.sqlite.SQLiteException: table sms already exists (code 1): , while compiling: CREATE TABLE sms (_id INTEGER PRIMARY KEY,thread_id INTEGER,address TEXT,person INTEGER,date INTEGER,date_sent INTEGER DEFAULT 0,protocol INTEGER,read INTEGER DEFAULT 0,status INTEGER DEFAULT -1,type INTEGER,reply_path_present INTEGER,subject TEXT,body TEXT,service_center TEXT,locked INTEGER DEFAULT 0,error_code INTEGER DEFAULT 0,seen INTEGER DEFAULT 0);
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.createSmsTables(MmsSmsDatabaseHelper.java:819)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsDatabaseHelper.onCreate(MmsSmsDatabaseHelper.java:447)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
    E/DatabaseUtils( 2431): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.queryInternal(MmsSmsProvider.java:317)
    E/DatabaseUtils( 2431): at com.android.providers.telephony.MmsSmsProvider.query(MmsSmsProvider.java:297)
    E/DatabaseUtils( 2431): at android.content.ContentProvider.query(ContentProvider.java:652)
    E/DatabaseUtils( 2431): at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
    E/DatabaseUtils( 2431): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113)
    E/DatabaseUtils( 2431): at android.os.Binder.execTransact(Binder.java:351)
    E/DatabaseUtils( 2431): at dalvik.system.NativeStart.run(Native Method)
    W/InputDispatcher( 2248): Attempted to unregister already unregistered input channel '421ed678 com.android.mms/com.android.mms.ui.ConversationList (server)'
    I/WindowState( 2248): WIN DEATH: Window{421ed678 u0 com.android.mms/com.android.mms.ui.ConversationList}
    I/ActivityManager( 2248): Displayed com.android.mms/.ui.ConversationList: +636ms (total +20m47s230ms)

RE: sms database format - Added by Paul Kocialkowski about 9 years ago

I don't know about how that database is used in particular, you should look at the parts of the Android code that handle this or refer to general purpose guides about sqlite to do things right.

RE: sms database format - Added by Fil Lupin about 9 years ago

Ok, thanks anyway...

RE: sms database format - Added by My Self about 9 years ago

I don't know if this is the adequate advice here, but the open source app SMSSecure: https://f-droid.org/repository/browse/?fdfilter=smssecure&fdid=org.smssecure.smssecure (which uses it's own [probably more robust] database, and could be [if you want so] a completely SMS-app replacement), has a great import functionality...

RE: sms database format - Added by Fil Lupin about 9 years ago

Hi, for information, I tried some other actions.
The script does not seems to work, so I tried to convert my sms with samsung smart switch.
When loading theses mmssms.db, I get this message :

--------- beginning of /dev/log/main
D/audio_hw_primary( 1898): select_output_device: AUDIO_DEVICE_OUT_SPEAKER
--------- beginning of /dev/log/system
I/ActivityManager( 2251): START u0 {act=android.intent.action.MAIN cat=[android.intent.category.LAUNCHER] flg=0x10200000 cmp=com.android.mms/.ui.ConversationList bnds=[424,1154][552,1282]} from pid 2491
W/WindowManager( 2251): Failure taking screenshot for (328x583) to layer 21005
I/ActivityManager( 2251): Start proc com.android.mms for activity com.android.mms/.ui.ConversationList: pid=3242 uid=10024 gids={50024, 3003, 1015, 1028}
W/Trace ( 3242): error opening trace file: No such file or directory (2)
D/CountryDetector( 2251): The first listener is added
V/MmsConfig( 3242): mnc/mcc:
V/MmsConfig( 3242): tag: bool value: enabledMMS - true
V/MmsConfig( 3242): tag: int value: maxMessageSize - 307200
V/MmsConfig( 3242): tag: int value: maxImageHeight - 480
V/MmsConfig( 3242): tag: int value: maxImageWidth - 640
V/MmsConfig( 3242): tag: int value: defaultSMSMessagesPerThread - 500
V/MmsConfig( 3242): tag: int value: defaultMMSMessagesPerThread - 50
V/MmsConfig( 3242): tag: int value: minMessageCountPerThread - 10
V/MmsConfig( 3242): tag: int value: maxMessageCountPerThread - 5000
V/MmsConfig( 3242): tag: string value: uaProfUrl - http://www.google.com/oha/rdf/ua-profile-kila.xml
V/MmsConfig( 3242): tag: int value: recipientLimit - -1
V/MmsConfig( 3242): tag: bool value: enableMultipartSMS - true
V/MmsConfig( 3242): tag: bool value: enableSplitSMS - false
V/MmsConfig( 3242): tag: int value: smsToMmsTextThreshold - -1
V/MmsConfig( 3242): tag: bool value: enableSlideDuration - true
V/MmsConfig( 3242): tag: int value: maxMessageTextSize - -1
E/DatabaseUtils( 2432): Writing exception to parcel
E/DatabaseUtils( 2432): android.database.sqlite.SQLiteException: Can't downgrade database from version 58 to 57
E/DatabaseUtils( 2432): at android.database.sqlite.SQLiteOpenHelper.onDowngrade(SQLiteOpenHelper.java:361)

It seems the problem is because I convert mmssms.db from an 4.1.2 android and I attempt to load them on a 4.2 replicant version.
Since I did not hear of any soft to convert a mmssms from a version to another one, here is the only way I see to solve this problem : the solution I imagine should be to install a 4.2.2 ROM to convert my SMS from my iphone format to this 4.2.2 format and get them back with adb to push them back on replicant 4.2
Do you see any other mean to do this ?

RE: sms database format - Added by Paul Kocialkowski about 9 years ago

Do you see any other mean to do this ?

I still think the best way would be to extract data from your current database and insert it to an empty freshly-created one by the system in the right format, all using sqlite, plain and simple.

RE: sms database format - Added by Fil Lupin about 9 years ago

If I understand what you mean, it would be :
- taking the empty mmssms.db from Replicant (version 58)
- reading each line of my current android mmssms.db (version 57)
- puting them one by one into Replicant mmssms.db (version 58)

It will put sms information into replicant database, but I am not sure that I will not lose metadata informations...

RE: sms database format - Added by Fil Lupin about 9 years ago

For information, I tried a simple copy of SQL lines from sms actual table into mmssms.db empty database.
Once copied on replicant and after opening message application, no sms is visible, and here is the result from logcat :

--------- beginning of /dev/log/main
D/audio_hw_primary( 1895): select_output_device: AUDIO_DEVICE_OUT_SPEAKER
--------- beginning of /dev/log/system
I/ActivityManager( 2264): START u0 {act=android.intent.action.MAIN cat=[android.intent.category.LAUNCHER] flg=0x10200000 cmp=com.android.mms/.ui.ConversationList bnds=[424,1154][552,1282]} from pid 2791
W/WindowManager( 2264): Failure taking screenshot for (328x583) to layer 21005
D/Mms:app ( 3112): cancelNotification
D/Mms ( 3112): [1] mDeleteObsoleteThreadsRunnable calling asyncDeleteObsoleteThreads
D/Mms ( 3112): [1] onQueryComplete finished DELETE_OBSOLETE_THREADS_TOKEN
I/ActivityManager( 2264): Displayed com.android.mms/.ui.ConversationList: +487ms
D/Mms:app ( 3112): cancelNotification
D/Mms:app ( 3112): cancelNotification
D/Mms:app ( 3112): cancelNotification
W/WindowManager( 2264): Failure taking screenshot for (328x583) to layer 21010
D/dalvikvm( 3112): GC_EXPLICIT freed 166K, 55% free 4844K/10664K, paused 2ms+3ms, total 22ms

As I thought, it seems you can not copy directly SQL lines from sms table since there are foreign keys which are linked to other tables which should be filled with corresponding data.

As the same time, with application samsung smart switch which I had locally installed. I discovered that samsung android 4.1.2 contains mmssms.db database version 58 where replicant 4.2 0003 needs a version 57. However, samsung smart switch is not compatible with samsung android version before 4.1.2 so I cannot export sms from iphone to pc and then from pc to 4.1.1 android phone and then transfer them to replicant 4.2.

Therefore I will try to copy content of all tables into replicant mmssms.db based on my mmssms.db obtained by samsung smart switch after deleting tables and fields which are not in replicant db (it seems there is not additionnal fields or tables).

RE: sms database format - Added by Fil Lupin about 9 years ago

Bad news, doing this copy of sqlite db does not seems to work either :

--------- beginning of /dev/log/main
D/audio_hw_primary( 1898): select_output_device: AUDIO_DEVICE_OUT_SPEAKER
--------- beginning of /dev/log/system
I/ActivityManager( 2270): START u0 {act=android.intent.action.MAIN cat=[android.intent.category.LAUNCHER] flg=0x10200000 cmp=com.android.mms/.ui.ConversationList bnds=[424,1154][552,1282]} from pid 2784
W/WindowManager( 2270): Failure taking screenshot for (328x583) to layer 21010
D/Mms:app ( 3105): cancelNotification
D/Mms:app ( 3105): cancelNotification
D/Mms:app ( 3105): cancelNotification
D/Mms ( 3105): [1] mDeleteObsoleteThreadsRunnable calling asyncDeleteObsoleteThreads
D/Mms:app ( 3105): cancelNotification
D/Mms ( 3105): [1] onQueryComplete finished DELETE_OBSOLETE_THREADS_TOKEN
I/ActivityManager( 2270): Displayed com.android.mms/.ui.ConversationList: +329ms
W/WindowManager( 2270): Failure taking screenshot for (328x583) to layer 21015
D/dalvikvm( 3105): GC_EXPLICIT freed 188K, 58% free 4521K/10664K, paused 1ms+2ms, total 18ms

RE: sms database format - Added by Fil Lupin almost 9 years ago

I move forward after a few tries. Here is a sketch for what I did :
- user_version is a SQL PRAGMA which is used by android to decide if it can use the database. It should be 57 for replicant 4.2 but the database converted by samsung smart switch from android 4.1.2 is 58 (I do not understand why a 4.2 android uses an older database version than a 4.1.2 android but whatever...)
- all the database schema should be converted to the 57 version. I started with schema I got from a new replicant installation /data/data/com.android.providers.telephony/databases/mmssms.db. I print schema for the replicant database and the android 4.1.2 database. Then, several steps :
..+ dropping every table, trigger and index which disappears from replicant mmssms.db
..+ converting all the tables for which schema changes and adding all the corresponding trigger (they are dropped when associated table is dropped)
Then when I push the new mmssms.db, I have to wait for android to rebuild mmssms.db-journal (it can take a while).

However, I have issues with access to MMS media...

Here is in attachment the SQL script I used (/!\ it does not actually allow to save mms part) if it can help other people.

Anyway, thank you Paul for the advice and direction you gave me. :)

transformation.sql (16.9 KB) transformation.sql

RE: sms database format - Added by Fil Lupin almost 9 years ago

About the mms attachment issue : when I attempt to save an attachment from a mms, I got the message "Couldn't save attachment.".
Some info about my config :
- I use a samsung galaxy S 3
- rights are drwxrwxr-x on directories into /sdcard/
- I do not have an external sdcard into my phone

Could you confirm this issue does not exist when you got a mms from a replicant (without importing mms)?

RE: sms database format - Added by Paul Kocialkowski almost 9 years ago

Could you confirm this issue does not exist when you got a mms from a replicant (without importing mms)?

Sure, MMS work fine on Replicant, I've sent and received plenty.

I also know for a fact that the attached files (both the sent and received ones) are not stored in the database. Instead, they're stored in separate folders (IIRC, received MMS are stored in /data/ while sent ones are someone on the Android folder of the sdcard).

RE: sms database format - Added by Fil Lupin almost 9 years ago

Thanks you for this information.
I confirm that MMS attachments are automatically stored on the phone, into /data/data/com.android.providers.telephony/app_parts/ directory (path found into _data field of part table, into mmssms.db sqlite file).
I will try copying the files hoping it will be ok to have access to old mms attachments on new replicant...

RE: sms database format - Added by Fil Lupin almost 9 years ago

I confirm it works !
I will write a new subject to be as clear as possible.

    (1-21/21)