| Tip |
|---|
Implemented in FarCry 6.1.0 |
| Excerpt |
|---|
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:
- 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:
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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.
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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.
...
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 |
| Note | ||
|---|---|---|
| ||
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):
...
| Tip | ||
|---|---|---|
| ||
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:
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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.
...
| Info | ||
|---|---|---|
| ||
|
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.
| Warning |
|---|
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.
...
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. |
| Info |
|---|
Specificity: |
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.
...