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]
> <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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [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.
