On Wed, Jan 31, 2018 at 5:27 PM, Stanislav Lobanov <n10101...@gmail.com> wrote: > Hello, i need to create a system, that can store historical versions of > objects. > > Example model looks like: > > class User(Base): > id=Column(Integer) # ForeignKey > start=Column(DateTime) > end=Column(DateTime) > name=Column(String) > > I have implemented .add_new() and .save() methods on base class, which > performs custom logic on inserting rows into database. > > .add_new() method just assigns next id, default start and end date values > (start=datetime.now() and end=None). > > Rows where start has value and end is null are considered "live" rows, all > other rows are considered as "historical" > > .save() method changes current record dates (end date becomes current > moment, so this version is considered as "historical", because it has start > and end date lifetime moments set). Also, it performs some custom logic on > determining if new version of record should be created — it should not be > created, if name is not chaged. > > Example: > > user = User(name='User1') > user.add_new() > # user has values: id=1, start=<current datetime>, end=None, name='User1' > > user.name='test2' > user.save() > # user has values: id=1, start=<previous start>, end=<current datetime>, > name='test2' > > user.name='test2' > user.save() > # user has values: id=1, start=<previous start>, end=<current datetime>, > name='test2' and no insert performed, because name is not changed. > > > > This methods look for me inconsistent and error prone, so i want to be able > to use session.add() method. > > Are there any possibility to add support for custom process of saving and > modifying objects by using default sqlalchemy's apis? > > P.S.: in current example, table has composite pk (id, start, end), so id is > not auto incremented when inserting new row. Also, in such scenario we can > not have ForeignKey constraint. >
The SQLAlchemy documentation has some examples of a couple of different ways of doing this: http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects The history_meta.py example creates separate "history" tables for each table that you want to be versioned. Whenever you UPDATE a versioned object, a new row gets INSERTed into the history table. The versioned_rows.py example is more like what you described, converting what would have been an UPDATE into an INSERT on the same table. Both examples use the "before_flush" session event to implement the versioning, so other parts of your code don't need to be aware of it. Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
