Following on from yesterdays first part looking at Data Access Layers in Classic ASP, in this article I discuss the design decisions for my own Dynamic SQL class which attempts to emulate ORM type functionality using VBScript and Classic ASP.
Opinion: why is the Macbook Air a full 300 pounds (600 dollars) more expensive than in the US?
Dynamic SQL as a Data Access Layer
In the last part I talked about traditional Data Access Layers (DALs) and their benefits in web application development. I also talked about the benefits of using a dynamic SQL module instead of the usual ORM and stub classes in the case of classic ASP. In this part I'm going to talk about the actual implementation of the dynamic SQL module.
Classy
As anyone who has used VBScript and Classic ASP will know, VBScript's support for object oriented programming is 'tacked on' at best and superfluous at worst. However, there are some times when you want to encapsulate code into an easily transported package and VBScript classes are still a fairly good way of doing this. Because I wanted my dynamic SQL module to be re-usable across as many projects as possible I decided to make it into a self contained class called DataAccess.
DataAccess had a number of public and private members. I decided that the public members should be the class interface and the private members should enclose the mechanics of the class (the actual 'doing code') generally this is all good OOP practice. I left some configuration properties at the top of the class so I could easily set the database connection details, database name, table prefixes, location of the configuration file, etc.
Validation
Perhaps the biggest technical hurdle was deciding how to deal with data validation. I liked the principle of stub classes where validation is set specifically per table and field in a class whose members and functions mirror the table structure, but I always felt the implementation was too verbose. I wanted a resource that my class could check every time it was asked to modify a value in the database. In that way I can handle field level validation, no matter what code is using the class, where the input is coming from and where the output is going to.
In the end I plumped for a flat configuration file which was descriptive in nature but fairly concise and functional. Each line of the file contained a name of a table and field and a set of validation rules which can be applied to it. It looks something like this:
members[email]=validatesMandatory
members[password]=validatesMandatory members[email]=validatesMandatory|validatesRegEx:(regex in here)
In this example, the 'members' represents the table name and the item in square brackets represents the fields within the table, on the other side of the equals sign is a list of pipe (|) separated rules. Validation rules were defined to check for mandatory values, number values, regular expression matches and unique values.
Class interface
So now I've got my configuration file, how do I get data in and out of my class? This also took quite a bit of consideration. I wanted to achieve several main things easily:
A function to add a new record from a collection of data
A function to update a record from a collection of data
A function to update a field in a record with a new value
A function to get data from a field in a record
A function to return a good ol'fashioned recordset object from a table.
I also wanted to be about to get data from across table joins without having to write reams of SQL, so I needed a short hand syntax to specify what I wanted the class to return. After some consideration I decided to use the familiar 'dot' notation to specify what I wanted to update, modify and return. This meant I could do table joins quite painlessly, the following are all examples of class calls:
As you can see, this represents some quite complex behavior with not a line of SQL in sight. In the find example, each part of the path represents a table, apart from the last bit which is the field I want to return. If the last part isn't a field name but is instead a table name then the function returns a recordset instead. The function uses a starting record id to work its way across from the first table onwards.
The create and update functions accept a collection of data which can then be compared against the validation rules and accepted or rejected. The updateField function takes a single value, starting record id and 'dot' notated path to the value you want to update, again the values are validated before they are accepted or rejected.
Next Time
So now you can see what I was aiming to achieve, you might want to know how I achieved it. In the next part I'll discuss some implementation details of the DataAccess class and how it was able to fulfill the complex behavior outlined above.
