base JSON type + unification across pg/mysql

[Mike Bayer]

since #3547, mysql has a JSON type also, the approach we've taken in #3503 needs to be expanded.

Critical is that a base sqlalchemy.sql.sqltypes.JSON type needs to exist analogous to sqlalchemy.sql.sqltypes.Array. Basic JSON single-index and pathed-index values should then work on PG or MySQL backends equally, which means the operator massaging we see in PG's version of _setup_getitem() as well as the early massaging we see in https://github.com/zzzeek/sqlalchemy/pull/221/files must be moved to the compiler. The JSON index operator needs to be a first class operator, and the PG and MySQL dialects will need to handle rendering using a visit_json_getitem_binary operator.

Additionally, the value passed to the index needs to be assembled into another typed object of some kind, that is, it's not actually possible to generically handle an expression like x['foo'] without this, because the 'foo' value has to be specially string-formatted on the MySQL backend into a JSON_EXTRACT expression, an expression like x[('foo', 'bar')] also needs this on MySQL and on Postgresql needs to be formatted as x#>'{foo, bar}'. This implies type-level conversion, e.g. a type such as JsonIndexType which then has a Postgresql and a MySQL implemenation.

#!

Python                 PG                   MySQL
--------           --------         ---------------------------

col['x']           col->'x'         JSON_EXTRACT(col, '$.x')
col[5]             col->5           JSON_EXTRACT(col, '$[5]')
col[('foo', 1)]    col#>'{foo,1}'   JSON_EXTRACT(col, '$.foo[1]')

[Mike Bayer]

• 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.

→ <<cset a80bb4e5aabc>>