#!/usr/bin/perl use strict; use warnings; use Time::Piece; my %sms; my $numsms = 0; # Dump the SMS from an N900 backup file: # 1. Open the backup folder and use an unpacker to open comm_and_cal.zip. # 2. Navigate to /Root/home/user/.rtcom-eventlogger/ in the archive. # 3. Open the backup.tgz archive. # 4. Extract el-v1.db and open it using "SQLite database browser". # 5. Select "File" -> "Export" -> "Table as CSV file". # 6. Choose table "Events" and store the file. # The file now contains the SMS along with some other events. Double # quotes (") in the SMS text are replaced by "" (double double quotes) # and new lines are still present. This script takes care of the former # and replaces them by '' (two apostrophes), but it doesn't handle the # latter. This makes some quasi-manual preprocessing necessary, which # can be done using the Geany text editor: # Replace all ([^"])\n by \1 to get rid of wrapped lines. Repeat this step # until the pattern is not found anymore. # If you ignore this step, multi-line messages will be silently discarded. # When this is done, the script can be run by supplying the converted # CSV file as standard input and redirecting standard output to another # CSV file: # ./n900_smsconv.pl < sms_in.csv > sms_out.csv # An application like "SMS Tools" can then be used to import the SMS # into your Android phone (choose the nokia format when asked). # The database contains some definitions for the various IDs, those # are shown in the following lists: # service_id field: # 1: RTCOM_EL_SERVICE_CALL # 2: RTCOM_EL_SERVICE_CHAT # 3: RTCOM_EL_SERVICE_SMS # event_type_id field: # 1: RTCOM_EL_EVENTTYPE_CALL # 2: RTCOM_EL_EVENTTYPE_CALL_MISSED # 3: RTCOM_EL_EVENTTYPE_CALL_VOICEMAIL # 4: RTCOM_EL_EVENTTYPE_CHAT_MESSAGE # 5: RTCOM_EL_EVENTTYPE_CHAT_NOTICE # 6: RTCOM_EL_EVENTTYPE_CHAT_ACTION # 7: RTCOM_EL_EVENTTYPE_CHAT_AUTOREPLY # 8: RTCOM_EL_EVENTTYPE_CHAT_JOIN # 9: RTCOM_EL_EVENTTYPE_CHAT_LEAVE # 10: RTCOM_EL_EVENTTYPE_CHAT_TOPIC # 11: RTCOM_EL_EVENTTYPE_SMS_MESSAGE # flags field: # 1: RTCOM_EL_FLAG_CHAT_GROUP # 2: RTCOM_EL_FLAG_CHAT_ROOM # 3: RTCOM_EL_FLAG_OPAQUE # 4: RTCOM_EL_FLAG_OFFLINE # 5: RTCOM_EL_FLAG_SMS_PENDING # 6: RTCOM_EL_FLAG_SMS_TEMPORARY_ERROR # 7: RTCOM_EL_FLAG_SMS_PERMANENT_ERROR foreach my $csvline (<STDIN>) { chomp $csvline; if ($csvline =~ m/ (\d+), # 1 id e.g. "3131" (3), # 2 service_id e.g. "3" (7), # 3 event_type_id e.g. "11" (\d+), # 4 storage_time e.g. "1396603341" (\d+), # 5 start_time e.g. "1396603339" (\d+), # 6 end_time e.g. "1396603341" or "0" (\d+), # 7 is_read e.g. "1" (\d+), # 8 flags e.g. "0" (\d+), # 9 bytes_sent e.g. "0" (\d+), # 10 bytes_received e.g. "0" ([^"]*), # 11 local_uid e.g. "ring tel ring" ([^"]*), # 12 local_name e.g. "<SelfHandle>" ([^"]*), # 13 remote_uid e.g. "+4179..." ([^"]*), # 14 channel e.g. "" (.*?), # 15 free_text e.g. "abcdef" ([^"]*), # 16 group_uid e.g. "6288879" (\d+), # 17 outgoing e.g. "0" or "1" (\d+) # 18 mc_profile e.g. "0" or "1" $/px) { $sms{$numsms}{'sent'} = int($17); $sms{$numsms}{'time'} = $5; $sms{$numsms}{'number'} = $13; $sms{$numsms}{'msg'} = $15 =~ s/""/''/ro; ++$numsms; }else{ print STDERR "not parsed: ".$csvline."\n"; } } print STDERR "Parsed $numsms messages.\n"; foreach my $key (sort {$a<=>$b} keys %sms) { print '"sms",'; my $number = $sms{$key}{'number'}; my $time = localtime($sms{$key}{'time'})->strftime('%Y.%m.%d %H:%M'); my $text = $sms{$key}{'msg'}; if ($sms{$key}{'sent'}) { print '"SENT",'; print '"",'; print "\"$number\","; } else { print '"READ,RECEIVED",'; print "\"$number\","; print '"",'; } print '"",'; print "\"$time\","; print '"",'; print "\"$text\"\n"; }