Sqlite.Aggregate

Create and add custom aggregate functions to a SQLite database.

Sqlite.Aggregate.register "fruit_portions" db <|
    Sqlite.Aggregate.aggregate
        { init = 0
        , function = 
            Sqlite.Aggregate.start <| \state ->
            Sqlite.Aggregate.arg Decode.string <| \split ->
            Sqlite.Aggregate.arg Decode.int <| \count -> 
            Sqlite.Aggregate.return <| \direction -> (
                let
                    portions =
                        when split is
                            "half" -> 2
                            "third" -> 3
                            "quarter" -> 4
                            _ -> 1
                in
                when direction is
                    Sqlite.Aggregate.Entering ->
                        state + (portions * count)

                    Sqlite.Aggregate.Exiting ->
                        state - (portions * count)
            )
        , result = \state -> Encode.int state
        }

SQLite aggregate functions are much like foldl in Gren - You take some state and some amount of data (in this case, rows returned from some SQL) and run a function over that state and each row, updating the state until there's no more data and the final state can be returned.

As an example, the custom aggregate function above could be used with the following SQL:

SELECT 
    fruit_portions('half', banana) AS banana_portion_count,
    fruit_portions('quarter', apple) AS apple_portion_count,
    fruit_portions('full', pear) AS pear_portion_count
FROM baskets;

In this case, you'd get the appropriate amount of portions for each type of fruit returned without any additional work or code in Gren or SQL. The custom aggregate function handles it for you!

Like custom functions, custom aggregate functions can have any number of expected arguments by chaining together multiple arg functions for each expected argument. All arguments are expected to be provided when using the function in SQL.

Custom aggregate functions in this module work as normal aggregate functions and as window functions. It does this by providing the Direction type, which allows both aggregate and window functions to work properly. If you want to write a custom aggregate function that will not be used as a window function, Direction can be ignored or is expected to always be Entering.

type Function state

A custom SQLite aggregate function.

type Aggregate state

An custom SQLite aggregate.

type Direction

= Entering

| Exiting

A type representing if a value is entering or exiting the aggregate window.

aggregate :

{ init : state

, function : Function state

, result : state -> Value

}

-> Aggregate state

Define an aggregate function.

init is the initial state the aggregate function will start with.
function is the actual function that runs on SQLite data . You can construct this function with the start, arg, and return functions in this module.
result transforms the final state value into a Sqlite.Encode.Value so SQLite can properly understand and use the result.

When the aggregate function is constructed, it can be registered with the SQLite database using the register function.

start : (state -> Function state) -> Function state

Start the process of creating a new aggregate function. This is required in order to get access to the aggregate state.

arg : Decoder a -> (a -> Function state) -> Function state

An argument in a custom SQLite function. The argument must be decoded into a Gren value by using a Sqlite.Decode.Decoder.

return : (Direction -> state) -> Function state

The final step in an aggregate function, giving you the Direction of the data and allowing you to return a final state value.

Aggregate functions must be registered with a database each time it's opened and used. It's recommended you register all necessary functions right after opening the database.