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
|
# zig-sqlite
This package is a thin wrapper around [sqlite](https://sqlite.org/index.html)'s C API.
## Requirements
* Linux
* the system and development package for sqlite
* `libsqlite3-dev` for Debian and derivatives
* `sqlite3-devel` for Fedora
## Features
* Preparing, executing statements
* comptime checked bind parameters
## Installation
Since there's no package manager for Zig yet, the recommended way is to use a git submodule:
$ git submodule add https://git.sr.ht/~vrischmann/zig-sqlite src/sqlite
Then add the following to your `build.zig` target(s):
exe.linkLibC();
exe.linkSystemLibrary("sqlite3");
exe.addPackage(.{ .name = "sqlite", .path = "src/sqlite/sqlite.zig" });
Now you should be able to import sqlite like this:
const sqlite = @import("sqlite");
## Usage
### Initialization
You must create and initialize an instance of `sqlite.Db`:
var db: sqlite.Db = undefined;
try db.init(allocator, .{ .mode = sqlite.Db.Mode{ .File = "/home/vincent/mydata.db" } });
The `init` method takes an allocator and an optional tuple which will used to configure sqlite.
Right now the only member used in that tuple is `mode` which defines if the sqlite database is in memory or uses a file.
### Preparing a statement
sqlite works exclusively by using prepared statements. The wrapper type is `sqlite.Statement`. Here is how you get one:
const query =
\\SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ?
;
var stmt = try db.prepare(query);
defer stmt.deinit();
The `Db.prepare` method takes a `comptime` query string.
### Executing a statement
For queries which do not return data (`INSERT`, `UPDATE`) you can use the `exec` method:
const query =
\\UPDATE foo SET salary = ? WHERE id = ?
;
var stmt = try db.prepare(query);
defer stmt.deinit();
try stmt.exec({
.salary = 20000,
.id = 40,
});
See the section "Bind parameters and resultset rows" for more information on the types mapping rules.
### Reading data
For queries which do return data you can use the `all` method:
const query =
\\SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ?
;
var stmt = try db.prepare(query);
defer stmt.deinit();
const rows = try stmt.all(
struct {
id: usize,
name: []const u8,
age: u16,
salary: u32,
},
.{ .allocator = allocator },
.{ .age1 = 20, .age2 = 40 },
);
for (rows) |row| {
std.log.debug("id: {} ; name: {}; age: {}; salary: {}", .{ row.id, row.name, row.age, row.salary });
}
The `all` method takes a type and an optional tuple.
The type represents a "row", it can be:
* a struct where each field maps to the corresponding column in the resultset (so field 0 must map to field 1 and so on).
* a single type, in that case the resultset must only return one column.
Not all types are allowed, see the section "Bind parameters and resultset rows" for more information on the types mapping rules.
### Bind parameters and resultset rows
Since sqlite doesn't have many [types](https://www.sqlite.org/datatype3.html) only a small number of Zig types are allowed in binding parameters and in resultset mapping types.
Here are the rules for bind parameters:
* any Zig `Int` or `ComptimeInt` is tread as a `INTEGER`.
* any Zig `Float` or `ComptimeFloat` is treated as a `REAL`.
* `[]const u8`, `[]u8` or any array of `u8` is treated as a `TEXT`.
* The custom `sqlite.Bytes` type is treated as a `TEXT` or `BLOB`.
Here are the rules for resultset rows:
* `INTEGER` can be read into any Zig `Int` provided the data fits.
* `REAL` can be read into any Zig `Float` provided the data fits.
* `TEXT` can be read into a `[]const u8` or `[]u8`.
* `TEXT` can be read into any array of `u8` provided the data fits.
* `BLOB` follows the same rules as `TEXT`.
### Comptime checked statements
Prepared statements contain _comptime_ metadata which is used to validate that every call to `exec`, `one` and `all` provides the appropriate number of bind parameters.
Right now there's no _type_ checking of bind parameters but it could probably be done.
|
