WeRiot Django Timescale integration

timescale--db--backend--base.py

import logging

from django.contrib.gis.db.backends.postgis.base import \
    DatabaseWrapper as PostgisDBWrapper
from django.db import ProgrammingError

from .schema import TimescaleSchemaEditor

logger = logging.getLogger(__name__)


class DatabaseWrapper(PostgisDBWrapper):
    SchemaEditorClass = TimescaleSchemaEditor

    def prepare_database(self):
        """Prepare the configured database.
        This is where we enable the `timescaledb` extension
        if it isn't enabled yet."""

        super().prepare_database()
        with self.cursor() as cursor:
            try:
                cursor.execute('CREATE EXTENSION IF NOT EXISTS timescaledb')
            except ProgrammingError:  # permission denied
                logger.warning(
                    'Failed to create "timescaledb" extension. '
                    'Usage of timescale capabilities might fail'
                    'If timescale is needed, make sure you are connected '
                    'to the database as a superuser '
                    'or add the extension manually.',
                    exc_info=True
                )

timescale--db--backend--schema.py

from django.contrib.gis.db.backends.postgis.schema import PostGISSchemaEditor

from timescale.fields import TimescaleDateTimeField


class TimescaleSchemaEditor(PostGISSchemaEditor):
    sql_add_hypertable = (
        "SELECT create_hypertable("
        "{table}, {partition_column}, "
        "chunk_time_interval => interval {interval})"
    )

    sql_drop_primary_key = (
        'ALTER TABLE {table} '
        'DROP CONSTRAINT {pkey}'
    )

    def drop_primary_key(self, model):
        """
        Hypertables can't partition if the primary key is not
        the partition column.

        So we drop the mandatory primary key django creates.
        """
        db_table = model._meta.db_table
        table = self.quote_name(db_table)
        pkey = self.quote_name(f'{db_table}_pkey')

        sql = self.sql_drop_primary_key.format(table=table, pkey=pkey)

        self.execute(sql)

    def create_hypertable(self, model, field):
        """
        Create the hypertable with the partition column being the field.
        """
        partition_column = self.quote_value(field.column)
        interval = self.quote_value(field.interval)
        table = self.quote_value(model._meta.db_table)

        sql = self.sql_add_hypertable.format(
            table=table, partition_column=partition_column, interval=interval
        )

        self.execute(sql)

    def create_model(self, model):
        super().create_model(model)

        for field in model._meta.local_fields:
            if not isinstance(field, TimescaleDateTimeField):
                continue

            self.drop_primary_key(model)
            self.create_hypertable(model, field)

timescale--expressions.py

from django.db import models


class TimeBucket(models.Func):
    function = 'time_bucket'

    def __init__(self, expression, interval):
        if not isinstance(interval, models.Value):
            interval = models.Value(interval)
        super().__init__(interval, expression)

timescale--fields.py

from django.db.models import DateTimeField


class TimescaleDateTimeField(DateTimeField):
    def __init__(self, *args, interval, **kwargs):
        self.interval = interval
        super().__init__(*args, **kwargs)

    def deconstruct(self):
        name, path, args, kwargs = super().deconstruct()
        kwargs['interval'] = self.interval

        return name, path, args, kwargs

no problem, I hope to have time in the near future to make this gist a full fledged django package

Working on a standalone project based off this gist - https://github.com/schlunsen/django-timescaledb

Help and pull requests are more than welcome

@schlunsen nice!

Was it a conscious choice to use Postgis as a Base engine? Perhaps it would make sense to have TimescaleDB mixin classes and then inherit them along with Postgres / Postgis to form TimescalePostgresWrapper / TimescalePostgisWrapper and similar schema editor classes?

P.S. Should we continue this discussion here or would you prefer an issue / PR in your repo?

@schlunsen nice!

Was it a conscious choice to use Postgis as a Base engine? Perhaps it would make sense to have TimescaleDB mixin classes and then inherit them along with Postgres / Postgis to form TimescalePostgresWrapper / TimescalePostgisWrapper and similar schema editor classes?

P.S. Should we continue this discussion here or would you prefer an issue / PR in your repo?

@moorchegue Thanks for the feedback!

No it wasn't really conscious choice, but I like your solution and will update accordingly when I have time.

A pull request is also more than welcome.

@schlunsen that's awesome, about the Base engine, I used it like that because I know I'll use postgis, but in all my todo list I had for a separate project I wanted to implement something like https://github.com/SectorLabs/django-postgres-extra/blob/master/psqlextra/backend/base_impl.py that deals with it nicely, I also based most of the work for timescale with that project.

Hopefully I'll have enough time to make some PRs in the new project.

This has been implemented in now in https://github.com/schlunsen/django-timescaledb

I've found a way to make django Autofield to be virtual, so django does not add it to queries. This makes possible to connect to timescale db, without database modifications

https://viewflow.medium.com/the-django-compositeforeignkey-field-get-access-to-a-legacy-database-without-altering-db-tables-74abc9868026

this gist and in consequence the django-timescaledb goes beyond accessing a preexisting table and you can create, and hopefully in a future be able to modify via migrations the details of the tables, along with some class helpers to help with the time series queries.

@dedsm yep, removing pk constraint is the nice hack, but without virtual field django still requires to have uniq the id column on the table, that blocks django integration in case of timeseriesdb tables was used in another systems.