Given the number of applications available for developing database front ends, it might seem strange that the most popular method of doing it is using HTML and an application server. It's become apparent, however, that the Web offers something in terms of simplicity and familiarity that users value more than the added functionality that dedicated packages can bring.
No doubt their bosses also value the cost savings, and it can be convenient for developers not to have to worry about installing dedicated packages or custom-built front ends.
The problem, though, with using ColdFusion and HTML for building front ends is similar to the problem of using Visual Basic or raw Java to build dynamic Web sites - you're using a language primarily designed for something else to do a job that would be better served by a dedicated toolset. When building front ends, you can easily find yourself duplicating code - or at best, writing similar functions over and over again, and having to test and debug those functions each time.
This is where dedicated packages have advantages over Web-based solutions. First, they don't require you to write SQL insert, or update or delete code; second, perhaps more important, they reuse the same forms for adding, editing, and searching data. Packages like Access and FileMaker let you design a form once, then use it for each of these separate functions without any extra effort.
Even if you stick to CFUPDATE and CFINSERT, you still end up duplicating code to create the forms for different functions. Although you can use Studio/Dreamweaver "wizards" to create the code in the first place, it still needs testing, debugging, and support in the long term.
To solve this problem I started developing Article Manager (AM) - a set of custom tags to simplify the process of building front ends, and thus avoid the need for duplicating code. It's so named because it started life as a content management tool for online magazines, but it's evolved into a general-purpose front-end tool without any specific implementations in mind.
Although it's still a set of ColdFusion custom tags, the long-term goal of the project is to create a pure tag-based markup language for database front ends. In its latest version, v.3, AM is nearly that, and in its next version it will be. It's intended solely for the dull administration side of Web sites - the side that dare I say, is being a little neglected with the excitement of Flash MX. Behind every dazzling Web site there needs to be a solid, easy-to-use management system, and AM is designed to build it.
Building Article Manager Applications
AM applications are built from two main components - list pages (see Figure 1) and record detail pages (see Figure 2). We'll call detail pages record pages, because as we'll see they do more than simply display record details.
Creating a list page couldn't be much simpler. You need to specify, at minimum, a table and the fields to show. The only code you'd need to make a list page like the one shown is:
<cf_recordlist table="ARTICLES" fields="HEADLINE,PUBDATE,AUTHOR">
By default, 50 records at a time are shown, with next and previous links added when needed. You can override this parameter globally or on a per-list basis. Dates are automatically detected and formatted using LSDateFormat() and a mask that must be specified in application.dateformat even if you're using the default locale.
The default list page looks like the one shown in Figure 1. Specifying an attribute of TYPE="GRID" uses the CFGRID tag to avoid the need for a separate record page.
In the AM example application, the Categories table uses this feature. Usually, though, you'll want to have separate record pages, because as well as allowing you to edit and add records, they also allow for searching,which will be covered later.
Using TYPE="Query" returns a query for a customized list. Because this is such a common requirement, the "more, next" part of <cf_recordlist> is available in its own custom tag.
"TABLE" and "FIELDS" are the only required attributes for a record list, but usually you'll want to specify others. "SO" can be used to specify a sort order. "SHOW_ID" lists the record ids in the list; "VIEW" specifies a view to query rather than a table, although TABLE must be specified as well for advanced searches and deletions. An example is shown in Listing 1.
If your database doesn't support views, you can specify "joined" tables using SQL (e.g., ARTICLES LEFT JOIN CATEGORIES ON ARTICLES.CATEGORIES_ID = CATEGORIES. CATEGORIES_ID).
"CRITERIA" specifies search criteria; "SELECT" and "GROUP" can also be used to pass custom SQL. It can get quite complex, and begs the question why not just have a Query attribute? This is because AM makes extensive use of joins for its searches. Search results and criteria are stored within the database, and the SQL can become quite complex.
Record Pages
A basic record page looks like Figure 2, with each field listed one after the other in a two-column table. The tag that actually displays the fields is <CF_RECORDFIELD>, which works similarly to <CFINPUT> and <CFSELECT>, combining and extending the two tags.
The type of field is specified in the "TYPE" attribute, and the various options are listed in Table 1. The ones that you may not be expecting are "Combo" - a text field with a list underneath it that allows you to select an option or type in a new value, and "Swapbox" - two multiple select lists that allow you to move values between them.
A basic record field tag requires only the field name:
<CF_RECORDFIELD FIELD="HEADLINE">
Usually you'll want to add a "LABEL", which, by default, is the field name with underscores changed to spaces. Any attributes available to CFINPUT or CFSELECT are also available, and the default "message" is more helpful. For text it says "please enter a value for field xxx"; for dates and numbers, the message is more specific, indicating the format required.
The QUERY attribute is also available for radio and checkbox fields, together with DISPLAY and VALUE. A REQUIRED="No" attribute applied to a list will insert a blank option when a query is specified.
Using the CONTROLS_FIELD and RELATED_FIELD attributes (see Figure 3), you can link two select boxes so that the "slave" shows only values from a query where a RELATED_KEY field matches the selected value in the "master" list (this will make better sense if you view the example application).
There are also field types that allow easy storage of images and files. One difference between AM and some systems is that images and files aren't stored within the database; they're saved to the file system and the filename is recorded in the database. The directory to use is configured in the application scope.
AM has also been designed to allow easy adding of your own field types, perhaps using a Java applet like Webedit or a DHTML calendar control. Simply customize a dsp_fieldtype.cfm file and an act_parse_fieldtype.cfm file using the templates and "fieldtype" can be used as a value for the type attribute in CF_RECORDFIELD.
One of AM's strengths is the JOIN_TABLE attribute that you can use with checkbox and swapbox fields to store the values in a separate table - a "join" table - for building many-to-many relationships. The join table should contain just two fields - the record id and the id of the related value. In the AM example application, these are ARTICLES_ID and CATEGORIES_ID. A full discussion of this technique for creating many-to-many joins is beyond the scope of this article, but to my mind they're the litmus test of good database design. If you're unfamiliar with the concept, getting to know it would be time well spent.
Two other useful attributes of CF_RECORDFIELD are DEFAULT - the value to use a new record (a value of "today" for a date field will enter the current date), and SAMEASLAST (specify true or false) - which will override the default by remembering the value from the last record edited and using that. It's useful when multiple records have to be entered at the same time.
Like dedicated packages, AM requires the fields to be specified only once. A record page has three "modes" - "new", "edit", and "find". "New" and "edit" show a blank form and the data for a specified record, respectively. AM takes care of all inserts and updates - there's no need for any SQL. This is all done by the <CF_RECORD> tag that goes above the <CF_RECORDFIELD> tags. The full code for the record page shown is in Listing 2. As shown in this listing, you have to add <cfform> tags and the <cf_am_submit> tag for the submit button, but this will change with the next release, leaving AM as almost a pure mark-up language.
When in "find" mode, the two-column table becomes three columns, and "qualifiers" are added for each type of field. All list boxes turn into multiple selects, and multiple fields have a qualifier of "all" or "any". It's a handy tool for editors and admin staff to quickly find records even if it won't replace hand-cranked SQL for report writing.
"Find" mode (see Figure 4) works differently than the other actions ("add", "edit") in that it posts the data to the list page for the table in question, and CF_RECORDLIST performs the find. Once in "find" mode, selecting "list" for the table in question will show only the "found set". The criteria of the search can be saved immediately after the search, and the results at any time hence (see Figure 5).
This is important because you can "omit" records from the found set, perform another search within the found set, or add the results to the found set - performing AND or OR searches in a slightly roundabout way. These saved "found" sets can be used for exports or reports (both easily done by using CF_RECORDLIST with a type="query" that you can use as you wish). Currently you can't save multiple criteria sets - although I hope to change this soon.
To clear a found set, the user simply clicks "Show all", and the record list reverts to showing all records. This is equivalent to an MS Access "clear filter" instruction. Searches are associated with a user using the value of SESSION.CFID - not a particularly sophisticated or robust solution, but one that is easily replaced with your own chosen mechanism (e.g., a user id [table of USERS?] or perhaps an authenticated user name).
Implementing Article Manager
AM currently works with Access, SQL Server, and MySQL (an improvement on earlier versions which used sub selects and only ran on Microsoft databases). The SQL is very standard, and there's no reason Openbase or Interbase shouldn't work. I just haven't gotten around to installing or testing them.
For AM to work, there are some restrictions on the design of your database and some tables that need to be added to your solution. These are for storing the search results and criteria. In addition, all tables have to have a numeric key set to increment and named TABLE_ID. Ideally this should be the primary key, but it doesn't have to be if you already have one. Every table also has to have a field called "TEMP_ID" of at least 35 characters, which is used when records are inserted to store a UUID and then used to return the new ID. This provides a cross-product solution to the problem of returning new IDs without relying on the Max(ID) method.
The biggest obstacle to implementing the system isn't usually these details, but the uniform look the system applies to every page. Without any of the bells and whistles of dedicated packages or any of the hand-finishing that often gets applied to Web-based solutions, AM applications can seem a little bare.
The first comments people usually make when they start using AM are along the lines of "I'd make that more obvious," or "it wasn't clear what had happened." The user experience, though, isn't something I'm in any hurry to change. I've found that you can waste lifetimes making things "user-friendly" when all they really needed was a few hours' familiarity.
Any process-specific additions take away from the simplicity that is key to successful programming. If I can keep a system strictly to standard AM lists and record pages, I know that it's going to be easy to support and maintain in the long term. Every tweak and modification to satisfy a first-look knee-jerk reaction is going to add manifold to the complexity of the project.
A True Markup Language?
A goal I set myself when I'm building content-management systems is that any bit of information on a Web site should be changeable with a single edit using a consistent interface. That's what I'm aiming for with Article Manager. A single change to a field tag should be enough to take care of all inserts, updates, and searches. The dream's still a long way off, but using AM it's certainly closer than hand cranking SQL and HTML forms.
In its next version, AM will become a genuine markup language, using XML in place of custom tags. While this is primarily to allow the introduction of new features (such as sub forms for related data), it will also pave the way for a Flash MX interpreter or even dedicated interpreters using systems like Delphi.
With the arrival of Flash MX and the growth in mobile browsers, the need to separate data and presentation has never been more apparent, and a database markup language would provide a level of abstraction that's missing from many of the early examples of Flash MX interfaces. HTML and ColdFusion have shown just how powerful simple, tag-based languages can be. It's vital that we don't lose that simplicity with the move to rich client interfaces, and with a database markup language, there's no reason why we should.
Download Now
Download Article Manager, the example application, and documentation at www.articlemanager.com
Subscribe to ColdFusion Developer's Journal Email News Alerts
Subscribe via RSS
