public static TypedExpression Add(TypedExpression left, TypedExpression right)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(left.RawQuery);
if (left.Type == KDPgValueTypeInstances.String && right.Type == KDPgValueTypeInstances.String)
{
rq.Append(" || ");
}
else if (left.Type == KDPgValueTypeInstances.DateTime && right.Type == KDPgValueTypeInstances.Interval)
{
rq.Append(" + ");
}
else if (left.Type == right.Type)
{
rq.Append(" + ");
}
else
{
throw new Exception("unsupported operation");
}
rq.AppendSurround(right.RawQuery);
return(new TypedExpression(rq, left.Type));
}
public static TypedExpression ArrayLength(TypedExpression exp)
{
RawQuery rq = new RawQuery();
switch (exp.Type)
{
case KDPgValueTypeBinary _:
rq.AppendFuncInvocation("octet_length", exp.RawQuery);
break;
case KDPgValueTypeString _:
rq.AppendFuncInvocation("LENGTH", exp.RawQuery);
break;
case KDPgValueTypeArray _:
rq.Append("array_length(");
rq.Append(exp.RawQuery);
rq.Append(",1)");
break;
default:
throw new Exception("Invalid value to get array count");
}
return(new TypedExpression(rq, KDPgValueTypeInstances.Integer));
}
public static TypedExpression Eq(TypedExpression left, TypedExpression right)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(left.RawQuery);
if (right.Type == KDPgValueTypeInstances.Null)
{
rq.Append(" IS NULL");
}
else
{
rq.Append(" = ");
if (left.Type == KDPgValueTypeInstances.Json)
{
rq.Append("to_jsonb(");
rq.AppendSurround(right.RawQuery);
rq.Append(")");
}
else
{
rq.AppendSurround(right.RawQuery);
}
}
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
public void WithColumnCombined()
{
var t1 = ModelsRegistry.GetTable <MyModel>();
var t2 = ModelsRegistry.GetTable <MyModel2>();
var rq1 = new RawQuery();
rq1.AppendColumn(NodeVisitor.EvaluateFuncExpressionToColumn <MyModel>(x => x.Name), new RawQuery.TableNamePlaceholder(t1, "M1"));
rq1.Append(" = 123");
rq1.ApplyAlias("M1", "t1");
var rq2 = new RawQuery();
rq2.AppendColumn(NodeVisitor.EvaluateFuncExpressionToColumn <MyModel2>(x => x.Name1), new RawQuery.TableNamePlaceholder(t2, "M2"));
rq2.Append(" = 456");
rq2.ApplyAlias("M2", "t2");
var rq = new RawQuery();
rq.Append(rq1);
rq.Append(", ");
rq.Append(rq2);
Utils.AssertRawQueryWithAliases(rq, @"t1.""name"" = 123, t2.name1 = 456");
}
public static TypedExpression ToUpper(TypedExpression value)
{
RawQuery rq = new RawQuery();
rq.Append("upper(");
rq.Append(value.RawQuery);
rq.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
public OrderBuilder <TModel> OrderBy <T>(Expression <Func <TModel, T> > exp)
{
var e = NodeVisitor.VisitFuncExpression(exp);
_rq.AppendSeparatorIfNotEmpty();
_rq.Append(e.RawQuery);
return(this);
}
public static TypedExpression CastJsonToType(TypedExpression value, KDPgValueType pgType)
{
RawQuery rq = new RawQuery();
rq.Append("(");
rq.AppendSurround(value.RawQuery);
rq.Append("#>>'{}')::");
rq.Append(pgType.PostgresTypeName);
return(new TypedExpression(rq, pgType));
}
public static TypedExpression Not(TypedExpression exp)
{
RawQuery rq = new RawQuery();
rq.Append("NOT(");
rq.Append(exp.RawQuery);
rq.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
public static TypedExpression CurrSeqValueOfTable(KdPgColumnDescriptor column)
{
RawQuery rq = new RawQuery();
rq.Append("currval(pg_get_serial_sequence(");
rq.Append(EscapeUtils.EscapePostgresString(EscapeUtils.QuoteTable(column.Table.Name, column.Table.Schema)));
rq.Append(",");
rq.Append(EscapeUtils.EscapePostgresString(column.Name));
rq.Append("))");
return(new TypedExpression(rq, KDPgValueTypeInstances.Integer));
}
public static TypedExpression SetConfigText(string name, TypedExpression value, bool local)
{
if (!VariableRegex.IsMatch(name))
{
throw new Exception("invalid variable name, allowed [a-zA-Z0-9_-]");
}
RawQuery rq = new RawQuery();
rq.Append($"set_config('vars.{name}', ");
rq.Append(value.RawQuery);
rq.Append("::text, ", local ? "true" : "false", ");");
return(new TypedExpression(rq, KDPgValueTypeInstances.Null));
}
public void Simple()
{
var rq = new RawQuery();
rq.Append("id = 123");
Utils.AssertRawQuery(rq, "id = 123");
}
public RawQuery GetRawQuery()
{
RawQuery rq = new RawQuery();
rq.Append("DELETE FROM ")
.AppendTableName(Table.Name, Table.Schema);
var whereRawQuery = _whereBuilder.GetRawQuery();
if (!whereRawQuery.IsEmpty)
{
rq.Append(" WHERE ");
rq.Append(whereRawQuery);
}
rq.SkipExplicitColumnTableNames();
return(rq);
}
public static SelectFromBuilder FromCombinedExpression <TCombinedModel, TNewModel>(TablesList tablesList, Expression <Func <TCombinedModel, TNewModel> > prBody)
{
var builder = new SelectFromBuilder();
builder.LeftJoinsExpressions = tablesList.JoinExpressions;
var options = new EvaluationOptions();
foreach (var tablePlaceholder in tablesList.Tables)
{
builder.AddTable(tablePlaceholder);
// options.ParameterToTableAlias.Add(tablePlaceholder.Name, tablePlaceholder);
}
var tableToPlaceholder = tablesList.Tables.ToDictionary(x => x.Name);
TypedExpression exp;
switch (prBody.Body)
{
/* For:
* .Select(x => new {
* M1 = x.M1,
* M2_name = x.M2.Name1,
* M3_calc = x.M2.Id * 2,
* })
*/
case NewExpression newExpression:
{
IEnumerable <Expression> args = newExpression.Arguments;
var resultProcessor = new AnonymousTypeSelectResultProcessor <TNewModel>();
builder.SelectResultProcessor = resultProcessor;
foreach (var argExpression in args)
{
// Member is Table (like M1 = x.M1)
if (argExpression is MemberExpression memberExpression && ModelsRegistry.IsTable(memberExpression.Type))
{
var tablePlaceholder = tableToPlaceholder[memberExpression.Member.Name];
var table = tablePlaceholder.Table;
var tableTestRawQuery = new RawQuery();
tableTestRawQuery.AppendTable(tablePlaceholder);
tableTestRawQuery.Append(" IS NULL");
builder.AddSelectPart(tableTestRawQuery, KDPgValueTypeInstances.Boolean);
foreach (var column in table.Columns)
{
var rq = new RawQuery();
rq.AppendColumn(column, tablePlaceholder);
builder.AddSelectPart(rq, column.Type);
}
resultProcessor.AddModelEntry(table);
}
private static TypedExpression BinaryOperator(string op, TypedExpression left, TypedExpression right, KDPgValueType outType)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(left.RawQuery);
rq.Append($" {op} ");
rq.AppendSurround(right.RawQuery);
return(new TypedExpression(rq, outType));
}
public static TypedExpression Contains(TypedExpression value, TypedExpression value2)
{
if (value.Type is KDPgValueTypeArray)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(value2.RawQuery);
rq.Append(" = ANY(");
rq.AppendSurround(value.RawQuery);
rq.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
else if (value.Type is KDPgValueTypeString)
{
return(Like(value, value2));
}
else
{
throw new InvalidOperationException("Contains cannot be used on non-list");
}
}
public void Nested()
{
var rq1 = new RawQuery();
rq1.Append("id = 12");
var rq2 = new RawQuery();
rq2.Append("id = 34");
var rq3 = new RawQuery();
rq3.Append(rq1);
rq3.Append(" OR ");
rq3.Append(rq2);
Utils.AssertRawQuery(rq1, "id = 12");
Utils.AssertRawQuery(rq2, "id = 34");
Utils.AssertRawQuery(rq3, "id = 12 OR id = 34");
}
public static TypedExpression Substring(TypedExpression value, TypedExpression start, TypedExpression length)
{
RawQuery rq = new RawQuery();
if (!(value.Type is KDPgValueTypeString))
{
throw new Exception("Substring must be string");
}
if (!(start.Type is KDPgValueTypeInteger))
{
throw new Exception("start must be integer");
}
if (!(length.Type is KDPgValueTypeInteger))
{
throw new Exception("start must be integer");
}
rq.Append("substring(");
rq.AppendSurround(value.RawQuery);
rq.Append(" from ");
rq.Append(start.RawQuery);
rq.Append(" for ");
rq.Append(length.RawQuery);
rq.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.String));
}
public override Task <SelectQueryResult <TOut> > QueryAsync <TModel, TOut>(SelectQuery <TModel, TOut> selectQuery)
{
_combinedRawQuery.Append(selectQuery.GetRawQuery());
_combinedRawQuery.Append(";\n");
IsEmpty = false;
var tcs = new TaskCompletionSource <SelectQueryResult <TOut> >();
_resultProcessors.Add(async reader => {
tcs.SetResult(await selectQuery.ReadResultAsync(reader));
});
return(tcs.Task);
}
public static TypedExpression GetConfigText(string name)
{
if (!VariableRegex.IsMatch(name))
{
throw new Exception("invalid variable name, allowed [a-zA-Z0-9_-]");
}
RawQuery rq = new RawQuery();
rq.Append($"current_setting('vars.{name}')::text");
return(new TypedExpression(rq, KDPgValueTypeInstances.String));
}
public RawQuery GetRawQuery()
{
RawQuery rq = new RawQuery();
rq.Append("UPDATE ")
.AppendTableName(Table.Name, Table.Schema)
.Append("\n");
rq.Append("SET ");
bool first = true;
foreach (var(column, typedExpression) in _updateStatementsBuilder.UpdateParts)
{
if (!first)
{
rq.Append(", ");
}
rq.AppendColumnName(column.Name)
.Append(" = ")
.Append(typedExpression.RawQuery);
first = false;
}
rq.Append("\n");
var whereRawQuery = _whereBuilder.GetRawQuery();
if (!whereRawQuery.IsEmpty)
{
rq.Append("WHERE ")
.Append(whereRawQuery);
}
rq.SkipExplicitColumnTableNames();
return(rq);
}
public static TypedExpression ContainsAny(TypedExpression left, TypedExpression right)
{
RawQuery rq = new RawQuery();
if (!(left.Type is KDPgValueTypeArray))
{
throw new Exception("Contains cannot be used on non-list");
}
rq.AppendSurround(left.RawQuery);
rq.Append(" && ");
rq.AppendSurround(right.RawQuery);
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
private static TypedExpression LikeBuilder(TypedExpression value, TypedExpression text, bool escape, bool anyStart, bool anyEnd, bool caseInsensitive)
{
RawQuery rq = new RawQuery();
if (!(value.Type is KDPgValueTypeString))
{
throw new Exception("value must be string");
}
if (!(text.Type is KDPgValueTypeString))
{
throw new Exception("value2 must be string");
}
rq.AppendSurround(value.RawQuery);
rq.Append(caseInsensitive ? " ILIKE (" : " LIKE (");
if (anyStart)
{
rq.Append("'%' || ");
}
if (escape)
{
rq.Append("kdpg_escape_like(");
}
rq.Append(text.RawQuery);
if (escape)
{
rq.Append(")");
}
if (anyEnd)
{
rq.Append(" || '%'");
}
rq.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
// helpers
private static TypedExpression JoinLogicExpressions(string op, IEnumerable <TypedExpression> expressions)
{
RawQuery rq = new RawQuery();
bool first = true;
foreach (var statement in expressions)
{
if (statement.RawQuery.IsEmpty)
{
continue;
}
if (!first)
{
rq.Append($" {op} ");
}
rq.AppendSurround(statement.RawQuery);
first = false;
}
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
public RawQuery GetRawQuery()
{
RawQuery rq = _fromBuilder.GetRawQuery();
var whereRawQuery = _whereBuilder.GetRawQuery();
if (!whereRawQuery.IsEmpty)
{
rq.Append(" WHERE ");
rq.Append(whereRawQuery);
}
if (_orderBuilder != null)
{
var r = _orderBuilder.GetRawQuery();
if (!r.IsEmpty)
{
rq.Append(" ORDER BY ");
rq.Append(r);
}
}
if (_limitBuilder != null)
{
if (_limitBuilder.LimitValue.HasValue)
{
rq.Append($" LIMIT {_limitBuilder.LimitValue}");
}
if (_limitBuilder.OffsetValue.HasValue)
{
rq.Append($" OFFSET {_limitBuilder.OffsetValue}");
}
}
if (_existsQuery)
{
var existsRq = new RawQuery();
existsRq.AppendFuncInvocation("SELECT EXISTS", rq);
rq = existsRq;
}
return(rq);
}
public RawQuery GetRawQuery()
{
if (IsEmpty)
{
return(RawQuery.Create("SELECT 0"));
}
RawQuery rq = new RawQuery();
var columns = _columns.Count == 0 ? AllColumnsWithoutAutoIncrement : _columns;
rq.Append("INSERT INTO ");
rq.AppendTableName(Table.Name, Table.Schema);
rq.Append("(");
rq.AppendColumnNames(columns.Select(x => x.columnDescriptor.Name));
if (_idColumn != null)
{
if (columns.Count > 0)
{
rq.Append(",");
}
rq.AppendColumnName(_idColumn.Name);
}
rq.Append(")");
rq.Append(" VALUES ");
var first = true;
foreach (var obj in _objects)
{
if (!first)
{
rq.Append(",");
}
rq.Append("(");
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
if (i > 0)
{
rq.Append(",");
}
if (column.subquery == null)
{
object val = ModelsRegistry.GetModelValueByColumn(obj, column.columnDescriptor);
var npgValue = PgTypesConverter.ConvertToPgValue(column.columnDescriptor.Type, val);
rq.Append(npgValue);
}
else
{
rq.AppendSurround(column.subquery.GetRawQuery());
}
}
if (_idColumn != null)
{
if (columns.Count > 0)
{
rq.Append(",");
}
rq.Append(ExpressionBuilders.CurrSeqValueOfTable(_idRefColumn).RawQuery);
}
rq.Append(")");
first = false;
}
if (_onInsertConflict == OnInsertConflict.DoNothing)
{
rq.Append(" ON CONFLICT DO NOTHING ");
}
if (_onInsertConflict == OnInsertConflict.DoUpdate)
{
rq.Append(" ON CONFLICT (");
var fields = new FieldListBuilder <TModel>();
_onInsertConflictUpdateFields(fields);
first = true;
foreach (var column in fields.Fields)
{
if (!first)
{
rq.Append(", ");
}
rq.AppendColumnName(column.Name);
first = false;
}
rq.Append(") DO UPDATE SET ");
var updateStatementsBuilder = new UpdateStatementsBuilder <TModel>();
_onInsertConflictUpdate(updateStatementsBuilder);
first = true;
foreach (var(column, typedExpression) in updateStatementsBuilder.UpdateParts)
{
if (!first)
{
rq.Append(", ");
}
rq.AppendColumnName(column.Name)
.Append(" = ")
.Append(typedExpression.RawQuery);
first = false;
}
}
if (TableModel.PrimaryKey != null)
{
rq.Append(" RETURNING ");
rq.AppendColumnName(TableModel.PrimaryKey.Name);
}
rq.Append(";");
if (_outputVariable != null)
{
rq.Append(" SELECT ");
rq.Append(ExpressionBuilders.SetConfigText(_outputVariable, ExpressionBuilders.LastVal(), true).RawQuery);
}
rq.SkipExplicitColumnTableNames();
return(rq);
}