Implement type-level sorting for Enum; apply to ORM primary keys

Added support for the use of an :class:`.Enum` datatype using Python
pep-435 enumeration objects as values for use as a primary key column
mapped by the ORM.  As these values are not inherently sortable, as
required by the ORM for primary keys, a new
:attr:`.TypeEngine.sort_key_function` attribute is added to the typing
system which allows any SQL type to  implement a sorting for Python objects
of its type which is consulted by the unit of work.   The :class:`.Enum`
type then defines this using the  database value of a given enumeration.
The sorting scheme can be  also be redefined by passing a callable to the
:paramref:`.Enum.sort_key_function` parameter.  Pull request courtesy
Nicolas Caniart.

Fixes: #4285
Closes: #4816
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4816
Pull-request-sha: 42266b766c1e462d5b8a409cda05d33dea13bd34

Change-Id: Iadcc16173c1ba26ffac5830db57743a4cb987c55

2 files changed, 36 insertions, 0 deletions

diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 631352ceb..fd15d7c79 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -20,6 +20,7 @@ from . import operators
 from . import roles
 from . import type_api
 from .base import _bind_or_error
+from .base import NO_ARG
 from .base import SchemaEventTarget
 from .elements import _defer_name
 from .elements import quoted_name
@@ -1356,6 +1357,19 @@ class Enum(Emulated, String, SchemaType):
 
            .. versionadded:: 1.2.3
 
+        :param sort_key_function: a Python callable which may be used as the
+           "key" argument in the Python ``sorted()`` built-in.   The SQLAlchemy
+           ORM requires that primary key columns which are mapped must
+           be sortable in some way.  When using an unsortable enumeration
+           object such as a Python 3 ``Enum`` object, this parameter may be
+           used to set a default sort key function for the objects.  By
+           default, the database value of the enumeration is used as the
+           sorting function.
+
+            .. versionadded:: 1.3.8
+
+
+
         """
         self._enum_init(enums, kw)
 
@@ -1377,6 +1391,7 @@ class Enum(Emulated, String, SchemaType):
         self.native_enum = kw.pop("native_enum", True)
         self.create_constraint = kw.pop("create_constraint", True)
         self.values_callable = kw.pop("values_callable", None)
+        self._sort_key_function = kw.pop("sort_key_function", NO_ARG)
 
         values, objects = self._parse_into_values(enums, kw)
         self._setup_for_values(values, objects, kw)
@@ -1450,6 +1465,13 @@ class Enum(Emulated, String, SchemaType):
         )
 
     @property
+    def sort_key_function(self):
+        if self._sort_key_function is NO_ARG:
+            return self._db_value_for_elem
+        else:
+            return self._sort_key_function
+
+    @property
     def native(self):
         return self.native_enum
 
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index 9838f0d5a..11407ad2e 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -135,6 +135,16 @@ class TypeEngine(Visitable):
 
     """
 
+    sort_key_function = None
+    """A sorting function that can be passed as the key to sorted.
+
+    The default value of ``None`` indicates that the values stored by
+    this type are self-sorting.
+
+    .. versionadded:: 1.3.8
+
+    """
+
     should_evaluate_none = False
     """If True, the Python constant ``None`` is considered to be handled
     explicitly by this type.
@@ -1354,6 +1364,10 @@ class TypeDecorator(SchemaEventTarget, TypeEngine):
         """
         return self.impl.compare_values(x, y)
 
+    @property
+    def sort_key_function(self):
+        return self.impl.sort_key_function
+
     def __repr__(self):
         return util.generic_repr(self, to_inspect=self.impl)