Setting And Clearing Nullable Values In A Data Access Layer In ColdFusion

CREATE TABLE `task` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`description` varchar(255) NOT NULL,
	`isComplete` tinyint unsigned NOT NULL,
	`createdAt` datetime NOT NULL,
	`completedAt` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

<cfscript>

	taskGateway = new TaskGateway();

	// Create a new task as a pending (ie, not yet completed) task.
	taskID = taskGateway.createTask(
		description = "Clean up Xmas tree",
		isComplete = false,
		createdAt = now(),
		completedAt = taskGateway.NULL_DATE
	);

	// MARK AN EXISTING TASK AS COMPLETE - that's easy, we just pass-in the date at which
	// we want the task to be known as completed.
	taskGateway.updateTask(
		id = taskID,
		isComplete = true,
		completedAt = now()
	);

	// MARK AN EXISTING TASK AS INCOMPLETE! That's where things get tricky - how do we
	// differentiate between OMITTING the "completedAt" argument (ie, we don't want to
	// update it) vs. clearing the "completedAt" field (ie, setting it to NULL). The
	// "magic date" makes this simple!
	taskGateway.updateTask(
		id = taskID,
		isComplete = false,
		completedAt = taskGateway.NULL_DATE
	);

</cfscript>

component
	output = false
	hint = "I provide data-access methods for the task model."
	{

	// This date is provided in order to make it easier to pass-in and handle a "NULL"
	// date for "completedAt". Instead of dealing with confusing optional arguments, we
	// can use a "magic date" to specify null. This allows us to avoid confusing optional
	// arguments and keep our SQL query logic simple.
	this.NULL_DATE = createDate( 100, 1, 1 );

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I create a new task and return the generated ID.
	*/
	public numeric function createTask(
		required string description,
		required boolean isComplete,
		required date createdAt,
		required date completedAt
		) {

		```
		<cfquery name="local.results" result="local.metaResults">
			/* DEBUG: taskGateway.createTask(). */
			INSERT INTO
				task
			SET
				description = <cfqueryparam value="#description#" sqltype="varchar" />,
				isComplete = <cfqueryparam value="#isComplete#" sqltype="tinyint" />,
				createdAt = <cfqueryparam value="#createdAt#" sqltype="timestamp" />,
				completedAt = <cfqueryparam value="#completedAt#" sqltype="timestamp"
					null="#isNullCompletedAt( completedAt )#"
				/>
			;
		</cfquery>
		```

		return( val( metaResults.generatedKey ) );

	}

	// ... truncated for demo ...

	/**
	* I update the task with the given ID.
	*/
	public void function updateTask(
		required numeric id,
		string description,
		boolean isComplete,
		date completedAt
		) {

		```
		<cfquery name="local.results" result="local.metaResults">
			/* DEBUG: taskGateway.updateTask(). */
			UPDATE
				task t
			SET
				<cfif arguments.keyExists( "description" )>
					t.description = <cfqueryparam value="#description#" sqltype="varchar" />,
				</cfif>

				<cfif arguments.keyExists( "isComplete" )>
					t.isComplete = <cfqueryparam value="#isComplete#" sqltype="tinyint" />,
				</cfif>

				<cfif arguments.keyExists( "completedAt" )>
					t.completedAt = <cfqueryparam value="#completedAt#" sqltype="timestamp"
						null="#isNullCompletedAt( completedAt )#"
					/>,
				</cfif>

				t.id = t.id
			WHERE
				t.id = <cfqueryparam value="#id#" sqltype="bigint" />
			;
		</cfquery>
		```

	}

	// ---
	// PRIVATE METHODS.
	// ---

	/**
	* I determine if the given completedAt value is the "magic" NULL_DATE value.
	*/
	private boolean function isNullCompletedAt( required date value ) {

		return( value == this.NULL_DATE );

	}

}