Result: Could not create insert trigger when column name is a keyword

[nikosvaggalis]

Hi Platform Windows 10x64. I tried to load the extension from CMD and sqlite3 with sqlite3 -header database.sqlite -cmd ".load sqlite_zstd.dll" but I get error " The specified module could not be found".

Then I tried loading the database together with the .dll in the tool "Db browser for SQLite" which did so successfully.But then when I run the following select statement

select zstd_enable_transparent('{"table": "notes", "column": "body", "compression_level": 19, "dict_chooser": "''a''"}')

I get error

Result: Could not create insert trigger

Caused by:
    0: near "order": syntax error
    1: Error code 1: SQL error or missing database
At line 1:

[phiresky]

What SQLite version? Try using 3.39

[nikosvaggalis]

Hi I tried with this one too https://github.com/nalgeon/sqlite/releases/tag/3.39.1

D:\shared\000>sqlite3 -header database.sqlite -cmd ".load sqlite_zstd.dll"
[2022-08-13T20:24:13Z INFO  sqlite_zstd::create_extension] [sqlite-zstd] initialized
SQLite version 3.39.1 2022-07-13 19:41:41
Enter ".help" for usage hints.
sqlite> select zstd_enable_transparent('{"table": "notes", "column": "body", "compression_level": 19, "dict_chooser": "''a''"}')
   ...> ;
[2022-08-13T20:24:39Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma journal_mode=WAL;`
[2022-08-13T20:24:39Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma auto_vacuum=full;`
[2022-08-13T20:24:39Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma busy_timeout=2000;` or higher
Runtime error: Could not create insert trigger

Caused by:
    0: near "order": syntax error
    1: Error code 1: SQL error or missing database

What SQLite version? Try using 3.39

[nikosvaggalis]

I found what it was.The table was containing a column called 'order' which is a reserved word and when trying to do any operations on it, it would give a syntax error.For instance update notes set order = 1; would result in error.The correct way of going about it is update notes set 'order' = 1;

So this is not a problem with your library.It works like a charm.So please consider this issue closed.Thanks

[phiresky]

oh, interesting. thanks for the info.

i think that is an issue with this library (even if minor), since the generated query strings could wrap all columns in ` to prevent this from happening. i'll reopen this since it can be fixed in sqlite-zstd, though I'm not sure if I'll have time to look into it. renaming the column is a good work around and i think this should be pretty uncommon in any case.

[nikosvaggalis]

turnin on debugging of the library revealed the problem.It tries to

create trigger `notes_insert_trigger`
instead of insert on `notes`
 for each row
     begin
     insert into `_notes_zstd`(id, parent_id, title, body, _body_dict, created_time, updated_time, 
is_conflict, latitude, longitude, altitude, author, source_url, is_todo, todo_due, todo_completed, 
source, source_application, application_data, order, user_created_time, user_updated_time, 
encryption_cipher_text, encryption_applied, markup_language, is_shared, 
share_id, conflict_original_id, master_key_id) select new.`id`,
    new.`parent_id`,
    new.`title`,
    new.`body` as `body`, null as `_body_dict`,
    new.`created_time`,
    new.`updated_time`,
    new.`is_conflict`,
    new.`latitude`,
    new.`longitude`,
    new.`altitude`,
    new.`author`,
    new.`source_url`,
    new.`is_todo`,
    new.`todo_due`,
    new.`todo_completed`,
    new.`source`,
    new.`source_application`,
    new.`application_data`,
    new.`order`,
    new.`user_created_time`,
    new.`user_updated_time`,
    new.`encryption_cipher_text`,
    new.`encryption_applied`,
    new.`markup_language`,
    new.`is_shared`,
    new.`share_id`,
    new.`conflict_original_id`,
    new.`master_key_id`;
                end;

it correctly quotes new.`order` but in the insert clause it does not.Turning application_data, order, to application_data,`order`, made it pass.