OGSA-DQP Query Guide

OGSA-DQP supports a subset of SQL with the addition of function calls which allow Web services to be invoked from queries. The parser is not sensitive to case when processing SQL keywords, but is case sensitive when recognising table, attribute and function names. Care should therefore be taken to refer to these elements exactly as they appear in the schemas imported by OGSA-DQP.

Supported data types

The following table describes the mapping of databases types to the OGSA-DQP type system used to evaluate queries. As the OGSA-DQP evaluation infrastructure is implemented in Java, each field in an imported table is mapped to a Java data type. Types that are not listed in this table are not supported and an attempt to import a database table containing an unsupported type with fail.

Database type	OGSA-DQP (Java) type
`VARCHAR`	`java.lang.String`
`TEXT`	`java.lang.String`
`CHAR`	`java.lang.String`
`LONGVARCHAR`	`java.lang.String`
`CHAR`	`java.lang.String`
`REAL`	`double`
`DOUBLE`	`double`
`FLOAT`	`double`
`NUMERIC`	`double`
`DECIMAL`	`double`
`INTEGER`	`int`
`BIGINT`	`int`
`TINYINT`	`int`
`SMALLINT`	`int`
`BIT`	`uk.org.ogsadai.dqp.common.types.Boolean`
`BOOLEAN`	`uk.org.ogsadai.dqp.common.types.Boolean`
`DATE`	`uk.org.ogsadai.dqp.common.types.Date`
`TIME`	`uk.org.ogsadai.dqp.common.types.DateTime`
`TIMESTAMP`	`uk.org.ogsadai.dqp.common.types.DateTime`

Note that only primitive data types such as int, float, string, etc. are supported as parameters and return types for functions. When a web service is imported that contains operations using unsupported types, the import of each such operation will be skipped during import. Only the operations using primitive type parameters and return types will be accessible as functions from within queries.

SQL queries

Grammar

The OGSA-DQP parser is described by the following grammar. Note that:

{ } is used to indicated grouping.
[ ] is used to denote optional elements.
* is used to indicate one or more occurences of a given element.

It is important than queries are terminated by a ; character.

<query>         ::= {<sfwQuery> | <query> <union> <query> [<orderBy>]};

<union>         ::= UNION | UNION ALL | INTERSECT | EXCEPT

<orderBy>       ::= ORDER BY <orderByList>

<orderByList>   ::= { <orderByItem>, <orderByList> } | <orderByItem>

<orderByItem>   ::= { <attribute> | <identifier> | <intLiteral> } |
                     { <attribute> | <identifier> | <intLiteral> } 
                     <direction>

<direction>     ::= ASC | DESC

<sfwQuery>      ::= SELECT <selectList> <fromClause>       
                   [<whereClause>] [<orderByClause>]

<selectList>    ::= <selectListItem>, <selectList>
                   | <selectListItem>

<selectListItem>::= <listItem> | <aggregate>

<aggregate>     ::= SUM (<listItem>) | MIN (<listItem>
                   | MAX (<listItem>) | AVG (<listItem>)
                   | COUNT (<listItem>)

<listItem>      ::= <attribute> | <literal> | <functionCall>

<attribute>     ::= [<identifier>.]<identifier>

<literal>       ::= <strLiteral> | <intLiteral> | <floatLiteral>
                   | <boolLiteral>

<functionCall>  ::= <identifier>(<parameterList>)

<parameterList> ::= <parameter>, <parameterList> | <parameter>

<parameter>     ::= <attribute> | <literal>

<whereClause>   ::= WHERE <condition> {AND <condition>}*

<condition>     ::= <attribute> IN (sfwQuery)
                   | <listItem> <pred> <listItem>
                   | <listItem> <pred> <sfwQuery)
  
<pred>          ::= = | != | < | > | <= | >=;

<fromClause>    ::= FROM fromList

<fromList>      ::= <identifier> | <identifier>, <fromList>

<identifier>    ::= <letter> { <letter> | <digit> | _ }*

<strLiteral>    ::= '{<character>}'

<letter>        ::= A | B | ... | Z
                   | a | b | ... | z

