#!/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"
      "(11)",                 # 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  outgoing           e.g. "0" or "1"
      "(\d+)",                # 9  flags              e.g. "0"
      "(\d+)",                # 10 bytes_sent         e.g. "0"
      "(\d+)",                # 11 bytes_received     e.g. "0"
      "([^"]*)",              # 12 local_uid          e.g. "ring tel ring"
      "([^"]*)",              # 13 local_name         e.g. "<SelfHandle>"
      "([^"]*)",              # 14 remote_uid         e.g. "+4179..."
      "([^"]*)",              # 15 channel            e.g. ""
      "(.*?)",                # 16 free_text          e.g. "abcdef"
      "([^"]*)"               # 17 group_uid          e.g. "6288879"
      $/pxo)
  {
    $sms{$numsms}{'sent'} = int($8);
    $sms{$numsms}{'time'} = $5;
    $sms{$numsms}{'number'} = $14;
    $sms{$numsms}{'msg'} = $16 =~ s/""/''/ro;
    ++$numsms;
  }
}

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";
}

#!/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";
}

#!/usr/bin/perl
#
# Export Maemo N900 SMS to CSV
# Allows importing to Android phones using "Anything With SMS"
#
# Use this format when importing with Anything With SMS:
# "$(folder)","$(address)","$(dateyyyy-MM-dd HH:mm:ss)","$(body)"\s*
#
# Written by Alan
#
use strict;
use warnings;
use Text::CSV;
use DBI;
use POSIX;

# SQLite format from n900 (file: el-v1.db)
# From an exported backup, in comm_and_cal.zip -> Root/home/user/.rtcom-eventlogger/backup.tgz -> el-v1.db
#
# CREATE TABLE Events (
# id INTEGER PRIMARY KEY,
# service_id INTEGER NOT NULL,
# event_type_id INTEGER NOT NULL,
# storage_time INTEGER NOT NULL,
# start_time INTEGER NOT NULL,
# end_time INTEGER,
# is_read INTEGER DEFAULT 0,
# outgoing BOOL DEFAULT 0,
# flags INTEGER DEFAULT 0,
# bytes_sent INTEGER DEFAULT 0,
# bytes_received INTEGER DEFAULT 0,
# local_uid TEXT,
# local_name TEXT,
# remote_uid TEXT,
# channel TEXT,
# free_text TEXT,
# group_uid TEXT
# );

# Output columns in the order expected by import program (Anything with SMS)
my @out_cols = ('folders', 'number', 'time', 'msg');

die "Usage: $0 <sqlite input> <csv output>" unless $#ARGV == 1;

my ($dbfile, $csvfile) = @ARGV;

my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile, '', '', {RaiseError => 1, PrintError => 0});
my $csv = Text::CSV->new( { always_quote => 1, auto_diag => 1, sep => ',', binary => 1}) or die "Error creating CSV object: ", Text::CSV->error_diag;
open(my $csvh, '>', $csvfile) or die "Error opening $csvfile: $!";

my $sth = $dbh->prepare('select * from Events');
my $res = $sth->execute;

# assign column mapping
$csv->column_names(\@out_cols);

# Print CSV header
# Commented because Anything With SMS doesn't like it
# print header to csv
# $csv->print($csvh, \@out_cols);
# $csvh->print("\n");

my $count = 0;
while (my $in = $sth->fetchrow_hashref) {
	next unless $in->{'service_id'} eq '3';
	$count++;
	my $out;
	
	# convert some fields for csv output
	$out->{'type'} = 'sms';
	
	$out->{'x'} = '';
	# flags
	my @folders;
	if ($in->{'outgoing'}) {
		if ($in->{'flags'} eq '0') {
			push(@folders, 'sent');
		} elsif ($in->{'flags'} eq '5') {
			push(@folders, 'submit');
		} else {
			push(@folders, 'outgoing');
		}
	} else {
		if ($in->{'is_read'} eq '1') {
			push(@folders, 'read');
		} else {
			push(@folders, 'read');
		}
	}
	$out->{'folders'} = join(',', @folders);
	$out->{'number'} = $in->{'remote_uid'};
	$out->{'msg'} = $in->{'free_text'};
	
	# Anything with SMS doesn't support escaped quotes or newlines in CSV files
	# Replace double quotes with singles, and newlines with spaces
	if (defined $out->{'msg'}) {
		$out->{'msg'} =~ s{"}{'};
		$out->{'msg'} =~ s{\n}{ };
	}
	$out->{'time'} = strftime('%Y-%m-%d %H:%M:%S', localtime($in->{'start_time'}));
	
	$csv->print_hr($csvh, $out);
	$csvh->print("\n");
}
$dbh->disconnect;
close($csvh);
print "Exported $count messages\n";
exit(0);