const std = @import("std");
const sqlite = @import("sqlite");
const Pad = @import("Pad.zig");
const UserExpires = @import("UserExpires.zig");
pub const schema_writer =
\\CREATE TABLE IF NOT EXISTS writer(
\\ username TEXT PRIMARY KEY,
\\ password TEXT NOT NULL
\\);
;
pub const schema_session =
\\CREATE TABLE IF NOT EXISTS session(
\\ token TEXT PRIMARY KEY,
\\ expires INTEGER NOT NULL,
\\ writer_username TEXT NOT NULL,
\\ FOREIGN KEY(writer_username) REFERENCES writer(username) ON DELETE CASCADE ON UPDATE CASCADE,
\\ UNIQUE(writer_username, token)
\\);
;
// basic hash: empty means there is none. server will store as hash<u+:+p>
// views_left: at query time, 0 means do not delete. if == 1, after fetching, will be deleted. if > 1, will be fetched
// delete_at: 0 means do not delete
pub const schema_pad =
\\CREATE TABLE IF NOT EXISTS pad(
\\ slug TEXT NOT NULL UNIQUE PRIMARY KEY CHECK(slug != '' AND slug != 'login' AND slug != 'logout' AND slug != 'signup'),
\\ content TEXT NOT NULL,
\\ basic_hash TEXT NOT NULL DEFAULT '',
\\ views_left INTEGER NOT NULL DEFAULT 0 CHECK (views_left >= 0),
\\ delete_at INTEGER NOT NULL DEFAULT 0 CHECK (delete_at >= 0),
\\ writer_username TEXT NOT NULL,
\\ FOREIGN KEY(writer_username) REFERENCES writer(username) ON DELETE CASCADE ON UPDATE CASCADE
\\);
;
pub const Db = extern struct {
const Self = @This();
db: *sqlite.Db,
pub fn new(all: std.mem.Allocator, db: *sqlite.Db) !*Self {
var it = try all.create(Self);
it.db = db;
return it;
}
pub fn migrate(self: *Self) !void {
try self.db.exec(schema_writer, .{}, .{});
try self.db.exec(schema_session, .{}, .{});
try self.db.exec(schema_pad, .{}, .{});
}
pub fn deletePad(self: *Self, slug: []const u8, username: []const u8) !void {
try self.db.exec("DELETE FROM pad WHERE slug = $slug{[]const u8} AND writer_username = $username{[]const u8}", .{}, .{
.slug = slug,
.username = username,
});
}
pub fn deletePadSlug(self: *Self, slug: []const u8) !void {
try self.db.exec("DELETE FROM pad WHERE slug = $slug{[]const u8}", .{}, .{
.slug = slug,
});
}
pub fn setPadViewsLeft(self: *Self, slug: []const u8, views_left: usize) !void {
try self.db.exec("UPDATE pad SET VIEWS_LEFT = $views_left{usize} WHERE slug = $slug{[]const u8}", .{}, .{
.views_left = views_left,
.slug = slug,
});
}
pub fn padWhereSlug(self: *Self, all: std.mem.Allocator, slug: []const u8) !?Pad {
return try self.db.oneAlloc(Pad, all, "SELECT slug, content, basic_hash, views_left, delete_at, writer_username FROM pad WHERE slug = $slug{[]const u8}", .{}, .{
.slug = slug,
});
}
pub fn userExpires(self: *Self, all: std.mem.Allocator, auth: []const u8) !?UserExpires {
const result = try self.db.oneAlloc(UserExpires, all, "SELECT s.expires, s.writer_username AS username FROM session s WHERE s.token = $token{[]const u8}", .{}, .{
.token = auth,
});
return result;
}
// stored as rounds:salt:hashed
pub fn getUserPassword(self: *Self, all: std.mem.Allocator, username: []const u8) !?[]const u8 {
const userPassword = try self.db.oneAlloc([]const u8, all, "SELECT password FROM writer WHERE username = $username{[]const u8};", .{}, .{
.username = username,
});
return userPassword;
}
pub fn addSession(self: *Self, username: []const u8, token: []const u8, expires: usize) !void {
try self.db.exec("INSERT INTO session (writer_username, token, expires) VALUES ($username{[]const u8}, $token{[]const u8}, $expires{usize});", .{}, .{
.username = username,
.token = token,
.expires = expires,
});
}
pub fn removeSessionToken(self: Self, token: []const u8) !void {
try self.db.exec("DELETE FROM session WHERE token = $token{[]const u8}", .{}, .{
.token = token,
});
}
pub fn getOwnPads(self: *Self, all: std.mem.Allocator, username: []const u8) ![]Pad {
var pads_stmt = try self.db.prepare("SELECT slug, content, basic_hash, views_left, delete_at, writer_username FROM pad WHERE writer_username = $username{[]const u8}");
defer pads_stmt.deinit();
const user_pads = try pads_stmt.all(Pad, all, .{}, .{
.username = username,
});
return user_pads;
}
pub fn addPad(self: *Self, slug: []const u8, content: []const u8, basic_hash: []const u8, views_left: usize, delete_at: usize, writer_username: []const u8) !void {
try self.db.exec(
\\INSERT INTO pad (slug, content, basic_hash, views_left, delete_at, writer_username) VALUES
\\($slug{[]const u8}, $content{[]const u8}, $basic_hash{[]const u8},
\\$views_left{usize}, $delete_at{usize}, $writer_username{[]const u8});
, .{}, .{
.slug = slug,
.content = content,
.basic_hash = basic_hash,
.views_left = views_left,
.delete_at = delete_at,
.writer_username = writer_username,
});
}
pub fn deleteUser(self: *Self, username: []const u8) !void {
try self.db.exec("DELETE FROM writer WHERE username = $username{[]const u8}", .{}, .{
.username = username,
});
// sqlite version may or may not honor delete cascades.
try self.db.exec("DELETE FROM session WHERE writer_username = $username{[]const u8}", .{}, .{
.username = username,
});
try self.db.exec("DELETE FROM pad WHERE writer_username = $username{[]const u8}", .{}, .{
.username = username,
});
}
pub fn addUser(self: *Self, username: []const u8, password: []const u8) !void {
try self.db.exec("INSERT INTO writer (username, password) VALUES ($username{[]const u8}, $password{[]const u8});", .{}, .{
.username = username,
.password = password,
});
}
};