Difference between revisions of "Migration from 5.0.4 to 5.1.8"

From OpenKM Documentation
Jump to: navigation, search
m (→‎System modifications)
Line 3: Line 3:
 
{{Warning|This migration process is under beta state and the SQL sentences are generated for MySQL. In case of other database may need some changes.}}
 
{{Warning|This migration process is under beta state and the SQL sentences are generated for MySQL. In case of other database may need some changes.}}
  
−
== Prepare system ==
+
* Login into Administration and execute these sentences in order to export users and roles:
 +
 
 +
<source lang="sql">
 +
SELECT CONCAT('INSERT INTO OKM_ROLE (UR_ROLE,UR_USER) VALUES (''', ROL_ID, ',''', ROL_ACTIVE, ''');') from OKM_ROLE;
 +
</source>
 +
 
 
* Stop JBoss
 
* Stop JBoss
 
* Make a backup!
 
* Make a backup!
Line 9: Line 14:
 
* Remove TiffTextExtractor from $JBOSS_HOME/repository.xml and $JBOSS_HOME/repository/workspaces/default/workspace.xml
 
* Remove TiffTextExtractor from $JBOSS_HOME/repository.xml and $JBOSS_HOME/repository/workspaces/default/workspace.xml
 
* Replace $JBOSS_HOME/server/default/deploy/OpenKM.war with the WAR from OpenKM 5.1.8
 
* Replace $JBOSS_HOME/server/default/deploy/OpenKM.war with the WAR from OpenKM 5.1.8
−
 
+
* Start JBoss again
−
== Database modifications ==
 
−
{{Note|You may want to run these sentences from Administration (http://localhost:8080/OpenKM/admin) - Database query, or from a database client like Aqua Data Studio.}}
 
−
 
 
−
=== Change table columns definition ===
 
−
 
 
−
<source lang="sql">
 
−
ALTER TABLE OKM_MAIL_ACCOUNT MODIFY COLUMN MA_ACTIVE char(1) NOT NULL;
 
−
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 ===
 
−
 
 
−
<source lang="sql">
 
−
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 ===
 
−
 
 
−
<source lang="sql">
 
−
ALTER TABLE OKM_BOOKMARK DROP COLUMN BM_PATH;
 
−
ALTER TABLE OKM_REPORT DROP COLUMN RP_TYPE;
 
−
ALTER TABLE OKM_CRON_TAB DROP COLUMN CT_TYPE;
 
−
</source>
 
−
 
 
−
=== Remove deprecated tables ===
 
−
 
 
−
<source lang="sql">
 
−
DROP TABLE OKM_FILTER_RULE;
 
−
</source>
 
−
 
 
−
=== Create new tables ===
 
−
 
 
−
<source lang="sql">
 
−
CREATE TABLE OKM_CONFIG  (
 
−
CFG_KEY varchar(255) NOT NULL,
 
−
CFG_TYPE varchar(255) NOT NULL,
 
−
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  (
 
−
PQS_COMMENT longtext NULL,
 
−
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  (
 
−
PSR_ACCEPTED char(1) NOT NULL,
 
−
PSR_COMMENT longtext NULL,
 
−
PSR_FROM varchar(127) NOT NULL,
 
−
PSR_ID int(11) AUTO_INCREMENT NOT NULL,
 
−
PSR_SEEN_DATE datetime NULL,
 
−
PSR_SENT_DATE datetime NOT NULL,
 
−
PSR_TO varchar(127) NOT NULL,
 
−
PSR_USER varchar(127) NOT NULL,
 
−
PSR_UUID varchar(255) NOT NULL,
 
−
PRIMARY KEY(PSR_ID)
 
−
);
 
−
 
 
−
CREATE TABLE OKM_PROP_SUB_SENT  (
 
−
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>
 
−
 
 
−
=== Import initial data ===
 
−
Execute default import script from https://openkm.svn.sourceforge.net/svnroot/openkm/branches/5.1/openkm/src/main/resources/default.sql.
 
−
 
 
−
== Complete process ==
 
−
* Start JBoss and cross your fingers! :)
 

Revision as of 13:42, 31 August 2011


Nota advertencia.png This migration process is under beta state and the SQL sentences are generated for MySQL. In case of other database may need some changes.

  • Login into Administration and execute these sentences in order to export users and roles:
SELECT CONCAT('INSERT INTO OKM_ROLE (UR_ROLE,UR_USER) VALUES (''', ROL_ID, ',''', ROL_ACTIVE, ''');') from OKM_ROLE;
  • Stop JBoss
  • Make a backup!
  • Delete $JBOSS_HOME/server/default/lib/hibernate-annotations.jar
  • Remove TiffTextExtractor from $JBOSS_HOME/repository.xml and $JBOSS_HOME/repository/workspaces/default/workspace.xml
  • Replace $JBOSS_HOME/server/default/deploy/OpenKM.war with the WAR from OpenKM 5.1.8
  • Start JBoss again
Retrieved from "https://www.openkm.com/wiki/index.php?title=Migration_from_5.0.4_to_5.1.8&oldid=4914"