From bd5762a9c35a04ab8327f340f20ac26cae6d3b2b Mon Sep 17 00:00:00 2001 From: Kaj-Michael Lang Date: Tue, 15 Apr 2008 13:06:14 +0300 Subject: [PATCH] Use helper macro to prepare sql statements. --- src/poi.c | 232 ++++++++++++++++++++++++++---------------------------- 1 file changed, 113 insertions(+), 119 deletions(-) diff --git a/src/poi.c b/src/poi.c index 1a1b02f..cd6845e 100644 --- a/src/poi.c +++ b/src/poi.c @@ -131,9 +131,8 @@ poi_populate_categories(sqlite3 *db) sqlite3_stmt *sql_cat; guint i; -sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)" - " values (?, ?, ?, 1, ?, ?, ?)", - -1, &sql_cat, NULL); +DB_PREP(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)" + " values (?, ?, ?, 1, ?, ?, ?)", sql_cat); for (i=0; default_poi_categories[i].name; i++) { sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type); @@ -171,8 +170,7 @@ poi_db_create(sqlite3 *db) gchar **pszResult; guint nRow, nColumn; -if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1", - &pszResult, &nRow, &nColumn, NULL)) { +if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1", &pszResult, &nRow, &nColumn, NULL)) { gchar *create_sql; g_printerr("Creating initial POI tables\n"); @@ -243,120 +241,116 @@ typedef enum { gboolean poi_db_prepare(sqlite3 *db) { - /* Select POIs inside given minmax lat,lon */ - if (sqlite3_prepare_v2(db, - "select " - POI_BASE_SQL_FIELDS - " from poi p, category c " - " where p.lat between ? and ? " - " and p.lon between ? and ? " - " and c.enabled=1 and p.cat_id=c.cat_id order by c.priority limit 500", - -1, &poisql.select_poi, NULL)!=SQLITE_OK) - return FALSE; - - /* Get POI with given ID */ - if (sqlite3_prepare_v2(db, - "select " - POI_BASE_SQL_FIELDS - " from poi p, category c " - " where p.poi_id = ? " - " and p.cat_id=c.cat_id", - -1, &poisql.select_poi_by_id, NULL)!=SQLITE_OK) - return FALSE; - - /* Search POIs by label and any category */ - if (sqlite3_prepare_v2(db, - "select " - POI_BASE_SQL_FIELDS - " from poi p, category c " - " where p.lat between ? and ? " - " and p.lon between ? and ? " - " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) order by p.label, c.label", - -1, &poisql.select_poi_search, NULL)!=SQLITE_OK) - return FALSE; - - /* Search POI by label and category */ - if (sqlite3_prepare_v2(db, - "select " - POI_BASE_SQL_FIELDS - " from poi p, category c " - " where p.lat between ? and ? " - " and p.lon between ? and ? " - " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) and c.cat_id = ? order by p.label", - -1, &poisql.select_poi_search_cat, NULL)!=SQLITE_OK) - return FALSE; - - /* Search POIs by category */ - if (sqlite3_prepare_v2(db, - "select " - POI_BASE_SQL_FIELDS - " from poi p, category c " - " where p.lat between ? and ? " - " and p.lon between ? and ? " - " and c.enabled=1 and p.cat_id=c.cat_id and c.cat_id=? order by p.label", - -1, &poisql.select_poi_by_cat, NULL)!=SQLITE_OK) - return FALSE; - - /* Select any nearest POI */ - if (sqlite3_prepare_v2(db, - "select " - POI_MINI_SQL_FIELDS - " from poi p, category c " - " where c.enabled = 1 and p.cat_id = c.cat_id " - " and p.lat between $LAT-0.10 and $LAT+0.10 " - " and p.lon between $LON-0.10 and $LAT+0.10 " - " order by (($LAT - p.lat) * ($LAT - p.lat) " - "+ ($LON - p.lon) * ($LON - p.lon)) limit 1", - -1, &poisql.select_nearest_poi, NULL)!=SQLITE_OK) - return FALSE; - - /* Insert POI */ - sqlite3_prepare_v2(db, "insert into poi (lat, lon, label, desc, url, postal_code, cat_id, addtime, public, source)" - " values (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)", -1, &poisql.insert_poi, NULL); - /* update poi */ - sqlite3_prepare_v2(db, "update poi set label=?, desc=?, cat_id=? where poi_id=?", -1, &poisql.update_poi, NULL); - /* delete from poi */ - sqlite3_prepare_v2(db, "delete from poi where poi_id=?", -1, &poisql.delete_poi, NULL); - /* delete from poi by cat_id */ - sqlite3_prepare_v2(db, "delete from poi where cat_id=?", -1, &poisql.delete_poi_by_catid, NULL); - - /* select from category */ - sqlite3_prepare_v2(db, - "select c.label, c.desc, c.enabled" - " from category c where c.cat_id = ?", - -1, &poisql.select_cat, NULL); - /* insert into category */ - sqlite3_prepare_v2(db, - "insert into category (label, desc, enabled)" - " values (?, ?, ?)", -1, &poisql.insert_cat, NULL); - /* update category */ - sqlite3_prepare_v2(db, - "update category set label = ?, desc = ?," - " enabled = ? where poi_id = ?", - -1, &poisql.update_cat, NULL); - /* delete from category */ - sqlite3_prepare_v2(db,"delete from category where cat_id = ?", - -1, &poisql.delete_cat, NULL); - /* enable category */ - sqlite3_prepare_v2(db, - "update category set enabled = ?" - " where cat_id = ?", -1, &poisql.toggle_cat, NULL); - /* select all category */ - sqlite3_prepare_v2(db, - "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color," - " count(p.poi_id)" - " from category c" - " left outer join poi p on c.cat_id = p.cat_id" - " group by c.cat_id, c.label, c.desc, c.enabled " - " order by c.priority,c.label", -1, &poisql.selall_cat, NULL); - - /* Select quick categories */ - sqlite3_prepare_v2(db, - "select c.cat_id, c.label, c.icon, c.color" - " from category c where c.enabled=1 " - " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL); - - return TRUE; +/* Select POIs inside given minmax lat,lon */ +DB_PREP(db, + "select " + POI_BASE_SQL_FIELDS + " from poi p, category c " + " where p.lat between ? and ? " + " and p.lon between ? and ? " + " and c.enabled=1 and p.cat_id=c.cat_id order by c.priority limit 500", + poisql.select_poi); + +/* Get POI with given ID */ +DB_PREP(db, + "select " + POI_BASE_SQL_FIELDS + " from poi p, category c " + " where p.poi_id = ? " + " and p.cat_id=c.cat_id", + poisql.select_poi_by_id); + +/* Search POIs by label and any category */ +DB_PREP(db, + "select " + POI_BASE_SQL_FIELDS + " from poi p, category c " + " where p.lat between ? and ? " + " and p.lon between ? and ? " + " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) order by p.label, c.label", + poisql.select_poi_search); + +/* Search POI by label and category */ +DB_PREP(db, + "select " + POI_BASE_SQL_FIELDS + " from poi p, category c " + " where p.lat between ? and ? " + " and p.lon between ? and ? " + " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) and c.cat_id = ? order by p.label", + poisql.select_poi_search_cat); + +/* Search POIs by category */ +DB_PREP(db, + "select " + POI_BASE_SQL_FIELDS + " from poi p, category c " + " where p.lat between ? and ? " + " and p.lon between ? and ? " + " and c.enabled=1 and p.cat_id=c.cat_id and c.cat_id=? order by p.label", + poisql.select_poi_by_cat); + +/* Select any nearest POI */ +DB_PREP(db, + "select " + POI_MINI_SQL_FIELDS + " from poi p, category c " + " where c.enabled = 1 and p.cat_id = c.cat_id " + " and p.lat between $LAT-0.10 and $LAT+0.10 " + " and p.lon between $LON-0.10 and $LAT+0.10 " + " order by (($LAT - p.lat) * ($LAT - p.lat) " + "+ ($LON - p.lon) * ($LON - p.lon)) limit 1", + poisql.select_nearest_poi); + +/* Insert POI */ +DB_PREP(db, "insert into poi (lat, lon, label, desc, url, postal_code, cat_id, addtime, public, source)" + " values (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)", poisql.insert_poi); +/* update poi */ +DB_PREP(db, "update poi set label=?, desc=?, cat_id=? where poi_id=?", poisql.update_poi); +/* delete from poi */ +DB_PREP(db, "delete from poi where poi_id=?", poisql.delete_poi); +/* delete from poi by cat_id */ +DB_PREP(db, "delete from poi where cat_id=?", poisql.delete_poi_by_catid); + +/* select from category */ +DB_PREP(db, + "select c.label, c.desc, c.enabled" + " from category c where c.cat_id = ?", + poisql.select_cat); +/* insert into category */ +DB_PREP(db, + "insert into category (label, desc, enabled)" + " values (?, ?, ?)", + poisql.insert_cat); +/* update category */ +DB_PREP(db, + "update category set label = ?, desc = ?," + " enabled = ? where poi_id = ?", + poisql.update_cat); +/* delete from category */ +DB_PREP(db,"delete from category where cat_id = ?", + poisql.delete_cat); +/* enable category */ +DB_PREP(db, + "update category set enabled = ?" + " where cat_id = ?", + poisql.toggle_cat); +/* select all category */ +DB_PREP(db, + "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color," + " count(p.poi_id)" + " from category c" + " left outer join poi p on c.cat_id = p.cat_id" + " group by c.cat_id, c.label, c.desc, c.enabled " + " order by c.priority,c.label", + poisql.selall_cat); +/* Select quick categories */ +DB_PREP(db, + "select c.cat_id, c.label, c.icon, c.color" + " from category c where c.enabled=1 " + " order by c.priority,c.label limit 9", + poisql.select_quick_cat); +return TRUE; } void -- 2.39.5