err.no Git - mapper/blob - src/poi.c

   1 #include <config.h>
   2
   3 #define _GNU_SOURCE
   4
   5 #include <unistd.h>
   6 #include <stdlib.h>
   7 #include <string.h>
   8 #include <strings.h>
   9 #include <stddef.h>
  10 #include <locale.h>
  11 #include <math.h>
  12 #include <errno.h>
  13 #include <glib/gstdio.h>
  14 #include <gtk/gtk.h>
  15 #include <fcntl.h>
  16 #include <gconf/gconf-client.h>
  17 #include <libxml/parser.h>
  18
  19 #include <libintl.h>
  20 #include <locale.h>
  21
  22 #include <sqlite3.h>
  23
  24 #include "utils.h"
  25 #include "poi.h"
  26 #include "gps.h"
  27 #include "map.h"
  28 #include "mapper-types.h"
  29 #include "settings.h"
  30 #include "db.h"
  31 #include "osm.h"
  32
  33 sqlite3_stmt *_stmt_select_poi = NULL;
  34 sqlite3_stmt *_stmt_select_nearest_poi = NULL;
  35 sqlite3_stmt *_stmt_insert_poi = NULL;
  36 sqlite3_stmt *_stmt_update_poi = NULL;
  37 sqlite3_stmt *_stmt_delete_poi = NULL;
  38 sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
  39 sqlite3_stmt *_stmt_nextlabel_poi = NULL;
  40
  41 sqlite3_stmt *_stmt_select_cat = NULL;
  42 sqlite3_stmt *_stmt_insert_cat = NULL;
  43 sqlite3_stmt *_stmt_update_cat = NULL;
  44 sqlite3_stmt *_stmt_delete_cat = NULL;
  45 sqlite3_stmt *_stmt_toggle_cat = NULL;
  46 sqlite3_stmt *_stmt_selall_cat = NULL;
  47
  48 struct _poi_categories {
  49         node_type_t type;
  50         gchar *name, *desc;
  51 };
  52 static struct _poi_categories default_poi_categories[] = {
  53         {NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." },
  54         {NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." },
  55         {NODE_AMENITY_HOSPITAL, "Hospital", "" },
  56         {NODE_AMENITY_PHARMACY, "Pharmacy", ""},
  57         {NODE_AMENITY_POLICE, "Police", "Police station"},
  58         {NODE_AMENITY_HOTEL, "Hotel", "Places to stay temporarily or for the night."},
  59         {NODE_AMENITY_HOSTEL, "Hostel", "Places to stay temporarily or for the night."},
  60         {NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint."},
  61         {NODE_AMENITY_BANK, "Bank", "Place to take care of your money."},
  62         {NODE_AMENITY_POST, "Post office", "Place to handle mail."},
  63         {NODE_AMENITY_POST_BOX, "Post box", "Send letters."},
  64         {NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here."},
  65         {NODE_AMENITY_RAILWAY_STATION, "Railway station", "Transportation by train."},
  66         {NODE_AMENITY_RAILWAY_HALT, "Railway halt", ""},
  67         {NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus."},
  68         {NODE_AMENITY_BOAT, "Harbour", "Transportation by boat."},
  69         {NODE_AMENITY_AIRPORT, "Airport", "Transportation by air."},
  70         {NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink."},
  71         {NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party."},
  72         {NODE_AMENITY_CINEMA, "Cinema", "Place to see movies"},
  73         {NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing"},
  74         {NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services."},
  75         {NODE_AMENITY_POW, "Place of Worchip", ""},
  76         {NODE_AMENITY_COLLEGE, "College Campus/Building", ""},
  77         {NODE_AMENITY_SCHOOL, "School", ""},
  78         {NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else."},
  79         {NODE_POI_END, NULL, NULL }
  80 };
  81
  82 gboolean
  83 poi_cb_populate_categories(sqlite3 *db)
  84 {
  85 sqlite3_stmt *sql_cat;
  86 gint i;
  87
  88 g_printf("Checking default categories\n");
  89 sqlite3_prepare(db,"insert into category (cat_id, label, desc, enabled)"
  90                         " values (?, ?, ?, 1)",
  91                         -1, &sql_cat, NULL);
  92
  93 for (i=0; default_poi_categories[i].name; i++) {
  94         sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
  95         sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name , -1, SQLITE_TRANSIENT);
  96         sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc , -1, SQLITE_TRANSIENT);
  97         sqlite3_step(sql_cat);
  98         sqlite3_reset(sql_cat);
  99         sqlite3_clear_bindings(sql_cat);
 100 }
 101
 102 sqlite3_finalize(sql_cat);
 103
 104 return TRUE;
 105 }
 106
 107 gboolean
 108 poi_db_create(sqlite3 *db)
 109 {
 110         gchar **pszResult;
 111         guint nRow, nColumn;
 112         if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 113                                            &pszResult, &nRow, &nColumn, NULL)) {
 114                 gchar *create_sql;
 115
 116                 g_printf("Creating initial tables\n");
 117
 118                 create_sql = sqlite3_mprintf
 119                     ("create table poi "
 120                         "(poi_id integer PRIMARY KEY, "
 121                         "lat real, "
 122                         "lon real, "
 123                         "elev real, "
 124                         "label text, "
 125                         "desc text, "
 126                         "public integer, "
 127                         "source integer default 0, "
 128                         "osm_id integer default 0, "
 129                         "priority integer default 2, "
 130                         "cat_id integer);"
 131                      "create table category (cat_id integer PRIMARY KEY,"
 132                         "label text, "
 133                         "desc text, "
 134                         "icon text, "
 135                         "color char(7), "
 136                         "enabled integer);");
 137
 138                 if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
 139                     && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 140                                           &pszResult, &nRow, &nColumn, NULL))) {
 141                         g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
 142                         sqlite3_close(db);
 143                         db = NULL;
 144                         return FALSE;
 145                 }
 146         } else
 147                 sqlite3_free_table(pszResult);
 148
 149         /* Make sure default categories exists */
 150         poi_cb_populate_categories(db);
 151         return TRUE;
 152 }
 153
 154 gboolean
 155 poi_db_prepare(sqlite3 *db)
 156 {
 157         /* select from poi */
 158         sqlite3_prepare(db,
 159                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 160                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 161                         " from poi p, category c "
 162                         " where p.lat between ? and ? "
 163                         " and p.lon between ? and ? "
 164                         " and c.enabled = 1 and p.cat_id = c.cat_id",
 165                         -1, &_stmt_select_poi, NULL);
 166
 167         /* select nearest pois */
 168         sqlite3_prepare(db,
 169                         "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
 170                         " from poi p, category c "
 171                         " where c.enabled = 1 and p.cat_id = c.cat_id "
 172                         " and p.lat between $LAT-0.15 and $LAT+0.15 "
 173                         " and p.lon between $LON-0.15 and $LAT+0.15 "
 174                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 175                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 176                         -1, &_stmt_select_nearest_poi, NULL);
 177
 178         /* insert poi */
 179         sqlite3_prepare(db,
 180                         "insert into poi (lat, lon, label, desc, cat_id, public)"
 181                         " values (?, ?, ?, ?, ?, 1)", -1, &_stmt_insert_poi, NULL);
 182         /* update poi */
 183         sqlite3_prepare(db, "update poi set label = ?, desc = ?, "
 184                         "cat_id = ? where poi_id = ?",
 185                         -1, &_stmt_update_poi, NULL);
 186         /* delete from poi */
 187         sqlite3_prepare(db, "delete from poi where poi_id = ?",
 188                         -1, &_stmt_delete_poi, NULL);
 189         /* delete from poi by cat_id */
 190         sqlite3_prepare(db, "delete from poi where cat_id = ?",
 191                         -1, &_stmt_delete_poi_by_catid, NULL);
 192         /* get next poilabel */
 193         sqlite3_prepare(db, "select ifnull(max(poi_id) + 1,1) from poi",
 194                         -1, &_stmt_nextlabel_poi, NULL);
 195
 196         /* select from category */
 197         sqlite3_prepare(db,
 198                         "select c.label, c.desc, c.enabled"
 199                         " from category c where c.cat_id = ?",
 200                         -1, &_stmt_select_cat, NULL);
 201         /* insert into category */
 202         sqlite3_prepare(db,
 203                         "insert into category (label, desc, enabled)"
 204                         " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
 205         /* update category */
 206         sqlite3_prepare(db,
 207                         "update category set label = ?, desc = ?,"
 208                         " enabled = ? where poi_id = ?",
 209                         -1, &_stmt_update_cat, NULL);
 210         /* delete from category */
 211         sqlite3_prepare(db,"delete from category where cat_id = ?",
 212                         -1, &_stmt_delete_cat, NULL);
 213         /* enable category */
 214         sqlite3_prepare(db,
 215                         "update category set enabled = ?"
 216                         " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
 217         /* select all category */
 218         sqlite3_prepare(db,
 219                         "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
 220                         " count(p.poi_id)"
 221                         " from category c"
 222                         " left outer join poi p on c.cat_id = p.cat_id"
 223                         " group by c.cat_id, c.label, c.desc, c.enabled "
 224                         " order by c.label", -1, &_stmt_selall_cat, NULL);
 225
 226         return TRUE;
 227 }
 228
 229 gboolean
 230 poi_db_disconnect(sqlite3 **db)
 231 {
 232 if (db && *db) {
 233         /* XXX: Handle prepared statements */
 234         sqlite3_close(*db);
 235         return TRUE;
 236 }
 237 return FALSE;
 238 }
 239
 240 gboolean
 241 poi_db_connect(sqlite3 **db, const gchar *poi_db)
 242 {
 243 if (db_connect(db, poi_db)) {
 244         poi_db_create(*db);
 245         poi_db_prepare(*db);
 246         return TRUE;
 247 }
 248 return FALSE;
 249 }
 250
 251 gboolean
 252 poi_category_update(gint cat_id, gchar * cat_label, gchar * cat_desc,
 253                     gint cat_enabled)
 254 {
 255 gboolean results=TRUE;
 256
 257 if (cat_id > 0) {
 258 /* edit category */
 259                 if (SQLITE_OK !=
 260                     sqlite3_bind_text(_stmt_update_cat, 1, cat_label, -1,
 261                                       g_free)
 262                     || SQLITE_OK != sqlite3_bind_text(_stmt_update_cat, 2,
 263                                                       cat_desc, -1, g_free)
 264                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 3,
 265                                                      cat_enabled)
 266                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 4,
 267                                                      cat_id)
 268                     || SQLITE_DONE != sqlite3_step(_stmt_update_cat)) {
 269                         results = FALSE;
 270                 }
 271                 sqlite3_reset(_stmt_update_cat);
 272                 sqlite3_clear_bindings(_stmt_update_cat);
 273         } else {
 274                 /* add category */
 275                 if (SQLITE_OK !=
 276                     sqlite3_bind_text(_stmt_insert_cat, 1, cat_label, -1,
 277                                       g_free)
 278                     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_cat, 2,
 279                                                       cat_desc, -1, g_free)
 280                     || SQLITE_OK != sqlite3_bind_int(_stmt_insert_cat, 3,
 281                                                      cat_enabled)
 282                     || SQLITE_DONE != sqlite3_step(_stmt_insert_cat)) {
 283                         results = FALSE;
 284                 }
 285                 sqlite3_reset(_stmt_insert_cat);
 286                 sqlite3_clear_bindings(_stmt_insert_cat);
 287         }
 288 return results;
 289 }
 290
 291 gboolean
 292 poi_category_delete(DeletePOI * dpoi)
 293 {
 294 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi_by_catid, 1, dpoi->id)
 295             || SQLITE_DONE != sqlite3_step(_stmt_delete_poi_by_catid)) {
 296                 sqlite3_reset(_stmt_delete_poi_by_catid);
 297                 return FALSE;
 298 }
 299 sqlite3_reset(_stmt_delete_poi_by_catid);
 300 sqlite3_clear_bindings(_stmt_delete_poi_by_catid);
 301
 302 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_cat, 1, dpoi->id) ||
 303     SQLITE_DONE != sqlite3_step(_stmt_delete_cat)) {
 304         sqlite3_reset(_stmt_delete_cat);
 305         return FALSE;
 306 }
 307 sqlite3_reset(_stmt_delete_cat);
 308 sqlite3_clear_bindings(_stmt_delete_cat);
 309 return TRUE;
 310 }
 311
 312
 313 gboolean
 314 poi_delete(DeletePOI * dpoi)
 315 {
 316 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi, 1, dpoi->id) ||
 317     SQLITE_DONE != sqlite3_step(_stmt_delete_poi)) {
 318         sqlite3_reset(_stmt_delete_poi);
 319         return FALSE;
 320 } else {
 321         sqlite3_reset(_stmt_delete_poi);
 322         sqlite3_clear_bindings(_stmt_delete_poi);
 323 }
 324 return TRUE;
 325 }
 326
 327 gboolean
 328 poi_update(gint poi_id, gint cat_id, gchar *poi_label, gchar *poi_desc)
 329 {
 330 if (SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 1, poi_label, -1, SQLITE_STATIC)
 331    || SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 2, poi_desc, -1, g_free)
 332    || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 3, cat_id)
 333    || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 4, poi_id)
 334    || SQLITE_DONE != sqlite3_step(_stmt_update_poi)) {
 335                 return FALSE;
 336         }
 337 sqlite3_reset(_stmt_update_poi);
 338 sqlite3_clear_bindings(_stmt_update_poi);
 339 return TRUE;
 340 }
 341
 342 gboolean
 343 poi_add(gdouble lat, gdouble lon, gint cat_id, gchar *poi_label, gchar *poi_desc)
 344 {
 345 if (SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 1, lat)
 346     || SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 2, lon)
 347     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 3, poi_label, -1, g_free)
 348     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 4, poi_desc, -1, g_free)
 349     || SQLITE_OK != sqlite3_bind_int(_stmt_insert_poi, 5, cat_id)
 350         || SQLITE_DONE != sqlite3_step(_stmt_insert_poi)) {
 351                 return FALSE;
 352         }
 353 sqlite3_reset(_stmt_insert_poi);
 354 sqlite3_clear_bindings(_stmt_insert_poi);
 355 return TRUE;
 356 }
 357