Named Queries

Named queries API is a way to provide simple access to complex queries via named "query templates". The idea is to store query templates with a name and allowing users to run them passing parameters and optionally authentication tokens.

The interface should be compatible with that of "map templates" implemented by the cartodb tiler: https://github.com/CartoDB/Windshaft-cartodb/wiki/Template-maps

Template format

Query templates would reference parameters using the <%= and %> separators. There would be system parameters (provided by the server, like client IP or similar) and user parameters (provided by the user at invocation time).

In order to avoid SQL injection, each parameter will be of a given "type", which determines the required escaping for it. Supported types are:

• sql_literal (internal single-quotes will be sql-escaped)
• sql_ident (internal double-quotes will be sql-escaped)
• number (can only contain numerical representation)
• ... (add more as need arises)

TODO: specify how parameters are named. Requires deciding if they are named or ordered

TODO: provide a list of system parameters (necessarely named) and their type

TODO: define how to specify user parameters and their types

TODO: analyze if default values for user parameters are needed (for checking SQL validity, maybe)

TODO: define how to provide authorization certificates

Named queries HTTP API

Registering a named query

TODO

Getting a list of existing named queries

TODO

Updating a named query

TODO

Getting a named query

TODO

Deleting a named query

TODO

Running a named query

TODO

Storage

Registry of named functions would be per-user and contain, for each named function:

• The name, primary key
• The template, with placeholders for parameters (if any)
• The minimum number of parameters
• Default values for not-given parameters
• List of source tables (needed to determine last modified info)
• Whether the query can change the database (db changing functions won't be cached)
• Whether the query should run with db owner permissions (ANALYZE CONSEQUENCES) -- in that case, an optional list of tokens required to be allowed to run it
• Optional authorization certificate (see https://github.com/CartoDB/Windshaft-cartodb/wiki/Signed-maps#example-api-for-signing-a-map)