Issue: Cannot create indexes using JSONPath with SQLite
Author: blemasle
State: closed
Created: 2018-08-17 05:12:39

Cannot create indexes using JSONPath with SQLite and apsw

Hi,

peewee : 3.6.4
apsw : 3.24.0-r1, with json1 extension enabled

For a rather tricky case, I need to create an index using the `json_extract` function. But the use of `JSONPath` produce a parametized SQL query, which is prohibited during indexes creation.

```python
import logging

from peewee import Model
from playhouse.apsw_ext import APSWDatabase
from playhouse.apsw_ext import TextField
from playhouse.sqlite_ext import JSONField

logging.basicConfig(level=logging.DEBUG)
db = APSWDatabase(':memory:')

class Anything(Model):
    value = JSONField()
    class Meta:
        database = db

anything_trick_case_idx = Anything.index('json_extract(value, "$.foo")', name='anything_trick_case_idx') # Work
anything_trick_case_idx = Anything.index(Anything.value['foo'], name='anything_trick_case_idx') # Fail
Anything.add_index(anything_trick_case_idx)
Anything.create_table()

k = Anything()
k.value = '{"foo": "bar"}'
k.save()

Anything.select().where(Anything.value['foo'] == 'bar').execute()
```

## Working log output
>DEBUG:peewee:('CREATE TABLE IF NOT EXISTS "anything" ("id" INTEGER NOT NULL PRIMARY KEY, "value" JSON NOT NULL)', [])
>DEBUG:peewee:('CREATE INDEX IF NOT EXISTS "anything_trick_case_idx" ON "anything" (json_extract(value, "$.foo"))', [])
>DEBUG:peewee:('INSERT INTO "anything" ("value") VALUES (?)', ['"{\\"foo\\": \\"bar\\"}"'])
>DEBUG:peewee:('SELECT "t1"."id", "t1"."value" FROM "anything" AS "t1" WHERE (json_extract("t1"."value", ?) = ?)', ['$.foo', 'bar'])

## Failing log output
>DEBUG:peewee:('CREATE TABLE IF NOT EXISTS "anything" ("id" INTEGER NOT NULL PRIMARY KEY, "value" JSON NOT NULL)', [])
>DEBUG:peewee:('CREATE INDEX IF NOT EXISTS "anything_trick_case_idx" ON "anything" (json_extract("value", ?))', ['$.foo'])
>Traceback (most recent call last):
>  File ".\test.py", line 23, in <module>
>    Anything.create_table()
>  File "C:\Users\bertr\Documents\Projects\Development\Python\GpsTrackerServer\.venv\env-3.7\lib\site-packages\peewee.py", line 5670, in create_table
>    cls._schema.create_all(safe, **options)
>  File "C:\Users\bertr\Documents\Projects\Development\Python\GpsTrackerServer\.venv\env-3.7\lib\site-packages\peewee.py", line 4920, in create_all
>    self.create_indexes(safe=safe)
>  File "C:\Users\bertr\Documents\Projects\Development\Python\GpsTrackerServer\.venv\env-3.7\lib\site-packages\peewee.py", line 4834, in create_indexes
>    self.database.execute(query)
>  File "C:\Users\bertr\Documents\Projects\Development\Python\GpsTrackerServer\.venv\env-3.7\lib\site-packages\peewee.py", line 2653, in execute
>    return self.execute_sql(sql, params, commit=commit)
>  File "C:\Users\bertr\Documents\Projects\Development\Python\GpsTrackerServer\.venv\env-3.7\lib\site-packages\playhouse\apsw_ext.py", line 110, in execute_sql
>    cursor.execute(sql, params or ())
>  File "c:\users\bertr\documents\projects\development\python\apsw-3.24.0-r1\src\cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
>    AddTraceBackHere(__FILE__, __LINE__, "APSWCursor_execute.sqlite3_prepare", "{s: O, s: O}",
>  File "c:\users\bertr\documents\projects\development\python\apsw-3.24.0-r1\src\statementcache.c", line 386, in sqlite3_prepare
>    AddTraceBackHere(__FILE__, __LINE__, "sqlite3_prepare", "{s: N}", "sql", convertutf8stringsize(buffer, buflen));
>apsw.SQLError: SQLError: parameters prohibited in index expressions

Is there any way to not use the raw sql synthax, or to fix the bug ideally ? :)

Cheers,
Bertrand

There's no way to fix this, unfortunately it's just a limit of SQLite that it won't accept the parameterized query in CREATE INDEX.

Indeed, I did not realize that was the case with every parametized index creation. Even the advanced index creation [stated in the doc](http://docs.peewee-orm.com/en/latest/peewee/models.html#advanced-index-creation) will fail with SQLite.

Would it not be possible somehow to alter the ouput of `_create_index` to render a full SQL query rather than a parametized one in the case of SQLite ? If there is no solution to improve this, I think the documentation should mention this somehow :)