public void ReplaceSubqueryWithValue_Handle_New_Lines()
{
//arrange
SelectParser selectParser = new SelectParser();
string query = @"select truck, origin, space
from someTable where origin > 8
AND truck = (
select truck from someTruckTable
)
OR space = 98";
InnerStatement subquery = selectParser.GetFirstMostInnerParantheses(query);
var expected = @"select truck, origin, space
from someTable where origin > 8
AND truck = 'F-150'
OR space = 98";
//act
var newQuery = new SelectParser().ReplaceSubqueryWithValue(query, subquery, "F-150", TypeEnum.String);
//assert
Assert.AreEqual(expected, newQuery);
}
public List <SelectColumnDto> ParseAggregates(List <string> columns)
{
List <SelectColumnDto> selectDtos = new List <SelectColumnDto>();
columns = columns.Select(x => x.ToLower().Replace(" ", "").Replace("\r\n", "")).ToList();
foreach (var col in columns)
{
InnerStatement innerStatement = GetFirstMostInnerParantheses(col);
string columnName = innerStatement == null ? col : innerStatement.Statement;
if (columnName == null)
{
selectDtos.Add(new SelectColumnDto {
ColumnName = col
});
continue;
}
string aggregrateFunction = col.Replace(columnName, "");
if (aggregrateFunction == "max()")
{
selectDtos.Add(new SelectColumnDto
{
ColumnName = columnName,
AggregateFunction = CompareDelegates.Max
});
}
else if (aggregrateFunction == "min()")
{
selectDtos.Add(new SelectColumnDto
{
ColumnName = columnName,
AggregateFunction = CompareDelegates.Min
});
}
else if (aggregrateFunction == "count()")
{
selectDtos.Add(new SelectColumnDto
{
ColumnName = columnName,
AggregateFunction = CompareDelegates.Count
});
}
else
{
selectDtos.Add(new SelectColumnDto
{
ColumnName = columnName,
AggregateFunction = null
});
}
}
return(selectDtos);
}
public IComparable[] GetRow(string dml)
{
string tableName = ParseTableName(dml);
TableDefinition tableDefinition = _schemaFetcher.GetTableDefinition(tableName);
InnerStatement innerStatementValues = GetFirstMostInnerParantheses(dml);
IComparable[] comparables = GetRow(innerStatementValues.Statement, tableDefinition);
return(comparables);
}
internal string ReplaceSubqueryWithValue(string query, InnerStatement subquery, string value, TypeEnum type)
{
string subQueryWithParantheses = query.Substring(subquery.StartIndexOfOpenParantheses,
subquery.EndIndexOfCloseParantheses - subquery.StartIndexOfOpenParantheses + 1);
if (type == TypeEnum.String)
{
value = "'" + value + "'";
}
return(query.Replace(subQueryWithParantheses, value));
}
public List <ColumnDefinition> GetColumnDefintions(string dml)
{
dml = ToLowerAndTrim(dml);
string[] parts = dml.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
string tableName = parts[2];
InnerStatement columnDefinitionStatement = GetOuterMostParantheses(dml);
string[] columnParts = columnDefinitionStatement.Statement.Split(',').Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
if (columnParts.Length > Constants.Max_Columns)
{
throw new Exception("Column definitions exceed max count of 100. Only 100 columns allowed per table");
}
List <ColumnDefinition> colDefinitions = new List <ColumnDefinition>();
for (int i = 0; i < columnParts.Length; i++)
{
string[] columnNameAndType = columnParts[i].Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
if (columnNameAndType.Length < 2)
{
throw new Exception($"Column name or data type missing from table definition: '{columnNameAndType[0]}'");
}
bool isIdentityColumn = IsIdentityColumn(columnNameAndType);
if (isIdentityColumn && i > 0)
{
throw new Exception("Identity column must be first column in table definition");
}
ColumnDefinition columnDefinition = new ColumnDefinition
{
ColumnName = columnNameAndType[0].RemoveNewLines(),
Index = (byte)i
};
columnDefinition.Type = ParseTypeAndByteSize(columnNameAndType[1].RemoveNewLines(), columnDefinition);
columnDefinition.IsIdentity = isIdentityColumn ? (byte)1 : (byte)0;
colDefinitions.Add(columnDefinition);
}
return(colDefinitions);
}
public void GetFirstMostInnerSelectStatement_Parses_With_Spaces()
{
//arrange
SelectParser selectParser = new SelectParser();
string query = @"select truck, origin, space
from someTable where origin > 8
AND truck = ( select truck from someTruckTable )
OR space = 98";
//act
InnerStatement subquery = selectParser.GetFirstMostInnerParantheses(query);
//assert
Assert.AreEqual(" select truck from someTruckTable ", subquery.Statement);
Assert.AreEqual(130, subquery.StartIndexOfOpenParantheses);
Assert.AreEqual(169, subquery.EndIndexOfCloseParantheses);
}
