Difference between revisions of "Migration from 5.0.4 to 5.1.8"

From OpenKM Documentation
Jump to: navigation, search
 
(26 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
{{Warning|This migration process is under alfa state.}}
+
{{TOCright}} __TOC__
  
−
Change table columns definition:
+
{{Note|In OpenKM 5.1.x the configuration is managed from Administration and stored in database, so most of the configuration in OpenKM.cfg from OpenKM 5.0 is not valid and should be migrated. Only database related stuff like '''hibernate.dialect''' and '''hibernate.hbm2ddl''' makes sense in OpenKM 5.1.x configuration file.}}
  
−
<source lang="sql">
+
== Changes ==
−
ALTER TABLE OKM_MAIL_ACCOUNT MODIFY COLUMN MA_ACTIVE char(1) NOT NULL;
+
'''com.openkm.extractor.TiffTextExtractor''' has been deprecated, so you need to edit the '''repository.xml''' and '''repository/workspaces/default/workspace.xml''' files and change by one of these options:
−
ALTER TABLE OKM_QUERY_PARAMS MODIFY COLUMN QP_DASHBOARD char(1) NOT NULL;
 
−
ALTER TABLE OKM_ROLE MODIFY COLUMN ROL_ACTIVE char(1) NOT NULL;
 
−
ALTER TABLE OKM_TWITTER_ACCOUNT MODIFY COLUMN TA_ACTIVE char(1) NOT NULL;
 
−
ALTER TABLE OKM_USER MODIFY COLUMN USR_ACTIVE char(1) NOT NULL;
 
−
ALTER TABLE OKM_ACTIVITY MODIFY COLUMN ACT_ITEM varchar(127) NULL;
 
−
ALTER TABLE OKM_REPORT MODIFY COLUMN RP_ACTIVE char(1) NOT NULL;
 
−
ALTER TABLE OKM_CRON_TAB MODIFY COLUMN CT_ACTIVE char(1) NOT NULL;
 
−
</source>
 
  
−
Rename table columns:
+
* com.openkm.extractor.AbbyTextExtractor
 +
* com.openkm.extractor.CuneiformTextExtractor
 +
* com.openkm.extractor.Tesseract2TextExtractor
 +
* com.openkm.extractor.Tesseract3TextExtractor
  
−
<source lang="sql">
+
See also [[Third-party_software_integration: OCR]].
−
ALTER TABLE OKM_EXTENSION CHANGE COLUMN EX_DESCRIPTION EXT_NAME varchar(255) NOT NULL;
 
−
ALTER TABLE OKM_EXTENSION CHANGE COLUMN EX_UUID EXT_UUID varchar(127) NOT NULL;
 
−
</source>
 
  
−
Remove deprecated table columns:
+
== OpenKM 5.0 stuff ==
 +
* Make a backup!
 +
* Log into OpenKM 5.0 and go to '''Administration''' > '''Database query''' and execute these sentences in order to export users and roles:
  
 +
=== Hypersonic ===
 
<source lang="sql">
 
<source lang="sql">
−
ALTER TABLE OKM_BOOKMARK DROP COLUMN BM_PATH;
+
SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' + USR_ID + ''', ''' + USR_NAME + ''', ''' + USR_PASSWORD + ''', ''' + USR_EMAIL + ''', ''' + CASEWHEN(USR_ACTIVE,'T', 'F') + ''');' from OKM_USER;
−
ALTER TABLE OKM_REPORT DROP COLUMN RP_TYPE;
+
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' + ROL_ID + ''', ''' + CASEWHEN(ROL_ACTIVE, 'T', 'F') + ''');' from OKM_ROLE;
−
ALTER TABLE OKM_CRON_TAB DROP COLUMN CT_TYPE;
+
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' + UR_USER + ''', ''' + UR_ROLE + ''');' FROM OKM_USER_ROLE;
 
</source>
 
</source>
  
−
Remove deprecated tables:
+
=== MySQL ===
−
 
 
 
<source lang="sql">
 
<source lang="sql">
−
DROP TABLE OKM_FILTER_RULE;
+
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, ''', ''', IF(USR_ACTIVE IS TRUE,'T', 'F'), ''');') from OKM_USER;
 +
SELECT CONCAT('INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''', ROL_ID, ''', ''', IF(ROL_ACTIVE IS TRUE, 'T', 'F'), ''');') from OKM_ROLE;
 +
SELECT CONCAT('INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''', UR_USER, ''', ''', UR_ROLE, ''');') from OKM_USER_ROLE;
 
</source>
 
</source>
  
−
Create new tables:
+
=== 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 || ''', ''' || CASE WHEN USR_ACTIVE=1 THEN 'T' ELSE 'F' END || ''');' from OKM_USER;
 +
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' || ROL_ID || ''', ''' || CASE WHEN ROL_ACTIVE=1 THEN 'T' ELSE 'F' END || ''');' from OKM_ROLE;
 +
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''' || UR_ROLE || ''');' from OKM_USER_ROLE;</source>
  
 +
