public bool GetDependsOnItem(string valueTableName, DependencyItemModel dependence, IndexItemModel item, out IndexItemModel dependsOnItem) { var referenceKeyParams = dependence.ReferenceKeysArr; var foreignKeyParams = dependence.ForeignKeysArr; var @params = foreignKeyParams .Select((fk, i) => new { fk, i }) .Select(fk => new KeyValuePair <string, string>(referenceKeyParams[fk.i], item[fk.fk].ToString())) .ToDictionary(fk => fk.Key, fk => fk.Value); var dynamicParams = new DynamicParameters(); foreach (var p in @params) { if (string.IsNullOrWhiteSpace(p.Value)) // An item has dependency with NULL value is not depends on anything { dependsOnItem = null; return(false); } dynamicParams.Add(p.Key, p.Value); } var sql = $@" SELECT TOP 1 * FROM {valueTableName} WHERE {string.Join(" AND ", @params.Select(p => $@"[{p.Key}] = @{p.Key}"))} "; dependsOnItem = _connection.Query <object>(sql, param: dynamicParams, transaction: _transaction) .Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))).FirstOrDefault(); return(true); }
public IEnumerable <IndexItemModel> GetIndexedItemsBySourceId(IIndexModel model, string id) { var items = _connection.Query($@" SELECT * FROM [{model.ValueTableName}] WHERE [SourceId] = @Id", param: new { Id = id }, transaction: _transaction) as IEnumerable <IDictionary <string, object> >; return(items?.Select(i => IndexItemModel.FromJObject(JObject.FromObject(i)))); }
public IndexItemModel GetIndexedItemDestinationId(IIndexModel model, string id) { var items = _connection.Query($@" SELECT * FROM [{model.ValueTableName}] WHERE [DestinationId] = @Id", param: new { Id = id }, transaction: _transaction) as IEnumerable <IDictionary <string, object> >; var item = items?.FirstOrDefault(); return(item != null?IndexItemModel.FromJObject(JObject.FromObject(item)) : null); }
public IEnumerable <IndexItemModel> GetIndexChangedItems( IIndexModel model, int limit, int offset, out int totalCount) { var @params = new DynamicParameters(); @params.Add("Limit", limit > 0 ? limit : 100); @params.Add("Offset", offset); @params.Add("ChangedState", ItemState.Changed); /** * Do not ignore RelatedItemNotFound and RelatedItemNotSync, * These items should be checked in code logic * RelatedItemNotFound: it might be found later * RelatedItemNotSynced: it will be synced later */ @params.Add("ExcludeStates", ItemState.Invalid); // TODO: RETRY COUNT? var sql = $@" SELECT v.* FROM {model.ValueTableName} v WHERE v.[DestinationId] IS NULL OR v.[State] IS NULL OR v.[State] = 0 OR ( (v.[State] & @ChangedState) > 0 AND (v.[State] & @ExcludeStates) = 0 ) ORDER BY v.[RetryCount], v.[Id] OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY; "; var countSql = $@" SELECT COUNT(v.Id) FROM {model.ValueTableName} v WHERE v.[DestinationId] IS NULL OR v.[State] IS NULL OR v.[State] = 0 OR ( (v.[State] & @ChangedState) > 0 AND (v.[State] & @ExcludeStates) = 0 ) "; totalCount = _connection .Query <int>(countSql, param: @params, transaction: _transaction) .FirstOrDefault(); var result = _connection .Query(sql, param: @params, transaction: _transaction) as IEnumerable <IDictionary <string, object> >; return(result.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d)))); }
public virtual IMapper Map(IEnumerable <object> data) { if (data == null) { return(this); } var destinationIdKey = Options?.FirstOrDefault(o => o.Name == "mapper_id_key").Value; var foreignKeyStr = Options?.FirstOrDefault(o => o.Name == "mapper_foreign_keys").Value; var foreignKeys = Regex.Split(foreignKeyStr, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase); var referenceKeyStr = Options?.FirstOrDefault(o => o.Name == "mapper_reference_keys").Value; var referenceKeys = Regex.Split(referenceKeyStr, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase); var affectedRows = 0; foreach (var item in data) { var jItem = JObject.FromObject(item); var destinationId = jItem.GetValue(destinationIdKey).ToString(); var queryParams = new DynamicParameters(); var conditions = new List <string>(); // !!! NEVER CHECK FOR DEPENDENCIES. An entity cannot be mapped if its values are required to be check SOURCE v.s DEST with dependencies for (var i = 0; i < foreignKeys.Length; i++) { var foreignValue = jItem.GetValue(foreignKeys[i]); // Foreign values should NEVER BE NULL conditions.Add($@"[{referenceKeys[i]}] = @{referenceKeys[i]}"); queryParams.Add(referenceKeys[i], foreignValue?.ToString()); } var indexedItem = Connection .Query <object>($@" SELECT * FROM [{EntityModel.ValueTableName}] WHERE {string.Join(" AND ", conditions)} ", queryParams) .Select(i => IndexItemModel.FromJObject(JObject.FromObject(i))) .FirstOrDefault(); if (indexedItem != null) { affectedRows += Connection .Execute($@" UPDATE [{EntityModel.ValueTableName}] SET [DestinationId] = @DestinationId WHERE [Id] = @Id ", new { Id = indexedItem.GetId(), DestinationId = jItem.GetValue(destinationIdKey).ToString() }); } } Report($@"Mapped {affectedRows} item(s)."); return(this); }
public IEnumerable <IndexItemModel> GetIndexedItems( IIndexModel model, IEnumerable <FilterArgument> filters, int limit, int offset, out int totalCount) { var @params = new DynamicParameters(); @params.Add("Limit", limit > 0 ? limit : 100); @params.Add("Offset", offset); var filterStrs = new List <string>(); var @where = string.Empty; if (filters != null && filters.Count() > 0) { foreach (var filter in filters) { var paramName = StringExtensions.StringExtensions.Random(10); filterStrs.Add($@"[{filter.Field}] {filter.Op} @Param_{paramName}"); @params.Add($@"Param_{paramName}", filter.Target); } //var condition = string.Join(" AND ", filters.Select(f => $@"[{f.Field}] {f.Op} {}")); @where = string.Join(" AND ", filterStrs); if (!string.IsNullOrWhiteSpace(@where)) { @where = $"WHERE {@where}"; } } var sql = $@" SELECT * FROM {model.ValueTableName} {@where} ORDER BY [Id] OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY; "; var countSql = $@" SELECT COUNT(*) FROM {model.ValueTableName} {@where} "; totalCount = _connection .Query <int>(countSql, param: @params, transaction: _transaction) .FirstOrDefault(); var result = _connection .Query(sql, param: @params, transaction: _transaction) as IEnumerable <IDictionary <string, object> >; return(result.Select(d => IndexItemModel.FromJObject(JObject.FromObject(d)))); }
public IndexItemModel GetDependsOnItem(string valueTableName, DependencyItemModel dependence, IndexItemModel item) { var referenceKeyParams = Regex.Split(dependence.ReferenceKeys, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase); var foreignKeyParams = Regex.Split(dependence.ForeignKeys, "[,;|]", RegexOptions.Multiline | RegexOptions.IgnoreCase); var @params = foreignKeyParams .Select((fk, i) => new { fk, i }) .Select(fk => new KeyValuePair <string, string>(referenceKeyParams[fk.i], item[fk.fk].ToString())) .ToDictionary(fk => fk.Key, fk => fk.Value); var dynamicParams = new DynamicParameters(); foreach (var p in @params) { dynamicParams.Add(p.Key, p.Value); } var sql = $@" SELECT TOP 1 * FROM {valueTableName} WHERE {string.Join(" AND ", @params.Select(p => $@"[{p.Key}] = @{p.Key}"))} "; var dependsOnItem = _connection.Query <object>(sql, param: dynamicParams, transaction: _transaction) .Select(d => IndexItemModel.FromJObject(JObject.FromObject(d))).FirstOrDefault(); return(dependsOnItem); }