Tables

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.