使用zig语言制作简单博客网站（三）后端集成sqlite数据库

zig后端添加sqlite依赖

• sqlite依赖我们使用开源项目 zig-sqlite
• 在 build.zig.zon 文件中添加以下代码，COMMIT值我们可以点击仓库的Commits按钮查看复制然后替换
  
  

    .dependencies = .{
        .httpz = .{
            .url = "https://github.com/karlseguin/http.zig/archive/fbca868592dc83ee3ee3cad414c62afa266f4866.tar.gz",
            .hash = "122089946af5ba1cdfae3f515f0fa1c96327f42a462515fbcecc719fe94fab38d9b8",
        },

        // 添加sqlite依赖
        .sqlite = .{
            .url = "https://github.com/vrischmann/zig-sqlite/archive/COMMIT.tar.gz",
            // hash值是随便写的，为了占位（长度有要求），实际使用时需要替换成正确的hash值
            .hash = "122089946af5ba1cdfae3f515f0fa1c96327f42a462515fbcecc719fe94fab382222",
        },
    },


        // 替换后的COMMIT
        // .sqlite = .{
        //     .url = "https://github.com/vrischmann/zig-sqlite/archive/7f4be7d30b126affb66b390c7825addb1c3506bd.tar.gz",
        //     .hash = "122089946af5ba1cdfae3f515f0fa1c96327f42a462515fbcecc719fe94fab382222",
        // },

然后我们运行zig build下载sqlite依赖并获取到hash值替换，最终替换正确hash结果如下

    .dependencies = .{
        .httpz = .{
            .url = "https://github.com/karlseguin/http.zig/archive/fbca868592dc83ee3ee3cad414c62afa266f4866.tar.gz",
            .hash = "122089946af5ba1cdfae3f515f0fa1c96327f42a462515fbcecc719fe94fab38d9b8",
        },

        // 添加sqlite依赖
        .sqlite = .{
            .url = "https://github.com/vrischmann/zig-sqlite/archive/7f4be7d30b126affb66b390c7825addb1c3506bd.tar.gz",
            .hash = "1220940ae067451e7e6824e9b92baceac93b0bd6fa9ffd315179cc9a7ce5430a46ac",
        },
    },

• 将sqlite模块添加到 build.zig 文件中

    const sqlite_module = b.dependency("sqlite", .{
        .target = target,
        .optimize = optimize,
    });
    exe.root_module.addImport("sqlite", sqlite_module.module("sqlite"));
    exe.linkLibrary(sqlite_module.artifact("sqlite"));

创建sqlite数据库文件

• 我们使用 SQLiteStudio 在项目根目录下创建一个名为 db_zigblog.db 的数据库文件
  

• 我们先添加两张表user表和article表，并添加一些预置数据

数据库sql语句

// user表
CREATE TABLE user (
    id       INTEGER       PRIMARY KEY AUTOINCREMENT,
    username VARCHAR (20)  UNIQUE
                           NOT NULL,
    password VARCHAR (100) NOT NULL,
    nickname VARCHAR (20) 
);
INSERT INTO "user" VALUES (1, 'tingyu', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '听雨');

// 文章表
CREATE TABLE article (
    id          INTEGER        PRIMARY KEY AUTOINCREMENT,
    title       VARCHAR (1024) NOT NULL,
    description VARCHAR (1024),
    content     TEXT           NOT NULL,
    istop       INTEGER (2)    NOT NULL DEFAULT (0),
    created_at  DATETIME,
    updated_at  DATETIME
);


INSERT INTO "article" VALUES (1, 'ubuntu安装教程', '最详细的ubuntu安装教程', 'ubuntu安装教程，ubuntu安装教程，ubuntu安装教程', '2024-8-23 16:08:43', '2024-8-23 16:08:43');

// 分类表
CREATE TABLE category (
    id   INTEGER      PRIMARY KEY AUTOINCREMENT,
    name VARCHAR (20) NOT NULL UNIQUE
);

测试zig连接sqlite

• 先将我们刚才创建的数据库文件db_zigblog.db 复制到 zig-out\bin 目录下，main.zig中添加下面代码做sqlite连接测试，结果如图

    // 测试api，测试连接sqlite数据库
    router.get("/api/sqlite/user", &getSqliteOneUser);


// 测试api，测试连接sqlite数据库
fn getSqliteOneUser(req: *httpz.Request, res: *httpz.Response) !void {
    _ = req;
    res.status = 200;

    var db = try sqlite.Db.init(.{
        .mode = sqlite.Db.Mode{ .File = "db_zigblog.db" },
        .open_flags = .{},
        .threading_mode = .MultiThread,
    });

    const query =
        \\SELECT id, username, nickname FROM user WHERE id = ?
    ;

    var stmt = try db.prepare(query);
    defer stmt.deinit();

    var gpa = std.heap.GeneralPurposeAllocator(.{}){};
    const allocator = gpa.allocator();

    const row = try stmt.oneAlloc(struct {
        id: u32,
        username: []const u8,
        nickname: []const u8,
    }, allocator, .{}, .{
        .id = 1,
    });

    if (row) |r| {
        std.debug.print("userid: {d}, username: {s}, nickname: {s}", .{ r.id, r.username, r.nickname });

        try res.json(.{ .code = 200, .msg = "ok", .data = .{
            .id = r.id,
            .username = r.username,
            .nickname = r.nickname,
        } }, .{});
    } else {
        try res.json(.{ .code = 404, .msg = "not found" }, .{});
    }
}