SQL Server - OpenKM 5.0

From OpenKM Documentation
Revision as of 19:05, 28 October 2010 by Jllort (talk | contribs)

Jump to: navigation, search

Nota clasica.png This configuration has been tested with Microsoft SQL Server 2005 and Microsoft SQL Server 2005 express edition.

Download MS SQLServer JDBC driver from MS SQLServer Home Page and move it to $JBOSS_HOME/server/default/lib.


Nota clasica.png Other JDBC driver for MS SQLServer we've tested is jdts.

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.SQLServerDialect
hibernate.hbm2ddl=create

Once the tables are created, change the hibernate.hbm2ddl property from create to none.


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 two databases are needed: 

okm_repo
okm_app

Start SQL Server Management Studio Express and create the two databases.

Mssql1.jpg


Select your appropiate database collate.


Mssql2.jpg


Troubleshooting, error you can not connect to MS SQL Server

Ensure you've got tcp protocol enabled ( by default is disabled )

Mssql5.jpg


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.

Connect to okm_app database and alter OKM_ACTIVITY the column ACT_ITEM from type text to varchar(MAX).

Mssql3.jpg

Then 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.


Mssql4.jpg


create table OKM_PROFILE (PRF_ID int identity not null, PRF_NAME varchar(255) not null, PRF_ACTIVE tinyint not null, PRF_MISC_ADVANCED_FILTERS tinyint default 0 null, PRF_MISC_USER_QUOTA numeric(19,0) default 0 null, PRF_MISC_WEB_SKIN varchar(255) default 'default' null, PRF_CHAT_CHAT_ENABLED tinyint default 0 null, PRF_CHAT_AUTO_LOGIN_ENABLED tinyint default 0 null, PRF_WIZARD_PROPERTY_GROUPS varchar(255) default '' null, PRF_WIZARD_WORKFLOWS varchar(255) default '' null, PRF_WIZARD_KEYWORDS_ENABLED tinyint default 0 null, PRF_WIZARD_CATEGORIES_ENABLED tinyint default 0 null, PRF_STACK_TAXONOMY_VISIBLE tinyint default 1 null, PRF_STACK_CATEGORIES_VISIBLE tinyint default 1 null, PRF_STACK_THESAURUS_VISIBLE tinyint default 1 null, PRF_STACK_TEMPLATES_VISIBLE tinyint default 1 null, PRF_STACK_PERSONAL_VISIBLE tinyint default 1 null, PRF_STACK_MAIL_VISIBLE tinyint default 1 null, PRF_STACK_TRASH_VISIBLE tinyint default 1 null, PRF_TAB_DESKTOP_VISIBLE tinyint default 1 null, PRF_TAB_SEARCH_VISIBLE tinyint default 1 null, PRF_TAB_DASHBOARD_VISIBLE tinyint default 1 null, PRF_TAB_ADMINISTRATION_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_PROPERTIES_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_SECURITY_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_NOTES_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_VERSIONS_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_PREVIEW_VISIBLE tinyint default 1 null, PRF_TAB_DOCUMENT_PROPERTY_GROUPS_VISIBLE tinyint default 1 null, PRF_TAB_FOLDER_PROPERTIES_VISIBLE tinyint default 1 null, PRF_TAB_FOLDER_SECURITY_VISIBLE tinyint default 1 null, PRF_TAB_MAIL_PROPERTIES_VISIBLE tinyint default 1 null, PRF_TAB_MAIL_SECURITY_VISIBLE tinyint default 1 null, PRF_DASHBOARD_USER_VISIBLE tinyint default 1 null, PRF_DASHBOARD_MAIL_VISIBLE tinyint default 1 null, PRF_DASHBOARD_NEWS_VISIBLE tinyint default 1 null, PRF_DASHBOARD_GENERAL_VISIBLE tinyint default 1 null, PRF_DASHBOARD_WORKFLOW_VISIBLE tinyint default 1 null, PRF_DASHBOARD_KEYWORDS_VISIBLE tinyint default 1 null, PRF_MENU_FILE_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_VISIBLE tinyint default 1 null, PRF_MENU_TOOLS_VISIBLE tinyint default 1 null, PRF_MENU_BOOKMARKS_VISIBLE tinyint default 1 null, PRF_MENU_HELP_VISIBLE tinyint default 1 null, PRF_MENU_FILE_CREATE_FOLDER_VISIBLE tinyint default 1 null, PRF_MENU_FILE_FIND_FOLDER_VISIBLE tinyint default 1 null, PRF_MENU_FILE_GO_FOLDER_VISIBLE tinyint default 1 null, PRF_MENU_FILE_DOWNLOAD_VISIBLE tinyint default 1 null, PRF_MENU_FILE_DOWNLOAD_PDF_VISIBLE tinyint default 1 null, PRF_MENU_FILE_ADD_DOCUMENT_VISIBLE tinyint default 1 null, PRF_MENU_FILE_START_WORKFLOW_VISIBLE tinyint default 1 null, PRF_MENU_FILE_REFRESH_VISIBLE tinyint default 1 null, PRF_MENU_FILE_SCANNER_VISIBLE tinyint default 1 null, PRF_MENU_FILE_UPLOADER_VISIBLE tinyint default 1 null, PRF_MENU_FILE_PURGE_VISIBLE tinyint default 1 null, PRF_MENU_FILE_PURGE_TRASH_VISIBLE tinyint default 1 null, PRF_MENU_FILE_RESTORE_VISIBLE tinyint default 1 null, PRF_MENU_FILE_EXPORT_VISIBLE tinyint default 1 null, PRF_MENU_FILE_CREATE_FROM_TEMPLATE_VISIBLE tinyint default 1 null, PRF_MENU_FILE_SEND_DOCUMENT_LINK_VISIBLE tinyint default 1 null, PRF_MENU_FILE_SEND_DOCUMENT_ATTACHMENT_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_LOCK_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_UNLOCK_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_CHECK_IN_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_CHECK_OUT_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_CANCEL_CHECK_OUT_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_DELETE_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_RENAME_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_COPY_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_MOVE_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_ADD_PROPERTY_GROUP_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_REMOVE_PROPERTY_GROUP_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_ADD_SUBSCRIPTION_VISIBLE tinyint default 1 null, PRF_MENU_EDIT_REMOVE_SUBSCRIPTION_VISIBLE tinyint default 1 null, PRF_MENU_BOOKMARK_MANAGE_BOOKMARKS_VISIBLE tinyint default 1 null, PRF_MENU_BOOKMARK_ADD_BOOKMARK_VISIBLE tinyint default 1 null, PRF_MENU_BOOKMARK_SET_HOME_VISIBLE tinyint default 1 null, PRF_MENU_BOOKMARK_GO_HOME_VISIBLE tinyint default 1 null, PRF_MENU_TOOL_LANGUAGES_VISIBLE tinyint default 1 null, PRF_MENU_TOOL_SKIN_VISIBLE tinyint default 1 null, PRF_MENU_TOOL_DEBUG_VISIBLE tinyint default 1 null, PRF_MENU_TOOL_ADMINISTRATION_VISIBLE tinyint default 1 null, PRF_MENU_TOOL_PREFERENCES_VISIBLE tinyint default 1 null, PRF_MENU_HELP_DOCUMENTATION_VISIBLE tinyint default 1 null, PRF_MENU_HELP_BUG_TRACKING_VISIBLE tinyint default 1 null, PRF_MENU_HELP_SUPPORT_VISIBLE tinyint default 1 null, PRF_MENU_HELP_FORUM_VISIBLE tinyint default 1 null, PRF_MENU_HELP_CHANGELOG_VISIBLE tinyint default 1 null, PRF_MENU_HELP_WEB_SITE_VISIBLE tinyint default 1 null, PRF_MENU_HELP_ABOUT_VISIBLE tinyint default 1 null, 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:sqlserver://localhost:1433/okm_app</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <user-name>sa</user-name>
    <password>****</password>
    <connection-property name="autoReconnect">true</connection-property>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <blocking-timeout-millis>5000</blocking-timeout-millis>
    <idle-timeout-minutes>28680</idle-timeout-minutes>
    <metadata>
      <type-mapping>MS SQLSERVER2000</type-mapping>
    </metadata>
</local-tx-datasource>

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">
        <!-- Security manager: FQN of class implementing the JackrabbitSecurityManager interface -->
        <!--<SecurityManager class="org.apache.jackrabbit.core.DefaultSecurityManager" workspaceName="security">-->
            <!-- workspace access: FQN of class implementing the WorkspaceAccessManager interface -->
            <!-- <WorkspaceAccessManager class="..."/> -->
            <!-- <param name="config" value="${rep.home}/security.xml"/> -->
        <!--</SecurityManager>-->

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

        <!-- <LoginModule class="org.apache.jackrabbit.core.security.simple.SimpleLoginModule"> -->
        <!-- <LoginModule class="org.apache.jackrabbit.core.security.authentication.DefaultLoginModule"> -->
           <!-- Anonymous user name ('anonymous' is the default value) -->
           <!-- <param name="anonymousId" value="anonymous"/> -->
           <!-- Administrator user id (default value if param is missing is 'admin') -->
           <!-- <param name="adminId" value="admin"/> -->
           <!-- <param name="principalProvider" value="..."/> -->
        <!--</LoginModule>-->
    </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.MSSqlPersistenceManager">
          <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
          <param name="url" value="jdbc:sqlserver://darkman97i2:1433;databaseName=okm_repoautoReconnect=true"/>
          <param name="schema" value="mssql"/>
          <param name="user" value="sa"/>
          <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.MSSqlPersistenceManager">
          <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
          <param name="url" value="jdbc:sqlserver://darkman97i2:1433;databaseName=okm_repo;autoReconnect=true"/>
          <param name="schema" value="mssql"/>
          <param name="user" value="sa"/>
          <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.

Retrieved from "https://www.openkm.com/wiki/index.php?title=SQL_Server_-_OpenKM_5.0&oldid=2849"