File SQL Assertions

A feature added in 2.0.24 is the ability to perform a sql assertion on a file. A minimal file assertion looks like this:

assert(file: 'TEST');

Which means use local assertion (expected) file named 'TEST', and a remote target file named 'TEST', using the assertion mode 'equals', and reference-file-type named 'TEST.fml'.

You can now say use a custom sql statement or sql script to process the source (actual) file and output in the target reference file format for comparison:

@Test
goodSqlExtract()
{
    assert(
        file: 'delimited_target_sql',
        sql: 'SELECT POS_NAME, INPUT, ISNUM FROM $file-table-name',
        source-reference-file-type: 'delimited_target_sample.out',
        target-reference-file-type: 'delimited_target_sql',
        target-file-name: 'delimited_target_sample.out'
    );
}

@Test
goodSqlScriptExtract()
{
    assert(
        file: 'delimited_target_sql',
        sql-script: 'extract',
        source-reference-file-type: 'delimited_target_sample.out',
        target-reference-file-type: 'delimited_target_sql',
        target-file-name: 'delimited_target_sample.out'
    );
}

In both of these situations, the source-reference-file-type (the actual file) is loaded using the delimited_target_sample.out.fml format, then the sql statement is applied to extract those contents into a local file which matches the target-reference-file-type (expected), then the two are compared.

Note the FROM clause on the SQL statement. The table name is the special identifier $file-table-name. This must be used because the table is virtual and does not have any guaranteed or predictable name.

If include / exclude lists are needed, they can be used like this:

@Test
goodScriptExtractWithExcludeColumns()
{
    assert(
        file: 'delimited_target_sql_excl',
        sql-script: 'extract',
        column-list-mode: 'exclude',
        column-list: ['INPUT'],
        source-reference-file-type: 'delimited_target_sample.out',
        target-reference-file-type: 'delimited_target_sql',
        target-file-name: 'delimited_target_sample.out'
    );
}

@Test
goodScriptExtractWithIncludeColumns()
{
    assert(
        file: 'delimited_target_sql_excl',
        sql-script: 'extract',
        column-list-mode: 'include',
        column-list: ['POS_NAME', 'ISNUM'],
        source-reference-file-type: 'delimited_target_sample.out',
        target-reference-file-type: 'delimited_target_sql',
        target-file-name: 'delimited_target_sample.out'
    );
}

In this case, the sql is used as a direct extract from the source (actual) reference file type, and the output is then transformed using the exclude / include lists, and that result is used to create a subset of the target reference file type and the files compared. If the target-reference-file-type had not been specified, the output of the sql script and the column list would have to exactly match the assertion file.