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
andOFFSET
clauses. I am not sure how the interface would work, although it is clear that they would not be consumable unless theORDER 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 callxBestIndex
multiple times if needed, and/or to have a separate configuration option for virtual tables which controls this, I suppose. SQLITE_INDEX_CONSTRAINT_TRUE
andSQLITE_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 onX < 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.