SQLite Forum

Improved virtual tables

Improved virtual tables

(1) By anonymous on 2020-03-15 19:43:06 [source]

I thought there should be some improved features for virtual tables, such as:

  • Incremental blob I/O for virtual tables. You could add methods such as xBlobOpen, xBlobClose, xBlobRead, xBlobWrite, for incremental blob I/O.
  • Consume LIMIT and OFFSET clauses. I am not sure how the interface would work, although it is clear that they would not be consumable unless the ORDER BY clause is consumed.
  • Indexes on expressions (including nondeterministic expressions and aggregate expressions; in the case of virtual tables, this is useful, since you might want to consume ORDER BY RANDOM() LIMIT 1 or something else).
  • Batch updates. Again I don't know how the interface should work, although one idea is extra fields for the sqlite3_index_info structure.
  • The colUsed field specifies more columns than needed. Some may be all uses consumable (and you may want to distinguish this case), and I think for updates, it always sets it to -1. A better way may be possible, although this may break compatibility. One way to avoid that could be to call xBestIndex multiple times if needed, and/or to have a separate configuration option for virtual tables which controls this, I suppose.
  • SQLITE_INDEX_CONSTRAINT_TRUE and SQLITE_INDEX_CONSTRAINT_FALSE. (Together with indexes on expressions, this can allow a few more things to be done that otherwise aren't done (for example, if you have an index on X < Y then you can consume this in this way).)
  • It may be sensible for the table-valued function syntax to automatically add unary + before the arguments to prevent the constraint from being reversed.

I may be wrong about some things; you can please to make a comment and then we can see how to improve the ideas of it, to know what exactly should be done (if any). For example, there may be better ways for some things than what I have mentioned in that list above.

(2) By anonymous on 2020-03-17 17:57:15 in reply to 1 [link] [source]

Upsert is not currently implemented for virtual tables, either. Maybe it should be implemented, perhaps sqlite3_vtab_config can enable it, and then it is supported only for the primary key (or rowid), and then sqlite3_vtab_on_conflict will return SQLITE_UPSERT, and then if xUpdate returns SQLITE_CONSTRAINT_PRIMARYKEY or SQLITE_CONSTRAINT_ROWID (whichever one is applicable) then it will execute the upsert.