public void DoubleAggregationDifferentFilters()
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName"
},
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount",
Function = AggregationType.Sum
},
new AggregationJson
{
Column = "Invoice.Id",
Function = AggregationType.Count,
Filters = new List <FilterJson>
{
new FilterJson
{
Column = "Invoice.Paid",
Operator = "=",
Value = JsonDocument.Parse("true").RootElement
}
}
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
with Aggregation0 as (
select |tbl0|!|VendorName| Select0, Sum(|tbl1|!|Amount|) Value0
from |TestSchema|!|Invoice| tbl1
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl1|!|VendorId|
group by |tbl0|!|VendorName|
) ,
Aggregation1 as (
select |tbl0|!|VendorName| Select0, Count(|tbl1|!|Id|) Value0
from |TestSchema|!|Invoice| tbl1
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl1|!|VendorId|
where |tbl1|!|Paid| = @filter0
group by |tbl0|!|VendorName|
)
select a0.Select0, a0.Value0 Value0 , a1.Value0 Value1
from Aggregation0 a0
left join Aggregation1 a1 on a1.Select0 = a0.Select0
order by a0.Value0 desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(1);
}
public void NoAggregation()
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName", "Tag.TagName"
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
select |tbl0|!|VendorName| Select0,
|tbl1|!|TagName| Select1
from |TestSchema|!|InvoiceTag| tbl2
join |TestSchema|!|Invoice| tbl3 on |tbl3|!|Id| = |tbl2|!|InvoiceId|
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl3|!|VendorId|
join |TestSchema|!|Tag| tbl1 on |tbl1|!|Id| = |tbl2|!|TagId|
group by |tbl0|!|VendorName| , |tbl1|!|TagName|
skip:5 take:10
");
filterParams.Names.Should().HaveCount(0);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
Random rand = new Random(DateTime.Now.Millisecond);
string lettersRandom = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_1234567890";
int len = lettersRandom.Length;
int count = int.Parse(GetVariable(query.Message, "Count"));
GenUserDataJson users = new GenUserDataJson();
string log, pass;
for (int i = 0; i < count; i++)
{
log = ""; pass = "";
for (int j = 0; j < 10; j++)
{
log += lettersRandom[rand.Next(0, len)];
}
for (int j = 0; j < 10; j++)
{
pass += lettersRandom[rand.Next(0, len)];
}
users.Users.Add(new UserData {
Login = log, Password = pass
});
DataBaseOperations.CreateUserProfile(log, pass);
}
session.Dialog.SendMessage(users);
}
public void MinimalSelectOneColumn()
{
var queryJson = new QueryJson
{
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Vendor.VendorName",
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
select Count(|tbl0|!|VendorName|) Value0
from |TestSchema|!|Vendor| tbl0
order by Count(|tbl0|!|VendorName|)
desc skip:5 take:10
");
filterParams.Names.Should().HaveCount(0);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
string userName, password;
userName = GetVariable(query.Message, "UserName");
password = GetVariable(query.Message, "Password");
string userPriv = DataBaseOperations.CheckUserLoginData(userName, password);
OperationCode code = OperationCode.UserAuthorized;
if (userPriv == "Admin")
{
session.UserRights = Rights.Admin;
code = OperationCode.AdminAuthorized;
}
else if (userPriv == "User")
{
session.UserRights = Rights.User;
code = OperationCode.UserAuthorized;
}
else
{
session.Dialog.SendMessage(
new Msg(OperationCode.AnswerError, WRONG_LOGIN_OR_PASS));
}
session.CreateLog(userName);
session.WriteLog(userName + " connected!");
session.Dialog.SendMessage(
new Msg(code, "Connected"));
}
public void AggregationFunctions(AggregationType type)
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName"
},
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount",
Function = type
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), $@"
select |tbl0|!|VendorName| Select0, {type}(|tbl1|!|Amount|) Value0
from |TestSchema|!|Invoice| tbl1
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl1|!|VendorId|
group by |tbl0|!|VendorName|
order by {type}(|tbl1|!|Amount|) desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(0);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
session.WriteLog("Select command: " + query.Message);
DataTableJson buf = DataBaseOperations.ExecuteDataTable(query);
buf.Dependence = DataTableDependeces.GetTableDependence(query.TableName);
session.Dialog.SendMessage(buf);
}
private void button_AddRow_Click(object sender, EventArgs e)
{
string command = CreateCommand();
QueryJson query = new QueryJson(OperationCode.INSERT, command, CurrentTable.Name);
var msg = Executer.ApplyCommand <QueryJson>(query);
MessageBox.Show(msg.Message);
}
public void DoubleAggregationMultipleSelects()
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName", "Department.DepartmentName"
},
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount",
Function = AggregationType.Sum
},
new AggregationJson
{
Column = "Invoice.Id",
Function = AggregationType.Count
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
with Aggregation0 as (
select |tbl0|!|VendorName| Select0,
|tbl1|!|DepartmentName| Select1,
Sum(|tbl2|!|Amount|) Value0
from |TestSchema|!|Invoice| tbl2
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl2|!|VendorId|
join |TestSchema|!|Department| tbl1 on |tbl1|!|Id| = |tbl2|!|DepartmentId|
group by |tbl0|!|VendorName| , |tbl1|!|DepartmentName|
) ,
Aggregation1 as (
select |tbl0|!|VendorName| Select0,
|tbl1|!|DepartmentName| Select1,
Count(|tbl2|!|Id|) Value0
from |TestSchema|!|Invoice| tbl2
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl2|!|VendorId|
join |TestSchema|!|Department| tbl1 on |tbl1|!|Id| = |tbl2|!|DepartmentId|
group by |tbl0|!|VendorName| , |tbl1|!|DepartmentName|
)
select a0.Select0, a0.Select1, a0.Value0 Value0 , a1.Value0 Value1
from Aggregation0 a0
left join Aggregation1 a1 on
a1.Select0 = a0.Select0 and
a1.Select1 = a0.Select1
order by a0.Value0 desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(0);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
string[] logins = GetVariable(query.Message, "Logins").
Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var login in logins)
{
DataBaseOperations.DeleteProfile(login);
}
}
public Query(QueryJson json, Schema schema)
{
Select = schema.Load(json.Select);
Aggregations = Aggregation.Load(json.Aggregations, schema);
Filters = Filter.Load(json.Filters, schema);
OrderBy = Ordering.Load(json.OrderBy, schema);
Totals = json.Totals;
Skip = json.Skip;
Take = json.Take;
}
/// <summary>
/// Отправляет команду command через iodialog с операцией "c" и выводит информацию в print.
/// </summary>
/// <param name="command">Текст команды</param>
/// <param name="c">Код операции.Код операции будет изменен в данной функции</param>
/// <param name="data">Будут ли данные от сервера</param>
/// <param name="print">Делегат для вывода информации</param>
public T ApplyCommand <T>(QueryJson msg) where T : Msg
{
var rec = SendCommandAndReceive <T>(msg);
if (rec.Code == OperationCode.AnswerError)
{
PrintErr(msg.Message);
return(default(T));
}
return(rec);
}
public static void Execute(Session userSession, QueryJson query)
{
if (Operations[query.Code].CheckRights(userSession.UserRights))
{
Operations[query.Code].Execute(userSession, query);
}
else
{
userSession.Dialog.SendMessage(OperationCode.AnswerAccessDenied);
}
}
public void FilterByPrimaryKeyOfOtherTable()
{
var queryJson = new QueryJson
{
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount"
}
},
Filters = new List <FilterJson>
{
new FilterJson
{
Column = "Vendor.Id",
Operator = "=",
Value = JsonDocument.Parse("42").RootElement
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
// As filter is on PK of Vendor, can just use FK of Invoice, avoid join
/*
* select Count(|tbl0|!|Amount|) Value0
* from |TestSchema|!|Invoice| tbl0
* where |tbl0|!|VendorId| = @filter0
* order by Count(|tbl0|!|Amount|) desc
* skip:5 take:10
*/
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
select Count(|tbl0|!|Amount|) Value0
from |TestSchema|!|Invoice| tbl0
join |TestSchema|!|Vendor| tbl1 on |tbl1|!|Id| = |tbl0|!|VendorId|
where |tbl1|!|Id| = @filter0
order by Count(|tbl0|!|Amount|) desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(1);
}
public void RejectsMalformedColumnName()
{
var queryJson = new QueryJson
{
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Amount",
}
}
};
Action a = () => new Query(queryJson, Schema);
a.Should().Throw <InvalidOperationException>();
}
public void RejectsBadColumnName()
{
var queryJson = new QueryJson
{
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Vendor.FictionalName",
}
}
};
Action a = () => new Query(queryJson, Schema);
a.Should().Throw <InvalidOperationException>();
}
public static DataTableJson ExecuteDataTable(QueryJson command)
{
SqlDataAdapter adapter = new SqlDataAdapter(command.Message, Connection);
DataTable dataTable = new DataTable(command.TableName);
adapter.Fill(dataTable);
StringBuilder xmlString = new StringBuilder();
var xmlWriter = XmlWriter.Create(xmlString);
dataTable.WriteXml(xmlWriter);
DataTableJson dataTableJson = new DataTableJson()
{
Code = OperationCode.AnswerOK,
DataTable = xmlString.ToString(),
};
return(dataTableJson);
}
protected virtual void WrapperApplyCommand(Session session, QueryJson query)
{
try
{
ApplyCommand(session, query);
}
catch (SqlException e)
{
session.Dialog.SendMessage(new Msg(OperationCode.AnswerError, "Server error: " + e.Message));
}
catch (IndexOutOfRangeException e)
{
session.Dialog.SendMessage(new Msg(OperationCode.AnswerError, "Server error: " + e.Message));
}
catch (Exception e)
{
session.Dialog.SendMessage(new Msg(OperationCode.AnswerError, "Server error: " + e.Message));
}
}
public void ExtraFilters()
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName"
},
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount",
Function = AggregationType.Sum
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var extra = new Filter(new FilterJson
{
Column = "Invoice.Paid",
Operator = "=",
Value = JsonDocument.Parse("true").RootElement
},
Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, new[] { extra }), @"
select |tbl0|!|VendorName| Select0, Sum(|tbl1|!|Amount|) Value0
from |TestSchema|!|Invoice| tbl1
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl1|!|VendorId|
where |tbl1|!|Paid| = @filter0
group by |tbl0|!|VendorName|
order by Sum(|tbl1|!|Amount|) desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(1);
}
public void ManyToMany()
{
var queryJson = new QueryJson
{
Select = new List <string> {
"Vendor.VendorName", "Tag.TagName"
},
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Invoice.Amount",
Function = AggregationType.Sum
}
},
Skip = 5,
Take = 10
};
var query = new Query(queryJson, Schema);
var filterParams = new DictionaryFilterParameters();
AssertSameSql(query.ToSql(Formatter, filterParams, Enumerable.Empty <Filter>()), @"
select |tbl0|!|VendorName| Select0,
|tbl1|!|TagName| Select1,
Sum(|tbl2|!|Amount|) Value0
from |TestSchema|!|InvoiceTag| tbl3
join |TestSchema|!|Invoice| tbl2 on |tbl2|!|Id| = |tbl3|!|InvoiceId|
join |TestSchema|!|Vendor| tbl0 on |tbl0|!|Id| = |tbl2|!|VendorId|
join |TestSchema|!|Tag| tbl1 on |tbl1|!|Id| = |tbl3|!|TagId|
group by |tbl0|!|VendorName| , |tbl1|!|TagName|
order by Sum(|tbl2|!|Amount|) desc
skip:5 take:10
");
filterParams.Names.Should().HaveCount(0);
}
protected abstract void ApplyCommand(Session session, QueryJson query);
public void Execute(Session session, QueryJson query)
{
WrapperApplyCommand(session, query);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
session.Close();
Thread.CurrentThread.Abort();
}
public void SqlAndDapperWithListFilter()
{
var queryJson = new QueryJson
{
Aggregations = new List <AggregationJson>
{
new AggregationJson
{
Column = "Vendor.VendorName",
}
},
Select = new List <string>
{
"Invoice.VendorId",
"Invoice.DepartmentId",
},
Filters = new List <FilterJson>
{
JsonSerializer.Deserialize <FilterJson>($@"{{
""column"": ""Invoice.Id"",
""operator"": ""IN"",
""value"": [2,4,6,8]
}}", JsonOptions)
}
};
var query = new Query(queryJson, Schema);
var filterParams = new DapperFilterParameters();
var querySql = query.ToSql(new SqlServerFormatter(), filterParams, Enumerable.Empty <Filter>());
querySql.Should().Contain("[tbl0].[Id] IN @filter0");
filterParams.ToString().Should().Be("@filter0 = 2, 4, 6, 8");
var db = new Mock <IDbConnection>();
var cmd = new Mock <IDbCommand>();
db.Setup(x => x.CreateCommand()).Returns(cmd.Object);
var dpc = new Mock <IDataParameterCollection>();
cmd.SetupGet(x => x.Parameters).Returns(dpc.Object);
cmd.SetupGet(x => x.CommandText).Returns(string.Empty);
var parameters = new List <Mock <IDbDataParameter> >();
cmd.Setup(x => x.CreateParameter()).Returns(() =>
{
var parameter = new Mock <IDbDataParameter>();
parameters.Add(parameter);
return(parameter.Object);
});
var reader = new Mock <IDataReader>();
cmd.Setup(x => x.ExecuteReader(It.IsAny <CommandBehavior>()))
.Returns(reader.Object);
reader.SetupGet(x => x.FieldCount).Returns(3);
reader.Setup(x => x.GetName(0)).Returns("Value0");
reader.Setup(x => x.GetName(1)).Returns("Select0");
reader.Setup(x => x.GetName(2)).Returns("Select1");
reader.SetupSequence(x => x.Read()).Returns(true).Returns(false);
reader.Setup(x => x.GetValue(0)).Returns("chips");
reader.Setup(x => x.GetValue(1)).Returns(DBNull.Value);
reader.Setup(x => x.GetValue(2)).Returns(13);
var log = new List <string>();
var result = query.Run(new SqlServerFormatter(), db.Object, log.Add);
var record = result.Records.Single();
record.Aggregated.Single().Should().Be("[chips]");
record.Selected.First().Should().Be(null);
record.Selected.Last().Should().Be(26);
}
protected override void WrapperApplyCommand(Session session, QueryJson query)
{
ApplyCommand(session, query);
}
protected override void ApplyCommand(Session session, QueryJson query)
{
session.WriteLog("Insert command: " + query.Message);
DataBaseOperations.ExecuteNonSqlReader(query.Message);
}
