trying to solve IT problems

I have something with wiki software. The concept attracts me. This resulted in me starting an implementation for a wiki renderer (staticwiki), but I needed something fully integrated earlier.

I had heard of xwiki before, but this article about the “ease” of extending xwiki made me interested to have a closer look.

Of course, I want this on my own terms, running inside JBoss (for easy integration with other applications) and running on firebird (my database of choice). Unfortunately, this caused me a bit more problems than I expected or hoped.

However, here is a way to do it, for on the 1.1.2 version of xwiki enterprise and JBoss 4.2.2.

create database

The xwiki distribution normally tries to create the database structure automatically. Unfortunately, this does not work when deploying using firebird. The ddl which is generated by hibernate contains many very long varchar fields which are beyond the limits imposed by firebird, so these are replaced by character blob fields (clob). The indexes are also on the long side, which makes firebird 2.0 mandatory and requires a larger page size when using the UTF-8 character set in the database.

The following script works though you obviously need to adjust the database location.
isql -u myusername -p mypassword

CREATE DATABASE "/data/xwiki/xwiki.fdb" PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8;

create table feeds_aggregatorgroup (agg_id integer not null, 
agg_name blob sub_type 1, primary key (agg_id));
create table feeds_aggregatorurl (agg_id integer not null, 
agg_name blob sub_type 1, agg_url blob sub_type 1, agg_date timestamp, 
agg_nb integer, primary key (agg_id));
create table feeds_aggregatorurlgroups (agl_id integer not null, 
agl_value varchar(255), agl_number integer not null, 
primary key (agl_id, agl_number));
create table feeds_feedentry (fee_id integer not null, 
fee_title blob sub_type 1, fee_author blob sub_type 1, 
fee_feedurl blob sub_type 1, fee_feedname blob sub_type 1, 
fee_url blob sub_type 1, fee_category blob sub_type 1, 
fee_content blob sub_type 1, fee_fullcontent blob sub_type 1, 
fee_xml blob sub_type 1, fee_date timestamp, fee_flag integer default 0, 
fee_read integer default 0, primary key (fee_id));
create table feeds_feedentrytags (fet_id integer not null, 
fet_value varchar(255), fet_number integer not null, 
primary key (fet_id, fet_number));
create table feeds_keyword (key_id integer not null, key_name blob sub_type 1, 
key_group blob sub_type 1, primary key (key_id));

create table xwikiattachment (XWA_ID numeric(18,0) not null, 
XWA_DOC_ID numeric(18,0), XWA_FILENAME varchar(255) not null, 
XWA_SIZE integer, XWA_DATE timestamp not null, XWA_AUTHOR varchar(255), 
XWA_VERSION varchar(255) not null, XWA_COMMENT varchar(255), 
primary key (XWA_ID));
create table xwikiattachment_archive (XWA_ID numeric(18,0) not null, 
XWA_ARCHIVE blob, primary key (XWA_ID));
create table xwikiattachment_content (XWA_ID numeric(18,0) not null, 
XWA_CONTENT blob not null, primary key (XWA_ID));
create table xwikibooleanclasses (XWN_ID integer not null, 
XWN_NAME varchar(255) not null, XWN_DISPLAYTYPE varchar(20), 
primary key (XWN_ID, XWN_NAME));
create table xwikiclasses (XWO_ID integer not null, 
XWO_NAME varchar(255) not null, XWO_CUSTOM_MAPPING blob sub_type 1, 
XWO_CUSTOM_CLASS varchar(255), XWO_VALIDATION_SCRIPT varchar(255), 
primary key (XWO_ID));

