DataFaucet ORM

Active Records

Simply put, an active record is a quick way of managing a single record in your database. The object simply automates some of your daily maintenance work by acting as a facade to your database, insulating you from the need to worry about the fact that the data is stored in a database and handling the tedioius task of writing SQL select, insert, update and delete statements. These tasks are sometimes also referred to as Create / Read / Update / Delete (CRUD) and because they are so common in programming work and because they are usually very uniform, they are a perfect opportunity for some automation.

To create an active record object you have a couple of options. The simplest way to create an active record is to request one from the datasource object like this:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get an active record object for a product 
  ar = ds.getActiveRecord("tblProduct",5); 
  
  // display the product name 
  writeoutput(ar.getValue("productName")); 
  
  // or get it this way 
  writeoutput(ar.getProductName()); 
</cfscript>

This will find the primary key in the tblProduct table and immediately read the data from the record with the primary key value of "5". All the values in that record of the tblProduct table are then immediately available as properties of the active record object.

The Duck!

Like the datasource object and the sql objects, the active record object extends the duck, which allows you to get and set properties of the object either using the original getValue() and setValue() methods or if you prefer more traditional bean methods you can use getPropertyName() and setPropertyName().

Data Management

Okay that's cool, but how does it help me manage my data? I'm glad you asked. To handle the standard CRUD operations, the active record object has four standard methods. These methods are conveniently named "create", "read", "update" and "delete".

create([insertData])Inserts the properties of the object into the database - this is done automatically by the update method, so you shouldn't need to use create in most cases and instead rely solely on update()
read([objectid])Fetches the most up-to-date information from the database - the objectid argument is optional if the object has already ben initialized or read
update([insertData])Commits the object's properties to the database table - if this is a new object, it is automatically inserted
delete()Removes the object from the database - automatically updates or deletes any relevant records in related tables (requires a foreign key constraint - which you should be using anyway) ;) - once an object is deleted, it can not be reinserted into the database via the insert() or update() methods - to re-insert a deleted record, use undelete()
undelete()Reinserts a deleted record into the database

Example:

<cf_validate form="#myform#">
  <!--- Woohoo! the user gave us good data, lets save it to the database --->
  <cfscript>     // get the datasource 
    ds = request.DataFaucet.getDatasource(); 
    
    // save the data 
    ds.getActiveRecord("tblProduct",form.productid).update(form);
  </cfscript>
</cf_validate>

Validation

When receiving data directly from a user via a browser, it's generally advisable to create very robust and custom data validation on the form. This has a few advantages including that the error messages can (and should ALWAYS) be customized for the form to provide the user with a clear explanation of what they need to correct in the form and that although validation must be performed on the server it can also be extended to the client via JavaScript to improve the user experience.

There are other times however when a user isn't directly involved, such as when data is delivered via a webservice. The active record object includes a validate() method which provides very rudimentary validation for the object. It will compare the properties of the object (or alternatively an arbitrary structure) against the data types of all the columns in the database tables managed by the record object. If you're delivering a webservice that requires validation, it's likely that you may want to extend the basic validation provided with your own validation for specific types of semantics that aren't covered by the basic data types described by the database, such as phone numbers, email addresses, serial numbers, etc. The system does not currently include any tools for implementing these custom validators.

The value returned from the validate method is an array of structures in which each element in the array is a detail for a value in the data which failed validation. The data is valid if the returned array is empty. If the array is not empty, each element in the array will contain these values:

columnthe name of the database column that failed validation
tablethe database table in which the column resides
propertythe name of the object property that maps to the failed column
errorTypethe type of validation error raised - types are required, format and length
formatthe data type of the failed column
invalidDatasome or all of the provided value that failed validation for the column - this value is truncated to a maximum of 23 characters

Primary Column and NewID()

For the active record to work at all, it needs to know what its "primary key" is. A primary key is simply a column in the database table which is unique on a given row, allowing an individual record to be identified or selected using the value in this column. The active record object needs to know which column to use to identify its record because it needs to use the value in this column to filter its select, update and delete statements so that only the individual record is affected and you don't end up calling ar.delete() and then wondering why your table is empty.

If your table contains a primary key constraint, then DataFaucet will use that information to determine the primary key column automatically. If not then you will need to use setColumn("primary","yourPrimaryColumn") to tell the active record object which column to use.

