Difference between revisions of "Knowledge:Oracle - OpenKM 5.1"
Line 131: | Line 131: | ||
PRF_MN_HLP_WEB_SITE_VIS bit default 1, | PRF_MN_HLP_WEB_SITE_VIS bit default 1, | ||
PRF_MNU_HLP_ABOUT_VIS bit default 1, | PRF_MNU_HLP_ABOUT_VIS bit default 1, | ||
− | + | primary key (PRF_ID) | |
); | ); | ||
Revision as of 10:03, 7 November 2010
In this OpenKM release you can create the databases automatically configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg.
hibernate.dialect=org.hibernate.dialect.Oracle9iDialect
hibernate.hbm2ddl=create
For Oracle 10g use the org.hibernate.dialect.Oracle10gDialect configuration. |
Once the tables are created, change the hibernate.hbm2ddl property from create to none.
More info about this at:
Database creation
Starting with OpenKM 5.0, only two databases are needed:
DROP DATABASE IF EXISTS okm_repo;
DROP DATABASE IF EXISTS okm_app;
CREATE DATABASE okm_repo CHARACTER SET UTF8;
CREATE DATABASE okm_app CHARACTER SET UTF8;
CREATE USER openkm IDENTIFIED BY '*secret*';
GRANT CONNECT, RESOURCE TO openkm;
GRANT ALL ON okm_repo TO openkm;
GRANT ALL ON okm_app TO openkm;
Solving some hibernate creation problems
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.
create table OKM_PROFILE (
PRF_ID integer generated by default as identity (start with 1),
PRF_NAME varchar(255) not null,
PRF_ACTIVE bit not null,
PRF_MSC_ADVANCED_FILTERS bit default 0,
PRF_MSC_USER_QUOTA bigint default 0,
PRF_MSC_WEB_SKIN varchar(255) default 'default',
PRF_CHT_CHAT_EN bit default 0,
PRF_CHT_AUTO_LOGIN_EN bit default 0,
PRF_WZRD_PROPERTY_GROUPS varchar(255) default '',
PRF_WZRD_WORKFLOWS varchar(255) default '',
PRF_WZRD_KEYWORDS_EN bit default 0,
PRF_WZRD_CATEGORIES_EN bit default 0,
PRF_STCK_TAXONOMY_VIS bit default 1,
PRF_STCK_CATEGORIES_VIS bit default 1,
PRF_STCK_THESAURUS_VIS bit default 1,
PRF_STCK_TEMPLATES_VIS bit default 1,
PRF_STCK_PERSONAL_VIS bit default 1,
PRF_STCK_MAIL_VIS bit default 1,
PRF_STCK_TRASH_VIS bit default 1,
PRF_TB_DESKTOP_VIS bit default 1,
PRF_TB_SEARCH_VIS bit default 1,
PRF_TB_DASHBOARD_VIS bit default 1,
PRF_TB_ADMIN_VIS bit default 1,
PRF_TB_DOC_PROPS_VIS bit default 1,
PRF_TB_DOC_SECURITY_VIS bit default 1,
PRF_TB_DOC_NOTES_VIS bit default 1,
PRF_TB_DOC_VERSIONS_VIS bit default 1,
PRF_TB_DOC_PREVIEW_VIS bit default 1,
PRF_TB_DOC_PROP_GRPS_VIS bit default 1,
PRF_TB_FLD_PROPS_VIS bit default 1,
PRF_TB_FLD_SECURITY_VIS bit default 1,
PRF_TB_FLD_NOTES_VIS bit default 1,
PRF_TB_ML_PROPS_VIS bit default 1,
PRF_TB_ML_SECURITY_VIS bit default 1,
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);
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_NAME, PRF_ACTIVE) VALUES ('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 |
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.Oracle9PersistenceManager">
<param name="driver" value="oracle.jdbc.OracleDriver"/>
<param name="url" value="jdbc:oracle:thin:@localhost:3306:sid"/>
<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.Oracle9PersistenceManager">
<param name="driver" value="oracle.jdbc.OracleDriver"/>
<param name="url" value="jdbc:oracle:thin:@localhost:3306:sid"/>
<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.