Difference between revisions of "SQL Server - OpenKM 5.0"
From OpenKM Documentation
| Line 26: | Line 26: | ||
okm_app | okm_app | ||
| − | [http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en Start SQL Server Management Studio Express] and create the two databases | + | [http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en Start SQL Server Management Studio Express] and create the two databases. |
[[file:Mssql1.jpg|center]] | [[file:Mssql1.jpg|center]] | ||
| + | |||
| + | |||
| + | Select your appropiate database collate. | ||
| Line 34: | Line 37: | ||
== Solving some hibernate creation problems == | == 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''' column type of field '''ACT_ITEM''' from text to '''varchar(MAX)'''. | ||
| + | |||
| + | [[File:Mssql3.jpg|center]] | ||
| + | |||
| + | 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. | ||
| + | |||
| + | <source lang="sql"> | ||
| + | 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); | ||
| + | </source> | ||
| + | |||
[[Category: Installation Guide]] | [[Category: Installation Guide]] | ||
[[Category: OKM Network]] | [[Category: OKM Network]] | ||
Revision as of 14:58, 23 October 2010
 |
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.
|
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.
 |
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.
Select your appropiate database collate.
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 column type of field ACT_ITEM from 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.
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);
