This site hosts historical documentation. Visit www.terracotta.org for recent product information.

Open Source
Search

BigMemory SQL Queries

Prerequisite: This topic assumes that you have read BigMemory Search Setup.

Introduction

BigMemory SQL allows you to search the in-memory data of BigMemory using expressions similar to those of Structured Query Language. BigMemory SQL is an alternative to the programmatic, Java-based Search API.

Any query that can be constructed using the Search API can also be expressed using BigMemory SQL. Similarly, any BigMemory SQL query can be expressed in the Search API. Because the Search API allows only lookup-style queries, BigMemory SQL supports only SELECT statements (and not, for example, INSERT or DELETE, even though they are found in SQL92).

For support for nulls, see Options for Working with Nulls.

TMC Support for BigMemory SQL

In the Terracotta Management Console, on the Application Data tab, select Contents, and in the Query field, enter your BigMemory SQL expression, then click Submit.

querysyntax

Note that you can also refine the search criteria by clicking the filter icon on the right side of the column heading:

filtering

Get Started with BigMemory SQL

  1. Confirm that the search configuration sub-elements are present in your ehcache.xml file. To enable searching the cache with BigMemory SQL, include the <searchable/> tag. To enable the addition of search attributes after the cache is initialized, include the allowDynamicIndexing option.

    This example defines the searchable cache's configuration in an ehcache.xml file called ehcache-users.xml.

    <ehcache name="Users">
  <cache name="Person">
    <searchable allowDynamicIndexing="true">
      <searchAttribute name="name" type="String" expression="value.getName()"/>
      <searchAttribute name="age" type="int" expression="value.getAge()"/>
    </searchable>
  </cache>
  <cache name="Address">
    <searchable>
      <searchAttribute name="zip" type="int" expression="value.address.getZip()"/>
    </searchable>
  </cache>
</ehcache>

  2. In your application, instantiate the QueryManager using the QueryManagerBuilder.

    // get the CacheManager that contains the caches to query
CacheManager usersCacheManager = new CacheManager("ehcache-users.xml");

// build the QueryManager
QueryManager queryManager = QueryManagerBuilder
        .newQueryManagerBuilder() 
        .addCache(Person)
        .addCache(Address)
        .build();

    For more information about this step, see the Query Manager API section below.

  3. Issue the BigMemory SQL query.

    // construct the queries
Query personQuery = queryManager.createQuery("select * from Person where age > 30");
Query addressQuery = queryManager.createQuery("select zip from Address where zip = 94115");

// execute the queries
Results personResults = personQuery.end().execute();
Results addressResults = addressQuery.end().execute();

// iterate over the results, etc.

    For more information about this step, see the BigMemory SQL Syntax and Examples section below.

Query Manager API

BigMemory SQL searches are performed using the QueryManager builder and interface.

public final class QueryManagerBuilder 
{
    public static QueryManagerBuilder newQueryManagerBuilder() 
    {
    }

    public QueryManagerBuilder addCache(Ehcache cache) 
    {
    }

    public QueryManagerBuilder addAllCachesCurrentlyIn(CacheManager cacheManager) 
    {
        for (String s : cacheManager.getCacheNames()) 
        {
            final Ehcache cache = cacheManager.getEhcache(s);
        }
    }

    public QueryManager build() 
    {
    }
}

public interface QueryManager 
{
  Query createQuery(String queryString);
}

You can explicitly add a cache to be searched by BigMemory SQL, or you can specify the CacheManager that contains the caches. The following example does both:

QueryManager queryManager = QueryManagerBuilder
         .newQueryManagerBuilder() 
         .addCache(cache1)
         .addCache(cache2)
         .addAllCachesCurrentlyIn(cacheManager1)
         .addAllCachesCurrentlyIn(cacheManager2)
    .build();

Note: A build of the QueryManager is a snapshot of the state of the CacheManagers and Caches. If CacheManagers or Caches are added or removed after the build, a new QueryManager should be built before issuing the query.

