Add support for MySQL JSONpr/221

3 files changed, 352 insertions, 2 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/__init__.py b/lib/sqlalchemy/dialects/mysql/__init__.py
index c1f78bd1d..55a4ebca7 100644
--- a/lib/sqlalchemy/dialects/mysql/__init__.py
+++ b/lib/sqlalchemy/dialects/mysql/__init__.py
@@ -21,6 +21,8 @@ from .base import \
     TINYBLOB, TINYINT, TINYTEXT,\
     VARBINARY, VARCHAR, YEAR, dialect
 
+from .json import JSON
+
 __all__ = (
     'BIGINT', 'BINARY', 'BIT', 'BLOB', 'BOOLEAN', 'CHAR', 'DATE', 'DATETIME',
     'DECIMAL', 'DOUBLE', 'ENUM', 'DECIMAL', 'FLOAT', 'INTEGER', 'INTEGER',
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 988746403..9ed13eb91 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -534,6 +534,13 @@ output::
    ``explicit_defaults_for_timestamp``.  Prior to this version, it will
    not render "NOT NULL" for a TIMESTAMP column that is ``nullable=False``.
 
+JSON Types
+----------
+
+The MySQL dialect supports the JSON datatype as of version 5.7:
+
+* :class:`.mysql.JSON`
+
 """
 
 import datetime
@@ -602,6 +609,8 @@ RESERVED_WORDS = set(
      'get', 'io_after_gtids', 'io_before_gtids', 'master_bind', 'one_shot',
         'partition', 'sql_after_gtids', 'sql_before_gtids',  # 5.6
 
+     'json',  # 5.7
+
      ])
 
 AUTOCOMMIT_RE = re.compile(
@@ -2225,6 +2234,12 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
         else:
             return self._extend_numeric(type_, "SMALLINT")
 
+    def visit_JSON(self, type_, **kw):
+        if not self.dialect._supports_json:
+            util.warn("Current MySQL version does not support JSON.")
+            return "TEXT"
+        return "JSON"
+
     def visit_BIT(self, type_, **kw):
         if type_.length is not None:
             return "BIT(%s)" % type_.length
@@ -2433,10 +2448,13 @@ class MySQLDialect(default.DefaultDialect):
         })
     ]
 
-    def __init__(self, isolation_level=None, **kwargs):
-        kwargs.pop('use_ansiquotes', None)   # legacy
+    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_deserializer = json_deserializer
+        self._json_serializer = json_serializer
 
     def on_connect(self):
         if self.isolation_level is not None:
@@ -2607,6 +2625,11 @@ class MySQLDialect(default.DefaultDialect):
         return self.server_version_info is None or \
             self.server_version_info >= (4, 0, 2)
 
+    @property
+    def _supports_json(self):
+        return self.server_version_info is None or \
+               self.server_version_info >= (5, 7, 9)
+
     @reflection.cache
     def get_schema_names(self, connection, **kw):
         rp = connection.execute("SHOW schemas")
diff --git a/lib/sqlalchemy/dialects/mysql/json.py b/lib/sqlalchemy/dialects/mysql/json.py
new file mode 100644
index 000000000..1eab58eea
--- /dev/null
+++ b/lib/sqlalchemy/dialects/mysql/json.py
@@ -0,0 +1,325 @@
+# postgresql/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 collections
+import json
+
+from .base import ischema_names
+from ... import types as sqltypes
+from ...sql import elements
+from ...sql import expression
+from ... import util
+from operator import getitem
+
+
+__all__ = ('JSON',)
+
+
+class JSON(sqltypes.Indexable, sqltypes.TypeEngine):
+
+    """Represent the Mysql JSON type.
+
+    The :class:`.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"}
+            )
+
+    :class:`.JSON` provides several operations:
+
+    * Index operations::
+
+        data_table.c.data['some key']
+
+    * Index operations with CAST
+      (equivalent to
+        ``CAST(JSON_EXPORT(data_table.data, $."some key" AS <type>)``)::
+
+        data_table.c.data['some key'].cast(Integer)
+
+    * Path index operations::
+
+        data_table.c.data[('key_1', 'key_2', ..., 'key_n')]
+
+    * Path attribute operations::
+
+        data_table.c.data.json.key_1.key_2
+
+        (equivalent to: data_table.c.data.json['key_1']['key_2'])
+
+    * Wildcards::
+
+        data_table.c.data['*'], rendered as [*], array index wildcard
+        data_table.c.data['%'], rendered as .*, object key wildcard
+
+    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`::
+
+        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
+    or per-connection level.   E.g.::
+
+        engine = create_engine("postgresql://scott:tiger@localhost/test",
+                                json_serializer=my_serialize_fn,
+                                json_deserializer=my_deserialize_fn
+                        )
+
+    When using the psycopg2 dialect, the json_deserializer is registered
+    against the database using ``psycopg2.extras.register_default_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())  # *always* insert SQL NULL
+        obj2 = MyObject(json_value=JSON.NULL)  # *always* insert JSON "null"
+
+        session.add_all([obj1, obj2])
+        session.commit()
+
+    .. versionadded:: 1.1
+
+    """
+
+    def __init__(self, none_as_null=False):
+        """Construct a :class:`.JSON` type.
+
+        :param none_as_null: 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:`.JSON.NULL`
+
+         .. versionadded:: 1.1.0
+
+         """
+        self.none_as_null = none_as_null
+
+    @property
+    def should_evaluate_none(self):
+        return not self.none_as_null
+
+    class Comparator(
+            sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator):
+        """Define comparison operations for :class:`.JSON`."""
+
+        def _setup_getitem(self, index):
+            return getitem, JSON._massage_index(index), self.type
+
+        def operate(self, op, *other, **kwargs):
+            if op.__name__ == 'getitem':
+                return JSON.MySqlJsonExtract(self.expr, *other)
+            else:
+                if len(other) > 0 and isinstance(
+                        other[0], (list, tuple, dict)):
+                    # convert list, tuple or dict json doc.
+                    other = list(other)
+                    other[0] = JSON.MySqlJsonDocument(other[0], op)
+
+                return super(JSON.Comparator, self).operate(
+                    op, *other, **kwargs)
+
+        @property
+        def json(self):
+            """Property which allows attribute access to document fields
+            eg:
+                data_table.c.data.json.k1
+
+            is equivalent to:
+                data_table.c.data['k1']
+            """
+            return JSON.MySqlJsonExtract(self.expr, "", attribute_access=True)
+
+    comparator_factory = Comparator
+
+    class MySqlJsonExtract(expression.Function, dict):
+        """Represents a Json Extract Function which can be invoked via getitem.
+        """
+
+        def __init__(self, doc, path, attribute_access=False):
+            self.json_doc = doc
+            self.json_path = path
+            self.attribute_access = attribute_access
+            expression.Function.__init__(
+                    self, "JSON_EXTRACT", doc, '$' + path, type_=JSON)
+
+            # these two lines, along with the json property on the
+            # comparator above, support the __getattr__ method below
+            if self.attribute_access:
+                local_dict = {}
+                dict.__init__(self, local_dict)
+
+        def __getitem__(self, path):
+            """getitem on an existing json_extract just adds to the path"""
+            new_path = self.json_path + JSON._massage_index(path)
+            return JSON.MySqlJsonExtract(
+                self.json_doc, new_path, attribute_access=self.attribute_access)
+
+        def __getattr__(self, item):
+            """Maps attributes to json doc path.  Only called if this class
+            does not have an attribute with this name.  This allows attribute
+            based access to sub-documents, but it means that hasattr()
+            will now be broken when acting on this class.
+
+            The test for "item[0] != '_' works around this for the known
+            cases of hasattr() usage in the framework. So attributes names
+            not starting with '_' will be considered to be part of the
+            json document.
+            """
+            if self.attribute_access or item[0] != '_':
+                return self.__getitem__(item)
+            raise AttributeError(item)
+
+    class MySqlJsonDocument(expression.Function):
+        """Represents a literal Json Document"""
+
+        def __init__(self, doc, op=None):
+            if isinstance(doc, (list, tuple)):
+                name = "json_array"
+                empty = '[]'
+            else:
+                name = "json_object"
+                empty = '{}'
+
+            self.obj = doc
+            bound_doc = expression.bindparam(
+                    name, value=doc, type_=JSON, unique=True,
+                    _compared_to_operator=op, _compared_to_type=JSON)
+
+            # could not find a way to represent a json literal document,
+            #  so resorted to JSON_MERGE() function which takes as a
+            # parameter a json document.
+            expression.Function.__init__(
+                self, "JSON_MERGE", empty, bound_doc, type_=JSON)
+
+    @staticmethod
+    def _massage_index(index):
+        """
+        Integers are assumed to be array lookups and are formatted as: [x]
+        Strings are assumed to be dict lookups and are formatted as: ."x"
+        Wildcard ['*'] from python is formatted as an array wildcard [*]
+        Wildcard ['%'] from python is formatted as an object wildcard .*
+        """
+
+        if isinstance(index, int):
+            index = '[%d]' % index
+        elif not isinstance(index, util.string_types):
+            assert isinstance(index, collections.Sequence)
+            tokens = ["%s" % JSON._massage_index(elem) for elem in index]
+            index = "".join(tokens)
+        elif index == '%':
+            index = '.*'
+        elif index == '*':
+            index = '[*]'
+        elif index[:2] != '**':
+            index = '."%s"' % index
+        return index
+
+    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:
+                encoded = json_serializer(value).encode(encoding)
+            else:
+                encoded = json_serializer(value)
+
+            return encoded
+
+        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
+
+
+ischema_names['json'] = JSON