powered by Jive Software

Open Archive: Server side message archive with support for XEP-0136


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.


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.

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

Please disregard. I just reinstalled and it is working. Not sure what happened there

Where can I get the source?

Oops, i get it…

To make it a bit easier for the next one looking for the source, here is the link to the subversion repo:


This sounds like a great plugin. I am looking forward to the time when it has Oracle database support! ,

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.

so if I convert that mysql script to ms sql… will the plugin then work with ms sql?

Yes that should work. Please report back if it worked and post the script so I can include it, too.

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.

Can you post the Mod that you did? I too am looking to use this with MS SQL

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?

Generally the queries are the same across all databases, they are hardcoded into the application.

You can have a look at them here.

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?

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

Hi jeffk…

I’m using the same environment (Openfire + MSSQL Server)… Did you get OpenArchive working against SQL Server?

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,



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,


endTime BIGINT,


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 (



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);

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.

Thanks for your contribution, I’ve added your script to SVN so it will be part of 1.0.5.