Project

General

Profile


Query Engine

Simple Query Interface - REST

Web service (REST) interface to relational databases with very limited functionality. Currently can access only 1 table or 1 UDF per query. The idea is to use views for more complex queries, e.g. joins.

Query request arguments

Data query requests to the Simple Query Interface are sent as GET HTTP requests with with URI <URI prefix>/query with the following arguments:

  • dbname=<database name> - required - Logical name of the database. Logical names for available databases are defined in the QEngine configuration file on the server
  • t=<table name> - required - Name of the database table
  • c=<column>[,<column>...] - optional - list of table columns, default is all columns (*)
  • w=<column>:<op>:<value> - optional, can be repeated - and'ed components of the "where" cause
    op is (FORTRAN comparison operatios) lt, le, eq, ne, ge, gt
  • w=<column>:<value> is equivalent to <column>:eq:<value>
  • o=<column>[,<column>...] - optional - list of columns to sort results by. Prepend "-" to the column name to sort in descending order
  • l=<limit> - optional - maximum number of rows to retrieve
  • x=(yes|no|clear) - optional - cache control, default is "yes":
    • yes - use cache foe for this query * no - no not use cache for this query, but do not clear existing cache * clear - clear existing cache for this query and the use the cache (populate it)
  • f=(text|csv|xml) - optional - output format, default is csv. xml is not implemented yet
  • F=<function name> - to call UDF instead of SELECT from a table.
  • a=<argument 1>&a=<argument 2>... - pass arguments to the UDF in the specified order. For example:
  
SELECT col1, col2 from func('xyz', 2, false) where col2 = 3
can be written as:
  /query?dbname=...&F=func&a=xyz&a=2&a=false&c=col1,col2&w=col2:eq:3

JSON data type

Postgres database supports JSON column type. The user can build queries with conditions including elements of JSON data structure. To do that, the user can use data path specification instead of column name. To specify a data path, use field names separated with dots. For example, column "data" in the table might have a JSON data structure like this:

{
   "key": 1234,
   "properties":  {
        "color":   {     "red": 10,     "green":  5,    "blue":  173 },
        "location":   [  1.5, 17.8, -1.45 ]
}

Here are some valid queries using "data" column and corresponding URL encoding:

properties.color.red > 3 w=data.properties.color.red:gt:3
location [1] < 20 w=data.location.1:lt:20

Invalidating Cache

If the cache needs to be invalidated, use "flush" method:

http://.../flush

Normal response from the flush method is "OK"

Examples

http://.../query?t=occupancychannel&c=*&dbname=nova_cond_dev&f=text&w=run:12815&o=-run,subrun

Run query on nova_cond_dev and return results as text

select * 
   from occupancychannel 
   where run = 12815 
   order by run desc, subrun

http://.../query?t=occupancychannel&c=channelid,occupancy&dbname=nova_cond_dev&f=csv&w=run:12815&w=subrun:3&x=no

Run query on nova_cond_dev and return results as CSV, do not use cached data

select channelid,occupancy
   from occupancychannel 
   where run = 12815 and 
      subrun = 3

Getting schema information

There are 2 web methods to get schema information from the database. They are grouped under "I" "subdirectory" in the URL: <URI prefix>/I/.... Currently, these methods are implemented for Postgres only.

List tables

http://..../I/tables?dbname=...&ns=...&f=(csv|json)

  • dbname - the logical database name, optional
  • ns - namespace name, optional, default=public
  • f - output format, optional, either json or csv. Default is json

List columns of the table

http://..../I/columns?dbname=...&t=table&f=(csv|json)

  • dbname - the logical database name, optional
  • t - table name, required. Table name can include namespace, e.g. "fardet.runs". Default namespace is "public"
  • f - output format optional, either json or csv. Default is json

Simple Query Interface - C API


typedef struct {
  char *column;         /* Column name to check         */
  char *value;          /* Value to check equality      */
} Constraint;

typedef enum {
  CacheControlUseCache,
  CacheControlNoCache,
  CacheControlFlushCache
} CacheControl;

typedef void *Dataset;

Dataset getQEData(const char *urlhead,
                char *dbname,                       /* Database configuration name      */
                char *table,                        /* Table name                       */
                char *columns[],                    /* NULL terminated. Optional        */
                Constraint constraints[],           /* NULL terminated. Optional        */
                char *orders[],                     /* NULL terminated. Optional        */
                int limit,                          /* 0 means no limit                 */
                CacheControl cache_control,
                int *error);

These functions are used along with WDALIB module to extract the data from the dataset. See: Web Data Access C API

Downloads

git repository:

The source tarfile for WDALIB module is available here