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

From OpenKM Documentation
Jump to: navigation, search
(→‎JBoss datasources)
 
(37 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{TOCright}} __TOC__
 
{{TOCright}} __TOC__
  
−
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in '''OpenKM.cfg'''.
+
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in [[OpenKM.cfg]]. This configuration is optimized for OpenKM 5.1.5 and newer. For older releases you may nee to set the dialect to "org.hibernate.dialect.OracleDialect".
  
 
<source lang="java">
 
<source lang="java">
−
hibernate.dialect=org.hibernate.dialect.Oracle9iDialect
+
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
−
hibernate.hbm2ddl=create
+
hibernate.hbm2ddl=none
 
</source>
 
</source>
  
−
{{Note|For ''Oracle 10g'' use the ''org.hibernate.dialect.OracleDialect'' configuration. The ''org.hibernate.dialect.Oracle10gDialect'' dialect will be supported in a near future.}}
+
{{Note|In others databases Hibernate can create the tables automagically, but due to some Oracle mapping issues you should create the tables yourself. See [[Oracle#Solving_hibernate_schema_creation_problems|Solving schema table creation problems]] for more info. We will fix this behavior in the next major OpenKM release.}}
−
 
 
−
Once the tables are created, change the '''hibernate.hbm2ddl''' property from ''create'' to ''none''.
 
  
 
{{Warning|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.}}
 
{{Warning|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:
 
More info about this at:
−
* [http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-optional-dialects Hibernate: SQL Dialects]
+
* [http://docs.jboss.org/hibernate/core/3.5/reference/en/html/session-configuration.html#configuration-optional-dialects Hibernate: SQL Dialects]
  
 
== Database creation ==
 
== Database creation ==
−
Starting with OpenKM 5.0, only two databases are needed but can both be in only one because there is no table name collision:
+
Starting with OpenKM 5.0, only one database is needed because there is no table name collision.
−
* okm_repo
+
 
−
* okm_app
+
The right sequence is:
  
−
== Solving some hibernate creation problems ==
+
* Edit [[OpenKM.cfg]] and set:
−
We've found some minimal 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 some script.
 
  
−
Open a sql console and execute the script to create OKM_PROFILE table and insert some fields that can not been executed by default in hibernate creation process.
+
<source lang="java">
 +
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
 +
hibernate.hbm2ddl=none
 +
</source>
 +
 
 +
* Edit and properly modify [[#JBoss datasources|JBoss datasources]].
 +
* Edit and properly modify repository.xml (See [[#Repository configuration|Repository configuration]])
 +
* 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:
  
 
<source lang="sql">
 
<source lang="sql">
−
create table OKM_PROFILE (
+
DROP TABLE DEFAULT_BUNDLE cascade constraints;
−
    PRF_ID integer generated by default as identity (start with 1),
+
DROP TABLE DEFAULT_REFS cascade constraints;
−
    PRF_NAME varchar(255) not null,
+
DROP TABLE DEFAULT_BINVAL cascade constraints;
−
    PRF_ACTIVE bit not null,
+
DROP TABLE DEFAULT_NAMES cascade constraints;
−
    PRF_MSC_ADVANCED_FILTERS bit default 0,
+
DROP SEQUENCE DEFAULT_seq_names_id;
−
    PRF_MSC_USER_QUOTA bigint default 0,
+
DROP TABLE VERSION_BUNDLE cascade constraints;
−
    PRF_MSC_WEB_SKIN varchar(255) default 'default',
+
DROP TABLE VERSION_REFS cascade constraints;
−
    PRF_CHT_CHAT_EN bit default 0,
+
DROP TABLE VERSION_BINVAL cascade constraints;
−
    PRF_CHT_AUTO_LOGIN_EN bit default 0,
+
DROP TABLE VERSION_NAMES cascade constraints;
−
    PRF_WZRD_PROPERTY_GROUPS varchar(255) default '',
+
DROP SEQUENCE VERSION_seq_names_id;
−
    PRF_WZRD_WORKFLOWS varchar(255) default '',
+
CREATE TABLE DEFAULT_BUNDLE (NODE_ID raw(16) NOT NULL, BUNDLE_DATA blob NOT NULL);
−
    PRF_WZRD_KEYWORDS_EN bit default 0,
+
CREATE UNIQUE INDEX DEFAULT_BUNDLE_IDX ON DEFAULT_BUNDLE (NODE_ID);
−
    PRF_WZRD_CATEGORIES_EN bit default 0,
+
CREATE TABLE DEFAULT_REFS (NODE_ID raw(16) NOT NULL, REFS_DATA blob NOT NULL);
−
    PRF_STCK_TAXONOMY_VIS bit default 1,
+
CREATE UNIQUE INDEX DEFAULT_REFS_IDX ON DEFAULT_REFS (NODE_ID);
−
    PRF_STCK_CATEGORIES_VIS bit default 1,
+
CREATE TABLE DEFAULT_BINVAL (BINVAL_ID varchar2(64) NOT NULL, BINVAL_DATA blob NULL);
−
    PRF_STCK_THESAURUS_VIS bit default 1,
+
CREATE UNIQUE INDEX DEFAULT_BINVAL_IDX ON DEFAULT_BINVAL (BINVAL_ID);
−
    PRF_STCK_TEMPLATES_VIS bit default 1,
+
CREATE TABLE DEFAULT_NAMES (ID INTEGER PRIMARY KEY, NAME varchar2(255) NOT NULL);
−
    PRF_STCK_PERSONAL_VIS bit default 1,
+
CREATE UNIQUE INDEX DEFAULT_NAMES_IDX ON DEFAULT_NAMES (NAME);
−
    PRF_STCK_MAIL_VIS bit default 1,
+
CREATE sequence DEFAULT_seq_names_id;
−
    PRF_STCK_TRASH_VIS bit default 1,
+
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;
−
    PRF_TB_DESKTOP_VIS bit default 1,
+
/
−
    PRF_TB_SEARCH_VIS bit default 1,
+
CREATE TABLE VERSION_BUNDLE (NODE_ID raw(16) NOT NULL, BUNDLE_DATA blob NOT NULL);
−
    PRF_TB_DASHBOARD_VIS bit default 1,
+
CREATE UNIQUE INDEX VERSION_BUNDLE_IDX ON VERSION_BUNDLE (NODE_ID);
−
    PRF_TB_ADMIN_VIS bit default 1,
+
CREATE TABLE VERSION_REFS (NODE_ID raw(16) NOT NULL, REFS_DATA blob NOT NULL);
−
    PRF_TB_DOC_PROPS_VIS bit default 1,
+
CREATE UNIQUE INDEX VERSION_REFS_IDX ON VERSION_REFS (NODE_ID);
−
    PRF_TB_DOC_SECURITY_VIS bit default 1,
+
CREATE TABLE VERSION_BINVAL (BINVAL_ID varchar2(64) NOT NULL, BINVAL_DATA blob NULL);
−
    PRF_TB_DOC_NOTES_VIS bit default 1,
+
CREATE UNIQUE INDEX VERSION_BINVAL_IDX ON VERSION_BINVAL (BINVAL_ID);
−
    PRF_TB_DOC_VERSIONS_VIS bit default 1,
+
CREATE TABLE VERSION_NAMES (ID INTEGER PRIMARY KEY, NAME varchar2(255) NOT NULL);
−
    PRF_TB_DOC_PREVIEW_VIS bit default 1,
+
CREATE UNIQUE INDEX VERSION_NAMES_IDX ON VERSION_NAMES (NAME);
−
    PRF_TB_DOC_PROP_GRPS_VIS bit default 1,
+
CREATE sequence VERSION_seq_names_id;
−
    PRF_TB_FLD_PROPS_VIS bit default 1,
+
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;
−
    PRF_TB_FLD_SECURITY_VIS bit default 1,
+
/
−
    PRF_TB_FLD_NOTES_VIS bit default 1,
+
</source>
−
    PRF_TB_ML_PROPS_VIS bit default 1,
+
 
−
    PRF_TB_ML_SECURITY_VIS bit default 1,
+
In addition if you want the datastore stored in database:
−
    PRF_DB_USER_VIS bit default 1,
 
−
    PRF_DB_MAIL_VIS bit default 1,
 
−
    PRF_DB_NEWS_VIS bit default 1,
 
−
    PRF_DB_GENERAL_VIS bit default 1,
 
−
    PRF_DB_WORKFLOW_VIS bit default 1,
 
−
    PRF_DB_KEYWORDS_VIS bit default 1,
 
−
    PRF_MN_FILE_VIS bit default 1,
 
−
    PRF_MN_EDIT_VIS bit default 1,
 
−
    PRF_MN_TOOLS_VIS bit default 1,
 
−
    PRF_MN_BOOKMARKS_VIS bit default 1,
 
−
    PRF_MN_HELP_VIS bit default 1,
 
−
    PRF_MN_FI_CREATE_FLD_VIS bit default 1,
 
−
    PRF_MN_FI_FIND_FLD_VIS bit default 1,
 
−
    PRF_MN_FI_GO_FLD_VIS bit default 1,
 
−
    PRF_MN_FI_DOWNLOAD_VIS bit default 1,
 
−
    PRF_MN_FI_DOWNLOAD_PDF_VIS bit default 1,
 
−
    PRF_MN_FI_ADD_DOC_VIS bit default 1,
 
−
    PRF_MN_FI_START_WORKFLOW_VIS bit default 1,
 
−
    PRF_MN_FI_REFRESH_VIS bit default 1,
 
−
    PRF_MN_FI_SCANNER_VIS bit default 1,
 
−
    PRF_MN_FI_UPLOADER_VIS bit default 1,
 
−
    PRF_MN_FI_PURGE_VIS bit default 1,
 
−
    PRF_MN_FI_PURGE_TRASH_VIS bit default 1,
 
−
    PRF_MN_FI_RESTORE_VIS bit default 1,
 
−
    PRF_MN_FI_EXPORT_VIS bit default 1,
 
−
    PRF_MN_FI_CREATE_FROM_TPL_VIS bit default 1,
 
−
    PRF_MN_FI_SEND_DOC_LINK_VIS bit default 1,
 
−
    PRF_MN_FI_SEND_DOC_ATTACH_VIS bit default 1,
 
−
    PRF_MN_ED_LOCK_VIS bit default 1,
 
−
    PRF_MN_ED_UNLOCK_VIS bit default 1,
 
−
    PRF_MN_ED_CIN_VIS bit default 1,
 
−
    PRF_MN_ED_COUT_VIS bit default 1,
 
−
    PRF_MN_ED_CANCEL_COUT_VIS bit default 1,
 
−
    PRF_MN_ED_DELETE_VIS bit default 1,
 
−
    PRF_MN_ED_RENAME_VIS bit default 1,
 
−
    PRF_MN_ED_COPY_VIS bit default 1,
 
−
    PRF_MN_ED_MOVE_VIS bit default 1,
 
−
    PRF_MN_ED_ADD_PROP_GRP_VIS bit default 1,
 
−
    PRF_MN_ED_REM_PROP_GRP_VIS bit default 1,
 
−
    PRF_MN_ED_ADD_SUBS_VIS bit default 1,
 
−
    PRF_MN_ED_REM_SUBS_VIS bit default 1,
 
−
    PRF_MN_BM_MNG_BOOKMARKS_VIS bit default 1,
 
−
    PRF_MN_BM_ADD_BOOKMARK_VIS bit default 1,
 
−
    PRF_MN_BM_SET_HOME_VIS bit default 1,
 
−
    PRF_MN_BM_GO_HOME_VIS bit default 1,
 
−
    PRF_MN_TL_LANGS_VIS bit default 1,
 
−
    PRF_MN_TL_SKIN_VIS bit default 1,
 
−
    PRF_MN_TL_DEBUG_VIS bit default 1,
 
−
    PRF_MN_TL_ADMIN_VIS bit default 1,
 
−
    PRF_MN_TL_PREFS_VIS bit default 1,
 
−
    PRF_MN_HLP_DOC_VIS bit default 1,
 
−
    PRF_MN_HLP_BUG_TRACKING_VIS bit default 1,
 
−
    PRF_MN_HLP_SUPPORT_VIS bit default 1,
 
−
    PRF_MN_HLP_FORUM_VIS bit default 1,
 
−
    PRF_MN_HLP_CHANGELOG_VIS bit default 1,
 
−
    PRF_MN_HLP_WEB_SITE_VIS bit default 1,
 
−
    PRF_MNU_HLP_ABOUT_VIS bit default 1,
 
−
    primary key (PRF_ID)
 
−
);
 
−
 
−
ALTER TABLE OKM_USER_CONFIG ADD constraint FK7798F4E88FDAFE34 FOREIGN KEY (UC_PROFILE) REFERENCES OKM_PROFILE;
 
−
 
−
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);
+
<source lang="sql">
−
INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES ('UserRole', 1);
+
create table DATASTORE (ID VARCHAR(255) PRIMARY KEY, LENGTH NUMBER, LAST_MODIFIED NUMBER, DATA BLOB);
 +
</source>
  
−
INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES ('okmAdmin', 'AdminRole');
+
== Solving hibernate schema creation problems ==
−
+
{{Note|Starting with OpenKM 5.1.6 the tables and initial database import has no problem, so you don't need all this work.}}
−
INSERT INTO OKM_PROFILE (PRF_NAME, PRF_ACTIVE) VALUES ('Default', 1);
 
  
 +
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:
  
 +
<source lang="sql">
 +
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);
 
</source>
 
</source>
  
Line 158: Line 110:
 
</source>
 
</source>
  
−
{{Note|This type-mapping applies both to Oracle 9i and Oracle 10g. Make sure that you have the  [http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html latest Oracle 10g version of ojdbc14.jar]. See also [http://www.orafaq.com/wiki/JDBC JDBC - Oracle FAQ.]}}
+
{{Note|This type-mapping applies both to Oracle 9i and Oracle 10g. Make sure that you have the  [http://www.oracle.com/technetwork/database/features/jdbc latest Oracle JDBC driver]. OpenKM needs JDK6 to run, so you will need '''ojdbc6.jar''' which is for Oracle 11g but also support Oracle 10g and Oracle 9i. See also [http://www.orafaq.com/wiki/JDBC JDBC - Oracle FAQ]. Read more about datasource configuration at [http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html 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.
 
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.
Line 165: Line 117:
  
 
== Login configuration ==
 
== Login configuration ==
 +
Read also: [http://community.jboss.org/wiki/DynamicLoginConfig Dynamic login-config.xml].
 +
 +
=== OpenKM 5.0 ===
 
<source lang="xml">
 
<source lang="xml">
 
<!-- OpenKM -->
 
<!-- OpenKM -->
Line 172: Line 127:
 
             <module-option name="dsJndiName">java:/OpenKMDS</module-option>
 
             <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="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>
 +
</source>
 +
 +
=== OpenKM 5.1 ===
 +
<source lang="xml">
 +
<!-- 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='T'</module-option>
 
             <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</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="hashAlgorithm">md5</module-option>
Line 191: Line 162:
 
         <param name="path" value="${rep.home}/repository"/>
 
         <param name="path" value="${rep.home}/repository"/>
 
     </FileSystem>
 
     </FileSystem>
−
 
+
 
     <!-- Security configuration -->
 
     <!-- Security configuration -->
 
     <Security appName="OpenKM">
 
     <Security appName="OpenKM">
Line 201: Line 172:
 
         <!-- </AccessManager> -->
 
         <!-- </AccessManager> -->
 
     </Security>
 
     </Security>
−
 
+
 
     <!-- Location of workspaces root directory and name of default workspace -->
 
     <!-- Location of workspaces root directory and name of default workspace -->
 
     <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
 
     <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
−
 
+
 
     <!-- Workspace configuration template:
 
     <!-- Workspace configuration template:
 
         used to create the initial workspace if there's no workspace yet -->
 
         used to create the initial workspace if there's no workspace yet -->
Line 213: Line 184:
 
             <param name="path" value="${wsp.home}"/>
 
             <param name="path" value="${wsp.home}"/>
 
         </FileSystem>
 
         </FileSystem>
−
 
+
 
         <!-- Persistence manager of the workspace:
 
         <!-- Persistence manager of the workspace:
 
             class: FQN of class implementing the PersistenceManager interface -->
 
             class: FQN of class implementing the PersistenceManager interface -->
−
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.Oracle9PersistenceManager">
+
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
−
           <param name="driver" value="oracle.jdbc.OracleDriver"/>
+
           <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
−
           <param name="url" value="jdbc:oracle:thin:@localhost:3306:sid"/>
+
           <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
 
           <param name="schema" value="oracle"/>
 
           <param name="schema" value="oracle"/>
 
           <param name="user" value="openkm"/>
 
           <param name="user" value="openkm"/>
Line 225: Line 196:
 
           <param name="externalBLOBs" value="false"/>
 
           <param name="externalBLOBs" value="false"/>
 
         </PersistenceManager>
 
         </PersistenceManager>
−
 
+
 
         <!-- Search index and the file system it uses.
 
         <!-- Search index and the file system it uses.
 
             class: FQN of class implementing the QueryHandler interface -->
 
             class: FQN of class implementing the QueryHandler interface -->
Line 252: Line 223:
 
         </SearchIndex>
 
         </SearchIndex>
 
     </Workspace>
 
     </Workspace>
−
 
+
 
     <!-- Configures the versioning -->
 
     <!-- Configures the versioning -->
 
     <Versioning rootPath="${rep.home}/version">
 
     <Versioning rootPath="${rep.home}/version">
Line 260: Line 231:
 
             <param name="path" value="${rep.home}/version" />
 
             <param name="path" value="${rep.home}/version" />
 
         </FileSystem>
 
         </FileSystem>
−
 
+
 
         <!-- Configures the persistence manager to be used for persisting version state.
 
         <!-- Configures the persistence manager to be used for persisting version state.
 
             Please note that the current versioning implementation is based on
 
             Please note that the current versioning implementation is based on
 
             a 'normal' persistence manager, but this could change in future
 
             a 'normal' persistence manager, but this could change in future
 
             implementations. -->
 
             implementations. -->
−
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.Oracle9PersistenceManager">
+
         <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
−
           <param name="driver" value="oracle.jdbc.OracleDriver"/>
+
           <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
−
           <param name="url" value="jdbc:oracle:thin:@localhost:3306:sid"/>
+
           <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
 
           <param name="schema" value="oracle"/>
 
           <param name="schema" value="oracle"/>
 
           <param name="user" value="openkm"/>
 
           <param name="user" value="openkm"/>
Line 275: Line 246:
 
         </PersistenceManager>
 
         </PersistenceManager>
 
     </Versioning>
 
     </Versioning>
−
 
+
 
     <!-- Search index for content that is shared repository wide
 
     <!-- Search index for content that is shared repository wide
 
         (/jcr:system tree, contains mainly versions) -->
 
         (/jcr:system tree, contains mainly versions) -->
Line 284: Line 255:
 
         <param name="supportHighlighting" value="false"/>
 
         <param name="supportHighlighting" value="false"/>
 
     </SearchIndex>
 
     </SearchIndex>
−
 
+
 
     <!-- DataStore improve file handling performance -->
 
     <!-- DataStore improve file handling performance -->
 
     <DataStore class="org.apache.jackrabbit.core.data.FileDataStore">
 
     <DataStore class="org.apache.jackrabbit.core.data.FileDataStore">
Line 291: Line 262:
 
     </DataStore>
 
     </DataStore>
 
</Repository>
 
</Repository>
 +
</source>
 +
 +
If you want all your information stored in the database, you can replace the FileDataStore configuration by DbDatastore:
 +
 +
<source lang="xml">
 +
<!-- DataStore improve file handling performance -->
 +
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
 +
    <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
 +
    <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
 +
    <param name="databaseType" value="oracle"/>
 +
    <param name="user" value="openkm"/>
 +
    <param name="password" value="*****"/>
 +
    <param name="minRecordLength" value="100"/>
 +
</DataStore>
 
</source>
 
</source>
  
Line 296: Line 281:
  
 
[[Category: Installation Guide]]
 
[[Category: Installation Guide]]
−
[[Category: OKM Network]]
 

Latest revision as of 11:34, 6 June 2013

In this OpenKM release you can create the databases automatically configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg. This configuration is optimized for OpenKM 5.1.5 and newer. For older releases you may nee to set the dialect to "org.hibernate.dialect.OracleDialect". 

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.hbm2ddl=none

Nota clasica.png In others databases Hibernate can create the tables automagically, but due to some Oracle mapping issues you should create the tables yourself. See Solving schema table creation problems for more info. We will fix this behavior in the next major OpenKM release.


Nota advertencia.png 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:

Database creation

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

The right sequence is: 

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.hbm2ddl=none
  • Edit and properly modify JBoss datasources.
  • Edit and properly modify repository.xml (See Repository configuration)
  • 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: 

DROP TABLE DEFAULT_BUNDLE cascade constraints;
DROP TABLE DEFAULT_REFS cascade constraints;
DROP TABLE DEFAULT_BINVAL cascade constraints;
DROP TABLE DEFAULT_NAMES cascade constraints;
DROP SEQUENCE DEFAULT_seq_names_id;
DROP TABLE VERSION_BUNDLE cascade constraints;
DROP TABLE VERSION_REFS cascade constraints;
DROP TABLE VERSION_BINVAL cascade constraints;
DROP TABLE VERSION_NAMES cascade constraints;
DROP SEQUENCE VERSION_seq_names_id;
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


Nota clasica.png Starting with OpenKM 5.1.6 the tables and initial database import has no problem, so you don't need all this work.

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>

Nota clasica.png This type-mapping applies both to Oracle 9i and Oracle 10g. Make sure that you have the latest Oracle JDBC driver. OpenKM needs JDK6 to run, so you will need ojdbc6.jar which is for Oracle 11g but also support Oracle 10g and Oracle 9i. 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

Read also: Dynamic login-config.xml.

OpenKM 5.0

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

OpenKM 5.1

<!-- 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='T'</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>

If you want all your information stored in the database, you can replace the FileDataStore configuration by DbDatastore: 

<!-- DataStore improve file handling performance -->
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="driver" value="oracle.jdbc.driver.OracleDriver"/>
    <param name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
    <param name="databaseType" value="oracle"/>
    <param name="user" value="openkm"/>
    <param name="password" value="*****"/>
    <param name="minRecordLength" value="100"/>
</DataStore>

More info about this at Jackrabbit Configuration.

Retrieved from "https://www.openkm.com/wiki/index.php?title=Knowledge:Oracle_-_OpenKM_5.1&oldid=9835"