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:
ParametersNormally, one does not need to pre-defined parameters for a statement. The SqlPattern class will automatically handle most parameters as follows:
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, LastYearTargetObjectThe 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, WAto 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.
PaginationFrequently, 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 = @@ROWCOUNTIF @DisplaySize = 0 OR @DisplaySize > 100000 SET @DisplaySize = 100000SELECT {Select.ForeignKeyLabel},@Count AS RecordCountFROM {From.ForeignKey}INNER JOIN @SortTable AS SortTable ON SortTable.IdentityID = [{Table}].[{Table}ID]WHERESortTable.RowNumber BETWEEN @RecordStart AND (@RecordStart + @DisplaySize - 1)ORDER BY SortTable.RowNumberSome items to note:
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).
|