Yes, but we can live and hope for the day a sqlite project roadmap is
disclosed with eponymous vtab API which supports completely dynamic column
outputs and a fully featured API for row valued language atoms.
When that day comes pivot tables, matrix operations, and other row type
introspection computations will be trivial to implement. This strategy
would be far superior to the ad hoc SQL syntax additions used by other
engines to support, for example, the very useful pivot table primitive.
In the meantime I was hoping to find out if anybody had some clever ideas
about how to shoehorn in a workable albeit opaque eval("<sql>") which
somehow communicates to the outer context the missing and vital information
about dynamic type of row along with the results.
Consider the following strategy for implementing pivot table.
There already exists an eval("<sql>") extension function: File
ext/misc/eval.c <http://www.sqlite.org/src/finfo?name=ext/misc/eval.c>
which returns a string value. Multiple invocations of this function could
be used to compute the output dimensions and column names of an arbitrary
query.
What's missing is a complementary operation which outputs a table of column
names and types for an arbitrary SQL query. Call this eponymous function
columnsof("<sql>")
By combining those two operations, it would be possible to natively compute
within sqlite a generalized pivot table which, for output at least, look
right.
The final missing ingredient would be an eponymous function, call it
tableof("<csv>") which can transform a well formed CSV string pivot table
from the previous step into a fully fledged table object with the correct
number and type of columns from the CSV string.
On Wed, Mar 8, 2017 at 12:09 AM, Dominique Devienne <ddevie...@gmail.com>
wrote:
> On Wed, Mar 8, 2017 at 3:47 AM, petern <peter.nichvolo...@gmail.com>
> wrote:
>
> > Things have changed somewhat since I wrote those comments. After the
> > introduction of row values in 3.15 https://www.sqlite.org/rowvalue.html
> ,
> > at least, now the sqlite ecosystem can cope with efficient vector valued
> > data for passing parameters into and out of the hypothetical eval() or
> > meta() vtab module.
> >
>
> How so? There are zero API functions taking or returning "row values",
> which is currently a pure SQL abstraction, and not a "physical" vector
> of values you can manipulate.
>
> You can create an eval() function now, via an eponymous vtable IMHO [1].
> it just won't participate in the outer query plan, of course. --DD
>
> [1] https://www.sqlite.org/vtab.html#eponymous_virtual_tables
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
