The weaving loom, the piston engine, and the computer are examples of machines which, through the use of previously unimaginable repetition, relieve humans of unimaginable drudgery. If the automation of repetitive tasks is not the greatest of all human technological advances, it must certainly rank near the top.
How strange then that people who surround themselves with computers are so eager to resign themselves to enormous amounts of highly repetitive and error-prone hand coding, which could easily be automated with just a little systematic effort. One such task is the encoding of CRUD (create, read, update, and delete) routines in databases. Now there are methods for avoiding the generation of CRUD routines entirely (by using DataSets and requesting automatic generation for example), but these have the performance disadvantage of needing to be interpreted at run time, and they don't give us the latitude to implement cool stuff like automatic auditing. RAP gives us the best of both worlds: automatic code generation, code pre-compilation, and the freedom to implement any automatic database features (such as auditing) that we wish.
The RAP Database Generator: database insert / update / delete routines
Once we have created all our application's primary tables and audit tables (which we covered in previous articles), we need database code to coherently manage them. The RAP database generator creates a set of stored procedures that performs this function for each table.
Insertion Routine
Here is the Insert routine (the bold stuff is the core; everything else is provided just for context):
-- **** AUTO-GENERATED 'INSERT' STORED PROCEDURE FOR 'TBadmUser' ****
if not object_id('SPTBadmUser_Insert','P') is null drop procedure SPTBadmUser_Insert
go
create procedure SPTBadmUser_Insert
-- declare parameters for all fields except primary key & archive management
@LoginName varchar(20),
@Notes varchar(max),
@AuditUserId bigint,
@AuditDateNEW datetime
as
-- INITIALIZE
declare @ReturnValue int
select @ReturnValue = 0
declare @ErrorMessage varchar(max)
-- MAIN PROCEDURE BODY
begin try
-- declare & set the Archive-field-related 'parameters' that we didn't pass
declare
@AuditStatus char(1)
select @AuditStatus = 'I'
-- insert all fields except primary key into the 'original' table
insert into TBadmUser
(
LoginName,
Notes,
AuditDate,
AuditUserId,
AuditStatus
)
output inserted.* into TBadmUser#
values (
@LoginName,
@Notes,
@AuditDateNEW,
@AuditUserId,
@AuditStatus
)
-- get the key of the newly created record
declare @UserId bigint
select @UserId = scope_identity()
end try
begin catch
select @ErrorMessage = error_message()
select @ReturnValue = -1
end catch
-- TERMINATE
if @ReturnValue < 0
exec SPutlRaiseSystemError 'SPTBadmUser_Insert', 'Database Error', @ErrorMessage
-- Emit the record we just inserted and return the return value
select * from UFTBadmUser##PK(null, @UserId)
return @ReturnValue
As you can see, the insertion routine is very simple. We've highlighted the core of it, which is a single INSERT statement with an OUTPUT clause. This statement inserts precisely the same record into both the original (i.e. current-data) table (in this case TBadmUser) and the archive table, as illustrated in the auditing discussion in the previous article.
Insertion Routine Parameters
The procedure is mostly quite straightforward and so we will explain only the few things of interest. First are the parameters:
@LoginName varchar(20),
@Notes varchar(max),
@AuditUserId bigint,
@AuditDateNEW datetime
The LoginName parameter is the only true "payload" field in this table. Had there been ten user-specified fields in the table definition, there would be ten parameters in place of this one. Such values are of course simply inserted into the new record.
The Notes and AuditUserId fields are two of the standard fields appearing in every table. It is common for people to eventually want to be able to put notes alongside many things, and so for the purpose of illustration we made it a standard field, but RAP does not require its presence. To create a database that does not have a Notes field in every record, simply remove the Notes definition from RAP's list of standard fields.
The application must supply the user ID (AuditUserId) of the user inserting the record.
Finally, the application must supply the AuditDate (i.e. the date/time of the record creation) for the new record. This warrants some explanation. First, we use the parameter name AuditDateNEW rather than just AuditDate because in the upcoming Update and Delete routines, the caller must pass in both the record's current AuditDate (for concurrency management) and a new AuditDate. The insertion of course does not require concurrency management, but for the sake of consistency we name this parameter AuditDateNEW, which is the name of the parameter serving the same purpose in the Update and Delete routines.
The reader may question why the application should pass the audit date at all; why not just have each insertion routine internally get the current date from the database and put that value in this field? The answer has to do with auditing. The reason we store the AuditDate at all is to retrieve historical data from the archive tables. If a set of records is being written (added, updated, deleted) as a group, then we need to be able to retrieve them as a group. In order to ensure that all records written together are also retrieved together, they must all have precisely the same AuditDate. Therefore we cannot leave it to the individual insertion routines (for each table) to each supply AuditDates on the fly. Instead, the application must generate a single date to be used on absolutely every record involved in a given transaction.
Update Routine
The update routine is similar to the insertion routine except that it updates an existing record.
-- **** AUTO-GENERATED 'UPDATE' STORED PROCEDURE FOR 'TBadmUser' ****
if not object_id('SPTBadmUser_Update','P') is null drop procedure SPTBadmUser_Update
go
create procedure SPTBadmUser_Update
-- declare parameters corresponding to current table fields, except audit status
-- NOTE: AuditDate must contain the original value from the fetch of this record
@UserId bigint,
@LoginName varchar(20),
@Notes varchar(max),
@AuditDate datetime,
@AuditUserId bigint,
@AuditDateNEW datetime
as
- INITIALIZE
declare @ReturnValue int
select @ReturnValue = 0
declare @ErrorMessage varchar(max)
-- MAIN PROCEDURE BODY
begin try
-- declare variables to hold values from all fields except primary key & audit status
declare
@_LoginName varchar(20),
@_Notes varchar(max),
@_AuditDate datetime,
@_AuditUserId bigint
-- assign field values from the record into these newly declared variables
select
@_LoginName = LoginName,
@_Notes = Notes,
@_AuditDate = AuditDate,
@_AuditUserId = AuditUserId
from TBadmUser
where
UserId = @UserId
-- see if this record has been deleted or modified since being acquired
exec SPutlCheckOptimisticConcurrency @_AuditDate, @AuditDate, 'SPTBadmUser_Update'
--test parameter values to see if any disagree with record values
declare @equal tinyint
select @equal = 1 -- assume all new values equal to old values
if @LoginName is null and @_LoginName is not null
or @LoginName is not null and @_LoginName is null
or @LoginName <> @_LoginName select @equal = 0
if @Notes is null and @_Notes is not null
or @Notes is not null and @_Notes is null
or @Notes <> @_Notes select @equal = 0
if @AuditUserId is null and @_AuditUserId is not null
or @AuditUserId is not null and @_AuditUserId is null
or @AuditUserId <> @_AuditUserId select @equal = 0
-- if anything was not equal, proceed with the audit and update
if @equal = 0
begin
-- set audit management values
declare
@AuditStatus char(1)
select @AuditStatus = 'U'
-- update and archive the record
update TBadmUser set
LoginName = @LoginName,
Notes = @Notes,
AuditDate = @AuditDateNEW,
AuditUserId = @AuditUserId,
AuditStatus = @AuditStatus
output inserted.* into TBadmUser#
where
UserId = @UserId
end
end try
begin catch
select @ErrorMessage = error_message()
select @ReturnValue = -1
end catch
-- TERMINATE
if @ReturnValue < 0
exec SPutlRaiseSystemError 'SPTBadmUser_Update', 'Database Error', @ErrorMessage
-- Emit the record we just inserted and return the return value
select * from UFTBadmUser##PK(null, @UserId)
return @ReturnValue
The update routine is slightly more complex than the insertion routine because the routine has to copy values out of the record being updated so as to be able to write them into the archive table. It also performs a test to see whether the record has changed, in order to minimize the amount of archiving (if there's been no change, why write another copy of it into the archive table?). But otherwise the code is very similar. The code in bold performs the actual update.
Deletion Routine
The deletion routine is similar to the insertion routine except that of course it deletes records instead of inserting them. Since only the primary key is needed to identify the record to be deleted, the only non-administrative parameter is the primary key. Here is the code for the deletion of a record from TBadmUser:
-- **** AUTO-GENERATED 'DELETE' STORED PROCEDURE FOR 'TBadmUser' ****
if not object_id('SPTBadmUser_Delete','P') is null drop procedure SPTBadmUser_Delete
go
create procedure SPTBadmUser_Delete
-- declare parameters for the primary key and audit date and userid
@UserId bigint,
@AuditDate datetime,
@AuditUserId bigint,
@AuditDateNEW datetime
as
-- INITIALIZE
declare @ReturnValue int
select @ReturnValue = 0
declare @ErrorMessage varchar(max)
-- MAIN PROCEDURE BODY
begin try
-- declare a variable to hold most values from the record being deleted
declare
@_LoginName varchar(20),
@_Notes varchar(max),
@_AuditDate datetime
-- assign values from the record being deleted
select
@_LoginName = LoginName,
@_Notes = Notes,
@_AuditDate = AuditDate
from TBadmUser
where
UserId = @UserId
-- see if this record has been deleted or modified since being acquired
exec SPutlCheckOptimisticConcurrency @_AuditDate, @AuditDate, 'SPTBadmUser_Delete'
-- set archive status
declare
@AuditStatus char(1)
select @AuditStatus = 'D'
-- delete & archive the record
delete from TBadmUser
output
@UserId,
@_LoginName,
@_Notes,
@AuditDateNEW,
@AuditUserId,
@AuditStatus
into TBadmUser#
where
UserId = @UserId
end try
begin catch
select @ErrorMessage = error_message()
select @ReturnValue = -1
end catch
-- TERMINATE
if @ReturnValue < 0
exec SPutlRaiseSystemError 'SPTBadmUser_Delete', 'Database Error', @ErrorMessage
return @ReturnValue
The deletion routine is slightly more complex than the insertion routine because the routine has to copy values out of the record being deleted so as to be able to write them into the archive table. But otherwise the code is very similar. The code in bold performs the actual deletion.
Summary
Of the four CRUD functions we've covered three (create, update, delete). Our examples here showed how RAP applications keep their primary and archive tables in sync. As mundane as this might seem, these three routines provide some dazzling capabilities:
- They preserve every version of every record that you've ever entered into every table of your application.
- They vastly enhance performance by actually deleting records from your primary (current) table. So you never throw away deleted records, yet at the same time your app doesn't ever have to wade through them when accessing current data (as would be the case with records that are merely "marked as deleted".
- They rigorously enforce referential integrity rules. Because they actually delete records when you request that they be deleted, it is not possible (as with typical mark-as-deleted apps) to have live children pointing to deleted parents.
In our next article we'll cover the "read" routines, which are quite interesting because they give RAP applications their amazing ability to see into your data as of any past date, without the need for any special auditing tools whatsoever (your very own app is your auditing tool). Together, the CUD routines in this article and the "read" routines in the next one provide you, for free, with an auditing capability that you could most likely never afford to implement yourself.
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.
