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.