Experimenting With Lazy Queries And Streaming CSV (Comma Separated Value) Data In Lucee CFML 5.3.7.47

<cfscript>

	// Allow lazy/non-lazy nature of query to be driven via URL for demo.
	param name="url.lazy" type="boolean" default=true;

	startedAt = getTickCount();

	// Unlike a normal CFQuery execution, a LAZY query does not load all records into
	// memory at one time. Instead, it keeps a pointer to the database and reads results
	// as necessary from the database. A LAZY query also DOES NOT WRAP the results, which
	// means that we are getting the native Java values and cannot use the traditional
	// CFML "member methods".
	```
	<cfquery name="entries" datasource="testing" lazy="#url.lazy#">
		SELECT
			l.id,
			l.userID,
			l.eventType,
			l.eventMetadata,
			l.ipAddress,
			l.createdAt,
			l.isSuspicious -- May be NULL.
		FROM
			audit_log l
		ORDER BY
			l.id DESC
	</cfquery>
	```

	// NOTE: I am using a Try/Catch so that I can make sure that I always call the
	// queryClose() function at the end. I am NOT SURE if this is strictly necessary. The
	// docs say this is necessary; but, I think it might only be necessary if I don't
	// read all the records (since the query seems to auto-close once all the records
	// have been returned)????
	try {

		// Since we're not loading all of the database records into memory at once, we
		// want to incrementally stream the response to the user. Otherwise, we end up
		// having to aggregate all the records in memory anyway (which counteracts the
		// lazy nature of our query). Under the surface of each ColdFusion page request
		// is a Java Servlet Response. By, dipping down into the Java layer, we can get
		// access to the actual Binary Output Stream to which we can write raw binary
		// content. In this case, we're going to create a DataOutputStream which will
		// allow us to write our serialized CSV String data to the underlying ColdFusion
		// Byte output stream.
		cfmlResponse = getPageContext().getResponse();
		cfmlOutputStream = cfmlResponse.getOutputStream();
		csvOutputStream = createObject( "java", "java.io.DataOutputStream" )
			.init( cfmlOutputStream )
		;

		fieldDelimiter = chr( 9 ); // Tab.
		rowDelimiter = chr( 10 ); // Newline.

		// NOTE: Unlike in the previous blog post (on CSV generation), where I was using
		// the CFContent tag, we don't have access to the body content at this point
		// (since we're going to stream it). As such, we have to explicitly provide the
		// CFHeader for Content-Type. This also means that we DO NOT have the "Content-
		// Length" ahead of time - I am not sure if this will cause an issue with any
		// browsers????
		header
			name = "Content-Type"
			value = "text/csv; charset=utf-8"
		;
		header
			name = "Content-Disposition"
			value = getContentDisposition( "audit-log-" & now().dateFormat( "yyyy-mm-dd" ) & ".csv" )
		;

		// Write the header-row to the CSV output stream.
		csvOutputStream.writeBytes(
			encodeCsvDataRow(
				row = [
					"ID",
					"User ID",
					"Event Type",
					"Event Metadata",
					"IP Address",
					"Created At",
					"Is Suspicious"
				],
				fieldDelimiter = fieldDelimiter
			)
		);
		csvOutputStream.writeBytes( rowDelimiter );
		csvOutputStream.flush();
		cfmlOutputStream.flush();

		// As we loop over the LAZY query, Lucee will read records from the database
		// pointer as needed. IN THEORY, this should keep the memory consumption lower,
		// (though I was not able to see a meaningful different in FusionReactor).
		// --
		// NOTE: I am not 100% clear on whether or not we can use a FOR-IN loop. As such,
		// I'm using the native CFLoop tag.
		loop query = entries {

			csvOutputStream.writeBytes(
				encodeCsvDataRow(
					row = [
						entries.id,
						entries.userID,
						entries.eventType,
						entries.eventMetadata,
						entries.ipAddress,
						// CAUTION: Since LAZY queries do not wrap the results, we DO NOT
						// GET to use MEMBER METHODS on the query column values - these
						// are the raw JAVA types. As such, we have to use the built-in
						// functions, instead, when formatting special values.
						dateTimeFormat( entries.createdAt, "medium" ),
						// If isSuspicious is NULL, leave it blank - we only want to
						// populate explicit "Yes" values.
						( val( entries.isSuspicious ) ? "Yes" : "" )
					],
					fieldDelimiter = fieldDelimiter
				)
			);
			csvOutputStream.writeBytes( rowDelimiter );
			csvOutputStream.flush();
			cfmlOutputStream.flush();

		}

	} finally {

		// For lazy queries, I THINK we have to explicitly close them in order to free up
		// the memory.
		if ( url.lazy ) {

			queryClose( entries );

		}

		// Close out output streams, and make sure no other data is sent to the browser.
		csvOutputStream?.close();
		cfmlOutputStream?.close();

	}

	systemOutput( "Runtime( lazy = #url.lazy# ): #( getTickCount() - startedAt )#", true, true );

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I encode the given collection of values for use in a CSV (Comma Separated Value)
	* row. The values are then serialized as a list using the given delimiter.
	* 
	* @row I am the collection being encoded.
	* @fieldDelimiter I am the list delimiter used in the encoding.
	*/
	private string function encodeCsvDataRow(
		required array row,
		required string fieldDelimiter
		) {

		var encodedRow = row
			.map( encodeCsvDataField )
			.toList( fieldDelimiter )
		;

		return( encodedRow );

	}


	/**
	* I encode the given value for use in a CSV (Comma Separated Value) field. The value
	* is wrapped in double-quotes and any embedded quotes are "escaped" using the
	* standard "doubling" syntax.
	* 
	* @value I am the field value being encoded.
	*/
	private string function encodeCsvDataField( required string value ) {

		var encodedValue = replace( value, """", """""", "all" );

		return( """#encodedValue#""" );

	}


	/**
	* I get the content-disposition (attachment) header value for the given filename.
	* 
	* @filename I am the name of the file to be saved on the client.
	*/
	private string function getContentDisposition( required string filename ) {

		var encodedFilename = encodeForUrl( filename );

		return( "attachment; filename=""#encodedFilename#""; filename*=UTF-8''#encodedFilename#" );

	}

</cfscript>