TRS.WCM.Developer/贵州项目/6_recover.sql

# 执行如下sql可清除之前所有迁移的数据，然后可以开始重新迁移
# ！！！如果海云系统启动并恢复了使用，则再也不能执行如下sql了，否则会误删海云的正常数据
# 清除的数据 不包括元数据表，一般情况下，元数据表不需要重新迁移；如果确定元数据有问题，也需要重新迁移，则手工删除所有......_city... 后缀的元数据表即可

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

  BEGIN
    SET @citySuffix = concat('贵州迁移站点__city', @n,'%');
    # xx ID 偏移量,获取海云系统中的对应 xx maxID 错开一段距离,改为存入最大id

	  SET @site_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmwebsite'), 0);
	  IF (@site_offset_num > 0)
	    then
        DELETE from trs_hycloud_iip.wcmclassification where CNAME like @citySuffix;
        DELETE from trs_hycloud_iip.wcmclassificationobj where OBJTYPE = 103 and OBJID > @site_offset_num;
        delete from trs_hycloud_iip.wcmwebsite where siteid > @site_offset_num;
    END IF ;

	  SET @view_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmviewinfo'), 0);
	  IF (@view_offset_num > 0)
	    then
        delete from trs_hycloud_iip.xwcmviewinfo where viewinfoid > @view_offset_num;
        delete from trs_hycloud_iip.xwcmmetaviewfieldgroup where METAVIEWID > @view_offset_num;
    END IF ;


	  SET @tabel_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmtableinfo'), 0);
	  IF (@tabel_offset_num > 0)
	    then
        delete from trs_hycloud_iip.xwcmtableinfo where tableinfoid > @tabel_offset_num;
    END IF ;

	  SET @group_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmmetaviewfieldgroup'), 0);
    IF (@group_offset_num > 0)
      then
        delete from trs_hycloud_iip.xwcmmetaviewfieldgroup where metaviewfieldgroupid > @group_offset_num;
    end if;

	  SET @class_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmclassinfoview'), 0);
    IF (@class_offset_num > 0)
      then
        delete from trs_hycloud_iip.xwcmclassinfoview where classinfoviewid > @class_offset_num;
    end if;

	  SET @classinfo_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmclassinfo'), 0);
	  IF (@classinfo_offset_num > 0)
	    then
        delete from trs_hycloud_iip.xwcmclassinfo where classinfoid > @classinfo_offset_num;
    end if;

    SET @db_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmdbfieldinfo'), 0);
	  IF (@db_offset_num > 0)
	    then
      delete from trs_hycloud_iip.xwcmdbfieldinfo where dbfieldinfoid > @db_offset_num;
    END IF ;

    SET @employer_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmmetaviewemployer'), 0);
	  IF (@employer_offset_num > 0)
	    then
      delete from trs_hycloud_iip.xwcmmetaviewemployer where metaviewemployerid > @employer_offset_num;
    END IF ;

    SET @xwcmviewfieldinfo_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmviewfieldinfo'), 0);
	  IF (@xwcmviewfieldinfo_offset_num > 0)
	    then
      delete from trs_hycloud_iip.xwcmviewfieldinfo where viewfieldinfoid > @xwcmviewfieldinfo_offset_num;
    END IF ;

    SET @wcmchannel_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmchannel'), 0);
	  IF (@wcmchannel_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmchannel where channelid > @wcmchannel_offset_num;
    END IF ;

    SET @wcmchannelsyn_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmchannelsyn'), 0);
	  IF (@wcmchannelsyn_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmchannelsyn where channelsynid > @wcmchannelsyn_offset_num;
    END IF ;

    SET @wcmtemplate_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmtemplate'), 0);
	  IF (@wcmtemplate_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmtemplate where tempid > @wcmtemplate_offset_num;
      delete from trs_hycloud_iip.wcmchannelchildindexquote where TEMPLATEID > @wcmtemplate_offset_num;
    END IF ;

    SET @config_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmfolderpublishconfig'), 0);
	  IF (@config_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmfolderpublishconfig where folderpublishconfigid > @config_offset_num;
    END IF ;

    SET @cwcmtemplateargument_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmtemplateargument'), 0);
	  IF (@cwcmtemplateargument_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmtemplateargument where templateargumentid > @cwcmtemplateargument_offset_num;
    END IF ;

    SET @wcmtemplateemploy_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmtemplateemploy'), 0);
	  IF (@wcmtemplateemploy_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmtemplateemploy where templateemployid > @wcmtemplateemploy_offset_num;
    END IF ;

    SET @wcmtemplatenest_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmtemplatenest'), 0);
	  IF (@wcmtemplatenest_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmtemplatenest where templatenestid > @wcmtemplatenest_offset_num;
    END IF ;

    SET @wcmtemplatequote_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmtemplatequote'), 0);
	  IF (@wcmtemplatequote_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmtemplatequote where templatequoteid > @wcmtemplatequote_offset_num;
    END IF ;

    SET @wcmappendix_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmappendix'), 0);
	  IF (@wwcmtemplatequote_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmappendix where appendixid > @wcmappendix_offset_num;
    END IF ;

    SET @wcmchnldoc_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmchnldoc'), 0);
	  IF (@wcmchnldoc_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmchnldoc where recid > @wcmchnldoc_offset_num;
    END IF ;

    SET @wcmdocument_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmdocument'), 0);
	  IF (@wcmdocument_offset_num > 0)
	    then
      delete from trs_hycloud_iip.wcmdocument where docid > @wcmdocument_offset_num;
    END IF ;

    SET @mas_masid_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'mas_masid'), 0);
	  IF (@mas_masid_offset_num > 0)
	    then
      delete from trs_mas.mas_masid where id > @mas_masid_offset_num;
    END IF ;

    SET @mas_masvideo_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'mas_masvideo'), 0);
	  IF (@mas_masvideo_offset_num > 0)
	    then
      delete from trs_mas.mas_masvideo where id > @mas_masvideo_offset_num;
    END IF ;

    delete from trs_data_migration.data_migration_log;

  END $$
DELIMITER ;

/**
# 清理元数据
USE trs_data_migration;

DROP PROCEDURE IF EXISTS sp_delete_metatables;
DELIMITER $$
CREATE PROCEDURE sp_delete_metatables()
	BEGIN

	  DECLARE isover INT DEFAULT 0;

    DECLARE  f_TABLENAME varchar(50) DEFAULT NULL;



  DECLARE cur CURSOR FOR
		SELECT
      TABLENAME
    FROM trs_data_migration.xwcmtableinfo
		ORDER BY TABLEINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;

	OPEN cur;

	FETCH cur INTO
    f_TABLENAME
    ;

  WHILE isover = 0 DO

    SET @sql = concat('create table if not exists ', 'trs_hycloud_iip.wcmmetatable', f_TABLENAME, '(', f_TABLENAME ,'ID INT);');

    prepare stmt from @sql;
    EXECUTE stmt;
    deallocate prepare stmt;

    SET @sql = concat('truncate table ', 'trs_hycloud_iip.wcmmetatable', f_TABLENAME);

    prepare stmt from @sql;
    EXECUTE stmt;
    deallocate prepare stmt;

    FETCH cur INTO
      f_TABLENAME
      ;

  END WHILE;

	CLOSE cur;

  END $$
DELIMITER ;

CALL trs_data_migration.sp_delete_metatables();
**/