Great explanation, thank you very much. I think that in case one uses versioned approach with changing ids then is it better to have some automated function that will make "expire-changestate-readd" dance by introspecting relations on the object, because if a programmer forget to override "new_version" method, then the system may start having consistency issues.
воскресенье, 9 декабря 2018 г., 19:57:26 UTC+3 пользователь Mike Bayer написал: > > On Sun, Dec 9, 2018 at 11:22 AM Stanislav Lobanov <n101...@gmail.com > <javascript:>> wrote: > > > > Hello, i have one more question: > > > > there is a Child definition: > > > > class Child(VersionedStartEnd, Base): > > __tablename__ = "child" > > > > id = Column(Integer, primary_key=True, autoincrement=True) > > start = Column(DateTime, primary_key=True) > > end = Column(DateTime, primary_key=True) > > data = Column(String) > > > > def __repr__(self): > > return f"<Child {self.data}>" > > > > def new_version(self, session): > > > > # expire parent's reference to us > > session.expire(self.parent, ["child"]) > > > > # create new version > > VersionedStartEnd.new_version(self, session) > > > > # re-add ourselves to the parent > > self.parent.child = self > > > > > > > > Can you clarify why we need to extend new_version method in child? I > have commented this code out and it seems that all tests pass. I understand > that on expiring "child" backref children will be fetched again (refreshed > from database), but it seems that it happens so anyway after > session.commit, because commit() expires objects in session. > > it looks like this version of the demo can squeak by without setting > that, because Parent has no actual columns that need to change when a > new Child is associated with it. However if you wanted any of that to > change, like Child.id changing on new version, then Parent would need > to see that its Parent.child relationship has changed. Taking > out the same override in the simpler versioned_rows.py example > illustrates this when run as the assertion for Parent.child_id fails. > I've made a modified version of the script, see below, that also > changes child.id when an update occurs which illustrates the main > thing that the parent.child = self thing accomplishes. > > More generally, the ORM does not expect that an object which is > "pending", that is, has no row inserted yet, would be linked to an > attribute like Parent.child without Parent.child seeing an "added" > event. In the absence of our manipulation of ORM state using > make_transient() or other below-the-ORM things like writing to > parent.__dict__ directly, the condition of "Parent.child refers to a > pending (e.g. non inserted) object but this object was not just > "added" to Parent.child is not possible to produce. So it's likely > safer to keep this "expire", "make a pending object", "reattach" > sequence as that more closely resembles what "normal" ORM manipulation > looks like. > > """Illustrates the same UPDATE into INSERT technique of > ``versioned_rows.py``, > but also emits an UPDATE on the **old** row to affect a change in > timestamp. > Also includes a :meth:`.QueryEvents.before_compile` hook to limit queries > to only the most recent version. > > """ > > from sqlalchemy import ( > create_engine, Integer, String, event, Column, DateTime, > inspect, literal > ) > from sqlalchemy.orm import ( > make_transient, Session, relationship, attributes, backref, > make_transient_to_detached, Query > ) > from sqlalchemy.ext.declarative import declarative_base > import datetime > import time > import itertools > > Base = declarative_base() > > # this will be the current time as the test runs > now = None > > > # in practice this would be a real "now" function > def current_time(): > return now > > > # a "sequence" that will give us new ids > > def sequence(): > counter = itertools.count(1) > > def generate(): > return next(counter) > > return generate > > > class VersionedStartEnd(object): > def __init__(self, **kw): > # reduce some verbosity when we make a new object > kw.setdefault("start", current_time() - > datetime.timedelta(days=3)) > kw.setdefault("end", current_time() + datetime.timedelta(days=3)) > super(VersionedStartEnd, self).__init__(**kw) > > def new_version(self, session): > > # our current identity key, which will be used on the "old" > # version of us to emit an UPDATE. this is just for assertion > purposes > old_identity_key = inspect(self).key > > # make sure self.start / self.end are not expired > self.id, self.start, self.end > > # turn us into an INSERT > make_transient(self) > > # make the "old" version of us, which we will turn into an > # UPDATE > old_copy_of_us = self.__class__( > id=self.id, start=self.start, end=self.end) > > # turn old_copy_of_us into an UPDATE > make_transient_to_detached(old_copy_of_us) > > # the "old" object has our old identity key (that we no longer > have) > assert inspect(old_copy_of_us).key == old_identity_key > > # now put it back in the session > session.add(old_copy_of_us) > > # now update the 'end' - SQLAlchemy sees this as a PK switch > old_copy_of_us.end = current_time() > > # fun fact! the new_version() routine is *not* called for > # old_copy_of_us! because we are already in the before_flush() > hook! > # this surprised even me. I was thinking we had to guard against > # it. Still might be a good idea to do so. > > self.start = current_time() > self.end = current_time() + datetime.timedelta(days=2) > self.id = None # this will generate a new id on INSERT > > > @event.listens_for(Session, "before_flush") > def before_flush(session, flush_context, instances): > for instance in session.dirty: > if not isinstance(instance, VersionedStartEnd): > continue > if not session.is_modified(instance, passive=True): > continue > > if not attributes.instance_state(instance).has_identity: > continue > > # make it transient > instance.new_version(session) > # re-add > session.add(instance) > > > @event.listens_for(Query, "before_compile", retval=True) > def before_compile(query): > """ensure all queries for VersionedStartEnd include criteria """ > > for ent in query.column_descriptions: > entity = ent['entity'] > if entity is None: > continue > insp = inspect(ent['entity']) > mapper = getattr(insp, 'mapper', None) > if mapper and issubclass(mapper.class_, VersionedStartEnd): > query = query.enable_assertions(False).filter( > # using a literal "now" because SQLite's "between" > # seems to be inclusive. In practice, this would be > # ``func.now()`` and we'd be using PostgreSQL > literal( > current_time() + datetime.timedelta(seconds=1) > ).between(ent['entity'].start, ent['entity'].end) > ) > > return query > > > class Parent(VersionedStartEnd, Base): > __tablename__ = 'parent' > id = Column(Integer, primary_key=True, default=sequence()) > start = Column(DateTime, primary_key=True) > end = Column(DateTime, primary_key=True) > data = Column(String) > > child_n = Column(Integer) > > child = relationship( > "Child", > primaryjoin=( > "Child.id == foreign(Parent.child_n)" > ), > > # note the primaryjoin can also be: > # > # "and_(Child.id == foreign(Parent.child_n), " > # "func.now().between(Child.start, Child.end))" > # > # however the before_compile() above will take care of this for us > in > # all cases except for joinedload. You *can* use the above > primaryjoin > # as well, it just means the criteria will be present twice for > most > # parent->child load operations > # > > uselist=False, > backref=backref('parent', uselist=False) > ) > > > class Child(VersionedStartEnd, Base): > __tablename__ = 'child' > > id = Column(Integer, primary_key=True, default=sequence()) > start = Column(DateTime, primary_key=True) > end = Column(DateTime, primary_key=True) > data = Column(String) > > def new_version(self, session): > > # expire parent's reference to us > session.expire(self.parent, ['child']) > > # create new version > VersionedStartEnd.new_version(self, session) > > # re-add ourselves to the parent. > # as Child has a new "id", this will cause an UPDATE > # to the parent.child_n column as well. > self.parent.child = self > > times = [] > > > def time_passes(s): > """keep track of timestamps in terms of the database and allow time to > pass between steps.""" > > # close the transaction, if any, since PG time doesn't increment in > the > # transaction > s.commit() > > # get "now" in terms of the DB so we can keep the ranges low and > # still have our assertions pass > if times: > time.sleep(1) > > times.append(datetime.datetime.now()) > > if len(times) > 1: > assert times[-1] > times[-2] > return times[-1] > > e = create_engine("sqlite://", echo='debug') > Base.metadata.create_all(e) > > s = Session(e) > > now = time_passes(s) > > c1 = Child(data='child 1') > p1 = Parent(data='c1', child=c1) > > s.add(p1) > s.commit() > > # assert raw DB data > assert s.query(Parent.__table__).all() == [ > (1, times[0] - datetime.timedelta(days=3), > times[0] + datetime.timedelta(days=3), 'c1', 1) > ] > assert s.query(Child.__table__).all() == [ > (1, times[0] - datetime.timedelta(days=3), > times[0] + datetime.timedelta(days=3), 'child 1') > ] > > now = time_passes(s) > > p1_check = s.query(Parent).first() > assert p1_check is p1 > assert p1_check.child is c1 > > p1.child.data = 'elvis presley' > s.commit() > > p2_check = s.query(Parent).first() > assert p2_check is p1_check > c2_check = p2_check.child > > # same object > assert p2_check.child is c1 > > # new data > assert c1.data == 'elvis presley' > > # new end time > assert c1.end == now + datetime.timedelta(days=2) > > # assert raw DB data > assert s.query(Parent.__table__).all() == [ > (1, times[0] - datetime.timedelta(days=3), > times[0] + datetime.timedelta(days=3), 'c1', 2) > ] > assert s.query(Child.__table__).order_by(Child.end).all() == [ > (1, times[0] - datetime.timedelta(days=3), times[1], 'child 1'), > (2, times[1], times[1] + datetime.timedelta(days=2), 'elvis presley') > ] > > now = time_passes(s) > > p1.data = 'c2 elvis presley' > > s.commit() > > # assert raw DB data. now there are two parent rows. > assert s.query(Parent.__table__).order_by(Parent.end).all() == [ > (1, times[0] - datetime.timedelta(days=3), times[2], 'c1', 2), > (2, times[2], times[2] + datetime.timedelta(days=2), 'c2 elvis > presley', 2) > ] > assert s.query(Child.__table__).order_by(Child.end).all() == [ > (1, times[0] - datetime.timedelta(days=3), times[1], 'child 1'), > (2, times[1], times[1] + datetime.timedelta(days=2), 'elvis presley') > ] > > # add some more rows to test that these aren't coming back for > # queries > s.add(Parent(data='unrelated', child=Child(data='unrelated'))) > s.commit() > > > # Query only knows about one parent for id=2 > p3_check = s.query(Parent).filter_by(id=2).one() > > assert p3_check is p1 > assert p3_check.child is c1 > > # and one child. > c3_check = s.query(Child).filter(Child.parent == p3_check).one() > assert c3_check is c1 > > # one child one parent.... > c3_check = s.query(Child).join(Parent.child).filter( > Parent.id == p3_check.id).one() > > > > > > > > > > > пятница, 7 декабря 2018 г., 17:59:58 UTC+3 пользователь Mike Ba > yer написал: > >> > >> I'm going to add this to the examples at > >> > https://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.versioned_rows > > >> since it has this new twist of doing both UPDATE and INSERT, if that's > >> OK with you > >> On Fri, Dec 7, 2018 at 1:20 AM Stanislav Lobanov <n101...@gmail.com> > wrote: > >> > > >> > Thank you so much, Mike! > >> > > >> > Did not tried it yet, but it looks like you nailed it :) I'll check > the sources on holidays. > >> > > >> > With the deepest respect, > >> > Stanislav. > >> > > >> > четверг, 6 декабря 2018 г., 22:41:20 UTC+3 пользователь Mike Bayer > написал: > >> >> > >> >> given that it looks like a new version for you means an UPDATE of > the > >> >> old row and an INSERT of the new, here is that, which is again > >> >> basically what we see at > >> >> > https://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_rows/versioned_rows.html > > >> >> with some extra steps to emit the UPDATE for the old row. this > >> >> recipe combines both the versioned event handler and the query > >> >> handler. > >> >> > >> >> from sqlalchemy import ( > >> >> create_engine, Integer, String, event, ForeignKey, Column, > DateTime, > >> >> inspect, func, select, cast > >> >> ) > >> >> from sqlalchemy.orm import ( > >> >> make_transient, Session, relationship, attributes, backref, > >> >> make_transient_to_detached, Query, selectinload > >> >> ) > >> >> from sqlalchemy.ext.declarative import declarative_base > >> >> import datetime > >> >> import time > >> >> > >> >> Base = declarative_base() > >> >> > >> >> # this will be the current time as the test runs > >> >> now = None > >> >> > >> >> > >> >> class VersionedStartEnd(object): > >> >> def __init__(self, **kw): > >> >> # reduce some verbosity when we make a new object > >> >> kw.setdefault("start", now - datetime.timedelta(days=3)) > >> >> kw.setdefault("end", now + datetime.timedelta(days=3)) > >> >> super(VersionedStartEnd, self).__init__(**kw) > >> >> > >> >> def new_version(self, session): > >> >> > >> >> # our current identity key, which will be used on the "old" > >> >> # version of us to emit an UPDATE. this is just for > assertion purposes > >> >> old_identity_key = inspect(self).key > >> >> > >> >> # make sure self.start / self.end are not expired > >> >> self.id, self.start, self.end > >> >> > >> >> # turn us into an INSERT > >> >> make_transient(self) > >> >> > >> >> # make the "old" version of us, which we will turn into an > >> >> # UPDATE > >> >> old_copy_of_us = self.__class__( > >> >> id=self.id, start=self.start, end=self.end) > >> >> > >> >> # turn old_copy_of_us into an UPDATE > >> >> make_transient_to_detached(old_copy_of_us) > >> >> > >> >> # the "old" object has our old identity key (that we no > longer have) > >> >> assert inspect(old_copy_of_us).key == old_identity_key > >> >> > >> >> # now put it back in the session > >> >> session.add(old_copy_of_us) > >> >> > >> >> # now update the 'end' - SQLAlchemy sees this as a PK switch > >> >> old_copy_of_us.end = now > >> >> > >> >> # fun fact! the new_version() routine is *not* called for > >> >> # old_copy_of_us! because we are already in the > before_flush() hook! > >> >> # this surprised even me. I was thinking we had to guard > against > >> >> # it. Still might be a good idea to do so. > >> >> > >> >> self.start = now > >> >> self.end = now + datetime.timedelta(days=2) > >> >> > >> >> > >> >> @event.listens_for(Session, "before_flush") > >> >> def before_flush(session, flush_context, instances): > >> >> for instance in session.dirty: > >> >> if not isinstance(instance, VersionedStartEnd): > >> >> continue > >> >> if not session.is_modified(instance, passive=True): > >> >> continue > >> >> > >> >> if not attributes.instance_state(instance).has_identity: > >> >> continue > >> >> > >> >> # make it transient > >> >> instance.new_version(session) > >> >> # re-add > >> >> session.add(instance) > >> >> > >> >> > >> >> @event.listens_for(Query, "before_compile", retval=True) > >> >> def before_compile(query): > >> >> """ensure all queries for VersionedStartEnd include criteria """ > >> >> > >> >> for ent in query.column_descriptions: > >> >> entity = ent['entity'] > >> >> if entity is None: > >> >> continue > >> >> insp = inspect(ent['entity']) > >> >> mapper = getattr(insp, 'mapper', None) > >> >> if mapper and issubclass(mapper.class_, VersionedStartEnd): > >> >> query = query.enable_assertions(False).filter( > >> >> func.now().between(ent['entity'].start, > ent['entity'].end) > >> >> ) > >> >> > >> >> return query > >> >> > >> >> > >> >> class Parent(VersionedStartEnd, Base): > >> >> __tablename__ = 'parent' > >> >> id = Column(Integer, primary_key=True, autoincrement=True) > >> >> start = Column(DateTime, primary_key=True) > >> >> end = Column(DateTime, primary_key=True) > >> >> data = Column(String) > >> >> > >> >> child_n = Column(Integer) > >> >> > >> >> child = relationship( > >> >> "Child", > >> >> primaryjoin=( > >> >> "Child.id == foreign(Parent.child_n)" > >> >> ), > >> >> > >> >> # note the primaryjoin can also be: > >> >> # > >> >> # "and_(Child.id == foreign(Parent.child_n), " > >> >> # "func.now().between(Child.start, Child.end))" > >> >> # > >> >> # however the before_compile() above will take care of this > for us in > >> >> # all cases except for joinedload. You *can* use the above > primaryjoin > >> >> # as well, it just means the criteria will be present twice > for most > >> >> # parent->child load operations > >> >> # > >> >> > >> >> uselist=False, > >> >> backref=backref('parent', uselist=False) > >> >> ) > >> >> > >> >> > >> >> class Child(VersionedStartEnd, Base): > >> >> __tablename__ = 'child' > >> >> > >> >> id = Column(Integer, primary_key=True, autoincrement=True) > >> >> start = Column(DateTime, primary_key=True) > >> >> end = Column(DateTime, primary_key=True) > >> >> data = Column(String) > >> >> > >> >> def new_version(self, session): > >> >> > >> >> # expire parent's reference to us > >> >> session.expire(self.parent, ['child']) > >> >> > >> >> # create new version > >> >> VersionedStartEnd.new_version(self, session) > >> >> > >> >> # re-add ourselves to the parent > >> >> self.parent.child = self > >> >> > >> >> times = [] > >> >> > >> >> > >> >> def time_passes(s): > >> >> """keep track of timestamps in terms of the database and allow > time to > >> >> pass between steps.""" > >> >> > >> >> # close the transaction, if any, since PG time doesn't increment > in the > >> >> # transaction > >> >> s.commit() > >> >> > >> >> # get "now" in terms of the DB so we can keep the ranges low and > >> >> # still have our assertions pass > >> >> if times: > >> >> time.sleep(1) > >> >> times.append(s.scalar(select([cast(func.now(), DateTime)]))) > >> >> if len(times) > 1: > >> >> assert times[-1] > times[-2] > >> >> return times[-1] > >> >> > >> >> e = create_engine("postgresql://scott:tiger@localhost/test", > echo='debug') > >> >> Base.metadata.drop_all(e) > >> >> Base.metadata.create_all(e) > >> >> > >> >> s = Session(e) > >> >> > >> >> now = time_passes(s) > >> >> > >> >> c1 = Child(data='child 1') > >> >> p1 = Parent(data='c1', child=c1) > >> >> > >> >> s.add(p1) > >> >> s.commit() > >> >> > >> >> # assert raw DB data > >> >> assert s.query(Parent.__table__).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[0] + datetime.timedelta(days=3), > >> >> 'c1', 1) > >> >> ] > >> >> assert s.query(Child.__table__).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[0] + datetime.timedelta(days=3), > >> >> 'child 1') > >> >> ] > >> >> > >> >> > >> >> now = time_passes(s) > >> >> > >> >> p1_check = s.query(Parent).first() > >> >> assert p1_check is p1 > >> >> assert p1_check.child is c1 > >> >> > >> >> p1.child.data = 'elvis presley' > >> >> > >> >> s.commit() > >> >> > >> >> p2_check = s.query(Parent).first() > >> >> assert p2_check is p1_check > >> >> c2_check = p2_check.child > >> >> > >> >> # same object > >> >> assert p2_check.child is c1 > >> >> > >> >> # new data > >> >> assert c1.data == 'elvis presley' > >> >> > >> >> # new end time > >> >> assert c1.end == now + datetime.timedelta(days=2) > >> >> > >> >> # assert raw DB data > >> >> assert s.query(Parent.__table__).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[0] + datetime.timedelta(days=3), > >> >> 'c1', 1) > >> >> ] > >> >> assert s.query(Child.__table__).order_by(Child.end).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[1], > >> >> 'child 1'), > >> >> (1, > >> >> times[1], > >> >> times[1] + datetime.timedelta(days=2), > >> >> 'elvis presley') > >> >> ] > >> >> > >> >> now = time_passes(s) > >> >> > >> >> p1.data = 'c2 elvis presley' > >> >> > >> >> s.commit() > >> >> > >> >> # assert raw DB data. now there are two parent rows. > >> >> assert s.query(Parent.__table__).order_by(Parent.end).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[2], > >> >> 'c1', 1), > >> >> (1, > >> >> times[2], > >> >> times[2] + datetime.timedelta(days=2), > >> >> 'c2 elvis presley', 1) > >> >> ] > >> >> assert s.query(Child.__table__).order_by(Child.end).all() == [ > >> >> (1, > >> >> times[0] - datetime.timedelta(days=3), > >> >> times[1], > >> >> 'child 1'), > >> >> (1, > >> >> times[1], > >> >> times[1] + datetime.timedelta(days=2), > >> >> 'elvis presley') > >> >> ] > >> >> > >> >> # add some more rows to test that these aren't coming back for > >> >> # queries > >> >> s.add(Parent(data='unrelated', child=Child(data='unrelated'))) > >> >> s.commit() > >> >> > >> >> > >> >> # Query only knows about one parent for id=1 > >> >> p3_check = s.query(Parent).filter_by(id=1).one() > >> >> > >> >> assert p3_check is p1 > >> >> assert p3_check.child is c1 > >> >> > >> >> # and one child. > >> >> c3_check = s.query(Child).filter(Child.parent == p3_check).one() > >> >> assert c3_check is c1 > >> >> > >> >> # one child one parent.... > >> >> c3_check = s.query(Child).join(Parent.child).filter( > >> >> Parent.id == p3_check.id).one() > >> >> > >> >> # try selectinload eager loading across multiple parents > >> >> for parent in s.query(Parent).options(selectinload(Parent.child)): > >> >> if parent.data == 'unrelated': > >> >> assert parent.child.data == 'unrelated' > >> >> elif parent.data == 'c2 elvis presley': > >> >> assert parent.child.data == 'elvis presley' > >> >> else: > >> >> assert False > >> >> > >> >> > >> >> > >> >> > >> >> On Thu, Dec 6, 2018 at 1:40 PM Mike Bayer <mik...@zzzcomputing.com> > wrote: > >> >> > > >> >> > On Thu, Dec 6, 2018 at 3:53 AM Stanislav Lobanov < > n101...@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. > >> >> > > >> >> > from the data above, this is not strictly the case. the row with > >> >> > "Elvis P.", the "end" date has been UPDATED from 2018-01-11 to > >> >> > 2018-01-02. is that correct? So you need an UPDATE *and* an > INSERT. > >> >> > Am seeing if I can make that happen. > >> >> > > >> >> > > >> >> > > > >> >> > > 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+...@googlegroups.com. > >> >> > > To post to this group, send email to sqlal...@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+...@googlegroups.com. > >> > To post to this group, send email to sqlal...@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+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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.