If you use an autonumber or identity column as the primary column in your table, that's about all you need to know. If you don't use an autonumber or identity column, then you may also need to know how to set the initial value of the primary column before performing a create operation. The primary key of any record can not be null, which means that the ActivRecord object needs to have a value for this column before it can insert a new record in the database. Because it's a standard and because it guarantees a high level of uniqueness, it's common to use a Universally Unique ID (UUID) to seed the primary key columns in databases. DataFaucet will generate a UUID automatically by default, which means your primary column needs to be a 35-character string. This is however not the only way to skin this cat, and so if you want to use some other method of generating an ID for a new record, you can override the newid() method in the active record object. That code might look like this:

<cffunction name="newID" access="private" output="false" returntype="string">
  <cfreturn toBase64(getTickCount() & RandRange(0,999999)) /> 
</cffunction>

You can use any method you'd like to generate your primary key as long as the function returns a simple value (string or number) and the function should be private. The default method in the active record object cascades the request for a newID as well, so if you want to configure your application to use a different method of generating new IDs by default, you can extend either the Datasource component or the SQLAgent component to overload the newID methods more globally.

Autonumber / Identity Columns and Sequences

The active record object should automatically know which column in the table is an autonumber or identity column and set this value automatically after a create operation if you are using ColdFusion 8. This takes the hassle out of managing autonumber columns, even with ColdFusion because the returned value in the ColdFusion result collection is platform-dependant! The insert statement object however eliminates the platform dependancy, allowing the active record to easily update its primary key. So getting the id of your inserted record to perform other operations (like browser redirection) is as easy as this:

<!--- save the product data --->
<cfset arProduct.create(form) />

<!--- continue to the next page --->
<cflocation url="nextProductPage.cfm?productid=#arProduct.getProductID()#" />

Some databases such as Oracle may not have an autonumber or identity column feature. In these cases the database may support part of the SQL standard called a "sequence". You can add support for a sequence as a method of seeding your new records in the active record object by setting the name of the sequence to use with the setSequence() method.

<cfcomponent output="false" extends="datafaucet.system.activerecord">
  <cfset setTable("mytable") />
  <cfset setSequence("mytable_seq") />
</cfcomponent>

Handling Nulls

