Open Archive: Server side message archive with support for XEP-0136.
Created on: Sep 4, 2007 5:53 PM by Stefan Reuter - Last Modified: Jan 18, 2008 1:04 PM by Stefan Reuter
Overview
Open Archive is a XEP-0136 complient server side message archive for the Openfire XMPP server.
Currently it supports automated message archiving and message retrieval through XEP-0136 compliant Jabber clients and a web UI for the administrator. Support for manual archiving and preferences is planned for future releases.
Open Archive requires either MySQL or the embedded HSQLDB, support for other databases might be added at a later point.
Update: Now we also support PostgreSQL and you can find a MSSQL DDL snippet in the comments below.
To install the plugin just rename it to archive.jar, place it into the plugins folder of your Openfire server and enable it in the Openfire console at Server Settings/Archive Settings.
The plugin is available at http://maven.reucon.com/projects/public/archive/
License: GNU General Public License, Version 2.0
If you are looking for compliance features see the Enterprise Edition which also includes support from Jive Software.
FAQ
Why am I getting duplicates of all conversations?
In contrast to the archiving feature of the Enterprise Edition that is targeted at archiving logs for compliance Open Archive is user centeric. Open Archive stores all conversations with a reference to a local user that owns the record. Users are allowed to retrieve and manage all conversations that they own.
This model results in a conversation between two local users to be stored twice because each of the users owns one copy of the conversation.
You can think of Open Archive as a server side implementation of the "history" that your Jabber clients usually store locally.
Why am I not getting correct localized texts in the admin UI but "???property???" instead?
Rename the plugin jar file to archive.jar before deploying it.
jeffk
says
in response to jeffk:
Please disregard. I just reinstalled and it is working. Not sure what happened there
Stefan Reuter
says
in response to Fábio Coelho:
To make it a bit easier for the next one looking for the source, here is the link to the subversion repo:
Rob Alexander
says:
This sounds like a great plugin. I am looking forward to the time when it has Oracle database support! 
,
Stefan Reuter
says
in response to Rob Alexander:
I currently don't have an Oracle instance to play with but if you translate the MySQL DDL script to Oracle it will probably work fine.
Jason Streit
says
in response to Stefan Reuter:
so if I convert that mysql script to ms sql... will the plugin then work with ms sql?
Stefan Reuter
says
in response to Jason Streit:
Yes that should work. Please report back if it worked and post the script so I can include it, too.
Jason Streit
says
in response to Stefan Reuter:
i seem to get duplicates of all conversations... like it takes the sending as a message and then the receiving as a message... so all conversations are in there twice... otherwise it seems to be working fine.
jeffk
says
in response to Jason Streit:
Can you post the Mod that you did? I too am looking to use this with MS SQL
jeffk
says
in response to Stefan Reuter:
The MySQL DDL script only contains the code to create the tables needed. Where is the search query code? Wouldn't that need to be modified as well?
Stefan Reuter
says
in response to jeffk:
Generally the queries are the same across all databases, they are hardcoded into the application.
You can have a look at them here.
jeffk
says
in response to Stefan Reuter:
Thanks for the quick reply. So all we need to do is modify the archive_mysql.sql and call it archive_sqlserver.sql ? If so, I will post the mod her so others can use it if needed.
Will it automatically know to use archive_sqlserver.sql if we have set OpenFire up to use MS SQL Server?
Stefan Reuter
says
in response to jeffk:
yes that's all you have to do. Openfire automatically pics up the correct sql file for your database product.
If you post it here I will also include it in the official distribtion so it will be part of the next release
Alipio Luiz
says
in response to jeffk:
Hi jeffk..
I'm using the same environment (Openfire + MSSQL Server).. Did you get OpenArchive working against SQL Server?
Jason Streit
says
in response to Alipio Luiz:
I got it working with MSSQL... here is the script I used... not sure if it is all right but it seems to work fine
CREATE TABLE archiveConversations (
conversationId BIGINT NOT NULL,
startTime BIGINT NOT NULL,
endTime BIGINT NOT NULL,
ownerJid VARCHAR(255) NOT NULL,
ownerResource VARCHAR(255),
withJid VARCHAR(255) NOT NULL,
withResource VARCHAR(255),
subject VARCHAR(255),
thread VARCHAR(255),
PRIMARY KEY (conversationId)
);
CREATE INDEX idx_archiveConversations_startTime ON archiveConversations (startTime);
CREATE INDEX idx_archiveConversations_endTime ON archiveConversations (endTime);
CREATE INDEX idx_archiveConversations_ownerJid ON archiveConversations (ownerJid);
CREATE INDEX idx_archiveConversations_withJid ON archiveConversations (withJid);
CREATE TABLE archiveParticipants (
participantId BIGINT NOT NULL,
startTime BIGINT NOT NULL,
endTime BIGINT,
jid VARCHAR(255) NOT NULL,
nick VARCHAR(255),
conversationId BIGINT NOT NULL,
PRIMARY KEY (participantId)
);
CREATE INDEX idx_archiveParticipants_conversationId ON archiveParticipants(conversationId);
CREATE INDEX idx_archiveParticipants_jid ON archiveParticipants(jid);
CREATE TABLE archiveMessages (
messageId BIGINT NOT NULL,
time BIGINT NOT NULL,
direction CHAR(4) NOT NULL,
type CHAR(15) NOT NULL,
subject VARCHAR(255),
body TEXT,
conversationId BIGINT NOT NULL,
PRIMARY KEY (messageId)
);
CREATE INDEX idx_archiveMessages_conversationId ON archiveMessages (conversationId);
CREATE INDEX idx_archiveMessages_time ON archiveMessages (time);
CREATE TABLE archivePrefItems (
username VARCHAR(64) NOT NULL,
jid VARCHAR(255),
saveMode INTEGER,
otrMode INTEGER,
expireTime BIGINT,
PRIMARY KEY (username,jid)
);
CREATE TABLE archivePrefMethods (
username VARCHAR(64) NOT NULL,
methodType VARCHAR(255) NOT NULL,
methodUsage INTEGER,
PRIMARY KEY (username,methodType)
);
INSERT INTO jive.jiveVersion (name, version) VALUES ('archive', 2);
jeffk
says
in response to Alipio Luiz:
We changed our plan and stayed with the current setup of MySQL. I will check with our database guy and see if he has time to submit a MSSQL MOD. I will post it here when and if I get it. It will be untested, but should be a good start.
Stefan Reuter
says
in response to Jason Streit:
Thanks for your contribution, I've added your script to SVN so it will be part of 1.0.5.
=Stefan
Blake
says:
Do any clients support XEP-0136 yet? Also, do you plan on providing an administrative interface to set/manage the negotiation of OTR modes between the server and clients?
Thanks,
-B
Stefan Reuter
says
in response to Blake:
Open Archive is an attempt to overcome the hen and egg problem of missing client/server support for XEP-0136. Up to now I am not aware of a client supporting the XEP.
Currently there are now plans to expand the features of Open Archive though that might change as soon as there is more interest.
Fernando Ribeiro
says:
I'm receiving "???login.title???" on title page.
Only this message have problem.
I have already renamed the plugin file to archive.jar.
Stefan Reuter
says
in response to Fernando Ribeiro:
That's a known issue but without functional implication
kael2xmpp
says
in response to Stefan Reuter:
"Open Archive is an attempt to overcome the hen and egg problem of missing client/server support for XEP-0136. Up to now I am not aware of a client supporting the XEP". Thank you very much for this plugin. It is a very great XMPP enhancement. Since few days, Synapse-IM, a (very cool) fork of Psi, supports Message Archiving in the SVN repository. "Currently there are now plans to expand the features of Open Archive though that might change as soon as there is more interest."
I, for one, would be very interested into archiving PubSub ATOM messages like the ones published @ xmpp:pubsub.ik.nu. I'm thinking to a mailbox-like GUI in which PubSub messages would be stored to avoid losing notifications and perhaps for an offline/disconnected mode.
Cheers.
Stefan Reuter
says
in response to kael2xmpp:
Cool that looks promising. Did you already have a chance to test it wit Open Archive?
kael2xmpp
says
in response to Stefan Reuter:
Yes, I'm currently using it with Openfire 3.4.4 and Archive 1.0.4 and it works well, except sometimes where it seems buggy but no sure (actually, it displays that some messages exist but they're not seen then).
Yeah, that really looks promising.
Stefan Reuter
says
in response to kael2xmpp:
Tried to compile an run it from svn trunk on Ubuntu 7.10 64bit but it dumped core as soon as I tried to add a new account. Are there binary snapshots somewhere?
Alipio Luiz
says
in response to Jason Streit:
Hey jstreit12,
Thank you to share this MOD... It works like a charm.. 
kael2xmpp
says
in response to Stefan Reuter:
Unfortunately, there's no recent binary snapshots.
I'm running SVNed Synapse-IM on Ubuntu Gusty and it works normally (except some dev bugs).
Odd it crashes on 64bit PC.
kael2xmpp
says
in response to Stefan Reuter:
You may want to try rev 226, now.
The next release will probably be available in one week, and there might be a binary snapshot for 64bits PC.
Andrzej Wójcik
says
in response to Stefan Reuter:
Hi, I'm developer of Synapse-IM. I've just tested my client on Ubuntu 7.10 64 bit without any problems (except I needed to get to know how to install any thing under Ubuntu). After that I compiled and launched Synapse-IM. It created account without problem.
But I knew where is "bug". You did not launched
sudo make install
and QCA 2.0 library cannot find plugins.
If you will have any problems with Synapse-IM let me know
Stefan Reuter
says
in response to Andrzej Wójcik:
Yes it turned out it was the missing "make install".
Works quite well now and really cool to finally see a first client in the wild
kael2xmpp
says:
I'd have several questions :
1. Is there a way to export archives ? I'd like to reinstall my system and save the archives file (currently using the embedded DB). This would a great feature to enhance the plugin.
2. When querying an
<link rel="alternate" href="http://rss.slashdot.org/r/Slashdot/slashdotatom/3/226047775/article.pl" type="text/html" />
<link rel="alternate" href="http://rss.slashdot.org/r/Slashdot/slashdotatom/3/226558395/article.pl" type="text/html" />
(with offline capability amongst other features).
Stefan Reuter
says
in response to kael2xmpp:
ad 1: Currently there is no such feature but you can export the tables at the db level and reimport into the new database. Then you can rebuild the index and everything should be fine again.
ad 2: There is a feature element when the connection is established. Does the XEP also require a disco item?
If it is and Open Archive is still missing it, just open a bug at http://jira.reucon.org/browse/OA
ad 3: In general this is possible, its just interesting how we could map this to the concept of a conversation and how (if) we would display that in the conversation browser on the UI. If you'ld like to add more information feel free to open a feature request on jira.
Sc00by
says:
Love the plugin! but I am having a few problems. I am receiving an 'error' when entering a search field and clicking the rebuild index button. Conversations are split and the header for the main page has "Openfire ???login.title???. I think it may have something to do with the version of openfire. I put the latest version on (3.4.5) and then found this plugin to play with so Im not sure if it would work better on an earlier version. Any ideas?
Does this log Conference rooms too,
I briefly enabled this but it started throwing a bunch of errors. Are there any known issues or conflicts. I also tried re-indexing the archive and that gave errors.
I am using the latest Openfire and mySQL
just curious