public IQueryResult Execute(IDatabaseConnection databaseConnection, IQuery query)
{
var sqlQuery = query.GetQuery();
var match = Regex.Match(sqlQuery, MATCH, RegexOptions.IgnoreCase);
if (!match.Success)
{
return(new SqlQueryResult(0, false, string.Format("Invalid query: --{0}--", sqlQuery), null));
}
var tableName = match.Groups["tableName"].Value;
var tableLocation = databaseConnection.GetServerLocation() + "/" + databaseConnection.GetDatabaseName() + "/" + tableName;
if (!databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ExistsTable(tableLocation))
{
return(new SqlQueryResult(0, false, string.Format("Table does not exist: --{0}--", sqlQuery), null));
}
var tableData = databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ReadAllLines(tableLocation);
var orderedTableColumns = tableData.FirstOrDefault().Split(',').ToList();
var tableColumns = orderedTableColumns.ToDictionary(k => k.Split(':')[0], v => v.Split(':')[1]);
var whereClause = new WhereClause(databaseConnection.GetDatabaseConfiguration());
if (match.Groups["whereClause"].Success)
{
var whereExpression = match.Groups["whereClause"].Success ? match.Groups["whereExpression"].Value : string.Empty;
whereClause.Build(whereExpression, tableColumns);
}
var updatedTableData = new Dictionary <int, string>();
for (var i = 1; i < tableData.Length; i++)
{
updatedTableData[i] = tableData[i];
}
for (var i = 1; i < tableData.Length; i++)
{
var row = tableData[i];
var rowData = GetRowData(databaseConnection.GetDatabaseConfiguration(), row, orderedTableColumns);
if (whereClause.MatchesRow(rowData))
{
updatedTableData.Remove(i);
}
}
var affectedRows = tableData.Length - updatedTableData.Count - 1;
var rawTableColumns = tableData.FirstOrDefault();
var rawTableData = string.Join("\n", updatedTableData.Values);
try
{
databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.WriteAllText(tableLocation, string.Format("{0}\n{1}", rawTableColumns, rawTableData));
return(new SqlQueryResult(affectedRows, true, string.Format("Successfully deleted {0} records.", affectedRows), null));
}
catch (Exception exception)
{
throw exception;
}
}
public void BuildingClauseWhenFilterIsNotDefined()
{
var data = new Dictionary <string, string>();
var result = WhereClause.Build("WHERE", arguments, new ODataOptions <Model>(data));
result.Should().BeEmpty();
}
public void BuildingClauseWhenFilterIsDefined()
{
var data = new Dictionary <string, string> {
["$filter"] = "Id eq 1 and (Name eq 'text' or tolower(Name) eq 'other') and Name ne null"
};
var result = WhereClause.Build("WHERE", arguments, new ODataOptions <Model>(data));
result.Should().Be("WHERE ((([Id] = @p0) AND (([Name] = @p1) OR (LOWER([Name]) = @p2))) AND ([Name] IS NOT NULL))");
arguments[0].Should().Be(1L);
arguments[1].Should().Be("text");
arguments[2].Should().Be("other");
}
public IQueryResult Execute(IDatabaseConnection databaseConnection, IQuery query)
{
var sqlQuery = query.GetQuery();
var match = Regex.Match(sqlQuery, MATCH, RegexOptions.IgnoreCase);
if (!match.Success)
{
return(new SqlQueryResult(0, false, string.Format("Invalid query: --{0}--", sqlQuery), null));
}
var tableName = match.Groups["tableName"].Value;
var tableLocation = databaseConnection.GetServerLocation() + "/" + databaseConnection.GetDatabaseName() + "/" + tableName;
if (!databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ExistsTable(tableLocation))
{
return(new SqlQueryResult(0, false, string.Format("Table does not exist: --{0}--", sqlQuery), null));
}
var columns = match.Groups["columns"].Value.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
if (!columns.Any())
{
return(new SqlQueryResult(0, false, string.Format("Invalid columns: --{0}--", sqlQuery), null));
}
var tableData = databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ReadAllLines(tableLocation);
var orderedTableColumns = tableData.FirstOrDefault().Split(',').ToList();
var tableColumns = orderedTableColumns.ToDictionary(k => k.Split(':')[0], v => v.Split(':')[1]);
if (columns.Count == 1 && columns[0].Equals("*"))
{
columns = tableColumns.Keys.ToList();
}
foreach (var columnName in columns)
{
if (!tableColumns.ContainsKey(columnName))
{
return(new SqlQueryResult(0, false, string.Format("Column does not exist: --{0}--", columnName), null));
}
}
var whereClause = new WhereClause(databaseConnection.GetDatabaseConfiguration());
if (match.Groups["whereClause"].Success)
{
var whereExpression = match.Groups["whereClause"].Success ? match.Groups["whereExpression"].Value : string.Empty;
whereClause.Build(whereExpression, tableColumns);
}
var result = new List <Dictionary <string, object> >();
for (var i = 1; i < tableData.Length; i++)
{
var row = tableData[i];
var rowData = GetRowData(databaseConnection.GetDatabaseConfiguration(), row, orderedTableColumns);
if (whereClause.MatchesRow(rowData))
{
var record = new Dictionary <string, object>();
foreach (var column in columns)
{
record[column] = rowData[column];
}
result.Add(record);
}
}
return(new SqlQueryResult(0, true, "Query successfully executed", result));
}
public IQueryResult Execute(IDatabaseConnection databaseConnection, IQuery query)
{
var sqlQuery = query.GetQuery();
var match = Regex.Match(sqlQuery, MATCH, RegexOptions.IgnoreCase);
if (!match.Success)
{
return(new SqlQueryResult(0, false, string.Format("Invalid query: --{0}--", sqlQuery), null));
}
var tableName = match.Groups["tableName"].Value;
var tableLocation = databaseConnection.GetServerLocation() + "/" + databaseConnection.GetDatabaseName() + "/" + tableName;
if (!databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ExistsTable(tableLocation))
{
return(new SqlQueryResult(0, false, string.Format("Table does not exist: --{0}--", sqlQuery), null));
}
var setExpression = match.Groups["set"].Value;
var idx = setExpression.ToUpper().IndexOf(" WHERE ");
var rawColumnsToUpdate = string.Empty;
var whereExpression = string.Empty;
if (idx >= 0)
{
rawColumnsToUpdate = setExpression.Substring(0, idx);
whereExpression = setExpression.Substring(idx + 7);
}
else
{
rawColumnsToUpdate = setExpression;
}
var columnsToUpdate = rawColumnsToUpdate.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToDictionary(k => k.Split('=')[0].Trim(), v => v.Split('=')[1].Trim());
var tableData = databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.ReadAllLines(tableLocation);
var orderedTableColumns = tableData.FirstOrDefault().Split(',').ToList();
var tableColumns = orderedTableColumns.ToDictionary(k => k.Split(':')[0], v => v.Split(':')[1]);
foreach (var columnName in columnsToUpdate.Keys)
{
if (!tableColumns.ContainsKey(columnName))
{
return(new SqlQueryResult(0, false, string.Format("Column does not exist: --{0}--", columnName), null));
}
}
var whereClause = new WhereClause(databaseConnection.GetDatabaseConfiguration());
if (!string.IsNullOrWhiteSpace(whereExpression))
{
whereClause.Build(whereExpression, tableColumns);
}
var affectedRows = 0;
for (var i = 1; i < tableData.Length; i++)
{
var row = tableData[i];
var rowData = GetRowData(databaseConnection.GetDatabaseConfiguration(), row, orderedTableColumns);
if (whereClause.MatchesRow(rowData))
{
foreach (var column in columnsToUpdate.Keys)
{
var columnDataType = tableColumns[column];
var rawValue = columnsToUpdate[column];
if (rawValue.StartsWith("'"))
{
rawValue = rawValue.Substring(1);
}
if (rawValue.EndsWith("'"))
{
rawValue = rawValue.Substring(0, rawValue.Length - 1);
}
var newValue = databaseConnection.GetDatabaseConfiguration().ParseDataTypeValue(columnDataType, rawValue);
rowData[column] = newValue;
}
row = string.Empty;
foreach (var column in orderedTableColumns)
{
row = string.Format("{0},{1}", row, rowData[column.Split(':')[0]]);
}
tableData[i] = row.Substring(1);
affectedRows++;
}
}
databaseConnection.GetDatabaseConfiguration().DatabaseStorageService.WriteAllLines(tableLocation, tableData);
return(new SqlQueryResult(affectedRows, true, "Query successfully executed", null));
}