Database Support

We currently support MS SQL Server, mySQL and H2 (for Express editions) databases. We would be happy to assist anyone in the community with the construction of additional dbgateway components; especially keen to see Oracle and Postgres support.

Implemented in FarCry 6.1.0

FarCry 6.1 presents a completely refactored database layer. The goal is to make the layer more distinct so that implementing a gateway CFC is enough to add support for a database to FarCry.

The following outlines the features required and a testing methodology.

Overview

The DB Factory (/farcry/core/packages/lib/db.cfc) provides a single point of access to all gateways. It caches the gateways for datasources and the table schemas.

The process for data access through the gateway is:

Initialise the Factory
Add a datasource if necessary
Add a table schema to the Factory:
- Schema can be created manually
- Or the Factory can generate it from a component
Deploy the table to the datasource
Access the various set/get/etc functions

NOTE All of this is done with the functions provided by the Factory.

Creating a new database gateway:

Adding the component
Functions that must be implemented
Testing

Factory Architecture and Usage

All access to the gateways go through the DB factory. It can be instantiated as many times as required, but the one loaded into application.fc.lib.db should be enough, even for multiple datasources.

<cfset db = createobject("component","farcry.core.packages.lib.db").init(dsn,dbtype,dbowner) />

Datasources

In the context of the Factory a datasource is the ColdFusion DSN + DB Type + DB Owner. Once this information is added to the Factory only the DSN is needed for database interactions.

As you saw above, the Factory is initialised with a dsn, dbtype, and dbowner as arguments. They are set to the application's values by default. But you can add as many datasources as you need:

<cfset db.initialiseGateway(dsn,dbtype,dbowner) />

Table Schema

The Factory (and the Gateways) use a data structure to describe the tables being accessed. All FarCry schemas are generated automatically from components, but it is possible to create them manually.

A table schema can be added with this, where typename is the schema key:

<cfset db.initialiseTableMetadata(typename,schema) />

The data is structured as follows:

Table schema structure

Key	Notes
tablename	The name of the table in the database
fields	A struct of field data structures
arrayfields	A struct mapping table names to field names for arrays properties e.g. arrayfields"dmHTML_aObjectIDs"="aObjectIDs"
indexes	An array of index data structures. Should include a "primary" index.

Field schema structure

Key	Notes
name	The field name
type	numeric/string/longchar/datetime - gateways use type and precision to determine how the field is deployed
precision	numeric: x,y (where x is digits before the decimal, and y is digits after the decimal), string: z (z is length of the string)
nullable	true/false - whether the field is nullable
default	Default value for the field
index	List of indexes the field is in, not including the primary key
bPrimaryKey	true/false - whether this field is part of the primary key

Array fields

Array fields are expected to have all of the table structure information in addition to the standard field information.

Index schema structure

Key	Notes
name	The name of the index. NOTE: the primary key should have a name of "primary".
type	primary/unclustered
fields	An array of fieldnames, in the order they should be applied in the index

Generating the table schema from a component

To generate a table schema by passing in a component path (schema will be keyed by that path):

<cfset db.initialiseTableMetadata(typename=componentpath) />

This generates a schema based on the following information:

Schema information	Source
Table name	Component name NOTE: Support for a dbTablename attribute is planned.
Fields	CFProperty tags
Field name	CFProperty "name" attribute
Field type	CFProperty "type" attribute: * boolean => numeric(1,0) * date, datetime => datetime * numeric, decimal => numeric(10,2) * string, nstring, varchar => string(255) * uuid => string(50) * variablename => string(64) * color => string(20) * email => string(255) * longchar, text => longchar * integer, int, smallint => numeric(11,0) * array => array property * everything else => as specified NOTE: The schema type can be overridden by adding the "dbType" attribute NOTE: these conversions may be updated in future releases
Field precision	CFProperty "dbPrecision" attribute (numeric fields: "x,y"; string fields: "z")
Field nullable	CFProperty "dbNullable" attribute
Field default	CFProperty "default" attribute
Primary key	CFProperty tags that have "dbPrimaryKey='true'" are added to the primary key
Indexes	Indexes are constructed using the "dbIndex" attributes of CFProperty tags. This attribute can have the following structures: * "true" => automatically converted to "propertyname_index:1" and used to generate an index * "indexname" => automatically converted to "indexname:n" and used to generate or update an index (if no position is specified, the field is appended) * "indexname:n" => an appropriate index is created or updated, with this field in the specified position * "indexnameA:nA,indexnameB:nB" => all the appropriate indexes are created or updated NOTE: All indexes besides the primary index are "unclustered" NOTE UUID fields have dbIndex="true" by default.
Array fields	A default schema is set up for array properties: * parentid, seq, data, typename fields * parentid and seq are the joint primary key * data is indexed
Array fields (extended)	An "extended" array field is one that has an "arrayProps" attribute defined on the CFProperty. This attribute should be in the form: subfieldnameA:subfieldtypeA,subfieldnameB:subfieldtypeB NOTE This format specifies fields to ADD to the defaults (parentid,seq,data,typename), and only supports simple field types

