Difference between revisions of "Knowledge:Oracle - OpenKM 5.1"

Revision as of 13:45, 20 January 2011

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:

Hibernate: SQL Dialects

Database creation

Starting with OpenKM 5.0, only one database is needed because there is no table name collision.

The right sequence is:

Edit OpenKM.cfg and set:
- hibernate.dialect=org.hibernate.dialect.OracleDialect
- hibernate.hbm2ddl=none
Edit and properly modify JBoss datasources.
Edit and properly modify repository.xml
Execute the table creation script.
Execute the inserts to create a minimal environment (see next point)
Start JBoss.

The application will create some tables related to Jackrabbit persistence, so the Oracle user should have the right permissions. If not possible, this creation script need to be execute to create the Jackrabbit related tables:

create table DEFAULT_BUNDLE (NODE_ID raw(16) not null, BUNDLE_DATA blob not null);
create unique index DEFAULT_BUNDLE_IDX on DEFAULT_BUNDLE (NODE_ID);
create table DEFAULT_REFS (NODE_ID raw(16) not null, REFS_DATA blob not null);
create unique index DEFAULT_REFS_IDX on DEFAULT_REFS (NODE_ID);
create table DEFAULT_BINVAL (BINVAL_ID varchar2(64) not null, BINVAL_DATA blob null);
create unique index DEFAULT_BINVAL_IDX on DEFAULT_BINVAL (BINVAL_ID);
create table DEFAULT_NAMES (ID INTEGER primary key, NAME varchar2(255) not null);
create unique index DEFAULT_NAMES_IDX on DEFAULT_NAMES (NAME);
create sequence DEFAULT_seq_names_id;
create trigger DEFAULT_t1 before insert on DEFAULT_NAMES for each row begin select DEFAULT_seq_names_id.nextval into :new.id from dual; end;
/
create table VERSION_BUNDLE (NODE_ID raw(16) not null, BUNDLE_DATA blob not null);
create unique index VERSION_BUNDLE_IDX on VERSION_BUNDLE (NODE_ID);
create table VERSION_REFS (NODE_ID raw(16) not null, REFS_DATA blob not null);
create unique index VERSION_REFS_IDX on VERSION_REFS (NODE_ID);
create table VERSION_BINVAL (BINVAL_ID varchar2(64) not null, BINVAL_DATA blob null);
create unique index VERSION_BINVAL_IDX on VERSION_BINVAL (BINVAL_ID);
create table VERSION_NAMES (ID INTEGER primary key, NAME varchar2(255) not null);
create unique index VERSION_NAMES_IDX on VERSION_NAMES (NAME);
create sequence VERSION_seq_names_id;
create trigger VERSION_t1 before insert on VERSION_NAMES for each row begin select VERSION_seq_names_id.nextval into :new.id from dual; end;
/

In addition if you want the datastore stored in database:

create table DATASTORE (ID VARCHAR(255) PRIMARY KEY, LENGTH NUMBER, LAST_MODIFIED NUMBER, DATA BLOB);

Solving hibernate schema creation problems

We've found some problems in default hibernate creation database script in okm_app database. It'll be needed to connect to database and make minimal changes and execute this script at File:Oracle-schema-okm-5.0.sql.

Also run these inserts to create a minimal environment:

insert into OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) values ('okmAdmin', 'Administrator', '21232f297a57a5a743894a0e4a801fc3', 'admin@noreply.com', 1);
insert into OKM_ROLE (ROL_ID, ROL_ACTIVE) values ('AdminRole', 1);
insert into OKM_ROLE (ROL_ID, ROL_ACTIVE) values ('UserRole', 1);
insert into OKM_USER_ROLE (UR_USER, UR_ROLE) values ('okmAdmin', 'AdminRole');
insert into OKM_PROFILE (PRF_ID, PRF_NAME, PRF_ACTIVE) values (1, 'Default', 1);

JBoss datasources

<local-tx-datasource>
    <jndi-name>OpenKMDS</jndi-name>
    <connection-url>jdbc:oracle:thin:@localhost:1521:sid</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</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>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
    <metadata>
        <type-mapping>Oracle9i</type-mapping>
    </metadata>
</local-tx-datasource>

This type-mapping applies both to Oracle 9i and Oracle 10g. Make sure that you have the latest Oracle 10g version of ojdbc14.jar. See also JDBC - Oracle FAQ. Read more about datasource configuration at Configuring JDBC DataSources.

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

<!-- 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=1</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>

Repository 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.OraclePersistenceManager">
          <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
          <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
          <param name="schema" value="oracle"/>
          <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.OraclePersistenceManager">
          <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
          <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
          <param name="schema" value="oracle"/>
          <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.

Difference between revisions of "Knowledge:Oracle - OpenKM 5.1"

Revision as of 13:45, 20 January 2011

Contents

Database creation

Solving hibernate schema creation problems

JBoss datasources

Login configuration

Repository configuration

Navigation menu

Views

Personal tools

Navigation

Search

Tools