None of my SQL tables has any data for conversation including ofMessageArchive.
Under C:\Program Files\Openfire\plugins\monitoring\database I see import_sqlserver.sql and monitoring_sqlserver.sql.
import_sqlserver.sql has the following content:
TRUNCATE TABLE ofConversation;
INSERT INTO ofConversation
(conversationID, room, isExternal, startDate, lastActivity, messageCount)
SELECT conversationID, room, isExternal, startDate, lastActivity, messageCount
FROM entConversation;
TRUNCATE TABLE ofConParticipant;
INSERT INTO ofConParticipant
(conversationID, joinedDate, leftDate, bareJID, jidResource, nickname)
SELECT conversationID, joinedDate, leftDate, bareJID, jidResource, nickname
FROM entConParticipant;
TRUNCATE TABLE ofMessageArchive;
INSERT INTO ofMessageArchive
(conversationID, fromJID, toJID, sentDate, body)
SELECT conversationID, fromJID, toJID, sentDate, body
FROM entMessageArchive;
TRUNCATE TABLE ofRRDs;
INSERT INTO ofRRDs
(id, updatedDate, bytes)
SELECT id, updatedDate, bytes
FROM entRRDs;
monitoring_sqlserver.sql has the following content:
/* $Revision$ /
/ $Date$ */
INSERT INTO ofVersion (name, version) VALUES (‘monitoring’, 0);
CREATE TABLE ofConversation (
conversationID BIGINT NOT NULL,
room NVARCHAR(1024) NULL,
isExternal TINYINT NOT NULL,
startDate BIGINT NOT NULL,
lastActivity BIGINT NOT NULL,
messageCount INT NOT NULL,
CONSTRAINT ofConversation_pk PRIMARY KEY (conversationID)
);
CREATE INDEX ofConversation_ext_idx ON ofConversation (isExternal);
CREATE INDEX ofConversation_start_idx ON ofConversation (startDate);
CREATE INDEX ofConversation_last_idx ON ofConversation (lastActivity);
CREATE TABLE ofConParticipant (
conversationID BIGINT NOT NULL,
joinedDate BIGINT NOT NULL,
leftDate BIGINT NULL,
bareJID NVARCHAR(255) NOT NULL,
jidResource NVARCHAR(255) NOT NULL,
nickname NVARCHAR(255) NULL
);
CREATE INDEX ofConParticipant_conv_idx ON ofConParticipant (conversationID, bareJID, jidResource, joinedDate);
CREATE INDEX ofConParticipant_jid_idx ON ofConParticipant (bareJID);
CREATE TABLE ofMessageArchive (
conversationID BIGINT NOT NULL,
fromJID NVARCHAR(1024) NOT NULL,
toJID NVARCHAR(1024) NOT NULL,
sentDate BIGINT NOT NULL,
body NTEXT
);
CREATE INDEX ofMessageArchive_con_idx ON ofMessageArchive (conversationID);
CREATE TABLE ofRRDs (
id NVARCHAR(100) NOT NULL,
updatedDate BIGINT NOT NULL,
bytes IMAGE NULL,
CONSTRAINT ofRRDs_pk PRIMARY KEY (id)
);
Thanks,
Mike