Lookup.Queries
Some roles (Public and Auth) are restricted to running only queries in the lookup.queries table. This is an important element in data security. Other roles (Super and Reader) are permitted to run lookup queries, in addition to free-form queries.
The lookup requests are made in the same fashion as other queries, except that the 'q' parameter is replaced by a 'kw' parameter. The 'kw' parameter is the key that looks up the desired query in the lookup.queries table. If the retrieved query has '%s' substitution tokens, the request should have sufficient numbered arguments (arg000, arg001...) to complete it.
Schema Lookup
To avoid name collisions with tables you might be porting from elsewhere, we put the lookup tables in their own schema, called lookup. The table with the queries is called queries.
This schema, and the queries table, is created whenever you enable the Auth or Public roles.
Queries
This table is created with the lookup schema.
Your Super role has INSERT and UPDATE permissions on the lookup.queries table, and you can use the SQL_form, or the Rdbadmin utility to add records.
The five fields are:
- tag
- The keyword by which the query is found. This keyword will be provided in the request as the 'kw' parameter.
- query
- The SQL query itself. It may optionally include interpolation tokens '%s' or cookie values '%{cookiename}'.
- format
- The default format for returning the result. This is optional, and can be overridden by a 'format' parameter in the request.
- authcheck
- If this boolean field is true, the users ip address will be temporarily blacklisted after a minimum number of consecutive requests without results. Use this to protect queries that make up your authentication system.
- nopermit
- This character array 'char[]' can contain any combination of 'a','p', and 'r', representing the 'Auth', 'Public', and 'Reader' roles. If the requesting role matches, the query will not be retrieved.
See also:
A more general overview of how to query is in How It Works, and there are more specific pages for Javascript and Python. Questions and Answers may be helpful also. The Roles page has more to say about Postgresql roles.