diff options
| -rw-r--r-- | doc/build/changelog/changelog_11.rst | 17 | ||||
| -rw-r--r-- | doc/build/changelog/migration_11.rst | 29 | ||||
| -rw-r--r-- | doc/build/orm/persistence_techniques.rst | 106 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/mapper.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/type_api.py | 65 | ||||
| -rw-r--r-- | test/orm/test_unitofworkv2.py | 47 |
7 files changed, 259 insertions, 11 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 2f49a6bdb..e20e0b4ca 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,21 @@ :version: 1.1.0b1 .. change:: + :tags: bug, orm + :tickets: 3250 + + Added a new type-level modifier :meth:`.TypeEngine.evaluates_none` + which indicates to the ORM that a positive set of None should be + persisted as the value NULL, instead of omitting the column from + the INSERT statement. This feature is used both as part of the + implementation for :ticket:`3514` as well as a standalone feature + available on any type. + + .. seealso:: + + :ref:`change_3250` + + .. change:: :tags: bug, postgresql :tickets: 2729 @@ -278,6 +293,8 @@ :ref:`change_3514` + :ref:`change_3250` + .. change:: :tags: feature, postgresql :tickets: 3514 diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index 21c976589..81c438e06 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -16,7 +16,7 @@ What's New in SQLAlchemy 1.1? some issues may be moved to later milestones in order to allow for a timely release. - Document last updated: September 2, 2015 + Document last updated: September 19, 2015 Introduction ============ @@ -233,6 +233,26 @@ relationship attribute to an object, which is handled distinctly:: :ticket:`3321` +.. _change_3250: + +New options allowing explicit persistence of NULL over a default +---------------------------------------------------------------- + +Related to the new JSON-NULL support added to Postgresql as part of +:ref:`change_3514`, the base :class:`.TypeEngine` class now supports +a method :meth:`.TypeEngine.evaluates_none` which allows a positive set +of the ``None`` value on an attribute to be persisted as NULL, rather than +omitting the column from the INSERT statement, which has the effect of using +the column-level default. This allows a mapper-level +configuration of the existing object-level technique of assigning +:func:`.sql.null` to the attribute. + +.. seealso:: + + :ref:`session_forcing_null` + +:ticket:`3250` + New Features and Improvements - Core ==================================== @@ -671,7 +691,8 @@ method were used, ``None`` would be ignored in all cases:: MyObject, [{"json_value": None}]) # would insert SQL NULL and/or trigger defaults -The :class:`.JSON` type now adds a new flag :attr:`.TypeEngine.evaluates_none` +The :class:`.JSON` type now implements the +:attr:`.TypeEngine.should_evaluate_none` flag, indicating that ``None`` should not be ignored here; it is configured automatically based on the value of :paramref:`.JSON.none_as_null`. Thanks to :ticket:`3061`, we can differentiate when the value ``None`` is actively @@ -693,7 +714,9 @@ previously. Below, the two variants are illustrated:: .. seealso:: - :ref:`change_3514_jsonnull` + :ref:`change_3250` + + :ref:`change_3514_jsonnull` .. _change_3514_jsonnull: diff --git a/doc/build/orm/persistence_techniques.rst b/doc/build/orm/persistence_techniques.rst index aee48121d..a30d486b5 100644 --- a/doc/build/orm/persistence_techniques.rst +++ b/doc/build/orm/persistence_techniques.rst @@ -78,6 +78,112 @@ proper context for the desired engine:: connection = session.connection(MyMappedClass) +.. _session_forcing_null: + +Forcing NULL on a column with a default +======================================= + +The ORM considers any attribute that was never set on an object as a +"default" case; the attribute will be omitted from the INSERT statement:: + + class MyObject(Base): + __tablename__ = 'my_table' + id = Column(Integer, primary_key=True) + data = Column(String(50), nullable=True) + + obj = MyObject(id=1) + session.add(obj) + session.commit() # INSERT with the 'data' column omitted; the database + # itself will persist this as the NULL value + +Omitting a column from the INSERT means that the column will +have the NULL value set, *unless* the column has a default set up, +in which case the default value will be persisted. This holds true +both from a pure SQL perspective with server-side defaults, as well as the +behavior of SQLAlchemy's insert behavior with both client-side and server-side +defaults:: + + class MyObject(Base): + __tablename__ = 'my_table' + id = Column(Integer, primary_key=True) + data = Column(String(50), nullable=True, server_default="default") + + obj = MyObject(id=1) + session.add(obj) + session.commit() # INSERT with the 'data' column omitted; the database + # itself will persist this as the value 'default' + +However, in the ORM, even if one assigns the Python value ``None`` explicitly +to the object, this is treated the **same** as though the value were never +assigned:: + + class MyObject(Base): + __tablename__ = 'my_table' + id = Column(Integer, primary_key=True) + data = Column(String(50), nullable=True, server_default="default") + + obj = MyObject(id=1, data=None) + session.add(obj) + session.commit() # INSERT with the 'data' column explicitly set to None; + # the ORM still omits it from the statement and the + # database will still persist this as the value 'default' + +The above operation will persist into the ``data`` column the +server default value of ``"default"`` and not SQL NULL, even though ``None`` +was passed; this is a long-standing behavior of the ORM that many applications +hold as an assumption. + +So what if we want to actually put NULL into this column, even though the +column has a default value? There are two approaches. One is that +on a per-instance level, we assign the attribute using the +:obj:`~.expression.null` SQL construct:: + + from sqlalchemy import null + + obj = MyObject(id=1, data=null()) + session.add(obj) + session.commit() # INSERT with the 'data' column explicitly set as null(); + # the ORM uses this directly, bypassing all client- + # and server-side defaults, and the database will + # persist this as the NULL value + +The :obj:`~.expression.null` SQL construct always translates into the SQL +NULL value being directly present in the target INSERT statement. + +If we'd like to be able to use the Python value ``None`` and have this +also be persisted as NULL despite the presence of column defaults, +we can configure this for the ORM using a Core-level modifier +:meth:`.TypeEngine.evaluates_none`, which indicates +a type where the ORM should treat the value ``None`` the same as any other +value and pass it through, rather than omitting it as a "missing" value:: + + class MyObject(Base): + __tablename__ = 'my_table' + id = Column(Integer, primary_key=True) + data = Column( + String(50).evaluates_none(), # indicate that None should always be passed + nullable=True, server_default="default") + + obj = MyObject(id=1, data=None) + session.add(obj) + session.commit() # INSERT with the 'data' column explicitly set to None; + # the ORM uses this directly, bypassing all client- + # and server-side defaults, and the database will + # persist this as the NULL value + +.. topic:: Evaluating None + + The :meth:`.TypeEngine.evaluates_none` modifier is primarily intended to + signal a type where the Python value "None" is significant, the primary + example being a JSON type which may want to persist the JSON ``null`` value + rather than SQL NULL. We are slightly repurposing it here in order to + signal to the ORM that we'd like ``None`` to be passed into the type whenever + present, even though no special type-level behaviors are assigned to it. + +.. versionadded:: 1.1 added the :meth:`.TypeEngine.evaluates_none` method + in order to indicate that a "None" value should be treated as significant. + + .. _session_partitioning: Partitioning Strategies diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 2a56649db..8a50270f5 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -258,7 +258,7 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): comparator_factory = Comparator @property - def evaluates_none(self): + def should_evaluate_none(self): return not self.none_as_null def bind_processor(self, dialect): diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py index 21577f5ea..5ade4b966 100644 --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -1921,7 +1921,7 @@ class Mapper(InspectionAttr): table, frozenset( col.key for col in columns - if col.type.evaluates_none + if col.type.should_evaluate_none ) ) for table, columns in self._cols_by_table.items() @@ -1936,7 +1936,7 @@ class Mapper(InspectionAttr): col.key for col in columns if not col.primary_key and not col.server_default and not col.default - and not col.type.evaluates_none) + and not col.type.should_evaluate_none) ) for table, columns in self._cols_by_table.items() ) diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index f5ab1a8d3..3b5391234 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -131,19 +131,76 @@ class TypeEngine(Visitable): """ - evaluates_none = False + should_evaluate_none = False """If True, the Python constant ``None`` is considered to be handled explicitly by this type. - The ORM will use this flag to ensure that a positive value of ``None`` - is definitely passed to the backend, ignoring whether or not there - are Python or server side defaults on this column. + The ORM uses this flag to indicate that a positive value of ``None`` + is passed to the column in an INSERT statement, rather than omitting + the column from the INSERT statement which has the effect of firing + off column-level defaults. It also allows types which have special + behavior for Python None, such as a JSON type, to indicate that + they'd like to handle the None value explicitly. + + To set this flag on an existing type, use the + :meth:`.TypeEngine.evaluates_none` method. + + .. seealso:: + + :meth:`.TypeEngine.evaluates_none` .. versionadded:: 1.1 """ + def evaluates_none(self): + """Return a copy of this type which has the :attr:`.should_evaluate_none` + flag set to True. + + E.g.:: + + Table( + 'some_table', metadata, + Column( + String(50).evaluates_none(), + nullable=True, + server_default='no value') + ) + + The ORM uses this flag to indicate that a positive value of ``None`` + is passed to the column in an INSERT statement, rather than omitting + the column from the INSERT statement which has the effect of firing + off column-level defaults. It also allows for types which have + special behavior associated with the Python None value to indicate + that the value doesn't necessarily translate into SQL NULL; a + prime example of this is a JSON type which may wish to persist the + JSON value ``'null'``. + + In all cases, the actual NULL SQL value can be always be + persisted in any column by using + the :obj:`~.expression.null` SQL construct in an INSERT statement + or associated with an ORM-mapped attribute. + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`session_forcing_null` - in the ORM documentation + + :paramref:`.postgresql.JSON.none_as_null` - Postgresql JSON + interaction with this flag. + + :attr:`.TypeEngine.should_evaluate_none` - class-level flag + + """ + typ = self.copy() + typ.should_evaluate_none = True + return typ + + def copy(self, **kw): + return self.adapt(self.__class__) + def compare_against_backend(self, dialect, conn_type): """Compare this type against the given backend type. diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py index d4870adc6..09240dfdb 100644 --- a/test/orm/test_unitofworkv2.py +++ b/test/orm/test_unitofworkv2.py @@ -1964,7 +1964,7 @@ class NullEvaluatingTest(fixtures.MappedTest, testing.AssertsExecutionResults): class EvalsNull(TypeDecorator): impl = String(50) - evaluates_none = True + should_evaluate_none = True def process_bind_param(self, value, dialect): if value is None: @@ -1979,6 +1979,11 @@ class NullEvaluatingTest(fixtures.MappedTest, testing.AssertsExecutionResults): Column('evals_null_default', EvalsNull(), default='default_val'), Column('no_eval_null_no_default', String(50)), Column('no_eval_null_default', String(50), default='default_val'), + Column( + 'builtin_evals_null_no_default', String(50).evaluates_none()), + Column( + 'builtin_evals_null_default', + String(50).evaluates_none(), default='default_val'), ) @classmethod @@ -2121,3 +2126,43 @@ class NullEvaluatingTest(fixtures.MappedTest, testing.AssertsExecutionResults): self._test_bulk_insert_novalue( "no_eval_null_default", 'default_val' ) + + def test_builtin_evalnull_nodefault_insert(self): + self._test_insert( + "builtin_evals_null_no_default", None + ) + + def test_builtin_evalnull_nodefault_bulk_insert(self): + self._test_bulk_insert( + "builtin_evals_null_no_default", None + ) + + def test_builtin_evalnull_nodefault_insert_novalue(self): + self._test_insert_novalue( + "builtin_evals_null_no_default", None + ) + + def test_builtin_evalnull_nodefault_bulk_insert_novalue(self): + self._test_bulk_insert_novalue( + "builtin_evals_null_no_default", None + ) + + def test_builtin_evalnull_default_insert(self): + self._test_insert( + "builtin_evals_null_default", None + ) + + def test_builtin_evalnull_default_bulk_insert(self): + self._test_bulk_insert( + "builtin_evals_null_default", None + ) + + def test_builtin_evalnull_default_insert_novalue(self): + self._test_insert_novalue( + "builtin_evals_null_default", 'default_val' + ) + + def test_builtin_evalnull_default_bulk_insert_novalue(self): + self._test_bulk_insert_novalue( + "builtin_evals_null_default", 'default_val' + ) |