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));
        }
示例#4
0
        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));
        }
示例#6
0
        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));
        }
示例#10
0
        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));
        }
示例#11
0
        public void Simple()
        {
            var rq = new RawQuery();

            rq.Append("id = 123");

            Utils.AssertRawQuery(rq, "id = 123");
        }
示例#12
0
        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);
        }
示例#13
0
        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);
                    }
示例#14
0
        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));
        }
示例#15
0
 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");
     }
 }
示例#16
0
        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");
        }
示例#17
0
        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));
        }
示例#18
0
        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);
        }
示例#19
0
        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));
        }
示例#20
0
        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);
        }
示例#21
0
        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));
        }
示例#22
0
        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));
        }
示例#23
0
        // 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));
        }
示例#24
0
        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);
        }
示例#25
0
        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);
        }