<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<section id="JCR.JDBCDataContainerConfig">
  <title>JDBC Data Container Config</title>

  <para>eXo JCR persistent data container can work in two configuration
  modes:<itemizedlist>
      <listitem>
        <para><phrase>Multi-database</phrase>: One database for each workspace
        (used in standalone eXo JCR service mode)</para>
      </listitem>

      <listitem>
        <para><phrase>Single-database</phrase>: All workspaces persisted in
        one database (used in embedded eXo JCR service mode, e.g. in GateIn)</para>
      </listitem>
    </itemizedlist></para>

  <para>The data container uses the JDBC driver to communicate with the actual
  database software, i.e. any JDBC-enabled data storage can be used with eXo
  JCR implementation.</para>

  <para>Currently the data container is tested with the following
  configurations:<itemizedlist>
      <listitem>
        <para>MySQL 5.0.18 MYSQL Connector/J 5.0.8</para>
      </listitem>

      <listitem>
        <para>MySQL 5.1.36 MYSQL Connector/J 5.1.14</para>
      </listitem>

      <listitem>
        <para>PostgresSQL 8.2.4 JDBC4 Driver, Version 8.2-507</para>
      </listitem>

      <listitem>
        <para>PostgresSQL 8.3.7 JDBC4 Driver, Version 8.3-606</para>
      </listitem>

      <listitem>
        <para>Oracle DB 10g R2 (10.2.0.4), JDBC Driver Oracle 10g R2
        (10.2.0.4)</para>
      </listitem>

      <listitem>
        <para>Oracle DB 11g R1 (11.1.0.6.0), JDBC Driver Oracle 11g R1
        (11.1.0.6.0)</para>
      </listitem>

      <listitem>
        <para>Oracle DB 11g R2 (11.2.0.1.0), JDBC Driver Oracle 11g R2
        (11.2.0.1.0)</para>
      </listitem>

      <listitem>
        <para>DB2 9.7.4 IBM Data Server Driver for JDBC and SQLJ (JCC Driver)
        v.9.7</para>
      </listitem>

      <listitem>
        <para>MS SQL Server 2005 SP3 JDBC Driver 3.0</para>
      </listitem>

      <listitem>
        <para>MS SQL Server 2008 JDBC Driver 3.0</para>
      </listitem>

      <listitem>
        <para>Sybase 15.0.3 ASE Driver: Sybase jConnect JDBC driver v7 (Build
        26502)</para>
      </listitem>

      <listitem>
        <para>HSQLDB (2.0.0)</para>
      </listitem>
    </itemizedlist></para>

  <para>Each database software supports ANSI SQL standards but also has its
  own specifics. So, each database has its own configuration in eXo JCR as a
  database dialect parameter. If you need a more detailed configuration of the
  database, it's possible to do that by editing the metadata SQL-script
  files.</para>

  <para>SQL-scripts you can obtain from jar-file
  exo.jcr.component.core-XXX.XXX.jar:conf/storage/. They also can be found at
  GitHub <ulink
  url="https://github.com/exoplatform/jcr/tree/stable/1.14.x/exo.jcr.component.core/src/main/resources/conf/storage/">here.</ulink></para>

  <para>In the next two tables correspondence between the scripts and
  databases is shown.</para>

  <table border="1">
    <caption>Single-database</caption>

    <tr>
      <td>MySQL DB</td>

      <td>jcr-sjdbc.mysql.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with utf-8</td>

      <td>jcr-sjdbc.mysql-utf8.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with MyISAM*</td>

      <td>jcr-sjdbc.mysql-myisam.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with MyISAM and utf-8*</td>

      <td>jcr-sjdbc.mysql-myisam-utf8.sql</td>
    </tr>

    <tr>
      <td>PostgresSQL</td>

      <td>jcr-sjdbc.pqsql.sql</td>
    </tr>

    <tr>
      <td>Oracle DB</td>

      <td>jcr-sjdbc.ora.sql</td>
    </tr>

    <tr>
      <td>DB2</td>

      <td>jcr-sjdbc.db2.sql</td>
    </tr>

    <tr>
      <td>MS SQL Server</td>

      <td>jcr-sjdbc.mssql.sql</td>
    </tr>

    <tr>
      <td>Sybase</td>

      <td>jcr-sjdbc.sybase.sql</td>
    </tr>

    <tr>
      <td>HSQLDB</td>

      <td>jcr-sjdbc.sql</td>
    </tr>
  </table>

  <table border="1">
    <caption>Multi-database</caption>

    <tr>
      <td>MySQL DB</td>

      <td>jcr-mjdbc.mysql.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with utf-8</td>

      <td>jcr-mjdbc.mysql-utf8.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with MyISAM*</td>

      <td>jcr-mjdbc.mysql-myisam.sql</td>
    </tr>

    <tr>
      <td>MySQL DB with MyISAM and utf-8*</td>

      <td>jcr-mjdbc.mysql-myisam-utf8.sql</td>
    </tr>

    <tr>
      <td>PostgresSQL</td>

      <td>jcr-mjdbc.pqsql.sql</td>
    </tr>

    <tr>
      <td>Oracle DB</td>

      <td>jcr-mjdbc.ora.sql</td>
    </tr>

    <tr>
      <td>DB2</td>

      <td>jcr-mjdbc.db2.sql</td>
    </tr>

    <tr>
      <td>MS SQL Server</td>

      <td>jcr-mjdbc.mssql.sql</td>
    </tr>

    <tr>
      <td>Sybase</td>

      <td>jcr-mjdbc.sybase.sql</td>
    </tr>

    <tr>
      <td>HSQLDB</td>

      <td>jcr-mjdbc.sql</td>
    </tr>
  </table>

  <para>In case the non-ANSI node name is used, it's necessary to use a
  database with <link linkend="JCR.MultilanguageSupport">MultiLanguage support</link>. Some JDBC
  drivers need additional parameters for establishing a Unicode friendly
  connection. E.g. under mysql it's necessary to add an additional parameter
  for the JDBC driver at the end of JDBC URL. For instance:
  <code>jdbc:mysql://exoua.dnsalias.net/portal?characterEncoding=utf8</code></para>

  <para>There are preconfigured configuration files for HSQLDB. Look for these
  files in /conf/portal and /conf/standalone folders of the jar-file
  <package>exo.jcr.component.core-XXX.XXX.jar</package> or source-distribution
  of eXo JCR implementation.</para>

  <para>By default, the configuration files are located in service jars
  <filename>/conf/portal/configuration.xml</filename> (eXo services including
  JCR Repository Service) and <filename>exo-jcr-config.xml</filename>
  (repositories configuration). In GateIn product, JCR is configured in
  portal web application
  <filename>portal/WEB-INF/conf/jcr/jcr-configuration.xml</filename> (JCR
  Repository Service and related serivces) and repository-configuration.xml
  (repositories configuration).</para>

  <para>Read more about <link linkend="JCR.eXoJCRconfiguration">Repository
  configuration</link>.</para>

  <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration">
    <title>General recommendations for database configuration</title>

    <itemizedlist>
      <listitem>
        <para>Please note, that JCR requires at least READ_COMMITED isolation
        level and other RDBMS configurations can cause some side-effects and
        issues. So, please, make sure proper isolation level is configured on
        database server side.</para>
      </listitem>
    </itemizedlist>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.DB2Configuration">
      <title>DB2 configuration</title>

      <itemizedlist>
        <listitem>
          <para>RDBMS reindexing feature use queries based on LIMIT and OFFSET
          clauses which are not enabled by default. However, you can ensure
          they are enabled by executing the following <programlisting>$ db2set DB2_COMPATIBILITY_VECTOR=MYS
