TRS.WCM.Developer/贵州项目/4_sp_city_n.sql

DROP PROCEDURE IF EXISTS sp_city_n;
DELIMITER $$
CREATE PROCEDURE sp_city_n(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 @prevRecId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmchnldoc'), 0);
    SET @prevDocId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmdocument'), 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 @prevClassInfoViewId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmclassinfoview'), 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);
    SET @prevMetaViewFieldGroupId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmmetaviewfieldgroup'), 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 @maxRecId= IFNULL((SELECT MAX(recId) from trs_hycloud_iip.wcmchnldoc),0);
	  SET @recIdOffset = @maxRecId - @prevRecId;
	  SET @maxDocId= IFNULL((SELECT MAX(DOCID) from trs_hycloud_iip.wcmdocument),0);
	  SET @docIdOffset = @maxDocId - @prevDocId;
	  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;
	  SET @maxClassInfoViewId = IFNULL((SELECT MAX(CLASSINFOVIEWID) from trs_hycloud_iip.xwcmclassinfoview),0);
	  SET @classInfoViewIdOffset = @maxClassInfoViewId - @prevClassInfoViewId;

    #基本信息迁移
    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);

    #数据迁移
    call sp_data_migration_wcmdocument(@prevDocId, @docIdOffset, @viewIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmchnldoc(@prevRecId, @recIdOffset, @docIdOffset, @siteIdOffset, @channelIdOffset, @viewIdOffset);
    call sp_data_migration_xwcmclassinfoview(@prevClassInfoViewId, @classInfoViewIdOffset, @classInfoIdOffset, @docIdOffset, @viewIdOffset);
    call sp_data_migration_wcmmetatable(@citySuffix, @prevTableInfoId, @prevDocId, @docIdOffset, @channelIdOffset);

    #附件迁移
    # 迁移 MAS 视频，6个wcm节点的视频是在一起的。可重复执行。
    # 支持断点续传，上一次迁移的最后一个 xx ID
    SET @prevMasId = ifnull((SELECT prev_id from data_migration_log where table_name = 'mas_masid'), 0);
    SET @prevMasVideoId = ifnull((SELECT prev_id from data_migration_log where table_name = 'mas_masvideo'), 0);
    SET @prevMaterialquoteId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmmaterial'), 0);
    SET @prevAppendixId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmappendix'), 0);

    SET @maxMasId = IFNULL((SELECT MAX(ID) from trs_mas.mas_masid),0);
    SET @masIdOffset = @maxMasId - @prevMasId;
    SET @maxMasVideoId = IFNULL((SELECT MAX(ID) from trs_mas.mas_videostream),0);
    SET @masVideoIdOffset = @maxMasVideoId - @prevMasVideoId;
    SET @maxNAppendixId= IFNULL((SELECT MAX(appendixId) from trs_hycloud_iip.wcmappendix),0);
	  SET @nappendixIdOffset = @maxNAppendixId - @prevMaterialquoteId;
	  SET @maxAppendixId= IFNULL((SELECT MAX(appendixId) from trs_hycloud_iip.wcmappendix),0);
	  SET @appendixIdOffset = @maxAppendixId - @prevAppendixId;

    call sp_data_migration_masid(@prevMasId, @masIdOffset);
    call sp_data_migration_masvideo(@prevMasVideoId, @masVideoIdOffset);
    #处理素材表
    call sp_data_migration_wcmappendix(@prevAppendixId, @appendixIdOffset, @docIdOffset);
    call sp_data_migration_xwcmmaterial(@prevMaterialquoteId, @nappendixIdOffset, @docIdOffset);

  END $$
DELIMITER ;