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.
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)
- does not restore MMS, exactly as Silence (https://github.com/jberkel/sms-backup-plus/issues/583#issuecomment-181022137)
- 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)
Silence (formerly SMSSecure) [https://git.silence.dev/Silence/Silence-Android]:
- does not allow to get back to another app with SMS & MMS (https://git.silence.dev/Silence/Silence-Android/-/issues/394 => https://git.silence.dev/Silence/Silence-Android/-/issues/145)
- does not allow MMS import. MMS import/export are only available by encrypted import/export (https://git.silence.dev/Silence/Silence-Android/-/issues/140 and https://git.silence.dev/Silence/Silence-Android/-/issues/184)
- does not manage MMS format since it is just a rebuild of Signal (https://github.com/xmikos/fdroiddata/issues/39#issuecomment-292017031)
- does not seem to manage MMS.
- could work to convert from android 4 to 6
- does not handle MMS (https://github.com/nerzhul/ownCloud-SMS-App/issues/153#issuecomment-329143176)
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.
- Silence does not allow to get back to another app with SMS & MMS (https://github.com/SilenceIM/Silence/issues/394)
- Silence seems to not allow MMS import. MMS import/export are only available by encrypted import/export (https://github.com/SilenceIM/Silence/issues/140 and https://github.com/SilenceIM/Silence/issues/184)
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