$ db2stop
$ db2start</programlisting></para>
        </listitem>

        <listitem>
          <para>Statistics is collected <ulink
          url="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0011762.htm">automatically</ulink>
          starting from DB2 Version 9, however it is needed to launch
          statistics collection manually during the very first start,
          otherwise it could be very long. You need to run '<ulink
          url="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001980.htm">RUNSTATS</ulink>'
          command</para>

          <programlisting>RUNSTATS ON TABLE &lt;scheme&gt;.&lt;table&gt; WITH DISTRIBUTION AND INDEXES ALL</programlisting>

          <para>for JCR_SITEM (or JCR_MITEM) and JCR_SVALUE (or JCR_MVALUE)
          tables.</para>
        </listitem>
      </itemizedlist>
    </section>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.MySQLConfiguration">
      <title>MySQL configuration</title>

      <itemizedlist>
        <listitem>
          <para>To prevent any consistency issues please ensure that InnoDB is
          configured as default MySQL engine (instead of MyISAM by default)
          before launching your application for the very first time otherwise
          when the application will create the tables MyISAM will be used as
          MySQL engine which is a non transactional engine that doesn't not
          support integrity constraints so even if later you swicth to InnoDB
          using an alter table all the integrity constraints would be missing
          as they would have been removed at table creation table by
          MyISAM.</para>
        </listitem>

        <listitem>
          <para>MyISAM is not supported due to its lack of transaction support
          and integrity check, so use it only if you don't expect any support
          and if performances in read accesses are more important than the
          consistency in your use-case. Therefore the dialects mysql-myisam
          and mysql-myisam-utf8 are only dedicated to the community.</para>
        </listitem>

        <listitem>
          <para>MySQL relies on collected statistics for keeping track of data
          distribution in tables and for optimizing join statements, but you
          can manually call '<ulink
          url="http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html">ANALYZE</ulink>'
          to update statistics if needed. For example</para>

          <programlisting>ANALYZE TABLE JCR_SITEM, JCR_SVALUE</programlisting>
        </listitem>
      </itemizedlist>
    </section>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.PostgreSQLConfiguration">
      <title>PostgreSQL configuration</title>

      <itemizedlist>
        <listitem>
          <para>Be aware, when using RDBMS for reindexing need to set
          "enable_seqscan" to "off" or "default_statistics_target" at least
          "50"</para>
        </listitem>

        <listitem>
          <para>Though PostgreSQL server performs query optimization
          automatically, you can manualy call '<ulink
          url="http://www.postgresql.org/docs/8.1/static/sql-analyze.html">ANALYZE</ulink>'
          command to collect statistics which can influence the performance.
          For example</para>

          <programlisting>ANALYZE JCR_SITEM
