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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
= Accessing a database
== Connecting to a database
The `sqlite::connection` holds a https://www.sqlite.org/c3ref/sqlite3.html[connection handle],
which is automatically closes on destruction.
[source,cpp]
.examples/tutorial.cpp
----
include::../example/tutorial.cpp[tag=conn_path]
----
Sqlite supports https://www.sqlite.org/inmemorydb.html[in memory databases],
which are created with the special path `":memory:"`. This is provided as a constant,
called `in_memory`.
[source,cpp]
----
include::../example/tutorial.cpp[tag=conn_mem]
----
== Executing SQL
Once the `conn` is created, the common first step is to create a database schema.
This can be done with the `execute` command, which allows the execution of multiple statements with one function invocation.
[source,cpp]
----
include::../example/tutorial.cpp[tag=execute]
----
Like most functions in sqlite, the `execute` function comes overloaded to allow two modes of error handling:
- errors as exception
- errors assigned to a `boost::system::error_code` and <<error_info>>
The second version takes both types by reference after the main arguments, i.e.:
.examples/tutorial_ec.cpp
[source,cpp]
----
include::../example/tutorial_ec.cpp[tag=execute]
----
The `boost::system::error_code` holds the https://www.sqlite.org/rescode.html[actual integer representing the error],
while `boost::sqlite::error_info` is a string-like type holding the https://www.sqlite.org/c3ref/errcode.html[error message].
For brevity, the tutorial section will use the exception overloads. See the <<reference>> for details on the overloads.
== Querying data
Once the database provides data it can be queried like this:
[source,cpp]
----
include::../example/tutorial.cpp[tag=query1]
----
The <<resultset>> is a forward range of the data queried from the database.
The first row of the result is already read.
[source,cpp]
----
include::../example/tutorial.cpp[tag=query2]
----
NOTE: The `resultset` has `begin()`/`end()` member functions, so that it can be used in a ranged for loop.
The <<row>> type is a range of <<field>>s,
which has all the information for the field requested.
In many use-cases this is however superfluous because the structure of the table is known.
To make this easy, you can call query with a type parameter (`query<T>`) which will yield a
`static_resultset<T>`. `T` can either be a tuple of a struct.
A tuple will assign the query results by order.
[source,cpp]
----
include::../example/tutorial.cpp[tag=query3]
----
Using a struct will assign the query results by name, i.e. it will reorder results accordingly.
If you're using a standard lower than C++20, the `struct` needs to be https://www.boost.org/doc/libs/develop/libs/describe/doc/html/describe.html#ref_boost_describe_struct::[described].
[source,cpp]
----
include::../example/tutorial.cpp[tag=query_t]
include::../example/tutorial.cpp[tag=query4]
----
The types allowed in the `static_resultset` are:
- `std::int64_t`
- `double`
- <<blob, 'blob'>>, <<blob_view, `blob_view`>>
- <<string>>, <<string_view>>
- `std::optional<T>`/`boost::optional<T>` with any of the types above.
WARNING: `blob_view` and `string_view` have a limited lifetime. They might be invalidated when the next row is read!
By default, the `static_result` does not check types, following https://www.sqlite.org/datatype3.html[sqlite's general dynamic type system].
You can eat type checks, by using strict mode:
[source,cpp]
----
include::../example/tutorial.cpp[tag=query_strict]
----
== Statements
To avoid https://xkcd.com/327/[sql injection], querys and execution should not be build dynamically, but be done with parameters.
This is done by creating a statement and then executing it with parameters.
[source,cpp]
----
include::../example/tutorial.cpp[tag=statement_insert]
----
The syntax in the sqlite provides multiple versions.
`?` is a positional parameter, that can have an explicit index at the end (e.g. `?3`) as in the above example.
`:`, `@`, `$` are named parameters, e.g. `$age`. Named parameters can make queries more readable and provide more checks.
NOTE: See the https://www.sqlite.org/c3ref/bind_blob.html[sqlite syntax] for more details.
A prepared statement can be used multiple times.
[source,cpp]
----
include::../example/tutorial.cpp[tag=statement_multi_insert]
----
WARNING: Calling `.execute` on an lvalue of `statement` will transfer ownership to the `resultset`,
while calling `.execute` on an rvalue will produce a `resultset` that points to the statement.
NOTE: The result of an `.execute` can also be turned into a static_resultset<T> by using `.execute<T>`.
|
