"Best" way to build a historical change tracking abstraction?

[Antony Gelberg]

I have a table "delivery" with a "status" column (yes, I still think in tables, not sure if this is a cause for concern...). I need to refactor it to track status history rather than just the current status. I would want to be able to query this data later to present history to the user.

I thought of something like splitting into tables "delivery" and "delivery_statuses", with timestamps as the delivery goes from status to status e.g. new -> in_progress -> delivered etc.

Then I thought this seems like a common-enough pattern. Is there any sqlalchemy magic should I be aware of for this type of scenario? Are hybrid attributes relevant? e.g. I can see that I'll want to easily query a delivery for its (current) status, and that could get messy. I have skimmed the hybrid attributes docs, but at the moment they're a bit Greek / new to me. However I do note that they have a section on hybrid attributes with relationships.

On IRC, I was also recommended to check out versioning, which seems to be an example rather than an integral part of sqlalchemy, and I'm not finding it any simpler to parse those docs.

Or is this a job for association proxy, which I'd always assumed was for simplifying many-to-many relationships, not a one-to-many like this. (

(In fact, this becomes a one-to-one relationship, once I limit "delivery_statuses" to the latest row.)

I appreciate any insight into the "best" way to build such a structure in sqlalchemy, and I'm happy to clarify my requirements more, feel free to ask me if anything is unclear.

[Mike Bayer]

On Sat, May 25, 2019, at 5:10 AM, Antony Gelberg wrote:

I have a table "delivery" with a "status" column (yes, I still think in tables, not sure if this is a cause for concern...). I need to refactor it to track status history rather than just the current status. I would want to be able to query this data later to present history to the user.

I thought of something like splitting into tables "delivery" and "delivery_statuses", with timestamps as the delivery goes from status to status e.g. new -> in_progress -> delivered etc.

sounds good

Then I thought this seems like a common-enough pattern. Is there any sqlalchemy magic should I be aware of for this type of scenario?

ORM events like SessionEvents.before_flush() or MapperEvents.before_update / MapperEvents.after_update are likely good candidates for this, if you are trying to have it so that in Python, you set just one value that "magically"becomes a new row in a table.      But you could also just use regular Python idioms like descriptors to create a new DeliveryStatus() object and add it to a Delivery.statuses collection when the "status" is set.    there are many ways to do it, start with the most simplistic and non-automated then think where you want to save on verbosity before using any special tricks.

Are hybrid attributes relevant? e.g. I can see that I'll want to easily query a delivery for its (current) status, and that could get messy.

I'd probably keep a column "current_status" in "delivery" which is a foreign key to a single row in delivery_statuses, then use many-to-one relationship which tends to work out cheaply.    hybrids are useful only when their use becomes apparent.    the pattern here would involve two separate foreign keys which requires the post_update flag documented at https://docs.sqlalchemy.org/en/13/orm/relationship_persistence.html#rows-that-point-to-themselves-mutually-dependent-rows .

the example here is, a "person" has a list of "toys", and then they also have a "favorite toy".   Or anything like that.    Related table has a foreign key back to the parent table, but the parent table also has a foreign key to a single record in that related collection.

I have skimmed the hybrid attributes docs, but at the moment they're a bit Greek / new to me. However I do note that they have a section on hybrid attributes with relationships.

start by doing things more simply and explicitly.  simple many-to-one relationship.  then work with that code so that it feels natural.  when you see the things you keep having to do repeatedly, then patterns like hybrids or association proxies will feel more natural to integrate.

On IRC, I was also recommended to check out versioning, which seems to be an example rather than an integral part of sqlalchemy, and I'm not finding it any simpler to parse those docs.

versioned_rows is an example of the SessionEvents.before_flush() event but this is more geared towards a table where no row is to ever be deleted or changed, which is overkill for what you are looking for.

Or is this a job for association proxy, which I'd always assumed was for simplifying many-to-many relationships, not a one-to-many like this. (

(In fact, this becomes a one-to-one relationship, once I limit "delivery_statuses" to the latest row.)

I can see assoc proxy here but as things are new to you, start simple.    table setup, then simple mapping, then work with that, then see where you need automation based on how you want your python code to work, e.g., "I don't want to make a new DeliveryStatus() object each time, I want it to be automatically created".   on the query side, decide if you want a link to the "current" status or not.

I appreciate any insight into the "best" way to build such a structure in sqlalchemy, and I'm happy to clarify my requirements more, feel free to ask me if anything is unclear.

everything is based on what you want to do in the Python code that uses these mappings, start with what you want to see happen first.   "I create a Delivery object, then assign delivery.status = "some string"" or it could be, "I create a Delivery object,then a DeliveryStatus object and say delivery.statuses.append(DeliveryStatus)", or it could be, "I load a Delivery object,then say delivery_object.update_status()", etc., all three of these totally made up patterns have different implications for where things might be coded.

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1f3c39d7-a5ce-4854-bbf2-fbf2456b0ecb%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.