sqlite: CREATE INDEX throws error <stmt identifier clojure real> expected, got 'CREATE'

[soshial]

SQLDelight Version

2.0.1

SQLDelight Dialect

app.cash.sqldelight:sqlite-3-38-dialect:2.0.1

Describe the Bug

Returns error: <stmt identifier clojure real> expected, got 'CREATE'

Stacktrace

No response

[griffio]

🥨 Work-around remove the quotes on "index name" as the issue is that the current grammar doesn't support quotes on index name -> https://github.com/AlecKazakova/sql-psi/blob/2cd4b1f2f8509c2c638cecf55baa675b32d03aae/core/src/main/kotlin/com/alecstrong/sql/psi/core/sql.bnf#L427 The grammar is in https://github.com/AlecKazakova/sql-psi/ as the index name definition seems to be the case for all dialects.

I may be wrong but SQLite uses double quotes for string literals rather than for quoting identifiers.

For example below -this executes even though "bogus" doesn't exist because sqlite treats quoted names as expressions.

CREATE TABLE xyz(
  id INTEGER
);

CREATE INDEX "idx-bogus"
ON xyz ("bogus");

MySql can use back-ticks and PostgreSql can use quotes in index names

[soshial]

What do you mean by bogus table? I am confused, because creating table with double quotes works with SQLite perfectly. And my SELECTs also work as intended and the table compiles into correct class objects.

[griffio]

sorry to confuse - sqlite does allow the index name to be in quotes, I was referring to create index where putting column names in quotes - it allows an index to be created even if that column name doesn't exist in the table.

Currently, to get it to compile with SqlDelight remove the quotes around match2parish - currently sql-delight/sql-psi defines table names and column names to be any quoted string but index names are defined as identifiers (a-z,A-Z,0-9) only.

As sqlite allows quoted index names then SqlDelight should as well.

[soshial]

Great, now it compiles, but the indices are not created on my DB file. Hmmm

UPD. I removed all double quotes.