Sqlite3 integration

Openfire Openfire Support
1

Hi all.

I recently tested whether OpenFire can use SQLite as a database. It seems it’s quite easy to set up:

  1. Get sqlite lib at https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.7.2.jar and put it to /opt/openfire/lib.

  2. Create database:

sqlite3 /path/to/openfire.db < /opt/openfire/resources/database/openfire_postgresql.sql

This will result in:

Error: near line 86: near "CONSTRAINT": syntax error

To fix this you should connect to the database and do:

pragma foreign_keys=on;
drop table ofRosterGroups;
CREATE TABLE "ofRosterGroups" (
  rosterID integer NOT NULL,
  rank integer not null,
  groupName VARCHAR(255) not null,
  constraint ofRosterGroups_pk primary key (rosterID, rank),
  foreign key (rosterID) references ofRoster(rosterID)
);
  1. When configuring service select:
  • Database Driver Preset: PostgreSQL
  • JDBC driver class: org.sqlite.JDBC
  • Database URL: jdbc:sqlite:/path/to/openfire.sqlite

The only bad thing I saw yet about this set up is:

2013.02.26 15:44:22 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - [SQLITE_ERROR] SQL error or missing database (near “ofPubsubItem”: syntax error)

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near “ofPubsubItem”: syntax error)

at org.sqlite.DB.newSQLException(DB.java:383)

at org.sqlite.DB.newSQLException(DB.java:387)

at org.sqlite.DB.throwex(DB.java:374)

at org.sqlite.NativeDB.prepare(Native Method)

at org.sqlite.DB.prepare(DB.java:123)

at org.sqlite.PrepStmt.(PrepStmt.java:42)

at org.sqlite.Conn.prepareStatement(Conn.java:404)

at org.sqlite.Conn.prepareStatement(Conn.java:399)

at org.sqlite.Conn.prepareStatement(Conn.java:383)

at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.logicalcobwebs.proxool.WrappedConnection.invoke(WrappedConnection.java:162)

at org.logicalcobwebs.proxool.WrappedConnection.intercept(WrappedConnection.java:8 7)

at $java.sql.Wrapper$$EnhancerByProxool$$ab743499.prepareStatement()

at org.jivesoftware.database.ProfiledConnection.prepareStatement(ProfiledConnectio n.java:608)

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPers istenceManager.java:1748)

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPers istenceManager.java:61)

at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersisten ceManager.java:259)

at java.util.TimerThread.mainLoop(Timer.java:555)

at java.util.TimerThread.run(Timer.java:505)

2

from my work with sqlite in java (i used sqlite4java library i think) it required some radical changes to how queries were run… which may be the exceptions you see thrown?

For example, in normal sql

PreparedStatement ps = connection.prepareStatement(“YOUR STATEMENT”);

but in sqlite i had to do

SQLiteStatement st = connection.prepare(“YOU STATEMENT”);

:-/

3

I didn’t say it can’t work, it works perfectly. I can add users, install plugins (like FastPath and hazelcast), connect and chat.

When installing FastPath installation process correctly creates all database structure, demo user, workgroups etc.