TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/迁移前每个节点的特殊处理/city3/00_Preprocessing.sql

-- 删除不好，不能在原库运行，改用创建中间表的方式，减小迁移数据量，加速迁移
-- 需求场景：按站点迁移一个节点，须保留的站点id人工整理

-- 创建所需的中间表
drop table if exists exchange_appendix;
create table exchange_appendix like wcmappendix;
drop table if exists exchange_channel;
create table exchange_channel like wcmchannel;
drop table if exists exchange_channelchildindexquote;
create table exchange_channelchildindexquote like wcmchannelchildindexquote;
drop table if exists exchange_channelsyn;
create table exchange_channelsyn like wcmchannelsyn;
drop table if exists exchange_chnldoc;
create table exchange_chnldoc like wcmchnldoc;
drop table if exists exchange_document;
create table exchange_document like wcmdocument;
drop table if exists exchange_folderpublishconfig;
create table exchange_folderpublishconfig like wcmfolderpublishconfig;
drop table if exists exchange_template;
create table exchange_template like wcmtemplate;
drop table if exists exchange_templateargument;
create table exchange_templateargument like wcmtemplateargument;
drop table if exists exchange_templateemploy;
create table exchange_templateemploy like wcmtemplateemploy;
drop table if exists exchange_templatenest;
create table exchange_templatenest like wcmtemplatenest;
drop table if exists exchange_templatequote;
create table exchange_templatequote like wcmtemplatequote;
drop table if exists exchange_website;
create table exchange_website like wcmwebsite;
drop table if exists exchange_classinfo;
create table exchange_classinfo like xwcmclassinfo;
drop table if exists exchange_classinfoview;
create table exchange_classinfoview like xwcmclassinfoview;
drop table if exists exchange_dbfieldinfo;
create table exchange_dbfieldinfo like xwcmdbfieldinfo;
drop table if exists exchange_material;
create table exchange_material like xwcmmaterial;
drop table if exists exchange_materialquote;
create table exchange_materialquote like xwcmmaterialquote;
drop table if exists exchange_metaviewemployer;
create table exchange_metaviewemployer like xwcmmetaviewemployer;
drop table if exists exchange_metaviewfieldgroup;
create table exchange_metaviewfieldgroup like xwcmmetaviewfieldgroup;
drop table if exists exchange_tableinfo;
create table exchange_tableinfo like xwcmtableinfo;
drop table if exists exchange_viewfieldinfo;
create table exchange_viewfieldinfo like xwcmviewfieldinfo;
drop table if exists exchange_viewinfo;
create table exchange_viewinfo like xwcmviewinfo;

INSERT INTO exchange_website SELECT  * from wcmwebsite where siteid in (
19,53,60,78,84,86,92,93,96,97,98,99,102,103,105,109,112,114,115,119,125,131,132,162,130,168,171,172,173,174,175,178
);
-- 修复部分站点站点不显示问题
UPDATE exchange_website set MEDIATYPE = 1;
-- 删除多余栏目
INSERT INTO exchange_channel SELECT * from wcmchannel where siteId in (SELECT siteId from exchange_website) and status >= 0;
-- 栏目模板触发发布关联关系
INSERT INTO exchange_channelchildindexquote SELECT * from wcmchannelchildindexquote where (FOLDERID IN (SELECT siteId from exchange_website) and FOLDERTYPE = 103) OR (FOLDERID IN (SELECT channelId from exchange_channel) and FOLDERTYPE = 101);
-- 文档分发关系
INSERT INTO exchange_channelsyn SELECT * from wcmchannelsyn where srcchannel in (SELECT channelId from exchange_channel) and tochannel in (SELECT channelId from exchange_channel);
-- 存放路径
INSERT INTO exchange_folderpublishconfig SELECT * from wcmfolderpublishconfig where (FOLDERID IN (SELECT siteId from exchange_website) and FOLDERTYPE = 103) OR (FOLDERID IN (SELECT channelId from exchange_channel) and FOLDERTYPE = 101);
-- 多余模板
INSERT INTO exchange_template SELECT * from wcmtemplate where rootid = 0 or rootid in (SELECT siteId from exchange_website);
-- 模板变量
INSERT INTO exchange_templateargument SELECT * from wcmtemplateargument where TEMPLATEID in (SELECT TEMPID from exchange_template);
-- 模板栏目绑定关系
INSERT INTO exchange_templateemploy SELECT * from wcmtemplateemploy WHERE (TEMPLATEID in (SELECT TEMPID from exchange_template)) or (EMPLOYERID IN (SELECT siteId from exchange_website) and EMPLOYERTYPE = 103) OR (EMPLOYERID IN (SELECT channelId from exchange_channel) and EMPLOYERTYPE = 101);
-- 嵌套模板信息
INSERT INTO exchange_templatenest SELECT * from wcmtemplatenest WHERE (templateId in (SELECT TEMPID from exchange_template)) and (nestedtemplateId in (SELECT TEMPID from exchange_template));
-- 模板引用信息
INSERT INTO exchange_templatequote SELECT * from wcmtemplatequote WHERE (templateId in (SELECT TEMPID from exchange_template)) or (quotedFolderId in (SELECT CHANNELID from exchange_channel) and quotedFolderType = 101) or (quotedFolderId in (SELECT siteId from exchange_website) and quotedFolderType = 103);
-- 栏目绑定的视图
INSERT INTO exchange_metaviewemployer SELECT * from xwcmmetaviewemployer where CHANNELID in (SELECT CHANNELID from exchange_channel);
-- 视图信息
INSERT INTO exchange_viewinfo SELECT * from xwcmviewinfo where VIEWINFOID in (SELECT VIEWID from exchange_metaviewemployer);
-- 视图字段信息
INSERT INTO exchange_viewfieldinfo SELECT * from xwcmviewfieldinfo where VIEWID in (SELECT viewinfoid from exchange_viewinfo);
INSERT INTO exchange_metaviewfieldgroup SELECT * from xwcmmetaviewfieldgroup where metaviewid in (SELECT viewinfoid from exchange_viewinfo);
-- 元数据表信息
INSERT INTO exchange_tableinfo SELECT * from xwcmtableinfo where TABLEINFOID in (SELECT MAINTABLEID from exchange_viewinfo);
INSERT INTO exchange_dbfieldinfo SELECT * from xwcmdbfieldinfo where TABLEID in (SELECT TABLEINFOID from exchange_tableinfo);
-- --------数据记录
INSERT INTO exchange_chnldoc SELECT * from wcmchnldoc WHERE CHNLID in (SELECT CHANNELID from exchange_channel);
-- exchange_chnldoc
INSERT INTO exchange_document SELECT * from wcmdocument WHERE docchannel in (SELECT CHANNELID from exchange_channel);

-- xwcmmaterialquote记录
INSERT INTO exchange_materialquote SELECT * from xwcmmaterialquote WHERE DOCID in (SELECT DOCID from wcmdocument);
-- xwcmmaterial记录
INSERT INTO exchange_material SELECT * from xwcmmaterial WHERE materialId in (SELECT materialId from exchange_materialquote);


-- SELECT CONCAT('DROP TABLE IF EXISTS exchange_metatable',TABLENAME,';') from exchange_tableinfo;
-- SELECT CONCAT('create table exchange_metatable',TABLENAME,' like wcmmetatable',TABLENAME ,';') from exchange_tableinfo;
-- SELECT CONCAT('INSERT INTO exchange_metatable',TABLENAME,' select * from wcmmetatable',TABLENAME ,' where metadataId in (select docid from exchange_document);') from exchange_tableinfo;