DataFaucet ORM

Galleon Forums

  1. Overview
  2. Search
  3. Foreign Keys
  4. Database Platforms
  5. CFQUERYPARAM
  6. Performance
  7. Install Scripts

Overview

Note: The project of converting the Galleon Forums application to use this ORM was undertaken before the DataFaucet project was started. At the time what is now the DataFaucet ORM was a part of the onTap framework, leading to the many references to the onTap framework in this article.


The onTap framework contains a SQL Abstraction library which has no comparable analog in the other frameworks I've ported. So being one of a kind, there's nothing to compare it to other than the original Galleon code. Given that I was porting Galleon to several frameworks however, I wanted to take this opportunity to show how the application of the SQL Abstraction library is able to make the original Galleon business objects both smaller and more flexible.

In total using the sql abstraction library removed 586 lines of code from the business objects and reduced the size of the affected code by 51%. The affected code in each individual method was reduced by an average of 63%. So long story short, it cut the query coding in half. It's important to note that this is not a representation of the entire application or even the complete size of the business objects, it is only a measurement of the ported / modified querying methods.

Also this is an average, so not all the modified methods turned out quite the same way. In fact I increased the size of the deleteThread method by two lines. But while there were maybe as many as a half dozen methods that got bigger or stayed the same size, the majority of changes were 75-80% reductions.

Roughly 60 methods were modified and in almost all of those methods some aspect of decoupling occurred. In addition new features or functionality were added to 7 of the updated methods. Less than a half-dozen new methods were added to provide this new functionality. In fact the sql library added functionality to the Galleon search feature while removing an argument from each of the search methods.

For detailed information about the individual business objects, view the spreadsheet.

Search

In truth using the sql library didn't add a lot of features to the Galleon forums. Probably the most signifant addition in terms of features is in the change to the search feature.

The original Galleon forums search form included a text entry for the user to enter their search terms and a select-box for the user to indicate the type of search they want to perform either "phrase" (verbatim), "all" (records containing each word) or "any" (records containing any one of the words). The business objects then contained four "search" methods for messages, threads, forums and conferences, each of which accepted two arguments (search terms and search type) and re-implemented the three search types.

By applying the sql library's andOrFilter feature, I've been able to remove the 2nd argument from each of these four functions and reduce each of them in size by no less than 80%. The end result is that the search feature is much more robust, allowing the user to perform searches that would not have been possible before. For example, a user can now search for one of two phrases instead of one of two words, such as "confirmation bias or morton's demon". This would not have been possible with the original search features, which would have allowed a user to search for either phrase "confirmation bias" or "motron's demon" by itself but not for both at the same time. Attempts to use "all" or "any" in the original would have produced a search for "confirmation and bias and morton's and demon" or for "confirmation or bias or morton's or demon".

As a final note, the andOrFilters are also i18n so assuming that the application is configured to display content in German, then German users can perform the same searches with the keywords "und" and "oder", i.e. "confirmation bias oder morton's demon".

Foreign Keys

Using the SQL Abstraction layer for Galleon is actually slightly more work than usual for me because I don't normally support MS Access databases and make a point of using foreign keys which aren't (as far as I know) available with Access. Because Galleon works on Access I had to manually include the column names in the join methods, which I usually "autowire" (similarly to the autowiring in an IOC framework) using the database metadata for determining which columns are foreign and primary keys.

Database Platforms

In the Galleon configuration file (/CFCs/settings.ini.cfm) there is a setting for "dbtype" which contains a string value of "sqlserver", "oracle", "access", etc. to indicate which of the four supported databases are to be used. Within the business objects, these database strings are then hard-coded into queries, which means porting it to support another database (postgreSQL for example) would be somewhat tedious (because the hard-coded strings make it tightly coupled to the database platform).

Tight coupling with regard to the database platform is actually common if not the norm with regard to web applications and applications are rarely moved from one database to another, so in a lot of cases tight coupling with the database platform isn't problematic, it just "is". This is particularly true for application service providers (ASP) who never license their application for external use or for internal applications with governmental clients.

