If the IP implementer selects to support SQL pass-through mode, then the IP is responsible for the complete execution of the statement. In this mode, the OpenAccess SDK SQL engine provides all the parsing and optional post-processing operations. The IP can choose to do its own ORDER BY or GROUP BY processing if it wishes, or delegate them to the OpenAccess SDK SQL engine. The IP must implement the schema management using the Dynamic Schema approach (see Dynamic schema management).
If the IP cannot handle all queries in SQL pass-through mode, then it should use row-based mode and use the feature of examining the query before deciding how to execute it. See Selective SQL pass-through.
The OpenAccess SDK SQL engine will handle the schema (using the IP-provided dynamic schema interface) and parsing of the SQL query. The IP can get access to the entire search expression and can transform the query based on its execution engine. The IP will build the final result rows (both for simple and join queries).
The IP needs to mark support for Pushdown query processing in the ip_support array. When IP EXECUTE is called for a DML command (SELECT, INSERT, UPDATE or DELETE), it should use the extended Parse Tree API to get the complete SQL query. The extended Parse Tree API will allow the IP to get the select list (the result values that need to be returned in the result rows), the list of tables, the search expression and any post-processing options like GROUP BY and ORDER BY. The SELECT list is exposed as a list of value expressions.
The internal tree of the search expression (WHERE clause) is exposed as a logical expression, consisting of different node types (Logical Expression, Condition, Value Expression, Value).
A WHERE clause of a SQL query is represented by a tree that includes the following types of elements:
• Logical expression node - contains AND, OR, or NOT.
• Value node – contains the column name, a literal value, a sub-query or a scalar function.
For example, consider the WHERE clause in the following query:
SELECT a,b,c FROM emp WHERE lastName LIKE 'Joe%' AND dept = 'eng'
The tree of the WHERE clause should be traversed depth-first to the left. Recurse the left branch until a terminal node is reached. Come up one level, go to the right, and then recurse into it the same way.
In this representation of the WHERE clause, the numbers in parentheses indicate the order in which the node should be extracted:
(4)AND
/ \
(2)LIKE (6)=
/ \ / \
(1)lastName (3)'joe%' (5)dept (7)'eng'
In this example AND is the logical expression node with right and left condition nodes. LIKE and = are condition nodes and lastName, 'joe%', dept and 'eng' are value nodes. The job of the IP is to execute the query and return rows. Each row that is added to the OpenAccess SDK SQL engine needs to include all the columns that are part of the SELECT list.
