Continuing with part 2 of the Helsinki presentation

The second part of my two-hour Helsinki presentation brings the message that in order to avoid PL/SQL spaghetti when taking the "fat database" approach, one must employ a layered PL/SQL code architecture inside the DBMS. This means that UI-code, BL-code and DL-code should not be merged together inside single pieces of PL/SQL code blocks, but instead remain completely separated. Now, for UI-code that's often easy since this code is most of the time developed outside the DBMS (unless you are using APEX). But even then no BL-, nor DL-code should be embedded inside the UI-technology du-jour that you use. Instead you need to introduce database UI-API's that act as services to the UI-layer: these services implement the required data retrieval and data manipulation that the UI requires and act as the "contract" that the UI has with the DBMS.

This "design by contract" is a very important aspect of the Helsinki way of building a WoD application. I'll give examples of these contracts in this and future posts.


Let's assume we have a simple database design of two tables: a BOOK table (which holds book titles) and a ORDERLINE table (which holds orders against titles for a user).


The EAN column is primary key in the BOOK table and columns USER and EAN are primary key in the ORDERLINE table. EAN is also foreign key back to the BOOKS table. All fairly simple so far.
There are also two "other" constraints involved in this two-table database design.

• A user cannot have more than a (sum) total of fifty books in ORDERLINE.
• A title cannot be ordered more than a (sum) total of eighty in ORDERLINE.
  

I use SQL assertions quite often nowadays to document constraints more formally. The two introduced for this small example can be specified as follows.

create assertion no_more_than_50_per_user as
check(not exists(select 'a user with more than 50 books'
               from (select username,sum(amount) as total
                     from orderline
                     group by username)
               where total > 50)
);

create assertion no_more_than_80_per_title as
check(not exists(select 'a title with more than 80 ordered'
               from (select ean,sum(amount) as total
                     from orderline
                     group by ean)
               where total > 80)
);

(By the way: had Oracle provided us with support for SQL assertions, the DL-code part would be done now.)

So that's our database design. And here is the UI-page that we need to build.


This page shows all current orderlines for a given user (shown at the top of the page). The user is given the possibility to change the amount of each orderline and press the "Save" button to store such changes. Orderlines can optionally be filtered: the user has a "EAN" item, "Title string" item, and "Search Orders" button to do so.

The full page flow is shown here.


In the Helsinki approach it is important to fully understand the "flows" that can happen for a page: it will help identifying the UI-API's that are required (per flow). In this case,

1. The page is entered via some other page / menu of the application.
2. The page is then rendered with no additional search criteria: all orderlines of the user that's currently logged on are retrieved and displayed. We need a UI-API for this obviously.
   
3. The user can supply addiotional search criteria and re-render the page by pressing the "Search Orders" button. For this flow we need to design some UI-API (which can preferably be combined with the one mentioned above).
4. The user can also enter new values for the currently ordered amount per orderline and press the "Save" button. The changes are then processed and the page is re-rendered. For this flow we also need to design some UI-API.
5. Finally the user can exit the page by pressing the "Exit" button.

Let's first take a short look at traditional approaches of UI-code communicating with the DBMS to retrieve and transact the data.

One way to implement flows 2 and 3 (i.e. the data retrieval) would be to code the SQL join that retrieves the necessary data. This SQL statement would be "parameterized" by three values: the user that's currently logged in, the value of the EAN item on the page, and the value of the Title string value on the page.


Not a very elegant query. It's basically a single query covering four cases.

1. Both search items are empty.
2. Only a value for EAN is supplied by the user.
3. Only a value for Title string is supplied by the user.
4. Values for both items are supplied by the user.

For the DBMS (read, optimizer) it would be much better to have a dedicated query per case above.

Another (worse) approach typically found in mid-tier centric architected applications is this one.


This again illustrates that mid-tier centric applications, due to their single-table only accesses, are very chatty: they communicate back and forth to the DBMS way more than is necessary.

A traditional approach to implement flow 4 (the Save button) goes as follows. The UI-code detects for which rows the user has entered a new amount. Per such row the UI-code then generates an update statement that writes the new amount in the row, using the PK-value to locate the row to be updated.


And, since we are changing data in this flow, the constraints will need to be validated: in this case two queries will be executed to verify our two constraints. By the way, I'm totally ignoring concurrency issues here now (will return to that in a future post).

Note: all SQL statements mentioned in above traditional approaches, will be burried away inside the UI-code layer. They are embedded in there, somewhere. They sit outside the DBMS which, in Helsinki, is not good.

The Helsinki approach

The Helsinki approach to this example would be to:

1. first implement all involved DL-code separately using database table triggers (or declarative constraints where possible), then
2. design/build the UI-API's required for the various flows, and finally
3. do "the rest": also known as, BL-code (of which there is not much in this particular example).

So let's start with number one: DL-code. Currently I see three options to do this: you Build the triggers on Your Own (BYO). I've demonstrated how this could be done while taking into account concurrency issues and efficiency, at Tom Kyte's blog a while ago (see this post for the specific url to Tom's blog). Or, you use a framework to generate the trigger code for you.

<Warning>
Shameless commercial advertisement about to happen...
</Warning>

As far as I know there are two database frameworks that can do this for you: CDM*RuleFrame (part of the Headstart offering of Oracle), or RuleGen (available from www.RuleGen.com, the company I work for). I just started running another blog where I intend to demonstrate RuleGen more often in the future. For now I've posted a highlevel description of how the two constraints of our example here would be fed into RuleGen.



