diff options
29 files changed, 1235 insertions, 510 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 83a57ba7c..63e0ca472 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,33 @@ :version: 1.1.0b1 .. change:: + :tags: feature, mysql + :tickets: 3547 + + Added :class:`.mysql.JSON` for MySQL 5.7. The JSON type provides + persistence of JSON values in MySQL as well as basic operator support + of "getitem" and "getpath", making use of the ``JSON_EXTRACT`` + function in order to refer to individual paths in a JSON structure. + + .. seealso:: + + :ref:`change_3547` + + .. change:: + :tags: feature, sql + :tickets: 3619 + + Added a new type to core :class:`.types.JSON`. This is the + base of the PostgreSQL :class:`.postgresql.JSON` type as well as that + of the new :class:`.mysql.JSON` type, so that a PG/MySQL-agnostic + JSON column may be used. The type features basic index and path + searching support. + + .. seealso:: + + :ref:`change_3619` + + .. change:: :tags: bug, sql :tickets: 3616 @@ -275,7 +302,7 @@ :tickets: 3132 Added support for the SQL-standard function :class:`.array_agg`, - which automatically returns an :class:`.Array` of the correct type + which automatically returns an :class:`.postgresql.ARRAY` of the correct type and supports index / slice operations, as well as :func:`.postgresql.array_agg`, which returns a :class:`.postgresql.ARRAY` with additional comparison features. As arrays are only @@ -292,8 +319,8 @@ :tags: feature, sql :tickets: 3516 - Added a new type to core :class:`.types.Array`. This is the - base of the PostgreSQL :class:`.ARRAY` type, and is now part of Core + Added a new type to core :class:`.types.ARRAY`. This is the + base of the PostgreSQL :class:`.postgresql.ARRAY` type, and is now part of Core to begin supporting various SQL-standard array-supporting features including some functions and eventual support for native arrays on other databases that have an "array" concept, such as DB2 or Oracle. @@ -399,7 +426,7 @@ :tickets: 3514 Additional fixes have been made regarding the value of ``None`` - in conjunction with the Postgresql :class:`.JSON` type. When + in conjunction with the Postgresql :class:`.postgresql.JSON` type. When the :paramref:`.JSON.none_as_null` flag is left at its default value of ``False``, the ORM will now correctly insert the Json "'null'" string into the column whenever the value on the ORM diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index b5889c763..70182091c 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -502,33 +502,138 @@ UNIONs with parenthesized SELECT statements is much less common than the :ticket:`2528` +.. _change_3619: + +JSON support added to Core +-------------------------- + +As MySQL now has a JSON datatype in addition to the Postgresql JSON datatype, +the core now gains a :class:`sqlalchemy.types.JSON` datatype that is the basis +for both of these. Using this type allows access to the "getitem" operator +as well as the "getpath" operator in a way that is agnostic across Postgresql +and MySQL. + +The new datatype also has a series of improvements to the handling of +NULL values as well as expression handling. + +.. seealso:: + + :ref:`change_3547` + + :class:`.types.JSON` + + :class:`.postgresql.JSON` + + :class:`.mysql.JSON` + +:ticket:`3619` + +.. _change_3514: + +JSON "null" is inserted as expected with ORM operations, regardless of column default present +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +The :class:`.types.JSON` type and its descendant types :class:`.postgresql.JSON` +and :class:`.mysql.JSON` have a flag :paramref:`.types.JSON.none_as_null` which +when set to True indicates that the Python value ``None`` should translate +into a SQL NULL rather than a JSON NULL value. This flag defaults to False, +which means that the column should *never* insert SQL NULL or fall back +to a default unless the :func:`.null` constant were used. However, this would +fail in the ORM under two circumstances; one is when the column also contained +a default or server_default value, a positive value of ``None`` on the mapped +attribute would still result in the column-level default being triggered, +replacing the ``None`` value:: + + obj = MyObject(json_value=None) + session.add(obj) + session.commit() # would fire off default / server_default, not encode "'none'" + +The other is when the :meth:`.Session.bulk_insert_mappings` +method were used, ``None`` would be ignored in all cases:: + + session.bulk_insert_mappings( + MyObject, + [{"json_value": None}]) # would insert SQL NULL and/or trigger defaults + +The :class:`.types.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:`.types.JSON.none_as_null`. +Thanks to :ticket:`3061`, we can differentiate when the value ``None`` is actively +set by the user versus when it was never set at all. + +If the attribute is not set at all, then column level defaults *will* +fire off and/or SQL NULL will be inserted as expected, as was the behavior +previously. Below, the two variants are illustrated:: + + obj = MyObject(json_value=None) + session.add(obj) + session.commit() # *will not* fire off column defaults, will insert JSON 'null' + + obj = MyObject() + session.add(obj) + session.commit() # *will* fire off column defaults, and/or insert SQL NULL + +The feature applies as well to the new base :class:`.types.JSON` type +and its descendant types. + +:ticket:`3514` + +.. _change_3514_jsonnull: + +New JSON.NULL Constant Added +^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +To ensure that an application can always have full control at the value level +of whether a :class:`.types.JSON`, :class:`.postgresql.JSON`, :class:`.mysql.JSON`, +or :class:`.postgresql.JSONB` column +should receive a SQL NULL or JSON ``"null"`` value, the constant +:attr:`.types.JSON.NULL` has been added, which in conjunction with +:func:`.null` can be used to determine fully between SQL NULL and +JSON ``"null"``, regardless of what :paramref:`.types.JSON.none_as_null` is set +to:: + + from sqlalchemy import null + from sqlalchemy.dialects.postgresql import JSON + + obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL + obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" + + session.add_all([obj1, obj2]) + session.commit() + +The feature applies as well to the new base :class:`.types.JSON` type +and its descendant types. + +:ticket:`3514` + .. _change_3516: Array support added to Core; new ANY and ALL operators ------------------------------------------------------ -Along with the enhancements made to the Postgresql :class:`.ARRAY` -type described in :ref:`change_3503`, the base class of :class:`.ARRAY` -itself has been moved to Core in a new class :class:`.types.Array`. +Along with the enhancements made to the Postgresql :class:`.postgresql.ARRAY` +type described in :ref:`change_3503`, the base class of :class:`.postgresql.ARRAY` +itself has been moved to Core in a new class :class:`.types.ARRAY`. Arrays are part of the SQL standard, as are several array-oriented functions such as ``array_agg()`` and ``unnest()``. In support of these constructs for not just PostgreSQL but also potentially for other array-capable backends in the future such as DB2, the majority of array logic for SQL expressions -is now in Core. The :class:`.Array` type still **only works on +is now in Core. The :class:`.types.ARRAY` type still **only works on Postgresql**, however it can be used directly, supporting special array use cases such as indexed access, as well as support for the ANY and ALL:: mytable = Table("mytable", metadata, - Column("data", Array(Integer, dimensions=2)) + Column("data", ARRAY(Integer, dimensions=2)) ) expr = mytable.c.data[5][6] expr = mytable.c.data[5].any(12) -In support of ANY and ALL, the :class:`.Array` type retains the same -:meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all` methods +In support of ANY and ALL, the :class:`.types.ARRAY` type retains the same +:meth:`.types.ARRAY.Comparator.any` and :meth:`.types.ARRAY.Comparator.all` methods from the PostgreSQL type, but also exports these operations to new standalone operator functions :func:`.sql.expression.any_` and :func:`.sql.expression.all_`. These two functions work in more @@ -541,7 +646,7 @@ as:: For the PostgreSQL-specific operators "contains", "contained_by", and "overlaps", one should continue to use the :class:`.postgresql.ARRAY` -type directly, which provides all functionality of the :class:`.Array` +type directly, which provides all functionality of the :class:`.types.ARRAY` type as well. The :func:`.sql.expression.any_` and :func:`.sql.expression.all_` operators @@ -564,7 +669,7 @@ such as:: New Function features, "WITHIN GROUP", array_agg and set aggregate functions ---------------------------------------------------------------------------- -With the new :class:`.Array` type we can also implement a pre-typed +With the new :class:`.types.ARRAY` type we can also implement a pre-typed function for the ``array_agg()`` SQL function that returns an array, which is now available using :class:`.array_agg`:: @@ -767,8 +872,9 @@ As described in :ref:`change_3499`, the ORM relies upon being able to produce a hash function for column values when a query's selected entities mixes full ORM entities with column expressions. The ``hashable=False`` flag is now correctly set on all of PG's "data structure" types, including -:class:`.ARRAY` and :class:`.JSON`. The :class:`.JSONB` and :class:`.HSTORE` -types already included this flag. For :class:`.ARRAY`, +:class:`.postgresql.ARRAY` and :class:`.postgresql.JSON`. +The :class:`.JSONB` and :class:`.HSTORE` +types already included this flag. For :class:`.postgresql.ARRAY`, this is conditional based on the :paramref:`.postgresql.ARRAY.as_tuple` flag, however it should no longer be necessary to set this flag in order to have an array value present in a composed ORM row. @@ -840,7 +946,7 @@ The JSON cast() operation now requires ``.astext`` is called explicitly As part of the changes in :ref:`change_3503`, the workings of the :meth:`.ColumnElement.cast` operator on :class:`.postgresql.JSON` and :class:`.postgresql.JSONB` no longer implictly invoke the -:attr:`.JSON.Comparator.astext` modifier; Postgresql's JSON/JSONB types +:attr:`.postgresql.JSON.Comparator.astext` modifier; Postgresql's JSON/JSONB types support CAST operations to each other without the "astext" aspect. This means that in most cases, an application that was doing this:: @@ -852,88 +958,6 @@ Will now need to change to this:: expr = json_col['somekey'].astext.cast(Integer) - -.. _change_3514: - -Postgresql JSON "null" is inserted as expected with ORM operations, regardless of column default present ------------------------------------------------------------------------------------------------------------ - -The :class:`.JSON` type has a flag :paramref:`.JSON.none_as_null` which -when set to True indicates that the Python value ``None`` should translate -into a SQL NULL rather than a JSON NULL value. This flag defaults to False, -which means that the column should *never* insert SQL NULL or fall back -to a default unless the :func:`.null` constant were used. However, this would -fail in the ORM under two circumstances; one is when the column also contained -a default or server_default value, a positive value of ``None`` on the mapped -attribute would still result in the column-level default being triggered, -replacing the ``None`` value:: - - obj = MyObject(json_value=None) - session.add(obj) - session.commit() # would fire off default / server_default, not encode "'none'" - -The other is when the :meth:`.Session.bulk_insert_mappings` -method were used, ``None`` would be ignored in all cases:: - - session.bulk_insert_mappings( - MyObject, - [{"json_value": None}]) # would insert SQL NULL and/or trigger defaults - -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 -set by the user versus when it was never set at all. - -If the attribute is not set at all, then column level defaults *will* -fire off and/or SQL NULL will be inserted as expected, as was the behavior -previously. Below, the two variants are illustrated:: - - obj = MyObject(json_value=None) - session.add(obj) - session.commit() # *will not* fire off column defaults, will insert JSON 'null' - - obj = MyObject() - session.add(obj) - session.commit() # *will* fire off column defaults, and/or insert SQL NULL - -:ticket:`3514` - -.. seealso:: - - :ref:`change_3250` - - :ref:`change_3514_jsonnull` - -.. _change_3514_jsonnull: - -New JSON.NULL Constant Added ----------------------------- - -To ensure that an application can always have full control at the value level -of whether a :class:`.postgresql.JSON` or :class:`.postgresql.JSONB` column -should receive a SQL NULL or JSON ``"null"`` value, the constant -:attr:`.postgresql.JSON.NULL` has been added, which in conjunction with -:func:`.null` can be used to determine fully between SQL NULL and -JSON ``"null"``, regardless of what :paramref:`.JSON.none_as_null` is set -to:: - - from sqlalchemy import null - from sqlalchemy.dialects.postgresql import JSON - - obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL - obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" - - session.add_all([obj1, obj2]) - session.commit() - -.. seealso:: - - :ref:`change_3514` - -:ticket:`3514` - .. _change_2729: ARRAY with ENUM will now emit CREATE TYPE for the ENUM @@ -975,6 +999,25 @@ emits:: Dialect Improvements and Changes - MySQL ============================================= +.. _change_3547: + +MySQL JSON Support +------------------ + +A new type :class:`.mysql.JSON` is added to the MySQL dialect supporting +the JSON type newly added to MySQL 5.7. This type provides both persistence +of JSON as well as rudimentary indexed-access using the ``JSON_EXTRACT`` +function internally. An indexable JSON column that works across MySQL +and Postgresql can be achieved by using the :class:`.types.JSON` datatype +common to both MySQL and Postgresql. + +.. seealso:: + + :ref:`change_3619` + +:ticket:`3547` + + .. _change_mysql_3216: No more generation of an implicit KEY for composite primary key w/ AUTO_INCREMENT diff --git a/doc/build/core/type_basics.rst b/doc/build/core/type_basics.rst index ec3c14dd6..9edba0061 100644 --- a/doc/build/core/type_basics.rst +++ b/doc/build/core/type_basics.rst @@ -38,9 +38,6 @@ database column type available on the target database when issuing a type is emitted in ``CREATE TABLE``, such as ``VARCHAR`` see `SQL Standard Types`_ and the other sections of this chapter. -.. autoclass:: Array - :members: - .. autoclass:: BigInteger :members: @@ -101,12 +98,19 @@ Standard Types`_ and the other sections of this chapter. .. _types_sqlstandard: -SQL Standard Types ------------------- +SQL Standard and Multiple Vendor Types +-------------------------------------- + +This category of types refers to types that are either part of the +SQL standard, or are potentially found within a subset of database backends. +Unlike the "generic" types, the SQL standard/multi-vendor types have **no** +guarantee of working on all backends, and will only work on those backends +that explicitly support them by name. That is, the type will always emit +its exact name in DDL with ``CREATE TABLE`` is issued. -The SQL standard types always create database column types of the same -name when ``CREATE TABLE`` is issued. Some types may not be supported -on all databases. + +.. autoclass:: ARRAY + :members: .. autoclass:: BIGINT @@ -140,6 +144,9 @@ on all databases. .. autoclass:: INT +.. autoclass:: JSON + :members: + .. autoclass:: sqlalchemy.types.INTEGER diff --git a/doc/build/dialects/mysql.rst b/doc/build/dialects/mysql.rst index 33a0d783b..100f2d2e3 100644 --- a/doc/build/dialects/mysql.rst +++ b/doc/build/dialects/mysql.rst @@ -74,6 +74,8 @@ construction arguments, are as follows: .. autoclass:: INTEGER :members: __init__ +.. autoclass:: JSON + :members: .. autoclass:: LONGBLOB :members: __init__ diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst index 7e2a20ef7..e158cacbf 100644 --- a/doc/build/dialects/postgresql.rst +++ b/doc/build/dialects/postgresql.rst @@ -78,9 +78,6 @@ construction arguments, are as follows: .. autoclass:: JSONB :members: -.. autoclass:: JSONElement - :members: - .. autoclass:: MACADDR :members: __init__ diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 12d4e8d1c..40b8000e8 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -54,7 +54,7 @@ from .sql import ( ) from .types import ( - Array, + ARRAY, BIGINT, BINARY, BLOB, @@ -76,6 +76,7 @@ from .types import ( INTEGER, Integer, Interval, + JSON, LargeBinary, NCHAR, NVARCHAR, diff --git a/lib/sqlalchemy/dialects/mysql/__init__.py b/lib/sqlalchemy/dialects/mysql/__init__.py index c1f78bd1d..ca204fcd1 100644 --- a/lib/sqlalchemy/dialects/mysql/__init__.py +++ b/lib/sqlalchemy/dialects/mysql/__init__.py @@ -15,7 +15,7 @@ base.dialect = mysqldb.dialect from .base import \ BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, DATETIME, \ DECIMAL, DOUBLE, ENUM, DECIMAL,\ - FLOAT, INTEGER, INTEGER, LONGBLOB, LONGTEXT, MEDIUMBLOB, \ + FLOAT, INTEGER, INTEGER, JSON, LONGBLOB, LONGTEXT, MEDIUMBLOB, \ MEDIUMINT, MEDIUMTEXT, NCHAR, \ NVARCHAR, NUMERIC, SET, SMALLINT, REAL, TEXT, TIME, TIMESTAMP, \ TINYBLOB, TINYINT, TINYTEXT,\ @@ -24,8 +24,8 @@ from .base import \ __all__ = ( 'BIGINT', 'BINARY', 'BIT', 'BLOB', 'BOOLEAN', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'DOUBLE', 'ENUM', 'DECIMAL', 'FLOAT', 'INTEGER', 'INTEGER', - 'LONGBLOB', 'LONGTEXT', 'MEDIUMBLOB', 'MEDIUMINT', 'MEDIUMTEXT', 'NCHAR', - 'NVARCHAR', 'NUMERIC', 'SET', 'SMALLINT', 'REAL', 'TEXT', 'TIME', + 'JSON', 'LONGBLOB', 'LONGTEXT', 'MEDIUMBLOB', 'MEDIUMINT', 'MEDIUMTEXT', + 'NCHAR', 'NVARCHAR', 'NUMERIC', 'SET', 'SMALLINT', 'REAL', 'TEXT', 'TIME', 'TIMESTAMP', 'TINYBLOB', 'TINYINT', 'TINYTEXT', 'VARBINARY', 'VARCHAR', 'YEAR', 'dialect' ) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 66ef0d5e2..61c4a3fac 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -538,10 +538,11 @@ output:: import re import sys +import json from ... import schema as sa_schema from ... import exc, log, sql, util -from ...sql import compiler +from ...sql import compiler, elements from array import array as _array from ...engine import reflection @@ -559,6 +560,7 @@ from .types import BIGINT, BIT, CHAR, DECIMAL, DATETIME, \ from .types import _StringType, _IntegerType, _NumericType, \ _FloatType, _MatchType from .enumerated import ENUM, SET +from .json import JSON, JSONIndexType, JSONPathType RESERVED_WORDS = set( @@ -663,7 +665,11 @@ colspecs = { sqltypes.Float: FLOAT, sqltypes.Time: TIME, sqltypes.Enum: ENUM, - sqltypes.MatchType: _MatchType + sqltypes.MatchType: _MatchType, + sqltypes.JSON: JSON, + sqltypes.JSON.JSONIndexType: JSONIndexType, + sqltypes.JSON.JSONPathType: JSONPathType + } # Everything 3.23 through 5.1 excepting OpenGIS types. @@ -683,6 +689,7 @@ ischema_names = { 'float': FLOAT, 'int': INTEGER, 'integer': INTEGER, + 'json': JSON, 'longblob': LONGBLOB, 'longtext': LONGTEXT, 'mediumblob': MEDIUMBLOB, @@ -728,6 +735,16 @@ class MySQLCompiler(compiler.SQLCompiler): def visit_sysdate_func(self, fn, **kw): return "SYSDATE()" + def visit_json_getitem_op_binary(self, binary, operator, **kw): + return "JSON_EXTRACT(%s, %s)" % ( + self.process(binary.left), + self.process(binary.right)) + + def visit_json_path_getitem_op_binary(self, binary, operator, **kw): + return "JSON_EXTRACT(%s, %s)" % ( + self.process(binary.left), + self.process(binary.right)) + def visit_concat_op_binary(self, binary, operator, **kw): return "concat(%s, %s)" % (self.process(binary.left), self.process(binary.right)) @@ -760,6 +777,8 @@ class MySQLCompiler(compiler.SQLCompiler): return self.dialect.type_compiler.process(adapted) elif isinstance(type_, sqltypes._Binary): return 'BINARY' + elif isinstance(type_, sqltypes.JSON): + return "JSON" elif isinstance(type_, sqltypes.NUMERIC): return self.dialect.type_compiler.process( type_).replace('NUMERIC', 'DECIMAL') @@ -1275,6 +1294,9 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler): def visit_VARBINARY(self, type_, **kw): return "VARBINARY(%d)" % type_.length + def visit_JSON(self, type_, **kw): + return "JSON" + def visit_large_binary(self, type_, **kw): return self.visit_BLOB(type_) @@ -1394,10 +1416,13 @@ class MySQLDialect(default.DefaultDialect): }) ] - def __init__(self, isolation_level=None, **kwargs): + def __init__(self, isolation_level=None, json_serializer=None, + json_deserializer=None, **kwargs): kwargs.pop('use_ansiquotes', None) # legacy default.DefaultDialect.__init__(self, **kwargs) self.isolation_level = isolation_level + self._json_serializer = json_serializer + self._json_deserializer = json_deserializer def on_connect(self): if self.isolation_level is not None: @@ -1564,6 +1589,10 @@ class MySQLDialect(default.DefaultDialect): default.DefaultDialect.initialize(self, connection) @property + def _is_mariadb(self): + return 'MariaDB' in self.server_version_info + + @property def _supports_cast(self): return self.server_version_info is None or \ self.server_version_info >= (4, 0, 2) diff --git a/lib/sqlalchemy/dialects/mysql/json.py b/lib/sqlalchemy/dialects/mysql/json.py new file mode 100644 index 000000000..a30cdc841 --- /dev/null +++ b/lib/sqlalchemy/dialects/mysql/json.py @@ -0,0 +1,90 @@ +# mysql/json.py +# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php + +from __future__ import absolute_import + +import json + +from ...sql import elements +from ... import types as sqltypes +from ... import util + + +class JSON(sqltypes.JSON): + """MySQL JSON type. + + MySQL supports JSON as of version 5.7. Note that MariaDB does **not** + support JSON at the time of this writing. + + The :class:`.mysql.JSON` type supports persistence of JSON values + as well as the core index operations provided by :class:`.types.JSON` + datatype, by adapting the operations to render the ``JSON_EXTRACT`` + function at the database level. + + .. versionadded:: 1.1 + + """ + + @util.memoized_property + def _str_impl(self): + return sqltypes.String(convert_unicode=True) + + def bind_processor(self, dialect): + string_process = self._str_impl.bind_processor(dialect) + + json_serializer = dialect._json_serializer or json.dumps + + def process(value): + if value is self.NULL: + value = None + elif isinstance(value, elements.Null) or ( + value is None and self.none_as_null + ): + return None + + serialized = json_serializer(value) + if string_process: + serialized = string_process(serialized) + return serialized + + return process + + def result_processor(self, dialect, coltype): + string_process = self._str_impl.result_processor(dialect, coltype) + json_deserializer = dialect._json_deserializer or json.loads + + def process(value): + if value is None: + return None + if string_process: + value = string_process(value) + return json_deserializer(value) + return process + + +class JSONIndexType(sqltypes.JSON.JSONIndexType): + def bind_processor(self, dialect): + def process(value): + if isinstance(value, int): + return "$[%s]" % value + else: + return '$."%s"' % value + + return process + + +class JSONPathType(sqltypes.JSON.JSONPathType): + def bind_processor(self, dialect): + def process(value): + return "$%s" % ( + "".join([ + "[%s]" % elem if isinstance(elem, int) + else '."%s"' % elem for elem in value + ]) + ) + + return process diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py index b88f139de..f4316d318 100644 --- a/lib/sqlalchemy/dialects/postgresql/array.py +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -84,12 +84,20 @@ class array(expression.Tuple): super(array, self).__init__(*clauses, **kw) self.type = ARRAY(self.type) - def _bind_param(self, operator, obj): - return array([ - expression.BindParameter(None, o, _compared_to_operator=operator, - _compared_to_type=self.type, unique=True) - for o in obj - ]) + def _bind_param(self, operator, obj, _assume_scalar=False, type_=None): + if _assume_scalar or operator is operators.getitem: + # if getitem->slice were called, Indexable produces + # a Slice object from that + assert isinstance(obj, int) + return expression.BindParameter( + None, obj, _compared_to_operator=operator, + type_=type_, + _compared_to_type=self.type, unique=True) + + else: + return array([ + self._bind_param(operator, o, _assume_scalar=True, type_=type_) + for o in obj]) def self_group(self, against=None): if (against in ( @@ -106,15 +114,15 @@ CONTAINED_BY = operators.custom_op("<@", precedence=5) OVERLAP = operators.custom_op("&&", precedence=5) -class ARRAY(SchemaEventTarget, sqltypes.Array): +class ARRAY(SchemaEventTarget, sqltypes.ARRAY): """Postgresql ARRAY type. .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now - a subclass of the core :class:`.Array` type. + a subclass of the core :class:`.types.ARRAY` type. The :class:`.postgresql.ARRAY` type is constructed in the same way - as the core :class:`.Array` type; a member type is required, and a + as the core :class:`.types.ARRAY` type; a member type is required, and a number of dimensions is recommended if the type is to be used for more than one dimension:: @@ -125,9 +133,9 @@ class ARRAY(SchemaEventTarget, sqltypes.Array): ) The :class:`.postgresql.ARRAY` type provides all operations defined on the - core :class:`.Array` type, including support for "dimensions", indexed - access, and simple matching such as :meth:`.Array.Comparator.any` - and :meth:`.Array.Comparator.all`. :class:`.postgresql.ARRAY` class also + core :class:`.types.ARRAY` type, including support for "dimensions", indexed + access, and simple matching such as :meth:`.types.ARRAY.Comparator.any` + and :meth:`.types.ARRAY.Comparator.all`. :class:`.postgresql.ARRAY` class also provides PostgreSQL-specific methods for containment operations, including :meth:`.postgresql.ARRAY.Comparator.contains` :meth:`.postgresql.ARRAY.Comparator.contained_by`, @@ -144,20 +152,20 @@ class ARRAY(SchemaEventTarget, sqltypes.Array): .. seealso:: - :class:`.types.Array` - base array type + :class:`.types.ARRAY` - base array type :class:`.postgresql.array` - produces a literal array value. """ - class Comparator(sqltypes.Array.Comparator): + class Comparator(sqltypes.ARRAY.Comparator): """Define comparison operations for :class:`.ARRAY`. Note that these operations are in addition to those provided - by the base :class:`.types.Array.Comparator` class, including - :meth:`.types.Array.Comparator.any` and - :meth:`.types.Array.Comparator.all`. + by the base :class:`.types.ARRAY.Comparator` class, including + :meth:`.types.ARRAY.Comparator.any` and + :meth:`.types.ARRAY.Comparator.all`. """ diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index e9001f79a..3f9fcb27f 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1056,6 +1056,16 @@ class PGCompiler(compiler.SQLCompiler): self.process(element.stop, **kw), ) + def visit_json_getitem_op_binary(self, binary, operator, **kw): + return self._generate_generic_binary( + binary, " -> ", **kw + ) + + def visit_json_path_getitem_op_binary(self, binary, operator, **kw): + return self._generate_generic_binary( + binary, " #> ", **kw + ) + def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( self.process(binary.left, **kw), diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 1a443c2d7..66c7ed0e5 100644 --- a/lib/sqlalchemy/dialects/postgresql/ext.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -159,7 +159,7 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE def array_agg(*arg, **kw): """Postgresql-specific form of :class:`.array_agg`, ensures return type is :class:`.postgresql.ARRAY` and not - the plain :class:`.types.Array`. + the plain :class:`.types.ARRAY`. .. versionadded:: 1.1 diff --git a/lib/sqlalchemy/dialects/postgresql/hstore.py b/lib/sqlalchemy/dialects/postgresql/hstore.py index b7b0fc007..d2d20386a 100644 --- a/lib/sqlalchemy/dialects/postgresql/hstore.py +++ b/lib/sqlalchemy/dialects/postgresql/hstore.py @@ -12,34 +12,33 @@ from .array import ARRAY from ... import types as sqltypes from ...sql import functions as sqlfunc from ...sql import operators -from ...sql.operators import custom_op from ... import util __all__ = ('HSTORE', 'hstore') -INDEX = custom_op( - "->", precedence=5, natural_self_precedent=True +GETITEM = operators.custom_op( + "->", precedence=15, natural_self_precedent=True, ) HAS_KEY = operators.custom_op( - "?", precedence=5, natural_self_precedent=True + "?", precedence=15, natural_self_precedent=True ) HAS_ALL = operators.custom_op( - "?&", precedence=5, natural_self_precedent=True + "?&", precedence=15, natural_self_precedent=True ) HAS_ANY = operators.custom_op( - "?|", precedence=5, natural_self_precedent=True + "?|", precedence=15, natural_self_precedent=True ) CONTAINS = operators.custom_op( - "@>", precedence=5, natural_self_precedent=True + "@>", precedence=15, natural_self_precedent=True ) CONTAINED_BY = operators.custom_op( - "<@", precedence=5, natural_self_precedent=True + "<@", precedence=15, natural_self_precedent=True ) @@ -166,7 +165,7 @@ class HSTORE(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): CONTAINED_BY, other, result_type=sqltypes.Boolean) def _setup_getitem(self, index): - return INDEX, index, self.type.text_type + return GETITEM, index, self.type.text_type def defined(self, key): """Boolean expression. Test for presence of a non-NULL value for diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 8a50270f5..6ff9fd88e 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -6,10 +6,10 @@ # the MIT License: http://www.opensource.org/licenses/mit-license.php from __future__ import absolute_import -import collections import json +import collections -from .base import ischema_names +from .base import ischema_names, colspecs from ... import types as sqltypes from ...sql import operators from ...sql import elements @@ -17,70 +17,68 @@ from ... import util __all__ = ('JSON', 'JSONB') - -# json : returns json -INDEX = operators.custom_op( - "->", precedence=5, natural_self_precedent=True -) - -# path operator: returns json -PATHIDX = operators.custom_op( - "#>", precedence=5, natural_self_precedent=True -) - -# json + astext: returns text ASTEXT = operators.custom_op( - "->>", precedence=5, natural_self_precedent=True + "->>", precedence=15, natural_self_precedent=True, ) -# path operator + astext: returns text -ASTEXT_PATHIDX = operators.custom_op( - "#>>", precedence=5, natural_self_precedent=True +JSONPATH_ASTEXT = operators.custom_op( + "#>>", precedence=15, natural_self_precedent=True, ) + HAS_KEY = operators.custom_op( - "?", precedence=5, natural_self_precedent=True + "?", precedence=15, natural_self_precedent=True ) HAS_ALL = operators.custom_op( - "?&", precedence=5, natural_self_precedent=True + "?&", precedence=15, natural_self_precedent=True ) HAS_ANY = operators.custom_op( - "?|", precedence=5, natural_self_precedent=True + "?|", precedence=15, natural_self_precedent=True ) CONTAINS = operators.custom_op( - "@>", precedence=5, natural_self_precedent=True + "@>", precedence=15, natural_self_precedent=True ) CONTAINED_BY = operators.custom_op( - "<@", precedence=5, natural_self_precedent=True + "<@", precedence=15, natural_self_precedent=True ) -class JSON(sqltypes.Indexable, sqltypes.TypeEngine): - """Represent the Postgresql JSON type. +class JSONPathType(sqltypes.JSON.JSONPathType): + def bind_processor(self, dialect): + def process(value): + assert isinstance(value, collections.Sequence) + tokens = [util.text_type(elem) for elem in value] + return "{%s}" % (", ".join(tokens)) - The :class:`.JSON` type stores arbitrary JSON format data, e.g.:: + return process - data_table = Table('data_table', metadata, - Column('id', Integer, primary_key=True), - Column('data', JSON) - ) +colspecs[sqltypes.JSON.JSONPathType] = JSONPathType - with engine.connect() as conn: - conn.execute( - data_table.insert(), - data = {"key1": "value1", "key2": "value2"} - ) - :class:`.JSON` provides several operations: +class JSON(sqltypes.JSON): + """Represent the Postgresql JSON type. + + This type is a specialization of the Core-level :class:`.types.JSON` + type. Be sure to read the documentation for :class:`.types.JSON` for + important tips regarding treatment of NULL values and ORM use. + + .. versionchanged:: 1.1 :class:`.postgresql.JSON` is now a Postgresql- + specific specialization of the new :class:`.types.JSON` type. + + The operators provided by the Postgresql version of :class:`.JSON` + include: * Index operations (the ``->`` operator):: data_table.c.data['some key'] + data_table.c.data[5] + + * Index operations returning text (the ``->>`` operator):: data_table.c.data['some key'].astext == 'some value' @@ -92,11 +90,11 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): * Path index operations (the ``#>`` operator):: - data_table.c.data[('key_1', 'key_2', ..., 'key_n')] + data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] * Path index operations returning text (the ``#>>`` operator):: - data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \ + data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == \ 'some value' .. versionchanged:: 1.1 The :meth:`.ColumnElement.cast` operator on @@ -108,36 +106,6 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): :class:`.JSON` by default, so that further JSON-oriented instructions may be called upon the result type. - The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not - detect in-place mutations to the structure. In order to detect these, the - :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will - allow "in-place" changes to the datastructure to produce events which - will be detected by the unit of work. See the example at :class:`.HSTORE` - for a simple example involving a dictionary. - - When working with NULL values, the :class:`.JSON` type recommends the - use of two specific constants in order to differentiate between a column - that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string - of ``"null"``. To insert or select against a value that is SQL NULL, - use the constant :func:`.null`:: - - conn.execute(table.insert(), json_value=null()) - - To insert or select against a value that is JSON ``"null"``, use the - constant :attr:`.JSON.NULL`:: - - conn.execute(table.insert(), json_value=JSON.NULL) - - The :class:`.JSON` type supports a flag - :paramref:`.JSON.none_as_null` which when set to True will result - in the Python constant ``None`` evaluating to the value of SQL - NULL, and when set to False results in the Python constant - ``None`` evaluating to the value of JSON ``"null"``. The Python - value ``None`` may be used in conjunction with either - :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL - values, but care must be taken as to the value of the - :paramref:`.JSON.none_as_null` in these cases. - Custom serializers and deserializers are specified at the dialect level, that is using :func:`.create_engine`. The reason for this is that when using psycopg2, the DBAPI only allows serializers at the per-cursor @@ -151,43 +119,16 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): When using the psycopg2 dialect, the json_deserializer is registered against the database using ``psycopg2.extras.register_default_json``. - .. versionadded:: 0.9 - .. seealso:: + :class:`.types.JSON` - Core level JSON type + :class:`.JSONB` """ - __visit_name__ = 'JSON' - - hashable = False astext_type = sqltypes.Text() - NULL = util.symbol('JSON_NULL') - """Describe the json value of NULL. - - This value is used to force the JSON value of ``"null"`` to be - used as the value. A value of Python ``None`` will be recognized - either as SQL NULL or JSON ``"null"``, based on the setting - of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL` - constant can be used to always resolve to JSON ``"null"`` regardless - of this setting. This is in contrast to the :func:`.sql.null` construct, - which always resolves to SQL NULL. E.g.:: - - from sqlalchemy import null - from sqlalchemy.dialects.postgresql import JSON - - obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL - obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" - - session.add_all([obj1, obj2]) - session.commit() - - .. versionadded:: 1.1 - - """ - def __init__(self, none_as_null=False, astext_type=None): """Construct a :class:`.JSON` type. @@ -210,15 +151,14 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): :attr:`.JSON.Comparator.astext` accessor on indexed attributes. Defaults to :class:`.types.Text`. - .. versionadded:: 1.1.0 + .. versionadded:: 1.1 """ - self.none_as_null = none_as_null + super(JSON, self).__init__(none_as_null=none_as_null) if astext_type is not None: self.astext_type = astext_type - class Comparator( - sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): + class Comparator(sqltypes.JSON.Comparator): """Define comparison operations for :class:`.JSON`.""" @property @@ -235,69 +175,19 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): :meth:`.ColumnElement.cast` """ - against = self.expr.operator - if against is PATHIDX: - against = ASTEXT_PATHIDX - else: - against = ASTEXT - return self.expr.left.operate( - against, self.expr.right, result_type=self.type.astext_type) - - def _setup_getitem(self, index): - if not isinstance(index, util.string_types): - assert isinstance(index, collections.Sequence) - tokens = [util.text_type(elem) for elem in index] - index = "{%s}" % (", ".join(tokens)) - operator = PATHIDX + if isinstance(self.expr.right.type, sqltypes.JSON.JSONPathType): + return self.expr.left.operate( + JSONPATH_ASTEXT, + self.expr.right, result_type=self.type.astext_type) else: - operator = INDEX - - return operator, index, self.type + return self.expr.left.operate( + ASTEXT, self.expr.right, result_type=self.type.astext_type) comparator_factory = Comparator - @property - def should_evaluate_none(self): - return not self.none_as_null - - def bind_processor(self, dialect): - json_serializer = dialect._json_serializer or json.dumps - if util.py2k: - encoding = dialect.encoding - else: - encoding = None - - def process(value): - if value is self.NULL: - value = None - elif isinstance(value, elements.Null) or ( - value is None and self.none_as_null - ): - return None - if encoding: - return json_serializer(value).encode(encoding) - else: - return json_serializer(value) - - return process - - def result_processor(self, dialect, coltype): - json_deserializer = dialect._json_deserializer or json.loads - if util.py2k: - encoding = dialect.encoding - else: - encoding = None - - def process(value): - if value is None: - return None - if encoding: - value = value.decode(encoding) - return json_deserializer(value) - return process - +colspecs[sqltypes.JSON] = JSON ischema_names['json'] = JSON diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index d33554922..82fcc9054 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -534,6 +534,7 @@ class PGDialect_psycopg2(PGDialect): sqltypes.Enum: _PGEnum, # needs force_unicode HSTORE: _PGHStore, JSON: _PGJSON, + sqltypes.JSON: _PGJSON, JSONB: _PGJSONB, UUID: _PGUUID } diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 2ca549267..2fe6ea02c 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -879,22 +879,28 @@ class SQLCompiler(Compiled): else: return text + def _get_operator_dispatch(self, operator_, qualifier1, qualifier2): + attrname = "visit_%s_%s%s" % ( + operator_.__name__, qualifier1, + "_" + qualifier2 if qualifier2 else "") + return getattr(self, attrname, None) + def visit_unary(self, unary, **kw): if unary.operator: if unary.modifier: raise exc.CompileError( "Unary expression does not support operator " "and modifier simultaneously") - disp = getattr(self, "visit_%s_unary_operator" % - unary.operator.__name__, None) + disp = self._get_operator_dispatch( + unary.operator, "unary", "operator") if disp: return disp(unary, unary.operator, **kw) else: return self._generate_generic_unary_operator( unary, OPERATORS[unary.operator], **kw) elif unary.modifier: - disp = getattr(self, "visit_%s_unary_modifier" % - unary.modifier.__name__, None) + disp = self._get_operator_dispatch( + unary.modifier, "unary", "modifier") if disp: return disp(unary, unary.modifier, **kw) else: @@ -928,7 +934,7 @@ class SQLCompiler(Compiled): kw['literal_binds'] = True operator_ = override_operator or binary.operator - disp = getattr(self, "visit_%s_binary" % operator_.__name__, None) + disp = self._get_operator_dispatch(operator_, "binary", None) if disp: return disp(binary, operator_, **kw) else: diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 68ea5624e..ddb57da77 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -164,27 +164,7 @@ def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): def _getitem_impl(expr, op, other, **kw): if isinstance(expr.type, type_api.INDEXABLE): - if isinstance(other, slice): - if expr.type.zero_indexes: - other = slice( - other.start + 1, - other.stop + 1, - other.step - ) - other = Slice( - _literal_as_binds( - other.start, name=expr.key, type_=type_api.INTEGERTYPE), - _literal_as_binds( - other.stop, name=expr.key, type_=type_api.INTEGERTYPE), - _literal_as_binds( - other.step, name=expr.key, type_=type_api.INTEGERTYPE) - ) - else: - if expr.type.zero_indexes: - other += 1 - - other = _literal_as_binds( - other, name=expr.key, type_=type_api.INTEGERTYPE) + other = _check_literal(expr, op, other) return _binary_operate(expr, op, other, **kw) else: _unsupported_impl(expr, op, other, **kw) @@ -260,6 +240,8 @@ operator_lookup = { "mod": (_binary_operate,), "truediv": (_binary_operate,), "custom_op": (_binary_operate,), + "json_path_getitem_op": (_binary_operate, ), + "json_getitem_op": (_binary_operate, ), "concat_op": (_binary_operate,), "lt": (_boolean_compare, operators.ge), "le": (_boolean_compare, operators.gt), @@ -295,7 +277,7 @@ operator_lookup = { } -def _check_literal(expr, operator, other): +def _check_literal(expr, operator, other, bindparam_type=None): if isinstance(other, (ColumnElement, TextClause)): if isinstance(other, BindParameter) and \ other.type._isnull: @@ -310,7 +292,7 @@ def _check_literal(expr, operator, other): if isinstance(other, (SelectBase, Alias)): return other.as_scalar() elif not isinstance(other, Visitable): - return expr._bind_param(operator, other) + return expr._bind_param(operator, other, type_=bindparam_type) else: return other diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 70046c66b..774e42609 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -682,9 +682,10 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): def reverse_operate(self, op, other, **kwargs): return op(other, self.comparator, **kwargs) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, + type_=type_, _compared_to_type=self.type, unique=True) @property @@ -1952,11 +1953,12 @@ class Tuple(ClauseList, ColumnElement): def _select_iterable(self): return (self, ) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return Tuple(*[ BindParameter(None, o, _compared_to_operator=operator, - _compared_to_type=type_, unique=True) - for o, type_ in zip(obj, self._type_tuple) + _compared_to_type=compared_to_type, unique=True, + type_=type_) + for o, compared_to_type in zip(obj, self._type_tuple) ]).self_group() @@ -3637,10 +3639,11 @@ class ColumnClause(Immutable, ColumnElement): else: return name - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.key, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) def _make_proxy(self, selectable, name=None, attach=True, diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 6cfbd12b3..3c654bf67 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -256,16 +256,18 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return self.select().execute() - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, - _compared_to_type=self.type, unique=True) + _compared_to_type=self.type, unique=True, + type_=type_) def self_group(self, against=None): # for the moment, we are parenthesizing all array-returning # expressions against getitem. This may need to be made # more portable if in the future we support other DBs # besides postgresql. - if against is operators.getitem: + if against is operators.getitem and \ + isinstance(self.type, sqltypes.ARRAY): return Grouping(self) else: return super(FunctionElement, self).self_group(against=against) @@ -423,10 +425,11 @@ class Function(FunctionElement): FunctionElement.__init__(self, *clauses, **kw) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.name, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) @@ -659,7 +662,7 @@ class array_agg(GenericFunction): """support for the ARRAY_AGG function. The ``func.array_agg(expr)`` construct returns an expression of - type :class:`.Array`. + type :class:`.types.ARRAY`. e.g.:: @@ -670,11 +673,11 @@ class array_agg(GenericFunction): .. seealso:: :func:`.postgresql.array_agg` - PostgreSQL-specific version that - returns :class:`.ARRAY`, which has PG-specific operators added. + returns :class:`.postgresql.ARRAY`, which has PG-specific operators added. """ - type = sqltypes.Array + type = sqltypes.ARRAY def __init__(self, *args, **kwargs): args = [_literal_as_binds(c) for c in args] @@ -694,7 +697,7 @@ class OrderedSetAgg(GenericFunction): func_clauses = self.clause_expr.element order_by = sqlutil.unwrap_order_by(within_group.order_by) if self.array_for_multi_clause and len(func_clauses.clauses) > 1: - return sqltypes.Array(order_by[0].type) + return sqltypes.ARRAY(order_by[0].type) else: return order_by[0].type @@ -719,7 +722,7 @@ class percentile_cont(OrderedSetAgg): modifier to supply a sort expression to operate upon. The return type of this function is the same as the sort expression, - or if the arguments are an array, an :class:`.Array` of the sort + or if the arguments are an array, an :class:`.types.ARRAY` of the sort expression's type. .. versionadded:: 1.1 @@ -736,7 +739,7 @@ class percentile_disc(OrderedSetAgg): modifier to supply a sort expression to operate upon. The return type of this function is the same as the sort expression, - or if the arguments are an array, an :class:`.Array` of the sort + or if the arguments are an array, an :class:`.types.ARRAY` of the sort expression's type. .. versionadded:: 1.1 diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index da3576466..f4f90b664 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -12,7 +12,6 @@ from .. import util - from operator import ( and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg, getitem, lshift, rshift @@ -720,7 +719,6 @@ def istrue(a): def isfalse(a): raise NotImplementedError() - def is_(a, b): return a.is_(b) @@ -837,6 +835,14 @@ def nullslast_op(a): return a.nullslast() +def json_getitem_op(a, b): + raise NotImplementedError() + + +def json_path_getitem_op(a, b): + raise NotImplementedError() + + _commutative = set([eq, ne, add, mul]) _comparison = set([eq, ne, lt, gt, ge, le, between_op, like_op]) @@ -879,7 +885,8 @@ def mirror(op): _associative = _commutative.union([concat_op, and_, or_]) -_natural_self_precedent = _associative.union([getitem]) +_natural_self_precedent = _associative.union([ + getitem, json_getitem_op, json_path_getitem_op]) """Operators where if we have (a op b) op c, we don't want to parenthesize (a op b). @@ -894,6 +901,8 @@ _PRECEDENCE = { from_: 15, any_op: 15, all_op: 15, + json_getitem_op: 15, + json_path_getitem_op: 15, getitem: 15, mul: 8, truediv: 8, diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 4abb9b15a..b65d39ba1 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -11,9 +11,12 @@ import datetime as dt import codecs +import collections +import json +from . import elements from .type_api import TypeEngine, TypeDecorator, to_instance -from .elements import quoted_name, TypeCoerce as type_coerce, _defer_name +from .elements import quoted_name, TypeCoerce as type_coerce, _defer_name, Slice, _literal_as_binds from .. import exc, util, processors from .base import _bind_or_error, SchemaEventTarget from . import operators @@ -85,20 +88,16 @@ class Indexable(object): """ - zero_indexes = False - """if True, Python zero-based indexes should be interpreted as one-based - on the SQL expression side.""" - class Comparator(TypeEngine.Comparator): def _setup_getitem(self, index): raise NotImplementedError() def __getitem__(self, index): - operator, adjusted_right_expr, result_type = \ + adjusted_op, adjusted_right_expr, result_type = \ self._setup_getitem(index) return self.operate( - operator, + adjusted_op, adjusted_right_expr, result_type=result_type ) @@ -1496,7 +1495,221 @@ class Interval(_DateAffinity, TypeDecorator): return self.impl.coerce_compared_value(op, value) -class Array(Indexable, Concatenable, TypeEngine): +class JSON(Indexable, TypeEngine): + """Represent a SQL JSON type. + + .. note:: :class:`.types.JSON` is provided as a facade for vendor-specific + JSON types. Since it supports JSON SQL operations, it only + works on backends that have an actual JSON type, currently + Postgresql as well as certain versions of MySQL. + + :class:`.types.JSON` is part of the Core in support of the growing + popularity of native JSON datatypes. + + The :class:`.types.JSON` type stores arbitrary JSON format data, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', JSON) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), + data = {"key1": "value1", "key2": "value2"} + ) + + The base :class:`.types.JSON` provides these two operations: + + * Keyed index operations:: + + data_table.c.data['some key'] + + * Integer index operations:: + + data_table.c.data[3] + + * Path index operations:: + + data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] + + Additional operations are available from the dialect-specific versions + of :class:`.types.JSON`, such as :class:`.postgresql.JSON` and + :class:`.postgresql.JSONB`, each of which offer more operators than + just the basic type. + + Index operations return an expression object whose type defaults to + :class:`.JSON` by default, so that further JSON-oriented instructions + may be called upon the result type. + + The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not + detect in-place mutations to the structure. In order to detect these, the + :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will + allow "in-place" changes to the datastructure to produce events which + will be detected by the unit of work. See the example at :class:`.HSTORE` + for a simple example involving a dictionary. + + When working with NULL values, the :class:`.JSON` type recommends the + use of two specific constants in order to differentiate between a column + that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string + of ``"null"``. To insert or select against a value that is SQL NULL, + use the constant :func:`.null`:: + + from sqlalchemy import null + conn.execute(table.insert(), json_value=null()) + + To insert or select against a value that is JSON ``"null"``, use the + constant :attr:`.JSON.NULL`:: + + conn.execute(table.insert(), json_value=JSON.NULL) + + The :class:`.JSON` type supports a flag + :paramref:`.JSON.none_as_null` which when set to True will result + in the Python constant ``None`` evaluating to the value of SQL + NULL, and when set to False results in the Python constant + ``None`` evaluating to the value of JSON ``"null"``. The Python + value ``None`` may be used in conjunction with either + :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL + values, but care must be taken as to the value of the + :paramref:`.JSON.none_as_null` in these cases. + + .. seealso:: + + :class:`.postgresql.JSON` + + :class:`.postgresql.JSONB` + + :class:`.mysql.JSON` + + .. versionadded:: 1.1 + + + """ + __visit_name__ = 'JSON' + + hashable = False + NULL = util.symbol('JSON_NULL') + """Describe the json value of NULL. + + This value is used to force the JSON value of ``"null"`` to be + used as the value. A value of Python ``None`` will be recognized + either as SQL NULL or JSON ``"null"``, based on the setting + of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL` + constant can be used to always resolve to JSON ``"null"`` regardless + of this setting. This is in contrast to the :func:`.sql.null` construct, + which always resolves to SQL NULL. E.g.:: + + from sqlalchemy import null + from sqlalchemy.dialects.postgresql import JSON + + obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL + obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" + + session.add_all([obj1, obj2]) + session.commit() + + """ + + def __init__(self, none_as_null=False): + """Construct a :class:`.types.JSON` type. + + :param none_as_null=False: if True, persist the value ``None`` as a + SQL NULL value, not the JSON encoding of ``null``. Note that + when this flag is False, the :func:`.null` construct can still + be used to persist a NULL value:: + + from sqlalchemy import null + conn.execute(table.insert(), data=null()) + + .. seealso:: + + :attr:`.types.JSON.NULL` + + """ + self.none_as_null = none_as_null + + class JSONIndexType(TypeEngine): + """Placeholder for the datatype of a JSON index value. + + This allows execution-time processing of JSON index values + for special syntaxes. + + """ + + class JSONPathType(TypeEngine): + """Placeholder type for JSON path operations. + + This allows execution-time processing of a path-based + index value into a specific SQL syntax. + + """ + + class Comparator(Indexable.Comparator, Concatenable.Comparator): + """Define comparison operations for :class:`.types.JSON`.""" + + @util.dependencies('sqlalchemy.sql.default_comparator') + def _setup_getitem(self, default_comparator, index): + if not isinstance(index, util.string_types) and \ + isinstance(index, collections.Sequence): + index = default_comparator._check_literal( + self.expr, operators.json_path_getitem_op, + index, bindparam_type=JSON.JSONPathType + ) + + operator = operators.json_path_getitem_op + else: + index = default_comparator._check_literal( + self.expr, operators.json_getitem_op, + index, bindparam_type=JSON.JSONIndexType + ) + operator = operators.json_getitem_op + + return operator, index, self.type + + comparator_factory = Comparator + + @property + def should_evaluate_none(self): + return not self.none_as_null + + @util.memoized_property + def _str_impl(self): + return String(convert_unicode=True) + + def bind_processor(self, dialect): + string_process = self._str_impl.bind_processor(dialect) + + json_serializer = dialect._json_serializer or json.dumps + + def process(value): + if value is self.NULL: + value = None + elif isinstance(value, elements.Null) or ( + value is None and self.none_as_null + ): + return None + + serialized = json_serializer(value) + if string_process: + serialized = string_process(serialized) + return serialized + + return process + + def result_processor(self, dialect, coltype): + string_process = self._str_impl.result_processor(dialect, coltype) + json_deserializer = dialect._json_deserializer or json.loads + + def process(value): + if value is None: + return None + if string_process: + value = string_process(value) + return json_deserializer(value) + return process + + +class ARRAY(Indexable, Concatenable, TypeEngine): """Represent a SQL Array type. .. note:: This type serves as the basis for all ARRAY operations. @@ -1506,17 +1719,17 @@ class Array(Indexable, Concatenable, TypeEngine): with PostgreSQL, as it provides additional operators specific to that backend. - :class:`.Array` is part of the Core in support of various SQL standard + :class:`.types.ARRAY` is part of the Core in support of various SQL standard functions such as :class:`.array_agg` which explicitly involve arrays; however, with the exception of the PostgreSQL backend and possibly some third-party dialects, no other SQLAlchemy built-in dialect has support for this type. - An :class:`.Array` type is constructed given the "type" + An :class:`.types.ARRAY` type is constructed given the "type" of element:: mytable = Table("mytable", metadata, - Column("data", Array(Integer)) + Column("data", ARRAY(Integer)) ) The above type represents an N-dimensional array, @@ -1529,11 +1742,11 @@ class Array(Indexable, Concatenable, TypeEngine): data=[1,2,3] ) - The :class:`.Array` type can be constructed given a fixed number + The :class:`.types.ARRAY` type can be constructed given a fixed number of dimensions:: mytable = Table("mytable", metadata, - Column("data", Array(Integer, dimensions=2)) + Column("data", ARRAY(Integer, dimensions=2)) ) Sending a number of dimensions is optional, but recommended if the @@ -1555,10 +1768,10 @@ class Array(Indexable, Concatenable, TypeEngine): >>> expr = table.c.column[5] # returns ARRAY(Integer, dimensions=1) >>> expr = expr[6] # returns Integer - For 1-dimensional arrays, an :class:`.Array` instance with no + For 1-dimensional arrays, an :class:`.types.ARRAY` instance with no dimension parameter will generally assume single-dimensional behaviors. - SQL expressions of type :class:`.Array` have support for "index" and + SQL expressions of type :class:`.types.ARRAY` have support for "index" and "slice" behavior. The Python ``[]`` operator works normally here, given integer indexes or slices. Arrays default to 1-based indexing. The operator produces binary expression @@ -1575,9 +1788,9 @@ class Array(Indexable, Concatenable, TypeEngine): mytable.c.data[2:7]: [1, 2, 3] }) - The :class:`.Array` type also provides for the operators - :meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all`. - The PostgreSQL-specific version of :class:`.Array` also provides additional + The :class:`.types.ARRAY` type also provides for the operators + :meth:`.types.ARRAY.Comparator.any` and :meth:`.types.ARRAY.Comparator.all`. + The PostgreSQL-specific version of :class:`.types.ARRAY` also provides additional operators. .. versionadded:: 1.1.0 @@ -1589,9 +1802,13 @@ class Array(Indexable, Concatenable, TypeEngine): """ __visit_name__ = 'ARRAY' + zero_indexes = False + """if True, Python zero-based indexes should be interpreted as one-based + on the SQL expression side.""" + class Comparator(Indexable.Comparator, Concatenable.Comparator): - """Define comparison operations for :class:`.Array`. + """Define comparison operations for :class:`.types.ARRAY`. More operators are available on the dialect-specific form of this type. See :class:`.postgresql.ARRAY.Comparator`. @@ -1601,11 +1818,32 @@ class Array(Indexable, Concatenable, TypeEngine): def _setup_getitem(self, index): if isinstance(index, slice): return_type = self.type - elif self.type.dimensions is None or self.type.dimensions == 1: - return_type = self.type.item_type + if self.type.zero_indexes: + index = slice( + index.start + 1, + index.stop + 1, + index.step + ) + index = Slice( + _literal_as_binds( + index.start, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.stop, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.step, name=self.expr.key, + type_=type_api.INTEGERTYPE) + ) else: - adapt_kw = {'dimensions': self.type.dimensions - 1} - return_type = self.type.adapt(self.type.__class__, **adapt_kw) + if self.type.zero_indexes: + index += 1 + if self.type.dimensions is None or self.type.dimensions == 1: + return_type = self.type.item_type + else: + adapt_kw = {'dimensions': self.type.dimensions - 1} + return_type = self.type.adapt( + self.type.__class__, **adapt_kw) return operators.getitem, index, return_type @@ -1635,7 +1873,7 @@ class Array(Indexable, Concatenable, TypeEngine): :func:`.sql.expression.any_` - :meth:`.Array.Comparator.all` + :meth:`.types.ARRAY.Comparator.all` """ operator = operator if operator else operators.eq @@ -1670,7 +1908,7 @@ class Array(Indexable, Concatenable, TypeEngine): :func:`.sql.expression.all_` - :meth:`.Array.Comparator.any` + :meth:`.types.ARRAY.Comparator.any` """ operator = operator if operator else operators.eq @@ -1683,18 +1921,18 @@ class Array(Indexable, Concatenable, TypeEngine): def __init__(self, item_type, as_tuple=False, dimensions=None, zero_indexes=False): - """Construct an :class:`.Array`. + """Construct an :class:`.types.ARRAY`. E.g.:: - Column('myarray', Array(Integer)) + Column('myarray', ARRAY(Integer)) Arguments are: :param item_type: The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like - ``INTEGER[][]``, are constructed as ``Array(Integer)``, not as - ``Array(Array(Integer))`` or such. + ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as + ``ARRAY(ARRAY(Integer))`` or such. :param as_tuple=False: Specify whether return results should be converted to tuples from lists. This parameter is @@ -1706,7 +1944,7 @@ class Array(Indexable, Concatenable, TypeEngine): on the database, how it goes about interpreting Python and result values, as well as how expression behavior in conjunction with the "getitem" operator works. See the description at - :class:`.Array` for additional detail. + :class:`.types.ARRAY` for additional detail. :param zero_indexes=False: when True, index values will be converted between Python zero-based and SQL one-based indexes, e.g. @@ -1714,7 +1952,7 @@ class Array(Indexable, Concatenable, TypeEngine): to the database. """ - if isinstance(item_type, Array): + if isinstance(item_type, ARRAY): raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " "handles multi-dimensional arrays of basetype") if isinstance(item_type, type): diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index 15bfad831..87c776e8c 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -487,6 +487,12 @@ class SuiteRequirements(Requirements): return exclusions.open() @property + def json_type(self): + """target platform implements a native JSON type.""" + + return exclusions.closed() + + @property def precision_numerics_general(self): """target backend has general support for moderately high-precision numerics.""" diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index 230aeb1e9..c6e882fb5 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -5,7 +5,7 @@ from ..assertions import eq_ from ..config import requirements from sqlalchemy import Integer, Unicode, UnicodeText, select from sqlalchemy import Date, DateTime, Time, MetaData, String, \ - Text, Numeric, Float, literal, Boolean + Text, Numeric, Float, literal, Boolean, cast, null, JSON from ..schema import Table, Column from ... import testing import decimal @@ -586,7 +586,246 @@ class BooleanTest(_LiteralRoundTripFixture, fixtures.TablesTest): ) -__all__ = ('UnicodeVarcharTest', 'UnicodeTextTest', +class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): + __requires__ = 'json_type', + __backend__ = True + + datatype = JSON + + data1 = { + "key1": "value1", + "key2": "value2" + } + + data2 = { + "Key 'One'": "value1", + "key two": "value2", + "key three": "value ' three '" + } + + data3 = { + "key1": [1, 2, 3], + "key2": ["one", "two", "three"], + "key3": [{"four": "five"}, {"six": "seven"}] + } + + data4 = ["one", "two", "three"] + + data5 = { + "nested": { + "elem1": [ + {"a": "b", "c": "d"}, + {"e": "f", "g": "h"} + ], + "elem2": { + "elem3": {"elem4": "elem5"} + } + } + } + + @classmethod + def define_tables(cls, metadata): + Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('name', String(30), nullable=False), + Column('data', cls.datatype), + Column('nulldata', cls.datatype(none_as_null=True)) + ) + + def test_round_trip_data1(self): + self._test_round_trip(self.data1) + + def _test_round_trip(self, data_element): + data_table = self.tables.data_table + + config.db.execute( + data_table.insert(), + {'name': 'row1', 'data': data_element} + ) + + row = config.db.execute( + select([ + data_table.c.data, + ]) + ).first() + + eq_(row, (data_element, )) + + def test_round_trip_none_as_sql_null(self): + col = self.tables.data_table.c['nulldata'] + + with config.db.connect() as conn: + conn.execute( + self.tables.data_table.insert(), + {"name": "r1", "data": None} + ) + + eq_( + conn.scalar( + select([self.tables.data_table.c.name]). + where(col.is_(null())) + ), + "r1" + ) + + eq_( + conn.scalar( + select([col]) + ), + None + ) + + def test_round_trip_json_null_as_json_null(self): + col = self.tables.data_table.c['data'] + + with config.db.connect() as conn: + conn.execute( + self.tables.data_table.insert(), + {"name": "r1", "data": JSON.NULL} + ) + + eq_( + conn.scalar( + select([self.tables.data_table.c.name]). + where(cast(col, String) == 'null') + ), + "r1" + ) + + eq_( + conn.scalar( + select([col]) + ), + None + ) + + def test_round_trip_none_as_json_null(self): + col = self.tables.data_table.c['data'] + + with config.db.connect() as conn: + conn.execute( + self.tables.data_table.insert(), + {"name": "r1", "data": None} + ) + + eq_( + conn.scalar( + select([self.tables.data_table.c.name]). + where(cast(col, String) == 'null') + ), + "r1" + ) + + eq_( + conn.scalar( + select([col]) + ), + None + ) + + def _criteria_fixture(self): + config.db.execute( + self.tables.data_table.insert(), + [{"name": "r1", "data": self.data1}, + {"name": "r2", "data": self.data2}, + {"name": "r3", "data": self.data3}, + {"name": "r4", "data": self.data4}, + {"name": "r5", "data": self.data5}] + ) + + def _test_index_criteria(self, crit, expected): + self._criteria_fixture() + with config.db.connect() as conn: + eq_( + conn.scalar( + select([self.tables.data_table.c.name]). + where(crit) + ), + expected + ) + + def test_crit_spaces_in_key(self): + col = self.tables.data_table.c['data'] + self._test_index_criteria( + cast(col["key two"], String) == '"value2"', + "r2" + ) + + def test_crit_simple_int(self): + col = self.tables.data_table.c['data'] + self._test_index_criteria( + cast(col[1], String) == '"two"', + "r4" + ) + + def test_crit_mixed_path(self): + col = self.tables.data_table.c['data'] + self._test_index_criteria( + cast(col[("key3", 1, "six")], String) == '"seven"', + "r3" + ) + + def test_crit_string_path(self): + col = self.tables.data_table.c['data'] + self._test_index_criteria( + cast(col[("nested", "elem2", "elem3", "elem4")], String) + == '"elem5"', + "r5" + ) + + def test_unicode_round_trip(self): + s = select([ + cast( + { + util.u('réveillé'): util.u('réveillé'), + "data": {"k1": util.u('drôle')} + }, + self.datatype + ) + ]) + eq_( + config.db.scalar(s), + { + util.u('réveillé'): util.u('réveillé'), + "data": {"k1": util.u('drôle')} + }, + ) + + def test_eval_none_flag_orm(self): + from sqlalchemy.ext.declarative import declarative_base + from sqlalchemy.orm import Session + + Base = declarative_base() + + class Data(Base): + __table__ = self.tables.data_table + + s = Session(testing.db) + + d1 = Data(name='d1', data=None, nulldata=None) + s.add(d1) + s.commit() + + s.bulk_insert_mappings( + Data, [{"name": "d2", "data": None, "nulldata": None}] + ) + eq_( + s.query( + cast(self.tables.data_table.c.data, String), + cast(self.tables.data_table.c.nulldata, String) + ).filter(self.tables.data_table.c.name == 'd1').first(), + ("null", None) + ) + eq_( + s.query( + cast(self.tables.data_table.c.data, String), + cast(self.tables.data_table.c.nulldata, String) + ).filter(self.tables.data_table.c.name == 'd2').first(), + ("null", None) + ) + + +__all__ = ('UnicodeVarcharTest', 'UnicodeTextTest', 'JSONTest', 'DateTest', 'DateTimeTest', 'TextTest', 'NumericTest', 'IntegerTest', 'DateTimeHistoricTest', 'DateTimeCoercedToDateTimeTest', diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index d82e683d9..ac6d3b439 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -17,7 +17,7 @@ __all__ = ['TypeEngine', 'TypeDecorator', 'UserDefinedType', 'SmallInteger', 'BigInteger', 'Numeric', 'Float', 'DateTime', 'Date', 'Time', 'LargeBinary', 'Binary', 'Boolean', 'Unicode', 'Concatenable', 'UnicodeText', 'PickleType', 'Interval', 'Enum', - 'Indexable', 'Array'] + 'Indexable', 'ARRAY', 'JSON'] from .sql.type_api import ( adapt_type, @@ -28,7 +28,7 @@ from .sql.type_api import ( UserDefinedType ) from .sql.sqltypes import ( - Array, + ARRAY, BIGINT, BINARY, BLOB, @@ -53,6 +53,7 @@ from .sql.sqltypes import ( INTEGER, Integer, Interval, + JSON, LargeBinary, MatchType, NCHAR, diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 49a8cfabd..f8c5c1c48 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -7,7 +7,7 @@ from sqlalchemy import testing import datetime from sqlalchemy import Table, MetaData, Column, Integer, Enum, Float, select, \ func, DateTime, Numeric, exc, String, cast, REAL, TypeDecorator, Unicode, \ - Text, null, text, column, Array, any_, all_ + Text, null, text, column, ARRAY, any_, all_ from sqlalchemy.sql import operators from sqlalchemy import types import sqlalchemy as sa @@ -819,7 +819,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): def test_array_index_map_dimensions(self): col = column('x', postgresql.ARRAY(Integer, dimensions=3)) is_( - col[5].type._type_affinity, Array + col[5].type._type_affinity, ARRAY ) assert isinstance( col[5].type, postgresql.ARRAY @@ -828,7 +828,7 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): col[5].type.dimensions, 2 ) is_( - col[5][6].type._type_affinity, Array + col[5][6].type._type_affinity, ARRAY ) assert isinstance( col[5][6].type, postgresql.ARRAY @@ -859,8 +859,8 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): ) # type affinity is Array... - is_(arrtable.c.intarr[1:3].type._type_affinity, Array) - is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + is_(arrtable.c.intarr[1:3].type._type_affinity, ARRAY) + is_(arrtable.c.strarr[1:3].type._type_affinity, ARRAY) # but the slice returns the actual type assert isinstance(arrtable.c.intarr[1:3].type, postgresql.ARRAY) @@ -892,12 +892,12 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): type_=postgresql.ARRAY(Integer) )[3], "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " - "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(param_7)s]" + "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(array_cat_1)s]" ) def test_array_agg_generic(self): expr = func.array_agg(column('q', Integer)) - is_(expr.type.__class__, types.Array) + is_(expr.type.__class__, types.ARRAY) is_(expr.type.item_type.__class__, Integer) def test_array_agg_specific(self): @@ -1811,7 +1811,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_getitem(self): self._test_where( self.hashcol['bar'] == None, - "(test_table.hash -> %(hash_1)s) IS NULL" + "test_table.hash -> %(hash_1)s IS NULL" ) def test_cols_get(self): @@ -1894,7 +1894,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_concat_get(self): self._test_cols( (self.hashcol + self.hashcol)['foo'], - "test_table.hash || test_table.hash -> %(param_1)s AS anon_1" + "(test_table.hash || test_table.hash) -> %(param_1)s AS anon_1" ) def test_cols_keys(self): @@ -1980,6 +1980,21 @@ class HStoreRoundTripTest(fixtures.TablesTest): cols = insp.get_columns('data_table') assert isinstance(cols[2]['type'], HSTORE) + def test_literal_round_trip(self): + # in particular, this tests that the array index + # operator against the function is handled by PG; with some + # array functions it requires outer parenthezisation on the left and + # we may not be doing that here + expr = hstore( + postgresql.array(['1', '2']), + postgresql.array(['3', None]))['1'] + eq_( + testing.db.scalar( + select([expr]) + ), + "3" + ) + @testing.requires.psycopg2_native_hstore def test_insert_native(self): engine = testing.db @@ -2411,100 +2426,33 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): ) % expected ) - def test_bind_serialize_default(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_bind_processor( - dialect) - eq_( - proc({"A": [1, 2, 3, True, False]}), - '{"A": [1, 2, 3, true, false]}' - ) - - def test_bind_serialize_None(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_bind_processor( - dialect) - eq_( - proc(None), - 'null' - ) - - def test_bind_serialize_none_as_null(self): - dialect = postgresql.dialect() - proc = JSON(none_as_null=True)._cached_bind_processor( - dialect) - eq_( - proc(None), - None - ) - eq_( - proc(null()), - None - ) - - def test_bind_serialize_null(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_bind_processor( - dialect) - eq_( - proc(null()), - None - ) - - def test_result_deserialize_default(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_result_processor( - dialect, None) - eq_( - proc('{"A": [1, 2, 3, true, false]}'), - {"A": [1, 2, 3, True, False]} - ) - - def test_result_deserialize_null(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_result_processor( - dialect, None) - eq_( - proc('null'), - None - ) - - def test_result_deserialize_None(self): - dialect = postgresql.dialect() - proc = self.test_table.c.test_column.type._cached_result_processor( - dialect, None) - eq_( - proc(None), - None - ) - # This test is a bit misleading -- in real life you will need to cast to # do anything def test_where_getitem(self): self._test_where( self.jsoncol['bar'] == None, - "(test_table.test_column -> %(test_column_1)s) IS NULL" + "test_table.test_column -> %(test_column_1)s IS NULL" ) def test_where_path(self): self._test_where( self.jsoncol[("foo", 1)] == None, - "(test_table.test_column #> %(test_column_1)s) IS NULL" + "test_table.test_column #> %(test_column_1)s IS NULL" ) def test_path_typing(self): col = column('x', JSON()) is_( - col['q'].type._type_affinity, JSON + col['q'].type._type_affinity, types.JSON ) is_( - col[('q', )].type._type_affinity, JSON + col[('q', )].type._type_affinity, types.JSON ) is_( - col['q']['p'].type._type_affinity, JSON + col['q']['p'].type._type_affinity, types.JSON ) is_( - col[('q', 'p')].type._type_affinity, JSON + col[('q', 'p')].type._type_affinity, types.JSON ) def test_custom_astext_type(self): @@ -2528,7 +2476,7 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_getitem_as_text(self): self._test_where( self.jsoncol['bar'].astext == None, - "(test_table.test_column ->> %(test_column_1)s) IS NULL" + "test_table.test_column ->> %(test_column_1)s IS NULL" ) def test_where_getitem_astext_cast(self): @@ -2548,7 +2496,7 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_path_as_text(self): self._test_where( self.jsoncol[("foo", 1)].astext == None, - "(test_table.test_column #>> %(test_column_1)s) IS NULL" + "test_table.test_column #>> %(test_column_1)s IS NULL" ) def test_cols_get(self): diff --git a/test/requirements.py b/test/requirements.py index ff93a9c3d..7031a70dc 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -531,6 +531,14 @@ class DefaultRequirements(SuiteRequirements): 'sybase') @property + def json_type(self): + return only_on([ + lambda config: against(config, "mysql >= 5.7") and + not config.db.dialect._is_mariadb, + "postgresql >= 9.3" + ]) + + @property def datetime_literals(self): """target dialect supports rendering of a date, time, or datetime as a literal string, e.g. via the TypeEngine.literal_processor() method. diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 51cfcb919..0074d789b 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -2,7 +2,7 @@ from sqlalchemy.testing import eq_, is_ import datetime from sqlalchemy import func, select, Integer, literal, DateTime, Table, \ Column, Sequence, MetaData, extract, Date, String, bindparam, \ - literal_column, Array, Numeric + literal_column, ARRAY, Numeric from sqlalchemy.sql import table, column from sqlalchemy import sql, util from sqlalchemy.sql.compiler import BIND_TEMPLATES @@ -558,7 +558,7 @@ class ReturnTypeTest(fixtures.TestBase): def test_array_agg(self): expr = func.array_agg(column('data', Integer)) - is_(expr.type._type_affinity, Array) + is_(expr.type._type_affinity, ARRAY) is_(expr.type.item_type._type_affinity, Integer) def test_mode(self): @@ -573,13 +573,13 @@ class ReturnTypeTest(fixtures.TestBase): def test_percentile_cont_array(self): expr = func.percentile_cont(0.5, 0.7).within_group( column('data', Integer)) - is_(expr.type._type_affinity, Array) + is_(expr.type._type_affinity, ARRAY) is_(expr.type.item_type._type_affinity, Integer) def test_percentile_cont_array_desc(self): expr = func.percentile_cont(0.5, 0.7).within_group( column('data', Integer).desc()) - is_(expr.type._type_affinity, Array) + is_(expr.type._type_affinity, ARRAY) is_(expr.type.item_type._type_affinity, Integer) def test_cume_dist(self): diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 03c0f89be..6a6c749a4 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -15,7 +15,7 @@ from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData from sqlalchemy.sql import compiler from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \ - Boolean, NullType, MatchType, Indexable, Concatenable, Array + Boolean, NullType, MatchType, Indexable, Concatenable, ARRAY, JSON from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util @@ -632,7 +632,125 @@ class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): ) -class IndexableTest(fixtures.TestBase, testing.AssertsCompiledSQL): +class JSONIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL): + def setUp(self): + class MyTypeCompiler(compiler.GenericTypeCompiler): + def visit_mytype(self, type, **kw): + return "MYTYPE" + + def visit_myothertype(self, type, **kw): + return "MYOTHERTYPE" + + class MyCompiler(compiler.SQLCompiler): + + def visit_json_getitem_op_binary(self, binary, operator, **kw): + return self._generate_generic_binary( + binary, " -> ", **kw + ) + + def visit_json_path_getitem_op_binary( + self, binary, operator, **kw): + return self._generate_generic_binary( + binary, " #> ", **kw + ) + + def visit_getitem_binary(self, binary, operator, **kw): + raise NotImplementedError() + + class MyDialect(default.DefaultDialect): + statement_compiler = MyCompiler + type_compiler = MyTypeCompiler + + class MyType(JSON): + __visit_name__ = 'mytype' + + pass + + self.MyType = MyType + self.__dialect__ = MyDialect() + + def test_setup_getitem(self): + col = Column('x', self.MyType()) + + is_( + col[5].type._type_affinity, JSON + ) + is_( + col[5]['foo'].type._type_affinity, JSON + ) + is_( + col[('a', 'b', 'c')].type._type_affinity, JSON + ) + + def test_getindex_literal_integer(self): + + col = Column('x', self.MyType()) + + self.assert_compile( + col[5], + "x -> :x_1", + checkparams={'x_1': 5} + ) + + def test_getindex_literal_string(self): + + col = Column('x', self.MyType()) + + self.assert_compile( + col['foo'], + "x -> :x_1", + checkparams={'x_1': 'foo'} + ) + + def test_path_getindex_literal(self): + + col = Column('x', self.MyType()) + + self.assert_compile( + col[('a', 'b', 3, 4, 'd')], + "x #> :x_1", + checkparams={'x_1': ('a', 'b', 3, 4, 'd')} + ) + + def test_getindex_sqlexpr(self): + + col = Column('x', self.MyType()) + col2 = Column('y', Integer()) + + self.assert_compile( + col[col2], + "x -> y", + checkparams={} + ) + + self.assert_compile( + col[col2 + 8], + "x -> (y + :y_1)", + checkparams={'y_1': 8} + ) + + def test_override_operators(self): + special_index_op = operators.custom_op('$$>') + + class MyOtherType(JSON, TypeEngine): + __visit_name__ = 'myothertype' + + class Comparator(TypeEngine.Comparator): + + def _adapt_expression(self, op, other_comparator): + return special_index_op, MyOtherType() + + comparator_factory = Comparator + + col = Column('x', MyOtherType()) + self.assert_compile( + col[5], + "x $$> :x_1", + checkparams={'x_1': 5} + ) + + +class ArrayIndexOpTest(fixtures.TestBase, testing.AssertsCompiledSQL): def setUp(self): class MyTypeCompiler(compiler.GenericTypeCompiler): def visit_mytype(self, type, **kw): @@ -658,31 +776,14 @@ class IndexableTest(fixtures.TestBase, testing.AssertsCompiledSQL): statement_compiler = MyCompiler type_compiler = MyTypeCompiler - class MyType(Indexable, TypeEngine): + class MyType(ARRAY): __visit_name__ = 'mytype' def __init__(self, zero_indexes=False, dimensions=1): if zero_indexes: self.zero_indexes = zero_indexes self.dimensions = dimensions - - class Comparator(Indexable.Comparator): - def _setup_getitem(self, index): - if isinstance(index, slice): - return_type = self.type - elif self.type.dimensions is None or \ - self.type.dimensions == 1: - return_type = Integer() - else: - adapt_kw = {'dimensions': self.type.dimensions - 1} - # this is also testing the behavior of adapt() - # that we can pass kw that override constructor kws. - # required a small change to util.constructor_copy(). - return_type = self.type.adapt( - self.type.__class__, **adapt_kw) - - return operators.getitem, index, return_type - comparator_factory = Comparator + self.item_type = Integer() self.MyType = MyType self.__dialect__ = MyDialect() @@ -694,13 +795,13 @@ class IndexableTest(fixtures.TestBase, testing.AssertsCompiledSQL): col = Column('x', self.MyType(dimensions=3)) is_( - col[5].type._type_affinity, self.MyType + col[5].type._type_affinity, ARRAY ) eq_( col[5].type.dimensions, 2 ) is_( - col[5][6].type._type_affinity, self.MyType + col[5][6].type._type_affinity, ARRAY ) eq_( col[5][6].type.dimensions, 1 @@ -2273,7 +2374,7 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): t = Table( 'tab1', m, - Column('arrval', Array(Integer)), + Column('arrval', ARRAY(Integer)), Column('data', Integer) ) return t diff --git a/test/sql/test_types.py b/test/sql/test_types.py index f1fb611fb..bb227bc5d 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -10,14 +10,14 @@ from sqlalchemy import ( and_, func, Date, LargeBinary, literal, cast, text, Enum, type_coerce, VARCHAR, Time, DateTime, BigInteger, SmallInteger, BOOLEAN, BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT, - INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, Array) + INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, ARRAY, JSON) from sqlalchemy.sql import ddl from sqlalchemy.sql import visitors from sqlalchemy import inspection from sqlalchemy import exc, types, util, dialects for name in dialects.__all__: __import__("sqlalchemy.dialects.%s" % name) -from sqlalchemy.sql import operators, column, table +from sqlalchemy.sql import operators, column, table, null from sqlalchemy.schema import CheckConstraint, AddConstraint from sqlalchemy.engine import default from sqlalchemy.testing.schema import Table, Column @@ -140,7 +140,7 @@ class AdaptTest(fixtures.TestBase): for is_down_adaption, typ, target_adaptions in adaptions(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif issubclass(typ, Array): + elif issubclass(typ, ARRAY): t1 = typ(String) else: t1 = typ() @@ -148,6 +148,8 @@ class AdaptTest(fixtures.TestBase): if not issubclass(typ, types.Enum) and \ issubclass(cls, types.Enum): continue + if cls.__module__.startswith("test"): + continue # print("ADAPT %s -> %s" % (t1.__class__, cls)) t2 = t1.adapt(cls) @@ -190,7 +192,7 @@ class AdaptTest(fixtures.TestBase): for typ in self._all_types(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif issubclass(typ, Array): + elif issubclass(typ, ARRAY): t1 = typ(String) else: t1 = typ() @@ -1406,23 +1408,98 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults): return o.read() +class JSONTest(fixtures.TestBase): + + def setup(self): + metadata = MetaData() + self.test_table = Table('test_table', metadata, + Column('id', Integer, primary_key=True), + Column('test_column', JSON), + ) + self.jsoncol = self.test_table.c.test_column + + self.dialect = default.DefaultDialect() + self.dialect._json_serializer = None + self.dialect._json_deserializer = None + + def test_bind_serialize_default(self): + proc = self.test_table.c.test_column.type._cached_bind_processor( + self.dialect) + eq_( + proc({"A": [1, 2, 3, True, False]}), + '{"A": [1, 2, 3, true, false]}' + ) + + def test_bind_serialize_None(self): + proc = self.test_table.c.test_column.type._cached_bind_processor( + self.dialect) + eq_( + proc(None), + 'null' + ) + + def test_bind_serialize_none_as_null(self): + proc = JSON(none_as_null=True)._cached_bind_processor( + self.dialect) + eq_( + proc(None), + None + ) + eq_( + proc(null()), + None + ) + + def test_bind_serialize_null(self): + proc = self.test_table.c.test_column.type._cached_bind_processor( + self.dialect) + eq_( + proc(null()), + None + ) + + def test_result_deserialize_default(self): + proc = self.test_table.c.test_column.type._cached_result_processor( + self.dialect, None) + eq_( + proc('{"A": [1, 2, 3, true, false]}'), + {"A": [1, 2, 3, True, False]} + ) + + def test_result_deserialize_null(self): + proc = self.test_table.c.test_column.type._cached_result_processor( + self.dialect, None) + eq_( + proc('null'), + None + ) + + def test_result_deserialize_None(self): + proc = self.test_table.c.test_column.type._cached_result_processor( + self.dialect, None) + eq_( + proc(None), + None + ) + + class ArrayTest(fixtures.TestBase): def _myarray_fixture(self): - class MyArray(Array): + class MyArray(ARRAY): pass return MyArray def test_array_index_map_dimensions(self): - col = column('x', Array(Integer, dimensions=3)) + col = column('x', ARRAY(Integer, dimensions=3)) is_( - col[5].type._type_affinity, Array + col[5].type._type_affinity, ARRAY ) eq_( col[5].type.dimensions, 2 ) is_( - col[5][6].type._type_affinity, Array + col[5][6].type._type_affinity, ARRAY ) eq_( col[5][6].type.dimensions, 1 @@ -1435,8 +1512,8 @@ class ArrayTest(fixtures.TestBase): m = MetaData() arrtable = Table( 'arrtable', m, - Column('intarr', Array(Integer)), - Column('strarr', Array(String)), + Column('intarr', ARRAY(Integer)), + Column('strarr', ARRAY(String)), ) is_(arrtable.c.intarr[1].type._type_affinity, Integer) is_(arrtable.c.strarr[1].type._type_affinity, String) @@ -1445,11 +1522,11 @@ class ArrayTest(fixtures.TestBase): m = MetaData() arrtable = Table( 'arrtable', m, - Column('intarr', Array(Integer)), - Column('strarr', Array(String)), + Column('intarr', ARRAY(Integer)), + Column('strarr', ARRAY(String)), ) - is_(arrtable.c.intarr[1:3].type._type_affinity, Array) - is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + is_(arrtable.c.intarr[1:3].type._type_affinity, ARRAY) + is_(arrtable.c.strarr[1:3].type._type_affinity, ARRAY) def test_array_getitem_slice_type_dialect_level(self): MyArray = self._myarray_fixture() @@ -1459,8 +1536,8 @@ class ArrayTest(fixtures.TestBase): Column('intarr', MyArray(Integer)), Column('strarr', MyArray(String)), ) - is_(arrtable.c.intarr[1:3].type._type_affinity, Array) - is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + is_(arrtable.c.intarr[1:3].type._type_affinity, ARRAY) + is_(arrtable.c.strarr[1:3].type._type_affinity, ARRAY) # but the slice returns the actual type assert isinstance(arrtable.c.intarr[1:3].type, MyArray) |