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")#">

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

Speaking on ‘ColdFusion 9: Advance ORM’ at CFUnited 2009

• Author: Manjukiran
• July 23, 2009
• Category: Adobe, CFUnited, ColdFusion, ORM

I am excited to say that I will be speaking on ColdFusion 9: Advanced ORM at CFUnited – on August 13th at 3:15PM.  Terry Ryan will cover the basics of ORM in an other session just before mine.  I am planning to cover the following as part of the talk:

• Relationship – one-to-many, many-to-one, one-to-one and many-to-many
• Event Handling
• Lazy Loading
• Caching
• Transactions
• ORM Session Management
• HBM files
• Development time support – ormreload(), logSQL, savemapping, etc
• Generating DDL

If you want me to cover any other topics, do comment about it(Note that the above list is tentative.  ORM is a vast subject and I need to consider the time factor as well.)  This is my second session in CFUnited.  Last year I had talked about Taking advantage of 64-bit support in ColdFusion.

Details about the other sessions from Adobe speakers is here.  Meet you all at CFUnited!

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

ColdFusion-ORM: Define Computed properties (formula attribute)

• Author: Manjukiran
• July 18, 2009
• Category: Adobe, ColdFusion, ORM

What are computed properties?
Consider the tables – Art and Artists.  Art and Artists form a one-to-many relationship where one Artist has created many Art.  Say, in each Artist object, you need a property that would contain the number of arts made by the Artist – use computed properties.  The formula to compute the value of the property should be specified using the formula attribute of the cfproperty tag.  The formula should be specified as a SQL that returns a single value.  Note that computed properties do not map to any column in the table and are not persistent.

Task:
Example to demonstrate using computed properties 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

Stuff that you would learn:
- how to define computed properties
- how to define the formula for computed properties

Steps to Run the example:
-  This example needs the cfartgallery datasource. This is shipped with ColdFusion by default.
-  Create a directory say "ormformula" under webroot.
-  Create the following files – Application.cfc, Artists.cfc and index.cfm.
-  Run the URL “http://<ip>:<port>/ormformula/index.cfm”

Application.cfc

component
{
    //Name of the application
    this.name = "ORM_Formula";

    //ormenabled should be set to true so that ORM is enabled for this application
    this.ormenabled = "true";

    //Set the datasource that needs to be used by the ORM Functions.  You can also set this in the ormsettings struct
    this.datasource = "cfartgallery";
}

Artists.cfc

component persistent="true" entityname="Artists" table="Artists"
{
    property name="id" column="ARTISTID" generator="increment";
    property name="firstname";
    property name="lastname";
    property name="address";
    property name="city";
    property name="state";
    property name="postalcode";
    property name="email";
    property name="phone";
    property name="fax";
    property name="thepassword";
    /*
        A property automatically becomes a computed property if formula attribute is specified.
        A SQL that returns a single value should be input to the formula attribute.  Note
        that computed properties do not map to any column in the table and are not persistent.

        In this example, the "numberOfArts" property will store the number of arts made by the
        Artist.  

        Have a close look at the SQL that is used. In this SQL, if you specify any column name, by default,
        hibernate assumes that the column is from the table to which this CFC maps to - here
        Artists table.  If you want to specify a column that belongs to another table, you should alias
        them. Hence, here, in the where condition art.ArtistId=ArtistId, the latter 'ArtistId' would be
        from the Artists table.  For each Artists entity, Hibernate would then substitue 'ArtistId' with
        the column-value for that entity.  For example, for the entity with ArtistId=5, the SQL would be:

            select Count(*) from Art art where art.ArtistId=5
    */
    property name="numberOfArts" formula="select Count(*) from Art art where art.ArtistId=ArtistId";
}

index.cfm

<!---
This example will teach you
- how to define computed properties
- how to define the formula for computed properties
--->

<cfset ormreload()>

<cfset artists = EntityLoad("Artists")>

<cfloop array="#artists#" index="artist">
    <cfoutput>
        #artist.getFirstName()# #artist.getLastName()# has <b>#artist.getNumberOfArts()#</b> arts<br>
    </cfoutput>
</cfloop>

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