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

   1 #include "config.h"
   2
   3 #include <gtk/gtk.h>
   4 #include <sqlite3.h>
   5
   6 #define _(String) gettext(String)
   7
   8 #include <libintl.h>
   9 #include <locale.h>
  10
  11 #include "utils.h"
  12 #include "ui-common.h"
  13 #include "poi.h"
  14
  15 sqlite3_stmt *_stmt_select_poi = NULL;
  16 sqlite3_stmt *_stmt_select_nearest_poi = NULL;
  17 sqlite3_stmt *_stmt_insert_poi = NULL;
  18 sqlite3_stmt *_stmt_update_poi = NULL;
  19 sqlite3_stmt *_stmt_delete_poi = NULL;
  20 sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
  21 sqlite3_stmt *_stmt_nextlabel_poi = NULL;
  22
  23 sqlite3_stmt *_stmt_select_cat = NULL;
  24 sqlite3_stmt *_stmt_insert_cat = NULL;
  25 sqlite3_stmt *_stmt_update_cat = NULL;
  26 sqlite3_stmt *_stmt_delete_cat = NULL;
  27 sqlite3_stmt *_stmt_toggle_cat = NULL;
  28 sqlite3_stmt *_stmt_selall_cat = NULL;
  29
  30 gboolean db_prepare(sqlite3 *db)
  31 {
  32
  33 return TRUE;
  34 }
  35
  36 gboolean db_close(sqlite3 *db)
  37 {
  38 if (db) {
  39         sqlite3_close(db);
  40         db=NULL;
  41 }
  42 return TRUE;
  43 }
  44
  45 gboolean db_connect(sqlite3 **ndb, const gchar *poi_db)
  46 {
  47         gchar buffer[100];
  48         gchar *perror;
  49         gchar **pszResult;
  50         guint nRow, nColumn;
  51         sqlite3 *db;
  52
  53         printf("%s()\n", __PRETTY_FUNCTION__);
  54
  55         if (ndb && *ndb)
  56                 db=*ndb;
  57         else
  58                 db=NULL;
  59
  60         if (db) {
  61                 sqlite3_close(db);
  62                 db = NULL;
  63         }
  64
  65         if (!poi_db)
  66                 return FALSE;
  67
  68         if (SQLITE_OK != (sqlite3_open(poi_db, &db))) {
  69                 gchar buffer2[200];
  70
  71                 snprintf(buffer2, sizeof(buffer2),
  72                          "%s: %s", _("Problem with POI database"),
  73                          sqlite3_errmsg(db));
  74                 sqlite3_close(db);
  75                 db = NULL;
  76                 popup_error(_window, buffer2);
  77                 return FALSE;
  78         }
  79
  80         *ndb=db;
  81
  82         if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
  83                                            &pszResult, &nRow, &nColumn, NULL)) {
  84                 gchar *create_sql;
  85
  86                 g_printf("Creating initial tables\n");
  87
  88                 create_sql =
  89                     sqlite3_mprintf
  90                     ("create table poi (poi_id integer PRIMARY KEY, lat real, "
  91                      "lon real, label text, desc text, cat_id integer);"
  92                      "create table category (cat_id integer PRIMARY KEY,"
  93                      "label text, desc text, enabled integer);"
  94                      /* Add some default categories... */
  95                      "insert into category (label, desc, enabled) "
  96                      "values ('%q', '%q', 1); "
  97                      "insert into category (label, desc, enabled) "
  98                      "values ('%q', '%q', 1); "
  99                      "insert into category (label, desc, enabled) "
 100                      "values ('%q', '%q', 1); "
 101                      "insert into category (label, desc, enabled) "
 102                      "values ('%q', '%q', 1); "
 103                      "insert into category (label, desc, enabled) "
 104                      "values ('%q', '%q', 1); "
 105                      "insert into category (label, desc, enabled) "
 106                      "values ('%q', '%q', 1); "
 107                      "insert into category (label, desc, enabled) "
 108                      "values ('%q', '%q', 1); "
 109                      "insert into category (label, desc, enabled) "
 110                      "values ('%q', '%q', 1); "
 111                      "insert into category (label, desc, enabled) "
 112                      "values ('%q', '%q', 1); "
 113                      "insert into category (label, desc, enabled) "
 114                      "values ('%q', '%q', 1); "
 115                      "insert into category (label, desc, enabled) "
 116                      "values ('%q', '%q', 1); ",
 117                      _("Fuel"),
 118                      _("Stations for purchasing fuel for vehicles."),
 119                      _("Residence"),
 120                      _("Houses, apartments, or other residences of import."),
 121                      _("Dining"),
 122                      _("Places to eat or drink."),
 123                      _("Shopping/Services"),
 124                      _("Places to shop or acquire services."),
 125                      _("Recreation"),
 126                      _("Indoor or Outdoor places to have fun."),
 127                      _("Transportation"),
 128                      _("Bus stops, airports, train stations, etc."),
 129                      _("Lodging"),
 130                      _("Places to stay temporarily or for the night."),
 131                      _("School"),
 132                      _("Elementary schools, college campuses, etc."),
 133                      _("Business"),
 134                      _("General places of business."),
 135                      _("Landmark"),
 136                      _("General landmarks."),
 137                      _("Other"),
 138                      _("Miscellaneous category for everything else."));
 139
 140                 if (SQLITE_OK !=
 141                     sqlite3_exec(db, create_sql, NULL, NULL, &perror)
 142                     && (SQLITE_OK !=
 143                         sqlite3_get_table(db, "select label from poi limit 1",
 144                                           &pszResult, &nRow, &nColumn, NULL))) {
 145                         snprintf(buffer, sizeof(buffer), "%s:\n%s",
 146                                  _("Failed to open or create database"),
 147                                  sqlite3_errmsg(db));
 148                         sqlite3_close(db);
 149                         db = NULL;
 150                         popup_error(_window, buffer);
 151                         return FALSE;
 152                 }
 153         } else
 154                 sqlite3_free_table(pszResult);
 155
 156         /* Prepare our SQL statements. */
 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"
 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"
 170                         " from poi p, category c "
 171                         " where c.enabled = 1 and p.cat_id = c.cat_id"
 172                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 173                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 174                         -1, &_stmt_select_nearest_poi, NULL);
 175
 176         /* insert poi */
 177         sqlite3_prepare(db,
 178                         "insert into poi (lat, lon, label, desc, cat_id)"
 179                         " values (?, ?, ?, ?, ?)", -1, &_stmt_insert_poi, NULL);
 180         /* update poi */
 181         sqlite3_prepare(db,
 182                         "update poi set label = ?, desc = ?, "
 183                         "cat_id = ? where poi_id = ?",
 184                         -1, &_stmt_update_poi, NULL);
 185         /* delete from poi */
 186         sqlite3_prepare(db,
 187                         " delete from poi where poi_id = ?",
 188                         -1, &_stmt_delete_poi, NULL);
 189         /* delete from poi by cat_id */
 190         sqlite3_prepare(db,
 191                         "delete from poi where cat_id = ?",
 192                         -1, &_stmt_delete_poi_by_catid, NULL);
 193         /* get next poilabel */
 194         sqlite3_prepare(db,
 195                         "select ifnull(max(poi_id) + 1,1) from poi",
 196                         -1, &_stmt_nextlabel_poi, NULL);
 197
 198         /* select from category */
 199         sqlite3_prepare(db,
 200                         "select c.label, c.desc, c.enabled"
 201                         " from category c where c.cat_id = ?",
 202                         -1, &_stmt_select_cat, NULL);
 203         /* insert into category */
 204         sqlite3_prepare(db,
 205                         "insert into category (label, desc, enabled)"
 206                         " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
 207         /* update category */
 208         sqlite3_prepare(db,
 209                         "update category set label = ?, desc = ?,"
 210                         " enabled = ? where poi_id = ?",
 211                         -1, &_stmt_update_cat, NULL);
 212         /* delete from category */
 213         sqlite3_prepare(db,
 214                         "delete from category where cat_id = ?",
 215                         -1, &_stmt_delete_cat, NULL);
 216         /* enable category */
 217         sqlite3_prepare(db,
 218                         "update category set enabled = ?"
 219                         " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
 220         /* select all category */
 221         sqlite3_prepare(db,
 222                         "select c.cat_id, c.label, c.desc, c.enabled,"
 223                         " count(p.poi_id)"
 224                         " from category c"
 225                         " left outer join poi p on c.cat_id = p.cat_id"
 226                         " group by c.cat_id, c.label, c.desc, c.enabled "
 227                         " order by c.label", -1, &_stmt_selall_cat, NULL);
 228
 229         printf("%s(): return\n", __PRETTY_FUNCTION__);
 230         return TRUE;
 231 }