move_track_channel.sql

CREATE DEFINER=`mor`@`%` PROCEDURE `move_track_channel`(IN `s_id` INT, IN `s_target` VARCHAR(16), IN `s_index` INT)
    NO SQL
proc:BEGIN



SELECT `t_order`,`track_id` INTO @order, @id FROM `r_link` WHERE `unique_id` = s_target;



IF(@order = s_index) THEN

	LEAVE proc;

END IF;



SELECT `duration` INTO @duration FROM `r_tracks` WHERE `tid` = @id;



IF(s_index > @order) THEN

	UPDATE `r_link` SET `t_order` = `t_order` - 1, `time_offset` = `time_offset` - @duration

        WHERE `t_order` > @order AND `t_order` <= s_index AND `stream_id` = s_id

        ORDER BY `t_order` ASC;



    SELECT `time_offset`, `track_id` INTO @tmpOffset, @tmpTrackId FROM `r_link`

    	WHERE `t_order` = s_index - 1 AND `stream_id` = s_id LIMIT 1;



    SELECT `duration` INTO @tmpDuration FROM `r_tracks`

        WHERE `tid` = @tmpTrackId;



	SET @newOffset := @tmpOffset + @tmpDuration;

ELSE

	SELECT `time_offset` INTO @newOffset FROM `r_link` WHERE `t_order` = s_index AND `stream_id` = s_id;

	UPDATE `r_link` SET `t_order` = `t_order` + 1, `time_offset` = `time_offset` + @duration

        WHERE `t_order` >= s_index AND `t_order` < @order AND `stream_id` = s_id

        ORDER BY `t_order` ASC;

END IF;



UPDATE `r_link` SET `t_order` = s_index, `time_offset` = @newOffset WHERE `unique_id` = s_target;



END ;;
DELIMITER ;