ColdFusion 9.0 Resources

Map the properties

Contents [Hide]

Primary key
- Simple primary key
- Composite Key
Generators
Column
- Computed property
Versioning
- Version
- Time stamp
CFC ORM types
Escaping SQL keywords in table and column name

The cfproperty tag is used to define:

Primary or composite key
Columns
Relationships
Versioning

The following table lists the common attributes that are used to define these mappings:

Attribute	Req/Optional	Default	Description
`default`	optional		This sets the default value on the property when the object is created.
`fieldtype`	optional	column	This attribute is used to specify the type of the property. Use this attribute to specify the following: Primary key: Specify `fieldtype = "id"` to map a property to the primary key in the table. For details, see Primary key. Column: Specify the `fieldtype = "column"` to map a property to a column in the table. For details, see Column. Relationship: Specify the `fieldtype = "relationship_type"`. The relationship_type can be `one-to-one`, `one-to-many`, `many-to-one`, or `many-to-many`. For details, see Define Relationships. Version: Specify the `fieldtype="version`” to indicate that the column contains versioned data. For details, see Version. Timestamp: Specify the `fieldtype="timestamp"` to indicate the column contains data with timestamp. For details, see Time stamp. Collection: Specify the `fieldtype="collection"` to define the collection mapping. For details, see Collection Mapping
`name`	required		Specifies the name of the property.
`type`	optional		Specifies the ColdFusion data type for the property.
`persistent`	optional	true	Specifies whether this property should be persisted. If `persistent="false`” all the ORM related attributes are ignored. Set this attribute to false if you do not want this property to be persisted by ORM. For example: <cfcomponent persistent=true> <cfproperty name="a"> <cfproperty name="b" persistent="true"> <cfproperty name="c" persistent="false"> </cfcomponent> When an object of this CFC is persisted, the properties a and b would be persisted in the database but the property c would not be persisted.
remotingFetch	optional	true	If `remotingFetch` is `false`, then the value of that attribute is not sent over flash remoting. The attribute is true by default for all properties. However, for ORM CFCs where persistent = true, the value of the `remotingFetch` attribute is set to false, by default, for one-to-one, one-to-many, many-to-one, or many-to-many relationships.

Primary key

Simple primary key

In a relational database, a primary key is defined as a key that uniquely identifies a row in a table. Typically, a table has one primary key that represents a single column of information in the table.

To indicate that a cfproperty maps to a primary key in the table, set the attribute fieldtype="id".

Syntax

<cfproperty 
    name="property_name" 
    fieldType="id" 
    ormtype="type" 
    column="column_name" 
    generator="increment|identity 
    |sequence|sequence-identity|seqhilo 
    |uuid|guid|native|[assigned]|select|foreign" 
    params="{key1=val1,key2=val2...}" 
    sqltype="sql_type" 
    length="column_length" 
    unsavedvalue="instantiated_instance">

Example

An example to define an assigned primary key:

<cfproperty name="artistid" fieldtype="id" column="ARTISTID" generator="assigned">

An example to define a auto-generated primary key using increment generator:

<cfproperty name="artid" fieldtype="id" column="ARTID" generator="increment">

An example to define a auto-generated primary key using a generator, which requires additional parameters:

<cfproperty name="id" fieldtype="id" column="ID" generator="sequence" params="{sequence='id_sequence'}>

Attributes

Attribute	Req/Opt	Default	Description
`column`	Optional	The value of the name attribute	Used to specify the primary key column name.
`fieldType`	Optional		Should be "id" for primary key. If `fieldtype` is not specified and the `useDBForMapping=true`, then the `fieldtype` is determined by inspecting the database.
`generator`	Optional	assigned	Algorithm used to generate unique identifiers for instances of the persistent CFC. See Generators for details.
`length`	optional		Use this attribute to specify the length of the column. This attribute is used for table creation only.
`name`	Required		Name of the property
`ormtype`	Optional	String	Used to specify the data type of the primary key. If data type is not set and the ORM setting `useDBForMapping=true`, then the `ormtype` is determined by inspecting the database. The different data types that are supported by ColdFusion are specified in the CFC ORM types.
`params`			Additional parameters required by the various generators to generate the ID. The value for the `params` attribute should follow the CF Struct syntax. For example: `params = {key1='value1', key2='value2'}` See Generators for details.
`sqltype`	optional		Use this attribute to specify the DB-specific SQLType that should be used for the column. This attribute is used for table creation only. If this attribute is not specified, ColdFusion will automatically decide the SQLType based on the `ormtype` specified for this property.
`unSavedValue`	optional		An identifier property value that indicates that an instance is newly instantiated and not saved/loaded in the database