ANALYZE JCR_SVALUE</programlisting>
        </listitem>
      </itemizedlist>
    </section>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.MSSQLConfiguration">
      <title>MS SQL configuration</title>

      <itemizedlist>
        <listitem>
          <para>One more mandatory JCR requirement for underlying databases is
          a case sensitive collation. Microsoft SQL Server both 2005 and 2008
          customers must configure their server with collation corresponding
          to personal needs and requirements, but obligatorily case sensitive.
          For more information please refer to Microsoft SQL Server
          documentation page "Selecting a SQL Server Collation" <ulink
          url="http://msdn.microsoft.com/en-us/library/ms144250.aspx">here.</ulink></para>
        </listitem>

        <listitem>
          <para>MS SQL DB server's optimizer automatically processes queries
          to increase performance. Optimization is based on statistical data
          which is collected automatically, but you can manually call <ulink
          url="http://en.wikipedia.org/wiki/Transact-SQL">Transact-SQL</ulink>
          command '<ulink
          url="http://msdn.microsoft.com/en-us/library/ms187348.aspx">UPDATE
          STATISTICS</ulink>' which in very few situations may increase
          performance. For example</para>

          <programlisting>UPDATE STATISTICS JCR_SITEM
UPDATE STATISTICS JCR_SVALUE</programlisting>
        </listitem>
      </itemizedlist>
    </section>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.SybaseConfiguration">
      <title>Sybase configuration</title>

      <itemizedlist>
        <listitem>
          <para>Sybase DB Server optimizer automatically processes queries to
          increase performance. Optimization is based on statistical data
          which is collected automatically, but you can manually call <ulink
          url="http://en.wikipedia.org/wiki/Transact-SQL">Transact-SQL</ulink>
          command '<ulink
          url="http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/37166;pt=37125">update
          statistics</ulink>' which in very few situations may increase
          performance. For example</para>

          <programlisting>update statistics JCR_SITEM
