Select Page

Creating A Database-Driven Scheduled Task Runner In ColdFusion

Ben Nadel
Published: June 11, 2023

While Dig Deep Fitness won’t have much in the way of asynchronous processing (at least initially), there are some “cleanup” tasks that I need to run. As such, I’ve created a scheduled task as part of the application bootstrapping. This approach has served me well over the years: I create a single ColdFusion scheduled task that pulls task data out of the database and acts as the centralized ingress to all the actual tasks that need to be run.

As much as possible, I like to own the logic in my application. Which means moving as much configuration into the Application.cfc as is possible. Thankfully, ColdFusion allows for a whole host of per-application settings such as SMTP mail servers, database datasources, file mappings, etc. By using the CFSchedule tag, we can include ColdFusion scheduled task configuration right there in the onApplicationStart() event handler.

Here’s a snippet of my onApplicationStart() method that sets up the single ingress point for all of my scheduled tasks. The ingress task is designed to run every 60-seconds.

component {
	public void function onApplicationStart() {
		// ... truncated code ...
		var config = getConfigSettings( useCacheConfig = false );
		cfschedule(
			action = "update",
			task = "Task Runner",
			group = "Dig Deep Fitness",
			mode = "application",
			operation = "HTTPRequest",
			url = "#config.scheduledTasks.url#/index.cfm?event=system.tasks",
			startDate = "1970-01-01",
			startTime = "00:00 AM",
			interval = 60 // Every 60-seconds.
		);
		// ... truncated code ...
	}
}

The action="update" will either create or modify the scheduled task with the given name. As such, this CFSchedule tag is idempotent, in that it is safe to run over-and-over again (every time the application gets bootstrapped).

The CFSchedule tag has a lot of options. But, I don’t really care about most of the features. I just want it to run my centralized task runner (ie, make a request to the given url) once a minute; and then, I’ll let my ColdFusion application handle the rest of the logic. For me, this reduces the amount of “magic”; and leads to better maintainability over time.

To manage the scheduled task state, I’m using a simple database table. In this table, the primary key (id) is the name of the ColdFusion component that will implement the actual task logic. Tasks can either be designated as daily tasks that run once at a given time (ex, 12:00 AM); or, they can be designated as interval tasks that run once every N-minutes.

