diff options
| -rw-r--r-- | src/db/sqlite.zig | 714 |
1 files changed, 546 insertions, 168 deletions
diff --git a/src/db/sqlite.zig b/src/db/sqlite.zig index d1accd5..0ef1b20 100644 --- a/src/db/sqlite.zig +++ b/src/db/sqlite.zig @@ -2,6 +2,9 @@ const std = @import("std"); const sqlite = @import("sqlite"); const assert = std.debug.assert; +const Allocator = std.mem.Allocator; +const Writer = std.Io.Writer; + pub fn Database(comptime T: type) type { const tables = switch (@typeInfo(T)) { .@"struct" => |s| s.fields, @@ -23,9 +26,9 @@ pub fn Database(comptime T: type) type { else => @compileError("Table `" ++ table.name ++ "` must be of type `struct`"), }; - // NOTE: A table must have at least one column - // (see `https://sqlite.org/syntax/create-table-stmt.html`). - if (columns.len == 0) @compileError("Table `" ++ table.name ++ "` must have at least one column"); + // NOTE: A table must have at least one column; see + // https://sqlite.org/syntax/create-table-stmt.html. + if (columns.len == 0) @compileError("Table `" ++ table.name ++ "` must have at least one column"); const TagIntColumns = @Int(.unsigned, std.math.log2_int_ceil(@TypeOf(columns.len), columns.len)); @@ -34,10 +37,14 @@ pub fn Database(comptime T: type) type { var column_values: [columns.len]TagIntColumns = undefined; inline for (&column_names, &column_types, &column_values, columns, 0..) |*column_name, *column_type, *column_value, column, column_index| { - // TODO: check column.type (of type `Type`) + if (!isTypename(column.type)) @compileError("Column `" ++ column.name ++ "` must be of type `Typename`"); + + const column_field_value = std.builtin.Type.StructField.defaultValue(column) orelse @compileError("Column `" ++ column.name ++ "` is missing value"); + + // TODO: use `column_field_value` to build `CREATE TABLE` SQLite statement - column_name.* = column.name; - column_type.* = column.type; + column_name.* = column.name; + column_type.* = column_field_value.type; column_value.* = column_index; } @@ -53,9 +60,9 @@ pub fn Database(comptime T: type) type { const Tables = @Union(.auto, TagTables, &table_names, &table_types, &@splat(.{})); return struct { - inner: Inner, + handle: *sqlite.sqlite3, - pub const Check = enum { + pub const Deinit = enum { ok, busy, }; @@ -82,10 +89,7 @@ pub fn Database(comptime T: type) type { const Schema = Tables; const Self = @This(); - const Inner = struct { - handle: *sqlite.sqlite3, - }; - + // Source: https://sqlite.org/c3ref/open.html pub fn init(kind: Kind) Error!Self { const name = switch (kind) { .temp_in_memory => ":memory:", @@ -97,10 +101,13 @@ pub fn Database(comptime T: type) type { const result: Result = .from(sqlite.sqlite3_open(name.ptr, &handle)); const primary = result.asPrimary(); - return if (primary == .ok) .{ .handle = handle.? } else result.toError(Error, handle); + if (primary != .ok) return result.toError(Error, handle); + + return .{ .handle = handle.? }; } - pub fn deinit(self: *Self) Check { + // See https://sqlite.org/c3ref/close.html + pub fn deinit(self: *Self) Deinit { const result: Result = .from(sqlite.sqlite3_close(self.handle)); const primary = result.asPrimary(); @@ -113,157 +120,495 @@ pub fn Database(comptime T: type) type { // NOTE: SELECT without FROM is non-conforming SQL // https://stackoverflow.com/questions/3732422/select-from-nothing // https://modern-sql.com/use-case/select-without-from - pub fn select(self: Self, comptime table: std.meta.Tag(Schema), comptime columns: []const std.meta.Tag(@FieldType(Schema, @tagName(table)))) Select(Self, table, columns) { - _ = rows; - return .{ .db = self.handle }; + pub fn select(self: Self, comptime from: std.meta.Tag(Schema), comptime columns: []const std.meta.Tag(@FieldType(Schema, @tagName(from)))) Select(Schema, from, columns) { + return .{ .handle = self.handle }; } }; } -fn Select(comptime Db: type, comptime table: std.meta.Tag(Db.Schema), comptime columns: []const std.meta.Tag(@FieldType(Db.Schema, @tagName(table)))) type { +pub const Integer = Typename(.integer); +pub const Real = Typename(.real); +pub const Text = Typename(.text); + +const Affinity = enum { + integer, + real, + text, + + const Value = union(Affinity) { + integer: i64, + real : f64, + text : []const u8, + + fn FieldType(comptime field: std.meta.Tag(Value)) type { + return @FieldType(Value, @tagName(field)); + } + + fn from(value: anytype) Value { + const T = @TypeOf(value); + + inline for (std.meta.fields(Value)) |field| { + if (field.type == T) return @unionInit(Value, field.name, value); + } + + @compileError("Cannot construct `Value` from value of type `" ++ @typeName(T) ++ "`"); + } + + fn serialise(self: Value, writer: *Writer) Writer.Error!void { + return switch (self) { + .integer => |value| writer.print("{d}", .{ value }), + .real => |value| writer.print("{d}", .{ value }), + .text => |value| writer.print("'{s}'", .{ value }), + }; + } + }; + + fn serialise(self: Affinity, writer: *Writer) Writer.Error!void { + return switch (self) { + .integer => writer.writeAll("INTEGER"), + .real => writer.writeAll("REAL"), + .text => writer.writeAll("TEXT"), + }; + } +}; + +// TODO: handle primary key (e.g. force every table to have one, etc.) +fn Typename(comptime affinity: Affinity) type { + const T = Affinity.Value.FieldType(affinity); + return struct { - db: *sqlite.sqlite3, - comptime table: std.meta.Tag(Db.Schema) = table, - comptime columns: []const std.meta.Tag(@FieldType(Db.Schema, @tagName(table))) = columns, + type : type = T, + affinity: Affinity = affinity, + default_value: ?T = null, + is_not_null : bool = false, + is_unique : bool = false, - const Self = @This(); + pub const any: Self = .{}; + pub const Self = @This(); + + pub fn default(comptime self: Self, comptime value: T) Self { + if (self.default_value) |_| @compileError("Duplicate `DEFAULT` constraint"); + + var this = self; + this.default_value = value; + + return this; + } + + pub fn notNull(comptime self: Self) Self { + if (self.is_not_null) @compileError("Duplicate `NOT NULL` constraint"); + + var this = self; + this.is_not_null = true; + + return this; + } + + pub fn unique(comptime self: Self) Self { + if (self.is_unique) @compileError("Duplicate `UNIQUE` constraint"); + + var this = self; + this.is_unique = true; + + return this; + } + + fn serialise(self: Self, writer: *Writer) Writer.Error!void { + try self.affinity.serialise(writer); + + if (self.default_value) |value| { + try writer.writeAll(" DEFAULT "); + try Affinity.Value.from(value).serialise(writer); + } + + if (self.is_not_null) { + try writer.writeAll(" NOT NULL"); + } - // pub fn where(self: Self, ) + if (self.is_unique) { + try writer.writeAll(" UNIQUE"); + } + } }; } -// pub const Type = struct { -// string: String.Static, -// type: type, -// -// pub fn integer() Type { -// // end of Type recursion -// } -// -// pub fn unique(Type) Type { -// -// } -// -// ... -// }; +fn isTypename(comptime T: type) bool { + if (!@hasField(T, "affinity")) return false; -// NEVER PUB!!! -const String = struct { - const Static = struct { - value: []const u8, - }; + const affinity = std.meta.fieldInfo(T, .affinity).defaultValue() orelse return false; - const Dynamic = struct { - fmt: []const u8, - args: type, - }; + return T == Typename(affinity); +} - fn contains(haystack: []const u8, needle: []const u8) bool { - if (haystack.len < needle.len) return false; +fn Select(comptime Schema: type, comptime from: std.meta.Tag(Schema), comptime columns: []const std.meta.Tag(@FieldType(Schema, @tagName(from)))) type { + if (columns.len == 0) @compileError("At least one column must be selected"); - var start: usize = 0; - while (start + needle.len <= haystack.len) : (start += 1) { - if (std.mem.eql(u8, haystack[start..(start + needle.len)], needle)) return true; - } + const TagIntColumns = @Int(.unsigned, std.math.log2_int_ceil(@TypeOf(columns.len), columns.len)); + + var column_names : [columns.len][]const u8 = undefined; + var column_types : [columns.len]type = undefined; + var column_values: [columns.len]TagIntColumns = undefined; - return false; + inline for (&column_names, &column_types, &column_values, columns, 0..) |*column_name, *column_type, *column_value, column,column_index| { + column_name.* = @tagName(column); + column_value.* = column_index; + column_type.* = @FieldType(@FieldType(Schema, @tagName(from)), @tagName(column)); } -}; -// pub fn Database(comptime S: Schema) type { -// _ = S; -// return struct { -// handle: *sqlite.sqlite3, -// -// pub const Check = enum { -// ok, -// unfinalised, -// }; -// -// pub const Error = set: { -// var set: type = error{}; -// -// for (std.meta.fieldNames(Result.Primary)) |name| { -// set = set || @TypeOf(@field(anyerror, name)); -// } -// for (std.meta.fieldNames(Result.Extended)) |name| { -// set = set || @TypeOf(@field(anyerror, name)); -// } -// -// break :set set; -// }; -// -// pub const Kind = union(enum) { -// temp_in_memory, -// temp_on_disk, -// path: []const u8, -// }; -// -// pub const Query = struct { -// handle: *sqlite.sqlite3_stmt, -// -// // Source: https://sqlite.org/c3ref/finalize.html -// pub fn deinit(self: *Query) void { -// const result: Result = .from(sqlite.sqlite3_finalize(self.handle)); -// -// // TODO: maybe don't panic? -// if (!result.isOk()) @panic("Last error was " ++ @errorName(result.toError())); -// -// self.* = undefined; -// } -// }; -// -// // Source: https://sqlite.org/c3ref/open.html -// pub fn init(kind: Kind) Error!Database { -// const name = switch (kind) { -// .temp_in_memory => ":memory:", -// .temp_on_disk => "", -// .path => |path| path, -// }; -// var handle: ?*sqlite.sqlite3 = null; -// -// const result: Result = .from(sqlite.sqlite3_open(name.ptr, &handle)); -// -// return if (result.isOk()) .{ .handle = handle.? } else result.toError(handle); -// } -// -// /// Closes the database and releases all of its resources, returning `.ok`. -// /// -// /// If there exist unfinalised prepared statements, BLOB handlers, and/or -// /// unfinished backup objects, `.unfinalised` is returned; -// /// -// /// See https://sqlite.org/c3ref/close.html -// pub fn deinit(self: *Database) Check { -// const result: Result = .from(sqlite.sqlite3_close(self.handle)); -// -// if (!result.isOk()) .unfinalised; -// -// self.* = undefined; -// -// return .ok; -// } -// -// // Source: https://sqlite.org/c3ref/prepare.html -// pub fn query(self: *Database, statement: []const u8) Error!Query { -// var handle: ?*sqlite.sqlite3_stmt = null; -// var unused: [*c]const u8 = null; -// -// const result: Result = .from(sqlite.sqlite3_prepare_v2(self.handle, statement.ptr, @intCast(statement.len), &handle, &unused)); -// -// return if (result.isOk()) .{ .handle = handle.? } else result.toError(self.handle); -// } -// }; -// } + // NOTE: Uniqueness of selected columns is enforced here. + const TagColumns = @Enum(TagIntColumns, .exhaustive, &column_names, &column_values); + const Selected = @Union(.auto, TagColumns, &column_names, &column_types, &@splat(.{})); -// pub const Database = struct { -// schema: Schema, -// -// pub fn foo(self: Database) void { -// _ = self; -// std.debug.print("asdf\n", .{}); -// } -// }; + return struct { + handle: *sqlite.sqlite3, + + filter: Filter = .none, + // sort: ..., // ORDER BY + take : Take = .unlimited, + unique: bool = false, + + pub const Filter = union(enum) { + none, + some: []const Node, + + const Offset = u16; + const Node = union(enum) { + binary: struct { + operator: enum { @"and", @"or" }, + lhs: Offset, + rhs: Offset, + }, + cmp: struct { + column : std.meta.Tag(Columns), + operator: Operator, + value : Affinity.Value, + fn serialise(self: @This(), writer: *Writer) Writer.Error!void { + try writer.writeAll(@tagName(self.column)); + try writer.writeByte(' '); + try self.operator.serialise(writer); + try writer.writeByte(' '); + try self.value.serialise(writer); + } + }, + }; + + pub fn @"and"(comptime lhs: Filter, comptime rhs: Filter) Filter { + const node: Node = .{ .binary = .{ .operator = .@"and", .lhs = 1, .rhs = lhs.nodes().len + 1 } }; + + return .{ .some = .{ node } ++ lhs.nodes() ++ rhs.nodes() }; + } + + pub fn cmp(comptime column: std.meta.Tag(Columns), operator: Operator, value: @FieldType(Columns, @tagName(column))) Filter { + const node: Node = .{ .cmp = .{ .column = column, .operator = operator, .value = .from(value) } }; + + return .{ .some = &.{ node } }; + } + + pub fn @"or"(comptime lhs: Filter, comptime rhs: Filter) Filter { + const node: Node = .{ .binary = .{ .operator = .@"or", .lhs = 1, .rhs = lhs.nodes().len + 1 } }; + + return .{ .some = .{ node } ++ lhs.nodes() ++ rhs.nodes() }; + } + + fn nodes(comptime self: Filter) []const Node { + return switch (self) { + .none => &.{}, + .some => |n| n, + }; + } + + // pub fn format(self: Filter, writer: *Writer) Writer.Error!void { + // const State = struct { + // index: usize = 0, + // node : ?*const Filter.Node = null, + // last_node: enum{ binary_expression, cmp, none } = .none, + // parentheses: isize = -1, + // + // const init: @This() = .{}; + // }; + // + // var state: State = .init; + // + // while (state.index < self.nodes.len) { + // switch (self.nodes[state.index]) { + // .@"and", .@"or" => { + // if (state.node) |node| { + // if (state.last_node == .cmp) { + // try writer.print(" {f} ", .{ node }); + // } + // } + // if (state.parentheses >= 0) try writer.writeByte('('); + // state.node = &self.nodes[state.index]; + // state.index += 1; + // state.last_node = .binary_expression; + // state.parentheses += 1; + // }, + // .cmp => { + // if (state.node) |node| { + // const index = node[0..1] - self.nodes.ptr; + // const parent = if (index > 0) &self.nodes[index - 1] else null; + // + // switch (node.*) { + // .@"and" => |value| { + // switch (self.nodes[index + value.lhs]) { + // .cmp => { + // try writer.print("{f}", .{ self.nodes[index + value.lhs] }); + // state.index += 1; + // }, + // else => {}, + // } + // try writer.print(" {f} ", .{ node }); + // switch (self.nodes[index + value.rhs]) { + // .cmp => { + // try writer.print("{f}", .{ self.nodes[index + value.rhs] }); + // state.index += 1; + // }, + // else => {}, + // } + // if (state.parentheses > 0) try writer.writeByte(')'); + // + // state.node = parent; + // state.parentheses -= 1; + // }, + // .@"or" => |value| { + // switch (self.nodes[index + value.lhs]) { + // .cmp => { + // try writer.print("{f}", .{ self.nodes[index + value.lhs] }); + // state.index += 1; + // }, + // else => {}, + // } + // try writer.print(" {f} ", .{ node }); + // switch (self.nodes[index + value.rhs]) { + // .cmp => { + // try writer.print("{f}", .{ self.nodes[index + value.rhs] }); + // state.index += 1; + // }, + // else => {}, + // } + // if (state.parentheses > 0) try writer.writeByte(')'); + // + // state.node = parent; + // state.parentheses -= 1; + // }, + // .cmp => unreachable, + // } + // } else { + // try writer.print("{f}", .{ self.nodes[state.index] }); + // state.index += 1; + // } + // state.last_node = .cmp; + // }, + // } + // } + // + // var index = state.parentheses; + // while (index > 0) : (index -= 1) { + // try writer.writeByte(')'); + // } + // } + }; + + pub const Limit = union(enum) { + const UInt = int: { + const info = @typeInfo(Affinity.Value.FieldType(.integer)); + break :int @Int(.unsigned, info.int.bits - @intFromBool(info.int.signedness == .signed)); + }; + + count : UInt, + offset: struct { + start: UInt, + count: UInt, + }, + }; + + const Columns = Selected; + const Self = @This(); + + const Take = union(enum) { + unlimited, + limit: Limit, + }; + + pub fn distinct(self: Self) Self { + if (self.unique) @panic("Duplicate `DISTINCT` constraint"); + + var this = self; + this.unique = true; + + return this; + } + + pub fn limit(self: Self, lim: Limit) Self { + if (self.take != .unlimited) @panic("Duplicate `LIMIT` constraint"); + + var this = self; + this.take = .{ .limit = lim }; + + return this; + } + + // FIXME: change return type to sql stmt and update error type + // TODO: return type must be SQLite stmt value; can error also be SQLite error? if so, make union with allocator.error + pub fn prepare(self: Self, allocator: Allocator) Allocator.Error!void { + var allocating_writer: std.Io.Writer.Allocating = .init(allocator); + defer allocating_writer.deinit(); + + self.serialise(&allocating_writer.writer) catch |err| switch (err) { + error.WriteFailed => return error.OutOfMemory, + }; + + const string = try allocating_writer.toOwnedSlice(); + defer allocator.free(string); + + std.debug.print("{s}\n", .{ string }); + } + + pub fn where(self: Self, comptime filter: Filter) Self { + if (self.filter != .none) @panic("Duplicate `WHERE` constraint"); + + var this = self; + this.filter = filter; + + return this; + } + + fn serialise(self: Self, writer: *Writer) Writer.Error!void { + try writer.writeAll("SELECT"); + + if (self.unique) { + try writer.writeAll(" DISTINCT"); + } + + const all_columns_selected: bool = columns.len == std.meta.fields(@FieldType(Schema, @tagName(from))).len; + + if (all_columns_selected) { + try writer.writeAll(" *"); + } else for (columns, 0..) |column, index| { + const separator = if (index == 0) " " else ", "; + + try writer.writeAll(separator); + try writer.writeAll(@tagName(column)); +} + + switch (self.filter) { + .none => {}, + .some => |nodes| { + try writer.writeAll(" WHERE "); + + const State = struct { + parent :?Filter.Offset = null, + current: Filter.Offset = 0, + depth : Filter.Offset = 0, + + const init: @This() = .{}; + }; + + var state: State = .init; + + while (state.current < nodes.len) { + switch (nodes[state.current]) { + .binary => { + if (state.parent) |parent| { + const operator = switch (nodes[parent]) { + .binary => |value| value.operator, + .cmp => unreachable, + }; + + // NOTE: This is always in bounds as the first node in `nodes` + // never has a parent, i.e. this branch is taken if and + // only if `state.current > 0` holds. + if (std.meta.activeTag(nodes[state.current - 1]) == .cmp) { + switch (operator) { + .@"and" => try writer.writeAll(" AND "), + .@"or" => try writer.writeAll(" OR "), + } + } + } + + state.parent = state.current; + state.current += 1; + state.depth += 1; + + if (state.depth > 1) try writer.writeByte('('); + }, + .cmp => |current| { + if (state.parent == null) { + try current.serialise(writer); + state.current += 1; + continue; + } + + const parent_index = state.parent.?; + const parent_has_parent = parent_index > 0 and std.meta.activeTag(nodes[parent_index - 1]) != .cmp; + const parent_of_parent = if (parent_has_parent) parent_index - 1 else null; + + switch (nodes[parent_index]) { + .binary => |parent| { + switch (nodes[parent_index + parent.lhs]) { + .cmp => |lhs| { + try lhs.serialise(writer); + state.current += 1; + }, + else => {}, + } + + switch (parent.operator) { + .@"and" => try writer.writeAll(" AND "), + .@"or" => try writer.writeAll(" OR "), + } + + switch (nodes[parent_index + parent.rhs]) { + .cmp => |rhs| { + try rhs.serialise(writer); + state.current += 1; + }, + else => {}, + } + + if (state.depth > 1) try writer.writeByte(')'); + + state.parent = parent_of_parent; + state.depth -= 1; + }, + .cmp => unreachable, + } + }, + } + } + + while (state.depth > 1) : (state.depth -= 1) { + try writer.writeByte(')'); + } + }, + } + + switch (self.take) { + .unlimited => {}, + .limit => |lim| switch (lim) { + .count => |value| { + const count: Affinity.Value = .{ .integer = value }; + + try writer.writeAll(" LIMIT "); + try count.serialise(writer); + }, + .offset => |value| { + const start: Affinity.Value = .{ .integer = value.start }; + const count: Affinity.Value = .{ .integer = value.count }; + + try writer.writeByte(' '); + try start.serialise(writer); + try writer.writeAll(", "); + try count.serialise(writer); + }, + }, + } + + try writer.writeByte(';'); + } + }; +} /// SQLite result codes. /// /// Primary result codes define a broad category of results, whereas extended @@ -430,40 +775,73 @@ const Result = enum(i32) { } }; +pub const Operator = enum { + lt, + leq, + eql, + neq, + geq, + gt, + + in, + not_in, + between, + is, + is_not, + + fn serialise(self: Operator, writer: *Writer) Writer.Error!void { + return switch (self) { + .lt => writer.writeAll("<"), + .leq => writer.writeAll("<="), + .eql => writer.writeAll("=="), + .neq => writer.writeAll("!="), + .geq => writer.writeAll(">="), + .gt => writer.writeAll(">"), + + .in => writer.writeAll("IN"), + .not_in => writer.writeAll("NOT IN"), + .between => writer.writeAll("BETWEEN"), + .is => writer.writeAll("IS"), + .is_not => writer.writeAll("IS NOT"), + }; + } +}; + test { + const allocator = std.testing.allocator; + const Schema = struct { contacts: Contacts, - foo: Foo, - - const Foo = struct { - bar: usize, - }; const Contacts = struct { - contact_id: usize, // CHANGEME: to `Type` - - const all = &.{.contact_id}; + // Read as "any column value is possible" + contact_id: Integer = .any, + // Read as "any column value defaults to 'John' and is not null" + first_name: Text = .notNull(.default(.any, "John")), + // Read as "any column value defaults to 'Doe' and is not null" + last_name : Text = .notNull(.default(.any, "Doe")), + // Read as "any column value is not null" + age : Integer = .notNull(.any), + // Read as "any column value defaults to '0.0' and is not null" + net_worth : Real = .notNull(.default(.any, 0.0)), }; }; var db: Database(Schema) = try .init(.temp_in_memory); defer assert(db.deinit() == .ok); - _ = db.select(.contacts, &.{.contact_id}, .all); - var ty = db.select(.contacts, Schema.Contacts.all, .all); - ty.table = .foo; + const query = try db.select(.contacts, &.{ .first_name , .last_name, .age }) + // TODO: convert to tests + // .where(.cmp(.first_name, .eql, "Bob")) + // .where(.@"and"(.cmp(.first_name, .eql, "Bob"), .cmp(.age, .geq, 42))) + // .where(.@"and"(.@"and"(.cmp(.first_name, .eql, "Bob"), .cmp(.age, .geq, 42)), .cmp(.last_name, .is_not, "Smith"))) + // .where(.@"or"(.@"and"(.cmp(.first_name, .eql, "Bob"), .cmp(.age, .geq, 42)), .@"and"(.cmp(.age, .eql, 69), .cmp(.last_name, .is_not, "Smith")))) + .where(.@"or"(.@"and"(.@"and"(.cmp(.first_name, .eql, "Bob"), .cmp(.age, .geq, 42)), .cmp(.last_name, .is_not, "Smith")), .cmp(.age, .eql, 69))) + .distinct() + .limit(.{ .count = 25 }) + .prepare(allocator); - // // SELECT * == SELECT ALL * - // db.select(.all).from(...) - // - // db.select(.distinct(...)).from(...)... - // - // // If the simple SELECT is a SELECT ALL, then the entire set of result rows are returned by the SELECT. If neither ALL or DISTINCT are present, then the behavior is as if ALL were specified. - // db.select(enum { all, distinct }, - // * == .all(), - // table-name.* == .table(.table_name), - // - // ) + _ = query; } // test { |