CODEWITHSUNDEEP
pythonpython-2.7sqlalchemyflask-sqlalchemy
Ellochka Cannibal
Ellochka Cannibal

Reputation: 1790

SqlAlchemy add tables versioning to existing tables

Imagine that I have one table in my project with some rows in it.

For example:

# -*- coding: utf-8 -*-
import sqlalchemy as sa

from app import db

class Article(db.Model):
    __tablename__ = 'article'

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.Unicode(255))
    content = sa.Column(sa.UnicodeText)

I'm using Flask-SQLAlchemy, so db.session is scoped session object. I saw in https://github.com/zzzeek/sqlalchemy/blob/master/examples/versioned_history/history_meta.py but i can't understand how to use it with my existing tables and anymore how to start it. (I get ArgumentError: Session event listen on a scoped_session requires that its creation callable is associated with the Session class. error when I pass db.session in versioned_session func)

From versioning I need the following:

1) query for old versions of object

2) query old versions by date range when they changed

3) revert old state to existing object

4) add additional info to history table when version is creating (for example editor user_id, date_edit, remote_ip)

Please, tell me what are the best practicies for my case and if you can add a little working example for it.

Upvotes: 2

Views: 2167

Answers (2)

keithb
keithb

Reputation: 1990

You can work around that error by attaching the event handler to the SignallingSession class[1] instead of the created session object:

from flask.ext.sqlalchemy import SignallingSession
from history_meta import versioned_session, Versioned

# Create your Flask app...

versioned_session(SignallingSession)
db = SQLAlchemy(app)

class Article(Versioned, db.Model):
    __tablename__ = 'article'

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.Unicode(255))
    content = sa.Column(sa.UnicodeText)

The sample code creates parallel tables with a _history suffix and an additional changed datetime column. Querying for old versions is just a matter of looking in that table.

For managing the extra fields, I would put them on your main table, and they'll automatically be kept track of in the history table.

[1] Note, if you override SQLAlchemy.create_session() to use a different session class, you should adjust the class you pass to versioned_session.

Upvotes: 1

Edward Z. Yang
Edward Z. Yang

Reputation: 26752

I think the problem is you're running into this bug: https://github.com/mitsuhiko/flask-sqlalchemy/issues/182

One workaround would be to stop using flask-sqlalchemy and configure sqlalchemy yourself.

Upvotes: 0

Related Questions