Project

General

Profile

How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6

Added by Fil Lupin over 7 years ago

edit: I completed the check-up. Currently (2018 june, 13th), there is no solution

Hi there,
I would like to understand how to keep SMS, MMS and contacts when upgrading from replicant 4.2 to 6

I installed replicant-6 on a phone with an old replicant 4.2 0004.
It is a i9300. I also tested this with an i9100.
At first, I kept my data by not wiping them (I know it is said at several points int the website I should not but my phone was stuck so the installation of replicant 6 was a walkthrough and not the initial goal ;)). I discovered my SMS/MMS are empty. Same for Contacts.

MMS/SMS:

Checking into "/data/data/com.android.providers.telephony/databases/" I found mmssms.db was empty: the size should be much more.
I push (adb push -p) my mmssms.db and mmssms.db-journal save files and give them the rights owners and rights, but sms still are empty in app.

Is there anything to do to make them work? It seems from https://redmine.replicant.us/boards/21/topics/12057 it should work.

Contacts:
I had a file 00001.vcf with all my contacts.
I imported it into Contacts but I can not select a contact without it crashing (see in the message below). It seems very strange since I can understand SMS & MMS format change between 4.2 and 6, but contacts and VCF format should definitively be the same. Aren't they?

Perhaps is it linked to my former issue (https://redmine.replicant.us/boards/9/topics/13191?r=14281#message-14281)

One extrem solution would be to install back replicant 4.2 and install again replicant-6 with specific steps to convert both sms and contacts... but I still need these steps. ;)

If anyone knows if it is possible and how to do this, I am very interested.
Thank you.

