SQL Server - OpenKM 5.0

From OpenKM Documentation
Revision as of 10:20, 5 September 2013 by Jllort (talk | contribs) (Jllort moved page Knowledge:MS SQLServer to SQL Server - OpenKM 5.0 without leaving a redirect: public resource)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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 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 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

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

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

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_MSC_ADVANCED_FILTERS tinyint default 0 null,
        PRF_MSC_USER_QUOTA numeric(19,0) default 0 null,
        PRF_MSC_WEB_SKIN varchar(255) default 'default' null,
        PRF_MSC_PRINT_PREVIEW tinyint default 1 null,
        PRF_CHT_CHAT_EN tinyint default 0 null,
        PRF_CHT_AUTO_LOGIN_EN tinyint default 0 null,
        PRF_WZRD_KEYWORDS_EN tinyint default 0 null,
        PRF_WZRD_CATEGORIES_EN tinyint default 0 null,
        PRF_WZRD_PROPERTY_GROUPS  tinyint DEFAULT 0 NULL,
        PRF_WZRD_WORKFLOWS   tinyint DEFAULT 0 NULL,
        PRF_STCK_TAXONOMY_VIS tinyint default 1 null,
        PRF_STCK_CATEGORIES_VIS tinyint default 1 null,
        PRF_STCK_THESAURUS_VIS tinyint default 1 null,
        PRF_STCK_TEMPLATES_VIS tinyint default 1 null,
        PRF_STCK_PERSONAL_VIS tinyint default 1 null,
        PRF_STCK_MAIL_VIS tinyint default 1 null,
        PRF_STCK_TRASH_VIS tinyint default 1 null,
        PRF_TB_DESKTOP_VIS tinyint default 1 null,
        PRF_TB_SEARCH_VIS tinyint default 1 null,
        PRF_TB_DASHBOARD_VIS tinyint default 1 null,
        PRF_TB_ADMIN_VIS tinyint default 1 null,
        PRF_TB_DOC_PROPS_VIS tinyint default 1 null,
        PRF_TB_DOC_SECURITY_VIS tinyint default 1 null,
        PRF_TB_DOC_NOTES_VIS tinyint default 1 null,
        PRF_TB_DOC_VERSIONS_VIS tinyint default 1 null,
        PRF_TB_DOC_PREVIEW_VIS tinyint default 1 null,
        PRF_TB_DOC_PROP_GRPS_VIS tinyint default 1 null,
        PRF_TB_FLD_PROPS_VIS tinyint default 1 null,
        PRF_TB_FLD_SECURITY_VIS tinyint default 1 null,
        PRF_TB_FLD_NOTES_VIS tinyint default 1 null,
        PRF_TB_ML_PROPS_VIS tinyint default 1 null,
        PRF_TB_ML_SECURITY_VIS tinyint default 1 null,
        PRF_DB_USER_VIS tinyint default 1 null,
        PRF_DB_MAIL_VIS tinyint default 1 null,
        PRF_DB_NEWS_VIS tinyint default 1 null,
        PRF_DB_GENERAL_VIS tinyint default 1 null,
        PRF_DB_WORKFLOW_VIS tinyint default 1 null,
        PRF_DB_KEYWORDS_VIS tinyint default 1 null,
        PRF_MN_FILE_VIS tinyint default 1 null,
        PRF_MN_EDIT_VIS tinyint default 1 null,
        PRF_MN_TOOLS_VIS tinyint default 1 null,
        PRF_MN_BOOKMARKS_VIS tinyint default 1 null,
        PRF_MN_HELP_VIS tinyint default 1 null,
        PRF_MN_FI_CREATE_FLD_VIS tinyint default 1 null,
        PRF_MN_FI_FIND_FLD_VIS tinyint default 1 null,
        PRF_MN_FI_GO_FLD_VIS tinyint default 1 null,
        PRF_MN_FI_DOWNLOAD_VIS tinyint default 1 null,
        PRF_MN_FI_DOWNLOAD_PDF_VIS tinyint default 1 null,
        PRF_MN_FI_ADD_DOC_VIS tinyint default 1 null,
        PRF_MN_FI_START_WORKFLOW_VIS tinyint default 1 null,
        PRF_MN_FI_REFRESH_VIS tinyint default 1 null,
        PRF_MN_FI_SCANNER_VIS tinyint default 1 null,
        PRF_MN_FI_UPLOADER_VIS tinyint default 1 null,
        PRF_MN_FI_PURGE_VIS tinyint default 1 null,
        PRF_MN_FI_PURGE_TRASH_VIS tinyint default 1 null,
        PRF_MN_FI_RESTORE_VIS tinyint default 1 null,
        PRF_MN_FI_EXPORT_VIS tinyint default 1 null,
        PRF_MN_FI_CREATE_FROM_TPL_VIS tinyint default 1 null,
        PRF_MN_FI_SEND_DOC_LINK_VIS tinyint default 1 null,
        PRF_MN_FI_SEND_DOC_ATTACH_VIS tinyint default 1 null,
        PRF_MN_ED_LOCK_VIS tinyint default 1 null,
        PRF_MN_ED_UNLOCK_VIS tinyint default 1 null,
        PRF_MN_ED_CIN_VIS tinyint default 1 null,
        PRF_MN_ED_COUT_VIS tinyint default 1 null,
        PRF_MN_ED_CANCEL_COUT_VIS tinyint default 1 null,
        PRF_MN_ED_DELETE_VIS tinyint default 1 null,
        PRF_MN_ED_RENAME_VIS tinyint default 1 null,
        PRF_MN_ED_COPY_VIS tinyint default 1 null,
        PRF_MN_ED_MOVE_VIS tinyint default 1 null,
        PRF_MN_ED_ADD_PROP_GRP_VIS tinyint default 1 null,
        PRF_MN_ED_REM_PROP_GRP_VIS tinyint default 1 null,
        PRF_MN_ED_ADD_SUBS_VIS tinyint default 1 null,
        PRF_MN_ED_REM_SUBS_VIS tinyint default 1 null,
        PRF_MN_BM_MNG_BOOKMARKS_VIS tinyint default 1 null,
        PRF_MN_BM_ADD_BOOKMARK_VIS tinyint default 1 null,
        PRF_MN_BM_SET_HOME_VIS tinyint default 1 null,
        PRF_MN_BM_GO_HOME_VIS tinyint default 1 null,
        PRF_MN_TL_LANGS_VIS tinyint default 1 null,
        PRF_MN_TL_SKIN_VIS tinyint default 1 null,
        PRF_MN_TL_DEBUG_VIS tinyint default 1 null,
        PRF_MN_TL_ADMIN_VIS tinyint default 1 null,
        PRF_MN_TL_PREFS_VIS tinyint default 1 null,
        PRF_MN_HLP_DOC_VIS tinyint default 1 null,
        PRF_MN_HLP_BUG_TRACKING_VIS tinyint default 1 null,
        PRF_MN_HLP_SUPPORT_VIS tinyint default 1 null,
        PRF_MN_HLP_FORUM_VIS tinyint default 1 null,
        PRF_MN_HLP_CHANGELOG_VIS tinyint default 1 null,
        PRF_MN_HLP_WEB_SITE_VIS tinyint default 1 null,
        PRF_MNU_HLP_ABOUT_VIS 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;databaseName=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>

Read also: Dynamic login-config.xml.

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_repo;autoReconnect=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=10212"