First of all, thank you for such a detailed answer. Thank you for sharing
your experience and continued support.
Now i understand that using database views is not the best approach. In the
previous project we used an approach with Query objects that were using
WHERE statements to filter needed objects and it worked well.
The problem i want to workaround is that using primary key of (id, start,
end) prevents me from using (lazy loaded) relations out of the box.
Using examples from official sqlalchemy documentation, please think of
Parent and Child as a related business entities:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", back_populates="parents")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parents = relationship("Parent", back_populates="child")
There are object relational impedance mismatch: one Parent business entity
can have two Childs. Three business objects. But the database can have
multiple versions of parent or any of children via compound primary key of
(id, start, end) where start and end is a date range.
My problem is that Children with pk (1, 2018-01-01, 2018-01-02) and (1,
2018-01-02, 2018-01-03) is in fact one business entity represented as two
model instances in sqlalchemy, so i can non figure out how can i force
relationships between "current" objects to work.
I tried to specify additional conditions on relationships and it worked for
read operations, but failed for write operations.
So i thought i can overcome this situation by using database views and
force sqlalchemy to think that there is only one "current" object at a
time, also for relationships.
Can you give me advice of how to configure sqlalchemy the right way for
this situation?
Is it good idea to use second versioning example, where two tables created
per object (primary and historical table)?
P.S.: sorry for my bad english, it is not on my tongue.
вторник, 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.
