/// <summary> /// Returns MessageProductionData for each Recepient in template. /// </summary> public IEnumerable <MessageProductionData> Collect(IMessageTemplate templ) { var output = new List <MessageProductionData>(); //Migrate all needed data to memory, because it will be intensive processed var filters = templ.Filters != null?templ.Filters.ToArray() : new tblFilter[] { }; var wildcards = templ.Wildcards != null?templ.Wildcards.ToArray() : new tblWildcard[] { }; var recepients = templ.Recepients != null?templ.Recepients.ToArray() : new tblRecepientCard[] { }; var userInputedValues = templ.FilterValueContainers != null?templ.FilterValueContainers.ToArray() : new FilterValueContainer[] { }; Dictionary <int, ValueOperatorPair[]> filtsToValOps = GetFiltersActualSettings(filters, userInputedValues); var Condition = SqlPredicate.BuildAndNode(); if (filters.Length > 0) { foreach (var f in filters) { var orNode = SqlPredicate.BuildOrNode(); var valops = filtsToValOps[f.Id]; foreach (var valop in valops) { orNode.Append(SqlPredicate.BuildEndNode(f.Key, valop.Operator, valop.Value, f.Type)); } Condition.Append(orNode); } } //Build list of needed colomns var colomns = wildcards.Select(x => x.Key) .Concat(recepients.Select(x => x.EmailKey)) .Concat(recepients.Select(x => x.NameKey)) .Concat(recepients.Select(x => x.PhoneKey)).Distinct(); var sqlData = Manager.SqlLogic.FetchData(colomns, templ.TableWithKeysName, "dbo", Condition); var wildcardsSummed = wildcards.SelectMany(x => x.ToKeyValues()); bool IsSms = templ.IsSms; foreach (var rec in recepients) { var prodData = new MessageProductionData(); var GroupKey = IsSms ? rec.PhoneKey : rec.EmailKey; prodData.TextProductionData = sqlData .Where(x => !string.IsNullOrWhiteSpace(x[GroupKey].ToString())) .GroupBy(x => x[GroupKey].ToString()); prodData.wildCards = wildcardsSummed.Concat(rec.ToKeyValues()); output.Add(prodData); } return(output); }
public async Task TestKeySetPredicate() { var dictionary = await Client.GetMapAsync <string, string>(CreateUniqueName()); await using var _ = DestroyAndDispose(dictionary); await dictionary.SetAsync("key1", "value1"); await dictionary.SetAsync("key2", "value2"); await dictionary.SetAsync("key3", "value3"); await using var context = await Client.BeginTransactionAsync(); var txDictionary = await context.GetMapAsync <string, string>(dictionary.Name); var sqlPredicate = new SqlPredicate("this == value1"); var keys = await txDictionary.GetKeysAsync(sqlPredicate); Assert.AreEqual(1, keys.Count); Assert.AreEqual("key1", keys.First()); var values = await txDictionary.GetValuesAsync(sqlPredicate); Assert.AreEqual(1, values.Count); Assert.AreEqual("value1", values.First()); await context.CommitAsync(); }
public new void ToString() { SqlPredicate p; p = new SqlPredicate("x is null"); p.ToString(); Assert.That(p.ToString(), Is.EqualTo("x IS NULL")); }
public void IsPlaceHolderOnly() { SqlPredicate p; p = new SqlPredicate("@PH"); Assert.That(p.IsPlaceHolderOnly, Is.True); p = new SqlPredicate("a is not null"); Assert.That(p.IsPlaceHolderOnly, Is.False); }
public long Count <TEntity>(string columnName, SqlPredicate whereStatement) where TEntity : new() { EnsureConnectionOpen(); QueryBuilder builder = QueryBuilder.Create().SpecifyColumns(null).AddSpecialColumn(new CountSpecialColumn(columnName, $"{(columnName == "*" ? "AllColumns" : columnName)}Count")); if (whereStatement != null) { builder.SetWhereStatement(whereStatement); } return(ExecuteScalar <TEntity, long>(builder)); }
public void Clone() { SqlPredicate p; SqlPredicate p1; p = new SqlPredicate("@PH"); p1 = p.Clone(); Assert.That(p.ToString(), Is.EqualTo("@PH")); Assert.That(p1.ToString(), Is.EqualTo("@PH")); p1.Place("PH", "x=1"); Assert.That(p.ToString(), Is.EqualTo("@PH")); Assert.That(p1.ToString(), Is.EqualTo("x=1")); }
public void Empty() { SqlPredicate p = new SqlPredicate(); Assert.That(p.And(p).ToString(), Is.EqualTo("")); Assert.That(p.Clone().ToString(), Is.EqualTo("")); Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new string[] { })); Assert.That(p.HasUnplacedHolder("T"), Is.False); Assert.That(p.HasUnplacedHolders(), Is.False); Assert.That(p.IsEmpty, Is.True); Assert.That(p.IsPlaceHolderOnly, Is.False); p.Place("PH", "a=1"); Assert.That(p.ToString(), Is.EqualTo("")); }
/// <summary> /// 查询已存在的数据项 /// </summary> /// <param name="conn"></param> /// <param name="userRole">要保存的数据项</param> /// <returns></returns> protected override (UserRole, Exception) QueryExists(IDbConnection conn, UserRole userRole) { if (!userRole.UserGuid.IsValid() || !userRole.RoleCode.IsValid()) { return(null, Exceptions.InvalidParam); } var matchFields = new string[] { nameof(userRole.Uid), nameof(userRole.UserGuid), nameof(userRole.RoleCode) }; var matchSql = $"{{0}} {LogicTypes.OR} ({{1}} {LogicTypes.AND} {{2}})"; var predicate = new SqlPredicate <UserRole> (userRole, matchSql, matchFields); var(_, items) = conn.GetWhere(predicate); return(items?.FirstOrDefault(), null); }
/// <summary> /// 查询已存在的数据项 /// </summary> /// <param name="conn"></param> /// <param name="rolePerm">要保存的数据项</param> /// <returns></returns> protected override (RolePermission, Exception) QueryExists(IDbConnection conn, RolePermission rolePerm) { if (null == rolePerm || !rolePerm.Uid.IsValid() || !rolePerm.RoleCode.IsValid() || !rolePerm.PermCode.IsValid()) { return(null, Exceptions.InvalidParam); } var matchFields = new string[] { nameof(rolePerm.Uid), nameof(rolePerm.RoleCode), nameof(rolePerm.PermCode) }; var matchSql = $"{{0}} {LogicTypes.OR} ({{1}} {LogicTypes.AND} {{2}})"; var predicate = new SqlPredicate <RolePermission> (rolePerm, matchSql, matchFields); var(_, items) = conn.GetWhere(predicate); return(items?.FirstOrDefault(), null); }
public void Place() { SqlPredicate p; p = new SqlPredicate("not c > 1"); p.Place("PH", "a"); Assert.That(p.ToString(), Is.EqualTo("NOT c>1")); p = new SqlPredicate("@PH"); Assert.Throws <CannotBuildASTException>(() => { p.Place("PH", "1"); }); p = new SqlPredicate("@PH"); p.Place("PH", "a=1"); Assert.That(p.ToString(), Is.EqualTo("a=1")); p = new SqlPredicate("Not @PH"); p.Place("PH", "x=2 or y=3"); Assert.That(p.ToString(), Is.EqualTo("NOT (x=2 OR y=3)")); }
public void HasUnplacedHolders() { SqlPredicate p; p = new SqlPredicate("@PH"); Assert.That(p.HasUnplacedHolders(), Is.True); p.Place("PH", "'abc' <= 'abcd'"); Assert.That(p.HasUnplacedHolders(), Is.False); p = new SqlPredicate("@PH1 And @PH2"); p.Place("PH1", "x = +100"); Assert.That(p.HasUnplacedHolders(), Is.True); p.Place("PH2", "y = -9"); Assert.That(p.HasUnplacedHolders(), Is.False); p = new SqlPredicate("@PH AND @PH"); p.Place("PH", "'abc' <= 'abcd'"); Assert.That(p.HasUnplacedHolders(), Is.False); p.Place("PH", "'abc' <= 'abcd'"); Assert.That(p.HasUnplacedHolders(), Is.False); }
public void GetAllPlaceHolders() { SqlPredicate p; p = new SqlPredicate("@PH"); Assert.That(p.GetAllPlaceHolders() , Is.EqualTo(new Dictionary <string, string> { { "PH", "" } })); p.Place("PH", "'abc' > 0"); Assert.That(p.GetAllPlaceHolders() , Is.EqualTo(new Dictionary <string, string> { { "PH", "'abc'>0" } })); p = new SqlPredicate("@PH1 And @PH2"); p.Place("PH1", "x=+100"); Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string> { { "PH1", "x=+100" }, { "PH2", "" } })); p.Place("PH2", "y = -9"); Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string> { { "PH1", "x=+100" }, { "PH2", "y=-9" } })); p = new SqlPredicate("@PH AND @PH"); p.Place("PH", "x = 'abc'"); Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string> { { "PH", "x='abc'" } })); p.Place("PH", "x = 'abc'"); Assert.That(p.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string> { { "PH", "x='abc'" } })); }
/// <summary> /// 修改密码 /// </summary> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="user"></param> /// <param name="newPasswordHash">新密码哈希值</param> /// <returns></returns> private Exception ChangePassword(IDbConnection conn, IDbTransaction trans, User user, string newPasswordHash) { if (null == user || !user.Uid.IsValid() || !user.PasswordHash.IsValid() || !newPasswordHash.IsValid()) { return(Exceptions.InvalidParam); } var matchFields = new string[] { nameof(user.Uid), nameof(user.IsDeleted) }; //未删除的用户 user.IsDeleted = 0; var predicate = new SqlPredicate <User> (user, matchFields); var(_, items, ex) = Select(predicate); var item = items?.FirstOrDefault(x => x != null); if (null == item) { return(Exceptions.NotFound); } if (!user.PasswordHash.Equals(item.PasswordHash)) { return(Exceptions.Indentify); } item.PasswordHash = newPasswordHash; item.UpdateUser = user.UpdateUser; item.UpdateTime = DateTime.Now; return(conn.Update(item, trans) ? null : Exceptions.Failure); }
/// <summary> /// 验证用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public (User, Exception) VerifyUser(User user) { if (null == user || !user.UserName.IsValid() || !user.PasswordHash.IsValid()) { return(null, Exceptions.InvalidParam); } var matchFields = new string[] { nameof(user.UserName), nameof(user.PasswordHash), nameof(user.IsDeleted) }; //未删除的用户 user.IsDeleted = 0; var predicate = new SqlPredicate <User> (user, matchFields); var(_, items, ex) = Select(predicate); if (items.IsEmpty()) { return(null, ex ?? Exceptions.NotFound); } return(items.First(), ex); }
public virtual void TestKeySetPredicate() { _map.Put("key1", "value1"); _map.Put("key2", "value2"); _map.Put("key3", "value3"); var context = Client.NewTransactionContext(); context.BeginTransaction(); var txnMap = context.GetMap <object, object>(_name); var sqlPredicate = new SqlPredicate <object, object>("this == value1"); var keys = txnMap.KeySet(sqlPredicate); Assert.AreEqual(1, keys.Count); Assert.AreEqual("key1", keys.First()); var values = txnMap.Values(sqlPredicate); Assert.AreEqual(1, values.Count); Assert.AreEqual("value1", values.First()); context.CommitTransaction(); }
public void And() { SqlPredicate p; SqlPredicate p1; p = new SqlPredicate("@PH and 1 = 1"); p1 = p.And(new SqlPredicate("x is null and 2=2")); Assert.That(p1.ToString(), Is.EqualTo("@PH AND 1=1 AND x IS NULL AND 2=2")); p1 = p.And(new SqlPredicate("x is null or 2=2")); Assert.That(p1.ToString(), Is.EqualTo("@PH AND 1=1 AND (x IS NULL OR 2=2)")); p = new SqlPredicate("@PH or 1 = 1"); p1 = p.And(new SqlPredicate("x is null and 2 = 2")); Assert.That(p1.ToString(), Is.EqualTo("(@PH OR 1=1) AND x IS NULL AND 2=2")); p = new SqlPredicate("x is null"); p1 = p.And(p); Assert.That(p1.ToString(), Is.EqualTo("x IS NULL AND x IS NULL")); p = new SqlPredicate("x = 11"); p1 = p.And(new SqlPredicate()); Assert.That(p1.ToString(), Is.EqualTo("x=11")); }
public void Place2() { SqlPredicate p; // // Predicate演算子の結合の優先順位が適用前後で変わらないことを確認する // p = new SqlPredicate("@PH"); p.Place("PH", "a=1 and b=2"); Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2")); p = new SqlPredicate("@PH"); p.Place("PH", "a=1 or b=2"); Assert.That(p.ToString(), Is.EqualTo("a=1 OR b=2")); p = new SqlPredicate("@PH"); p.Place("PH", "not a=1"); Assert.That(p.ToString(), Is.EqualTo("NOT a=1")); p = new SqlPredicate("@PH"); p.Place("PH", "a=1 collate jp"); Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp")); p = new SqlPredicate("@PH AND 1 = 1"); p.Place("PH", "a=1 and b=2"); Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2 AND 1=1")); p = new SqlPredicate("@PH AND 1 = 1"); p.Place("PH", "a=1 or b=2"); Assert.That(p.ToString(), Is.EqualTo("(a=1 OR b=2) AND 1=1")); p = new SqlPredicate("@PH AND 1 = 1"); p.Place("PH", "not a=1"); Assert.That(p.ToString(), Is.EqualTo("NOT a=1 AND 1=1")); p = new SqlPredicate("@PH AND 1 = 1"); p.Place("PH", "a=1 collate jp"); Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp AND 1=1")); p = new SqlPredicate("@PH OR 1 = 1"); p.Place("PH", "a=1 and b=2"); Assert.That(p.ToString(), Is.EqualTo("a=1 AND b=2 OR 1=1")); p = new SqlPredicate("@PH OR 1 = 1"); p.Place("PH", "a=1 or b=2"); Assert.That(p.ToString(), Is.EqualTo("a=1 OR b=2 OR 1=1")); p = new SqlPredicate("@PH OR 1 = 1"); p.Place("PH", "not a=1"); Assert.That(p.ToString(), Is.EqualTo("NOT a=1 OR 1=1")); p = new SqlPredicate("@PH OR 1 = 1"); p.Place("PH", "a=1 collate jp"); Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp OR 1=1")); p = new SqlPredicate("Not @PH"); p.Place("PH", "a=1 and b=2"); Assert.That(p.ToString(), Is.EqualTo("NOT (a=1 AND b=2)")); p = new SqlPredicate("Not @PH"); p.Place("PH", "a=1 or b=2"); Assert.That(p.ToString(), Is.EqualTo("NOT (a=1 OR b=2)")); p = new SqlPredicate("Not @PH"); p.Place("PH", "not a=1"); Assert.That(p.ToString(), Is.EqualTo("NOT NOT a=1")); p = new SqlPredicate("Not @PH"); p.Place("PH", "a=1 collate jp"); Assert.That(p.ToString(), Is.EqualTo("NOT a=1 COLLATE jp")); p = new SqlPredicate("@PH collate jp"); p.Place("PH", "a=1 and b=2"); Assert.That(p.ToString(), Is.EqualTo("(a=1 AND b=2) COLLATE jp")); p = new SqlPredicate("@PH collate jp"); p.Place("PH", "a=1 or b=2"); Assert.That(p.ToString(), Is.EqualTo("(a=1 OR b=2) COLLATE jp")); p = new SqlPredicate("@PH collate jp"); p.Place("PH", "not a=1"); Assert.That(p.ToString(), Is.EqualTo("NOT a=1 COLLATE jp")); p = new SqlPredicate("@PH collate jp"); p.Place("PH", "a=1 collate jp"); Assert.That(p.ToString(), Is.EqualTo("a=1 COLLATE jp COLLATE jp")); }
public QueryBuilder SetWhereStatement(SqlPredicate whereStatement) { WhereStatement = whereStatement; return(this); }