Difference between revisions of "MySQL - OpenKM 5.0"

From OpenKM Documentation
Jump to: navigation, search
(→‎JBoss datasources)
 
(32 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
{{TOCright}} __TOC__
 
{{TOCright}} __TOC__
 +
{{Note|These instructions are also valid for OpenKM 5.1, but [[#Login configuration]] has a minor change.}}
 +
 +
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in [[OpenKM.cfg]].
 +
 +
<source lang="java">
 +
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
 +
hibernate.hbm2ddl=create
 +
</source>
 +
 +
Once the tables are created, change the '''hibernate.hbm2ddl''' property from ''create'' to ''none''.
 +
 +
{{Warning|This configuration property should be set before the database creation. Once the database has been initialized don't modify it because can damage your installation. If your OpenKM installation has been already configured with another database (default one is an embedded one called HSQL) you can't switch to another database simply changing this property.}}
 +
 +
More info about this at:
 +
* [http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-optional-dialects Hibernate: SQL Dialects]
  
 
== Database creation ==
 
== Database creation ==
Line 8: Line 23:
 
DROP DATABASE IF EXISTS okm_app;
 
DROP DATABASE IF EXISTS okm_app;
  
−
CREATE DATABASE okm_repo DEFAULT CHARACTER SET utf8;
+
CREATE DATABASE okm_repo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
−
CREATE DATABASE okm_app DEFAULT CHARACTER SET utf8;
+
CREATE DATABASE okm_app DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
  
 
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
 
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
 +
GRANT ALL ON okm_repo.* TO openkm@localhost WITH GRANT OPTION;
 
GRANT ALL ON okm_app.* TO openkm@localhost WITH GRANT OPTION;
 
GRANT ALL ON okm_app.* TO openkm@localhost WITH GRANT OPTION;
 
</source>
 
</source>
 +
 +
You can also change the default collation of a previously created database:
 +
 +
<source lang="sql">
 +
ALTER DATABASE okm_app DEFAULT COLLATE utf8_bin;
 +
</source>
 +
 +
But remember to change the collation of the already created tables:
 +
 +
<source lang="sql">
 +
ALTER TABLE table_name COLLATE utf8_bin;
 +
</source>
 +
 +
This script may help:
 +
 +
<source lang="bash">
 +
#!/bin/bash
 +
PASSWORD=xxx
 +
DATABASE=okm_app
 +
 +
echo "* Changing collation for database $DATABASE...";
 +
mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER DATABASE $TABLE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;"
 +
TABLES=$(mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "show tables")
 +
 +
for TABLE in $TABLES ; do
 +
    echo "* Changing collation for table $TABLE...";
 +
    mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER TABLE $TABLE CONVERT TO CHARACTER SET utf8;"
 +
    mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER TABLE $TABLE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;"
 +
done
 +
 +
</source>
 +
 +
More info at [http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html MySQL: Case Sensitivity in String Searches].
 +
 +
== JBoss datasources ==
 +
 +
<pre>$ vim $JBOSS_HOME/server/default/deploy/openkm-ds.xml</pre>
 +
 +
<source lang="xml">
 +
<local-tx-datasource>
 +
    <jndi-name>OpenKMDS</jndi-name>
 +
    <connection-url>jdbc:mysql://localhost:3306/okm_app?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8</connection-url>
 +
    <driver-class>com.mysql.jdbc.Driver</driver-class>
 +
    <user-name>openkm</user-name>
 +
    <password>*****</password>
 +
    <min-pool-size>5</min-pool-size>
 +
    <max-pool-size>20</max-pool-size>
 +
    <idle-timeout-minutes>28680</idle-timeout-minutes>
 +
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
 +
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
 +
    <metadata>
 +
        <type-mapping>mySQL</type-mapping>
 +
    </metadata>
 +
</local-tx-datasource>
 +
</source>
 +
 +
The type mapping should match a type-mapping/name element from ''$JBOSS_HOME/server/default/conf/standardjbosscmp-jdbc.xml''. Example configurations for many third-party JDBC drivers are included in the ''$JBOSS_HOME/docs/examples/jca'' directory.
 +
 +
You may be interested in [http://community.jboss.org/wiki/encryptingdatasourcepasswords Encrypting DataSource Passwords].
 +
 +
== Login configuration ==
 +
<pre>$ vim $JBOSS_HOME/server/default/conf/login-config.xml</pre>
 +
<source lang="xml">
 +
<!-- OpenKM -->
 +
<application-policy name = "OpenKM">
 +
    <authentication>
 +
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
 +
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
 +
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active=true</module-option>
 +
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
 +
            <module-option name="hashAlgorithm">md5</module-option>
 +
            <module-option name="hashEncoding">hex</module-option>
 +
        </login-module>
 +
    </authentication>
 +
</application-policy>
 +
</source>
 +
 +
For OpenKM 5.1.x this is the right configuration:
 +
<source lang="xml">
 +
<!-- OpenKM -->
 +
<application-policy name = "OpenKM">
 +
    <authentication>
 +
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
 +
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
 +
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active='T'</module-option>
 +
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
 +
            <module-option name="hashAlgorithm">md5</module-option>
 +
            <module-option name="hashEncoding">hex</module-option>
 +
        </login-module>
 +
    </authentication>
 +
</application-policy>
 +
</source>
 +
 +
Read also: [http://community.jboss.org/wiki/DynamicLoginConfig Dynamic login-config.xml].
  
 
== Repository configuration ==
 
== Repository configuration ==
 +
Default location for ''repository.xml'':
 +
<pre>$ vim $JBOSS_HOME/repository.xml</pre>
 +
You can change it by editing [[OpenKM.cfg]]. For more information see [[Application_configuration | Application configuration]].
 
<source lang="xml">
 
<source lang="xml">
 
<?xml version="1.0"?>
 
<?xml version="1.0"?>
Line 53: Line 166:
 
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
 
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
 
           <param name="driver" value="com.mysql.jdbc.Driver"/>
 
           <param name="driver" value="com.mysql.jdbc.Driver"/>
−
           <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true"/>
+
           <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
 
           <param name="schema" value="mysql"/>
 
           <param name="schema" value="mysql"/>
 
           <param name="user" value="openkm"/>
 
           <param name="user" value="openkm"/>
Line 102: Line 215:
 
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
 
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
 
           <param name="driver" value="com.mysql.jdbc.Driver"/>
 
           <param name="driver" value="com.mysql.jdbc.Driver"/>
−
           <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true"/>
+
           <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
 
           <param name="schema" value="mysql"/>
 
           <param name="schema" value="mysql"/>
 
           <param name="user" value="openkm"/>
 
           <param name="user" value="openkm"/>
Line 129: Line 242:
  
 
More info about this at [http://jackrabbit.apache.org/jackrabbit-configuration.html Jackrabbit Configuration].
 
More info about this at [http://jackrabbit.apache.org/jackrabbit-configuration.html Jackrabbit Configuration].
−
 
−
== JBoss datasources ==
 
−
<source lang="xml">
 
−
<local-tx-datasource>
 
−
    <jndi-name>OpenKMDS</jndi-name>
 
−
    <connection-url>jdbc:mysql://localhost:3306/okm_app?autoReconnect=true</connection-url>
 
−
    <driver-class>com.mysql.jdbc.Driver</driver-class>
 
−
    <user-name>openkm</user-name>
 
−
    <password>*****</password>
 
−
    <min-pool-size>5</min-pool-size>
 
−
    <max-pool-size>20</max-pool-size>
 
−
    <idle-timeout-minutes>28680</idle-timeout-minutes>
 
−
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
 
−
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
 
−
    <metadata>
 
−
        <type-mapping>MySQL</type-mapping>
 
−
    </metadata>
 
−
</local-tx-datasource>
 
−
</source>
 
−
 
−
== Login configuration ==
 
−
<source lang="xml">
 
−
<!-- OpenKM -->
 
−
<application-policy name = "OpenKM">
 
−
    <authentication>
 
−
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
 
−
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
 
−
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active=true</module-option>
 
−
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
 
−
            <module-option name="hashAlgorithm">md5</module-option>
 
−
            <module-option name="hashEncoding">hex</module-option>
 
−
        </login-module>
 
−
    </authentication>
 
−
</application-policy>
 
−
</source>
 
  
 
[[Category: Installation Guide]]
 
[[Category: Installation Guide]]
−
[[Category: OKM Network]]
 

Latest revision as of 18:39, 1 December 2012


Nota clasica.png These instructions are also valid for OpenKM 5.1, but #Login configuration has a minor change.

In this OpenKM release you can create the databases automatically configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg. 

hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.hbm2ddl=create

Once the tables are created, change the hibernate.hbm2ddl property from create to none.


Nota advertencia.png This configuration property should be set before the database creation. Once the database has been initialized don't modify it because can damage your installation. If your OpenKM installation has been already configured with another database (default one is an embedded one called HSQL) you can't switch to another database simply changing this property.

More info about this at:

Database creation

Starting with OpenKM 5.0, only two databases are needed: 

DROP DATABASE IF EXISTS okm_repo;
DROP DATABASE IF EXISTS okm_app;

CREATE DATABASE okm_repo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
CREATE DATABASE okm_app DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
GRANT ALL ON okm_repo.* TO openkm@localhost WITH GRANT OPTION;
GRANT ALL ON okm_app.* TO openkm@localhost WITH GRANT OPTION;

You can also change the default collation of a previously created database: 

ALTER DATABASE okm_app DEFAULT COLLATE utf8_bin;

But remember to change the collation of the already created tables: 

ALTER TABLE table_name COLLATE utf8_bin;

This script may help: 

#!/bin/bash
PASSWORD=xxx
DATABASE=okm_app

echo "* Changing collation for database $DATABASE...";
mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER DATABASE $TABLE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;"
TABLES=$(mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "show tables")

for TABLE in $TABLES ; do
    echo "* Changing collation for table $TABLE...";
    mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER TABLE $TABLE CONVERT TO CHARACTER SET utf8;"
    mysql -h localhost -u root -p$PASSWORD $DATABASE -Bse "ALTER TABLE $TABLE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;"
done

More info at MySQL: Case Sensitivity in String Searches.

JBoss datasources

$ vim $JBOSS_HOME/server/default/deploy/openkm-ds.xml
<local-tx-datasource>
    <jndi-name>OpenKMDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_app?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*****</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>28680</idle-timeout-minutes>
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
    <metadata>
        <type-mapping>mySQL</type-mapping>
    </metadata>
</local-tx-datasource>

The type mapping should match a type-mapping/name element from $JBOSS_HOME/server/default/conf/standardjbosscmp-jdbc.xml. Example configurations for many third-party JDBC drivers are included in the $JBOSS_HOME/docs/examples/jca directory.

You may be interested in Encrypting DataSource Passwords.

Login configuration

$ vim $JBOSS_HOME/server/default/conf/login-config.xml
<!-- OpenKM -->
<application-policy name = "OpenKM">
    <authentication>
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active=true</module-option>
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
            <module-option name="hashAlgorithm">md5</module-option>
            <module-option name="hashEncoding">hex</module-option>
        </login-module>
    </authentication>
</application-policy>

For OpenKM 5.1.x this is the right configuration: 

<!-- OpenKM -->
<application-policy name = "OpenKM">
    <authentication>
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active='T'</module-option>
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
            <module-option name="hashAlgorithm">md5</module-option>
            <module-option name="hashEncoding">hex</module-option>
        </login-module>
    </authentication>
</application-policy>

Read also: Dynamic login-config.xml.

Repository configuration

Default location for repository.xml: 

$ vim $JBOSS_HOME/repository.xml

You can change it by editing OpenKM.cfg. For more information see Application configuration. 

<?xml version="1.0"?>
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 1.6//EN"
                            "http://jackrabbit.apache.org/dtd/repository-1.6.dtd">
<Repository>
    <!-- virtual file system where the repository stores global state
        (e.g. registered namespaces, custom node types, etc.) -->
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
        <param name="path" value="${rep.home}/repository"/>
    </FileSystem>

    <!-- Security configuration -->
    <Security appName="OpenKM">
        <!-- Access manager: FQN of class implementing the AccessManager interface -->
        <AccessManager class="com.openkm.core.OKMAccessManager"/>
        <!-- <AccessManager class="org.apache.jackrabbit.core.security.SimpleAccessManager"/> -->
        <!-- <AccessManager class="org.apache.jackrabbit.core.security.DefaultAccessManager"> -->
            <!-- <param name="config" value="${rep.home}/access.xml"/> -->
        <!-- </AccessManager> -->
    </Security>

    <!-- Location of workspaces root directory and name of default workspace -->
    <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>

    <!-- Workspace configuration template:
         used to create the initial workspace if there's no workspace yet -->
    <Workspace name="${wsp.name}">
        <!-- Virtual file system of the workspace:
             class: FQN of class implementing the FileSystem interface -->
        <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
            <param name="path" value="${wsp.home}"/>
        </FileSystem>

        <!-- Persistence manager of the workspace:
             class: FQN of class implementing the PersistenceManager interface -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
          <param name="driver" value="com.mysql.jdbc.Driver"/>
          <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
          <param name="schema" value="mysql"/>
          <param name="user" value="openkm"/>
          <param name="password" value="*****"/>
          <param name="schemaObjectPrefix" value="${wsp.name}_"/>
          <param name="externalBLOBs" value="false"/>
        </PersistenceManager>

        <!-- Search index and the file system it uses.
             class: FQN of class implementing the QueryHandler interface -->
        <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
            <param name="path" value="${wsp.home}/index"/>
            <param name="textFilterClasses" value="
            org.apache.jackrabbit.extractor.PlainTextExtractor,
            org.apache.jackrabbit.extractor.MsWordTextExtractor,
            org.apache.jackrabbit.extractor.MsExcelTextExtractor,
            org.apache.jackrabbit.extractor.MsPowerPointTextExtractor,
            org.apache.jackrabbit.extractor.OpenOfficeTextExtractor,
            org.apache.jackrabbit.extractor.RTFTextExtractor,
            org.apache.jackrabbit.extractor.HTMLTextExtractor,
            org.apache.jackrabbit.extractor.XMLTextExtractor,
            org.apache.jackrabbit.extractor.PngTextExtractor,
            org.apache.jackrabbit.extractor.MsOutlookTextExtractor,
            com.openkm.extractor.PdfTextExtractor,
            com.openkm.extractor.AudioTextExtractor,
            com.openkm.extractor.ExifTextExtractor,
            com.openkm.extractor.TiffTextExtractor,
            com.openkm.extractor.SourceCodeTextExtractor,
            com.openkm.extractor.MsOffice2007TextExtractor"/>
            <param name="extractorPoolSize" value="2"/>
            <param name="supportHighlighting" value="false"/>
            <param name="indexingConfiguration" value="${wsp.home}/../../../indexing_configuration.xml"/>
        </SearchIndex>
    </Workspace>

    <!-- Configures the versioning -->
    <Versioning rootPath="${rep.home}/version">
        <!-- Configures the filesystem to use for versioning for the respective
             persistence manager -->
        <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
            <param name="path" value="${rep.home}/version" />
        </FileSystem>

        <!-- Configures the persistence manager to be used for persisting version state.
             Please note that the current versioning implementation is based on
             a 'normal' persistence manager, but this could change in future
             implementations. -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
          <param name="driver" value="com.mysql.jdbc.Driver"/>
          <param name="url" value="jdbc:mysql://localhost:3306/okm_repo?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
          <param name="schema" value="mysql"/>
          <param name="user" value="openkm"/>
          <param name="password" value="*****"/>
          <param name="schemaObjectPrefix" value="version_"/>
          <param name="externalBLOBs" value="false"/>
        </PersistenceManager>
    </Versioning>

    <!-- Search index for content that is shared repository wide
         (/jcr:system tree, contains mainly versions) -->
    <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
        <param name="path" value="${rep.home}/repository/index"/>
        <param name="textFilterClasses" value=""/>
        <param name="extractorPoolSize" value="2"/>
        <param name="supportHighlighting" value="false"/>
    </SearchIndex>

    <!-- DataStore improve file handling performance -->
    <DataStore class="org.apache.jackrabbit.core.data.FileDataStore">
        <param name="path" value="${rep.home}/repository/datastore"/>
        <param name="minRecordLength" value="100"/>
    </DataStore>
</Repository>

More info about this at Jackrabbit Configuration.

Retrieved from "https://www.openkm.com/wiki/index.php?title=MySQL_-_OpenKM_5.0&oldid=8040"