表字段关联关系：

WCMSCHEDULE .OPTYPE=WCMOPER.OPERID
WCMDOCUMENT.DOCID=WCMCHNLDOC.DOCID
WCMDOCUMENT.DOCPUBURL =WCMCHNLDOC.DOCPUBURL
WCMDOCUMENT.DOCRELTIME =WCMCHNLDOC. DOCRELTIME
wcmappendix.APPDOCID=WCMDOCUMENT.DOCID
WCMDOCUMENT.DOCCHANNEL=WCMCHNLDOC.CHNLID=WCMCHANNEL.CHANNELID
WCMWEBSITE.SITEID=WCMDOCUMENT.SITEID =WCMCHANNEL.SITEID

待补充

1.删除引用

--1.删除所有引用
------1.1删除所有引用前，先进行备份
CREATE TABLE WCMCHNLDOCMODALBACK AS SELECT * FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3);
  
------1.2在备份后，删除所有引用
DELETE FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3);
 
   
  
--2.删除指定栏目（XXX，YYY）下的所有引用文档，其中XXX，YYY为栏目ID
------2.1删除引用前，先进行备份
CREATE TABLE WCMCHNLDOCMODALBACK AS SELECT * FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3) AND CHNLID IN (XXX,-XXX,YYY,-YYY);
  
------2.2在备份后，删除指定栏目（XXX，YYY）下的所有引用文档
DELETE FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3) AND CHNLID IN (XXX,-XXX,YYY,-YYY);

2.删除指定的站点

--删除站点XX，其中XX为站点的id
 
--删除附件
delete from WCMAppendix where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and wcmdocument.DocId=WCMAppendix.AppDocId);
commit;
 
--删除相关文档
ALTER TABLE WCMRelation NOLOGGING;
delete from WCMRelation where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and (WCMRelation.DocId=WCMDocument.DocId or WCMRelation.RelDocId=WCMDocument.DocId));
commit;
 
--删除文档的关键词
ALTER TABLE WCMDocKeyword NOLOGGING;
delete from WCMDocKeyword where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and WCMDocKeyword.DocId=WCMDocument.DocId);
commit;
 
--删除文档工作流流转信息（WCMFlowDoc，WCMFlowDocBak）
ALTER TABLE WCMFlowDoc NOLOGGING;
delete WCMFlowDoc where
exists(
select DocId from WCMDocument where WCMFlowDoc.ObjId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)) );
commit;
 
ALTER TABLE WCMFlowDocBak NOLOGGING;
delete WCMFlowDocBak where
exists(
select DocId from WCMDocument where WCMFlowDocBak.ObjId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)) );
commit;
 
--删除文档备份信息（WCMDOCBAK）
ALTER TABLE WCMDOCBAK NOLOGGING;
delete WCMDOCBAK where
exists(
select DocId from WCMDocument where WCMDOCBAK.DocId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)));
commit;
 
--删除文档实体
ALTER TABLE wcmdocument NOLOGGING;
delete from wcmdocument where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx));
commit;
 
--删除文档引用
ALTER TABLE wcmchnldoc NOLOGGING;
delete from WCMCHNLDOC where CHNLID in(select CHANNELID from WCMWEBSITE where SITEID in (xx));
commit;
 
--删除栏目
ALTER TABLE wcmchannel NOLOGGING;
delete from wcmchannel where siteid in(xx);
commit;
 
--删除站点
ALTER TABLE wcmwebsite NOLOGGING;
delete from wcmwebsite where siteid in(XX);
commit;

3.WCMChnlDoc,WCMDocument表SiteId为空的历史数据处理

siteid为空可能是历史数据导致，因为后面在添加一篇文档的时候，都会给siteid赋值；
 
关于历史的siteid为空的问题，请执行以下SQL：
WCMChnlDoc表siteid为空的处理：
update wcmchnldoc set siteid = (select siteid from wcmchannel where wcmchnldoc.chnlid= wcmchannel.channelid)
where exists(select siteid from wcmchannel where wcmchnldoc.chnlid= wcmchannel.channelid);
 

WCMDOCUMENT表siteid为空的处理：
update WCMDOCUMENT set SITEID=(select SITEID from WCMCHANNEL where channelid = WCMDOCUMENT.DOCCHANNEL)
where exists(select siteid from wcmchannel where WCMDOCUMENT.DOCCHANNEL= wcmchannel.channelid);

