Example business case is: Parent and child are added to the system (current date is 2018-01-01)
Parent id | start | end | data | child_id 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just pointer to child with some id (now points to first child record) Child id | start | end | data | 1 | 2018-01-01 | 2018-01-11 | Elvis P. | # this is current version Then on 2018-01-02 children's name is changed from "Elvis P." to "Elvis Presley". That change creates second version of child with ID=1: Parent id | start | end | data | child_id 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just pointer to child with some id (now logically points to second child version) Child # please notice that id is not changed as this is same child id | start | end | data | 1 | 2018-01-01 | 2018-01-02 | Elvis P. | # this is not current (latest) version anymore 1 | 2018-01-02 | 2018-01-11 | Elvis Presley | # but this is See? Parent does not care about what changes were made to child, it is just cares to relate to the latest version of child, so there are no composite foreign key to child table (this restriction comes from legacy system and i can not add "child_start" and "child_end" columns to form full composite FK to child table). When child is updated (effectively UPDATE is converted to INSERT) then only child table is modified, so there are no cascades to parent, because parent just targets to the row where Child.id == 1. And this is my problem, because i do not know to to make such "implicit" relationships in sqlalchemy. By implicit i mean a situation when relation is made by child.id AND latest date range for related object. Currently we would be using following query to retreive information about parent and it's child (in it's latest state): select * from parent, child where parent.child_id=child.id and now() between parent.start and parent.end -- gives us latest parent state and now() between child.start and child.end -- gives us latest child state As you can see such a queries is hard to write, it is repetitive and error prone. Also, i'm using versioned approach in one of the projects (using your example). Everything related to data consistency, data integrity, data querying must be done by hands, because i did not found a way to provide cascades and correct relationship behaviour without full composite FK. Thanks! вторник, 4 декабря 2018 г., 15:36:42 UTC+3 пользователь Stanislav Lobanov написал: > > Hello. > > I have a table with schema: > > name: users > fields: id int, name text, start datetime, end datetime > primary key: id, start, end > > This is kind of a historical table, where each row defines separate > "historical version" of an object. There are a single business User entity > (model) with possibly many historical versions. > > Such table structure makes it very hard to define relationships and work > with related objects. Also it is hard to work with "current" version of > User entity, because to retreive it we need to query it with "now() between > start and end" constraint. > > So i thought that maybe i can create a view for that table that will hold > only current versions and map that view onto User entity to hide all > historical complexities and compound PK from sqlalchemy. > > The question: is it possible to implement a mapping that will read from > view but write into real table? > > For example, view can have fields *id* (pk) and *name*. > > I know that there are great examples of versioning with sqlalchemy but i > want to hide non-functional implementation details from my business > code/entities with view. > > Thanks! > -- 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.