Composite Key

If the primary key consists of more than one column, it is called as a composite key. A composite key can be specified by using fieldtype='id' on all the properties that form the primary key.

Example

If in a table, the columns Order_id and Product_id form a composite key, then, they should be defined as:

<cfproperty name="Order_Id" fieldtype="id" column="Order_Id"> 
<cfproperty name="Product_Id" fieldtype="id" column="Product_Id">

For a composite key, the generator should always be "assigned".

Generators

Generators are algorithms that are used to generate unique identifiers for instances of the persistent CFC. Generators can have any one of the following values:

increment: This algorithm generates identifiers of type long, short, or int by incrementing a counter maintained by ORM. This is commonly used when auto-generation for the primary key is not enabled in the table and you want ORM to generate the primary key. This should be used when a single instance of ColdFusion is the only process to insert data into the table.
identity: For databases such as DB2, MySQL, Sybase, and MS SQL, which support identity columns, you can use identity key generation. The key is automatically generated by the database and the object is populated with the generated key. This strategy requires ORM to execute two SQL queries to insert a new object.
sequence: For databases such as DB2, Oracle, PostgreSQL, Interbase, McKoi, and SAP, which support sequences, you can use sequence style key generation. The key is automatically generated by the database and the object is populated with the generated key. This strategy requires ORM to execute two SQL queries to insert a new object. This generator includes the sequence parameter, which needs to be specified in the params attribute.

For example:
```
<cfproperty name="id" fieldtype="id" generator="sequence" params="{sequence='id_sequence'}">
```
native: This algorithm is commonly used to automatically generate primary keys. This generator picks identity or sequence depending upon the capabilities of the underlying database.
assigned: This algorithm is used when the application needs to assign its own identifier to the object. It is the default generator used by ColdFusion.
foreign: This is used with a <one-to-one> primary key association. In this case, the primary key is the same as the primary key of the associated object. This generator would need the property parameter, which needs to be specified in the params attribute. The value of the param property should be the name of the relationship property.

See One-to-one relationships for details.
seqhilo: See www.hibernate.org/5.html.
uuid: See www.hibernate.org/5.html.
guid: See www.hibernate.org/5.html.
select: See www.hibernate.org/5.html.
sequence-identity: See www.hibernate.org/5.html.

Column

To indicate that a cfproperty maps to a column in the table, specify fieldtype="column". If the fieldtype is not specified for cfproperty, it is mapped as a column property.

Syntax

<cfproperty 
    name="Property name" 
    fieldtype="column" 
    column="column_name" 
    persistent="true|false" 
    formula="SQL expression" 
    ormtype="ormtype" 
    update="[true]|false" 
    insert="[true]|false" 
    optimisticLock="[true]|false" 
    generated="true|[false]" 
    length="column_length" 
    precision="precision" 
    scale="scale" 
    index="index_name" 
    unique = "true|[false]" 
    uniquekey="uniquekey_name" 
    notnull="true|[false]" 
    dbdefault="default_col_value" 
    sqltype="sql_type">

Example

To specify a simple property:

<cfproperty name="FIRSTNAME"/>

To specify a property which has a different name than that of the column name:

<cfproperty name="LNAME" column="LASTNAME"/>

To specify a property which should be read-only:

<cfproperty name="password" column="THEPASSWORD" insert="false" update="false">

Attributes

Attribute	Req/Opt	Default	Description
`column`	optional	Name of the property	Name of the column.
`dbdefault`			This sets the default value of a column in the table when schema is exported.
`fieldType`	optional	column	Should be “column” for column property.
`formula`	optional		SQL expression that defines the value of the property. If you specify a formula, the column value is ignored. See Computed property.
`generated`	optional	never	Specifies that this property value is actually generated by the `database {never\|always\|insert}` never: Specifies that the value for this property is never generated by database always: Specifies that the value for this property is always generated by database insert: Specifies that the value for this property is generated at the time of insert but is not regenerated at updates.
`insert`	optional	true	Specifies whether the column should be included in SQL UPDATE and/or INSERT statements: {true/false} Set `update=false` and `insert=false` if the column needs to be read-only.
`name`	Required		Name of the property. If this attribute is not specified, the name of the property is used as the column name.
`optimisticlock`	optional	true	Whether updates to this property require acquisition of the optimistic lock on the table row: {true/false}
`ormtype`	optional	string	Specifies the data type. If data type is not set and the ORM setting `useDBForMapping=true`, then the data type is obtained by inspecting the database.
`update`	optional	true	Specifies whether the column should be included in SQL update statement: {true/false} Set `update=false` and `insert=false` if the column needs to be read-only.

