DataStore SCQL


One of the most useful aspects of using local queries is that you can dynamically specify your conditions using a rich SQL-like language called SCQL (SproutCore Query Language).  SCQL implements enough grammar to support basic conditions—much like the ‘WHERE’ clause in traditional SQL.  In addition to the basic conditions, SCQL allows you to insert named and unnamed wildcards that will be substituted with parameters later on.  You can also register your own comparison operations if needed.

 

Basic Grammar

 

An SCQL statement simply lists a set of comparison, optionally grouped by parenthesis.  Comparisons are processed from left to right unless parenthesis get in the way.  For example, this would be a simple SCQL statement:

 

firstName = “John”

 

You can join multiple comparisons using the AND or OR operators.  You should wrap each comparison in parenthesis:

 

((firstName = “John”) OR (firstName = “Jane”) AND (lastName = “Doe”))

 

Property Keys

 

For simple comparisons, the value on the left hand side is almost always a key name that would be found on the matching record.  Note that this key is for the record not the data hash.  This means if you remap a key name you should use the remapped name.  Likewise, you can use computed properties for comparison here as well.

 

For example, the comparison:

 

fullName = “John Doe”

 

would find all records whose “fullName” property is “John Doe”, even if fullName is actually computed from firstName and lastName.

 

Simple Values

 

The right hand side of a simple comparison is usually a simple value.  The value may be a string (surrounded by quotes), boolean (written as true/false or YES/NO without quotes), or a number. 

 

firstName = “John” // string

isActive = YES  // boolean

birthyear = 1979 // number

 

Wildcards

 

You can use one of two wildcard methods any place you would normally use a simple value.  Wildcards will be substituted with a parameter from the query’s “parameters” property when the search is actually performed.

 

Wildcards can be either named or positional.  Named wildcards are strings wrapped in curly brackets “{}”.  Positional wildcards simply use the string “%@”.    You should use either named wildcards or positional wildcards in statement, but not both.  

 

// named wildcards

var q = SC.Query.local(MyApp.Contact, “firstName = {name}”, { name: “John” });

 

// positional wildcards

var q = SC.Query.local(MyApp.Contact, “firstName = %@”, [“John”]);

 

If you use named wildcards, the “parameters” property on the query MUST be a hash with same-named properties to substitute.  If you use positional wildcards, the “parameters” property MUST be an array of values.  They will be substituted from left to right.

Note that arrays, hashes, and objects cannot be expressed directly in SCQL.  You MUST use wildcards instead to use these values.

 

Available Comparisons 

 

SC.Query comes with the following basic comparisons built in:

 

Comparison Description
A = B is equal
A != B is not equal
A < B less than
A > B greater than
A <= B less than or equal to
A >= B greater than or equal to
A BEGINS_WITH B string A begins with string B
A ENDS_WITH B string A ends with string B
A CONTAINS B string A has substring B
A MATCHES B string A matches Regexp B
A ANY B A is in array B
TYPE_IS A Expects A to be a string naming a record type.  Only matches records of that type.  Useful for parameterized searches

 

In addition, SC.Query supports AND, OR, and NOT logical operators.

 

Implementation Details

 

SC.Query uses a simple parse tree to evaluate your SCQL.  It first tokenizes your query string, then maps each token to a handler function.  This creates a tree of functions that are simply executed in order from left to right to evaluate the final results.  This parsing is only done once when your query is created, so it is really fast.

 

Registering Your Own Comparison Operators

 

The SCQL features are defined inside SC.Query.queryLanguage and can be extended by calling

 

 

SC.Query.registerQueryExtension(tokenName,token);

 

 

The parameters are

 

An example will make the following easier to understand. Here is the definition of the equality comparison, taken from the query language definition:

 

tokenName: '=',

token: {

  reservedWord: true,

  leftType:     'PRIMITIVE',

  rightType:    'PRIMITIVE',

  evalType:     'BOOLEAN',

  evaluate:     function (r,w) {

                  var left  = this.leftSide.evaluate(r,w);

                  var right = this.rightSide.evaluate(r,w);

                  return left == right;

                }

}

 

A token definition must provide three kinds of information:

  1. how to read it from the query string
  2. how to link tokens into a tree
  3. how to evaluate the token when performing the query

 

1) Here I will just explain the most common case, which is to declare a reserved word, like '=' or 'BEGINS_WITH'. To accomplish this your token definition must contain 'reservedWord: true'. This will tell the tokenizer that your tokenName is to be considered as it s own kind of token.

Please have a look at the source in query.js for the other options.

 

2) You have to set the following properties on your token definition:

 

The leftType and rightType properties declare what types your token expects to find on its neighbours, evalType declares the type your token does evaluate to.

If you want to define a token that only takes one side, like an unary operator, you can just define the type of the side you want to operate on and ommit the definition of the other side. (Look at definitions of 'NOT' and 'TYPE_IS' in the source.)

So token types just serve the one purpose of linking tokens together. You could introduce any type you want, but probably you will use the two types that are used by the build in tokens:

 

Knowing this we can read the type declarations of the example token above. It will take two primitive values and evaluate to a boolean.

 

3) Finally must define an evaluation function for your token. It has to take two parameters and return true or false.

On evaluation the first parameter will be given a SC.Record instance, the second one will recieve the parameters of the SC.Query object calling your function.

After parsing did complete all tokens will be arranged in a token-tree. Every token will have a leftSide and a rightSide property (except you did limit your token to one side, or did define a primitive token), which will point to the corresponding child tokens. Evaluation of the query will start on the root token and will expect it to call evaluate() on it s child tokens passing the given SC.Record instance and the parameters allong to them.

So you will have to call leftSide.evaluate(record, parameters) to get the actual value of your token s left side, likewise for the right side.

After getting the evaluation values of your token s children you can compute your own evaluation value and give it back to your parent token (or the query itself if your token happens to be the root token).

Please have another look at the equality comparison example above to see how this is done.

 

Now let's try to define our own token. There is no build in 'XOR' operator, so we will define one.

Put the following at the end of your core.js:

 

var tokenName = 'XOR';

var token = {

  reservedWord: true,

  leftType:     'BOOLEAN',

  rightType:    'BOOLEAN',

  evalType:     'BOOLEAN',

  evaluate:     function (r,w) {

                  var left  = this.leftSide.evaluate(r,w);

                  var right = this.rightSide.evaluate(r,w);

                  return left != right;

                }

};

 
SC.Query.registerQueryExtension(tokenName, token);
 
Done! We have a 'XOR' operator available!
 
Another one?
 
How about a operator that checks if a certain string value is omly contains capitals?
Ok, example is made up, but it will illustrate some more features of the query parser, so let s try it.
 
var tokenName = 'IS_UPPERCASE';
var token = {
  reservedWord: true,
  leftType:     'PRIMITIVE',
  evalType:     'BOOLEAN',
  evaluate:     function (r,w) {
                  var left  = this.leftSide.evaluate(r,w);
                  return left == left.toUpperCase();
                }
};
SC.Query.registerQueryExtension(tokenName, token);
 
Use it like this:
 
var query = SC.Query.local('YourRecordType', 'name IS_UPPERCASE' );
var results = YourApp.store.find(query);
 

Moving On

 

Learn about Nested Stores »

Back to DataStore Programming Guide Home »