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 #define UINT_BITMAX "11930464.71111111"
  56
  57 #define SQL_NOW "", "now", ""
  58
  59 #ifdef LEGACY_SQL
  60         #define BBOX_VA_ARGS            top, bottom, left, right
  61         #define SQL_BY_BBOX             "lat BETWEEN %f AND %f AND long BETWEEN %f AND %f"
  62         #define SQL_NODES               "nodes_legacy"
  63         #define SQL_NODE_SELECT         "SELECT DISTINCT id, long, lat, usernames.username, timestamp "
  64 #elif  LEGACY_SQL_UINT
  65         #define BBOX_VA_ARGS            top, bottom, left, right
  66         #define SQL_BY_BBOX             "lat BETWEEN (%f * " UINT_BITMAX ") AND (%f * " UINT_BITMAX ") AND long BETWEEN (%f * " UINT_BITMAX ") AND (%f * " UINT_BITMAX ")"
  67         #define SQL_NODES               "nodes_legacy_uint"
  68         #define SQL_NODE_SELECT         "SELECT DISTINCT id, CAST(long AS double) / " UINT_BITMAX ", CAST(lat AS double) / " UINT_BITMAX ", usernames.username, timestamp "
  69 #else
  70         #define BBOX_VA_ARGS            bottom, left, bottom, right, top, right, top, left, bottom, left
  71         #define SQL_BY_BBOX             "WithIn(g, 'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))') = TRUE"
  72         #define SQL_NODES               "nodes_legacy_gis"
  73         #define SQL_NODE_SELECT         "SELECT DISTINCT id, X(g), Y(g), usernames.username, timestamp"
  74 #endif
  75 //      #define SQL_NODE_CREATE_GET_ID   "SELECT id FROM " SQL_NODES " ORDER BY id DESC LIMIT 1;"
  76 //      #define SQL_NODE_CREATE          "INSERT INTO " SQL_NODES " (long, lat, username, timestamp) VALUES (%f, %f, %d, %s)"
  77         #define SQL_NODE_CREATE          "INSERT INTO " SQL_NODES " (long, lat, username) VALUES (%f, %f, %d)"
  78         #define SQL_NODE_CREATE_NODE_TAG "INSERT INTO node_tags (node, k, v) VALUES (%d, '%s', '%s')"
  79         #define SQL_NODE_UPDATE_BY_ID    "UPDATE " SQL_NODES " SET long = %f, lat = %f, username = %d, timestamp = %s%s%s WHERE id = %d"
  80         #define SQL_NODE_UPDATE_NODE_TAG "UPDATE node_tags SET v = '%s' WHERE node = %d AND k = '%s'"
  81
  82         #define SQL_NODE_DELETE_NODE_TAG "DELETE FROM node_tags WHERE node = %lu AND k NOT IN (%s)"
  83
  84         #define SQL_RELATION_CREATE      "INSERT INTO relations (username) VALUES (%lu)"
  85         #define SQL_RELATION_UPDATE      "UPDATE relations SET username = %d, timestamp = %s%s%s WHERE id = %lu"
  86         #define SQL_RELATION_CREATE_NODE_TAG "INSERT INTO relation_tags (relation, k, v) VALUES (%d, '%s', '%s')"
  87         #define SQL_RELATION_UPDATE_NODE_TAG "UPDATE relation_tags SET v = '%s' WHERE relation = %d AND k = '%s'"
  88         #define SQL_RELATION_DELETE_NODE_TAG "DELETE FROM relation_tags WHERE relation = %d AND k NOT IN (%s)"
  89
  90         #define SQL_WAY_CREATE          "INSERT INTO ways (username) VALUES (%lu)"
  91         #define SQL_WAY_UPDATE          "UPDATE ways SET username = %d, timestamp = %s%s%s WHERE id = %lu"
  92         #define SQL_WAY_CREATE_NODE_TAG "INSERT INTO way_tags (walat, k, v) VALUES (%d, '%s', '%s')"
  93         #define SQL_WAY_UPDATE_NODE_TAG "UPDATE way_tags SET v = '%s' WHERE walat = %d AND k = '%s'"
  94         #define SQL_WAY_DELETE_NODE_TAG "DELETE FROM way_tags WHERE walat = %d AND k NOT IN (%s)"
  95
  96
  97         #define SQL_TRANSACTION_START    "START TRANSACTION"
  98         #define SQL_TRANSACTION_COMMIT   "COMMIT"
  99         #define SQL_TRANSACTION_ROLLBACK "ROLLBACK"
 100
 101
 102         #define SQL_NODE                SQL_NODE_SELECT \
 103                                         "FROM " SQL_NODES ", usernames "\
 104                                         "WHERE " SQL_NODES ".username = usernames.id"
 105
 106
 107         #define SQL_BY_ID               " id = %ld"
 108         #define SQL_ORDER_BY_ID         " ORDER BY id"
 109
 110         #define SQL_NODE_BY_ID          SQL_NODE " AND " SQL_BY_ID
 111         #define SQL_NODE_BY_BBOX        SQL_NODE " AND " SQL_BY_BBOX
 112
 113 /*      #define SQL_NODE_BY_BBOX        SQL_NODE_SELECT \
 114                                                  "FROM " SQL_NODES ", members_node, usernames, members_node AS loc1, " SQL_NODES " AS loc2 "\
 115                                                  "WHERE " SQL_NODES ".id = members_node.to_node AND "\
 116                                                           SQL_NODES ".username = usernames.id AND "\
 117                                                         "members_node.relation = loc1.relation AND "\
 118                                                         "loc2.id = loc1.to_node AND loc2.lat > %f AND loc2.long > %f AND loc2.lat < %f AND loc2.long < %f" \
 119                                                         "ORDER BY " SQL_NODES ".id"*/
 120         //#define SQL_NODE_BY_BBOX        "SELECT DISTINCT id, long, 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.long > %f AND loc2.lat < %f AND loc2.long < %f;"
 121
 122 // Oude
 123 //      #define SQL_NODE_BY_BBOX        "SELECT DISTINCT id, long, 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 long > %f AND lat < %f AND long < %f) ORDER BY nodes_legacy.id;"
 124
 125         #define SQL_NODE_TAGS_SELECT    "SELECT node, k, v "
 126         #define SQL_NODE_TAGS           SQL_NODE_TAGS_SELECT " "\
 127                                         "FROM node_tags, " SQL_NODES " "\
 128                                         "WHERE node_tags.node = " SQL_NODES ".id"
 129
 130         #define SQL_NODE_TAGS_BY_ID     SQL_NODE_TAGS " AND " SQL_BY_ID SQL_ORDER_BY_ID
 131         #define SQL_NODE_TAGS_BY_BBOX   SQL_NODE_TAGS " AND " SQL_BY_BBOX SQL_ORDER_BY_ID
 132
 133         #define SQL_DELETE_NODE_BY_ID   "UPDATE " SQL_NODES " SET visible = FALSE WHERE id = %ld"
 134
 135         #define SQL_RELATION_SELECT     "SELECT DISTINCT id, usernames.username, timestamp "
 136         #define SQL_RELATION            SQL_RELATION_SELECT \
 137                                         "FROM relations, usernames "\
 138                                         "WHERE relations.username = usernames.id"
 139
 140         #define SQL_RELATION_BY_NODE    SQL_RELATION_SELECT \
 141                                         "FROM relations, usernames, " SQL_NODES ", members_node "\
 142                                         "WHERE relations.username = usernames.id AND "\
 143                                               SQL_NODES ".id = members_node.to_node AND members_node.relation = id"
 144
 145         #define SQL_RELATION_BY_NODE_ID SQL_RELATION_BY_NODE " AND " SQL_NODES ".id = %ld" SQL_ORDER_BY_ID
 146         #define SQL_RELATION_BY_BBOX    SQL_RELATION_BY_NODE " AND " SQL_BY_BBOX SQL_ORDER_BY_ID
 147         #define SQL_RELATION_BY_ID      SQL_RELATION " AND id = %ld"
 148
 149
 150         #define SQL_REL_MEM_NOD_SELECT  "SELECT DISTINCT relation, to_node, role "
 151         #define SQL_RELATION_MEMBER_NODE SQL_REL_MEM_NOD_SELECT \
 152                                          "FROM members_node "
 153         #define SQL_RELATION_MEMBER_NODE_BY_ID SQL_RELATION_MEMBER_NODE "WHERE relation=%ld"
 154
 155         #define SQL_REL_MEM_REL_SELECT  "SELECT DISTINCT relation, to_relation, role "
 156         #define SQL_RELATION_MEMBER_RELATION SQL_REL_MEM_REL_SELECT \
 157                                              "FROM members_relation "
 158
 159 //      #define SQL_RELATION_MEMBER_RELATION_BY_ID SQL_RELATION_MEMBER_RELATION "WHERE relation=%ld"
 160         #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"
 161
 162         #define SQL_RELATION_MEMBER_NODE_BY_BBOX SQL_REL_MEM_NOD_SELECT \
 163                                                  "FROM members_node, members_node AS constr, " SQL_NODES " "\
 164                                                  "WHERE members_node.relation = constr.relation AND "\
 165                                                         SQL_NODES ".id = constr.to_node AND " SQL_BY_BBOX \
 166                                                       " ORDER BY relation, to_node"
 167
 168         #define SQL_RELATION_MEMBER_RELATION_BY_BBOX SQL_REL_MEM_NOD_SELECT \
 169                                                      "FROM members_relation, members_node, " SQL_NODES " "\
 170                                                      "WHERE members_node.relation = members_relation.relation AND "\
 171                                                       SQL_NODES ".id = members_node.to_node AND " SQL_BY_BBOX " ORDER BY relation, to_node"
 172
 173         #define SQL_RELATION_TAGS_SELECT "SELECT DISTINCT relation, k, v "
 174         #define SQL_RELATION_TAGS       SQL_RELATION_TAGS_SELECT " "\
 175                                         "FROM relation_tags "\
 176                                         "WHERE "
 177
 178         #define SQL_ORDER_BY_RELATION   " ORDER BY relation"
 179         #define SQL_RELATION_TAGS_BY_ID SQL_RELATION_TAGS "relation = %ld" SQL_ORDER_BY_RELATION
 180
 181         #define SQL_RELATION_TAGS_BY_BBOX1 SQL_RELATION_TAGS_SELECT \
 182                                         "FROM " SQL_NODES ", members_node, relation_tags "\
 183                                         "WHERE "\
 184                                                SQL_NODES ".id = members_node.to_node AND members_node.relation = relation_tags.relation AND "\
 185                                                SQL_BY_BBOX
 186         #define SQL_RELATION_TAGS_BY_BBOX SQL_RELATION_TAGS_BY_BBOX1 SQL_ORDER_BY_RELATION
 187
 188
 189         #define SQL_DELETE_RELATION_BY_ID "UPDATE relations SET visible = FALSE WHERE id = %ld"
 190         #define SQL_DELETE_WAY_BY_ID "UPDATE ways SET visible = FALSE WHERE id = %ld"
 191
 192         #define SQL_WAY                 SQL_RELATION_SELECT \
 193                                         "FROM relations, usernames, relation_tags AS constr "\
 194                                         "WHERE relations.username = usernames.id AND "\
 195                                         "constr.relation = relations.id AND constr.k='type' AND constr.v='way'"
 196         #define SQL_WAY_BY_ID           SQL_WAY " AND id=%ld"
 197         #define SQL_WAY_ND_SELECT       "SELECT DISTINCT relation, to_node "
 198         #define SQL_WAY_ND              SQL_WAY_ND_SELECT \
 199                                         "FROM members_node, relation_tags "\
 200                                         "WHERE relation_tags.relation = members_node.relation AND k='type' AND v='way'"
 201
 202         #define SQL_WAY_ND_BY_ID        SQL_WAY_ND " AND relation=%ld ORDER BY relation, idx"
 203         #define SQL_WAY_BY_NODE         SQL_RELATION_SELECT \
 204                                         "FROM relations, usernames, " SQL_NODES ", members_node, relation_tags AS constr "\
 205                                         "WHERE relations.username = usernames.id AND constr.relation = id "\
 206                                         "AND constr.k='type' AND constr.v='way' AND "\
 207                                         SQL_NODES ".id = members_node.to_node AND members_node.relation = id"
 208         #define SQL_WAY_BY_NODE_ID      SQL_WAY_BY_NODE " AND " SQL_NODES ".id = %ld ORDER BY id"
 209         #define SQL_WAY_BY_BBOX         SQL_WAY_BY_NODE "AND " SQL_BY_BBOX " ORDER BY id"
 210 /*      #define SQL_ND_BY_BBOX          SQL_WAY_ND_SELECT \
 211                                         "FROM members_node, relation_tags, " SQL_NODES " "\
 212                                         "WHERE relation_tags.relation = members_node.relation AND k='type' AND v='way' "\
 213                                         "AND " SQL_NODES ".id = members_node.to_node" SQL_BY_BBOX " ORDER BY relation, idx"*/
 214 //      #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 long > %f AND lat < %f AND long < %f AND relation_tags.k = 'type' AND relation_tags.v = 'way') ORDER BY members_node.relation, members_node.idx;"
 215 //
 216
 217         #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 long > %f AND lat > %f AND long < %f AND lat < %f AND k = 'type' AND v = 'way') ORDER BY relation, idx"
 218
 219 //      #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 long > %f AND lat < %f AND long < %f AND relation_tags.k = 'type' AND relation_tags.v = 'way')) ORDER BY members_node.relation, members_node.idx;"
 220
 221         #define SQL_WAY_TAGS_BY_ID      SQL_RELATION_TAGS_BY_ID
 222         #define SQL_WAY_TAGS_BY_BBOX    SQL_RELATION_TAGS_BY_BBOX1 SQL_ORDER_BY_RELATION
 223 //      #define SQL_WAY_TAGS_BY_BBOX    SQL_RELATION_TAGS_BY_BBOX1 " AND k<>'type' AND v<>'way' " SQL_ORDER_BY_RELATION
 224
 225         #define SQL_USERID_BY_NAME      "SELECT '0';"