Wednesday 31 December 2008

Dumping Emesene Chat Logs to HTML

Emesene is a free GTK based client for the Microsoft Live Messenger network. If you do not need the multi-protocol support of Pidgin, Emesene is a nice tool to have. It is very customizable and emulates WLM pretty closely.
One drawback of Emesene is the way it stores chat logs. First, a plugin named "Logger" needs to be enabled for logging to work. The Logger plugin stores the chats in an SQLite database which makes it very difficult to obtain the chat logs back. As far as I could understand, there are two ways to view the chat logs:

1. Enable the Eval plugin and execute the following command in a chat window
/eval out(controller.pluginManager.getPlugin("Logger").get_last_message("name@hotmail.com", 15))
This effectively shows the last 15 messages received from the user with the email address "name@hotmail.com". Unfortunately, the displayed messages are not formatted nicely and it is very difficult to follow the conversation this way. (For interested Python hackers, more info can be found here.)

2. Browse the SQLite database manually.
If your WLM account is me@hotmail.com, then the chat logs will be stored in "~/.config/emesene1.0/me_hotmail_com/cache/me@hotmail.com.db". You can browse this file by using a tool like sqliteman. Simply run
sudo yum install sqliteman to install it from the repositories. Then you can invoke it from the command line by simply typing "sqliteman". Unfortunately since the tables are normalized, it is very difficult to follow a conversation this way. You can write a SQL query to do a join between the tables so that the conversation is more readable, but this is simply unacceptable for normal users who may not be familiar with SQL.

Since none of the above methods were satisfactory for exporting an Emesene chat log; I wrote a simple Perl script to read the database and dump the conversations in HTML format. The script takes three arguments.
1. Full path to the Emesene SQLite database file. (eg. ~/.config/emesene1.0/me_hotmail_com/cache/me@hotmail.com.db)
2. Email address of the other participant. Partial strings are accepted. (eg. If you want to dump all the conversations with some_person@hotmail.com, you can simply type some_person)
3. Path to output the HTML file. (Eg. ~/tmp/my_chat_log.html)

So for example, to dump all conversations between you and some_person@hotmail.com, the script should be invoked as follows:
./emlog.pl ~/.config/emesene1.0/me_hotmail_com/cache/me\@hotmail.com.db some_person my_chats_wth_some_person.html

To access the SQLite database, this script uses DBD::SQLite module from CPAN. You may need to install it first using the command:
sudo cpan -i DBD::SQLite
The script can be downloaded from here. It is distributed under the Apache license, so feel free to modify it to your own needs.

Source Code:


#!/usr/bin/perl
################################################################################
# Copyright 2008 Charith K. Ellawala
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###############################################################################

use DBI qw(:sql_types);


# check command line arguments
if($#ARGV != 2)
{
die("Usage: emlog.pl <path_to_db_file> <participant_email> <output_file>\n");

}

# assign args to meaningful names
my($dbfile,$email,$outfile) = @ARGV;

# open a connection to the db

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","", {RaiseError => 1}) or die $DBI::errstr;


# prepare the SQL and execute it
my $sth = $dbh->prepare("SELECT DISTINCT c.id, datetime(c.started,'unixepoch') FROM conversation c, conversation_event ce WHERE c.id = ce.id_conversation AND ce.id_user = (SELECT id FROM user WHERE account LIKE ?) ORDER BY c.started");
$sth->bind_param(1,"\%$email\%",SQL_VARCHAR);

$sth->execute();

# open the file for output
open(FH,">$outfile") or die "Unable to open $outfile for writing";


# HTML header. Change values here to customize the look
my $html = <<END;
<html>
<head>
<title>Emesene Chat Log</title>

<style>
body
{
font-family:Sans;
font-size:12px;
}

.conv0
{
background-color:#E0E0E0;
border:1px solid black;
padding:5px;
}


.conv1
{
background-color:#FFFFFF;
border:1px solid black;
padding:5px;
}

.user0
{
color:blue;
padding:3px;
display:block;
border-bottom:1px dashed gray;
}


.user1
{
color:red;
padding:3px;
display:block;
border-bottom:1px dashed gray;
}
</style>
</head>
<body>

END
print FH $html;

# iterate through the conversations
my ($cid, $uid);
$cid = $uid = 0;


while(($id,$time) = $sth->fetchrow_array)
{
$uid = 0;
print FH "<div class=\"conv$cid\">";
print FH "<h3>Conversation time: $time</h3><br/>\n";

# get the content of the conversation
my $sth2 = $dbh->prepare("SELECT u.account, ce.data, datetime(e.stamp,'unixepoch') FROM conversation_event ce, user u, event e WHERE ce.id_user = u.id AND ce.id_event = e.id AND ce.id_conversation = $id");
$sth2->execute();

my $prevacc = '';
while(($acc,$txt,$ts) = $sth2->fetchrow_array)

{
# bit of modulo logic to colourize the different participants
if($acc ne $prevacc)
{

$prevacc = $acc;
$uid = ($uid + 1) % 2;
}


# The first two lines of the chat are garbage. Get rid of them here
my @tmp = split(/\n/,$txt);
splice(@tmp,0,2);
$txt = join('<br/>',@tmp);
print FH "<span class=\"user$uid\"><i style=\"color:black;\">[$ts] $acc :</i><br/>&nbsp;&nbsp;&nbsp;&nbsp;$txt</span><br/>\n";
}

print FH "</div><br/>";
$sth2->finish();
$cid = ($cid + 1) % 2;

}

print FH "</body>\n</html>";
close(FH);

# we are done
$sth->finish();

$dbh->disconnect();







syntax highlighted by Code2HTML, v. 0.9.1

2 comments:

Anonymous said...

2013. This old post saved my life. Trust me, I don't know how to thank you. I'm crying now. You're a genius.

JanuZ said...

Wow, this still works?.
I am glad that it was useful to you.