handler_osm_sql.h

   1 /* -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
   2
   3 /* Cherokee/MonetDB OSM Handler
   4  *
   5  * SQL statements
   6  *
   7  * Authors:
   8  *      Stefan de Konink <handlerosm@kinkrsoftware.nl>
   9  */
  10
  11 #include <cherokee/common-internal.h>
  12 #include "handler_osm.h"
  13
  14 #define CHANGES_TIME_DEFAULT 1
  15 #define CHANGES_TIME_MAX     24
  16
  17 #define OSM_TAG         "osm"
  18 #define OSM_VERSION     "0.5"
  19 #define OSM_GENERATOR   "Cherokee/MonetDB OSM Server"
  20 #define OSM_ATTRIBUTES  "version=\"" OSM_VERSION "\" generator=\"" OSM_GENERATOR "\""
  21
  22 #define NODE_TAG        "node"
  23 #define NODE_ATTRIBUTES "id=\"%s\" lat=\"%s\" lon=\"%s\" visible = \"true\" user=\"%s\" timestamp=\"%s\""
  24
  25 #define TAG_TAG         "tag"
  26 #define TAG_ATTRIBUTES  "k=\"%s\" v=\"%s\""
  27
  28 #define WAY_TAG         "way"
  29 #define WAY_ATTRIBUTES  "id=\"%s\"  visible = \"true\" user=\"%s\" timestamp=\"%s\""
  30
  31 #define ND_TAG          "nd"
  32 #define ND_ATTRIBUTES   "ref=\"%s\""
  33
  34 #define RELATION_TAG    "relation"
  35 #define RELATION_ATTRIBUTES "id=\"%s\" visible = \"true\" user=\"%s\" timestamp=\"%s\""
  36
  37 #define MEMBER_TAG      "member"
  38 #define MEMBER_ATTRIBUTES "type=\"%s\" ref=\"%s\" role=\"%s\""
  39 #define MEMBER_TYPE_NODE "node"
  40 #define MEMBER_TYPE_WAY "way"
  41 #define MEMBER_TYPE_RELATION "relation"
  42
  43 #define XMLHEADER    "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"CRLF
  44 #define XML(THISTAG,LEVEL) LEVEL "<" THISTAG ## _TAG " " THISTAG ## _ATTRIBUTES
  45 #define XMLCONTINUE   ">" CRLF
  46 #define XMLCLOSESHORT "/>" CRLF
  47 #define XMLCLOSE(THISTAG,LEVEL) LEVEL "</" THISTAG ## _TAG ">" CRLF
  48
  49 #define PREFERENCES_TAG   "preferences"
  50 #define PREFERENCES_ATTRIBUTES ""
  51 #define PREFERENCE_TAG    "preference"
  52 #define PREFERENCE_ATTRIBUTES "k=\"%s\" v=\"%s\""
  53
  54
  55
  56
  57
  58
  59
  60
  61 #ifdef LEGACY_SQL
  62         #define BBOX_VA_ARGS            top, bottom, left, right
  63         #define SQL_BY_BBOX             "lat BETWEEN %f AND %f AND lon BETWEEN %f AND %f"
  64         #define SQL_NODES               "nodes_legacy"
  65         #define SQL_NODE_SELECT         "SELECT DISTINCT id, lon, lat, usernames.username, timestamp "
  66 #else
  67         #define BBOX_VA_ARGS            bottom, left, bottom, right, top, right, top, left, bottom, left
  68         #define SQL_BY_BBOX             "WithIn(g, 'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))') = TRUE"
  69         #define SQL_NODES               "nodes"
  70         #define SQL_NODE_SELECT         "SELECT DISTINCT id, X(g), Y(g), usernames.username, timestamp"
  71 #endif
  72 //      #define SQL_NODE_CREATE_GET_ID   "SELECT id FROM " SQL_NODES " ORDER BY id DESC LIMIT 1;"
  73 //      #define SQL_NODE_CREATE          "INSERT INTO " SQL_NODES " (lon, lat, username, timestamp) VALUES (%f, %f, %d, %s)"
  74         #define SQL_NODE_CREATE          "INSERT INTO " SQL_NODES " (lon, lat, username) VALUES (%f, %f, %d)"
  75         #define SQL_NODE_CREATE_NODE_TAG "INSERT INTO node_tags (node, k, v) VALUES (%d, '%s', '%s')"
  76         #define SQL_NODE_UPDATE_BY_ID    "UPDATE " SQL_NODES " SET lon = %f, lat = %f, username = %d WHERE id = %d"
  77         #define SQL_NODE_UPDATE_NODE_TAG "UPDATE node_tags SET v = '%s' WHERE node = %d AND k = '%s'"
  78
  79         #define SQL_NODE_DELETE_NODE_TAG "DELETE FROM node_tags WHERE node = %lu AND k NOT IN (%s)"
  80
  81         #define SQL_RELATION_CREATE      "INSERT INTO relations (username) VALUES (%lu)"
  82         #define SQL_RELATION_UPDATE      "UPDATE relations SET username = %d WHERE id = %lu"
  83         #define SQL_RELATION_CREATE_NODE_TAG "INSERT INTO relation_tags (relation, k, v) VALUES (%d, '%s', '%s')"
  84         #define SQL_RELATION_UPDATE_NODE_TAG "UPDATE relation_tags SET v = '%s' WHERE relation = %d AND k = '%s'"
  85         #define SQL_RELATION_DELETE_NODE_TAG "DELETE FROM relation_tags WHERE relation = %d AND k NOT IN (%s)"
  86
  87         #define SQL_WAY_CREATE          "INSERT INTO ways (username) VALUES (%lu)"
  88         #define SQL_WAY_UPDATE          "UPDATE ways SET username = %d WHERE id = %lu"
  89         #define SQL_WAY_CREATE_NODE_TAG "INSERT INTO way_tags (walat, k, v) VALUES (%d, '%s', '%s')"
  90         #define SQL_WAY_UPDATE_NODE_TAG "UPDATE way_tags SET v = '%s' WHERE walat = %d AND k = '%s'"
  91         #define SQL_WAY_DELETE_NODE_TAG "DELETE FROM way_tags WHERE walat = %d AND k NOT IN (%s)"
  92
  93
  94         #define SQL_TRANSACTION_START    "START TRANSACTION"
  95         #define SQL_TRANSACTION_COMMIT   "COMMIT"
  96         #define SQL_TRANSACTION_ROLLBACK "ROLLBACK"
  97
  98
  99         #define SQL_NODE                SQL_NODE_SELECT \
 100                                         "FROM " SQL_NODES ", usernames "\
 101                                         "WHERE " SQL_NODES ".username = usernames.id"
 102
 103
 104         #define SQL_BY_ID               " id = %ld"
 105         #define SQL_ORDER_BY_ID         " ORDER BY id"
 106
 107         #define SQL_NODE_BY_ID          SQL_NODE " AND " SQL_BY_ID
 108         #define SQL_NODE_BY_BBOX        SQL_NODE " AND " SQL_BY_BBOX
 109
 110 /*      #define SQL_NODE_BY_BBOX        SQL_NODE_SELECT \
 111                                                  "FROM " SQL_NODES ", members_node, usernames, members_node AS loc1, " SQL_NODES " AS loc2 "\
 112                                                  "WHERE " SQL_NODES ".id = members_node.to_node AND "\
 113                                                           SQL_NODES ".username = usernames.id AND "\
 114                                                         "members_node.relation = loc1.relation AND "\
 115                                                         "loc2.id = loc1.to_node AND loc2.lat > %f AND loc2.lon > %f AND loc2.lat < %f AND loc2.lon < %f" \
 116                                                         "ORDER BY " SQL_NODES ".id"*/
 117         //#define SQL_NODE_BY_BBOX        "SELECT DISTINCT id, lon, lat, '0', timestamp FROM nodes_legaclat, members_node, members_node AS loc1, nodes_legaclat AS loc2 WHERE nodes_legacy.id = members_node.to_node AND members_node.relation = loc1.relation AND loc2.id = loc1.to_node AND loc2.lat > %f AND loc2.lon > %f AND loc2.lat < %f AND loc2.lon < %f;"
 118
 119 // Oude
 120 //      #define SQL_NODE_BY_BBOX        "SELECT DISTINCT id, lon, lat, username, timestamp FROM nodes_legaclat, usernames, members_node WHERE nodes_legacy.username = usernames.id AND members_node.to_node = nodes_legacy.id AND members_node.relation IN (SELECT DISTINCT relation FROM members_node, nodes_legaclat WHERE members_node.to_node = nodes_legacy.id AND lat > %f AND lon > %f AND lat < %f AND lon < %f) ORDER BY nodes_legacy.id;"
 121
 122         #define SQL_NODE_TAGS_SELECT    "SELECT node, k, v "
 123         #define SQL_NODE_TAGS           SQL_NODE_TAGS_SELECT " "\
 124                                         "FROM node_tags, " SQL_NODES " "\
 125                                         "WHERE node_tags.node = " SQL_NODES ".id"
 126
 127         #define SQL_NODE_TAGS_BY_ID     SQL_NODE_TAGS " AND " SQL_BY_ID SQL_ORDER_BY_ID
 128         #define SQL_NODE_TAGS_BY_BBOX   SQL_NODE_TAGS " AND " SQL_BY_BBOX SQL_ORDER_BY_ID
 129
 130         #define SQL_DELETE_NODE_BY_ID   "UPDATE " SQL_NODES " SET visible = FALSE WHERE id = %ld"
 131
 132         #define SQL_RELATION_SELECT     "SELECT DISTINCT id, usernames.username, timestamp "
 133         #define SQL_RELATION            SQL_RELATION_SELECT \
 134                                         "FROM relations, usernames "\
 135                                         "WHERE relations.username = usernames.id"
 136
 137         #define SQL_RELATION_BY_NODE    SQL_RELATION_SELECT \
 138                                         "FROM relations, usernames, " SQL_NODES ", members_node "\
 139                                         "WHERE relations.username = usernames.id AND "\
 140                                               SQL_NODES ".id = members_node.to_node AND members_node.relation = id"
 141
 142         #define SQL_RELATION_BY_NODE_ID SQL_RELATION_BY_NODE " AND " SQL_NODES ".id = %ld" SQL_ORDER_BY_ID
 143         #define SQL_RELATION_BY_BBOX    SQL_RELATION_BY_NODE " AND " SQL_BY_BBOX SQL_ORDER_BY_ID
 144         #define SQL_RELATION_BY_ID      SQL_RELATION " AND id = %ld"
 145
 146
 147         #define SQL_REL_MEM_NOD_SELECT  "SELECT DISTINCT relation, to_node, role "
 148         #define SQL_RELATION_MEMBER_NODE SQL_REL_MEM_NOD_SELECT \
 149                                          "FROM members_node "
 150         #define SQL_RELATION_MEMBER_NODE_BY_ID SQL_RELATION_MEMBER_NODE "WHERE relation=%ld"
 151
 152         #define SQL_REL_MEM_REL_SELECT  "SELECT DISTINCT relation, to_relation, role "
 153         #define SQL_RELATION_MEMBER_RELATION SQL_REL_MEM_REL_SELECT \
 154                                              "FROM members_relation "
 155
 156 //      #define SQL_RELATION_MEMBER_RELATION_BY_ID SQL_RELATION_MEMBER_RELATION "WHERE relation=%ld"
 157         #define SQL_RELATION_MEMBER_RELATION_BY_ID "SELECT members_relation.relation, to_relation, role, (v='way') FROM members_relation LEFT JOIN relation_tags ON to_relation = relation_tags.relation WHERE k='type' AND v='way' AND members_relation.relation=%ld"
 158
 159         #define SQL_RELATION_MEMBER_NODE_BY_BBOX SQL_REL_MEM_NOD_SELECT \
 160                                                  "FROM members_node, members_node AS constr, " SQL_NODES " "\
 161                                                  "WHERE members_node.relation = constr.relation AND "\
 162                                                         SQL_NODES ".id = constr.to_node AND " SQL_BY_BBOX \
 163                                                       " ORDER BY relation, to_node"
 164
 165         #define SQL_RELATION_MEMBER_RELATION_BY_BBOX SQL_REL_MEM_NOD_SELECT \
 166                                                      "FROM members_relation, members_node, " SQL_NODES " "\
 167                                                      "WHERE members_node.relation = members_relation.relation AND "\
 168                                                       SQL_NODES ".id = members_node.to_node AND " SQL_BY_BBOX " ORDER BY relation, to_node"
 169
 170         #define SQL_RELATION_TAGS_SELECT "SELECT DISTINCT relation, k, v "
 171         #define SQL_RELATION_TAGS       SQL_RELATION_TAGS_SELECT " "\
 172                                         "FROM relation_tags "\
 173                                         "WHERE "
 174
 175         #define SQL_ORDER_BY_RELATION   " ORDER BY relation"
 176         #define SQL_RELATION_TAGS_BY_ID SQL_RELATION_TAGS "relation = %ld" SQL_ORDER_BY_RELATION
 177
 178         #define SQL_RELATION_TAGS_BY_BBOX1 SQL_RELATION_TAGS_SELECT \
 179                                         "FROM " SQL_NODES ", members_node, relation_tags "\
 180                                         "WHERE "\
 181                                                SQL_NODES ".id = members_node.to_node AND members_node.relation = relation_tags.relation AND "\
 182                                                SQL_BY_BBOX
 183         #define SQL_RELATION_TAGS_BY_BBOX SQL_RELATION_TAGS_BY_BBOX1 SQL_ORDER_BY_RELATION
 184
 185
 186         #define SQL_DELETE_RELATION_BY_ID "UPDATE relations SET visible = FALSE WHERE id = %ld"
 187         #define SQL_DELETE_WAY_BY_ID "UPDATE ways SET visible = FALSE WHERE id = %ld"
 188
 189         #define SQL_WAY                 SQL_RELATION_SELECT \
 190                                         "FROM relations, usernames, relation_tags AS constr "\
 191                                         "WHERE relations.username = usernames.id AND "\
 192                                         "constr.relation = relations.id AND constr.k='type' AND constr.v='way'"
 193         #define SQL_WAY_BY_ID           SQL_WAY " AND id=%ld"
 194         #define SQL_WAY_ND_SELECT       "SELECT DISTINCT relation, to_node "
 195         #define SQL_WAY_ND              SQL_WAY_ND_SELECT \
 196                                         "FROM members_node, relation_tags "\
 197                                         "WHERE relation_tags.relation = members_node.relation AND k='type' AND v='way'"
 198
 199         #define SQL_WAY_ND_BY_ID        SQL_WAY_ND " AND relation=%ld ORDER BY relation, idx"
 200         #define SQL_WAY_BY_NODE         SQL_RELATION_SELECT \
 201                                         "FROM relations, usernames, " SQL_NODES ", members_node, relation_tags AS constr "\
 202                                         "WHERE relations.username = usernames.id AND constr.relation = id "\
 203                                         "AND constr.k='type' AND constr.v='way' AND "\
 204                                         SQL_NODES ".id = members_node.to_node AND members_node.relation = id"
 205         #define SQL_WAY_BY_NODE_ID      SQL_WAY_BY_NODE " AND " SQL_NODES ".id = %ld ORDER BY id"
 206         #define SQL_WAY_BY_BBOX         SQL_WAY_BY_NODE "AND " SQL_BY_BBOX " ORDER BY id"
 207 /*      #define SQL_ND_BY_BBOX          SQL_WAY_ND_SELECT \
 208                                         "FROM members_node, relation_tags, " SQL_NODES " "\
 209                                         "WHERE relation_tags.relation = members_node.relation AND k='type' AND v='way' "\
 210                                         "AND " SQL_NODES ".id = members_node.to_node" SQL_BY_BBOX " ORDER BY relation, idx"*/
 211 //      #define SQL_ND_BY_BBOX          "SELECT relation, to_node FROM members_node WHERE relation IN (SELECT DISTINCT relation FROM relation_tags, members_node, nodes_legaclat WHERE relation_tags.relation = members_node.relation AND members_node.to_node = nodes_legacy.id AND lat > %f AND lon > %f AND lat < %f AND lon < %f AND relation_tags.k = 'type' AND relation_tags.v = 'way') ORDER BY members_node.relation, members_node.idx;"
 212 //
 213
 214         #define SQL_ND_BY_BBOX          "SELECT relation, to_node FROM members_node WHERE relation IN (SELECT DISTINCT relations.id FROM nodes_legaclat, members_node, relations, relation_tags WHERE relations.id = relation_tags.relation AND members_node.relation = relations.id AND members_node.to_node = nodes_legacy.id AND lon > %f AND lat > %f AND lon < %f AND lat < %f AND k = 'type' AND v = 'way') ORDER BY relation, idx"
 215
 216 //      #define SQL_ND_BY_BBOX          "SELECT relation, to_node FROM members_node WHERE relation IN (SELECT id FROM relations WHERE id IN (SELECT DISTINCT relation FROM relation_tags, members_node, nodes_legaclat WHERE relation_tags.relation = members_node.relation AND members_node.to_node = nodes_legacy.id AND lat > %f AND lon > %f AND lat < %f AND lon < %f AND relation_tags.k = 'type' AND relation_tags.v = 'way')) ORDER BY members_node.relation, members_node.idx;"
 217
 218         #define SQL_WAY_TAGS_BY_ID      SQL_RELATION_TAGS_BY_ID
 219         #define SQL_WAY_TAGS_BY_BBOX    SQL_RELATION_TAGS_BY_BBOX1 SQL_ORDER_BY_RELATION
 220 //      #define SQL_WAY_TAGS_BY_BBOX    SQL_RELATION_TAGS_BY_BBOX1 " AND k<>'type' AND v<>'way' " SQL_ORDER_BY_RELATION