create table xwikiclassesprop (XWP_ID integer not null, 
XWP_NAME varchar(255) not null, XWP_PRETTYNAME varchar(255), 
XWP_CLASSTYPE varchar(255), XWP_UNMODIFIABLE smallint, 
XWP_NUMBER integer, XWP_CUSTOMDISPLAY blob sub_type 1, 
XWP_VALIDATION_REGEXP varchar(255), primary key (XWP_ID, XWP_NAME));
create table xwikicomments (XWC_ID integer not null, XWC_AUTHOR varchar(255), 
XWC_HIGHLIGHT blob sub_type 1, XWC_COMMENT blob sub_type 1, 
XWP_REPLYTO integer, XWP_DATE timestamp, primary key (XWC_ID));
create table xwikidateclasses (XWS_ID integer not null, 
XWS_NAME varchar(255) not null, XWS_SIZE integer, 
XWS_EMPTY_IS_TODAY integer, XWS_DATE_FORMAT varchar(255), 
primary key (XWS_ID, XWS_NAME));
create table xwikidates (XWS_ID integer not null, 
XWS_NAME varchar(255) not null, XWS_VALUE timestamp, 
primary key (XWS_ID, XWS_NAME));

create table xwikidblistclasses (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, XWL_DISPLAYTYPE varchar(20), 
XWL_MULTISELECT smallint, XWL_SIZE integer, XWL_RELATIONAL smallint, 
XWL_SQL varchar(2000), primary key (XWL_ID, XWL_NAME));
create table xwikidoc (XWD_ID numeric(18,0) not null, 
XWD_FULLNAME varchar(255) not null, XWD_NAME varchar(255) not null, 
XWD_TITLE varchar(255) not null, XWD_LANGUAGE varchar(5), 
XWD_DEFAULT_LANGUAGE varchar(5), XWD_TRANSLATION integer not null, 
XWD_DATE timestamp not null, XWD_CONTENT_UPDATE_DATE timestamp not null, 
XWD_CREATION_DATE timestamp not null, XWD_AUTHOR varchar(255) not null, 
XWD_CONTENT_AUTHOR varchar(255) not null, XWD_CREATOR varchar(255) not null, 
XWD_WEB varchar(255) not null, XWD_CONTENT blob sub_type 1 not null, 
XWD_VERSION varchar(255) not null, XWD_CUSTOM_CLASS varchar(255) not null, 
XWD_PARENT varchar(511) not null, XWD_CLASS_XML blob sub_type 1, 
XWD_ELEMENTS integer, XWD_DEFAULT_TEMPLATE varchar(255) not null, 
XWD_VALIDATION_SCRIPT varchar(255) not null, 
XWD_COMMENT varchar(1023) not null, XWD_ARCHIVE blob sub_type 1, 
primary key (XWD_ID));

