Difference between revisions of "MySQL"

From OpenKM Documentation
Jump to: navigation, search
(→‎JBoss datasources)
Line 137: Line 137:
 
<source lang="xml">
 
<source lang="xml">
 
<?xml version="1.0" encoding="UTF-8"?>
 
<?xml version="1.0" encoding="UTF-8"?>
−
 
 
<datasources>
 
<datasources>
 
   <!-- OpenKM User Activity -->
 
   <!-- OpenKM User Activity -->
 
   <local-tx-datasource>
 
   <local-tx-datasource>
 
     <jndi-name>OKMActivityDS</jndi-name>
 
     <jndi-name>OKMActivityDS</jndi-name>
−
     <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMActivity</connection-url>
+
     <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
−
     <driver-class>org.hsqldb.jdbcDriver</driver-class>
+
     <driver-class>com.mysql.jdbc.Driver</driver-class>
−
     <user-name>sa</user-name>
+
     <user-name>root</user-name>
−
     <password></password>
+
     <password>xxx</password>
 
     <min-pool-size>5</min-pool-size>
 
     <min-pool-size>5</min-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <track-statements/>
 
     <track-statements/>
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <metadata>
 
     <metadata>
−
       <type-mapping>Hypersonic SQL</type-mapping>
+
       <type-mapping>MySQL SQL</type-mapping>
 
     </metadata>
 
     </metadata>
−
    <depends>jboss:service=Hypersonic,database=OKMActivity</depends>
 
 
   </local-tx-datasource>
 
   </local-tx-datasource>
−
 
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
 
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMActivity">
 
−
    <attribute name="Database">OKMActivity</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
 
   
 
   
 
   <!-- OpenKM User Auth -->
 
   <!-- OpenKM User Auth -->
 
   <local-tx-datasource>
 
   <local-tx-datasource>
 
     <jndi-name>OKMAuthDS</jndi-name>
 
     <jndi-name>OKMAuthDS</jndi-name>
−
     <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMAuth</connection-url>
+
     <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
−
     <driver-class>org.hsqldb.jdbcDriver</driver-class>
+
     <driver-class>com.mysql.jdbc.Driver</driver-class>
−
     <user-name>sa</user-name>
+
     <user-name>root</user-name>
−
     <password></password>
+
     <password>xxx</password>
 
     <min-pool-size>5</min-pool-size>
 
     <min-pool-size>5</min-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <track-statements/>
 
     <track-statements/>
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <metadata>
 
     <metadata>
−
       <type-mapping>Hypersonic SQL</type-mapping>
+
       <type-mapping>MySQL SQL</type-mapping>
 
     </metadata>
 
     </metadata>
−
    <depends>jboss:service=Hypersonic,database=OKMAuth</depends>
 
 
   </local-tx-datasource>
 
   </local-tx-datasource>
−
 
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
 
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMAuth">
 
−
    <attribute name="Database">OKMAuth</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
 
    
 
    
 
   <!-- OpenKM Dashboard Stats -->
 
   <!-- OpenKM Dashboard Stats -->
 
   <local-tx-datasource>
 
   <local-tx-datasource>
 
     <jndi-name>OKMDashboardStatsDS</jndi-name>
 
     <jndi-name>OKMDashboardStatsDS</jndi-name>
−
     <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMDashboardStats</connection-url>
+
     <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
−
     <driver-class>org.hsqldb.jdbcDriver</driver-class>
+
     <driver-class>com.mysql.jdbc.Driver</driver-class>
−
     <user-name>sa</user-name>
+
     <user-name>root</user-name>
−
     <password></password>
+
     <password>xxx</password>
 
     <min-pool-size>5</min-pool-size>
 
     <min-pool-size>5</min-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <track-statements/>
 
     <track-statements/>
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <metadata>
 
     <metadata>
−
       <type-mapping>Hypersonic SQL</type-mapping>
+
       <type-mapping>MySQL SQL</type-mapping>
 
     </metadata>
 
     </metadata>
−
    <depends>jboss:service=Hypersonic,database=OKMDashboardStats</depends>
 
 
   </local-tx-datasource>
 
   </local-tx-datasource>
