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); }