Some helpers for CFWheels DBMigrate Plugin

Here is a handy controller that I use with my CFWheels application deployments & for debugging my database migrations with Troy Murray’s invaluable CFWheels DBMigrate Plugin.

I use a git branching strategy that utilises feature branches, which can sometimes lead to database migration files being out of sequence based on their creation date.. So I needed a way to test that all current migration are in the correct order… Enter the magical DBMigrate.cfc controller.

To get this up and running, You’ll need to install the CFWheels DBMigrate Plugin, the /models/DBMigrateVersion.cfc model file and the /controllers/DBMigrate.cfc controller from https://github.com/chapmandu/cfwheels-dbmigrate-plugin-tools.

Once these files are in place, you can call the following URLs and see the JSON they return:

http://yoursite/index.cfm?controller=dbmigrate&action=ping

{
db: "okay"
}

http://yoursite/index.cfm?controller=dbmigrate&action=migrations

{
totalMigrationCount: 4,
isOrdered: true,
latest: "20160205201245",
versions: [
{
version: 20140527111730,
cfc: "20140527111730_create_tables",
migrated: true,
details: "create tables"
},
{
version: 20160205201146,
cfc: "20160205201146_insert_lookup_rows",
migrated: false,
details: "insert lookup rows"
},
{
version: 20160205201233,
cfc: "20160205201233_add_plain_text_password_field_to_users_table",
migrated: false,
details: "add plain text password field to users table"
},
{
version: 20160205201245,
cfc: "20160205201245_create_credit_card_number_columns",
migrated: false,
details: "create credit card number columns"
}
],
current: 20140527111730,
migratedCount: 1,
notMigratedCount: 3,
isMigrated: false
}

http://yoursite/index.cfm?controller=dbmigrate&action=current

{
current: 20140527111730
}

http://yoursite/index.cfm?controller=dbmigrate&action=latest

{
latest: 20160205201245
}

http://yoursite/index.cfm?controller=dbmigrate&action=ismigrated

{
ismigrated: false
}

The main one I use for my automated tests is the big sexy action=migrations packet. If any of the migrations are not in the correct sequence, the isordered node in the JSON will have a value of false Here is the RocketUnit test that I run to check if all migration are in the right order (and can be run automatically).

public void function test_db_checks_awaiting_migration_files_should_be_in_order()
{
    loc.args.action = "migrations";
    loc.response = getResponse(argumentCollection=loc.args);
    loc.substring = '"isordered":true'
    assert('loc.response contains loc.substring');
}

Disclaimer: Please be careful leaving this controller lying around in your production environment.. It doesn’t perform any database changes, but can give some basic information about your database migrations to anyone who may stumble upon your URL..

If you use the DBMigrate plugin, you may like this blog post… If you don’t use the plugin.. You really should.

Advertisements

Create globally unique URLs within CFWheels applications

One great feature of the CFWheels framework is being able to override inbuilt functions, yet still be able to call the core function from within. This allows you to slightly tweak core functions without having to copy, paste & butcher core code, which would 1) go against the DRY principle, and 2) create possible legacy issues when the core functions are updated.

The example below tweaks the urlFor() function by adding a random token to all urls generated within a CFWheels application. This can be handy to avoid page caching as every url is unique.

Because the urlFor() function is used within linkTo(), redirectTo() and startFormTag(), this single tweak trickles down to all CFWheels functions that generate urls.

// controllers/Controller.cfc

/**
 * Hint: Appends a token to all urlFor() calls
 */

public any function urlFor() {

	// create your random string
	var randomToken = LCase(Replace(CreateUUID(), "-", "", "all"));

	// defaults
	param name="arguments.params" default="";
	param name="arguments.token" default="true";

	// add the token to the URL unless told otherwise.
	if (arguments.token) {
		arguments.params = ListAppend(arguments.params, randomToken, "&");
	}

	// pass any args to the core urlFor helper
	return core.urlFor(argumentCollection=arguments);
}

Your urls that once looked like this /mypage, now magically look like /mypage?d9e4bd5a3b78427dab33fe02e0c14b5a=

Easily build where clauses for the CFWheels ORM with this whereify helper

Rather than stuff around building up a string when one’s WHERE clause is conditional.. I make heavy use of this simple little helper which takes an array, and returns a finished string ready to pass to the CFWheels ORM.

// helpers.cfm

/**
 * Hint
 * I surround each array element in brackets and return delimited by an operator
 */
public string function whereify(required array array, string operator="AND") {
	var loc = {};
	loc.array = [];
	for (loc.i=1; loc.i <= ArrayLen(arguments.array); loc.i++) {
		loc.array[loc.i] = "(#arguments.array[loc.i]#)";
	}
	return ArrayToList(loc.array, " #arguments.operator# ");
}

And here’s how I use it..

// YourController.cfc

var loc = {};
// build a sexy where clause array..
loc.where = [];
ArrayAppend(loc.where, "foo = 'bar'");
if (StructKeyExists(params, "slayer")) {
	ArrayAppend(loc.where, "somecolumn IN (1,2,3)");
}

// use the whereify helper in your ORM call
// Produces: (foo = 'bar') AND (somecolumn IN (1,2,3))
users = model("User").findAll(whereify(loc.where));

// You can also specify an operator
// Produces: (foo = 'bar') OR (somecolumn IN (1,2,3))
users = model("User").findAll(whereify(loc.where, "OR"));