1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
using System.Text;
namespace CrupestApi.Commons.Crud;
public interface IWhereClause : IClause
{
// Does not contain "WHERE" keyword!
(string sql, ParamList parameters) GenerateSql(string? dbProviderId = null);
}
public class CompositeWhereClause : IWhereClause
{
public CompositeWhereClause(string concatOp, bool parenthesesSubclause, params IWhereClause[] subclauses)
{
ConcatOp = concatOp;
ParenthesesSubclause = parenthesesSubclause;
Subclauses = subclauses.ToList();
}
public string ConcatOp { get; }
public bool ParenthesesSubclause { get; }
public List<IWhereClause> Subclauses { get; }
public CompositeWhereClause Eq(string column, object? value)
{
Subclauses.Add(SimpleCompareWhereClause.Eq(column, value));
return this;
}
public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null)
{
var parameters = new ParamList();
var sql = new StringBuilder();
var subclauses = GetSubclauses();
if (subclauses is null) return ("", new());
var first = true;
foreach (var subclause in Subclauses)
{
var (subSql, subParameters) = subclause.GenerateSql(dbProviderId);
if (subSql is null) continue;
if (first)
{
first = false;
}
else
{
sql.Append($" {ConcatOp} ");
}
if (ParenthesesSubclause)
{
sql.Append("(");
}
sql.Append(subSql);
if (ParenthesesSubclause)
{
sql.Append(")");
}
parameters.AddRange(subParameters);
}
return (sql.ToString(), parameters);
}
public object GetSubclauses()
{
return Subclauses;
}
}
public class AndWhereClause : CompositeWhereClause
{
public AndWhereClause(params IWhereClause[] clauses)
: this(true, clauses)
{
}
public AndWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
: base("AND", parenthesesSubclause, clauses)
{
}
public static AndWhereClause Create(params IWhereClause[] clauses)
{
return new AndWhereClause(clauses);
}
}
public class OrWhereClause : CompositeWhereClause
{
public OrWhereClause(params IWhereClause[] clauses)
: this(true, clauses)
{
}
public OrWhereClause(bool parenthesesSubclause, params IWhereClause[] clauses)
: base("OR", parenthesesSubclause, clauses)
{
}
public static OrWhereClause Create(params IWhereClause[] clauses)
{
return new OrWhereClause(clauses);
}
}
// It's simple because it only compare column and value but not expressions.
public class SimpleCompareWhereClause : IWhereClause
{
public string Column { get; }
public string Operator { get; }
public object? Value { get; }
public List<string> GetRelatedColumns()
{
return new List<string> { Column };
}
// It's user's responsibility to keep column safe, with proper escape.
public SimpleCompareWhereClause(string column, string op, object? value)
{
Column = column;
Operator = op;
Value = value;
}
public static SimpleCompareWhereClause Create(string column, string op, object? value)
{
return new SimpleCompareWhereClause(column, op, value);
}
public static SimpleCompareWhereClause Eq(string column, object? value)
{
return new SimpleCompareWhereClause(column, "=", value);
}
public static SimpleCompareWhereClause Neq(string column, object? value)
{
return new SimpleCompareWhereClause(column, "<>", value);
}
public static SimpleCompareWhereClause Gt(string column, object? value)
{
return new SimpleCompareWhereClause(column, ">", value);
}
public static SimpleCompareWhereClause Gte(string column, object? value)
{
return new SimpleCompareWhereClause(column, ">=", value);
}
public static SimpleCompareWhereClause Lt(string column, object? value)
{
return new SimpleCompareWhereClause(column, "<", value);
}
public static SimpleCompareWhereClause Lte(string column, object? value)
{
return new SimpleCompareWhereClause(column, "<=", value);
}
public (string sql, ParamList parameters) GenerateSql(string? dbProviderId = null)
{
var parameters = new ParamList();
var parameterName = parameters.AddRandomNameParameter(Value, Column);
return ($"{Column} {Operator} @{parameterName}", parameters);
}
}
public class WhereClause : AndWhereClause
{
public WhereClause()
{
}
public void Add(IWhereClause subclause)
{
Subclauses.Add(subclause);
}
}
|