Once the DL-code is taken care of, you continue with step 2, being: design the database API for the page.


In this case the page has three items that determine what data (orderline rows) is to be shown:

• the user that's logged in (on the top of the page, slightly to the left)
• the value of the EAN enterable item
• the value of the "Title String" enterable item

For flows 2 and 3 (first entry of this page, and user presses "Search Orders" button), we can design the UI-API in a couple of ways. We'll describe three alternatives: one using a ref-cursor, one using a pipelined-function view, and one using XML.

Here's the first one.


The UI-code layer calls a stored procedure and supplies the three (IN) values, user, ean, search-string, which the stored procedure then uses to build the query-text that is to be used for that case. This query-text is then returned as a ref-cursor in an OUT-parameter of the stored procedure. Note, we should now return a query-text *per case*, and not have one generic query-text (using nvl's). Here's a stored procedure get_rows_page_1 demonstrating this:

create or replace procedure get_rows_page_1
(p_username    in varchar2
,p_ean         in varchar2
,p_book_string in varchar2
,p_cursor     out sys_refcursor) is
--
begin
--
if p_username is null
then
--
open p_cursor for
  select bo.username
        ,bo.ean
        ,substr(bo.ean||' '||b.author||' '||b.title||' / '||b.publisher,1,95) as title
        ,bo.amount
        ,to_number(null) as new_amount
  from book b
      ,bookorder bo
  where b.ean = bo.ean
    and 1 = 0;                         -- ***** NO USER INFO  ************************
--
else
--
if p_ean is null and p_book_string is null
then
  --
  open p_cursor for
    select bo.username
          ,b.ean
          ,substr(bo.ean||' '||b.author||' '||b.title||' / '||b.publisher,1,95) as title
          ,bo.amount
          ,to_number(null) as new_amount    -- **** NOTE: ALSO RETURNING THE UPDATE-ITEM *********
    from book b
        ,bookorder bo
    where b.ean = bo.ean
      and bo.username = p_username;     -- ***** NO ADDITIONAL FILTERS  ************************
  --
elsif p_ean is not null and p_book_string is null
then
  --
  open p_cursor for
    select bo.username
          ,b.ean
          ,substr(bo.ean||' '||b.author||' '||b.title||' / '||b.publisher,1,95) as title
          ,bo.amount
          ,to_number(null) as new_amount
    from book b
        ,bookorder bo
    where b.ean = bo.ean
      and bo.username = p_username
      and bo.ean = p_ean;               -- **** ONLY EAN FILTER  ************************
  --
elsif p_ean is null and p_book_string is not null
then
  --
  open p_cursor for
    select bo.username
          ,b.ean
          ,substr(bo.ean||' '||b.author||' '||b.title||' / '||b.publisher,1,95) as title
          ,bo.amount
          ,to_number(null) as new_amount
    from book b
        ,bookorder bo
    where b.ean = bo.ean
      and bo.username = p_username
      and(b.title like '%'||p_book_string||'%'  -- **** ONLY TITLE STRING FILTER  ************************
         or b.author like '%'||p_book_string||'%'
         or b.publisher like '%'||p_book_string||'%');
  --
else /* both filters not null */
  --
  open p_cursor for
    select bo.username
          ,b.ean
          ,substr(bo.ean||' '||b.author||' '||b.title||' / '||b.publisher,1,95) as title
          ,bo.amount
          ,to_number(null) as new_amount
    from book b
        ,bookorder bo
    where b.ean = bo.ean
      and bo.username = p_username
      and bo.ean = p_ean
      and(b.title like '%'||p_book_string||'%'  -- **** BOTH FILTERS  ************************
         or b.author like '%'||p_book_string||'%'
         or b.publisher like '%'||p_book_string||'%');
  --
end if;
--
end if;
--
end;
/

We've now succesfully pushed all business logic into the database-layer. All the UI-code now needs to do is: execute this procedure and fetch rows from the ref-cursor returned until %NOTFOUND, and then render the page.

I'll show the other two alternatives (a pipelined-function view and using XML) in the next blogpost.

The Helsinki Platform

Nice picture from "OraDude" showing the Fat Database, or Helsinki's first observation.

Two weeks to go for ODTUG

ODTUG Kaleidoscoop 2009 is approaching soon. I'll be hosting two presentations.

1. Fat Databases: a Layered Approach
   This will basically be the Helsinki Declaration talk, only crammed into just one hour. I'll probably skip the four observations and go straight to the WoD application and its code classification (DL, BL and UI-code). And close with a short demo by building a page (with Apex of course) the Helsinki way.
   The talk will be on wednesday morning 8AM! So you'll need to get up early if you want to join. It's in the Regency Ballroom I (sounds like a big room...).
   
2. Semantic Query Optimization
   I've been giving this talk for a few times now. The first time was at Hotsos 2008. Semantic query optimization is about using integrity constraints in the optimization phase that generates the execution plan. A few years ago I investigated the capabilities of Oracle's optimizer (10G and 11G) in this area. The talk summarizes this investigation and draws a few conclusions.
   This talk will be right after my other talk at 9.15AM in the same location: Regency Ballroom I. So you can just remain seated and be entertained by me during two consecutive slots.

Hope to see you there!

Rebound for database design?

My fellow Oaktable member Robyn Sands posted something very "inline" with the Helsinki declaration. Good comments too.