The createQuery() method takes one string argument that represents a BigMemory SQL SELECT clause:

Query query = queryManager.createQuery("queryString");

BigMemory SQL Syntax and Examples

SELECT Clause

This section provides a detailed specification for the SELECT clause. The general form of SELECT statement is:

SELECT [ * | KEY | VALUE | [ (attribute 1, attribute 2, ... , attribute N) | aggregatorFunction(attribute) ] ]
     [ FROM cache]
     [ WHERE condition ]
     [ GROUP BY attribute]
     [ ORDER BY {attribute}]
     [ LIMIT { count }]

The SELECT clause defines the Attributes to be selected.

Note: Attributes are specified by their names and must match the Search configuration. Otherwise, an exception is thrown.

You can also select the cache key and value denoted by the keywords key and value. A SELECT clause can have Attributes, keys, and values, in any order. To return all searchable attributes, use the wildcard character * .

The name of the cache to be queried is specified using the FROM clause.

// get all attributes for a person named Dave 
select * from Person where name = 'Dave' 

//get only the key
select key from Person where name = 'Dave'  

//get only the value
select value from Person where name = 'Dave'            

// get key, value and all attributes for a person named Dave 
select *, key, value from Person where name = 'Dave' 

// get only the age for a person named Dave
select age from Person where name = 'Dave'           

// get both age and zip
select age, zip from Person where name = 'Dave'

Aggregator Functions

Aggregator functions can be used to perform calculations on a specified attribute's values. The following functions are available: * sum * max * min * average (can be 'average' or 'avg') * count

// get the average age for all persons older than 30
select avg(age) from Person where age > 30
Aggregation Examples
select key,sum(age) from Person 
select key,average(age) from Person where age > 10
select key,sum(age),min(age) from Person where age > 10

FROM Clause

By default, the QueryManager locates the cache name attached to the FROM clause from all CacheManagers specified when the QueryManager was built.

... from [ Cache | CacheManager.Cache ] ...

If there are multiple CacheManagers, two or more might have a cache with the same name. For example, suppose that in addition to the ehcache-users.xml configuration (presented in #1 of Get Started above), there is another configuration file called ehcache-address.xml that provides detailed address information.

<ehcache name="Detailed-Address">
  <!--another cache with the name "Address" is present in ehcache-users.xml-->
  <cache name="Address"> 
    <searchable>
      <searchAttribute name="name" type="String" expression="value.getName()"/>         
      <searchAttribute name="street" type="String" expression="value.getStreet()"/>
      <searchAttribute name="apartment" type="String" expression="value.getApartment()"/>
      <searchAttribute name="city" type="String" expression="value.getCity()"/>
      <searchAttribute name="zip" type="int" expression="value.getZip()"/> 
    </searchable>
  </cache>
</ehcache>

Because more than one cache has the name Address, we prefix the CacheManager name, that is, Users.Address or Detailed-Address.Address:

// get cache managers
CacheManager usersCacheManager = new CacheManager("ehcache-users.xml"); 
CacheManager addressCacheManager = new CacheManager("ehcache-address.xml");

// needed only once
QueryManager qm = QueryManagerBuilder.newQueryManagerBuilder()
.addAllCahcesCurrentlyIn(Users,Detailed-Address)
.build();

// ** Default use case
// Since the Person cache is unique, we don't need to supply the cache manager 
Query nameQuery1 = qm.createQuery("select name from Person where age > 21");

// ** Explicit scoping use case #1
// search Address cache defined in ehcache-users.xml where the CacheManager name is "Users"
Query nameQuery2 = qm.createQuery("select name from Users.Address where zip = 94115");

// ** Explicit scoping use case #2
// search Address cache defined in ehcache-address.xml where the CacheManager name is "Detailed-Address"
Query nameQuery3 = qm.createQuery("select name from Detailed-Address.Address where zip = 94115");

// get the results
Results allNamesInUserRecordsOver21 = nameQuery1.end().execute(); 
Results allNamesInUserRecordsIn94115 = nameQuery2.end().execute(); 
Results allNamesInAddressBookIn94115 = nameQuery3.end().execute();

  // iterate over the results, etc.

Note: If no CacheManager is specified in the FROM clause, and multiple caches with the same name are found, an exception is thrown with a message that more than one cache with the same name exists.

WHERE Clause for condition expressions

The condition expression associated with the WHERE clause fetches only those Ehcache elements that match a particular criterion. The general syntax of the condition expression is:

((attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value) [ AND | OR | NOT ]
(attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value) [ AND | OR | NOT ]
(attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value) [ AND | OR | NOT ]
. . .))

