CentraSite Utilities Core Library - CentraSite Query Language

CentraSite Query Language

The CentraSite Query Language (CSQL) is a very reduced subset of HQL, the Hibernate Query Language. Compared to the native CentraSite Query API, which is based on JAXR and XQuery, it provides a number of advantages:

It is based on an abstract meta model. As such, it is ideally suited for model driven software development (MDSD). Of course, this meta model happens to be based on the CentraSite registries meta model: It can be created either by dynamically reading the CentraSite registry (slower, but easier to handle) or by providing a generated representation.
It is version agnostic: If the internal database schema changes, then your application upgrade can be done by replacing the CSUtils Core Library, rather than rewriting your XQuery statements.
It supports joins. Yes, it really does.
It is much easier to understand and read.
It supports parameters. No need to escape strings or stuff like that, and much easier to protect yourself against SQL (sorry, I meant XQuery ...) injection.

Examples

Let's go straight into the query language by showing some examples.

A simple example

Take a look at the following query:

    FROM ROOrg AS o WHERE o.counter = 57

This example would return all instances of a custom asset type ROOrg with a slot counter, which has the integer value 57.

A join

Here's an example of a query, which performs a join over two custom asset types, that are connected through a relation "ResponsibleFor".

    FROM ROUser AS u JOIN u.ResponsibleFor AS o
    WHERE o.counter = 57 AND u.comment = 'NONE'

This query would return a list of rows. Each row would be an array of two registry objects: One instance of ROUser, and one instance of ROOrg. Both instances are connected through the relation ResponsibleFor and have the values 57, and 'NONE' in the slots counter, and comment, respectively.

The API

Here's the source code that shows how to use the CSQL:

  ConnectionProvider connProvider = new DefaultConnectionProvider(url, userName, password);
  ModelDrivenRegistryFacade facade = new SimpleModelDrivenRegistryFacade(connProvider.getConnection());
  ROMetaModelAccessor acc = facade.getMetaModelAccessor();
  String q = "FROM ROOrg AS o WHERE o.counter = 57";
  List<RegistryObject> ros = acc.executeQuery(q);

And here's how to modify the last two lines for the join query:

  String q = "FROM ROUser AS u JOIN u.ResponsibleFor AS o"
             + " WHERE o.counter = 57 AND u.comment = 'NONE'";
  List<RegistryObject[]> ros = acc.executeArrayQuery(q);

Functions

Some builtin functions are available, which can be applied to registry object properties, or constants. A typical example would be:

  FROM User AS u WHERE upper(u.name()) LIKE upper('abc%')

The example would check, whether the users name is matching the given pattern, ignoring the case of characters. Available functions are:

Function name	Description	Arguments
lower	Converts the given string to lower case.	String to convert
upper	Converts the given string to upper case.	String to convert

Registry object methods

So far, we've been only using slot values in queries. Of course, we'd also like to use object names and descriptions. This can be done by using the methods "name", and "description", with queries like this:

  FROM ROUser AS u JOIN u.ResponsibleFor AS o WHERE o.name()='Some User'
  FROM ROUser AS u JOIN u.ResponsibleFor AS o WHERE o.name('de-DE')='Some User'
  FROM ROUser AS u JOIN u.ResponsibleFor AS o WHERE o.description() IS NULL

These examples would query for users named "Some User", or without description.

Method name	Description	Arguments
description	The registry objects description.	Optional locale
key	The registry objects key.	No
majorVersion	The registry objects major version.	No
majorVersion	The registry objects minor version.	No
name	The registry objects name.	Optional locale
stability	The registry objects stability. Note: The value is the stability name, for example DYNAMIC, not the integer value.	No
status	The registry objects status. Note: The value is the status name, for example SUBMITTED, not the integer value.	No
userVersion	The registry objects user version.	No

Parameters

The above examples aren't typical for real world applications. In most cases, you'd like to use parameters, rather than constants. Here's how you would achieve that:

  String q = "FROM ROUser AS u JOIN u.ResponsibleFor AS o"
             + " WHERE o.counter = ? AND u.comment = ?";
  List<RegistryObject[]> ros = st.executeArrayQuery(facade, Integer.valueOf(57), "NONE");

If you prefer named parameters, that's possible too:

  String q = "FROM ROUser AS u JOIN u.ResponsibleFor AS o"
             + " WHERE o.counter = :cntr AND u.comment = :cmmnt";
  CsqlStatement st = acc.prepareStatement(q);
  st.setInt("cntr", 57);
  st.setString("cmmnt", "NONE");
  List<RegistryObject[]> ros = st.executeArrayQuery();

Namespace Declarations

Suggest the following query:

  FROM Organization AS o

This query works fine, if the name Organization is unique in your registry. More precisely: If there is at most one asset type with local name Organization. The query fails, if there are, for example, two asset types named SomeNamespaceOrganization and OtherNamespaceOrganization.

Otherwise, you've got to tell the query engine explicitly, which name to use by using namespaces. Here is an example:

  DECLARE NAMESPACE s='SomeNamespace'
  FROM s:Organization AS o

Pseudo object types

Pseudo object types are used to search for objects, which may have any of a given set of types. For example:

  DECLARE NAMESPACE q='http://namespaces.csutils.sf.net/query/1.0.0'\n"
  FROM q:allTypes o WHERE o.name()=?

This query uses the pseudo object type http://namespaces.csutils.sf.net/query/1.0.0allTypes. This pseudo object type includes all object types in the registry. In other words, the query would search for arbitrary objects with a particular name.

A shortcut for the above example would be

  FROM * o WHERE o.name()=?

TODO

Unfortunately, there's a real lot to do. The CSQL is basically an initial draft, and nothing more:

Support for builtin object types like organization (with email address, phone number, ...)
Not to mention serious query language features like subqueries.

CentraSite Utilities Core

Project Documentation