.. _dynamic-columns-field:
------------
DynamicField
------------
.. currentmodule:: django_mysql.models
**MariaDB** has a feature called **Dynamic Columns** that allows you to store
different sets of columns for each row in a table. It works by storing the data
in a blob and having a small set of functions to manipulate this blob.
(`Docs `_).
Django-MySQL supports the *named* Dynamic Columns of MariaDB 10.0+, as opposed
to the *numbered* format of 5.5+. It uses the
`mariadb-dyncol `_ python package
to pack and unpack Dynamic Columns blobs in Python rather than in MariaDB
(mostly due to limitations in the Django ORM).
.. class:: DynamicField(spec=None, **kwargs)
A field for storing Dynamic Columns. The Python data type is ``dict``. Keys
must be ``str``\s and values must be one of the supported value types in
``mariadb-dyncol``:
* ``str``
* ``int``
* ``float``
* ``datetime.date``
* ``datetime.datetime``
* ``datetime.datetime``
* A nested dict conforming to this spec too
Note that there are restrictions on the range of values supported for some
of these types, and that ``decimal.Decimal`` objects are not yet supported
though they are valid in MariaDB. For more information consult the
``mariadb-dyncol`` documentation.
Values may also be ``None``, though they will then not be stored, since
dynamic columns do not store ``NULL``, so you should use ``.get()`` to
retrieve values that may be ``None``.
To use this field, you'll need to:
1. Use MariaDB 10.0.2+
2. Install ``mariadb-dyncol`` (``python -m pip install mariadb-dyncol``)
3. Use either the ``utf8mb4`` or ``utf8`` character set for your
database connection.
These are all checked by the field and you will see sensible errors for
them when Django's checks run if you have a ``DynamicField`` on a model.
.. attribute:: spec
This is an optional type specification that checks that the named
columns, if present, have the given types. It is validated against on
``save()`` to ensure type safety (unlike normal Django validation which
is only used in forms). It is also used for type information for
lookups (below).
``spec`` should be a ``dict`` with string keys and values that are the
type classes you expect. You can also nest another such dictionary as a
value for validating nested dynamic columns.
For example:
.. code-block:: python
import datetime
class SpecModel(Model):
attrs = DynamicField(
spec={
"an_integer_key": int,
"created_at": datetime.datetime,
"nested_columns": {
"lat": int,
"lon": int,
},
}
)
This will enforce the following rules:
* ``instance.attrs['an_integer_key']``, if present, is an ``int``
* ``instance.attrs['created_at']``, if present, is an ``datetime.datetime``
* ``instance.attrs['nested_columns']``, if present, is a ``dict``
* ``instance.attrs['nested_columns']['lat']``, if present, is an ``int``
* ``instance.attrs['nested_columns']['lon']``, if present, is an ``int``
Trying to save a ``DynamicField`` with data that does not match the
rules of its ``spec`` will raise ``TypeError``. There is no automatic
casting, e.g. between ``int`` and ``float``. Note that columns not in
``spec`` will still be allowed and have no type enforced.
For example:
.. code-block:: pycon
>>> SpecModel.objects.create(attrs={"an_integer_key": 1}) # Fine
>>> SpecModel.objects.create(attrs={"an_integer_key": 2.0})
Traceback (most recent call last):
...
TypeError: Key 'an_integer_key' should be of type 'int'
>>> SpecModel.objects.create(attrs={"non_spec_key": "anytype"}) # Fine
DynamicFields in Forms
----------------------
By default a ``DynamicField`` has no form field, because there isn't really a
practical way to edit its contents. If required, is possible to add extra form
fields to a ``ModelForm`` that then update specific dynamic column names on the
instance in the form's ``save()``.
Querying DynamicField
---------------------
You can query by names, including nested names. In cases where names collide
with existing lookups (e.g. you have a column named ``'exact'``), you might
want to use the :class:`~django_mysql.models.functions.ColumnGet` database
function. You can also use the
:class:`~django_mysql.models.functions.ColumnAdd` and
:class:`~django_mysql.models.functions.ColumnDelete` functions for atomically
modifying the contents of dynamic columns at the database layer.
We'll use the following example model:
.. code-block:: python
from django_mysql.models import DynamicField, Model
class ShopItem(Model):
name = models.CharField(max_length=200)
attrs = DynamicField(
spec={
"size": str,
}
)
def __str__(self):
return self.name
Exact Lookups
~~~~~~~~~~~~~
To query based on an exact match, just use a dictionary.
For example:
.. code-block:: pycon
>>> ShopItem.objects.create(name="Camembert", attrs={"smelliness": 15})
>>> ShopItem.objects.create(name="Cheddar", attrs={"smelliness": 15, "hardness": 5})
>>> ShopItem.objects.filter(attrs={"smelliness": 15})
[]
>>> ShopItem.objects.filter(attrs={"smelliness": 15, "hardness": 5})
[]
Name Lookups
~~~~~~~~~~~~
To query based on a column name, use that name as a lookup with one of the
below SQL types added after an underscore. If the column name is in your
field's ``spec``, you can omit the SQL type and it will be extracted
automatically - this includes keys in nested ``dict``\s.
The list of SQL types is:
* ``BINARY`` - ``dict`` (a nested ``DynamicField``)
* ``CHAR`` - ``str``
* ``DATE`` - ``datetime.date``
* ``DATETIME`` - ``datetime.datetime``
* ``DOUBLE`` - ``float``
* ``INTEGER`` - ``int``
* ``TIME`` - ``datetime.time``
These will also use the correct Django ORM field so chained lookups based on
that type are possible, e.g. ``dynamicfield__age_INTEGER__gte=20``.
Beware that getting a named column can always return ``NULL`` if the column is
not defined for a row.
For example:
.. code-block:: pycon
>>> ShopItem.objects.create(name="T-Shirt", attrs={"size": "Large"})
>>> ShopItem.objects.create(
... name="Rocketship",
... attrs={"speed_mph": 300, "dimensions": {"width_m": 10, "height_m": 50}},
... )
# Basic template: DynamicField + '__' + column name + '_' + SQL type
>>> ShopItem.objects.filter(attrs__size_CHAR="Large")
[]
# As 'size' is in the field's spec, there is no need to give the SQL type
>>> ShopItem.objects.filter(attrs__size="Large")
[]
# Chained lookups are possible based on the data type
>>> ShopItem.objects.filter(attrs__speed_mph_INTEGER__gte=100)
[]
# Nested keys can be looked up
>>> ShopItem.objects.filter(attrs__dimensions_BINARY__width_m_INTEGER=10)
[]
# Nested DynamicFields can be queried as ``dict``s, as per the ``exact`` lookup
>>> ShopItem.objects.filter(attrs__dimensions_BINARY={"width_m": 10, "height_m": 50})
[]
# Missing keys are always NULL
>>> ShopItem.objects.filter(attrs__blablabla_INTEGER__isnull=True)
[, ]