http://sqlite.org/bindptr.html
Great stuff. This will make it possible to, e.g., have JSON functions that keep parsed JSON texts... parsed for as long as possible. The user's SQL will have to invoke an encoder function to encode parsed JSON texts back to actual text, sadly, but that's not a very big deal. I'd like to propose making this slightly (a lot) better as follows: a) add a serializer callback function (not just a destructor) When SQLite3 would need to store a pointer value, or when a CAST is used on a pointer value, SQLite3 would invoke the serializer to produce a value of the normal types supported by SQLite3 (e.g, text, blob, ...). b) allow one to define new SQL types as an existing type with an associated parser function that produces a pointer value. This would entail a sqlite3 C API for creating SQL "types" and associating with them: a pointer type name, a parser, a serializer, and a destructor. (This would also enable a simpler sqlite3_bind/result/value_pointer() API that doesn't need the destructor to be provided.) c) maybe it'd be nice to have a void * context data argument for the parser, serializer, and destructor functions. These two enhancements would make it possible to transparently use JSON in SQLite3 with JSON texts kept parsed as long as possible, thus reducing the amount of parsing and encoding. A particularly interesting use of this for me would be SQLite3 bindings for https://stedolan.github.io/jq. Imagine a jq() function (including aggregate and table-valued kinds of functions). One could: -- select the first foo key's value anywhere in the given JSON SELECT t.name, jq('..?|.foo', t.json_column) AS result FROM my_table t; -- select all the foo keys' values anywhere in the given JSON (here jq() is a table-valued function) SELECT r.name, r.result FROM (SELECT t.name AS name, jq('..?|.foo', t.json_column) AS result FROM my_table t) r; -- aggregate into a JSON array (yes, SQLite3 already has this, I'm aware, but this is just a simple example) SELECT t.name, jq('[inputs]', t.foo) FROM my_table t GROUP BY t.name; I hope it's not too late for this idea. Thanks, Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
