Thanks for your replies.

I have looked at the other options available. While they are suitable for 
version control, they're not well suited for queries where you want to see 
the state of a given object at a given time. "Show me what X looked like on 
Y date" can be useful, and shouldn't require a reversion. In this case, I'm 
experimenting with developing an implementation of the Memento [1] standard 
within Django. 

http://mementoweb.org/guide/rfc/

I've figured out how to modify the SELECT query, and now I'm working on 
UPDATE. For select you need to subclass the Compiler and implement a custom 
`get_from_clause` method. UPDATE is a trickier, but I've made some progress.

Like I said, it may be stopped in its tracks when it comes to schema 
changes and migrations, but for now I'm satisfied with the amount of 
progress I'm making; it's all about finding which knobs to turn! 

Temporal Tables are a fairly well-established, if advanced, pattern in SQL 
servers, and is part of the spec in SQL 2011.

On Wednesday, 13 September 2017 18:36:46 UTC+1, Matthew Pava wrote:
>
> If you want to do auditing of the database, I would look at packages 
> available for Django.  This is the first I've read about "temporal tables," 
> and my first impression is that of grave concern. 
> Django-reversion is a popular package: 
> https://github.com/etianen/django-reversion 
>
>
> -----Original Message----- 
> From: django...@googlegroups.com <javascript:> [mailto:
> django...@googlegroups.com <javascript:>] On Behalf Of Christian 
> Ledermann 
> Sent: Wednesday, September 13, 2017 12:00 PM 
> To: django...@googlegroups.com <javascript:> 
> Subject: Re: FROM ONLY in Postgres back-end 
>
> On 12 September 2017 at 14:32, Andrew Hankinson <andrew.h...@gmail.com 
> <javascript:>> wrote: 
> > Hello, 
> > 
> > tl;dr I'm trying to implement 'temporal tables' [0] in Django with a 
> > Postgres backend, and I'm stuck on how to generate the appropriate 
> > SELECT ... FROM ONLY and UPDATE queries. 
> > 
> > The temporal tables pattern depends on having two mirror tables. For a 
> > simple example, let's call them "person" and "person_history". As 
> > records are changed in "person," the previous states of that table are 
> > stored in "person_history". 
> > 
> > The simplest way to implement table mirroring is to use "INHERITS" in 
> > Postgres, e.g., 
> > 
> > CREATE TABLE person_history () INHERITS (person); 
> > 
> > This is the simplest since any changes to the 'person' schema will 
> > automatically be reflected in person_history. (And no, I haven't 
> > tested schema changes and column renaming and interactions with 
> > migrations with Django just yet... it will come later). I've 
> > implemented this as Operations in the table migrations. 
> > 
> > When a table linked by "INHERITS" is queried, it will query both the 
> > 'person' and 'person_history' tables by default. This will obviously 
> > break a lot of things in Django. (e.g., a .get() will return more than 
> > one row...) The easiest way around that is to change "FROM" to "FROM 
> > ONLY" in all SQL queries, e.g., 
> > 
> > SELECT name FROM ONLY person WHERE id = 1; 
> > 
> > The same goes for UPDATE. 
> > 
> > The part where I'm stuck is trying to figure out at what level I start 
> > digging. 
>
> It depends 
>
> > Do I modify QuerySet to try and implement my own custom lookup code? 
>
> you can always do raw SQL queries, but that kind of defeats the purpose of 
> having an ORM. 
>
> > Or will I need to dig into the SQL compiler to do this? 
>
> Likely. 
>
> > Or is this 
> > whole thing a waste of my time? 
>
> even more likely 
>
> > 
> > Or maybe there's a magic hook somewhere I'm missing that will let me 
> > inject what I need. I'm hoping for this one, but I haven't gotten my 
> > hopes up. :) 
> > 
> > Thanks in advance for your help, 
> > -Andrew 
> > 
> > [0] 
> > http://clarkdave.net/2015/02/historical-records-with-postgresql-and-te 
> > mporal-tables-and-sql-2011/ 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> > Groups "Django users" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to django-users...@googlegroups.com <javascript:>. 
> > To post to this group, send email to django...@googlegroups.com 
> <javascript:>. 
> > Visit this group at https://groups.google.com/group/django-users. 
> > To view this discussion on the web visit 
> > 
> https://groups.google.com/d/msgid/django-users/f3423173-d0ab-4c7c-a5af-1592947f07ca%40googlegroups.com.
>  
>
> > For more options, visit https://groups.google.com/d/optout. 
>
>
>
> -- 
> Best Regards, 
>
> Christian Ledermann 
>
> Newark-on-Trent - UK 
> Mobile : +44 7474997517 
>
> https://uk.linkedin.com/in/christianledermann 
> https://github.com/cleder/ 
>
>
> <*)))>{ 
>
> If you save the living environment, the biodiversity that we have left, 
> you will also automatically save the physical environment, too. But If you 
> only save the physical environment, you will ultimately lose both. 
>
> 1) Don’t drive species to extinction 
>
> 2) Don’t destroy a habitat that species rely on. 
>
> 3) Don’t change the climate in ways that will result in the above. 
>
> }<(((*> 
>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "Django users" group. 
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to django-users...@googlegroups.com <javascript:>. 
> To post to this group, send email to django...@googlegroups.com 
> <javascript:>. 
> Visit this group at https://groups.google.com/group/django-users. 
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/django-users/CABCjzWqk-8p%2BqN9mLUfMuW2kAjP1Z7%3Dx8s3im78ARmsXrqkPjA%40mail.gmail.com.
>  
>
> For more options, visit https://groups.google.com/d/optout. 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/795782a9-9395-409b-b2c8-d0c72d83ce2f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to