An Experiment In Using An Embedded Apache Derby Database To Power ColdFusion Query-Of-Queries

<cfoutput>

	<!--- Query for all the friends. --->
	<cfquery name="friends" datasource="testing">
		SELECT
			f.id,
			f.name
		FROM
			friend f
		ORDER BY
			f.name ASC
	</cfquery>


	ORIGINAL NAMES:<br />

	<!--- Output the original names. --->
	<cfloop query="friends">

		#friends.name#<br />

	</cfloop>


	<!--- ------------------------------------------------- --->
	<!--- ------------------------------------------------- --->
	<br />
	<!--- ------------------------------------------------- --->
	<!--- ------------------------------------------------- --->


	<!---
		Now, perform a "query of queries" on the above object.
		This will actually run in an embedded Apache Derby database.
	--->
	<cf_qoq name="friends">
		INSERT INTO {friends}
		(
			id,
			name
		) VALUES (
			6,
			'Jennifer'
		);

		INSERT INTO {friends}
		(
			id,
			name
		) VALUES (
			7,
			'Kathleen'
		);

		UPDATE
			{friends}
		SET
			name = 'Sarah'
		WHERE
			name = 'Sara'
		;

		DELETE FROM
			{friends}
		WHERE
			name LIKE '%a'
		;

		SELECT
			f.id,
			f.name
		FROM
			{friends} f
		ORDER BY
			f.name ASC
		;
	</cf_qoq>


	NEW NAMES:<br />

	<!--- Output the updated names. --->
	<cfloop query="friends">

		#friends.name#<br />

	</cfloop>


</cfoutput>

<!--- Check to see which mode the tag is executing. --->
<cfif (thisTag.executionMode eq "start")>


	<!---
		Make sure we have a name to work with - these query of
		queries are NON-destructive. As such, they have to be moved
		into another query variable.
	--->
	<cfparam
		name="attributes.name"
		type="variablename"
		/>

	<!--- Define the DSN to be used for this scratch database. --->
	<cfset dsn = "queryofqueries" />


