Code On Time: Application Factory

Blog

Application Factory

Saturday, May 12, 2012Print Subscribe

Data validation is the single most important component of any data-driven application.

Modern multi-tier web applications introduce a certain level of complexity when it comes to validation. The data travels between multiple tiers: a web browser, application server, and database. Validation can be performed on any application tier and requires a different skill level:

Web Browser tier validation is performed with JavaScript code.
Application Server tier validation is performed with C#/Visual Basic.
Database tier validation is performed with the dialect of SQL supported by the database engine.

Code On Time web applications offer a unique mechanism called SQL Business Rules. These rules exist on the application server tier, are executed by the database engine tier, and affect presentation in the web browser client tier.

Consider the following script written in T-SQL.

-- debug
declare @UnitPrice money                      -- data field value
declare @BusinessRules_PreventDefault bit     -- application tier property
declare @Arguments_CommandName nvarchar(50)   -- application tier property
declare @Result_Focus nvarchar(255)           -- client tier property
declare @Result_ShowViewMessage nvarchar(255) -- client tier property

set @UnitPrice = 10
set @Arguments_CommandName = 'Update'
-- end debug

-- lookup database to find the average price of products
declare @AverageUnitPrice money 
select @AverageUnitPrice = avg(UnitPrice) 
from Products

-- perform validation
if @UnitPrice is not null and @UnitPrice > @AverageUnitPrice
begin
 set @BusinessRules_PreventDefault = 1
 set @Result_Focus = 
    'UnitPrice, You are not authorized to enter ' + 
    'a price higher than the average of ' + cast(@AverageUnitPrice as nvarchar) + '.'
 set @Result_ShowViewMessage = 'Error trying to execute "' + @Arguments_CommandName + 
    '" command.'
end

-- debug
if @BusinessRules_PreventDefault = 1
begin
    print 'BusinessRules_PreventDefault: ' + cast(@BusinessRules_PreventDefault as nvarchar)
    print 'Result_Focus: ' + @Result_Focus
    print 'Result_ShowViewMessage: ' + @Result_ShowViewMessage
end
-- end debug

The script is compatible with the Northwind sample database.

If you execute the script in SQL Management Studio, then the following output will be displayed in the Messages window.

BusinessRules_PreventDefault: 1
Result_Focus: UnitPrice, You are not authorized to enter a price higher
than the average of 28.87.
Result_ShowViewMessage: Error trying to execute "Update" command.

Replace the value of “30” assigned to the Unit Price with “10”, execute the script, and there will be no messages in the output. The validation test has passed.

Start the Project Designer and right-click Products / Business Rules node on the Controllers tab in Project Explorer. Select New Business Rule option.

Enter the following properties and click OK button to save the business rule.

Property	Value
Command Name	Insert\|Update
Type	SQL
Phase	Before
Script	Paste the entire script from above

The hierarchy of the data controller will look as follows.

Click Browse on the designer toolbar. The application will be generated and open in a default web browser. Navigate to Products page and try editing or creating a product with Unit Price higher than $28.87.

The application server tier will not perform the requested Insert or Update if the parameter @BusinessRules_PreventDefault is set to “1” by the business rule.

This is how the error messages are displayed on the client web browser tier when a violation is detected in the form view.

These are the error messages displayed when entering a new product in the data sheet view.

The application framework automatically removes the debug / end debug sections in the SQL Business Rule script. From the database engine prospective, the script looks like this:

-- lookup database to find the average price of products
declare @AverageUnitPrice money 
select @AverageUnitPrice = avg(UnitPrice) 
from Products

-- perform validation
if @UnitPrice is not null and @UnitPrice > @AverageUnitPrice
begin
 set @BusinessRules_PreventDefault = 1
 set @Result_Focus = 
    'UnitPrice, You are not authorized to enter ' + 
    'a price higher than the average of ' + cast(@AverageUnitPrice as nvarchar) + '.'
 set @Result_ShowViewMessage = 'Error trying to execute "' + @Arguments_CommandName + '" command.'
end

Labels: Application Factory, Business Rules/Logic, Web Application Generator, Workflow

Friday, May 11, 2012Print Subscribe

Improving Efficiency of Output Cache Table

Custom result sets can be produced by stored procedures and web services. A quick look at the sample output cache table and output caching script will give you a few ideas about introducing optimization in the caching.

The datable table CustOrderHist_Cache stores cached output created by concurrent users in the process of interacting with the application. The highlighted columns are the actual output columns produced by the stored procedure.

The output caching script filters out the data by CacheUserName column to segregate the user-specific result sets. The performance will improve if the column indexed.