update statistics JCR_SVALUE</programlisting>
        </listitem>
      </itemizedlist>
    </section>

    <section id="JCR.JDBCDataContainerConfig.GeneralRecommendationsForDatabaseConfiguration.OracleConfiguration">
      <title>Oracle configuration</title>

      <itemizedlist>
        <listitem>
          <para>Oracle DB automatically collects statistics to optimize
          performance of queries, but you can manually call '<ulink
          url="http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_4005.htm">ANALYZE</ulink>'
          command to start collecting statistics immediately which may improve
          performance. For example</para>

          <programlisting>ANALYZE INDEX JCR_PK_SITEM COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_FK COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_NAME COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_ID COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SVALUE COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SVALUE_PROPERTY COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SREF COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SREF_PROPERTY COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SCONTAINER COMPUTE STATISTICS</programlisting>
        </listitem>
      </itemizedlist>
    </section>
  </section>

  <section id="JCR.JDBCDataContainerConfig.Multi-databaseConfiguration">
    <title>Multi-database Configuration</title>

    <para>You need to configure each workspace in a repository. You may have
    each one on different remote servers as far as you need.</para>

    <para>First of all configure the data containers in the
    <classname>org.exoplatform.services.naming.InitialContextInitializer</classname>
    service. It's the JNDI context initializer which registers (binds) naming
    resources (DataSources) for data containers.</para>

    <para>For example (standalone mode, two data containers
    <parameter>jdbcjcr</parameter> - local HSQLDB,
    <parameter>jdbcjcr1</parameter> - remote MySQL):</para>

    <programlisting language="xml">&lt;component&gt;
    &lt;key&gt;org.exoplatform.services.naming.InitialContextInitializer&lt;/key&gt;
    &lt;type&gt;org.exoplatform.services.naming.InitialContextInitializer&lt;/type&gt;
    &lt;component-plugins&gt;
      &lt;component-plugin&gt;
        &lt;name&gt;bind.datasource&lt;/name&gt;
        &lt;set-method&gt;addPlugin&lt;/set-method&gt;
        &lt;type&gt;org.exoplatform.services.naming.BindReferencePlugin&lt;/type&gt;
        &lt;init-params&gt;
          &lt;value-param&gt;
            &lt;name&gt;bind-name&lt;/name&gt;
            &lt;value&gt;jdbcjcr&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;class-name&lt;/name&gt;
            &lt;value&gt;javax.sql.DataSource&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;factory&lt;/name&gt;
            &lt;value&gt;org.apache.commons.dbcp.BasicDataSourceFactory&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;properties-param&gt;
            &lt;name&gt;ref-addresses&lt;/name&gt;
            &lt;description&gt;ref-addresses&lt;/description&gt;
            &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver"/&gt;
            &lt;property name="url" value="jdbc:hsqldb:file:target/temp/data/portal"/&gt;
            &lt;property name="username" value="sa"/&gt;
            &lt;property name="password" value=""/&gt;
          &lt;/properties-param&gt;
        &lt;/init-params&gt;
      &lt;/component-plugin&gt;
      &lt;component-plugin&gt;
        &lt;name&gt;bind.datasource&lt;/name&gt;
        &lt;set-method&gt;addPlugin&lt;/set-method&gt;
        &lt;type&gt;org.exoplatform.services.naming.BindReferencePlugin&lt;/type&gt;
        &lt;init-params&gt;
          &lt;value-param&gt;
            &lt;name&gt;bind-name&lt;/name&gt;
            &lt;value&gt;jdbcjcr1&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;class-name&lt;/name&gt;
            &lt;value&gt;javax.sql.DataSource&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;factory&lt;/name&gt;
            &lt;value&gt;org.apache.commons.dbcp.BasicDataSourceFactory&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;properties-param&gt;
            &lt;name&gt;ref-addresses&lt;/name&gt;
            &lt;description&gt;ref-addresses&lt;/description&gt;
            &lt;property name="driverClassName" value="com.mysql.jdbc.Driver"/&gt;
            &lt;property name="url" value="jdbc:mysql://exoua.dnsalias.net/jcr"/&gt;
            &lt;property name="username" value="exoadmin"/&gt;
            &lt;property name="password" value="exo12321"/&gt;
            &lt;property name="maxActive" value="50"/&gt;
            &lt;property name="maxIdle" value="5"/&gt;
            &lt;property name="initialSize" value="5"/&gt;
          &lt;/properties-param&gt;
        &lt;/init-params&gt;
      &lt;/component-plugin&gt;
    &lt;component-plugins&gt;
    &lt;init-params&gt;
      &lt;value-param&gt;
        &lt;name&gt;default-context-factory&lt;/name&gt;
        &lt;value&gt;org.exoplatform.services.naming.SimpleContextFactory&lt;/value&gt;
      &lt;/value-param&gt;
    &lt;/init-params&gt;
  &lt;/component&gt;
  </programlisting>

    <para>We configure the database connection parameters:<itemizedlist>
        <listitem>
          <para><parameter>driverClassName</parameter>, e.g.
          "org.hsqldb.jdbcDriver", "com.mysql.jdbc.Driver",
          "org.postgresql.Driver"</para>
        </listitem>

        <listitem>
          <para><parameter>url</parameter>, e.g.
          "jdbc:hsqldb:file:target/temp/data/portal",
          "jdbc:mysql://exoua.dnsalias.net/jcr"</para>
        </listitem>

        <listitem>
          <para><parameter>username</parameter>, e.g. "sa", "exoadmin"</para>
        </listitem>

        <listitem>
          <para><parameter>password</parameter>, e.g. "", "exo12321"</para>
        </listitem>
      </itemizedlist></para>

    <para>There can be connection pool configuration parameters
    (org.apache.commons.dbcp.BasicDataSourceFactory):<itemizedlist>
        <listitem>
          <para><parameter>maxActive</parameter>, e.g. 50</para>
        </listitem>

        <listitem>
          <para><parameter>maxIdle</parameter>, e.g. 5</para>
        </listitem>

        <listitem>
          <para><parameter>initialSize</parameter>, e.g. 5</para>
        </listitem>

        <listitem>
          <para>and other according to <ulink
          url="http://jakarta.apache.org/commons/dbcp/configuration.html">Apache
          DBCP configuration</ulink></para>
        </listitem>
      </itemizedlist></para>

    <para>When the data container configuration is done, we can configure the
    repository service. Each workspace will be configured for its own data
    container.</para>

    <para>For example (two workspaces <parameter>ws</parameter> - jdbcjcr,
    <parameter>ws1</parameter> - jdbcjcr1):</para>

    <programlisting language="xml">&lt;workspaces&gt;
  &lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="source-name" value="jdbcjcr"/&gt;
      &lt;property name="dialect" value="hsqldb"/&gt;
      &lt;property name="multi-db" value="true"/&gt;
      &lt;property name="max-buffer-size" value="200K"/&gt;
      &lt;property name="swap-directory" value="target/temp/swap/ws"/&gt;   
    &lt;/properties&gt;
    &lt;/container&gt;
    &lt;cache enabled="true"&gt;
      &lt;properties&gt;
        &lt;property name="max-size" value="10K"/&gt;&lt;!-- 10Kbytes --&gt;
        &lt;property name="live-time" value="30m"/&gt;&lt;!-- 30 min --&gt;
      &lt;/properties&gt;
    &lt;/cache&gt;
    &lt;query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex"&gt;
    &lt;properties&gt;
      &lt;property name="index-dir" value="target/temp/index"/&gt;
    &lt;/properties&gt;
    &lt;/query-handler&gt;
    &lt;lock-manager&gt;
    &lt;time-out&gt;15m&lt;/time-out&gt;&lt;!-- 15 min --&gt;
    &lt;persister class="org.exoplatform.services.jcr.impl.core.lock.FileSystemLockPersister"&gt;
      &lt;properties&gt;
      &lt;property name="path" value="target/temp/lock/ws"/&gt;
      &lt;/properties&gt;
    &lt;/persister&gt;
    &lt;/lock-manager&gt;
  &lt;/workspace&gt;
  &lt;workspace name="ws1" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="source-name" value="jdbcjcr1"/&gt;
      &lt;property name="dialect" value="mysql"/&gt;
      &lt;property name="multi-db" value="true"/&gt;
      &lt;property name="max-buffer-size" value="200K"/&gt;
      &lt;property name="swap-directory" value="target/temp/swap/ws1"/&gt;   
    &lt;/properties&gt;
    &lt;/container&gt;
    &lt;cache enabled="true"&gt;
      &lt;properties&gt;
        &lt;property name="max-size" value="10K"/&gt;
        &lt;property name="live-time" value="5m"/&gt;
      &lt;/properties&gt;
    &lt;/cache&gt;
    &lt;query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex"&gt;
    &lt;properties&gt;
      &lt;property name="index-dir" value="target/temp/index"/&gt;
    &lt;/properties&gt;
    &lt;/query-handler&gt;
    &lt;lock-manager&gt;
    &lt;time-out&gt;15m&lt;/time-out&gt;&lt;!-- 15 min --&gt;
    &lt;persister class="org.exoplatform.services.jcr.impl.core.lock.FileSystemLockPersister"&gt;
      &lt;properties&gt;
      &lt;property name="path" value="target/temp/lock/ws1"/&gt;
      &lt;/properties&gt;
    &lt;/persister&gt;
    &lt;/lock-manager&gt;
  &lt;/workspace&gt;
