I made many updates to my implementation, which can be found here:
https://chiselapp.com/user/andy/repository/sqlite-andy/timeline?r=andygoth-tcl-function https://chiselapp.com/user/andy/repository/sqlite-andy/artifact?fn=src/tclsqlite.c&ci=andygoth-tcl-function https://chiselapp.com/user/andy/repository/sqlite-andy/vdiff?from=root:andygoth-tcl-function&to=andygoth-tcl-function Documentation and test suite still to come. [db function] now accepts the following switches: -argcount N: Required argument count -deterministic: Promise to be consistent -state: Pass state value as argument after method name (default) -class: Use an object-oriented approach instead of -state -initial VAL: Constructor argument(s), defaults to empty -scalar: Create a scalar function (default) -aggregate: Create a non-window aggregate function -window: Create a window-enabled aggregate function -class and -initial require -window or -aggregate. First, let me demonstrate the -state (default) calling convention: db function square -deterministic { apply {{arg} { expr {$arg * $arg} }} } db function list -deterministic -window { apply {{method state args} { switch $method { step {concat $state $args} inverse {lrange $state [llength $args] end} value {set state} } }} } db function sum -deterministic -initial 0 -window { apply {{method state args} { switch $method { step {expr {$state + [lindex $args 0]}} inverse {expr {$state - [lindex $args 0]}} new - value {set state} } }} } db eval { WITH data (label, number) AS ( VALUES ('f', -6), ('b', -2), ('a', 1), ('e', 5), ('d', -4), ('c', 3)) SELECT sum(square(number)) OVER win AS sumsqu , list(label, number) OVER win AS list FROM data WINDOW win AS (ORDER BY label ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) } { puts [list $sumsqu $list] } 5 {a 1 b -2} 14 {a 1 b -2 c 3} 29 {b -2 c 3 d -4} 50 {c 3 d -4 e 5} 77 {d -4 e 5 f -6} 61 {e 5 f -6} Now, here's the -class calling convention: oo::class create ListAggregate { variable state method step {args} {lappend state {*}$args} method inverse {args} {set state [lrange $state [llength $args] end]} method value {} {return $state} } db function list -deterministic -window -class ListAggregate oo::class create SumAggregate { variable state constructor {{i 0}} {set state $i} method step {i} {set state [expr {$state + $i}]} method inverse {i} {set state [expr {$state - $i}]} method value {} {return $state} } db function sum -deterministic -window -class SumAggregate The output is the same as with the first syntax example. Here, I don't use the -initial switch, instead relying on its empty default. Furthermore, I rely on the default constructor argument of 0. With -initial, I could explicitly set an initial value for the sum. -initial behaves a little differently between -state and -class modes. For -state, it gives the argument to the "new" method. For -class, it gives the argument *list* to the "new" method. Hence, when empty or omitted, for -state mode the "new" method gets empty string as its argument, whereas for -class mode it gets no arguments. -initial can be used to implement a function once in Tcl, then instantiate it in SQLite several times with some varying parameters to create a family of functions. You may have noticed that I don't have "final" anymore. Instead, I rolled it into "value" and "destroy", the latter of which I haven't shown in my examples because the default is good. To see what's going on under the hood, try this: db function list -deterministic -window -initial {foo bar} -class { apply {{new args} { puts \[[lrange [info level 0] 2 end]\] list apply {{method args} { global state puts [lrange [info level 0] 2 end] switch $method { new {set state {}} step {lappend state {*}$args} inverse {set state [lrange $state [llength $args] end]} value {set state} destroy {unset state} } }} }} } db eval { WITH data (label, number) AS ( VALUES ('f', -6), ('b', -2), ('a', 1), ('e', 5), ('d', -4), ('c', 3)) SELECT label , list(label, number) OVER win AS list FROM data WINDOW win AS (ORDER BY label ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) } { puts [list $label $list] } The above prints: [new foo bar] [step a 1] [step b -2] [value] a {a 1 b -2} [step c 3] [value] b {a 1 b -2 c 3} [inverse a 1] [step d -4] [value] c {b -2 c 3 d -4} [inverse b -2] [step e 5] [value] d {c 3 d -4 e 5} [inverse c 3] [step f -6] [value] e {d -4 e 5 f -6} [inverse d -4] [value] f {e 5 f -6} [destroy] Here's the above example, reworked to not use -class: db function list -deterministic -window -initial {foo bar} { apply {{method state args} { puts \[[lrange [info level 0] 2 end]\] switch $method { step {concat $state $args} inverse {lrange $state [llength $args] end} value {set state} destroy {unset state} } }} } Used with the prior [db eval] query, the above prints: [new {foo bar}] [step {} a 1] [step {a 1} b -2] [value {a 1 b -2}] a {a 1 b -2} [step {a 1 b -2} c 3] [value {a 1 b -2 c 3}] b {a 1 b -2 c 3} [inverse {a 1 b -2 c 3} a 1] [step {b -2 c 3} d -4] [value {b -2 c 3 d -4}] c {b -2 c 3 d -4} [inverse {b -2 c 3 d -4} b -2] [step {c 3 d -4} e 5] [value {c 3 d -4 e 5}] d {c 3 d -4 e 5} [inverse {c 3 d -4 e 5} c 3] [step {d -4 e 5} f -6] [value {d -4 e 5 f -6}] e {d -4 e 5 f -6} [inverse {d -4 e 5 f -6} d -4] [value {e 5 f -6}] f {e 5 f -6} [destroy {e 5 f -6}] -- Andy Goth | <andrew.m.goth/at/gmail/dot/com> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
