Experimenting With Compress() And Uncompress() In MySQL

CREATE TABLE `error_log` (
	`id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
	`message` VARCHAR(100) NOT NULL,
	`errorData` BLOB NOT NULL,
	`metaData` BLOB NOT NULL,
	`ipAddress` VARCHAR(35) NOT NULL,
	`createdAt` DATETIME NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<!---
	Table structure for the error log:

	CREATE TABLE `error_log` (
		`id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
		`message` VARCHAR(100) NOT NULL,
		`errorData` BLOB NOT NULL,
		`metaData` BLOB NOT NULL,
		`ipAddress` VARCHAR(35) NOT NULL,
		`createdAt` DATETIME NOT NULL,
		PRIMARY KEY (`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--->
<cfcomponent
	output="false"
	hint="I provide a repository for error logging.">

	<!--- I get the most recent errors in the error log. --->
	<cffunction name="getRecentErrors" access="public" returntype="query" output="false">

		<!--- Define arguments. --->
		<cfargument name="count" type="numeric" required="false" default="10" />

		<!---
			When getting the error log items, we have to be sure to decompress the error
			data, returning it as a string.
		--->
		<cfquery name="local.result" datasource="testing">
			SELECT
				e.id,
				e.message,

				<!---
					The compressed values are being stored as Binary (Blob). To make
					these easier to consume, we're going to convert the Blob values to
					strings on the way out.
				--->
				CONVERT( UNCOMPRESS( e.errorData ) USING 'utf8' ) AS errorData,
				CONVERT( UNCOMPRESS( e.metaData ) USING 'utf8' ) AS metaData,

				e.ipAddress,
				e.createdAt
			FROM
				error_log e
			ORDER BY
				e.id DESC
			LIMIT
				<cfqueryparam value="#count#" cfsqltype="cf_sql_integer" />
		</cfquery>

		<cfreturn result />

	</cffunction>


	<!---
		I log the given error and related data to the error log. The errorData and
		metaData will be serialized as JSON (JavaScript Object Notation) when persisted.
		As such, you need to make sure the given data can be properly serialized in
		ColdFusion using serializeJson().
	--->
	<cffunction name="logError" access="public" returntype="void" output="false">

		<!--- Define arguments. --->
		<cfargument name="message" type="string" required="true" />
		<cfargument name="errorData" type="any" required="true" />
		<cfargument name="metaData" type="any" required="true" />
		<cfargument name="ipAddress" type="string" required="true" />
		<cfargument name="createdAt" type="date" required="false" default="#now()#" />

		<!--- Clean up any data restrictions to prevent Database errors. --->
		<cfset message = left( message, 100 ) />
		<cfset ipAddress = left( ipAddress, 35 ) />

		<!--- Serialize the data structures for storage. --->
		<cfset errorData = serializeJson( errorData ) />
		<cfset metaData = serializeJson( metaData ) />

		<!---
			When storing error, we're going to compress the long-text values of the
			error-data and the meta-data. These don't get queried a lot, so there
			shouldn't be too much of a performance concern.
		--->
		<cfquery name="local.result" datasource="testing">
			INSERT INTO error_log
			(
				message,
				errorData,
				metaData,
				ipAddress,
				createdAt
			) VALUES (
				<!--- message. --->
				<cfqueryparam value="#message#" cfsqltype="cf_sql_varchar" />,

				<!--- errorData. --->
				COMPRESS( <cfqueryparam value="#errorData#" cfsqltype="cf_sql_longvarchar" /> ),

				<!--- metaData. --->
				COMPRESS( <cfqueryparam value="#metaData#" cfsqltype="cf_sql_longvarchar" /> ),

				<!--- ipAddress. --->
				<cfqueryparam value="#ipAddress#" cfsqltype="cf_sql_varchar" />,

				<!--- createdAt. --->
				<cfqueryparam value="#createdAt#" cfsqltype="cf_sql_timestamp" />
			)
		</cfquery>

	</cffunction>

</cfcomponent>

<cfscript>

	logger = new Logger();

	// For the sake of the demo, we're going to create an error on each load of the page.
	try {

		// Divide by zero - are you mad, man?!
		result = 50 / 0;

	} catch ( any error ) {

		// Log the error - here we can pass in the error object as well as any arbitrary
		// data that may be relevent, as part of the metaData object.
		logger.logError(
			error.message,
			error,
			{
				user = "Ben Nadel",
				requestData = getHttpRequestData(),
				cgi = cgi
			},
			cgi.remote_addr
		);

	}

	// Get recent errors to display in our error log. When we get the data back from the
	// database, it will still be serialized; we will have to deserialize it ourselves
	// when outputtting.
	recentErrors = logger.getRecentErrors();

</cfscript>

<!--- Reset the output buffer. --->
<cfcontent type="text/html; charset=utf-8" />

<cfoutput>

	<!doctype html>
	<html>
	<head>
		<meta charset="utf-8" />

		<title>
			Experimenting With MySQL Compress() And Uncompress() Methods In ColdFusion
		</title>
	</head>
	<body>

		<h1>
			Experimenting With MySQL Compress() And Uncompress() Methods In ColdFusion
		</h1>

		<table width="100%" border="1" cellpadding="5" cellspacing="1">
			<thead>
				<th>
					ID
				</th>
				<th>
					Created
				</th>
				<th>
					IP Address
				</th>
				<th>
					Message
				</th>
				<th width="50%">
					Error Data
				</th>
				<th width="50%">
					Meta Data
				</th>
			</thead>
			<tbody>
				<cfloop query="recentErrors">

					<tr valign="top">
						<td>
							#recentErrors.id#
						</td>
						<td style="white-space: nowrap ;">
							#dateFormat( recentErrors.createdAt, "Mmm d" )# at
							#timeFormat( recentErrors.createdAt, "HH:mm:ss" )#
						</td>
						<td>
							#recentErrors.ipAddress#
						</td>
						<td style="white-space: nowrap ;">
							#htmlEditFormat( recentErrors.message )#
						</td>
						<td>
							<!--- Deserialize the data. --->
							<cfdump
								var="#deserializeJson( recentErrors.errorData )#"
								expand="false"
								/>
						</td>
						<td>
							<!--- Deserialize the data. --->
							<cfdump
								var="#deserializeJson( recentErrors.metaData )#"
								expand="false"
								/>
						</td>
					</tr>

				</cfloop>
			</tbody>
		</table>

	</body>
	</html>

</cfoutput>