Difference between revisions of "PostgreSQL - OpenKM 4.1"
From OpenKM Documentation
(Created page with '{{TOCright}} __TOC__ == Repository configuration == <source lang="xml"> <?xml version="1.0" encoding="UTF-8"?> <Repository> <FileSystem class="org.apache.jackrabbit.core.fs.lo…') |
(→Repository configuration) |
||
| Line 1: | Line 1: | ||
{{TOCright}} __TOC__ | {{TOCright}} __TOC__ | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
== Table creation == | == Table creation == | ||
Revision as of 14:49, 16 September 2010
Table 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 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');
#
# 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.PostgreSQLDialect</property>
And in File:Jbpm.jpdl.postgresql.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: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>
More info about this at Configuring JDBC DataSources.