4.对指定的表进行归档

4.1按照年份生成归档表

说明：归档表按照年份作为表的后缀名称，不同年份将产生不同的归档表，如：WCMDocument2012；同时归档表的前缀名称也可以指定，如：MyWCMDocument2012

添加了一个对Oracle数据库中的表进行归档的存储过程
  
--对指定的表“STABLENAME”进行归档，在表“STABLENAME”中仅保留前“NPAGESIZE”个月的数据
--历史数据则保留在归档表中
--归档时，以时间字段“SFIELDNAME”作为时间划分的标准
--归档后，以“SARCHIVETABLENAMEPRE”作为产生的归档表名的前缀，后面跟着年份值
--如果表名前缀为空串""，则使用“STABLENAME”作为前缀，如归档后表名：WCMDOCUMENT2009
CREATE OR REPLACE PROCEDURE TRS_P_TABLE_ARCHIVE(
 STABLENAME IN VARCHAR2, --需要进行归档的表名
 SFIELDNAME IN VARCHAR2, --对表进行归档时，参考的时间类型的字段名称
 NPAGESIZE IN NUMBER, --对前多少个月的数据进行归档
 SARCHIVETABLENAMEPRE IN VARCHAR2 --历史数据存放的归档后的表名前缀
)
AS
 --当前待处理数据所在的年份(YYYY)
 SDATAYEAR VARCHAR2(10);
  
 --当前待处理数据所在的日期(YYYY-MM)
 PREVIOUSMONTH VARCHAR2(20);
  
 --产生的新的归档表名称
 SARCHIVETABLENAME VARCHAR2(30);
  
 --临时变量，用于判断归档表是否存在
 NTABLECOUNT NUMBER;
BEGIN
  
 --获取当前待处理的数据的年份信息
 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -NPAGESIZE), 'YYYY') INTO SDATAYEAR FROM DUAL;
  
 --初始化归档表前缀信息，没有传入，则取原始表名称
 SARCHIVETABLENAME := SARCHIVETABLENAMEPRE;
 IF SARCHIVETABLENAME IS NULL THEN
 SARCHIVETABLENAME := STABLENAME;
 END IF;
  
 --构造归档后的表名(表前缀加上年份)
 SARCHIVETABLENAME := SARCHIVETABLENAME || SDATAYEAR;
  
 --判断当前处理的归档表是否存在，不存在则创建
 SELECT COUNT(*) INTO NTABLECOUNT FROM USER_TABLES WHERE TABLE_NAME = UPPER(SARCHIVETABLENAME);
 IF NTABLECOUNT <= 0 THEN
 EXECUTE IMMEDIATE 'CREATE TABLE ' || SARCHIVETABLENAME || ' AS SELECT * FROM ' || STABLENAME || ' WHERE 0 != 0';
 END IF;
  
 --初始化月份值
 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -NPAGESIZE), 'YYYY-MM') INTO PREVIOUSMONTH FROM DUAL;
  
 --在备份表中插入数据
 EXECUTE IMMEDIATE 'INSERT INTO ' || SARCHIVETABLENAME || ' SELECT * FROM ' || STABLENAME || ' WHERE TO_CHAR(' || SFIELDNAME || ', ''YYYY-MM'') = ''' || PREVIOUSMONTH || '''';
 COMMIT;
  
 --删除原表中的数据
 EXECUTE IMMEDIATE 'DELETE FROM ' || STABLENAME || ' WHERE TO_CHAR(' || SFIELDNAME || ', ''YYYY-MM'') = ''' || PREVIOUSMONTH || '''';
 COMMIT;
  
 --对原表进行统计分析优化
 DBMS_STATS.GATHER_TABLE_STATS (
 ownname => 'TRSWCM65CNR',
 tabname => STABLENAME,
 estimate_percent => 100
 );
END;
/
  
  
使用方式：
如：添加一个定时任务，对表tt中的数据，每一个月第一天凌晨12点进行数据归档
  
VARIABLE jobno number;
begin
dbms_job.submit(:jobno, 'TRS_P_TABLE_ARCHIVE(''tt'',''tdate'',2,'''' );', SYSDATE, 'TRUNC(LAST_DAY(SYSDATE) + 1)');
commit;
end;
/

4.2仅生产唯一归档表

