by Jared Beck and Leon Miller-Out of Singlebrook Technology
- Introduction
- Overview
- Requirements
- Features
- Usage
- Data Definition (Tables)
- ColdFusion Components (CFCs)
- When tables do not follow naming conventions
- Soft Deletion (Tombstoning)
- Appendix
- History
- Features that need documentation
DBRow and DBSet constitute an Object-Relational-Mapping (ORM) system for ColdFusion MX. The original goal was to promote the DRY principle (Do not Repeat Yourself) by using the structure of database tables to automatically populate objects with properties and data. It was later discovered to be an implementation of the Active Record design pattern.
<cfset w = CreateObject('component', 'widget')>
<cfset w.new()>
<cfset w.widget_name = "Rocket">
<cfset w.year_invented = "282">
<cfset w.inventor = "Emperor Carinus">
<cfset w.store(getID = true)>
<cfoutput>The primary key of the new record is #w.widgetid#</cfoutput>
<cfset w = CreateObject('component', 'widget')>
<cfset w.load(url.widgetID)>
<cfset w.widget_name = form.widget_name>
<cfset w.store()>
<cfset w = CreateObject('component', 'widget')>
<cfset w.load(url.widgetID)>
<cfset w.delete()>
Dbrow also supports soft-deletion (tombstoning) if the underlying table has a 'deleted' column. See below for more details.
| DBRow Version | ColdFusion Versions | RDBMSs |
|---|---|---|
| 3.2 | 9+ | MSSQL,PostgreSQL,MySQL |
| 3.1 | 8+ | MSSQL,PostgreSQL,MySQL |
| 3.0 | 6.1+ | MSSQL,PostgreSQL,MySQL |
| 2 | 6.1+ | MSSQL,PostgreSQL,MySQL |
| 1 | 6.1+ | None in particular |
The application must have the following variables set:
- request.timeLong - a timespan, usually set to createTimeSpan(0,2,0,0). Used for caching purposes.
- request.timeNone - a timespan set to createTimeSpan(0,0,0,0). Used for caching purposes.
The application may have the following variables set to reduce configuration code required in model and set objects' pseudoconstructors:
- application.datasource - the name of a ColdFusion datasource pointing to the database where the objects are persisted.
- application.objectMap - a ColdFusion mapping (in dot-delimited format) that points to the folder where the dbrow objects live. If you want to organize your dbrow objects into sub-folders, use dbrow3mapper.
- application.dbrow3mapper
- application.dbrow3cache
- Automatic CRUD (Create, Read, Update, Delete) methods. These are provided by the store(), load(), store(), and delete() methods respectively.
- Stub methods provide hooks for attaching custom code pre- and post- all CRUD operations. (e.g. beforeStore(), afterLoad())
- Remotely-accessible List/Edit views (including New and Delete functions) allow for the rapid development of a basic administration area with a minimum of .cfm files.
- Automatic analysis of foreign key constraints provides support for 1-to-1 and 1-to-many relationships, including rendering of lists of related items in Edit view. Many-to-many relationships are also supported, but must be manually defined, and do not yet have an automatically-generated GUI.
- Custom form fields can be created with setField(). Custom fields can include the standard fields with drawStandardFormField().
- Data validation
- Data is automatically validated before store().
- Custom validation rules can be added with addValidation().
- Client-side validation is handled by formvalidation.js (svn+ssh://svn.singlebrook.com/svn/_shared/trunk/_js/formval/formvalidation.js). See that file for usage instructions. dbrow3.getValidationAttribs(propertyName) will give you the necessary attributes to put in your input tag.
- Server-side validation uses database metadata (datatypes, NOT NULL, etc) and custom rules
- getErrorArray() returns an array of validation errors. If you want a struct, use getErrorStruct().
- getError(arErrors, propertyName) gives you the error message for a specific property.
Create the database and the tables that will support your application. Follow dbrow naming conventions for table and column names (see below).
CREATE TABLE tblWidget ( widgetid [whatever auto-increment your RDMS wants] primary key, widget_name text, year_invented integer, inventor text );
Follow these naming conventions to reduce object pseudoconstructor configuration.
- Table name: tbl#theObject#
- Primary key: #theObject#ID
- Object name: #theObject#_name
- Datasource: #application.datasource#
Create a CFC that extends one of the RDBMS-specific versions. Instances of this object represent one row in your table.
<cfcomponent name="widget" extends="com.singlebrook.dbrow3_1.dbrow3_mysql">
<cfset theObject = "widget">
<!--- Additional configuration here if necessary (see Configuration Options) --->
</cfcomponent>
These optional variables may be set in the model object's pseudoconstructor.
theID - Specifies the primary key column. Default is "#theObject#ID".
theTable - Specifies the name of the table in the database. Default is "tbl#theObject#".
theDatasource - Specifies the ColdFusion datasource that points to the database. Default is #application.datasource#.
theFieldsToSkip - Comma-separated list of columns that will not be inserted/updated when an instance is store()ed. This should include the primary key column if it is an auto-incrementing field. Default is the empty string.
theNameField - Specifies which column will be used as an instance's name. Used in various places, but notably in loadByName(). Default is "#theObject#_name".
hiddenFieldList - Comma-separated list of columns that will not be displayed when drawForm() is called.
theObjectMap - Specifies a ColdFusion mapping (in dot-delimited format) that points to the folder where the dbrow objects live. Default is "#application.objectMap#".
binaryFieldList - Comma-separated list of columns that hold binary data. This should be deprecated in favor of inspecting the column metadata.
These methods may be called in the model object's pseudoconstructor to control the behavior of the listing and form drawing methods.
setLabel(column, label) - Overrides the default label for a column. Default labels are constructed by replacing underscores in the column name with spaces, then capitalizing the first letter of each word.
setField(column, fieldHTML) - Overrides the default form field for a column. Default form fields are based on column metadata (type and length) and foreign keys.
Create a CFC that extends dbset3. This object will be used to search for and load up multiple objects as a recordset or array.
<cfcomponent name="widget_set" extends="com.singlebrook.dbrow3_1.dbset3">
<cfset theObject = "widget">
<!--- Additional configuration here if necessary (see Set Configuration Options) --->
<cfset this.init()>
</cfcomponent>
These optional variables may be set in the set object's pseudoconstructor.
Coming Soon
When the table does not follow naming conventions, extra configuration is necessary in the object pseudoconstructor. E.g.:
CREATE TABLE noncoventional (
noncoventional-primarykey integer,
noncoventional-name text
);
<cfcomponent name="widget" extends="com.singlebrook.dbrow3_1.dbrow3_mysql">
<cfscript>
theObject = "noncoventional";
theID = "noncoventional-primarykey";
theTable = "noncoventional";
theDatasource = "myCFDatasource";
theNameField = "noncoventional-name";
</cfscript>
</cfcomponent>
"Your app deleted my record!"
We often implement soft deletion to help troubleshoot and recover from accidental record deletion by app users.
If the table has a 'deleted' column, dbrow3.delete() will set deleted = true instead of actually deleting the record. The name of the 'deleted' column is not configurable.
Attempts to load() a soft-deleted record will throw com.singlebrook.dbrow3.LoadDeletedRecordException. If you have a good reason to load() a soft-deleted record, then use load()'s includeDeleted argument.
dbrow - Given a datasource and table name, handles CRUD (Create, Read, Update, Delete) functionality, automatically generating object properties based on database columns.
dbset - Very little built-in functionality other than generic getAll() method.
dbrow2 - Major new features include:
- Property datatypes and bound variables in SQL queries. This necessitates the addition of RDBMS-specific adapters that map RDBMS native datatypes to ColdFusion datatypes and query the database for the necessary metadata.
- Automatic analysis of 1-to-many relationships
- Form-drawing capability, including automatic generation of dropdowns of related items for fields with foreign key constraints
- Support for client-side form validation via formvalidation.js
dbset2 - Begins to encapsulate more useful functionality. Major new features include:
- List view with configurable fields. Links to dbrow2's edit view for editing items.
New related Singleton objects that improve performance and flexibility of dbrow2 and dbset2.
- dbrow2mapper - Maps object names to table names, etc.
- dbrow2cache - Caches metadata information to speed up object instantiation.
dbrow3 - Major new features include:
- Automatic tombstoning of objects (instead of outright deletion)
- Support for many-to-many relationships (currently in code only, not yet supported in form fields).
- Server-side data validation based on database properties and custom rules
- Updates to dbrow3mapper to understand types
dbset3 - Additions include:
- More filtering options in getAll()
- Tombstoning support to hide tombstoned items by default.
- Form and field drawing
- Configuration options for dbset objects
- Associations
- Usage of _set objects
- Data validation (server and client-side)
- dbrow3mapper
- dbrow3cache
- Object "type" system