Logo Search packages:      
Sourcecode: s3d version File versions  Download package

db.c

/*
 * db.c
 *
 * Copyright (C) 2006-2008 Simon Wunderlich <dotslash@packetmixer.de>
 *
 * This file is part of s3dosm, a gps card application for s3d.
 * See http://s3d.berlios.de/ for more updates.
 *
 * s3dosm 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.
 *
 * s3dosm 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 s3dosm; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

#include <sqlite3.h>
#include "s3dosm.h"
#include <stdio.h>
#include <string.h> /* stdup() */
#include <unistd.h> /* unlink() */
#include <stdlib.h> /* atoi() */
static char qbuf[QBUF];
static int qlen = 0;
static int tagid = 1;  /* tagid, incremented with each new object */

static sqlite3 *db;
static char *dbFile = NULL;

/* TODO: remove '' for security reasons */
static void clean_string(char *clean, const char *dirty, int n)
{
      strncpy(clean, dirty, n);
      clean[n-1] = 0;
}

int db_add_tag(object_t *obj, const char *key, const char *val)
{
      char tagquery[MAXQ];
      char mkey[MAXQ], mval[MAXQ];
      clean_string(mkey, key, MAXQ);
      clean_string(mval, val, MAXQ);
      sqlite3_snprintf(MAXQ, tagquery, "INSERT INTO tag VALUES (%d, '%q','%q' );", (int)obj->tagid, mkey, mval);
      db_exec(tagquery, NULL, NULL);
      return(0);
}

int db_insert_node(node_t *node)
{
      char addquery[MAXQ];
      node->base.tagid = tagid++;

      if (node->base.id == 0) /* give own id */
            sqlite3_snprintf(MAXQ, addquery, "INSERT INTO node (layer_id, latitude, longitude, altitude, visible, tag_id) VALUES (%d, %f, %f, %f, %d, %d);",
                             (int)node->base.layerid,    node->lat,  node->lon,  node->alt,  node->visible, (int)node->base.tagid);
      else
            sqlite3_snprintf(MAXQ, addquery, "INSERT INTO node (layer_id, node_id,latitude, longitude, altitude, visible, tag_id) VALUES (%d, %d, %f, %f, %f, %d, %d);",
                             (int)node->base.layerid, (int)node->base.id, node->lat,  node->lon,  node->alt,  node->visible, (int)node->base.tagid);

      db_exec(addquery, NULL, NULL);
      return(0);
}

int db_insert_segment(segment_t *seg)
{
      char addquery[MAXQ];
      seg->base.tagid = tagid++;


      if (seg->base.id == 0) { /* give own id */
            printf("ugh, segment id is 0!\n");
            exit(0);
      }

      sqlite3_snprintf(MAXQ, addquery, "INSERT INTO segment (layer_id, seg_id, node_from, node_to, tag_id) VALUES (%d, %d, %d, %d, %d);",
                       (int)seg->base.layerid, (int)seg->base.id, (int)seg->from, (int)seg->to, (int)seg->base.tagid);
      db_exec(addquery, NULL, NULL);

      return(0);
}

int db_insert_way_only(way_t *way)
{
      char addquery[MAXQ];
      way->base.tagid = tagid++;
      sqlite3_snprintf(MAXQ, addquery, "INSERT INTO way (layer_id, way_id, tag_id) VALUES (%d, %d, %d);", (int)way->base.layerid, (int)way->base.id, (int)way->base.tagid);
      db_exec(addquery, NULL, NULL);
      return(0);
}

int db_insert_way_seg(way_t *way, int seg_n)
{
      char addquery[MAXQ];
      sqlite3_snprintf(MAXQ, addquery, "UPDATE segment SET way_id=%d WHERE seg_id=%d AND layer_id=%d;", (int)way->base.id, seg_n, (int)way->base.layerid);
      db_exec(addquery, NULL, NULL);
      return(0);
}

