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
|
#include <asm-generic/errno-base.h>
#include <stdio.h>
#include <sqlite3.h>
#include <sys/stat.h>
#include <unistd.h>
#include "config.h"
#include "db.h"
/* return values:
0 -> success
1 -> failure
*/
int create_tables(const char *path);
int db_init() {
int res;
char db_path[BUF_LEN];
// create USER_DATA_DIR if not exists
res = mkdir(USER_DATA_DIR, S_IRWXU);
//if(res != 0 && res != EEXIST) return 1; // TODO write a better check incase mkdir fails
// return if db already exists
res = access(USER_DATA_PATH, F_OK);
if(res == 0) return 0;
// create database with tables
res = create_tables(USER_DATA_PATH);
if(res != 0) return 2;
return 0;
}
int create_tables(const char *path) {
sqlite3 *db;
char queries[2][BUF_LEN];
int num_queries = 2;
char *zErrMsg = NULL; // hold error from sqlite3_exec
int res;
int i;
// Group table - stores groups of entries
sprintf(queries[0],
"CREATE TABLE IF NOT EXISTS 'Group' ("
"'ID' int NOT NULL PRIMARY KEY, "
"'Name' varchar(%d) NOT NULL, "
"'Desc' varchar(%d) DEFAULT NULL, "
"'Url' varchar(%d) DEFAULT NULL);",
BUF_LEN, BUF_LEN, BUF_LEN);
// Entry table - stores individual entries
sprintf(queries[1],
"CREATE TABLE IF NOT EXISTS 'Entry' ("
"'ID' int NOT NULL PRIMARY KEY, "
"'Group_ID' int NOT NULL, " // FIXME might want foreign key constraint
"'Due_date' date DEFAULT NULL, "
"'Alt_due_date' varchar(%d) DEFAULT NULL, "
"'Title' varchar(%d) NOT NULL, "
"'Color' varchar(%d) DEFAULT NULL, "
"'Highlight' varchar(%d) DEFAULT NULL, "
"'Done' boolean DEFAULT NULL, "
"'Url' varchar(%d) DEFAULT NULL);",
BUF_LEN, BUF_LEN, BUF_LEN, BUF_LEN, BUF_LEN);
// open the database and run each query
res = sqlite3_open(path, &db);
if(res) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
for(i = 0; i < num_queries; ++i) {
res = sqlite3_exec(db, queries[i], NULL, NULL, &zErrMsg);
if(res != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
}
// wrap-up
sqlite3_close(db);
return 0;
}
|
