DB8 Usage

Queries and indexing

Query format and operators

A DB8 query takes the following form of the JSON object.

{
    "select"  : string array,
    "from"    : string,
    "where"   : [ {
        "prop"    : string,
        "op"      : string,
        "val"     : any
  } ]
    "orderBy" : string,
    "desc"    : boolean,
    "incDel"  : boolean,
    "limit"   : integer,
    "page"    : string
}

See Query object in the Database API Reference Guide for field descriptions.

For example, let's assume we are given the following task.

Give me the display name and state for all contacts living in California, order by display name, return in descending order, include records that have been deleted, return up to 40 records, and use the "next page" key returned from the previous query.

Then our query object would look like the following:

{
    "select"  : ["displayName", "state"],
    "from"    : "com.yourdomain.contacts:1",
    "where"   : [{"prop":"state","op":"=","val":"CA"}],
    "orderBy" : "displayName",
    "desc"    : true,
    "incDel"  : true,
    "limit"   : 40,
    "page"    : "BGhlbGxvAAs3"
}

Queries allow you to do the following:

Specify the following filter operators.

Operator Description
= equals
< less than
> greater than
>= greater than or equal
!= not equal
% wildcard
? full-text search

Though there is no specific range filter operator, you could get a range using other filter operators (i.e., > and <) in two where clauses. The % operator (aka - the prefix operator) is a type of wildcard -- it will return all matches beginning with the value you specify.

For example:
```
"where":[{
  "prop":"displayName",
  "op":"%",
  "val":"J"
}]
```
This where clause returns all objects whose displayName field begins with 'J'.

In SQL, this JSON formatted where clause would look like this:
```
WHERE displayName LIKE 'J%'
```
Order objects on a specified field in ascending or descending order.
Limit the number of objects returned (maximum is 500).
Get the number of objects that would have been returned if there had not been a limit.
Use a page identifier (key) returned from one query to get the next page of objects.

Operator	Description
=	equals
<	less than
>	greater than
>=	greater than or equal
!=	not equal
%	wildcard
?	full-text search

All queries must be on indexed fields

It is important to note that all queries must be on indexed fields and the indexes property that you created determines the kind of queries you can make. This means two things:

You can only query on an indexed property.
Your queries are limited to those that can be answered with contiguous results from the index table(s).

You define the indexes property with IndexClause object when you create a kind with the putKind() call.

You can create multiple indices for your kind, each of which can consist of multiple properties. Each entry in an index is an array of values. So for instance, with the example we have seen in DB8 Service Basics, if you create multiple property indexes composed first of a state property and then a displayName property, the entries would look like this:

["CA", "John Doe"], ["CA", "Mabel Syrup"], ["OR", "Don Juan"]...

The indices are ordered first by state, then displayName. This means you could get results if you queried on state (i.e., state = "CA"), since state values are contiguously ordered in the index, but not if you queried on displayName.

Not only do results have to be contiguously ordered, but they have to be guaranteed to be contiguously ordered in advance. What does this mean? Let's take an example; given the following [state, displayName] index:

["CA", "Helen Wheels"], ["CA", "Jerry Seinfeld"], ["CA", "John Doe"], ["CA", "Mabel Syrup"], ["CA", "Tony Soprano"]

You could do a search on a single displayName entry, i.e., "where" :[{"prop":"displayName", "op":"=", "val":"Tony Soprano"}] that would return one result. It would meet the criteria of being contiguously ordered, but there is no way of guaranteeing that in advance. Property values are not guaranteed to be unique, and there could be multiple "Tony Soprano" entries in different states. To remedy this, you could create an additional index consisting of displayName first and, optionally, other properties after that.

You need to keep the above information in mind when you are creating your indexes and how your data is going to be accessed.

Schema enforcement

DB8 Service provides a mechanism for validating JSON data objects when stored with the put() method. Your app or service can do this when creating the data object's kind with the putKind() method using the schema property. For this field, enter the object's JSON schema according to official JSON standards.

The following is a simple example of how this works using Enyo's serviceRequest API.

Create a kind with a schema:

var request = new enyo.ServiceRequest();
var parameter = {
  id: 'com.yourdomain.schema.test:1',
  // Define schema here
  schema: {
    id: 'com.yourdomain.schema.test:1',
    type: 'object',
    properties: {
      _kind: {
        type: 'string',
        value: 'com.yourdomain.schema.test:1',
      },
      foo: {
        type: 'string',
        description: 'foo string',
      },
      bar: {
        type: 'string',
        description: 'bar string',
      },
      isMember: {
        type: 'boolean',
        description: 'Is member flag',
      },
    },
  },
  owner: 'com.yourdomain.dbtest',
  indexes: [
    {
      name: 'foo',
      props: [{ name: 'foo' }],
    },
    {
      name: 'barfoo',
      props: [{ name: 'bar' }, { name: 'foo' }],
    },
  ],
};
request.service = 'luna://com.palm.db/putKind';
request.response(this, 'registerDatabaseKindResponse');
request.error(this, 'registerDatabaseKindError');
request.go(parameter);

One of the following two handlers is called when creating the kind is complete.

registerDatabaseKindResponse: function(inSender, inResponse) {
    enyo.log("Schema test, putKind success!");
},
registerDatabaseKindError: function(inSender, inResponse) {
    enyo.log("Schema test, putKind failure!");
},

If the kind creation has succeeded, you will see the following output in the log.

Schema test, putKind success!

Store an object with all the required fields, as in the schema defined in step 1.