The following attributes are used only when DDL generation is required and not used for runtime.

Attribute	Req/Opt	Default	Description
`dbdefault`	optional		Specifies the default value of the column in the table.
`index`	optional		Specifies the name of an index that is created using the mapped column.
`length`	optional		Specifies the length value.
`notnull`	optional	false	A Boolean value that specifies whether a notnull constraint should be added for this column.
`precision`	optional		Specifies the precision value.
`scale`	optional		Specifies the scale value.
`sqltype`	optional		This allows user to override the default mapping of `ormtype` to SQL datatype. `sqltype` is used as the DB specific SQL type for a column when creating the table. If this attribute is not specified, ColdFusion will automatically decide the `sqltype` based on the `ormtype` specified for this property. For example: <cfProperty name="active" ormtype="char" sqltype="bit"> <cfProperty name="balance" ormtype="float" sqltype="decimal(13,3)">
`unique`	optional		Specifies if there should be a unique constraint on the column.
`uniquekey`	optional		Groups columns in a single unique key constraint.

Computed property

Computed property is a property whose value does not come from a column but is computed using a SQL query. Use formula attribute to specify the SQL to be used to retrieve the value for this property.

<cfcomponent persistent="true" table="ARTISTS" schema="APP"> 
    <cfproperty name="ID" column="ARTISTID" fieldtype="id"/> 
    <cfproperty name="FIRSTNAME"/> 
    <cfproperty name="LASTNAME"/> 
    <cfproperty name="NumberOfArts" formula="select count(*) from Art art where 
                        art.ArtistID=ArtistID"/> 
</cfcomponent>

Versioning

Versioning is a technique that allows you to implement concurrency control fora component. You can specify either version or timestamp property for a component.

For details, see Optimistic locking.

Note: A component can have only one versioning property, either timestamp or version. If you specify multiple versioning properties, such as two timestamps, or two versions, or a timestamp and a version, an error is thrown.

Version

Use the version attribute to indicate that the column contains versioned data. The version attribute is useful for long transactions.

Syntax

<cfproperty 
name="fieldname" 
fieldtype="version" 
column="column name" 
ormtype="type" 
generated="true|[false]" 
insert="[true]|false">

Example

To create a simple version property:

<cfproperty name="version" fieldtype="version">

Attribute

Attribute	Req/Opt	Default	Description
`column`	Optional		The name of the column that contains versioned data
`fieldtype`	Required		Should be “version” for primary key.
`generated`	Optional	never	Specifies if the versioned field is generated by the database. The values are `"never"` and `"always"`.
`insert`	Optional		Specifies if the versioned field should be included in the SQL INSERT statement.
`name`	Required		Name of the property.
`ormtype`	Optional	`int`	The data type can be any one of the following: integer long short

Time stamp

Use the timestamp attribute to indicate that the column contains time-stamped data. Use the timestamp attribute as an alternative to the version attribute.

Syntax

<cfproperty 
name="fieldname" 
fieldtype="timestamp" 
column="column name" 
generated="true|[false]" 
source="[vm]|db">

Attribute	Req/Opt	Default	Description
`column`	Optional		The name of the column that contains time-stamped data.
`fieldtype`	Required		Specifies the field type. Specify the field type value as `timestamp` for a time-stamped field.
`generated`	Optional	never	Specifies if the timestamp field is generated by the database. You can select from the following values: never always
`name`	Required		Name of the property.
`source`	Optional	vm	Specifies the source from where the timestamp has to be retrieved. You can select from the following values: db vm

CFC ORM types

You can use any of the following ORM data types for CFCs:

string
character
char
short
integer
int
long
big_decimal
float
double
Boolean
yes_no
true_false
text
date
timestamp
binary
serializable
blob
clob

Escaping SQL keywords in table and column name

ColdFusion automatically escapes the table name or column name if it is an SQL keyword or if there is a space in it.

The list of SQL keywords are present in <CF_HOME>/lib/sqlkeywords.properties file. This file contains standard ANSI SQL keywords and some database-specific keywords. You can modify this file to include any other SQL keyword that is missing. In case you are adding SQL keyword for a database other than the ones specified in this file, you should also add it to the 'ANSI' list so that ColdFusion can use it.