|By Joshua Oster-Morris||
|March 11, 2002 12:00 AM EST||
Most ColdFusion developers have noticed that there are many similarities between each application they write.
Most database tables require you to write a data entry screen, a form action page, and at least one SQL query. I can't tell you how many times I've typed in alternating <TR> and <TD> tags with <CFINPUT> and field descriptions between them. I've even gone so far as to print a screenshot of the data structures from my DBMS so I wouldn't miss any fields and I would get the validate and size settings correct. These redundant processes are repeated throughout the entire application development process. Wouldn't it be nice if all the tedium of building apps could be taken care of automatically and we would only have to deal with the complicated parts of our applications?
All great ideas begin with inspiration. My inspiration was Ben Forta. At the ColdFusion Southwest Mini Conference in April 2001, Mr. Forta and I discussed using SQL stored procedures throughout my applications rather than normal queries. He mused about writing applications without any <CFQUERY> tags, using <CFSTOREDPROC> instead. He then dismissed the notion somewhat because it's impractical to deal with the more complex syntax of <CFSTOREDPROC> for all the queries associated with the average CF application. I wanted to make it practical enough so I could take advantage of all the benefits of using stored procedures (speed, security, etc.). I realized that all I needed to know to automate the creation of the stored procedures was the name of each column and its data type and size. That information is available through a SQL query, isn't it?
Manufacturing the Prefab Parts
With the information available in the sysobjects, syscolumns, and systypes tables in my MS-SQL Server, I set about creating a ColdFusion application factory. I selected Java as my language of choice to create this factory (see Figure 1). I prefer the state-aware nature of local applications to the state-unaware nature of Web apps. Also I thought it would be more useful for developers who don't run their own servers and may not have the ability to use the <CFFILE ACTION="WRITE">, which would be required to make the factory work. Not to mention that it will run on both Unix and Windows machines. Java is so cool!
The parts my factory manufactures are:
-Stored procedures (see Listing 1)
-Triggers for returning the @@identity associated with INSERTs (see Listing 2)
-Custom tags that call each stored procedure (see Listing 3)
-Custom tags that call data entry screen widgets (see Listing 4)
-Custom tags that call the data entry action page widgets (see Listing 5)
-Custom tags that call Combo and List Box widgets (see Listing 6)
-Snippets for all the custom tags created above so they can be easily entered into applications (see Figure 2)
Use of this factory would certainly not be limited to the templates that I created. I've designed a programming interface for the factory so you can design your own templates. The syntax for this template-generation interface is ColdFusion-friendly using tags like <query>, <loop>, and <if> (see Figure 3).
You may download a copy of my ColdFusion Factory from www.cffactory.com/.
Building the Application
Building the application from the prefab parts was a bit disconcerting the first time I tried. I felt quite strange without all that repetition while building all the screens for the application. Using my system, I was merely calling a series of custom tags to build the screens (see Listing 7). The action pages are even easier to make, though the same process applies (see Listing 8).
You could also easily use the factory to make all the fuses associated with data entry screens and queries within the framework of a Fusebox application.
Benefits of the Code Factory
Speed. Accuracy. I first developed this process and software in response to a request from a customer to write a mission-critical piece of software. When I asked about the time frame, the response was, "I need it in five weeks. Can you do it?"
Not wanting to turn away the business, I gave him the thumbs-up and quickly took my leave to start investigating the requirements of the new system. Once I had a general idea of the required data to store, I built the data structures. This is when I realized I was in trouble. Printing out the entity-relationship diagram showed me that my tables fit snuggly on six pages. I would still be doing busy work on the application in six weeks rather than installing it. I needed to do something fast.
I remembered my conversation with Mr. Forta about stored procedures and that I planned on writing a little something to deal with that problem. I realized it could be extended to deal with input modules as well as stored procedures. I spent a couple of sleepless days and nights working on my factory. When it was finished, I wasn't sure yet if it would help. I hit the "Go" button and waited breathlessly to see if it would work. It did. About two seconds later I was ahead of my prior level of project completion by about five weeks. That left me time to deal with the more complicated work of writing the business logic, etc.
Flexibility. Then disaster struck. The customer had neglected to inform me of some rather important information that would change the data structures dramatically. After I my heart attack, I put these changes into the context of my new tool and realized that this might be only a two-second fix. Once again I held my breath and watched as more miracles happened. All those templates that would need to be rewritten, double-checked, and repaired, worked the first time. It was mind-boggling.
How to Make Your Own Factory
What's absolutely required to make this work is:
1. A completed database design with the structures built in your database server; this should be completed before you start any coding, even if you use more conventional development strategies.
2. A database server that offers you access to sysobjects and syscolumn style data; MS-SQL Server, Oracle, and Sybase are all good choices.
The key is to understand the sys* tables in your database server. I use MS-SQL Server; see Listing 9 for information about that product.
As you may have noticed, I've limited the sysobjects table to return only "user" tables, not the ColdFusion client-variable tables. The information afforded by these two simple queries allows you to write code to automate the creation of all the types of files I discussed.
The details of how I wrote my ColdFusion Factory are beyond the scope of this article, but all the information you need to follow in my footsteps is in your database right now.
- Where Are RIA Technologies Headed in 2008?
- The Next Programming Models, RIAs and Composite Applications
- AJAX World RIA Conference & Expo Kicks Off in New York City
- Constructing an Application with Flash Forms from the Ground Up
- Building a Zip Code Proximity Search with ColdFusion
- Personal Branding Checklist
- CFEclipse: The Developer's IDE, Eclipse For ColdFusion
- Has the Technology Bounceback Begun?
- Adobe Flex 2: Advanced DataGrid
- i-Technology Viewpoint: We Need Not More Frameworks, But Better Programmers
- Web Services Using ColdFusion and Apache CXF
- Passing Parameters to Flex That Works