TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/迁移前每个节点的特殊处理/city6/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 (
328,392,332,379,350,341,257,287,289,372,177,217,319,382,349,329,307,317,326,252,383,304,303,369,313,280,264,256,380,352,141,344,267,373,193,188,269,258,358,279,345,312,316,273,261,192,359,140,142,325,301,283,322,388,254,324,315,300,173,262,346,263,137,375,309,334,386,216,268,327,333,293,275,305,351,348,282,308,368,255,387,302,318,163,311,278,271,331,310,265,374,340,272,371,296,260,249,339,335,299,248,274,355,215,253,384,277,290,181,143,198,242,377,259,323,353,285,366,378,314,266,363,336,288,292,284,286,294,276,295,306,298,364,270,390,376,245,367,162,250,337
);
-- 修复部分站点站点不显示问题
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 exchange_document);
-- xwcmmaterial记录
INSERT INTO exchange_material SELECT * from xwcmmaterial WHERE materialId in (SELECT materialId from exchange_materialquote);
-- metatable
DROP TABLE IF EXISTS exchange_metatableldr;
DROP TABLE IF EXISTS exchange_metatableGovInfo;
DROP TABLE IF EXISTS exchange_metatablezxft;
DROP TABLE IF EXISTS exchange_metatablelyd;
DROP TABLE IF EXISTS exchange_metatabledlgg;
DROP TABLE IF EXISTS exchange_metatableGXZZZS;
DROP TABLE IF EXISTS exchange_metatabletjsj;
create table exchange_metatableldr like wcmmetatableldr;
create table exchange_metatableGovInfo like wcmmetatableGovInfo;
create table exchange_metatablezxft like wcmmetatablezxft;
create table exchange_metatablelyd like wcmmetatablelyd;
create table exchange_metatabledlgg like wcmmetatabledlgg;
create table exchange_metatableGXZZZS like wcmmetatableGXZZZS;
create table exchange_metatabletjsj like wcmmetatabletjsj;
INSERT INTO exchange_metatableldr select * from wcmmetatableldr where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatableGovInfo select * from wcmmetatableGovInfo where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatablezxft select * from wcmmetatablezxft where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatablelyd select * from wcmmetatablelyd where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatabledlgg select * from wcmmetatabledlgg where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatableGXZZZS select * from wcmmetatableGXZZZS where metadataId in (select docid from exchange_chnldoc);
INSERT INTO exchange_metatabletjsj select * from wcmmetatabletjsj where metadataId in (select docid from exchange_chnldoc);

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;