CSV To CTE Transformer In Angular 18

ID,EMAIL
1,ben@example.com
2,laura@example.com
3,smith@example.com

WITH
	derived (
		id,
		email
	) AS (
		VALUES
			ROW ( 1, 'ben@example.com' ),
			ROW ( 2, 'laura@example.com' ),
			ROW ( 3, 'smith@example.com' )
	)
SELECT
	d.id,
	d.email
FROM
	derived d
;

type FieldSettings = FieldSetting[];

interface FieldSetting {
	fieldIndex: number;
	columnName: string;
	exampleValue: string;
	includeInCte: boolean;
}

<p>
	This takes a CSV (comma-separated values) payload and transforms it into a custom CTE
	(common table expression) that can be used in MySQL 8.
</p>

<p class="flex-row">
	<button
		(click)="applySampleTemplate( sampleTemplate1 )"
		class="sample">
		Try with sample data
	</button>
	<button
		(click)="applySampleTemplate( sampleTemplate2 )"
		class="sample">
		Try less data
	</button>
</p>

<form>

	<h2>
		Step 1: Paste CSV Text
	</h2>

	<p class="csv">
		<textarea
			name="csvInput"
			[(ngModel)]="csvInput"
			(ngModelChange)="processInput()"
			class="csv__input"
		></textarea>

		<label class="csv__header flex-row">
			<input
				type="checkbox"
				name="ignoreFirstRow"
				[(ngModel)]="ignoreFirstRow"
				(ngModelChange)="processInput()"
			/>
			Ignore first row (column headers).
		</label>
	</p>

	<h2>
		Step 2: Configure Field-to-Column Mappings
	</h2>

	@for ( fieldSetting of fieldSettings ; track $index ) {

		<p class="field">
			<span class="flex-row">
				<input
					type="text"
					name="fieldName{{ $index }}"
					[(ngModel)]="fieldSetting.columnName"
					(ngModelChange)="renderCte()"
					class="field__name"
				/>
				<label class="flex-row">
					<input
						type="checkbox"
						name="includeField{{ $index }}"
						[(ngModel)]="fieldSetting.includeInCte"
						(ngModelChange)="renderCte()"
					/>
					include in SQL.
				</label>
			</span>
			<span class="field__example">
				Example: {{ fieldSetting.exampleValue }}
			</span>
		</p>

	}

	<h2>
		Step 3: Copy SQL Statement For Fun And Profit
	</h2>

	<p class="cte">
		<textarea
			name="cteOutput"
			[(ngModel)]="cteOutput"
			class="cte__output"
		></textarea>
	</p>

</form>


<!-- Sample CSV data that be loaded into the demo. -->
<template #sampleTemplate1>
	... truncated for demo ...
</template>
<template #sampleTemplate2>
	... truncated for demo ...
</template>

// Import vendor modules.
import { Component } from "@angular/core";
import { FormsModule } from "@angular/forms";
import { inject } from "@angular/core";

// Import app modules.
import { CsvParser } from "./csv-parser";
import { CsvRow } from "./csv-parser";
import { CsvRows } from "./csv-parser";

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

type FieldSettings = FieldSetting[];

interface FieldSetting {
	fieldIndex: number;
	columnName: string;
	exampleValue: string;
	includeInCte: boolean;
}

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

@Component({
	selector: "app-root",
	standalone: true,
	imports: [
		FormsModule
	],
	styleUrl: "./app.component.less",
	templateUrl: "./app.component.html"
})
export class AppComponent {

	private csvParser = inject( CsvParser );

	public csvInput = "";
	public csvRows: CsvRows = [];
	public ignoreFirstRow = false;
	public fieldSettings: FieldSettings = [];
	public cteOutput = "";

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

	/**
	* I apply and process the given sample data template reference.
	*/
	public applySampleTemplate( sampleTemplate: HTMLTemplateElement ) {

		this.csvInput = sampleTemplate.content.textContent!.trim();
		this.ignoreFirstRow = true;
		this.processInput();

	}


	/**
	* I process the CSV input and generate the CTE output.
	*/
	public processInput() {

		this.csvRows = this.csvParser.parse( this.csvInput );
		this.fieldSettings = [];
		this.cteOutput = "";

		// If we have no rows, there's nothing else to process.
		if ( ! this.csvRows.length ) {
			
			return;

		}

		// If we only have one row and we want to ignore it, there's nothing else to
		// process.
		if ( this.ignoreFirstRow && ( this.csvRows.length === 1 ) ) {

			return;

		}

		// If we ignore the first row, it means that the first row is the field headers.
		// We can therefore use the result of the splice() operation to default the names
		// of the fields in the SQL statement.
		var splicedRow: CsvRow = [];

		if ( this.ignoreFirstRow ) {

			splicedRow = this.csvRows.splice( 0, 1 )
				.at( 0 )!
				.map( ( value ) => value.replace( /\s+/g, "" ) )
			;

		}

		// We're going to assume that the row structures are uniform and use the first row
		// as a representation of the overall row structure. This will be used to define
		// the default field names (giving precedence to the spliced row above).
		this.fieldSettings = this.csvRows[ 0 ].map(
			( fieldValue, i ) => {

				return {
					fieldIndex: i,
					columnName: ( splicedRow[ i ] || `f${ i + 1 }` ),
					exampleValue: fieldValue,
					includeInCte: true
				};

			}
		);

		this.renderCte();

	}


