<?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>MySQL 5.5.17 MYSQL Connector/J 5.1.18</para>
      </listitem>

      <listitem>
        <para>MySQL Cluster 7.2 (NDB engine)</para>
      </listitem>

      <listitem>
        <para>MySQL Cluster 7.3 (NDB engine)</para>
      </listitem>

      <listitem>
        <para>MySQL Cluster 7.4 (NDB engine)</para>
      </listitem>

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

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

      <listitem>
        <para>PostgreSQL 8.4.14 JDBC4 Driver, Version 8.4-702</para>
      </listitem>

      <listitem>
        <para>PostgreSQL 9.1.5 JDBC4 Driver, Version 9.1-902</para>
      </listitem>

      <listitem>
        <para>PostgreSQL 9.2.4 JDBC4 Driver, Version 9.2-1002</para>
      </listitem>

      <listitem>
        <para>Enterprise DB Postgres Plus Advanced Server 9.2.1 JDBC4 Driver,
        Version 9.2.1.3</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>MS SQL Server 2008 R2 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>Sybase ASE 15.7 Driver: Sybase jConnect JDBC driver v7 (build
        26666)</para>
      </listitem>

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

      <listitem>
        <para>H2 (1.3.161)</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.16.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>MySQL DB with NDB engine</td>

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

    <tr>
      <td>MySQL DB with NDB engine and utf-8</td>

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

    <tr>
      <td>PostgreSQL and Postgre Plus</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>

    <tr>
      <td>H2</td>

      <td>jcr-sjdbc.h2.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>MySQL DB with NDB engine</td>

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

    <tr>
      <td>MySQL DB with NDB engine and utf-8</td>

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

    <tr>
      <td>PostgreSQL and Postgre Plus</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>

    <tr>
      <td>H2</td>

      <td>jcr-mjdbc.h2.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>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>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. This dialect is 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/PostgrePlus configuration</title>

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

        <listitem>
          <para>Though PostgreSQL/PostgrePlus 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>

        <listitem>
          <para>If for a version prior to 9.1, the parameter
          standard_conforming_strings is enabled, you need to use "pgsql-scs"
          as dialect</para>
        </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 TABLE JCR_SITEM COMPUTE STATISTICS
ANALYZE TABLE JCR_SVALUE COMPUTE STATISTICS
ANALYZE TABLE JCR_SREF COMPUTE STATISTICS
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>
    <title>Isolated-database Configuration</title>

    <para>Isolated-database configuration allows to configure single database
    for repository but separate database tables for each workspace. First step
    is to configure the data container 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:</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;/properties-param&gt;
      &lt;/init-params&gt;
    &lt;/component-plugin&gt;
  &lt;/external-component-plugins&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>When the data container configuration is done, we can configure the
    repository service. Each workspace will be configured for the same data
    container.</para>

    <para>For example:</para>

    <programlisting language="xml">&lt;workspaces&gt;
   &lt;workspace name="ws"&gt;
      &lt;!-- for system storage --&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="db-structure-type" value="isolated" /&gt;
            ...
         &lt;/properties&gt;
         ...
      &lt;/container&gt;
      ...
   &lt;/workspace&gt;

   &lt;workspace name="ws1"&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="db-structure-type" value="isolated" /&gt;
            ...
         &lt;/properties&gt;
         ...
      &lt;/container&gt;
      ...
   &lt;/workspace&gt;
&lt;/workspaces&gt;</programlisting>

    <para>In this way, we have configured two workspace which will be
    persisted in different database tables.</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.Multi-databaseConfiguration">
    <title>Multi-database Configuration</title>

    <note>
      <para>This configuration option is now deprecated. Use isolated database
      configuration instead.</para>
    </note>

    <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:</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;/component&gt;
                    </programlisting>

    <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:</para>

    <programlisting language="xml">&lt;workspaces&gt;
   &lt;workspace name="ws"&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="db-structure-type" value="multi"/&gt;
            ...
         &lt;/properties&gt;
      &lt;/container&gt;
      ...
   &lt;/workspace&gt;

   &lt;workspace name="ws1"&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="db-structure-type" value="multi"/&gt;
            ...
         &lt;/properties&gt;
      &lt;/container&gt;
      ...
   &lt;/workspace&gt;
&lt;/workspaces&gt;                                     </programlisting>

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

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

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

    <para>For example:</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.</para>

    <para>For example:</para>

    <programlisting language="xml">&lt;workspaces&gt;
  &lt;workspace name="ws"&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="db-structure-type" value="single" /&gt;
        ...
      &lt;/properties&gt;
    &lt;/container&gt;
    ...
  &lt;/workspace&gt;

  &lt;workspace name="ws1"&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="db-structure-type" value="single" /&gt;
      ...
    &lt;/properties&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.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"&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"&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"&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>