For example:

select *  from Person where ((age > 21 or zip=94115) and (time > 10 or fun > 100));

Where Examples

select * from Person where age = 18
select * from Person where age != 18
select * from Person where age < 18
select * from Person where age > 18
select * from Person where age <= 18
select * from Person where age >= 18

Ilike Example

select city from Address where city ilike 'San*'

Note: ilike takes the wildcard character * for zero or more characters, and ^ for a single character.

  • To find 'cat' and 'cow', use select * from searchable where animal ilike 'c*'
  • To find 'cat' but exclude 'cow', use select * from searchable where animal ilike 'c?t'
  • To find elephant and sheep but exclude weasel, use select * from searchable where animal ilike '*e*p*'

Like Example

select city from Address where city like 'San%'

Note: like takes the wildcard character % for zero or more characters, and _ for a single character.

  • To find 'cat' and 'cow', use select * from searchable where animal like 'c%'
  • to find 'cat' but exclude 'cow', use select * from searchable where animal like 'c_t'
  • To find elephant and sheep but exclude weasel, use select * from searchable where animal like '%e%p%'

BigMemory SQL does not support the following for like and ilike:

  • [charlist] — Sets and ranges of characters to match
  • [^charlist] or [!charlist] — Matches only a character NOT specified within the brackets

Between Example

select * from Person where age between 10 and 20

Boolean Operators

select key,value from Person where (age > 100 and zip = 20144)
select key,value from Person where ((age > 100 and zip = 20144) and time > 10)
select key,value from Person where (age > 100 or zip = 20144)
select key,value from Person where ((age > 100 or zip = 20144) or time > 10)

select key,value from Person where ((age > 100 and zip = 20144) or time > 10)
select key,value from Person where ((age > 100 or zip = 20144) and (time > 10 or fun > 100))
select key,value from Person where ((age > 100 or zip = 20144) and time > 10)

Data Types

To be searchable using BigMemory SQL, the data type of an Attribute must be one of the following:

  • boolean
  • byte
  • char
  • float
  • short
  • long
  • int
  • double
  • date
  • sqldate
  • String

The value of an Attribute in the WHERE clause must follow these data type rules:

  • Except for integer and string types, an explicit cast must indicate the data type.

  • Values for string, boolean, date, and sqldate must be surrounded by single quotes.

Data Types Examples

select * from Person where age = 11 // age is of type int
select * from Person where name = 'Mary' // name is of type String
select * from Person where gender = (char)'M' 
select * from Person where isMale = (bool)'true' 
select * from Person where age = (byte)11 
select * from Person where age = (short)11 
select * from Person where age = (long)11 
select * from Person where age = (double)11.1 
select * from Person where birthDate = (date)'2003-01-10T14:25:22'

The data type name is case-sensitive. For example, use lowercase 'd' in (double) to indicate a primitive of type double.

Enums

To search for a specific enum type, cast the value with the enum class name. The class name must be fully qualified and must be in the same format as the return value of String.class.getName().

select * from Person where age = (enum some.company.package.Foo)'Bar'

Dates

