Support for temporal and bitemporal data according to SQL:2011

[Riaz Jahangir]

We'd like to set up Django with MariaDB but have a specific need for the temporal features in SQL:2011 -- being able to take advantage of MariaDB's application time and system time periods for business cases and an audit trail. So we couldn't use the standard MySQL backend.

Is this feature already in the pipeline somewhere? We could look at contributing.

[Adam Johnson]

Hi Riaz!

I've had a look at temporal tables too, but without a particular use case. I was thinking about adding them to my library Django-MySQL ( https://django-mysql.readthedocs.io/en/latest/ ), which provides all kinds of Django - MySQL/MariaDB extensions. Temporal tables are indeed not supported by Django core or any library I have found.

For setting up temporal tables, you can use an extra RunSQL operation in the migration that does the CreateModel, to run e.g. "ALTER TABLE t ADD SYSTEM VERSIONING". Any specific migration operation there would be a fairly thin wrapper around this.

Querying them will be slightly harder without modifying the core ORM. I think it can be done (perhaps for simple queries only) by a context manager that inserts some database instrumentation to modify the SQL, similar to how I backported a database feature in this post: https://adamj.eu/tech/2020/07/29/backporting-a-django-orm-feature-with-database-instrumentation/ . I'm imagining a syntax like:

with system_time.as_of(some_datetime, Book):

    books = list(books_qs)

This is a bit clunky but should work.

Ultimately I think this would be a good feature for core Django. Of the core backend databases, it seems that Oracle supports temporal tables too. The most popular third party backend is MS SQL, which also supports temporal tables. And it would need to be an extension to QuerySet for easy support, perhaps a method called QuerySet.as_of(). If you'd like to put together a proposal I'd be interested in helping that get started.

Thanks,

Adam

On Fri, 16 Oct 2020 at 23:45, Riaz Jahangir <ri...@silviaterra.com> wrote:

We'd like to set up Django with MariaDB but have a specific need for the temporal features in SQL:2011 -- being able to take advantage of MariaDB's application time and system time periods for business cases and an audit trail. So we couldn't use the standard MySQL backend.

Is this feature already in the pipeline somewhere? We could look at contributing.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/34960ada-481b-45fd-9f59-81339c992188n%40googlegroups.com.

--

Adam