Named Queries

From KallestadWiki

Jump to: navigation, search

Contents

[edit]

Overview

Named Queries are exactly that - queries that are named.

Named queries have their statement execution plans cached at startup time to maximize processing efficiency.

Note to self - implement functionality to flush and reanalyze execution plans for cached statements without having to restart

Named Queries are complex objects, the data structure is outlined below.

<<insert data structure>>

Queries consist of the SQL statements, Parameters, and Bindings. The statements themselves can of course receive variables that affect the query, but the statements do need to be complete enough so that an execution plan can be formed.

In other words: 

Select * from table where ?

Is not a valid named query

The following: 

Select * from table where field = ?

is a valid query.

The second query provides enough information to the database platform to figure out how to best respond to the query request. The first query is too ambiguous to develop a complete plan without further information. Since named queries are planned and cached at startup, they have to be at least reasonably complete.

Named Queries are available to be queried via web services API, or through triggered actions. Named queries have permission properties to define the groups, individual users, and availability of them for usage from within the framework (i.e. api availability vs. triggered action availability).

[edit]

Data Structure

The execution of a Named Query requires a data object containing enough information to run the named query.

The data structure is as follows: 

object -> nq == string Query Name
       -> pv == array Parameter Values

Future: Type check so that a single passed value can be a string rather than a single element array

Take for instance the following named query: 

Query Name: get_user_info
Query Statement: 
  select first_name, last_name, address, phone from users 
   where first_name = ? and last_name = ? limit ?,?
Expected Parameters: First, Last, Results Limit, Offset
Bindings: Limit = Integer, Offset = Integer

To execute the above query, an object similar to the one that follows would need to be passed: 

object -> nq => 'get_user_info'
          pv => ['fred','flinstone',1,0]
[edit]

Sanity Checks

When a named query action is triggered, the following sanity checks are in place:

  • does the object model match the expected object model
  • does the specified named query exist
  • does the number of parameters provided equal the number of parameters expected?
[edit]

Restrictions

Named Queries are most commonly select statements, but can also be Delete, Insert, or Update statements.

[edit]

Kallestad Development Framework Topics

Retrieved from "http://www.stevekallestad.com/wiki/Named_Queries"
Personal tools