The active record object will automatically nullify any columns with foreign key constraints (which you should be using anyway) if no value is supplied for the column. If you need to nullify values in other columns, there are two ways to do that. The first is by using the setNull argument in the update() method (ar.update(insertData=form,setNull="col_1,col_2"). The second method is to use the setNull() function (setNull("col_1,col_2")). Both methods are committed to the database immediately.

IsLoaded() and IsDeleted()

Occasionally the active record object needs to know if the record exists in the database. In its simplest terms, you can test this using the isLoaded() method. When the active record object performs a read operation, it sets its status internally to indicate if it is loaded (data was returned from the database when read) or it has been deleted (the returned query contained no data). So if the active record loaded successfully, a call to isLoaded() will return true and similarly if it attempted to read the record and found no data then it assumes the record has been deleted and a call to isDeleted() will return true. Once a record has been deleted, it can only be reinserted into the database using the Undelete() method.

In some circumstances you might want to reuse some of the code in your active record across several objects. While you could create a separate active record object for each iteration, sometimes it's easier (and more efficient) to simply set all the properties in an already created object. This way you can loop over a query containing data from several records and reuse the active record code. When you do this, you'll want a way to tell the active record that it has all the data it needs without performing its own read operation. This can be done via the setLoaded() method.

Example:

<cfset ds = request.DataFaucet.getDatasource() />
<cfset ar = ds.getActiveRecord(classname="my.object.class").init() />

<cfloop query="myquery">
  <cfset ar.setProperties(myquery,currentrow) />
  
  <cfif currentrow eq 1>
    <cfset ar.setLoaded() />
  </cfif>
  
  <cfif ar.someMethod()>
    <cfset ar.doSomething() />
  </cfif>
</cfloop>

In a perfect world, this is a bit less encapsulated than might be desirable, because the object generating the query in this loop must know what table the active record object uses and because not performing its own read operation means the active record may not execute some code which executes during or after the read.

Then again, we don't live in a perfect world.

Customizing the Record

What you've seen up to this point are just the simplest ways to use the active record object however, they don't offer much flexibility. You have a table and you have data, and they get mapped one-to-one. While that's nice in itself, in order to get the real benefit out of an active record object you have to customize it.

Wait? I thought you said this was easy and it would reduce my workload. Now you're telling me I have to do more?

More than I've shown, but probably still less work than you're accustomed to doing. DataFaucet is designed with convention over configuration as one of its leading principals, so to that end you should only ever be required to write code for something in your application that is "non-standard". It's those non-standard parts that are the reason why you need to extend the active record object.

To extend the active record object you simply create a CFC and use the extends attribute of the cfcomponent tag. You may need to use a ColdFusion server mapping for this to work if your copy of DataFaucet isn't directly inside your web root or if you are using shared hosting.

<cfcomponent output="false" extends="datafaucet.system.activerecord">
  <cfset setTable("tblProduct") />
  <cfset setColumn("author","createdby") />
  <cfset setColumn("editor","modifiedby") />
  <cfset setColumn("created","datecreated") />
  <cfset setColumn("modified","datemodified") />
  
  <cffunction name="init" access="public" output="false">
    <cfargument name="productid" type="string" required="false" default="" />
    
    <cfif len(trim(arguments.productid))>
      <cfset this.read(arguments.productid); />
    </cfif>
    
    <cfreturn this />
  </cffunction>
  
  <cffunction name="set_ProductPrice" access="private" output="false" returntype="void">
    <cfargument name="propertyname" type="string" required="true" />
    <cfargument name="propertyvalue" type="numeric" required="true" />
    
    <cfset getSomeObject().logPriceChange(this) />
    <cfset setProperty(propertyname,propertyvalue) />
  </cffunction>
  
  <cffunction name="get_TaxesAndFees" access="private" output="false" returntype="numeric">
    <cfreturn getSomeObject().getTaxesAndFees(getProperty("productprice")) />
  </cffunction>
</cfcomponent>

Although this might seem long for an example, the above might be a complete component, from start to finish, instead of the many hundred or thousand-line files we typically work with.

So here's an explanation of how all the pieces in the above example work.

Getters and Setters

Although the above sample might seem a little complicated at first it is really quite simple. Any time a property is set on an active record object there might be some data massaging needed to ensure that the value being added is correct. So for that purpose, external objects can only set properties on the active record via the public setValue() function (or the duck will allow you to use setPropertyXXX(theValue) which is the same thing). The setValue function then checks to see if you've created a private data massaging function internally within your active record object and if so it executes the data massaging function. If it doesn't find a data massaging function, it assumes everything is okay and sets the property via setProperty().

Here's a visual:

userObject 
  → ActiveProduct.setProductPrice(price) - public (optional) 
    → ActiveProduct.setValue("ProductPrice",price) - public 
      → ActiveProduct.set_ProductPrice("ProductPrice",price) - private (optional) 
        → ActiveProduct.setProperty("ProductPrice",price) - private 

So if you didn't write the set_ProductPrice function, the same flow might look like this:

userObject 
  → ActiveProduct.setValue("ProductPrice",price) - public 
    → ActiveProduct.setProperty("ProductPrice",price) - private 

There are two extra steps in the first example. The first extra step at the top is just a convenience for people who like to use setProductPrice(price) instead of setValue("productPrice",price). The third line in the above example showing set_ProductPrice("ProductPrice",price) is the only one you really need to worry about. That's where you perform any data massaging for values being set in your active record. If you don't need to massage the data, then you don't need to write the function.

The same logical flow is also true of the getter functions, although they have only the propertyname argument, which you can omit/ignore most of the time.

Column Prefixes

While we're on the subject of getters and setters let's talk about column names. As programmers we're always told to create descriptive variable names and this also applies to our database columns. So when given the choice between creating a "price" column and a "ProductPrice" column, the holy keepers of "best practice" would proclaim that "Price" is inspecific and the column should always be named "ProductPrice". One reason is because the product may include other kinds of prices such as discount price, etc. and another reason is that in the event that we need to find all instances of the product price in our application code, it's easier to find them if we can search for "productprice" instead of merely "price" which is likely to return many false-positives for other kinds of prices like discount prices, etc. If we are then mapping those columns to an object in our OO architecture, we wind up with a method call on our object that looks like this:

<cfset temp = product.getProductPrice() />

Not all of us like this, because of the duplication of the defining text "product" in both the name of the object and the name of the method. In an ideal world, you would think that product.getPrice() would suffice and you can tell from reading it that the price being retrieved will be the product price.

Some other ORMs which prefer configuration rather than convention will attempt to resolve this issue by providing an alias for each individual column in their configuration XML. You can see this in the configuration for Hibernate as an example. There are a couple of problems with this solution. The first is that you'll almost never rename any column except to remove some or all of the table name and/or special characters like underscores, which means if you have a big table and lots of aliased columns, you're duplicating lots and lots of column aliases that could be automated. The second problem is that choosing to use the added flexibility it provides would largely be a mistake, because it would introduce gratuitous confusion into the code (i.e. "spaghetti"), which good OO practices strive to avoid.

Here is an example of how another ORM might offer this configuration (this is NOT a DataFaucet example):

<mapping>
  <component name="product" table="tblProduct">
    <id name="ProductID" type="long" column="ProductID">
      <generator class="assigned"/>
    </id>
    
    <property name="Name">
      <column name="ProductName" />
    </property>
    <property name="Price">
      <column name="ProductPrice" />
    </property>
    <property name="Description">
      <column name="ProductDescription"/>
    </property>
    <property name="Location">
      <column name=ProductLocation/>
    </property>
  </component>
</mapping>

So once you had this definition in the ORM tool's mapping XML then you could create a "product.product" object, use it to read some data from your database and you could fetch its description property using product.getDescription() instead of product.getProductDescription(). Great! But as you can see, doing that creates a lot of duplication of effort in mapping that object to the table. All the columns in the above example are "aliased" to property names that are nothing more than the name of the column, minus some variation of the name of the table (product).

The advantage of this design is also it's greatest weakness. It gives you the flexibility to assign column names to property names however you want, so you could for example have a column name "ProjectLocation" and fetch it from your object with a method name like getCity(). Ooops! Is "city" the "location" or is the "location" something all-together different, like the state, county or street address... Or maybe location is the mailing address of a central office located somewhere all-together different. The only advantage we were hoping to get from using the aliases in the XML was more legible code. Unfortunately the end result is the opposite of our goal (less legible code). So perhaps it's best just to avoid this kind of copious confusion.

This leads us to the DataFaucet solution: occluded prefixes.

You can set a column prefix for occlusion in your active record by using the setColumn() method and specifying the column descriptor "prefix". There are two columns that can be omitted from the occlusion, which are the "primary" column (your table's primary key) and a "name" column (which can be any column). These two columns are omitted from the prefix stripping by default, so if you want to strip the prefix from them also, you would have to enable them to be stripped using the columns "prefixprimary" and "prefixname", which each contain a boolean (true or false).

Confused? I don't blame you. Everything is easier with examples.

<cfcomponent output="false" displayname="ProjectPhase"  extends="datafaucet.system.activerecord">
  <cfscript>     setTable("tblProjectPhase"); 
    
    // remove the prefix "phase" from all columns in this object 
    setColumn("prefix","phase"); 
    
    // strip from the PhaseID primary-key column (not recommended) 
    setColumn("PrefixPrimary",false); 
    
    // don't strip from the "PhaseName" column 
    setColumn("name","PhaseName"); 
  </cfscript>
</cfcomponent>

After creating the table tblProjectPhase with the columns phaseid, phasename, phasedescription and phaselocation and then creating this CFC, you might read a phase and display it's properties:

<cfset ar = ds.getActiveRecord(className="project.phase").init(1004) />
<cfdump var="#ar.getProperties()#" />

And see something like this:

struct
DESCRIPTION planning phase
ID 1004
LOCATION headquarters
PHASENAME Phase I

So I've already covered the advantages of doing this (more legible code). Since this is a design pattern, I'll also briefly explain some consequences.

You'll have to be somewhat careful with occluded prefixes because they can cause problems when you don't expect them to. When performing queries against the table the returned columns will be different than the properties in your object. Fortunately the active record object fixes a lot of the fidgety issues related to passing data between the record and the rest of your application, but since you won't always be using an active record object, there may be cases in which you are using a query and a property structure and the two don't match.

One last thing to remember is that part of the reason why the system allows you to omit a "name" column from prefix stripping is because "name" by itself can frequently cause namespace conflicts. One example of this is if you have an HTML form and an input element named "name", then any JavaScript in your HTML will have difficulty accessing the input element because it becomes occluded by the form's name attribute. This could cause problems with JavaScript form validation for example if the name field is required and your JavaScript attempts to reference form.name.value, discovers the undefined value and throws an error. It's likely the same might also happen with any column named "ID" and you can see from the output above why I don't recommend stripping the prefix from the table's primary key.

The Active Record object also provides one additional method of modifying column names which is primarily for the purpose of smoothing over interaction with corporate or legacy databases where column names may contain a lot of odd characters, such as underscores. If a database contains a lot of columns like project_loc_addr, project_loc_city, project_loc_state, project_loc_zip, the underscores can become cumbersome to type. You can automatically remove the underscores or other characters from the column names in an Active Record object by adding the unwanted characters to the "badchars" column, i.e. setColumn("badchars","_").

Creating the active record object

So once you've extended the active record, this will also change the way you create the object. Where before you used the datasource object and called the getActiveRecord() method with a table name and objectid, now you'll need to do something a little different. You've got several choices.

You can:

The common theme between all three of these methods is that they each find a way to set the datasource for the created active record object. Using the datasource object's getActiveRecord() method with a className argument allows the datasource to set itself before returning the object. The other two methods involve setting the datasource after it's been created.

Read-Only Columns

You can instruct the active record object to ignore specific columns by setting them in the semantic column "ignored". For example, ar.setColumn("ignored","col_1,col_2") will cause the active record object to omit the the specified columns during an update operation. Columns can not be ignored during insertion in the current version.

Related Tables

When you extend the active record class you also get access to its SQL methods. This means that for a CFC that extends active record, you can easily fetch data from related tables using the internal method getSQLStatement("select",table).

For example, to get a query containing all the products in a category from a productCategoryActiveRecord object, you might have a function like this:

<cffunction name="getProducts" access="public" output="false" returntype="query">
  <cfreturn getSQLStatement("select","tblProduct").execute() />
</cffunction>

The getSQLStatement() object assumes that the related table (tblProduct in this case) will contain columns matching the primary key of the active record table (in this case probably "productcategoryid"). It then automatically filters the statement based on the primary key. So the query executed by this function would look like this:

SELECT * FROM [catalog].[schema].[tblProduct] WHERE productcategoryid = 
<cfqueryparam value="#ar.getProductCategoryID()#" cfsqltype="cf_sql_varchar" />

And is equivalent of this:

<cffunction name="getProducts" access="public" output="false" returntype="query">
  <cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
    var ds = getDatasource(); 
    var stmt = ds.getStatement("select").init("tblProduct"); 
    stmt.filter("productcategoryid",this.getProductCategoryID()); 
    return stmt.execute(); 
  </cfscript>
</cffunction>

The getSQLStatement method simply eliminates the need to duplicate these few lines of code in several different methods by encapsulating it into a single method call.

If the related table does not have matching columns, getSQLStatement() will produce an error. In this case you will need to use getDatasource() to access the datasource object directly to build your query.

The getSQLStatement method has these arguments

ArgumentDescription
typeselect, insert, update or delete
tablename of the affected database table
columnsname of columns to select
orderbyorder of the returned records
insertDataadditional input data for update statements

Many to Many Relationships

A many to many relationship between records in two tables requires the inclusion of a third, cross-reference table in a relational database. This cross-reference table usually contains just two columns with foreign keys to either of the relevant tables. For example in the Waterlog sample application the tblWaterlogCategory table containing blog categories and the tblWaterlogEntry table containing blog entries have a many-to-many relationship. Each category can have many blog entries and each entry can be in many categories. This relationship is managed through a third table named tblWaterlogEntryCategory. This third table contains only two columns: categoryid and entryid.

Because many to many relationships like this are quite common, the active record object includes some tools for helping you manage many to many relationships to reduce your workload. Primarily you will likely want to use the addManyToManyRelationship() function, which assigns a particular table to hold many-to-many data.

Example:

<cfcomponent output="false" displayname="arBlogEntry"  extends="datafaucet.system.activerecord">
  <cfset setTable("tblWaterlogEntry") />
  
  <!--- manage the many-to-many relationship to categories 
    using the waterlogentrycategory table --->
  <cfset addManyToManyRelationship("tblWaterlogEntryCategory") />
  
  <cffunction name="init" access="public" output="false">
    <cfargument name="entryid" type="string" required="false" default="" />
    
    <cfif len(trim(entryid))>
      <cfset read(entryid) />
    </cfif>
    
    <cfreturn this />
  </cffunction>
</cfcomponent>

Once the addManyToManyRelationship() method is called in the constructor, the active record object will then automatically read the list of values from the many-to-many table as a comma-delimited list into a property having the name of the foreign key column for the alternate table. When performing a create or update operation the same property value is used to repopulate data in the cross-reference table, which is especially helpful when creating forms for editing the active record object. You can also manually specify column names for the many to many relationship in the addManyToManyRelationship() method.

The method has these properties:

ArgumentDescription
tablethe name of the cross-reference table where many-to-many data is stored
columnname of the column in the cross-reference table which contains the value of the active record's primary key
referencesname of the column in the cross-reference table which contains the value of primary key records in the final linked table - this is also the name of the property in the active record object
removeAllboolean - when true (default) all cross-reference records are removed prior to reinsertion when managing cross-reference id's - otherwise only records not found in the property list will be removed

In some cases for performance reasons you might not want all the cross-reference data to be queried every time the active record object is loaded from the database. In this case you can use the other many-to-many functions provided to read or set these values on a case-by-case basis as needed. All many-to-many functions are located in the armanytomany.cfc in the system directory.

Join-Subclass

Sometimes it may be desirable to have an active record object that manages data across multiple tables simultaneously. An example of this might be in a content management system, where all content records are stored in a central Content table, while certain specific types of content have additional data stored in related tables. In this case you might have a FileContent class with information about the file stored in a FileContent table and another NewsContent class with information about the news article in a NewsContent table. However in the case of both the FileContent records and the NewsContent records, general Content information not specifically related to the File or the News still needs to be stored in the Content table. Using a join-subclass allows you to retain all the properties and behavior of the general content class while storing additional data in one or more extra tables.

Creating a join-subclass is simple. You merely extend the active record class as you normally would and in addition to setting the table and optional columns, you specify the additional tables you want to include with the addTable() method. This method accepts the name of the joined table, the source "column" and target "references" column as its arguments. If you're using a foreign key constraint on the joined table however, you can omit the column names when adding the table and the active record object will determine the relationship keys automatically.

Example:

<cfcomponent output="false" displayname="arContent"  extends="datafaucet.system.activerecord">
  <cfset setTable("tblContent") />
  
  <cffunction name="init" access="public" output="false">
    <cfargument name="contentid" type="string" required="false" default="" />
    <cfif len(trim(arguments.contentid))>
      <cfset this.read(arguments.contentid) />
    </cfif>
  </cffunction>
</cfcomponent>

<cfcomponent output="false" displayname="arFileContent" extends="arContent">
  <cfset addTable("tblFileContent") />
</cfcomponent>

It's important to note that addTable should only be used on tables that have a one-to-one relationship with the active record table. This means that for each active record, there will be no more than one matching record in the added table. One-to-many relationships should be handled via related table queries and many to many relationships should be handled via addManyToManyRelationship() feature or other many to many methods.

Automated Installation

Active record objects can be designed to automatically install the required table if needed simply by adding CFPROPERTY tags to the CFC or if you need more granular control over the data structure, by setting the schema variable to a string of XML containing the DDL syntax for the desired tables, views, sequences and stored procedures. This keeps information about the table neatly encapsulated in the active record object. The first time the active record attempts to discover its columns from the database it will install the table after discovering that no columns exist.

The simplest way to create a self-installing record object is by using the CFPROPERTY tag to indicate the desired columns. Each property tag should specify a normal CF type value of date, string, numeric, boolean or UUID. For string and numeric properties and additional length attribute may be included to indicate the length of the column or the precision and scale of a numeric column. Numeric columns will default to a type of integer although bigint, smallint, tinyint or real can be substituted for precision and scale. For string columns, adding a ! to the length will make the column fixed instead of variable width and adding a * to the length will make the column multibyte for internationalized strings. The standard cfproperty attributes required and default will map directly to the table columns although invalid default values will be removed. For a default value of the current time with a date property, use a default value of "now".

The primary key for the created table can be designated by adding a "key" attribute to the appropriate CFPROPERTY tag with a value of "true".

Foreign key constraints may be created simply by adding a references attribute with the name of the target table and column to which the foreign key constraint should be made.

This table shows how to define various column types:

Data TypeProperty Definition
integer<cfproperty name="NumPages" type="numeric" required="false" />
numeric(3,2)<cfproperty name="MyFloat" type="numeric" required="false" length="3,2" />
real<cfproperty name="MyReal" type="numeric" required="false" length="real" />
tinyint<cfproperty name="NumChildren" type="numeric" required="false" length="tinyint" />
char(10)<cfproperty name="ssn" type="string" required="false" length="10!" />
nchar(10)<cfproperty name="ssn" type="string" required="false" length="10!*" />
nvarchar(50)<cfproperty name="name" type="string" required="false" />
nvarchar(1000)<cfproperty name="notes" type="string" required="false" length="1000*" />
varchar(35)<cfproperty name="id" type="uuid" required="true" />
varchar(50)<cfproperty name="name" type="string" required="false" length="50" />
timestamp<cfproperty name="datecreated" type="date" required="false" default="now" />
autonumber<cfproperty name="myid" type="numeric" key="true" autonumber="true" />
long text<cfproperty name="TheWholeChapter" type="string" required="false" length="long*" />
bit<cfproperty name="isDeleted" type="boolean" required="true" default="0" />
foreign key<cfproperty name="categoryid" type="uuid" required="true" references="tblCategory.CategoryID" />
many-to-many<cfproperty name="projectid" type="string" required="false" references="tblProject.ProjectID" xref="tblStaffProjectXref" />
NOTE: Individual database engines may make data type substitutions as appropriate for a given database. For example, MySQL doesn't support "long text" and so will revert to a 2000 character varchar column. Similarly Oracle will make a substitution for a data type of "money". This is not an exhaustive list of exceptions.

The following example shows how you might create a simple product record object using primarily CFPROPERTY tags.

<cfcomponent output="false" extends="datafaucet.system.activerecord">
  <cfproperty name="productid" type="uuid" key="true" />
  <cfproperty name="productname" type="string" required="true" length="100*" />
  <cfproperty name="productdescription" type="string" required="false" length="long*" />
  <cfproperty name="productprice" type="numeric" required="true" length="real" />
  
  <!--- create a foreign key constraint to ensure this product is associated with a vendor --->
  <cfproperty name="vendorid" type="uuid" required="true" references="tblVendor.VendorID" />
  
  <!--- create a cross-reference table to allow a many-to-many relationship with product categories --->
  <cfproperty name="categoryid" type="string" 
    references="tblProductCategory.CategoryID" 
    xref="tblProductCategoryXref" />
  
  <cfset setTable("tblProduct") />
  <cfset addManyToManyRelationship("tblProductCategoryXref") />
</cfcomponent>

If you need more control over the created table you can create the schema XML for your record manually either in the variables.schema variable or by overwriting the getSchema() method. This gives you the ability to create multiple tables, views, stored procedures and more. The following example shows an active record object that will alternatively use an autonumber column for databases that support them OR use a sequence for databases that support sequences. The unsupported feature (sequence or autonumber) is ignored by the target database engine when the object is installed.

Example:

<cfcomponent output="false" extends="datafaucet.system.activerecord">
  <cfset setTable("tblProduct") />
  <cfset setSequence("seqProduct") />
  
  <cfsavecontent variable="variables.schema"><cfoutput>
    <ddl>
      <create type="table" name="#getTable()#">
        <col name="productid" type="integer" autonumber="true" key="true" />
        <col name="productname" type="nvarchar(20)" null="false" />
        <col name="productdescription" type="nlongvarchar" />
        <col name="productprice" type="real" required="true" />
        <!--- create a foreign key constraint to ensure this product is placed in a category --->
        <col name="categoryid" type="varchar(35)" required="true" references="tblProductCategory.CategoryID" />
      </create>
      <create type="sequence" name="seqProduct" />
    </ddl>
  </cfoutput></cfsavecontent>
</cfcomponent>

You can also include ancillary tables that might be needed by the record object in its schema, although you should avoid duplicating the schema for any tables that have their own separate active record objects.

If necessary, an active record object can also be installed manually using the install() method. The install method can also be used to upgrade a previously installed active record object. When called the object will add any new tables or columns not previously installed.

<cfset ds = request.DataFaucet.getDatasource() />
<cfset ds.getActiveRecord(className="my.ar.class").install() />

In the event that you need to install a table that has a separate active record object, you can easily install related active record objects by overriding the install() method of activerecord.cfc to create an instance of the related object and install it manually. The following example shows how you might install tables for a product-category record when installing a product object.

Example:

<cffunction name="install" access="public" output="false" returntype="boolean">
  <cfscript>     // get the datasource 
    var ds = getDatasource(); 
    
    // install product category tables 
    var pc = ds.getActiveRecord(className="my.product.categoryrecord").init(); 
    
    // install tables for this product object 
    super.install(); // this calls ds.parseDDL(getUpgradeXML()); 
    
    // finished!
    return true;
  </cfscript>
</cffunction>

Returning true at the end of this install method tells the active record object that the required tables have been successfully installed. The getUpgradeXML() method shown in the example above creates an instance of the SchemaExport CFC and exports the tables and columns that have already been installed, then performs a diff operation to compare the installed tables against the tables specified in schema. The result of the diff is a DDL packet that will install any new tables or columns not previously installed. Future versions of DataFaucet will also support modifying the data types of already installed table columns.

During active development if you're making frequent changes to the tables, you can also remove all the tables created in its schema XML by calling the active record's uninstall() method. Using these methods will ensure that the DataFaucet's cache is always in-sync with any changes you've made to your database schema.

Listeners and Observers

In addition to the rather direct approach of using the getter and setter methods to perform "AOP" tasks like logging, the active record object also provides the ability to register listeners and observers, which allow you to dispatch code execution without needing to write extra get/set methods in the active record object.

A listener is a CFC that responds to specific events which occur within the active record object. For example you might have a listener that responds to the "update" event by logging information about changes in the price of a product. The events broadcast by the active record object occur when the object connects to the database. The events are Create, Read, Update, Delete and Undelete. To use a listener you must register it in the active record object using the addListener(event,Listener) method. Once the listener is registered, it will be notified via a Respond(event,ActivRecord) method each time the registered event occurs.

An observer is like a listener except that it is notified when one of the active record's properties changes. Changes to properties can occur at any time and don't necessarily coincide with any database activity. To use an observer you must register it in the active record object using the addObserver() method. Once the observer is registered, it will be notified via the Observe() method each time the property is set.

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the listener and observer objects 
  listener = CreateObject("component","my.product.listener").init(); 
  observer = CreateObject("component","my.product.observer").init();
  
  // get a product active record object 
  ar = ds.getActiveRecord(className="my.product.class"); 
  
  // set a listener to log the price change 
  ar.addListener("update",listener); 
  
  // read the product data from the database 
  ar.read(form.productid); 
  
  // set an observer to tell the logger about price changes 
  ar.addObserver("productprice",observer); 
  
  // set the price 
  // the observer is notified when this happens 
  // via observer.osbserve(
  //  propertyname="productprice",
  //  propertyvalue=50,
  //  overwrite=true,
  //  instance=ar); 
  ar.setProductPrice(50); 
  
  // commit the change to the database
  // the listener is notified when this happens 
  // via listener.respond("update",ar); 
  ar.update(); 
</cfscript>

So the listener and observer objects respectively might look like this:

Observer:
<cfcomponent output="false">
  <cffunction name="init" access="public" output="false">
    <cfreturn this /> 
  </cffunction>
  
  <cffunction name="observe" access="public" output="false">
    <cfargument name="propertyname" type="string" required="true" />
    <cfargument name="propertyvalue" type="string" required="true" />
    <cfargument name="overwrite" type="boolean" required="false" default="true" />
    <cfargument name="instance" type="any" required="true" />
    
    <!--- let the logger know that the price was modified --->
    <cfset instance.setPriceModified(iif(arguments.overwrite,1,0)) />
  </cffunction>
  ...
</cfcomponent>

Listener:
<cfcomponent output="false">
  <cffunction name="init" access="public" output="false">
    <cfargument name="priceLogger" type="any" required="true" />
    <cfset variables.priceLogger = arguments.priceLogger />
    <cfreturn this /> 
  </cffunction>
    
  <cffunction name="respond" access="public" output="false">
    <cfargument name="event" type="string" required="true" />
    <cfargument name="instance" type="any" required="false" />
    
    <!--- Check to see if the price was changed --->
    <cfif val(instance.getPriceModified())>
      <!--- It was. Log the change --->
      <cfset priceLogger.logPriceChange(instance) />
    </cfif>
  </cffunction>
</cfcomponent>

The Waterlog sample application contains a debatably better example of the use of listeners with its entryviewcounter.cfc object. This object listens to the BlogEntryActiveRecord.cfc object for a "read" operation and tallies up all the reads performed in memory. When the application expires, the onApplicationEnd event then calls a method on the BlogActiveRecord.cfc which gathers the collection of view-counts stored in memory and updates each entry in the database with the modified view count.

This way instead of hitting the database an extra time to update the view count every time an entry is viewed, the application waits until there is minimal load (when the application has been given enough "down time" to expire) and then performs a much smaller handful of updates. So instead of updating the database ten times for ten views, it updates the database only once for all ten views on that record.

To provide more accurate view counts, the view counter object also updates the viewcount property of the active record object when it's read and is also attached as a listener to select statements which fetch lists of entries by month, by search terms or by category, updating the returned query to reflect the additional views stored in memory. In practice I might omit this step partly because it means more work for the server and partly because it's not really vital that this view information be highly accurate. I included it in the sample application more for the purpose of showing how the statement listeners work.

The waterlog sample application also uses the active record listeners to keep the blog's cache of categories and recent entries in-sync by updating the "cachedafter" property of each of these two statement objects to the current time (now()) when create, update or delete events are broadcast.

Individual active record objects may create their own interception points for listeners by using the Broadcast(event) method. As an example, if you had a BlogEntryActiveRecord.cfc for managing a blog entry and it managed its own comments (instead of offloading comments to a separate BlogCommentActiveRecord.cfc), you might want to notify listeners on the entry record when new comments are added for doing things like sending email, etc. So the addComment() method in the entry CFC might look like this:

<cffunction name="addComment" access="public" output="false">
  <cfargument name="commenttext" type="string" required="true" />
  <cfargument name="commentname" type="string" required="false" default="anonymous" />
  <cfargument name="commentemail" type="string" required="false" default="" />
  
  <cfscript>     var ins = getSQLStatement("insert","tblBlogComment"); 
    arguments.entryid = this.getEntryID(); 
    ins.setValue("insertdata",arguments).execute(); 
    
    // let the listeners know there's a new comment added 
    broadcast("addcomment"); 
  </cfscript>
  
  <cfreturn this />
</cffunction>