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

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmviewfieldinfo;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmviewfieldinfo(IN citySuffix VARCHAR(20),
                                              IN prevViewFieldInfoId BIGINT(20),
                                              IN viewFieldInfoIdOffset BIGINT(20),
                                              IN tableInfoIdOffset BIGINT(20),
                                              IN classIdOffset BIGINT(20),
                                              IN dbFieldIdOffset BIGINT(20),
                                              IN fieldGroupIdOffset BIGINT(20),
                                              IN relationViewIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE isover INT DEFAULT 0;


    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmviewfieldinfo';
    DECLARE table_migr_table_name varchar(255) DEFAULT 'xwcmtableinfo';
    DECLARE class_migr_table_name varchar(255) DEFAULT 'xwcmclassinfo';
    DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE db_migr_table_name varchar(255) DEFAULT 'xwcmdbfieldinfo';


    DECLARE  f_VIEWFIELDINFOID int(11) DEFAULT NULL;
    DECLARE  f_TABLENAME varchar(50) DEFAULT NULL;
    DECLARE  f_TABLEID int(11) DEFAULT '0';
    DECLARE  f_FIELDNAME varchar(50) DEFAULT NULL;
    DECLARE  f_DBFIELDNAME varchar(50) DEFAULT NULL;
    DECLARE  f_DBTYPE int(11) DEFAULT '0';
    DECLARE  f_DBLENGTH int(11) DEFAULT '0';
    DECLARE  f_ANOTHERNAME varchar(200) DEFAULT NULL;
    DECLARE  f_FIELDTYPE int(11) DEFAULT '0';
    DECLARE  f_DEFAULTVALUE varchar(200) DEFAULT NULL;
    DECLARE  f_ENMVALUE varchar(1000) DEFAULT NULL;
    DECLARE  f_NOTNULL smallint(6) DEFAULT NULL;
    DECLARE  f_CLASSID int(11) DEFAULT '0';
    DECLARE  f_VIEWID int(11) DEFAULT '0';
    DECLARE  f_FIELDORDER int(11) DEFAULT '0';
    DECLARE  f_INOUTLINE smallint(6) DEFAULT NULL;
    DECLARE  f_INDETAIL smallint(6) DEFAULT '1';
    DECLARE  f_SEARCHFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_TITLEFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_DBFIELD int(11) DEFAULT '0';
    DECLARE  f_VALIDATOR varchar(200) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_FROMMAINTABLE int(11) DEFAULT '1';
    DECLARE  f_LOCATECHANNEL varchar(100) DEFAULT NULL;
    DECLARE  f_RADORCHK smallint(6) DEFAULT NULL;
    DECLARE  f_NOTEDIT smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENFIELD smallint(6) DEFAULT '0';
    DECLARE  f_INMULTITABLE smallint(6) DEFAULT '1';
    DECLARE  f_DBSCALE int(11) DEFAULT '3';
    DECLARE  f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE  f_IDENTITYFIELD smallint(6) DEFAULT NULL;
    DECLARE  f_LIBFILEDID int(11) DEFAULT NULL;
    DECLARE  f_FIELDGROUPID int(11) DEFAULT NULL;
    DECLARE  f_RELATIONVIEWID int(11) DEFAULT '0';
    DECLARE  f_ISURLFIELD smallint(6) DEFAULT '0';
    DECLARE  f_EDITSTYLE varchar(10) DEFAULT NULL;
    DECLARE  f_APPENDIXSELECTLIB varchar(10) DEFAULT NULL;
    DECLARE  f_APPENDIXNOTUPLOAD varchar(10) DEFAULT NULL;
    DECLARE  f_FieldWidth varchar(50) DEFAULT NULL;
    DECLARE  f_FieldClass varchar(300) DEFAULT NULL;
    DECLARE  f_ISONLYSITE varchar(10) DEFAULT NULL;



  DECLARE cur CURSOR FOR
		SELECT
      VIEWFIELDINFOID,
      TABLENAME,
      TABLEID,
      FIELDNAME,
      DBFIELDNAME,
      DBTYPE,
      DBLENGTH,
      ANOTHERNAME,
      FIELDTYPE,
      DEFAULTVALUE,
      ENMVALUE,
      NOTNULL,
      CLASSID,
      VIEWID,
      FIELDORDER,
      INOUTLINE,
      INDETAIL,
      SEARCHFIELD,
      TITLEFIELD,
      DBFIELD,
      VALIDATOR,
      CRUSER,
      CRTIME,
      FROMMAINTABLE,
      LOCATECHANNEL,
      RADORCHK,
      NOTEDIT,
      HIDDENFIELD,
      INMULTITABLE,
      DBSCALE,
      ATTRIBUTE,
      IDENTITYFIELD,
      LIBFILEDID,
      FIELDGROUPID,
      RELATIONVIEWID,
      ISURLFIELD,
      EDITSTYLE,
      APPENDIXSELECTLIB,
      APPENDIXNOTUPLOAD,
      FieldWidth,
      FieldClass,
      ISONLYSITE
    FROM xwcmviewfieldinfo
		ORDER BY VIEWFIELDINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_VIEWFIELDINFOID,
    f_TABLENAME,
    f_TABLEID,
    f_FIELDNAME,
    f_DBFIELDNAME,
    f_DBTYPE,
    f_DBLENGTH,
    f_ANOTHERNAME,
    f_FIELDTYPE,
    f_DEFAULTVALUE,
    f_ENMVALUE,
    f_NOTNULL,
    f_CLASSID,
    f_VIEWID,
    f_FIELDORDER,
    f_INOUTLINE,
    f_INDETAIL,
    f_SEARCHFIELD,
    f_TITLEFIELD,
    f_DBFIELD,
    f_VALIDATOR,
    f_CRUSER,
    f_CRTIME,
    f_FROMMAINTABLE,
    f_LOCATECHANNEL,
    f_RADORCHK,
    f_NOTEDIT,
    f_HIDDENFIELD,
    f_INMULTITABLE,
    f_DBSCALE,
    f_ATTRIBUTE,
    f_IDENTITYFIELD,
    f_LIBFILEDID,
    f_FIELDGROUPID,
    f_RELATIONVIEWID,
    f_ISURLFIELD,
    f_EDITSTYLE,
    f_APPENDIXSELECTLIB,
    f_APPENDIXNOTUPLOAD,
    f_FieldWidth,
    f_FieldClass,
    f_ISONLYSITE
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_VIEWFIELDINFOID > prevViewFieldInfoId)

        THEN

            SET @VIEWFIELDINFOID = f_VIEWFIELDINFOID;
            SET f_VIEWFIELDINFOID = f_VIEWFIELDINFOID + viewFieldInfoIdOffset;

            SET @table_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = table_migr_table_name AND start_id < f_TABLEID and  end_id >= f_TABLEID), 0);
            SET f_TABLEID = f_TABLEID + @table_offset_num;

            SET @class_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = class_migr_table_name AND start_id < f_CLASSID and  end_id >= f_CLASSID), 0);
            SET f_CLASSID = f_CLASSID + @class_offset_num;

            SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_VIEWID and  end_id >= f_VIEWID), 0);
            SET f_VIEWID = f_VIEWID + @view_offset_num;

            SET @db_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = db_migr_table_name AND start_id < f_DBFIELD and  end_id >= f_DBFIELD), 0);
            SET f_DBFIELD = f_DBFIELD + @db_offset_num;

            -- SET f_LIBFILEDID = f_LIBFILEDID + ;
            SET f_FIELDGROUPID = ifnull((SELECT METAVIEWFIELDGROUPID FROM trs_hycloud_iip.xwcmmetaviewfieldgroup WHERE METAVIEWID = f_VIEWID AND GROUPNAME = '基本属性'), 0);

            SET @ralation_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_RELATIONVIEWID and  end_id >= f_RELATIONVIEWID), 0);
            SET f_RELATIONVIEWID = f_RELATIONVIEWID + @ralation_offset_num;

            insert into trs_hycloud_iip.xwcmviewfieldinfo
              (
                VIEWFIELDINFOID,
                TABLENAME,
                TABLEID,
                FIELDNAME,
                DBFIELDNAME,
                DBTYPE,
                DBLENGTH,
                ANOTHERNAME,
                FIELDTYPE,
                DEFAULTVALUE,
                ENMVALUE,
                NOTNULL,
                CLASSID,
                VIEWID,
                FIELDORDER,
                INOUTLINE,
                INDETAIL,
                SEARCHFIELD,
                TITLEFIELD,
                DBFIELD,
                VALIDATOR,
                CRUSER,
                CRTIME,
                FROMMAINTABLE,
                LOCATECHANNEL,
                RADORCHK,
                NOTEDIT,
                HIDDENFIELD,
                INMULTITABLE,
                DBSCALE,
                ATTRIBUTE,
                IDENTITYFIELD,
                LIBFILEDID,
                FIELDGROUPID,
                RELATIONVIEWID,
                ISURLFIELD,
                EDITSTYLE,
                APPENDIXSELECTLIB,
                APPENDIXNOTUPLOAD,
                FieldWidth,
                FieldClass,
                ISONLYSITE
              )
              VALUES
                (
                  f_VIEWFIELDINFOID,
                  concat(f_TABLENAME, citySuffix),
                  f_TABLEID,
                  f_FIELDNAME,
                  f_DBFIELDNAME,
                  f_DBTYPE,
                  f_DBLENGTH,
                  f_ANOTHERNAME,
                  f_FIELDTYPE,
                  f_DEFAULTVALUE,
                  f_ENMVALUE,
                  f_NOTNULL,
                  f_CLASSID,
                  f_VIEWID,
                  f_FIELDORDER,
                  f_INOUTLINE,
                  f_INDETAIL,
                  f_SEARCHFIELD,
                  f_TITLEFIELD,
                  f_DBFIELD,
                  f_VALIDATOR,
                  f_CRUSER,
                  f_CRTIME,
                  f_FROMMAINTABLE,
                  f_LOCATECHANNEL,
                  f_RADORCHK,
                  f_NOTEDIT,
                  f_HIDDENFIELD,
                  f_INMULTITABLE,
                  f_DBSCALE,
                  f_ATTRIBUTE,
                  f_IDENTITYFIELD,
                  f_LIBFILEDID,
                  f_FIELDGROUPID,
                  f_RELATIONVIEWID,
                  f_ISURLFIELD,
                  f_EDITSTYLE,
                  f_APPENDIXSELECTLIB,
                  f_APPENDIXNOTUPLOAD,
                  f_FieldWidth,
                  f_FieldClass,
                  f_ISONLYSITE
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, viewFieldInfoIdOffset, prevViewFieldInfoId, @VIEWFIELDINFOID);
    END IF ;

		FETCH cur INTO
      f_VIEWFIELDINFOID,
      f_TABLENAME,
      f_TABLEID,
      f_FIELDNAME,
      f_DBFIELDNAME,
      f_DBTYPE,
      f_DBLENGTH,
      f_ANOTHERNAME,
      f_FIELDTYPE,
      f_DEFAULTVALUE,
      f_ENMVALUE,
      f_NOTNULL,
      f_CLASSID,
      f_VIEWID,
      f_FIELDORDER,
      f_INOUTLINE,
      f_INDETAIL,
      f_SEARCHFIELD,
      f_TITLEFIELD,
      f_DBFIELD,
      f_VALIDATOR,
      f_CRUSER,
      f_CRTIME,
      f_FROMMAINTABLE,
      f_LOCATECHANNEL,
      f_RADORCHK,
      f_NOTEDIT,
      f_HIDDENFIELD,
      f_INMULTITABLE,
      f_DBSCALE,
      f_ATTRIBUTE,
      f_IDENTITYFIELD,
      f_LIBFILEDID,
      f_FIELDGROUPID,
      f_RELATIONVIEWID,
      f_ISURLFIELD,
      f_EDITSTYLE,
      f_APPENDIXSELECTLIB,
      f_APPENDIXNOTUPLOAD,
      f_FieldWidth,
      f_FieldClass,
      f_ISONLYSITE
      ;
		
	END WHILE;
	
	CLOSE cur;

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



  END $$
DELIMITER ;