Sqlite json

Added support for SQLite's json functionality via the new
SQLite implementation for :class:`.sqltypes.JSON`, :class:`.sqlite.JSON`.
The name used for the type is ``JSON``, following an example found at
SQLite's own documentation. Pull request courtesy Ilja Everilä.

Fixes: #3850
Change-Id: I3d2714fb8655343a99d13dc751b16b93d05d7dda
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/434

3 files changed, 104 insertions, 3 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/__init__.py b/lib/sqlalchemy/dialects/sqlite/__init__.py
index cb5337adb..a73581521 100644
--- a/lib/sqlalchemy/dialects/sqlite/__init__.py
+++ b/lib/sqlalchemy/dialects/sqlite/__init__.py
@@ -8,7 +8,7 @@
 from . import base, pysqlite, pysqlcipher  # noqa
 
 from sqlalchemy.dialects.sqlite.base import (
-    BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, FLOAT, INTEGER, REAL,
+    BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, FLOAT, INTEGER, JSON, REAL,
     NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, VARCHAR
 )
 
@@ -17,5 +17,5 @@ base.dialect = dialect = pysqlite.dialect
 
 
 __all__ = ('BLOB', 'BOOLEAN', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL',
-           'FLOAT', 'INTEGER', 'NUMERIC', 'SMALLINT', 'TEXT', 'TIME',
+           'FLOAT', 'INTEGER', 'JSON', 'NUMERIC', 'SMALLINT', 'TEXT', 'TIME',
            'TIMESTAMP', 'VARCHAR', 'REAL', 'dialect')
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 5117025fb..c6932be8f 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -478,6 +478,7 @@ from ...sql import compiler
 from ...types import (BLOB, BOOLEAN, CHAR, DECIMAL, FLOAT,
                       INTEGER, REAL, NUMERIC, SMALLINT, TEXT,
                       TIMESTAMP, VARCHAR)
+from .json import JSON, JSONIndexType, JSONPathType
 
 
 class _DateTimeMixin(object):
@@ -753,6 +754,9 @@ class TIME(_DateTimeMixin, sqltypes.Time):
 colspecs = {
     sqltypes.Date: DATE,
     sqltypes.DateTime: DATETIME,
+    sqltypes.JSON: JSON,
+    sqltypes.JSON.JSONIndexType: JSONIndexType,
+    sqltypes.JSON.JSONPathType: JSONPathType,
     sqltypes.Time: TIME,
 }
 
@@ -771,6 +775,7 @@ ischema_names = {
     'FLOAT': sqltypes.FLOAT,
     'INT': sqltypes.INTEGER,
     'INTEGER': sqltypes.INTEGER,
+    'JSON': JSON,
     'NUMERIC': sqltypes.NUMERIC,
     'REAL': sqltypes.REAL,
     'SMALLINT': sqltypes.SMALLINT,
@@ -855,6 +860,16 @@ class SQLiteCompiler(compiler.SQLCompiler):
         return "%s IS %s" % (self.process(binary.left),
                              self.process(binary.right))
 
+    def visit_json_getitem_op_binary(self, binary, operator, **kw):
+        return "JSON_QUOTE(JSON_EXTRACT(%s, %s))" % (
+            self.process(binary.left, **kw),
+            self.process(binary.right, **kw))
+
+    def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
+        return "JSON_QUOTE(JSON_EXTRACT(%s, %s))" % (
+            self.process(binary.left, **kw),
+            self.process(binary.right, **kw))
+
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
 
@@ -973,6 +988,12 @@ class SQLiteTypeCompiler(compiler.GenericTypeCompiler):
         else:
             return "TIME_CHAR"
 
+    def visit_JSON(self, type_, **kw):
+        # note this name provides NUMERIC affinity, not TEXT.
+        # should not be an issue unless the JSON value consists of a single
+        # numeric value.   JSONTEXT can be used if this case is required.
+        return "JSON"
+
 
 class SQLiteIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = set([
@@ -1065,9 +1086,12 @@ class SQLiteDialect(default.DefaultDialect):
     _broken_fk_pragma_quotes = False
     _broken_dotted_colnames = False
 
-    def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
+    def __init__(self, isolation_level=None, native_datetime=False,
+                 _json_serializer=None, _json_deserializer=None, **kwargs):
         default.DefaultDialect.__init__(self, **kwargs)
         self.isolation_level = isolation_level
+        self._json_serializer = _json_serializer
+        self._json_deserializer = _json_deserializer
 
         # this flag used by pysqlite dialect, and perhaps others in the
         # future, to indicate the driver is handling date/timestamp
diff --git a/lib/sqlalchemy/dialects/sqlite/json.py b/lib/sqlalchemy/dialects/sqlite/json.py
new file mode 100644
index 000000000..90929fbd8
--- /dev/null
+++ b/lib/sqlalchemy/dialects/sqlite/json.py
@@ -0,0 +1,77 @@
+from ... import types as sqltypes
+
+
+class JSON(sqltypes.JSON):
+    """SQLite JSON type.
+
+    SQLite supports JSON as of version 3.9 through its JSON1_ extension. Note
+    that JSON1_ is a
+    `loadable extension <https://www.sqlite.org/loadext.html>`_ and as such
+    may not be available, or may require run-time loading.
+
+    The :class:`.sqlite.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 wrapped in the ``JSON_QUOTE`` function at the database level.
+    Extracted values are quoted in order to ensure that the results are
+    always JSON string values.
+
+    .. versionadded:: 1.3
+
+    .. seealso::
+
+        JSON1_
+
+    .. _JSON1: https://www.sqlite.org/json1.html
+
+    """
+
+
+# Note: these objects currently match exactly those of MySQL, however since
+# these are not generalizable to all JSON implementations, remain separately
+# implemented for each dialect.
+class _FormatTypeMixin(object):
+    def _format_value(self, value):
+        raise NotImplementedError()
+
+    def bind_processor(self, dialect):
+        super_proc = self.string_bind_processor(dialect)
+
+        def process(value):
+            value = self._format_value(value)
+            if super_proc:
+                value = super_proc(value)
+            return value
+
+        return process
+
+    def literal_processor(self, dialect):
+        super_proc = self.string_literal_processor(dialect)
+
+        def process(value):
+            value = self._format_value(value)
+            if super_proc:
+                value = super_proc(value)
+            return value
+
+        return process
+
+
+class JSONIndexType(_FormatTypeMixin, sqltypes.JSON.JSONIndexType):
+
+    def _format_value(self, value):
+        if isinstance(value, int):
+            value = "$[%s]" % value
+        else:
+            value = '$."%s"' % value
+        return value
+
+
+class JSONPathType(_FormatTypeMixin, sqltypes.JSON.JSONPathType):
+    def _format_value(self, value):
+        return "$%s" % (
+            "".join([
+                "[%s]" % elem if isinstance(elem, int)
+                else '."%s"' % elem for elem in value
+            ])
+        )