Doing CRUD Operations in Transfer (Transfer Tutorial Part 1)

I have been slowly working my way through making a Transfer version of cfpetmarket , which has been a great deal of fun.

What I propose to do with this series of articles is to go through some small cases in the cfpetmarket application, as it will eventually be released, and use it as a base to explain some of the functionality of Transfer.

This may end up being a repeat of some of things I've talked about before with Transfer, but I think some small things have changed, so I'm happy to go over them again.

First off all, we'll need to setup Transfer, which is pretty straight forward.  Simply place the 'transfer' directory in the root of your web directory, or you can make a '/transfer' mapping to the directory if you want to keep it off the web root.

From here, we first need to create two XML files.

The first is where we set what our data source definition is, I like to call mine 'datasource.xml', but realistically you can call it whatever you like.

The datasource.xml for cfpetmarket looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<datasource xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/datasource.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>petmarket</name>
  <username></username>
  <password></password>
</datasource>

You will notice there is a reference to a datasource.xsd file.  I use Eclipse as an IDE, and when I create my XML file, I refer it to an xml schema file, in this case datasource.xsd, so I get my context suggestion and xml validation.

This should look pretty straight forward, where the 'name' element is the name of the datasource, and the 'username' and 'password' elements are for optional username and password fields for the ColdFusion datasource.

The second file we create is a 'transfer.xml', this defines each of our Transfer Objects in our application.  Again, it doesn't have to be called 'transfer.xml', but since that's the name of the framework, I figured it was appropriate.

Again, we will reference an XSD file, and in this case it will be the 'transfer.xsd' schema.  Normally we would have several 'object' and 'package' elements, but we'll add them in a little bit, so for now, all we'll worry about is:

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/transfer.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <objectDefinitions/>
</transfer>

Normally we would have our package and object definitions inside the 'objectDefinitions' element, but we'll take a look at the database first.

We have to create a singleton of the TransferFactory, so we can actually use Transfer.  Singleton is just a fancy shmancy way of saying 'there is only of these in the application'.  The easiest way to do this is to create it in the application scope.  

This is probably the least sophisticated way of handling a singleton, but for the purposes of this article (and in cfpetmarket) it will work well for us.

The TransferFactory takes 3 arguments

  1. The relative path to the datasource.xml file
  2. The relative path to the transfer.xml file
  3. The path from root to where Transfer is going generate it's definition files

The first two are relatively straight forward arguments, the third may not be so.

The way Transfer works is by generating ColdFusion code the first time it is run.  Once that code is generated, it is then run and utilised in generating the Transfer Objects that Transfer creates.  Therefore, Transfer needs to know this directory both so it can generate the required files, and also so that it can find them later on.

An example initialisation would look like this:

application.transferFactory = createObject("component", "transfer.TransferFactory").init("/config/datasource.xml",
"/config/transfer.xml",
"/config/definitions");

In this case, we will be creating the definition files in the '/config/defintions' folder off the web root.  The definitions don't have to be created here, but it seemed like a logical place for the files to go.

Now that we have our TransferFactory up and running, we'll look the database.

In this tutorial, as all we are looking at is Create, Read, Update and Delete (CRUD) operations, we'll only be looking at one table in the database, and there are more coming.

The table we are looking at is the 'category' table.  The category table looks like so:

  categoryoid int 
PK displayname varchar
  systemname varchar
  color varchar
  locale varchar
  image varchar

It should be noted that we are dealing with a MS SQLServer database, but it is possible to use other databases with Transfer.

It should also be noted, within the actual cfpetmarket application there is no Identity seed on the 'categoryoid' column, but for the sake of this article, we're going to pretend that there is.

So let's go back to our transfer.xml file, and we're going to create a 'package' inside this definition.  Packages are just like regular packages, in that they are a means to organise all our objects into separate areas for ease of use.

We'll create a 'pets' package for our 'Category' to eventually sit under, as it is a Pet Store Category.

Creating a package looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/transfer.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <objectDefinitions>
      <package name="pets">
    </package>
  </objectDefinitions>
</transfer>

Packages can contain packages, so you are not limited to how many levels you are able to go.

Next we have to define an object, and tell it which table it corresponds to.  We're going to call our object 'Category' and point it to the 'category' table

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/transfer.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <objectDefinitions>
      <package name="pets">
          <object name="Category" table="category">
          </object>
      </package>
  </objectDefinitions>
</transfer>

Next we're going to tell it which column is the primary key on the table –

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/transfer.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <objectDefinitions>
      <package name="pets">
          <object name="Category" table="category">
              <id name="categoryoid" type="numeric"/>
          </object>
      </package>
  </objectDefinitions>
