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

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

    DECLARE migr_table_name varchar(255) DEFAULT 'mas_masvideo';

    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_AUTHOR varchar(255) DEFAULT NULL;
    DECLARE  f_AUTOCLIPSTATUS int(11) DEFAULT NULL;
    DECLARE  f_BEGINPROCDSSINGTIME bigint(20) DEFAULT NULL;
    DECLARE  f_CAITIMES int(11) DEFAULT NULL;
    DECLARE  f_CANCOMMENT bit(1) DEFAULT NULL;
    DECLARE  f_CATEGORYDN varchar(255) DEFAULT NULL;
    DECLARE  f_CATEGORYID int(11) DEFAULT NULL;
    DECLARE  f_CATEGORYNAME varchar(255) DEFAULT NULL;
    DECLARE  f_COMMENTCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_CONSUMERAPP varchar(255) DEFAULT NULL;
    DECLARE  f_CONSUMERAPPID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDAPPID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDMANNER varchar(255) DEFAULT NULL;
    DECLARE  f_CURRWEEKCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_CURRWEEKPUBLISHVODCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_DINGTIMES int(11) DEFAULT NULL;
    DECLARE  f_ELAPSEDSECONDS int(11) DEFAULT NULL;
    DECLARE  f_ELAPSEDSECONDSB int(11) DEFAULT NULL;
    DECLARE  f_ENDTIME double DEFAULT NULL;
    DECLARE  f_FAILCOUNTBYPUBLISH int(11) DEFAULT NULL;
    DECLARE  f_FILEEXT varchar(255) DEFAULT NULL;
    DECLARE  f_FILENAME varchar(255) DEFAULT NULL;
    DECLARE  f_FILENAMEBASE varchar(255) DEFAULT NULL;
    DECLARE  f_FILTERWORDS varchar(255) DEFAULT NULL;
    DECLARE  f_FSROOT varchar(255) DEFAULT NULL;
    DECLARE  f_INUSEBYTHIRDAPP bit(1) DEFAULT NULL;
    DECLARE  f_INCPLAYCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_INTRO varchar(1024) DEFAULT NULL;
    DECLARE  f_LANG varchar(255) DEFAULT NULL;
    DECLARE  f_MTYPE varchar(255) DEFAULT NULL;
    DECLARE  f_NAME varchar(255) DEFAULT NULL;
    DECLARE  f_ORIGINFILENAME varchar(255) DEFAULT NULL;
    DECLARE  f_PARENTVIDEOID int(11) DEFAULT NULL;
    DECLARE  f_PEOPLE varchar(255) DEFAULT NULL;
    DECLARE  f_PLAYCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_PLAYERRORCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_PRESTATUS varchar(255) DEFAULT NULL;
    DECLARE  f_PROCESSSTATUS varchar(255) DEFAULT NULL;
    DECLARE  f_PUBLISHFAILINFO varchar(255) DEFAULT NULL;
    DECLARE  f_PUBLISHVODCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_PUSHCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_PUSHTARGETCOUNT bigint(20) DEFAULT NULL;
    DECLARE  f_RANK double DEFAULT NULL;
    DECLARE  f_RATETIMES int(11) DEFAULT NULL;
    DECLARE  f_REFERENCED varchar(255) DEFAULT NULL;
    DECLARE  f_REGION varchar(255) DEFAULT NULL;
    DECLARE  f_REJECTREASONID int(11) DEFAULT NULL;
    DECLARE  f_REPOSITORYID int(11) DEFAULT NULL;
    DECLARE  f_SAMPLEID int(11) DEFAULT NULL;
    DECLARE  f_SCORE int(11) DEFAULT NULL;
    DECLARE  f_SIZE bigint(20) DEFAULT NULL;
    DECLARE  f_SOURCE varchar(255) DEFAULT NULL;
    DECLARE  f_SOURCEVIDEOID int(11) DEFAULT NULL;
    DECLARE  f_STARTTIME double DEFAULT NULL;
    DECLARE  f_STATUS varchar(255) DEFAULT NULL;
    DECLARE  f_STATUSDETAIL varchar(255) DEFAULT NULL;
    DECLARE  f_STREAMID int(11) DEFAULT NULL;
    DECLARE  f_STREAMS varchar(255) DEFAULT NULL;
    DECLARE  f_SUBPATH varchar(255) DEFAULT NULL;
    DECLARE  f_SUBTITLE mediumtext;
    DECLARE  f_TAGS varchar(512) DEFAULT NULL;
    DECLARE  f_TARGETID int(11) DEFAULT NULL;
    DECLARE  f_THUMBNAME varchar(255) DEFAULT NULL;
    DECLARE  f_THUMBURL varchar(255) DEFAULT NULL;
    DECLARE  f_THUMBS varchar(255) DEFAULT NULL;
    DECLARE  f_TITLE varchar(512) DEFAULT NULL;
    DECLARE  f_TYPE int(11) DEFAULT NULL;
    DECLARE  f_TYPENAMES varchar(255) DEFAULT NULL;
    DECLARE  f_UNPASSREASON varchar(255) DEFAULT NULL;
    DECLARE  f_UNPASSTYPE int(11) DEFAULT NULL;
    DECLARE  f_VIDEOURL varchar(255) DEFAULT NULL;
    DECLARE  f_YEAR varchar(255) 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,
      AUTHOR,
      AUTOCLIPSTATUS,
      BEGINPROCDSSINGTIME,
      CAITIMES,
      CANCOMMENT,
      CATEGORYDN,
      CATEGORYID,
      CATEGORYNAME,
      COMMENTCOUNT,
      CONSUMERAPP,
      CONSUMERAPPID,
      CREATEDAPPID,
      CREATEDMANNER,
      CURRWEEKCOUNT,
      CURRWEEKPUBLISHVODCOUNT,
      DINGTIMES,
      ELAPSEDSECONDS,
      ELAPSEDSECONDSB,
      ENDTIME,
      FAILCOUNTBYPUBLISH,
      FILEEXT,
      FILENAME,
      FILENAMEBASE,
      FILTERWORDS,
      FSROOT,
      INUSEBYTHIRDAPP,
      INCPLAYCOUNT,
      INTRO,
      LANG,
      MTYPE,
      NAME,
      ORIGINFILENAME,
      PARENTVIDEOID,
      PEOPLE,
      PLAYCOUNT,
      PLAYERRORCOUNT,
      PRESTATUS,
      PROCESSSTATUS,
      PUBLISHFAILINFO,
      PUBLISHVODCOUNT,
      PUSHCOUNT,
      PUSHTARGETCOUNT,
      RANK,
      RATETIMES,
      REFERENCED,
      REGION,
      REJECTREASONID,
      REPOSITORYID,
      SAMPLEID,
      SCORE,
      SIZE,
      SOURCE,
      SOURCEVIDEOID,
      STARTTIME,
      STATUS,
      STATUSDETAIL,
      STREAMID,
      STREAMS,
      SUBPATH,
      SUBTITLE,
      TAGS,
      TARGETID,
      THUMBNAME,
      THUMBURL,
      THUMBS,
      TITLE,
      TYPE,
      TYPENAMES,
      UNPASSREASON,
      UNPASSTYPE,
      VIDEOURL,
      YEAR
    FROM mas_masvideo
		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_AUTHOR,
    f_AUTOCLIPSTATUS,
    f_BEGINPROCDSSINGTIME,
    f_CAITIMES,
    f_CANCOMMENT,
    f_CATEGORYDN,
    f_CATEGORYID,
    f_CATEGORYNAME,
    f_COMMENTCOUNT,
    f_CONSUMERAPP,
    f_CONSUMERAPPID,
    f_CREATEDAPPID,
    f_CREATEDMANNER,
    f_CURRWEEKCOUNT,
    f_CURRWEEKPUBLISHVODCOUNT,
    f_DINGTIMES,
    f_ELAPSEDSECONDS,
    f_ELAPSEDSECONDSB,
    f_ENDTIME,
    f_FAILCOUNTBYPUBLISH,
    f_FILEEXT,
    f_FILENAME,
    f_FILENAMEBASE,
    f_FILTERWORDS,
    f_FSROOT,
    f_INUSEBYTHIRDAPP,
    f_INCPLAYCOUNT,
    f_INTRO,
    f_LANG,
    f_MTYPE,
    f_NAME,
    f_ORIGINFILENAME,
    f_PARENTVIDEOID,
    f_PEOPLE,
    f_PLAYCOUNT,
    f_PLAYERRORCOUNT,
    f_PRESTATUS,
    f_PROCESSSTATUS,
    f_PUBLISHFAILINFO,
    f_PUBLISHVODCOUNT,
    f_PUSHCOUNT,
    f_PUSHTARGETCOUNT,
    f_RANK,
    f_RATETIMES,
    f_REFERENCED,
    f_REGION,
    f_REJECTREASONID,
    f_REPOSITORYID,
    f_SAMPLEID,
    f_SCORE,
    f_SIZE,
    f_SOURCE,
    f_SOURCEVIDEOID,
    f_STARTTIME,
    f_STATUS,
    f_STATUSDETAIL,
    f_STREAMID,
    f_STREAMS,
    f_SUBPATH,
    f_SUBTITLE,
    f_TAGS,
    f_TARGETID,
    f_THUMBNAME,
    f_THUMBURL,
    f_THUMBS,
    f_TITLE,
    f_TYPE,
    f_TYPENAMES,
    f_UNPASSREASON,
    f_UNPASSTYPE,
    f_VIDEOURL,
    f_YEAR
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_ID > prevMasVideoId)

        THEN

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

            insert into trs_mas.mas_masvideo
              (
                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,
                AUTHOR,
                AUTOCLIPSTATUS,
                BEGINPROCDSSINGTIME,
                CAITIMES,
                CANCOMMENT,
                CATEGORYDN,
                CATEGORYID,
                CATEGORYNAME,
                COMMENTCOUNT,
                CONSUMERAPP,
                CONSUMERAPPID,
                CREATEDAPPID,
                CREATEDMANNER,
                CURRWEEKCOUNT,
                CURRWEEKPUBLISHVODCOUNT,
                DINGTIMES,
                ELAPSEDSECONDS,
                ELAPSEDSECONDSB,
                ENDTIME,
                FAILCOUNTBYPUBLISH,
                FILEEXT,
                FILENAME,
                FILENAMEBASE,
                FILTERWORDS,
                FSROOT,
                INUSEBYTHIRDAPP,
                INCPLAYCOUNT,
                INTRO,
                LANG,
                MTYPE,
                NAME,
                ORIGINFILENAME,
                PARENTVIDEOID,
                PEOPLE,
                PLAYCOUNT,
                PLAYERRORCOUNT,
                PRESTATUS,
                PROCESSSTATUS,
                PUBLISHFAILINFO,
                PUBLISHVODCOUNT,
                PUSHCOUNT,
                PUSHTARGETCOUNT,
                RANK,
                RATETIMES,
                REFERENCED,
                REGION,
                REJECTREASONID,
                REPOSITORYID,
                SAMPLEID,
                SCORE,
                SIZE,
                SOURCE,
                SOURCEVIDEOID,
                STARTTIME,
                STATUS,
                STATUSDETAIL,
                STREAMID,
                STREAMS,
                SUBPATH,
                SUBTITLE,
                TAGS,
                TARGETID,
                THUMBNAME,
                THUMBURL,
                THUMBS,
                TITLE,
                TYPE,
                TYPENAMES,
                UNPASSREASON,
                UNPASSTYPE,
                VIDEOURL,
                YEAR
              )
              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_AUTHOR,
                  f_AUTOCLIPSTATUS,
                  f_BEGINPROCDSSINGTIME,
                  f_CAITIMES,
                  f_CANCOMMENT,
                  f_CATEGORYDN,
                  f_CATEGORYID,
                  f_CATEGORYNAME,
                  f_COMMENTCOUNT,
                  f_CONSUMERAPP,
                  f_CONSUMERAPPID,
                  f_CREATEDAPPID,
                  f_CREATEDMANNER,
                  f_CURRWEEKCOUNT,
                  f_CURRWEEKPUBLISHVODCOUNT,
                  f_DINGTIMES,
                  f_ELAPSEDSECONDS,
                  f_ELAPSEDSECONDSB,
                  f_ENDTIME,
                  f_FAILCOUNTBYPUBLISH,
                  f_FILEEXT,
                  f_FILENAME,
                  f_FILENAMEBASE,
                  f_FILTERWORDS,
                  f_FSROOT,
                  f_INUSEBYTHIRDAPP,
                  f_INCPLAYCOUNT,
                  f_INTRO,
                  f_LANG,
                  f_MTYPE,
                  f_NAME,
                  f_ORIGINFILENAME,
                  f_PARENTVIDEOID,
                  f_PEOPLE,
                  f_PLAYCOUNT,
                  f_PLAYERRORCOUNT,
                  f_PRESTATUS,
                  f_PROCESSSTATUS,
                  f_PUBLISHFAILINFO,
                  f_PUBLISHVODCOUNT,
                  f_PUSHCOUNT,
                  f_PUSHTARGETCOUNT,
                  f_RANK,
                  f_RATETIMES,
                  f_REFERENCED,
                  f_REGION,
                  f_REJECTREASONID,
                  f_REPOSITORYID,
                  f_SAMPLEID,
                  f_SCORE,
                  f_SIZE,
                  f_SOURCE,
                  f_SOURCEVIDEOID,
                  f_STARTTIME,
                  f_STATUS,
                  f_STATUSDETAIL,
                  f_STREAMID,
                  f_STREAMS,
                  f_SUBPATH,
                  f_SUBTITLE,
                  f_TAGS,
                  f_TARGETID,
                  f_THUMBNAME,
                  f_THUMBURL,
                  f_THUMBS,
                  f_TITLE,
                  f_TYPE,
                  f_TYPENAMES,
                  f_UNPASSREASON,
                  f_UNPASSTYPE,
                  f_VIDEOURL,
                  f_YEAR
                );

          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_AUTHOR,
      f_AUTOCLIPSTATUS,
      f_BEGINPROCDSSINGTIME,
      f_CAITIMES,
      f_CANCOMMENT,
      f_CATEGORYDN,
      f_CATEGORYID,
      f_CATEGORYNAME,
      f_COMMENTCOUNT,
      f_CONSUMERAPP,
      f_CONSUMERAPPID,
      f_CREATEDAPPID,
      f_CREATEDMANNER,
      f_CURRWEEKCOUNT,
      f_CURRWEEKPUBLISHVODCOUNT,
      f_DINGTIMES,
      f_ELAPSEDSECONDS,
      f_ELAPSEDSECONDSB,
      f_ENDTIME,
      f_FAILCOUNTBYPUBLISH,
      f_FILEEXT,
      f_FILENAME,
      f_FILENAMEBASE,
      f_FILTERWORDS,
      f_FSROOT,
      f_INUSEBYTHIRDAPP,
      f_INCPLAYCOUNT,
      f_INTRO,
      f_LANG,
      f_MTYPE,
      f_NAME,
      f_ORIGINFILENAME,
      f_PARENTVIDEOID,
      f_PEOPLE,
      f_PLAYCOUNT,
      f_PLAYERRORCOUNT,
      f_PRESTATUS,
      f_PROCESSSTATUS,
      f_PUBLISHFAILINFO,
      f_PUBLISHVODCOUNT,
      f_PUSHCOUNT,
      f_PUSHTARGETCOUNT,
      f_RANK,
      f_RATETIMES,
      f_REFERENCED,
      f_REGION,
      f_REJECTREASONID,
      f_REPOSITORYID,
      f_SAMPLEID,
      f_SCORE,
      f_SIZE,
      f_SOURCE,
      f_SOURCEVIDEOID,
      f_STARTTIME,
      f_STATUS,
      f_STATUSDETAIL,
      f_STREAMID,
      f_STREAMS,
      f_SUBPATH,
      f_SUBTITLE,
      f_TAGS,
      f_TARGETID,
      f_THUMBNAME,
      f_THUMBURL,
      f_THUMBS,
      f_TITLE,
      f_TYPE,
      f_TYPENAMES,
      f_UNPASSREASON,
      f_UNPASSTYPE,
      f_VIDEOURL,
      f_YEAR
      ;
		
	END WHILE;
	
	CLOSE cur;

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

	END $$
DELIMITER ;