My 2c about table design (not SQLA): I would suggest having a child with
just id, a parent table with just id and child_id (foreign key to
child.id), and then store additional "versioned" data in separate
parent_history and child_history tables that have foreign keys only to
their respective parent and child tables. If using Postgresql, each of
parent_history and child_history would have an id (pointing back to
parent.id or child.id, respectively) and a tsrange, and an EXCLUDE USING
gist (id WITH =, range WITH &&) constraint
(see https://www.postgresql.org/docs/9.4/rangetypes.html).
On Wednesday, December 5, 2018 at 12:07:01 PM UTC-5, Stanislav Lobanov
wrote:
>
> I'm very sorry but I absolutely forgot to mention a problem with primary
> keys.
>
> As i said, all objects in the database have id, start and end primary keys.
>
> CREATE TABLE parent (
> id SERIAL NOT NULL,
> start TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> end TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> data VARCHAR,
> child_id INTEGER,
> PRIMARY KEY (id, start, end)
> )
>
> CREATE TABLE child (
> id SERIAL NOT NULL,
> start TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> end TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> data VARCHAR,
> PRIMARY KEY (id, start, end)
> )
>
> Using single table versioning pattern i'm stuck with relationship
> definition:
>
> 1. Relation always should relate to only "current" rows
> 2. Relation is represented as a single scalar column (child_id in this
> example), without extra start and end foreign key columns.
>
> This is my models:
>
>
> class Parent(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, ForeignKey('child.n'))
> child = relationship("Child", backref=backref('parent', uselist=False))
>
>
> class Child(Versioned, 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)
>
>
> Of course this models definition gives me various errors about primary and
> foreign keys, because foreign key must consist of (id, start, end).
>
> But, as i said, we consider that relation always targets to current rows
> only. We are avoiding cascade re-assigning of changed children to parents.
>
> "primaryjoin" can be used for that:
>
> relation(..., primaryjoin="and_(Parent.child_id=Child.id,
> func.now().between(Child.start, Child.end))"
>
> Something like that. I tried that approach and it did not work for me
> (maybe i did not configured mappers or relations correctly — i was able to
> read, but not write.)
>
> So, the point is that i do not know how to make it work in a way that:
>
> 1. when reading, relation is joined on func.now() between start and end
> 2. when writing to this relation new version of child is created with new
> start and end, *preserving id.*
>
> Let me show data in the tables to explain myself better:
>
> Parent
> id | start | end | data | child_id
> 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # -> this
> should reference to child with id=1 and func.now() between start and end
>
> Child
> id | start | end | data |
> 1 | 2018-01-01 | 2018-01-02 | child 1 |
> 1 | 2018-01-02 | 2018-01-11 | child 1.1 | # -> this row considered
> current if func.now() is 2018-01-05 for example
>
>
>
> I want to avoid declaring complex code/join conditions, that is why i
> thought about using views or dual-tables-versioning.
>
> среда, 5 декабря 2018 г., 18:13:11 UTC+3 пользователь Mike Bayer написал:
>>
>> On Wed, Dec 5, 2018 at 2:42 AM Stanislav Lobanov <[email protected]>
>> 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 [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.
