TRS.WCM.Developer/贵州项目/0_delete_redundance_data.sql

-- 迁移前先清除冗余数据
-- 需求场景：按站点迁移一个节点，须保留的站点id人工整理

DELETE from wcmwebsite where siteid not in (124,120,66,108,35,52,101,91,76,88,184,195,188,177,194,193,187,190,183,186,191,189,175,176,180,179,174,169,130,166,165,164,199,198,197,173,182,155,171,196);
-- 删除多余栏目
DELETE from wcmchannel where siteId not in (SELECT siteId from wcmwebsite) or status < 0;
-- 栏目模板触发发布关联关系
DELETE from wcmchannelchildindexquote where FOLDERID not in (SELECT siteId from wcmwebsite) and FOLDERTYPE = 103;
DELETE from wcmchannelchildindexquote where FOLDERID not in (SELECT channelId from wcmchannel) and FOLDERTYPE = 101;
-- 栏目模板触发发布关联关系(似乎多余)
DELETE from wcmchannelchildindexquote where TEMPLATEID not in (SELECT TEMPID from wcmtemplate);
-- 文档分发关系
DELETE from wcmchannelsyn where srcchannel not in (SELECT channelId from wcmchannel);
DELETE from wcmchannelsyn where tochannel not in (SELECT channelId from wcmchannel);
-- 存放路径
DELETE from wcmfolderpublishconfig where FOLDERID not in (SELECT channelId from wcmchannel) and FOLDERTYPE = 101;
DELETE from wcmfolderpublishconfig where FOLDERID not in (SELECT siteId from wcmwebsite) and FOLDERTYPE = 103;
-- 多余模板
DELETE from wcmtemplate where rootid <> 0 and rootid not in (SELECT siteId from wcmwebsite);
-- 模板变量
DELETE from wcmtemplateargument where TEMPLATEID not in (SELECT TEMPID from wcmtemplate);
-- 模板栏目绑定关系
DELETE from wcmtemplateemploy where EMPLOYERID not in (SELECT CHANNELID from wcmchannel) and EMPLOYERTYPE = 101;
DELETE from wcmtemplateemploy where EMPLOYERID not in (SELECT siteId from wcmwebsite) and EMPLOYERTYPE = 103;
DELETE from wcmtemplateemploy where TEMPLATEID not in (SELECT TEMPID from wcmtemplate);
-- 嵌套模板信息
DELETE from wcmtemplatenest where templateId not in (SELECT TEMPID from wcmtemplate);
DELETE from wcmtemplatenest where nestedtemplateId not in (SELECT TEMPID from wcmtemplate);
-- 模板引用信息
DELETE from wcmtemplatequote where templateId not in (SELECT TEMPID from wcmtemplate);
DELETE from wcmtemplatequote where quotedFolderId not in (SELECT CHANNELID from wcmchannel) and quotedFolderType = 101;
DELETE from wcmtemplatequote where quotedFolderId not in (SELECT siteId from wcmwebsite) and quotedFolderType = 103;
-- 栏目绑定的视图
DELETE from xwcmmetaviewemployer where CHANNELID not in (SELECT CHANNELID from wcmchannel);
-- 视图信息
DELETE from xwcmviewinfo where VIEWINFOID not in (SELECT VIEWID from xwcmmetaviewemployer);
-- 视图字段信息
DELETE from xwcmviewfieldinfo where VIEWID not in (SELECT viewinfoid from xwcmviewinfo);
DELETE from xwcmmetaviewfieldgroup where metaviewid not in (SELECT viewinfoid from xwcmviewinfo);
-- 元数据表信息
DELETE from xwcmtableinfo where TABLEINFOID not in (SELECT MAINTABLEID from xwcmviewinfo);
DELETE from xwcmdbfieldinfo where TABLEID not in (SELECT TABLEINFOID from xwcmtableinfo);

-- --------数据记录
-- chnldoc记录
DELETE from wcmchnldoc where CHNLID not in (SELECT CHANNELID from wcmchannel);
-- 时间: 609.133s
-- document
DELETE from wcmdocument where docchannel not in (SELECT CHANNELID from wcmchannel);
-- 时间: 1295.934s
-- metatable如何清理？