From d0a9897bc07982224a928ca278437c9a568a81d1 Mon Sep 17 00:00:00 2001 From: Kaj-Michael Lang Date: Tue, 5 Feb 2008 13:47:42 +0200 Subject: [PATCH] Database helper changes. - Add error message output to db helpers. - Add db_exec_sql helper to exec raw sql - Update usage of db_exec in osm.c - Use helpers to exec the table and index creation sql statements. --- src/db.c | 77 +++++++++++++++++++++++++++++++++---------------------- src/db.h | 3 ++- src/osm.c | 28 +++++++++++--------- 3 files changed, 64 insertions(+), 44 deletions(-) diff --git a/src/db.c b/src/db.c index 1a6725f..92ef0d8 100644 --- a/src/db.c +++ b/src/db.c @@ -32,39 +32,39 @@ gchar *_mapper_db=NULL; void db_create_tables(sqlite3 *db) { -sqlite3_exec(db, OSM_TABLE_NODES,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_NODE_TAGS,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_TAGS,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_UPDATES,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_N2N,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_NAMES,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_PC,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_NAMES_NLS,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_WAY_REF,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_PLACES,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_POI,NULL, NULL, NULL); -sqlite3_exec(db, OSM_TABLE_POI_CATEGORY,NULL, NULL, NULL); +db_exec_sql(db, OSM_TABLE_NODES); +db_exec_sql(db, OSM_TABLE_NODE_TAGS); +db_exec_sql(db, OSM_TABLE_WAY); +db_exec_sql(db, OSM_TABLE_WAY_TAGS); +db_exec_sql(db, OSM_TABLE_WAY_UPDATES); +db_exec_sql(db, OSM_TABLE_WAY_N2N); +db_exec_sql(db, OSM_TABLE_WAY_NAMES); +db_exec_sql(db, OSM_TABLE_WAY_PC); +db_exec_sql(db, OSM_TABLE_WAY_NAMES_NLS); +db_exec_sql(db, OSM_TABLE_WAY_REF); +db_exec_sql(db, OSM_TABLE_PLACES); +db_exec_sql(db, OSM_TABLE_POI); +db_exec_sql(db, OSM_TABLE_POI_CATEGORY); } void db_create_indexes(sqlite3 *db) { -sqlite3_exec(db, OSM_INDEX_1, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_2, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_3, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_4, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_5, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_6, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_7, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_8, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_9, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_10, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_11, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_12, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_13, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_14, NULL, NULL, NULL); -sqlite3_exec(db, OSM_INDEX_15, NULL, NULL, NULL); +db_exec_sql(db, OSM_INDEX_1); +db_exec_sql(db, OSM_INDEX_2); +db_exec_sql(db, OSM_INDEX_3); +db_exec_sql(db, OSM_INDEX_4); +db_exec_sql(db, OSM_INDEX_5); +db_exec_sql(db, OSM_INDEX_6); +db_exec_sql(db, OSM_INDEX_7); +db_exec_sql(db, OSM_INDEX_8); +db_exec_sql(db, OSM_INDEX_9); +db_exec_sql(db, OSM_INDEX_10); +db_exec_sql(db, OSM_INDEX_11); +db_exec_sql(db, OSM_INDEX_12); +db_exec_sql(db, OSM_INDEX_13); +db_exec_sql(db, OSM_INDEX_14); +db_exec_sql(db, OSM_INDEX_15); } /** @@ -87,17 +87,32 @@ sqlite3_free_table(pszResult); return TRUE; } +gboolean +db_exec_sql(sqlite3 *db, const gchar *sql) +{ +gint r; + +r=sqlite3_exec(db, sql, NULL, NULL, NULL); +if (r!=SQLITE_OK) + g_printerr("SQL ERROR:(%s) %s\n", sql, sqlite3_errmsg(db)); + +return (r==SQLITE_OK) ? TRUE : FALSE; +} + /** * Helper that execs a given prepared sql statment and resets clears bindings. * return TRUE or FALSE depending on success of the query. * */ gboolean -db_exec(sqlite3_stmt *sql) +db_exec(sqlite3 *db, sqlite3_stmt *sql) { gint r; +g_assert(sql); r=sqlite3_step(sql); +if (r!=SQLITE_OK) + g_printerr("SQL ERROR: %s\n", sqlite3_errmsg(db)); sqlite3_reset(sql); sqlite3_clear_bindings(sql); @@ -112,7 +127,6 @@ gboolean db_close(sqlite3 **db) { if (db && *db) { - g_print("Closing database.\n"); sqlite3_close(*db); *db=NULL; } @@ -135,6 +149,7 @@ if (!mapper_db) return FALSE; if (SQLITE_OK!=(sqlite3_open(mapper_db, db))) { + g_printerr("SQL OPEN: %s\n", sqlite3_errmsg(db)); sqlite3_close(*db); *db=NULL; return FALSE; @@ -142,7 +157,7 @@ if (SQLITE_OK!=(sqlite3_open(mapper_db, db))) { /* Use smaller cache as the IT does not have much memory to spare */ #ifdef WITH_DEVICE_770 -sqlite3_exec(*db, "PRAGMA cache_size = 1000;", NULL, NULL, NULL); +sqlite3_exec(*db, "PRAGMA cache_size = 2000;", NULL, NULL, NULL); #else sqlite3_exec(*db, "PRAGMA cache_size = 8000;", NULL, NULL, NULL); #endif diff --git a/src/db.h b/src/db.h index 2277039..94ff03b 100644 --- a/src/db.h +++ b/src/db.h @@ -30,7 +30,8 @@ gchar *_mapper_db; gboolean db_connect(sqlite3 **db, const gchar *mapper_db); gboolean db_check(sqlite3 *db, const gchar *table); gboolean db_close(sqlite3 **db); -gboolean db_exec(sqlite3_stmt *sql); +gboolean db_exec_sql(sqlite3 *db, const gchar *sql); +gboolean db_exec(sqlite3 *db, sqlite3_stmt *sql); void db_create_tables(sqlite3 *db); void db_create_indexes(sqlite3 *db); diff --git a/src/osm.c b/src/osm.c index 53f9bcc..3b2fc12 100644 --- a/src/osm.c +++ b/src/osm.c @@ -527,12 +527,16 @@ db_insert_node(node *n) gint32 lat, lon; g_assert(n); -sqlite3_bind_int(sql.insert_node, 1, n->id); lat=lat2mp_int(n->lat); lon=lon2mp_int(n->lon); + +sqlite3_bind_int(sql.insert_node, 1, n->id); + +/* Projected and integerized lat/lot * sqlite3_bind_int(sql.insert_node, 2, lat); sqlite3_bind_int(sql.insert_node, 3, lon); +/* Original */ sqlite3_bind_double(sql.insert_node, 4, n->lat); sqlite3_bind_double(sql.insert_node, 5, n->lon); sqlite3_bind_int(sql.insert_node, 6, n->type); @@ -558,7 +562,7 @@ sqlite3_bind_text(sql.insert_place, 3, n->data->name, -1, SQLITE_TRANSIENT); sqlite3_bind_int(sql.insert_place, 4, n->data->isin_p); sqlite3_bind_int(sql.insert_place, 5, n->data->isin_c); -return db_exec(sql.insert_place); +return db_exec(db,sql.insert_place); } static gboolean @@ -584,7 +588,7 @@ if (n->data->url) if (n->data->postal_code) sqlite3_bind_text(sql.insert_poi, 11, n->data->postal_code, -1, SQLITE_TRANSIENT); -return db_exec(sql.insert_poi); +return db_exec(db,sql.insert_poi); } /** @@ -596,7 +600,7 @@ db_update_node_links(node *n) g_assert(n); sqlite3_bind_int(sql.update_node, 1, n->id); -return db_exec(sql.update_node); +return db_exec(db,sql.update_node); } /** @@ -628,7 +632,7 @@ sqlite3_bind_int(sql.insert_way_n2n, 3, nt->id); g_printf("%d [%d - %d]\n", w->id, nf->id, nt->id); #endif -db_exec(sql.insert_way_n2n); +db_exec(db,sql.insert_way_n2n); db_update_node_links(nf); db_update_node_links(nt); return TRUE; @@ -654,7 +658,7 @@ if (w->data->ref) if (w->data->int_ref) sqlite3_bind_text(sql.insert_way_ref, 3, w->data->int_ref, -1, SQLITE_TRANSIENT); -db_exec(sql.insert_way_ref); +db_exec(db,sql.insert_way_ref); } /** @@ -684,14 +688,14 @@ if (norm) g_free(norm); #endif -db_exec(sql.insert_way_name); +db_exec(db,sql.insert_way_name); } static void db_delete_way_names_nls(way *w) { sqlite3_bind_int(sql.delete_way_names_nls, 1, w->id); -db_exec(sql.delete_way_names_nls); +db_exec(db,sql.delete_way_names_nls); } static void @@ -705,14 +709,14 @@ if (!w->data->postal_code) sqlite3_bind_int(sql.insert_way_pc, 1, w->id); sqlite3_bind_text(sql.insert_way_pc, 2, w->data->postal_code, -1, SQLITE_TRANSIENT); -db_exec(sql.insert_way_pc); +db_exec(db,sql.insert_way_pc); } static void db_delete_way_pc(way *w) { sqlite3_bind_int(sql.delete_way_pc, 1, w->id); -db_exec(sql.delete_way_pc); +db_exec(db,sql.delete_way_pc); } static void @@ -733,7 +737,7 @@ if (norm && strcmp((gchar *)value, norm)!=0) { if (norm) g_free(norm); #endif -db_exec(sql.insert_way_names_nls); +db_exec(db,sql.insert_way_names_nls); } static void @@ -808,7 +812,7 @@ if (wmn) { g_printerr("Failed to get way location node!\n"); } -db_exec(sql.insert_way_data); +db_exec(db,sql.insert_way_data); db_insert_way_ref(w); db_insert_way_name(w); -- 2.39.5