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

Writing Nearly Codeless Apps: Part 4

By David Ziffer,

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.

  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: 6970 | Views in the last 30 days: 1
 
Related Articles
FORUM

delete records

delete records

FORUM

Insert / Deletes

Insert / Deletes

SCRIPT

Deleting Duplicate Records

In Datawarehousing, a normal scenario is eleminating duplicate records/rows or deleting duplicate re...

FORUM

deleting huge records From Table

deleting huge records From Table

FORUM

Delete parent record

without deleting child record

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