A MySql procedure to help migrate log entries from one table to another in REDCap

migrateLogEventTable

create
    definer = andy123@`10.130.0.0/255.255.0.0` procedure migrateLogEventTable(IN pid int, IN source_table varchar(255),
                                                                              IN target_table varchar(255))
p1:BEGIN

    set @pid = pid;
    set @source_table = source_table;
    set @target_table = target_table;

    -- MAKE SURE WE ARENT MOVING TO SAME TABLE!
    IF @source_table = @target_table THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'The Source and Target tables are the same';
    end if;

    -- GET CURRENT TABLE
    select log_event_table from redcap_projects where project_id = @pid INTO @current_table;

    -- SWITCH OVER PROJECT TO NEW LOG TABLE
    IF @current_table COLLATE 'utf8_unicode_ci' = @source_table THEN
        set @s3 = CONCAT('update redcap_projects set log_event_table = \'', @target_table, '\' where project_id = ', @pid);
        # select CONCAT('Switched over project ', @pid, ' to use ', @target_table);
        prepare stmt3 from @s3;
        execute stmt3;
        deallocate prepare stmt3;
    end if;

    -- START TRANSACTION
    start transaction;

    -- GET THE COUNT AND LATEST TIMESTAMP OF RECORDS IN THE SOURCE TABLE
    set @s1 = CONCAT('select',
        ' count(*), IFNULL(max(log_event_id),0)',
        ' from ', @source_table, ' where project_id = ', @pid,
        ' into @source_count, @source_max_id');
    #select @s1;
    prepare stmt1 from @s1;
    execute stmt1;
    deallocate prepare stmt1;


    -- INSERT RECORDS INTO THE TARGET TABLE
    IF @source_count > 0 THEN
        set @s2 = CONCAT('insert into ', @target_table, '
            (project_id,ts,user,ip,page,event,object_type,sql_log,pk,event_id,data_values,description,legacy,change_reason)
            select project_id,ts,user,ip,page,event,object_type,sql_log,pk,event_id,data_values,description,legacy,change_reason
            from ', @source_table, ' rle where rle.project_id = ', @pid,
            ' and rle.log_event_id <= ', @source_max_id,
            ' order by rle.log_event_id asc')
        ;
        prepare stmt2 from @s2;
        execute stmt2;
        SET @countRow1 = ROW_COUNT();
        deallocate prepare stmt2;
        #select CONCAT(@countRow1, ' records copied from ', @source_table, ' to ', @target_table);

        -- REMOVE RECORDS FROM SOURCE TABLE
        set @s7 = CONCAT('delete from ', @source_table, ' where project_id = ', @pid, ' and log_event_id <= ', @source_max_id);
        prepare stmt7 from @s7;
        execute stmt7;
        SET @countRow2 =  ROW_COUNT();
        deallocate prepare stmt7;
        #select CONCAT(@countRow2, ' records deleted from ', @source_table);
        select CONCAT(@countRow1, ' rows copied to ', @target_table, ' and ', @countRow2, ' rows deleted from ', @source_table) as `Result`;
    else
        select CONCAT('No rows for project ', @pid, ' in ', @source_table);
    end if;

    -- COMMIT OUR MOVE
    commit;

    -- GET SOURCE COUNT AFTER TO SEE IF INSERTS HAVE TAKEN PLACE SINCE STARTING TRANSACTION
#    set @s4 = CONCAT('select @source_count_new := count(*) as `Rows Remaining in Source` from ', @source_table, ' where project_id = ', @pid);
    set @s4 = CONCAT('select count(*) from ', @source_table, ' where project_id = ', @pid, ' into @source_count_new');
    prepare stmt4 from @s4;
    execute stmt4;
    deallocate prepare stmt4;

    IF @source_count_new > 0 THEN
        -- INSERT NEWLY ADDED RECORDS INTO THE TARGET TABLE
        select CONCAT_WS('', @source_count_new, ' new records were added since this began.  Please rerun to complete migration') as `RERUN NECESSARY`;
    end if;

END;