4.2.5. Frequently asked questions

Q1. Which database server is better for eXo JCR?
Q2. How to setup eXo JCR for multilingual content on MySQL?
Q3. Does MySQL have limitation affecting on eXo JCR features?
Q4. Does use of Sybase database need special options in eXo JCR configuration?
Q5. It is better to use queries which access data by the JCR API?
Q6. Is ordering by jcr:path or Item name supported?

Q1.

Which database server is better for eXo JCR?

If the question is about the performance, it is difficult to answer, because each database can be configured to have better performance in a special case. According to the results of our internal tests, the best choice is Oracle 11G R2 even when you store the binary data in the database. For other databases, it is recommended to store the binary data in the file system unless you have only small file content to store. MySQL and PostgreSQL are also demonstrated in our benchmark results that they could provide good performance. DB2 and MSSQL are slower in default configurations. The default configuration of Sybase is the slowest, but in this question, take the database server maintenance into account. The installation of MySQL and PostgreSQL is simple and they can work even on any limited hardware. The same actual for maintenance during the work. Note for Sybase: "check-sns-new-connection" data container configuration parameter should be set to "true". For testing purpose, embedded database such as HSQLDB is the best choice. Apache Derby and H2 are also supported. But, H2 surprisingly needs "beta" feature enabled - MVCC=TRUE in JDBC URL.

Q2.

How to setup eXo JCR for multilingual content on MySQL?

MySQL database should be created in utf8 encoding with collation utf8_bin, since eXo JCR is case-sensitive:

CREATE DATABASE db1 CHARACTER SET utf8 COLLATE utf8_bin;

The eXo JCR application (for example, GateIn) should use JCR dialect "MySQL-UTF8".

Note

The "MySQL-UTF8" dialect cannot be auto-detected, it should be set explicitly in the configuration.

Q3.

Does MySQL have limitation affecting on eXo JCR features?

Index's key length of JCR_SITEM (JCR_MITEM) table for mysql-utf8 dialect is reduced to 765 bytes (or 255 chars).

Q4.

Does use of Sybase database need special options in eXo JCR configuration?

To enable JCR to work properly with Sybase, a check-sns-new-connection property with the 'false' value is required for each workspace data container:


<container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer">
    <properties>
        <property name="source-name" value="jdbcjcr" />
        <property name="dialect" value="auto" />
        <property name="multi-db" value="true" />
        <property name="update-storage" value="false" />
        <property name="max-buffer-size" value="200k" />
        <property name="swap-directory" value="target/temp/swap/ws" />
        <property name="swap-directory" value="target/temp/swap/ws" />
        <property name="check-sns-new-connection" value="false" />
    </properties>

Q5.

It is better to use queries which access data by the JCR API?

No, direct access to items via JCR API is more efficient. Search will consume additional resources for index querying and only then return the items.

Q6.

Is ordering by jcr:path or Item name supported?

No, it is not supported. There are two ways to order results, when the path may be used as criteria:

  • Order by property with the NAME or PATH value type (JCR supports it).

  • Order by jcr:path - sort by the exact path of node (JCR does not support it).

Order by jcr:path

If no order specification is supplied in the query statement, implementations may support document order on the result nodes (see 6.6.4.2 Document Order of JSR-170), and it is sorted by order number.

By default, (if query does not contain any ordering statements) result nodes are sorted by the document order.

SELECT * FROM nt:unstructured WHERE jcr:path LIKE 'testRoot/%'

For specified jcr:path ordering, there is different proceeding in XPath and SQL:

  • SQL no matter ascending or descending - query returns result nodes in random order:

    SELECT * FROM nt:unstructured WHERE
                  jcr:path LIKE 'testRoot/%' ORDER BY jcr:path
  • XPath - jcr:path order construction is ignored (so result is not sorted according to the path):

    /testRoot/*
                  @jcr:primaryType='nt:unstructured'
                  order by jcr:path
Copyright ©. All rights reserved. eXo Platform SAS
blog comments powered byDisqus