summaryrefslogtreecommitdiff
path: root/src/main.zig
diff options
context:
space:
mode:
Diffstat (limited to 'src/main.zig')
-rw-r--r--src/main.zig513
1 files changed, 0 insertions, 513 deletions
diff --git a/src/main.zig b/src/main.zig
deleted file mode 100644
index 88888cf..0000000
--- a/src/main.zig
+++ /dev/null
@@ -1,513 +0,0 @@
1const std = @import("std");
2const build_options = @import("build_options");
3const debug = std.debug;
4const mem = std.mem;
5const testing = std.testing;
6
7const c = @cImport({
8 @cInclude("sqlite3.h");
9});
10
11const logger = std.log.scoped(.sqlite);
12
13/// Db is a wrapper around a SQLite database, providing high-level functions for executing queries.
14/// A Db can be opened with a file database or a in-memory database:
15///
16/// // File database
17/// var db: sqlite.Db = undefined;
18/// try db.init(allocator, .{ .mode = { .File = "/tmp/data.db" } });
19///
20/// // In memory database
21/// var db: sqlite.Db = undefined;
22/// try db.init(allocator, .{ .mode = { .Memory = {} } });
23///
24pub const Db = struct {
25 const Self = @This();
26
27 allocator: *mem.Allocator,
28 db: *c.sqlite3,
29
30 /// Mode determines how the database will be opened.
31 pub const Mode = union(enum) {
32 File: []const u8,
33 Memory,
34 };
35
36 /// init creates a database with the provided `mode`.
37 pub fn init(self: *Self, allocator: *mem.Allocator, options: anytype) !void {
38 self.allocator = allocator;
39
40 const mode = if (@hasField(@TypeOf(options), "mode")) options.mode else .Memory;
41
42 switch (mode) {
43 .File => |path| {
44 logger.info("opening {}", .{path});
45
46 // Need a null-terminated string here.
47 const pathZ = try allocator.dupeZ(u8, path);
48 defer allocator.free(pathZ);
49
50 var db: ?*c.sqlite3 = undefined;
51 const result = c.sqlite3_open_v2(
52 pathZ,
53 &db,
54 c.SQLITE_OPEN_READWRITE | c.SQLITE_OPEN_CREATE,
55 null,
56 );
57 if (result != c.SQLITE_OK or db == null) {
58 logger.warn("unable to open database, result: {}", .{result});
59 return error.CannotOpenDatabase;
60 }
61
62 self.db = db.?;
63 },
64 .Memory => {
65 logger.info("opening in memory", .{});
66
67 var db: ?*c.sqlite3 = undefined;
68 const result = c.sqlite3_open_v2(
69 ":memory:",
70 &db,
71 c.SQLITE_OPEN_READWRITE | c.SQLITE_OPEN_MEMORY,
72 null,
73 );
74 if (result != c.SQLITE_OK or db == null) {
75 logger.warn("unable to open database, result: {}", .{result});
76 return error.CannotOpenDatabase;
77 }
78
79 self.db = db.?;
80 },
81 }
82 }
83
84 /// deinit closes the database.
85 pub fn deinit(self: *Self) void {
86 _ = c.sqlite3_close(self.db);
87 }
88
89 /// exec is a convenience function which prepares a statement and executes it directly.
90 pub fn exec(self: *Self, comptime query: []const u8, values: anytype) !void {
91 var stmt = try self.prepare(query, values);
92 defer stmt.deinit();
93 try stmt.exec();
94 }
95
96 /// prepare prepares a statement for the `query` provided.
97 ///
98 /// The query is analysed at comptime to search for bind markers.
99 /// prepare enforces having as much fields in the `values` tuple as there are bind markers.
100 ///
101 /// Example usage:
102 ///
103 /// var stmt = try db.prepare("INSERT INTO foo(id, name) VALUES(?, ?)", .{
104 /// .id = 3540,
105 /// .name = "Eminem",
106 /// });
107 /// defer stmt.deinit();
108 ///
109 /// Note that the name of the fields in the tuple are irrelevant, only the types are.
110 pub fn prepare(self: *Self, comptime query: []const u8, values: anytype) !Statement {
111 return Statement.prepare(self, 0, query, values);
112 }
113
114 /// rowsAffected returns the number of rows affected by the last statement executed.
115 pub fn rowsAffected(self: *Self) usize {
116 return @intCast(usize, c.sqlite3_changes(self.db));
117 }
118};
119
120/// Statement is a wrapper around a SQLite statement, providing high-level functions to execute
121/// a statement and retrieve rows for SELECT queries.
122///
123/// The exec function can be used to execute a query which does not return rows:
124///
125/// var stmt = try db.prepare("UPDATE foo SET id = ? WHERE name = ?", .{
126/// .id = 200,
127/// .name = "José",
128/// });
129/// defer stmt.deinit();
130///
131/// The one function can be used to select a single row:
132///
133/// var stmt = try db.prepare("SELECT name FROM foo WHERE id = ?", .{ .id = 200 });
134/// defer stmt.deinit();
135///
136/// const name = try stmt.one([]const u8, .{});
137///
138/// The all function can be used to select all rows:
139///
140/// var stmt = try db.prepare("SELECT id, name FROM foo", .{});
141/// defer stmt.deinit();
142///
143/// const Row = struct {
144/// id: usize,
145/// name: []const u8,
146/// };
147/// const rows = try stmt.all(Row, .{ .allocator = allocator });
148///
149/// Look at aach function for more complete documentation.
150///
151pub const Statement = struct {
152 const Self = @This();
153
154 stmt: *c.sqlite3_stmt,
155
156 fn prepare(db: *Db, flags: c_uint, comptime query: []const u8, values: anytype) !Self {
157 const StructType = @typeInfo(@TypeOf(values)).Struct;
158 comptime {
159 const bind_parameter_count = std.mem.count(u8, query, "?");
160 if (bind_parameter_count != StructType.fields.len) {
161 @compileError("bind parameter count != number of fields in tuple/struct");
162 }
163 }
164
165 // prepare
166
167 var stmt = blk: {
168 var tmp: ?*c.sqlite3_stmt = undefined;
169 const result = c.sqlite3_prepare_v3(
170 db.db,
171 query.ptr,
172 @intCast(c_int, query.len),
173 flags,
174 &tmp,
175 null,
176 );
177 if (result != c.SQLITE_OK) {
178 logger.warn("unable to prepare statement, result: {}", .{result});
179 return error.CannotPrepareStatement;
180 }
181 break :blk tmp.?;
182 };
183
184 // Bind
185
186 inline for (StructType.fields) |struct_field, _i| {
187 const i = @as(usize, _i);
188 const field_type_info = @typeInfo(struct_field.field_type);
189 const field_value = @field(values, struct_field.name);
190 const column = i + 1;
191
192 switch (struct_field.field_type) {
193 []const u8, []u8 => {
194 _ = c.sqlite3_bind_text(stmt, column, field_value.ptr, @intCast(c_int, field_value.len), null);
195 },
196 else => switch (field_type_info) {
197 .Int, .ComptimeInt => _ = c.sqlite3_bind_int64(stmt, column, @intCast(c_longlong, field_value)),
198 .Float, .ComptimeFloat => _ = c.sqlite3_bind_double(stmt, column, field_value),
199 .Array => |arr| {
200 switch (arr.child) {
201 u8 => {
202 const data: []const u8 = field_value[0..field_value.len];
203
204 _ = c.sqlite3_bind_text(stmt, column, data.ptr, @intCast(c_int, data.len), null);
205 },
206 else => @compileError("cannot populate field " ++ field.name ++ " of type array of " ++ @typeName(arr.child)),
207 }
208 },
209 else => @compileError("cannot bind field " ++ struct_field.name ++ " of type " ++ @typeName(struct_field.field_type)),
210 },
211 }
212 }
213
214 return Self{
215 .stmt = stmt,
216 };
217 }
218
219 pub fn deinit(self: *Self) void {
220 const result = c.sqlite3_finalize(self.stmt);
221 if (result != c.SQLITE_OK) {
222 logger.err("unable to finalize prepared statement, result: {}", .{result});
223 }
224 }
225
226 pub fn exec(self: *Self) !void {
227 const result = c.sqlite3_step(self.stmt);
228 switch (result) {
229 c.SQLITE_DONE => {},
230 c.SQLITE_BUSY => return error.SQLiteBusy,
231 else => std.debug.panic("invalid result {}", .{result}),
232 }
233 }
234
235 /// one reads a single row from the result set of this statement.
236 ///
237 /// The data in the row is used to populate a value of the type `Type`.
238 /// This means that `Type` must have as many fields as is returned in the query
239 /// executed by this statement.
240 /// This also means that the type of each field must be compatible with the SQLite type.
241 ///
242 /// Here is an example of how to use an anonymous struct type:
243 ///
244 /// const row = try stmt.one(
245 /// struct {
246 /// id: usize,
247 /// name: []const u8,
248 /// age: usize,
249 /// },
250 /// .{ .allocator = allocator },
251 /// );
252 ///
253 /// The `options` tuple is used to provide additional state in some cases, for example
254 /// an allocator used to read text and blobs.
255 ///
256 pub fn one(self: *Self, comptime Type: type, options: anytype) !?Type {
257 const TypeInfo = @typeInfo(Type);
258
259 var result = c.sqlite3_step(self.stmt);
260
261 switch (TypeInfo) {
262 .Int => return switch (result) {
263 c.SQLITE_ROW => try self.readInt(Type, options),
264 c.SQLITE_DONE => null,
265 else => std.debug.panic("invalid result {}", .{result}),
266 },
267 .Struct => return switch (result) {
268 c.SQLITE_ROW => try self.readStruct(Type, options),
269 c.SQLITE_DONE => null,
270 else => std.debug.panic("invalid result {}", .{result}),
271 },
272 else => @compileError("cannot read into type " ++ @typeName(Type)),
273 }
274 }
275
276 /// all reads all rows from the result set of this statement.
277 ///
278 /// The data in each row is used to populate a value of the type `Type`.
279 /// This means that `Type` must have as many fields as is returned in the query
280 /// executed by this statement.
281 /// This also means that the type of each field must be compatible with the SQLite type.
282 ///
283 /// Here is an example of how to use an anonymous struct type:
284 ///
285 /// const rows = try stmt.all(
286 /// struct {
287 /// id: usize,
288 /// name: []const u8,
289 /// age: usize,
290 /// },
291 /// .{ .allocator = allocator },
292 /// );
293 ///
294 /// The `options` tuple is used to provide additional state in some cases.
295 /// Note that for this function the allocator is mandatory.
296 ///
297 pub fn all(self: *Self, comptime Type: type, options: anytype) ![]Type {
298 const TypeInfo = @typeInfo(Type);
299
300 var rows = std.ArrayList(Type).init(options.allocator);
301
302 var result = c.sqlite3_step(self.stmt);
303 while (result == c.SQLITE_ROW) : (result = c.sqlite3_step(self.stmt)) {
304 const columns = c.sqlite3_column_count(self.stmt);
305
306 var value = switch (TypeInfo) {
307 .Int => blk: {
308 debug.assert(columns == 1);
309 break :blk try self.readInt(Type, options);
310 },
311 .Struct => blk: {
312 std.debug.assert(columns == @typeInfo(Type).Struct.fields.len);
313 break :blk try self.readStruct(Type, options);
314 },
315 else => @compileError("cannot read into type " ++ @typeName(Type)),
316 };
317
318 try rows.append(value);
319 }
320
321 if (result != c.SQLITE_DONE) {
322 logger.err("unable to iterate, result: {}", .{result});
323 return error.SQLiteStepError;
324 }
325
326 return rows.span();
327 }
328
329 fn readInt(self: *Self, comptime Type: type, options: anytype) !Type {
330 const n = c.sqlite3_column_int64(self.stmt, 0);
331 return @intCast(Type, n);
332 }
333
334 fn readStruct(self: *Self, comptime Type: type, options: anytype) !Type {
335 var value: Type = undefined;
336
337 inline for (@typeInfo(Type).Struct.fields) |field, _i| {
338 const i = @as(usize, _i);
339 const field_type_info = @typeInfo(field.field_type);
340
341 switch (field.field_type) {
342 []const u8, []u8 => {
343 const data = c.sqlite3_column_blob(self.stmt, i);
344 if (data == null) {
345 @field(value, field.name) = "";
346 } else {
347 const size = @intCast(usize, c.sqlite3_column_bytes(self.stmt, i));
348
349 var tmp = try options.allocator.alloc(u8, size);
350 mem.copy(u8, tmp, @ptrCast([*c]const u8, data)[0..size]);
351
352 @field(value, field.name) = tmp;
353 }
354 },
355 else => switch (field_type_info) {
356 .Int => {
357 const n = c.sqlite3_column_int64(self.stmt, i);
358 @field(value, field.name) = @intCast(field.field_type, n);
359 },
360 .Float => {
361 const f = c.sqlite3_column_double(self.stmt, i);
362 @field(value, field.name) = f;
363 },
364 .Void => {
365 @field(value, field.name) = {};
366 },
367 .Array => |arr| {
368 switch (arr.child) {
369 u8 => {
370 const data = c.sqlite3_column_blob(self.stmt, i);
371 const size = @intCast(usize, c.sqlite3_column_bytes(self.stmt, i));
372
373 if (size > @as(usize, arr.len)) return error.ArrayTooSmall;
374
375 mem.copy(u8, @field(value, field.name)[0..], @ptrCast([*c]const u8, data)[0..size]);
376 },
377 else => @compileError("cannot populate field " ++ field.name ++ " of type array of " ++ @typeName(arr.child)),
378 }
379 },
380 else => @compileError("cannot populate field " ++ field.name ++ " of type " ++ @typeName(field.field_type)),
381 },
382 }
383 }
384
385 return value;
386 }
387};
388
389test "sqlite: statement exec" {
390 var arena = std.heap.ArenaAllocator.init(testing.allocator);
391 defer arena.deinit();
392 var allocator = &arena.allocator;
393
394 var db: Db = undefined;
395 try db.init(testing.allocator, .{ .mode = dbMode() });
396
397 // Create the tables
398
399 comptime const all_ddl = &[_][]const u8{
400 \\CREATE TABLE user(
401 \\ id integer PRIMARY KEY,
402 \\ name text,
403 \\ age integer
404 \\)
405 ,
406 \\CREATE TABLE article(
407 \\ id integer PRIMARY KEY,
408 \\ author_id integer,
409 \\ data text,
410 \\ FOREIGN KEY(author_id) REFERENCES user(id)
411 \\)
412 };
413 inline for (all_ddl) |ddl| {
414 var stmt = try db.prepare(ddl, .{});
415 defer stmt.deinit();
416 try stmt.exec();
417 }
418
419 // Add data
420
421 const User = struct {
422 id: usize,
423 name: []const u8,
424 age: usize,
425 };
426
427 const users = &[_]User{
428 .{ .id = 20, .name = "Vincent", .age = 33 },
429 .{ .id = 40, .name = "Julien", .age = 35 },
430 .{ .id = 60, .name = "José", .age = 40 },
431 };
432
433 for (users) |user| {
434 try db.exec("INSERT INTO user(id, name, age) VALUES(?, ?, ?)", user);
435
436 const rows_inserted = db.rowsAffected();
437 testing.expectEqual(@as(usize, 1), rows_inserted);
438 }
439
440 // Read a single user
441
442 {
443 var stmt = try db.prepare("SELECT id, name, age FROM user WHERE id = ?", .{ .id = 20 });
444 defer stmt.deinit();
445
446 var rows = try stmt.all(User, .{ .allocator = allocator });
447 for (rows) |row| {
448 testing.expectEqual(users[0].id, row.id);
449 testing.expectEqualStrings(users[0].name, row.name);
450 testing.expectEqual(users[0].age, row.age);
451 }
452 }
453
454 // Read all users
455
456 {
457 var stmt = try db.prepare("SELECT id, name, age FROM user", .{});
458 defer stmt.deinit();
459
460 var rows = try stmt.all(User, .{ .allocator = allocator });
461 testing.expectEqual(@as(usize, 3), rows.len);
462 for (rows) |row, i| {
463 const exp = users[i];
464 testing.expectEqual(exp.id, row.id);
465 testing.expectEqualStrings(exp.name, row.name);
466 testing.expectEqual(exp.age, row.age);
467 }
468 }
469
470 // Test with anonymous structs
471
472 {
473 var stmt = try db.prepare("SELECT id, name, age FROM user WHERE id = ?", .{ .id = 20 });
474 defer stmt.deinit();
475
476 var row = try stmt.one(
477 struct {
478 id: usize,
479 name: []const u8,
480 age: usize,
481 },
482 .{ .allocator = allocator },
483 );
484 testing.expect(row != null);
485
486 const exp = users[0];
487 testing.expectEqual(exp.id, row.?.id);
488 testing.expectEqualStrings(exp.name, row.?.name);
489 testing.expectEqual(exp.age, row.?.age);
490 }
491
492 // Test with a single integer
493
494 {
495 var stmt = try db.prepare("SELECT age FROM user WHERE id = ?", .{ .id = 20 });
496 defer stmt.deinit();
497
498 var age = try stmt.one(usize, .{});
499 testing.expect(age != null);
500
501 testing.expectEqual(@as(usize, 33), age.?);
502 }
503}
504
505fn dbMode() Db.Mode {
506 return if (build_options.is_ci) blk: {
507 break :blk .{ .Memory = {} };
508 } else blk: {
509 const path = "/tmp/zig-sqlite.db";
510 std.fs.cwd().deleteFile(path) catch {};
511 break :blk .{ .File = path };
512 };
513}
HIC SVNT DRACONES