Generating Components From Tables and Queries

Quite often I find it useful to have a ColdFusion object which reflects the data in the row of a query or database table.

I have a written a generator to accomplish this. Please note that this is CF 9 specific. But wait, you say, CF 9 has ORM built in why not just use that. There are two limitations of Hibernate as implemented in CF 9 which rule it out for my use. 1)It is limited to a single datasource and 2) it will not work on an arbitrary query.

We have an application for which different clients have different datasources. The database schema is the same for each client but the database instance, and hence datasource is different.

Also, we often move data returned by a query around the application and to the client side to be used by jQuery. It is much easier to use an object for this than using queries directly.

The Generator

The generator retrieves information about the table or query, and creates a cfc reflecting that structure and writes it to the appropriate location in the file system. The generator is designed to be run once and need not be run again unless the schema changes. If it is rerun it respects changes made to the cfc by the developer and does not overwrite them.

I will discuss the generator code a bit later but lets look at a typical cfc that is generated first. After all if you don't find the generated cfc useful why would you care about how it is generated?

Sample cfc of the artists table in cfartgallery

Notice the metadata attached to the component: datasource, primary key, pkIsIdentity. These were collected by the generator when it was run and are used when interacting with the database. The datasource is the datasource used when the component was generated. It is used by default but you may set a new datasource at runtime if necessary.

Also notice that accessors is set to true. When CF sees this it generates getters and setters automatically. If the developer overrides one of the accessors in the "usercode" section the code "getter=false or setter=false" is added to the appropriate property when the generator is rerun.

The first thing you may notice is that the code contains no useful methods. It is basically a list of properties reflecting the structure of the table. Notice that these properties incorporate the database type from the database. The component extends com.vawter.utilities.baseTableClass which contains all the methods and does the heavy lifting.

Methods inherited from the superclass:

1. setDatsource(string ds)
2. getDatasource()
3. add()
4. update()
5. delete()
6. read()
7. save()
8. fillFromQuery(query q)
9. fillObjectFromQueryRow(query q,numeric row)
10. fillObjectFromStruct(struct inStruct)
11. getJson()
12. getJsonFromArray(array aObj)

The purpose of the first two methods is to change the datasource. The next five should be obvious. They are just the standard CRUD operations.

The next three are methods for populating the object. fillFromQuery is a little different. It creates an array of objects, one for each row of the supplied query. Each instance is of the same type as the calling object.

getJson returns a string of the serialized data in the object and getJsonFromArray returns a string of the serialized data from an array of objects (usually from fillFromQuery).

All the methods except 1,2,11,12 return a struct with elements result and errormessage. Result is true or false and if false errormessage is the reason. fillFromQuery returns an additional element "records" which is an array of objects populated from the query.

To use the read method you first populate the object with the pk you want to fetch and then call read. The method queries the database for that record and updates the object. Values other than the pk which were in the original object are ignored.

The CRUD methods are only useful for tables and views. It makes no sense to "add" to the database if the object reflects the data from an arbitrary query. Also the table must have a primary key or the CRUD operations will fail.

Sample usage of the generated code

The Generator Code

The generator code is straightforward. After collecting necessary file locations and the desired component path, it collects the metadata for the table or query and information about the defaults, generates the code and writes it to the file system.

Collecting metadata for tables and views

The tag provides information about the table structure and default values.

Unfortunately cfscript does not have an equivalent for cfdbinfo. To use it and retain script notation we use an include statement: include "incdbinfo.cfm". incdbinfo.cfm is very simple:

If one had an action other than "columns" one would not have a table name, hence the if statement.

Collecting metadata for a supplied query

For a query, dbinfo is not available. We can, however collect information on the columns and data types by executing the query and then using the metadata.

For queries qSQL is the developer supplied sql. Since this generator should not live in a production environment we are not overly concerned about sql injection.

Notice the "where 1 = 0" statement. We need no records in the result set to get the metadata so we insure that none are returned.

The metadata returned is an array of structs, one for each column. The struct contains the column name, the column data type, and whether the data is case sensitive. The column names are the same case as the database. For the examples in this post they are all upper case but in the general case they are not. We preserve the case when generating code so our properties reflect the underlying database case. This makes reading the code much easier and means that when we pass the data to javascript we are certain of the case of the variable names. Of course within CF the case of the property is not important.

Sample Usage of Generator

local.res will return a struct with 3 elements a)result(true|false), b)errormessage c) the text of the cfc generated The text of the cfc generated is just for informational purposes. The real code is written to the file system You will probably want to import the generated code into your version control system.

Summary

It is easy to create objects representing tables or queries. The components generated are simple with all the methods containe in a common superclass.

You may download the code for the generator here.

In a future post I will discuss moving objects to and from the client using jQuery.

Comments and suggestions welcomed,