create table xwikidoubles (XWD_ID integer not null, 
XWD_NAME varchar(255) not null, XWD_VALUE double precision, 
primary key (XWD_ID, XWD_NAME));
create table xwikifloats (XWF_ID integer not null, 
XWF_NAME varchar(255) not null, XWF_VALUE float, 
primary key (XWF_ID, XWF_NAME));
create table xwikiintegers (XWI_ID integer not null, 
XWI_NAME varchar(255) not null, XWI_VALUE integer, 
primary key (XWI_ID, XWI_NAME));
create table xwikilargestrings (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, XWL_VALUE blob sub_type 1, 
primary key (XWL_ID, XWL_NAME));
create table xwikilinks (XWL_DOC_ID numeric(18,0) not null, 
XWL_LINK varchar(255) not null, XWL_FULLNAME varchar(255), 
primary key (XWL_DOC_ID, XWL_LINK));
create table xwikilistitems (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, XWL_VALUE varchar(255), 
XWL_NUMBER integer not null, primary key (XWL_ID, XWL_NAME, XWL_NUMBER));
create table xwikilists (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, primary key (XWL_ID, XWL_NAME));
create table xwikilock (XWL_DOC_ID numeric(18,0) not null, 
XWL_AUTHOR varchar(255), XWL_DATE timestamp not null, 
primary key (XWL_DOC_ID));
create table xwikilongs (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, XWL_VALUE numeric(18,0), 
primary key (XWL_ID, XWL_NAME));
create table xwikinumberclasses (XWN_ID integer not null, 
XWN_NAME varchar(255) not null, XWN_SIZE integer, XWN_NUMBERTYPE varchar(20), 
primary key (XWN_ID, XWN_NAME));
create table xwikiobjects (XWO_ID integer not null, XWO_NUMBER integer, 
XWO_NAME varchar(255) not null, XWO_CLASSNAME varchar(255) not null, 
primary key (XWO_ID));
create table xwikipreferences (XWP_ID integer not null, 
XWP_LANGUAGE varchar(255), XWP_DEFAULT_LANGUAGE varchar(255), 
XWP_MULTI_LINGUAL integer, XWP_AUTHENTICATE_EDIT integer, 
XWP_AUTHENTICATE_VIEW integer, XWP_AUTH_ACTIVE_CHECK integer, 
XWP_BACKLINKS integer, XWP_SKIN varchar(255), XWP_STYLESHEET varchar(255), 
XWP_STYLESHEETS varchar(255), XWP_EDITOR varchar(255), 
XWP_EDITBOX_WIDTH varchar(255), XWP_EDITBOX_HEIGHT varchar(255), 
XWP_WEBCOPYRIGHT varchar(255), XWP_TITLE varchar(255), 
XWP_VERSION varchar(255), XWP_MENU blob sub_type 1, XWP_META blob sub_type 1, 
XWP_USE_EMAIL_VERIFICATION integer, XWP_SMTP_SERVER varchar(255), 
XWP_ADMIN_EMAIL varchar(255), XWP_VALIDATION_EMAIL_CONTENT blob sub_type 1, 
XWP_CONFIRMATION_EMAIL_CONTENT blob sub_type 1, 
XWP_INVITATION_EMAIL_CONTENT blob sub_type 1, XWP_MACROS_LANGUAGE varchar(255), 
XWP_MACROS_VELOCITY varchar(255), XWP_MACROS_GROOVY varchar(255), 
XWP_MACROS_WIKI2 varchar(255), XWP_MACROS_MAPPING blob sub_type 1, 
XWP_NOTIFICATION_PAGES varchar(255), XWP_LEFT_PANELS varchar(2000), 
XWP_RIGHT_PANELS varchar(2000), XWP_SHOW_LEFT_PANELS integer, 
XWP_SHOW_RIGHT_PANELS integer, XWP_PAGE_WIDTH varchar(255), 
XWP_LANGUAGES varchar(255), XWP_REGISTRATION_ANONYMOUS varchar(255), 
XWP_REGISTRATION_REGISTERED varchar(255), XWP_EDIT_ANONYMOUS varchar(255), 
XWP_EDIT_REGISTERED varchar(255), XWP_COMMENT_ANONYMOUS varchar(255), 
XWP_COMMENT_REGISTERED varchar(255), XWP_DOCUMENT_BUNDLES varchar(255), 
primary key (XWP_ID));
create table xwikiproperties (XWP_ID integer not null, 
XWP_NAME varchar(255) not null, XWP_CLASSTYPE varchar(255), 
primary key (XWP_ID, XWP_NAME));
create table xwikislistclasses (XWL_ID integer not null, 
XWL_NAME varchar(255) not null, XWL_DISPLAYTYPE varchar(20), 
XWL_MULTISELECT smallint, XWL_SIZE integer, XWL_RELATIONAL smallint, 
XWL_VALUES varchar(2000), primary key (XWL_ID, XWL_NAME));
create table xwikistatsdoc (XWS_ID integer not null, XWS_NUMBER integer, 
XWS_NAME varchar(255) not null, XWS_CLASSNAME varchar(255) not null, 
XWS_ACTION varchar(255) not null, XWS_PAGE_VIEWS integer, 
XWS_UNIQUE_VISITORS integer, XWS_PERIOD integer, XWS_VISITS integer, 
primary key (XWS_ID));
create table xwikistatsreferer (XWR_ID integer not null, XWR_NUMBER integer, 
XWR_NAME varchar(255) not null, XWR_CLASSNAME varchar(255) not null, 
XWR_REFERER varchar(255) not null, XWR_PAGE_VIEWS integer, 
XWR_PERIOD integer, primary key (XWR_ID));
create table xwikistatsvisit (XWV_ID integer not null, XWV_NUMBER integer, 
XWV_NAME varchar(255) not null, XWV_CLASSNAME varchar(255) not null, 
XWV_IP varchar(32) not null, XWV_USER_AGENT varchar(255) not null, 
XWV_COOKIE varchar(255) not null, XWV_UNIQUE_ID varchar(255) not null, 
XWV_PAGE_VIEWS integer, XWV_PAGE_SAVES integer, XWV_DOWNLOADS integer, 
XWV_START_DATE timestamp, XWV_END_DATE timestamp, primary key (XWV_ID));
create table xwikistringclasses (XWS_ID integer not null, 
XWS_NAME varchar(255) not null, XWS_SIZE integer, XWS_ROWS integer, 
primary key (XWS_ID, XWS_NAME));
create table xwikistrings (XWS_ID integer not null, 
XWS_NAME varchar(255) not null, XWS_VALUE varchar(255), 
primary key (XWS_ID, XWS_NAME));

