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
  32 sqlite3 *_db = NULL;
  33 gchar *_poi_db = NULL;
  34
  35 sqlite3_stmt *_stmt_select_poi = NULL;
  36 sqlite3_stmt *_stmt_select_nearest_poi = NULL;
  37 sqlite3_stmt *_stmt_insert_poi = NULL;
  38 sqlite3_stmt *_stmt_update_poi = NULL;
  39 sqlite3_stmt *_stmt_delete_poi = NULL;
  40 sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
  41 sqlite3_stmt *_stmt_nextlabel_poi = NULL;
  42
  43 sqlite3_stmt *_stmt_select_cat = NULL;
  44 sqlite3_stmt *_stmt_insert_cat = NULL;
  45 sqlite3_stmt *_stmt_update_cat = NULL;
  46 sqlite3_stmt *_stmt_delete_cat = NULL;
  47 sqlite3_stmt *_stmt_toggle_cat = NULL;
  48 sqlite3_stmt *_stmt_selall_cat = NULL;
  49
  50 gboolean
  51 poi_db_create(sqlite3 *db)
  52 {
  53         gchar **pszResult;
  54         guint nRow, nColumn;
  55         if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
  56                                            &pszResult, &nRow, &nColumn, NULL)) {
  57                 gchar *create_sql;
  58
  59                 g_printf("Creating initial tables\n");
  60
  61                 create_sql =
  62                     sqlite3_mprintf
  63                     ("create table poi (poi_id integer PRIMARY KEY, "
  64                         "lat real, "
  65                         "lon real, "
  66                         "elev real, "
  67                         "label text, "
  68                         "desc text, "
  69                         "public integer, "
  70                         "cat_id integer);"
  71                      "create table category (cat_id integer PRIMARY KEY,"
  72                         "label text, "
  73                         "desc text, "
  74                         "icon text, "
  75                         "color char(7), "
  76                         "enabled integer);"
  77                      /* Add some default categories... */
  78                      "insert into category (label, desc, enabled) "
  79                      "values ('%q', '%q', 1); "
  80                      "insert into category (label, desc, enabled) "
  81                      "values ('%q', '%q', 1); "
  82                      "insert into category (label, desc, enabled) "
  83                      "values ('%q', '%q', 1); "
  84                      "insert into category (label, desc, enabled) "
  85                      "values ('%q', '%q', 1); "
  86                      "insert into category (label, desc, enabled) "
  87                      "values ('%q', '%q', 1); "
  88                      "insert into category (label, desc, enabled) "
  89                      "values ('%q', '%q', 1); "
  90                      "insert into category (label, desc, enabled) "
  91                      "values ('%q', '%q', 1); "
  92                      "insert into category (label, desc, enabled) "
  93                      "values ('%q', '%q', 1); "
  94                      "insert into category (label, desc, enabled) "
  95                      "values ('%q', '%q', 1); "
  96                      "insert into category (label, desc, enabled) "
  97                      "values ('%q', '%q', 1); "
  98                      "insert into category (label, desc, enabled) "
  99                      "values ('%q', '%q', 1); ",
 100                      _("Fuel"),
 101                      _("Stations for purchasing fuel for vehicles."),
 102                      _("Residence"),
 103                      _("Houses, apartments, or other residences of import."),
 104                      _("Dining"),
 105                      _("Places to eat or drink."),
 106                      _("Shopping/Services"),
 107                      _("Places to shop or acquire services."),
 108                      _("Recreation"),
 109                      _("Indoor or Outdoor places to have fun."),
 110                      _("Transportation"),
 111                      _("Bus stops, airports, train stations, etc."),
 112                      _("Lodging"),
 113                      _("Places to stay temporarily or for the night."),
 114                      _("School"),
 115                      _("Elementary schools, college campuses, etc."),
 116                      _("Business"),
 117                      _("General places of business."),
 118                      _("Landmark"),
 119                      _("General landmarks."),
 120                      _("Other"),
 121                      _("Miscellaneous category for everything else."));
 122
 123                 if (SQLITE_OK !=
 124                     sqlite3_exec(&db, create_sql, NULL, NULL, &perror)
 125                     && (SQLITE_OK !=
 126                         sqlite3_get_table(db, "select label from poi limit 1",
 127                                           &pszResult, &nRow, &nColumn, NULL))) {
 128                         g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
 129                         sqlite3_close(db);
 130                         db = NULL;
 131                         return FALSE;
 132                 }
 133         } else
 134                 sqlite3_free_table(pszResult);
 135
 136         return TRUE;
 137 }
 138
 139 gboolean
 140 poi_db_prepare(sqlite3 *db)
 141 {
 142         /* select from poi */
 143         sqlite3_prepare(db,
 144                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 145                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 146                         " from poi p, category c "
 147                         " where p.lat between ? and ? "
 148                         " and p.lon between ? and ? "
 149                         " and c.enabled = 1 and p.cat_id = c.cat_id",
 150                         -1, &_stmt_select_poi, NULL);
 151
 152         /* select nearest pois */
 153         sqlite3_prepare(db,
 154                         "select p.lat, p.lon, p.label, c.label"
 155                         " from poi p, category c "
 156                         " where c.enabled = 1 and p.cat_id = c.cat_id "
 157                         " and p.lat between $LAT-0.15 and $LAT+0.15 "
 158                         " and p.lon between $LON-0.15 and $LAT+0.15 "
 159                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 160                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 161                         -1, &_stmt_select_nearest_poi, NULL);
 162
 163         /* insert poi */
 164         sqlite3_prepare(db,
 165                         "insert into poi (lat, lon, label, desc, cat_id, public)"
 166                         " values (?, ?, ?, ?, ?, 1)", -1, &_stmt_insert_poi, NULL);
 167         /* update poi */
 168         sqlite3_prepare(db,
 169                         "update poi set label = ?, desc = ?, "
 170                         "cat_id = ? where poi_id = ?",
 171                         -1, &_stmt_update_poi, NULL);
 172         /* delete from poi */
 173         sqlite3_prepare(db,
 174                         " delete from poi where poi_id = ?",
 175                         -1, &_stmt_delete_poi, NULL);
 176         /* delete from poi by cat_id */
 177         sqlite3_prepare(db,
 178                         "delete from poi where cat_id = ?",
 179                         -1, &_stmt_delete_poi_by_catid, NULL);
 180         /* get next poilabel */
 181         sqlite3_prepare(db,
 182                         "select ifnull(max(poi_id) + 1,1) from poi",
 183                         -1, &_stmt_nextlabel_poi, NULL);
 184
 185         /* select from category */
 186         sqlite3_prepare(db,
 187                         "select c.label, c.desc, c.enabled"
 188                         " from category c where c.cat_id = ?",
 189                         -1, &_stmt_select_cat, NULL);
 190         /* insert into category */
 191         sqlite3_prepare(db,
 192                         "insert into category (label, desc, enabled)"
 193                         " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
 194         /* update category */
 195         sqlite3_prepare(db,
 196                         "update category set label = ?, desc = ?,"
 197                         " enabled = ? where poi_id = ?",
 198                         -1, &_stmt_update_cat, NULL);
 199         /* delete from category */
 200         sqlite3_prepare(db,
 201                         "delete from category where cat_id = ?",
 202                         -1, &_stmt_delete_cat, NULL);
 203         /* enable category */
 204         sqlite3_prepare(db,
 205                         "update category set enabled = ?"
 206                         " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
 207         /* select all category */
 208         sqlite3_prepare(db,
 209                         "select c.cat_id, c.label, c.desc, c.enabled,"
 210                         " count(p.poi_id)"
 211                         " from category c"
 212                         " left outer join poi p on c.cat_id = p.cat_id"
 213                         " group by c.cat_id, c.label, c.desc, c.enabled "
 214                         " order by c.label", -1, &_stmt_selall_cat, NULL);
 215
 216         return TRUE;
 217 }
 218
 219 gboolean
 220 poi_db_disconnect(sqlite3 **db)
 221 {
 222 if (db && *db) {
 223         /* XXX: Handle prepared statements */
 224         sqlite3_close(*db);
 225         return TRUE;
 226 }
 227 return FALSE;
 228 }
 229
 230 gboolean
 231 poi_db_connect(sqlite3 **db, const gchar *poi_db)
 232 {
 233 if (db_connect(db, poi_db)) {
 234         poi_db_create(*db);
 235         poi_db_prepare(*db);
 236         return TRUE;
 237 }
 238 return FALSE;
 239 }
 240
 241 gboolean
 242 poi_category_update(gint cat_id, gchar * cat_label, gchar * cat_desc,
 243                     gint cat_enabled)
 244 {
 245 gboolean results=TRUE;
 246
 247 if (cat_id > 0) {
 248 /* edit category */
 249                 if (SQLITE_OK !=
 250                     sqlite3_bind_text(_stmt_update_cat, 1, cat_label, -1,
 251                                       g_free)
 252                     || SQLITE_OK != sqlite3_bind_text(_stmt_update_cat, 2,
 253                                                       cat_desc, -1, g_free)
 254                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 3,
 255                                                      cat_enabled)
 256                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 4,
 257                                                      cat_id)
 258                     || SQLITE_DONE != sqlite3_step(_stmt_update_cat)) {
 259                         results = FALSE;
 260                 }
 261                 sqlite3_reset(_stmt_update_cat);
 262         } else {
 263                 /* add category */
 264                 if (SQLITE_OK !=
 265                     sqlite3_bind_text(_stmt_insert_cat, 1, cat_label, -1,
 266                                       g_free)
 267                     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_cat, 2,
 268                                                       cat_desc, -1, g_free)
 269                     || SQLITE_OK != sqlite3_bind_int(_stmt_insert_cat, 3,
 270                                                      cat_enabled)
 271                     || SQLITE_DONE != sqlite3_step(_stmt_insert_cat)) {
 272                         results = FALSE;
 273                 }
 274                 sqlite3_reset(_stmt_insert_cat);
 275         }
 276 return results;
 277 }
 278
 279 gboolean
 280 poi_category_delete(DeletePOI * dpoi)
 281 {
 282 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi_by_catid, 1, dpoi->id)
 283             || SQLITE_DONE != sqlite3_step(_stmt_delete_poi_by_catid)) {
 284                 sqlite3_reset(_stmt_delete_poi_by_catid);
 285                 return FALSE;
 286 }
 287 sqlite3_reset(_stmt_delete_poi_by_catid);
 288
 289 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_cat, 1, dpoi->id) ||
 290     SQLITE_DONE != sqlite3_step(_stmt_delete_cat)) {
 291         sqlite3_reset(_stmt_delete_cat);
 292         return FALSE;
 293 }
 294 sqlite3_reset(_stmt_delete_cat);
 295 return TRUE;
 296 }
 297
 298
 299 gboolean
 300 poi_delete(DeletePOI * dpoi)
 301 {
 302 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi, 1, dpoi->id) ||
 303     SQLITE_DONE != sqlite3_step(_stmt_delete_poi)) {
 304         sqlite3_reset(_stmt_delete_poi);
 305         return FALSE;
 306 } else {
 307         sqlite3_reset(_stmt_delete_poi);
 308 }
 309 return TRUE;
 310 }
 311