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

DROP PROCEDURE IF EXISTS sp_city_base;
DELIMITER $$
CREATE PROCEDURE sp_city_base(IN n INT)

  BEGIN

    # 每迁移一个wcm节点，@n 必须 +1
    SET @n = n;

    # 每个wcm节点，元数据表需要加上后缀，以区分同名元数据表
    SET @citySuffix = concat('_city', @n);

    # 支持断点续传，上一次迁移的最后一个 xx ID
    SET @prevSiteId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmwebsite'), 0);
    SET @prevChannelId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmchannel'), 0);
    SET @prevChannelSynId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmchannelsyn'), 0);
    SET @prevFolderPublishConfigId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmfolderpublishconfig'), 0);
    SET @prevTemplateId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmtemplate'), 0);
    SET @prevTemplateArgId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmtemplateargument'), 0);
    SET @prevTemplateEmployId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmtemplateemploy'), 0);
    SET @prevTemplateNestId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmtemplatenest'), 0);
    SET @prevTemplateQuoteId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmtemplatequote'), 0);
    SET @prevClassInfoId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmclassinfo'), 0);
    SET @prevFieldId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmdbfieldinfo'), 0);
    SET @prevMetaViewEmployerId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmmetaviewemployer'), 0);
    SET @prevTableInfoId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmtableinfo'), 0);
    SET @prevViewFieldInfoId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmviewfieldinfo'), 0);
    SET @prevViewId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmviewinfo'), 0);


    # 支持数据增量 偏移量  修改
    # xx ID 偏移量,获取海云系统中的对应 xx maxID 错开一段距离,改为存入最大id
	  SET @maxSiteId = IFNULL((SELECT MAX(siteId) from trs_hycloud_iip.wcmwebsite),0);
		SET @siteIdOffset = @maxSiteId - @prevSiteId;
	  SET @maxChannelId= IFNULL((SELECT MAX(channelId) from trs_hycloud_iip.wcmchannel),0);
	  SET @channelIdOffset = @maxChannelId - @prevChannelId;
	  SET @maxViewId= IFNULL((SELECT MAX(viewInfoId) from trs_hycloud_iip.xwcmviewinfo),0);
	  SET @viewIdOffset = @maxViewId - @prevViewId;
	  SET @maxTableInfoId= IFNULL((SELECT MAX(TABLEINFOID) from trs_hycloud_iip.xwcmtableinfo),0);
	  SET @tableInfoIdOffset = @maxTableInfoId - @prevTableInfoId;
	  SET @maxFieldId = IFNULL((SELECT MAX(DBFIELDINFOID) from trs_hycloud_iip.xwcmdbfieldinfo),0);
	  SET @fieldIdOffset = @maxFieldId - @prevFieldId;
	  SET @maxChannelSynId= IFNULL((SELECT MAX(channelSynId) from trs_hycloud_iip.wcmchannelsyn),0);
	  SET @channelSynIdOffset = @maxChannelSynId - @prevChannelSynId;
	  SET @maxTemplateId = IFNULL((SELECT MAX(TEMPID) from trs_hycloud_iip.wcmtemplate),0);
	  SET @templateIdOffset = @maxTemplateId - @prevTemplateId;
	  SET @maxFolderPublishConfigId = IFNULL((SELECT MAX(FOLDERPUBLISHCONFIGID) from trs_hycloud_iip.wcmfolderpublishconfig),0);
	  SET @folderPublishConfigIdOffset = @maxFolderPublishConfigId - @prevFolderPublishConfigId;
	  SET @maxTemplateEmployId = IFNULL((SELECT MAX(TEMPLATEEMPLOYID) from trs_hycloud_iip.wcmtemplateemploy),0);
	  SET @templateEmployIdOffset = @maxTemplateEmployId - @prevTemplateEmployId;
	  SET @maxTemplateNestId= IFNULL((SELECT MAX(TEMPLATENESTID) from trs_hycloud_iip.wcmtemplatenest),0);
	  SET @templateNestIdOffset = @maxTemplateNestId - @prevTemplateNestId;
	  SET @maxTemplateQuoteId = IFNULL((SELECT MAX(TEMPLATEQUOTEID) from trs_hycloud_iip.wcmtemplatequote),0);
	  SET @templateQuoteIdOffset = @maxTemplateQuoteId - @prevTemplateQuoteId;
	  SET @maxMetaViewEmployerId = IFNULL((SELECT MAX(METAVIEWEMPLOYERID) from trs_hycloud_iip.xwcmmetaviewemployer),0);
	  SET @metaViewEmployerIdOffset = @maxMetaViewEmployerId - @prevMetaViewEmployerId;
	  SET @maxViewFieldInfoId = IFNULL((SELECT MAX(VIEWFIELDINFOID) from trs_hycloud_iip.xwcmviewfieldinfo),0);
	  SET @viewFieldInfoIdOffset = @maxViewFieldInfoId - @prevViewFieldInfoId;
	  SET @maxTemplateArgId= IFNULL((SELECT MAX(TEMPLATEARGUMENTID) from trs_hycloud_iip.wcmtemplateargument),0);
	  SET @templateArgIdOffset = @maxTemplateArgId - @prevTemplateArgId;
	  SET @maxClassInfoId = IFNULL((SELECT MAX(classInfoId) from trs_hycloud_iip.xwcmclassinfo),0);
	  SET @classInfoIdOffset = @maxClassInfoId - @prevClassInfoId;

    #基本信息迁移
    call sp_data_migration_wcmwebsite(@citySuffix, @prevSiteId, @siteIdOffset);
    call sp_data_migration_wcmchannel(@citySuffix, @prevChannelId, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmchannelsyn(@prevChannelSynId, @channelSynIdOffset, @channelIdOffset);
    call sp_data_migration_xwcmtableinfo(@citySuffix, @prevTableInfoId, @tableInfoIdOffset);
    call sp_data_migration_xwcmclassinfo(@prevClassInfoId, @classInfoIdOffset);
    call sp_data_migration_wcmtemplate(@prevTemplateId, @templateIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmtemplatenest(@prevTemplateNestId, @templateNestIdOffset);
    call sp_data_migration_wcmfolderpublishconfig(@prevFolderPublishConfigId, @folderPublishConfigIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmtemplateargument(@prevTemplateArgId, @templateArgIdOffset, @templateIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmtemplateemploy(@prevTemplateEmployId, @templateEmployIdOffset, @templateIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmtemplatequote(@prevTemplateQuoteId, @templateQuoteIdOffset, @templateIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmchannelchildindexquote(@templateIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_xwcmviewinfo(@citySuffix, @prevViewId, @viewIdOffset, @tableInfoIdOffset);
    call sp_data_migration_xwcmdbfieldinfo(@citySuffix, @prevFieldId, @fieldIdOffset, @prevClassInfoId, @tableInfoIdOffset);
    call sp_data_migration_xwcmmetaviewemployer(@prevMetaViewEmployerId, @metaViewEmployerIdOffset, @viewIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_xwcmviewfieldinfo(@citySuffix, @prevViewFieldInfoId, @viewFieldInfoIdOffset, @tableInfoIdOffset, @prevClassInfoId, @dbFieldIdOffset, @metaViewFieldGroupIdOffset, @viewIdOffset, @viewIdOffset);

  END $$
DELIMITER ;