var testObj = {
  _kind: 'com.yourdomain.schema.test:1',
  foo: 'myFoo',
  bar: 'myBar',
  isMember: true,
};
var objs = [testObj];
var request = new enyo.ServiceRequest();
var parameter = { objects: objs };
request.service = 'luna://com.palm.db/put';
request.response(this, 'putDataIntoDatabaseResponse');
request.error(this, 'putDataIntoDatabaseError');
request.go(parameter);

One of the following two handlers is called when the request for storing an object has been completed.

putDataIntoDatabaseResponse: function(inSender, inResponse) {
    enyo.log("Schema test, put success!");
},
putDataIntoDatabaseError: function(inSender, inResponse) {
    enyo.log("Schema test, put failure!");
},

You will see the following output in the log.

Schema test, put success!

Try to store an object with a missing required field:

var incompletedObj = {
  _kind: 'com.yourdomain.schema.test:1',
  foo: 'myFoo',
  bar: 'myBar',
};
objs = [incompletedObj];

var request = new enyo.ServiceRequest();
var parameter = { objects: objs };
request.service = 'luna://com.palm.db/put';
request.response(this, 'putDataIntoDatabaseResponse');
request.error(this, 'putDataIntoDatabaseError');
request.go(parameter);

Since your request did not comply with the schema registered, you will see the following output in the log.

Schema test, put failure!

Try to store an object with a field that has a wrong type value:

var invalidObj = {
  _kind: 'com.palm.schema.test:1',
  foo: 'myFoo',
  bar: 'myBar',
  isMember: 'true',
};
objs = [invalidObj];

var request = new enyo.ServiceRequest();
var parameter = { objects: objs };
request.service = 'luna://com.palm.db/put';
request.response(this, 'putDataIntoDatabaseResponse');
request.error(this, 'putDataIntoDatabaseError');
request.go(parameter);

Similar to step 3, your request did not comply with the schema registered, you will see the following output in the log.

Schema test, put failure!

Change notification

You can be notified only when a subset of an object's properties is updated using revision sets. For example, a sync engine might want to be notified only when a contact's phone number changes. A revision set creates a property that is only updated when one of the set's properties is updated.

If the revision set with the name phoneRev on the property phoneNumber creates a property phoneRev with an integer value that is set to the current value of _rev whenever the phoneNumber property is updated. This allows an app to create a watch using a query of the form "where phoneRev > X" to be notified when a phone number is updated.

Revision sets are specified at kind creation with the putKind() method. The following creates a revision set for the state property:

//**
//** Note here that the revision set field ("stateRev") is added
//** to the indexes so that we can query on it later.
//**
var indexes = [
  {
    name: 'state',
    props: [{ name: 'state' }],
  },
  {
    name: 'stateRev',
    props: [{ name: 'stateRev' }],
  },
];
var revSets = [
  {
    name: 'stateRev',
    props: [{ name: 'state' }],
  },
];

var request = new enyo.ServiceRequest();
var parameter = {
  id: 'com.yourdomain.sample:1',
  owner: 'com.yourdomain.dbtest',
  indexes: indexes,
  revSets: revSets,
};
request.service = 'luna://com.palm.db/putKind';
request.go(parameter);

Once a kind is created, we will put a contact data using the put() method.

var contact = {
  _kind: 'com.yourdomain.sample:1',
  name: 'Mabel Syrup',
  state: 'CA',
};
var objs = [contact];
var parameter = { objects: objs };

var request = new enyo.ServiceRequest();
request.service = 'luna://com.palm.db/put'; // putting data into DB
request.response(this, 'putDataIntoDatabaseResponse');
request.go(parameter);

The following handler is called when the "putting" is complete.

putDataIntoDatabaseResponse: function(inSender, inResponse) {
    if (inResponse.returnValue === true) {
        enyo.log("put success! results=" + JSON.stringify(inResponse.results));
    } else {
        enyo.log("put failure!");
    }
}

You will see the following output in the log.

put success! results=[{"id":"++HEIviIqT+9MYkj","rev":4881}]

After you "put" a record, we will use get() to get the revision set number.

var id = '++HEIviIqT+9MYkj';
var ids = [id1];
var parameter = { ids: ids };
var request = new enyo.ServiceRequest();
request.service = 'luna://com.palm.db/get';
request.go(parameter);

As the result, you will see the following.

get success!, results ={
  "returnValue":true,
  "results":[
  {
    "_id":"++HEIviIqT+9MYkj",
    "_kind":"com.yourdomain.sample:1",
    "_rev":4881,
    "name":"Mabel Syrup",
    "state":"CA",
    "stateRev":4881
  }]
}

Using the revision set number, you can be notified when the revision number gets modified. Note that if you want to do a query on a revision set, then there has to be an index for it (see the putKind example above). The following is an example of calling the watch() method. (the fired flag is true in the results)

var fquery = {
    "from":"com.yourdomain.sample:1",
    "where":[
        {
            "prop":"stateRev",
            "op":">",
            "val":4881
        }
    ]
};

var parameter = {"query": fquery};
var request = new enyo.ServiceRequest();
request.service = "luna://com.palm.db/watch"; // watch
request.go(parameter);

If the state field is subsequently updated, the watch fires. For example, the following merge() does this:

var mprops = { "state":"MA"};
var mquery = {
    "from":"com.yourdomain.sample:1",
    "where":[
        {"prop":"state","op":"%","val":"C"}
    ]
};
var parameter = { "query": mquery, "props": mprops };
var request = new enyo.ServiceRequest();
request.service = "luna://com.palm.db/merge";
request.go(parameter);

No Headings

webOS News