todo.sr.ht

~sylvain

#92 post apply hook not called on rollback 1 year, 11 months ago

Ticket created by ~sylvain on ~olly/yoyo

On rollback the 'post-apply.py' is not called, so rollback can't rely on it to recreate VIEWs for example.

May be the documentation should explain that, or a config or something to enable it.

Workaround, add it manually a the end of rollback step:

WARNING: rollback steps are played in reverse order, so the 'end of the rollback' is the first defined step.

A sample of code (See #91 for the whole generated multi-step code)

def recreate_all_views_step_07(conn):
    cursor = conn.cursor()
    sql_queries = ["""SQL CREATE VIEW goes HERE""" ]
    for q in sql_queries:
        cursor.execute(q)

# steps SQL code is ommited apply_steps{} and rollback_steps{} dict of SQL
# forward step is relying on post-apply.py so no step for recreating all views
steps = [
	step(apply_steps['drop_all_views_00_sql0'],              recreate_all_views_step_07),
	step(apply_steps['drop_all_views_00_sql1'],              rollback_steps['drop_table_06_sql0']),
	step(apply_steps['drop_all_views_00_sql2'],              rollback_steps['rename_table_05_sql0']),
	step(apply_steps['create_backup_table_01_sql0'],         rollback_steps['drop_table_04_sql0']),
	step(apply_steps['ensure_drop_table_if_exists_02_sql0'], rollback_steps['copy_table_03_sql0']),
	step(apply_steps['create_table_03_sql0'],                rollback_steps['create_table_02_sql0']),
	step(apply_steps['remove_duplicate_04_sql0'],            rollback_steps['ensure_drop_table_if_exists_01_sql0']),
	step(apply_steps['copy_table_05_sql0'],                  rollback_steps['drop_all_views_00_sql2']),
	step(apply_steps['drop_table_06_sql0'],                  rollback_steps['drop_all_views_00_sql1']),
	step(apply_steps['rename_table_07_sql0'],                rollback_steps['drop_all_views_00_sql0']),
]

my sample post-apply.py hook for completeness and understanding

"""
recreate all views from sql/views/*,sql folder
"""

import os
import sys
sys.path.append('.')

from dump_sql_views import get_sql_views_from_file

def create_all_views(conn):
    cursor = conn.cursor()
    for create_view in get_sql_views_from_file(if_not_exists=True).values():
       cursor.execute(create_view)

from yoyo import step

__depends__ = {}

steps = [
    step(create_all_views),
]

#91 rollback step in reverse order and sqlite alter foreign_key constraint 1 year, 11 months ago

Comment by ~sylvain on ~olly/yoyo

Hello,

I finally coded some step generator, for recreating a table with a new definition and keeping a backup for rollback. And re-injecting existing data from original table definition (eventually with transformation).

The goal in this case is to add a UNIQUE constraint in a sqlite table, which is not supported as a single ALTER TABLE (see initial issue description).

My input code is as follow:

mysteps = []
mysteps.append(drop_all_views())
mysteps.append(create_backup_table('user_settings'))
mysteps.append(ensure_drop_table_if_exists('user_settings'))
mysteps.append(create_table('user_settings', 'new', new_create_table_sql))
mysteps.append(remove_duplicate('user_settings', columns_compare='user_id, settings_id'))
mysteps.append(copy_table('user_settings', to_table='new'))
mysteps.append(drop_table('user_settings'))
mysteps.append(rename_table('user_settings', from_table='new'))

# ========================== rollback steps ======================================

rollback_steps = []
rollback_steps.append(drop_all_views())
rollback_steps.append(ensure_drop_table_if_exists('user_settings', suffix='old'))
rollback_steps.append(create_table('user_settings', 'old', get_create_table(db, 'user_settings')))
rollback_steps.append(copy_table('user_settings', from_table='bak', to_table='old'))
rollback_steps.append(drop_table('user_settings'))
rollback_steps.append(rename_table('user_settings', from_table='old', to_table=None))
rollback_steps.append(drop_table('user_settings', suffix='bak'))

I use this code as a base generator to output fixed (commit-able) steps for my table changes. (It could be a class for generating an alter table in sqlite by recreating the table). The rollback_steps are not deduced from forward step, it's not so simple, and makes the code a bit too complex (I tried).

I also tried to generate the yoyo steps as merging the code above into the final steps = [] assignment. So keeping SQL changes mostly hidden and importing my generator code as dependency.

But I realized it was also not quite readable (as generated code), and that I would prefer to have the full fixed SQL as output, in a human readable fashion. So I finally generated the ordered SQL first, then the combined steps = [] assignment plus reversed rollback.

The advantage, for me, is to keep a fixed in time SQL migration, not linked to a possibly changing code. This means that if the steps_generator evolves in time, the generated steps, at the time the migration has been done, is still fixed as it has been injected is SQL.

My actual generator produces from the above coded definition:

# =================================== apply_steps ======================================

apply_steps = {
    # - - - - - - - - - - - - drop_all_views()
    "drop_all_views_00_sql0" : """
    DROP VIEW IF EXISTS shopping_item_with_quantity
    """,
    "drop_all_views_00_sql1" : """
    DROP VIEW IF EXISTS shopping_list_item_changed
    """,
    "drop_all_views_00_sql2" : """
    DROP VIEW IF EXISTS recipe_show_match_ingredient
    """,
    # - - - - - - - - - - - - create_backup_table(table='user_settings', suffix='bak')
    "create_backup_table_01_sql0" : """
    -- copy actual table as backup for rollback
    -- the backup is kept over the migration
    CREATE TABLE user_settings_bak AS SELECT * FROM user_settings
    """,
    # - - - - - - - - - - - - ensure_drop_table_if_exists(table='user_settings', suffix='new')
    "ensure_drop_table_if_exists_02_sql0" : """
    DROP TABLE IF EXISTS "user_settings_new"
    """,
    # - - - - - - - - - - - - create_table(table='user_settings', suffix='new', sql='CREATE ...')
    "create_table_03_sql0" : """
    CREATE TABLE user_settings_new (
    id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    settings_id INTEGER NOT NULL,
    value_for_user VARCHAR,
    PRIMARY KEY (id),

    CONSTRAINT fk_user_settings_user_id
      FOREIGN KEY (user_id)
      REFERENCES user(id)
      ON DELETE CASCADE,
    CONSTRAINT fk_user_settings_settings_id
      FOREIGN KEY (settings_id)
      REFERENCES settings(id)
      ON DELETE CASCADE,

    -- add new UNIQUE constraint:
    UNIQUE(user_id, settings_id)
)
    """,
    # - - - - - - - - - - - - remove_duplicate(table='user_settings', columns_compare='user_id, settings_id')
    "remove_duplicate_04_sql0" : """
    DELETE FROM user_settings WHERE id IN (
        SELECT id FROM
        ( -- List the rows that will be deleted as duplicate
    SELECT *
    FROM user_settings AS t1
    WHERE EXISTS (
        SELECT 1
        FROM user_settings AS t2
        WHERE t1.user_id = t2.user_id AND t1.settings_id = t2.settings_id
        AND t2.id > t1.id
    ) )
    )
    """,
    # - - - - - - - - - - - - copy_table(table='user_settings', from_table=None, to_table='new')
    "copy_table_05_sql0" : """
    -- populating the new table from existing data
    INSERT INTO user_settings_new
    (
        id,user_id,settings_id,value_for_user
    )
    SELECT
        -- keep same id
        id,user_id,settings_id,value_for_user
    FROM user_settings
    """,
    # - - - - - - - - - - - - drop_table(table='user_settings', suffix=None)
    "drop_table_06_sql0" : """
    DROP TABLE user_settings
    """,
    # - - - - - - - - - - - - rename_table(table='user_settings', from_table='new', to_table=None)
    "rename_table_07_sql0" : """
    ALTER TABLE user_settings_new RENAME TO user_settings
    """,
}

# =================================== rollback_steps ======================================

rollback_steps = {
    # - - - - - - - - - - - - drop_all_views()
    "drop_all_views_00_sql0" : """
    DROP VIEW IF EXISTS shopping_item_with_quantity
    """,
    "drop_all_views_00_sql1" : """
    DROP VIEW IF EXISTS shopping_list_item_changed
    """,
    "drop_all_views_00_sql2" : """
    DROP VIEW IF EXISTS recipe_show_match_ingredient
    """,
    # - - - - - - - - - - - - ensure_drop_table_if_exists(table='user_settings', suffix='old')
    "ensure_drop_table_if_exists_01_sql0" : """
    DROP TABLE IF EXISTS "user_settings_old"
    """,
    # - - - - - - - - - - - - create_table(table='user_settings', suffix='old', sql='CREATE ...')
    "create_table_02_sql0" : """
    CREATE TABLE user_settings_old (
        id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        settings_id INTEGER NOT NULL,
        value_for_user VARCHAR,
        PRIMARY KEY (id),

        CONSTRAINT fk_user_settings_user_id
          FOREIGN KEY (user_id)
          REFERENCES user(id)
          ON DELETE CASCADE,
        CONSTRAINT fk_user_settings_settings_id
          FOREIGN KEY (settings_id)
          REFERENCES settings(id)
          ON DELETE CASCADE
    )
    """,
    # - - - - - - - - - - - - copy_table(table='user_settings', from_table='bak', to_table='old')
    "copy_table_03_sql0" : """
    -- populating the new table from existing data
    INSERT INTO user_settings_old
    (
        id,user_id,settings_id,value_for_user
    )
    SELECT
        -- keep same id
        id,user_id,settings_id,value_for_user
    FROM user_settings_bak
    """,
    # - - - - - - - - - - - - drop_table(table='user_settings', suffix=None)
    "drop_table_04_sql0" : """
    DROP TABLE user_settings
    """,
    # - - - - - - - - - - - - rename_table(table='user_settings', from_table='old', to_table=None)
    "rename_table_05_sql0" : """
    ALTER TABLE user_settings_old RENAME TO user_settings
    """,
    # - - - - - - - - - - - - drop_table(table='user_settings', suffix='bak')
    "drop_table_06_sql0" : """
    DROP TABLE user_settings_bak
    """,
}
steps = [
	step(apply_steps['drop_all_views_00_sql0']),
	step(apply_steps['drop_all_views_00_sql1'],              rollback_steps['drop_table_06_sql0']),
	step(apply_steps['drop_all_views_00_sql2'],              rollback_steps['rename_table_05_sql0']),
	step(apply_steps['create_backup_table_01_sql0'],         rollback_steps['drop_table_04_sql0']),
	step(apply_steps['ensure_drop_table_if_exists_02_sql0'], rollback_steps['copy_table_03_sql0']),
	step(apply_steps['create_table_03_sql0'],                rollback_steps['create_table_02_sql0']),
	step(apply_steps['remove_duplicate_04_sql0'],            rollback_steps['ensure_drop_table_if_exists_01_sql0']),
	step(apply_steps['copy_table_05_sql0'],                  rollback_steps['drop_all_views_00_sql2']),
	step(apply_steps['drop_table_06_sql0'],                  rollback_steps['drop_all_views_00_sql1']),
	step(apply_steps['rename_table_07_sql0'],                rollback_steps['drop_all_views_00_sql0']),
]

Answering my own question "adding my own code in a migration step", here an example of migrations/post-apply.py:

"""                                                                                                                                                  
recreate all views from sql/views/*.sql 
"""

import os
import sys
sys.path.append('.')

from dump_sql_views import get_sql_views

def create_all_views(conn):
    cursor = conn.cursor()
    for create_view in get_sql_views(if_not_exists=True).values():
       cursor.execute(create_view)

from yoyo import step

__depends__ = {}

steps = [
    step(create_all_views),
]

As you can see the steps = [] assignment order for a 8 + 7 step become more clear (still not related step by step). So I'm asking myself the benefit of keeping step and rollback step correlated as a single step() call?

If you're interested in my code generator, comment here, I will publish it. It's actually a PoC and not very coders friendly. ;-)