BigMemory SQL can parse two date types: * the java.util.Date cast by the date keyword * its subclass, java.sql.Date, which is cast using the sqldate keyword

BigMemory SQL can parse the following common formats of date and time strings:

 'yyyy-MM-ddTHH:mm:ss.SSS z'
 'yyyy-MM-ddTHH:mm:ss.SSS'
 'yyyy-MM-ddTHH:mm:ss z'
 'yyyy-MM-ddTHH:mm:ss'
 'yyyy-MM-ddz'
 'yyyy-MM-dd'

Formats which are supported by ISO 8601 but not by BigMemory SQL are DD omission (YYYY-MM), Week Date formats (YYYY-Www-D), ordinal dates (YYYY-DDD), durations (e.g., P3Y6M4DT12H30M5S), and time intervals (e.g., 2007-03-01T13:00:00Z/2008-05-11T15:30:00Z).

Examples of Dates

select * from Person where dateOfBirth = (date)'2012-12-01T10:10:20'
select * from Person where dateOfBirth = (date)'2012-12-01'
select * from Person where dateOfBirth = (date)'2012-12-01T10:22'
select * from Person where dateOfBirth = (date)'2012-12-01T10:10:22.433 EST'

GROUP BY Clause

The GROUP BY clause provides the option to group results according to specified attributes. Adding a GROUP BY clause to the query both groups the results and allows aggregate functions to be performed on the grouped attributes. The general syntax of the group by clause is:

group by <attribute 1>, <attribute 2>, . . . <attribute n>

The following apply to both the Ehcache Search API's Group By clause and the GROUP BY clause in BigMemory SQL:

  • Any attribute specified with the GROUP BY clause should also be included in the target list of the SELECT clause.
  • Special KEY or VALUE attributes cannot be used in a GROUP BY clause. This means that KEY, VALUE, or * cannot be used in a query that has a GROUP BY clause.
  • Adding a GROUP BY clause to a query changes the semantics of any aggregators passed in, so that they apply only within each group.
  • If at least one aggregation function is specified in a query, the grouped attributes are not required to be included in the result set, However, grouped attributes are typically requested to make result processing easier.

GROUP BY Examples

select age from Person where ((age > 100 and zip = 20144) or time > 10) group by age 
select age, zip from Person where ((age > 100 and zip = 20144) or time > 10) group by age, zip

ORDER BY Clause

To order the Query results, add an ORDER BY clause.

The general form of the ORDER BY clause is:

order by <attribute 1> <asc[ending] | desc[ending]>, <attribute 2> <asc[ending] | desc[ending]>, . . . <attribute n> <asc[ending] | desc[ending]>

The default ordering direction is ascending.

ORDER BY Examples

select * from Person where age > 30 order by age asc, name desc

select age from Person where ((age > 100 and zip=20144) or time > 10) order by age descending 

select age, zip from Person where ((age > 100 and zip=20144) or time > 10) order by age desc, zip asc

ORDER BY and GROUP BY Together

If ORDER BY is used with GROUP BY, the ordering attributes are limited to those listed in the GROUP BY clause.

select age from Person where ((age > 100 and zip = 20144) or time > 10) group by age order by age ascending 
select age, zip from Person where ((age > 100 and zip = 20144) or time > 10) group by age, zip order by age asc, zip desc

LIMIT Clause

The LIMIT clause can be used to restrict the number of results returned by the search query. Ordinarily, the LIMIT clause is used with an ORDER BY clause to get the top results.

select * from Person where age < 80 limit 100

The LIMIT clause takes a single integer argument to restrict the result set with this general form:

limit <integer>

Example of Limiting Search Results

select age, zip from Person where ((age > 100 and zip = 20144) or time > 10) order by age asc limit 10

Additional Syntax

If you have a backslash in the string you want to search for (e.g., "path\name"), escape the backslash:

select * from Person where firstName = 'path\\name'
Visit www.terracotta.org for most recent product information.