MockFileDatabase does not normalise SQL statements

[azizmwondha]

5921 removed line trimming from the MockFileDatabase.

This however raises other issues:

1. query matching becomes inconsistent because in SQL

select a
from (select 1 as a);

is identical to

select a from (select 1 as a);

2. query matching with Java text blocks always fails because

"select a
from (select 1 as a);"

and

"""
select a
from (select 1 as a);"""

are treated differently. Text blocks make SQL statements look more human friendly in code editors, but they become impossible to use with jOOQ.

I'd like to propose another way of parsing lines.

After the SQL statement has been read to the end, then:

• replace all \r and \n with a single space
• replace all \t with a single space
• replace all multiple spaces to a single space
• trim the resulting String

The same logic should then also be applied to MockExecuteContext.sql().

This will ensure that all SQL statements have the same syntax and will vastly increase the likeiyhood of finding SQL matches during execution.

[lukaseder]

Thanks for your suggestion. I don't think this is something we should invest time on. Sooner rather than later, someone will suggest supporting structurally equivalent SQL, e.g. these are the same:

• SELECT 1 a
• SELECT 1 AS a

Or even:

• SELECT t.a FROM t
• SELECT u.a FROM t AS u

And that can't be done without parsing, which we're trying to avoid here.. Knowing that regular expressions are already supported when matching queries (where you can write \s+).

I won't reject the issue, though. Perhaps a sufficient number of users request the same thing in the future.