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 "data.h"
#include <sqlite3.h>
#include <gtk/gtk.h>
#include <unistd.h>
#include <string.h>
#include <stdlib.h>
#include <sys/stat.h>
#include <errno.h>
#include "collections.h"
static gboolean data_check_and_create_database(gchar *data_file);
static gint data_create_new_database(const char *filename);
GList *data_get_series(void)
{
sqlite3 *database;
sqlite3_stmt *statement;
gchar *data_file;
GList *list = NULL;
data_file = collections_get_data_file();
if (data_check_and_create_database(data_file)) {
if (sqlite3_open(data_file, &database) == SQLITE_OK) {
int res;
const char *sqlStatement =
" SELECT name, "
" current_qty, "
" total_qty "
" FROM collection "
" ORDER BY name "
" COLLATE NOCASE ";
res = sqlite3_prepare_v2(database,
sqlStatement,
strlen(sqlStatement),
&statement, NULL);
if (res == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
struct collection *col =
(struct collection *)malloc(sizeof(struct collection *));
col->name = g_strdup(sqlite3_column_text(statement, 0));
col->current_qty = sqlite3_column_int(statement, 1);
col->total_qty = sqlite3_column_int(statement, 2);
list = g_list_append(list, (gpointer)col);
}
}
else
g_print("error %d: %s\n", res, sqlite3_errmsg(database));
/* Release the compiled statement from memory */
sqlite3_finalize(statement);
}
sqlite3_close(database);
}
return list;
}
gboolean data_add_series(gchar *name, int total_qty)
{
sqlite3 *database;
sqlite3_stmt *statement;
gchar *data_file;
data_file = collections_get_data_file();
if (data_check_and_create_database(data_file)) {
if (sqlite3_open(data_file, &database) == SQLITE_OK) {
int res;
const char *sqlStatement =
g_strdup_printf("INSERT INTO collection (name, current_qty, total_qty) "
" VALUES ('%s', 0, %d)", name, total_qty);
res = sqlite3_prepare_v2(database,
sqlStatement,
strlen(sqlStatement),
&statement, NULL);
if (res == SQLITE_OK) {
if (sqlite3_step(statement) == SQLITE_DONE)
return TRUE;
}
}
}
return FALSE;
}
static gboolean data_check_and_create_database(gchar *data_file)
{
if (!access(data_file, R_OK) == 0)
if (data_create_new_database(data_file)) {
g_printerr("Couldn't create a new database\n");
return FALSE; /* Couldn't create database, can't continue */
}
return TRUE;
}
static gint data_create_new_database(const char *filename)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
if (access(collections_get_config_dir(), R_OK) == -1) {
if (mkdir(collections_get_config_dir(), S_IRWXU) == -1)
g_printerr("Can't create directory: %s\n", strerror(errno));
}
/* Open database connection, create file */
rc = sqlite3_open(filename, &db);
if (rc) {
g_printerr("Can't open database: %s\n", sqlite3_errmsg(db));
if (db)
sqlite3_close(db);
return -1;
}
/* Create collections table */
rc = sqlite3_exec(db, "CREATE TABLE collection("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(100),"
"current_qty INTEGER,"
"total_qty INTEGER"
")", NULL, NULL, &zErrMsg);
if (rc != SQLITE_OK) {
g_printerr("Can't create collection table: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
sqlite3_close(db);
return -1;
}
/* Create items table */
rc = sqlite3_exec(db, "CREATE TABLE item("
"collection_id INTEGER,"
"id INTEGER,"
"title VARCHAR(100),"
"PRIMARY KEY(collection_id, id),"
"FOREIGN KEY(collection_id)"
" REFERENCES collection(id)"
")", NULL, NULL, &zErrMsg);
if (rc != SQLITE_OK) {
g_printerr("Can't create item table: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
sqlite3_close(db);
return -1;
}
sqlite3_close(db);
return 0;
}
|
