Knowledge:Migration from 5.1.10 to 6.0

From OpenKM Documentation
Revision as of 13:40, 2 January 2013 by Jllort (talk | contribs) (→‎Other tables to import data)

Jump to: navigation, search

Important before importing documents !!! take a look at tables with UUID; if contains some record then is needed import with UUID: 

SELECT * FROM OKM_CONTACT;
SELECT * FROM OKM_FORUM;
SELECT * FROM OKM_FORUM_POST;
SELECT * FROM OKM_FORUM_TOPIC;
SELECT * FROM OKM_STAPLE;
SELECT * FROM OKM_STAPLE_GROUP;
SELECT * FROM OKM_WIKI_PAGE;
  • Configuration property logo.text has been renamed to logo.banner.
  • Log into OpenKM 5.1 and go to Administration > Repository export. If you want to preserver document and folder metadata, please check the metadata option.

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 -t -h localhost -u openkm -p --no-create-db --no-create-info okm_app OKM_DB_METADATA_TYPE > OKM_DB_METADATA_TYPE.sql
mysqldump -t -h localhost -u openkm -p --no-create-db --no-create-info 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:

Other tables to import data


Nota clasica.png OKM_CONFIG table has UUID's take in consideration how data has been imported in major cases default folder is root and can be solved with a simple update after imported. Anyway to be imported correctly profiles should be created with same order in version 5.x 

mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_CONFIG > OKM_CONFIG.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAMP_IMAGE > OKM_STAMP_IMAGE.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAMP_IMAGE_USER > OKM_STAMP_IMAGE_USER.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAMP_TEXT > OKM_STAMP_TEXT.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAMP_TEXT_USER > OKM_STAMP_TEXT_USER.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAMP_TEXT_USER > OKM_STAMP_TEXT_USER.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_CONTACT > OKM_CONTACT.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_FORUM > OKM_FORUM.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_FORUM_TOPIC > OKM_FORUM_TOPIC.sql
mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_FORUM_POST > OKM_FORUM_POST.sql
mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_REPORT > OKM_REPORT.sql
mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_EXTENSION > OKM_EXTENSION.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_CONFIG.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_STAMP_IMAGE.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_STAMP_IMAGE_USER.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_STAMP_TEXT.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_STAMP_TEXT_USER.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_FORUM.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_FORUM_TOPIC.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_FORUM_POST.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_REPORT.sql
mysql -h localhost -u openkm -p[password] okmdb < OKM_EXTENSION.sql

Table that need some extra work to be imported

SELECT * FROM OKM_BOOKMARK; // Change of column name
SELECT * FROM OKM_MAIL_ACCOUNT; // Needs to add extra '\0' ( dump -> add -> import )

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_NODE, 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=8391"