+/*
+ * This file is part of mapper
+ *
+ * Copyright (C) 2007 Kaj-Michael Lang
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License along
+ * with this program; if not, write to the Free Software Foundation, Inc.,
+ * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+ */
+
#include "config.h"
-#include <gtk/gtk.h>
+#include <glib.h>
#include <sqlite3.h>
-#define _(String) gettext(String)
-
-#include <libintl.h>
-#include <locale.h>
+#include "db.h"
+#include "osm-sql-tables.h"
-#include "utils.h"
-#include "ui-common.h"
-#include "poi.h"
+sqlite3 *_db=NULL;
+gchar *_mapper_db=NULL;
-sqlite3_stmt *_stmt_select_poi = NULL;
-sqlite3_stmt *_stmt_select_nearest_poi = NULL;
-sqlite3_stmt *_stmt_insert_poi = NULL;
-sqlite3_stmt *_stmt_update_poi = NULL;
-sqlite3_stmt *_stmt_delete_poi = NULL;
-sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
-sqlite3_stmt *_stmt_nextlabel_poi = NULL;
+void
+db_create_tables(sqlite3 *db)
+{
+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);
+}
-sqlite3_stmt *_stmt_select_cat = NULL;
-sqlite3_stmt *_stmt_insert_cat = NULL;
-sqlite3_stmt *_stmt_update_cat = NULL;
-sqlite3_stmt *_stmt_delete_cat = NULL;
-sqlite3_stmt *_stmt_toggle_cat = NULL;
-sqlite3_stmt *_stmt_selall_cat = NULL;
+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);
+}
-gboolean db_prepare(sqlite3 *db)
+/**
+ * A simple check to test if a table exists
+ *
+ */
+gboolean
+db_check(sqlite3 *db, const gchar *table)
{
+gchar **pszResult;
+guint nRow, nColumn;
+gchar sql[64];
+
+g_snprintf(sql, sizeof(sql),"select count(*) from %s", table);
+
+if (SQLITE_OK != sqlite3_get_table(db, sql, &pszResult, &nRow, &nColumn, NULL))
+ return FALSE;
+sqlite3_free_table(pszResult);
return TRUE;
}
-gboolean db_close(sqlite3 *db)
+gboolean
+db_exec_sql(sqlite3 *db, const gchar *sql)
+{
+gint r;
+
+r=sqlite3_exec(db, sql, NULL, NULL, NULL);
+if (r!=SQLITE_OK && r!=SQLITE_DONE)
+ g_printerr("SQL ERROR %d:(%s) %s\n", r, sql, sqlite3_errmsg(db));
+
+return (r==SQLITE_OK || r==SQLITE_DONE) ? TRUE : FALSE;
+}
+
+/**
+ * Helper that execs a given prepared sql statment and resets clears bindings.
+ * return TRUE or FALSE depending on success of the query.
+ *
+ */
+gboolean
+db_exec(sqlite3 *db, sqlite3_stmt *sql)
+{
+gint r;
+
+g_assert(sql);
+r=sqlite3_step(sql);
+if (r!=SQLITE_OK && r!=SQLITE_DONE)
+ g_printerr("SQL ERROR %d: %s\n", r, sqlite3_errmsg(db));
+sqlite3_reset(sql);
+sqlite3_clear_bindings(sql);
+
+return (r==SQLITE_OK || r==SQLITE_DONE) ? TRUE : FALSE;
+}
+
+/**
+ * Close the database connection
+ *
+ */
+gboolean
+db_close(sqlite3 **db)
{
-if (db) {
- sqlite3_close(db);
- db=NULL;
+if (db && *db) {
+ sqlite3_close(*db);
+ *db=NULL;
}
return TRUE;
}
-gboolean db_connect(sqlite3 **ndb, const gchar *poi_db)
+gboolean
+db_transaction_begin(sqlite3 *db)
+{
+return db_exec_sql(db, "begin;");
+}
+
+gboolean
+db_transaction_commit(sqlite3 *db)
+{
+return db_exec_sql(db, "commit;");
+}
+
+/**
+ * Connect to given sqlite database
+ *
+ */
+gboolean
+db_connect(sqlite3 **db, const gchar *mapper_db)
{
- gchar buffer[100];
- gchar *perror;
- gchar **pszResult;
- guint nRow, nColumn;
- sqlite3 *db;
-
- printf("%s()\n", __PRETTY_FUNCTION__);
-
- if (ndb && *ndb)
- db=*ndb;
- else
- db=NULL;
-
- if (db) {
- sqlite3_close(db);
- db = NULL;
- }
-
- if (!poi_db)
- return FALSE;
-
- if (SQLITE_OK != (sqlite3_open(poi_db, &db))) {
- gchar buffer2[200];
-
- snprintf(buffer2, sizeof(buffer2),
- "%s: %s", _("Problem with POI database"),
- sqlite3_errmsg(db));
- sqlite3_close(db);
- db = NULL;
- popup_error(_window, buffer2);
- return FALSE;
- }
-
- *ndb=db;
-
- if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
- &pszResult, &nRow, &nColumn, NULL)) {
- gchar *create_sql;
-
- g_printf("Creating initial tables\n");
-
- create_sql =
- sqlite3_mprintf
- ("create table poi (poi_id integer PRIMARY KEY, lat real, "
- "lon real, label text, desc text, cat_id integer);"
- "create table category (cat_id integer PRIMARY KEY,"
- "label text, desc text, enabled integer);"
- /* Add some default categories... */
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); "
- "insert into category (label, desc, enabled) "
- "values ('%q', '%q', 1); ",
- _("Fuel"),
- _("Stations for purchasing fuel for vehicles."),
- _("Residence"),
- _("Houses, apartments, or other residences of import."),
- _("Dining"),
- _("Places to eat or drink."),
- _("Shopping/Services"),
- _("Places to shop or acquire services."),
- _("Recreation"),
- _("Indoor or Outdoor places to have fun."),
- _("Transportation"),
- _("Bus stops, airports, train stations, etc."),
- _("Lodging"),
- _("Places to stay temporarily or for the night."),
- _("School"),
- _("Elementary schools, college campuses, etc."),
- _("Business"),
- _("General places of business."),
- _("Landmark"),
- _("General landmarks."),
- _("Other"),
- _("Miscellaneous category for everything else."));
-
- if (SQLITE_OK !=
- sqlite3_exec(db, create_sql, NULL, NULL, &perror)
- && (SQLITE_OK !=
- sqlite3_get_table(db, "select label from poi limit 1",
- &pszResult, &nRow, &nColumn, NULL))) {
- snprintf(buffer, sizeof(buffer), "%s:\n%s",
- _("Failed to open or create database"),
- sqlite3_errmsg(db));
- sqlite3_close(db);
- db = NULL;
- popup_error(_window, buffer);
- return FALSE;
- }
- } else
- sqlite3_free_table(pszResult);
-
- /* Prepare our SQL statements. */
- /* select from poi */
- sqlite3_prepare(db,
- "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
- " p.cat_id, c.label, c.desc"
- " 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",
- -1, &_stmt_select_poi, NULL);
-
- /* select nearest pois */
- sqlite3_prepare(db,
- "select p.lat, p.lon, p.label, c.label"
- " from poi p, category c "
- " where c.enabled = 1 and p.cat_id = c.cat_id"
- " order by (($LAT - p.lat) * ($LAT - p.lat) "
- "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
- -1, &_stmt_select_nearest_poi, NULL);
-
- /* insert poi */
- sqlite3_prepare(db,
- "insert into poi (lat, lon, label, desc, cat_id)"
- " values (?, ?, ?, ?, ?)", -1, &_stmt_insert_poi, NULL);
- /* update poi */
- sqlite3_prepare(db,
- "update poi set label = ?, desc = ?, "
- "cat_id = ? where poi_id = ?",
- -1, &_stmt_update_poi, NULL);
- /* delete from poi */
- sqlite3_prepare(db,
- " delete from poi where poi_id = ?",
- -1, &_stmt_delete_poi, NULL);
- /* delete from poi by cat_id */
- sqlite3_prepare(db,
- "delete from poi where cat_id = ?",
- -1, &_stmt_delete_poi_by_catid, NULL);
- /* get next poilabel */
- sqlite3_prepare(db,
- "select ifnull(max(poi_id) + 1,1) from poi",
- -1, &_stmt_nextlabel_poi, NULL);
-
- /* select from category */
- sqlite3_prepare(db,
- "select c.label, c.desc, c.enabled"
- " from category c where c.cat_id = ?",
- -1, &_stmt_select_cat, NULL);
- /* insert into category */
- sqlite3_prepare(db,
- "insert into category (label, desc, enabled)"
- " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
- /* update category */
- sqlite3_prepare(db,
- "update category set label = ?, desc = ?,"
- " enabled = ? where poi_id = ?",
- -1, &_stmt_update_cat, NULL);
- /* delete from category */
- sqlite3_prepare(db,
- "delete from category where cat_id = ?",
- -1, &_stmt_delete_cat, NULL);
- /* enable category */
- sqlite3_prepare(db,
- "update category set enabled = ?"
- " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
- /* select all category */
- sqlite3_prepare(db,
- "select c.cat_id, c.label, c.desc, c.enabled,"
- " 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.label", -1, &_stmt_selall_cat, NULL);
-
- printf("%s(): return\n", __PRETTY_FUNCTION__);
- return TRUE;
+if (db && *db) {
+ sqlite3_close(*db);
+ *db=NULL;
}
+
+if (!mapper_db)
+ return FALSE;
+
+if (SQLITE_OK!=(sqlite3_open(mapper_db, db))) {
+ g_printerr("SQL OPEN: %s\n", sqlite3_errmsg(db));
+ sqlite3_close(*db);
+ *db=NULL;
+ return FALSE;
+}
+
+/* Use smaller cache as the IT does not have much memory to spare */
+#ifdef WITH_DEVICE_770
+sqlite3_exec(*db, "PRAGMA cache_size = 2000;", NULL, NULL, NULL);
+#else
+sqlite3_exec(*db, "PRAGMA cache_size = 8000;", NULL, NULL, NULL);
+#endif
+
+return TRUE;
+}
+
projects / mapper / blobdiff