vscode-sqlite icon indicating copy to clipboard operation
vscode-sqlite copied to clipboard

support sqlite 3.41.0 by avoiding double quotes for values

Open Pyrolistical opened this issue 1 year ago • 18 comments

In standard sql single quotes are for values and double quotes are for identities like column names. Although sqlite does support double quote values this seems to have changed in 3.41.0.

I have sqlite 3.41.0 installed and when this extension uses it, I get the following error when I try to open a database:

[12:12:12 AM][vscode-sqlite][ERROR] Failed to open database '...\database.db': Parse error near line 4: no such column: table
  aster                                 WHERE (type="table" OR type="view")     
                                      error here ---^

I was able to track down the query: https://github.com/AlexCovizzi/vscode-sqlite/blob/c09e34880e5cb34cf7960bca908e56a52e67207d/src/sqlite/schema.ts#L46-L50

If one tries to run that query on an empty sqlite database in the cli, we get a similar error

>sqlite3 
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT name, type FROM sqlite_master
   ...> WHERE (type="table" OR type="view")
   ...> AND name <> 'sqlite_sequence'
   ...> AND name <> 'sqlite_stat1'
   ...> ORDER BY type ASC, name ASC;
Parse error: no such column: table
  SELECT name, type FROM sqlite_master WHERE (type="table" OR type="view") AND n
                                     error here ---^
sqlite>

The workaround is to pin the sqlite client to the one included with the extension. Set the vscode setting sqlite.sqlite3 to the correct client in .vscode\extensions\alexcvzz.vscode-sqlite-0.14.1\bin

Pyrolistical avatar Mar 15 '23 19:03 Pyrolistical