Natively support uniqueidentifier column type

[kevin-brown]

The native column type for UUIDs in SQL Server is uniqueidentifier, and it's been supported since SQL Server 2008 and is also supported in Azure SQL Database.

https://msdn.microsoft.com/en-us/library/ms187942.aspx

Is there any reason why it's not being used in this library? I noticed that a char(32) is being used instead.

[michiya]

Hi,

SQL Server supports native uniqueidentifier type but pyodbc doesn't. See the following pyodbc Wiki for more details. https://github.com/mkleehammer/pyodbc/wiki/Data-Types

That's why django-pyodbc-azure assigns a char(32) column for an UUIDField now as well as Django's standard backend adapters for databases that doesn't support native UUID type. https://docs.djangoproject.com/en/1.10/ref/models/fields/#uuidfield

I hope to have a chance to add support for native UUID type columns to django-pyodbc-azure in the future.

[kevin-brown]

pyodbc appears to have tests that cover uniqueidentifier. It looks like this is wrapped up in the documentation under GUID, which is how they refer to UUIDs (SQL Server doesn't appear to have a GUID column type).

[michiya]

Django passes an UUID value to a backend as a Python UUID object if the backend supports UUID natively, otherwise as a 32-character hexadecimal string without hyphens. Unfortunately no value transformation hook on UUIDField is avaliable for backends unlike other standard model fields. https://github.com/django/django/blob/1.10.5/django/db/models/fields/init.py#L2375-L2377

As you can see in the pyodbc test code, pyodbc only accepts a string value for an uniqueidentifier column. And to make things worse, SQL Server only accepts a 36-character string with hyphens as an uniqueidentifer. It would be easy to implement this feature if one of the following conditions is met in the future, otherwise it would be tricky.

• pyodbc supports UUID natively (acceepts Python UUID object as an uniqueidentifier value)
• SQL Server accepts 32-character hexadecimal string without hyphens as an uniqueidentifier
• Django provides a value transformation hook on UUIDField for backend adapters to add hyphens to 32-character UUID string

[kevin-brown]

I'm going to do some digging to see if there are alternate hook points that we can use for UUIDField. I've noticed that django-mssql has native support for it, but after looking around I can't find many database backends with native support.

pyodbc supports UUID natively (acceepts Python UUID object as an uniqueidentifier value)

I've opened a ticket over at the pyodbc repository about this: https://github.com/mkleehammer/pyodbc/issues/177

[kevin-brown]

My working theory is that since pyodbc is expecting that SQL_GUID is a string, it will convert whatever is passed in to a string. So, since the string version of a UUID object includes the dashes (which is why Django strips them out), the format should line up and it should "just work".

I was able to test this out by enabling the has_native_uuid_field feature temporarily. It appears as though the compilers are generating the right format for a UUID, so that's promising.

>>> from sql_server.pyodbc.features import DatabaseFeatures
>>> DatabaseFeatures.has_native_uuid_field = True
>>> import uuid
>>> test_uuid = uuid.uuid4()
>>> from foo.models import Bar
>>> bars = Bar.objects.filter(id=test_uuid)
>>> str(bars.query)
'SELECT [foo_bar].[id] FROM [foo_bar] WHERE [foo_bar].[id] = 3d6dba9e-5313-419f-914c-33117e7f470c'

When it actually hits pyodbc as a prepared statement though... pyodbc complains, because it doesn't convert it to a string by default. So, back to the drawing board.

Edit: When I forced all UUID params to be strings, it did in fact generate a valid query. But I have not yet found a clean hook point where that can be done.

[kevin-brown]

Good news @michiya, pyodbc now supports UUIDs when passed into a query.

https://github.com/mkleehammer/pyodbc/issues/177

[madthew]

anyone succeeded in using MSSQL uniqueidentifiers?

[saschwarz]

I have an experimental branch that seems to be working: https://github.com/saschwarz/django-pyodbc-azure/commit/a1df832529831c2045f8d0cd8bbd938376d393df

I created a new field type that acts like an AutoField and generates a uniqueidentifier db column that uses newsequentialid() to create GUIDs in the database:

class UniqueIdentifierField(models.UUIDField):

    def db_type(self, connection):
        if self.primary_key:
            return 'uniqueidentifier default (newsequentialid())'
        else:
            return 'uniqueidentifier'

    def rel_db_type(self, connection):
        return 'uniqueidentifier'

    # leave id out of payload on insert
    def contribute_to_class(self, cls, name, **kwargs):
        assert not self.primary_key or (self.primary_key and not cls._meta.auto_field), "A model can't have more than one AutoField."
        super().contribute_to_class(cls, name, **kwargs)
        if self.primary_key:
            cls._meta.auto_field = self

    def get_db_prep_value(self, value, connection, prepared=False):
        if value is None:
            return None
        if not isinstance(value, uuid.UUID):
            value = self.to_python(value)

        return str(value)

    def from_db_value(self, value, expression, connection):
        return self._to_uuid(value)

    def to_python(self, value):
        return self._to_uuid(value)

    def _to_uuid(self, value):
        if value is not None and not isinstance(value, uuid.UUID):
            try:
                return uuid.UUID(value)
            except (AttributeError, ValueError):
                raise exceptions.ValidationError(
                    self.error_messages['invalid'],
                    code='invalid',
                    params={'value': value},
                )
        return value

The field is used like so:

class Action(models.Model):
    id = UniqueIdentifierField(primary_key=True, max_length=36, editable=False, default=None)
    text = models.CharField(
        max_length=512,
        help_text="Template string for the action string shown in-app")
    ...

[njho]

Is @saschwarz solution the accepted solution for this issue at this time?

[aceofwings]

Hi It seems that pyodbc now supports native UUID types now. Can it be safe to say that the features for this plugin be updated to include? has_native_uuid_field = True

[aceofwings]

I have forked over the repository with the change for UUID native enabled.

https://github.com/aceofwings/django-pyodbc-azure/blob/azure-2.1/README.rst

[aceofwings]

Hi all,

Seems that the support for this package has gone dormant.

Please checkout this project for updates

https://github.com/ESSolutions/django-mssql-backend

[thaibee]

I have an experimental branch that seems to be working: saschwarz@a1df832

I created a new field type that acts like an AutoField and generates a uniqueidentifier db column that uses newsequentialid() to create GUIDs in the database:

class UniqueIdentifierField(models.UUIDField):

    def db_type(self, connection):
        if self.primary_key:
            return 'uniqueidentifier default (newsequentialid())'
        else:
            return 'uniqueidentifier'

    def rel_db_type(self, connection):
        return 'uniqueidentifier'

    # leave id out of payload on insert
    def contribute_to_class(self, cls, name, **kwargs):
        assert not self.primary_key or (self.primary_key and not cls._meta.auto_field), "A model can't have more than one AutoField."
        super().contribute_to_class(cls, name, **kwargs)
        if self.primary_key:
            cls._meta.auto_field = self

    def get_db_prep_value(self, value, connection, prepared=False):
        if value is None:
            return None
        if not isinstance(value, uuid.UUID):
            value = self.to_python(value)

        return str(value)

    def from_db_value(self, value, expression, connection):
        return self._to_uuid(value)

    def to_python(self, value):
        return self._to_uuid(value)

    def _to_uuid(self, value):
        if value is not None and not isinstance(value, uuid.UUID):
            try:
                return uuid.UUID(value)
            except (AttributeError, ValueError):
                raise exceptions.ValidationError(
                    self.error_messages['invalid'],
                    code='invalid',
                    params={'value': value},
                )
        return value

The field is used like so:

class Action(models.Model):
    id = UniqueIdentifierField(primary_key=True, max_length=36, editable=False, default=None)
    text = models.CharField(
        max_length=512,
        help_text="Template string for the action string shown in-app")
    ...

Thank you very much. It's working very well!