SQL Creation Guide
Reference Replacer

Reference Replacer SQL queries must follow a set format. This guide explains how to build a valid SQL query.

Overview of Single Query Mode

To be efficient, the Ref Replacer provides a Single Query mode which will attempt to retrieve all references for matching files in one query, rather than a query per file. This means the application needs to link the SQL results back to the originating file, without knowing the file name. For example, imagine the application has matched 3 files, but only two results are returned by the SQL query. How can the application link those results back to the right file?

When the SQL query is built, the application creates a map of the matching references that are inserted into the WHERE clause. Your query must return these references in the same order which allows the application to link the results back to the file without the SQL query worrying about the file name.

Query Return Fields

In order for the application to understand your query results, the return fields must be aliased. Lets say your pattern will match files named abcde.txt, returning 'bd' as the references:

^a(b)c(d)e.txt$

Your SQL query would need to look like:

SELECT
    source_ref AS 'src_0',
    source_ref2 AS 'src_1',
    new_ref AS 'sql_0',
    new_ref2 AS 'sql_1'
FROM table
WHERE
    source_ref = '{src_0}' AND
    source_ref2 = '{src_1}'

Here the src_0 and src_1 in the WHERE clause are replaced with b and d respectively. The application uses those in the map, and when the query returns b and d in src_0 and src_1, they are linked back to file, allowing sql_0 and sql_1 to replace the references in the output file name.

This also means one row of results can be used to process multiple files, for e.g. if you had another file abcde.jpg, the references are the same, so the one row will link to both files.

When the query is run in single query mode, it will look like:

SELECT
    source_ref AS 'src_0',
    source_ref2 AS 'src_1',
    new_ref AS 'sql_0',
    new_ref2 AS 'sql_1'
FROM table
WHERE
    (source_ref = 'b' AND source_ref2 = '{d') OR
    (source_ref = 'e' AND source_ref2 = '{f')