Statements: QBO 3's version of Stored Procedures

Statements, handled by the DbStatement class, are QBO 3's version of a stored procedure. A statement is SQL combined with expressions handled by the SqlPattern class, such as:

SELECT {Table}ID, {Table} FROM {From.ForeignKey} WHERE {Where.Basic}

The expressions in curly braces will be replaced with SQL specific to the class handling the statement at run time. This allows statements to be designed without knowing all of the tables or fields at design time. This allows patterns to be changed without re-factoring dozens or hundreds of stored procedures.

Statements comprise the following attributes:

• • Name: unique within a class, the name of the statement (e.g. "Search", or "Select", or "MyCustomStatement")

  • Query: a mix of SQL and SqlPattern clauses (like the example above)

  • Parameters: predefined parameters to pass to the statement

  • ReturnType: DataSet, DataReader, XmlReader, Object

  • Transform: presentation transform (XSLT) to use when displaying the output of the statement

  • Root: name of the containing DataSet or XML; defaults to {Table}

  • Permission: name of the permission required to invoke the statement; defaults to {Table}{Statement.Name}

  • Timeout: the timeout set when executing the query (if NULL, the application setting default timeout will be used)

  • BaseStatement: another statement to inherit attributes from

  • BaseOperation: another operation (statement, C# method, or IService entry) to invoke

  • TargetObject: another class (parameter-driven) to execute the statement against

  • Cascade: when true, the SQL will be replicate for each of the base classes the class inherits from; useful for INSERTs and UPDATEs that touch base tables

  • RequireParameters: when true, the SQL will only be emitted if parameters are present for the target table; useful in conjunction with Cascase

  • AllowInheritance: when true, will execute as an inherited user without verifying permissions

  • AllowDirtyReads: when true, will execute in a mode that allows dirty data reads (which can avoid database blocking)

Parameters

Normally, one does not need to pre-defined parameters for a statement. The SqlPattern class will automatically handle most parameters as follows:

• • UserID: this is always included as a parameter (even if not used by the SQL), and is hard-coded to be the current user's PersonID

  • Default Parameters: these are pre-defined in the qbo.Application.Properties.Settings.DefaultParameter setting, and are auto-added to the parameters if not otherwise specified

    • currently, DefaultParameters are DisplaySize=25&Batch=500&RecordStart=0

  • All parameters specified in the RESTful call (e.g. all query string parameters) will be passed to the statement

So, if we call Organization.ashx/Save?Organization=Amce Software&City=Los Angeles&State=CA, Orgainzation, City, and State will automatically added as parameters, available for use by {Where.Basic} or explicit calls.

In some cases, we may have SQL that expects a know parameter (e.g. @Object and @ObjectID). If the user does not specify these parameters on the REST call, the parameters will not be available to the statement, and a database error will result. In such cases, it makes sense to define the expected parameters for a statement, like this:

<Statement Name="ListByObject" Query="SELECT ... FROM {Table} WHERE Object=@Object AND ObjectID= @ObjectID">

  <Parameters>

    <Parameter Name="Object" DbType="String"/>

    <Parameter Name="ObjectID" DbType="Int64"/>

  </Parameters>

</Statement>

You may also specify default values for these parameters, including dynamically parsed dates.  For example:

<Statement Name="ListByObject" Query="SELECT ... FROM {Table} WHERE CreatedDate BETWEEN @StartDate AND @EndDate">

  <Parameters>

    <Parameter Name="StartDate" DbType="DateTime" Value="1/1/2010"/>

    <Parameter Name="EndDate" DbType="DateTime" Value="{tomorrow}"/>

  </Parameters>

</Statement>

Valid date substitution values include:

Today, Yesterday, Tomorrow, FirstOfWeek, FirstOfLastWeek, FirstOfNextWeek, FirstOfMonth, FirstOfLastMonth, FirstOfNextMonth, FirstOfYear, FirstOfLastYear, FirstOfNextYear, LastOfWeek, LastOfLastWeek, LastOfNextWeek, LastOfMonth, LastOfLastMonth, LastOfNextMonth, LastOfYear, LastOfLastYear, LastOfNextYear, LastThirty, LastWeek, LastMonth, LastYear

TargetObject

The TargetObject parameter of a statement allows for an dependency-injection like model for statements. For example, the CollectionMember class is used to define related groups ("Collections") of records. It is useful to be able to search CollectionMember records based on values in the record they're mapped to.

For example, one might create a Collection of Organizations called 'Title Companies', and then display a list of all title companies with a filter by State. State does not exist in the CollectionMember table, but does exist in the Organization table (in this case, the target object). We can define a List statement with a like this:

<Statement Name="List" Root="CollectionMember" TargetObject="SourceObject" Query="SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic}"/>

and then invoke:

CollectionMember.ashx/List?SourceObject=Organization&State=CA, NV, AZ, OR, WA

to get a list of all Title Companies in the western states.

When the CollectionMember class processes this statement, we see that the TargetObject parameter 'SourceObject' = 'Organization'. The CollectionMember will dynamically instantiate an Organization object, add a CollectionMember_List statement to its configuration, and execute it. This means that the SqlPattern expressions are evaluated against Organization in this case, instead of against CollectionMember.

Pagination

Frequently, QBO tables contain millions of rows of data. Displaying such data in a UI obviously requires pagination. Many application frameworks place the burden of such pagination in the application tier. In our experience, this frequently degrades application performance, since potentially millions of rows of data are transmitted between the SQL server and the app / web server, only to be filtered down to the top 25 in the application tier.

Thus, QBO 3's search pattern implements pagination in the data tier, so that the data being passed to the application tier is just the data needed for presentation. To achieve this, the following construct (pseudo-code) is used:

DECLARE @SortTable TABLE (RowNumber int IDENTITY(0,1), IdentityID int);

DECLARE @Count int;

IF @Batch < @DisplaySize SET @Batch = @DisplaySize;

IF @DisplaySize = 0 SET @Batch = 100000000;

INSERT INTO @SortTable (IdentityID) 

SELECT TOP (@Batch) {Table}.{Table}ID FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Filters} {Extranet.Default} ORDER BY {OrderBy.Parameters}

