Generated Field
Paolo Melchiorre
I am at PyCon DE 2022 in Berlin with Markus and I shared with him this
idea that I have been thinking about for a few months, given his
interest I also share it with you.
I figured we could add in Django a "GeneratedField" which accepts a
"base_field" attribute in a similar way to "ArrayField" and then an
expression to generate a value for the field.
For example this model:
class Album(models.Model):
...
title = models.CharField(max_length=120)
search = GeneratedField(
SearchVectorField(),
F('title')
)
would generate an SQL code like this in PostgreSQL:
CREATE TABLE album (
...
title char(120),
search tsvector GENERATED ALWAYS AS title STORED
);
I found some documentation for different database backend about
generated column (or similar names)
# SQLite
https://www.sqlite.org/gencol.html
https://www.sqlite.org/releaselog/3_31_0.html
# PostgreSQL
https://www.postgresql.org/docs/12/ddl-generated-columns.html
https://www.postgresql.org/docs/12/release-12.html
# Oracle
https://oracle-base.com/articles/11g/virtual-columns-11gr1
# MySQL
https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
# MariaDB
https://mariadb.com/kb/en/changes-improvements-in-mariadb-102/
https://mariadb.com/kb/en/generated-columns/
I'd love to hear your thoughts on this idea and if you think it might
be something worth continuing to investigate.
Greetings from PyCon DE 2022 in Berlin,
Paolo
--
https://www.paulox.net
Adam Johnson
--
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/CAKFO%2Bx5GHUEVdzi2awYtH5C17tTPTPh%2ByoDP%3DKC18pF8%2Bi_7PA%40mail.gmail.com.
Mariusz Felisiak
Kye Russell
Kye
--
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/5bf85475-4d97-4249-bd68-baf9906d9fffn%40googlegroups.com.
charettes
Adam Johnson
What about we make the expected signature `GeneratedField(expression, base_field=None)` where a missing `base_field` defaults to `expression.output_field`? That would allow the exact expected SQL to be generated with `GeneratedField('title', base_field=SearchVectorField())` if there's a requirement for it.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3887ba35-fa4d-4360-badb-58097c57c59bn%40googlegroups.com.
Dan Davis
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM2CWm0HOkLC%3DBjqQ92928L%3DD%3DXOZzUtA1cHdgc235oYRQ%40mail.gmail.com.
Jeremy Nauta
from django.db.backends.utils import CursorWrapper
from django.db.models import Expression, Field
from django.db.models.sql import Query
class GeneratedField(Field):
"""
Wrapper field used to support generated columns in postgres.
"""
def __init__(self, expression: Expression, db_collation: str = None, *args, **kwargs):
"""
:param expression: DB expression used to calculate the auto-generated field value
"""
self.expression = expression
self.db_collation = db_collation
kwargs['editable'] = False # This field can never be edited
kwargs['blank'] = True # This field never requires a value to be set
kwargs['null'] = True # This field never requires a value to be set
super().__init__(*args, **kwargs)
def _compile_expression(self, cursor: CursorWrapper, sql: str, params: dict):
"""
Compiles SQL and its associated parameters into a full SQL query. Usually sql params are kept
separate until `cursor.execute()` is called, but this is not possible since this function
must return a single sql string.
"""
return cursor.mogrify(sql, params).decode()
def db_type(self, connection):
"""
Called when calculating SQL to create DB column (e.g. DB migrations)
https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.Field.db_type
"""
db_type = self.expression.output_field.db_type(connection=connection)
# Convert any F() references to concrete field names
query = Query(model=self.model, alias_cols=False)
expression = self.expression.resolve_expression(query, allow_joins=False)
# Compile expression into SQL
expression_sql, params = expression.as_sql(
compiler=connection.ops.compiler('SQLCompiler')(
query, connection=connection, using=None
),
connection=connection,
)
with connection.cursor() as cursor:
expression_sql = self._compile_expression(
cursor=cursor, sql=expression_sql, params=params
)
return f'{db_type} GENERATED ALWAYS AS ({expression_sql}) STORED'
def rel_db_type(self, connection):
"""
Called when calculating SQL to reference DB column
https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.Field.rel_db_type
"""
return self.expression.output_field.db_type(connection=connection)
def deconstruct(self):
"""
Add custom field properties to allow migrations to deconstruct field
https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.Field.deconstruct
"""
name, path, args, kwargs = super().deconstruct()
kwargs['expression'] = self.expression
if self.db_collation is not None:
kwargs['db_collation'] = self.db_collation
return name, path, args, kwargs
class GeneratedFieldQuerysetMixin:
"""
Must be added to queryset classes
"""
def _insert(self, objs, fields, *args, **kwargs):
if getattr(self.model, '_generated_fields', None) and fields:
# Don't include generated fields when performing a `model.objects.bulk_create()`
fields = [f for f in fields if f not in self.model._generated_fields()]
return super()._insert(objs, fields, *args, **kwargs)
class GeneratedFieldModelMixin:
"""
Must be added to model class
"""
def _generated_fields(cls) -> list[Field]:
"""
:return all fields of the model that are generated
"""
return [
f
for f in cls._meta.fields
if isinstance(f, GeneratedField)
]
def _do_insert(self, manager, using, fields, *args, **kwargs):
generated_fields = self._generated_fields()
if generated_fields and fields:
# Don't include generated fields when performing a `save()` or `create()`
fields = [f for f in fields if f not in generated_fields]
return super()._do_insert(manager, using, fields, *args, **kwargs)
def _do_update(self, base_qs, using, pk_val, values, *args, **kwargs):
generated_fields = self._generated_fields()
if generated_fields and values:
# Don't include generated fields when performing an `update()`
values = [v for v in values if v[0] not in generated_fields]
return super()._do_update(base_qs, using, pk_val, values, *args, **kwargs)
schinckel
Jeremy Nauta
https://github.com/django/django/pull/16417