1const std = @import("std");
2const string = []const u8;
3const tracer = @import("tracer");
4const extras = @import("extras");
5const builtin = @import("builtin");
6
7const Driver = @This();
8const SQLITE_STATIC: *allowzero anyopaque = @ptrFromInt(0);
9const SQLITE_TRANSIENT: *allowzero anyopaque = @ptrFromInt(std.math.maxInt(usize));
10
11pub const Error = s.Error || error{Overflow};
12
13db: *c.sqlite3,
14
15pub fn connect(allocator: std.mem.Allocator, path: [:0]const u8) !Driver {
16 var driver: Driver = try .connect_only(allocator, path);
17 errdefer driver.close();
18 _ = try driver.first(allocator, void, "PRAGMA journal_mode = WAL", .{});
19 _ = try driver.first(allocator, void, "PRAGMA busy_timeout = 5000", .{});
20 _ = try driver.first(allocator, void, "PRAGMA synchronous = NORMAL", .{});
21 _ = try driver.first(allocator, void, "PRAGMA cache_size = 1000000000", .{});
22 _ = try driver.first(allocator, void, "PRAGMA foreign_keys = true", .{});
23 _ = try driver.first(allocator, void, "PRAGMA temp_store = memory", .{});
24 _ = try driver.first(allocator, void, "PRAGMA mmap_size = 1073741824", .{});
25 _ = try driver.first(allocator, void, "PRAGMA auto_vacuum = INCREMENTAL", .{});
26 return driver;
27}
28pub fn connect_only(allocator: std.mem.Allocator, path: [:0]const u8) !Driver {
29 std.log.scoped(.zorm).info("connecting to {s} @ {s}", .{ "sqlite3", path });
30 _ = allocator;
31 var db: ?*c.sqlite3 = null;
32 var flags: c_int = 0;
33 flags |= c.SQLITE_OPEN_READWRITE;
34 flags |= c.SQLITE_OPEN_CREATE;
35 flags |= c.SQLITE_OPEN_FULLMUTEX;
36 s.assert(c.sqlite3_open_v2(path, &db, flags, null));
37 std.debug.assert(c.sqlite3_threadsafe() > 0);
38 return .{ .db = db.? };
39}
40
41pub fn close(self: Driver) void {
42 s.assert(c.sqlite3_close_v2(self.db));
43}
44
45pub fn collect(self: Driver, alloc: std.mem.Allocator, comptime T: type, comptime query: string, args: anytype) ![]T {
46 const t = tracer.trace(@src(), " {s}", .{query});
47 defer t.end();
48
49 var list = std.array_list.Managed(T).init(alloc);
50 errdefer list.deinit();
51 var stmt: Statement = try .prepare(self, query);
52 defer stmt.finalize();
53 try stmt.bindArgs(alloc, args);
54 const iter = stmt.iterate();
55 errdefer iter.reset();
56 while (try iter.step(alloc, T)) |row| try list.append(row);
57 return list.toOwnedSlice();
58}
59
60pub fn exec(self: Driver, alloc: std.mem.Allocator, comptime query: string, args: anytype) !void {
61 const t = tracer.trace(@src(), " {s}", .{query});
62 defer t.end();
63
64 var stmt: Statement = try .prepare(self, query);
65 defer stmt.finalize();
66 try stmt.bindArgs(alloc, args);
67 try stmt.exec(alloc);
68}
69
70pub fn first(self: Driver, alloc: std.mem.Allocator, comptime T: type, comptime query: string, args: anytype) !?T {
71 const t = tracer.trace(@src(), " {s}", .{query});
72 defer t.end();
73
74 var stmt: Statement = try .prepare(self, query);
75 defer stmt.finalize();
76 try stmt.bindArgs(alloc, args);
77 const iter = stmt.iterate();
78 errdefer iter.reset();
79 return iter.step(alloc, T);
80}
81
82pub fn doesTableExist(self: Driver, alloc: std.mem.Allocator, name: string) !bool {
83 const t = tracer.trace(@src(), " {s}", .{name});
84 defer t.end();
85
86 for (try self.collect(alloc, string, "select name from sqlite_master where type = ? AND name = ?", .{ .type = "table", .name = name })) |item| {
87 if (std.mem.eql(u8, item, name)) {
88 return true;
89 }
90 }
91 return false;
92}
93
94pub fn hasColumnWithName(self: Driver, alloc: std.mem.Allocator, comptime table: string, comptime column: string) !bool {
95 const t = tracer.trace(@src(), " {s}.{s}", .{ table, column });
96 defer t.end();
97
98 for (try pragma.table_info(self, alloc, table)) |item| {
99 if (std.mem.eql(u8, item.name, column)) {
100 return true;
101 }
102 }
103 return false;
104}
105
106pub fn createTable(self: Driver, alloc: std.mem.Allocator, comptime name: []const u8, comptime pk_name: []const u8, pk_type: type) !void {
107 const t = tracer.trace(@src(), " {s} ({s})", .{ name, pk_name });
108 defer t.end();
109 try self.exec(alloc, comptime std.fmt.comptimePrint("create table {s}({s} {s} primary key not null) strict", .{ name, pk_name, nameForType2(pk_type) }), .{});
110}
111
112pub fn addColumn(self: Driver, alloc: std.mem.Allocator, comptime table_name: []const u8, comptime col_name: []const u8, T: type) !void {
113 const t = tracer.trace(@src(), " {s}.{s}", .{ table_name, col_name });
114 defer t.end();
115 try self.exec(alloc, comptime std.fmt.comptimePrint("alter table {s} add \"{s}\" {s}", .{ table_name, col_name, nameForType(T) }), .{});
116}
117
118pub fn addColumnForeign(self: Driver, alloc: std.mem.Allocator, comptime table_name: []const u8, comptime col_name: []const u8, T: type, comptime table_name2: []const u8, comptime col_name2: []const u8) !void {
119 const t = tracer.trace(@src(), " {s}.{s}", .{ table_name, col_name });
120 defer t.end();
121 try self.exec(alloc, comptime std.fmt.comptimePrint("alter table {s} add \"{s}\" {s} references \"{s}\" (\"{s}\")", .{ table_name, col_name, nameForType(T), table_name2, col_name2 }), .{});
122}
123
124pub fn nameForType(T: type) []const u8 {
125 if (@typeInfo(T) == .optional) {
126 return nameForType2(@typeInfo(T).optional.child);
127 }
128 return nameForType2(T) ++ " not null default (" ++ defaultForType(T) ++ ")";
129}
130
131pub fn nameForType2(T: type) []const u8 {
132 const tinfo = @typeInfo(T);
133
134 if (comptime extras.isZigString(T)) {
135 return "text";
136 }
137 if (tinfo == .@"struct") {
138 const info = tinfo.@"struct";
139 if (@hasDecl(T, "BaseType") and T.BaseType != []const u8) return T.baseTypeName;
140 if (@hasDecl(T, "BaseType")) return nameForType2(T.BaseType);
141 if (info.layout == .@"packed") return nameForType2(info.backing_integer.?);
142 return nameForType2(T.BaseType);
143 }
144 if (tinfo == .@"enum") {
145 if (@hasDecl(T, "BaseType") and T.BaseType != []const u8) return T.baseTypeName;
146 return nameForType2(T.BaseType);
147 }
148 if (tinfo == .int or tinfo == .bool) {
149 return "integer";
150 }
151 if (comptime extras.isArrayOf(u8)(T)) {
152 return "blob";
153 }
154 @compileError(@typeName(T)); // TODO
155}
156
157pub fn defaultForType(T: type) []const u8 {
158 const info = @typeInfo(T);
159 if (info == .bool) {
160 return "false";
161 }
162 if (info == .@"struct") {
163 const sinfo = info.@"struct";
164 if (@hasDecl(T, "BaseType")) return defaultForType(T.BaseType);
165 if (sinfo.layout == .@"packed") return defaultForType(sinfo.backing_integer.?);
166 }
167 if (comptime extras.isZigString(T)) {
168 return "''";
169 }
170 if (info == .int) {
171 return "0";
172 }
173 if (info == .@"enum") {
174 return defaultForType(T.BaseType);
175 }
176 @compileError(@typeName(T)); // TODO
177}
178
179pub const Pragma = struct {
180 pub const TableInfo = struct {
181 cid: u16,
182 name: string,
183 type: string,
184 notnull: bool,
185 dflt_value: string,
186 pk: bool,
187 };
188};
189
190pub const pragma = struct {
191 pub fn table_info(self: Driver, alloc: std.mem.Allocator, comptime name: string) ![]const Pragma.TableInfo {
192 const t = tracer.trace(@src(), " {s}", .{name});
193 defer t.end();
194
195 return try self.collect(alloc, Pragma.TableInfo, "pragma table_info(" ++ name ++ ")", .{});
196 }
197};
198
199pub const c = @cImport({
200 @cInclude("sqlite3.h");
201});
202
203pub const s = struct {
204 const Error = error{
205 Unexpected,
206 SQLITE_ERROR,
207 SQLITE_INTERNAL,
208 SQLITE_PERM,
209 SQLITE_ABORT,
210 SQLITE_BUSY,
211 SQLITE_LOCKED,
212 OutOfMemory,
213 SQLITE_READONLY,
214 SQLITE_INTERRUPT,
215 SQLITE_IOERR,
216 SQLITE_CORRUPT,
217 SQLITE_NOTFOUND,
218 SQLITE_FULL,
219 SQLITE_CANTOPEN,
220 SQLITE_PROTOCOL,
221 SQLITE_EMPTY,
222 SQLITE_SCHEMA,
223 SQLITE_TOOBIG,
224 SQLITE_CONSTRAINT,
225 SQLITE_MISMATCH,
226 SQLITE_MISUSE,
227 SQLITE_NOLFS,
228 SQLITE_AUTH,
229 SQLITE_FORMAT,
230 SQLITE_RANGE,
231 SQLITE_NOTADB,
232 SQLITE_NOTICE,
233 SQLITE_WARNING,
234 };
235 pub fn rc2e(code: c_int) s.Error {
236 if (code == c.SQLITE_ERROR) return error.SQLITE_ERROR;
237 if (code == c.SQLITE_INTERNAL) return error.SQLITE_INTERNAL;
238 if (code == c.SQLITE_PERM) return error.SQLITE_PERM;
239 if (code == c.SQLITE_ABORT) return error.SQLITE_ABORT;
240 if (code == c.SQLITE_BUSY) return error.SQLITE_BUSY;
241 if (code == c.SQLITE_LOCKED) return error.SQLITE_LOCKED;
242 if (code == c.SQLITE_NOMEM) return error.OutOfMemory;
243 if (code == c.SQLITE_READONLY) return error.SQLITE_READONLY;
244 if (code == c.SQLITE_INTERRUPT) return error.SQLITE_INTERRUPT;
245 if (code == c.SQLITE_IOERR) return error.SQLITE_IOERR;
246 if (code == c.SQLITE_CORRUPT) return error.SQLITE_CORRUPT;
247 if (code == c.SQLITE_NOTFOUND) return error.SQLITE_NOTFOUND;
248 if (code == c.SQLITE_FULL) return error.SQLITE_FULL;
249 if (code == c.SQLITE_CANTOPEN) return error.SQLITE_CANTOPEN;
250 if (code == c.SQLITE_PROTOCOL) return error.SQLITE_PROTOCOL;
251 if (code == c.SQLITE_EMPTY) return error.SQLITE_EMPTY;
252 if (code == c.SQLITE_SCHEMA) return error.SQLITE_SCHEMA;
253 if (code == c.SQLITE_TOOBIG) return error.SQLITE_TOOBIG;
254 if (code == c.SQLITE_CONSTRAINT) return error.SQLITE_CONSTRAINT;
255 if (code == c.SQLITE_MISMATCH) return error.SQLITE_MISMATCH;
256 if (code == c.SQLITE_MISUSE) return error.SQLITE_MISUSE;
257 if (code == c.SQLITE_NOLFS) return error.SQLITE_NOLFS;
258 if (code == c.SQLITE_AUTH) return error.SQLITE_AUTH;
259 if (code == c.SQLITE_FORMAT) return error.SQLITE_FORMAT;
260 if (code == c.SQLITE_RANGE) return error.SQLITE_RANGE;
261 if (code == c.SQLITE_NOTADB) return error.SQLITE_NOTADB;
262 if (code == c.SQLITE_NOTICE) return error.SQLITE_NOTICE;
263 if (code == c.SQLITE_WARNING) return error.SQLITE_WARNING;
264 return error.Unexpected;
265 }
266 pub fn rc2p(code: c_int) s.Error {
267 if (builtin.mode == .Debug) @panic(std.mem.sliceTo(c.sqlite3_errstr(code), 0));
268 return rc2e(code);
269 }
270 pub fn assert(code: c_int) void {
271 if (code == c.SQLITE_OK) return;
272 @panic(std.mem.sliceTo(c.sqlite3_errstr(code), 0));
273 }
274 pub fn please(code: c_int) !void {
275 if (code == c.SQLITE_OK) return;
276 return rc2p(code);
277 }
278 pub fn rc2p_d(db: *c.sqlite3, code: c_int) s.Error {
279 if (builtin.mode == .Debug) @panic(std.mem.sliceTo(c.sqlite3_errmsg(db), 0));
280 return rc2e(code);
281 }
282 pub fn assert_d(db: *c.sqlite3, code: c_int) void {
283 if (code == c.SQLITE_OK) return;
284 @panic(std.mem.sliceTo(c.sqlite3_errmsg(db), 0));
285 }
286 pub fn please_d(db: *c.sqlite3, code: c_int) !void {
287 if (code == c.SQLITE_OK) return;
288 return rc2p_d(db, code);
289 }
290};
291
292pub const Statement = struct {
293 db: *c.sqlite3,
294 stmt: *c.sqlite3_stmt,
295
296 pub fn prepare(driver: Driver, query: []const u8) !Statement {
297 var stmt: ?*c.sqlite3_stmt = null;
298 var flags: c_uint = 0;
299 _ = &flags;
300 try s.please_d(driver.db, c.sqlite3_prepare_v3(driver.db, query.ptr, @intCast(query.len), flags, &stmt, null));
301 return .{ .db = driver.db, .stmt = stmt.? };
302 }
303
304 pub fn finalize(stmt: Statement) void {
305 s.assert_d(stmt.db, c.sqlite3_finalize(stmt.stmt));
306 }
307
308 pub fn bindArgs(stmt: Statement, allocator: std.mem.Allocator, args: anytype) !void {
309 if (comptime extras.isSlice(@TypeOf(args))) {
310 for (args, 0..) |a, i| {
311 const A = @TypeOf(a);
312 try bindType(stmt, allocator, i + 1, A, a);
313 }
314 return;
315 }
316 inline for (@typeInfo(@TypeOf(args)).@"struct".fields, 0..) |f, i| {
317 try bindType(stmt, allocator, i + 1, f.type, @field(args, f.name));
318 }
319 }
320
321 fn bindType(stmt: Statement, allocator: std.mem.Allocator, idx: usize, T: type, value: T) !void {
322 if (comptime extras.isZigString(T)) {
323 return bindText(stmt, idx, value, SQLITE_STATIC);
324 }
325 if (comptime extras.isArrayOf(u8)(T)) {
326 return bindBlob(stmt, idx, &value, SQLITE_TRANSIENT);
327 }
328 switch (@typeInfo(T)) {
329 .@"struct" => |info| {
330 if (@hasDecl(T, "BaseType")) return bindBaseType(stmt, allocator, idx, T, value);
331 if (info.layout == .@"packed") return bindType(stmt, allocator, idx, info.backing_integer.?, @bitCast(value));
332 return bindBaseType(stmt, allocator, idx, T, value);
333 },
334 .int => |info| {
335 comptime std.debug.assert(info.bits <= 64);
336 if (value > std.math.maxInt(c.sqlite_int64)) return error.Overflow;
337 if (value < std.math.minInt(c.sqlite_int64)) return error.Overflow;
338 return s.please_d(stmt.db, c.sqlite3_bind_int64(stmt.stmt, @intCast(idx), @intCast(value)));
339 },
340 .optional => |info| {
341 if (value == null) return s.please(c.sqlite3_bind_null(stmt.stmt, @intCast(idx)));
342 return bindType(stmt, allocator, idx, info.child, value.?);
343 },
344 .bool => {
345 return bindType(stmt, allocator, idx, u1, @intFromBool(value));
346 },
347 .@"enum" => {
348 if (T.BaseType == []const u8 and !@hasDecl(T, "bindField")) {
349 return bindType(stmt, allocator, idx, []const u8, @tagName(value));
350 }
351 return bindBaseType(stmt, allocator, idx, T, value);
352 },
353 .@"union" => {
354 switch (value) {
355 inline else => |val| return bindType(stmt, allocator, idx, @TypeOf(val), val),
356 }
357 },
358 else => @compileError(@typeName(T)),
359 }
360 }
361
362 fn bindBaseType(stmt: Statement, allocator: std.mem.Allocator, idx: usize, T: type, value: T) !void {
363 const bind_fn = T.bindField;
364 const info = @typeInfo(@TypeOf(bind_fn)).@"fn";
365 switch (info.params.len) {
366 1 => {
367 const base = try value.bindField();
368 if (T.BaseType == string and comptime extras.isArrayOf(u8)(@TypeOf(base))) return bindText(stmt, idx, &base, SQLITE_TRANSIENT);
369 return bindType(stmt, allocator, idx, @TypeOf(base), base);
370 },
371 2 => {
372 const base = try value.bindField(allocator);
373 return bindType(stmt, allocator, idx, @TypeOf(base), base);
374 },
375 else => comptime unreachable,
376 }
377 }
378
379 fn bindText(stmt: Statement, idx: usize, value: []const u8, destructor: *allowzero anyopaque) !void {
380 const destructor_real: c.sqlite3_destructor_type = blk: {
381 // https://github.com/ziglang/translate-c/issues/128
382 @setRuntimeSafety(false);
383 const ptr: c.sqlite3_destructor_type = @ptrCast(@alignCast(destructor));
384 break :blk ptr;
385 };
386 return s.please_d(stmt.db, c.sqlite3_bind_text64(stmt.stmt, @intCast(idx), value.ptr, value.len, destructor_real, c.SQLITE_UTF8));
387 }
388
389 fn bindBlob(stmt: Statement, idx: usize, value: []const u8, destructor: *allowzero anyopaque) !void {
390 const destructor_real: c.sqlite3_destructor_type = blk: {
391 // https://github.com/ziglang/translate-c/issues/128
392 @setRuntimeSafety(false);
393 const ptr: c.sqlite3_destructor_type = @ptrCast(@alignCast(destructor));
394 break :blk ptr;
395 };
396 return s.please_d(stmt.db, c.sqlite3_bind_blob64(stmt.stmt, @intCast(idx), value.ptr, value.len, destructor_real));
397 }
398
399 pub fn exec(stmt: Statement, allocator: std.mem.Allocator) !void {
400 const iter = stmt.iterate();
401 errdefer iter.reset();
402 const row = try iter.step(allocator, void);
403 if (builtin.mode == .Debug) std.debug.assert(row == null);
404 }
405
406 pub fn iterate(stmt: Statement) Iterator {
407 return .{ .db = stmt.db, .stmt = stmt.stmt };
408 }
409
410 pub const Iterator = struct {
411 db: *c.sqlite3,
412 stmt: *c.sqlite3_stmt,
413
414 pub fn reset(iter: Iterator) void {
415 return s.please_d(iter.db, c.sqlite3_reset(iter.stmt)) catch {};
416 }
417
418 pub fn step(iter: Iterator, allocator: std.mem.Allocator, T: type) !?T {
419 const code = c.sqlite3_step(iter.stmt);
420 if (code == c.SQLITE_DONE) return null;
421 if (code != c.SQLITE_ROW) return s.rc2p_d(iter.db, code);
422 if (T == void) return;
423 if (T == string) return try readType(iter, allocator, 0, string);
424 if (@typeInfo(T) == .int) return try readType(iter, allocator, 0, T);
425 var result: T = undefined;
426 inline for (@typeInfo(T).@"struct".fields, 0..) |f, i| {
427 @field(result, f.name) = try readType(iter, allocator, i, f.type);
428 }
429 return result;
430 }
431
432 fn readType(iter: Iterator, allocator: std.mem.Allocator, idx: usize, T: type) !T {
433 if (comptime extras.isZigString(T)) {
434 const res = c.sqlite3_column_text(iter.stmt, @intCast(idx));
435 if (res == null) return "";
436 const ptr: [*:0]const u8 = @ptrCast(res);
437 const len = c.sqlite3_column_bytes(iter.stmt, @intCast(idx));
438 return try allocator.dupe(u8, ptr[0..@intCast(len) :0]);
439 }
440 if (comptime extras.isArrayOf(u8)(T)) {
441 const info = @typeInfo(T).array;
442 const ptr: [*:0]const u8 = @ptrCast(c.sqlite3_column_blob(iter.stmt, @intCast(idx)));
443 const len = c.sqlite3_column_bytes(iter.stmt, @intCast(idx));
444 std.debug.assert(len == info.len);
445 return ptr[0..info.len].*;
446 }
447 switch (@typeInfo(T)) {
448 .int => |info| {
449 comptime std.debug.assert(info.bits <= 64);
450 const res = c.sqlite3_column_int64(iter.stmt, @intCast(idx));
451 if (res > std.math.maxInt(T)) return error.Overflow;
452 if (res < std.math.minInt(T)) return error.Overflow;
453 return @intCast(res);
454 },
455 .bool => {
456 return @bitCast(try readType(iter, allocator, idx, u1));
457 },
458 .@"struct" => |info| {
459 if (@hasDecl(T, "BaseType")) return readBaseType(iter, allocator, idx, T);
460 if (info.layout == .@"packed") return @bitCast(try readType(iter, allocator, idx, info.backing_integer.?));
461 return readBaseType(iter, allocator, idx, T);
462 },
463 .optional => |info| {
464 if (c.sqlite3_column_type(iter.stmt, @intCast(idx)) == c.SQLITE_NULL) return null;
465 return try readType(iter, allocator, idx, info.child);
466 },
467 .@"enum" => {
468 if (T.BaseType == string) {
469 const ptr: [*:0]const u8 = @ptrCast(c.sqlite3_column_text(iter.stmt, @intCast(idx)));
470 const len = c.sqlite3_column_bytes(iter.stmt, @intCast(idx));
471 const str = ptr[0..@intCast(len) :0];
472 const enm = std.meta.stringToEnum(T, str);
473 return enm orelse T.default;
474 }
475 },
476 else => {}, // else => @compileError(T), // https://codeberg.org/ziglang/zig/issues/32119
477 }
478 }
479
480 fn readBaseType(iter: Iterator, allocator: std.mem.Allocator, idx: usize, T: type) !T {
481 const base = try readType(iter, allocator, idx, T.BaseType);
482 return T.readField(allocator, base);
483 }
484 };
485};