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 <n101...@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+...@googlegroups.com. >> > To post to this group, send email to sqlal...@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.