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
|
#include <stdio.h>
#include <sqlite3.h>
#include "db_init.h"
static const char *SCHEMA =
"PRAGMA journal_mode = WAL;"
"PRAGMA busy_timeout = 5000;"
"CREATE TABLE IF NOT EXISTS urls ("
" id INTEGER PRIMARY KEY,"
" url TEXT UNIQUE NOT NULL,"
" status TEXT NOT NULL DEFAULT 'pending'"
" CHECK (status IN ('pending','in_progress','done','failed','skipped'))"
");"
"CREATE INDEX IF NOT EXISTS idx_urls_status ON urls(status);"
"CREATE TABLE IF NOT EXISTS links ("
" from_id INTEGER NOT NULL REFERENCES urls(id),"
" to_id INTEGER NOT NULL REFERENCES urls(id),"
" UNIQUE(from_id, to_id)"
");"
/* crash recovery: anything left mid-fetch from a previous run goes back to pending */
"UPDATE urls SET status = 'pending' WHERE status = 'in_progress';";
/* Opens (creating if needed) and initializes the DB. Returns NULL on failure. */
sqlite3 *db_init(const char *path) {
sqlite3 *db;
if (sqlite3_open(path, &db) != SQLITE_OK) {
fprintf(stderr, "db_init: cannot open %s: %s\n", path, sqlite3_errmsg(db));
sqlite3_close(db);
return NULL;
}
char *err = NULL;
if (sqlite3_exec(db, SCHEMA, NULL, NULL, &err) != SQLITE_OK) {
fprintf(stderr, "db_init: schema error: %s\n", err);
sqlite3_free(err);
sqlite3_close(db);
return NULL;
}
return db;
}
/* Each worker thread should open its own connection with the same PRAGMAs
* (WAL is persisted in the file, but busy_timeout is per-connection): */
sqlite3 *db_open_worker(const char *path) {
sqlite3 *db;
if (sqlite3_open(path, &db) != SQLITE_OK) {
fprintf(stderr, "db_open_worker: cannot open %s: %s\n", path, sqlite3_errmsg(db));
sqlite3_close(db);
return NULL;
}
sqlite3_exec(db, "PRAGMA busy_timeout = 5000;", NULL, NULL, NULL);
return db;
}
|