int db_insert_layer(const char *layer_name)
{
      char findquery[MAXQ];
      char addquery[MAXQ];
      char clayer[MAXQ];
      int layerid = -1;
      clean_string(clayer, layer_name, MAXQ);

      sqlite3_snprintf(MAXQ, findquery, "SELECT layer_id FROM layer WHERE name='%q';", clayer);
      db_exec(findquery, db_getint, &layerid);
      if (layerid == -1) { /* need to add */
            sqlite3_snprintf(MAXQ, addquery, "INSERT INTO layer(name) VALUES ('%q');", clayer);
            db_exec(addquery, NULL, NULL);
            db_flush();
            db_exec(findquery, db_getint, &layerid);
      }
      return(layerid);
}

#define MAGIC 1337 /* just to elevate the nodes a little bit */
static int found = 0;
/* tries to find node coordinates of ip, returns 1 if has found something */
int db_olsr_check(const char *ip, float *pos)
{
      char findquery[MAXQ];
      char clean_ip[16];
      float p[6];
      char *s = NULL;
      clean_string(clean_ip, ip, 16);
      if (NULL != (s = strchr(clean_ip, '/')))  /* don't process ip's with subnet information */
            *s = 0; /* TERMINATING ZERO!! */

      sqlite3_snprintf(MAXQ, findquery, "SELECT latitude, longitude, altitude FROM node WHERE tag_id=(SELECT tag_id FROM tag WHERE tagkey='ip' AND tagvalue='%q');", clean_ip);
      found = MAGIC;
      db_exec(findquery, db_getpoint, p);
      if (found == 1) {
            pos[0] = p[0];
            pos[1] = p[1];
            pos[2] = p[2];
            found = 0;
            return(1);
      }
      found = 0;
      return(0);
}

/* initializes the starting point of nodes  by averaging its lon/lat */
int db_olsr_node_init(float *pos)
{
      found = 0;
      db_exec("SELECT AVG(latitude) as latitude, AVG(longitude) as longitude, AVG(altitude) as altitude FROM node WHERE tag_id IN (SELECT tag_id FROM tag WHERE tagkey='ip');", db_getpoint, pos);
      printf("pos = %3.3f %3.3f %3.3f\n", pos[0], pos[1], pos[2]);
      return(0); /* return 1 if something is found, 0 if pos[0] its still 0 */
}


/* expecting a 3x float vector, returns the points coordinates */
int db_getpoint(void *data, int argc, char **argv, char **azColName)
{
      float lo = 0.0, la = 0.0, alt = 0.0;
      float *p = (float*)data;
      int i;
      for (i = 0; i < argc; i++) {
            if (argv[i]) {
                  if (0 == strcmp(azColName[i], "longitude"))   lo = strtod(argv[i], NULL);
                  else if (0 == strcmp(azColName[i], "latitude"))  la = strtod(argv[i], NULL);
                  else if (0 == strcmp(azColName[i], "altitude"))  alt = strtod(argv[i], NULL);
            }
      }
      if (lo == 0.0) {
            printf("missing lo\n");
            exit(0);
      }
      if (la == 0.0) {
            printf("missing la\n");
            exit(0);
      }
      if (found == MAGIC) alt = 2;
      calc_earth_to_eukl(la, lo, alt, p);
      p[3] = la;
      p[4] = lo;
      p[5] = alt;
      found = 1;
      return(0);
}

/* sqlite3-callback to get an integer of the database */
int db_getint(void *tagid, int S3DOSMUNUSED(argc), char **argv, char **S3DOSMUNUSED(azColName))
{
      if (argv[0] != NULL)
            *((int *)tagid) = atoi(argv[0]);
      return 0;
}

/* sqlite3-callback to get a string of the database */
static int db_getstr(void *string, int S3DOSMUNUSED(argc), char **argv, char **S3DOSMUNUSED(azColName))
{
      if (argv[0])
            strncpy((char *)string, argv[0], MAXQ);
      return(0);
}

