Database Gateway API

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:

  1. Initialise the Factory
  2. Add a datasource if necessary
  3. Add a table schema to the Factory:
  4. Deploy the table to the datasource
  5. 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:

  1. Adding the component
  2. Functions that must be implemented
  3. 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.