On Mon, Sep 8, 2014 at 9:44 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > Not really very difficult, and it does not require another type. You just > need a scalar function to process the URI passed to the function and return > the result -- just like the readfile() (in the fileio.c extension, or > included in the shell) function does for a "file-specifier" which can be > opened by the platform open api.
Here's intended usage: sqlite3> .load jq sqlite3> -- the jq() function takes a JSON text and a jq program as (TEXT type) arguments sqlite3> SELECT jq(json_column, '.Title') FROM some_table WHERE jq(json_column, '.Author.surname') LIKE 'Smith'; <titles> sqlite3> Now suppose you have a more complex query, with sub-queries say, so that a result from jq() can be fed to jq() again. Without a pseudo-type the jq() function must always return encoded JSON texts (as TEXT) and must always consume encoded JSON texts (as TEXT). With a pseudo-type the jq() function can consume either TEXT or the pseudo-type, and it outputs the pseudo-type, which is converted to TEXT as needed by calling the pseudo-type's encoder method. I'm not entirely sure what you and/or John M. have in mind as for using a URI scheme. I assume you mean something like "encode internal values as a URI", which is fine at first, but then you realize it leads to unavoidable leaks. Suppose we have a JSON C API that uses pointers (or structs with pointers) to objects on the heap. And then suppose that we encode these as "URIs" as TEXT or BLOB (the latter makes encoding and decoding easy). But now we have a problem: if a SQL expression concatenates/substrings these values we might not recognize the result as a valid "URI" and we'll leak the heap object. But with a pseudo-type there's no such risk. If at any point a SQL sub-expression needs to manipulate a value where one of these pseudo-types appears then SQLite3 would call the pseudo-type's encoder to get a JSON text (or XML, or whatever) and then apply sub-string/concat/whatever to that. I'd hate to have to say to users that they have to be careful with their SQL, else they could leak parsed JSON/XML objects... Among other things that would be insecure. A pseudo-type would be an optimization, really, and only an optimization, but a very valuable one, optimizing two things in the case of XPath and jq: - some XML / JSON parsing/encoding can be avoided - XPath / jq program compilations can be cached (like SQL statement compilation) For intense XML / JSON applications those two optimizations could mean a lot. I work with one application that uses Postgres because of the hstore. It used to support SQLite3 as well, but now it can't because SQLite3 has nothing like the hstore. It'd be nice if SQLite3 had something like the PG hstore, but nicer and with JSON as the text encoding. If SQLite3 could have a nice and well-performing XML interface then Richard Hipp's OpenDocument on SQLite3 concept would be easier to bring to fruition. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
