I am in the process of developing a plugin, which implements JEP-0136: Message Archiving http://www.jabber.org/jeps/jep-0136.html. I am planning on storing the message archives in the database (in new, non-existant tables).
What is the protocol for creating these tables? Do we require the server administrator to create the tables (via an SQL script) prior to installing the plugin? Do we try to create the tables (via the plugin) if they don’'t already exist?
Hi Michael,
I am in the process of developing a plugin, which implements JEP-0136: Message
Archiving http://www.jabber.org/jeps/jep-0136.html. I am planning on storing the
message archives in the database (in new, non-existant tables).
Sounds like a great idea.
What is the protocol for creating these tables?
What we’'ve done is just grab a database connection and execute the table creation sql.
Do we require the server administrator to create the tables (via an SQL script)
prior to installing the plugin?
In most cases no, but it depends on how locked down the permissions are on the database. While it is pretty rare, some dba’'s might prevent the account that is used to access the existing tables to create new ones.
Do we try to create the tables (via the plugin) if they don’'t already exist?
Yes, or you can use the JiveGlobals setProperty() and getPropety() methods to indicate whether or not you’'ve previously created the tables.
Hope that helps,
Ryan
Ryan said:
What is the protocol for creating these tables?
What we’'ve done is just grab a database connection
and execute the table creation sql.
For those developers interested in doing something similar, here are some of my notes:
Instead of embedding table creation SQL in my Java code, I am planning on creating SQL scripts and include them in my plugin JAR. Since the plugin is initialized with the path to the plugin files, I should be able to grab the SQL scripts from the file system.
Then, I will execute the contents of the SQL script by doing something similar to org.jivesoftware.database.DbConnectionManager#upgradeDatabase
Would it make sense to extract a portion of that method, upgradeDatabase, into a new, public, static method on DbConnectionManager, e.g., executeSQL(java.io.Reader, java.sql.Connection) ?
Hi,
"Instead of embedding table creation SQL in my Java code, I am planning on creating SQL scripts and include them in my plugin JAR. Since the plugin is initialized with the path to the plugin files, I should be able to grab the SQL scripts from the file system "[/i] sounds very good.
I think this is the way also Wildfire should create database tables. I read http://www.jivesoftware.org/builds/wildfire/docs/latest/documentation/database.h tml today for the first time and I must wonder why every database except the embedded database (that’‘s the one I’'m currently using) must be setup manually using scripts.
LG