On Thu, Dec 6, 2018 at 3:53 AM Stanislav Lobanov <n10101...@gmail.com> wrote: > > 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
given that now() is the current time, how do you determine "end' when writing these rows? "end" must always be a date that's in the future? What happens when now() passes "end", you get None for your object? > > As you can see such a queries is hard to write, it is repetitive and error > prone. the examples I've shared are capable of adding this criteria so this problem should be considered solved > > 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. I just need to understand where you are getting "end" from and a full POC can be worked up > > 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. -- 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.
