More on using a view in a CFWheels model

I have been pulling my hair out (of which I do not have a lot), to try to figure out why I was getting an "Element I.VALUE is undefined in LOC." error when trying to return data with the findall method on a model that used a view when I was passing in a where clause.

Of course, I assumed it had something to do with being a view. After tracing the error down to a single line in the /wheels/model/crud.cfm addWhereClauseParameters method, I figured it was time to post a question to the forum.

Then I took one more look at my where clause. I found at first that if I only had one expression, everything worked fine, so I thought it had something to do with multiple expressions. Then I noticed I had two spaces before one of the AND's (which must be capitalized, by the way). I removed that space, and it worked.

Then I tried to use a small method to create the where clause (it is complicated and I had created it by hand at first to test). But I started getting the same error.

Then I saw that my method placed a space at the end of the where clause. I modified the method to remove this space, and it worked again.

Using a database View in CFWheels to populate a model

Because I am working on a legacy app with CFWheels, and that application had interfaces to the database outside of the CF application, database views became an important place to put some business logic, so it was available to all interfaces.

So, in order to be able to use CFWheels in some of the ways it was meant to, I need to be able to make a view be the source for models. Per, the keeper of CFW, pointed out that since views would not have primary keys, the CFW code would likely fail. And indeed, trying to access a model with the table property set to the name of a view caused the "No Primary Key" error, which insisted I set the primary keys for the table.

I found the line in the wheels code where this is checked, and temporarily (or perhaps longer) commented it out (model/initialization.cfm line 82-83).

This allowed me to call the model using cfset x = model("mymodel").mymethod. At least, it didn't error out. Within the method, I have a call to a cfquery, to match some legacy code. This all worked.

Next, I wanted to see if I could use the finders CFWheels gives us.

Unfortunately, I get an "Element I.VALUE is undefined in LOC" error coming from the SQLServer adapter. I will be looking into this

Getting the app to run

After changing all of the include statements to use absolute paths with the application mapping I created, lo and behold, I was able to login and was properly directed to the landing page.

However, there are a couple of oddities. First, it appears that my css is being overwritten by the cfwheels css. Second, the welcome to wheels message is displaying at the bottom of the page, underneath my content.

This was because I had simply placed the line from the cfwheels index.cfm at the bottom of my original index.cfm. Thus after running all of my code, it ran the cfwheels related code. And since the page I was displaying was index.cfm, it found the matching cfw route, which was to the congratulations page.

What I need is a way to distinguish whether the request was a cfw action or whether it was for the legacy app.

After playing around at displaying cfw pages versus legacy pages, I found that when the request was formatted in the cfw way (index.cfm/:controller/:action), "index.cfm" does not appear in the cgi.path_info. "Index.cfm" does appear in the script name though. So I added a little check at the top of my legacy index.cfm file to run the cfw index page if the situation is as I described above, and if not, run the legacy index.cfm.

Now this is done with limited testing. Since I don't have any cfw forms at this point, I don't know if this will break down when I begin to move my legacy code to cfw.

First Things First for using CFWheels in a legacy application.

Using the migrations code I created a series of migrations to bring a backup of the production database up to the current schema. Previously, all of database changes had been in a series of SQL files that I had labeled 1 - 10 and that I would have manually against the database.

Now I can just grab a copy of the current production database, restore it, and run the migrations and I know I will be up to date on all of my database changes. I was able to run both up and down without issues.

Now then, it is time to get my legacy code to work with CFWheels. Much of the code from the application has been around since 2003 and relatively untouched (there was never funding to do much to it).

First, I branched the code in my repository, so I could continue to have the legacy application pre-wheels if I need it. Then in my new cfwheels branch, I added the downloaded code from the CFWheels site (version 0.9.3) to the application. Fortunately, I did not have any file naming clashes other than the index.cfm and the application.cfc files. For those, I merged my existing code from the application to those from the cfwheels files.

As I lark, I tried to run the application. FAIL. It turns out, that CFWheels wants you to put the code that would traditionally be in your application.cfc file in their separate events files (named onapplicationstart.cfm, onrequeststart.cfm, etc). So I moved my code into those files.

This caused another problem. Since my code in my old application.cfc file was in the base folder for the application, any files that it needed to include were relative to the base. Since my code was no longer in the root (it was now in the events folder), I needed a way for me to easily point the old code to the location of the old files.

So I created an application specific mapping. I do not like making mappings through the Admin interface, as it limits where I can deploy the application. Even though the folks at CFWheels would prefer you didn't make any changes to the application.cfc file, I placed the mapping there, within a cfscript block.

