Difference between revisions of "Migration from 5.0.4 to 5.1.8"
From OpenKM Documentation
(→Database modifications) |
|||
Line 9: | Line 9: | ||
== Database modifications == | == 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 | + | === Change table columns definition === |
<source lang="sql"> | <source lang="sql"> | ||
Line 23: | Line 24: | ||
</source> | </source> | ||
− | Rename table columns | + | === Rename table columns === |
<source lang="sql"> | <source lang="sql"> | ||
Line 30: | Line 31: | ||
</source> | </source> | ||
− | Remove deprecated table columns | + | === Remove deprecated table columns === |
<source lang="sql"> | <source lang="sql"> | ||
Line 38: | Line 39: | ||
</source> | </source> | ||
− | Remove deprecated tables | + | === Remove deprecated tables === |
<source lang="sql"> | <source lang="sql"> | ||
Line 44: | Line 45: | ||
</source> | </source> | ||
− | Create new tables | + | === Create new tables === |
<source lang="sql"> | <source lang="sql"> | ||
Line 325: | Line 326: | ||
); | ); | ||
</source> | </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. | ||
== System modifications == | == System modifications == | ||
* Start JBoss and cross your fingers! :) | * Start JBoss and cross your fingers! :) |
Revision as of 13:37, 30 August 2011
This migration process is under beta state and the SQL sentences are generated for MySQL. In case of other database may need some changes. |
Contents
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
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! :)