I hope your work makes into the SQLite source code. It will be useful.
Gerry Snyder
On Tue, Jan 29, 2019 at 12:16 AM Andy Goth <andrew.m.g...@gmail.com> wrote:
> I wish to define custom aggregate functions in Tcl, but this capability is
> currently not exposed through the Tcl interface. Thus I am thinking about
> how best to add it. Here's a first crack at a design proposal:
>
> Extend the [db function] command to accept an -aggregate switch that makes
> the new function be an aggregate function. Otherwise, the function defaults
> to being a scalar function.
>
> When an aggregate Tcl function is called from SQL, it is invoked in two
> phases: step and final. The step phase receives the input data, and the
> final phase produces the result.
>
> During step, the function is invoked once for each row (in the group). For
> the first row (in the group), the first argument to the function will be
> empty string, and subsequent arguments are the SQL values from the row
> being processed. For each additional row (in the group), the first argument
> is the Tcl value returned by the prior invocation, and subsequent arguments
> are as above.
>
> During final, the function is invoked one last time (at the end of each
> group). Its sole argument is the return value of the last step invocation,
> or empty string if there were no rows. The return value of the Tcl function
> is used as the return value of the SQL function.
>
> If there were no rows and GROUP BY was used, the function is not invoked at
> all, because there were no groups.
>
> The Tcl function can tell whether it is in the step or final phase by how
> many arguments it receives. If it receives multiple, it is in step. If it
> receives only one, it is in final.
>
> Depending on how it is written, the Tcl function may be able to tell if
> it's being called for the first time (within the group) by checking if its
> first argument is empty string. If non-empty, it definitely has been called
> before (within the group). If empty, it is most likely on the first row
> and, if in step, may need to initialize.
>
> However, if the aggregate function wishes to disregard some of its input
> data, it may choose to return empty string to discard any state data
> arising from the current and prior rows (in the group). This will result in
> empty string being the first argument to the next invocation. This mode of
> operation is ill-advised but not illegal, though maybe some valid use cases
> may exist, provided that ORDER BY is being used.
>
> It is an error to pass an aggregate Tcl function zero arguments.
>
> Here's an example that will return the entire Fossil global_config table as
> a key/value dict:
>
> db function list -deterministic -aggregate {
> apply {{state args} {
> concat $state $args
> }
> }
>
> db onecolumn {
> SELECT list(name, value)
> FROM global_config
> ORDER BY name
> }
>
> As for implementation, I think the existing code can be leveraged to a
> great extent. Adapt the existing tclSqlFunc() to be the backend to new
> tclSqlFuncScalar(), tclSqlFuncStep(), and tclSqlFuncFinal() functions, and
> adjust the DB_FUNCTION code to recognize -aggregate and pass different
> function pointers accordingly. Use sqlite3_aggregate_context() to hold the
> Tcl_Obj * resulting from each invocation, then let it be the first argument
> to the next invocation.
>
> I would be happy to implement this myself, since this is functionality I
> will be needing soon. Once I'm satisfied with it and have test suite
> updates, what's the recommended method for sharing my patch?
>
> Question: does xFinal() get called if an error occurs during (or between)
> calling xStep()? Are errors even possible? I'm curious if there's any way
> to leak the Tcl_Obj pointed to by the aggregate context.
> _______________________________________________
> 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
