ColdFusion-ORM: Auto-generation of tables, Naming Strategy and automatically populating data

• Author: Manjukiran
• October 9, 2009
• Category: Adobe, ColdFusion, ORM

Task:
Example to demonstrate Auto-generation of tables, Naming strategy and automatically populating data in ColdFusion-ORM

Previous Related Posts:
Getting Started with ORM
ColdFusion-ORM: Using CRUD Functions
ColdFusion-ORM: Define One-to-Many and Many-to-one relationships
ColdFusion-ORM: Collections
ColdFusion-ORM: Define Computed Properties

Stuff that you would learn:
- To auto-generate tables in ORM
- To fill data into the auto-generated tables
- To use the DDL-only attributes
- To define custom Naming Strategy

Steps to Run the example:
-  Create a datasource by name ‘test_datasource’ pointing to a empty database. Note that the example will take care of creating the tables and populating data into it.
-  Create a directory say "orm_autogen" under webroot.
-  Create the following files – Application.cfc, Art.cfc, Artists.cfc, mysqlscript.sql, lcasestrategy.cfc and index.cfm.
-  Run the URL “http://<ip>:<port>/orm_autogen/index.cfm”

Application.cfc

<cfcomponent>
    <cfset this.name = "ORM_Autogen">
    <cfset this.ormenabled = "true">
    <!---
    * 'test_datasource' is a datasource which should be created
    * as a pre-requisite for this example. Artists table contains
    --->
    <cfset this.datasource = "test_datasource">

    <!---
    * To auto-generate tables, dbcreate should be specified.
    * dbcreate is 'none' by default.
    * It can be 'dropcreate' or 'update'.
    * Setting it to 'update' creates the table if it does not
    * exist or update the table if it exists.
    * Setting it to 'dropcreate' drops the table if it
    * exists and then creates it.
    --->
    <cfset this.ormsettings.dbcreate = "dropcreate">

    <!---
    * Path to the SQL script file that should be executed after
    * ORM is initialized.
    * Note that this applies only if dbcreate is set to dropcreate.
    * This must be the absolute file path or the path relative
    * to the application.
    * The SQL script file lets you populate the tables before
    * the application is accessed.
    --->
    <cfset this.ormsettings.sqlscript="mysqlscript.sql">

    <!---
    * (Logical column name is the name of the property OR the value
    * of the column attribute if specified)
    * By default, when auto-generating the tables, the logical column
    * name is used as the column name.
    * If you want to change it, you should use the namingstrategy setting.

    * By default, namingstrategy="default". 

    * namingstrategy="smart": This strategy changes the logical table or
    * column name to uppercase. Also, if the logical table or column name
    * is in camel case, this strategy breaks the camelcased name and separates
    * the broken words using '_'.
    * For eg: firstName -> FIRST_NAME, dateOfBirth -> DATE_OF_BIRTH.

    * If you want to use a custom strategy, then, you should create a CFC,
    * implement the cfide.orm.INamingStrategy interface and specify the CFC
    * name for this setting.  In this example, I have demonstrated the
    * custom strategy.
    --->
    <cfset this.ormsettings.namingstrategy="lcasestrategy">
</cfcomponent>

Art.cfc

<cfcomponent persistent="true" table="Art">
      <cfproperty name="artId" generator="identity" fieldtype="id">
    <!---
    * Note that the attributes 'ormtype' and 'length' are used
    * only when the tables are auto-generated.  There are other
    * similar attributes like dbdefault, index, notnull, precision,
    * scale, sqltype, unique and uniquekey.
    --->
      <cfproperty name="artName" ormtype="string" length="50">
      <cfproperty name="price" ormtype="double">
      <cfproperty name="largeImage" ormtype="string" length="30">
      <cfproperty name="mediaId" ormtype="integer" length="10">
      <cfproperty name="isSold" ormtype="boolean" dbdefault=1>
      <cfproperty name="artist" fkcolumn="artistid" fieldtype="many-to-one" cfc="Artists">
</cfcomponent>

Artists.cfc

<cfcomponent persistent="true" table="Artists">
      <cfproperty name="artistId" fieldtype="id" ormtype="integer" length=10>
      <cfproperty name="firstName" ormtype="string" length="20" notnull="true">
      <cfproperty name="lastName" ormtype="string" length="20" notnull="true">
      <cfproperty name="address"  ormtype="string" length="50">
      <cfproperty name="city" ormtype="string" length="20">
      <cfproperty name="state" ormtype="string" length="2">
      <cfproperty name="postalCode" ormtype="string" length="10">
      <cfproperty name="email" ormtype="string" length="50" unique="true">
      <cfproperty name="phone" ormtype="string" length="20">
      <cfproperty name="fax" ormtype="string" length="12">
      <cfproperty name="thePassword" ormtype="string" length="20">
</cfcomponent>

lcasestrategy.cfc

<cfcomponent implements="cfide.orm.INamingStrategy">
    <!---
    * ColdFusion calls this method for each table name to generate
    * the new table name. The logical table name is the input.
    --->
    <cffunction name="getTableName" returntype="String" access="public">
        <cfargument name="tableName" type="String"> 

        <cfreturn lCase(tableName)> 

    </cffunction>

    <!---
    * ColdFusion calls this method for each column name to generated the
    * new column name. The logical column name is the input.
    --->
    <cffunction name="getColumnName" returntype="String" access="public">
        <cfargument name="columnName" type="String">

        <cfreturn lCase(columnName)> 

    </cffunction>
</cfcomponent>

mySQLScript.sql

--This SQl script will be automatically executed after the tables are auto-generated.
--Note that each SQL should be separated by a ';'.
insert into Artists(artistid, firstname, lastname, address, city, state, postalcode, email, phone, fax, thepassword)
values(1, 'Aiden', 'Donolan', '352 Corporate Ave.', 'Denver', 'CO', '80206-4526', 'aiden.donolan@donolan.com', '555-751-8464', '555-751-8463', 'peapod');
insert into Artists(artistid, firstname, lastname, address, city, state, postalcode, email, phone, fax, thepassword)
values(2, 'Austin', 'Weber', '25463 Main Street, Suite C', 'Berkeley', 'CA', '94707-4513', 'austin@life.com', '555-513-4318', '510-513-4888', 'nopolyes');
insert into Art(artname, price, largeimage, mediaid, issold, artistid)
values('Michael', 13900, 'aiden02.jpg', 1, 0, 1);
insert into Art(artname, price, largeimage, mediaid, issold, artistid)
values('Space', 9800, 'elecia01.jpg', 2, 1, 2);

index.cfm

<!---
* This example demonstrates Auto-generation of tables.
* This example will teach you
* - how to auto-generate tables in ORM
* - to fill data into the auto-generated tables
* - DDL-only attributes
* - how to use custom Naming Strategy

* 'test_datasource' is a datasource which should be created
* as a pre-requisite for this example. Artists table contains
* a list of artists records.  Art table contains a list of art
* records.  Artists table has a one-to-many relationship with
* Art table. These table would be created used in this example.
--->

<!---Load the artists and dump it--->
<cfdump var="#EntityLoad("Artists")#">

• 7 Responses
• Leave a comment...
• Comment Feeds