Difference between revisions of "Knowledge:Migration from 5.1.10 to 6.0"

From OpenKM Documentation
Jump to: navigation, search
 
(54 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
{{Note|OpenKM 6.0 is not officially released, but you can download a beta soon.}}
+
{{TOCright}} __TOC__
  
−
Configuration property '''logo.text''' has been renamed to '''logo.banner'''.
+
'''Important before importing documents !!!''' take a look at tables with UUID; if contains some record then is needed import with UUID:
 +
<source lang="sql">
 +
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;
 +
</source>
 +
 
 +
* 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.
  
 +
* Download update from http://openkm.com/download/okm/OpenKM-5.1.11.zip (z63kQNaU)
 +
 +
== 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 ===
 
<source lang="sql">
 
<source lang="sql">
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.bookmark.add' WHERE TR_KEY='filebrowser.menu.add.bookmark';
+
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;
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.add.property.group' WHERE TR_KEY='filebrowser.menu.add.property.group';
+
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''ROLE_' + ROL_ID + ''', ''' + ROL_ACTIVE + ''');' from OKM_ROLE;
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.export' WHERE TR_KEY='filebrowser.menu.export';
+
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' + UR_USER + ''', ''ROLE_' + UR_ROLE + ''');' FROM OKM_USER_ROLE;
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.lock' WHERE TR_KEY='general.menu.file.lock';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.unlock' WHERE TR_KEY='general.menu.file.unlock';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.checkout' WHERE TR_KEY='general.menu.file.checkout';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.checkin' WHERE TR_KEY='general.menu.file.checkin';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.cancel.checkout' WHERE TR_KEY='general.menu.file.cancel.checkout';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.delete' WHERE TR_KEY='general.menu.file.delete';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.copy' WHERE TR_KEY='filebrowser.menu.copy';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.move' WHERE TR_KEY='filebrowser.menu.move';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.rename' WHERE TR_KEY='filebrowser.menu.rename';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.remove.property.group' WHERE TR_KEY='filebrowser.menu.remove.property.group';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.tools.debug.console' WHERE TR_KEY='general.menu.debug.console';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.tools.administration' WHERE TR_KEY='general.menu.administration';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.documentation' WHERE TR_KEY='general.menu.documentation';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.report' WHERE TR_KEY='general.menu.bug.report';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.support.request' WHERE TR_KEY='general.menu.support.request';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.public.forum' WHERE TR_KEY='general.menu.public.forum';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.version.changes' WHERE TR_KEY='general.menu.version.changes';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.project.web' WHERE TR_KEY='general.menu.project.web';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.help.about' WHERE TR_KEY='general.menu.about';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.add.subscription' WHERE TR_KEY='filebrowser.menu.add.subscription';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.remove.subscription' WHERE TR_KEY='filebrowser.menu.remove.subscription';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.find.folder' WHERE TR_KEY='tree.menu.directory.find.folder';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.find.document' WHERE TR_KEY='tree.menu.directory.find.document';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.restore' WHERE TR_KEY='trash.menu.directory.restore';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.purge' WHERE TR_KEY='trash.menu.directory.purge';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.start.workflow' WHERE TR_KEY='filebrowser.menu.start.workflow';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.file.create.from.template' WHERE TR_KEY='filebrowser.menu.create.from.template';
 
−
UPDATE OKM_TRANSLATION SET TR_KEY='general.menu.edit.add.note' WHERE TR_KEY='document.add.note';
 
 
</source>
 
</source>
 +
 +
=== MySQL ===
 +
<source lang="sql">
 +
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;
 +
</source>
 +
 +
=== PostgreSQL ===
 +
<source lang="sql">
 +
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;</source>
 +
 +
=== Oracle ===
 +
<source lang="sql">
 +
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;</source>
 +
 +
* 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):
 +
<source lang="sql">
 +
-- 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_%';
 +
</source>
 +
* 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 ===
 +
<source lang="sql">
 +
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;
 +
</source>
 +
 +
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 ==
 +
{{Note|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 withoud force UUID. Anyway to be imported correctly profiles should be created with same order in version 5.x }}
 +
 +
<source lang="bash">
 +
$ mysqldump -t -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_USER_CONFIG > OKM_USER_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
 +
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAPLE_GROUP > OKM_STAPLE_GROUP.sql
 +
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAPLE > OKM_STAPLE.sql
 +
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_CONTACT > OKM_CONTACT.sql
 +
 +
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_USER_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
 +
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_STAPLE_GROUP.sql
 +
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_STAPLE.sql
 +
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_CONTACT.sql
 +
</source>
 +
 +
== Table that need some extra work to be imported ==
 +
<source lang="sql">
 +
SELECT * FROM OKM_BOOKMARK; -- Change of column name
 +
SELECT * FROM OKM_MAIL_ACCOUNT; -- Needs to add extra '\0' ( dump -> add -> import )
 +
</source>
 +
 +
== 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 ===
 +
<source lang="sql">
 +
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;
 +
</source>
 +
 +
* Execute the resulting SQL sentences in the OpenKM 6.0 installation.

Latest revision as of 10:18, 17 February 2013

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 withoud force UUID. 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_USER_CONFIG > OKM_USER_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
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAPLE_GROUP > OKM_STAPLE_GROUP.sql
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_STAPLE > OKM_STAPLE.sql
$ mysqldump -h localhost -u openkm -p[password] --no-create-db --no-create-info okm_app OKM_CONTACT > OKM_CONTACT.sql

$ mysql -h localhost -u openkm -p[password] okmdb < OKM_USER_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
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_STAPLE_GROUP.sql
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_STAPLE.sql
$ mysql -h localhost -u openkm -p[password] okmdb < OKM_CONTACT.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=8990"