SQL Preprocessor
This section explains how to use the SQL preprocessor in Exasol.
Exasol provides an SQL preprocessor that can preprocess all executed SQL statements. Using the preprocessor, unsupported
SQL constructs can be transformed into existing SQL features (see examples below). Additionally, you
can introduce syntactic sugar
by replacing simple constructs with more complex elements.
By default, the SQL preprocessor is deactivated. Using the statements ALTER SESSION and ALTER SYSTEM, you can define a script for a session or for the whole system that is responsible for the preprocessing of all SQL commands. Before an SQL statement is passed to the actual database compiler, the preprocessor does a text transformation. Within the script, you can get and set the original text and manipulate it by using the auxiliary library (sqlparsing). For details of scripting language, see Scripting.
Be cautious before activating SQL preprocessing for a whole system, since the SQL statements may stop working if preprocessing has been incorrectly implemented.
You can deactivate the preprocessing using the statements ALTER SESSION and ALTER SYSTEM (by setting the parameter SQL_PREPROCESSOR_SCRIPT
to the NULL
value), as these statements are deliberately
excluded from the preprocessing.
For data security reasons, all statements that include
passwords are excluded (CREATE USER
, ALTER USER
, CREATE CONNECTION
, ALTER CONNECTION
, IMPORT
,
EXPORT
if the IDENTIFIED BY
clause was specified).
In the auditing table EXA_DBA_AUDIT_SQL
, a separate entry for the execution of the preprocessor script is
added (EXECUTE SCRIPT
and the original text within a comment). The executed transformed SQL statement is
listed in another entry.
Library sqlparsing
Exasol provides a library with parsing capabilities for SQL text manipulation.
- Split into tokens
tokenize()
- Identification of token types
iswhitespace()
iscomment()
iswhitespaceorcomment()
isidentifier()
iskeyword()
isstringliteral()
isnumericliteral()
isany()
- Normalizing a string
normalize()
- Finding token sequences
find()
- Access to the SQL text
getsqltext()
setsqltext()
Details
sqlparsing.tokenize(sqlstring)
Splits an input string into an array of strings which correspond to the tokens recognized by the database compiler.
If you concatenate these tokens, you will get the original input string (including upper/lowercase, line
breaks, whitespaces, and so on). Therefore, the equation table.concat(tokens)==sqlstring
is valid.
The possible tokens are as follows:
- Valid SQL identifiers, for example, test.tab or "foo"."bar"
- Keywords, for example, SELECT
- String literals, for example, 'abcdef'
- Numerical literals without sign, for example, 123e4
- Connected whitespaces corresponding to the SQL standard
- Comments corresponding to the SQL standard (line and block comments)
- Multi-character tokens, for example, ':=', '::', '||', '->', '>>', '<<', '>=', '<=', '<>', '!=', '^='
- Single-character tokens, for example, '+', '-', '/', '*', '~'
--/
CREATE SCRIPT example(sql_text) AS
local tokens = sqlparsing.tokenize(sql_text)
for i=1,#tokens do
print(tokens[i])
end
/
EXECUTE SCRIPT example('SELECT dummy FROM dual') WITH OUTPUT;
sqlparsing.iscomment(tokenstring)
Returns whether the input string is a comment token.
sqlparsing.iswhitespace(tokenstring)
Returns whether the input string is a whitespace token.
sqlparsing.iswhitespaceorcomment(tokenstring)
Returns whether the input string is a whitespace or comment token. This function can be useful for function
find()
, because it filters all irrelevant tokens corresponding to the SQL standard.
sqlparsing.isidentifier(tokenstring)
Returns whether the input string is an identifier token.
sqlparsing.iskeyword(tokenstring)
Returns whether the input string is a SQL keyword token (for example, SELECT
, FROM
, TABLE
). The functions
isidentifier()
and iskeyword()
return both true for non-reserved keywords. Therefore, you can identify non-reserved keywords.
sqlparsing.isstringliteral(tokenstring)
Returns whether the input string is a string literal token.
sqlparsing.isnumericliteral(tokenstring)
Returns whether the input string is a numeric literal token.
sqlparsing.isany(tokenstring)
Always returns true. This can be useful if you want to find any first relevant token (as match function
within the method find()
).
sqlparsing.normalize(tokenstring)
Returns a normalized string for similar representations (upper/lowercase identifiers), on the basis of the following rules:
- Regular identifiers are transformed into uppercase letters, for example, dual -> DUAL
- Keywords are transformed in uppercase letters, for example, From -> FROM
- Whitespace-Token of any size are replaced by a single whitespace
- In numerical literals, an optional lowercase "e" is replaced by "E", for example, 1.2e34 -> 1.2E34
sqlparsing.find(tokenlist, startTokenNr, searchForward, searchSameLevel, ignoreFunction, match1, [match2, ... matchN])
Searches in the token list, starting from positions startTokenNr
, forward or backward (searchForward
),
and optionally only within the current level of brackets (searchSameLevel
), for the directly successive
sequence of tokens which are matched by parameters match1, ... matchN
. In that search process, all tokens
that match by function ignoreFunction
will be not considered by the match functions.
Please note that only the first occurrence is returned. In case of multiple occurrences, find can be called multiple times with a start position which indicates where the search begins.
If the searched token sequence is found, then an array of size N is returned (in case of N match elements) whose
X-th entry contains the position of the token within the token list which was matched by matchX
. If the token
sequence was not found, the function returns nil
.
The details of the parameters are as follows:
Paramenter | Description |
---|---|
tokenlist |
List of tokens which is produced by the function |
startTokenNr |
Number of the first token to be considered for the search. |
searchForward |
Defines whether the search should be applied forward ( That means that if you search the token sequence KEYWORD, IDENTIFIER, within the token list 'select' 'abc' 'from' 'dual', and start from position 3, then 'from' 'dual' will be matched and not 'from' 'abc', even when searching backward. If you start your search at position 2, then the backward search will return 'select' 'abc', and the forward search will return 'from' 'dual'. |
searchSameLevel |
Defines whether the search should be limited to the current level of brackets ( Example: Search the closing bracket within the token sequence 'SELECT' 't1.x' '+' '('
'SELECT' 'min' '(' 'y' ')' 'FROM' 't2' ')' 'FROM' 't1' which corresponds to the bracket at position 4: |
ignoreFunction |
A function of type function(string)->bool is expected. The tokens for which the
function |
match1...matchN |
By |
sqlparsing.getsqltext()
Returns the current SQL statement text.
This function is only available within the main SQL Preprocessor script.
sqlparsing.setsqltext(string)
Sets the SQL statement text to a new value which will be passed to the database compiler for execution.
This function is only available within the main SQL Preprocessor script.
Best Practice
For better management of the SQL Preprocessor, you should consider the followings:
- Extensively test a preprocessor script in your own session before activating it throughout the system.
- Implement the SQL processing by using separate auxiliary scripts and integrated in one main
script which is just a wrapper to hand over the SQL text (for example.
sqlparsing.setsqltext(myscript.preprocess(sqlparsing.getsqltext()))
). This is because the functionsgetsqltext()
andsetsqltext()
are only available within the preprocessing and not in normal script executions. By the separation you can test the processing on several test SQL constructs (on your own daily SQL history stored within a table) before activating the main script as preprocessor script. - Ensure that all necessary privileges are granted to execute the preprocessor script. It is recommended that you start a test with a user without special rights. Otherwise, certain user groups could be blocked from executing any SQL statements.
- Make the preprocessing as simple as possible. Especially,
query()
andpquery()
should only be used in exceptional cases if you activate the preprocessing globally, because all SQL queries will be decelerated, and a parallel access on similar tables increases the risk of transaction conflicts.
Examples
This section shows some examples for Preprocessor scripts that explain the functionality.
IF() function
In this example, the IF()
function (currently not supported in Exasol) is transformed into an equivalent CASE WHEN
expression.
--/
CREATE SCRIPT sql_preprocessing.transformIf() AS
function processIf(sqltext)
while (true) do
local tokens = sqlparsing.tokenize(sqltext)
local ifStart = sqlparsing.find(tokens,
1,
true,
false,
sqlparsing.iswhitespaceorcomment,
'IF',
'(' )
if (ifStart==nil) then
break;
end
local ifEnd = sqlparsing.find(tokens,
ifStart[2],
true,
true,
sqlparsing.iswhitespaceorcomment,
')')
if (ifEnd==nil) then
error("if statement not ended properly")
break;
end
local commas1 = sqlparsing.find(tokens,
ifStart[2]+1,
true,
true,
sqlparsing.iswhitespaceorcomment,
',' )
if (commas1==nil) then
error("invalid if function")
break;
end
local commas2 = sqlparsing.find(tokens,
commas1[1]+1,
true,
true,
sqlparsing.iswhitespaceorcomment,
',' )
if (commas2==nil) then
error("invalid if function")
break;
end
local ifParam1=table.concat(tokens, '', ifStart[2]+1, commas1[1]-1)
local ifParam2=table.concat(tokens, '', commas1[1]+1, commas2[1]-1)
local ifParam3=table.concat(tokens, '', commas2[1]+1, ifEnd[1]-1)
local caseStmt='CASE WHEN ('..ifParam1..') != 0 \
THEN ('..ifParam2..') \
ELSE ('..ifParam3..') END '
sqltext=table.concat(tokens, '',1,
ifStart[1]-1)..caseStmt..table.concat(tokens,
'',
ifEnd[1]+1)
end
return sqltext
end
/
--/
CREATE SCRIPT sql_preprocessing.preprocessIf() AS
import( 'sql_preprocessing.transformIf', 'transformIf' )
sqlparsing.setsqltext(
transformIf.processIf(sqlparsing.getsqltext()))
/
SELECT IF( 3+4 > 5, 6, 7 ) from dual;
ALTER SESSION SET sql_preprocessor_script=
sql_preprocessing.preprocessIf;
SELECT IF( 3+4 > 5, 6, 7 ) AS col1 FROM dual;
ls command
In this example, Unix command ls
is transferred to the database. This command returns either
the list of all objects within a schema or the list of all schemas if no schema is opened. Additionally, you can apply
filters (case insensitive) for example, ls '%name%'
to display all objects whose name contains the text 'name'.
--/
CREATE SCRIPT sql_preprocessing.addunixcommands() AS
function processLS(input, tokens, commandPos)
local result = query("SELECT CURRENT_SCHEMA")
local current_schema = result[1][1]
local returnText = ""
local searchCol = ""
if (current_schema==null) then
returnText = "SELECT schema_name FROM exa_schemas WHERE true"
searchCol = "schema_name"
elseif (current_schema=='SYS' or current_schema=='EXA_STATISTICS') then
returnText = "SELECT object_name, object_type FROM exa_syscat \
WHERE schema_name='"..current_schema.."'"
searchCol = "object_name"
else
returnText = "SELECT object_name, object_type FROM exa_all_objects \
WHERE root_type='SCHEMA' \
AND root_name='"..current_schema.."'"
searchCol = "object_name"
end
local addFilters = {}
local lastValid = commandPos
local foundPos = sqlparsing.find(tokens,
lastValid+1,
true,
false,
sqlparsing.iswhitespaceorcomment,
sqlparsing.isany)
while (not(foundPos==nil) )
do
local foundToken = tokens[foundPos[1]]
if (sqlparsing.isstringliteral(foundToken)) then
addFilters[#addFilters+1] = "UPPER("..searchCol..") \
LIKE UPPER("..foundToken .. ")"
elseif (not (sqlparsing.normalize(foundToken) == ';')) then
error("only string literals allowed as arguments for ls,\
but found '"..foundToken.."'")
end
lastValid = foundPos[1]
foundPos = sqlparsing.find(tokens,
lastValid+1,
true,
false,
sqlparsing.iswhitespaceorcomment,
sqlparsing.isany)
end
if ( #addFilters > 0 ) then
local filterText = table.concat(addFilters, " OR ")
return returnText.." AND ("..filterText..")".." ORDER BY "..searchCol
else
return returnText.." ORDER BY "..searchCol
end
end
function processUnixCommands(input)
local tokens = sqlparsing.tokenize(input)
local findResult = sqlparsing.find(tokens,
1,
true,
false,
sqlparsing.iswhitespaceorcomment,
sqlparsing.isany)
if (findResult==nil) then
return input
end
local command = tokens[findResult[1]]
if (sqlparsing.normalize( command )=='LS') then
return processLS(input, tokens, findResult[1])
end
return input;
end
/
--/
CREATE SCRIPT sql_preprocessing.preprocessWithUnixTools() AS
import( 'sql_preprocessing.addunixcommands', 'unixCommands' )
sqlparsing.setsqltext(
unixCommands.processUnixCommands(sqlparsing.getsqltext()));
/
ALTER SESSION SET sql_preprocessor_script=
sql_preprocessing.preprocessWithUnixTools;
OPEN SCHEMA sql_preprocessing;
LS '%unix%';
CLOSE SCHEMA;
LS;
ANY/ALL
ANY and ALL SQL constructs are currently not supported by Exasol. However, using the following script, you can add this functionality.
--/
CREATE SCRIPT sql_preprocessing.transformAnyAll() AS
function rebuildAnyAll(inputsqltext)
local sqltext = inputsqltext;
local tokens = sqlparsing.tokenize(sqltext);
local found = true;
local searchStart = 1;
-- search for sequence >|>=|<|<= ANY|ALL ( SELECT
repeat
local foundPositions =
sqlparsing.find(tokens,
searchStart,
true,
false,
sqlparsing.iswhitespaceorcomment,
function (token)
return (token=='<' or token=='<=' or token=='>'
or token=='>=' or token=='!=' or token=='<>'
or token=='=');
end, -- match <|<=|>|>=|=|!=|<>
function ( token )
local normToken = sqlparsing.normalize(token);
return (normToken=='ANY' or normToken=='SOME'
or normToken=='ALL');
end, -- match ANY|ALL
'(', -- match (
'SELECT' -- match SELECT
);
if (foundPositions==nil) then
found = false;
break;
end
local operatorPos = foundPositions[1];
local anyAllPos = foundPositions[2];
local openBracketPos = foundPositions[3];
searchStart = anyAllPos + 1
foundPositions = sqlparsing.find(tokens,
openBracketPos,
true,
true,
sqlparsing.iswhitespaceorcomment,
')');
if (foundPositions ~= nil) then
local closeBracketPos = foundPositions[1]
local operatorToken = tokens[operatorPos];
local anyOrAll = sqlparsing.normalize(tokens[anyAllPos]);
if (operatorToken=='<' or operatorToken=='<='
or operatorToken=='>' or operatorToken=='>=') then
-- now we have <|<=|>|>= ANY|ALL (SELECT <something> FROM
-- rebuild to <|<=|>|>= (SELECT MIN|MAX(<something>) FROM
local setfunction = 'MIN';
if ( ((anyOrAll=='ANY' or anyOrAll=='SOME') and
(operatorToken=='<' or operatorToken=='<=')
) or
(anyOrAll=='ALL' and (operatorToken=='>'
or operatorToken=='>=')
)
) then
setfunction = 'MAX';
end
tokens[anyAllPos] = '';
tokens[openBracketPos] =
'(SELECT ' .. setfunction .. '(anytab.anycol) FROM (';
tokens[closeBracketPos] = ') as anytab(anycol) )';
elseif (operatorToken=='=' and anyOrAll=='ALL') then
-- special rebuild for = ALL
-- rebuild to=(SELECT CASE WHEN COUNT(DISTINCT <something>)==1
-- THEN FIRST_VALUE(<something>) ELSE NULL END FROM
tokens[anyAllPos] = '';
tokens[openBracketPos] =
'(SELECT CASE WHEN COUNT(DISTINCT anytab.anycol) = 1 \
THEN FIRST_VALUE(anytab.anycol) ELSE NULL END FROM (';
tokens[closeBracketPos] = ') as anytab(anycol) )';
elseif ((operatorToken=='!=' or operatorToken=='<>')
and anyOrAll=='ALL') then
-- special rebuild for != ALL
-- rebuild to NOT IN
tokens[operatorPos] = ' NOT IN '
tokens[anyAllPos] = ''
elseif (operatorToken=='!=' and
(anyOrAll=='ANY' or anyOrAll=='SOME')) then
--special rebuild for != ANY, rebuild to
-- CASE WHEN (SELECT COUNT(DISTINCT <something>) FROM ...) == 1
-- THEN operand != (SELECT FIRST_VALUE(<something>) FROM ...)
-- ELSE operand IS NOT NULL END
--note: This case would normally require to determine the operand
-- which requires full understanding of a value expression
-- in SQL standard which is nearly impossible in
-- preprocessing (and very susceptible to errors)
-- so we evaluate the
-- SELECT COUNT(DISTINCT <something) FROM ...) == 1 here and
-- insert the correct expression
--
-- first preprocess the inner query
local queryText = table.concat(tokens,
'',
openBracketPos,
closeBracketPos)
queryText = rebuildAnyAll( queryText )
-- since the subquery was already processed we can continue
-- searching *after* the SELECT
searchStart = closeBracketPos + 1
local distinctQueryText='SELECT COUNT(DISTINCT anytab.anycol) \
FROM '..queryText..' AS anytab(anycol)'
local success, result = pquery(distinctQueryText)
if (success) then
if (result[1][1] == 1) then
tokens[anyAllPos] ='(SELECT FIRST_VALUE(anytab.anycol) \
FROM '..queryText..' AS anytab(anycol))'
else
tokens[operatorPos] = ' IS NOT NULL '
tokens[anyAllPos] = ''
end
-- clear all tokens of the SELECT
for curTokenNr=openBracketPos,closeBracketPos do
tokens[curTokenNr] = ''
end
end
end
end
until found == false;
return table.concat(tokens);
end
/
--/
CREATE SCRIPT sql_preprocessing.preprocessAnyAll AS
import('sql_preprocessing.transformAnyAll', 'anyallparser');
sqlparsing.setsqltext(
anyallparser.rebuildAnyAll(sqlparsing.getsqltext()))
/
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES 1,2,3,4,5,6,7,8,9,10;
CREATE TABLE t2 (j INT);
INSERT INTO t2 VALUES 5,6,7;
SELECT i FROM t1 WHERE i < ALL(SELECT j FROM t2);
Error: [0A000] Feature not supported: comparison with quantifier ALL
ALTER SESSION SET sql_preprocessor_script=
sql_preprocessing.preprocessAnyAll;
SELECT i FROM t1 WHERE i < ALL(SELECT j FROM t2);