The current code assumes "the_geom" and "the_geom_webmercator" both include geometric data.
ref: app/controllers/app.js

This ticket is to drop the assumption and actually check each field type instead.

Add support for format=svg.

How to return attributes ?
Should style be left to caller ? (I guess so, assuming it is possible).
Should identifier attribute be parameterizable or forced as "cartodb_id" or similar ?
Should transforms be left to caller ? (I guess so, assuming it is possible).
Should we automatically provide bounding box information to help with transforms ?

The fix of #13 broke .. RETURNING .. queries because now any non-select is threated specially.

Needs to be fixed in the "master" branch.
Also needs automated tests, for both RETURNING and returning something meaningful for "affected rows" when there's no RETURNING clause

- Mention the requirement of a "publicuser" postgresql user
- Move configuration section before usage section (or within it)
- Merge "core requirements" and "dependencies" section ?
- ...

Basically just try to set things up w/out knowing anything about node.js and see if it works.
It's been done today with the "cartodb" README with good results.

It's currently impossible to use the SQL-API without setting up a redis database and having the redis server running. Since it seems that redis use is authentication it would be nice to have the possibility to use other authentication mechanisms making it easier to just start an SQL-API with only PostgreSQL running.

Right now v2 caches everything. It should not cache when there is a word of INSERT/UPDATE etc.

But I am not sure if the cache is being handle on the SQL-API @lbosque @javisantana might have some more info about this.

uncaught: AssertionError: null == "1"
at /home/src/cartodb/CartoDB-SQL-API/test/unit/metadata.test.js:21:16

brianc/node-postgres#48

Allow exporting a query as a shapefile

expresso keeps hanging..

this sidesteps the obvious problems of us having to bypass the cache for mutable queries.

We know that all non-auth are read only, and also can assume that authenticated queries are by and large made as a result of writes being made against a table.

See 86e1439#commitcomment-1804109

There seem to be some problems with the connection pooling during execution of a unit test. May be related to pooling since disabling it fixed the test.

both acceptance tests hang, with both expresso and mocha, must be some callback never being invoked...

As of 86e1439 the psql unit test fails. Need to figure out why.
See also issue #47

Would be nice to be able to push data to cartodb and then read right back into TileMill. TileMill likes a helping hand to know the file format of urls that respond with geodata and setting content-disposition will enable this. So, if the user requests:

http://tileio.cartodb.com/api/v1/sql?q=SELECT%20*%20FROM%20places_by_lon_lat&format=geojson
Then having cartodb set content-disposition to something like foo.geojson or foo.json would allow the data to be autorecognized as geojson in tilemill.

ref: https://github.com/Vizzuality/cartodb/issues/217

http://support.cartodb.com/discussions/suggestions/50-sql-api-different-json-object-for-non-query-request

when the api fails (i.e a query has syntax errors) the response has no cross origin headers and it is not posible to manage the error on the client side

see #6

create/drop table actions
logging

etc

We want to support KML output.
It seems we supported it long ago, given issue #5
Commit 19b2829 by Andrew Hill seems to be the one introducing it on September 2011. Could not find the commit that removed it.

https://github.com/ql-io/cluster2

This will let us bump to node 0.6

bc back history

as node does not communicate with postgres we have to:

• setup synching of the password hash to redis
• reimpiment the password hashing algorithm that rails uses in redis
• update the api itself to allow username/password
• when user changes password, redis hash must be update (etc, you get the idea)

Another kind of output format should be an SQL dump.

Some requests result in a socket hang up error message. Sounds like a crash of the server, but I'm not sure what it is. Anyway I'd expect a meaningful error message always...

Queries fetching lots of data can kill the backend.

I didn't find anything about libxmljs in the README file.

This is the error:

npm ERR! [email protected] preinstall: make node
npm ERR! sh "-c" "make node" failed with 2
npm ERR!
npm ERR! Failed at the [email protected] preinstall script.
npm ERR! This is most likely a problem with the libxmljs package,
npm ERR! not with npm itself.
npm ERR! Tell the author that this fails on your system:
npm ERR! make node
npm ERR! You can get their info via:
npm ERR! npm owner ls libxmljs
npm ERR! There is likely additional logging output above.
npm ERR!
npm ERR! System Darwin 10.8.0
npm ERR! command "node" "/Users/jmedina/nvm/v0.6.14/bin/npm" "install"
npm ERR! cwd /Users/jmedina/Workspace/CartoDB-SQL-API
npm ERR! node -v v0.6.14
npm ERR! npm -v 1.1.12
npm ERR! code ELIFECYCLE
npm ERR! message [email protected] preinstall: make node
npm ERR! message sh "-c" "make node" failed with 2
npm ERR! errno {}
npm ERR!
npm ERR! Additional logging details can be found in:
npm ERR! /Users/jmedina/Workspace/CartoDB-SQL-API/npm-debug.log
npm not ok