Regards, Sylvain.

#91 rollback step in reverse order and sqlite alter foreign_key constraint 1 year, 11 months ago

Ticket created by ~sylvain on ~olly/yoyo

Hello,

I've found your sql migration tools, which is quite interesting! So I'm using it for a personal project, handling a small SQL DB in sqlite3 and python flask without ORM for managing recipes and shopping_list.

I was first handling my migration by storing them in raw sql files and injecting via sqlite command line.

sqlite for my project is sufficient so I don't plan to move to anything else for now and sqlite engine in 2022 includes a lot of feature including foreign_keys etc.

However adding more constraint on an existing table is much more difficult than a simple ALTER TABLE. See: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

But still fully doable by copy to a new table with inserting new data from the old table. This is giving full sens of implementing migration as the dev DB is different of the prod DB in term of data. Copy the whole table and rename it after is manageble with low data volume I suppose.

So comes yoyo and the step concept.

Here's my step for creating a new foreign_key constraint on my existing table:
"""
add user_id column to shopping_current
we recreate the whole table because of fk_shopping_current_user_id
rollback step are played in reverse order
"""

from yoyo import step

__depends__ = {'20221124_01_xhhbH-create-user-table'}

steps = [
    step("""
    -- copy actual table as backup for rollback
    -- the backup is kept over the migration
    CREATE TABLE shopping_current_bak AS SELECT * FROM shopping_current
    """,
    """
    DROP TABLE shopping_current_bak
    """ ),
    step("""
    DROP TABLE IF EXISTS "shopping_current_new"
    """,
    """
    -- RENAME
    ALTER TABLE shopping_current_restore RENAME TO shopping_current
    """),
    step("""
    CREATE TABLE "shopping_current_new" (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        value VARCHAR,
        -- new foreignkey on user
        user_id INTEGER NOT NULL,
        PRIMARY KEY (id),

        FOREIGN KEY(user_id) REFERENCES "user" (id),
        CONSTRAINT fk_shopping_current_user_id
          FOREIGN KEY (user_id)
          REFERENCES user(id)
          ON DELETE CASCADE
    )
    """,
    """
    -- on rollback we also DROP original table, and recreate the old one from backup
    DROP TABLE shopping_current
    """
    ),
    step("""
    -- populating the new table
    INSERT INTO shopping_current_new
    (
        id,
        name,
        value,
        user_id
    )
    SELECT
        NULL,
        c.name,
        c.value,
        u.id
    FROM shopping_current AS c
    -- join without ON so we get cross product for all user
    JOIN user AS u
    """,
    """
    INSERT INTO shopping_current_restore
    (
        id,
        name,
        value
    )
    SELECT
        id,
        name,
        value
    -- select from backup
    FROM shopping_current_bak
    """),
    step("""
    -- DROP original table
    DROP TABLE shopping_current
    """,
    """
    -- re-create old schema
    CREATE TABLE "shopping_current_restore" (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        value VARCHAR,
        PRIMARY KEY (id)
    )
    """
    ),
    step("""
    -- RENAME
    ALTER TABLE shopping_current_new RENAME TO shopping_current
    """,
    """
    DROP TABLE IF EXISTS "shopping_current_restore"
    """
    ),
]
I discovered by using it, that the rollback steps are played in reverse order. It doesn't seems to be mentioned anywhere in the doc. https://ollycope.com/software/yoyo/latest/

This link should point to the code where the steps are read reversed: https://hg.sr.ht/~olly/yoyo/browse/yoyo/migrations.py?rev=tip#L248

While it seems quite logical, it's quite difficult to read on a 6 step migration. I'm planing a longer step migration and it impose me to jump up and down in my migration code. As you can read the steps aren't fully correlated to the SQL code in the forward step.

Now, I'm planning to import some function to avoid recoding all those merely same steps each time I need to change the constraints on my table. I don't know yet how to import python's code in the steps. It seems I've to play with sys.path so yoyo can find it.

Could you point me to some python import example / good practice in yoyo migrations steps file?

Regards, Sylvain.