&lt;/workspaces&gt;
</programlisting>

    <itemizedlist>
      <listitem>
        <para><parameter>source-name</parameter>: A javax.sql.DataSource name
        configured in InitialContextInitializer component (was
        <parameter>sourceName</parameter> prior JCR 1.9);</para>
      </listitem>

      <listitem>
        <para><parameter>dialect</parameter>: A database dialect, one of
        "hsqldb", "mysql", "mysql-utf8", "pgsql", "oracle", "oracle-oci",
        "mssql", "sybase", "derby", "db2", "db2v8" or "auto" for dialect
        autodetection;</para>
      </listitem>

      <listitem>
        <para><parameter>multi-db</parameter>: Enable multi-database container
        with this parameter (set value "true");</para>
      </listitem>

      <listitem>
        <para><parameter>max-buffer-size: A</parameter> a threshold (in bytes)
        after which a javax.jcr.Value content will be swapped to a file in a
        temporary storage. I.e. swap for pending changes.</para>
      </listitem>

      <listitem>
        <para><parameter>swap-directory</parameter>: A path in the file system
        used to swap the pending changes.</para>
      </listitem>
    </itemizedlist>

    <para>In this way, we have configured two workspace which will be
    persisted in two different databases (ws in HSQLDB, ws1 in MySQL).</para>

    <note>
      <para>Starting from v.1.9 <link
      linkend="JCR.eXoJCRconfiguration">repository configuration</link>
      parameters supports human-readable formats of values (e.g. 200K - 200
      Kbytes, 30m - 30 minutes etc)</para>
    </note>
  </section>

  <section id="JCR.JDBCDataContainerConfig.Single-databaseConfiguration">
    <title>Single-database configuration</title>

    <para>It's more simple to configure a single-database data container. We
    have to configure one naming resource.</para>

    <para>For example (embedded mode for <parameter>jdbcjcr</parameter> data
    container):</para>

    <programlisting language="xml">&lt;external-component-plugins&gt;
    &lt;target-component&gt;org.exoplatform.services.naming.InitialContextInitializer&lt;/target-component&gt;
    &lt;component-plugin&gt;
        &lt;name&gt;bind.datasource&lt;/name&gt;
        &lt;set-method&gt;addPlugin&lt;/set-method&gt;
        &lt;type&gt;org.exoplatform.services.naming.BindReferencePlugin&lt;/type&gt;
        &lt;init-params&gt;
          &lt;value-param&gt;
            &lt;name&gt;bind-name&lt;/name&gt;
            &lt;value&gt;jdbcjcr&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;class-name&lt;/name&gt;
            &lt;value&gt;javax.sql.DataSource&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;value-param&gt;
            &lt;name&gt;factory&lt;/name&gt;
            &lt;value&gt;org.apache.commons.dbcp.BasicDataSourceFactory&lt;/value&gt;
          &lt;/value-param&gt;
          &lt;properties-param&gt;
            &lt;name&gt;ref-addresses&lt;/name&gt;
            &lt;description&gt;ref-addresses&lt;/description&gt;
            &lt;property name="driverClassName" value="org.postgresql.Driver"/&gt;
            &lt;property name="url" value="jdbc:postgresql://exoua.dnsalias.net/portal"/&gt;
            &lt;property name="username" value="exoadmin"/&gt;
            &lt;property name="password" value="exo12321"/&gt;
            &lt;property name="maxActive" value="50"/&gt;
            &lt;property name="maxIdle" value="5"/&gt;
            &lt;property name="initialSize" value="5"/&gt;
          &lt;/properties-param&gt;
        &lt;/init-params&gt;
    &lt;/component-plugin&gt;
  &lt;/external-component-plugins&gt;
  </programlisting>

    <para>And configure repository workspaces in repositories configuration
    with this one database. Parameter "multi-db" must be switched off (set
    value "false").</para>

    <para>For example (two workspaces <parameter>ws</parameter> - jdbcjcr,
    <parameter>ws1</parameter> - jdbcjcr):</para>

    <programlisting language="xml">&lt;workspaces&gt;
  &lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="source-name" value="jdbcjcr"/&gt;
      &lt;property name="dialect" value="pgsql"/&gt;
      &lt;property name="multi-db" value="false"/&gt;
      &lt;property name="max-buffer-size" value="200K"/&gt;
      &lt;property name="swap-directory" value="target/temp/swap/ws"/&gt;
    &lt;/properties&gt;
    &lt;/container&gt;
    &lt;cache enabled="true"&gt;
    &lt;properties&gt;
      &lt;property name="max-size" value="10K"/&gt;
      &lt;property name="live-time" value="30m"/&gt;
    &lt;/properties&gt;
    &lt;/cache&gt;
    &lt;query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex"&gt;
    &lt;properties&gt;
      &lt;property name="index-dir" value="../temp/index"/&gt;
    &lt;/properties&gt;
    &lt;/query-handler&gt;
    &lt;lock-manager&gt;
    &lt;time-out&gt;15m&lt;/time-out&gt;
    &lt;persister class="org.exoplatform.services.jcr.impl.core.lock.FileSystemLockPersister"&gt;
      &lt;properties&gt;
      &lt;property name="path" value="target/temp/lock/ws"/&gt;
      &lt;/properties&gt;
    &lt;/persister&gt;
    &lt;/lock-manager&gt;
  &lt;/workspace&gt;
  &lt;workspace name="ws1" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="source-name" value="jdbcjcr"/&gt;
      &lt;property name="dialect" value="pgsql"/&gt;
      &lt;property name="multi-db" value="false"/&gt;
      &lt;property name="max-buffer-size" value="200K"/&gt;
      &lt;property name="swap-directory" value="target/temp/swap/ws1"/&gt;
    &lt;/properties&gt;
    &lt;/container&gt;
    &lt;cache enabled="true"&gt;
    &lt;properties&gt;
      &lt;property name="max-size" value="10K"/&gt;
      &lt;property name="live-time" value="5m"/&gt;
    &lt;/properties&gt;
    &lt;/cache&gt;
    &lt;lock-manager&gt;
    &lt;time-out&gt;15m&lt;/time-out&gt;
    &lt;persister class="org.exoplatform.services.jcr.impl.core.lock.FileSystemLockPersister"&gt;
      &lt;properties&gt;
      &lt;property name="path" value="target/temp/lock/ws1"/&gt;
      &lt;/properties&gt;
    &lt;/persister&gt;
    &lt;/lock-manager&gt;
  &lt;/workspace&gt;