<digit>         ::= 0 | 1 | ... | 9

<character>     ::= <letter> | <digit> | <specialChar>

<intLiteral>    :: <digit> {<digit>}*

<floatLiteral>  :: [<intLiteral>].<intLiteral>

<boolLiteral>   ::= TRUE | FALSE

<specialChar>   ::= _ | %

Table prefixing

To avoid name clashes during database schema import, each table name is prefixed with the database name it is defined in with a delimiter of _ e.g. DatabaseName_TableName.

However, if physical database schema information isn't provided by an OGSA-DAI server then the table names are prefixed by the OGSA-DAI resource IDs.

In either case, in your queries you need to prefix the table names with the database name or resource ID.

If you get the imported schemas from the DQP coordinator then this lists the table names prefixed by the database names or resource IDs, so it's always a good idea to check this first.

Restrictions

The following restrictions are expected to be addressed in subsequent versions of OGSA-DQP:

No support for SELECT * ....
Nested queries: currently nested queries with no correlated variables are allowed in the WHERE clause - other forms of nested queries are not supported.
Functions cannot return collections.
No support for variable assignment i.e. ... FROM person_person AS p;
Not support for OR clauses.
Queries cannot return multiple aggregates e.g. SELECT COUNT(name), AVG(id) from person_person; will not work.
There is no support for GROUP BY.

Example queries

A non-exhaustive range of queries is presented here to give an overview of the supported query types. The examples presented here assume that the following tables have been imported:

`employee`
`name`	`VARCHAR`
`job_title`	`VARCHAR`
`id`	`INTEGER`
`dob`	`DATE`

`person`
`name`	`VARCHAR`
`id`	`INTEGER`
`dob`	`DATE`

It is assumed that the person table exists in a database named person. This means that the imported table is referred to as person_person. The employee table is in a database named company and is therefore referred to as company_employee.

Simple selections


select name from person_person where id<5;
select name from person_person where id<2;
select name, id from person_person where id<25;
select name, dob from person_person where dob<'1940-01-01' and id<50;

Cartesian product


select company_employee.name, person_person.name from company_employee, person_person where company_employee.id<3 and person_person.id<3;

Joins


select person_person.id, company_employee.id, person_person.name, company_employee.name from company_employee, person_person where person_person.name=company_employee.name and person_person.id<company_employee.id and company_employee.id<100 and person_person.id<100;
select person_person.id, company_employee.id, person_person.name, company_employee.name from company_employee, person_person where person_person.name=company_employee.name and company_employee.id<100 and person_person.id<100;
select person_person.id, company_employee.id, person_person.name, company_employee.name from company_employee, person_person where person_person.id<company_employee.id and company_employee.id<10 and person_person.id<10;

Aggregates


select sum(id) from person_person where id<50;
select min(id) from person_person where id<50;
select max(id) from person_person where id<50;
select stdev(id) from person_person where id<50;
select avg(id) from person_person where id<50;
select id, name from person_person where id=(select min(id) from person_person where id<50) and id<200;
select id, name from person_person where id=(select max(id) from person_person where id<50) and id<200;
select person_person.id, name from person_person where name in (select name from company_employee where id<100) and id<100;

Functions


select id, concatenate(person_person.id,':00152') from person_person where id<10 and substring(person_person.name,'mith')=true;

Union


(select dob, id from company_employee where id>240 and id<250) except (select dob, id from company_employee where id<2);
(select dob, id, name from company_employee where id>240 and id<250) union (select dob, id, name from company_employee where id<25);
(select id from company_employee where id<250) union all (select id from company_employee where id<25);
(select id from company_employee where id<250) union all (select id from company_employee where id<25);
(select id from company_employee where id<250) except (select id from company_employee where id<25);
(select id from company_employee where id<22) union all ((select id from company_employee where id<2) union all (select id from company_employee where id<2));

Order by


select id from person_person order by id asc;
select id from person_person order by id desc;
select id from person_person order by name asc, dob desc;

Nested queries


select name from person_person where name in (select name from company_employee);
select name from person_person where id=(select count(name) from company_employee);