Question: After adding versioned to a table which contained data, how can I set them as version 0?

[avilaton]

Hi,

first of all thanks for building this.

I have added versioned to some of the tables in my system which already contained data. I would like to store those versions of the data as version 0 in the supporting tables. How would you go about that?

Thanks

[ztane]

+1 especially for the native versioning.

[ztane]

For PostgreSQL native versioning and alembic, I ended up doing this in Alembic upgrade:

def create_initial_import_command(class_name, transaction_id):
    target_table = Base._decl_class_registry[class_name + 'Version'].__table__
    source_name = target_table.name.replace('_version', '')

    t_cols = []
    s_cols = []
    for c in list(target_table.columns):
        cname = c.name
        if cname.endswith('_mod'):
            s_cols.append('true')
        elif cname == 'transaction_id':
            s_cols.append(str(transaction_id))
        elif cname == 'end_transaction_id':
            s_cols.append('null')
        elif cname == 'operation_type':
            s_cols.append('0')  # insert
        else:
            s_cols.append('"%s"' % cname)

        t_cols.append('"%s"' % cname)

    command = 'INSERT INTO "{target_table}" ({target_columns}) ' \
              'SELECT {source_columns} FROM "{source_table}"'

    return command.format(
        target_table=target_table.name,
        source_table=source_name,
        target_columns=', '.join(t_cols),
        source_columns=', '.join(s_cols)
    )

...
    op.execute('INSERT INTO transaction (id) values (DEFAULT)')
    op.execute(create_initial_import_command('Foo', 1))
    op.execute(create_initial_import_command('Bar', 1))

Where 'Foo' and 'Bar' are declarative class names. This will insert a new transaction with default id (which presumably gets the id 1 as the table was newly created along with its SERIAL). This works with PostgreSQL native versioning (with PropertyModTrackerPlugin), and possibly with some other configuration.

[wodow]

A quick fix is possible: iterate over all records and use sqlalchemy.orm.attributes.flag_modified() [1] to mark any field as dirty.

Easier to run after and outside of the Alembic migration that creates the version table.

[1] http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.attributes.flag_modified

[beenje]

I tried using sqlalchemy.orm.attributes.flag_dirty(instance) on all records but that doesn't seem to trigger anything...

Anyone who has a working recipe?

[beenje]

OK, I don't know why I went for flag_dirty instead of flag_modified as suggested. I confirm that flag_modified works as expected.

[beenje]

If it can help, I ended doing the following that can be run inside the Alembic migration:

from alembic import op

def create_transaction():
    """Create a new transaction and return the id"""
    conn = op.get_bind()
    conn.execute("INSERT INTO transaction (issued_at) values (now())")
    result = conn.execute("SELECT id FROM transaction ORDER BY issued_at DESC LIMIT 1")
    return result.fetchone()[0]

def create_initial_version(version_table, transaction_id):
    conn = op.get_bind()
    # Get all values from the source table
    source_table = version_table.name.replace("_version", "")
    result = conn.execute(f"SELECT * from {source_table}")
    values = [{key: value for key, value in row.items()} for row in result]
    # Add the transaction_id and operation_type
    for d in values:
        d["transaction_id"] = transaction_id
        # INSERT = 0 (sqlalchemy_continuum/operation.py)
        d["operation_type"] = 0
    op.bulk_insert(version_table, values)

def upgrade():
    my_table_version = op.create_table(
        "my_table_version",
        sa.Column(....
    )
    transaction_id = create_transaction()
    create_initial_version(my_table_version, transaction_id)