(Click to open topic with navigation)

Insight database

This section describes the behavior of the Insight database query API in Moab Web Services. It contains the URLs and responses delivered to and from MWS.

Supported methods

Resource GET PUT POST DELETE
/rest/insight/priv/<view> Get all rows in a view -- -- --
/rest/insight/user/<view> Get all rows the current user can see -- -- --

This topic contains these sections:

Getting rows in a relational database view

Queries with JSON operators

The HTTP GET method is used to retrieve rows in a view or table of the Insight database in JSON format. An array of JSON objects is returned. Each JSON object corresponds to a row in the view/table. The field name and values in each JSON object correspond the column names and values in the view/table. For example if view myview1 in the Insight database contained the following data:

Table 13-4: "myview1"

column1 column2 column3 user_name
alpha delta 1 wsmith
bravo echo 2 tjones
charlie echo 3 wsmith

Then you would query this data as follows:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3

The resulting JSON would look like this:

[
  {
    "column1":"alpha",
    "column2":"delta",
    "column3":1,
    "user_name":"wsmith"
  },
  {
    "column1":"bravo",
    "column2":"echo",
    "column3":2,
    "user_name":"tjones"
  },
  {
    "column1":"charlie",
    "column2":"echo",
    "column3":3,
    "user_name":"wsmith"
  }
]

You can query using MongoDB comparision and logical operators. For example:

Supported Mongo comparison operators include $gt, $gte, $in, $lt, $lte, $ne, and $nin. Supported logical operators include $or, $and, $not, and $nor.

Queries with dates and times

You can also query using date/time. For example:

Queries with wildcards

You can query for rows where a column value matches a specified pattern, instead of matching an exact string, by using the $like operator, which is similar to the SQL LIKE operator. $like allows the user of "%" as a wildcard that matches any substring. For example:

When performing $like queries, you may need to replace % with the URL-encoded value of %25; otherwise, the meta-character may be interpreted as %.

You can also use "_" to substitute for any single character. For example, to query for all rows that start with any character and end in "cho":

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&query={"column3":{"$like":"_cho"}}

This matches rows where colum3 has the value "acho," "bcho," "ccho," "dcho," "echo," etc.

Privileged queries vs. user queries

The examples above show queries performed using the privileged query URL (/rest/insight/priv). The privileged query does not impose any additional conditions to what you specify in your query and max and offset parameters. By default only administrators have permission to use the privileged query URL. If you grant an unprivileged user access to the privileged query URL, the user could see data he should not be permitted to see.

If an unprivileged user needs to query the database view, he should only have permission to do so with the user query URL. This URL (/rest/insight/user) adds the implicit condition that the value for the user_name column must match that of the user that is currently logged in.

For example, the following two queries, the first a user query performed by user wsmith and the second a privileged query, are essentially the same:

GET http://localhost:8080/mws/rest/insight/user/myview1?api-version=3&query={"column2":"echo"}
GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&query={$and":[{"column2":"echo"},{"user_name":"wsmith"}]

Note that the user query URL can only access views that have a user_name column.

Field selection

To select which columns you want to see in the results, use the fields parameter. For example, to see only column1 and column2:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&fields=column1,column2

Sorting

You can also sort the results by a one or more columns in ascending or descending order. Specifying additional sort columns provides an alternate value by which MWS will sort the rows if the values in the first column(s) match in multiple rows. There is no limit to the number of columns you can specify in your sort URL.

All sort columns must be specified in the fields selection parameter.

To sort the results by a single column in ascending order:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort={"column1":1}

To sort the results by a single column in descending order:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort={"column1":-1}

To add a second sort column by which MWS will sort any rows with the same value in the first column:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort=[{"column1":1},{"column2":1}]

To add a third sort column by which MWS will sort any rows with the same values in the first column and the second column:

GET http://localhost:8080/mws/rest/insight/priv/myview1?api-version=3&sort=[{"column1":1},{"column2":1},{"column3":1}]

Quick reference

GET http://localhost:8080/mws/rest/insight/priv/<view>?api-version=3
GET http://localhost:8080/mws/rest/insight/user/<view>?api-version=3

Get all rows in a view

URLs and parameters

GET http://localhost:8080/mws/rest/insight/priv/<view>?api-version=3
GET http://localhost:8080/mws/rest/insight/user/<view>?api-version=3
Parameter Required Type Description Example
fields No Comma-separated string Includes only specified fields in output. fields=name,type
max No Integer The maximum number of items to return. max=100
offset No Integer The index of the first item to return. offset=200
pretty No Boolean true shows pretty printing of output. Default is false. pretty=true
query No JSON

Queries for specific results.

It is possible to query by one or more fields based on a subset of MongoDB query syntax. (See above.)

 query={"name":"node003"}
sort No JSON Sort the results. Use 1 for ascending and -1 for descending. sort={"name":-1}

Get all rows

Queries performed with the privileged query URL (/rest/insight/priv/<view>) return all rows in a view that match that query.

Sample response

GET http://localhost:8080/mws/rest/insight/priv/node_management_view?api-version=3&pretty=true&fields=name,state,user_name
------------------------------------

{
  "totalCount": 5,
  "resultCount": 5,
  "results":   [
    {
    "job_name": "Moab.613",
    "job_state": "Running",
    "user_name": "wsmith"
    },
    {
    "job_name": "Moab.614",
    "job_state": "Running",
    "user_name": "tjones"
    },
    {
    "job_name": "Moab.615",
    "job_state": "Idle",
    "user_name": "wsmith"
    },
    {
    "job_name": "Moab.616",
    "job_state": "Running",
    "user_name": "tjones"
    },
    {
    "job_name": "Moab.617",
    "job_state": "Idle",
    "user_name": "tjones"
    },
   ]
}

Get all rows the current user can see

Queries performed with the user query URL (/rest/insight/user/<view>) only return rows that the current user is authorized to see. The rows returned contain values in the user_name column that match that of the user who is currently logged in. For example, if tjones is logged in, then the user query URL will only return rows where user_name is equal to "tjones" and that match any other query submitted.

Note that you can only use the user query URL to query views with a user_name column.

Sample response

GET http://localhost:8080/mws/rest/insight/user/node_management_view?api-version=3&pretty=true&fields=name,state,user_name&query={“job_state”:”Running”}
{
  "totalCount": 2,
  "resultCount": 2,
  "results":   [
    {
      "job_name": "Moab.614",
      "job_state": "Running",
      "user_name": "tjones"
    },
    {
      "job_name": "Moab.616",
      "job_state": “Running",
      "user_name": "tjones"
    },
  ]
}

Because tjones is currently logged in, MWS only returns information about running jobs submitted by tjones even though the query does not explicitly filter by user_name.

Related topics 

© 2014 Adaptive Computing