Many of us however are working on applications which are much more like Ray's Galleon forums, meaning that they're intended for general consumption. And if you're planning to sell these applications, then the fewer restrictions on your codebase, the broader your customer base. So for my part, I prefer to design commercial applications to be as flexible as possible and in part that means being agnostic about the customer's choice of database platform. I'd much rather NOT lose a customer simply because their company chose to standardize on a different database platform

In addition to allowing you to support more database platforms, the SQL Abstraction layer also helps to reduce the size of your code. In the Galleon business objects I removed several switch-case statements that contained hard-coded database names. And in turn that means easier maintenance.

CFQUERYPARAM

If you're not using the onTap framework for your data access, and you're not using the CFQUERYPARAM tag in your ColdFusion code, you need to start. There are numerous articles available online regarding the importance of using cfqueryparam for both security (SQL-injection attacks) and performance / scalability. I won't repeat any of that here, I'll just say that if you're not already familiar with the benefits of cfqueryparam, then get familiar.

That being said, the SQL Abstraction library in the onTap framework actually automates these tags for you, so you'll never have to actually write one. This is a good thing because one of the things the cfqueryparam tag requires is that you specify the data type of the variable going into the query. It might sound innoccuous, but the reality is that this increases coupling in the application.

As an example, lets say that you had a table that used a numeric (integer) column for its primary key. Everyting works great and you're moving along just fine until a few years later when you decide it would be much more valuable to use a UUID or GUID column instead of an integer column for the primary key of that table. If you've been using cfqueryparam like you should, then there are likely hundreds of cfqueryparam tags in your application that would need to be updated to change the column to varchar because each of them contains cfsqltype="cf_sql_integer" and would now need to be cfsqltype="cf_sql_varchar". The onTap framework eliminates the need to specify the parameter type by automating them (much like the autowiring in an IoC framework), which means those hundreds of references would be reduced to zero or close to zero.

Even if you never did actually change any of your columns there are two other things to consider. First, cfsqltype declarations sometimes change from one database platform to another. For example, "long text" or "character large object" columns are cfsqltype="text" for MS SQL Server or cfsqltype="clob" for oracle. This means that automating the cfsqltype is also an important part of creating database agnosticity so that you can sell your application to clients using different databases (and for that matter you might even modify the abstraction engine to simulate data types not supported by your database, for example MySQL doesn't currently have a "memo" data type, so the engine converts them to the largest available varchar). Secondly it just reduces the amount of code you have to write and that's a plus all by itself.

So as I converted the business objects I also counted the number of cfqueryparam tags I removed as an additional measure of the amount of code and maintenance saved using the sql library. It turns out I removed a total of 181 cfqueryparam tags. Since there were no queries I was unable to replicate using the sql library, there are no cfqueryparam tags remaining after conversion.

Performance

Insert queries individually add about 35 milliseconds per query, with the number of columns not having much affect on the overhead. Selection queries require a similar amount of extra time. For individual queries this is negligible, since it adds less than a tenth of a second (or about the amount of time it takes to blink). It only becomes a problem when a given page attempts to execute a very large number of queries, which isn't the best practice.

Within the Galleon forums application those methods most negatively impacted by the additional overhead of the sql library are low volume methods, such as conference deletion or adding messages. Both of these methods as examples execute more queries than are really necessary and perform other related tasks (file deletion and email sending) which might be better performed by scheduled tasks at some other time.

Since users shouldn't notice the added overhead of using the sql library, purely mechanical performance should not be a barrier to adoption.

Install Scripts

One other thing I'll mention here briefly is that if I had written something like Galleon myself using the framework, there wouldn't be four separate install scripts for different databases. Actually there would just be one xml file containing all the schema information for the database and the SQL library would install the appropriate tables via a coldfusion template. Granted that some databases aren't configured to allow DDL to be executed via ColdFusion, in which case the SQL library could alternatively generate the script needed for a given database at run time, which eliminates the hassle of maintaining separate install scripts.