Difference between revisions of "Knowledge:Migration from 5.1.10 to 6.0"
From OpenKM Documentation
(→Other tables to import data) |
|||
| (3 intermediate revisions by 2 users not shown) | |||
| Line 13: | Line 13: | ||
* Configuration property '''logo.text''' has been renamed to '''logo.banner'''. | * 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. | * 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 == | == Authentication stuff == | ||
| Line 90: | Line 93: | ||
{{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 }} | {{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 == | == Table that need some extra work to be imported == | ||
<source lang="sql"> | <source lang="sql"> | ||
| − | SELECT * FROM OKM_BOOKMARK; | + | SELECT * FROM OKM_BOOKMARK; -- Change of column name |
| − | SELECT * FROM OKM_MAIL_ACCOUNT; | + | SELECT * FROM OKM_MAIL_ACCOUNT; -- Needs to add extra '\0' ( dump -> add -> import ) |
</source> | </source> | ||
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.
- 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
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
 |
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.
