Migration from 5.0.4 to 5.1.8

From OpenKM Documentation
Revision as of 13:37, 30 August 2011 by Pavila (talk | contribs) (→‎Database modifications)

Jump to: navigation, search

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.

System modifications

  • 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

Database modifications


Nota clasica.png 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

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;

Rename table columns

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;

Remove deprecated table columns

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;

Remove deprecated tables

DROP TABLE OKM_FILTER_RULE;

Create new tables

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)
);

Import initial data

Execute default import script from https://openkm.svn.sourceforge.net/svnroot/openkm/branches/5.1/openkm/src/main/resources/default.sql.

System modifications

  • Start JBoss and cross your fingers! :)
Retrieved from "https://www.openkm.com/wiki/index.php?title=Migration_from_5.0.4_to_5.1.8&oldid=4900"