#include #include #include #include "db.h" static sqlite3 *db = NULL; void db_close(); static int db_create_tables() { char *errmsg; int rc; rc = sqlite3_exec(db, "create table directory (" " id integer not null," " parent integer null," " name varchar(255)," " primary key (id)" ");" "create table wallpaper (" " id integer not null," " dirid integer not null," " date integer not null," " filepath text not null," " size integer not null," " width integer not null," " height integer not null," " primary key (id)" ");" "create table tag (" " id integer not null," " parent integer null," " name varchar(100) not null," " primary key (id)" ");" "create table walltags (" " wallid integer not null," " tagid integer not null," " primary key (wallid, tagid)" ");", NULL, NULL, &errmsg); if(rc != SQLITE_OK) { fprintf(stderr, "db_create_tables failed: %s\n", errmsg); sqlite3_free(errmsg); return 0; } return 1; } int db_open() { int rc; int datafound; char *dbfile; char *datadir; datadir = g_strdup_printf("%s/walls", g_get_user_data_dir()); if(access(datadir, F_OK) == -1 && g_mkdir_with_parents(datadir, 0700) == -1) { g_free(datadir); return 0; } dbfile = g_strdup_printf("%s/db", datadir); datafound = g_access(dbfile, F_OK) == 0; g_free(datadir); rc = sqlite3_open(dbfile, &db); g_free(dbfile); if(rc != SQLITE_OK) { return 0; } if(!datafound && !db_create_tables()) { db_close(); return 0; } return 1; } void db_close() { if(db) { sqlite3_close(db); db = NULL; } } sqlite_uint64 db_add_directory(const char *path, sqlite_uint64 parent) { sqlite3_stmt *stmt; int rc; if(parent) { rc = sqlite3_prepare_v2(db, "INSERT INTO directory (name, parent) VALUES (?, ?)", -1, &stmt, NULL); } else { rc = sqlite3_prepare_v2(db, "INSERT INTO directory (name) VALUES (?)", -1, &stmt, NULL); } if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_text(stmt, 1, path, -1, SQLITE_STATIC); if(rc != SQLITE_OK) { return 0; } if(parent) { rc = sqlite3_bind_int(stmt, 2, parent); if(rc != SQLITE_OK) { return 0; } } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) { return sqlite3_last_insert_rowid(db); } else { return 0; } } sqlite_uint64 db_get_directory(const char *path) { sqlite3_stmt *stmt; int rc; sqlite_uint64 dirid; rc = sqlite3_prepare_v2(db, "SELECT id FROM directory WHERE name = ? LIMIT 1", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_text(stmt, 1, path, -1, SQLITE_STATIC); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); if(rc == SQLITE_ROW) { dirid = sqlite3_column_int64(stmt, 0); sqlite3_finalize(stmt); return dirid; } sqlite3_finalize(stmt); return 0; } int db_get_top_level_directories(GArray **array) { struct directory_t temp, *temp2; sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT id, name FROM directory WHERE parent ISNULL ORDER BY name", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } *array = g_array_new(TRUE, FALSE, sizeof(struct directory_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.name = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.dirid = sqlite3_column_int64(stmt, 0); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; (*array)->len; i++) { temp2 = &g_array_index(*array, struct directory_t, i); g_free(temp2->name); } g_array_free(*array, TRUE); return 0; } return 1; } int db_get_directories(sqlite_uint64 parent, GArray **array) { struct directory_t temp, *temp2; sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT id, name FROM directory WHERE parent = ? ORDER BY name", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, parent); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } *array = g_array_new(TRUE, FALSE, sizeof(struct directory_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.name = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.dirid = sqlite3_column_int64(stmt, 0); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct directory_t, i); g_free(temp2->name); } g_array_free(*array, TRUE); return 0; } return 1; } sqlite_uint64 db_add_wallpaper(const char *filepath, sqlite_uint64 dirid, int size, int width, int height) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "INSERT INTO wallpaper (dirid, date, filepath, size, width, height) VALUES (?, datetime('now'), ?, ?, ?, ?)", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, dirid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_text(stmt, 2, filepath, -1, SQLITE_STATIC); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_int(stmt, 3, size); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_int(stmt, 4, width); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_int(stmt, 5, height); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) { return sqlite3_last_insert_rowid(db); } else { return 0; } } sqlite_uint64 db_get_wallpaper(const char *path) { sqlite3_stmt *stmt; int rc; sqlite_uint64 dirid; rc = sqlite3_prepare_v2(db, "SELECT id FROM wallpaper WHERE filepath = ? LIMIT 1", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_text(stmt, 1, path, -1, SQLITE_STATIC); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); if(rc == SQLITE_ROW) { dirid = sqlite3_column_int64(stmt, 0); sqlite3_finalize(stmt); return dirid; } sqlite3_finalize(stmt); return 0; } int db_remove_wallpaper(sqlite_uint64 id) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "DELETE FROM wallpaper WHERE id = ?", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, id); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); return rc == SQLITE_DONE; } int db_get_wallpaper_data(sqlite_uint64 id, struct wallpaper_t *wall) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT id, filepath, date, size, width, height FROM wallpaper WHERE id = ? LIMIT 1", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, id); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); if(rc == SQLITE_ROW) { wall->filepath = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); wall->id = sqlite3_column_int64(stmt, 0); if(sizeof(time_t) == 8) { wall->date = sqlite3_column_int64(stmt, 2); } else { wall->date = sqlite3_column_int(stmt, 2); } wall->size = sqlite3_column_int(stmt, 3); wall->width = sqlite3_column_int(stmt, 4); wall->height = sqlite3_column_int(stmt, 5); } sqlite3_finalize(stmt); return 1; } int db_get_wall_tags(sqlite_uint64 wallid, GArray **array) { struct tag_t temp, *temp2; sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT t.id, t.name, t.parent FROM tag t JOIN walltags w ON (w.tagid = t.id AND w.wallid = ?)", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, wallid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } *array = g_array_new(FALSE, FALSE, sizeof(struct tag_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.id = sqlite3_column_int64(stmt, 0); temp.parent = sqlite3_column_int64(stmt, 2); temp.name = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct tag_t, i); g_free(temp2->name); } g_array_free(*array, TRUE); return 0; } return 1; } int db_get_wallpapers(sqlite_uint64 dirid, GArray **array) { struct wallpaper_t temp, *temp2; sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT id, filepath, date, size, width, height FROM wallpaper WHERE dirid = ? ORDER BY filepath", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, dirid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } *array = g_array_new(TRUE, FALSE, sizeof(struct wallpaper_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.filepath = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.id = sqlite3_column_int64(stmt, 0); if(sizeof(time_t) == 8) { temp.date = sqlite3_column_int64(stmt, 2); } else { temp.date = sqlite3_column_int(stmt, 2); } temp.size = sqlite3_column_int(stmt, 3); temp.width = sqlite3_column_int(stmt, 4); temp.height = sqlite3_column_int(stmt, 5); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct wallpaper_t, i); g_free(temp2->filepath); } g_array_free(*array, TRUE); return 0; } return 1; } static gchar *gen_joinstring(int c) { char **split, *join; split = g_malloc(sizeof(gchar*) * (c+1)); split[c] = NULL; for(int i = 0; i < c; i++) { split[i] = "?"; } join = g_strjoinv(", ", split); g_free(split); return join; } /* * tags = uint64 * walls = struct tag_t * */ int db_get_walls_by_tags(GArray *tags, GArray **array) { sqlite3_stmt *stmt; int rc; char *join, *query; struct wallpaper_t temp, *temp2; join = gen_joinstring(tags->len); query = g_strdup_printf("SELECT w.id, w.filepath, w.date, w.size, w.width, w.height " "FROM wallpaper w JOIN walltags t ON (t.wallid = w.id) WHERE t.tagid IN (%s)", join); g_free(join); rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL); g_free(query); if(rc != SQLITE_OK) { return 0; } for(int i = 0; i < tags->len; i++) { sqlite_uint64 id; id = g_array_index(tags, sqlite_uint64, i); rc = sqlite3_bind_int64(stmt, i + 1, id); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } } *array = g_array_new(FALSE, FALSE, sizeof(struct wallpaper_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.filepath = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.id = sqlite3_column_int64(stmt, 0); if(sizeof(time_t) == 8) { temp.date = sqlite3_column_int64(stmt, 2); } else { temp.date = sqlite3_column_int(stmt, 2); } temp.size = sqlite3_column_int(stmt, 3); temp.width = sqlite3_column_int(stmt, 4); temp.height = sqlite3_column_int(stmt, 5); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct wallpaper_t, i); g_free(temp2->filepath); } g_array_free(*array, TRUE); return 0; } return 1; } sqlite_uint64 db_add_tag(const char *name, sqlite_uint64 parent) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "INSERT INTO tag (name, parent) VALUES (?, ?)", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } if(parent == 0) { rc = sqlite3_bind_null(stmt, 2); } else { rc = sqlite3_bind_int64(stmt, 2, parent); } if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) { return sqlite3_last_insert_rowid(db); } else { return 0; } } static void db_remove_tag_walltags(sqlite_uint64 tagid) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "DELETE FROM walltags WHERE tagid = ?", -1, &stmt, NULL); if(rc != SQLITE_OK) { return; } rc = sqlite3_bind_int64(stmt, 1, tagid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return; } sqlite3_step(stmt); sqlite3_finalize(stmt); } static void db_remove_tag_tag(sqlite_uint64 tagid) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "DELETE FROM tag WHERE id = ?", -1, &stmt, NULL); if(rc != SQLITE_OK) { return; } rc = sqlite3_bind_int64(stmt, 1, tagid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return; } sqlite3_step(stmt); sqlite3_finalize(stmt); } void db_remove_tag(sqlite_uint64 tagid) { db_remove_tag_walltags(tagid); db_remove_tag_tag(tagid); } int db_get_tags_all(GArray **array) { struct tag_t temp, *temp2; sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "SELECT id, name FROM tag ORDER BY name", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } *array = g_array_new(FALSE, FALSE, sizeof(struct tag_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.name = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.id = sqlite3_column_int64(stmt, 0); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct tag_t, i); g_free(temp2->name); } g_array_free(*array, TRUE); return 0; } return 1; } int db_get_top_level_tags(GArray **array) { return db_get_tags(array, 0); } int db_get_tags(GArray **array, sqlite3_uint64 parent) { struct tag_t temp, *temp2; sqlite3_stmt *stmt; int rc; if(parent == 0) { rc = sqlite3_prepare_v2(db, "SELECT id, name, parent FROM tag WHERE parent IS NULL ORDER BY name", -1, &stmt, NULL); } else { rc = sqlite3_prepare_v2(db, "SELECT id, name, parent FROM tag WHERE parent = ? ORDER BY name", -1, &stmt, NULL); } if(rc != SQLITE_OK) { return 0; } if(parent) { rc = sqlite3_bind_int64(stmt, 1, parent); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } } *array = g_array_new(FALSE, FALSE, sizeof(struct tag_t)); while((rc = sqlite3_step(stmt)) == SQLITE_ROW) { temp.name = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); temp.id = sqlite3_column_int64(stmt, 0); temp.parent = sqlite3_column_int64(stmt, 2); g_array_append_val(*array, temp); } sqlite3_finalize(stmt); if(rc != SQLITE_DONE) { for(int i = 0; i < (*array)->len; i++) { temp2 = &g_array_index(*array, struct tag_t, i); g_free(temp2->name); } g_array_free(*array, TRUE); return 0; } return 1; } int db_add_wall_tag(sqlite_uint64 wallid, sqlite_uint64 tagid) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "INSERT OR REPLACE INTO walltags (wallid, tagid) VALUES (?, ?)", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, wallid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_int64(stmt, 2, tagid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) { return 1; } else { return 0; } } int db_remove_wall_tag(sqlite_uint64 wallid, sqlite_uint64 tagid) { sqlite3_stmt *stmt; int rc; rc = sqlite3_prepare_v2(db, "DELETE FROM walltags WHERE wallid = ? AND tagid = ?", -1, &stmt, NULL); if(rc != SQLITE_OK) { return 0; } rc = sqlite3_bind_int64(stmt, 1, wallid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_bind_int64(stmt, 2, tagid); if(rc != SQLITE_OK) { sqlite3_finalize(stmt); return 0; } rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) { return 1; } else { return 0; } }