说明：对每一张需要归档的表，系统只会产生一张归档表，归档表的名称可以在调用存储过程时指定，也可以采用默认值【原始表明】+【ARCHIVE】

添加了一个对Oracle数据库中的表进行归档的存储过程
  
--对指定的表“STABLENAME”进行归档，在表“STABLENAME”中仅保留前“NPAGESIZE”个月的数据
--历史数据则保留在归档表中
--归档时，以时间字段“SFIELDNAME”作为时间划分的标准
--归档后，以“SARCHIVETABLENAME”作为产生的归档表
--如果归档表为空串""，则使用“STABLENAME”+ARCHIVE作为前缀，如归档后表名：WCMDOCUMENTARCHIVE
CREATE OR REPLACE PROCEDURE TRS_P_TABLE_ARCHIVE2(
 STABLENAME IN VARCHAR2, --需要进行归档的表名
 SFIELDNAME IN VARCHAR2, --对表进行归档时，参考的时间类型的字段名称
 NPAGESIZE IN NUMBER, --对前多少个月的数据进行归档
 SARCHIVETABLENAME0 IN VARCHAR2 --历史数据存放的归档后的表名前缀
)
AS
 --当前待处理数据所在的年份(YYYY)
 SDATAYEAR VARCHAR2(10);
  
 --当前待处理数据所在的日期(YYYY-MM)
 PREVIOUSMONTH VARCHAR2(20);
 
 --产生的新的归档表名称
 SARCHIVETABLENAME VARCHAR2(30); 
  
 --临时变量，用于判断归档表是否存在
 NTABLECOUNT NUMBER;
BEGIN
  
 --获取当前待处理的数据的年份信息
 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -NPAGESIZE), 'YYYY') INTO SDATAYEAR FROM DUAL;
  
 --初始化归档，没有传入，则取原始表名称+ARCHIVE
 SARCHIVETABLENAME := SARCHIVETABLENAME0;
 IF SARCHIVETABLENAME IS NULL THEN
 SARCHIVETABLENAME := STABLENAME || 'ARCHIVE';
 END IF;
  
  
 --判断当前处理的归档表是否存在，不存在则创建
 SELECT COUNT(*) INTO NTABLECOUNT FROM USER_TABLES WHERE TABLE_NAME = UPPER(SARCHIVETABLENAME);
 IF NTABLECOUNT <= 0 THEN
 EXECUTE IMMEDIATE 'CREATE TABLE ' || SARCHIVETABLENAME || ' AS SELECT * FROM ' || STABLENAME || ' WHERE 0 != 0';
 END IF;
  
 --初始化月份值
 SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -NPAGESIZE), 'YYYY-MM') INTO PREVIOUSMONTH FROM DUAL;
  
 --在备份表中插入数据
 EXECUTE IMMEDIATE 'INSERT INTO ' || SARCHIVETABLENAME || ' SELECT * FROM ' || STABLENAME || ' WHERE TO_CHAR(' || SFIELDNAME || ', ''YYYY-MM'') = ''' || PREVIOUSMONTH || '''';
 COMMIT;
  
 --删除原表中的数据
 EXECUTE IMMEDIATE 'DELETE FROM ' || STABLENAME || ' WHERE TO_CHAR(' || SFIELDNAME || ', ''YYYY-MM'') = ''' || PREVIOUSMONTH || '''';
 COMMIT;
  
 --对原表进行统计分析优化
 DBMS_STATS.GATHER_TABLE_STATS (
 ownname => 'TRSWCM65CNR',
 tabname => STABLENAME,
 estimate_percent => 100
 );
END;
/
  
  
使用方式：
如：添加一个定时任务，对表tt中的数据，每一个月第一天凌晨12点进行数据归档
  
VARIABLE jobno number;
begin
dbms_job.submit(:jobno, 'TRS_P_TABLE_ARCHIVE2(''tt'',''tdate'',2,'''' );', SYSDATE, 'TRUNC(LAST_DAY(SYSDATE) + 1)');
commit;
end;
/

4.3WCMDocument及WCMChnlDoc的归档示例

对于WCMDocument及WCMChnlDoc的归档，我们采用4.2章节的介绍，都归档到一张固定的表中，如：WCMDocumentArchive，WCMChnlDocArchive。

但WCMChnlDoc表有些特殊，因为存在如下情况：