If you want to see the complete trace:

http://pastie.org/4596125

format should be transformed to lowercase before the checks

Currently hard-coded in the PSQL module

Being able to specify connection parameters and permissions in a simple file would enable easier setup and testing.
Redis use should then be a pluggable thing to still play nicely with cartodb.

Would be nice to be able to dump out rows as csv as well as json, geojson, and kml.

eg:

http://tileio.cartodb.com/api/v1/sql?q=SELECT%20*%20FROM%20places_by_lon_lat&format=csv

A parameter could be accepted to control what is done with the geometry. Options could include:

• detect simple points, dump lon/lat in separate columns
• dump geometry into a field called 'wkt' in WKT format
• dump geometry in a single field as geojson (like datacouch.com)

Not sure what's the best practice here, but the .yml for main cartodb are not in the repo so maybe neither should the env.js in here, or we'd always be forced to work in a dirty git tree while developing if we need any special setting (PGSQL port for me..)

The correct location of map_key is the HASH member "map_key", not the SET value whose name ends in :map_key

Currently hard-coded in app/models/psql.js and the tests, would be useful to have it configurable

Possibly because chunk size is not being set large enough.

https://javi.cartodb.com/api/v1/sql?q=select%20*%20from%20tracker&format=kml

with geojson is working: https://javi.cartodb.com/api/v1/sql?q=select%20*%20from%20tracker&format=geojson

Fetching data from the API shows "node" process keeping CPU 100% busy for the whole transfer period.
It took 6.5 minutes for me to download 148MB from "localhost" and for all that time node was up there heating the room

Some tests are done using PSQL.query but the callback is never called. Timeout of 10 seconds occurs !
This is verified while porting the tests to mocha.

Running development branch of Carto-SQL-API (output from git rev-list --all | wc -l is 217) on an Ubuntu 12.04 LTS w/ PostgreSQL 9.1 installed. The output from SELECT postgis_lib_version(); reads:

POSTGIS="2.0.1 r9979" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 20
09" LIBXML="2.7.8" LIBJSON="UNKNOWN"

The SQL-API returns both GeoJSON and JSON from AJAX requests coming the client application (ModestMaps, markers.js, wax.js and jQuery). Inspecting the log files of 2 consecutive runs, the error is raised after 85 (1st run) and 87 (2nd run) SQL requests to port 8282 (at which the SQL API is listening). The max connection in postgresql.conf is set to 100.

Below you find the output from the node.js process:

events.js:45
throw arguments[1]; // Unhandled 'error' event
^
error: remaining connection slots are reserved for non-replication superuser connections
at [object Object]. (/home/gisuser/src/CartoDB-SQL-API/node_modules/pg/lib/connection.js:412:11)
at [object Object].parseMessage (/home/gisuser/src/CartoDB-SQL-API/node_modules/pg/lib/connection.js:287:17)
at Socket. (/home/gisuser/src/CartoDB-SQL-API/node_modules/pg/lib/connection.js:45:22)
at Socket.emit (events.js:64:17)
at Socket._onReadable (net.js:672:14)
at IOWatcher.onReadable as callback

Please let me know should you require further information to narrow down your investigation into this issue.

72c3911 needs testing

When the user tries to get some sql and it is has not valid syntax he can't read the error from javascript due to cors headers are off.

when you execute some query and its wrong the server responds with a 400 error but the response does not include any error information.

It would be good to have something like (JSON):

{error: "rambo relation not found"}

I've achieved install CartoDB-SQL-API, but now, when I make a request to the api the server fails.

1.9.2 :~/WorkSpace/CartoDB-SQL-API $ node app.js development
CartoDB SQL API listening on port 8080
FATAL ERROR: v8::HandleScope::Close() Local scope has already been closed

Node version: 0.6.14
NPM version: 1.1.12

CartoDB-SQL-API passes "make check" with node-0.8.2 but the version of Express it depends does not advertise support for node-0.8 so "npm install" fails. We can't force installation using "npm-shrinkwrap.json" due to https://github.com/isaacs/npm/issues/2601

There's no API documentation in the repository.
Best would probably be having it embedded in the code itself