The thread is a little old, but I just received my second L5 and took the plunge to move my main sim from Android. After a couple of days messing around I managed to cobble together a PHP (sorry if anyone’s offended by that) script that reads a xml dump of Android sms messages and generates SQL statements to import them into Chatty’s DB - (after importing contacts from a .vcf file).
If anyone is still interested, here it is … comments and instructions in the script. As noted below, Chatty will only display the threads when you receive or compose a message from/to one of your contacts.
Don’t forget to take a backup first …
#!/usr/bin/php -q
<?php
/*
* Quick kludgy script to import Android messages into L5.
*
* Not warrantied in any way - as the saying goes, if it breaks you get to keep the pieces.
* Probably error/omissions/bugs exist - please check and correct.
*
* Android message dump was created with SMS Backup & Restore
* Syntech Pty Ltd
* free version
*
* The sms backup file is xml format.
*
* Chatty uses a SQLite database
*
* ~/.purple/chatty/db/chatty-history.db
*
* MAKE A BACKUP OF ~/.purple/chatty/db/chatty-history.db
* copy your backup to at least two places just in case you're as dangerously inattentive as I am
* - mind you, Portuguese wine is very good.
*
* Here's what I did ...
*
* Took a backup of my Android contacts and saved them to a .vcf file.
* Imported the contacts into my L5 using the contacts gui.
* Dumped Android sms messages and copied the xml file to a Linux laptop
* copied the ~/.purple/chatty/db/chatty-history.db to the same laptop
* installed Sqlite (sqlite3) onto the laptop and searched for how to use it on the command line
* CLI Sqlite interface
*
*
user@laptop: sqlite3 chatty-history.db
SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite>
The most useful commands at this point (IMO) are
.mode column
.tables
The first avoids frustration trying to read pipe-separated non-width-formatted data.
The second lists the tables.
I also ran schemaspy against the database and checked out the relationships between the tables.
This script will echo the SQL INSERT and UPDATE statements to STDOUT.
It does not execute the SQL.
I ran the script and redirected the output to a text file
so,
sms_android_to_l5.php > to_chatty.sql
then invoked sqlite3 and passed this file to it, saving the output to a log file
so,
sqlite3 ~/path/to/copyof/chatty-history.db ".read to_chatty.sql" > to_chatty.log 2>&1
The last few SQL statements of this script query the relevant tables so you can check the log file to
satisfy yourself that everything looks OK.
When you're happy (all relative) ... copy the updated database back to your L5
~/.purple/chatty/db/chatty-history.db
It seems that Chatty will not display the threads until either,
you receive a text from one of your contacts, or
you start a new message and choose someone from the contacts in the xml file (i'e', the threads that you imported).
*
*/
$x = "/home/USER/Downloads/sms.xml"; // <=== set correct path to xml backup file
$users = [];
$guid = "lower(hex(randomblob(4))) || '-' || " //generate guid for messages - found on t'internet
."lower(hex(randomblob(2))) || '-4' || "
."substr(lower(hex(randomblob(2))),2) || '-' || "
."substr('89ab',abs(random()) % 4 + 1, 1) || "
."substr(lower(hex(randomblob(2))),2) || '-' || "
."lower(hex(randomblob(6)))"
;
$me_name = 'invalid-0000000000000000'; //Chatty uses this name / id for the L5 user
$me_id = 1;
$xmlDoc = new DOMDocument(); //Read file into DOM document
$dom = $xmlDoc->load($x); //
$e = $xmlDoc->documentElement; //Get top level node
$msg_array = $e->getElementsByTagName('sms'); //Get all <sms> tags
foreach($msg_array as $msg) { //iterate through messages
if($msg->hasAttributes()) { //ignore empty / malformed tags
//Message details are held as attributes, not <tags>
$address = preg_replace('/^0/', '+44', $msg->getAttribute('address')); //address attribute = phone number
$users[$address] = $users[$address] ?? []; //create array for phone numbers
$users[$address]["user"] = "INSERT INTO users (username, alias, avatar_id, type) " //create entry in users table - OK to overwrite for each message with the same phone #
."VALUES ("
."'$address', "
."'".$msg->getAttribute('contact_name')."', NULL, 1"
.");";
$users[$address]["thread"] = "INSERT INTO threads (" // messages are associated with message threads
."name, alias, avatar_id, " // - create an entry for this phone #
."account_id, type, encrypted, " // - set the last_read_id to the max msg id for this phone - number
."last_read_id, visibility, " // it will be incremented for each new message for this phone #
."notification" // visibility = 0 (from chatty source code : #define THREAD_VISIBILITY_VISIBLE 0)
.") VALUES ("
."'".$address."', NULL, NULL, 1, 0, 0, "
."(SELECT max(id) FROM messages "
."WHERE sender_id = ("
."SELECT id FROM users "
."WHERE username = '".$address
."'))"
.", 0, 1);"
;
$users[$address]["thread_members"] = "INSERT INTO thread_members " // add phone number to this (i.e., the latest) thread.
."("
."thread_id, user_id"
.") VALUES ("
."(SELECT max(id) FROM threads), "
."(SELECT id FROM users "
."WHERE username = '$address')"
.");\n"
;
$users[$address]['msgs'] = $users[$address]['msgs'] ?? []; // create array for messages for this phone number
$direction = $msg->getAttribute('type') == 2 ? -1:1; // xml file has type="1" (inbound) or "2" (outbound)
// chatty has 1 (inbound) or -1 (outbound)
$users[$address]['msgs'][] = "INSERT INTO messages (" // Add new message
."uid, thread_id, sender_id, "
."user_alias, body, body_type, "
."direction, time, status, "
."encrypted, preview_id, subject)"
." VALUES ("
."$guid, (SELECT max(id) FROM threads), " // the message belongs to the current (max) thread
."(SELECT id FROM users WHERE username = '$address'), "// set sender_id to current phone number - Needs to be fixed later
."NULL, '" // for outbound messages as they are from $me
.str_replace("'", "''", $msg->getAttribute('body')) // Double any embedded single quotes '
."', 1, $direction, "
.substr($msg->getAttribute('date'), 0, 10)
.", ".($direction == -1 ? '4' : 'NULL').", 0, NULL, NULL);"; // xml file has 13 digits for time, chatty uses 10
$users[$address]['msgs_upd'] = "UPDATE threads SET last_read_id = " // set current message as last one in thread
."(SELECT max(id) FROM messages) WHERE id = " // - incremented for each new message
."(SELECT max(id) FROM threads);"; // Final update to set the latest msg id for this thread.
}
}
echo "BEGIN;\n\n";
foreach($users as $user) {
if (!array_key_exists('user', $user)) continue;
echo $user['user']."\n";
echo $user['thread']."\n";
echo $user['thread_members'];
foreach($user['msgs'] as $msg) {
echo $msg."\n";
}
echo $user['msgs_upd']."\n";
}
//Fix sender_id for outbound messages
echo "update messages set sender_id = $me_id WHERE direction = -1;\n";
//Admire your handiwork (or weep)
echo ".mode column\n";
echo "SELECT * FROM users;\n";
echo "SELECT * FROM threads;\n";
echo "SELECT * FROM messages ORDER BY sender_id;\n";
echo "\nCOMMIT;\n";
