Difference between revisions of "Knowledge:Migration from 6.2.21 to 6.4.2"

$ grep "ERROR .*hbm2ddl" $TOMCAT_HOME/logs/catalina.log

SELECT AAC_ID, AAC_TYPE, 'UPDATE OKM_AUTO_ACTION set AAC_CLASS_NAME=''' || (SELECT AMD_CLASS_NAME FROM OKM_AUTO_METADATA WHERE AMD_ID=AAC_TYPE) || ''' where AAC_ID=' || AAC_ID || ';'  FROM OKM_AUTO_ACTION;
SELECT AVL_ID, AVL_TYPE, 'UPDATE OKM_AUTO_VALIDATION set AVL_CLASS_NAME=''' || (SELECT AMD_CLASS_NAME FROM OKM_AUTO_METADATA WHERE AMD_ID=AVL_TYPE) || ''' where AVL_ID=' || AVL_ID || ';' FROM OKM_AUTO_VALIDATION;

SELECT AAC_ID, AAC_TYPE, CONCAT('UPDATE OKM_AUTO_ACTION set AAC_CLASS_NAME=''',(SELECT AMD_CLASS_NAME FROM OKM_AUTO_METADATA WHERE AMD_ID=AAC_TYPE),''' where AAC_ID=',AAC_ID,';')  FROM OKM_AUTO_ACTION;
SELECT AVL_ID, AVL_TYPE, CONCAT('UPDATE OKM_AUTO_VALIDATION set AVL_CLASS_NAME=''',(SELECT AMD_CLASS_NAME FROM OKM_AUTO_METADATA WHERE AMD_ID=AVL_TYPE),''' where AVL_ID=',AVL_ID,';') FROM OKM_AUTO_VALIDATION;

-- Configuration
UPDATE OKM_CONFIG SET CFG_KEY='text.banner' WHERE CFG_KEY='logo.banner';
UPDATE OKM_CONFIG SET CFG_KEY='text.welcome' WHERE CFG_KEY='logo.welcome';
UPDATE OKM_CONFIG SET CFG_TYPE='string' WHERE CFG_KEY='max.file.size';

-- Default tenant and node class values
UPDATE OKM_NODE_BASE SET NBS_TENANT='1', NBS_NODE_CLASS='0';

-- Default folder style
INSERT INTO `OKM_FOLDER_STYLE` VALUES (1,'T','Default','R0lGODlhDwANAKUrAI8ZLJ8dL54gNbkVKKYkMJ8nNdAWH9UVHq4kOK0rOr4kNa0vPrQyO/8VG7wyOLwyP8Q2PcQ2RP81Jf9IP9hwdeB7ePd8ef5/ZviAgPiGheiRiPiOiPiTkPCZkPiYk/ihmPiomPinoPiooPi5sPjQyPjRyfjTy/nUzfnWz/nX0frW2P///////////////////////////////////////////////////////////////////////////////////yH5BAEKAD8ALAAAAAAPAA0AAAZywN8PQoQIj8gfYsVcIZLHCAjU0VQokWz26CB5v2DH8TEqm8+CIyPEDi3e8HiCQ198PiLS5cCfLAgbGwuBGx4lByoSDQMkARYLGJEYGSaIigolAAsWnJ0nlg0KJj8FpaYFFAaJoSdQR6AKKK5CEwO2EylBADs=','image/gif','R0lGODlhDwANAKU6ALkVKJ8nNdAWH9UVHklTY74kNa0vPqswP6oxQKU0Q0Zhcv8VGzpoeD9tfEJvfkVygUl1hEx4hlR3hlJ4h1B5iE96if81Jf9IP9hwdXicqIGjrvd8ebCaof5/ZviAgPiGhZKwupezu6yrsPiOiJq2v6Kzu/iYk6jAyPihmLK9w/iooL7L0LnN08HKzr/L0L3M0cDLz8rZ3vjQyPjRyfjTy/nUzfnWz/nX0frW2Pr7/P///////////////////////yH5BAEKAD8ALAAAAAAPAA0AAAZ6wN+PUqFMhMjkz5Fr5hxKZOV0IoE0mYq2IkFGYuCwOOJlmVkvV6sFWymQEOopdTDY7/cHiVRKoFAqMh0DhBcGDSEiBiOMJjMDOBYLADIMHAYemR4fNJCSBTMECBukpTWeCwU0PwGtrgEYApGpNVFIqAU2tkIXAL4XN0EAOw==','image/gif','R0lGODlhDwALAKUtAI8ZLJ8dL54gNaYkMJ8nNa4kOKItOq0rOqQxPq0vPrQyO680QrwyOLwyP8Q2PcQ2RNhwdeB7ePd8efd+e/eBffiAgPeDgPiGheiRiPiJiPiMiviOiPiPjfiQiviTkPCZkPiYk/ialfidl/ihmPiomPinoPiooPiqoviro/ispPi5sPjQyP////jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTy/jTyyH+FUNyZWF0ZWQgd2l0aCBUaGUgR0lNUAAh+QQBCgA/ACwAAAAADwALAAAGZcDfz0F0CI/IX4HFZBWSxweJ9MFEII9s9shYeb9gxrGhKpvPgqOixC4l3vD3AnHw2BOjkcl0QqXyBAMbGwmDGyAgISIbHQQ/ARIJFZMVFxcZGhyOPwAJEp+gExQWm0IEp6ioBkhBADs=','image/gif','image/gif','R0lGODlhDwALAKU8AJ8nNVRKW6ItOqQxPq0vPllTZaczQqY0Q680QjpoeD9tfEJvfkVygWVpdUl1hH1fbnJldGtpeEx4hl9xf1Z2hVJ4h096iXicqIGjrvd8efd+e5OnsfeBffiAgPeDgPiGhZKwuviJiJazvJ6wuPiMiviOiPiPjfiQipq2v7Kts/iYk/ialfidl6jAyKnAyPihmL+5vPiooPiqoviro/ispLnN08TJzcbJzMTKzcfJy8rZ3vr7/P///////////////yH5BAEKAD8ALAAAAAAPAAsAAAZswN+vYrFMhMjkb7Fr7hZKpKXVQoEwF8oDEnkgJbqweNz41s61G86WaxeQDqoLRqjb64gBA4VKEV4vMTEyMzSAAAoiIwQljSoqKywlJwA/CRsHHZodHx8hJCaVPwEGGaanGhweokIArq+vAklBADs=',1,'R0lGODlhDwANAKUjAI9SGZ5fHZ5fIJ5mJ6VsJK1sJLltFa1yK61yL7R/Mrx/MryFMsOFNsOLNtSLFf+oFdiwcODAeP/GJP/GP+jQiPjQePjQgPDYkPjYiPjYkP/iZPjgmPjgoPjomPjosPrs1fjwyPr59//++f///////////////////////////////////////////////////////////////////////////////////////////////////////////////////yH+FUNyZWF0ZWQgd2l0aCBUaGUgR0lNUAAh+QQBCgA/ACwAAAAADwANAAAGacDfr0FsCI/IX2HEHBWSR0anc6FEIIxs9rgAeb/gxVHhKZvPgmOCw+Yg3vD4IUNHbO4ijWM/QRAwGAiAgCIOHxIPBiEBFQgWj4+Fh4khAAgVmJmSiIo/A5+gA5uUUEeGnCGlQhMGrRMhQQA7','image/gif','R0lGODlhDwANAKUiACRUZC9ebjRjcjhmdjlndz1rej5sez9tfEl1hEx4hlB7ilJ9i1aAjlmDkX6grIGjrpSxu5i0vpy3wKa/x6zDy67FzLLIz7fL0rnN07/R18HT2cTV2sjY3c3b4Nrl6O3y9Pf5+vr7/P///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////yH5BAEKAD8ALAAAAAAPAA0AAAZpwN+PQWQIj8gfIcQMEZLHBYdjmUAei2z2qPB4v2DFMdEpm8+BI2LD3hze8LgBQz9o7qBMYy85DC4XB4CAIA0eEQ4FHwEUBxWPj4WHiR8ABxSYmZKIij8Cn6ACm5RQR4acH6VCEgWtEh9BADs=','image/gif','R0lGODlhDwALAIQaAI9SGZ5fHZ5fIJ5mJ6VsJK1sJK1yK61yL7R/Mrx/MryFMsOFNsOLNtiwcODAeOjQiPjQePjQgPDYkPjYiPjYkPjgmPjgoPjomPjosPjwyP///////////////////////yH+FUNyZWF0ZWQgd2l0aCBUaGUgR0lNUAAh+QQBCgAfACwAAAAADwALAAAFTeD3MSQjnuhXaKxWpOdyXdLjNEuen0rm/0DFKYEpGo+CE8LCtBye0KiBQj1Urtjr40CYTA7ecHj7CUAOkbQ6Tf4ADpC4PN4WDe74PCoEADs=','image/gif','image/gif','R0lGODlhDwALAIQaACRUZC9ebjRjcjhmdjlndz5sez9tfEl1hEx4hlB7ilJ9i1aAjoGjrpSxu6a/x6zDy67FzLLIz7fL0rnN08HT2cTV2sjY3c3b4Nrl6Pr7/P///////////////////////yH5BAEKAB8ALAAAAAAPAAsAAAVN4Pct5CKe6EdkbEakp2JZkdMwSp6fCeb/wMQJcSkaj4HTocKsGJ7QaGFCNVCu2KvDMJBIDN5wePsJPAyQtDpN/gAMj7g83hYJ7vg8KgQAOw==');

-- Default folder style value
UPDATE OKM_NODE_FOLDER SET NFL_STYLE = '0';

-- Register metadata
INSERT INTO OKM_PROPERTY_GROUP_DEF (PGD_ID, PGD_TENANT, PGD_DEFINITION) VALUES (1, 1, '');

-- Number of records counter by user is 0
UPDATE OKM_USER_ITEMS SET UI_RECORDS=0;

-- Created default tenant
INSERT INTO OKM_TENANT (TNT_ID, TNT_NAME) VALUES (1, 'Default');

-- Update role to tenant 1
select CONCAT('INSERT INTO OKM_ENTITY_TENANT (ETN_ENTITY,ETN_TENANT,ETN_TYPE) VALUES (''',ROL_ID,''',''','1',''',''','role',''');') from OKM_ROLE;

-- Update users to tenant 1
select CONCAT('INSERT INTO OKM_ENTITY_TENANT (ETN_ENTITY,ETN_TENANT,ETN_TYPE) VALUES (''',USR_ID,''',''','1',''',''','role',''');') from OKM_USER;

DROP TABLE OKM_REG_PROPERTY_GROUP;
DROP TABLE OKM_REG_PROPERTY;
CREATE TABLE OKM_REG_PROPERTY_GROUP  ( 
 RPG_ID bigint(20) AUTO_INCREMENT NOT NULL,
 RPG_NAME varchar(64) NULL,
 RPG_TENANT bigint(20) NOT NULL,
 PRIMARY KEY(RPG_ID),
 CONSTRAINT RPG_NAME
 UNIQUE (RPG_NAME, RPG_TENANT)
);
CREATE TABLE OKM_REG_PROPERTY  ( 
 RPR_GROUP bigint(20) NOT NULL,
 RPR_NAME varchar(64) NOT NULL,
 RPR_TYPE varchar(64) NULL,
 PRIMARY KEY(RPR_GROUP,RPR_NAME),
 CONSTRAINT FKB3632CCED4ABA44F FOREIGN KEY(RPR_GROUP) REFERENCES OKM_REG_PROPERTY_GROUP(RPG_ID) ON DELETE RESTRICT ON UPDATE RESTRICT 
);
CREATE INDEX FKB3632CCED4ABA44F USING BTREE ON OKM_REG_PROPERTY(RPR_GROUP);

-- Purge older database indexes, columns or changes in columns
ALTER TABLE OKM_ACTIVITY DROP INDEX IDX_ACTIVITY_DATACT;
ALTER TABLE OKM_ACTIVITY DROP INDEX IDX_ACTIVITY_USRACT;
ALTER TABLE OKM_AUTO_ACTION DROP COLUMN AAC_TYPE;
DROP TABLE OKM_AUTO_METADATA;
ALTER TABLE OKM_AUTO_VALIDATION DROP COLUMN AVL_TYPE;
ALTER TABLE OKM_DASHBOARD_ACTIVITY DROP INDEX IDX_DASH_ACTI_DATACT;
ALTER TABLE OKM_DASHBOARD_ACTIVITY DROP INDEX IDX_DASH_ACTI_USRACT;
ALTER TABLE OKM_DASHBOARD_ACTIVITY DROP COLUMN DAC_PATH;
ALTER TABLE OKM_MAIL_ACCOUNT MODIFY MA_MMARK_SEEN int(11) NOT NULL;
ALTER TABLE OKM_NODE_BASE DROP COLUMN NDC_SCRIPT_CODE;
ALTER TABLE OKM_NODE_BASE DROP COLUMN NDC_SCRIPTING;
ALTER TABLE OKM_PROFILE MODIFY PRF_DB_MAIL_VIS char(1) NULL DEFAULT 'F';
ALTER TABLE OKM_PROFILING DROP INDEX IDX_PROFILING_DATCLS;
ALTER TABLE OKM_PROFILING DROP INDEX IDX_PROFILING_USRCLS;
ALTER TABLE OKM_STAMP_TEXT MODIFY ST_TEXT	varchar(255) NULL;

-- UPDATE OKM_AUTO_ACTION_PARAMS
ALTER TABLE OKM_AUTO_ACTION_PARAMS DROP PRIMARY KEY, ADD PRIMARY KEY(AAP_ACTION,AAP_ORDER); 
SHOW CREATE TABLE OKM_AUTO_ACTION_PARAMS;

-- DELETE AAP_VALIDATION CONSTRAINT in this installation case was FK1F747E2D721D97C2 the name in other installation will be different
ALTER TABLE OKM_AUTO_ACTION_PARAMS DROP FOREIGN KEY FK1F747E2D721D97C2;
ALTER TABLE OKM_AUTO_ACTION_PARAMS DROP INDEX FK1F747E2D721D97C2;
ALTER TABLE OKM_AUTO_ACTION_PARAMS DROP COLUMN AAP_VALIDATION;

mysqlcheck -h localhost -u root -p[password] okmdb;
mysqloptimize -h localhost -u root -p[password] okmdb;
# analyze seems not be needed because optimize does it operation
mysqlanalyze -h localhost -u root -p[password] okmdb;