sqlite3.zig

const std = @import("std");

const sqlite3 = *@Type(.Opaque);
const sqlite3_stmt = *@Type(.Opaque);

const sqllog = std.log.scoped(.sqlite3);

extern fn sqlite3_errmsg(db: sqlite3) [*:0]const u8;
extern fn sqlite3_open(path: [*:0]const u8, db: *sqlite3) c_int;
extern fn sqlite3_close(db: sqlite3) c_int;
extern fn sqlite3_exec(
    db: sqlite3,
    sql: [*:0]const u8,
    callback: ?fn (
        user: ?*c_void,
        row: c_int,
        data: [*:null]const ?[*:0]const u8,
        columns: [*:null]const ?[*:0]const u8,
    ) callconv(.C) c_int,
    user: ?*c_void,
    errmsg: ?*?[*:0]const u8,
) c_int;
extern fn sqlite3_prepare(
    db: sqlite3,
    sql: [*]const u8,
    nbytes: c_int,
    stmt: *sqlite3_stmt,
    tail: ?*[*:0]const u8,
) c_int;
extern fn sqlite3_finalize(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_reset(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_step(stmt: sqlite3_stmt) c_int;
extern fn sqlite3_clear_bindings(stmt: sqlite3_stmt) c_int;

extern fn sqlite3_bind_parameter_index(stmt: sqlite3_stmt, name: [*:0]const u8) c_int;
extern fn sqlite3_bind_blob(stmt: sqlite3_stmt, pos: c_int, data: [*]const u8, len: u32, de: ?fn (ptr: *c_void) callconv(.C) void) c_int;
extern fn sqlite3_bind_zeroblob(stmt: sqlite3_stmt, pos: c_int, len: u32) c_int;
extern fn sqlite3_bind_zeroblob64(stmt: sqlite3_stmt, pos: c_int, len: u64) c_int;
extern fn sqlite3_bind_double(stmt: sqlite3_stmt, pos: c_int, data: f64) c_int;
extern fn sqlite3_bind_int(stmt: sqlite3_stmt, pos: c_int, data: i32) c_int;
extern fn sqlite3_bind_int64(stmt: sqlite3_stmt, pos: c_int, data: i64) c_int;
extern fn sqlite3_bind_null(stmt: sqlite3_stmt, pos: c_int) c_int;
extern fn sqlite3_bind_text(stmt: sqlite3_stmt, pos: c_int, data: [*]const u8, len: u32, de: ?fn (ptr: *c_void) callconv(.C) void) c_int;

extern fn sqlite3_column_blob(stmt: sqlite3_stmt, pos: c_int) [*]const u8;
extern fn sqlite3_column_double(stmt: sqlite3_stmt, pos: c_int) f64;
extern fn sqlite3_column_int(stmt: sqlite3_stmt, pos: c_int) i32;
extern fn sqlite3_column_int64(stmt: sqlite3_stmt, pos: c_int) i64;
extern fn sqlite3_column_text(stmt: sqlite3_stmt, pos: c_int) [*]const u8;
extern fn sqlite3_column_bytes(stmt: sqlite3_stmt, pos: c_int) c_int;
extern fn sqlite3_column_type(stmt: sqlite3_stmt, pos: c_int) ColumnType;

pub const Database = struct {
    raw: sqlite3,

    fn reportError(self: @This()) anyerror {
        std.log.err("{}", .{sqlite3_errmsg(self.raw)});
        return error.DbError;
    }

    pub fn open(path: [*:0]const u8) !@This() {
        var ret: @This() = .{ .raw = undefined };
        const res = sqlite3_open(path, &ret.raw);
        if (res != 0) return error.DbError;
        return ret;
    }

    pub fn close(self: @This()) void {
        const res = sqlite3_close(self.raw);
        if (res != 0) @panic("sqlite3_close error");
    }

    pub fn exec(self: @This(), sql: []const u8) !void {
        sqllog.info("exec {}", .{sql});
        var stmt = try self.prepare(sql);
        defer stmt.deinit();
        try stmt.exec();
    }

    pub fn prepare(self: @This(), sql: []const u8) !PreparedStatement {
        sqllog.info("prepare {}", .{sql});
        var ret: PreparedStatement = .{ .raw = undefined };
        const res = sqlite3_prepare(self.raw, sql.ptr, @intCast(c_int, sql.len), &ret.raw, null);
        if (res != 0) return self.reportError();
        return ret;
    }
};

pub const TransportStrategy = union(enum) {
    const FuncType = fn (data: *c_void) callconv(.C) void;

    static: void,
    transient: void,
    dynamic: FuncType,

    fn get(self: @This()) ?FuncType {
        return switch (self) {
            .static => null,
            .transient => @intToPtr(FuncType, @bitCast(usize, @as(isize, -1))),
            .dynamic => |ptr| ptr,
        };
    }
};

pub const ColumnType = extern enum {
    int = 1, float = 2, text = 3, blob = 4, @"null" = 5
};

pub const ColumnValue = union(ColumnType) {
    int: i64,
    float: f64,
    text: []const u8,
    blob: []const u8,
    @"null": void,

    fn asOptional(self: @This(), comptime T: type) !?T {
        return switch (T) {
            i32 => switch (self) {
                .int => |val| std.math.cast(i32, val),
                .float => |val| @floatToInt(i32, val),
                .text, .blob => error.WrongType,
                .@"null" => @as(?T, null),
            },
            u32 => switch (self) {
                .int => |val| try std.math.cast(u32, val),
                .float => |val| @floatToInt(u32, val),
                .text, .blob => error.WrongType,
                .@"null" => @as(?T, null),
            },
            i64 => switch (self) {
                .int => |val| val,
                .float => |val| @floatToInt(i64, val),
                .text, .blob => error.WrongType,
                .@"null" => @as(?T, null),
            },
            f32 => switch (self) {
                .int => |val| @intToFloat(f32, val),
                .float => |val| @floatCast(f32, val),
                .text, .blob => error.WrongType,
                .@"null" => @as(?T, null),
            },
            f64 => switch (self) {
                .int => |val| @intToFloat(f64, val),
                .float => |val| @floatCast(f64, val),
                .text, .blob => error.WrongType,
                .@"null" => @as(?T, null),
            },
            bool => switch (self) {
                .int => |val| val != 0,
                .float => |val| val != 0,
                .text, .blob => |val| val.len != 0,
                .@"null" => false,
            },
            []const u8 => switch (self) {
                .int => error.WrongType,
                .float => error.WrongType,
                .text, .blob => |val| val,
                .@"null" => @as(?T, null),
            },
            else => @compileError("Unhandled type: " ++ @typeName(T)),
        };
    }

    pub fn as(self: @This(), comptime T: type) !T {
        return switch (@typeInfo(T)) {
            .Optional => |opt| self.asOptional(opt.child),
            else => (try self.asOptional(T)) orelse error.NullValue,
        };
    }
};

fn ArrChild(comptime T: type) type {
    return switch (@typeInfo(T)) {
        .Pointer => |ptr| ArrChild(ptr.child),
        .Array => |arr| ArrChild(arr.child),
        else => T,
    };
}

pub const PreparedStatement = struct {
    raw: sqlite3_stmt,

    pub fn deinit(self: @This()) void {
        const res = sqlite3_finalize(self.raw);
        if (res != 0) @panic("sqlite3_close error");
    }

    fn bindNotNull(self: @This(), idx: c_int, data: anytype) !void {
        const res = switch (@TypeOf(data)) {
            comptime_int => if (data <= std.math.maxInt(i32))
                sqlite3_bind_int(self.raw, idx, data)
            else
                sqlite3_bind_int64(self.raw, idx, data),
            i32 => sqlite3_bind_int(self.raw, idx, data),
            i64 => sqlite3_bind_int64(self.raw, idx, data),
            f32, f64, comptime_float => sqlite3_bind_double(self.raw, idx, data),
            else => @compileError("not support type"),
        };
        if (res != 0) return error.DbError;
    }

    pub fn bind(self: @This(), idx: c_int, data: anytype) !void {
        if (@typeInfo(@TypeOf(data)) == .Optional) {
            if (data) |notnull| {
                return self.bindNotNull(idx, notnull);
            } else {
                const res = sqlite3_bind_null(self.raw, idx);
                if (res != 0) return error.DbError;
            }
        } else {
            return self.bindNotNull(idx, data);
        }
    }

    pub fn bindNull(self: @This(), idx: c_int) !void {
        const res = sqlite3_bind_null(self.raw, idx);
        if (res != 0) return error.DbError;
    }

    pub fn bindText(self: @This(), idx: c_int, data: ?[]const u8, strategy: TransportStrategy) !void {
        const res = if (data) |notnull|
            sqlite3_bind_text(self.raw, idx, notnull.ptr, @intCast(u32, notnull.len), strategy.get())
        else
            sqlite3_bind_null(self.raw, idx);
        if (res != 0) return error.DbError;
    }

    pub fn bindBlob(self: @This(), idx: c_int, data: ?[]const u8, strategy: TransportStrategy) !void {
        const res = if (data) |notnull|
            sqlite3_bind_blob(self.raw, idx, notnull.ptr, @intCast(u32, notnull.len), strategy.get())
        else
            sqlite3_bind_null(self.raw, idx);
        if (res != 0) return error.DbError;
    }

    pub fn bindSlice(self: @This(), slice: []const ColumnValue, strategy: TransportStrategy) !void {
        for (slice) |v, oi| {
            const i = @intCast(c_int, oi + 1);
            try switch (v) {
                .int => |val| self.bind(i, val),
                .float => |val| self.bind(i, val),
                .text => |val| self.bindText(i, val, strategy),
                .blob => |val| self.bindBlob(i, val, strategy),
                .@"null" => |val| self.bindNull(i),
            };
        }
    }

    pub fn Binder(comptime T: type) type {
        const defs = std.meta.fields(T);
        return struct {
            stmt: PreparedStatement,
            cache: [defs.len]c_int = undefined,

            fn init(self: *@This()) !void {
                inline for (defs) |field, i| {
                    const idx = sqlite3_bind_parameter_index(self.stmt.raw, "$" ++ field.name);
                    if (idx == 0) return error.InvalidField;
                    self.cache[i] = idx;
                }
            }

            fn bindNotNull(self: *const @This(), i: c_int, value: anytype, strategy: TransportStrategy) !void {
                return switch (@TypeOf(value)) {
                    ColumnValue => |v| switch (v) {
                        .int => |val| self.stmt.bind(i, val),
                        .float => |val| self.stmt.bind(i, val),
                        .text => |val| self.stmt.bindText(i, val, strategy),
                        .blob => |val| self.stmt.bindBlob(i, val, strategy),
                        .@"null" => |val| self.stmt.bindNull(i),
                    },
                    comptime_int => self.stmt.bind(i, @as(i64, value)),
                    comptime_float => self.stmt.bind(i, @as(f64, value)),
                    i32, i64, f32, f64 => self.stmt.bind(i, value),
                    u32 => self.stmt.bind(i, @intCast(i64, value)),
                    []const u8 => self.stmt.bindText(i, value, strategy),
                    else => switch (@typeInfo(@TypeOf(value))) {
                        .Pointer => |ptr| switch (@typeInfo(ptr.child)) {
                            .Array => |arr| if (arr.child == u8) self.stmt.bindText(i, value, strategy) else @compileError("Unknown type: " ++ @typeName(@TypeOf(value))),
                            else => @compileError("Unknown type: " ++ @typeName(@TypeOf(value))),
                        },
                        else => @compileError("Unknown type: " ++ @typeName(@TypeOf(value))),
                    },
                };
            }

            fn bindNullable(self: *const @This(), i: c_int, value: anytype, strategy: TransportStrategy) !void {
                return switch (@typeInfo(@TypeOf(value))) {
                    .Optional => if (value) |nn| self.bindNotNull(i, nn, strategy) else self.stmt.bindNull(i),
                    else => self.bindNotNull(i, value, strategy),
                };
            }

            pub fn bindObj(self: *const @This(), obj: T, strategy: TransportStrategy) !void {
                inline for (defs) |field, i| {
                    const value = @field(obj, field.name);
                    const idx = self.cache[i];
                    try self.bindNullable(idx, value, strategy);
                }
            }

            pub fn execObj(self: *const @This(), obj: T) !void {
                defer self.stmt.clear() catch {};
                inline for (defs) |field, i| {
                    const value = @field(obj, field.name);
                    const idx = self.cache[i];
                    try self.bindNullable(idx, value, .static);
                }
                defer self.stmt.reset() catch {};
                try self.stmt.exec();
            }
        };
    }

    pub fn getBinder(self: @This(), comptime T: type) !Binder(T) {
        var ret: Binder(T) = .{ .stmt = self };
        try ret.init();
        return ret;
    }

    pub fn bindAll(self: @This(), val: anytype, strategy: TransportStrategy) !void {
        const binder = try self.getBinder(@TypeOf(val));
        try binder.bindObj(val, strategy);
    }

    pub fn execAll(self: @This(), val: anytype) !void {
        const binder = try self.getBinder(@TypeOf(val));
        try binder.execObj(val);
    }

    pub fn execAllMulti(self: @This(), arr: anytype) !void {
        const binder = try self.getBinder(ArrChild(@TypeOf(arr)));
        for (arr) |item| try binder.execObj(item);
    }

    pub fn step(self: @This()) !bool {
        return switch (sqlite3_step(self.raw)) {
            0 => @panic("Unexpected code"),
            100 => return true,
            101 => return false,
            else => return error.DbError,
        };
    }

    pub fn exec(self: @This()) !void {
        if (try self.step()) return error.UnexpectedRow;
    }

    pub fn reset(self: @This()) !void {
        const res = sqlite3_reset(self.raw);
        if (res != 0) return error.DbError;
    }

    pub fn clear(self: @This()) !void {
        const res = sqlite3_clear_bindings(self.raw);
        if (res != 0) return error.DbError;
    }

    pub fn get(self: @This(), idx: c_int) ColumnValue {
        return switch (sqlite3_column_type(self.raw, idx)) {
            .int => ColumnValue{ .int = sqlite3_column_int64(self.raw, idx) },
            .float => ColumnValue{ .float = sqlite3_column_double(self.raw, idx) },
            .text => blk: {
                const ret = sqlite3_column_text(self.raw, idx);
                const len = sqlite3_column_bytes(self.raw, idx);
                break :blk ColumnValue{ .text = ret[0..@intCast(usize, len)] };
            },
            .blob => blk: {
                const ret = sqlite3_column_blob(self.raw, idx);
                const len = sqlite3_column_bytes(self.raw, idx);
                break :blk ColumnValue{ .blob = ret[0..@intCast(usize, len)] };
            },
            .@"null" => .@"null",
        };
    }

    pub fn iter(self: @This(), comptime Template: type) Iterator(Template) {
        return .{ .stmt = self };
    }

    pub fn next(self: @This(), comptime Template: type) !?Template {
        if (!try self.step())
            return null;

        var ret: Template = undefined;
        comptime const defs = std.meta.fields(Template);
        inline for (defs) |field, i| {
            @field(ret, field.name) = try self.get(i).as(field.field_type);
        }
        return ret;
    }
};