On Wed, Dec 5, 2018 at 2:42 AM Stanislav Lobanov <n10101...@gmail.com> wrote:
>
> 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.
OK so just to confirm the first assumption, in your code you only want
to see one Child at a time, that is parent.child is scalar and has
just one date range, right?
>
>
> 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.
With the filtered approach, whether you do it in a view or in the
query, there's just one parent.child object, you just write to it. if
you're talking about the process by which when you write to
parent.child, a new row in the DB replaces it and the row you just saw
goes into "history" , we have a lot of examples of how to do that too
as I had another job for a long time *after* the previous job where we
did everything that way :). Otherwise please be more specific what
"failed for write operations" means.
>
> 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)?
the tradeoffs with single versioned table vs. table plus historical
have to do with how you want to query the data. when you use a
single table, you have the advantage that you can change the date
range you are injecting into the WHERE clause and you can then load up
a full model in Python that represents a historical snapshot. OTOH
if you just need the "historical" data for an occasional "archive"
view then it may be more convenient to dump changes into a separate
table, and here in Openstack I argue they shouldn't even be doing
that, just dump archive stuff to a file (because we never use our
historical data and it fills up databases with useless crap). in any
case, if you want to really be thorough, you can disable UPDATE
statements on the table in question either through grants or triggers,
since you want to only be doing INSERTs to the 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 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.
