SQL and the Rules Engine

An important part of your USoft Rules Engine will be written in SQL.

In USoft, key objects such as Constraints, Logical Views and Batch SQL Tasks are all expressed in SQL.

The strength of the USoft concept is that you can achieve maximum expressivity and functionality without using complex SQL syntax. All you need is a good command of the standard ANSI-92 SQL grammar.

The dialect of core SQL constructs supported by USoft is the subset of ANSI SQL listed in this topic.

You can use this SQL dialect in all SQL contexts offered by USoft tools, such as SQL Command, Authorizer Conditions, BenchMark, and SQL statements in GUI tools, except if limitations are stated.

This help topic provides only a brief overview of the main constructs.

Use SELECT statements to retrieve data, to define Logical Views, and (while defining Constraints) to define data that is the subject of restrictive rules.

Syntax

SELECT         expression
,              ...
FROM           table  alias
,              ...    alias
RELATE         alias  "role"  alias
,              ...
WHERE          condition
{ AND | OR }   ...
GROUP BY   ...
HAVING     ...
ORDER BY   ...

Example

This SELECT statement retrieves country information of all Latin-American countries:

SELECT        c.country
,             b.max_no_of_assets
,             COUNT(*) no_of_customers
FROM          country c
,             customer cs
,             boundary b
RELATE        cs "RESIDES IN" c
WHERE         b.country = c.country
AND           b.name = 'max_no_of_assets'
AND           c.area = 'LATIN AMERICA'
GROUP BY      c.country
,             b.max_no_of_assets
ORDER BY      c.country

The required SELECT clause must contain a list (called output list) of 1 or more expressions. Expressions must be separated by commas.

The FROM clause must identify 1 or more tables. Multiple table names must be separated by commas. In USoft, you can omit the FROM clause if you don't need to retrieve stored data from the RDBMS, as when you are only interested in the outcome of a SQL function:

SELECT AVG ( 178, 211 )

If the FROM clause lists multiple tables, the statement is called a join between those tables. The result of a join contains a record for each combination of a record of the first table and a record of the second table: if the first table has 1000 records and the second table 20, the result will contain 20,000 records. You can limit such a large result set by adding restrictive conditions in one or more RELATE clauses and/or one or more WHERE clauses.

Using RELATE, a USoft-specific extension to SQL, your join result will be restricted to combinations of records of two tables that "belong together" as defined by a Relationship between the tables. You can add multiple RELATE clauses using commas as a separator. Any RELATE clause or clauses must immediately follow the FROM clause.

Using WHERE, AND and OR, you can further restrict your query result by applying standard first-order logic to your result set. This part of SQL is explained in all SQL textbooks.

GROUP BY, HAVING and ORDER BY are also standard SQL clauses explained in any tutorial. Using GROUP BY, you can group retrieved data by category and apply group operations to each category, such as counting customers by country of residence:

Using HAVING, you can apply restrictions to the groups defined by GROUP BY that you want to see in your result.

Using ORDER BY, always as the last clause of any SELECT statement, you can sort the result set in a specific way.

A subquery is a SELECT statement nested in an embedding SQL statement, which may be a higher-level SELECT, but also an INSERT, UPDATE or DELETE statement. The subquery is placed between parentheses:

higher-level-statement
(
SELECT ...
)

Use subqueries to retrieve additional data needed for the execution of your higher-level statement. You can nest subqueries any level deep.

An independent subquery will return a result that is then passed to the embedding statement. The embedding statement is executed as an independent next step. The subquery does not reference elements in the higher-level query. In the example, the count(*) function will return an integer that is passed to the higher-level SELECT. Then, this higher-level SELECT will execute. In the example, if the subquery returns 10, the overall result set will be restricted to customers who have more than 10 assets. The same boundary value applies to all customers:

SELECT    c.name, COUNT(*)
FROM      customer c
,         asset a
RELATE    c "HAS" a
GROUP BY  c.name
HAVING    COUNT(*) >
(
    SELECT   b.value
    FROM     boundary b
    WHERE    b.name = 'max_no_of_assets'
)

A joined subquery refers to a table instance in the higher-level SQL statement, joining each record of the subquery to each corresponding record in the higher-level query, much in the same way as joining takes place when you name more than 1 table in the FROM clause. In the example, each customer who has more assets than allowed in her country of residence is included in the result set. The boundary value is different for customers in different countries:

SELECT    c.name, COUNT(*)
FROM      customer c
,         asset a
RELATE    c "HAS" a
GROUP BY  c.name
HAVING    COUNT(*) >
(
    SELECT   b.value
    FROM     boundary b
    WHERE    b.name = 'max_no_of_assets'
    AND      b.country = c.country_of_residence
)