TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/如增量迁移需运行SQL/3_sp_data_migration_wcmdocument.sql

DROP PROCEDURE IF EXISTS sp_data_migration_increment_wcmdocument;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_increment_wcmdocument(IN prevDocId BIGINT(20),
                                              IN docIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))

	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmdocument';
    DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE f_DOCID int(11) DEFAULT 0;
    DECLARE f_DOCCHANNEL int(11) DEFAULT 0;
    DECLARE f_DOCVERSION int(11) DEFAULT 0;
    DECLARE f_DOCTYPE int(11) DEFAULT 0;
    DECLARE f_DOCTITLE varchar(1000) DEFAULT 0;
    DECLARE f_ShortTitle varchar(400) DEFAULT NULL;
    DECLARE f_DOCSOURCE int(11) DEFAULT 0;
    DECLARE f_DOCSECURITY int(11) DEFAULT 0;
    DECLARE f_DOCSTATUS int(11) DEFAULT 0;
    DECLARE f_DOCKIND int(11) DEFAULT NULL;
    DECLARE f_DOCCONTENT longtext;
    DECLARE f_DOCHTMLCON longtext;
    DECLARE f_DOCABSTRACT varchar(2000) DEFAULT NULL;
    DECLARE f_DOCKEYWORDS varchar(200) DEFAULT NULL;
    DECLARE f_DOCRELWORDS varchar(200) DEFAULT NULL;
    DECLARE f_DOCPEOPLE varchar(200) DEFAULT NULL;
    DECLARE f_DOCPLACE varchar(200) DEFAULT NULL;
    DECLARE f_DOCAUTHOR varchar(200) DEFAULT NULL;
    DECLARE f_DOCEDITOR varchar(50) DEFAULT NULL;
    DECLARE f_DOCAUDITOR varchar(50) DEFAULT NULL;
    DECLARE f_DOCOUTUPID int(11) DEFAULT NULL;
    DECLARE f_DOCVALID datetime DEFAULT NULL;
    DECLARE f_DOCPUBURL varchar(400) DEFAULT NULL;
    DECLARE f_DOCPUBTIME datetime DEFAULT NULL;
    DECLARE f_DOCRELTIME datetime DEFAULT NULL;
    DECLARE f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;
    DECLARE f_DOCWORDSCOUNT int(11) DEFAULT 0;
    DECLARE f_DOCPRO int(11) DEFAULT 1;
    DECLARE f_RIGHTDEFINED smallint(6) DEFAULT 0;
    DECLARE f_TITLECOLOR varchar(20) DEFAULT NULL;
    DECLARE f_TEMPLATEID int(11) DEFAULT 0;
    DECLARE f_SCHEDULE int(11) DEFAULT 0;
    DECLARE f_DOCNO varchar(20) DEFAULT NULL;
    DECLARE f_DOCFLAG int(11) DEFAULT 0;
    DECLARE f_EDITOR varchar(100) DEFAULT NULL;
    DECLARE f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE f_HITSCOUNT int(11) DEFAULT 0;
    DECLARE f_DOCPUBHTMLCON longtext ;
    DECLARE f_SUBDOCTITLE varchar(500) DEFAULT NULL;
    DECLARE f_ATTACHPIC smallint(6) DEFAULT NULL;
    DECLARE f_DOCLINK varchar(500) DEFAULT NULL;
    DECLARE f_DOCFILENAME varchar(50) DEFAULT NULL;
    DECLARE f_DOCFROMVERSION int(11) DEFAULT NULL;
    DECLARE f_OPERTIME datetime DEFAULT NULL;
    DECLARE f_OPERUSER varchar(50) DEFAULT NULL;
    DECLARE f_FLOWOPERATIONMARK varchar(20) DEFAULT NULL;
    DECLARE f_FLOWPREOPERATIONMARK varchar(20) DEFAULT NULL;
    DECLARE f_FLOWOPERATIONMASKENUM varchar(20) DEFAULT NULL;
    DECLARE f_DOCSOURCENAME varchar(100) DEFAULT NULL;
    DECLARE f_DOCLINKTO varchar(300) DEFAULT NULL;
    DECLARE f_DOCMIRRORTO varchar(300) DEFAULT NULL;
    DECLARE f_RANDOMSERIAL varchar(100) DEFAULT NULL;
    DECLARE f_POSTUSER varchar(50) DEFAULT NULL;
    DECLARE f_ISPAGEIMG tinyint(4) DEFAULT NULL;
    DECLARE f_PUBLISHDATE varchar(50) DEFAULT NULL;
    DECLARE f_PAGENUM varchar(20) DEFAULT NULL;
    DECLARE f_PAGENAME varchar(150) DEFAULT NULL;
    DECLARE f_PDFFILENAME varchar(150) DEFAULT NULL;
    DECLARE f_PAGEIMAGEFILENAME varchar(50) DEFAULT NULL;
    DECLARE f_MAP varchar(500) DEFAULT NULL;
    DECLARE f_YINTI varchar(200) DEFAULT NULL;
    DECLARE f_SITEID int(11) DEFAULT 0;
    DECLARE f_SRCSITEID int(11) DEFAULT 0;
    DECLARE f_DOCFIRSTPUBTIME datetime DEFAULT NULL;
    DECLARE f_NODEID int(11) DEFAULT 0;
    DECLARE f_ORDERID int(11) DEFAULT 0;
    DECLARE f_CRDEPT varchar(200) DEFAULT NULL;
    DECLARE f_DOCFORM int(11) DEFAULT 0;
    DECLARE f_DOCLEVEL int(11) DEFAULT NULL;
    DECLARE f_olddocpuburl varchar(400) DEFAULT NULL;
    DECLARE f_AttachVideo smallint(6) DEFAULT 0;
    DECLARE f_AttachAudio smallint(6) DEFAULT 0;
    DECLARE f_ThumbFiles varchar(2000) DEFAULT NULL;
    DECLARE f_commentFlag tinyint(4) DEFAULT 0;
    DECLARE f_readingMoodFlag tinyint(4) DEFAULT 0;
    DECLARE f_FromType int(11) DEFAULT 0 ;
    DECLARE f_FromId int(11) DEFAULT 0 ;
    DECLARE f_OriginDocId int(11) DEFAULT 0 ;





  DECLARE cur_doc CURSOR FOR
		SELECT
      DOCID,
      DOCCHANNEL,
      DOCVERSION,
      DOCTYPE,
      DOCTITLE,
      NULL,
      DOCSOURCE,
      DOCSECURITY,
      DOCSTATUS,
      DOCKIND,
      DOCCONTENT,
      DOCHTMLCON,
      DOCABSTRACT,
      DOCKEYWORDS,
      DOCRELWORDS,
      DOCPEOPLE,
      DOCPLACE,
      DOCAUTHOR,
      DOCEDITOR,
      DOCAUDITOR,
      DOCOUTUPID,
      DOCVALID,
      DOCPUBURL,
      DOCPUBTIME,
      DOCRELTIME,
      CRUSER,
      CRTIME,
      DOCWORDSCOUNT,
      DOCPRO,
      RIGHTDEFINED,
      TITLECOLOR,
      TEMPLATEID,
      SCHEDULE,
      DOCNO,
      DOCFLAG,
      EDITOR,
      ATTRIBUTE,
      HITSCOUNT,
      DOCPUBHTMLCON,
      SUBDOCTITLE,
      ATTACHPIC,
      DOCLINK,
      DOCFILENAME,
      DOCFROMVERSION,
      OPERTIME,
      OPERUSER,
      FLOWOPERATIONMARK,
      FLOWPREOPERATIONMARK,
      FLOWOPERATIONMASKENUM,
      DOCSOURCENAME,
      DOCLINKTO,
      DOCMIRRORTO,
      RANDOMSERIAL,
      POSTUSER,
      ISPAGEIMG,
      PUBLISHDATE,
      PAGENUM,
      PAGENAME,
      PDFFILENAME,
      PAGEIMAGEFILENAME,
      MAP,
      YINTI,
      SITEID,
      SRCSITEID,
      DOCFIRSTPUBTIME,
      NODEID,
      ORDERID,
      CRDEPT,
      DOCFORM,
      DOCLEVEL,
      olddocpuburl,
      0,
      0,
      null,
      null,
      null,
      0,
      0,
      0
		FROM wcmdocument
		WHERE docid > prevDocId
		ORDER BY docid asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur_doc;
	
	FETCH cur_doc INTO
    f_DOCID,
    f_DOCCHANNEL,
    f_DOCVERSION,
    f_DOCTYPE,
    f_DOCTITLE,
    f_ShortTitle,
    f_DOCSOURCE,
    f_DOCSECURITY,
    f_DOCSTATUS,
    f_DOCKIND,
    f_DOCCONTENT,
    f_DOCHTMLCON,
    f_DOCABSTRACT,
    f_DOCKEYWORDS,
    f_DOCRELWORDS,
    f_DOCPEOPLE,
    f_DOCPLACE,
    f_DOCAUTHOR,
    f_DOCEDITOR,
    f_DOCAUDITOR,
    f_DOCOUTUPID,
    f_DOCVALID,
    f_DOCPUBURL,
    f_DOCPUBTIME,
    f_DOCRELTIME,
    f_CRUSER,
    f_CRTIME,
    f_DOCWORDSCOUNT,
    f_DOCPRO,
    f_RIGHTDEFINED,
    f_TITLECOLOR,
    f_TEMPLATEID,
    f_SCHEDULE,
    f_DOCNO,
    f_DOCFLAG,
    f_EDITOR,
    f_ATTRIBUTE,
    f_HITSCOUNT,
    f_DOCPUBHTMLCON,
    f_SUBDOCTITLE,
    f_ATTACHPIC,
    f_DOCLINK,
    f_DOCFILENAME,
    f_DOCFROMVERSION,
    f_OPERTIME,
    f_OPERUSER,
    f_FLOWOPERATIONMARK,
    f_FLOWPREOPERATIONMARK,
    f_FLOWOPERATIONMASKENUM,
    f_DOCSOURCENAME,
    f_DOCLINKTO,
    f_DOCMIRRORTO,
    f_RANDOMSERIAL,
    f_POSTUSER,
    f_ISPAGEIMG,
    f_PUBLISHDATE,
    f_PAGENUM,
    f_PAGENAME,
    f_PDFFILENAME,
    f_PAGEIMAGEFILENAME,
    f_MAP,
    f_YINTI,
    f_SITEID,
    f_SRCSITEID,
    f_DOCFIRSTPUBTIME,
    f_NODEID,
    f_ORDERID,
    f_CRDEPT,
    f_DOCFORM,
    f_DOCLEVEL,
    f_olddocpuburl,
    f_AttachVideo,
    f_AttachAudio,
    f_ThumbFiles,
    f_commentFlag,
    f_readingMoodFlag,
    f_FromType,
    f_FromId,
    f_OriginDocId
    ;


    select concat('开始迁移 ', migr_table_name) info;

    WHILE isover= 0 DO

      # 断点续传
      IF (f_DOCID > prevDocId)

        THEN
          # 迁移后的数据，ID增加某一特定的值
          # 文档ID
          SET @DOCID = f_DOCID;
          SET f_DOCID = f_DOCID + docIdOffset;
          #原文档id
          IF (f_OriginDocId > 0)
          THEN
            SET @doc_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = migr_table_name AND start_id < f_OriginDocId and  end_id >= f_OriginDocId), docIdOffset);
            SET f_OriginDocId = f_OriginDocId + @doc_offset_num;
          else
            SET f_OriginDocId = f_DOCID;
          END IF;
          # 栏目ID
          SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_DOCCHANNEL and  end_id >= f_DOCCHANNEL), channelIdOffset);
          SET f_DOCCHANNEL = f_DOCCHANNEL + @channel_offset_num;
          # 视图ID
          IF (f_DOCKIND > 0)
          THEN
            SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_DOCKIND and  end_id >= f_DOCKIND), viewIdOffset);
            SET f_DOCKIND = f_DOCKIND + @view_offset_num;
          END IF ;
          # 站点ID
          SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_SITEID and  end_id >= f_SITEID), siteIdOffset);
          SET f_SITEID = f_SITEID + @site_offset_num;
          IF (f_DOCTYPE = 1)
            THEN SET f_DOCTYPE = 20;
          END IF ;

          insert into trs_hycloud_iip.wcmdocument
            (
              DOCID,
              DOCCHANNEL,
              DOCVERSION,
              DOCTYPE,
              DOCTITLE,
              ShortTitle,
              DOCSOURCE,
              DOCSECURITY,
              DOCSTATUS,
              DOCKIND,
              DOCCONTENT,
              DOCHTMLCON,
              DOCABSTRACT,
              DOCKEYWORDS,
              DOCRELWORDS,
              DOCPEOPLE,
              DOCPLACE,
              DOCAUTHOR,
              DOCEDITOR,
              DOCAUDITOR,
              DOCOUTUPID,
              DOCVALID,
              DOCPUBURL,
              DOCPUBTIME,
              DOCRELTIME,
              CRUSER,
              CRTIME,
              DOCWORDSCOUNT,
              DOCPRO,
              RIGHTDEFINED,
              TITLECOLOR,
              TEMPLATEID,
              SCHEDULE,
              DOCNO,
              DOCFLAG,
              EDITOR,
              ATTRIBUTE,
              HITSCOUNT,
              DOCPUBHTMLCON,
              SUBDOCTITLE,
              ATTACHPIC,
              DOCLINK,
              DOCFILENAME,
              DOCFROMVERSION,
              OPERTIME,
              OPERUSER,
              FLOWOPERATIONMARK,
              FLOWPREOPERATIONMARK,
              FLOWOPERATIONMASKENUM,
              DOCSOURCENAME,
              DOCLINKTO,
              DOCMIRRORTO,
              RANDOMSERIAL,
              POSTUSER,
              ISPAGEIMG,
              PUBLISHDATE,
              PAGENUM,
              PAGENAME,
              PDFFILENAME,
              PAGEIMAGEFILENAME,
              MAP,
              YINTI,
              SITEID,
              SRCSITEID,
              DOCFIRSTPUBTIME,
              NODEID,
              ORDERID,
              CRDEPT,
              DOCFORM,
              DOCLEVEL,
              olddocpuburl,
              AttachVideo,
              AttachAudio,
              ThumbFiles,
              commentFlag,
              readingMoodFlag,
              FromType,
              FromId,
              OriginDocId
            )
            VALUES
              (
                f_DOCID,
                f_DOCCHANNEL,
                f_DOCVERSION,
                f_DOCTYPE,
                f_DOCTITLE,
                f_ShortTitle,
                f_DOCSOURCE,
                f_DOCSECURITY,
                f_DOCSTATUS,
                f_DOCKIND,
                f_DOCCONTENT,
                f_DOCHTMLCON,
                f_DOCABSTRACT,
                f_DOCKEYWORDS,
                f_DOCRELWORDS,
                f_DOCPEOPLE,
                f_DOCPLACE,
                f_DOCAUTHOR,
                f_DOCEDITOR,
                f_DOCAUDITOR,
                f_DOCOUTUPID,
                f_DOCVALID,
                f_DOCPUBURL,
                f_DOCPUBTIME,
                f_DOCRELTIME,
                f_CRUSER,
                f_CRTIME,
                f_DOCWORDSCOUNT,
                f_DOCPRO,
                f_RIGHTDEFINED,
                f_TITLECOLOR,
                f_TEMPLATEID,
                f_SCHEDULE,
                f_DOCNO,
                f_DOCFLAG,
                f_EDITOR,
                f_ATTRIBUTE,
                f_HITSCOUNT,
                f_DOCPUBHTMLCON,
                f_SUBDOCTITLE,
                f_ATTACHPIC,
                f_DOCLINK,
                f_DOCFILENAME,
                f_DOCFROMVERSION,
                f_OPERTIME,
                f_OPERUSER,
                f_FLOWOPERATIONMARK,
                f_FLOWPREOPERATIONMARK,
                f_FLOWOPERATIONMASKENUM,
                f_DOCSOURCENAME,
                f_DOCLINKTO,
                f_DOCMIRRORTO,
                f_RANDOMSERIAL,
                f_POSTUSER,
                f_ISPAGEIMG,
                f_PUBLISHDATE,
                f_PAGENUM,
                f_PAGENAME,
                f_PDFFILENAME,
                f_PAGEIMAGEFILENAME,
                f_MAP,
                f_YINTI,
                f_SITEID,
                f_SRCSITEID,
                f_DOCFIRSTPUBTIME,
                f_NODEID,
                f_ORDERID,
                f_CRDEPT,
                f_DOCFORM,
                f_DOCLEVEL,
                f_olddocpuburl,
                f_AttachVideo,
                f_AttachAudio,
                f_ThumbFiles,
                f_commentFlag,
                f_readingMoodFlag,
                f_FromType,
                f_FromId,
                f_OriginDocId
                );

          SET @log_sql = build_migration_log_sql(migr_table_name, @DOCID, now());

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, docIdOffset, prevDocId, @DOCID);
    END IF ;

		FETCH cur_doc INTO
      f_DOCID,
      f_DOCCHANNEL,
      f_DOCVERSION,
      f_DOCTYPE,
      f_DOCTITLE,
      f_ShortTitle,
      f_DOCSOURCE,
      f_DOCSECURITY,
      f_DOCSTATUS,
      f_DOCKIND,
      f_DOCCONTENT,
      f_DOCHTMLCON,
      f_DOCABSTRACT,
      f_DOCKEYWORDS,
      f_DOCRELWORDS,
      f_DOCPEOPLE,
      f_DOCPLACE,
      f_DOCAUTHOR,
      f_DOCEDITOR,
      f_DOCAUDITOR,
      f_DOCOUTUPID,
      f_DOCVALID,
      f_DOCPUBURL,
      f_DOCPUBTIME,
      f_DOCRELTIME,
      f_CRUSER,
      f_CRTIME,
      f_DOCWORDSCOUNT,
      f_DOCPRO,
      f_RIGHTDEFINED,
      f_TITLECOLOR,
      f_TEMPLATEID,
      f_SCHEDULE,
      f_DOCNO,
      f_DOCFLAG,
      f_EDITOR,
      f_ATTRIBUTE,
      f_HITSCOUNT,
      f_DOCPUBHTMLCON,
      f_SUBDOCTITLE,
      f_ATTACHPIC,
      f_DOCLINK,
      f_DOCFILENAME,
      f_DOCFROMVERSION,
      f_OPERTIME,
      f_OPERUSER,
      f_FLOWOPERATIONMARK,
      f_FLOWPREOPERATIONMARK,
      f_FLOWOPERATIONMASKENUM,
      f_DOCSOURCENAME,
      f_DOCLINKTO,
      f_DOCMIRRORTO,
      f_RANDOMSERIAL,
      f_POSTUSER,
      f_ISPAGEIMG,
      f_PUBLISHDATE,
      f_PAGENUM,
      f_PAGENAME,
      f_PDFFILENAME,
      f_PAGEIMAGEFILENAME,
      f_MAP,
      f_YINTI,
      f_SITEID,
      f_SRCSITEID,
      f_DOCFIRSTPUBTIME,
      f_NODEID,
      f_ORDERID,
      f_CRDEPT,
      f_DOCFORM,
      f_DOCLEVEL,
      f_olddocpuburl,
      f_AttachVideo,
      f_AttachAudio,
      f_ThumbFiles,
      f_commentFlag,
      f_readingMoodFlag,
      f_FromType,
      f_FromId,
      f_OriginDocId
      ;
		
	END WHILE;
	
	CLOSE cur_doc;

    select concat('完成迁移 ', migr_table_name) info;


  END $$
DELIMITER ;