public override void ConfigureCommand(NpgsqlCommand command)
{
var sql = Selector.ToSelectClause(null);
sql += $" where 1 = 1";
if (_version > 0)
{
var versionParam = command.AddParameter(_version);
sql += " and version <= :" + versionParam.ParameterName;
}
if (_before.HasValue)
{
var beforeParam = command.AddParameter(_before.Value);
sql += " and timestamp <= :" + beforeParam.ParameterName;
}
if (_after.HasValue)
{
var afterParam = command.AddParameter(_after.Value);
sql += " and timestamp >= :" + afterParam.ParameterName;
}
sql += " order by version";
command.AppendQuery(sql);
}
public void add_second_parameter()
{
var command = new NpgsqlCommand();
command.AddParameter("a");
var param = command.AddParameter("b");
param.ParameterName.ShouldBe("p1");
}
public static IList <string> GetStringList(this IManagedConnection runner, string sql, params object[] parameters)
{
var list = new List <string>();
var cmd = new NpgsqlCommand();
cmd.WithText(sql);
parameters.Each(x =>
{
var param = cmd.AddParameter(x);
cmd.CommandText = cmd.CommandText.UseParameter(param);
});
using (var reader = runner.ExecuteReader(cmd))
{
while (reader.Read())
{
list.Add(reader.GetString(0));
}
reader.Close();
}
return(list);
}
public NpgsqlParameter AddParameter(object query, NpgsqlCommand command)
{
var newValue = _getter((TObject)query);
var param = command.AddParameter(newValue);
return(param);
}
public void ConfigureCommand(NpgsqlCommand command)
{
var sql = _sql;
if (!sql.Contains("select", StringComparison.OrdinalIgnoreCase))
{
var mapping = _schema.MappingFor(typeof(T)).ToQueryableDocument();
var tableName = mapping.Table.QualifiedName;
sql = "select data from {0} {1}".ToFormat(tableName, sql);
}
var firstParameter = _parameters.FirstOrDefault();
if (_parameters.Length == 1 && firstParameter != null && firstParameter.IsAnonymousType())
{
command.AddParameters(firstParameter);
}
else
{
_parameters.Each(x =>
{
var param = command.AddParameter(x);
sql = sql.UseParameter(param);
});
}
command.AppendQuery(sql);
}
public NpgsqlParameter AddParameter(object query, NpgsqlCommand command)
{
var dict = BuildDictionary((TQuery)query);
var array = new IDictionary <string, object>[] { dict };
dict = new Dictionary <string, object> {
{ _pathToCollection.Last().Name, array }
};
_pathToCollection.Reverse().Skip(1).Each(member =>
{
dict = new Dictionary <string, object> {
{ member.Name, dict }
};
});
var json = _serializer.ToCleanJson(dict);
var param = command.AddParameter(json);
param.NpgsqlDbType = NpgsqlDbType.Jsonb;
return(param);
}
public void ConfigureCommand(NpgsqlCommand command)
{
var parameter = command.AddParameter(_id);
var sql =
$"select {_mapping.SelectFields().Join(", ")} from {_mapping.Table.QualifiedName} as d where id = :{parameter.ParameterName}";
command.AppendQuery(sql);
}
public static NpgsqlCommand Returns(this NpgsqlCommand command, string name, NpgsqlDbType type)
{
var parameter = command.AddParameter(name);
parameter.NpgsqlDbType = type;
parameter.Direction = ParameterDirection.ReturnValue;
return(command);
}
public void ConfigureCommand(NpgsqlCommand command)
{
var sql = ToSelectClause(null);
var param = command.AddParameter(_streamId);
sql += " where id = :" + param.ParameterName;
command.AppendQuery(sql);
}
public string ToSql(NpgsqlCommand command)
{
var json = _serializer.ToCleanJson(_dictionary);
var param = command.AddParameter(json);
param.NpgsqlDbType = NpgsqlDbType.Jsonb;
return($"d.data @> :{param.ParameterName}");
}
private NpgsqlParameter addParameter(object value, NpgsqlDbType dbType)
{
var name = "p" + _counter++;
var param = _command.AddParameter(name, value);
param.NpgsqlDbType = dbType;
return(param);
}
private string buildSubQuery(SubQueryExpression subQuery, NpgsqlCommand command)
{
var contains = subQuery.QueryModel.ResultOperators.OfType <ContainsResultOperator>().FirstOrDefault();
if (contains == null)
{
throw new NotSupportedException("Only the Contains() operator is supported on subqueries within Collection.Any() searches");
}
// build rhs of ?|
var from = subQuery.QueryModel.MainFromClause.FromExpression as ConstantExpression;
if (from == null || !supportedTypes.Any(supp => isListOrArrayOf(from.Type, supp)))
{
throwNotSupportedContains();
}
var fromParam = command.AddParameter(from.Value);
fromParam.NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Text;
// check/build lhs of ?|
var item = contains.Item as QuerySourceReferenceExpression;
if (item == null)
{
throwNotSupportedContains();
}
if (!supportedTypes.Any(supp => supp == item.ReferencedQuerySource.ItemType))
{
throwNotSupportedContains();
}
var itemSource = item.ReferencedQuerySource as MainFromClause;
if (itemSource == null)
{
throwNotSupportedContains();
}
var member = itemSource.FromExpression as MemberExpression;
if (member == null)
{
throwNotSupportedContains();
}
var visitor = new FindMembers();
visitor.Visit(member);
var members = visitor.Members;
if (!members.Any())
{
throwNotSupportedContains();
}
var path = members.Select(m => m.Name).Join("'->'");
return($"data->'{path}' ?| :{fromParam.ParameterName}");
}
public void ConfigureCommand(NpgsqlCommand command)
{
var sql = "select " +
string.Join(", ", _fields.OrderBy(kv => kv.Value).Select(kv => kv.Key)) +
$" from {_mapping.Table.QualifiedName} where id = :id";
command.AppendQuery(sql);
command.AddParameter("id", _id);
}
public void ConfigureCommand(NpgsqlCommand command)
{
var sql =
$"select {DocumentMapping.VersionColumn}, {DocumentMapping.LastModifiedColumn} from {_mapping.Table.QualifiedName} where id = :id";
command.AppendQuery(sql);
command.AddParameter("id", _id);
}
public static string ApplySkip(this QueryModel model, NpgsqlCommand command, string sql)
{
var skip = model.FindOperators <SkipResultOperator>().LastOrDefault();
if (skip != null)
{
var param = command.AddParameter(skip.Count.Value());
sql += " OFFSET :" + param.ParameterName;
}
return(sql);
}
public string ToSql(NpgsqlCommand command)
{
var sql = _sql;
_parameters.Each(x =>
{
var param = command.AddParameter(x.Item1, x.Item2);
sql = sql.ReplaceFirst(_token, ":" + param.ParameterName);
});
return(sql);
}
private IEnumerable <string> buildBinary(BinaryExpression[] binaryExpressions, NpgsqlCommand command)
{
if (!binaryExpressions.Any())
{
yield break;
}
var dictionary = new Dictionary <string, object>();
// Are we querying directly againt the elements as you would for primitive types?
if (binaryExpressions.All(x => x.Left is QuerySourceReferenceExpression && x.Right is ConstantExpression))
{
if (binaryExpressions.Any(x => x.NodeType != ExpressionType.Equal))
{
throw new NotSupportedException("Only the equality operator is supported on Collection.Any(x => x) searches directly against the element");
}
var values = binaryExpressions.Select(x => x.Right.Value()).ToArray();
if (_members.Length == 1)
{
dictionary.Add(_members.Single().Name, values);
}
else
{
throw new NotSupportedException();
}
}
else
{
var search = new Dictionary <string, object>();
binaryExpressions.Each(x => gatherSearch(x, search));
if (_members.Length == 1)
{
dictionary.Add(_members.Single().Name, new[] { search });
}
else
{
throw new NotImplementedException();
}
}
var json = _serializer.ToCleanJson(dictionary);
var param = command.AddParameter(json);
param.NpgsqlDbType = NpgsqlDbType.Jsonb;
yield return($"d.data @> :{param.ParameterName}");
}
public void add_first_parameter()
{
var command = new NpgsqlCommand();
var param = command.AddParameter("a");
param.Value.ShouldBe("a");
param.ParameterName.ShouldBe("p0");
param.NpgsqlDbType.ShouldBe(NpgsqlDbType.Text);
command.Parameters.ShouldContain(param);
}
public NpgsqlParameter AddParameter(object query, NpgsqlCommand command)
{
var newValue = _getter((TObject)query);
if (Parser != null)
{
newValue = Parser.FormatValue(null, newValue.ToString()).As <TProperty>();
}
var param = command.AddParameter(newValue);
return(param);
}
public NpgsqlCommand BuildCommand(string sql, params object[] args)
{
var cmd = new NpgsqlCommand(sql);
if (args == null)
{
return(cmd);
}
foreach (var arg in args)
{
cmd.AddParameter(arg);
}
return(cmd);
}
public override void ConfigureCommand(NpgsqlCommand command)
{
var sql = _selector.ToSelectClause(null);
var param = command.AddParameter(_streamId);
sql += $" where stream_id = :{param.ParameterName}";
if (_version > 0)
{
var versionParam = command.AddParameter(_version);
sql += " and version <= :" + versionParam.ParameterName;
}
if (_timestamp.HasValue)
{
var timestampParam = command.AddParameter(_timestamp.Value);
sql += " and timestamp <= :" + timestampParam.ParameterName;
}
sql += " order by version";
command.AppendQuery(sql);
}
public void ConfigureCommand <T>(NpgsqlCommand cmd, string sql, object[] parameters)
{
var mapping = _schema.MappingFor(typeof(T));
if (!sql.Contains("select", StringComparison.OrdinalIgnoreCase))
{
var tableName = mapping.TableName;
sql = "select data from {0} {1}".ToFormat(tableName, sql);
}
parameters.Each(x =>
{
var param = cmd.AddParameter(x);
sql = sql.UseParameter(param);
});
cmd.AppendQuery(sql);
}
public static string ApplyTake(this QueryModel model, NpgsqlCommand command, int limit, string sql)
{
if (limit > 0)
{
sql += " LIMIT " + limit;
}
else
{
var take = model.FindOperators <TakeResultOperator>().LastOrDefault();
if (take != null)
{
var param = command.AddParameter(take.Count.Value());
sql += " LIMIT :" + param.ParameterName;
}
}
return(sql);
}
public void ConfigureCommand(NpgsqlCommand command)
{
var sql = _sql;
if (!sql.Contains("select", StringComparison.OrdinalIgnoreCase))
{
var mapping = _schema.MappingFor(typeof(T)).ToQueryableDocument();
var tableName = mapping.Table.QualifiedName;
sql = "select data from {0} {1}".ToFormat(tableName, sql);
}
_parameters.Each(x =>
{
var param = command.AddParameter(x);
sql = sql.UseParameter(param);
});
command.AppendQuery(sql);
}
public IEnumerable <T> Query <T>(string sql, params object[] parameters)
{
if (!sql.Contains("select"))
{
var tableName = _schema.StorageFor(typeof(T)).TableName;
sql = "select data from {0} {1}".ToFormat(tableName, sql);
}
var cmd = new NpgsqlCommand();
parameters.Each(x =>
{
var param = cmd.AddParameter(x);
sql = sql.UseParameter(param);
});
cmd.CommandText = sql;
return(_serializer.FromJson <T>(_runner.QueryJson(cmd)));
}
public static IEnumerable <T> Fetch <T>(this NpgsqlCommand cmd, string sql, Func <DbDataReader, T> transform, params object[] parameters)
{
cmd.WithText(sql);
parameters.Each(x =>
{
var param = cmd.AddParameter(x);
cmd.CommandText = cmd.CommandText.UseParameter(param);
});
var list = new List <T>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(transform(reader));
}
}
return(list);
}
private Task <T> load <T>(object id) where T : class
{
if (_identityMap.Has <T>(id))
{
return(Task.FromResult(_identityMap.Retrieve <T>(id)));
}
var source = new TaskCompletionSource <T>();
var mapping = _schema.MappingFor(typeof(T));
var parameter = _command.AddParameter(id);
_command.AppendQuery(
$"select {mapping.SelectFields("d")} from {mapping.TableName} as d where id = :{parameter.ParameterName}");
var handler = new SingleResultReader <T>(source, _schema.StorageFor(typeof(T)), _identityMap);
addHandler(handler);
return(source.Task);
}
private NpgsqlCommand BuildCommand <T>(string sql, params object[] parameters)
{
var cmd = new NpgsqlCommand();
var mapping = _schema.MappingFor(typeof(T));
if (!sql.Contains("select", StringComparison.OrdinalIgnoreCase))
{
var tableName = mapping.TableName;
sql = "select data from {0} {1}".ToFormat(tableName, sql);
}
parameters.Each(x =>
{
var param = cmd.AddParameter(x);
sql = sql.UseParameter(param);
});
cmd.CommandText = sql;
return(cmd);
}
public NpgsqlParameter AddParameter(object value, NpgsqlDbType?dbType = null)
{
return(_command.AddParameter(value, dbType));
}
public string ToSql(NpgsqlCommand command)
{
var wheres = _expression
.QueryModel
.BodyClauses
.OfType <WhereClause>()
.Select(x => x.Predicate)
.ToArray();
if (!wheres.All(x => x is BinaryExpression))
{
throw new NotImplementedException();
}
var visitor = new FindMembers();
visitor.Visit(_expression.QueryModel.MainFromClause.FromExpression);
var members = visitor.Members;
var binaryExpressions = wheres.OfType <BinaryExpression>().ToArray();
var dictionary = new Dictionary <string, object>();
// Are we querying directly againt the elements as you would for primitive types?
if (binaryExpressions.All(x => x.Left is QuerySourceReferenceExpression && x.Right is ConstantExpression))
{
if (binaryExpressions.Any(x => x.NodeType != ExpressionType.Equal))
{
throw new NotSupportedException("Only the equality operator is supported on Collection.Any(x => x) searches directly against the element");
}
var values = binaryExpressions.Select(x => x.Right.Value()).ToArray();
if (members.Count == 1)
{
dictionary.Add(members.Single().Name, values);
}
else
{
throw new NotSupportedException();
}
}
else
{
var search = new Dictionary <string, object>();
binaryExpressions.Each(x => gatherSearch(x, search));
if (members.Count == 1)
{
dictionary.Add(members.Single().Name, new[] { search });
}
else
{
throw new NotImplementedException();
}
}
var json = _serializer.ToCleanJson(dictionary);
var param = command.AddParameter(json);
param.NpgsqlDbType = NpgsqlDbType.Jsonb;
return($"d.data @> :{param.ParameterName}");
}