<cfelse>


	<!--- Get the SQL from the generated content. --->
	<cfset rawSql = thisTag.generatedContent />

	<!---
		Gather the table names that were referenced in the SQL code
		- we will have to convert these to temp tables.
	--->
	<cfset tableNames = reMatch(
		"\{[^}]+\}",
		rawSql
		) />

	<!--- Make sure that at least one table was referenced. --->
	<cfif !arrayLen( tableNames )>

		<cfthrow
			type="InvalidSQL"
			message="Your SQL does not reference any tables."
			detail="SQL: [#rawSql#]."
			/>

	</cfif>

	<!---
		Now, loop over the table names to clean them up and to make
		sure they actually exist in the caller scope.
	--->
	<cfloop
		index="index"
		from="1"
		to="#arrayLen( tableNames )#"
		step="1">

		<!--- Clean up the table name. --->
		<cfset name = reReplace(
			tableNames[ index ],
			"[{}]+",
			"",
			"all"
			) />

		<!--- Make sure the query variable actually exists. --->
		<cfif !structKeyExists( caller, name )>

			<cfthrow
				type="InvalidTableReference"
				message="The table [#name#] could not be found."
				detail="The query variable with the lable [#name#] could not be accessed."
				/>

		</cfif>

		<!--- Add the clean table name back into the array. --->
		<cfset tableNames[ index] = name />

	</cfloop>

	<!---
		Now that we've validated the tables, we have to loop over
		each one to create it in our embedded derby database. Let's
		create a mapping of real names to temp tables.
	--->
	<cfset tempTables = {} />


	<!--- Wrap all the table generation in a transaction. --->
	<cftransaction>


		<!---
			Loop over the tables to create and populate the
			temp counterparts.
		--->
		<cfloop
			index="name"
			array="#tableNames#">

			<!--- Generate a random name for the temp table. --->
			<cfset tempName = replace(
				("TABLE-" & createUUID()),
				"-",
				"",
				"all"
				) />

			<!--- Store the temp table in our map. --->
			<cfset tempTables[ name ] = tempName />

			<!--- Get a reference to the query object itself. --->
			<cfset queryObject = caller[ name ] />

			<!---
				Get the meta data for the query; this will hopefully
				give us the columns and data types we need to construct
				a true SQL table.
			--->
			<cfset metaData = getMetaData( queryObject ) />

			<!--- Create our temp table. --->
			<cfquery name="createTable" datasource="#dsn#">
				CREATE TABLE #tempName# (

					<!---
						Loop over the meta data to design the columns
						of the temp table we need to create.
					--->
					<cfloop
						index="columnIndex"
						from="1"
						to="#arrayLen( metaData )#"
						step="1">

						<!--- Check for the need for a comma. --->
						<cfif (columnIndex gt 1)>
							,
						</cfif>

						<!--- Define the column name. --->
						#metaData[ columnIndex ].name#

						<!--- Define the column type. --->
						<cfif reFindNoCase( "int|double|float", metaData[ columnIndex ].typeName )>

							DOUBLE

						<cfelse>

							<!---
								By default, the column will be a
								varchar. I wanted to use a LONG
								varchar, but those don't allow any
								comparisons to be executed.
							--->
							VARCHAR( 3000 )

						</cfif>

					</cfloop>

				)
			</cfquery>

			<!---
				Now that we have created the table, we have to
				populate it with the original query data. We have to
				execute these
			--->
			<cfloop query="queryObject">

				<!--- Add the row of data. --->
				<cfquery name="populateTable" datasource="#dsn#">
					INSERT INTO #tempName# VALUES (

						<!---
							Loop over the column names to select the
							column values. It is important that these
							columns be in the same order as the table
							creation since we are not defining the
							name within the INSERT statement.
						--->
						<cfloop
							index="columnIndex"
							from="1"
							to="#arrayLen( metaData )#"
							step="1">

							<!--- Check for the need for a comma. --->
							<cfif (columnIndex gt 1)>
								,
							</cfif>

							<!--- Insert the value. --->
							<cfif reFindNoCase( "int|double|float", metaData[ columnIndex ].typeName )>

								<!--- Numeric. --->
								#queryObject[ metaData[ columnIndex ].name ][ queryObject.currentRow ]#

							<cfelse>

								<!--- String. --->
								'#queryObject[ metaData[ columnIndex ].name ][ queryObject.currentRow ]#'

							</cfif>

						</cfloop>

					)
				</cfquery>

			</cfloop>

		</cfloop>


		<!---
			At this point, we've created all of our temp tables and
			populated them with the query object data. Now, we have
			to take the SQL from this tag and execute it. Derby does
			not allow multi-queries; as such, we have to split the
			raw sql on the ; characrter (proof of concept).
		--->


		<!---
			Before we run the sql, let's replace the tables names
			with the temp table names.
		--->
		<cfloop
			index="name"
			array="#tableNames#">

			<cfset rawSql = replace(
				rawSql,
				"{#name#}",
				tempTables[ name ],
				"all"
				) />

		</cfloop>

		<!---
			Now, let's loop over the portions of the SQL to execute
			them against the temp database.
		--->
		<cfloop
			index="sqlPortion"
			list="#rawSql#"
			delimiters=";">

			<!--- Make sure the SQL portion has content. --->
			<cfif len( trim( sqlPortion ) )>

				<!--- Execute the sql. --->
				<cfquery name="lastResult" datasource="#dsn#">
					#preserveSingleQuotes( sqlPortion )#
				</cfquery>

			</cfif>

		</cfloop>


		<!--- Clear the generated content. --->
		<cfset thisTag.generatedContent = "" />

		<!---
			Check to see if we have a value to store into our
			result variable.
		--->
		<cfif structKeyExists( variables, "lastResult" )>

			<!---
				Store the last result. When doing this, use the
				duplicate() method - this will fix a bug with CFDump
				and identity columns on our temp queries.
			--->
			<cfset caller[ attributes.name ] = duplicate( lastResult ) />

		</cfif>


		<!---
			Roll back all of the database updates. This will keep our
			scratch database clean (including table creation).
		--->
		<cftransaction action="rollback" />

	</cftransaction>


</cfif>