alter table feeds_aggregatorurlgroups add constraint FK7B845B068E772CC foreign key (agl_id) references feeds_aggregatorurl;
alter table feeds_feedentrytags add constraint FKBE0037834855150F foreign key (fet_id) references feeds_feedentry;
alter table xwikibooleanclasses add constraint FK6D139D269159A4BC foreign key (XWN_ID, XWN_NAME) references xwikiclassesprop;
alter table xwikidateclasses add constraint FK14DA8B9099E425B2 foreign key (XWS_ID, XWS_NAME) references xwikiclassesprop;
alter table xwikidates add constraint FKDEAEAB5D3433FD87 foreign key (XWS_ID, XWS_NAME) references xwikiproperties;
alter table xwikidblistclasses add constraint FK5A4B5F628DEF0AC0 foreign key (XWL_ID, XWL_NAME) references xwikiclassesprop;
alter table xwikidoubles add constraint FK5A1CD9A1A947AA5 foreign key (XWD_ID, XWD_NAME) references xwikiproperties;
alter table xwikifloats add constraint FKFB291FBF1DFF14A1 foreign key (XWF_ID, XWF_NAME) references xwikiproperties;
alter table xwikiintegers add constraint FK7F8AB31D231EFB9B foreign key (XWI_ID, XWI_NAME) references xwikiproperties;
alter table xwikilargestrings add constraint FK6661970F283EE295 foreign key (XWL_ID, XWL_NAME) references xwikiproperties;
alter table xwikilistitems add constraint FKC0862BA3FB72A11 foreign key (XWL_ID, XWL_NAME) references xwikilists;
alter table xwikilists add constraint FKDF23086D283EE295 foreign key (XWL_ID, XWL_NAME) references xwikiproperties;
alter table xwikilongs add constraint FKDF25AE4F283EE295 foreign key (XWL_ID, XWL_NAME) references xwikiproperties;
alter table xwikinumberclasses add constraint FK38FB15959159A4BC foreign key (XWN_ID, XWN_NAME) references xwikiclassesprop;
alter table xwikislistclasses add constraint FK17ED3B9D8DEF0AC0 foreign key (XWL_ID, XWL_NAME) references xwikiclassesprop;
alter table xwikistringclasses add constraint FK300D1FCD99E425B2 foreign key (XWS_ID, XWS_NAME) references xwikiclassesprop;
alter table xwikistrings add constraint FK2780715A3433FD87 foreign key (XWS_ID, XWS_NAME) references xwikiproperties;

prepare jboss

To assure you can access firebird from inside any of the applications deployed on jboss, you need to download the jaybird jdbc drivers. Put the “jaybird-2.1.2.jar” (or a more recent version) file which is in the zip in the “server/xxx/lib” directory on your jboss installation (where xxx is the jboss configuration you want to run, probably “default”).

prepare xwiki

Download the 1.2.2 version of xwiki enterprise, using the “war” distribution. Unzip this in a separate directory in the JBoss deploy directory (“server/xxx/deploy”) (I called this “xwiki-std-1.2.2.war”). Do make sure the directory name ends in “.war”.

Edit the xwiki configuration settings in “WEB-INF/xwiki.cfg”. You should uncomment “xwiki.temp.dir” and “xwiki.work.dir” and assure these directories exist and your running jboss can use them.

The database access needs to be configured. There are two options to install the database access, either using a datasource or direct connection managed by the application.

application managed connection