CREATE TABLE `scheduled_task` (
	`id` varchar(50) NOT NULL,
	`description` varchar(50) NOT NULL,
	`isDailyTask` tinyint unsigned NOT NULL,
	`timeOfDay` time NOT NULL,
	`intervalInMinutes` int unsigned NOT NULL,
	`lastExecutedAt` datetime NOT NULL,
	`nextExecutedAt` datetime NOT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	KEY `byExecutionDate` (`nextExecutedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This table can get more robust depending on your application needs. For example, at work, we include a “parameters” column that allows data to be passed from one task execution to another. For Dig Deep Fitness, I don’t need this level of robustness (yet).

My single CFSchedule tag is setup to invoke a URL end-point. The end-point does nothing but turn around and call my Task “Workflow” component:

<cfscript>
	scheduledTaskWorkflow = request.ioc.get( "lib.workflow.ScheduledTaskWorkflow" );
	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //
	taskCount = scheduledTaskWorkflow.executeOverdueTasks();
</cfscript>
<cfsavecontent variable="request.template.primaryContent">
	<cfoutput>
		<p>
			Executed tasks: #numberFormat( taskCount )#
		</p>
	</cfoutput>
</cfsavecontent>

As you can see, this ColdFusion template turns around and calls executeOverdueTasks(). This method looks at the database for overdue tasks; and then invokes each one as a separate HTTP call. Unlike Lucee CFML, which can have nested threads, Adobe ColdFusion cannot have nested threads (as of ACF 2021). As such, in order to allow for each separate task to spawns its own child threads (as needed), I need each task to be executed as a top-level ColdFusion page request.

Here’s the part of my ScheduledTaskWorkflow.cfc that relates to the centralized ingress / overall task runner:

component {
	// Define properties for dependency-injection.
	// ... truncated ...
	// ---
	// PUBLIC METHODS.
	// ---
	/**
	* I execute any overdue scheduled tasks.
	*/
	public numeric function executeOverdueTasks() {
		var tasks = scheduledTaskService.getOverdueTasks();
		for ( var task in tasks ) {
			makeTaskRequest( task );
		}
		return( tasks.len() );
	}
	// ---
	// PRIVATE METHODS.
	// ---
	/**
	* Each task is triggered as an individual HTTP request so that it can run in its own
	* context and spawn sub-threads if necessary.
	*/
	private void function makeTaskRequest( required struct task ) {
		// NOTE: We're using a small timeout because we want the tasks to all fire in
		// parallel (as much as possible).
		cfhttp(
			result = "local.results",
			method = "post",
			url = "#scheduledTasks.url#/index.cfm?event=system.tasks.executeTask",
			timeout = 1
			) {
			cfhttpparam(
				type = "formfield",
				name = "taskID",
				value = task.id
			);
			cfhttpparam(
				type = "formfield",
				name = "password",
				value = scheduledTasks.password
			);
		}
	}
}

The scheduledTaskService.getOverdueTasks() call is just a thin wrapper around a database call to get rows where the nextExecutedAt value is less than now. Each overdue task is then translated into a subsequent CFHttp call to an end-point that executes a specific task. Note that I am passing through a password field in an attempt to secure the task execution.

The end-point for the specific task just turns around and calls back into this workflow:

<cfscript>
	scheduledTaskWorkflow = request.ioc.get( "lib.workflow.ScheduledTaskWorkflow" );
	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //
	param name="form.taskID" type="string";
	param name="form.password" type="string";
	scheduledTaskWorkflow.executeOverdueTask( form.taskID, form.password );
</cfscript>
<cfsavecontent variable="request.template.primaryContent">
	<cfoutput>
		<p>
			Executed task: #encodeForHtml( form.taskID )#
		</p>
	</cfoutput>
</cfsavecontent>

Here’s the part of my ScheduledTaskWorkflow.cfc that relates to the execution of a single task. Remember that the taskID in this case is the filename for the ColdFusion component that implements the logic. I’m using my dependency injection (DI) framework to access that component dynamically in the Inversion of Control (IoC) container:

ioc.get( "lib.workflow.task.#task.id#" )

The workflow logic is fairly straightforward – I get the task, check to see if it’s overdue, execute it, and then update the nextExecutedAt date (depending on weather it’s a daily task or an interval task).

component {
	// Define properties for dependency-injection.
	// ... truncated code ...
	// ---
	// PUBLIC METHODS.
	// ---
	/**
	* I execute the overdue scheduled task with the given ID.
	*/
	public void function executeOverdueTask(
		required string taskID,
		required string password
		) {
		if ( compare( password, scheduledTasks.password ) ) {
			throw(
				type = "App.ScheduledTasks.IncorrectPassword",
				message = "Scheduled task invoked with incorrect password."
			);
		}
		var task = scheduledTaskService.getTask( taskID );
		var timestamp = clock.utcNow();
		if ( task.nextExecutedAt > timestamp ) {
			return;
		}
		lock
			name = "ScheduledTaskWorkflow.executeOverdueTask.#task.id#"
			type = "exclusive"
			timeout = 1
			throwOnTimeout = false
			{
			// Every scheduled task must implement an .executeTask() method.
			ioc
				.get( "lib.workflow.task.#task.id#" )
				.executeTask( task )
			;
			if ( task.isDailyTask ) {
				var lastExecutedAt = clock.utcNow();
				var tomorrow = timestamp.add( "d", 1 );
				var nextExecutedAt = createDateTime(
					year( tomorrow ),
					month( tomorrow ),
					day( tomorrow ),
					hour( task.timeOfDay ),
					minute( task.timeOfDay ),
					second( task.timeOfDay )
				);
			} else {
				var lastExecutedAt = clock.utcNow();
				var nextExecutedAt = lastExecutedAt.add( "n", task.intervalInMinutes );
			}
			scheduledTaskService.updateTask(
				id = task.id,
				lastExecutedAt = lastExecutedAt,
				nextExecutedAt = nextExecutedAt
			);
		} // END: Task lock.
	}
}

And that’s all there is to it. Now, whenever I need to add a new scheduled task, I simply:

  1. Create a ColdFusion component that implements the logic (via an .executeTask() method).

  2. Add a new row to my scheduled_task database table with the execution scheduling properties.

Like I said earlier, I’ve been using this approach for years and I’ve always been happy with it. It reduces the “magic” of the scheduled task, and moves as much of the logic in the application where it can be seen, maintained, and included within the source control.

Want to use code from this post?
Check out the license.

Source: www.bennadel.com