EDIT: here is an abstract of informations I gathered, please let me know if you have any other information:
oandbackup [https://github.com/jensstein/oandbackup]:
  • does not convert SMS/MMS from Android 4 to 6 (even if it allows to save and restore SMS/MMS)
SMSBackup + [https://f-droid.org/en/packages/com.zegoggles.smssync/]:

Silence (formerly SMSSecure) [https://git.silence.dev/Silence/Silence-Android]:

LibreSignal []: Slight backup:
  • does not seem to manage MMS.
Nextcloud SMS (https://f-droid.org/packages/fr.unix_experience.owncloud_sms/):

Replies (21)

RE: compatibilty of MMSSMS format between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

I tried some methodes which could be walkthrough.

"Contacts" app: by selecting any contact, Contacts briefly show the contact and then crashes before I can select the sms button, with the message: "Unfortunatly, Contacts has stopped"

I tried changing back rights to rw-rw-rw, it does not change anything (and I doubt changing back owner to root changes anything ;)).

You will find attached the logcat I got when Contacts crashes.

I saw that some user succeed into keeping their SMS & MMS (https://redmine.replicant.us/boards/21/topics/12057?r=13803#message-13803), I'm curious to understand how. :)

RE: compatibilty of MMSSMS format between replicant 4.2 and 6 - Added by Wolfgang Wiedmeyer over 7 years ago

This is already addressed in the wiki. The database formats are incompatible. And if you keep your Replicant 4.2 data, you will likely run into similar issues with other apps.

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

Sorry, I've seen this but I've also seen some people which seem to have kept their SMS so I had hope...
I did not interest myself to SMS backup/convert app since a long time, do you have any idea what I could use?

And for contacts, do you have any idea why it does not work?

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Wolfgang Wiedmeyer over 7 years ago

Regarding the Contacts issue, it's possible that the import actually works well, but the crash is caused when the Contacts app does a lookup in the incompatible SMS database:

06-22 16:35:04.495  4243  4243 V SmsInteractionsLoader: SmsInteractionsLoader
06-22 16:35:04.505  4243  4264 V SmsInteractionsLoader: loadInBackground
06-22 16:35:04.585  2854  3065 E SQLiteLog: (1) no such table: sms_restricted
06-22 16:35:04.585  2854  3065 E DatabaseUtils: Writing exception to parcel
06-22 16:35:04.585  2854  3065 E DatabaseUtils: android.database.sqlite.SQLiteException: no such table: sms_restricted (code 1):

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

Wolfgang Wiedmeyer wrote:

Regarding the Contacts issue, it's possible that the import actually works well, but the crash is caused when the Contacts app does a lookup in the incompatible SMS database:
[...]

Oh, I didn't though about this since I did not opened sms. Thank you.

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Wolfgang Wiedmeyer over 7 years ago

I'm not sure anymore if Replicant 4.2 had an option to export SMS in a same way as the Contacts app. I'm using Silence since a long time. You could try to do an import of your SMS into this app. AFAIK it is superior in handling such issues compared to the default SMS app. By the way, you can use Silence as a regular SMS app as well without the encryption.

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

Hi, I installed again Replicant 4-2 and installed Silence app, make an export sms and installed again Replicant 6 to import contacts and sms.
It worked.
If you do not choose Silence as default app for SMS, you can import then new SMS/MMS into silence without loosing former ones.

One issue: when opening clock app, it crashes. It seems even if I wiped out data and cache, data are still into the phone and so it is probably the cause.

However, there is one black point to this app: I did not find how to convert silence SMS back to SMS basic app. It is far away from the philosophy of GNU since it significates loosing all your SMS if app support stops or if this app does not work under your new phone.
So it seems to me this app represents more a last resort than a real solution...
edit : it seems one can go back to sms/mms default app (https://github.com/SilenceIM/Silence/issues/584)
However, since my phone can't be set in USB keystick mode, I can't import/export data to my computer. I am not sure if this is linked to silence installation even if it seems so.

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

By the way, Silence crashes when beginning a crypted conversation.
Does anyone has the same problem?

RE: How to keep MMSSMS and Contacts between replicant 4.2 and 6 - Added by Fil Lupin over 7 years ago

After 10 days of normal use, Signal crashes every time it is launched, after entering passphrase. It is then only possible to watch sms for one second sometimes.
In this case, it is not possible to export sms and so changing default app for sms will make loose sms received since signal is used.

edit : It is seems there are some cases where it is impossible to use last version (unstable) in a stable way. Moreover, it seems impossible to manually export/import from Silence.
I strongly advise to avoid installing Silence.

Drawbacks :

I conclude using Silence to convert MMS and SMS messages from replicant 4 to replicant 6 is a very bad idea.
Has anyone found another method?

Currently, there is no solution at all to keep MMS and SMS when changing from Replicant 4.2 to Replicant 6.

SMS application Silence does not manage MMS and keep user captive.

SMS application LibreSignal does not manage MMS format since it is just a rebuild of Signal (https://github.com/xmikos/fdroiddata/issues/39#issuecomment-292017031)

SMS backup application SMS Backup+ (https://f-droid.org/en/packages/com.zegoggles.smssync/) is again available on F-droid. It needs either to send private sms to gmail or to create our own's IMAP server, and recommand to not import/export more than 5000 messages which is the case when upgrading version (source: https://github.com/jberkel/sms-backup-plus#faq-restore-many-messages). It seems the only direction available currently.

Slight backup does not seem to manage MMS.

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Kurtis Hanna about 7 years ago

Fil, I know you already know this, but for other people that are following along at home, SMS Backup+ just was added back to F-Droid.

That's pretty lame that Silence doesn't backup MMS. I didn't know that. I just did an export and then an import of all my Silence messages and the process worked pretty well. I didn't look to see if I lost all my group texts and other MMS texts tho. I'm assuming I lost all those now, after reading this.

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin about 7 years ago

Hi Hanna,
it is very difficult to understand what is managed and what is not given the available documentation/information.
What I am sure of is MMS cannot be imported from another SMS/MMS manager. You can check this by reading on your phone a SMS thread with a MMS inside, and check that there is not anymore attachment inside.

For everything else, I based my conclusion on the public doc and the answers of the maintainer.
However, there is not really any doc and the maintainer does a great work but he seems to have been a bit busy these last months.
If you have any answer, for example to understand if you can export and then import again MMS, it would be interesting to know if it works in encrypted or non encrypted export.

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Jeremy Rand about 7 years ago

Have you tried Nextcloud SMS? https://f-droid.org/packages/fr.unix_experience.owncloud_sms/

Based on the description, it looks like it might be usable for this. (I haven't tried it, since I don't use SMS.)

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Kurtis Hanna about 7 years ago

Hi Lupin,
I was just noticing that oandbackup wasn't mentioned in this thread.

Did you happen to try that?

I found this in their github issues:

sms messages are in mmssms.db of com.android.providers.telephony

https://github.com/jensstein/oandbackup/issues/95#issuecomment-87449614

Perhaps you could back that up with oandbackup in 4.2 and try to restore them in 6.0.1?

-Hanna

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin about 7 years ago

Hi Jeremy,
on the page of nextcloud SMS, it is written it is compatible with Android 4 and 5. However, my goal was to convert SMS to Android 6 so it seems this solution will not do it.

Hi Hanna, thank you for your message
In fact, I looked after some tips on the behaviour of oandbackup when you suggested me this soft (https://redmine.replicant.us/boards/21/topics/12057?r=14380#message-14380) but it saves and backups data.
It seems it is not able to convert SMS format.

Maybe I missed something, so do not hesitate to correct me if I'm wrong.
I'll be happy to find a solution! :)

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Jeremy Rand about 7 years ago

Fil Lupin wrote:

Hi Jeremy,
on the page of nextcloud SMS, it is written it is compatible with Android 4 and 5. However, my goal was to convert SMS to Android 6 so it seems this solution will not do it.

The F-Droid app description says 4.0 to 5.0, but the F-Droid metadata says 4.1 or newer. Based on this upstream issue [1], it looks like the app works on 7.1.1, so I suspect that the F-Droid metadata is correct and the F-Droid app description is wrong.

If you can confirm that it works on 6.0, please consider submitting a bug report to F-Droid about the incorrect description.

EDIT: The app description on the Google Play page for the same app [2] says it works up to 7.1. So I think the F-Droid description is outdated.

[1] https://github.com/nerzhul/ownCloud-SMS-App/issues/141
[2] https://play.google.com/store/apps/details?id=fr.unix_experience.owncloud_sms

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin about 7 years ago

Thank you Jeremy, I'll try this. Hope this will work ! :)

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin almost 7 years ago

Hi there, after some months and a phone stolen, I made a few new tests:

- I confirm oandbackup does not work to convert SMS/MMS from Android 4 to 6 (even if it allows to save and restore SMS/MMS)
- SMSBackup + does not allow to restore MMS, exactly as Silence (https://github.com/jberkel/sms-backup-plus/issues/583#issuecomment-181022137)

Moreover, I undestand from https://github.com/nerzhul/ownCloud-SMS-App/issues/153#issuecomment-329143176 that Nextcloud SMS does not handle MMS neither...

Currently, I do not know any solution to keep MMS when upgrading from Replicant 4.2 to Replicant 6.

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin almost 2 years ago

Hi,
reading https://redmine.replicant.us/projects/replicant/wiki/RestoreApplicationInternalData, I wonder if does anyone knew if there are any news about using Silence application to export and import MMS & SMS ?

  • Is it possible to get back to stock MMS & SMS application?
  • Is it possible to backup MMS in addition to SMS?

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin 9 months ago

I confirm it still is not possible to import/export MMS from Silence, so this is not a solution.

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin 9 months ago

Since there is no hope for a tool to to this, I analyzed format of mmssms.db schema for replicant 4.2 0004 and replicant 6.0 0003.

To do this, I used a GPL/LGPL tool DB-Browser 3.10.1 to read sqlite files (https://sqlitebrowser.org/).

I noticed that replicant 4.2 0004 has a user version=57 and replicant 6.0 0003 has user version=66 (see https://www.sqlite.org/pragma.html#pragma_user_version).

When I compare database schema, here is the result:

--- replicant4.2.0004/data/data/com.android.providers.telephony/databases/mmssms.db.sql
+++ replicant6.0 0003/data/data/com.android.providers.telephony/databases/mmssms.db.sql
@@ -30,6 +30,7 @@
     `snippet`    TEXT,
     `snippet_cs`    INTEGER DEFAULT 0,
     `read`    INTEGER DEFAULT 1,
+    `archived`    INTEGER DEFAULT 0,
     `type`    INTEGER DEFAULT 0,
     `error`    INTEGER DEFAULT 0,
     `has_attachment`    INTEGER DEFAULT 0
@@ -55,8 +56,12 @@
     `body`    TEXT,
     `service_center`    TEXT,
     `locked`    INTEGER DEFAULT 0,
+    `sub_id`    INTEGER DEFAULT -1,
+    `phone_id`    INTEGER DEFAULT -1,
     `error_code`    INTEGER DEFAULT 0,
+    `creator`    TEXT,
     `seen`    INTEGER DEFAULT 0,
+    `priority`    INTEGER DEFAULT -1,
     PRIMARY KEY(`_id`)
 );
 CREATE TABLE IF NOT EXISTS `raw` (
@@ -67,6 +72,8 @@
     `sequence`    INTEGER,
     `destination_port`    INTEGER,
     `address`    TEXT,
+    `sub_id`    INTEGER DEFAULT -1,
+    `phone_id`    INTEGER DEFAULT -1,
     `pdu`    TEXT,
     PRIMARY KEY(`_id`)
 );
@@ -82,6 +89,8 @@
     `err_code`    INTEGER,
     `retry_index`    INTEGER NOT NULL DEFAULT 0,
     `due_time`    INTEGER,
+    `pending_sub_id`    INTEGER DEFAULT -1,
+    `pending_phone_id`    INTEGER DEFAULT 0,
     `last_try`    INTEGER,
     PRIMARY KEY(`_id`)
 );
@@ -117,7 +126,10 @@
     `d_tm`    INTEGER,
     `d_rpt`    INTEGER,
     `locked`    INTEGER DEFAULT 0,
+    `sub_id`    INTEGER DEFAULT -1,
+    `phone_id`    INTEGER DEFAULT -1,
     `seen`    INTEGER DEFAULT 0,
+    `creator`    TEXT,
     `text_only`    INTEGER DEFAULT 0
 );
 CREATE TABLE IF NOT EXISTS `part` (
@@ -166,10 +178,12 @@
     `type`,
     `thread_id`
 );
+CREATE INDEX IF NOT EXISTS `index_part` ON `part` (
+    `mid`
+);
 CREATE TRIGGER update_threads_on_update_pdu  AFTER UPDATE of thread_id ON pdu  BEGIN   UPDATE threads SET has_attachment=1 WHERE _id IN    (SELECT pdu.thread_id FROM part JOIN pdu      WHERE part.ct != 'text/plain' AND part.ct != 'application/smil'      AND part.mid = pdu._id); END;
 CREATE TRIGGER update_threads_on_update_part  AFTER UPDATE of mid ON part  WHEN new.ct != 'text/plain' AND new.ct != 'application/smil'  BEGIN   UPDATE threads SET has_attachment=1 WHERE _id IN    (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid      WHERE part._id=new._id LIMIT 1);  END;
 CREATE TRIGGER update_threads_on_insert_part  AFTER INSERT ON part  WHEN new.ct != 'text/plain' AND new.ct != 'application/smil'  BEGIN   UPDATE threads SET has_attachment=1 WHERE _id IN    (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid      WHERE part._id=new._id LIMIT 1);  END;
-CREATE TRIGGER update_threads_on_delete_part  AFTER DELETE ON part  WHEN old.ct != 'text/plain' AND old.ct != 'application/smil'  BEGIN   UPDATE threads SET has_attachment =    CASE     (SELECT COUNT(*) FROM part JOIN pdu      WHERE pdu.thread_id = threads._id      AND part.ct != 'text/plain' AND part.ct != 'application/smil'      AND part.mid = pdu._id)   WHEN 0 THEN 0    ELSE 1    END;  END;
 CREATE TRIGGER update_threads_error_on_update_sms   AFTER UPDATE OF type ON sms  WHEN (OLD.type != 5 AND NEW.type = 5)    OR (OLD.type = 5 AND NEW.type != 5) BEGIN   UPDATE threads SET error =     CASE      WHEN NEW.type = 5 THEN error + 1      ELSE error - 1    END   WHERE _id = NEW.thread_id; END;
 CREATE TRIGGER update_threads_error_on_update_mms   AFTER UPDATE OF err_type ON pending_msgs   WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)    OR (OLD.err_type >= 10 AND NEW.err_type < 10) BEGIN  UPDATE threads SET error =     CASE      WHEN NEW.err_type >= 10 THEN error + 1      ELSE error - 1    END   WHERE _id =   (SELECT DISTINCT thread_id    FROM pdu    WHERE _id = NEW.msg_id); END;
 CREATE TRIGGER update_threads_error_on_move_mms   BEFORE UPDATE OF msg_box ON pdu   WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4)   AND (OLD._id IN (SELECT DISTINCT msg_id                   FROM pending_msgs                   WHERE err_type >= 10)) BEGIN   UPDATE threads SET error = error - 1  WHERE _id = OLD.thread_id; END;
@@ -181,15 +195,15 @@
 CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER  UPDATE OF date, body, type  ON sms BEGIN  UPDATE threads SET    date = (strftime('%s','now') * 1000),     snippet = new.body,     snippet_cs = 0  WHERE threads._id = new.thread_id;   UPDATE threads SET message_count =      (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND sms.type != 3) +      (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND (m_type=132 OR m_type=130 OR m_type=128)        AND msg_box != 3)   WHERE threads._id = new.thread_id;   UPDATE threads SET read =     CASE (SELECT COUNT(*)          FROM sms          WHERE read = 0            AND thread_id = threads._id)      WHEN 0 THEN 1      ELSE 0    END  WHERE threads._id = new.thread_id; END;
 CREATE TRIGGER pdu_update_thread_read_on_update AFTER  UPDATE OF read  ON pdu   WHEN new.m_type=132    OR new.m_type=130    OR new.m_type=128 BEGIN   UPDATE threads SET read =     CASE (SELECT COUNT(*)          FROM pdu          WHERE read = 0            AND thread_id = threads._id             AND (m_type=132 OR m_type=130 OR m_type=128))       WHEN 0 THEN 1      ELSE 0    END  WHERE threads._id = new.thread_id; END;
 CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON pdu   WHEN new.m_type=132    OR new.m_type=130    OR new.m_type=128 BEGIN  UPDATE threads SET    date = (strftime('%s','now') * 1000),     snippet = new.sub,     snippet_cs = new.sub_cs  WHERE threads._id = new.thread_id;   UPDATE threads SET message_count =      (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND sms.type != 3) +      (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND (m_type=132 OR m_type=130 OR m_type=128)        AND msg_box != 3)   WHERE threads._id = new.thread_id;   UPDATE threads SET read =     CASE (SELECT COUNT(*)          FROM pdu          WHERE read = 0            AND thread_id = threads._id             AND (m_type=132 OR m_type=130 OR m_type=128))       WHEN 0 THEN 1      ELSE 0    END  WHERE threads._id = new.thread_id; END;
-CREATE TRIGGER pdu_update_thread_on_delete AFTER DELETE ON pdu BEGIN   UPDATE threads SET      date = (strftime('%s','now') * 1000)  WHERE threads._id = old.thread_id;   UPDATE threads SET message_count =      (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = old.thread_id        AND sms.type != 3) +      (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = old.thread_id        AND (m_type=132 OR m_type=130 OR m_type=128)        AND msg_box != 3)   WHERE threads._id = old.thread_id;   UPDATE threads SET snippet =    (SELECT snippet FROM     (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu      UNION SELECT date, body AS snippet, thread_id FROM sms)    WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1)   WHERE threads._id = OLD.thread_id;   UPDATE threads SET snippet_cs =    (SELECT snippet_cs FROM     (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu      UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)    WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1)   WHERE threads._id = OLD.thread_id; END;
 CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER  UPDATE OF date, sub, msg_box  ON pdu   WHEN new.m_type=132    OR new.m_type=130    OR new.m_type=128 BEGIN  UPDATE threads SET    date = (strftime('%s','now') * 1000),     snippet = new.sub,     snippet_cs = new.sub_cs  WHERE threads._id = new.thread_id;   UPDATE threads SET message_count =      (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND sms.type != 3) +      (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads       ON threads._id = thread_id      WHERE thread_id = new.thread_id        AND (m_type=132 OR m_type=130 OR m_type=128)        AND msg_box != 3)   WHERE threads._id = new.thread_id;   UPDATE threads SET read =     CASE (SELECT COUNT(*)          FROM pdu          WHERE read = 0            AND thread_id = threads._id             AND (m_type=132 OR m_type=130 OR m_type=128))       WHEN 0 THEN 1      ELSE 0    END  WHERE threads._id = new.thread_id; END;
 CREATE TRIGGER part_cleanup DELETE ON pdu BEGIN   DELETE FROM part  WHERE mid=old._id;END;
 CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words  SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2);  END;
-CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM  words WHERE source_id = OLD._id AND table_to_use = 2; END;
 CREATE TRIGGER insert_mms_pending_on_update AFTER UPDATE ON pdu WHEN new.m_type=128  AND new.msg_box=4  AND old.msg_box!=4 BEGIN   INSERT INTO pending_msgs    (proto_type,     msg_id,     msg_type,     err_type,     err_code,     retry_index,     due_time)   VALUES     (1,      new._id,      new.m_type,0,0,0,0);END;
 CREATE TRIGGER insert_mms_pending_on_insert AFTER INSERT ON pdu WHEN new.m_type=130  OR new.m_type=135 BEGIN   INSERT INTO pending_msgs    (proto_type,     msg_id,     msg_type,     err_type,     err_code,     retry_index,     due_time)   VALUES     (1,      new._id,      new.m_type,0,0,0,0);END;
 CREATE TRIGGER delete_mms_pending_on_update AFTER UPDATE ON pdu WHEN old.msg_box=4  AND new.msg_box!=4 BEGIN   DELETE FROM pending_msgs  WHERE msg_id=new._id; END;
 CREATE TRIGGER delete_mms_pending_on_delete AFTER DELETE ON pdu BEGIN   DELETE FROM pending_msgs  WHERE msg_id=old._id; END;
 CREATE TRIGGER cleanup_delivery_and_read_report AFTER DELETE ON pdu WHEN old.m_type=128 BEGIN   DELETE FROM pdu  WHERE (m_type=134    OR m_type=136)    AND m_id=old.m_id; END;
 CREATE TRIGGER addr_cleanup DELETE ON pdu BEGIN   DELETE FROM addr  WHERE msg_id=old._id;END;
+CREATE VIEW sms_restricted AS SELECT * FROM sms WHERE type=1 OR type=2;
+CREATE VIEW pdu_restricted AS SELECT * FROM pdu WHERE (msg_box=1 OR msg_box=2) AND (m_type!=130);
 COMMIT;

To be able to create a script to convert 4.2 to 6, I need some insights about the content of several columns:
  • threads.archived (default value 0), probably 0 if not archived and 1 if archived
  • sms.sub_id (default value -1)
  • sms.phone_id (default value -1)
  • sms.creator (no default value)
  • sms.priorty (default value -1)
  • raw.sub_id (default value -1)
  • raw.phone_id (default value -1)
  • pending_msgs.pending_sub_id (default value -1), probably just for pending sms/mms not sent
  • pending_msgs.pending_phone_id (default value 0)
  • pdu.sub_id (default value -1)
  • pdu.phone_id (default value -1)
  • pdu.creator (no default value)

creator fields are the two fields

To help, you need to get your MMS/SMS database with

adb pull /data/data/com.android.providers.telephony/databases/mmssms.db
sqlite3 mmssms.db .dump > mmssms.sql
cat mmssms-new.sql | grep -m 4 'INSERT INTO sms' | awk -v FPAT="([^,]*)|('([^']|'')*')" -v OFS=, '{ print $1, $2 , "PHONE_NB", $4, $5, $6, $7, $8, $9, $10, $11, $12, "SMS_CONTENT", $14, $15, $16, $17, $18, $19, $20, $21, $22}'
# output should be 4 lines like: 
# INSERT INTO sms VALUES(1,1,PHONE_NB,NULL,1281738006000,0,NULL,1,-1,2,NULL,NULL,SMS_CONTENT,NULL,0,-1,-1,0, =>CREATOR_FIELD<= ,1,-1);

Could someone launch these instructions and give me the ouput, if necessary anonymizing the CREATOR_FIELD?

Regards

RE: How to keep MMS, SMS and Contacts when moving from replicant 4.2 to 6 - Added by Fil Lupin 9 months ago

Searching for information about database format, I found a webpage about converting android 6 to android 7.
It gave me ideas to convert database between replicant versions 4.2 and 6 (sadly, I lost it).

Anyway, those principles can be used for converting any replicant version to any other version, if we can access to a mmssms.db database and analyze its differences with the currently used database.

I would like to get a fully CLI script but I did not have currently have time so I will use a GUI (DB-Browser).

Necessary tools:
  • adb
  • sqlite3
  • DB-Browser (sqlitebrowser.org)
## LOCAL PREPARATION
# work into a dedicated directory
mkdir mmssms-convertion
cd mmssms-convertion

## REPLICANT 4.2 & 6 PHONES BACKUP
# connect replicant 4.2 phone
# assure root mode is ok
adb kill-server && adb root
# Force stop messenging app
# get database from old replicant 4.2 phone
adb pull /data/data/com.android.providers.telephony/databases/mmssms.db ./mmssms-old.db

# connect replicant 6.0 phone
# be sure to disable delete old messages option if necessary
# assure root mode is ok
adb kill-server && adb root
# Force stop messenging app
# get database from new replicant 6.0 phone
adb pull /data/data/com.android.providers.telephony/databases/mmssms.db ./mmssms-default.db

## DATABASE CONVERTION 
## REPLICANT 4.2
# convert database into sql
sqlite3 mmssms-old.db .dump > mmssms-old.sql
# Open mmssms-old.db with DB-Browser, File/Export/Database to SQL file..., select canonical_addresses, threads & sms tables, Keep column names in INSERT INTO, Export data only, click OK and save it into mmssms-old-inserts-sms-threads-canonical_addresses.sql

## REPLICANT 6.0
# convert database into sql
#sqlite3 mmssms-default.db .dump > mmssms-default.sql

# Open mmssms-new.db with DB-Browser, Execute SQL, load mmssms-old-with-sms-threads-tables-replaced.sql file, execute, write it into mmssms-default.db (it can take 90 min for 110,000 sms databases)

## UPDATE NEW DATABASE
# Force stop messenging app
# update database on replicant6
adb root
# check rights of mmssms.db on replicant 6.0 phone
adb shell ls -laZ /data/data/com.android.providers.telephony/databases/
adb push mmssms-default.db /data/data/com.android.providers.telephony/databases/mmssms.db
adb shell chown radio:radio /data/data/com.android.providers.telephony/databases/mmssms.db
adb shell chmod 660 /data/data/com.android.providers.telephony/databases/mmssms.db
#Unnecessary: adb shell chcon u:object_r:radio_data_file:s0 /data/data/com.android.providers.telephony/databases/mmssms.db
adb shell ls -laZ /data/data/com.android.providers.telephony/databases/
# restart replicant 6.0 phone
# check sms & mms are all there, through DB-Browser and the application
# Enjoy!

Here are some steps which could be useful for a full CLI script:

# Open mmssms-default.db with DB-Browser, File/Export/Database to SQL file..., select all tables, Keep column names in INSERT INTO, Export schema only, Keep old schema (CREATE TABLE IF NOT EXISTS), click OK and save it into mmssms-default-schema.sql
# get sms & threads table replicant 6.0 definition
#TABLE_sms=$( cat mmssms-default.sql | grep 'CREATE TABLE sms')
#TABLE_threads=$( cat mmssms-default.sql | grep 'CREATE TABLE threads')

# replace old replicant 4.2 sms table definition by new one (replicant 6.0) 
#sed -e "s/CREATE TABLE sms[[:space:]]*([a-zA-Z0-9_, \-]*);/$TABLE_sms/" mmssms-old.sql  > mmssms-old-with-sms-table-replaced.sql
# check the only difference is sms table
#diff mmssms-old.sql mmssms-old-with-sms-table-replaced.sql
# replace old replicant 4.2 threads table definition by new one (replicant 6.0) 
#sed -e "s/CREATE TABLE threads[[:space:]]*([a-zA-Z0-9_, \-]*);/$TABLE_threads/" mmssms-old-with-sms-table-replaced.sql  > mmssms-old-with-sms-threads-tables-replaced.sql
# check the only difference is threads table
#diff mmssms-old-with-sms-table-replaced.sql mmssms-old-with-sms-threads-tables-replaced.sql
# associates replicant 6.0 tables with sms, threads and canonical_addresses table contents
# cat mmssms-old-with-sms-threads-tables-replaced.sql mmssms-old-inserts-sms-threads-canonical_addresses.sql  | sqlite3 > mmssms-new.db
    (1-21/21)