Difference between revisions of "PostgreSQL"

From OpenKM Documentation
Jump to: navigation, search
 
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{TOCright}} __TOC__
 
{{TOCright}} __TOC__
  
−
Download PostgreSQL JDBC driver from [http://jdbc.postgresql.org/ PostgreSQL Driver Page] and move it to ''$JBOSS_HOME/server/default/lib''.
+
Download PostgreSQL JDBC driver from [http://jdbc.postgresql.org/ PostgreSQL Driver Page] and move it to ''$JBOSS_HOME/server/default/lib'' if you have OpenKM-5.X and ''$TOMCAT_HOME/lib'' for OpenKM-6.X.
  
−
== Repository configuration ==
+
{{Note|'''OpenKM 5.1.8''' has been verified to run with '''PostgreSQL 9.1''' using the JDBC driver '''postgresql-9.1-901.jdbc4.jar''' under JBoss 4.2.3.}}
−
<source lang="xml">
 
−
<?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.PostgreSQLPersistenceManager">
 
−
      <param name="driver" value="org.postgresql.Driver"/>
 
−
      <param name="url" value="jdbc:postgresql://localhost:5432/openkm?autoReconnect=true"/>
 
−
      <param name="schema" value="postgresql"/>
 
−
      <param name="user" value="db_user"/>
 
−
      <param name="password" value="db_pass"/>
 
−
      <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.PostgreSQLPersistenceManager">
 
−
      <param name="driver" value="org.postgresql.Driver"/>
 
−
      <param name="url" value="jdbc:postgresql://localhost:5432/openkm?autoReconnect=true"/>
 
−
      <param name="schema" value="postgresql"/>
 
−
      <param name="user" value="db_user"/>
 
−
      <param name="password" value="db_pass"/>
 
−
      <param name="schemaObjectPrefix" value="version_"/>
 
−
      <param name="externalBLOBs" value="false"/>
 
−
    </PersistenceManager>
 
−
  </Versioning>
 
−
  <!-- Also see DatabaseDataStore-->
 
−
  <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
 
−
</Repository>
 
−
</source>
 
  
−
More info about this at [http://jackrabbit.apache.org/jackrabbit-configuration.html Jackrabbit Configuration].
+
* [[PostgreSQL-OpenKM 6.0 | OpenKM 6.0]]  (also valid for OpenKM 6.X+)
 +
* [[PostgreSQL-OpenKM 5.0 | OpenKM 5.0]] (also valid for OpenKM 5.X+)
 +
* [[PostgreSQL-OpenKM 4.1 | OpenKM 4.1]]
  
−
== Table creation ==
+
If you want to obtain the maximun performance from PostgreSQL, [http://pgfouine.projects.postgresql.org/index.html pgFouine] will help in your task.
−
<source lang="sql">
 
−
#
 
−
# 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));
 
−
</source>
 
−
 
 
−
<source lang="sql">
 
−
#
 
−
# 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 SERIAL, 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 SERIAL, 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');
 
−
</source>
 
−
 
 
−
<source lang="sql">
 
−
#
 
−
# Generic dashboard stats database definition
 
−
#
 
−
CREATE TABLE dashboard_stats(ds_user VARCHAR(32), ds_source VARCHAR(64), ds_node VARCHAR(256), ds_date TIMESTAMP);
 
−
</source>
 
−
 
 
−
== 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:
 
−
 
 
−
<source lang="xml">
 
−
<!-- hibernate dialect -->
 
−
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
 
−
</source>
 
−
 
 
−
to
 
−
 
 
−
<source lang="xml">
 
−
<!-- hibernate dialect -->
 
−
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
 
−
</source>
 
−
 
 
−
And in [[File:Jbpm.jpdl.postgresql.sql]] are the sententeces needed to create the tables. More info about this at [http://docs.jboss.org/jbpm/v3/userguide/thejbpmdatabase.html The jBPM Database].
 
−
 
 
−
== JBoss datasources ==
 
−
<source lang="xml">
 
−
<?xml version="1.0" encoding="UTF-8"?>
 
−
<datasources>
 
−
  <!-- OpenKM User Activity -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMActivityDS</jndi-name>
 
−
    <connection-url>jdbc:postgresql://localhost:5432/openkm</connection-url>
 
−
    <driver-class>org.postgresql.Driver</driver-class>
 
−
    <user-name>db_user</user-name>
 
−
    <password>db_pass</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>PostgreSQL</type-mapping>
 
−
    </metadata>
 
−
  </local-tx-datasource>
 
−
 
−
  <!-- OpenKM User Auth -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMAuthDS</jndi-name>
 
−
    <connection-url>jdbc:postgresql://localhost:5432/openkm</connection-url>
 
−
    <driver-class>org.postgresql.Driver</driver-class>
 
−
    <user-name>db_user</user-name>
 
−
    <password>db_pass</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>PostgreSQL</type-mapping>
 
−
    </metadata>
 
−
  </local-tx-datasource>
 
−
 
 
−
  <!-- OpenKM Dashboard Stats -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMDashboardStatsDS</jndi-name>
 
−
    <connection-url>jdbc:postgresql://localhost:5432/openkm</connection-url>
 
−
    <driver-class>org.postgresql.Driver</driver-class>
 
−
    <user-name>db_user</user-name>
 
−
    <password>db_pass</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>PostgreSQL</type-mapping>
 
−
    </metadata>
 
−
  </local-tx-datasource>
 
−
 
−
  <!-- OpenKM Workflow -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMWorkflowDS</jndi-name>
 
−
    <connection-url>jdbc:postgresql://localhost:5432/openkm</connection-url>
 
−
    <driver-class>org.postgresql.Driver</driver-class>
 
−
    <user-name>db_user</user-name>
 
−
    <password>db_pass</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>PostgreSQL</type-mapping>
 
−
    </metadata>
 
−
  </local-tx-datasource>
 
−
</datasources>
 
−
</source>
 
−
 
 
−
More info about this at [http://www.jboss.org/file-access/default/members/jbossas/freezone/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html Configuring JDBC DataSources].
 
  
 
[[Category: Installation Guide]]
 
[[Category: Installation Guide]]
−
[[Category:OKM Network]]
 

Latest revision as of 09:27, 5 September 2013

Download PostgreSQL JDBC driver from PostgreSQL Driver Page and move it to $JBOSS_HOME/server/default/lib if you have OpenKM-5.X and $TOMCAT_HOME/lib for OpenKM-6.X.


Nota clasica.png OpenKM 5.1.8 has been verified to run with PostgreSQL 9.1 using the JDBC driver postgresql-9.1-901.jdbc4.jar under JBoss 4.2.3.

If you want to obtain the maximun performance from PostgreSQL, pgFouine will help in your task.

Retrieved from "https://www.openkm.com/wiki/index.php?title=PostgreSQL&oldid=10216"