One of the ways that Rapid Application Prototype (RAP) makes systematic design palatable is that it provides short-term rewards in return for desirable long-term behaviors. For example, ideally programmers should always:

• define primary keys
• define foreign keys
• define unique indexes

As trivial as these might seem, it is the rare project in which these tasks are performed rigorously. The primary reason is that programmers are generally not rewarded for exhibiting these behaviors. Another is that in the short term, programmers may actually be punished for doing these things. For example, if a programmer defines a foreign key and then writes code that violates the relationship, the easiest way to alleviate the problem and meet one's schedule is to simply remove the foreign key.

To reverse this unfortunate psychology, RAP rewards programmers immediately for good behavior. When a programmer creates any of the above objects, RAP rewards him/her by auto-generating lookup routines that alleviate the programmer's need to write such routines. Eventually the programmer comes to realize that the fastest way to get an application written is to exhaustively define keys and indexes, ensuring that all key-and-index based lookups that the programmer might want in the future are already written.

The RAP Database Generator: database fetch routines

The database generator generates four "fetch" user

functions for table TBadmUser:

• UFTBadmUser##PK (@AsOfDate, @UserId)
  • is generated from the primary key (UserId)
  • retrieves the record whose UserId is @UserId
• UFTBadmUser##UK_LoginName (@AsOfDate, @LoginName)
  • is generated from the unique index on column LoginName (UK_LoginName)
  • retrieves the record whose LoginName is @LoginName
• UFTBadmUser#PK (@AsOfDate, @UserId)
• is just like UFTBadmUser##PK but also retrieves all records if @UserId is null
• UFTBadmUser#UK_LoginName (@AsOfDate, @LoginName)
• is just like UFTBadmUser##UK_LoginName but also retrieves

  all records if @LoginName is null

We generate user functions rather than stored procedures because user functions are far more reusable. The output of a table-valued user function can take the place of a table in any SQL query, so unlike with stored procedures we can combine as many user-function retrievals as we wish into queries of any desired complexity. Thus the query logic that RAP generates is not confined to simply piping data back to the application. We could, for example, use these user functions to build a view or stored procedure that drives a report. We will see shortly why this is useful.

You may have noticed that each user function has "@AsOfDate" as its first argument. Let's look at the code in one of these functions to see what that is about:

-- **** AUTO-GENERATED 'FETCH' USER FUNCTION FOR 'TBadmUser[PK_TBadmUser]'  ****
create function UFTBadmUser##PK
(
-- declare the 'As Of' parameter
@AsOf datetime,
-- declare other parameters
@UserId bigint
)
-- declare the return value
returns @ReturnValue table
(
UserId bigint,
LoginName varchar(20),
Notes varchar(max),
AuditDate datetime,
AuditUserId bigint,
AuditStatus char(1)
)
as
begin
if @AsOf is null
begin
insert into @ReturnValue
select
UserId,
LoginName,
Notes,
AuditDate,
AuditUserId,
AuditStatus
from TBadmUser
where 
-- match every index field to its corresponding parameter
UserId = @UserId
end
else
begin
insert into @ReturnValue
select
A.UserId,
A.LoginName,
A.Notes,
A.AuditDate,
A.AuditUserId,
A.AuditStatus
from TBadmUser# A
where
-- match every index field to its corresponding parameter
A.UserId = @UserId
-- don't emit records marked 'deleted'
and A.AuditStatus <> 'D'
-- require the most recent version preceding the 'As Of' date
and A.AuditDate =
(
select max(AuditDate) from TBadmUser#
where
-- match the primary keys
UserId = A.UserId
-- choose only records at or before the specified AsOfDate
and AuditDate <= @AsOf
)
end
return
end

The function looks to see whether you have passed a value of null for @AsOfDate. If so, then it simply retrieves the record matching the specified @UserId from the primary table (TBadmUser).

