Great read, thanks! But i try to avoid using triggers, because: 1. i want the application to handle such cases 2. triggers can be hard to maintain, slow and can constraint you in some scenarios
I have heard about this approach and i know, that it works very well, it if fits the project technical requirements. четверг, 1 февраля 2018 г., 5:35:26 UTC+3 пользователь Jonathan Rogers написал: > > > > On Wednesday, January 31, 2018 at 12:27:12 PM UTC-5, Stanislav Lobanov > 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. >> > > This might be slightly off-topic, but I have implemented automatic > management of temporal state tables as you describe with triggers in > Postgres. Ordinary INSERT, UPDATE and DELETE statements on a current table > or view result in the necessary INSERTs and UPDATEs on the historical state > table. I learned how to implement this from "Developing Time-Oriented > Database Applications in SQL", which has in-depth explanations of several > ways of representing and managing temporal data with many examples: > https://www2.cs.arizona.edu/~rts/publications.html#books > > -- 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.
