TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/3_sp_data_migration_wcmchannel.sql

DROP PROCEDURE IF EXISTS sp_data_migration_wcmchannel;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmchannel(IN citySuffix VARCHAR(20),
                                              IN prevChnlId BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmchannel';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';

    DECLARE f_CHANNELID int DEFAULT 0;
    DECLARE f_SITEID int DEFAULT 0;
    DECLARE f_CHNLNAME varchar(200) DEFAULT 0;
    DECLARE f_CHNLDESC varchar(200) DEFAULT NULL;
    DECLARE f_CHNLTABLE varchar(200) DEFAULT NULL;
    DECLARE f_CHNLQUERY varchar(500) DEFAULT NULL;
    DECLARE f_CHNLPROP int DEFAULT NULL;
    DECLARE f_PARENTID int DEFAULT 0;
    DECLARE f_CHNLORDER int DEFAULT 0;
    DECLARE f_SCHEDULE int DEFAULT NULL;
    DECLARE f_CHNLDATAPATH varchar(200) DEFAULT NULL;
    DECLARE f_CHNLORDERBY varchar(200) DEFAULT NULL;
    DECLARE f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;
    DECLARE f_STATUS int DEFAULT 0;
    DECLARE f_CHNLTYPE int DEFAULT 0;
    DECLARE f_CHNLOUTLINETEMP int DEFAULT 0;
    DECLARE f_CHNLDETAILTEMP int DEFAULT 0;
    DECLARE f_PUBLISHPRO smallint(6) DEFAULT 1;
    DECLARE f_OPERUSER varchar(50) DEFAULT NULL;
    DECLARE f_OPERTIME datetime DEFAULT NULL;
    DECLARE f_LINKURL varchar(200) DEFAULT NULL;
    DECLARE f_CONTENTADDEDITPAGE varchar(200) DEFAULT NULL;
    DECLARE f_CONTENTLISTPAGE varchar(200) DEFAULT NULL;
    DECLARE f_CONTENTSHOWPAGE varchar(200) DEFAULT NULL;
    DECLARE f_OUTLINEFILEDS varchar(1000) DEFAULT NULL;
    DECLARE f_OUTLINEDBFIELDS varchar(1000) DEFAULT NULL;
    DECLARE f_ISCLUSTER smallint(6) DEFAULT 0;
    DECLARE f_SHOWFIELDS varchar(300) DEFAULT NULL;
    DECLARE f_FIELDSWIDTH varchar(200) DEFAULT NULL;
    DECLARE f_BASEPROPS varchar(200) DEFAULT NULL;
    DECLARE f_OTHERPROPS varchar(200) DEFAULT NULL;
    DECLARE f_ADVANCEPROPS varchar(200) DEFAULT NULL;
    DECLARE f_TOOLBAR longtext;
    DECLARE f_ADVTOOLBAR longtext;
    DECLARE f_ISCONTAINSCHILDREN int DEFAULT 0;
    DECLARE f_METASYNFIELDS varchar(500) DEFAULT NULL;
    DECLARE f_FLUENCEXML longtext;
    DECLARE f_USEDOCLEVEL smallint(6) DEFAULT NULL;
    DECLARE f_lastmodifytime datetime DEFAULT NULL;
    DECLARE f_VIEWPROPS varchar(300) DEFAULT NULL;
    DECLARE f_NEEDEDPROPS varchar(300) DEFAULT NULL;
    DECLARE f_INHERIT int DEFAULT 0;
    DECLARE f_IsSubscribe smallint(6) DEFAULT 0;
    DECLARE f_ISPUSHABLE tinyint(4) DEFAULT 0 ;
    DECLARE f_ISDISTRIBUTABLE tinyint(4) DEFAULT 0 ;
    DECLARE f_CHANNELLOGO varchar(200) DEFAULT NULL;
    DECLARE f_SEARCHWORDVALUE varchar(40) DEFAULT NULL;
    DECLARE f_CHNLDESCPINYIN varchar(254) DEFAULT NULL;


  DECLARE cur CURSOR FOR
		SELECT
      CHANNELID,
      SITEID,
      CHNLNAME,
      CHNLDESC,
      CHNLTABLE,
      CHNLQUERY,
      CHNLPROP,
      PARENTID,
      CHNLORDER,
      SCHEDULE,
      CHNLDATAPATH,
      CHNLORDERBY,
      ATTRIBUTE,
      CRUSER,
      CRTIME,
      STATUS,
      CHNLTYPE,
      CHNLOUTLINETEMP,
      CHNLDETAILTEMP,
      PUBLISHPRO,
      OPERUSER,
      OPERTIME,
      LINKURL,
      CONTENTADDEDITPAGE,
      CONTENTLISTPAGE,
      CONTENTSHOWPAGE,
      OUTLINEFILEDS,
      OUTLINEDBFIELDS,
      ISCLUSTER,
      SHOWFIELDS,
      FIELDSWIDTH,
      BASEPROPS,
      OTHERPROPS,
      ADVANCEPROPS,
      TOOLBAR,
      ADVTOOLBAR,
      ISCONTAINSCHILDREN,
      METASYNFIELDS,
      FLUENCEXML,
      USEDOCLEVEL,
      lastmodifytime,
      VIEWPROPS,
      NEEDEDPROPS,
      INHERIT,
      0,
      0,
      0,
      null,
      null,
      null
    FROM wcmchannel
		ORDER BY channelid asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CHANNELID,
    f_SITEID,
    f_CHNLNAME,
    f_CHNLDESC,
    f_CHNLTABLE,
    f_CHNLQUERY,
    f_CHNLPROP,
    f_PARENTID,
    f_CHNLORDER,
    f_SCHEDULE,
    f_CHNLDATAPATH,
    f_CHNLORDERBY,
    f_ATTRIBUTE,
    f_CRUSER,
    f_CRTIME,
    f_STATUS,
    f_CHNLTYPE,
    f_CHNLOUTLINETEMP,
    f_CHNLDETAILTEMP,
    f_PUBLISHPRO,
    f_OPERUSER,
    f_OPERTIME,
    f_LINKURL,
    f_CONTENTADDEDITPAGE,
    f_CONTENTLISTPAGE,
    f_CONTENTSHOWPAGE,
    f_OUTLINEFILEDS,
    f_OUTLINEDBFIELDS,
    f_ISCLUSTER,
    f_SHOWFIELDS,
    f_FIELDSWIDTH,
    f_BASEPROPS,
    f_OTHERPROPS,
    f_ADVANCEPROPS,
    f_TOOLBAR,
    f_ADVTOOLBAR,
    f_ISCONTAINSCHILDREN,
    f_METASYNFIELDS,
    f_FLUENCEXML,
    f_USEDOCLEVEL,
    f_lastmodifytime,
    f_VIEWPROPS,
    f_NEEDEDPROPS,
    f_INHERIT,
    f_IsSubscribe,
    f_ISPUSHABLE,
    f_ISDISTRIBUTABLE,
    f_CHANNELLOGO,
    f_SEARCHWORDVALUE,
    f_CHNLDESCPINYIN
    ;


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



    WHILE isover = 0 DO

      # 断点续传
      IF (f_CHANNELID > prevChnlId)

        THEN

            SET @CHANNELID = f_CHANNELID;
            SET f_CHANNELID = f_CHANNELID + channelIdOffset;

            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;

            # 当父栏目为0时，表示根节点
            IF (f_PARENTID = 0) THEN
              SET f_PARENTID = 0;
            else
              SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = migr_table_name AND start_id < f_PARENTID and  end_id >= f_PARENTID), channelIdOffset);
              SET f_PARENTID = f_PARENTID + @channel_offset_num;
            END IF;


            insert into trs_hycloud_iip.wcmchannel
              (
                CHANNELID,
                SITEID,
                CHNLNAME,
                CHNLDESC,
                CHNLTABLE,
                CHNLQUERY,
                CHNLPROP,
                PARENTID,
                CHNLORDER,
                SCHEDULE,
                CHNLDATAPATH,
                CHNLORDERBY,
                ATTRIBUTE,
                CRUSER,
                CRTIME,
                STATUS,
                CHNLTYPE,
                CHNLOUTLINETEMP,
                CHNLDETAILTEMP,
                PUBLISHPRO,
                OPERUSER,
                OPERTIME,
                LINKURL,
                CONTENTADDEDITPAGE,
                CONTENTLISTPAGE,
                CONTENTSHOWPAGE,
                OUTLINEFILEDS,
                OUTLINEDBFIELDS,
                ISCLUSTER,
                SHOWFIELDS,
                FIELDSWIDTH,
                BASEPROPS,
                OTHERPROPS,
                ADVANCEPROPS,
                TOOLBAR,
                ADVTOOLBAR,
                ISCONTAINSCHILDREN,
                METASYNFIELDS,
                FLUENCEXML,
                USEDOCLEVEL,
                lastmodifytime,
                VIEWPROPS,
                NEEDEDPROPS,
                INHERIT,
                IsSubscribe,
                ISPUSHABLE,
                ISDISTRIBUTABLE,
                CHANNELLOGO,
                SEARCHWORDVALUE,
                CHNLDESCPINYIN
              )
              VALUES
                (
                  f_CHANNELID,
                  f_SITEID,
                  f_CHNLNAME,
                  f_CHNLDESC,
                  f_CHNLTABLE,
                  f_CHNLQUERY,
                  f_CHNLPROP,
                  f_PARENTID,
                  f_CHNLORDER,
                  f_SCHEDULE,
                  f_CHNLDATAPATH,
                  f_CHNLORDERBY,
                  f_ATTRIBUTE,
                  f_CRUSER,
                  f_CRTIME,
                  f_STATUS,
                  f_CHNLTYPE,
                  f_CHNLOUTLINETEMP,
                  f_CHNLDETAILTEMP,
                  f_PUBLISHPRO,
                  f_OPERUSER,
                  f_OPERTIME,
                  f_LINKURL,
                  f_CONTENTADDEDITPAGE,
                  f_CONTENTLISTPAGE,
                  f_CONTENTSHOWPAGE,
                  f_OUTLINEFILEDS,
                  f_OUTLINEDBFIELDS,
                  f_ISCLUSTER,
                  f_SHOWFIELDS,
                  f_FIELDSWIDTH,
                  f_BASEPROPS,
                  f_OTHERPROPS,
                  f_ADVANCEPROPS,
                  f_TOOLBAR,
                  f_ADVTOOLBAR,
                  f_ISCONTAINSCHILDREN,
                  f_METASYNFIELDS,
                  f_FLUENCEXML,
                  f_USEDOCLEVEL,
                  f_lastmodifytime,
                  f_VIEWPROPS,
                  f_NEEDEDPROPS,
                  f_INHERIT,
                  f_IsSubscribe,
                  f_ISPUSHABLE,
                  f_ISDISTRIBUTABLE,
                  f_CHANNELLOGO,
                  f_SEARCHWORDVALUE,
                  f_CHNLDESCPINYIN
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, channelIdOffset, prevChnlId, @CHANNELID);
    END IF ;

		FETCH cur INTO
      f_CHANNELID,
      f_SITEID,
      f_CHNLNAME,
      f_CHNLDESC,
      f_CHNLTABLE,
      f_CHNLQUERY,
      f_CHNLPROP,
      f_PARENTID,
      f_CHNLORDER,
      f_SCHEDULE,
      f_CHNLDATAPATH,
      f_CHNLORDERBY,
      f_ATTRIBUTE,
      f_CRUSER,
      f_CRTIME,
      f_STATUS,
      f_CHNLTYPE,
      f_CHNLOUTLINETEMP,
      f_CHNLDETAILTEMP,
      f_PUBLISHPRO,
      f_OPERUSER,
      f_OPERTIME,
      f_LINKURL,
      f_CONTENTADDEDITPAGE,
      f_CONTENTLISTPAGE,
      f_CONTENTSHOWPAGE,
      f_OUTLINEFILEDS,
      f_OUTLINEDBFIELDS,
      f_ISCLUSTER,
      f_SHOWFIELDS,
      f_FIELDSWIDTH,
      f_BASEPROPS,
      f_OTHERPROPS,
      f_ADVANCEPROPS,
      f_TOOLBAR,
      f_ADVTOOLBAR,
      f_ISCONTAINSCHILDREN,
      f_METASYNFIELDS,
      f_FLUENCEXML,
      f_USEDOCLEVEL,
      f_lastmodifytime,
      f_VIEWPROPS,
      f_NEEDEDPROPS,
      f_INHERIT,
      f_IsSubscribe,
      f_ISPUSHABLE,
      f_ISDISTRIBUTABLE,
      f_CHANNELLOGO,
      f_SEARCHWORDVALUE,
      f_CHNLDESCPINYIN
      ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;