• NAME
• SYNOPSIS
• DESCRIPTION
• new
• do_rewrite
• SQL SYNTAX RESTRICTIONS
• FILTER REWRITING

---

NAME

Communiware::Filter::SQL - implements SQL filters

---

SYNOPSIS

  $sth = new Communiware::Filter::SQL($ctx, $filtername, \@params, \%hints, @fields);

---

DESCRIPTION

new

  $sth = new Communiware::Filter::SQL($ctx, $flname, \@params, \%hints, @fields);

do_rewrite

Recieves SQL query and list of attributes

  do_rewrite($ctx, $query, @fields);

Returns sql query rewritten so that it returns all the requested attributes. Attributes which do not have TABLE_NAME field filled in ATTRIBUTE table (as UPDATABLE, some INTERNAL and some COMPUTED) are ignored. If fields are prefixed with + or - they, order by clause of statement is replaced by given fields (+ for ascending and - for descending)

---

SQL SYNTAX RESTRICTIONS

Following rules should be followed to make SQL query work as communiware filter:

All columns returned should be named appropriately. If expression or function calls are used in SELECT clause, appropriate AS clause should be provided. Attribute names in AS clause shouldn't be double-quoted, so SQL server could uppercase them and filter rewriter wouldn't be confused with them.
If communiware attribute, listed in ATTRIBUTE table is returned, it should be named as it is named in this table. Otherwise filter rewriter would attempt to extract it second time. It is better to write filters which extract ITEM_ID alone and let rewriter do the rest, unless you know for sure, that you can achieve better performance with handcrafted query.
All dates should be returned as result of fdate function. Communiware idiom is SELECT fdate(PUBLISHED) as PUBLISHED
Avoid anonymous views. If it is absoultely neccessary to use anonymous view (subquery) in FROM clause, mark filter as non-rewritable.
If, for some reason, you need to use GROUP BY clause, filter should be marked as non-rewritable.
If you use ITEM_ID field from any table other then ITEM in select clause, qualify it by table name

---

FILTER REWRITING

Rewriting of filters pursue following goals:

Extract as much as possible attributes at once to avoid need of additional SQL queries from getattr procedure.
Move burden of result sorting from Communiware engine to database server, thus allowing to minimize fetch.

Filters should conform following rules to be rewritable:

There should be a field in select clause, which is extracted as ITEM_ID. (although it is requirement for all other filters too).
No anonymous views should be listed in from clause.
If ITEM table is used in the FROM clause, and its ITEM_ID is equial to extracted ITEM_ID, it shouldn't be aliased. Otherwise it must be aliased. Same stands for any of extended attributes table.
No attribute aliases in select clause should be double-quoted.

Rewriting is perfomed in following steps:

List of given attributes is checked. Attributes which cannot be added to filter (all QUALIFIED and some COMPUTED) are removed from it and order by clause is construncted if there are some attributes with + and -. List of required tables is constructed
List of attributes already selected by filter is checked and any attributes which are present in it, are removed from list of needed attributes. At the same step primary key - thing which be used as item id is extracted
FROM clause is checked. If ITEM table, or some extended attributes table, which is needed for current attribute list is missing from FROM clause, it is added, and appropriate join expression is appended to WHERE clause. (it is assumed, that if any extended attriubte requested, all items would have it, so INNER join is performed.
If ORDER BY clause was constructed on step 1, it replaces current ORDER BY clause.