Array tables

If a component is registered such that it's tablename corresponds to the tablename for an array in another component, the new schema replaces the array schema.\\\\These tables use "objectid" as the primary key, and have a normal index on parentid and seq.

Deploying schemas

Basic schema deployment, where bDropType is true if you want to drop the table if it already exists:

<cfset db.deployType(typename,bDropType,dsn) />

Dropping an existing schema:

<cfset db.dropType(typename,dsn) />

Create, Set, Get, Delete

createData will add an objectid if it is missing and it is the primary key for the schema. However all other uses of these functions require the primary key values to be provided, either in the stProperties struct or as an argument.

Create

<cfset db.createData(typename,stProperties,dsn) />

Set

<cfset db.setData(typename,stProperties,dsn) />

Get

<cfset db.getData(typename,*objectid,*bDepth,*fields,dsn) />

Get

ObjectID ... or whatever the primary key is for the schema. Get can be used for simple array properties by specifying parentid and seq
bDepth (defaults to 1)
0: Everything (with full structs for all array field elements)
1: Everything (only extended array field as structs)
2: No array fields
3: No array or longchar fields
fields - Overrides the default fields returned. NOTE: the bDepth field may restrict the list further.

Delete

<cfset db.deleteData(typename,*objectid,dsn) />

Delete

ObjectID ... or whatever the primary key is for the schema. Get can be used for simple array properties by specifying parentid and seq

Gateway

Location

Gateways are added to the dbgateways package. They can be implemented in any plugin / project for testing, but must be in core in order to be used during installation.

We have plans to change the dbgateways package so that instead of the standard extension behaviour (e.g. components in the package replace the corresponding components in core), gateway components would be loaded with a mixin process. This approach would allow plugin writers to put their own custom SQL in a single component where it can be easily ported to other database servers.

Component

The component should have the following attributes.

Attribute	Notes
dbType	A list of key:label values for each of the database types this gateway supports. The labels are used to populate the database type selection list during installation. When initializing a datasource this attribute is used to identify load the appropriate gateway.
usesDBOwner	0/1/true/false. Used by installer to determine if the database owner input should be shown.

Note that this document assumes that gateways will extend BaseGateway to reuse the SQL that has already been written. However this is not required. Gateways can re-implement all required functions if necessary.

Functions

Name	Specificity	Notes
createData	Base
setData	Base
setArrayData	Base
getData	Base
deleteData	Base
isDeployed	Base
deploySchema	DB gateway	Because this function translates FarCry data types to database datatypes.
dropSchema	Base
diffSchema	Base
introspectType	DB gateway
isFieldAltered	Base, typically overridden
setArrayTypenames	Base	Base updates via a join, so some gateways may need to override it
combineResults	Base	Utility function
init	Base	Is passed the datasource and database owner
getValueFromDB	DB gateway	Performs any necessary translation between the database value and FarCry value. e.g. in some versions of FarCry post 2050 dates are null dates. This function identifies those dates and converts them to empty strings. Because of the spotty support for actual null date columns, this function usually needs to be reimplemented for each database.
getValueForDB	DB gateway	Performs any necessary translation from FarCry to database values. Returns a struct containing cfsqltype, null, and value, as appropriate for a cfqueryparam tag. Also see getValueFromDB.
addColumn	DB gateway	Because this function translates FarCry data types to database datatypes.
dropColumn	DB gateway
repairColumn	DB gateway
addIndex	Base
dropIndex	Base
repairIndex	Base	This function simply calls dropIndex, then addIndex.
introspectType	DB gateway	Has been implemented with two utility functions introspectTable and introspectIndexes. Most gateways will have the same structure with different SQL, but only introspectType is accessed outside the component.

Specificity:
Base This functionality is implemented in the base gateway, and probably won't need to be overridden.
Base, typically overridden Base does provide a default implementation, but it will probably not be enough.
DB gateway New database gateways need to include an implementation of this function.

Testing

An exhaustive test suite (for mxUnit) has been added to core. By default this suite uses datasource defined for the application for testing, but you can override this by setting dsn, dbowner, and dbtype on application.stPlugins.testMXUnit.

The tests can be run using the testMXUnit plugin.