From 0ea9b3200dacf84a22be03bbcf62915ff28debf3 Mon Sep 17 00:00:00 2001 From: Alexander Sulfrian Date: Fri, 21 Jan 2011 18:14:08 +0100 Subject: changed sql statements to prepared statements --- src/nodejs/no1.js | 316 +++++++++++++++++++++++++++++------------------------- 1 file changed, 172 insertions(+), 144 deletions(-) (limited to 'src') diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index 00c7393..94e475d 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -43,22 +43,50 @@ function toISO8601(date) { pad_two(date.getUTCMinutes()), ':', pad_two(date.getUTCSeconds()), - '+01:00' //FIX ME + '+01:00' //FIX ME ].join(''); } function createWayBboxQuery(key, value, left, bottom, right, top) { - return "SELECT id,tstamp,version,changeset_id, nodes, user_id, hstore_to_array(tags) as tags FROM ways WHERE (tags @> hstore('" + key + "','" + value + "') AND linestring && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + - left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; + return { + text: 'SELECT id,tstamp,version,changeset_id,nodes,user_id,hstore_to_array(tags) as tags \ + FROM ways \ + WHERE ( \ + tags @> hstore($1, $2) AND \ + linestring && st_setsrid(st_makebox2d( \ + st_setsrid(st_makepoint($3, $4), 4326), \ + st_setsrid(st_makepoint($5, $6), 4326) \ + ), 4326) \ + )', + values: [key, value, left, bottom, right, top], + name: 'way bbox query' + }; } function createNodeBboxQuery(key, value, left, bottom, right, top) { - return "SELECT id, user_id,tstamp,version,changeset_id, hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon FROM nodes WHERE (tags @> hstore('" + key + "','" + value + "') AND geom && st_setsrid(st_makebox2d(st_setsrid(st_makepoint(" + - left + "," + bottom + "),4326), st_setsrid(st_makepoint(" + right + "," + top + "),4326)),4326));"; + return { + text: 'SELECT id,user_id,tstamp,version,changeset_id,hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon \ + FROM nodes \ + WHERE ( \ + tags @> hstore($1, $2) AND \ + geom && st_setsrid(st_makebox2d( \ + st_setsrid(st_makepoint($3, $4), 4326), \ + st_setsrid(st_makepoint($5, $6), 4326) \ + ), 4326) \ + )', + values: [key, value, left, bottom, right, top], + name: 'node bbox query' + }; } function createNodesForWayQuery(nodes) { - return "SELECT id, tstamp, version, changeset_id, hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon FROM nodes WHERE id = ANY('" + nodes + "');"; + return { + text: 'SELECT id,tstamp,version,changeset_id,hstore_to_array(tags) as tags, X(geom) as lat, Y(geom) as lon \ + FROM nodes \ + WHERE (id = ANY($1))', + values: [nodes], + name: 'nodes for way' + }; } function nodeWorldHandler(req, res, key, value) { @@ -69,49 +97,49 @@ function nodeWorldHandler(req, res, key, value) { function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { console.log("nodeBboxHandler"); db_connect(res, function(client) { - console.log(createNodeBboxQuery(key, value, left, bottom, right, top)); - var success = false; - var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); - - query.on('error', function(err) { - console.log(err); - res.writeHead(404,{}); - res.end('\n'); - }); - - query.on('end', function() { - //console.log("end event\n"); - if(success) { - res.write(""); - res.end(); - } - else { - //empty response - res.writeHead(404,{}); - res.end(); - //perhaps write 404? is error also raised? - } - }); - - query.on('row', function(row) { - if(!success) { - success = true; - res.writeHead(200, {'Content-Type': 'text/plain'}); - res.write(""); - } - - console.log(row); - - var node = builder.begin('node') - .att('id', row.id) - .att('timetamp', toISO8601(row.tstamp)) - .att('version', row.version) - .att('changeset', row.changeset_id) - .att('lat', row.lat) - .att('lon', row.lon); - - res.write(createXmlFromRow(row)); - }); + console.log(createNodeBboxQuery(key, value, left, bottom, right, top)); + var success = false; + var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); + + query.on('error', function(err) { + console.log(err); + res.writeHead(404,{}); + res.end('\n'); + }); + + query.on('end', function() { + //console.log("end event\n"); + if(success) { + res.write(""); + res.end(); + } + else { + //empty response + res.writeHead(404,{}); + res.end(); + //perhaps write 404? is error also raised? + } + }); + + query.on('row', function(row) { + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + + console.log(row); + + var node = builder.begin('node') + .att('id', row.id) + .att('timetamp', toISO8601(row.tstamp)) + .att('version', row.version) + .att('changeset', row.changeset_id) + .att('lat', row.lat) + .att('lon', row.lon); + + res.write(createXmlFromRow(row)); + }); }); } @@ -128,8 +156,8 @@ function createXmlFromRow(row) { var temp = row.tags.replace("{","").replace("}","").split(","); for(var x=0;x"); - res.end(); - } - //res.write(""); - //res.end(); //problem!!! - } - else { - res.writeHead(404,{}); - res.end(); - //perhaps write 404? - } - }); - - query.on('row', function(row) { - if(!success) { - success = true; - res.writeHead(200, {'Content-Type': 'text/plain'}); - res.write(""); - } - //console.log(row); - if(row.nodes != '{}') { - count++; - var subquery = client.query(createNodesForWayQuery(row.nodes)); - subquery.on('error',function(err) {}); - subquery.on('end', function() { - count--; - if(count==0) - res.write(""); - res.end(); - }); - subquery.on('row', function(row) { - console.log(row); - var node = builder.begin('node') - .att('id', row.id) - .att('timetamp', toISO8601(row.tstamp)) - .att('version', row.version) - .att('changeset', row.changeset_id) - .att('lat', row.lat) - .att('lon', row.lon); - if(row.tags != '{}') { - var temp = row.tags.replace("{","").replace("}","").split(","); - for(var x=0;x"); + res.end(); + } + //res.write(""); + //res.end(); //problem!!! + } + else { + res.writeHead(404,{}); + res.end(); + //perhaps write 404? + } + }); + + query.on('row', function(row) { + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + //console.log(row); + if(row.nodes != '{}') { + count++; + var subquery = client.query(createNodesForWayQuery(row.nodes)); + subquery.on('error',function(err) {}); + subquery.on('end', function() { + count--; + if(count==0) + res.write(""); + res.end(); + }); + subquery.on('row', function(row) { + console.log(row); + var node = builder.begin('node') + .att('id', row.id) + .att('timetamp', toISO8601(row.tstamp)) + .att('version', row.version) + .att('changeset', row.changeset_id) + .att('lat', row.lat) + .att('lon', row.lon); + if(row.tags != '{}') { + var temp = row.tags.replace("{","").replace("}","").split(","); + for(var x=0;x Date: Fri, 21 Jan 2011 18:27:23 +0100 Subject: fixed codelayout --- src/nodejs/no1.js | 164 +++++++++++++++++++++++++++--------------------------- 1 file changed, 82 insertions(+), 82 deletions(-) (limited to 'src') diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index 9e59bcf..57b15b1 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -5,14 +5,14 @@ var xmlGenerator = require('./xmlGenerator.js'); // load config var config = require('./config.json'); process.argv.forEach(function (val,index, array){ - if(val=="-c"){ + if(val=="-c"){ path = array[index+1]; if( path[0] != '/'){ - path = __dirname + '/' + path; + path = __dirname + '/' + path; } config = require(path); - } - }); + } +}); var connectionString = config['connectionString']; //set up logger @@ -74,38 +74,38 @@ function nodeWorldHandler(req, res, key, value) { function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { log.error("nodeBboxHandler"); db_connect(res, function(client) { - log.info(createNodeBboxQuery(key, value, left, bottom, right, top)); - var success = false; - var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); + log.info(createNodeBboxQuery(key, value, left, bottom, right, top)); + var success = false; + var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); - query.on('error', function(err) { - log.error(err); - res.writeHead(404,{}); - res.end('\n'); - }); + query.on('error', function(err) { + log.error(err); + res.writeHead(404,{}); + res.end('\n'); + }); - query.on('end', function() { - //console.log("end event\n"); - if(success) { + query.on('end', function() { + //console.log("end event\n"); + if(success) { res.write(""); res.end(); - } - else { + } + else { //empty response res.writeHead(404,{}); res.end(); //perhaps write 404? is error also raised? - } - }); + } + }); - query.on('row', function(row) { - if(!success) { - success = true; - res.writeHead(200, {'Content-Type': 'text/plain'}); - res.write(""); - } - res.write(xmlGenerator.createNode(row)); - }); + query.on('row', function(row) { + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + res.write(xmlGenerator.createNode(row)); + }); }); } @@ -120,71 +120,71 @@ function connectionError(err, res) { function db_connect(res, callback) { pg.connect(connectionString, function(err, client) { - if(err) { + if(err) { log.error('message'); res.writeHead(404,{}); res.end(); - } else { + } else { log.info("db connection was successfull"); callback(client); - } - }); + } + }); } function wayBboxHandler(req, res, key, value, left, bottom, right, top) { db_connect(res, function(client) { - var count = 0; - var success = false; - //console.log(createWayBboxQuery(key, value, left, bottom, right, top)); - var query = client.query(createWayBboxQuery(key, value, left, bottom, right, top)); + var count = 0; + var success = false; + //console.log(createWayBboxQuery(key, value, left, bottom, right, top)); + var query = client.query(createWayBboxQuery(key, value, left, bottom, right, top)); - query.on('error', function(err) { - log.error(err); - res.writeHead(404,{}); - res.end(); - }); + query.on('error', function(err) { + log.error(err); + res.writeHead(404,{}); + res.end(); + }); - query.on('end', function() { - if(success) { + query.on('end', function() { + if(success) { if(count == 0) { - res.write(""); - res.end(); + res.write(""); + res.end(); } //res.write(""); //res.end(); //problem!!! - } - else { + } + else { res.writeHead(404,{}); res.end(); //perhaps write 404? - } - }); + } + }); - query.on('row', function(row) { - if(!success) { - success = true; - res.writeHead(200, {'Content-Type': 'text/plain'}); - res.write(""); - } - //console.log(row); - if(row.nodes != '{}') { - count++; - var subquery = client.query(createNodesForWayQuery(row.nodes)); - subquery.on('error',function(err) {}); - subquery.on('end', function() { - count--; - if(count==0) + query.on('row', function(row) { + if(!success) { + success = true; + res.writeHead(200, {'Content-Type': 'text/plain'}); + res.write(""); + } + //console.log(row); + if(row.nodes != '{}') { + count++; + var subquery = client.query(createNodesForWayQuery(row.nodes)); + subquery.on('error',function(err) {}); + subquery.on('end', function() { + count--; + if(count==0) res.write(""); - res.end(); - }); - subquery.on('row', function(row) { - res.write(xmlGenerator.createNode(row)); - }); - - //console.log(createNodesForWayQuery(row.nodes)); - } - res.write(xmlGenerator.createWay(row)); - }); + res.end(); + }); + subquery.on('row', function(row) { + res.write(xmlGenerator.createNode(row)); + }); + + //console.log(createNodesForWayQuery(row.nodes)); + } + res.write(xmlGenerator.createWay(row)); + }); }); } @@ -198,16 +198,16 @@ function relationBboxHandler(req, res, key, value, left, bottom, right, top) { } myRoutes = clutch.route404([ - //['GET /api/(\\w+)(\\[bbox=(\\d,\\d,\\d,\\d)\\])*\\[(\\w+)=(\\w+)\\]$', helloSomeone], - ['GET /api/node\\[(\\w+)=(\\w+)\\]$',nodeWorldHandler], - //['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(\\.\\d+)?),(\\d+),(\\d+),(\\d+)\\]$',nodeBboxHandler], - ['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',nodeBboxHandler], - //['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+\\.\\d+),(\\d+),(\\d+),(\\d+)\\]$',nodeBboxHandler], - ['GET /api/way\\[(\\w+)=(\\w+)\\]$',wayWorldHandler], - ['GET /api/way\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',wayBboxHandler], - ['GET /api/relation\\[(\\w+)=(\\w+)\\]$',relationWorldHandler], - //['GET /api/relation\\[(\\w+)=(\\w+)\\](\\[bbox=(\\d),(\\d),(\\d),(\\d)\\])$',relationBboxHandler], - ]); + //['GET /api/(\\w+)(\\[bbox=(\\d,\\d,\\d,\\d)\\])*\\[(\\w+)=(\\w+)\\]$', helloSomeone], + ['GET /api/node\\[(\\w+)=(\\w+)\\]$',nodeWorldHandler], + //['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(\\.\\d+)?),(\\d+),(\\d+),(\\d+)\\]$',nodeBboxHandler], + ['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',nodeBboxHandler], + //['GET /api/node\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+\\.\\d+),(\\d+),(\\d+),(\\d+)\\]$',nodeBboxHandler], + ['GET /api/way\\[(\\w+)=(\\w+)\\]$',wayWorldHandler], + ['GET /api/way\\[(\\w+)=(\\w+)\\]\\[bbox=(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?),(\\d+(?:\\.\\d+)?)\\]$',wayBboxHandler], + ['GET /api/relation\\[(\\w+)=(\\w+)\\]$',relationWorldHandler], + //['GET /api/relation\\[(\\w+)=(\\w+)\\](\\[bbox=(\\d),(\\d),(\\d),(\\d)\\])$',relationBboxHandler], +]); var http = require('http'); -- cgit v1.2.3 From 6238d916128a31ceebbef3a05a1f684705772c92 Mon Sep 17 00:00:00 2001 From: Alexander Sulfrian Date: Fri, 21 Jan 2011 18:38:51 +0100 Subject: removed some unused debug --- src/nodejs/no1.js | 2 -- 1 file changed, 2 deletions(-) (limited to 'src') diff --git a/src/nodejs/no1.js b/src/nodejs/no1.js index 57b15b1..37e106d 100644 --- a/src/nodejs/no1.js +++ b/src/nodejs/no1.js @@ -72,9 +72,7 @@ function nodeWorldHandler(req, res, key, value) { } function nodeBboxHandler(req, res, key, value, left, bottom, right, top) { - log.error("nodeBboxHandler"); db_connect(res, function(client) { - log.info(createNodeBboxQuery(key, value, left, bottom, right, top)); var success = false; var query = client.query(createNodeBboxQuery(key, value, left, bottom, right, top)); -- cgit v1.2.3