Difference between revisions of "MySQL - OpenKM 4.1"
From OpenKM Documentation
m |
m |
||
Line 270: | Line 270: | ||
[[Category: Installation Guide]] | [[Category: Installation Guide]] | ||
− |
Revision as of 17:24, 1 October 2012
Database creation
The first thing to do is create the required databases where the tables will be created:
DROP DATABASE IF EXISTS okm_repository;
DROP DATABASE IF EXISTS okm_activity;
DROP DATABASE IF EXISTS okm_auth;
DROP DATABASE IF EXISTS okm_dashboard;
DROP DATABASE IF EXISTS okm_workflow;
CREATE DATABASE okm_repository DEFAULT CHARACTER SET utf8;
CREATE DATABASE okm_activity DEFAULT CHARACTER SET utf8;
CREATE DATABASE okm_auth DEFAULT CHARACTER SET utf8;
CREATE DATABASE okm_dashboard DEFAULT CHARACTER SET utf8;
CREATE DATABASE okm_workflow DEFAULT CHARACTER SET utf8;
GRANT USAGE ON *.* TO 'openkm'@'%' IDENTIFIED BY '*secret*';
GRANT ALL PRIVILEGES ON *.* TO 'openkm'@'%';
Table creation
In this OpenKM 4.1 release you can create the databases automatically configuring the system.database property in OpenKM.cfg.
system.database=mysql
OpenKM 4.0 and older
#
# Generic activity database definition
#
CREATE TABLE activity(act_date TIMESTAMP, act_user VARCHAR(32), act_token VARCHAR(48), act_action VARCHAR(48), act_item VARCHAR(256), act_params VARCHAR(256));
#
# Generic auth database definition
#
CREATE TABLE users(usr_id VARCHAR(32), usr_name VARCHAR(64), usr_pass VARCHAR(32) NOT NULL, usr_email VARCHAR(32) NOT NULL, usr_active BOOLEAN, PRIMARY KEY(usr_id));
CREATE TABLE roles(rol_id VARCHAR(32), PRIMARY KEY(rol_id));
CREATE TABLE user_role(ur_user VARCHAR(32), ur_role VARCHAR(32), PRIMARY KEY(ur_user, ur_role));
CREATE TABLE mail_accounts(ma_id INTEGER AUTO_INCREMENT, ma_user VARCHAR(32), ma_mhost VARCHAR(32), ma_muser VARCHAR(32), ma_mpass VARCHAR(32), ma_mfolder VARCHAR(32), ma_active BOOLEAN, PRIMARY KEY(ma_id));
CREATE TABLE twitter_accounts(ta_id INTEGER AUTO_INCREMENT, ta_user VARCHAR(32), ta_tuser VARCHAR(32), ta_active BOOLEAN, PRIMARY KEY(ta_id));
# INSERT DEFAULT USER / ROLES
INSERT INTO users (usr_id, usr_name, usr_pass, usr_email, usr_active) VALUES ('okmAdmin', 'Administrator', '21232f297a57a5a743894a0e4a801fc3', '', true);
INSERT INTO roles (rol_id) VALUES ('AdminRole');
INSERT INTO roles (rol_id) VALUES ('UserRole');
INSERT INTO user_role (ur_user, ur_role) VALUES ('okmAdmin', 'AdminRole');
#
# Generic dashboard stats database definition
#
CREATE TABLE dashboard_stats(ds_user VARCHAR(32), ds_source VARCHAR(64), ds_node VARCHAR(256), ds_date TIMESTAMP);
Workflow engine
You have to modify the file WEB-INF/classes/hibernate.cfg.xml located inside the OpenKM.war archive. These are the entries to change:
<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
to
<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
And in File:Jbpm.jpdl.mysql.sql are the sententeces needed to create the tables. More info about this at:
JBoss datasources
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<!-- OpenKM User Activity -->
<local-tx-datasource>
<jndi-name>OKMActivityDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/okm_activity?autoReconnect=true</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>openkm</user-name>
<password>*secret*</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
</local-tx-datasource>
<!-- OpenKM User Auth -->
<local-tx-datasource>
<jndi-name>OKMAuthDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/okm_auth?autoReconnect=true</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>openkm</user-name>
<password>*secret*</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
</local-tx-datasource>
<!-- OpenKM Dashboard Stats -->
<local-tx-datasource>
<jndi-name>OKMDashboardStatsDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/okm_dashboard?autoReconnect=true</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>openkm</user-name>
<password>*secret*</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
</local-tx-datasource>
<!-- OpenKM Workflow -->
<local-tx-datasource>
<jndi-name>OKMWorkflowDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/okm_workflow?autoReconnect=true</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>openkm</user-name>
<password>*secret*</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
More info about this at Configuring JDBC DataSources.
Login configuration
Due to the nature of HSQL database and the booleans, you need a little modification in the $JBOSS_HOME/server/default/conf/login-config.xml. Edit this file and change:
<!-- OpenKM -->
<application-policy name = "OpenKM">
<authentication>
<login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
<module-option name="dsJndiName">java:/OKMAuthDS</module-option>
<module-option name="principalsQuery">select usr_pass as PASSWD from users where usr_id=? and usr_active='true'</module-option>
<module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from 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>
to
<!-- OpenKM -->
<application-policy name = "OpenKM">
<authentication>
<login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
<module-option name="dsJndiName">java:/OKMAuthDS</module-option>
<module-option name="principalsQuery">select usr_pass as PASSWD from users where usr_id=? and usr_active=true</module-option>
<module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from 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>
Repository configuration
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 1.4//EN"
"http://jackrabbit.apache.org/dtd/repository-1.4.dtd">
<Repository>
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${rep.home}/repository"/>
</FileSystem>
<Security appName="OpenKM">
<AccessManager class="es.git.openkm.core.OKMAccessManager"/>
</Security>
<Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
<Workspace name="${wsp.name}">
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${wsp.home}"/>
</FileSystem>
<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_repository?autoReconnect=true"/>
<param name="schema" value="mysql"/>
<param name="user" value="openkm"/>
<param name="password" value="*secret*"/>
<param name="schemaObjectPrefix" value="${wsp.name}_"/>
<param name="externalBLOBs" value="false"/>
</PersistenceManager>
<SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
<param name="path" value="${wsp.home}/index"/>
<param name="useCompoundFile" value="true"/>
<param name="minMergeDocs" value="100"/>
<param name="volatileIdleTime" value="3"/>
<param name="maxMergeDocs" value="100000"/>
<param name="mergeFactor" value="10"/>
<param name="bufferSize" value="10"/>
<param name="cacheSize" value="1000"/>
<param name="forceConsistencyCheck" value="false"/>
<param name="autoRepair" value="true"/>
<param name="analyzer" value="org.apache.lucene.analysis.standard.StandardAnalyzer"/>
<param name="respectDocumentOrder" value="false"/>
<param name="indexingConfiguration" value="$ {wsp.home}/../../../indexing_configuration.xml"/>
<param name="textFilterClasses" value="
org.apache.jackrabbit.core.query.lucene.TextPlainTextFilter,
org.apache.jackrabbit.extractor.PdfTextExtractor,
org.apache.jackrabbit.extractor.HTMLTextExtractor,
org.apache.jackrabbit.extractor.XMLTextExtractor,
org.apache.jackrabbit.extractor.RTFTextExtractor,
org.apache.jackrabbit.extractor.OpenOfficeTextExtractor,
es.git.openkm.extractor.MsExcelTextExtractor,
es.git.openkm.extractor.MsPowerPointTextExtractor,
es.git.openkm.extractor.MsWordTextExtractor,
es.git.openkm.extractor.MsOffice2007TextExtractor,
es.git.openkm.extractor.ExifTextExtractor,
es.git.openkm.extractor.TiffTextExtractor,
es.git.openkm.extractor.AudioTextExtractor" />
</SearchIndex>
</Workspace>
<Versioning rootPath="${rep.home}/version">
<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
<param name="path" value="${rep.home}/version"/>
</FileSystem>
<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_repository?autoReconnect=true"/>
<param name="schema" value="mysql"/>
<param name="user" value="openkm"/>
<param name="password" value="*secret*"/>
<param name="schemaObjectPrefix" value="version_"/>
<param name="externalBLOBs" value="false"/>
</PersistenceManager>
</Versioning>
<!-- Also see DatabaseDataStore-->
<DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
</Repository>
More info about this at Jackrabbit Configuration.