Named Queries

THIS IS JUST A DRAFT IS NOT ACTUALLY IMPLEMENTED

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)

Valid user parameter names start with a letter and can only contain letters, numbers or underscores. System parameter names are prefixed with sys:: to avoid clash with user parameters.

User parameters need to be explicitly defined at NamedQuery creation time, with their defaults (for checking SQL validity).

// namedquery.json 
{
  version: '0.0.1',
  // there can be at most 1 template with the same name for any user 
  // valid names start with a letter and only contains letter, numbers
  // or underscores
  name: 'query_name', 
  // embedded authorization certificate
  auth: {
   // See https://github.com/CartoDB/Windshaft-cartodb/wiki/Signed-maps
   method: 'token', // or "open"
   valid_tokens: ['auth_token1','auth_token2'] // only for 'token' method
  },
  // Variables not listed here are not substituted
  // Variable not provided at instantiation time trigger an error
  // A default is required for optional variables
  // Type specification is used for quoting, to avoid injections
  placeholders: {
      x: { type:'number', default:0 },
      y: { type:'number', default:0 },
      z: { type:'number', default:0 },
      iso2: { type:'sql_literal', default: 'ES' }
  },
  sql: "select CDB_TorqueTile(" +
       "$$ SELECT * FROM countries WHERE iso2 = '<%= iso2 %>' $$," +
       "<%= z %>, <%= x %>, <%= y %>)",
  affected_tables: 'countries' // optional
}

Named queries HTTP API

Registering a named query

TODO NOTE we could define a built-in named query to do this (if it was in the database)

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)