I made many updates to my implementation, which can be found here:

https://chiselapp.com/user/andy/repository/sqlite-andy/timeline?r=andygoth-tcl-functionhttps://chiselapp.com/user/andy/repository/sqlite-andy/artifact?fn=src/tclsqlite.c&ci=andygoth-tcl-functionhttps://chiselapp.com/user/andy/repository/sqlite-andy/vdiff?from=root:andygoth-tcl-function&to=andygoth-tcl-functionDocumentation 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

[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users