	/**
	* I render the CTE output based on the current view-model.
	*/
	public renderCte() {

		// Filter down to the fields we want to include in the CTE rendering.
		var settings = this.fieldSettings.filter(
			( fieldSetting ) => {

				return fieldSetting.includeInCte;

			}
		);

		// If none of the field are to be included, there's nothing left to process.
		if ( ! settings.length ) {

			this.cteOutput = "";
			return;

		}

		this.cteOutput = `
			WITH
				derived (
					${ derivedColumns( settings ) }
				) AS (
					VALUES
						${ derivedRows( settings, this.csvRows ) }
				)
			SELECT
				${ selectColumns( settings ) }
			FROM
				derived d
			;
		`;

		// Our string template approach added a bunch of extra indentation on each line.
		// Let's remove that indentation to create a properly formatted SQL statement.
		this.cteOutput = this.cteOutput
			.trim()
			.replace( /^\t{3}/gm, "" )
		;

		// Function-local helper methods.

		function derivedColumns( settings: FieldSettings ) {

			return settings
				.map(
					( setting ) => {

						return `\`${ setting.columnName }\``;

					}
				)
				.join( ",\n					" )
			;

		}

		function derivedRows( settings: FieldSettings, rows: CsvRows ) {

			return rows
				.map(
					( row ) => {

						return `ROW ( ${ derivedRow( settings, row ) } )`;

					}
				)
				.join( ",\n						" )
			;

		}

		function derivedRow( settings: FieldSettings, row: CsvRow ) {

			return settings
				.map(
					( setting ) => {

						return `'${ row[ setting.fieldIndex ].replace( /'/g, "''" ) }'`;

					}
				)
				.join( ", " )
			;

		}

		function selectColumns( settings: FieldSettings ) {

			return settings
				.map(
					( setting ) => {

						return `d.\`${ setting.columnName }\``;

					}
				)
				.join( ",\n				" )
			;

		}

	}

}

// Import vendor modules.
import { Injectable } from "@angular/core";

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

export type CsvRows = CsvRow[];
export type CsvRow = string[];

// This is the native match that exec() will return.
type ExecMatch = RegExpExecArray | null;
// This is the translated match that we use internally.
type ParserMatch = FieldMatch | null;
// This is the translated match interface.
interface FieldMatch {
	value: string;
	isQuotedValue: boolean;
	isNakedvalue: boolean;
	delimiter: string;
	isFieldDelimiter: boolean;
	isRowDelimiter: boolean;
	isEndOfInput: boolean;
}

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

@Injectable({
	providedIn: "root"
})
export class CsvParser {

	/**
	* I parse the given input into a CSV rows result.
	*/
	public parse( input: string ) : CsvRows {

		// Note: This is a judgement call, but stripping off any trailing newlines at the
		// end of the input seems to result in a more meaningful match. Otherwise you can
		// end up with some empty row at the end depending on the algorithm.
		input = input.replace( /[\r\n]+$/, "" );

		if ( ! input ) {

			return [];

		}

		var pattern = this.createPattern();
		var match: ParserMatch;
		var row: CsvRow = [];
		var rows: CsvRows = [row];

		while ( match = this.transformMatch( pattern.exec( input ) ) ) {

			row.push( match.value );

			// Because our pattern can match an empty field followed by the end-of-input,
			// there's a zero-length match at the end of the input sequence that never
			// moves the lastIndex of the exec() function forward. As such, we have to
			// explicitly break out of the loop once we've reached the end.
			if ( match.isEndOfInput ) {

				break;

			}

			if ( match.isRowDelimiter ) {

				row = [];
				rows.push( row );

			}

		}

		return rows;

	}

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

	/**
	* I create the RegExp instance for matching fields and delimiters.
	*/
	private createPattern() : RegExp {

		// Note: The String.raw tag will return the embedded escape sequences (\r\n) as
		// string literals instead of interpreting them as escape sequences.
		var source = String.raw`
			(?:
				"([^"]*(?:""[^"]*)*)"    # Quoted field value.
				|
				([^",\r\n]*)             # Naked field value.
			)
			(,|\r\n?|\n|$)               # Field delimiter.
		`;

		// The native RegExp doesn't support the "verbose" flag. As such, let's strip out
		// any comments and whitespace in the current source.
		source = source
			.replace( /# [^\r\n]*/g, " " )
			.replace( /\s+/g, "" )
		;

		return new RegExp( source, "gy" );

	}


	/**
	* I transform the native exec() match into a local parser match.
	*/
	private transformMatch( match: ExecMatch ) : ParserMatch {

		if ( ! match ) {

			return null;

		}

		var value = ( match[ 1 ] || match[ 2 ] || "" );
		var isQuotedValue = ( match[ 1 ] !== undefined );
		var isNakedvalue = ! isQuotedValue;
		var delimiter = match[ 3 ];
		var isFieldDelimiter = ( delimiter === "," );
		var isRowDelimiter = ! isFieldDelimiter;
		var isEndOfInput = ! delimiter;

		// If the field value is quoted, canonicalize any embedded quotes.
		if ( isQuotedValue ) {

			value = value.replace( /""/g, '"' );

		}

		return {
			value,
			isQuotedValue,
			isNakedvalue,
			delimiter,
			isFieldDelimiter,
			isRowDelimiter,
			isEndOfInput
		};

	}

}