>
> given that now() is the current time, how do you determine "end' when
> writing these rows? "end" must always be a date that's in the
> future? What happens when now() passes "end", you get None for your
> object?
>
When new object is created the "end" date is automatically (or manually)
set to some date far away from now. Also there are no deletes as well. If i
want to delete object, i set it's end to now().
If now() passes "end" then query returns nothing (None or empty query
result), that means that object was deleted.
This approach lets us easily restore "deleted" objects or rollback them to
specific versions in the past.
the examples I've shared are capable of adding this criteria so this
problem should be considered solved
True. I will use it for queries.
I just need to understand where you are getting "end" from and a full
> POC can be worked up
>
"end" is not null field, so the system checks if it is null and sets it to
some constant. In one of my previous projects such a constant was
2060-01-01.
Example code from versioned rows (simplified, not production code):
class VersionExtension(SessionExtension):
def before_flush(self, session, flush_context, instances):
now = dt.datetime.utcnow()
for instance in session.new:
instance.start = now
instance.end = dt.datetime(2060, 1, 1)
for instance in session.dirty:
if not isinstance(instance, Versioned):
continue
if not session.is_modified(instance, passive=True):
continue
if not attributes.instance_state(instance).has_identity:
continue
previous_id = instance.id
instance.end = now
# make it transient
instance.new_version(session)
# keep id because it is the same object
instance.id = previous_id
instance.start = now + dt.timedelta(microseconds=1)
instance.end = dt.datetime(2060, 1, 1)
# re-add
session.add(instance)
Thanks :)
четверг, 6 декабря 2018 г., 18:02:48 UTC+3 пользователь Mike Bayer написал:
>
> On Thu, Dec 6, 2018 at 3:53 AM Stanislav Lobanov <n101...@gmail.com
> <javascript:>> wrote:
> >
> > Example business case is:
> >
> > Parent and child are added to the system (current date is 2018-01-01)
> >
> > Parent
> > id | start | end | data | child_id
> > 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just
> pointer to child with some id (now points to first child record)
> >
> > Child
> > id | start | end | data |
> > 1 | 2018-01-01 | 2018-01-11 | Elvis P. | # this is current
> version
> >
> > Then on 2018-01-02 children's name is changed from "Elvis P." to "Elvis
> Presley". That change creates second version of child with ID=1:
> >
> > Parent
> > id | start | end | data | child_id
> > 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just
> pointer to child with some id (now logically points to second child
> version)
> >
> > Child # please notice that id is not changed as this is same child
> > id | start | end | data |
> > 1 | 2018-01-01 | 2018-01-02 | Elvis P. | # this is not
> current (latest) version anymore
> > 1 | 2018-01-02 | 2018-01-11 | Elvis Presley | # but this is
> >
> >
> > See? Parent does not care about what changes were made to child, it is
> just cares to relate to the latest version of child, so there are no
> composite foreign key to child table (this restriction comes from legacy
> system and i can not add "child_start" and "child_end" columns to form full
> composite FK to child table).
> >
> > When child is updated (effectively UPDATE is converted to INSERT) then
> only child table is modified, so there are no cascades to parent, because
> parent just targets to the row where Child.id == 1.
> >
> > And this is my problem, because i do not know to to make such "implicit"
> relationships in sqlalchemy. By implicit i mean a situation when relation
> is made by child.id AND latest date range for related object.
> >
> > Currently we would be using following query to retreive information
> about parent and it's child (in it's latest state):
> >
> > select * from parent, child
> > where parent.child_id=child.id
> > and now() between parent.start and parent.end -- gives us latest parent
> state
> > and now() between child.start and child.end -- gives us latest child
> state
>
> given that now() is the current time, how do you determine "end' when
> writing these rows? "end" must always be a date that's in the
> future? What happens when now() passes "end", you get None for your
> object?
>
>
> >
> > As you can see such a queries is hard to write, it is repetitive and
> error prone.
>
> the examples I've shared are capable of adding this criteria so this
> problem should be considered solved
>
> >
> > Also, i'm using versioned approach in one of the projects (using your
> example). Everything related to data consistency, data integrity, data
> querying must be done by hands, because i did not found a way to provide
> cascades and correct relationship behaviour without full composite FK.
>
> I just need to understand where you are getting "end" from and a full
> POC can be worked up
>
>
> >
> > Thanks!
> >
> >
> > вторник, 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.
>
четверг, 6 декабря 2018 г., 18:02:48 UTC+3 пользователь Mike Bayer написал:
>
> On Thu, Dec 6, 2018 at 3:53 AM Stanislav Lobanov <n101...@gmail.com
> <javascript:>> wrote:
> >
> > Example business case is:
> >
> > Parent and child are added to the system (current date is 2018-01-01)
> >
> > Parent
> > id | start | end | data | child_id
> > 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just
> pointer to child with some id (now points to first child record)
> >
> > Child
> > id | start | end | data |
> > 1 | 2018-01-01 | 2018-01-11 | Elvis P. | # this is current
> version
> >
> > Then on 2018-01-02 children's name is changed from "Elvis P." to "Elvis
> Presley". That change creates second version of child with ID=1:
> >
> > Parent
> > id | start | end | data | child_id
> > 1 | 2018-01-01 | 2018-01-11 | c1 | 1 # just
> pointer to child with some id (now logically points to second child
> version)
> >
> > Child # please notice that id is not changed as this is same child
> > id | start | end | data |
> > 1 | 2018-01-01 | 2018-01-02 | Elvis P. | # this is not
> current (latest) version anymore
> > 1 | 2018-01-02 | 2018-01-11 | Elvis Presley | # but this is
> >
> >
> > See? Parent does not care about what changes were made to child, it is
> just cares to relate to the latest version of child, so there are no
> composite foreign key to child table (this restriction comes from legacy
> system and i can not add "child_start" and "child_end" columns to form full
> composite FK to child table).
> >
> > When child is updated (effectively UPDATE is converted to INSERT) then
> only child table is modified, so there are no cascades to parent, because
> parent just targets to the row where Child.id == 1.
> >
> > And this is my problem, because i do not know to to make such "implicit"
> relationships in sqlalchemy. By implicit i mean a situation when relation
> is made by child.id AND latest date range for related object.
> >
> > Currently we would be using following query to retreive information
> about parent and it's child (in it's latest state):
> >
> > select * from parent, child
> > where parent.child_id=child.id
> > and now() between parent.start and parent.end -- gives us latest parent
> state
> > and now() between child.start and child.end -- gives us latest child
> state
>
> given that now() is the current time, how do you determine "end' when
> writing these rows? "end" must always be a date that's in the
> future? What happens when now() passes "end", you get None for your
> object?
>
>
> >
> > As you can see such a queries is hard to write, it is repetitive and
> error prone.
>
> the examples I've shared are capable of adding this criteria so this
> problem should be considered solved
>
> >
> > Also, i'm using versioned approach in one of the projects (using your
> example). Everything related to data consistency, data integrity, data
> querying must be done by hands, because i did not found a way to provide
> cascades and correct relationship behaviour without full composite FK.
>
> I just need to understand where you are getting "end" from and a full
> POC can be worked up
>
>
> >
> > Thanks!
> >
> >
> > вторник, 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.
