Managing database queries that are used across entire website

Question

As my application has grown, I've noticed that I am reusing a lot of database queries across multiple webpages.

At the moment I have done it using a .CFM file which has many <cfstoredproc> tags that gets included on every page that needs database data. All I am doing is wrapping these stored procedure executions in a <cfif> tag which tests what the name of the calling page is and then executes the appropriate <cfstoredproc> block of code.

I am no expert in anything, but this doesn't feel right to me. I just don't know how to manage all my database queries correctly so that they can be shared across any CFM page in the entire website. For example, one page might need the "GetUsers" stored procedure and another page might need "GetOrders".

I'm just about to embark on creating a CFC which holds every separate <cfstoredproc> or <cfquery> in its own method/function. E.g.:

<cfcomponent name="DBQueries" hint="Everything for DB retrieval">
 <cffunction name="GetUsers" returntype="query">
   <cfstoredproc procedure="GetUsers">
   <cfprocresult name="rsUsers">
   </cfstoredproc>
   <cfreturn rsUsers>
 </cffunction>
.....
 <cffunction name="DBQuery100">
   <cfstoredproc procedure="GetSomething" returntype="query">
   <cfprocresult name="rsSomething">
   </cfstoredproc>
   <cfreturn rsSomething>
 </cffunction>
</cfcomponent>

Then on a main .CFM page I will invoke the component and method required to return the data. Is this a good way to achieve DB query management?

Answer 1

The fact that it's database related is not as relevant as the fact that you have code repetition. You are on the right track in your effort to make the code more re-useable.

If you put your queries into a cfc, you might consider taking this one step further. Instead of invoking it all the time, use the onApplicationStart method of your Application.cfc to create an application variable that's available to all users on all pages.

Another approach is to put all these database tags into a .cfm file and to put a cfinclude in the onRequestStart method of your Application.cfc.

Both methods work. And, as is almost always the case when you compare two things, each has advantages over the other.

Answer 2

Consider the following two db tables

User

UserID PrimaryKey firstname lastname

Security

SecurityID PrimaryKey UserID ForeignKey Permission

All database tables have Create, Read, Update, Delete operations (CRUD)

CRUD operations can exist in several places

1. Inside of <cfquery> tags
2. Inside of Stored procedures
3. Others

The thing is all the CRUD operations belong together in their own way. Consider making a User object (user.cfc).

<cfcomponent>
   <cffunction name="create"></cffunction>
   <cffunction name="read"></cffunction>
   <cffunction name="update"></cffunction>
   <cffunction name="delete"></cffunction>
 </cfcomponent> 

Security is a part of user management, so is the object a one to one match to the db table? In some environments like ORM the answer is yes, in others not.

If you consider security to be a part of user managment, your user.cfc might look like this

<cfcomponent>
   <cffunction name="create"></cffunction>
   <cffunction name="read" hint="Read will also read security info"></cffunction>
   <cffunction name="update" hint="Perhaps this can update security too"></cffunction>
   <cffunction name="delete" hint="Delete will also delete security info"></cffunction>

   <cffunction name="create_security"></cffunction>
   <cffunction name="read_secrity" hint="This may not even be needed"></cffunction>
   <cffunction name="update_security"></cffunction>       
   <cffunction name="delete_security" hint="This may not even be needed"></cffunction>
</cfcomponent> 

At the end of the day you may find that you need far fewer objects (*.cfcs) than tables.

OK, now you have you user.cfc what do you do with it? It can be attached to you the rest of your app in various different ways

• application.User = new user();
• session.User = new user();
• request.User = new user();

Each one of these is very from the next. Before we go down the road of which is appropriate, we have to consider member data, and how long we want it around.

<cfcomponent>
   <cfset this.userid = ""><!--- This always points to the user I want to interact with --->

   <cffunction name="create"></cffunction>
   <cffunction name="read"></cffunction>
   <cffunction name="update"></cffunction>
   <cffunction name="delete"></cffunction>
 </cfcomponent> 

It is likely that your CRUD operations are going to interact with the same UserID for all their operations. You may find that after you update a record, you will often read it. Rather than always stating which UserID you are interacting with, you may just want to set it up once, and have all the functions just use the same one.

OK, now let's get back over to where you will be using them

application.User

Only one User object will exist in the the whole system. It will be created when the request comes in onthe site. This object will be shared for every request. If you attach your user object here, that suggests that all requests will be looking at the same user.

session.User One User object will exist for a given end-user in the outside world. It will separated from all other end-users. This suggests that each end user will be looking at their own user AND that even as they click around the site, they will still be looking at the same user

request.User One User object will exist per request. It will only exist for a particular request, and then be discarded. This suggests that looking at at particular User is meaningful on this request, but the next may be quite different, or maybe not even about users.

~~~~~~~~~~~~~~~

At the end of the day, you will need to decide how to bundle your DB interactions, and how long you will keep those bundled action together

Answer 3

I would have a model per table.

in there you have every query that ever does anything to that table

Lets say the Users table

Users.cfc

would have all the methods which return queries

getUsers - return alll users

getUserById - could be a paramater on the first function also.

Then when you need to work out where something in orders is being updated there is only one place to look.

I get the results like this

<cfset users = new model.Users().getUsers() />

or I use script

users = new model.Users().getUsers();

And if your really brave, try doing all the queries in script also.

One last thing to consider, if the data isn't changing, cache the query.

Things like OrderType or similar, you will get a lot of performance benefit rather than repeating the query over and over.