1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
#include "db.h"
#include <stdlib.h>
#include <string.h>
#include <err.h>
#include <assert.h>
#include <sqlite3.h>
#include <unistd.h>
static int
busy_handler(void *dummy, int times_invoked)
{
(void)dummy;
uint32_t sleep_s = (times_invoked > 4 ? 4 : times_invoked) + 1;
warnx("sqlite3 db is busy :(... sleeping for %u seconds", sleep_s);
sleep(sleep_s);
return 1;
}
void
db_init(struct db *db, const char *target)
{
assert(db && target);
*db = (struct db){0};
if (sqlite3_open(target, (sqlite3**)&db->handle) != SQLITE_OK)
errx(EXIT_FAILURE, "sqlite3_open: %s", sqlite3_errmsg(db->handle));
if (sqlite3_busy_handler(db->handle, busy_handler, NULL) != SQLITE_OK)
errx(EXIT_FAILURE, "sqlite3_busy_handler");
}
static struct db_query_arg
stmt_column_to_arg(sqlite3_stmt *stmt, const int index)
{
assert(stmt);
switch (sqlite3_column_type(stmt, index)) {
case SQLITE_NULL:
return (struct db_query_arg){ .type = DB_ARG_NULL };
case SQLITE_INTEGER:
return (struct db_query_arg){ .u.i32 = sqlite3_column_int(stmt, index), .type = DB_ARG_I32 };
case SQLITE_FLOAT:
return (struct db_query_arg){ .u.f64 = sqlite3_column_double(stmt, index), .type = DB_ARG_F64 };
case SQLITE_BLOB:
return (struct db_query_arg){ .u.blob = { .data = sqlite3_column_blob(stmt, index), .sz = sqlite3_column_bytes(stmt, index) }, .type = DB_ARG_BLOB };
case SQLITE_TEXT:
return (struct db_query_arg){ .u.blob = { .data = sqlite3_column_text(stmt, index), .sz = sqlite3_column_bytes(stmt, index) }, .type = DB_ARG_UTF8 };
}
assert(0 && "should not happen");
return (struct db_query_arg){0};
}
static int
bind_arg_to_stmt(const struct db_query_arg *arg, const int index, sqlite3_stmt *stmt)
{
assert(arg && stmt);
switch (arg->type) {
case DB_ARG_NULL:
return sqlite3_bind_null(stmt, index);
case DB_ARG_I32:
return sqlite3_bind_int(stmt, index, arg->u.i32);
case DB_ARG_F64:
return sqlite3_bind_double(stmt, index, arg->u.f64);
case DB_ARG_BLOB:
if (arg->u.blob.sz && !arg->u.blob.data)
return sqlite3_bind_zeroblob(stmt, index, arg->u.blob.sz);
return sqlite3_bind_blob(stmt, index, arg->u.blob.data, arg->u.blob.sz, NULL);
case DB_ARG_UTF8:
assert(!arg->u.blob.sz || arg->u.blob.data);
return sqlite3_bind_text64(stmt, index, arg->u.blob.data, arg->u.blob.sz, NULL, SQLITE_UTF8);
}
assert(0 && "should not happen");
return SQLITE_ERROR;
}
struct db_query
db_query_begin(struct db *db, const char *sql, const struct db_query_arg args[], const int num_args)
{
assert(db && sql);
assert(args || !num_args);
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL) != SQLITE_OK)
errx(EXIT_FAILURE, "sqlite3_prepare_v2: %s", sqlite3_errmsg(db->handle));
for (int i = 0; i < num_args; ++i) {
if (bind_arg_to_stmt(&args[i], i + 1, stmt) != SQLITE_OK)
errx(EXIT_FAILURE, "sqlite3_bind: %s", sqlite3_errmsg(db->handle));
}
return (struct db_query){stmt};
}
int
db_query_fetch(struct db_query *query, struct db_query_arg columns[], const int num_columns)
{
assert(query);
assert(columns || !num_columns);
int ret;
if ((ret = sqlite3_step(query->handle)) != SQLITE_ROW && ret != SQLITE_DONE)
errx(EXIT_FAILURE, "sqlite3_step: %s", sqlite3_errstr(ret));
if (ret == SQLITE_DONE)
return 0;
int count = sqlite3_column_count(query->handle);
if (!num_columns)
return count;
count = (count > num_columns ? num_columns : count);
for (int i = 0; i < count; ++i)
columns[i] = stmt_column_to_arg(query->handle, i);
return count;
}
void
db_query_end(struct db_query *query)
{
if (!query)
return;
sqlite3_finalize(query->handle);
*query = (struct db_query){0};
}
int
db_query_single(struct db *db, const char *sql, const struct db_query_arg args[], const int num_args, struct db_query_arg columns[], const int num_columns)
{
db_query_end(&db->single);
db->single = db_query_begin(db, sql, args, num_args);
return db_query_fetch(&db->single, columns, num_columns);
}
void
db_gc(struct db *db)
{
assert(db);
db_query_end(&db->single);
}
void
db_release(struct db *db)
{
if (!db)
return;
db_query_end(&db->single);
sqlite3_close(db->handle);
*db = (struct db){0};
}
|
