On Sun, Dec 9, 2018 at 11:22 AM Stanislav Lobanov <n10101...@gmail.com> 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+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.
