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 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));
}
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 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));
}
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 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 In(TypedExpression left, TypedExpression exp)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(left.RawQuery)
.Append(" IN (")
.Append(exp.RawQuery)
.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
public static TypedExpression In(TypedExpression left, IEnumerable array)
{
RawQuery rq = new RawQuery();
rq.AppendSurround(left.RawQuery)
.Append(" = ANY(")
.Append(PgTypesConverter.ConvertObjectToPgValue(array))
.Append(")");
return(new TypedExpression(rq, KDPgValueTypeInstances.Boolean));
}
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 NestedSurrounded()
{
var rq1 = new RawQuery();
rq1.Append("id = 12");
var rq2 = new RawQuery();
rq2.Append("id = 34");
var rq3 = new RawQuery();
rq3.AppendSurround(rq1);
rq3.Append(" OR ");
rq3.AppendSurround(rq2);
Utils.AssertRawQuery(rq1, "id = 12");
Utils.AssertRawQuery(rq2, "id = 34");
Utils.AssertRawQuery(rq3, "(id = 12) OR (id = 34)");
}
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()
{
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);
}