Improved virtual tables
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
xBlobWrite, for incremental blob I/O.
OFFSETclauses. I am not sure how the interface would work, although it is clear that they would not be consumable unless the
ORDER BYclause 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 1or something else).
- Batch updates. Again I don't know how the interface should work, although one idea is extra fields for the
colUsedfield 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
xBestIndexmultiple times if needed, and/or to have a separate configuration option for virtual tables which controls this, I suppose.
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 < Ythen 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.
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
SQLITE_CONSTRAINT_ROWID (whichever one is applicable) then it will execute the upsert.