The same consideration applies to the column CachExpires.

A single index that includes both CacheUserName and CacheExpires will likely work well in most situations.

The primary key column CacheID is provided to allow selecting data rows in the application user interface. Our sample result set could be changed to use the ProductName and CustomerID as a compound primary key. It is possible since the stored procedure CustOrderHist produces a list of unique products purchased by a customer. In that case you can delete the CacheID column and simplify the output caching script.

Another area of optimization if the length of time the custom result set remains cached. Most applications will tolerate the data to be “stale” for at least a few minutes if not hours.

The output cache table may end up containing orphan cached data if users are not working with the app on a daily basis. It is a good idea to enhance the caching script with a simple delete statement that wipes out any cached data rows with the cache expiration overdue by a few days.

Labels: Application Factory, Data Sources

Sunday, April 29, 2012Print Subscribe

Automatic Denormalization

Code On Time application generator performs automatic denormalization when constructing application data controllers from tables of a normalized database.

Database architects put a significant effort in creating a normalized database structure. Normalization ensures efficient data storage and maintenance. It is also much easier to extent a normalized database with new tables and columns.

Consider the following subset of tables from the Northwind sample.

Product information is stored in three tables. Raw product data replaces category and supplier information with ID of records stored in Categories and Suppliers tables.

This is not how the application users see the world. Business users want to see the actual category and supplier when looking at a product record instead of a numeric key value.

Application generator composes the following command text stored in the data controller definition.

<dataController name="Products" . . . .>
  <commands>
    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Products"."ProductID" "ProductID"
    ,"Products"."ProductName" "ProductName"
    ,"Products"."SupplierID" "SupplierID"
    ,"Supplier"."CompanyName" "SupplierCompanyName"
    ,"Products"."CategoryID" "CategoryID"
    ,"Category"."CategoryName" "CategoryCategoryName"
    ,"Products"."QuantityPerUnit" "QuantityPerUnit"
    ,"Products"."UnitPrice" "UnitPrice"
    ,"Products"."UnitsInStock" "UnitsInStock"
    ,"Products"."UnitsOnOrder" "UnitsOnOrder"
    ,"Products"."ReorderLevel" "ReorderLevel"
    ,"Products"."Discontinued" "Discontinued"
from "dbo"."Products" "Products"
    left join "dbo"."Suppliers" "Supplier" on 
      "Products"."SupplierID" = "Supplier"."SupplierID"
    left join "dbo"."Categories" "Category" on 
      "Products"."CategoryID" = "Category"."CategoryID"
]]></text>
    </command>
    . . . . .

This is the partial output of the query executed in SQL Management Studio.

If you run the sample application then the Products page will be presented in a denormalized user-friendly fashion.

The actual query executed by application is not the same as text stored in the data controller definition. In fact, the application framework uses the command text as a developer-friendly dictionary to locate at runtime the expressions behind the field names, the base table, and “join” constructs.

This is the actual query text that matches the screen shot.

with page_cte__ as (
    select
        row_number() over (order by "Products"."ProductID") as row_number__
        ,"Products"."ProductName" "ProductName"
        ,"Products"."SupplierID" "SupplierID"
        ,"Products"."CategoryID" "CategoryID"
        ,"Products"."QuantityPerUnit" "QuantityPerUnit"
        ,"Products"."UnitPrice" "UnitPrice"
        ,"Products"."UnitsInStock" "UnitsInStock"
        ,"Products"."UnitsOnOrder" "UnitsOnOrder"
        ,"Products"."ReorderLevel" "ReorderLevel"
        ,"Products"."Discontinued" "Discontinued"
        ,"Products"."ProductID" "ProductID"
        ,"Supplier"."CompanyName" "SupplierCompanyName"
        ,"Category"."CategoryName" "CategoryCategoryName"
    from
    "dbo"."Products" "Products"
        left join "dbo"."Suppliers" "Supplier" on 
            "Products"."SupplierID" = "Supplier"."SupplierID"
        left join "dbo"."Categories" "Category" on 
            "Products"."CategoryID" = "Category"."CategoryID"

    where
    (
    ("Supplier"."CompanyName"=@p0)
    )
)
select * from page_cte__ 
where 
    row_number__ > @PageRangeFirstRowNumber and 
    row_number__ <= @PageRangeLastRowNumber

Notice the use of parameters that prevent any possibility of SQL injection attack.

The SQL statement also utilizes a common table expression for efficient data retrieval.

You can control the inclusion of fields in the data controllers with the help of denormalization field map.

Labels: Application Factory, Web Application Generator