gistfile1.sql

CREATE TABLE {{ table.name }}_post_{{ issue }}
LIKE {{ table.name }};

-- Your alterations here.

CREATE TRIGGER ai_{{ table.name }}
AFTER INSERT
FOR EACH ROW
    REPLACE INTO {{ table.name }}_post_{{ issue }} (
{% for column in table.columns %}
        {{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
    ) VALUES (
        -- Your alterations here.
{% for column in table.columns %}
        NEW.{{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
    );

CREATE TRIGGER au_{{ table.name }}
AFTER UPDATE
FOR EACH ROW
    REPLACE INTO {{ table.name }}_post_{{ issue }} (
{% for column in table.columns %}
        {{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
    ) VALUES (
        -- Your alterations here.
{% for column in table.columns %}
        NEW.{{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
    );

CREATE TRIGGER ad_{{ table.name }}
AFTER DELETE
FOR EACH ROW
    DELETE FROM {{ table.name }}_post_{{ issue }}
    WHERE
{% for column in table.keys.PRIMARY.columns %}
        {% if not loop.first %}AND {% endif %}{{ column.name }} = OLD.{{ column.name }}
{% endfor %}
;

DELIMITER !!

CREATE PROCEDURE migrate_{{ table.name }}()
BEGIN
    DECLARE done INT DEFAULT 0;
    
{% for column in table.keys.PRIMARY.columns %}
    DECLARE row_{{ column.name }} {{ column.type }};
{% endfor %}
    
    DECLARE original_{{ table.name }} CURSOR FOR
    SELECT
{% for column in table.keys.PRIMARY.columns %}
        {{column.name}}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
    FROM {{table.name}};
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;
    
    OPEN original_{{ table.name }};
    
    copy_loop: LOOP
        FETCH original_{{ table.name }} INTO
{% for column in table.keys.PRIMARY.columns %}
            row_{{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
        ;
        IF done THEN
          LEAVE copy_loop;
        END IF;
        
        REPLACE INTO {{ table.name }}_post_{{ issue }}
        (
{% for column in table.columns %}
            {{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
        )
        SELECT 
            -- Your alterations here.
{% for column in table.columns %}
            {{ column.name }}
{%- if not loop.last -%}
,
{%- endif %}

{% endfor %}
        FROM {{ table.name }}
        WHERE
{% for column in table.keys.PRIMARY.columns %}
            {% if not loop.first %}AND {% endif %}{{ column.name }} = row_{{ column.name }}
{% endfor %}
        ;
    END LOOP;
END
!!

DELIMITER ;

CALL migrate_{{ table.name }}();

RENAME TABLE
    {{ table.name }} TO {{ table.name }}_pre_{{ issue }},
    {{ table.name }}_post_{{ issue }} TO {{ table.name }};

DROP PROCEDURE migrate_{{ table.name }};

DROP TRIGGER ai_{{ table.name }};
DROP TRIGGER au_{{ table.name }};
DROP TRIGGER ad_{{ table.name }};

DROP TABLE {{ table.name }}_pre_{{ issue }};