假定对WCMDocument、WCMChnlDoc表中12个月前的数据进行归档，WCMChnlDoc表中存在一条引用记录A，该引用创建时间在12个月以内，但它的实体文档已经被归档到了备份表，因为实体在12个月之前。

对于这种场景，我们需要将引用A也转移至归档表中。

--定义一个存储过程，将不存在实体的引用文档都转移到归档表
CREATE OR REPLACE PROCEDURE TRS_P_SYNCHNLDOC(
 SWCMCHNLDOCARCHIVENAME IN VARCHAR2 --WCMCHNLDOC归档后的表名
)
  
BEGIN
--初始化归档，没有传入，则取原始表名称+ARCHIVE
IF SWCMCHNLDOCARCHIVENAME IS NULL THEN
 SWCMCHNLDOCARCHIVENAME := 'WCMCHNLDOCARCHIVE';
END IF;
  
--将不存在实体的引用文档都转移到归档表
INSERT INTO WCMCHNLDOCARCHIVE SELECT * FROM WCMCHNLDOC WHERE NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID = WCMCHNLDOC.DOCID);
 
--将不存在实体的引用文档删除
DELETE FROM WCMCHNLDOC WHERE NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID = WCMCHNLDOC.DOCID); 
END;
/
 
使用方式：
添加一个定时任务，对表WCMDocumnent、WCMChnlDoc中的数据进行归档、同时将无实体的引用归档；
每一个月第一天凌晨12点进行数据归档
VARIABLE jobno number;
begin
dbms_job.submit(:jobno, 'TRS_P_TABLE_ARCHIVE2(''WCMDocument'',''CrTime'',12,'''' );TRS_P_TABLE_ARCHIVE2(''WCMChnlDoc'',''CrTime'',12,'''' );TRS_P_SYNCHNLDOC('''');', SYSDATE, 'TRUNC(LAST_DAY(SYSDATE) + 1)');
commit;
end;
/ 
注意：需要按照顺序，先归档WCMDocument，再归档WCMChnldoc,再归档无实体的引用。

5 我的工作列表已处理页面分页信息错乱问题

//删除彻底删除文档，还存在的FLOWDOC
DELETE FROM WCMFLOWDOC WHERE OBJID NOT IN(
 SELECT DOCID FROM WCMDOCUMENT WHERE ObjType = 605);
  
//设置已标记为删除的文档，流转轨迹标记为已删除
UPDATE WCMFLOWDOC SET ISOBJDELETED = 1 WHERE OBJID IN(
 SELECT DOCID FROM WCMDOCUMENT WHERE DOCSTATUS < 0 AND ObjType = 605);

6 垃圾或错误数据查找
Edit
由于某些原因，数据库中的数据可能不正确，以下SQL可以查找出一些不正确的数据，如果查询后结果集存在数据，则这些数据在某种程度上有问题。

SELECT * FROM WCMAPPENDIX WHERE NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID = WCMAPPENDIX.APPDOCID);
  
SELECT * FROM WCMCHANNEL WHERE NOT EXISTS(SELECT 1 FROM WCMWEBSITE WHERE WCMWEBSITE.SITEID = WCMCHANNEL.SITEID);
  
SELECT * FROM WCMCHANNELCONTENTLINK WHERE NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID = WCMCHANNELCONTENTLINK.CHANNELID);
  
SELECT * FROM WCMCHANNELSYN WHERE NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID=WCMCHANNELSYN.SRCCHANNEL OR WCMCHANNEL.CHANNELID=WCMCHANNELSYN.TOCHANNEL);
  
SELECT * FROM WCMCHNLDOC WHERE NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID=WCMCHNLDOC.DOCID);
  
SELECT CHNLID, DOCID, COUNT(*) FROM WCMCHNLDOC GROUP BY CHNLID, DOCID HAVING COUNT(*) >= 2;
  
SELECT * FROM WCMCHNLDOC WHERE NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID = WCMCHNLDOC.CHNLID OR WCMCHANNEL.CHANNELID=-WCMCHNLDOC.CHNLID);
  
SELECT * FROM WCMDOCUMENT WHERE NOT EXISTS(SELECT 1 FROM WCMCHNLDOC WHERE WCMDOCUMENT.DOCID=WCMCHNLDOC.DOCID);
  
SELECT * FROM WCMDOCUMENT WHERE NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID = WCMDOCUMENT.DOCCHANNEL OR WCMCHANNEL.CHANNELID=-WCMDOCUMENT.DOCCHANNEL);
  
--扩展字段相关垃圾数据
SELECT * FROM WCMCONTENTEXTFIELD WHERE NOT EXISTS(SELECT 1 FROM WCMEXTFIELD WHERE WCMEXTFIELD.EXTFIELDID=WCMCONTENTEXTFIELD.EXTFIELDID);
  
SELECT * FROM WCMDOCKEYWORD WHERE NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID = WCMDOCKEYWORD.DOCID);
  
SELECT * FROM WCMFLOWDOC WHERE OBJTYPE=605 AND NOT EXISTS(SELECT 1 FROM WCMDOCUMENT WHERE WCMDOCUMENT.DOCID=WCMFLOWDOC.OBJID);
  
SELECT EMPLOYERTYPE, EMPLOYERID, COUNT(*) FROM WCMFLOWEMPLOY GROUP BY EMPLOYERTYPE,EMPLOYERID HAVING COUNT(*) >= 2;
  
SELECT * FROM WCMFLOWEMPLOY WHERE EMPLOYERTYPE=101 AND NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID = WCMFLOWEMPLOY.EMPLOYERID);
  
SELECT * FROM WCMGRPUSER WHERE NOT EXISTS(SELECT 1 FROM WCMUSER WHERE WCMUSER.USERID = WCMGRPUSER.USERID);
  
SELECT * FROM WCMGRPUSER WHERE NOT EXISTS(SELECT 1 FROM WCMGROUP WHERE WCMGROUP.GROUPID = WCMGRPUSER.GROUPID);
  
SELECT * FROM WCMGRPROLE WHERE NOT EXISTS(SELECT 1 FROM WCMROLE WHERE WCMROLE.ROLEID = WCMGRPROLE.ROLEID);
  
SELECT * FROM WCMGRPROLE WHERE NOT EXISTS(SELECT 1 FROM WCMGROUP WHERE WCMGROUP.GROUPID = WCMGRPROLE.GROUPID);
  
SELECT * FROM WCMROLEUSER WHERE NOT EXISTS(SELECT 1 FROM WCMUSER WHERE WCMUSER.USERID = WCMROLEUSER.USERID);
  
SELECT * FROM WCMROLEUSER WHERE NOT EXISTS(SELECT 1 FROM WCMROLE WHERE WCMROLE.ROLEID = WCMROLEUSER.ROLEID);
 
--模板相关的垃圾数据 
----该模板在栏目上创建，但不存在所属栏目的模板
  
SELECT * FROM WCMTEMPLATE WHERE FOLDERTYPE=101 AND NOT EXISTS(SELECT 1 FROM WCMCHANNEL WHERE WCMCHANNEL.CHANNELID = WCMTEMPLATE.FOLDERID);
  
----该模板在站点上创建，但不存在所属站点的模板
 
SELECT * FROM WCMTEMPLATE WHERE FOLDERTYPE=103 AND NOT EXISTS(SELECT 1 FROM WCMWEBSITE WHERE WCMWEBSITE.SITEID = WCMTEMPLATE.ROOTID);
  
----按照模板所属的站点和栏目，显示首页模板超过2个的站点或栏目，理论上一个站点或栏目只能有一个首页模板
 
SELECT EMPLOYERTYPE, EMPLOYERID, TEMPLATETYPE, COUNT(*) FROM WCMTEMPLATEEMPLOY WHERE ISDEFAULT=1 GROUP BY EMPLOYERTYPE, EMPLOYERID, TEMPLATETYPE HAVING COUNT(*) >= 2;
 
--元数据垃圾数据，其中XXX换成真实的元数据英文名称
select * from wcmmetatableXXX where not exists(select 1 from wcmdocument where wcmdocument.docid=wcmmetatableXXX.metadataid);
select * from wcmmetatableXXX where not exists(select 1 from wcmchnldoc where wcmchnldoc.docid=wcmmetatableXXX.metadataid);  

7.WCM52升级后文档列表没有数据

将WCM52升级到WCM65，升级后，进入栏目文档列表，发现文档列表没有数据，但数据库中WCMChnlDoc和WCMDocument表中的确存在文档，出现这种情况的原因是：WCMChnlDoc的docChannel字段为空导致；

可以通过在数据库中执行以下SQL进行修复：

update wcmchnldoc set docchannel= (select docchannel from wcmdocument where wcmchnldoc.docid= wcmdocument.docid) where exists(select docchannel from wcmdocument where wcmchnldoc.docid= wcmdocument.docid);

8.将指定站点已发文档变成新稿

将里面的？换成真实的siteid

UPDATE WCMCHNLDOC SET DOCSTATUS=1 WHERE DOCSTATUS=10 AND CHNLID IN (SELECT CHANNELID FROM WCMCHANNEL WHERE SITEID=?);
UPDATE WCMDOCUMENT SET DOCSTATUS=1 WHERE DOCSTATUS=10 AND DOCCHANNEL IN (SELECT CHANNELID FROM WCMCHANNEL WHERE SITEID=?);

应客户要求，统计一个时间段内某几个站点的发稿量。
 

样本，ORACLE11G：

select(SELECT WCMWEBSITE.SITENAME FROM WCMWEBSITE whereWCMDOCUMENT.SITEID=WCMWEBSITE.SITEID) as zhandian,DOCTITLE as biaoti,(SELECTchnlname FROM wcmchannel where WCMDOCUMENT.DOCCHANNEL=wcmchannel.channelid) aslanmuming,WCMDOCUMENT.DOCSOURCENAME as laiyuan,WCMDOCUMENT.DOCRELTIME asshijian,WCMDOCUMENT.OPERUSER as faburen from WCMDOCUMENT where SITEID in (1,20)and DOCSTATUS=10 AND CRTIME-to_date('2017-06-01 00:00:00','yyyy-mm-ddhh24:mi:ss')>0 AND CRTIME-to_date('2017-06-20 00:00:00','yyyy-mm-ddhh24:mi:ss')<0

批量修改某些文档状态
自定义表单栏目1375中，回复内容不为空以及用户选择允许公开发布的信息，批量设置为已签。

设置之后还需要人工校验。因判断回复内容有误差

表[TRSWCMV7].[dbo].[WCMCHNLDOC]  [TRSWCMV7].[dbo].[WCMDOCUMENT]

已签

[TRSWCMV7].[dbo].[WCMCHNLDOC].docstatus=16

[TRSWCMV7].[dbo].[WCMDOCUMENT].docstatus=16

频道

[TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCHANNEL=1375

[TRSWCMV7].[dbo].[WCMCHNLDOC].chnlid=1375

条件 [TRSWCMV7].[dbo].[WCMDOCUMENT]DOCCHANNEL=1375 and[TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCONTENT like '%<my:回复内容>%'and DOCCONTENT like  '%Yes%'

[TRSWCMV7].[dbo].[WCMDOCUMENT].docid=[TRSWCMV7].[dbo].[WCMCHNLDOC].docid

修改 [TRSWCMV7].[dbo].[WCMCHNLDOC].docstatus=16 与[TRSWCMV7].[dbo].[WCMDOCUMENT].docstatus=16

最后：

生成检索语句

select * from  [TRSWCMV7].[dbo].[WCMCHNLDOC]

 where [TRSWCMV7].[dbo].[WCMCHNLDOC].docid in (select docid from[TRSWCMV7].[dbo].[WCMDOCUMENT] where[TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCHANNEL=1375 and [TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCONTENTlike '%<my:回复内容>%' and DOCCONTENT like '%Yes%')

生成更改数据语句

update [TRSWCMV7].[dbo].[WCMCHNLDOC] set[TRSWCMV7].[dbo].[WCMCHNLDOC].DOCSTATUS=16

where[TRSWCMV7].[dbo].[WCMCHNLDOC].chnlid=1375 and [TRSWCMV7].[dbo].[WCMCHNLDOC].docidin

(select docid from[TRSWCMV7].[dbo].[WCMDOCUMENT] where[TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCHANNEL=1375 and[TRSWCMV7].[dbo].[WCMDOCUMENT].DOCCONTENT like '%<my:回复内容>%'and DOCCONTENT like  '%Yes%')

数据库内删除不能在wcm里删除的文档
 

站点4废稿箱中删除文档，部分文档报错，废稿箱中文档标题消失，无法删除无法清空废稿箱，检查数据库发现文档对应的记录在WCMDOCUMENT中已经删除但是在WCMCHNLDOC里还存在，使用如下语句可以得到数据差异列表。

select * from[TRSWCMV7].[dbo].[WCMCHNLDOC]  wheredocid not in (select DOCID from [TRSWCMV7].[dbo].[WCMDOCUMENT] whereDOCSTATUS=-10) and DOCSTATUS=-10 and SITEID=4 order by DOCID

查看WCMDOCUMENT表语句，可人工查看差异

select * from[TRSWCMV7].[dbo].[WCMDOCUMENT]  whereDOCSTATUS=-10 and SITEID=4

 order by DOCID 

删除数据语句（谨慎操作，未证实安全，注意备份）

delete from[TRSWCMV7].[dbo].[WCMCHNLDOC]  wheredocid not in (select DOCID from [TRSWCMV7].[dbo].[WCMDOCUMENT] whereDOCSTATUS=-10) and DOCSTATUS=-10 and SITEID=4 

wcm发布置标中如何知道该文档是否有附件
 

exists(select APPDOCID from WCMAppendixwhere AppFlag=10 and WCMAppendix.AppDocId=WCMChnlDoc.DocId)

AppFlag=10 文件附件； AppFlag=20 图片附件； AppFlag=40 链接附件

数据库迁移文档、频道、站点数据后，批量更改旧ip到新ip：

update  wcmdocument  set doclink=replace(doclink,'156.9.12.9','156.9.18.126') where doclink like 'http://156.9.12.9%';

update  wcmdocument  set docpuburl=replace(docpuburl,'156.9.12.9','156.9.18.126') where docpuburl like 'http://156.9.12.9%';

update  wcmchnldoc  set docpuburl=replace(docpuburl,'156.9.12.9','156.9.18.126') where docpuburl like 'http://156.9.12.9%';

update  WCMFOLDERPUBLISHCONFIG  set rootdomain=replace(rootdomain,'156.9.12.9','156.9.18.126') where foldertype=103 and rootdomain like 'http://156.9.12.9%';

操作数据表实现分发站点的停用及批量新建分发站点

需求：
1、停用WCM现有的所有分发点
2、每个站点增加新的分发点

不能在页面上操作的原因：
由于网站群的站点太多，而且切换时间必须断，在页面上操作工作量太大，考虑直接修改数据表来实现，需要提供SQL语句。

实现方法
1、禁用所有分发站点

sql语句：update WCMPUBLISHDISTRIBUTION set ENABLED=0;

2、每个站点建立一个分发站点

下面sql语句中，红色的部分为本地分发站点的存储目录，相当于在WCM中新建分发站点时要填的存储路径。
蓝字‘FILE’表示新建站点里面的类型。
QQ截图20121112162339.png

insert into WCMPUBLISHDISTRIBUTION(PUBLISHDISTRIBUTIONID, FOLDERTYPE, FOLDERID, TARGETTYPE, TARGETSERVER, LOGINUSER, LOGINPASSWORD, DATAPATH, ENABLED, CRUSER, CRTIME, ANONYMFTP, TARGETPORT, PASSIVEMODE) select SITEID+(select max(PUBLISHDISTRIBUTIONID)+1 from WCMPUBLISHDISTRIBUTION),103,siteid,'FILE','','','','e:\',1,CRUSER,CRTIME,0,null,1 from WCMWEBSITE;

3、其它必要操作

更新WCMID表：
update WCMID set NEXTID=0 where TABLENAME='WCMPUBLISHDISTRIBUTION';

4、重启WCM

因为涉及到数据库的更新，所以要重启一下WCM。

    注意
上面第2步中的SQL语句适用于没有建过分发站点的系统，也就是说，WCMPUBLISHDISTRIBUTION表中之前必须有数据。如果系统没有建过分发站点，WCMPUBLISHDISTRIBUTION表中无数据，那么第2步中的语句会报错，需要改成如下语句：
insert into WCMPUBLISHDISTRIBUTION(PUBLISHDISTRIBUTIONID, FOLDERTYPE, FOLDERID, TARGETTYPE, TARGETSERVER, LOGINUSER, LOGINPASSWORD, DATAPATH, ENABLED, CRUSER, CRTIME, ANONYMFTP, TARGETPORT, PASSIVEMODE) select SITEID,103,siteid,'FILE','','','','e:\',1,CRUSER,CRTIME,0,null,1 from WCMWEBSITE;