I have re-read our dialogue and the new FilteredQuery example and decided
to use following approach:
1. single table versioning
2. global filtering with temporal options
That way i can have whole history in one table and force application to use
only "latest" object versions. I agree that in 2018 this approach may look
like outdated or legacy for new projects, but it has it's advantages and
much simpler as opposed to a event sourcing approach, for example.
Thank you so much for helping me!
Have a good day, sir!
среда, 5 декабря 2018 г., 18:13:11 UTC+3 пользователь Mike Bayer написал:
>
> On Wed, Dec 5, 2018 at 2:42 AM Stanislav Lobanov <n101...@gmail.com
> <javascript:>> 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+...@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.
