// *** DEBUG START ***
// Remove comments for testing in NODE
export { SqlParse };
// *** DEBUG END ***/
// Code inspired from: https://github.com/dsferruzza/simpleSqlParser
/**
* @classdesc
* Parse SQL SELECT statement and convert into Abstract Syntax Tree
*/
class SqlParse {
/**
* @param {String} cond
* @returns {String}
*/
static sqlCondition2JsCondition(cond) {
const ast = SqlParse.sql2ast(`SELECT A FROM c WHERE ${cond}`);
let sqlData = "";
if (ast.WHERE !== undefined) {
const conditions = ast.WHERE;
if (conditions.logic === undefined) {
sqlData = SqlParse.resolveSqlCondition("OR", [conditions]);
}
else {
sqlData = SqlParse.resolveSqlCondition(conditions.logic, conditions.terms);
}
}
return sqlData;
}
/**
* Parse a query
* @param {String} sqlStatement
* @returns {Object}
*/
static sql2ast(sqlStatement) {
const query = SqlParse.filterCommentsFromStatement(sqlStatement)
// Define which words can act as separator
const myKeyWords = SqlParse.generateUsedKeywordList(query);
const [querySqlKeywords, querySqlRegexKeywords] = SqlParse.generateSqlSeparatorWords(myKeyWords);
// Hide sub-queries and other inner SQL that may contain keywords used for splitting the query,
// to avoid them to be split by mistake.
const modifiedQuery = SqlParse.hideSqlParts(query, querySqlRegexKeywords);
// Write the position(s) in query of these separators
const extractedKeywordsByPosition = SqlParse.getPositionsOfSqlParts(modifiedQuery, querySqlKeywords);
// Delete duplicates (caused, for example, by JOIN and INNER JOIN)
const sqlOrderedKeywords = SqlParse.removeDuplicateEntries(extractedKeywordsByPosition);
// Generate protected word list to reverse the use of protect()
const protectedRegexKeywords = querySqlRegexKeywords.map(item => SqlParse.protect(item));
// Protect STRING constants in case the constant itself contains SQL keywords.
const queryWithProtectedConstants = SelectKeywordAnalysis.hideQuotedConstants(modifiedQuery, SqlParse.protect);
// Any constants/inner SQL that were protected, need to be converted back to original literal values.
const keywordParameterValues = SqlParse.unhideSqlParts(queryWithProtectedConstants, querySqlRegexKeywords, protectedRegexKeywords);
// Analyze parts
const result = SqlParse.analyzeParts(sqlOrderedKeywords, keywordParameterValues);
SqlParse.assignDerivedTableNameForSubqueries(result);
return result;
}
/**
* Remove comments from SQL statement.
* @param {String} statement
* @returns {String}
*/
static filterCommentsFromStatement(statement) {
// Remove comments with lines starting with '--' and join lines together.
// If comment is within a STRING on a newline, it will fail ...
// We leave inline comments and multi-line /* */ comments for another day.
const filteredStatement = statement.split('\n').filter(line => !line.trim().startsWith('--')).join(' ');
return filteredStatement;
}
/**
*
* @param {String} logic
* @param {Object} terms
* @returns {String}
*/
static resolveSqlCondition(logic, terms) {
let jsCondition = "";
for (const cond of terms) {
if (cond.logic === undefined) {
if (jsCondition !== "" && logic === "AND") {
jsCondition += " && ";
}
else if (jsCondition !== "" && logic === "OR") {
jsCondition += " || ";
}
jsCondition += ` ${cond.left}`;
if (cond.operator === "=") {
jsCondition += " == ";
}
else {
jsCondition += ` ${cond.operator}`;
}
jsCondition += ` ${cond.right}`;
}
else {
jsCondition += SqlParse.resolveSqlCondition(cond.logic, cond.terms);
}
}
return jsCondition;
}
/**
* Returns a list of all keywords used in their original CASE.
* @param {String} query
* @returns {String[]}
*/
static generateUsedKeywordList(query) {
const generatedList = new Set();
// Define which words can act as separator
const keywords = ['SELECT', 'FROM', 'JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'FULL JOIN', 'ORDER BY', 'GROUP BY', 'HAVING', 'WHERE', 'LIMIT', 'UNION ALL', 'UNION', 'INTERSECT', 'EXCEPT', 'PIVOT', 'USING'];
const modifiedQuery = query.toUpperCase();
for (const word of keywords) {
let pos = modifiedQuery.indexOf(word, 0);
while (pos !== -1) {
generatedList.add(query.substring(pos, pos + word.length));
pos++;
pos = modifiedQuery.indexOf(word, pos);
}
}
// @ts-ignore
return [...generatedList];
}
/**
*
* @param {String[]} keywords
* @returns {String[][]}
*/
static generateSqlSeparatorWords(keywords) {
let parts_name = keywords.map(item => `${item} `);
parts_name = parts_name.concat(keywords.map(item => `${item}(`));
const parts_name_escaped = parts_name.map(item => item.replace('(', String.raw`[\(]`));
return [parts_name, parts_name_escaped];
}
/**
*
* @param {String} str
* @param {String[]} parts_name_escaped
* @param {Function} replaceFunction
*/
static hideInnerSql(str, parts_name_escaped, replaceFunction) {
if (!str.includes("(") && !str.includes(")")) {
return str;
}
let bracketCount = 0;
let endCount = -1;
let newStr = str;
for (let i = newStr.length - 1; i >= 0; i--) {
const ch = newStr.charAt(i);
if (ch === ")") {
bracketCount++;
if (bracketCount === 1) {
endCount = i;
}
}
else if (ch === "(") {
bracketCount--;
if (bracketCount === 0) {
let query = newStr.substring(i, endCount + 1);
// Hide words defined as separator but written inside brackets in the query
query = query.replaceAll(new RegExp(parts_name_escaped.join('|'), 'gi'), replaceFunction);
newStr = newStr.substring(0, i) + query + newStr.substring(endCount + 1);
}
}
}
return newStr;
}
/**
*
* @param {String} sqlQuery
* @param {String[]} sqlKeywords
* @returns {any[]}
*/
static getPositionsOfSqlParts(sqlQuery, sqlKeywords) {
// Write the position(s) in query of these separators
const sqlKeywordPostions = [];
const modifiedQuery = SelectKeywordAnalysis.hideQuotedConstants(sqlQuery, SqlParse.toBlank);
for (const item of sqlKeywords) {
let pos = 0;
let part = 0;
do {
part = modifiedQuery.indexOf(item, pos);
if (part !== -1) {
const realName = item.replace(/^((\w|\s)+?)\s?\(?$/i, SqlParse.realNameCallback);
if (sqlKeywordPostions[part] === undefined || sqlKeywordPostions[part].length < realName.length) {
sqlKeywordPostions[part] = realName;
}
pos = part + realName.length;
}
}
while (part !== -1);
};
return sqlKeywordPostions;
}
/**
*
* @param {String} _match
* @param {String} name
* @returns {String}
*/
static realNameCallback(_match, name) {
return name;
}
/**
* Delete duplicates (caused, for example, by JOIN and INNER JOIN)
* @param {String[]} partsOrder
*/
static removeDuplicateEntries(partsOrder) {
let busyUntil = 0;
partsOrder.forEach((item, key) => {
if (busyUntil > key) {
// This position is already used by a previous keyword (example: LEFT JOIN, RIGHT JOIN),
// we keep the longest keyword and delete the shorter one.
partsOrder[key] = '';
}
else {
busyUntil = key + item.length;
// Replace JOIN by INNER JOIN
if (item.toUpperCase() === 'JOIN') {
partsOrder[key] = 'INNER JOIN';
}
}
});
return partsOrder.filter(item => item !== undefined && item !== '');
}
/**
* Add some # inside a string to avoid it to match a regex/split
* @param {String} str
* @returns {String}
*/
static protect(str) {
let result = '#';
const length = str.length;
for (let i = 0; i < length; i++) {
result += `${str[i]}#`;
}
return result;
}
/**
* Restore a string output by protect() to its original state
* @param {String} str
* @returns {String}
*/
static unprotect(str) {
let result = '';
const length = str.length;
for (let i = 1; i < length; i = i + 2) result += str[i];
return result;
}
/**
*
* @param {String} str
* @returns {String}
*/
static toBlank(str) {
return ' '.repeat(str.length);
}
/**
*
* @param {String[]} partsOrder
* @param {String[]} parts
* @returns {Object}
*/
static analyzeParts(partsOrder, parts) {
const result = {};
let j = 0;
partsOrder.forEach(item => {
const itemName = item.toUpperCase();
const selectComponentAst = SelectKeywordAnalysis.analyze(item, parts[j]);
if (result[itemName] === undefined) {
result[itemName] = selectComponentAst;
}
else {
if (typeof result[itemName] === 'string' || result[itemName][0] === undefined) {
const tmp = result[itemName];
result[itemName] = [];
result[itemName].push(tmp);
}
result[itemName].push(selectComponentAst);
}
j++;
});
// Reorganize joins
SqlParse.reorganizeJoins(result);
if (result.JOIN !== undefined) {
for (const [key, item] of result.JOIN.entries()) {
result.JOIN[key].cond = CondParser.parse(item.cond);
}
}
SqlUnionParse.reorganizeUnions(result);
return result;
}
/**
*
* @param {String} query
* @param {String[]} parts_name_escaped
* @returns {String}
*/
static hideSqlParts(query, parts_name_escaped) {
// Hide words defined as separator but written inside brackets in the query
const hiddenQuery = SqlParse.hideInnerSql(query, parts_name_escaped, SqlParse.protect);
// Include brackets around separate selects used in things like UNION, INTERSECT...
let modifiedQuery = SqlUnionParse.sqlSetStatementSplitter(hiddenQuery);
// The SET THEORY type statement splitter creates a bracketed sub-query, which we need to hide.
if (modifiedQuery !== hiddenQuery) {
modifiedQuery = SqlParse.hideInnerSql(modifiedQuery, parts_name_escaped, SqlParse.protect);
}
return modifiedQuery;
}
/**
* Modifies split parts to unhide constants and inner SQL that were protected before splitting.
*
* @param {String} modifiedQuery
* @param {String[]} parts_name_escaped - List of protected keywords used for splitting the query.
* @param {String[]} words - List of protected keywords to reverse the protection in the split parts.
*/
static unhideSqlParts(modifiedQuery, parts_name_escaped, words) {
const splitParts = modifiedQuery.split(new RegExp(parts_name_escaped.join('|'), 'i'));
const revealedParts = [];
for (let i = 1; i < splitParts.length; i++) {
revealedParts.push(SelectKeywordAnalysis.hideQuotedConstants(splitParts[i], SqlParse.unprotect));
}
return revealedParts.map(part => SqlParse.hideInnerSql(part, words, SqlParse.unprotect));
}
/**
* Modifies AST for subqueries in FROM clause. Creates an ALIAS name, which is then used as FROM table name.
* @param {Object} result
*/
static assignDerivedTableNameForSubqueries(result) {
if (result.FROM !== undefined && result.FROM.FROM !== undefined && result.FROM.FROM.as !== undefined) {
if (result.FROM.FROM.as === '') {
throw new Error("Every derived table must have its own alias");
}
// Subquery FROM creates an ALIAS name, which is then used as FROM table name.
result.FROM.table = result.FROM.FROM.as;
result.FROM.isDerived = true;
}
}
/**
*
* @param {Object} result
*/
static reorganizeJoins(result) {
const joinArr = [
['FULL JOIN', 'full'],
['RIGHT JOIN', 'right'],
['INNER JOIN', 'inner'],
['LEFT JOIN', 'left']
];
for (const join of joinArr) {
const [joinName, joinType] = join;
SqlParse.reorganizeSpecificJoin(result, joinName, joinType);
}
}
/**
*
* @param {Object} result
* @param {String} joinName
* @param {String} joinType
*/
static reorganizeSpecificJoin(result, joinName, joinType) {
if (result[joinName] !== undefined) {
if (result.JOIN === undefined) {
result.JOIN = [];
}
if (result[joinName][0] === undefined) {
result[joinName].type = joinType;
result.JOIN.push(result[joinName]);
}
else {
for (const item of result[joinName]) {
item.type = joinType;
result.JOIN.push(item);
}
}
delete result[joinName];
}
}
}
/**
* @classdesc Parsing SQL set commands into AST.
*/
class SqlUnionParse {
/**
*
* @param {String} src
* @returns {String}
*/
static sqlSetStatementSplitter(src) {
let newStr = src;
// Define which words can act as separator
const reg = SqlUnionParse.makeSqlPartsSplitterRegEx(["UNION ALL", "UNION", "INTERSECT", "EXCEPT"]);
const matchedUnions = reg.exec(newStr);
if (matchedUnions === null || matchedUnions.length === 0) {
return newStr;
}
let prefix = "";
const parts = [];
let pos = newStr.search(matchedUnions[0]);
if (pos > 0) {
prefix = newStr.substring(0, pos);
newStr = newStr.substring(pos + matchedUnions[0].length);
}
for (let i = 1; i < matchedUnions.length; i++) {
const match = matchedUnions[i];
pos = newStr.search(match);
parts.push(newStr.substring(0, pos));
newStr = newStr.substring(pos + match.length);
}
if (newStr.length > 0)
parts.push(newStr);
newStr = prefix;
for (let i = 0; i < matchedUnions.length; i++) {
newStr += `${matchedUnions[i]} (${parts[i]}) `;
}
return newStr;
}
/**
*
* @param {String[]} keywords
* @returns {RegExp}
*/
static makeSqlPartsSplitterRegEx(keywords) {
// Define which words can act as separator
let parts_name = keywords.map(item => `${item} `);
parts_name = parts_name.concat(keywords.map(item => `${item}(`));
parts_name = parts_name.concat(parts_name.map(item => item.toLowerCase()));
const parts_name_escaped = parts_name.map(item => item.replace('(', String.raw`[\(]`));
return new RegExp(parts_name_escaped.join('|'), 'gi');
}
/**
*
* @param {Object} result
*/
static reorganizeUnions(result) {
const astRecursiveTableBlocks = ['UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT'];
for (const union of astRecursiveTableBlocks) {
if (typeof result[union] === 'string') {
result[union] = [SqlParse.sql2ast(SqlUnionParse.parseUnion(result[union]))];
}
else if (result[union] !== undefined) {
for (let i = 0; i < result[union].length; i++) {
result[union][i] = SqlParse.sql2ast(SqlUnionParse.parseUnion(result[union][i]));
}
}
}
}
/**
*
* @param {String} inStr
* @returns {String}
*/
static parseUnion(inStr) {
let unionString = inStr;
if (unionString.startsWith("(") && unionString.endsWith(")")) {
unionString = unionString.substring(1, unionString.length - 1);
}
return unionString;
}
}
/**
* @classdesc Lexical analyzer for SELECT statement.
*/
class CondLexer {
constructor(source) {
this.source = source;
this.cursor = 0;
this.currentChar = "";
this.startQuote = "";
this.bracketCount = 0;
this.readNextChar();
}
// Read the next character (or return an empty string if cursor is at the end of the source)
readNextChar() {
this.currentChar = typeof this.source === 'string' ? this.source[this.cursor++] ?? "" : "";
}
/**
* Determine the next token
* @returns {Object}
*/
readNextToken() {
if (/\w/.test(this.currentChar))
return this.readWord();
if (/["'`]/.test(this.currentChar))
return this.readString();
if (/[()]/.test(this.currentChar))
return this.readGroupSymbol();
if (/[!=<>]/.test(this.currentChar))
return this.readOperator();
if (/[+\-*/%]/.test(this.currentChar))
return this.readMathOperator();
if (this.currentChar === '?')
return this.readBindVariable();
if (this.currentChar === "") {
return { type: 'eot', value: '' };
}
this.readNextChar();
return { type: 'empty', value: '' };
}
/**
*
* @returns {Object}
*/
readWord() {
let tokenValue = "";
this.bracketCount = 0;
let insideQuotedString = false;
this.startQuote = "";
while (/./.test(this.currentChar)) {
// Check if we are in a string
insideQuotedString = this.isStartOrEndOfString(insideQuotedString);
if (this.isFinishedWord(insideQuotedString))
break;
tokenValue += this.currentChar;
this.readNextChar();
}
if (/^(AND|OR)$/i.test(tokenValue)) {
return { type: 'logic', value: tokenValue.toUpperCase() };
}
if (/^(IN|IS|NOT|LIKE|EXISTS|EXISTS|BETWEEN)$/i.test(tokenValue)) {
return { type: 'operator', value: tokenValue.toUpperCase() };
}
return { type: 'word', value: tokenValue };
}
/**
*
* @param {Boolean} insideQuotedString
* @returns {Boolean}
*/
isStartOrEndOfString(insideQuotedString) {
if (!insideQuotedString && /['"`]/.test(this.currentChar)) {
this.startQuote = this.currentChar;
return true;
}
else if (insideQuotedString && this.currentChar === this.startQuote) {
// End of quoted string.
return false;
}
return insideQuotedString;
}
/**
*
* @param {Boolean} insideQuotedString
* @returns {Boolean}
*/
isFinishedWord(insideQuotedString) {
if (insideQuotedString)
return false;
// Token is finished if there is a closing bracket outside a string and with no opening
if (this.currentChar === ')' && this.bracketCount <= 0) {
return true;
}
if (this.currentChar === '(') {
this.bracketCount++;
}
else if (this.currentChar === ')') {
this.bracketCount--;
}
// Token is finished if there is a operator symbol outside a string
if (/[!=<>]/.test(this.currentChar)) {
return true;
}
// Token is finished on the first space which is outside a string or a function
return this.currentChar === ' ' && this.bracketCount <= 0;
}
/**
*
* @returns {Object}
*/
readString() {
let tokenValue = "";
const quote = this.currentChar;
tokenValue += this.currentChar;
this.readNextChar();
while (this.currentChar !== quote && this.currentChar !== "") {
tokenValue += this.currentChar;
this.readNextChar();
}
tokenValue += this.currentChar;
this.readNextChar();
// Handle this case : `table`.`column`
if (this.currentChar === '.') {
tokenValue += this.currentChar;
this.readNextChar();
tokenValue += this.readString().value;
return { type: 'word', value: tokenValue };
}
return { type: 'string', value: tokenValue };
}
/**
*
* @returns {Object}
*/
readGroupSymbol() {
const tokenValue = this.currentChar;
this.readNextChar();
return { type: 'group', value: tokenValue };
}
/**
*
* @returns {Object}
*/
readOperator() {
let tokenValue = this.currentChar;
this.readNextChar();
if (/[=<>]/.test(this.currentChar)) {
tokenValue += this.currentChar;
this.readNextChar();
}
return { type: 'operator', value: tokenValue };
}
/**
*
* @returns {Object}
*/
readMathOperator() {
const tokenValue = this.currentChar;
this.readNextChar();
return { type: 'mathoperator', value: tokenValue };
}
/**
*
* @returns {Object}
*/
readBindVariable() {
let tokenValue = this.currentChar;
this.readNextChar();
while (/\d/.test(this.currentChar)) {
tokenValue += this.currentChar;
this.readNextChar();
}
return { type: 'bindVariable', value: tokenValue };
}
}
/**
* @classdesc SQL Condition parser class
*/
class CondParser {
constructor(source) {
this.lexer = new CondLexer(source);
this.currentToken = {};
this.readNextToken();
}
/**
* Parse a string
* @param {String} source
* @returns {Object}
*/
static parse(source) {
return new CondParser(source).parseExpressionsRecursively();
}
/**
* Read the next token (skip empty tokens)
* @returns {Object}
*/
readNextToken() {
this.currentToken = this.lexer.readNextToken();
while (this.currentToken.type === 'empty')
this.currentToken = this.lexer.readNextToken();
return this.currentToken;
}
/**
* Wrapper function ; parse the source
* @returns {Object}
*/
parseExpressionsRecursively() {
return this.parseLogicalExpression();
}
/**
* Parse logical expressions (AND/OR)
* @returns {Object}
*/
parseLogicalExpression() {
let leftNode = this.parseConditionExpression();
while (this.currentToken.type === 'logic') {
const logic = this.currentToken.value;
this.readNextToken();
const rightNode = this.parseConditionExpression();
// If we are chaining the same logical operator, add nodes to existing object instead of creating another one
if (leftNode.logic !== undefined && leftNode.logic === logic && leftNode.terms !== undefined) {
leftNode.terms.push(rightNode);
}
else if (leftNode.operator === "BETWEEN" || leftNode.operator === "NOT BETWEEN") {
leftNode = CondParser.createWhereBetweenAstLogic(leftNode, rightNode);
}
else {
const terms = [leftNode, rightNode].slice(0);
leftNode = { logic, terms };
}
}
return leftNode;
}
/**
* Parse conditions ([word/string] [operator] [word/string])
* @returns {Object}
*/
parseConditionExpression() {
let left = this.parseBaseExpression();
if (this.currentToken.type !== 'operator') {
return left;
}
let operator = this.currentToken.value;
this.readNextToken();
// If there are 2 adjacent operators, join them with a space (exemple: IS NOT)
if (this.currentToken.type === 'operator') {
operator += ` ${this.currentToken.value}`;
this.readNextToken();
}
let right = null;
if (this.currentToken.type === 'group' && (operator === 'EXISTS' || operator === 'NOT EXISTS')) {
[left, right] = this.parseSelectExistsSubQuery();
}
else if (this.currentToken.type === 'word' && operator === 'NOT') {
// The NOT operator is used in the WHERE clause to return all records that DO NOT match the specified criteria.
// It reverses the result of a condition from true to false and vice-versa.
const condition = this.parseConditionExpression();
return CondParser.createNotOperatorAstLogic(condition.left, condition.right, condition.operator);
}
else {
right = this.parseBaseExpression(operator);
}
return { operator, left, right };
}
/**
* Modify AST for BETWEEN logic. Create two comparisons connected with AND/OR
* (AND - BETWEEN, OR - NOT BETWEEN)
* @param {Object} leftNode - contains field to compare AND the low value.
* @param {Object} rightNode - contains high value.
* @returns {Object} - AST with logic and terms for comparison.
*/
static createWhereBetweenAstLogic(leftNode, rightNode) {
const firstOp = leftNode.operator === "BETWEEN" ? ">=" : "<";
const secondOp = leftNode.operator === "BETWEEN" ? "<=" : ">";
const logic = leftNode.operator === "BETWEEN" ? "AND" : "OR";
const terms = [];
terms.push({ left: leftNode.left, right: leftNode.right, operator: firstOp },
{ left: leftNode.left, right: rightNode, operator: secondOp });
return { logic, terms };
}
/**
*
* @param {Object} left
* @param {Object} right
* @param {String} compOp
* @returns {Object}
*/
static createNotOperatorAstLogic(left, right, compOp) {
const operator = CondParser.negateOperator(compOp);
return { operator, left, right };
}
/**
*
* @param {String} operator
* @returns {String}
*/
static negateOperator(operator) {
const operatorMap = {
"=": "<>", "<>": "=", "!=": "=", ">": "<=", "<": ">=", ">=": "<", "<=": ">",
"IS": "IS NOT", "IS NOT": "IS", "LIKE": "NOT LIKE", "NOT LIKE": "LIKE",
"IN": "NOT IN", "NOT IN": "IN", "EXISTS": "NOT EXISTS", "NOT EXISTS": "EXISTS",
"BETWEEN": "NOT BETWEEN", "NOT BETWEEN": "BETWEEN"
}
return operatorMap[operator] || operator;
}
/**
*
* @returns {Object[]}
*/
parseSelectExistsSubQuery() {
let rightNode = null;
const leftNode = '""';
this.readNextToken();
if (this.currentToken.type === 'word' && this.currentToken.value === 'SELECT') {
rightNode = this.parseSelectIn("", true);
if (this.currentToken.type === 'group') {
this.readNextToken();
}
}
return [leftNode, rightNode];
}
// Parse base items
/**
*
* @param {String} operator
* @returns {Object}
*/
parseBaseExpression(operator = "") {
let astNode = {};
// If this is a word/string, return its value
if (this.currentToken.type === 'word' || this.currentToken.type === 'string') {
astNode = this.parseWordExpression();
}
// If this is a group, skip brackets and parse the inside
else if (this.currentToken.type === 'group') {
astNode = this.parseGroupExpression(operator);
}
else if (this.currentToken.type === 'bindVariable') {
astNode = this.currentToken.value;
this.readNextToken();
}
return astNode;
}
/**
*
* @returns {Object}
*/
parseWordExpression() {
let astNode = this.currentToken.value;
this.readNextToken();
if (this.currentToken.type === 'mathoperator') {
astNode += ` ${this.currentToken.value}`;
this.readNextToken();
while ((this.currentToken.type === 'mathoperator' || this.currentToken.type === 'word') && this.currentToken.type !== 'eot') {
astNode += ` ${this.currentToken.value}`;
this.readNextToken();
}
}
return astNode;
}
/**
*
* @param {String} operator
* @returns {Object}
*/
parseGroupExpression(operator) {
this.readNextToken();
let astNode = this.parseExpressionsRecursively();
const isSelectStatement = typeof astNode === "string" && astNode.toUpperCase() === 'SELECT';
if (operator === 'IN' || operator === 'NOT IN' || isSelectStatement) {
astNode = this.parseSelectIn(astNode, isSelectStatement);
}
this.readNextToken();
return astNode;
}
/**
*
* @param {any} startAstNode
* @param {Boolean} isSelectStatement
* @returns {Object}
*/
parseSelectIn(startAstNode, isSelectStatement) {
let astNode = startAstNode;
let inCurrentToken = this.currentToken;
let bracketCount = 1;
// If only one item in list, we hit the end bracket immediately.
bracketCount += CondParser.groupBracketIncrementer(inCurrentToken);
while (bracketCount !== 0 && inCurrentToken.type !== 'eot') {
this.readNextToken();
if (isSelectStatement) {
astNode += ` ${inCurrentToken.value}`;
}
else {
astNode += `,${inCurrentToken.value}`;
}
inCurrentToken = this.currentToken;
bracketCount += CondParser.groupBracketIncrementer(inCurrentToken);
}
if (isSelectStatement) {
astNode = SqlParse.sql2ast(astNode);
}
return astNode;
}
/**
*
* @param {Object} inCurrentToken
* @returns {Number}
*/
static groupBracketIncrementer(inCurrentToken) {
let diff = 0;
if (inCurrentToken.type === 'group') {
if (inCurrentToken.value === '(') {
diff = 1;
}
else if (inCurrentToken.value === ')') {
diff = -1;
}
}
return diff
}
}
/**
* @classdesc Analyze each distinct component of SELECT statement.
*/
class SelectKeywordAnalysis {
/**
*
* @param {String} itemName
* @param {Object} part
* @returns {any}
*/
static analyze(itemName, part) {
const keyWord = itemName.toUpperCase().replaceAll(' ', '_');
if (SelectKeywordAnalysis[keyWord] === undefined) {
throw new Error(`Can't analyze statement ${itemName}`);
}
return SelectKeywordAnalysis[keyWord](part);
}
/**
* Sql SELECT
* @param {String} str
* @param {Boolean} isOrderBy
* @returns {Object[]}
*/
static SELECT(str, isOrderBy = false) {
const selectParts = SelectKeywordAnalysis.protect_split(',', str);
const selectResult = selectParts.filter(item => item !== '')
.map(item => SelectKeywordAnalysis.extractSelectField(item, isOrderBy));
if (selectResult.length === 0) {
throw new Error("No fields SELECTED.");
}
return selectResult;
}
/**
*
* @param {String} item
* @param {Boolean} isOrderBy
* @returns {Object}
*/
static extractSelectField(item, isOrderBy) {
let order = "";
if (isOrderBy) {
const order_by = /^(.+?)(\s+ASC|DESC)?$/gi;
const orderData = order_by.exec(item);
if (orderData !== null) {
order = orderData[2] === undefined ? "ASC" : SelectKeywordAnalysis.trim(orderData[2]);
item = orderData[1].trim();
}
}
// Is there a column alias?
const [name, as] = SelectKeywordAnalysis.getNameAndAlias(item);
const splitPattern = /[\s()*/%+-]+/g;
let terms = name.split(splitPattern);
if (terms !== null) {
const aggFunc = ["SUM", "MIN", "MAX", "COUNT", "AVG", "DISTINCT", "GROUP_CONCAT"];
terms = (aggFunc.includes(terms[0].toUpperCase())) ? null : terms;
}
if (name !== "*" && terms !== null && terms.length > 1) {
const subQuery = SelectKeywordAnalysis.parseForCorrelatedSubQuery(item);
return { name, terms, as, subQuery, order };
}
return { name, as, order };
}
/**
* Sql FROM
* @param {String} str
* @returns {Object}
*/
static FROM(str) {
const subqueryAst = this.parseForCorrelatedSubQuery(str);
if (subqueryAst !== null) {
// If there is a subquery creating a DERIVED table, it must have a derived table name.
// Extract this subquery AS tableName.
const [, alias] = SelectKeywordAnalysis.getNameAndAlias(str);
if (alias !== "" && subqueryAst.FROM !== undefined) {
subqueryAst.FROM.as = alias.toUpperCase();
}
return subqueryAst;
}
let fromParts = str.split(',');
fromParts = fromParts.map(item => SelectKeywordAnalysis.trim(item));
const fromResult = fromParts.map(item => {
const [table, as] = SelectKeywordAnalysis.getNameAndAlias(item);
return { table, as };
});
return fromResult[0];
}
/**
* Sql LEFT JOIN
* @param {String} str
* @returns {Object}
*/
static LEFT_JOIN(str) {
return SelectKeywordAnalysis.allJoins(str);
}
/**
* Sql INNER JOIN
* @param {String} str
* @returns {Object}
*/
static INNER_JOIN(str) {
return SelectKeywordAnalysis.allJoins(str);
}
/**
* Sql RIGHT JOIN
* @param {String} str
* @returns {Object}
*/
static RIGHT_JOIN(str) {
return SelectKeywordAnalysis.allJoins(str);
}
/**
* Sql FULL JOIN
* @param {String} str
* @returns {Object}
*/
static FULL_JOIN(str) {
return SelectKeywordAnalysis.allJoins(str);
}
/**
*
* @param {String} str
* @returns {Object}
*/
static allJoins(str) {
const subqueryAst = this.parseForCorrelatedSubQuery(str);
const strParts = str.toUpperCase().split(' ON ');
const table = strParts[0].split(' AS ');
const joinResult = {};
joinResult.table = subqueryAst === null ? SelectKeywordAnalysis.trim(table[0]) : subqueryAst;
joinResult.as = SelectKeywordAnalysis.trim(table[1]) ?? '';
joinResult.cond = SelectKeywordAnalysis.trim(strParts[1]);
return joinResult;
}
/**
* Sql WHERE
* @param {String} str
* @returns {Object}
*/
static WHERE(str) {
return CondParser.parse(str);
}
/**
* Sql ORDER BY
* @param {String} str
* @returns {Object[]}
*/
static ORDER_BY(str) {
return SelectKeywordAnalysis.SELECT(str, true);
}
/**
* Sql GROUP BY
* @param {String} str
* @returns {Object[]}
*/
static GROUP_BY(str) {
return SelectKeywordAnalysis.SELECT(str);
}
/**
* Sql PIVOT
* @param {String} str
* @returns {Object[]}
*/
static PIVOT(str) {
const strParts = str.split(',');
const pivotResult = [];
for (const item of strParts) {
const pivotOn = /([\w.]+)/gi;
const pivotData = pivotOn.exec(item);
if (pivotData !== null) {
const tmp = {};
tmp.name = SelectKeywordAnalysis.trim(pivotData[1]);
tmp.as = "";
pivotResult.push(tmp);
}
};
return pivotResult;
}
/**
* Sql LIMIT
* @param {String} str
* @returns {Object}
*/
static LIMIT(str) {
const limitResult = {};
limitResult.nb = Number(str);
limitResult.from = 0;
return limitResult;
}
/**
* Sql HAVING
* @param {String} str
* @returns {Object}
*/
static HAVING(str) {
return CondParser.parse(str);
}
/**
* Sql UNION
* @param {String} str
* @returns {String}
*/
static UNION(str) {
return SelectKeywordAnalysis.trim(str);
}
/**
* Sql UNION ALL
* @param {String} str
* @returns {String}
*/
static UNION_ALL(str) {
return SelectKeywordAnalysis.trim(str);
}
/**
* Sql INTERSECT
* @param {String} str
* @returns {String}
*/
static INTERSECT(str) {
return SelectKeywordAnalysis.trim(str);
}
/**
* Sql EXCEPT
* @param {String} str
* @returns {String}
*/
static EXCEPT(str) {
return SelectKeywordAnalysis.trim(str);
}
/**
* If we find 'SELECT ' within brackets, parse the string within brackets as a correlated sub-query.
* @param {String} selectField
* @returns {Object}
*/
static parseForCorrelatedSubQuery(selectField) {
let subQueryAst = null;
const regExp = /\(\s*(SELECT[\s\S]+)\)/i;
const matches = regExp.exec(selectField);
if (matches !== null && matches.length > 1) {
subQueryAst = SqlParse.sql2ast(matches[1]);
}
return subQueryAst;
}
/**
* Split a string using a separator, only if this separator isn't beetween brackets
* @param {String} separator
* @param {String} str
* @returns {String[]}
*/
static protect_split(separator, str) {
const sep = '######';
let inQuotedString = false;
let quoteChar = "";
let bracketCount = 0;
let newStr = "";
for (const c of str) {
if (!inQuotedString && /['"`]/.test(c)) {
inQuotedString = true;
quoteChar = c;
}
else if (inQuotedString && c === quoteChar) {
inQuotedString = false;
}
else if (!inQuotedString && c === '(') {
bracketCount++;
}
else if (!inQuotedString && c === ')') {
bracketCount--;
}
if (c === separator && (bracketCount > 0 || inQuotedString)) {
newStr += sep;
}
else {
newStr += c;
}
}
let strParts = newStr.split(separator);
strParts = strParts.map(item => SelectKeywordAnalysis.trim(item.replaceAll(sep, separator)));
return strParts;
}
/**
* Trim input if input is a string.
* @param {any} data trim() if a string.
* @returns {any} Trimmed input OR original data if not a string.
*/
static trim(data) {
return typeof data === 'string' ? data.trim() : data;
}
/**
* If an ALIAS is specified after 'AS', return the field/table name and the alias.
* @param {String} item
* @returns {String[]} Two items: Real Name, Alias
*/
static getNameAndAlias(item) {
const NAME_AS_ALIAS = " AS ";
let realName = item;
let alias = "";
const lastAsIndex = SelectKeywordAnalysis.lastIndexOfOutsideLiteral(item.toUpperCase(), NAME_AS_ALIAS);
if (lastAsIndex !== -1) {
const subStr = item.substring(lastAsIndex + NAME_AS_ALIAS.length).trim();
if (subStr.length > 0) {
alias = subStr;
// Remove quotes, if any.
if ((subStr.startsWith("'") && subStr.endsWith("'")) ||
(subStr.startsWith('"') && subStr.endsWith('"')) ||
(subStr.startsWith('[') && subStr.endsWith(']'))) {
alias = subStr.substring(1, subStr.length - 1);
}
// Remove everything after 'AS'.
realName = item.substring(0, lastAsIndex).trim();
}
}
return [realName, alias];
}
/**
* Search for last occurence of a string that is NOT inside a quoted string literal.
* @param {String} srcString String to search
* @param {String} searchString String to find outside of a string constant. WARNING: If this search is SPACE(S), function will fail.
* @returns {Number} -1 indicates search string not found. Otherwise it is start position of found string.
*/
static lastIndexOfOutsideLiteral(srcString, searchString) {
const searchableString = SelectKeywordAnalysis.hideQuotedConstants(srcString, SqlParse.toBlank);
return searchableString.lastIndexOf(searchString);
}
/**
*
* @param {String} srcString - Source string. Parse and convert any literal string constants in source.
* @param {Function} proFuncion - this function is performed on string literal constant.
* @returns {String} - converted source string. If no literal constants found, original string is returned.
*/
static hideQuotedConstants(srcString, proFuncion) {
let newString = "";
let literalConstant = "";
let inQuote = "";
for (const ch of srcString) {
if (inQuote !== "") {
// Is this the end of string literal?
if ((inQuote === ch) || (inQuote === "[" && ch === "]")) {
inQuote = "";
newString += proFuncion(literalConstant);
literalConstant = "";
newString += ch;
}
else {
// Inside string constant.
literalConstant += ch;
}
}
else if ("\"'[".includes(ch)) {
// The starting quote.
inQuote = ch;
newString += ch;
}
else {
// Outside of any string constant.
newString += ch;
}
}
// If no termination quote, we need to just add literal constant as is.
newString += literalConstant === "" ? '' : proFuncion(literalConstant);
return newString;
}
}