Comment gérer des données hiérarchiques avec Django et PostgreSQL

@k4nar

La solution classique: django-mptt

5 champs sur le modèle:
```
parent, tree_id, level, left, right
```
Maintient l'ordre des nœuds adjacents
Alternative: django-treebeard

La solution classique: django-mptt

Requêtes complexes, difficiles à écrire en pur SQL
Trop de hacks avec l'ORM, difficile d'optimiser les requêtes

Une insertion entraîne de nombreuses écritures
>99% de bloat sur la table

Problèmes en écriture

Problèmes en lecture

La solution PostgreSQL: ltree

Extension incluse dans Postgres depuis la 8.3 (2008)
Algorithme des "Materialized Path"
Type de champ avec des opérateurs associés

La solution PostgreSQL: ltree

Exemple de requêtes

SELECT * FROM categories WHERE path <@ 'Top.Science'

+--------------+----------------------------------+
| name         | path                             |
|--------------+----------------------------------|
| Science      | Top.Science                      |
| Biology      | Top.Science.Biology              |
| Genetics     | Top.Science.Biology.Genetics     |
| Neuroscience | Top.Science.Biology.Neuroscience |
| Maths        | Top.Science.Maths                |
+--------------+----------------------------------+

La solution PostgreSQL: ltree

Exemple de requêtes

SELECT * FROM categories WHERE path @> 'Top.Science.Biology'

+---------+---------------------+
| name    | path                |
|---------+---------------------|
| Top     | Top                 |
| Science | Top.Science         |
| Biology | Top.Science.Biology |
+---------+---------------------+

La solution PostgreSQL: ltree

Exemple de requêtes

SELECT * FROM categories WHERE path ~ 'Top.Science.*{1}'

+---------+---------------------+
| name    | path                |
|---------+---------------------|
| Biology | Top.Science.Biology |
| Maths   | Top.Science.Maths   |
+---------+---------------------+

La solution PostgreSQL: ltree

Avantages

Inconvénients

Un seul champ à maintenir
Champ indexable & opérateurs optimisés
Très simple à reconstruire ou débugguer

L'ordre des nœuds n'est pas conservé
Nécessite de construire le chemin

Intégrer ltree à Django

Le modèle

from django.db import models

from .ltree import LtreeField


class Category(models.Model):
    parent = models.ForeignKey('self', null=True, related_name='children')
    name = models.CharField(max_length=32)
    path = LtreeField()

Intégrer ltree à Django

Définition du champ

from django.db import models


class LtreeField(models.TextField):
    description = 'ltree'

    def __init__(self, *args, **kwargs):
        kwargs['editable'] = False
        kwargs['null'] = True
        kwargs['default'] = None
        super(LtreeField, self).__init__(*args, **kwargs)

    def db_type(self, connection):
        return 'ltree'

Intégrer ltree à Django

Définition des lookups

class Ancestor(models.Lookup):
    lookup_name = 'ancestor'

    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return '%s @> %s' % (lhs, rhs), params


class Descendant(models.Lookup):
    lookup_name = 'descendant'

    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return '%s <@ %s' % (lhs, rhs), params

Intégrer ltree à Django

Migration Django

class Migration(migrations.Migration):

    dependencies = [
        ('categories', '0001_initial'),
    ]

    operations = [
        CreateExtension('ltree'),
        migrations.AddField(
            model_name='category',
            name='path',
            field=demo.categories.ltree.LtreeField(
                editable=False, null=True, default=None
            ),
        ),
        migrations.RunSQL(get_sql('index.sql')),
        migrations.RunSQL(get_sql('constraint.sql')),
        migrations.RunSQL(get_sql('triggers.sql')),
    ]

Intégrer ltree à Django

Indexes

-- used when we access the path directly
CREATE INDEX categories_category_path
          ON categories_category
       USING btree(path);

-- used when we get descendants or ancestors
CREATE INDEX categories_category_path_gist
          ON categories_category
       USING GIST(path);

Intégrer ltree à Django

Contrainte d'intégrité

-- make sure we cannot have a path where one of the ancestor is the row itself
-- (this would cause an infinite recursion)
ALTER TABLE categories_category
        ADD CONSTRAINT check_no_recursion
            CHECK(index(path, name::text::ltree) = (nlevel(path) - 1));

Intégrer ltree à Django

Construire le chemin: les triggers

-- calculate the path every time we insert a new category
DROP TRIGGER IF EXISTS category_path_insert_trg ON categories_category;
CREATE TRIGGER category_path_insert_trg
               BEFORE INSERT ON categories_category
               FOR EACH ROW
               EXECUTE PROCEDURE _update_category_path();


-- calculate the path when updating the parent or the name
DROP TRIGGER IF EXISTS category_path_update_trg ON categories_category;
CREATE TRIGGER category_path_update_trg
               BEFORE UPDATE ON categories_category
               FOR EACH ROW
               WHEN (OLD.parent_id IS DISTINCT FROM NEW.parent_id
                     OR OLD.name IS DISTINCT FROM NEW.name)
               EXECUTE PROCEDURE _update_category_path();

Intégrer ltree à Django

Construire le chemin: la fonction

-- function to calculate the path of any given category
CREATE OR REPLACE FUNCTION _update_category_path() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path = NEW.name::ltree;
    ELSE
        SELECT path || NEW.name
          FROM categories_category
         WHERE NEW.parent_id IS NULL or id = NEW.parent_id
          INTO NEW.path;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Intégrer ltree à Django

Mettre à jour les enfants: le trigger

-- if the path was updated, update the path of the descendants
DROP TRIGGER IF EXISTS category_path_after_trg ON categories_category;
CREATE TRIGGER category_path_after_trg
               AFTER UPDATE ON categories_category
               FOR EACH ROW
               WHEN (NEW.path IS DISTINCT FROM OLD.path)
               EXECUTE PROCEDURE _update_descendants_category();

Intégrer ltree à Django

Mettre à jour les enfants: la fonction

-- function to update the path of the descendants of a category
CREATE OR REPLACE FUNCTION _update_descendants_category() RETURNS TRIGGER AS
$$
BEGIN
    UPDATE categories_category AS cc
       SET path = NEW.path || subpath(cc.path, nlevel(OLD.path))
     WHERE cc.path <@ OLD.path AND id != NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Intégrer ltree à Django

Exemple de requêtes

>>> Category.objects.filter(path__descendant='Top.Science')
<QuerySet [
    <Category: Top.Science>,
    <Category: Top.Science.Biology>,
    <Category: Top.Science.Biology.Genetics>,
    <Category: Top.Science.Biology.Neuroscience>,
    <Category: Top.Science.Maths>
]>

Intégrer ltree à Django

Exemple de requêtes

>>> Category.objects.filter(path__ancestor='Top.Science.Biology')
<QuerySet [
    <Category: Top>,
    <Category: Top.Science>,
    <Category: Top.Science.Biology>
]>

Tout le code est disponible ici:

https://github.com/peopledoc/django-ltree-demo