Edit the file “WEB-INF/hibernate.cfg.xml”, using the following database settings, replacing the username, password and database location with the settings you used to create the database.

    <!-- Firebird configuration. -->
    <property name="connection.url">jdbc:firebirdsql:localhost/3050://data/xwiki/xwiki.fdb?encoding=UNICODE_FSS<</property>
    <property name="connection.username">myusername</property>
    <property name="connection.password">mypassword</property>
    <property name="connection.driver_class">org.firebirdsql.jdbc.FBDriver</property>
    <property name="dialect">org.hibernate.dialect.FirebirdDialect</property>
    <property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
    <property name="connection.pool_size">2</property>
    <property name="statement_cache.size">2</property>
    <property name="hbm2ddl.auto">validate</property>
    <mapping resource="xwiki.hbm.xml"/>
    <mapping resource="feeds.hbm.xml"/>

using a datasource

Create the datasource in jboss. Put the following content in your JBoss deploy directory (server/xxx/deploy) under the name “xwiki-ds.xml”. Replace database location and uername/password with your settings.

<?xml version="1.0" encoding="UTF-8"?>

<!-- ==================================================================== -->
<!-- New ConnectionManager setup for firebird dbs using jca-jdbc xa driver-->
<!-- Build jmx-api (build/build.sh all) and view for config documentation -->
<!-- ==================================================================== -->

<connection-factories>

<local-tx-datasource>
    <jndi-name>jdbc/XWikiDS</jndi-name>

   <connection-url>jdbc:firebirdsql:localhost/3050:/data/xwiki/xwiki.fdb</connection-url>
   <driver-class>org.firebirdsql.jdbc.FBDriver</driver-class>

   <transaction-isolation>TRANSACTION_REPEATABLE_READ</transaction-isolation>
   <connection-property name="lc_ctype" type="java.lang.String">UNICODE_FSS</connection-property>
   <connection-property name="maxStatements">10</connection-property>
   <user-name>myusername</user-name>
   <password>mypassword</password>

   <min-pool-size>0</min-pool-size>
   <max-pool-size>200</max-pool-size>

   <blocking-timeout-millis>5000</blocking-timeout-millis>

   <idle-timeout-minutes>15</idle-timeout-minutes>
                          
   <check-valid-connection-sql>SELECT CAST(1 as INTEGER) FROM rdb$database</check-valid-connection-sql>
                              
   <track-statements>false</track-statements>

   <prepared-statement-cache-size>0</prepared-statement-cache-size>
                                  
   <metadata>                     
       <type-mapping>Firebird</type-mapping>
   </metadata>  
</local-tx-datasource>
                      
</connection-factories>

Now you need to assure this datasource is used. Inside the exploded xwiki directory, edit “WEB-INF/web.xml” to include the following (it probably is already there in comments).

  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/XWikiDS</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

You also need to configure Hibernate to apply these settings in the “WEB-INF/hibernate.cfg.xml” using the following

    <!-- Firebird datasource configuration. -->
    <property name="connection.datasource">java:/jdbc/XWikiDS</property>
    <property name="dialect">org.hibernate.dialect.FirebirdDialect</property>
    <property name="statement_cache.size">2</property>
    <property name="hbm2ddl.auto">none</property>
    <mapping resource="xwiki.hbm.xml"/>
    <mapping resource="feeds.hbm.xml"/>

set xwiki as context root

Though this step is obviously not required, it was necessary (and a stumbleblock) in my configuration. You need to tell jboss that the application is applied as root by creating (in the xwiki.war) “WEB-INF/jboss-web.xml with the following content

<jboss-web>
    <context-root>/</context-root>
</jboss-web>

In some cases you may also need to remove the “server/xxx/deploy/jboss-web.deployer/ROOT.war” directory.

To assure xwiki itself also “knows” the change, you should also edit the “WEB-INF/web.xml” file and clear the display name
<display-name></display-name>

get it running
Start jboss. Depending on your configuration, it may be needed to increase permgen and heap space (set the JAVA_OPTS environment variable to something like “-server -Xms382m -Xmx1024m -XX:MaxPermSize=200m”). You should now be able to surf to your local jboss iinstance and start creating your wiki content.