=== Oracle ===
 
<source lang="sql">
 
<source lang="sql">
−
CREATE TABLE OKM_CONFIG  (  
+
SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' || USR_ID || ''', ''' || USR_NAME || ''', ''' || USR_PASSWORD || ''', ''' || USR_EMAIL || ''', ''' || DECODE(USR_ACTIVE, 1, 'T', 'F') || ''');' from OKM_USER;
−
CFG_KEY varchar(255) NOT NULL,
+
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' || ROL_ID || ''', ''' || DECODE(ROL_ACTIVE, 1, 'T', 'F') || ''');' from OKM_ROLE;
−
CFG_TYPE varchar(255) NOT NULL,
+
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''' || UR_ROLE || ''');' from OKM_USER_ROLE;</source>
−
CFG_VALUE longtext NULL,
 
−
PRIMARY KEY(CFG_KEY)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_CONTACT  (
 
−
CON_ADDRESS varchar(256) NULL,
 
−
CON_CITY varchar(127) NULL,
 
−
CON_COUNTRY varchar(127) NULL,
 
−
CON_EXTERNAL_ID varchar(512) NULL,
 
−
CON_FAX varchar(40) NULL,
 
−
CON_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
CON_MAIL varchar(256) NULL,
 
−
CON_MOBILE varchar(40) NULL,
 
−
CON_NAME varchar(256) NULL,
 
−
CON_NOTES longtext NULL,
 
−
CON_ORIGIN varchar(127) NULL,
 
−
CON_PHONE varchar(40) NULL,
 
−
CON_POSTAL_CODE varchar(40) NULL,
 
−
CON_PROVINCE varchar(127) NULL,
 
−
CON_WEB varchar(256) NULL,
 
−
PRIMARY KEY(CON_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_DB_METADATA_SEQUENCE  (
 
−
DMS_COLUMN varchar(32) NULL,
 
−
DMS_ID bigint(20) AUTO_INCREMENT NOT NULL,
 
−
DMS_TABLE varchar(32) NULL,
 
−
DMS_VALUE bigint(20) NULL,
 
−
PRIMARY KEY(DMS_ID)
 
−
);
 
−
ALTER TABLE OKM_DB_METADATA_SEQUENCE
 
−
ADD CONSTRAINT DMS_TABLE
 
−
UNIQUE (DMS_TABLE, DMS_COLUMN);
 
−
 
 
−
CREATE TABLE OKM_DB_METADATA_TYPE  (
 
−
DMT_ID bigint(20) AUTO_INCREMENT NOT NULL,
 
−
DMT_REAL_COLUMN varchar(6) NULL,
 
−
DMT_TABLE varchar(32) NULL,
 
−
DMT_TYPE varchar(32) NULL,
 
−
DMT_VIRTUAL_COLUMN varchar(32) NULL,
 
−
PRIMARY KEY(DMT_ID)
 
−
);
 
−
ALTER TABLE OKM_DB_METADATA_TYPE
 
−
ADD CONSTRAINT DMT_TABLE
 
−
UNIQUE (DMT_TABLE, DMT_REAL_COLUMN);
 
−
 
 
−
CREATE TABLE OKM_DB_METADATA_VALUE  (
 
−
DMV_COL00 varchar(512) NULL,
 
−
DMV_COL01 varchar(512) NULL,
 
−
DMV_COL02 varchar(512) NULL,
 
−
DMV_COL03 varchar(512) NULL,
 
−
DMV_COL04 varchar(512) NULL,
 
−
DMV_COL05 varchar(512) NULL,
 
−
DMV_COL06 varchar(512) NULL,
 
−
DMV_COL07 varchar(512) NULL,
 
−
DMV_COL08 varchar(512) NULL,
 
−
DMV_COL09 varchar(512) NULL,
 
−
DMV_COL10 varchar(512) NULL,
 
−
DMV_COL11 varchar(512) NULL,
 
−
DMV_COL12 varchar(512) NULL,
 
−
DMV_COL13 varchar(512) NULL,
 
−
DMV_COL14 varchar(512) NULL,
 
−
DMV_ID bigint(20) AUTO_INCREMENT NOT NULL,
 
−
DMV_TABLE varchar(32) NULL,
 
−
PRIMARY KEY(DMV_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_FORUM  (
 
−
FRM_ACTIVE char(1) NOT NULL,
 
−
FRM_DATE datetime NOT NULL,
 
−
FRM_DESCRIPTION longtext NOT NULL,
 
−
FRM_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
FRM_LAST_POST_DATE datetime NOT NULL,
 
−
FRM_LAST_POST_USER varchar(127) NOT NULL,
 
−
FRM_NAME varchar(255) NOT NULL,
 
−
FRM_NUM_POSTS int(11) NOT NULL,
 
−
FRM_NUM_TOPICS int(11) NOT NULL,
 
−
PRIMARY KEY(FRM_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_FORUM_POST  (
 
−
FPS_DATE datetime NOT NULL,
 
−
FPS_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
FPS_MESSAGE longtext NOT NULL,
 
−
FPS_SUBJECT varchar(255) NOT NULL,
 
−
FPS_TOPIC int(11) NOT NULL,
 
−
FPS_USER varchar(127) NOT NULL,
 
−
PRIMARY KEY(FPS_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_FORUM_TOPIC  (  
 
−
FTP_DATE datetime NOT NULL,
 
−
FTP_FORUM int(11) NOT NULL,
 
−
FTP_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
FTP_LAST_POST_DATE datetime NOT NULL,
 
−
FTP_LAST_POST_USER varchar(127) NOT NULL,
 
−
FTP_REPLIES int(11) NOT NULL,
 
−
FTP_TITLE varchar(127) NOT NULL,
 
−
FTP_USER varchar(127) NULL,
 
−
FTP_UUID varchar(127) NULL,
 
−
FTP_VIEWS int(11) NOT NULL,
 
−
PRIMARY KEY(FTP_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_LANGUAGE  (
 
−
LG_ID varchar(8) NOT NULL,
 
−
LG_IMAGE_CONTENT longtext NOT NULL,
 
−
LG_IMAGE_MIME varchar(127) NOT NULL,
 
−
LG_NAME varchar(127) NOT NULL,
 
−
PRIMARY KEY(LG_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_MAIL_FILTER_RULE  (
 
−
MFR_ACTIVE char(1) NOT NULL,
 
−
MFR_FIELD varchar(255) NOT NULL,
 
−
MFR_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
MFR_MAIL_FILTER int(11) NOT NULL,
 
−
MFR_OPERATION varchar(255) NOT NULL,
 
−
MFR_VALUE varchar(255) NOT NULL,
 
−
PRIMARY KEY(MFR_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_MESSAGE_RECEIVED  (
 
−
MSR_CONTENT longtext NULL,
 
−
MSR_FROM varchar(127) NOT NULL,
 
−
MSR_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
MSR_SEEN_DATE datetime NULL,
 
−
MSR_SENT_DATE datetime NOT NULL,
 
−
MSR_SUBJECT varchar(255) NOT NULL,
 
−
MSR_TO varchar(255) NOT NULL,
 
−
MSR_USER varchar(127) NOT NULL,
 
−
PRIMARY KEY(MSR_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_MESSAGE_SENT  (
 
−
MSS_CONTENT longtext NULL,
 
−
MSS_FROM varchar(127) NOT NULL,
 
−
MSS_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
MSS_SENT_DATE datetime NOT NULL,
 
−
MSS_SUBJECT varchar(255) NOT NULL,
 
−
MSS_TO varchar(255) NOT NULL,
 
−
MSS_USER varchar(127) NOT NULL,
 
−
PRIMARY KEY(MSS_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROFILE_MSC_EXTENSION  (
 
−
PEX_EXTENSION varchar(255) NULL,
 
−
PEX_ID int(11) NOT NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROFILE_MSC_REPORT  (
 
−
PRP_ID int(11) NOT NULL,
 
−
PRP_REPORT int(11) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROFILE_WZRD_PROP_GRP  (
 
−
PPG_ID int(11) NOT NULL,
 
−
PPG_PROPERTY_GROUP varchar(255) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROFILE_WZRD_WORKFLOW  (
 
−
PWF_ID int(11) NOT NULL,
 
−
PWF_WORKFLOW varchar(255) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROP_QUERY_RECEIVED  (
 
−
PQR_ACCEPTED char(1) NOT NULL,
 
−
PQR_COMMENT longtext NULL,
 
−
PQR_FROM varchar(127) NOT NULL,
 
−
PQR_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
PQR_QUERY int(11) NULL,
 
−
PQR_SEEN_DATE datetime NULL,
 
−
PQR_SENT_DATE datetime NOT NULL,
 
−
PQR_TO varchar(255) NOT NULL,
 
−
PQR_USER varchar(127) NOT NULL,
 
−
PRIMARY KEY(PQR_ID)
 
−
);
 
  
−
CREATE TABLE OKM_PROP_QUERY_SENT  (
+
* Copy the output of these sentences to a secure place.
−
PQS_COMMENT longtext NULL,
+
* Stop JBoss.
−
PQS_FROM varchar(127) NOT NULL,
 
−
PQS_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
PQS_QUERY int(11) NULL,
 
−
PQS_SENT_DATE datetime NOT NULL,
 
−
PQS_TO varchar(127) NOT NULL,
 
−
PQS_USER varchar(127) NOT NULL,
 
−
PRIMARY KEY(PQS_ID)
 
−
);
 
  
−
CREATE TABLE OKM_PROP_SUB_RECEIVED  (
+
== OpenKM 5.1 stuff ==
−
PSR_ACCEPTED char(1) NOT NULL,
+
* Stop JBoss.
−
PSR_COMMENT longtext NULL,
+
* Copy or move the '''$JBOSS_50_HOME/repository.xml''' file and '''$JBOSS_50_HOME/repository''' directory to '''$JBOSS_51_HOME'''
−
PSR_FROM varchar(127) NOT NULL,
+
* Change '''$JBOSS_51_HOME/server/default/conf/login-config.xml''' according to those changes made at '''$JBOSS_50_HOME/server/default/conf/login-config.xml''', if any. Maybe you don't need to do this because didn't change anything in the OpenKM 5.0.x installation.
−
PSR_ID int(11) AUTO_INCREMENT NOT NULL,
+
* Change '''hibernate.hbm2ddl=none''' to '''hibernate.hbm2ddl=create''' in '''OpenKM.cfg'''; otherwise, the translation table won't be created, which results in an "Error getting translations: English translation is mandatory and can not be deleted" error message.
−
PSR_SEEN_DATE datetime NULL,
+
* Start JBoss.
−
PSR_SENT_DATE datetime NOT NULL,
+
* Log into OpenKM 5.1 and go to '''Administration''' > '''Database query''' and execute the output of the previous stored SQL sentences.
−
PSR_TO varchar(127) NOT NULL,
+
* Now your users and roles from the old OpenKM 5.0.4 has been restored in OpenKM 5.1.8
−
PSR_USER varchar(127) NOT NULL,
+
* Change '''hibernate.hbm2ddl=create''' to '''hibernate.hbm2ddl=none''' in '''OpenKM.cfg'''
−
PSR_UUID varchar(255) NOT NULL,
 
−
PRIMARY KEY(PSR_ID)
 
−
);
 
  
−
CREATE TABLE OKM_PROP_SUB_SENT  (
+
[[Category: Migration Guide]]
−
PS_COMMENT longtext NULL,
 
−
PS_FROM varchar(127) NOT NULL,
 
−
PS_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
PS_SENT_DATE datetime NOT NULL,
 
−
PS_TO varchar(127) NOT NULL,
 
−
PS_USER varchar(127) NOT NULL,
 
−
PS_UUID varchar(255) NOT NULL,
 
−
PRIMARY KEY(PS_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_QUERY_PARAMS_SHARED  (
 
−
QPS_ID int(11) NOT NULL,
 
−
QPS_USER varchar(255) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_STAMP_IMAGE  (
 
−
SI_ACTIVE char(1) NOT NULL,
 
−
SI_DESCRIPTION varchar(512) NULL,
 
−
SI_EXPR_X varchar(255) NOT NULL,
 
−
SI_EXPR_Y varchar(255) NOT NULL,
 
−
SI_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
SI_IMAGE_CONTENT longtext NOT NULL,
 
−
SI_IMAGE_MIME varchar(255) NOT NULL,
 
−
SI_LAYER int(11) NOT NULL,
 
−
SI_NAME varchar(255) NOT NULL,
 
−
SI_OPACITY float NOT NULL,
 
−
PRIMARY KEY(SI_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_STAMP_IMAGE_USER  (
 
−
SIU_ID int(11) NOT NULL,
 
−
SIU_USER varchar(255) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_STAMP_TEXT  (
 
−
ST_ACTIVE char(1) NOT NULL,
 
−
ST_ALIGN int(11) NOT NULL,
 
−
ST_COLOR varchar(255) NOT NULL,
 
−
ST_DESCRIPTION varchar(512) NULL,
 
−
ST_EXPR_X varchar(255) NOT NULL,
 
−
ST_EXPR_Y varchar(255) NOT NULL,
 
−
ST_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
ST_LAYER int(11) NOT NULL,
 
−
ST_NAME varchar(255) NOT NULL,
 
−
ST_OPACITY float NOT NULL,
 
−
ST_ROTATION int(11) NOT NULL,
 
−
ST_SIZE int(11) NOT NULL,
 
−
ST_TEXT varchar(255) NOT NULL,
 
−
PRIMARY KEY(ST_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_STAMP_TEXT_USER  (
 
−
STU_ID int(11) NOT NULL,
 
−
STU_USER varchar(255) NULL
 
−
);
 
−
 
 
−
CREATE TABLE OKM_TRANSLATION  (
 
−
TR_KEY varchar(127) NOT NULL,
 
−
TR_LANGUAGE varchar(8) NOT NULL,
 
−
TR_MODULE varchar(127) NOT NULL,
 
−
TR_TEXT varchar(255) NOT NULL,
 
−
PRIMARY KEY(TR_MODULE,TR_KEY,TR_LANGUAGE)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_ZOHO_EDITING  (
 
−
ZED_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
ZED_TMP varchar(255) NULL,
 
−
ZED_USER varchar(127) NOT NULL,
 
−
ZED_UUID varchar(127) NULL,
 
−
PRIMARY KEY(ZED_ID)
 
−
);
 
−
</source>
 

Latest revision as of 13:04, 27 August 2012


Nota clasica.png In OpenKM 5.1.x the configuration is managed from Administration and stored in database, so most of the configuration in OpenKM.cfg from OpenKM 5.0 is not valid and should be migrated. Only database related stuff like hibernate.dialect and hibernate.hbm2ddl makes sense in OpenKM 5.1.x configuration file.

Changes

com.openkm.extractor.TiffTextExtractor has been deprecated, so you need to edit the repository.xml and repository/workspaces/default/workspace.xml files and change by one of these options:

  • com.openkm.extractor.AbbyTextExtractor
  • com.openkm.extractor.CuneiformTextExtractor
  • com.openkm.extractor.Tesseract2TextExtractor
  • com.openkm.extractor.Tesseract3TextExtractor

See also Third-party_software_integration: OCR.

OpenKM 5.0 stuff

  • Make a backup!
  • Log into OpenKM 5.0 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 + ''', ''' + CASEWHEN(USR_ACTIVE,'T', 'F') + ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' + ROL_ID + ''', ''' + CASEWHEN(ROL_ACTIVE, 'T', 'F') + ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' + UR_USER + ''', ''' + 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, ''', ''', IF(USR_ACTIVE IS TRUE,'T', 'F'), ''');') from OKM_USER;
SELECT CONCAT('INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''', ROL_ID, ''', ''', IF(ROL_ACTIVE IS TRUE, 'T', 'F'), ''');') from OKM_ROLE;
SELECT CONCAT('INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''', UR_USER, ''', ''', 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 || ''', ''' || CASE WHEN USR_ACTIVE=1 THEN 'T' ELSE 'F' END || ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' || ROL_ID || ''', ''' || CASE WHEN ROL_ACTIVE=1 THEN 'T' ELSE 'F' END || ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''' || 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 || ''', ''' || DECODE(USR_ACTIVE, 1, 'T', 'F') || ''');' from OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' || ROL_ID || ''', ''' || DECODE(ROL_ACTIVE, 1, 'T', 'F') || ''');' from OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' || UR_USER || ''', ''' || UR_ROLE || ''');' from OKM_USER_ROLE;
  • Copy the output of these sentences to a secure place.
  • Stop JBoss.

OpenKM 5.1 stuff

  • Stop JBoss.
  • Copy or move the $JBOSS_50_HOME/repository.xml file and $JBOSS_50_HOME/repository directory to $JBOSS_51_HOME
  • Change $JBOSS_51_HOME/server/default/conf/login-config.xml according to those changes made at $JBOSS_50_HOME/server/default/conf/login-config.xml, if any. Maybe you don't need to do this because didn't change anything in the OpenKM 5.0.x installation.
  • Change hibernate.hbm2ddl=none to hibernate.hbm2ddl=create in OpenKM.cfg; otherwise, the translation table won't be created, which results in an "Error getting translations: English translation is mandatory and can not be deleted" error message.
  • Start JBoss.
  • Log into OpenKM 5.1 and go to Administration > Database query and execute the output of the previous stored SQL sentences.
  • Now your users and roles from the old OpenKM 5.0.4 has been restored in OpenKM 5.1.8
  • Change hibernate.hbm2ddl=create to hibernate.hbm2ddl=none in OpenKM.cfg
Retrieved from "https://www.openkm.com/wiki/index.php?title=Migration_from_5.0.4_to_5.1.8&oldid=7036"