Support for SQLite views?

[gennaios]

Hello,

It seems that views are not yet supported for SQLite? At least I didn't see them generated.

I use them as external content tables for full-text search. With the way FTS works with SQLite, in some cases I need to retrieve values from the view to delete or insert entries to the FTS index.

Such would be very helpful.

[stephenafamo]

I would be very happy to review a PR for this, I will label this so that anyone interested may decide to send one in.

[gennaios]

My experience with go isn't that great but if I have the time I may be able to try. Seems that some of the code is already there. I also tried white listing views and such caused some error; perhaps a fix for that would go together.

[stephenafamo]

I also tried white listing views and such caused some error; perhaps a fix for that would go together.

What error did you get?

[gennaios]

Error: unable to initialize tables: no tables found in database. Perhaps someday boiler needs to check whether a whitelist entry is a table or view. I haven't checked the docs, as I haven't tried such before, if views are specified differently.

Something else, perhaps boiler should by default exclude some SQLite tables. sqlite_stat1 and sqlite_stat4 are used to gather query statistics such that SQLite can later optimize such. An error from not blacklisting them:

Error: unable to initialize tables: primary key missing in tables (sqlite_stat4)

[stephenafamo]

Perhaps someday boiler needs to check whether a whitelist entry is a table or view.

A whitelist says "generate for only these tables/views". Since SQLite views are not supported, then if you whitelist only a view, it is the same as whitelisting a non-existent table.

Something else, perhaps boiler should by default exclude some SQLite tables. sqlite_stat1 and sqlite_stat4 are used to gather query statistics such that SQLite can later optimize such.

Kindly send in a PR for this 🙏🏾