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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmfolderpublishconfig;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmfolderpublishconfig(IN prevFolderPublishConfigId BIGINT(20),
                                              IN folderPublishConfigIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

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

    DECLARE f_FOLDERPUBLISHCONFIGID int(11) DEFAULT 0;
    DECLARE f_FOLDERTYPE int(11) DEFAULT 0;
    DECLARE f_FOLDERID int(11) DEFAULT 0;
    DECLARE f_OUTLINEFILE varchar(100) DEFAULT NULL;
    DECLARE f_DETAILFILEEXT varchar(10) DEFAULT NULL;
    DECLARE f_DATAPATH varchar(200) DEFAULT NULL;
    DECLARE f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;
    DECLARE f_ROOTDOMAIN varchar(250) DEFAULT NULL;
    DECLARE f_SITELANGUAGE int(11) DEFAULT NULL;
    DECLARE f_PAGEENCODING varchar(50) DEFAULT NULL;




    DECLARE cur CURSOR FOR
		SELECT
      FOLDERPUBLISHCONFIGID,
      FOLDERTYPE,
      FOLDERID,
      OUTLINEFILE,
      DETAILFILEEXT,
      DATAPATH,
      CRUSER,
      CRTIME,
      ROOTDOMAIN,
      SITELANGUAGE,
      PAGEENCODING
    FROM wcmfolderpublishconfig
		ORDER BY FOLDERPUBLISHCONFIGID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_FOLDERPUBLISHCONFIGID,
    f_FOLDERTYPE,
    f_FOLDERID,
    f_OUTLINEFILE,
    f_DETAILFILEEXT,
    f_DATAPATH,
    f_CRUSER,
    f_CRTIME,
    f_ROOTDOMAIN,
    f_SITELANGUAGE,
    f_PAGEENCODING
    ;


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



    WHILE isover= 0 DO

      # 断点续传
      IF (f_FOLDERPUBLISHCONFIGID > prevFolderPublishConfigId)

        THEN

            SET @FOLDERPUBLISHCONFIGID = f_FOLDERPUBLISHCONFIGID;
            SET f_FOLDERPUBLISHCONFIGID = f_FOLDERPUBLISHCONFIGID + folderPublishConfigIdOffset;

            IF (f_FOLDERTYPE = 103)
              THEN
                SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_FOLDERID and  end_id >= f_FOLDERID), siteIdOffset);
                SET f_FOLDERID = f_FOLDERID + @site_offset_num;
            END IF ;

            IF (f_FOLDERTYPE = 101)
              THEN
                SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_FOLDERID and  end_id >= f_FOLDERID), channelIdOffset);
                SET f_FOLDERID = f_FOLDERID + @channel_offset_num;
            END IF ;


            insert into trs_hycloud_iip.wcmfolderpublishconfig
              (
                FOLDERPUBLISHCONFIGID,
                FOLDERTYPE,
                FOLDERID,
                OUTLINEFILE,
                DETAILFILEEXT,
                DATAPATH,
                CRUSER,
                CRTIME,
                ROOTDOMAIN,
                SITELANGUAGE,
                PAGEENCODING
              )
              VALUES
                (
                  f_FOLDERPUBLISHCONFIGID,
                  f_FOLDERTYPE,
                  f_FOLDERID,
                  f_OUTLINEFILE,
                  f_DETAILFILEEXT,
                  f_DATAPATH,
                  f_CRUSER,
                  f_CRTIME,
                  f_ROOTDOMAIN,
                  f_SITELANGUAGE,
                  f_PAGEENCODING
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, folderPublishConfigIdOffset, prevFolderPublishConfigId, @FOLDERPUBLISHCONFIGID);
    END IF ;

		FETCH cur INTO
      f_FOLDERPUBLISHCONFIGID,
      f_FOLDERTYPE,
      f_FOLDERID,
      f_OUTLINEFILE,
      f_DETAILFILEEXT,
      f_DATAPATH,
      f_CRUSER,
      f_CRTIME,
      f_ROOTDOMAIN,
      f_SITELANGUAGE,
      f_PAGEENCODING
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;