&lt;/workspaces&gt;
</programlisting>

    <para>In this way, we have configured two workspaces which will be
    persisted in one database (PostgreSQL).</para>

    <section id="JCR.JDBCDataContainerConfig.Single-databaseConfiguration.ConfigurationWithoutDataSource">
      <title>Configuration without DataSource</title>

      <para>Repository configuration without using of the
      <classname>javax.sql.DataSource</classname> bounded in JNDI.</para>

      <para>This case may be usable if you have a dedicated JDBC driver
      implementation with special features like XA transactions,
      statements/connections pooling etc:<itemizedlist>
          <listitem>
            <para>You have to remove the configuration in
            <classname>InitialContextInitializer</classname> for your database
            and configure a new one directly in the workspace
            container.</para>
          </listitem>

          <listitem>
            <para>Remove parameter "source-name" and add next lines instead.
            Describe your values for a JDBC driver, database url and
            username.</para>
          </listitem>
        </itemizedlist></para>

      <note>
        <para>But be careful in this case JDBC driver should implement and
        provide connection pooling. Connection pooling is very recommended for
        use with JCR to prevent a database overload.</para>
      </note>

      <programlisting language="xml">&lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
  &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="dialect" value="hsqldb"/&gt;
      &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver"/&gt;
      &lt;property name="url" value="jdbc:hsqldb:file:target/temp/data/portal"/&gt;
      &lt;property name="username" value="su"/&gt;
      &lt;property name="password" value=""/&gt; 
      ......</programlisting>
    </section>

    <section id="JCR.JDBCDataContainerConfig.Single-databaseConfiguration.DynamicWorkspaceCreation">
      <title>Dynamic Workspace Creation</title>

      <para>Workspaces can be added dynamically during runtime.</para>

      <para>This can be performed in two steps:<itemizedlist>
          <listitem>
            <para>Firstly,
            <classname>ManageableRepository.configWorkspace(WorkspaceEntry
            wsConfig)</classname> - register a new configuration in
            RepositoryContainer and create a WorkspaceContainer.</para>
          </listitem>

          <listitem>
            <para>Secondly, the main step,
            <classname>ManageableRepository.createWorkspace(String
            workspaceName)</classname> - creation of a new workspace.</para>
          </listitem>
        </itemizedlist></para>
    </section>
  </section>

  <section id="JCR.JDBCDataContainerConfig.SimpleAndComplexQueries">
    <title>Simple and Complex queries</title>

    <para>eXo JCR provides two ways for interact with Database -
    <classname>JDBCStorageConnection</classname> that uses simple queries and
    <classname>CQJDBCStorageConection</classname> that uses complex queries
    for reducing amount of database callings.</para>

    <para>Simple queries will be used if you chose
    <classname>org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer</classname>:</para>

    <programlisting language="xml">&lt;workspaces&gt;
  &lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer"&gt;
    ...
  &lt;/workspace&gt;