/* get the value for a a certain tagid and keyvalue (field). Write into target, which has to be allocated with MAXQ bytes of space.
 * Nothing is written when nothing is found. */
int db_gettag(int tagid, const char *field, char *target)
{
      char query[MAXQ];
      target[0] = 0;
      sqlite3_snprintf(MAXQ, query, "SELECT tagvalue FROM tag WHERE tagkey='%q' AND tag_id=%d;", field, tagid);
      db_exec(query, db_getstr, target);
      return(target[0] == 0);
}

int callback(void *S3DOSMUNUSED(NotUsed), int argc, char **argv, char **azColName)
{
      int i;
      for (i = 0; i < argc; i++) {
            printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
      }
      printf("\n");
      return 0;
}

static int db_really_exec(const char *query, sqlite3_callback callback, const void *arg)
{
      char *zErrMsg = NULL;
      int rc;
      if (SQLITE_OK != (rc = sqlite3_exec(db, query, callback, (void*)arg, &zErrMsg))) {
            fprintf(stderr, "query: %s\n", query);
            fprintf(stderr, "SQL error: %s\n", zErrMsg);
            exit(-1);
      }
      return(SQLITE_OK != rc); /* 0 = okay */
}

/* call this if you're finished with a few stackable operations */
void db_flush(void)
{
      if (qlen > 0)
            db_really_exec(qbuf, NULL, NULL);
      qbuf[0] = 0;
      qlen = 0;
}

int db_exec(const char *query, sqlite3_callback callback, const void *arg)
{
      int ret;
#ifdef DB_STACK
      if (callback == NULL) { /* we can stack it */
            int len;
            len = strlen(query);
            if (len + qlen >= QBUF)
                  db_flush();
            strncat(qbuf, query, QBUF);
            qlen += strlen(query);
            ret = 0;
      } else
#endif

      {
            ret = db_really_exec(query, callback, arg);  /* pass it to the real function */
      }
      return(ret);
}

int db_init(const char *dbFile)
{
      int rc;
      tagid = 1;
      qbuf[0] = 0;  /* clear querybuffer */
      qlen = 0;

      unlink(dbFile); /* remove if already there */
      rc = sqlite3_open(dbFile, &db);
      if (rc) {
            fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            return(-1);
      }
      return(0);
}

int db_quit(void)
{
      sqlite3_close(db);
      if (dbFile != NULL)
            if (unlink(dbFile))
                  perror("db_quit()");
      return(0);
}

int db_create(void)
{
      db_exec("CREATE TABLE node (layer_id INT, node_id INTEGER , latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, altitude DOUBLE PRECISION, visible BOOLEAN, tag_id INT, s3doid INT, PRIMARY KEY(layer_id,node_id));", NULL, NULL);
      db_exec("CREATE TABLE segment (layer_id INT, seg_id INTEGER, node_from INT, node_to INT, tag_id INT, way_id INT,PRIMARY KEY(layer_id,seg_id));", NULL, NULL);
      db_exec("CREATE TABLE way (layer_id INTEGER, way_id INTEGER, tag_id INT, s3doid INT, PRIMARY KEY(layer_id,way_id));", NULL, NULL);
      db_exec("CREATE TABLE layer (layer_id INTEGER, name TEXT, PRIMARY KEY(layer_id));", NULL, NULL);
      db_exec("CREATE TABLE tag (tag_id INT, tagkey TEXT, tagvalue TEXT, PRIMARY KEY(tag_id, tagkey));", NULL, NULL);

      /*
      db_exec("CREATE UNIQUE INDEX node_id_index ON node (node_id,layer_id);", NULL, NULL);
      db_exec("CREATE UNIQUE INDEX segment_id_index ON segment (seg_id,layer_id);", NULL, NULL);
      db_exec("CREATE UNIQUE INDEX tag_id_index ON tag (tag_id,tagkey);", NULL, NULL);
      */
      db_flush();
      return(0);
}


Generated by  Doxygen 1.6.0   Back to index