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); }