} // parseTableItem()
private FromItem parseAllJoinItems(String itemToken)
{
String[] separators = { "(?i) JOIN " };
String[] joinSplit = itemToken.Split(separators, StringSplitOptions.RemoveEmptyEntries);
List <String> joinsList = new List <String>();
for (int i = 0; i < joinSplit.Length - 1; i++)
{
joinSplit[i] = joinSplit[i].Trim();
joinSplit[i + 1] = joinSplit[i + 1].Trim();
String leftPart = joinSplit[i].Substring(0, joinSplit[i].LastIndexOf(" "));
String joinType = joinSplit[i].Substring(joinSplit[i].LastIndexOf(" "));
String rightPart = (i + 1 == joinSplit.Length - 1) ?
joinSplit[i + 1] :
joinSplit[i + 1].Substring(0, joinSplit[i + 1].LastIndexOf(" "));
joinsList.Add((leftPart + " " + joinType + " JOIN " + rightPart).Replace(" +", " "));
String rightTable = rightPart.Substring(0, rightPart.ToUpper().LastIndexOf(" ON "));
String nextJoinType = joinSplit[i + 1].Substring(joinSplit[i + 1].LastIndexOf(" "));
joinSplit[i + 1] = rightTable + " " + nextJoinType;
}
HashSet <FromItem> fromItems = new HashSet <FromItem>();
FromItem leftFromItem = null;
foreach (String token in joinsList)
{
leftFromItem = parseJoinItem(leftFromItem, token, fromItems);
}
return(leftFromItem);
} // parseAllJoinItems()
} // getTableFromItems()
public static List <FromItem> getTableFromItems(FromItem item)
{
List <FromItem> result = new List <FromItem>();
if (item.getTable() != null)
{
result.Add(item);
}
else if (item.getSubQuery() != null)
{
FromItem[] sqItems = getTableFromItems(item.getSubQuery());
for (int i = 0; i < sqItems.Length; i++)
{
result.Add(sqItems[i]);
}
}
else if (item.getJoin() != JoinType.None)
{
FromItem leftSide = item.getLeftSide();
result.AddRange(getTableFromItems(leftSide));
FromItem rightSide = item.getRightSide();
result.AddRange(getTableFromItems(rightSide));
}
else
{
throw new InvalidOperationException("FromItem was neither of Table type, SubQuery type or Join type: " + item);
}
return(result);
} // getTableFromItems()
public DBRefList <T> Retrieve <T>(Clause clause = null) where T : TableEntity
{
var script = new SQLScript(Accessor, Commons.Objects.Enums.SQLTYPE.SELECT);
var from = new FromItem <T>(Accessor);
var select = new SelectItem(Accessor, from);
script.AddItems(select, from);
if (clause != null)
{
var where = new WhereItem(Accessor, clause, from);
script.AddItems(where);
}
var list = new DBRefList <T>();
var table = Accessor.Retrieve(script.ExportCommand());
if (table != null && table.Rows.Count > 0)
{
foreach (var row in table.Rows)
{
list.NewEntity().LoadFrom(row as DataRow, 0);
}
}
return(list);
}
private string BuildSQLText_SELECT()
{
if (_list.Count <= 0)
{
return(null);
}
var buffer = new StringBuilder();
//1.Parts:SELECT
if (!_list.Any(itm => itm is SelectItem))
{
throw new ApplicationException("The script is missing select-item!");
}
SelectItem select = null;
_list.Where(itm => itm is SelectItem).ToList().ForEach(itm =>
{
select = select == null ? (SelectItem)itm : select + (SelectItem)itm;
});
buffer.AppendLine(select.Text);
//2.Parts:FROM
if (!_list.Any(itm => itm is FromItem))
{
throw new ApplicationException("The script is missing from-item!");
}
if (_list.Count(itm => itm is FromItem) > 1)
{
throw new ApplicationException("The script contains multiple from-items!");
}
FromItem from = _list.First(itm => itm is FromItem) as FromItem;
buffer.AppendLine(from.Text);
//3.Parts:JOIN
if (_list.Any(itm => itm is JoinItem))
{
_list.Where(itm => itm is JoinItem).ToList().ForEach(join =>
{
buffer.AppendLine(join.Text);
});
}
//4.Parts:WHERE
//if (!_list.Any(itm => itm is WhereItem)) throw new ApplicationException("The script is missing where-item!");
WhereItem where = null;
if (_list.Any(itm => itm is WhereItem))
{
_list.Where(itm => itm is WhereItem).ToList().ForEach(itm =>
{
where = where == null ? (WhereItem)itm : where + (WhereItem)itm;
});
buffer.AppendLine(where.Text);
}
return(buffer.ToString());
}
public override QsiTableNode VisitFromItem(FromItem item)
{
if (item.getAlias()?.getAliasColumns()?.size() > 0)
{
throw new QsiException(QsiError.Syntax);
}
return(base.VisitFromItem(item));
}
} // parseAllJoinItems()
// this method will be documented based on this example itemToken: FOO f
// INNER JOIN BAR b ON f.id = b.id
private FromItem parseJoinItem(FromItem leftFromItem, String itemToken, HashSet <FromItem> fromItems)
{
int indexOfJoin = itemToken.ToUpper().IndexOf(" JOIN ");
// firstPart = "FOO f INNER"
String firstPart = itemToken.Substring(0, indexOfJoin).Trim();
// secondPart = "BAR b ON f.id = b.id"
String secondPart = itemToken.Substring(indexOfJoin + " JOIN ".Length).Trim();
int indexOfJoinType = firstPart.LastIndexOf(" ");
// joinTypeString = "INNER"
String joinTypeString = firstPart.Substring(indexOfJoinType).Trim().ToUpper();
JoinType joinType = (JoinType)Enum.Parse(typeof(JoinType), joinTypeString);
// firstTableToken = "FOO f"
String firstTableToken = firstPart.Substring(0, indexOfJoinType).Trim();
int indexOfOn = secondPart.ToUpper().IndexOf(" ON ");
// secondTableToken = "BAR b"
String secondTableToken = secondPart.Substring(0, indexOfOn).Trim();
FromItem leftSide = parseTableItem(firstTableToken);
FromItem rightSide = parseTableItem(secondTableToken);
fromItems.Add(leftSide);
fromItems.Add(rightSide);
// onClausess = ["f.id = b.id"]
String[] separators = { " AND " };
String[] onClauses = secondPart.Substring(indexOfOn + " ON ".Length).Split(separators, StringSplitOptions.RemoveEmptyEntries);
SelectItem[] leftOn = new SelectItem[onClauses.Length];
SelectItem[] rightOn = new SelectItem[onClauses.Length];
for (int i = 0; i < onClauses.Length; i++)
{
String onClause = onClauses[i];
int indexOfEquals = onClause.IndexOf("=");
// leftPart = "f.id"
String leftPart = onClause.Substring(0, indexOfEquals).Trim();
// rightPart = "b.id"
String rightPart = onClause.Substring(indexOfEquals + 1).Trim();
FromItem[] items = new FromItem[fromItems.Count];
fromItems.CopyTo(items);
leftOn[i] = findSelectItem(leftPart, items);
rightOn[i] = findSelectItem(rightPart, items);
}
FromItem leftItem = (leftFromItem != null) ? leftFromItem : leftSide;
FromItem result = new FromItem(joinType, leftItem, rightSide, leftOn, rightOn);
result.setQuery(_query);
return(result);
} // parseJoinItem()
public List <FromItem> ParseTableSources(TableSourcesContext node)
{
List <FromItem> fromItems = new List <FromItem>();
TableSourceContext[] tables = node.tableSource();
foreach (TableSourceContext table in tables)
{
if (table is TableSourceBaseContext tb)
{
TableSourceItemContext tsi = tb.tableSourceItem();
FromItem fromItem = new FromItem();
if (tsi is SubqueryTableItemContext subquery)
{
fromItem.SubSelectStatement = this.ParseSelectStatement(subquery.selectStatement());
UidContext uid = subquery.uid();
if (uid != null)
{
fromItem.Alias = new TokenInfo(uid)
{
Type = TokenType.Alias
};
}
}
else
{
TableName tableName = this.ParseTableName(tsi);
fromItem.TableName = tableName;
JoinPartContext[] joins = tb.joinPart();
if (joins != null && joins.Length > 0)
{
foreach (JoinPartContext join in joins)
{
JoinItem joinItem = this.ParseJoin(join);
fromItem.JoinItems.Add(joinItem);
}
}
}
fromItems.Add(fromItem);
}
}
return(fromItems);
}
} // constructor
// Override
public void parse(String delim, String itemToken) // throws MetaModelException
{
if ("*".Equals(itemToken))
{
_query.selectAll();
return;
}
String alias = null;
int indexOfAlias = itemToken.ToUpper().LastIndexOf(" AS ");
if (indexOfAlias != -1)
{
alias = itemToken.Substring(indexOfAlias + " AS ".Length);
itemToken = itemToken.Substring(0, indexOfAlias).Trim();
}
try
{
SelectItem selectItem = findSelectItem(itemToken);
if (selectItem == null)
{
throw new QueryParserException("Not capable of parsing SELECT token: " + itemToken);
}
if (alias != null)
{
selectItem.setAlias(alias);
}
_query.select(selectItem);
}
catch (MultipleSelectItemsParsedException e)
{
FromItem fromItem = e.getFromItem();
if (fromItem != null)
{
_query.selectAll(fromItem);
}
else
{
throw e;
}
}
} // parse()
} // constructor
// @Override
public void parse(String delim, String itemToken)
{
FromItem fromItem;
int parenthesisStart = itemToken.IndexOf('(');
if (parenthesisStart != -1)
{
if (parenthesisStart != 0)
{
throw new QueryParserException("Not capable of parsing FROM token: " + itemToken
+ ". Expected parenthesis to start at first character.");
}
int parenthesisEnd = itemToken.IndexOf(')', parenthesisStart);
if (parenthesisEnd == -1)
{
throw new QueryParserException("Not capable of parsing FROM token: " + itemToken
+ ". Expected end parenthesis.");
}
String subQueryString = itemToken.Substring(parenthesisStart + 1, parenthesisEnd);
logger.debug("Parsing sub-query: {0}", subQueryString);
Query subQuery = new QueryParser(_dataContext, subQueryString).parse();
fromItem = new FromItem(subQuery);
string alias = itemToken.Substring(parenthesisEnd + 1).Trim();
if (!alias.IsEmpty())
{
fromItem.setAlias(alias);
}
}
else if (itemToken.ToUpper().IndexOf(" JOIN ") != -1)
{
fromItem = parseAllJoinItems(itemToken);
}
else
{
fromItem = parseTableItem(itemToken);
}
_query.from(fromItem);
} // parse()
// @Override
public SatisfiedFromBuilder on(Column left, Column right) // throws IllegalArgumentException
{
if (left == null)
{
throw new ArgumentException("left cannot be null");
}
if (right == null)
{
throw new ArgumentException("right cannot be null");
}
getQuery().getFromClause().removeItem(leftItem);
SelectItem [] leftOn = new SelectItem[] { new SelectItem(left) };
SelectItem[] rightOn = new SelectItem[] { new SelectItem(right) };
FromItem fromItem = new FromItem(joinType, leftItem, rightItem, leftOn, rightOn);
getQuery().from(fromItem);
return(this);
}
private Column findColumnInAliasedTable(Column column, FromItem fromItem, String aliasPart, String columnPart)
{
if (column != null)
{
// ensure that if the column has already been found, return it
return(column);
}
Table table = fromItem.getTable();
if (table != null)
{
String alias = fromItem.getAlias();
if (alias != null && alias.Equals(aliasPart))
{
column = table.getColumnByName(columnPart);
}
}
else
{
FromItem leftSide = fromItem.getLeftSide();
column = findColumnInAliasedTable(column, leftSide, aliasPart, columnPart);
FromItem rightSide = fromItem.getRightSide();
column = findColumnInAliasedTable(column, rightSide, aliasPart, columnPart);
if (column != null)
{
Query subQuery = fromItem.getSubQuery();
if (subQuery != null)
{
List <FromItem> items = subQuery.getFromClause().getItems();
foreach (FromItem subQueryFromItem in items)
{
column = findColumnInAliasedTable(column, subQueryFromItem, aliasPart, columnPart);
}
}
}
}
return(column);
}
} // constructor
public DataSet executeQuery(Query query)
{
List <SelectItem> selectItems = query.getSelectClause().getItems();
List <FromItem> fromItems = query.getFromClause().getItems();
List <FilterItem> whereItems = query.getWhereClause().getItems();
List <SelectItem> whereSelectItems = query.getWhereClause().getEvaluatedSelectItems();
List <GroupByItem> groupByItems = query.getGroupByClause().getItems();
List <SelectItem> groupBySelectItems = query.getGroupByClause().getEvaluatedSelectItems();
List <SelectItem> havingSelectItems = query.getHavingClause().getEvaluatedSelectItems();
List <SelectItem> orderBySelectItems = query.getOrderByClause().getEvaluatedSelectItems();
List <FilterItem> havingItems = query.getHavingClause().getItems();
List <OrderByItem> orderByItems = query.getOrderByClause().getItems();
int firstRow = (query.getFirstRow() == null ? 1 : query.getFirstRow());
int maxRows = (query.getMaxRows() == null ? -1 : query.getMaxRows());
if (maxRows == 0)
{
// no rows requested - no reason to do anything
return(new EmptyDataSet(selectItems));
}
// check certain common query types that can often be optimized by
// subclasses
bool singleFromItem = fromItems.Count == 1;
bool noGrouping = groupByItems.IsEmpty() && havingItems.IsEmpty();
if (singleFromItem && noGrouping)
{
FromItem fromItem = query.getFromClause().getItem(0);
Table table = fromItem.getTable();
if (table != null)
{
// check for SELECT COUNT(*) queries
if (selectItems.Count == 1)
{
SelectItem selectItem = query.getSelectClause().getItem(0);
if (SelectItem.isCountAllItem(selectItem))
{
bool functionApproximationAllowed = selectItem.isFunctionApproximationAllowed();
if (isMainSchemaTable(table))
{
logger.debug("Query is a COUNT query with {} where items. Trying executeCountQuery(...)",
whereItems.Count);
NNumber count = executeCountQuery(table, whereItems, functionApproximationAllowed);
if (count == null)
{
logger.debug(
"DataContext did not return any count query results. Proceeding with manual counting.");
}
else
{
List <Row> data = new List <Row>(1);
DataSetHeader header = new SimpleDataSetHeader(new SelectItem[] { selectItem });
data.Add(new DefaultRow(header, new Object[] { count }));
return(new InMemoryDataSet(header, data));
}
}
}
}
bool is_simple_select = isSimpleSelect(query.getSelectClause());
if (is_simple_select)
{
// check for lookup query by primary key
if (whereItems.Count == 1)
{
FilterItem whereItem = whereItems[0];
SelectItem selectItem = whereItem.getSelectItem();
if (!whereItem.isCompoundFilter() && selectItem != null && selectItem.getColumn() != null)
{
Column column = selectItem.getColumn();
if (column.isPrimaryKey() && OperatorType.EQUALS_TO.Equals(whereItem.getOperator()))
{
logger.debug(
"Query is a primary key lookup query. Trying executePrimaryKeyLookupQuery(...)");
if (table != null)
{
if (isMainSchemaTable(table))
{
Object operand = whereItem.getOperand();
Row row = executePrimaryKeyLookupQuery(table, selectItems, column, operand);
if (row == null)
{
logger.debug(
"DataContext did not return any GET query results. Proceeding with manual lookup.");
}
else
{
DataSetHeader header = new SimpleDataSetHeader(selectItems);
return(new InMemoryDataSet(header, row));
}
}
}
}
}
}
// check for simple queries with or without simple criteria
if (orderByItems.IsEmpty())
{
DataSet ds = null;
// no WHERE criteria set
if (whereItems.IsEmpty())
{
ds = materializeTable(table, selectItems, firstRow, maxRows);
return(ds);
}
ds = materializeTable(table, selectItems, whereItems, firstRow, maxRows);
return(ds);
}
}
}
}
// Creates a list for all select items that are needed to execute query
// (some may only be used as part of a filter, but not shown in result)
List <SelectItem> workSelectItems = CollectionUtils.concat(true, selectItems, whereSelectItems,
groupBySelectItems, havingSelectItems, orderBySelectItems);
// Materialize the tables in the from clause
DataSet[] fromDataSets = new DataSet[fromItems.Count];
for (int i = 0; i < fromDataSets.Length; i++)
{
FromItem fromItem = fromItems[i];
fromDataSets[i] = materializeFromItem(fromItem, workSelectItems);
}
// Execute the query using the raw data
DataSet dataSet = null; // MetaModelHelper.getCarthesianProduct(fromDataSets, whereItems);
// we can now exclude the select items imposed by the WHERE clause (and
// should, to make the aggregation process faster)
workSelectItems = CollectionUtils.concat(true, selectItems, groupBySelectItems, havingSelectItems,
orderBySelectItems);
if (groupByItems.Count > 0)
{
dataSet = MetaModelHelper.getGrouped(workSelectItems, dataSet, groupByItems);
}
else
{
dataSet = MetaModelHelper.getAggregated(workSelectItems, dataSet);
}
dataSet = MetaModelHelper.getFiltered(dataSet, havingItems);
if (query.getSelectClause().isDistinct())
{
dataSet = MetaModelHelper.getSelection(selectItems, dataSet);
dataSet = MetaModelHelper.getDistinct(dataSet);
dataSet = MetaModelHelper.getOrdered(dataSet, orderByItems);
}
else
{
dataSet = MetaModelHelper.getOrdered(dataSet, orderByItems);
dataSet = MetaModelHelper.getSelection(selectItems, dataSet);
}
dataSet = MetaModelHelper.getPaged(dataSet, firstRow, maxRows);
return(dataSet);
} // executeQuery()
} // executePrimaryKeyLookupQuery()
protected DataSet materializeFromItem(FromItem fromItem, List <SelectItem> selectItems)
{
DataSet dataSet;
JoinType joinType = fromItem.getJoin();
if (fromItem.getTable() != null)
{
// We need to materialize a single table
Table table = fromItem.getTable();
List <SelectItem> selectItemsToMaterialize = new List <SelectItem>();
foreach (SelectItem selectItem in selectItems)
{
FromItem selectedFromItem = selectItem.getFromItem();
if (selectedFromItem != null)
{
if (selectedFromItem.equals(fromItem))
{
selectItemsToMaterialize.Add(selectItem.replaceFunction(null));
}
}
else
{
// the select item does not specify a specific
// from-item
Column selectedColumn = selectItem.getColumn();
if (selectedColumn != null)
{
// we assume that if the table matches, we will use the
// column
if (selectedColumn.getTable() != null && selectedColumn.getTable().Equals(table))
{
selectItemsToMaterialize.Add(selectItem.replaceFunction(null));
}
}
}
}
if (logger.isDebugEnabled())
{
logger.debug("calling materializeTable(" + table.getName() + "," + selectItemsToMaterialize + ",1,-1");
}
// Dispatching to the concrete subclass of
// QueryPostprocessDataContextStrategy
dataSet = materializeTable(table, selectItemsToMaterialize, 1, -1);
}
else if (joinType != JoinType.None)
{
// We need to (recursively) materialize a joined FromItem
if (fromItem.getLeftSide() == null || fromItem.getRightSide() == null)
{
throw new ArgumentException("Joined FromItem requires both left and right side: " + fromItem);
}
DataSet[] fromItemDataSets = new DataSet[2];
// materialize left side
List <SelectItem> leftOn = NArrays.AsList(fromItem.getLeftOn());
fromItemDataSets[0] = materializeFromItem(fromItem.getLeftSide(),
CollectionUtils.concat(true, selectItems, leftOn));
// materialize right side
List <SelectItem> rightOn = NArrays.AsList(fromItem.getRightOn());
fromItemDataSets[1] = materializeFromItem(fromItem.getRightSide(),
CollectionUtils.concat(true, selectItems, rightOn));
FilterItem[] onConditions = new FilterItem[leftOn.Count];
for (int i = 0; i < onConditions.Length; i++)
{
FilterItem whereItem = new FilterItem(leftOn[i], OperatorType.EQUALS_TO, rightOn[i]);
onConditions[i] = whereItem;
}
switch (joinType)
{
case JoinType.INNER:
dataSet = MetaModelHelper.getCarthesianProduct(fromItemDataSets, onConditions);
break;
case JoinType.LEFT:
dataSet = MetaModelHelper.getLeftJoin(fromItemDataSets[0], fromItemDataSets[1], onConditions);
break;
case JoinType.RIGHT:
dataSet = MetaModelHelper.getRightJoin(fromItemDataSets[0], fromItemDataSets[1], onConditions);
break;
default:
throw new ArgumentException("FromItem type not supported: " + fromItem);
}
}
else if (fromItem.getSubQuery() != null)
{
// We need to (recursively) materialize a subquery
dataSet = executeQuery(fromItem.getSubQuery());
}
else
{
throw new ArgumentException("FromItem type not supported: " + fromItem);
}
if (dataSet == null)
{
throw new ArgumentException("FromItem was not succesfully materialized: " + fromItem);
}
return(dataSet);
} // materializeFromItem()
public override string ToString()
{
return("From: " + (FromItem == null ? "NULL" : FromItem.ToString()) + " -> To: " + (ToItem == null ? "NULL" : ToItem.ToString()));
}
public MultipleSelectItemsParsedException(FromItem fromItem)
{
_fromItem = fromItem;
}
} // parse()
/**
* Finds/creates a SelectItem based on the given expression. Unlike the
* {@link #parse(String, String)} method, this method will not actually add
* the selectitem to the query.
*
* @param expression
* @return
*
* @throws MultipleSelectItemsParsedException
* if an expression yielding multiple select-items (such as "*")
* was passed in the expression
*/
public SelectItem findSelectItem(String expression) // throws MultipleSelectItemsParsedException
{
if ("*".Equals(expression))
{
throw new MultipleSelectItemsParsedException(null);
}
if ("COUNT(*)".Equals(expression, StringComparison.CurrentCultureIgnoreCase))
{
return(SelectItem.getCountAllItem());
}
String unmodifiedExpression = expression;
bool functionApproximation;
FunctionType function;
int startParenthesis = expression.IndexOf('(');
if (startParenthesis > 0 && expression.EndsWith(")"))
{
functionApproximation = (expression.StartsWith(SelectItem.FUNCTION_APPROXIMATION_PREFIX));
String functionName = expression.Substring(
(functionApproximation ? SelectItem.FUNCTION_APPROXIMATION_PREFIX.Length : 0), startParenthesis);
function = FunctionTypeFactory.get(functionName.ToUpper());
if (function != null)
{
expression = expression.Substring(startParenthesis + 1, expression.Length - 1).Trim();
if (function is CountAggregateFunction && "*".Equals(expression))
{
SelectItem select_item = SelectItem.getCountAllItem();
select_item.setFunctionApproximationAllowed(functionApproximation);
return(select_item);
}
}
}
else
{
function = null;
functionApproximation = false;
}
String columnName = null;
FromItem fromItem = null;
// attempt to find from item by cutting up the string in prefix and
// suffix around dot.
{
int splitIndex = expression.LastIndexOf('.');
while (fromItem == null && splitIndex != -1)
{
String prefix = expression.Substring(0, splitIndex);
columnName = expression.Substring(splitIndex + 1);
fromItem = _query.getFromClause().getItemByReference(prefix);
splitIndex = expression.LastIndexOf('.', splitIndex - 1);
}
}
if (fromItem == null)
{
if (_query.getFromClause().getItemCount() == 1)
{
fromItem = _query.getFromClause().getItem(0);
columnName = expression;
}
else
{
fromItem = null;
columnName = null;
}
}
if (fromItem != null)
{
if ("*".Equals(columnName))
{
throw new MultipleSelectItemsParsedException(fromItem);
}
else if (fromItem.getTable() != null)
{
Column column = fromItem.getTable().getColumnByName(columnName);
int offset = -1;
while (function == null && column == null)
{
// check for MAP_VALUE shortcut syntax
offset = columnName.IndexOf('.', offset + 1);
if (offset == -1)
{
break;
}
String part1 = columnName.Substring(0, offset);
column = fromItem.getTable().getColumnByName(part1);
if (column != null)
{
String part2 = columnName.Substring(offset + 1);
return(new SelectItem(new MapValueFunction(), new Object[] { part2 }, column, fromItem));
}
}
if (column != null)
{
SelectItem select_item = new SelectItem(function, column, fromItem);
select_item.setFunctionApproximationAllowed(functionApproximation);
return(select_item);
}
}
else if (fromItem.getSubQuery() != null)
{
Query subQuery = fromItem.getSubQuery();
SelectItem subQuerySelectItem = new SelectItemParser(subQuery, _allowExpressionBasedSelectItems)
.findSelectItem(columnName);
if (subQuerySelectItem == null)
{
return(null);
}
return(new SelectItem(subQuerySelectItem, fromItem));
}
}
// if the expression is alias of some select item defined return that
// select item
SelectItem aliasSelectItem = MetaModelHelper.getSelectItemByAlias(_query, unmodifiedExpression);
if (aliasSelectItem != null)
{
return(aliasSelectItem);
}
if (_allowExpressionBasedSelectItems)
{
SelectItem select_item = new SelectItem(function, expression, null);
select_item.setFunctionApproximationAllowed(functionApproximation);
return(select_item);
}
return(null);
} // findSelectItem()
public JoinFromBuilderImpl(Query query, FromItem leftItem, Table rightTable, JoinType joinType, DataContext dataContext) : base(query, dataContext)
{
this.joinType = joinType;
this.leftItem = leftItem;
this.rightItem = new FromItem(rightTable);
}
public TableFromBuilderImpl(Table table, Query query, DataContext dataContext) : base(query, dataContext)
{
_from_item = new FromItem(table);
query.from(_from_item);
}
} // parse()
private FromItem parseTableItem(String itemToken)
{
// From token can be starting with [
String tableNameToken;
String aliasToken;
char startDelimiter = itemToken.Trim()[0];
if (delimiterMap.ContainsKey(startDelimiter))
{
char endDelimiter = delimiterMap[startDelimiter];
int endIndex = itemToken.Trim().LastIndexOf(endDelimiter, itemToken.Trim().Length);
if (endIndex <= 0)
{
throw new QueryParserException("Not capable of parsing FROM token: " + itemToken + ". Expected end "
+ endDelimiter);
}
tableNameToken = itemToken.Trim().Substring(1, endIndex).Trim();
if (itemToken.Trim().Substring(1 + endIndex).Trim().Equals("", StringComparison.CurrentCultureIgnoreCase))
{
/*
* As per code in FromClause Method: getItemByReference(FromItem
* item, String reference) if (alias == null && table != null &&
* reference.equals(table.getName())) { Either we have to change
* the code to add alias.equals("") there or return null here.
*/
aliasToken = null;
}
else
{
aliasToken = itemToken.Trim().Substring(1 + endIndex).Trim();
}
}
else
{
// Default assumption is space being delimiter for tablename and
// alias.. If otherwise use DoubleQuotes or [] around tableName
String[] tokens = itemToken.Split(' ');
tableNameToken = tokens[0];
if (tokens.Length == 2)
{
aliasToken = tokens[1];
}
else if (tokens.Length == 1)
{
aliasToken = null;
}
else
{
throw new QueryParserException("Not capable of parsing FROM token: " + itemToken);
}
}
Table table = _dataContext.getTableByQualifiedLabel(tableNameToken);
if (table == null)
{
throw new QueryParserException("Not capable of parsing FROM token: " + itemToken);
}
FromItem result = new FromItem(table);
result.setAlias(aliasToken);
result.setQuery(_query);
return(result);
} // parseTableItem()
public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
{
base.Build(statement, appendSeparator);
if (statement is SelectStatement select)
{
this.BuildSelectStatement(select, appendSeparator);
}
else if (statement is InsertStatement insert)
{
this.Append($"INSERT INTO {insert.TableName}", false);
if (insert.Columns.Count > 0)
{
this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
}
if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
{
this.AppendChildStatements(insert.SelectStatements, true);
}
else
{
this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
}
}
else if (statement is UnionStatement union)
{
this.AppendLine(this.GetUnionTypeName(union.Type));
this.Build(union.SelectStatement);
}
else if (statement is UpdateStatement update)
{
List <TokenInfo> tableNames = new List <TokenInfo>();
#region Handle for mysql and plsql update join
if (update.TableNames.Count == 1)
{
string[] items = update.TableNames[0].Symbol.Split(' ');
if (items.Length > 1)
{
string alias = items[1];
bool added = false;
foreach (NameValueItem nameValue in update.SetItems)
{
if (nameValue.Name.Symbol.Trim().StartsWith(alias + ".", StringComparison.OrdinalIgnoreCase))
{
if (!added)
{
tableNames.Add(new TokenInfo(alias));
added = true;
}
if (nameValue.Value.Symbol?.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) > 0)
{
string from = nameof(TSqlParser.FROM);
string where = nameof(TSqlParser.WHERE);;
string oldValue = nameValue.Value.Symbol;
int fromIndex = oldValue.ToUpper().IndexOf(from);
nameValue.Value.Symbol = Regex.Replace(oldValue.Substring(0, fromIndex), "SELECT ", "", RegexOptions.IgnoreCase).Trim(' ', '(');
if (update.FromItems == null || update.FromItems.Count == 0)
{
update.FromItems = new List <FromItem>();
FromItem fromItem = new FromItem()
{
TableName = update.TableNames[0]
};
int whereIndex = oldValue.ToUpper().IndexOf(where);
string tableName = oldValue.Substring(fromIndex + from.Length, whereIndex - (fromIndex + from.Length) - 1);
string condition = oldValue.Substring(whereIndex + where.Length).Trim(')');
fromItem.JoinItems.Add(new JoinItem()
{
TableName = new TableName(tableName), Condition = new TokenInfo(condition)
});
update.FromItems.Add(fromItem);
}
}
}
}
}
}
#endregion
if (tableNames.Count == 0)
{
tableNames.AddRange(update.TableNames);
}
this.AppendLine($"UPDATE {string.Join(",", tableNames)} SET");
this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));
if (update.FromItems != null && update.FromItems.Count > 0)
{
int i = 0;
foreach (FromItem fromItem in update.FromItems)
{
if (i == 0)
{
this.AppendLine($"FROM {fromItem.TableName}");
}
foreach (JoinItem joinItem in fromItem.JoinItems)
{
string condition = joinItem.Condition == null ? "" : $" ON {joinItem.Condition}";
this.AppendLine($"{joinItem.Type} JOIN {joinItem.TableName}{condition}");
}
i++;
}
}
if (update.Condition != null && update.Condition.Symbol != null)
{
this.AppendLine($"WHERE {update.Condition}");
}
if (update.Option != null)
{
this.AppendLine(update.Option.ToString());
}
this.AppendLine(";");
}
else if (statement is DeleteStatement delete)
{
this.AppendLine($"DELETE FROM {delete.TableName}");
if (delete.Condition != null)
{
this.AppendLine($"WHERE {delete.Condition}");
}
this.AppendLine(";");
}
else if (statement is DeclareStatement declare)
{
if (declare.Type == DeclareType.Variable)
{
string defaultValue = (declare.DefaultValue == null ? "" : $" = {declare.DefaultValue}");
this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
}
else if (declare.Type == DeclareType.Table)
{
this.AppendLine($"DECLARE {declare.Name} TABLE (");
int i = 0;
foreach (var column in declare.Table.Columns)
{
this.AppendLine($"{column.Name} {column.DataType}{(i == declare.Table.Columns.Count - 1 ? "" : ",")}");
}
this.AppendLine(")");
}
}
else if (statement is SetStatement set)
{
if (set.Key != null && set.Value != null)
{
TokenInfo valueToken = set.Value;
if (valueToken != null)
{
if (valueToken.Type == TokenType.RoutineName)
{
this.MakeupRoutineName(valueToken);
}
else
{
TokenInfo child = valueToken.Tokens.FirstOrDefault(item => item.Type == TokenType.RoutineName);
if (child != null)
{
this.MakeupRoutineName(valueToken);
}
}
}
this.AppendLine($"SET {set.Key } = {set.Value };");
}
}
if (statement is IfStatement @if)
{
foreach (IfStatementItem item in @if.Items)
{
if (item.Type == IfStatementType.IF)
{
this.AppendLine($"{item.Type} {item.Condition}");
}
else
{
this.AppendLine($"{item.Type}");
}
this.AppendLine("BEGIN");
if (item.Statements.Count > 0)
{
this.AppendChildStatements(item.Statements, true);
}
else
{
this.AppendLine("PRINT('BLANK!');");
}
this.AppendLine("END");
}
}
else if (statement is CaseStatement @case)
{
string variableName = @case.VariableName.ToString();
IfStatement ifStatement = new IfStatement();
int i = 0;
foreach (var item in @case.Items)
{
IfStatementItem ifItem = new IfStatementItem();
ifItem.Type = i == 0 ? IfStatementType.IF : item.Type;
if (item.Type != IfStatementType.ELSE)
{
ifItem.Condition = new TokenInfo($"{variableName}={item.Condition}")
{
Type = TokenType.Condition
};
}
i++;
}
this.Build(ifStatement);
}
else if (statement is LoopStatement loop)
{
this.AppendLine($"WHILE {loop.Condition}");
this.AppendLine("BEGIN");
this.AppendChildStatements(loop.Statements, true);
this.AppendLine("END");
}
else if (statement is WhileStatement @while)
{
this.AppendLine($"WHILE { @while.Condition }");
this.AppendLine("BEGIN");
this.AppendChildStatements(@while.Statements, true);
this.AppendLine("END");
}
else if (statement is LoopExitStatement whileExit)
{
this.AppendLine($"IF {whileExit.Condition}");
this.AppendLine("BEGIN");
this.AppendLine("BREAK");
this.AppendLine("END");
}
else if (statement is TryCatchStatement tryCatch)
{
this.AppendLine("BEGIN TRY");
this.AppendChildStatements(tryCatch.TryStatements, true);
this.AppendLine("END TRY");
this.AppendLine("BEGIN CATCH");
this.AppendChildStatements(tryCatch.CatchStatements, true);
this.AppendLine("END CATCH");
}
else if (statement is ReturnStatement @return)
{
this.AppendLine($"RETURN {@return.Value};");
}
else if (statement is PrintStatement print)
{
this.AppendLine($"PRINT {print.Content.Symbol?.Replace("||", "+")};");
}
else if (statement is CallStatement execute)
{
this.AppendLine($"EXECUTE {execute.Name} {string.Join(",", execute.Arguments)};");
}
else if (statement is TransactionStatement transaction)
{
TransactionCommandType commandType = transaction.CommandType;
switch (commandType)
{
case TransactionCommandType.BEGIN:
this.AppendLine("BEGIN TRANS");
break;
case TransactionCommandType.COMMIT:
this.AppendLine("COMMIT");
break;
case TransactionCommandType.ROLLBACK:
this.AppendLine("ROLLBACK");
break;
}
}
else if (statement is LeaveStatement leave)
{
this.AppendLine("RETURN;");
}
else if (statement is DeclareCursorStatement declareCursor)
{
this.AppendLine($"DECLARE {declareCursor.CursorName} CURSOR FOR");
this.Build(declareCursor.SelectStatement);
}
else if (statement is OpenCursorStatement openCursor)
{
this.AppendLine($"OPEN {openCursor.CursorName}");
}
else if (statement is FetchCursorStatement fetchCursor)
{
this.AppendLine($"FETCH NEXT FROM {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)}");
}
else if (statement is CloseCursorStatement closeCursor)
{
this.AppendLine($"CLOSE {closeCursor.CursorName}");
if (closeCursor.IsEnd)
{
this.AppendLine($"DEALLOCATE {closeCursor.CursorName}");
}
}
else if (statement is DeallocateCursorStatement deallocateCursor)
{
this.AppendLine($"DEALLOCATE {deallocateCursor.CursorName}");
}
else if (statement is TruncateStatement truncate)
{
this.AppendLine($"TRUNCATE TABLE {truncate.TableName}");
}
return(this);
}
private void FillSelectScript(string name, int level, SQLScriptCollection collection, WhereItem where = null, Stack <JoinItem <TableEntity> > stack = null)
{
if (collection == null)
{
throw new ArgumentNullException("collection");
}
var script = collection.NewSQLScript(level == 0 ? 0.ToString() : string.Format("{0}-{1}", name.Trim(), level), SQLTYPE.SELECT);
var alias = collection.NewTableAlias();
var from = new FromItem <TableEntity>(collection.accessor, Entity, alias);
var select = new SelectItem(collection.accessor, from);
script.AddItems(select, from);
//Build Jions
JoinItem <TableEntity>[] join_items = null;
if (stack != null)
{
if (HasForeigns)
{
join_items = new JoinItem <TableEntity> [stack.Count];
stack.CopyTo(join_items, 0);
}
TableItem table1 = from;
JoinItem <TableEntity> table2 = null;
while (stack.Count > 0)
{
table2 = stack.Pop().Clone() as JoinItem <TableEntity>;
table2.ON(table1, table2.Keys.ToArray());
table2.Entity.AddJoinFilter(table2, collection.SQLParamCreater);
script.AddItems(table2);
table1 = table2;
}
}
//Build Where
if (level == 0)
{
var where1 = new WhereItem(collection.accessor, from);
Columns.Where(c => c.IsPK).ToList().ForEach(col =>
{
var param = collection.NewParameter(col.Value);
where1.And(where1.ColumnEquals(col.Name, param));
});
where = where == null ? where1 : where + where1;
}
if (where != null)
{
script.AddItem(where.Clone() as WhereItem);
}
var where0 = new WhereItem(collection.accessor, from);
Entity.AddWhereFilter(where0, collection.SQLParamCreater);
script.AddItem(where0);
foreach (var foreign in this.Foreigns)
{
var nstack = join_items != null ? new Stack <JoinItem <TableEntity> >(join_items) : new Stack <JoinItem <TableEntity> >();
nstack.Push(new JoinItem <TableEntity>(collection.accessor, Entity, alias, foreign.Keys.Select(key => key.ToKeyValuePair()).ToArray()));
foreign.EntityInst.Schema.FillSelectScript(foreign.Name, level + 1, collection, where.Clone() as WhereItem, nstack);
}
}