&lt;/worksapces&gt;
</programlisting>

    <para>Complex queries will be used if you chose
    <classname>org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer</classname>:</para>

    <programlisting language="xml">&lt;workspaces&gt;
  &lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
    &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    ...
  &lt;/workspace&gt;
&lt;/worksapces&gt;</programlisting>

    <para>Why we should use a Complex Queries?<simplelist>
        <member>They are optimised to reduce amount of requests to
        database.</member>
      </simplelist>Why we should use a Simple Queries?<simplelist>
        <member>Simple queries implemented in way to support as many database
        dialects as possible.</member>

        <member>Simple queries do not use sub queries, left or right
        joins.</member>
      </simplelist></para>
  </section>

  <section id="JCR.JDBCDataContainerConfig.ForseQueryHints">
    <title>Forse Query Hints</title>

    <para>Some databases supports hints to increase query performance (like
    Oracle, MySQL, etc). eXo JCR have separate Complex Query implementation
    for Orcale dialect, that uses query hints to increase performance for few
    important queries.</para>

    <para>To enable this option put next configuration property:</para>

    <programlisting language="xml">&lt;workspace name="ws" auto-init-root-nodetype="nt:unstructured"&gt;
  &lt;container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer"&gt;
    &lt;properties&gt;
      &lt;property name="dialect" value="oracle"/&gt;
      &lt;property name="force.query.hints" value="true" /&gt;
      ......</programlisting>

    <para>Query hints enabled by default.</para>

    <para>eXo JCR uses query hints only for Complex Query Oracle dialect. For
    all other dialects this parameter is ignored.</para>
  </section>

  <section id="JCR.JDBCDataContainerConfig.NotesForMicrosoftWindowsUsers">
    <title>Notes for Microsoft Windows users</title>

    <para>The current configuration of eXo JCR uses <ulink
    url="http://commons.apache.org/dbcp/">Apache DBCP</ulink> connection pool
    (<classname>org.apache.commons.dbcp.BasicDataSourceFactory</classname>).
    It's possible to set a big value for maxActive parameter in
    <filename>configuration.xml</filename>. That means usage of lots of TCP/IP
    ports from a client machine inside the pool (i.e. JDBC driver). As a
    result, the data container can throw exceptions like "Address already in
    use". To solve this problem, you have to configure the client's machine
    networking software for the usage of shorter timeouts for opened TCP/IP
    ports.</para>

    <para>Microsoft Windows has <parameter>MaxUserPort</parameter>,
    <parameter>TcpTimedWaitDelay</parameter> registry keys in the node
    <parameter>HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters</parameter>,
    by default these keys are unset, set each one with values like
    these:<itemizedlist>
        <listitem>
          <para>"TcpTimedWaitDelay"=dword:0000001e, sets TIME_WAIT parameter
          to 30 seconds, default is 240.</para>
        </listitem>

        <listitem>
          <para>"MaxUserPort"=dword:00001b58, sets the maximum of open ports
          to 7000 or higher, default is 5000.</para>
        </listitem>
      </itemizedlist></para>

    <para>A sample registry file is below:</para>

    <programlisting>Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"MaxUserPort"=dword:00001b58
"TcpTimedWaitDelay"=dword:0000001e</programlisting>
  </section>
</section>