The interesting part is where you pass an actual date in @AsOfDate. In that case, the "else" query (above) executes. This query gets its data from the archive table (TBadmUser#). The logic in the "where" clause simply says, "get the record that matches the @UserId and whose AuditDate is the most recent date that precedes or equals @AsOfDate".

So ... you have a set of fetch routines which, if all passed the same @AsOfDate, will return records from archive tables representing the data that was in place "as of" that date. You can do this for every table in the application and you can build queries of any complexity from these routines. So in other words, you can build your own user functions or stored procedures that accept an @AsOfDate, construct your queries using these routines instead of table names, and such routines will return the data that was stored in your application as of the @AsOfDate.

Or to put it another way, both your RAP application and any procedures you write using these functions can retrieve data "as of" any given date. This is how the RAP sample application lets you see data "as of" any date you specify at login time.

Writing queries using the fetch routines

The routines that RAP generates are sufficient to provide you application with all the basic CRUD information needed to drive the app. However if you need to write queries that perform custom searches, or if your app will generate reports, then you will have to write some amount of custom code to support these features.

When creating a stored procedure to drive a report, typically you'd write a query that does some number of joins on tables, like this one:

create procedure Report_PersonsAndNames
as
select
P.GovtIdNumber,
N.First,
N.Middle,
N.Last
from TBcrmPerson P
join TBcrmPersonName N on
N.PersonId = P.PersonId

This procedure joins two tables containing data on persons and their names (in

the RAP example, persons may have multiple names, e.g. maiden name, current

name, etc.). The procedure above would produce a report capable of displaying only

current data in your app's primary tables.

However in a RAP application you'd more likely code this procedure this way

using the supplied fetch routines, rather than direct references to primary table names:

create procedure Report_PersonsAndNames
    @AsOfDate datetime
as
select
P.GovtIdNumber,
N.First,
N.Middle,
N.Last
from dbo.UFTBcrmPerson#PK(@AsOfDate, null) P
join dbo.UFTBcrmPersonName#PK(@AsOfDate, null) N on
N.PersonId = P.PersonId

Note that we've added a parameter to the stored procedure, namely @AsOfDate.

We've made the following substitutions:

• in place of a direct reference to TBcrmPerson (the primary table

  containing Person data), we have:

  • dbo.UFTBcrmPerson#PK(@AsOfDate, null)
• in place of a direct reference to TBcrmPersonName (the primary table

  containing PersonName data), we have

  • dbo.UFTBcrmPersonName#PK(@AsOfDate, null)

Note also that the @AsOfDate passed to the stored procedure is passed on to each

fetch routine. Each fetch routine is also called with a null second argument,

indicating that all records from the table should be returned (we could filter

the output by passing a second procedure parameter to one or more of the fetch

routines, but let's keep it simple). The effect of

this procedure is to produce a complete report "as of" the passed-in

date. If

you pass an @AsOfDate of null, then the fetch routines retrieve current data

from their corresponding primary tables. If

you pass an actual date, then the fetch routines retrieve data from their

corresponding archive tables as of the @AsOfDate.

What this means is that you can produce a report of any

desired complexity whose data is "as of" any specified date. So

in addition to the fact that your RAP application can retrieve application data

"as of" any given date, you can also construct reports and other custom

data-driven objects that can perform in the same way that the core application

does, by simply having the application pass your objects the desired "as of"

date.

The RAP Database Generator: database fetch procedures

The Database Generator also generates stored procedures whose behaviors are

identical to the user functions above:

• SPTBadmUser##PK
• SPTBadmUser##UK_LoginName
• SPTBadmUser#PK
• SPTBadmUser#UK_LoginName

These are the routines that your RAP application will make available for the

application's

use in querying the database. Note that you don't have to write a line of this

by hand. And in fact if you don't need to write any specialized queries, you

don't even have to know how these routines work.

Summary

We are basically done with the database. Without having written any code at all except for the original table definitions, our database now contains:

• the tables we defined, with the system requiring only a few restrictions on our behaviors:
  • standardized primary keys
  • standardized audit/status fields
  • standard table naming conventions
• auto-generated objects, including:
  • archive tables, to hold copies of every record ever entered into or deleted from our application,
  • one insertion routine per primary table (that modifies both the primary and archive tables)
  • one update routine per primary table (ditto)
  • one deletion routine per primary table (ditto)
  • a set of fetch functions and stored procedures for each supplied:
    • primary key
    • foreign key
    • unique index / constraint

This is a rather enormous amount of payback in return for the relatively simple restrictions we were required to place upon ourselves (see the first bullet item above). But the payback doesn't stop here, because in our next article we will see how RAP automatically generates essentially our entire application's data layer as well.

The Series

This is part of a series that examines the RAP application development system and a philosophy that believes in more standardization for both our database development and application organization.

1. Part 1 - An Introduction
2. Part 2- Database Design
3. Part 3 - Auditing
4. Part 4 - Insert/Update/Delete Routines
5. Part 5 - Fetch Routines