From 72a6b23d42b9ed6f885bb801515062951e61fcf5 Mon Sep 17 00:00:00 2001 From: Vincent Rischmann Date: Sun, 27 Dec 2020 18:49:17 +0100 Subject: introduce *Alloc methods Stmt.oneAlloc, Db.oneAlloc and Iterator.nextAlloc do the same thing as Stmt.one, Db.one, Iterator.next respectively but they can allocate memory. This is useful when reading TEXT or BLOB columns because if you can't allocate memory the only way to read these types is with an array which means you must have an idea of the maximum size of the column. --- sqlite.zig | 157 ++++++++++++++++++++++++++++++++++++++++++------------------- 1 file changed, 109 insertions(+), 48 deletions(-) diff --git a/sqlite.zig b/sqlite.zig index e72c2d5..4cd91fd 100644 --- a/sqlite.zig +++ b/sqlite.zig @@ -203,6 +203,13 @@ pub const Db = struct { return try stmt.one(Type, options, values); } + /// oneAlloc is like `one` but can allocate memory. + pub fn oneAlloc(self: *Self, comptime Type: type, allocator: *mem.Allocator, comptime query: []const u8, options: anytype, values: anytype) !?Type { + var stmt = try self.prepare(query); + defer stmt.deinit(); + return try stmt.oneAlloc(Type, allocator, options, values); + } + /// prepare prepares a statement for the `query` provided. /// /// The query is analysed at comptime to search for bind markers. @@ -259,16 +266,55 @@ pub fn Iterator(comptime Type: type) type { stmt: *c.sqlite3_stmt, // next scans the next row using the prepared statement. - // // If it returns null iterating is done. + // + // This cannot allocate memory. If you need to read TEXT or BLOB columns you need to use arrays or alternatively call nextAlloc. pub fn next(self: *Self, options: anytype) !?Type { var result = c.sqlite3_step(self.stmt); if (result == c.SQLITE_DONE) { return null; } + if (result != c.SQLITE_ROW) { + return error.SQLiteStepError; + } + const columns = c.sqlite3_column_count(self.stmt); + + switch (TypeInfo) { + .Int => { + debug.assert(columns == 1); + return try self.readInt(Type, 0); + }, + .Float => { + debug.assert(columns == 1); + return try self.readFloat(Type, 0); + }, + .Bool => { + debug.assert(columns == 1); + return try self.readBool(0); + }, + .Void => { + debug.assert(columns == 1); + }, + .Array => { + debug.assert(columns == 1); + return try self.readArray(Type, 0); + }, + .Struct => { + std.debug.assert(columns == TypeInfo.Struct.fields.len); + return try self.readStruct(.{}); + }, + else => @compileError("cannot read into type " ++ @typeName(Type) ++ " ; if dynamic memory allocation is required use nextAlloc"), + } + } + + // nextAlloc is like `next` but can allocate memory. + pub fn nextAlloc(self: *Self, allocator: *mem.Allocator, options: anytype) !?Type { + var result = c.sqlite3_step(self.stmt); + if (result == c.SQLITE_DONE) { + return null; + } if (result != c.SQLITE_ROW) { - logger.err("unable to iterate, result: {}", .{result}); return error.SQLiteStepError; } @@ -277,15 +323,15 @@ pub fn Iterator(comptime Type: type) type { switch (Type) { []const u8, []u8 => { debug.assert(columns == 1); - return try self.readBytes(Type, options.allocator, 0, .Text); + return try self.readBytes(Type, allocator, 0, .Text); }, Blob => { debug.assert(columns == 1); - return try self.readBytes(Blob, options.allocator, 0, .Blob); + return try self.readBytes(Blob, allocator, 0, .Blob); }, Text => { debug.assert(columns == 1); - return try self.readBytes(Text, options.allocator, 0, .Text); + return try self.readBytes(Text, allocator, 0, .Text); }, else => {}, } @@ -312,11 +358,13 @@ pub fn Iterator(comptime Type: type) type { }, .Pointer => { debug.assert(columns == 1); - return try self.readPointer(Type, 0, options); + return try self.readPointer(Type, allocator, 0); }, .Struct => { std.debug.assert(columns == TypeInfo.Struct.fields.len); - return try self.readStruct(options); + return try self.readStruct(.{ + .allocator = allocator, + }); }, else => @compileError("cannot read into type " ++ @typeName(Type)), } @@ -458,7 +506,7 @@ pub fn Iterator(comptime Type: type) type { } } - fn readPointer(self: *Self, comptime PointerType: type, i: usize, options: anytype) !PointerType { + fn readPointer(self: *Self, comptime PointerType: type, allocator: *mem.Allocator, i: usize) !PointerType { const type_info = @typeInfo(PointerType); var ret: PointerType = undefined; @@ -467,7 +515,7 @@ pub fn Iterator(comptime Type: type) type { switch (ptr.size) { .One => unreachable, .Slice => switch (ptr.child) { - u8 => ret = try self.readBytes(PointerType, options.allocator, i, .Text), + u8 => ret = try self.readBytes(PointerType, allocator, i, .Text), else => @compileError("cannot read pointer of type " ++ @typeName(PointerType)), }, else => @compileError("cannot read pointer of type " ++ @typeName(PointerType)), @@ -516,7 +564,7 @@ pub fn Iterator(comptime Type: type) type { .Bool => try self.readBool(i), .Void => {}, .Array => try self.readArray(field.field_type, i), - .Pointer => try self.readPointer(field.field_type, i, options), + .Pointer => try self.readPointer(field.field_type, options.allocator, i), else => @compileError("cannot populate field " ++ field.name ++ " of type " ++ @typeName(field.field_type)), }, }; @@ -734,10 +782,10 @@ pub fn Statement(comptime opts: StatementOptions, comptime query: ParsedQuery) t /// const row = try stmt.one( /// struct { /// id: usize, - /// name: []const u8, + /// name: [400]u8, /// age: usize, /// }, - /// .{ .allocator = allocator }, + /// .{}, /// .{ .foo = "bar", .age = 500 }, /// ); /// @@ -747,6 +795,7 @@ pub fn Statement(comptime opts: StatementOptions, comptime query: ParsedQuery) t /// The `values` tuple is used for the bind parameters. It must have as many fields as there are bind markers /// in the input query string. /// + /// This cannot allocate memory. If you need to read TEXT or BLOB columns you need to use arrays or alternatively call `oneAlloc`. pub fn one(self: *Self, comptime Type: type, options: anytype, values: anytype) !?Type { if (!comptime std.meta.trait.is(.Struct)(@TypeOf(options))) { @compileError("options passed to iterator must be a struct"); @@ -758,6 +807,18 @@ pub fn Statement(comptime opts: StatementOptions, comptime query: ParsedQuery) t return row; } + /// oneAlloc is like `one` but can allocate memory. + pub fn oneAlloc(self: *Self, comptime Type: type, allocator: *mem.Allocator, options: anytype, values: anytype) !?Type { + if (!comptime std.meta.trait.is(.Struct)(@TypeOf(options))) { + @compileError("options passed to iterator must be a struct"); + } + + var iter = try self.iterator(Type, values); + + const row = (try iter.nextAlloc(allocator, options)) orelse return null; + return row; + } + /// all reads all rows from the result set of this statement. /// /// The data in each row is used to populate a value of the type `Type`. @@ -773,7 +834,8 @@ pub fn Statement(comptime opts: StatementOptions, comptime query: ParsedQuery) t /// name: []const u8, /// age: usize, /// }, - /// .{ .allocator = allocator }, + /// allocator, + /// .{}, /// .{ .foo = "bar", .age = 500 }, /// ); /// @@ -783,18 +845,16 @@ pub fn Statement(comptime opts: StatementOptions, comptime query: ParsedQuery) t /// The `values` tuple is used for the bind parameters. It must have as many fields as there are bind markers /// in the input query string. /// - /// Note that this allocates all rows into a single slice: if you read a lot of data this can - /// use a lot of memory. - /// - pub fn all(self: *Self, comptime Type: type, options: anytype, values: anytype) ![]Type { + /// Note that this allocates all rows into a single slice: if you read a lot of data this can use a lot of memory. + pub fn all(self: *Self, comptime Type: type, allocator: *mem.Allocator, options: anytype, values: anytype) ![]Type { if (!comptime std.meta.trait.is(.Struct)(@TypeOf(options))) { @compileError("options passed to iterator must be a struct"); } var iter = try self.iterator(Type, values); - var rows = std.ArrayList(Type).init(options.allocator); + var rows = std.ArrayList(Type).init(allocator); while (true) { - const row = (try iter.next(options)) orelse break; + const row = (try iter.nextAlloc(allocator, options)) orelse break; try rows.append(row); } @@ -866,22 +926,25 @@ test "sqlite: db pragma" { if (build_options.in_memory) { const journal_mode = try db.pragma( - []const u8, + [128:0]u8, "journal_mode", - .{ .allocator = &arena.allocator }, + .{}, .{"wal"}, ); testing.expect(journal_mode != null); testing.expectEqualStrings("memory", journal_mode.?); } else { - const journal_mode = try db.pragma( - []const u8, - "journal_mode", - .{ .allocator = &arena.allocator }, - .{"wal"}, - ); - testing.expect(journal_mode != null); - testing.expectEqualStrings("wal", journal_mode.?); + { + const journal_mode = try db.pragma([128:0]u8, .{}, "journal_mode", arg); + testing.expect(journal_mode != null); + testing.expectEqualStrings("wal", mem.spanZ(&journal_mode.?)); + } + + { + const journal_mode = try db.pragmaAlloc([]const u8, &arena.allocator, .{}, "journal_mode", arg); + testing.expect(journal_mode != null); + testing.expectEqualStrings("wal", journal_mode.?); + } } } @@ -920,11 +983,9 @@ test "sqlite: read a single user into a struct" { var stmt = try db.prepare("SELECT id, name, age, weight FROM user WHERE id = ?{usize}"); defer stmt.deinit(); - var rows = try stmt.all( - TestUser, - .{ .allocator = &arena.allocator }, - .{ .id = @as(usize, 20) }, - ); + var rows = try stmt.all(TestUser, &arena.allocator, .{}, .{ + .id = @as(usize, 20), + }); for (rows) |row| { testing.expectEqual(test_users[0].id, row.id); testing.expectEqualStrings(test_users[0].name, row.name); @@ -933,14 +994,15 @@ test "sqlite: read a single user into a struct" { // Read a row with db.one() { - var row = try db.one( + var row = try db.oneAlloc( struct { id: usize, name: Text, age: usize, }, + &arena.allocator, "SELECT id, name, age FROM user WHERE id = ?{usize}", - .{ .allocator = &arena.allocator }, + .{}, .{@as(usize, 20)}, ); testing.expect(row != null); @@ -963,11 +1025,7 @@ test "sqlite: read all users into a struct" { var stmt = try db.prepare("SELECT id, name, age, weight FROM user"); defer stmt.deinit(); - var rows = try stmt.all( - TestUser, - .{ .allocator = &arena.allocator }, - .{}, - ); + var rows = try stmt.all(TestUser, &arena.allocator, .{}, .{}); testing.expectEqual(@as(usize, 3), rows.len); for (rows) |row, i| { const exp = test_users[i]; @@ -988,7 +1046,7 @@ test "sqlite: read in an anonymous struct" { var stmt = try db.prepare("SELECT id, name, name, age, id, weight FROM user WHERE id = ?{usize}"); defer stmt.deinit(); - var row = try stmt.one( + var row = try stmt.oneAlloc( struct { id: usize, name: []const u8, @@ -997,7 +1055,8 @@ test "sqlite: read in an anonymous struct" { is_id: bool, weight: f64, }, - .{ .allocator = &arena.allocator }, + &arena.allocator, + .{}, .{ .id = @as(usize, 20) }, ); testing.expect(row != null); @@ -1022,13 +1081,14 @@ test "sqlite: read in a Text struct" { var stmt = try db.prepare("SELECT id, name, age FROM user WHERE id = ?{usize}"); defer stmt.deinit(); - var row = try stmt.one( + var row = try stmt.oneAlloc( struct { id: usize, name: Text, age: usize, }, - .{ .allocator = &arena.allocator }, + &arena.allocator, + .{}, .{@as(usize, 20)}, ); testing.expect(row != null); @@ -1069,9 +1129,10 @@ test "sqlite: read a single text value" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - const name = try stmt.one( + const name = try stmt.oneAlloc( typ, - .{ .allocator = &arena.allocator }, + &arena.allocator, + .{}, .{ .id = @as(usize, 20) }, ); testing.expect(name != null); @@ -1245,7 +1306,7 @@ test "sqlite: statement iterator" { var rows = std.ArrayList(Type).init(allocator); while (true) { - const row = (try iter.next(.{ .allocator = allocator })) orelse break; + const row = (try iter.nextAlloc(allocator, .{})) orelse break; try rows.append(row); } -- cgit v1.2.3 From b257b89ec8c2a20bfc2454aa7b019a1497902f82 Mon Sep 17 00:00:00 2001 From: Vincent Rischmann Date: Sun, 27 Dec 2020 19:03:32 +0100 Subject: add more tests --- sqlite.zig | 111 +++++++++++++++++++++++++++++++++++++++++++++---------------- 1 file changed, 83 insertions(+), 28 deletions(-) diff --git a/sqlite.zig b/sqlite.zig index 4cd91fd..bc1235a 100644 --- a/sqlite.zig +++ b/sqlite.zig @@ -993,6 +993,26 @@ test "sqlite: read a single user into a struct" { } // Read a row with db.one() + { + var row = try db.one( + struct { + id: usize, + name: [128:0]u8, + age: usize, + }, + "SELECT id, name, age FROM user WHERE id = ?{usize}", + .{}, + .{@as(usize, 20)}, + ); + testing.expect(row != null); + + const exp = test_users[0]; + testing.expectEqual(exp.id, row.?.id); + testing.expectEqualStrings(exp.name, mem.spanZ(&row.?.name)); + testing.expectEqual(exp.age, row.?.age); + } + + // Read a row with db.oneAlloc() { var row = try db.oneAlloc( struct { @@ -1129,12 +1149,9 @@ test "sqlite: read a single text value" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - const name = try stmt.oneAlloc( - typ, - &arena.allocator, - .{}, - .{ .id = @as(usize, 20) }, - ); + const name = try stmt.oneAlloc(typ, &arena.allocator, .{}, .{ + .id = @as(usize, 20), + }); testing.expect(name != null); switch (typ) { Text, Blob => { @@ -1181,7 +1198,9 @@ test "sqlite: read a single integer value" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - var age = try stmt.one(typ, .{}, .{ .id = @as(usize, 20) }); + var age = try stmt.one(typ, .{}, .{ + .id = @as(usize, 20), + }); testing.expect(age != null); testing.expectEqual(@as(typ, 33), age.?); @@ -1198,7 +1217,9 @@ test "sqlite: read a single value into void" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - _ = try stmt.one(void, .{}, .{ .id = @as(usize, 20) }); + _ = try stmt.one(void, .{}, .{ + .id = @as(usize, 20), + }); } test "sqlite: read a single value into bool" { @@ -1211,7 +1232,9 @@ test "sqlite: read a single value into bool" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - const b = try stmt.one(bool, .{}, .{ .id = @as(usize, 20) }); + const b = try stmt.one(bool, .{}, .{ + .id = @as(usize, 20), + }); testing.expect(b != null); testing.expect(b.?); } @@ -1232,7 +1255,9 @@ test "sqlite: insert bool and bind bool" { var stmt: Statement(.{}, ParsedQuery.from(query)) = try db.prepare(query); defer stmt.deinit(); - const b = try stmt.one(bool, .{}, .{ .is_published = true }); + const b = try stmt.one(bool, .{}, .{ + .is_published = true, + }); testing.expect(b != null); testing.expect(b.?); } @@ -1293,30 +1318,60 @@ test "sqlite: statement iterator" { testing.expectEqual(@as(usize, 1), rows_inserted); } - // Get the data with an iterator - var stmt2 = try db.prepare("SELECT name, age FROM user"); - defer stmt2.deinit(); + // Get data with a non-allocating iterator. + { + var stmt2 = try db.prepare("SELECT name, age FROM user"); + defer stmt2.deinit(); - const Type = struct { - name: Text, - age: usize, - }; + const RowType = struct { + name: [128:0]u8, + age: usize, + }; + + var iter = try stmt2.iterator(RowType, .{}); + + var rows = std.ArrayList(RowType).init(allocator); + while (true) { + const row = (try iter.next(.{})) orelse break; + try rows.append(row); + } - var iter = try stmt2.iterator(Type, .{}); + // Check the data + testing.expectEqual(expected_rows.items.len, rows.items.len); - var rows = std.ArrayList(Type).init(allocator); - while (true) { - const row = (try iter.nextAlloc(allocator, .{})) orelse break; - try rows.append(row); + for (rows.items) |row, j| { + const exp_row = expected_rows.items[j]; + testing.expectEqualStrings(exp_row.name, mem.spanZ(&row.name)); + testing.expectEqual(exp_row.age, row.age); + } } - // Check the data - testing.expectEqual(expected_rows.items.len, rows.items.len); + // Get data with an iterator + { + var stmt2 = try db.prepare("SELECT name, age FROM user"); + defer stmt2.deinit(); + + const RowType = struct { + name: Text, + age: usize, + }; - for (rows.items) |row, j| { - const exp_row = expected_rows.items[j]; - testing.expectEqualStrings(exp_row.name, row.name.data); - testing.expectEqual(exp_row.age, row.age); + var iter = try stmt2.iterator(RowType, .{}); + + var rows = std.ArrayList(RowType).init(allocator); + while (true) { + const row = (try iter.nextAlloc(allocator, .{})) orelse break; + try rows.append(row); + } + + // Check the data + testing.expectEqual(expected_rows.items.len, rows.items.len); + + for (rows.items) |row, j| { + const exp_row = expected_rows.items[j]; + testing.expectEqualStrings(exp_row.name, row.name.data); + testing.expectEqual(exp_row.age, row.age); + } } } -- cgit v1.2.3 From 9255ecf9bf5f16f4f172e5d8754b18b2a361565c Mon Sep 17 00:00:00 2001 From: Vincent Rischmann Date: Sun, 27 Dec 2020 23:05:37 +0100 Subject: add pragmaAlloc --- sqlite.zig | 64 +++++++++++++++++++++++++++++++++++++++++--------------------- 1 file changed, 43 insertions(+), 21 deletions(-) diff --git a/sqlite.zig b/sqlite.zig index bc1235a..6abedc4 100644 --- a/sqlite.zig +++ b/sqlite.zig @@ -159,6 +159,30 @@ pub const Db = struct { return getLastDetailedErrorFromDb(self.db); } + fn getPragmaQuery(comptime buf: []u8, comptime name: []const u8, comptime arg: anytype) []const u8 { + return if (arg.len == 1) blk: { + break :blk try std.fmt.bufPrint(buf, "PRAGMA {} = {}", .{ name, arg[0] }); + } else blk: { + break :blk try std.fmt.bufPrint(buf, "PRAGMA {}", .{name}); + }; + } + + /// pragmaAlloc is like `pragma` but can allocate memory. + /// + /// Useful when the pragma command returns text, for example: + /// + /// const journal_mode = try db.pragma([]const u8, allocator, .{}, "journal_mode", .{}); + /// + pub fn pragmaAlloc(self: *Self, comptime Type: type, allocator: *mem.Allocator, options: anytype, comptime name: []const u8, comptime arg: anytype) !?Type { + comptime var buf: [1024]u8 = undefined; + comptime var query = getPragmaQuery(&buf, name, arg); + + var stmt = try self.prepare(query); + defer stmt.deinit(); + + return try stmt.oneAlloc(Type, allocator, options, .{}); + } + /// pragma is a convenience function to use the PRAGMA statement. /// /// Here is how to set a pragma value: @@ -167,21 +191,14 @@ pub const Db = struct { /// /// Here is how to query a pragama value: /// - /// const journal_mode = try db.pragma( - /// []const u8, - /// "journal_mode", - /// .{ .allocator = allocator }, - /// .{}, - /// ); + /// const journal_mode = try db.pragma([128:0]const u8, .{}, "journal_mode", .{}); /// /// The pragma name must be known at comptime. - pub fn pragma(self: *Self, comptime Type: type, comptime name: []const u8, options: anytype, arg: anytype) !?Type { + /// + /// This cannot allocate memory. If your pragma command returns text you must use an array or call `pragmaAlloc`. + pub fn pragma(self: *Self, comptime Type: type, options: anytype, comptime name: []const u8, arg: anytype) !?Type { comptime var buf: [1024]u8 = undefined; - comptime var query = if (arg.len == 1) blk: { - break :blk try std.fmt.bufPrint(&buf, "PRAGMA {} = {}", .{ name, arg[0] }); - } else blk: { - break :blk try std.fmt.bufPrint(&buf, "PRAGMA {}", .{name}); - }; + comptime var query = getPragmaQuery(&buf, name, arg); var stmt = try self.prepare(query); defer stmt.deinit(); @@ -920,19 +937,24 @@ test "sqlite: db pragma" { var db: Db = undefined; try db.init(initOptions()); - const foreign_keys = try db.pragma(usize, "foreign_keys", .{}, .{}); + const foreign_keys = try db.pragma(usize, .{}, "foreign_keys", .{}); testing.expect(foreign_keys != null); testing.expectEqual(@as(usize, 0), foreign_keys.?); + const arg = .{"wal"}; + if (build_options.in_memory) { - const journal_mode = try db.pragma( - [128:0]u8, - "journal_mode", - .{}, - .{"wal"}, - ); - testing.expect(journal_mode != null); - testing.expectEqualStrings("memory", journal_mode.?); + { + const journal_mode = try db.pragma([128:0]u8, .{}, "journal_mode", arg); + testing.expect(journal_mode != null); + testing.expectEqualStrings("memory", mem.spanZ(&journal_mode.?)); + } + + { + const journal_mode = try db.pragmaAlloc([]const u8, &arena.allocator, .{}, "journal_mode", arg); + testing.expect(journal_mode != null); + testing.expectEqualStrings("memory", journal_mode.?); + } } else { { const journal_mode = try db.pragma([128:0]u8, .{}, "journal_mode", arg); -- cgit v1.2.3 From faad7cb2da89a49082a25854b945314960f506c9 Mon Sep 17 00:00:00 2001 From: Vincent Rischmann Date: Mon, 28 Dec 2020 23:23:24 +0100 Subject: readme: document both allocating and non-allocating methods --- README.md | 177 +++++++++++++++++++++++++++++++++++--------------------------- 1 file changed, 101 insertions(+), 76 deletions(-) diff --git a/README.md b/README.md index 452e3e7..cdebc3c 100644 --- a/README.md +++ b/README.md @@ -2,13 +2,13 @@ This package is a thin wrapper around [sqlite](https://sqlite.org/index.html)'s C API. -## Status +# Status While the core functionality works right now, the API is still subject to changes. If you use this library, expect to have to make changes when you update the code. -## Requirements +# Requirements * [Zig master](https://ziglang.org/download/) * Linux @@ -16,12 +16,12 @@ If you use this library, expect to have to make changes when you update the code * `libsqlite3-dev` for Debian and derivatives * `sqlite3-devel` for Fedora -## Features +# Features * Preparing, executing statements * comptime checked bind parameters -## Installation +# Installation Since there's no package manager for Zig yet, the recommended way is to use a git submodule: @@ -43,9 +43,9 @@ Now you should be able to import sqlite like this: const sqlite = @import("sqlite"); ``` -## Usage +# Usage -### Initialization +## Initialization You must create and initialize an instance of `sqlite.Db`: @@ -65,7 +65,9 @@ The `init` method takes an allocator and a `InitOptions` struct which will be us Only the `mode` field is mandatory, the other fields have sane default values. -### Preparing a statement +## Preparing a statement + +### Common use sqlite works exclusively by using prepared statements. The wrapper type is `sqlite.Statement`. Here is how you get one: @@ -80,7 +82,7 @@ defer stmt.deinit(); The `Db.prepare` method takes a `comptime` query string. -### Executing a statement +## Executing a statement For queries which do not return data (`INSERT`, `UPDATE`) you can use the `exec` method: @@ -100,104 +102,131 @@ try stmt.exec({ See the section "Bind parameters and resultset rows" for more information on the types mapping rules. -### Reading data +## Reading data + +For queries which return data you have multiple options: +* `Statement.all` which takes an allocator and can allocate memory. +* `Statement.one` which does not take an allocator and cannot allocate memory (aside from what SQLite allocates itself). +* `Statement.oneAlloc` which takes an allocator and can allocate memory. + +### Type parameter + +All these methods take a type as first parameter. -For queries which do return data you can use the `all` method: +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. + +### Non allocating + +Using `one`: ```zig const query = - \\SELECT id, name, age, salary FROM employees WHERE age > ? AND age < ? + \\SELECT name, age FROM employees WHERE id = ? ; var stmt = try db.prepare(query); defer stmt.deinit(); -const rows = try stmt.all( +const row = try stmt.one( struct { - id: usize, - name: []const u8, - age: u16, - salary: u32, + name: [128:0]u8, + age: usize, }, - .{ .allocator = allocator }, - .{ .age1 = 20, .age2 = 40 }, + .{}, + .{ .id = 20 }, ); -for (rows) |row| { - std.log.debug("id: {} ; name: {}; age: {}; salary: {}", .{ row.id, row.name, row.age, row.salary }); +if (row) |age| { + std.log.debug("age: {}", .{age}); } ``` +Notice that to read text we need to use a 0-terminated array; if the `name` column is bigger than 127 bytes the call to `one` will fail. -The `all` method takes a type and an options tuple. +The sentinel is mandatory: without one there would be no way to know where the data ends in the array. -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. +The convenience function `sqlite.Db.one` works exactly the same way: -Not all types are allowed, see the section "Bind parameters and resultset rows" for more information on the types mapping rules. +```zig +const query = + \\SELECT age FROM employees WHERE id = ? +; + +const row = try db.one(usize, query, .{}, .{ .id = 20 }); +if (row) |age| { + std.log.debug("age: {}", .{age}); +} +``` -The options tuple is used to pass additional state required for some queries, usually it will be an allocator. -Not all queries require an allocator, hence why it's not required for every call. +### Allocating -The `one` method on a statement works the same way except it returns the first row of the result set: +Using `all`: ```zig const query = - \\SELECT age FROM employees WHERE id = ? + \\SELECT name FROM employees WHERE age > ? AND age < ? ; var stmt = try db.prepare(query); defer stmt.deinit(); -const row = try stmt.one(usize, .{}, .{ .id = 20 }); -if (row) |age| { - std.log.debug("age: {}", .{age}); +const rows = try stmt.all([]const u8, allocator, .{}, .{ + .age1 = 20, + .age2 = 40, +}); +for (rows) |row| { + std.log.debug("id: {} ; name: {}; age: {}; salary: {}", .{ row.id, row.name, row.age, row.salary }); } ``` -The convienence function `sqlite.Db.one` works exactly the same way: +Using `oneAlloc`: ```zig const query = - \\SELECT age FROM employees WHERE id = ? + \\SELECT name FROM employees WHERE id = ? ; -const row = try db.one(usize, query, .{}, .{ .id = 20 }); -if (row) |age| { - std.log.debug("age: {}", .{age}); -} +var stmt = try db.prepare(query); +defer stmt.deinit(); + +const name = try stmt.oneAlloc([]const u8, allocator, .{}, .{ + .id = 200, +}); +std.log.debug("name: {}", .{name}); ``` -### Iterating +## Iterating Another way to get the data returned by a query is to use the `sqlite.Iterator` type. -You can only get one by calling the `iterator` method on a statement: +You can only get one by calling the `iterator` method on a statement. + +The `iterator` method takes a type which is the same as with `all`, `one` or `oneAlloc`: every row retrieved by calling `next` or `nextAlloc` will have this type. + +Iterating is done by calling the `next` or `nextAlloc` method on an iterator. Just like before, `next` cannot allocate memory while `nextAlloc` can allocate memory. + +`next` or `nextAlloc` will either return an optional value or an error; you should keep iterating until `null` is returned. + +### Non allocating ```zig -var stmt = try db.prepare("SELECT name FROM user WHERE age < ?"); +var stmt = try db.prepare("SELECT age FROM user WHERE age < ?"); defer stmt.deinit(); -var iter = try stmt.iterator([]const u8, .{ +var iter = try stmt.iterator(usize, .{ .age = 20, }); -var names = std.ArrayList([]const u8).init(allocator); while (true) { - const row = (try iter.next(.{ .allocator = allocator })) orelse break; - try rows.append(row); + const age = (try iter.next(.{})) orelse break; + std.debug.print("age: {}\n", .{age}); } ``` -The `iterator` method takes a type which is the same as with `all` or `one`: every row retrieved by calling `next` will have this type. - -Using the iterator is straightforward: call `next` on it in a loop; it can either fail with an error or return an optional value: if that optional is null, iterating is done. +### Allocating -The `next` method takes an options tuple which serves the same function as the one in `all` or `one`. - -The code example above uses the iterator but it's no different than just calling `all` used like this; the real benefit of the iterator is to be able to process each row -sequentially without needing to store all the resultset in memory at the same time. - -Here's an example: ```zig var stmt = try db.prepare("SELECT name FROM user WHERE age < ?"); defer stmt.deinit(); @@ -210,15 +239,12 @@ while (true) { var arena = std.heap.ArenaAllocator.init(allocator); defer arena.deinit(); - const name = (try iter.next(.{ .allocator = &arena.allocator })) orelse break; - - // do stuff with name here + const name = (try iter.nextAlloc(&arena.allocator, .{})) orelse break; + std.debug.print("name: {}\n", .{name}); } ``` -Used like this the memory required for the row is only used for one iteration. You can imagine this is especially useful if your resultset contains millions of rows. - -### Bind parameters and resultset rows +## 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. @@ -238,11 +264,11 @@ Here are the rules for resultset rows: Note that arrays must have a sentinel because we need a way to communicate where the data actually stops in the array, so for example use `[200:0]u8` for a `TEXT` field. -## Comptime checks +# Comptime checks Prepared statements contain _comptime_ metadata which is used to validate every call to `exec`, `one` and `all` _at compile time_. -### Check the number of bind parameters. +## Check the number of bind parameters. The first check makes sure you provide the same number of bind parameters as there are bind markers in the query string. @@ -251,7 +277,7 @@ Take the following code: var stmt = try db.prepare("SELECT id FROM user WHERE age > ? AND age < ? AND weight > ?"); defer stmt.deinit(); -const rows = try stmt.all(usize, .{ .allocator = allocator }, .{ +const rows = try stmt.all(usize, .{}, .{ .age_1 = 10, .age_2 = 20, }); @@ -259,23 +285,23 @@ _ = rows; ``` It fails with this compilation error: ``` -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:465:17: error: number of bind markers not equal to number of fields +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:738:17: error: number of bind markers not equal to number of fields @compileError("number of bind markers not equal to number of fields"); ^ -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:543:22: note: called from here +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:817:22: note: called from here self.bind(values); ^ -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:619:41: note: called from here +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:905:41: note: called from here var iter = try self.iterator(Type, values); ^ -./src/main.zig:16:30: note: called from here - const rows = try stmt.all(usize, .{ .allocator = allocator }, .{ +./src/main.zig:19:30: note: called from here + const rows = try stmt.all(usize, allocator, .{}, .{ ^ ./src/main.zig:5:29: note: called from here pub fn main() anyerror!void { ``` -### Assign types to bind markers and check them. +## Assign types to bind markers and check them. The second (and more interesting) check makes sure you provide appropriately typed values as bind parameters. @@ -307,21 +333,20 @@ const rows = try stmt.all(usize, .{ .allocator = allocator }, .{ .weight = false, }); _ = rows; - ``` Now this fails to compile: ``` -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:485:25: error: value type bool is not the bind marker type usize +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:745:25: error: value type bool is not the bind marker type usize @compileError("value type " ++ @typeName(struct_field.field_type) ++ " is not the bind marker type " ++ @typeName(typ)); ^ -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:557:22: note: called from here +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:817:22: note: called from here self.bind(values); ^ -/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:633:41: note: called from here +/home/vincent/dev/perso/libs/zig-sqlite/sqlite.zig:905:41: note: called from here var iter = try self.iterator(Type, values); ^ -./src/main.zig:16:30: note: called from here - const rows = try stmt.all(usize, .{ .allocator = allocator }, .{ +./src/main.zig:19:30: note: called from here + const rows = try stmt.all(usize, allocator, .{}, .{ ^ ./src/main.zig:5:29: note: called from here pub fn main() anyerror!void { @@ -346,7 +371,7 @@ To finish our example, passing the proper type allows it compile: var stmt = try db.prepare("SELECT id FROM user WHERE age > ? AND age < ? AND weight > ?{usize}"); defer stmt.deinit(); -const rows = try stmt.all(usize, .{ .allocator = allocator }, .{ +const rows = try stmt.all(usize, .{}, .{ .age_1 = 10, .age_2 = 20, .weight = @as(usize, 200), -- cgit v1.2.3 From 56d67e5d5c3b7fa2c094719757b168c3e5725057 Mon Sep 17 00:00:00 2001 From: Vincent Rischmann Date: Thu, 31 Dec 2020 14:50:12 +0100 Subject: fix readme * we can't actually bind arrays. * arrays require a sentinel --- README.md | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/README.md b/README.md index cdebc3c..dea6990 100644 --- a/README.md +++ b/README.md @@ -251,7 +251,7 @@ Since sqlite doesn't have many [types](https://www.sqlite.org/datatype3.html) on 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`. +* `[]const u8`, `[]u8` is treated as a `TEXT`. * The custom `sqlite.Blob` type is treated as a `BLOB`. * The custom `sqlite.Text` type is treated as a `TEXT`. @@ -259,7 +259,7 @@ 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. +* `TEXT` can be read into any array of `u8` with a sentinel provided the data fits. * `BLOB` follows the same rules as `TEXT`. Note that arrays must have a sentinel because we need a way to communicate where the data actually stops in the array, so for example use `[200:0]u8` for a `TEXT` field. -- cgit v1.2.3