</transfer>

You will notice that we haven't specified what the column is in the 'id' element.  There is a 'column' attribute to the 'id' element, however it's default value is the value of the 'name' attribute, and in this case, the value of 'categoryoid' is perfect for the name of the 'id' property.

Next we will look at the properties of the 'Category' objects, and strangely enough, these are pretty much exactly the same format as the 'id' element, except that they are 'property' elements.

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../transfer/resources/xsd/transfer.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <objectDefinitions>
      <package name="pets">
          <object name="Category" table="category">
                 <id name="categoryoid" type="numeric"/>
              <property name="displayname" type="string"/>
              <property name="systemname" type="string"/>
              <property name="color" type="string"/>
              <property name="categoryLocale" type="string" column="locale"/>
              <property name="image" type="string"/>          
        </object>
      </package>
  </objectDefinitions>
</transfer>

As you can probably see, each 'property' element corresponds with a column on the 'category' table.  On the last item, we've decided we wanted to have a different property name than the name of the column, so in this case, we've called the property name 'categoryLocale' and the column is set to 'locale'.  The reason for this will become clearer later on.

So now, if we reset our initialisation on the TransferFactory, so that it re-reads our new transfer.xml file, we have ability to create, read, update and delete records on the 'category' table.

So from here, if we want a new 'Category' object, we first have to get an instance of Transfer from the TransferFactory –

<cfset transfer = application.transferFactory.getTransfer()>

And then we request the object

<cfset category = transfer.new("pets.Category")>

It should be noted that the class name is case sensitive. So in this case, looking for a class called 'pets.category' will result in an error.

Now we have an object of type 'transfer.com.TransferObject'.  But if you look at the API for a TransferObject, there isn't much to it that allows you to do much with the Object itself.  That is because Transfer adds the required methods to the TransferObject at run time, so that you can manipulate the object.

So for each property and also for the id, there is a get and set method for each of the names of the property.  i.e. There is now a 'setCategoryOID',  'getCategoryOID', 'setDisplayName', 'getDisplayName', 'setColor', 'getColor' etc methods on the object for us to use.

Now you may also be able to see why we created the property named 'categoryLocale' and mapped it back to the column 'locale'.  The reason for this is that 'setLocale' is a ColdFusion native function, and you cannot create a function with the name 'setLocale', otherwise the system will throw an error. Therefore we had to have a different name for the property other than 'Locale', otherwise there would have been a issue.

So if we want to set some properties of the Category before we insert it into the database, we can use the setter functions on the object, like so:

<cfset category.setCategoryName("Fish")>
<cfset category.setImage("fish.jpg")>
<cfset category.setColor("#fc3e41")>

And so on.

Now, the easy bit comes – inserting it into the database:

<cfset transfer.save(category)>

And that is it.  It has now been inserted into the database, and the 'categoryOID" property on the 'category' object has automatically been set to the ID that the database would have created.

Now if we decide we want to change the image on the category, we can now do:

<cfset category. setImage("fish2.jpg")>
<cfset transfer.save(category)>

And the category has been updated in the database.  The 'save' command intelligently calls the appropriate 'INSERT' or 'UPDATE' command in the database, depending on whether or not the object has been committed to the database yet.

If you want to retrieve the category object from the database, you only need to tell Transfer the class that you want, and the id of the category you wish to retrieve, like so:

<cfset category = transfer.get("pets.Category", 1234)>

Again, note that the class name is case sensitive.

And to finally complete the set, if you want to delete a Category –

<cfset transfer.delete(category)>

And the Category has been deleted from the database.  Nothing more than that.

That pretty much covers all the aspects of getting creates, reads, updates and deletes happening on your database with Transfer.

Don't forget more details on Transfer can be found at the home page, as we've only just scratched the surface, and if this interests you, please feel free to join the mailing list.

Next article we'll start looking at composite objects and how they are handled in Transfer.

Leave a Comment

Comments

  • jax | May 15, 2007

    <cfset category.setCategoryName("Fish")>

    Shouldn’t that read:

    <cfset category.setDisplayName("Fish")>

    ?

  • Mark | May 16, 2007

    Yup, you would be right! my bad!

  • mone | December 14, 2007

    Can we delete the category without loading it first? e.g.:

    <cfset transfer.delete("pets.Category", 1234)>

  • Mark | December 15, 2007

    mone –

    Yes, and no. Ideally, you want to delete the object itself, so Transfer can do it’s own internal cache cleanup.

    That being said, if you want to delete via SQL, check the documentation for how to explicitly discard objects from the cache.