−
 
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
 
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMDashboardStats">
 
−
    <attribute name="Database">OKMDashboardStats</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
 
   
 
   
 
   <!-- OpenKM Workflow -->
 
   <!-- OpenKM Workflow -->
 
   <local-tx-datasource>
 
   <local-tx-datasource>
 
     <jndi-name>OKMWorkflowDS</jndi-name>
 
     <jndi-name>OKMWorkflowDS</jndi-name>
−
     <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMWorkflow</connection-url>
+
     <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
−
     <driver-class>org.hsqldb.jdbcDriver</driver-class>
+
     <driver-class>com.mysql.jdbc.Driver</driver-class>
−
     <user-name>sa</user-name>
+
     <user-name>root</user-name>
−
     <password></password>
+
     <password>xxx</password>
 
     <min-pool-size>5</min-pool-size>
 
     <min-pool-size>5</min-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <max-pool-size>20</max-pool-size>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <idle-timeout-minutes>0</idle-timeout-minutes>
 
     <track-statements/>
 
     <track-statements/>
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
     <metadata>
 
     <metadata>
−
       <type-mapping>Hypersonic SQL</type-mapping>
+
       <type-mapping>MySQL SQL</type-mapping>
 
     </metadata>
 
     </metadata>
−
    <depends>jboss:service=Hypersonic,database=OKMWorkflow</depends>
 
 
   </local-tx-datasource>
 
   </local-tx-datasource>
−
 
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
 
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMWorkflow">
 
−
    <attribute name="Database">OKMWorkflow</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
 
</datasources>
 
</datasources>
 
</source>
 
</source>

Revision as of 11:13, 25 January 2010

Download MySQL JDBC driver from MySQL Home Page and move it to $JBOSS_HOME/server/default/lib.

Repository configuration

<?xml version="1.0" encoding="UTF-8"?>
<Repository>
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${rep.home}/repository"/>
  </FileSystem>
  <Security appName="OpenKM">
    <AccessManager class="com.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/openkm?autoReconnect=true"/>
      <param name="schema" value="mysql"/>
      <param name="user" value="root"/>
      <param name="password" value="xxx"/>
      <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,
com.openkm.extractor.MsExcelTextExtractor,
com.openkm.extractor.MsPowerPointTextExtractor,
com.openkm.extractor.MsWordTextExtractor,
com.openkm.extractor.MsOffice2007TextExtractor,
com.openkm.extractor.ExifTextExtractor,
com.openkm.extractor.TiffTextExtractor,
com.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/openkm?autoReconnect=true"/>
      <param name="schema" value="mysql"/>
      <param name="user" value="root"/>
      <param name="password" value="xxx"/>
      <param name="schemaObjectPrefix" value="version_"/>
      <param name="externalBLOBs" value="false"/>
    </PersistenceManager>
  </Versioning>
</Repository>

Database creation

#
# 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>

<!-- JDBC connection properties (begin) ===
<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="hibernate.connection.url">jdbc:hsqldb:mem:jbpm</property>
<property name="hibernate.connection.username">sa</property>
<property name="hibernate.connection.password"></property>
==== JDBC connection properties (end) -->

to 

<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>

<!-- JDBC connection properties (begin) -->
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/openkm</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">xxx</property>
<!-- JDBC connection properties (end) -->

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/openkm</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>xxx</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 SQL</type-mapping>
    </metadata>
  </local-tx-datasource>
 
  <!-- OpenKM User Auth -->
  <local-tx-datasource>
    <jndi-name>OKMAuthDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>xxx</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 SQL</type-mapping>
    </metadata>
  </local-tx-datasource>
   
  <!-- OpenKM Dashboard Stats -->
  <local-tx-datasource>
    <jndi-name>OKMDashboardStatsDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>xxx</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 SQL</type-mapping>
    </metadata>
  </local-tx-datasource>
 
  <!-- OpenKM Workflow -->
  <local-tx-datasource>
    <jndi-name>OKMWorkflowDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/openkm</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>xxx</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 SQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>
Retrieved from "https://www.openkm.com/wiki/index.php?title=MySQL&oldid=484"