this.mappings["/app"] = getDirectoryFromPath(GetBaseTemplatePath());

Now, whenever I need to include some legacy template in the cfwheels world, I can use the absolute path of "/app/mytemplate.cfm". Hopefully, this will greatly simplify my translation of the legacy application to cfwheels.

Next up, authentication.

Some Coldfusion to manage database schema changes similar to Rails Migrations

The pleasure and pain of developing Coldfusion applications. Easy to build, pain to keep track of the schema changes (particularly when you have several databases (development, test, qa, production) and several developers.

If you are like me, you have tried several different processes and policies to try to alleviate this. From naming conventions and sql files, to keeping track in the database the specific changes. Though there are any number of workable solutions, all, in the end, rely too much on manual steps and the diligence of the individual (programmer, dba, etc).

Perhaps the biggest problem is synchronizing what schema changes are necessary for any particular version of the code. And rolling back those changes to revert to an earlier version of the code.

Having spent the last couple of years building Rails applications, I have come to lean heavily on Migrations. These little wonders allow you to easily create, maintain, share, and roll back database changes that are integrated directly into the code version you are working on, testing, or releasing. Because they are Ruby code, they are kept in the same repository as the application code. You can learn more about Rails migrations here: Rails Migrations

Since I have recently been asked to revive a relatively dormant Coldfusion application, I knew that trying to manage the database changes over time was going to be a headache, particularly since I knew there was a better way.

Thus, I cobbled together some code that would allow me to approximate Rails Migrations in Coldfusion, using Coldfusion code to maintain the database changes.

Here is how it works.

First, create a "migrations" folder at the root of your application and add it as a mapping in your application.cfc template "this" section (i.e. this.mappings["/migrations"] = getDirectoryFromPath(getCurrentTemplatePath()) & "/migrations";)

Access the code at SourceForge and place it into the migrations folder. Take a look at the code, as query statement parameters need to be adjusted to match how your application accesses your datasource (suggestions on how to make this more generic are welcome). You will need to make changes to both the migrate.cfc and the sample_migration.cfc.

The code consists of three files: migrate.cfm, migrate.cfc, and sample_migration.cfc.

The migrate.cfm file is used to call one of three actions: setup, create, and run. Setup needs to be run first, as it creates a table in your database called "migrations" that tracks which database changes have been completed.

http://{application_root}/migrations?action=setup

"Create" uses the sample_migration.cfc to create a new migration with a unique naming convention. The convention is yyyymmddhhmmss_{a name of your choosing}_mg.cfc. You pass "create" a meaningful name which describes what the migration accomplishes.

http://{application_root}/migrations?action=create&name=add_work_phone_to_person_table

This will create a new file in your migrations folder and call it something similar to 20091004142253_add_work_phone_to_person_table_mg.cfc. Migrations must start with a 14 character number (representing the date and time the migration was created), and end with "_mg" to indicate it is a migrations file.

Once you have created the migration file, there are two places to edit it. Under the "migrate_up" function and query add sql statements for the desired schema change (i.e. "alter table person add work_phone varchar(50)"). Under the "migrate_down" function and query, add the analogous sql code that would revert the change (i.e. "alter table person drop column work_phone").

You can create as many migrations as you like, and then run them all at once, or run each migration individually after creating it. A migration can be include schema changes, new views, new indices, changes to data, etc. Basically, anything that you can run through a CF query statement. I suggest you do no more than one change in each migration, as they are easier to track and revert.


To run the migrations, simply call http://{application_root}/migrations?action=run. This will look at all of the migration files in your migrations folder, check whether they have been previously run (by looking at the records in the migrations table), and if not, run the migration (call the migration's migrate_up function).

To revert back to a particular migration (basically, to back up the database in time), include a version number when call the run action: http://{application_root}/migrations?action=run&version=20091004142253. Any migrations that have a higher version number that have already been run will be reverted (they will have their migrate_down code run).

The idea is that you are making changes to your application code that have dependent changes in the database. Create your migration at the same time you implement your application changes, and save them both to your repository, and they will be kept in synch.

If you have multiple developers making changes to the database, they can each be writing their migrations (it is unlikely that two will have the same version number). When a developer updates their local code base from the repository, they will get the migrations created by other developers and can run them, in addition to their own.

The code that I have written to handle this is rudimentary at best. I am sure better CF developers could produce a much more robust set of code, that better handles security (who is allowed to run the migration code) and requires less modification for incorporation into disparate applications.

I am very interested in your thoughts and improvements to the concept and the code. Perhaps someone else has already done this (I couldn't find anything, but that may mean little). If so, i would love to hear about it.