- 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.
fixes #3547
- 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.
fixes #3619
- reorganization of migration docs etc. to try to refer both to
the fixes to JSON that helps Postgresql while at the same time
indicating these are new features of the new base JSON type.
- a rework of the Array/Indexable system some more, moving things
that are specific to Array out of Indexable.
- new operators for JSON indexing added to core so that these can
be compiled by the PG and MySQL dialects individually
- rename sqltypes.Array to sqltypes.ARRAY - as there is no generic
Array implementation, this is an uppercase type for now, consistent
with the new sqltypes.JSON type that is also not a generic implementation.
There may need to be some convention change to handle the case of
datatypes that aren't generic, rely upon DB-native implementations,
but aren't necessarily all named the same thing.

3 files changed, 125 insertions, 6 deletions

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