TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/迁移前每个节点的特殊处理/city7/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;
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
(112,11,12,15,18,20,21,22,23,24,25,26,29,30,32,33,34,36,38,39,40,41,43,49,52,57,58,59,60,61,62,63,65,67,68,69,73,74,75,76,79,83,85,86,89,91,93,219,228,232,233,234,72,235,31,37,103,102,229,53,48,88,87,17,64,47,19,231,14,84);
-- 修复部分站点站点不显示问题
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 DOCID in (SELECT DOCID from exchange_chnldoc);
-- metatable

DROP TABLE IF EXISTS exchange_metatableGovInfo;
DROP TABLE IF EXISTS exchange_metatablefzbxzzfry;
DROP TABLE IF EXISTS exchange_metatablefzbxzzfzt;
DROP TABLE IF EXISTS exchange_metatablespzdgg;
DROP TABLE IF EXISTS exchange_metatableyfbzt;
DROP TABLE IF EXISTS exchange_metatablebloodStatus;
create table exchange_metatableGovInfo like wcmmetatableGovInfo;
create table exchange_metatablefzbxzzfry like wcmmetatablefzbxzzfry;
create table exchange_metatablefzbxzzfzt like wcmmetatablefzbxzzfzt;
create table exchange_metatablespzdgg like wcmmetatablespzdgg;
create table exchange_metatableyfbzt like wcmmetatableyfbzt;
create table exchange_metatablebloodStatus like wcmmetatablebloodStatus;
INSERT INTO exchange_metatableGovInfo select * from wcmmetatableGovInfo where metadataId in (select docid from exchange_document);
INSERT INTO exchange_metatablefzbxzzfry select * from wcmmetatablefzbxzzfry where metadataId in (select docid from exchange_document);
INSERT INTO exchange_metatablefzbxzzfzt select * from wcmmetatablefzbxzzfzt where metadataId in (select docid from exchange_document);
INSERT INTO exchange_metatablespzdgg select * from wcmmetatablespzdgg where metadataId in (select docid from exchange_document);
INSERT INTO exchange_metatableyfbzt select * from wcmmetatableyfbzt where metadataId in (select docid from exchange_document);
INSERT INTO exchange_metatablebloodStatus select * from wcmmetatablebloodStatus where metadataId in (select docid from exchange_document);
-- 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;