Hello again, i'm bumping this thread to pop a context for a further
discussion.
I'm using this VersionedStartEnd approach and i think that i found a bug.
Consider that there are Child instance and Parent instance:
Parent
id | start | end | data | child_id
1 | 2018-01-01 | 2018-01-02 | c1 | 1
1 | 2018-01-02 | 2018-01-11 | changed!| 1 # this row is
current version
Child
id | start | end | data |
1 | 2018-01-01 | 2018-01-11 | Elvis P. | # this is current version
This join:
# one child one parent....
c3_check = s.query(Child).join(Parent.child).filter(
Parent.id == p3_check.id).one()
generates following query:
SELECT child.id AS child_id, child.start AS child_start, child."end" AS
child_end, child.data AS child_data
FROM parent JOIN child ON child.id = parent.child_n
WHERE parent.id = ? AND ? BETWEEN child.start AND child."end"
which is wrong, because ON condition must contain "now() between parent.start
and parent.end", because
we want to join only on current versions:
SELECT child.id AS child_id, child.start AS child_start, child."end" AS
child_end, child.data AS child_data
FROM parent JOIN child ON child.id = parent.child_n and now() between
parent.start and parent.end
WHERE parent.id = ? AND ? BETWEEN child.start AND child."end"
I know that i can hardcode this join behaviour in primary join, but i want this
to happen automatically
using some event handler or any other available mechanism, because i want to
change join conditions to
join on "the very first" record and "the very last record" dynamically:
c3_check = s.query(Child).join(Parent.child).option(Current(Child), Current(
Parent)).filter(
Parent.id == p3_check.id).one()
class Current(MapperOption):
def __init__(self, class_):
self.class_ = class_
def apply_filter(self, entity, query):
return query.filter(func.now().between(entity.fd, entity.td))
# before_compile event then checks if there are any options for
# any entities in query and applies filtering based on provided
# MapperOption.apply_filter, so there are actually few filtering
# strategies — Current, Last, First with different apply_filter
# implementations.
The problem is happening because "before_compile" event handler uses
query.column_descriptions,
and there are only Child, but not parent, so it can not add a correct filter.
Parent is added via join and invisible to this event handler.
Are there any way to get information about joined tables and linked entities in
"before_compile"
event handler?
Something like that:
@event.listens_for(Query, "before_compile", retval=True)
def before_compile(query):
"""ensure all queries for VersionedStartEnd include criteria """
entities = query.column_descriptions + query.get_entities_from_join()
for ent in entities:
# check entity and apply filtering
return query
Thanks!
воскресенье, 9 декабря 2018 г., 19:57:26 UTC+3 пользователь Mike Bayer
написал:
>
> On Sun, Dec 9, 2018 at 11:22 AM Stanislav Lobanov <[email protected]
> <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 <[email protected]>
> 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 <[email protected]>
> wrote:
> >> >> >
> >> >> > On Thu, Dec 6, 2018 at 3:53 AM Stanislav Lobanov <
> [email protected]> 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 [email protected].
> >> >> > > To post to this group, send email to [email protected].
> >> >> > > 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 [email protected].
> >> > To post to this group, send email to [email protected].
> >> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
