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

DROP PROCEDURE IF EXISTS sp_data_migration_mas_videostream;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_mas_videostream(
                                              IN prevMasVideoId BIGINT(20),
                                              IN videostreamIdOffset BIGINT(20),
                                              IN masVideoIdOffset BIGINT(20))
	BEGIN 
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'mas_videostream';

    DECLARE  f_ID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDTIME bigint(20) DEFAULT NULL;
    DECLARE  f_CREATEDUSER varchar(255) DEFAULT NULL;
    DECLARE  f_CREATEDUSERID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDUSERNICKNAME varchar(255) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDTIME bigint(20) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSER varchar(255) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSERID int(11) DEFAULT NULL;
    DECLARE  f_ATTACHEDPIC int(11) DEFAULT NULL;
    DECLARE  f_AUDIOBITRATE int(11) DEFAULT NULL;
    DECLARE  f_AUDIOCHANNELS int(11) DEFAULT NULL;
    DECLARE  f_AUDIOCODEC varchar(255) DEFAULT NULL;
    DECLARE  f_AUDIOFORMAT varchar(255) DEFAULT NULL;
    DECLARE  f_AUDIOSAMPLERATE int(11) DEFAULT NULL;
    DECLARE  f_BITRATE int(11) DEFAULT NULL;
    DECLARE  f_DEMUXER varchar(255) DEFAULT NULL;
    DECLARE  f_DURATION int(11) DEFAULT NULL;
    DECLARE  f_FPS int(11) DEFAULT NULL;
    DECLARE  f_FRAMERATE double DEFAULT NULL;
    DECLARE  f_HEIGHT int(11) DEFAULT NULL;
    DECLARE  f_mediaType varchar(255) DEFAULT NULL;
    DECLARE  f_NBFRAMES int(11) DEFAULT NULL;
    DECLARE  f_PIXELFORMAT varchar(255) DEFAULT NULL;
    DECLARE  f_VIDEOCODEC varchar(255) DEFAULT NULL;
    DECLARE  f_VIDEOFORMAT varchar(255) DEFAULT NULL;
    DECLARE  f_VIDEOLEVEL varchar(255) DEFAULT NULL;
    DECLARE  f_VIDEOPROFILE varchar(255) DEFAULT NULL;
    DECLARE  f_WIDTH int(11) DEFAULT NULL;
    DECLARE  f_CONSEQUENT int(11) DEFAULT NULL;
    DECLARE  f_CONSOLEONLY bit(1) DEFAULT NULL;
    DECLARE  f_CONSUMERAPPID int(11) DEFAULT NULL;
    DECLARE  f_CPUTIME double DEFAULT NULL;
    DECLARE  f_ELAPSEDSECONDS int(11) DEFAULT NULL;
    DECLARE  f_FILEEXT varchar(255) DEFAULT NULL;
    DECLARE  f_FILENAME varchar(255) DEFAULT NULL;
    DECLARE  f_FORMAT varchar(255) DEFAULT NULL;
    DECLARE  f_FSROOT varchar(255) DEFAULT NULL;
    DECLARE  f_ISRETRANSCODE bit(1) DEFAULT NULL;
    DECLARE  f_MAXMENKB varchar(255) DEFAULT NULL;
    DECLARE  f_OPERATOR varchar(255) DEFAULT NULL;
    DECLARE  f_PROGRESSIVESTATUS varchar(255) DEFAULT NULL;
    DECLARE  f_REPOSITORYID int(11) DEFAULT NULL;
    DECLARE  f_SIZE bigint(20) DEFAULT NULL;
    DECLARE  f_SUBPATH varchar(255) DEFAULT NULL;
    DECLARE  f_TRANSCODECMD varchar(1024) DEFAULT NULL;
    DECLARE  f_TYPE int(11) DEFAULT NULL;
    DECLARE  f_VIDEOID int(11) DEFAULT NULL;






    DECLARE cur CURSOR FOR
		SELECT
      ID,
      CREATEDTIME,
      CREATEDUSER,
      CREATEDUSERID,
      CREATEDUSERNICKNAME,
      LASTMODIFIEDTIME,
      LASTMODIFIEDUSER,
      LASTMODIFIEDUSERID,
      ATTACHEDPIC,
      AUDIOBITRATE,
      AUDIOCHANNELS,
      AUDIOCODEC,
      AUDIOFORMAT,
      AUDIOSAMPLERATE,
      BITRATE,
      DEMUXER,
      DURATION,
      FPS,
      FRAMERATE,
      HEIGHT,
      mediaType,
      NBFRAMES,
      PIXELFORMAT,
      VIDEOCODEC,
      VIDEOFORMAT,
      VIDEOLEVEL,
      VIDEOPROFILE,
      WIDTH,
      CONSEQUENT,
      CONSOLEONLY,
      CONSUMERAPPID,
      CPUTIME,
      ELAPSEDSECONDS,
      FILEEXT,
      FILENAME,
      FORMAT,
      FSROOT,
      ISRETRANSCODE,
      MAXMENKB,
      OPERATOR,
      PROGRESSIVESTATUS,
      REPOSITORYID,
      SIZE,
      SUBPATH,
      TRANSCODECMD,
      TYPE,
      VIDEOID
    FROM mas_videostream
		ORDER BY ID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_ID,
    f_CREATEDTIME,
    f_CREATEDUSER,
    f_CREATEDUSERID,
    f_CREATEDUSERNICKNAME,
    f_LASTMODIFIEDTIME,
    f_LASTMODIFIEDUSER,
    f_LASTMODIFIEDUSERID,
    f_ATTACHEDPIC,
    f_AUDIOBITRATE,
    f_AUDIOCHANNELS,
    f_AUDIOCODEC,
    f_AUDIOFORMAT,
    f_AUDIOSAMPLERATE,
    f_BITRATE,
    f_DEMUXER,
    f_DURATION,
    f_FPS,
    f_FRAMERATE,
    f_HEIGHT,
    f_mediaType,
    f_NBFRAMES,
    f_PIXELFORMAT,
    f_VIDEOCODEC,
    f_VIDEOFORMAT,
    f_VIDEOLEVEL,
    f_VIDEOPROFILE,
    f_WIDTH,
    f_CONSEQUENT,
    f_CONSOLEONLY,
    f_CONSUMERAPPID,
    f_CPUTIME,
    f_ELAPSEDSECONDS,
    f_FILEEXT,
    f_FILENAME,
    f_FORMAT,
    f_FSROOT,
    f_ISRETRANSCODE,
    f_MAXMENKB,
    f_OPERATOR,
    f_PROGRESSIVESTATUS,
    f_REPOSITORYID,
    f_SIZE,
    f_SUBPATH,
    f_TRANSCODECMD,
    f_TYPE,
    f_VIDEOID
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_ID > prevMasVideoId)

        THEN

            SET @ID = f_ID;
            SET f_ID = f_ID + videostreamIdOffset;
            SET f_VIDEOID = f_VIDEOID + masVideoIdOffset;

            insert into trs_mas.mas_videostream
              (
                ID,
                CREATEDTIME,
                CREATEDUSER,
                CREATEDUSERID,
                CREATEDUSERNICKNAME,
                LASTMODIFIEDTIME,
                LASTMODIFIEDUSER,
                LASTMODIFIEDUSERID,
                ATTACHEDPIC,
                AUDIOBITRATE,
                AUDIOCHANNELS,
                AUDIOCODEC,
                AUDIOFORMAT,
                AUDIOSAMPLERATE,
                BITRATE,
                DEMUXER,
                DURATION,
                FPS,
                FRAMERATE,
                HEIGHT,
                mediaType,
                NBFRAMES,
                PIXELFORMAT,
                VIDEOCODEC,
                VIDEOFORMAT,
                VIDEOLEVEL,
                VIDEOPROFILE,
                WIDTH,
                CONSEQUENT,
                CONSOLEONLY,
                CONSUMERAPPID,
                CPUTIME,
                ELAPSEDSECONDS,
                FILEEXT,
                FILENAME,
                FORMAT,
                FSROOT,
                ISRETRANSCODE,
                MAXMENKB,
                OPERATOR,
                PROGRESSIVESTATUS,
                REPOSITORYID,
                SIZE,
                SUBPATH,
                TRANSCODECMD,
                TYPE,
                VIDEOID
              )
              VALUES
                (
                  f_ID,
                  f_CREATEDTIME,
                  f_CREATEDUSER,
                  f_CREATEDUSERID,
                  f_CREATEDUSERNICKNAME,
                  f_LASTMODIFIEDTIME,
                  f_LASTMODIFIEDUSER,
                  f_LASTMODIFIEDUSERID,
                  f_ATTACHEDPIC,
                  f_AUDIOBITRATE,
                  f_AUDIOCHANNELS,
                  f_AUDIOCODEC,
                  f_AUDIOFORMAT,
                  f_AUDIOSAMPLERATE,
                  f_BITRATE,
                  f_DEMUXER,
                  f_DURATION,
                  f_FPS,
                  f_FRAMERATE,
                  f_HEIGHT,
                  f_mediaType,
                  f_NBFRAMES,
                  f_PIXELFORMAT,
                  f_VIDEOCODEC,
                  f_VIDEOFORMAT,
                  f_VIDEOLEVEL,
                  f_VIDEOPROFILE,
                  f_WIDTH,
                  f_CONSEQUENT,
                  f_CONSOLEONLY,
                  f_CONSUMERAPPID,
                  f_CPUTIME,
                  f_ELAPSEDSECONDS,
                  f_FILEEXT,
                  f_FILENAME,
                  f_FORMAT,
                  f_FSROOT,
                  f_ISRETRANSCODE,
                  f_MAXMENKB,
                  f_OPERATOR,
                  f_PROGRESSIVESTATUS,
                  f_REPOSITORYID,
                  f_SIZE,
                  f_SUBPATH,
                  f_TRANSCODECMD,
                  f_TYPE,
                  f_VIDEOID
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, masVideoIdOffset, prevMasVideoId, @ID);

    END IF ;

		FETCH cur INTO
      f_ID,
      f_CREATEDTIME,
      f_CREATEDUSER,
      f_CREATEDUSERID,
      f_CREATEDUSERNICKNAME,
      f_LASTMODIFIEDTIME,
      f_LASTMODIFIEDUSER,
      f_LASTMODIFIEDUSERID,
      f_ATTACHEDPIC,
      f_AUDIOBITRATE,
      f_AUDIOCHANNELS,
      f_AUDIOCODEC,
      f_AUDIOFORMAT,
      f_AUDIOSAMPLERATE,
      f_BITRATE,
      f_DEMUXER,
      f_DURATION,
      f_FPS,
      f_FRAMERATE,
      f_HEIGHT,
      f_mediaType,
      f_NBFRAMES,
      f_PIXELFORMAT,
      f_VIDEOCODEC,
      f_VIDEOFORMAT,
      f_VIDEOLEVEL,
      f_VIDEOPROFILE,
      f_WIDTH,
      f_CONSEQUENT,
      f_CONSOLEONLY,
      f_CONSUMERAPPID,
      f_CPUTIME,
      f_ELAPSEDSECONDS,
      f_FILEEXT,
      f_FILENAME,
      f_FORMAT,
      f_FSROOT,
      f_ISRETRANSCODE,
      f_MAXMENKB,
      f_OPERATOR,
      f_PROGRESSIVESTATUS,
      f_REPOSITORYID,
      f_SIZE,
      f_SUBPATH,
      f_TRANSCODECMD,
      f_TYPE,
      f_VIDEOID
      ;
		
	END WHILE;
	
	CLOSE cur;

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

	END $$
DELIMITER ;