1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
= Adding functions
== Scalar Functions
Scalar functions are data transformers.
[source,cpp]
----
include::../example/tutorial.cpp[tag=to_upper]
----
<1> The context is optional, and can be used to share between invocations, within the same query or to set errors/return values.
<2> Let this function take 1 parameter. If this is `span::extent` it will be any size. Functions can be overloaded by the number of parameters.
<3> Any exception will be turned into an sqlite-error code.
<4> The function is determistic, i.e. it has no side effects (see https://www.sqlite.org/c3ref/c_deterministic.html[Function Flags])
The return type `T` of the function gets transformed into an sqlite value by using a `tag_invoke` overload.
This interface is public and meant to be extended.
[source,cpp]
----
void tag_invoke(set_result_tag, sqlite3_context * ctx, T);
----
The builtin types are:
- `blob`
- `zero_blob`
- `double`
- `std::int64_t`
- `nullptr`
- `string_view`
- `std::string`
- `sqlite::value`
- `variant2::monostate`
- `error`
- `std::unique_ptr<T>` (see the https://www.sqlite.org/bindptr.html[pointer passing interface])
- `variant2::variant<Ts...>` if all `Ts` are supported
- `result<T>`
- `boost::json::value` //when including boost/sqlite/json.hpp
The translation of the exceptions happens as follows:
[cols="1,1"]
|===
| Type | Sqlite error code
| `boost::system::system_error` | `.code()`
| `std::bad_alloc` | SQLITE_NOMEM
| `std::length_error` | SQLITE_TOOBIG
| `std::out_of_range` | SQLITE_RANGE
| `std::logic_error` | SQLITE_MISUSE
| `...` | SQLITE_ERROR
|===
NOTE: You can also return an <<`result`,sqlite::result>> type instead of throwing exceptions.
== A ggregate functions
An aggregate function builds a value from multiple values of the same column, e.g:
[source,sqlite]
----
select avg(age) from users ;
----
`avg` is a built-in function, but
Below is a toy example. We count how many retirees are among the users, based on a retirement age.
[source,cpp]
----
include::../example/tutorial.cpp[tag=oldest]
----
The `retirees` object will be constructed for every query it's used in, and the parameters passed witht he `make_tuple`
will be passed to the constructor.
The `step` will be called for every value and `final` at the end when the query has ended and a value is required.
The value types & exceptions are the same as for the scalar function.
== Window functions
Window functions look similar to aggregate functions, they only need an `inverse` function, that shares the signature with `step`.
It is recommended to consult the https://www.sqlite.org/windowfunctions.html[sqlite window function documentation].
NOTE: Window functions are only available for sqlite 3.25.0 and higher.
|
