Knowledge:Migration from 5.1.10 to 6.0

From OpenKM Documentation
Revision as of 12:15, 19 September 2012 by Jllort (talk | contribs) (→‎Profile stuff)

Jump to: navigation, search

Nota clasica.png OpenKM 6.0 Community is not officially released, but you can download a beta soon.

Configuration property logo.text has been renamed to logo.banner.

Authentication stuff

  • Log into OpenKM 5.1.10 and go to Administration > Database query and execute these sentences in order to export users and roles:

Hypersonic

SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' + USR_ID + ''', ''' + USR_NAME + ''', ''' + USR_PASSWORD + ''', ''' + USR_EMAIL + ''', ''' + USR_ACTIVE + ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''ROLE_' + ROL_ID + ''', ''' + ROL_ACTIVE + ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' + UR_USER + ''', ''ROLE_' + UR_ROLE + ''');' FROM OKM_USER_ROLE;

MySQL

SELECT CONCAT('INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''', USR_ID, ''', ''', USR_NAME, ''', ''', USR_PASSWORD, ''', ''', USR_EMAIL, ''', ''', USR_ACTIVE, ''');') from OKM_USER;
SELECT CONCAT('INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''ROLE_', ROL_ID, ''', ''', ROL_ACTIVE, ''');') from OKM_ROLE;
SELECT CONCAT('INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''', UR_USER, ''', ''ROLE_', UR_ROLE, ''');') from OKM_USER_ROLE;

PostgreSQL

SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' || USR_ID || ''', ''' || USR_NAME || ''', ''' || USR_PASSWORD || ''', ''' || USR_EMAIL || ''', ''' || USR_ACTIVE || ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''ROLE_' || ROL_ID || ''', ''' || ROL_ACTIVE || ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''ROLE_' || UR_ROLE || ''');' from OKM_USER_ROLE;

Oracle

SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' || USR_ID || ''', ''' || USR_NAME || ''', ''' || USR_PASSWORD || ''', ''' || USR_EMAIL || ''', ''' || USR_ACTIVE || ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''ROLE_' || ROL_ID || ''', ''' || ROL_ACTIVE || ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''ROLE' || UR_ROLE || ''');' from OKM_USER_ROLE;
  • Copy the output of these sentences to a secure place.
  • Replace sql text ROLE_UserRole and ROLE_AdminRole to ROLE_USER and ROLE_ADMIN.
  • Due to changes in the authentication and authorization backend, role name have changed to ROLE_XXXX. So if you have a role called "SalesRole" it need to be changed to "ROLE_SALES".
  • Note that also need to change these roles in the .okm files if you exported the repository contents with metadata. In Linux you can use this command:
 $ find . -name "*.okm" -exec sed -i 's/SalesRole/ROLE_SALES/' {} \;
  • Also is possible to update the roles with a SQL sentence (select one depending on your database SQL dialect):
-- Common
update OKM_NODE_ROLE_PERMISSION set NRP_ROLE='ROLE_USER' where NRP_ROLE = 'UserRole';
update OKM_NODE_ROLE_PERMISSION set NRP_ROLE='ROLE_ADMIN' where NRP_ROLE = 'AdminRole';

-- MySQL
update OKM_NODE_ROLE_PERMISSION set NRP_ROLE=concat('ROLE_', NRP_ROLE) where NRP_ROLE not like 'ROLE_%';

-- PostgreSQL & Oracle
update OKM_NODE_ROLE_PERMISSION set NRP_ROLE='ROLE_' || NRP_ROLE where NRP_ROLE not like 'ROLE_%';
  • Execute the resulting SQL sentences in the OpenKM 6.0 installation.

Database metadata stuff

  • Log into OpenKM 5.1.10 and go to Administration > Database query and execute these sentences in order to export database metadata:

MySQL

SELECT CONCAT('INSERT INTO OKM_DB_METADATA_TYPE (DMT_REAL_COLUMN, DMT_TABLE, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (''', DMT_REAL_COLUMN, ''', ''', DMT_TABLE, ''', ''', DMT_TYPE, ''', ''',DMT_VIRTUAL_COLUMN, ''');') FROM OKM_DB_METADATA_TYPE;
SELECT CONCAT('INSERT INTO OKM_DB_METADATA_VALUE (DMV_COL00, DMV_COL01, DMV_COL02, DMV_COL03, DMV_COL04, DMV_COL05, DMV_COL06, DMV_COL07, DMV_COL08, DMV_COL09, DMV_COL10, DMV_COL11, DMV_COL12, DMV_COL13, DMV_COL14, DMV_TABLE) VALUES (''', DMV_COL00, ''', ''', DMV_COL01, ''', ''', DMV_COL02, ''', ''', DMV_COL03, ''', ''', DMV_COL04, ''', ''', DMV_COL05, ''', ''', DMV_COL06, ''', ''', DMV_COL07, ''', ''', DMV_COL08, ''', ''', DMV_COL09, ''', ''', DMV_COL10, ''', ''', DMV_COL11, ''', ''', DMV_COL12, ''', ''', DMV_COL13, ''', ''', DMV_COL14, ''', ''', DMV_TABLE, ''');') FROM OKM_DB_METADATA_VALUE;

If you have a lot of records is possible dumping only a specific table 

mysqldump -h localhost -u openkm -p okm_app OKM_DB_METADATA_TYPE > OKM_DB_METADATA_TYPE.sql
mysqldump -h localhost -u openkm -p okm_app OKM_DB_METADATA_VALUE > OKM_DB_METADATA_VALUE.sql

Edit sql files and delete drop and create tables, you should only use insert script values. 

mysql -h localhost -u openkm -p okmdb < OKM_DB_METADATA_TYPE.sql
mysql -h localhost -u openkm -p okmdb < OKM_DB_METADATA_VALUE.sql
  • Execute the resulting SQL sentences in the OpenKM 6.0 installation.

Extensions stuff

  • Log into OpenKM 5.1.10 and go to Administration > Database query and execute these sentences in order to export extensions:

MySQL

SELECT CONCAT('INSERT INTO OKM_EXTENSION (EXT_UUID, EXT_NAME) VALUES (''', EXT_UUID, ''', ''', EXT_NAME, ''');') FROM OKM_EXTENSION;
  • Execute the resulting SQL sentences in the OpenKM 6.0 installation.

Report stuff

MySQL

mysqldump -h localhost -u openkm -p okm_app OKM_REPORT > OKM_REPORT.sql

Edit sql files and delete drop and create tables, you should only use insert script values. 

mysql -h localhost -u openkm -p okmdb < OKM_REPORT.sql

Profile stuff

Unfortunatelly there's no way to migrate from older profile table to newer. Profiles should be created manually in OpenKM 6.0 with same order has been created in OpenKM version 5.0. After it, we can restore user profile configuration.

  • Log into OpenKM 5.1.10 and go to Administration > Database query and execute these sentences in order to export user profile configuration.

Mysql

SELECT CONCAT('INSERT INTO OKM_USER_CONFIG (UC_USER, UC_HOME_PATH, UC_HOME_UUID, UC_HOME_TYPE, UC_PROFILE) VALUES (''', UC_USER, ''', ''', UC_HOME_PATH, ''', ''', UC_HOME_UUID, ''', ''', UC_HOME_TYPE, ''', ''', UC_PROFILE, ''');') FROM OKM_USER_CONFIG;
  • Execute the resulting SQL sentences in the OpenKM 6.0 installation.
Retrieved from "https://www.openkm.com/wiki/index.php?title=Knowledge:Migration_from_5.1.10_to_6.0&oldid=7182"