This site hosts historical documentation. Visit www.terracotta.org for recent product information.
Prerequisite: This topic assumes that you have read BigMemory Search Setup.
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.
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.
Note that you can also refine the search criteria by clicking the filter icon on the right side of the column heading:
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>
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.
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.
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");
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 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
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
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.
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));
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
select city from Address where city ilike 'San*'
Note: ilike
takes the wildcard character * for zero or more characters, and ^ for a single character.
select * from searchable where animal ilike 'c*'
select * from searchable where animal ilike 'c?t'
select * from searchable where animal ilike '*e*p*'
select city from Address where city like 'San%'
Note: like
takes the wildcard character % for zero or more characters, and _ for a single character.
select * from searchable where animal like 'c%'
select * from searchable where animal like 'c_t'
select * from searchable where animal like '%e%p%'
BigMemory SQL does not support the following for like
and ilike
:
select * from Person where age between 10 and 20
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)
To be searchable using BigMemory SQL, the data type of an Attribute must be one of the following:
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.
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.
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'
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).
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'
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:
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
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.
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
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
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>
select age, zip from Person where ((age > 100 and zip = 20144) or time > 10) order by age asc limit 10
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'