Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Writing Nearly Codeless Apps: Part 5

By David Ziffer,

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

Total article views: 5307 | Views in the last 30 days: 1
 
Related Articles
FORUM

Auto generate insert Procedure

Auto generate insert Procedure

ARTICLE

The DBA Routine

What's the routine of a normal DBA job? Too many people work alone and don't get feedback or comment...

SCRIPT

Generating permutations - procedure

This procedure prepares a query for generating permutations of n-numbers set and executes it.

SCRIPT

Insert Generator - Stored Procedure

Stored procedure to generate an insert script for a table.

FORUM

View All procedures accessed by an application

view all stored procedures being accessed by an application at run time

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones