From 95fdbaddf4af1e687f0539c63741da7c1f17706d Mon Sep 17 00:00:00 2001 From: Kaj-Michael Lang Date: Tue, 18 Mar 2008 19:50:25 +0200 Subject: [PATCH] Use array of table and index definitions. Add helper db function to exec arrays of sql statements. Create tables and indexes in osm_init --- src/db.c | 46 ++++++++---------------------- src/db.h | 4 +-- src/osm-db-import.c | 69 ++++++++++++++++++++++++++++----------------- src/osm-db.c | 49 ++++++++++++++++++++++++++++++++ src/osm-db.h | 2 ++ 5 files changed, 107 insertions(+), 63 deletions(-) diff --git a/src/db.c b/src/db.c index 6fe268c..19f24bc 100644 --- a/src/db.c +++ b/src/db.c @@ -29,42 +29,20 @@ sqlite3 *_db=NULL; gchar *_mapper_db=NULL; -void -db_create_tables(sqlite3 *db) +gboolean +db_exec_sql_array(sqlite3 *db, const gchar **sql) { -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); +gboolean r=TRUE; +char **d; + +d=sql; +while (*d!=NULL) { + g_debug("[%s]", *d); + if (db_exec_sql(db, *d)==FALSE) + r=FALSE; + d++; } - -void -db_create_indexes(sqlite3 *db) -{ -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); +return r; } /** diff --git a/src/db.h b/src/db.h index 0949279..99607af 100644 --- a/src/db.h +++ b/src/db.h @@ -32,11 +32,9 @@ gboolean db_check(sqlite3 *db, const gchar *table); gboolean db_close(sqlite3 **db); gboolean db_exec_sql(sqlite3 *db, const gchar *sql); gboolean db_exec(sqlite3 *db, sqlite3_stmt *sql); +gboolean db_exec_sql_array(sqlite3 *db, const gchar **sql); gboolean db_transaction_begin(sqlite3 *db); gboolean db_transaction_commit(sqlite3 *db); -void db_create_tables(sqlite3 *db); -void db_create_indexes(sqlite3 *db); - #endif diff --git a/src/osm-db-import.c b/src/osm-db-import.c index fc94004..6d5a0e7 100644 --- a/src/osm-db-import.c +++ b/src/osm-db-import.c @@ -352,7 +352,7 @@ static gboolean use_bbox; static void osm_free_way_data(way *w); static void print_way(way *w); -static void db_prepare(sqlite3 *db); +static gboolean osm_db_prepare(sqlite3 *db); static gboolean db_insert_node(node *n); static guint32 osm_find_way_place(way *w, node_type_t nt); @@ -390,8 +390,8 @@ sqlite3_finalize(sql.delete_way_names_nls); sqlite3_finalize(sql.insert_way_names_nls); } -static void -db_prepare(sqlite3 *db) +static gboolean +osm_db_prepare(sqlite3 *db) { /* Way nodes */ sqlite3_prepare_v2(db, "insert or replace into nodes (nid,ilat,ilon,rlat,rlon,l,f) values (?,?,?,?,?,0,?)", -1, &sql.insert_node, NULL); @@ -433,6 +433,8 @@ sqlite3_prepare_v2(db, "delete from way_names_nls where wid=?", -1, &sql.delete_ /* Way ref and int_ref */ sqlite3_prepare_v2(db, "insert or replace into way_ref (rid,ref,int_ref) values (?, ?, ?)", -1, &sql.insert_way_ref, NULL); sqlite3_prepare_v2(db, "delete from way_ref", -1, &sql.delete_way_ref, NULL); + +return TRUE; } /********************************************************************/ @@ -616,8 +618,6 @@ return db_exec(db,sql.insert_way_ref); static gboolean db_insert_way_name(way *w) { -gchar *norm; - if (!w->data) return TRUE; if (!w->data->name) @@ -629,12 +629,15 @@ sqlite3_bind_int(sql.insert_way_name, 1, w->id); sqlite3_bind_text(sql.insert_way_name, 2, w->data->name, -1, SQLITE_TRANSIENT); #ifdef TRANSLIT_NAMES -norm=g_convert(w->data->name, -1, "ASCII//TRANSLIT//IGNORE", "utf8", NULL, NULL, NULL); -if (norm && strcmp(w->data->name, norm)!=0) { - sqlite3_bind_text(sql.insert_way_name, 3, norm, -1, SQLITE_TRANSIENT); +{ + gchar *norm; + norm=g_convert(w->data->name, -1, "ASCII//TRANSLIT//IGNORE", "utf8", NULL, NULL, NULL); + if (norm && strcmp(w->data->name, norm)!=0) { + sqlite3_bind_text(sql.insert_way_name, 3, norm, -1, SQLITE_TRANSIENT); + } + if (norm) + g_free(norm); } -if (norm) - g_free(norm); #endif return db_exec(db,sql.insert_way_name); @@ -671,20 +674,21 @@ return db_exec(db,sql.delete_way_pc); static void db_insert_way_names_nls_cb(gpointer key, gpointer value, gpointer user_data) { -gchar *norm; - way *w=(way *)user_data; sqlite3_bind_int(sql.insert_way_names_nls, 1, w->id); sqlite3_bind_text(sql.insert_way_names_nls, 2, (gchar *)key, -1, SQLITE_TRANSIENT); sqlite3_bind_text(sql.insert_way_names_nls, 3, (gchar *)value, -1, SQLITE_TRANSIENT); #ifdef TRANSLIT_NAMES -norm=g_convert((gchar *value), -1, "ASCII//TRANSLIT//IGNORE", "utf8", NULL, NULL, NULL); -if (norm && strcmp((gchar *)value, norm)!=0) { - sqlite3_bind_text(sql.insert_way_names_nls, 4, norm, -1, SQLITE_TRANSIENT); +{ + gchar *norm; + norm=g_convert((gchar *value), -1, "ASCII//TRANSLIT//IGNORE", "utf8", NULL, NULL, NULL); + if (norm && strcmp((gchar *)value, norm)!=0) { + sqlite3_bind_text(sql.insert_way_names_nls, 4, norm, -1, SQLITE_TRANSIENT); + } + if (norm) + g_free(norm); } -if (norm) - g_free(norm); #endif db_exec(db,sql.insert_way_names_nls); } @@ -1126,8 +1130,10 @@ node *n=(node *)value; dbnode_cnt++; db_insert_node(n); -if (dbnode_cnt % 26214==0) - g_printf("\rNodes: %f%%\n",((float)dbnode_cnt/(float)node_cnt)*100); +if (dbnode_cnt % 20000==0) + g_printf("\rNodes: %f%% (%u/%u)\n", + ((float)dbnode_cnt/(float)node_cnt)*100, + dbnode_cnt, node_cnt); } /** @@ -1210,8 +1216,10 @@ osm_way_save(way *value, gpointer user_data) { dbway_cnt++; db_insert_way(value); -if (dbway_cnt % 16384==0 && dbway_cnt>0) { - g_printf("\rWays: %f%%\n",(((float)dbway_cnt/(float)way_cnt)*100)); +if (dbway_cnt % 15000==0 && dbway_cnt>0) { + g_printf("\rWays: %f%% (%u/%u)\n", + (((float)dbway_cnt/(float)way_cnt)*100), + dbway_cnt, way_cnt); print_way(value); } } @@ -1375,8 +1383,9 @@ t=check_tag(name); switch (t) { case IN_NODE_TAG: - if (node_cnt % 262140==0) { - g_printf("Nodes: %d of %d, POIs: %d, Outside box: %d\n", node_cnt-node_skip_cnt, node_cnt, noded_cnt, node_skip_cnt); + if (node_cnt % 25000==0) { + g_printf("Nodes: %u of %u, POIs: %u, Outside box: %u\n", + node_cnt-node_skip_cnt, node_cnt, noded_cnt, node_skip_cnt); } if (!osm_node_tags) @@ -1748,9 +1757,17 @@ if (db_connect(&db, database)!=TRUE) { return FALSE; } -db_create_tables(db); -db_create_indexes(db); -db_prepare(db); + +if (!osm_db_create(db)) { + g_printerr("Failed to create osm tables or indexes\n"); + return FALSE; +} + +if (!osm_db_prepare(db)) { + g_printerr("Failed to prepare sql statements\n"); + return FALSE; +} + osm_planet_parser_init(); if (osm_planet_parse_file(planet)==FALSE) { diff --git a/src/osm-db.c b/src/osm-db.c index 28d218b..5f21868 100644 --- a/src/osm-db.c +++ b/src/osm-db.c @@ -32,8 +32,10 @@ #include "osm.h" #include "latlon.h" +#include "db.h" #include "osm-db.h" #include "settings.h" +#include "osm-sql-tables.h" /* #define DEBUG_OSM */ /* #define DEBUG_OSM_TIME */ @@ -74,6 +76,40 @@ static struct sql_select_stmt sql; static GTimer *dbt; static GtkProgressBar *dbpw=NULL; +static const gchar *osm_tables[]={ + OSM_TABLE_NODES, + OSM_TABLE_NODE_TAGS, + OSM_TABLE_WAY, + OSM_TABLE_WAY_TAGS, + OSM_TABLE_WAY_UPDATES, + OSM_TABLE_WAY_N2N, + OSM_TABLE_WAY_NAMES, + OSM_TABLE_WAY_PC, + OSM_TABLE_WAY_NAMES_NLS, + OSM_TABLE_WAY_REF, + OSM_TABLE_PLACES, + OSM_TABLE_POI, + OSM_TABLE_POI_CATEGORY, + NULL}; + +static const gchar *osm_indexes[]={ + OSM_INDEX_1, + OSM_INDEX_2, + OSM_INDEX_3, + OSM_INDEX_4, + OSM_INDEX_5, + OSM_INDEX_6, + OSM_INDEX_7, + OSM_INDEX_8, + OSM_INDEX_9, + OSM_INDEX_10, + OSM_INDEX_11, + OSM_INDEX_12, + OSM_INDEX_13, + OSM_INDEX_14, + OSM_INDEX_15, + NULL }; + /* Cache hash tables */ struct osm_place_cache { GHashTable *cache; @@ -90,6 +126,14 @@ osm_way_node *osm_way_get_next_node(osm_way *w); /*****************************************************************************/ +gboolean +osm_db_create(sqlite3 *db) +{ +return db_exec_sql_array(db, osm_tables) && db_exec_sql_array(db, osm_indexes); +} + +/*****************************************************************************/ + void osm_set_way_range(guint sr) { @@ -302,6 +346,11 @@ if (!db || !*db) { osmdb=*db; memset(&sql, 0, sizeof(sql)); +if (osm_db_create(osmdb)==FALSE) { + g_printerr("Failed to create OSM tables or indexes: %s", sqlite3_errmsg(osmdb)); + return FALSE; +} + if (osm_db_prepare(osmdb)==FALSE) { g_printerr("Failed to prepare OSM SQL statements: %s", sqlite3_errmsg(osmdb)); return FALSE; diff --git a/src/osm-db.h b/src/osm-db.h index f443532..c3c0cc9 100644 --- a/src/osm-db.h +++ b/src/osm-db.h @@ -48,6 +48,8 @@ typedef enum { gboolean osm_init(sqlite3 **db); void osm_deinit(void); +gboolean osm_db_create(sqlite3 *db); + GSList *osm_get_route_node(guint nid, osm_node_direction d); void osm_set_way_range_from_speed(gfloat speed); -- 2.39.5