public void add_second_parameter()
{
var command = new NpgsqlCommand();
command.AddParameter("a");
var param = command.AddParameter("b");
param.ParameterName.ShouldBe("arg1");
}
public string ToSql(NpgsqlCommand command)
{
var json = _serializer.ToCleanJson(_dictionary);
var param = command.AddParameter(json);
param.NpgsqlDbType = NpgsqlDbType.Jsonb;
return $"d.data @> :{param.ParameterName}";
}
public string ToSql(NpgsqlCommand command)
{
var sql = _sql;
_parameters.Each(x =>
{
var param = command.AddParameter(x);
sql = sql.ReplaceFirst("?", ":" + param.ParameterName);
});
return sql;
}
public void add_first_parameter()
{
var command = new NpgsqlCommand();
var param = command.AddParameter("a");
param.Value.ShouldBe("a");
param.ParameterName.ShouldBe("arg0");
param.NpgsqlDbType.ShouldBe(NpgsqlDbType.Text);
command.Parameters.ShouldContain(param);
}
public string ToSql(NpgsqlCommand command)
{
var param = command.AddParameter(_values, _dbType);
return $"{_locator} = ANY(:{param.ParameterName})";
}
/// <summary>
/// Transfere dados de um arquivo Excel para o banco de dados atual.
/// Conexão com o banco atual precisa estar aberta.
/// Não pára a execução se der um problema num comando de inserção específico.
/// </summary>
/// <returns>Número de linhas transferidas.</returns>
/// <param name="p_filename">Nome do arquivo de origem.</param>
/// <param name="p_separator">Separador de campos do arquivo CSV.</param>
/// <param name="p_delimitator">Delimitador de campos do arquivo CSV.</param>
/// <param name="p_header">Se deve considerar a primeira linha como cabeçalho ou não.</param>
/// <param name="p_encoding">Codificação para leitura do arquivo CSV.</param>
/// <param name="p_newtable">Nome da nova tabela a ser criada no banco de dados.</param>
/// <param name="p_progress">Evento de progresso.</param>
/// <param name="p_error">Evento de erro.</param>
public override uint TransferFromFile(string p_filename, char p_separator, char p_delimitator, bool p_header, System.Text.Encoding p_encoding, string p_newtable, Spartacus.Utils.ProgressEventClass p_progress, Spartacus.Utils.ErrorEventClass p_error)
{
Spartacus.Database.Command v_cmd;
Spartacus.Utils.Excel v_excel = null;
uint v_transfered = 0;
string v_createtable;
string v_insert;
try
{
v_excel = new Spartacus.Utils.Excel();
v_excel.Import(p_filename, p_separator, p_delimitator, p_header, p_encoding);
v_createtable = "create table " + p_newtable + " (";
for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
{
if (k < v_excel.v_set.Tables[0].Columns.Count-1)
v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar,";
else
v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar)";
}
try
{
this.Execute(v_createtable);
}
catch (Spartacus.Database.Exception e)
{
p_error.FireEvent(v_createtable + "\n" + e.v_message);
}
v_cmd = new Spartacus.Database.Command();
v_cmd.v_text = "insert into " + p_newtable + " values (";
for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
{
if (k < v_excel.v_set.Tables[0].Columns.Count-1)
v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#,";
else
v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#)";
v_cmd.AddParameter(v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower(), Spartacus.Database.Type.QUOTEDSTRING);
}
foreach (System.Data.DataRow r in v_excel.v_set.Tables[0].Rows)
{
foreach (System.Data.DataColumn c in v_excel.v_set.Tables[0].Columns)
v_cmd.SetValue(c.ColumnName, r[c].ToString());
v_insert = v_cmd.GetUpdatedText();
try
{
this.Execute(v_insert);
v_transfered++;
p_progress.FireEvent(v_transfered);
}
catch (Spartacus.Database.Exception e)
{
p_error.FireEvent(v_insert + "\n" + e.v_message);
}
}
return v_transfered;
}
catch (Spartacus.Utils.Exception e)
{
throw new Spartacus.Database.Exception(e);
}
catch (Spartacus.Database.Exception e)
{
throw e;
}
finally
{
if (v_excel != null)
{
v_excel.Clear();
v_excel = null;
}
}
}
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}";
}
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 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}";
}
public NpgsqlParameter AddParameter(object query, NpgsqlCommand command)
{
var param = command.AddParameter(_value);
return param;
}