SET @Count = @@ROWCOUNT

IF @DisplaySize = 0 OR @DisplaySize > 100000 SET @DisplaySize = 100000

SELECT {Select.ForeignKeyLabel},

@Count AS RecordCount

FROM {From.ForeignKey}

INNER JOIN @SortTable AS SortTable 

ON SortTable.IdentityID = [{Table}].[{Table}ID]

WHERE

SortTable.RowNumber BETWEEN @RecordStart AND (@RecordStart + @DisplaySize - 1)

ORDER BY 

SortTable.RowNumber

Some items to note:

• • DisplaySize: this parameter determines the number of rows to return; by default the display size is 25 records

  • RecordStart: this parameter determines which record to start with; page 1 would set RecordStart = 0, page two would set RecordStart to 25 (page * display size)

  • Batch: this parameter determines the maximum number of rows to add to the table variable; the default is 500

  • the RecordCount column returned will include the number of matching records (regardless of DisplaySize and RecordStart), up to Batch

Batch is used to manage memory SQL-server side. Building table variables of 100,000 rows is much more expensive than building table variables of 500 rows. In typical use cases, if a user is searching and the results return more than 500 rows, the user is not likely to pagination through 20 pages of information; instead, they will typically refine their search (by adding search terms or more explicit criteria).

There are use cases when one wants a precise count of matching rows, or even to return all matching rows (such as downloading all matching rows to a spreadsheet). In such cases, the UI can submit Batch=10000000, ensuring all rows (up to 1 million) are returned (albeit with an 'expensive' table variable operation).