zzstoatzz.io
polls on atproto
pollz.waow.tech
atproto
zig
1const std = @import("std");
2const json = std.json;
3const mem = std.mem;
4const Thread = std.Thread;
5const Allocator = mem.Allocator;
6const zqlite = @import("zqlite");
7
8pub var conn: zqlite.Conn = undefined;
9pub var mutex: Thread.Mutex = .{};
10
11pub fn init(path: [*:0]const u8) !void {
12 std.debug.print("opening database at: {s}\n", .{path});
13 conn = zqlite.open(path, zqlite.OpenFlags.Create | zqlite.OpenFlags.ReadWrite) catch |err| {
14 std.debug.print("failed to open database: {}\n", .{err});
15 return err;
16 };
17 try initSchema();
18}
19
20pub fn close() void {
21 conn.close();
22}
23
24fn initSchema() !void {
25 mutex.lock();
26 defer mutex.unlock();
27
28 conn.execNoArgs(
29 \\CREATE TABLE IF NOT EXISTS polls (
30 \\ uri TEXT PRIMARY KEY,
31 \\ repo TEXT NOT NULL,
32 \\ rkey TEXT NOT NULL,
33 \\ text TEXT NOT NULL,
34 \\ options TEXT NOT NULL,
35 \\ created_at TEXT NOT NULL
36 \\)
37 ) catch |err| {
38 std.debug.print("failed to create polls table: {}\n", .{err});
39 return err;
40 };
41
42 conn.execNoArgs(
43 \\CREATE TABLE IF NOT EXISTS votes (
44 \\ uri TEXT PRIMARY KEY,
45 \\ subject TEXT NOT NULL,
46 \\ option INTEGER NOT NULL,
47 \\ voter TEXT NOT NULL,
48 \\ created_at TEXT,
49 \\ UNIQUE(subject, voter)
50 \\)
51 ) catch |err| {
52 std.debug.print("failed to create votes table: {}\n", .{err});
53 return err;
54 };
55
56 // add created_at column if it doesn't exist (migration for existing DBs)
57 conn.execNoArgs("ALTER TABLE votes ADD COLUMN created_at TEXT") catch {};
58
59 conn.execNoArgs(
60 \\CREATE INDEX IF NOT EXISTS idx_votes_subject ON votes(subject)
61 ) catch |err| {
62 std.debug.print("failed to create index: {}\n", .{err});
63 return err;
64 };
65
66 conn.execNoArgs(
67 \\CREATE INDEX IF NOT EXISTS idx_votes_voter ON votes(subject, voter)
68 ) catch |err| {
69 std.debug.print("failed to create voter index: {}\n", .{err});
70 return err;
71 };
72
73 std.debug.print("database schema initialized\n", .{});
74}
75
76pub fn insertPoll(uri: []const u8, did: []const u8, rkey: []const u8, text_json: []const u8, options_json: []const u8, created_at: []const u8) !void {
77 mutex.lock();
78 defer mutex.unlock();
79
80 conn.exec(
81 "INSERT OR IGNORE INTO polls (uri, repo, rkey, text, options, created_at) VALUES (?, ?, ?, ?, ?, ?)",
82 .{ uri, did, rkey, text_json, options_json, created_at },
83 ) catch |err| {
84 std.debug.print("db insert poll error: {}\n", .{err});
85 return err;
86 };
87}
88
89pub fn insertVote(uri: []const u8, subject: []const u8, option: i32, voter: []const u8, created_at: ?[]const u8) !void {
90 mutex.lock();
91 defer mutex.unlock();
92
93 // upsert: update if exists and new vote is newer, otherwise insert
94 // this handles out-of-order events from tap
95 conn.exec(
96 \\INSERT INTO votes (uri, subject, option, voter, created_at)
97 \\VALUES (?, ?, ?, ?, ?)
98 \\ON CONFLICT(subject, voter) DO UPDATE SET
99 \\ uri = excluded.uri,
100 \\ option = excluded.option,
101 \\ created_at = excluded.created_at
102 \\WHERE excluded.created_at > votes.created_at OR votes.created_at IS NULL
103 , .{ uri, subject, option, voter, created_at }) catch |err| {
104 std.debug.print("db insert vote error: {}\n", .{err});
105 return err;
106 };
107}
108
109pub fn deletePoll(uri: []const u8) void {
110 mutex.lock();
111 defer mutex.unlock();
112
113 conn.exec("DELETE FROM polls WHERE uri = ?", .{uri}) catch |err| {
114 std.debug.print("db delete poll error: {}\n", .{err});
115 };
116 // also delete associated votes
117 conn.exec("DELETE FROM votes WHERE subject = ?", .{uri}) catch |err| {
118 std.debug.print("db delete votes error: {}\n", .{err});
119 };
120}
121
122pub fn deleteVote(uri: []const u8) void {
123 mutex.lock();
124 defer mutex.unlock();
125
126 // only delete if the URI matches - if a newer vote replaced this one,
127 // the URI won't match and we should not delete
128 conn.exec("DELETE FROM votes WHERE uri = ?", .{uri}) catch |err| {
129 std.debug.print("db delete vote error: {}\n", .{err});
130 };
131}