DB : group manipulation

1 files changed, 239 insertions, 96 deletions

diff --git a/src/db.c b/src/db.c
index 504421c..f88a367 100644
--- a/src/db.c
+++ b/src/db.c
@@ -270,54 +270,14 @@ HAPI int db_create_table(sqlite3 *db)
 {
 	const char *TABLES[] = {
 		"CREATE TABLE IF NOT EXIST db_checksum (version INT);",
-		"CREATE TABLE IF NOT EXIST path_information ("
-			"path_id INTEGER PRIMARY KEY"
-			", user_id INTEGER NOT NULL"
-			", latitude DOUBLE NOT NULL"
-			", longitude DOUBLE NOT NULL"
+		"CREATE TABLE IF NOT EXIST group ("
+			"time INTEGER PRIMARY KEY"
 			", title TEXT"
-			", score INT"
-			", price INT"
-			", visible BOOL"
-			", FOREIGH KEY(user_id) REFERENCES user(user_id)"
-			");",
-		"CREATE TABLE IF NOT EXIST path ("
-			"path_id INTEGER NOT NULL"
-			", order INTEGER NOT NULL"
-			", content_id INTENGER NOT NULL"
-			", PRIMARY KEY(path_id, order)"
-			", FOREIGN KEY(path_id) REFERENCES path(path_id)"
-			", FOREIGH KEY(content_id) REFERENCES content(contend_id)",
-			");",
-		"CREATE TABLE IF NOT EXIST content ("
-			"content_id INTEGER PRIMARY KEY"
-			", user_id INTEGER NOT NULL"
-			", latitude DOUBLE NOT NULL"
-			", longitude DOUBLE NOT NULL"
-			", title TEXT"
-			", content TEXT"
-			", tag TEXT"
-			", location TEXT"
-			", icon TEXT"
-			", attach_id INTEGER"
-			", FOREIGN KEY(user_id) REFERENCES user(user_id)"
-			", FOREIGN KEY(attach_id) REFERENCES attach(attach_id)"
-			");",
-		"CREATE TABLE IF NOT EXIST attach ("
-			"attach_id INTEGER PRIMARY KEY"
-			", path TEXT NOT NULL"
-			", ref_count INT NOT NULL"
-			");",
-		"CREATE TABLE IF NOT EXIST purchase ("
-			"user_id INTEGER NOT NULL"
-			", content_id INTEGER NOT NULL"
-			", PRIMARY KEY(user_id, content_id)"
-			", FOREIGN KEY(user_id) REFERENCES user(user_id)"
-			", FOREIGN KEY(content_id) REFERENCES content(content_id)"
-			");",
-		"CREATE TABLE IF NOT EXIST user ("
-			"user_id INTEGER PRIMARY KEY"
-			", name TEXT"
+			", city1 INT"
+			", city2 INT"
+			", city3 INT"
+			", city4 INT"
+			", city5 INT"
 			");",
 	};
 	int count = 0;
@@ -338,12 +298,7 @@ HAPI int db_drop_table(sqlite3 *db)
 {
 	const char *TABLES[] = {
 		"DROP TABLE IF EXIST db_checksum;",
-		"DROP TABLE IF EXIST path_information;",
-		"DROP TABLE IF EXIST path;",
-		"DROP TABLE IF EXIST content;",
-		"DROP TABLE IF EXIST attach;",
-		"DROP TABLE IF EXIST purchase;",
-		"DROP TABLE IF EXIST user;",
+		"DROP TABLE IF EXIST group;",
 	};
 	int count = 0;
 	int i = 0;
@@ -361,97 +316,285 @@ HAPI int db_drop_table(sqlite3 *db)
 
 HAPI int db_insert_version(sqlite3 *db, int version)
 {
-	const char *QUERY_SYNTAX = "INSERT INTO db_checksum (version) values (%d);";
-	char *query = NULL;
+	const char *const QUERY_SYNTAX = "INSERT INTO db_checksum (version) VALUES (?);";
+	sqlite3_stmt *st = NULL;
 
-	query = sqlite3_mprintf(QUERY_SYNTAX, version);
-	retv_if(!query, APPL_ERROR_FAIL);
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_DB_FAILED);
 
-	if (db_exec(db, query) != APPL_ERROR_NONE) {
-		_E("Cannot execute query.[%s]", query);
-		sqlite3_free(query);
-		return APPL_ERROR_FAIL;
-	}
+	goto_if(_db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
 
-	sqlite3_free(query);
+	_db_reset(db, st);
+	_db_finalize(db, st);
 
-	/* keep the home DB opened */
+	/* keep this DB opened */
 
 	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_DB_FAILED;
 }
 
 
 
 HAPI int db_remove_version(sqlite3 *db, int version)
 {
-	const char *QUERY_SYNTAX = "DELETE FROM db_checksum WHERE version=%d;";
-	char *query = NULL;
+	const char *const QUERY_SYNTAX = "DELETE FROM db_checksum WHERE version = ?;";
+	sqlite3_stmt *st = NULL;
 
-	query = sqlite3_mprintf(QUERY_SYNTAX, version);
-	retv_if(!query, APPL_ERROR_FAIL);
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_DB_FAILED);
 
-	if (db_exec(db, query) != APPL_ERROR_NONE) {
-		_E("Cannot execute query.[%s]", query);
-		sqlite3_free(query);
-		return APPL_ERROR_FAIL;
-	}
+	goto_if(_db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
 
-	sqlite3_free(query);
+	_db_reset(db, st);
+	_db_finalize(db, st);
 
-	/* keep the home DB opened */
+	/* keep this DB opened */
 
 	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_DB_FAILED;
 }
 
 
 
 HAPI int db_update_version(sqlite3 *db, int version)
 {
-	const char *QUERY_SYNTAX = "UPDATE db_checksum SET version=%d;";
-	char *query = NULL;
+	const char *const QUERY_SYNTAX = "UPDATE db_checksum SET version = ?;";
+	sqlite3_stmt *st = NULL;
 
-	query = sqlite3_mprintf(QUERY_SYNTAX, version);
-	retv_if(!query, APPL_ERROR_FAIL);
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_DB_FAILED);
 
-	if (db_exec(db, query) != APPL_ERROR_NONE) {
-		_E("Cannot execute query.[%s]", query);
-		sqlite3_free(query);
-		return APPL_ERROR_FAIL;
-	}
+	goto_if(_db_bind_int(db, st, 1, version) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
 
-	sqlite3_free(query);
+	_db_reset(db, st);
+	_db_finalize(db, st);
 
-	/* keep the home DB opened */
+	/* keep this DB opened */
 
 	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_DB_FAILED;
 }
 
 
 
 HAPI int db_count_version(sqlite3 *db)
 {
-	const char *QUERY_SYNTAX = "SELECT COUNT(*) FROM db_checksum;";
+	const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM db_checksum;";
 	sqlite3_stmt *st = NULL;
 	int count = 0;
 
-	st = db_prepare(db, QUERY_SYNTAX);
-	retv_if(!st, APPL_ERROR_FAIL);
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_DB_FAILED);
 
-	if (db_next(db, st) == APPL_ERROR_FAIL) {
-		_E("db_next error");
-		db_finalize(db, st);
+	if (_db_next(db, st) == -1) {
+		_E("_db_next error");
+		_db_finalize(db, st);
 		return -1;
 	}
 
-	count = db_get_int(st, 0);
-	db_reset(db, st);
-	db_finalize(db, st);
+	count = _db_get_int(st, 0);
+	_db_reset(db, st);
+	_db_finalize(db, st);
 
-	/* keep the home DB opened */
+	/* keep this DB opened */
 
 	return count;
 }
 
 
 
+HAPI int _db_count_group(sqlite3 *db, int *count)
+{
+	const char *const QUERY_SYNTAX = "SELECT COUNT(*) FROM group;";
+	sqlite3_stmt *st = NULL;
+
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_FAIL);
+
+	goto_if(_db_next(db, st) == -1, error);
+
+	*count = _db_get_int(st, 0);
+
+	_db_reset(db, st);
+	_db_finalize(db, st);
+
+	/* keep this DB opened */
+
+	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_FAIL;
+}
+
+
+
+HAPI int _db_insert_group(sqlite3 *db, const char *title, int city1, int city2, int city3, int city4, int city5)
+{
+	const char *const QUERY_INSERT = "INSERT INTO group (time, title, city1, city2, city3, city4, city5) VALUES (DATETIME('now'), ?, ?, ?, ?, ?, ?);";
+	sqlite3_stmt *st = NULL;
+	int ret = APPL_ERROR_NONE;
+
+	st = _db_prepare(db, QUERY_INSERT);
+	retv_if(!st, APPL_ERROR_FAIL);
+
+	goto_if(_db_bind_str(db, st, 1, title) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 2, city1) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 3, city2) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 4, city3) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 5, city4) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 6, city5) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
+
+	_db_reset(db, st);
+	_db_finalize(db, st);
+
+	/* keep the sticker panel DB opened */
+
+	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_FAIL;
+}
+
+
+
+HAPI int _db_update_group(sqlite3 *db, int time, const char *title, int city1, int city2, int city3, int city4, int city5)
+{
+	const char *const QUERY_UPDATE = "UPDATE group SET title = ?, city1 = ?, city2 = ?, city3 = ?, city4 = ?, city5 = ? WHERE time = ?;";
+	sqlite3_stmt *st = NULL;
+	int count = 0;
+	int ret = APPL_ERROR_NONE;
+
+	st = _db_prepare(db, QUERY_UPDATE);
+	retv_if(!st, APPL_ERROR_FAIL);
+
+	goto_if(_db_bind_str(db, st, 1, title) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 2, city1) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 3, city2) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 4, city3) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 5, city4) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 6, city5) != APPL_ERROR_NONE, error);
+	goto_if(_db_bind_int(db, st, 7, time) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
+
+	_db_reset(db, st);
+	_db_finalize(db, st);
+
+	/* keep the sticker panel DB opened */
+
+	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_FAIL;
+}
+
+
+
+HAPI int _db_delete_group(sqlite3 *db, int time)
+{
+	const char *const QUERY_SYNTAX = "DELETE FROM group WHERE time = ?;";
+	sqlite3_stmt *st = NULL;
+
+	st = _db_prepare(db, QUERY_SYNTAX);
+	retv_if(!st, APPL_ERROR_FAIL);
+
+	goto_if(_db_bind_int(db, st, 1, time) != APPL_ERROR_NONE, error);
+	goto_if(_db_next(db, st) == -1, error);
+
+	_db_reset(db, st);
+	_db_finalize(db, st);
+
+	/* keep the sticker panel DB opened */
+
+	return APPL_ERROR_NONE;
+
+error:
+	_db_finalize(db, st);
+	return APPL_ERROR_FAIL;
+}
+
+
+
+struct _group_s {
+	char *title;
+	int time;
+	int city1;
+	int city2;
+	int city3;
+	int city4;
+	int city5;
+};
+typedef struct _group_s group_s;
+
+
+
+HAPI int _db_list_group(sqlite3 *db, Eina_List **group_list, int limit)
+{
+	const char *const QUERY_LIST = "SELECT time, title, city1, city2, city3, city4, city5 FROM group ORDER BY time ASC";
+	const char *id = NULL;
+	sqlite3_stmt *st = NULL;
+	group_s *group_info = NULL;
+
+	int type = 0;
+	int ret = -1;
+
+	st = _db_prepare(db, QUERY_LIST);
+	retv_if(!st, APPL_ERROR_FAIL);
+
+	do {
+		ret = _db_next(db, st);
+		if (SQLITE_DONE == ret) {
+			break;
+		} else if (-1 == ret) {
+			_E("_db_next() error");
+			goto error;
+		}
+
+		time = _db_get_int(st, 0);
+		title = _db_get_str(st, 1);
+		city1 = _db_get_int(st, 2);
+		city2 = _db_get_int(st, 3);
+		city3 = _db_get_int(st, 4);
+		city4 = _db_get_int(st, 5);
+		city5 = _db_get_int(st, 6);
+
+		group_info = group_info_create(time, title, city1, city2, city3, city4, city5);
+		continue_if(!group_info);
+
+		*group_list = eina_list_append(*group_list, group_info);
+	} while (SQLITE_ROW == ret);
+
+	_db_reset(db, st);
+	_db_finalize(db, st);
+
+	/* keep the sticker panel DB opened */
+
+	return APPL_ERROR_NONE;
+
+error:
+	EINA_LIST_FREE(*group_list, group_info) {
+		group_info_destroy(group_info);
+	}
+
+	_db_finalize(db, st);
+	return APPL_ERROR_FAIL;
+}
+
+
+
 // End of file.