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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
= Virtual tables
[#vtables]
https://www.sqlite.org/vtab.html[A Virtual Table] is an object that is registered with a <<connection>>.
It looks like a regular table, but is actually invoking virtual methods internally.
This allows users to expose functionality from C++ to sqlite through a table interface.
NOTE: The implementation uses abstract base classes to make interfaces easy to implement.
You can disable the use of virtual by defining `BOOST_SQLITE_VIRTUAL` which will yield linker errors instead.
== Eponymous
And eponymous table is a virtual table the exists without needing to be created from sqlite.
Such a table can either expose global values or can be used to build https://www.sqlite.org/vtab.html#tabfunc2[table-valued functions].
=== Eponymous value table
The easiest example of a data structure to expose to sql, is a simple ordered map:
[source,cpp]
----
container::flat_map<std::string, std::string> data;
----
To create a virtual table it needs to have a cursor first. The cursor is what runs over the table, akin to an iterator.
[source,cpp]
----
include::../example/ordered_map.cpp[tag=cursor]
----
<1> Declare the value the cursor produces
<2> The data the cursor operates on
<3> Advance the cursor by one
<4> The module is declared without ROW id, see below
<5> Get the actual column value
<6> Tell sqlite when the cursor reached its end
The cursor above just runs over the ordered map, returning values.
However, the map is always ordered, so the cursor can optimize the built-in filter for sqlite.
That is, sqlite will filter values on its own, but it's more efficient to do that before returning them.
This can be done in the `filter` function, which can be written as follows:
[source,cpp]
----
include::../example/ordered_map.cpp[tag=filter]
----
<1> If the first idx is not null, it means the order is reversed (see best_index below)
<2> The equal constraint can be fulfilled by setting the range to enclose the value.
<3> A greater than/equal constraint can be fulfilled by moving the beginning of the range.
<4> A lesser than/equal constraint can be fulfilled by moving the end of the range.
NOTE: Constraints of the same can appear multiple times, so the filter must only narrow the range.
After the cursor, the actual table needs to be implemented:
[source,cpp]
----
include::../example/ordered_map.cpp[tag=table]
----
<1> Declare the table to not be read-only, which adds functions 4-6.
<2> The declaration is a literal that tells sqlite the shape of the table. It includes `WITHOUT ROWID` as `cursor.row_id()` mustn't be used.
<3> This function gets called when the table gets scanned, i.e. a cursor gets created.
<4> Delete value from the table, inherited from `modifiable`.
<5> Insert a value into the table, inherited from `modifiable`.
<6> Update a value in the table, inherited from `modifiable`.
Next, there also should be a `best_index` function. This function informs sqlite about the available
filter features so that sqlite can omit the operations.
[source,cpp]
----
include::../example/ordered_map.cpp[tag=best_index]
----
<1> The data being passed on as idxStr to `cursor.filter`.
<2> These are the constraints handled by filter, so only those get encoded.
<3> filter assumes idx != 0 means descending, i.e. inverted
<4> Tell sqlite the data is ordered already.
<5> Set the index information to be passed to filter.
With that all defined, the only thing left is to declare the module & create it.
[source,cpp]
----
include::../example/ordered_map.cpp[tag=module]
----
=== Function table
An eponymous function can be used as a function. For this example, a `query` vtable will be used to
parse the query part of a url and return it as a table:
[source,sqlite]
----
include::../example/url.sql[tag=query]
----
[cols="1,1,1"]
|===
|0|name|boost
|1|thingy|foo
|2|name|sqlite
|3|foo|
|===
To achieve this a cursor is needed:
[source,cpp]
----
include::../example/url.cpp[tag=query_cursor]
----
<1> Return the parts of the query view as columns
<2> Pick the input value here.
This is the module & table declaration;
[source,cpp]
----
include::../example/url.cpp[tag=query_boiler_plate]
----
<1> The hidden colum to use for the input value.
<2> Assert only equal is used for input data.
NOTE: See the https://www.sqlite.org/vtab.html#tabfunc2[sqlite documentation here].
== Non-eponymous Tables
A non-eponymous table is a table backed by an actual resource.
The module needs to provide a `create` function in the module,
in addition to the `connect` one.
The table is required to have a `destroy` function that removes the resource.
[source,cpp]
----
struct csv_module final : sqlite::vtab::module<csv_table>
{
// create the csv file
sqlite::result<table_type> create(sqlite::connection /*db*/,
int argc, const char * const argv[]);
// connect to an existing csv file.
sqlite::result<table_type> connect(sqlite::connection /*db*/,
int argc, const char * const argv[]);
};
----
The virtual table can be created with the special syntax:
[source,cpp]
----
CREATE VIRTUAL TABLE csv_example USING csv_file(./csv-example.csv, username, first_name, last_name);
----
When the above query is executed `csv_module.create()` will be executed with `"./csv-example.csv", "username", "first_name", "last_name".
If the database gets opened with a previously created virtual table,
`csv_module.connect()` will be called with the same parameters.
Executing `DROP TABLE csv_example` will call `destroy()` on the `csv_table`.
NOTE: The `destroy` function will be invoked when the virtual table gets dropped,
not when the connection gets closed.
|
