Following on from parts 1 and 2 of my Data Access Layers in Classic ASP series, in this part I will look at the implementation details of the DataAccess class and what problems I had to solve to get the functionality and behavior I required.
Opinion: why is the Macbook Air a full 300 pounds (600 dollars) more expensive than in the US?
Implementing a Dynamic SQL module
In the last part I took a basic overview of the dynamic SQL module I had built called DataAccess. Although I looked at some of the details regarding implementation and the class interface I didn't really touch on how I implemented the class itself and the problems and considerations I had to face. I want to use this article to touch on some specific implementation details. Get comfortable, this is going to be a long one!
'dot' delimited path syntax
In my mind one of the major wins of the DataAccess class interface was the use of the 'dot' delimited syntax to specify what tables and fields to retrieve or update. This was actually quite tricky to achieve and relied on several factors.
As a recap, the dot notation allowed me to be able to reference a field or record set using a starting id and then a dot separated list of tables and fields. For example, if I wanted to access the category name of a given product I would use:
find(product_id, "products.categories.name")
Where "find" is the function, "product_id" is the id of the product I'm interested in, "product.category" are a join of the product and category tables (assuming a one to many relationship) and "name" is the field I want to return from the category table. In SQL this would have looked like:
SELECT * FROM products LEFT JOIN categories ON products.category_id = category.id WHERE products.id = product_id
So quite a difference then. But how does the class know how to decipher the 'dot' delimited path into SQL? In this case I have to make some assumptions about the table and field names:
I always assume that the table names will be plural
I assume the foreign key name will be a singular version of the target table name with "_id" at the end so a foreign key which joins catergories will be "category_id"
Which means there should never be a table name of that format
With these rules we can start to write code which checks for the existence of the foreign key fields within the tables specified in the path. Instead of attempting to construct complex join queries my code does simple SELECT statements on each required table in a looping manor. The algorithm would look something like this:
Split the path into an array using the '.' as a delimiter
Check if the last array element is a table name (so we know whether to return a value or recordset)
for the first element in the array, get the passed in record id and create a simple SELECT to pull back our first record.
look for a foreign key in our current record pointing to the next table, if not found try and pull back a recordset from the next table using what we think should be a foreign key back to our current record.
Repeat step 4 for each element in the array until we either have to return a recordset or a value
If all went well we should end up with the value or recordset we wanted. Using this same method we can actually use this to update fields as well by replacing the last step with an update command.
Creating and Updating entire records
The challenge was quite different in deciding how to insert or update an entire table row. I wanted to pass in table name and data collection and let the class do the validation and then build and execute the required SQL. I wanted a detailed description of any validation errors and to know what the new record ID was if appropriate.
Several factors came into play here:
I wanted to potentially pass in a raw form collection object or a self made dictionary object
I needed a way of matching form field/dictionary fields to database fields
I needed to validate and return a potentially complex failure response to the calling code
Because I needed a sensible way to match form fields against database fields I needed a naming conversion for the form fields. I decided to use the same as the validation config file: tablename[fieldname]. This made the task of validating the form input much simpler as I only had to find a validation rule with the same name as the form field I was checking. It also meant I could specifically match form fields against the database name and leave form fields which I didn't want to process (such as action codes, etc).
It was then a safe bet that if the form field name contained the table name it was supposed to be part of the insert or update, and if there was a validation rule containing that name then it needed to be validated.
If the form collection/dictionary collection failed validation I needed a way to let the calling code know. I decided to return a two element array. The first element contained the status of the operation (Success or Failed). The second element contained the new record id on success, or a pipe separated list of which fields failed validation (and why) on failure.
Checkboxes
As anyone who has tried to write something like this before will know, there is a fly in the HTML form ointment, Checkboxes! The problem with checkboxes is that if they are not selected when the form is submitted then no value is sent to the server, not even the form variable name! Its as if the checkbox doesn't exist.
Take this scenario as a demonstration of the problem this raises:
You have a form with a checkbox to say whether as a use you want to opt-in to something or not, lets say you are editing your preferences and you are currently opted-in, the checkbox will be ticked
Now say you want to opt-out, you uncheck the checkbox and submit the form
EITHER: The form value is never sent to the server so the update function never knows to create the SQL to update that field with a null or zero, no change occurs and the user stays opted-in
OR: You've set the field as mandatory in the validation config, the form value is never sent to the server and the validation fails because the validator cannot find a value for the field. The user can only submit the form when the checkbox is ticked
This really is a bit of a problem and can cause many issues. The way I eventually worked around it was to have checkboxes validate to number and have number validations override mandatory field validations. The default behavior of my number validation code is to return a 0 if no value is given. I build all my checkboxes to have a numeric value of 1 (because I typically use them as switches) so that way either the form sends the 1 if the checkbox is ticked, or if its not ticked the validation code forces the value to 0 and the database is updated accordingly.
This is VERY hacky but at the moment its the most convenient solution and works in 90%+ of checkbox cases for my purposes. The other 10% of the time I can usually work around the issue without having to disturb the DataAccess class code.
Conclusion
We that pretty much covers all the detail I wanted to discuss. Of course the actual class code is much larger and complex than I've alluded to here but I wanted to talk about the main conceptual and practical issues in creating something like a dynamic SQL module in classic ASP.
My solution is NOT perfect but it does work web for the most common cases of use which is what I required of it. So far, its working well in five separate projects and allowed me to build a lot of code much faster than I would otherwise have been able to. It has met all of my design goals I don't have any performance issues with it at all, even on high load sites.
As well as these benefits, it has helped me reduce code complexity in other areas of my applications and dramatically increase code readability. I am willing to provide code snippets and further advice if required so please ask me!
