I have some proposals for feature requests for virtual table mechanism of SQLite. Some of this can be useful when accessing remote data over the internet or whatever. Here is the list:
* A new method "xInterrupt", called when sqlite3_interrupt() is called. This can be used to cancel downloads/uploads (e.g. if libcurl is used, xInterrupt might set a flag that causes the XFERINFOFUNCTION to return nonzero, which causes libcurl to return CURLE_ABORTED_BY_CALLBACK, which in turn may cause xFilter or xNext to return SQLITE_INTERRUPT). * Possibility to declare columns whose values are expressions; these columns are never given values by UPDATE or INSERT, have no name, and are always hidden. The expressions need not be deterministic. This can be used for example to consume "ORDER BY RANDOM() LIMIT 1"; it need only ask the server for a single random row, rather than downloading everything and selecting a random row on the client side. There are other uses too, such as more kind of constraints can be detected in the WHERE clause. * The ability to consume LIMIT/OFFSET clauses. (Of course, the LIMIT/OFFSET clause cannot be consumed unless the ORDER BY clause and WHERE clause are also consumed. Because there may be some unusable constraints, the virtual table module may not be given the LIMIT/OFFSET clauses even if there are some, because it cannot be consumed.) For example, the Scryfall API is paginated, so it would help with that; see also the above, where "ORDER BY RANDOM() LIMIT 1" is used to request a single random card, it can use that to know that you only want one and form the request it sends to the server in that way. (Note also that the built-in MIN() and MAX() functions may generate a ORDER BY and LIMIT clause automatically; to the virtual table, they may be considered the same as explicit ORDER BY and LIMIT clauses.) * A "boolean" constraint type. Together with consuming expressions, this might be used for implementing some kind of constraints which is otherwise difficult to do (such as checking if a bit is set in a number, or comparing if one column's value is greater than another). There are also some other less important stuff, but that still would help. The Scryfall documentation mentions many things. While they could be represented in SQL code, the current virtual table mechanism of SQLite is not capable to do a lot of these things so that an extension could be made to automatically convert the query. Some things are: * Aggregate queries (e.g. the "total_cards" field in Scryfall). * Queries with JOIN (this may be very difficult). There is also the possibility that some of the stuff I mentioned is difficult; in such a case, possibly, only some of the things I mentioned might be implemented and others aren't. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
