26559531993

Committed 28 May 2026 06:51AM UTC coverage: 54.902% (+0.5%) from 54.377%

Build # 26559531993

Build Type

push

github

Committed by

pwelter34

Commit Message

Add UPSERT support to query builder and SQL generators

Coverage Stats

1405 of 3347 branches covered (41.98%)

Branch coverage included in aggregate %.

179 of 227 new or added lines in 7 files covered. (78.85%)

4341 of 7119 relevant lines covered (60.98%)

308.3 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

54.17

/src/FluentCommand/Query/UpsertBuilder.cs

using FluentCommand.Query.Generators;

namespace FluentCommand.Query;

/// <summary>
/// Provides a builder for constructing SQL UPSERT statements with fluent, chainable methods.
/// </summary>
public class UpsertBuilder : UpsertBuilder<UpsertBuilder>
{
    /// <summary>
    /// Initializes a new instance of the <see cref="UpsertBuilder"/> class.
    /// </summary>
    /// <param name="queryGenerator">The <see cref="IQueryGenerator"/> used to generate SQL expressions.</param>
    /// <param name="parameters">The list of <see cref="QueryParameter"/> objects for the query.</param>
    public UpsertBuilder(
        IQueryGenerator queryGenerator,
        List<QueryParameter> parameters)
        : base(queryGenerator, parameters)
    { }
}

/// <summary>
/// Provides a generic base class for building SQL UPSERT statements with fluent, chainable methods.
/// </summary>
/// <typeparam name="TBuilder">The type of the builder for fluent chaining.</typeparam>
public abstract class UpsertBuilder<TBuilder> : StatementBuilder<TBuilder>
    where TBuilder : UpsertBuilder<TBuilder>
{
    /// <summary>
    /// Initializes a new instance of the <see cref="UpsertBuilder{TBuilder}"/> class.
    /// </summary>
    /// <param name="queryGenerator">The <see cref="IQueryGenerator"/> used to generate SQL expressions.</param>
    /// <param name="parameters">The list of <see cref="QueryParameter"/> objects for the query.</param>
    protected UpsertBuilder(
        IQueryGenerator queryGenerator,
        List<QueryParameter> parameters)
        : base(queryGenerator, parameters)
    {
    }

    /// <summary>
    /// Gets the collection of column expressions for the UPSERT statement.
    /// </summary>
    protected HashSet<ColumnExpression> ColumnExpressions { get; } = new();

    /// <summary>
    /// Gets the collection of value expressions for the UPSERT statement.
    /// </summary>
    protected HashSet<string> ValueExpressions { get; } = new();

    /// <summary>
    /// Gets the collection of key column expressions for the UPSERT statement.
    /// </summary>
    protected HashSet<ColumnExpression> KeyExpressions { get; } = new();

    /// <summary>
    /// Gets the collection of output column expressions for the UPSERT statement.
    /// </summary>
    protected HashSet<ColumnExpression> OutputExpressions { get; } = new();

    /// <summary>
    /// Gets the target table expression for the UPSERT statement.
    /// </summary>
    protected TableExpression? TableExpression { get; private set; }

    /// <summary>
    /// Sets the target table to insert into or update.
    /// </summary>
    /// <param name="tableName">The name of the table.</param>
    /// <param name="tableSchema">The schema of the table (optional).</param>
    /// <param name="tableAlias">The alias for the table (optional).</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder Into(
        string tableName,
        string? tableSchema = null,
        string? tableAlias = null)
    {
        TableExpression = new TableExpression(tableName, tableSchema, tableAlias);

        return (TBuilder)this;
    }

    /// <summary>
    /// Adds a value for the specified column name and value.
    /// </summary>
    /// <typeparam name="TValue">The type of the value.</typeparam>
    /// <param name="columnName">The name of the column.</param>
    /// <param name="parameterValue">The value to insert or update for the column.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder Value<TValue>(
        string columnName,
        TValue? parameterValue)
    {
        return Value(columnName, parameterValue, typeof(TValue));
    }

    /// <summary>
    /// Adds a value for the specified column name, value, and type.
    /// </summary>
    /// <param name="columnName">The name of the column.</param>
    /// <param name="parameterValue">The value to insert or update for the column.</param>
    /// <param name="parameterType">The type of the parameter value.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    /// <exception cref="ArgumentException">Thrown if <paramref name="columnName"/> is null or empty.</exception>
    /// <exception cref="ArgumentNullException">Thrown if <paramref name="parameterType"/> is <c>null</c>.</exception>
    public TBuilder Value(
        string columnName,
        object? parameterValue,
        Type parameterType)
    {
        if (string.IsNullOrWhiteSpace(columnName))
            throw new ArgumentException($"'{nameof(columnName)}' cannot be null or empty.", nameof(columnName));

        if (parameterType is null)
            throw new ArgumentNullException(nameof(parameterType));

        var paramterName = NextParameter();

        var columnExpression = new ColumnExpression(columnName);

        ColumnExpressions.Add(columnExpression);
        ValueExpressions.Add(paramterName);

        Parameters.Add(new QueryParameter(paramterName, parameterValue, parameterType));

        return (TBuilder)this;
    }

    /// <summary>
    /// Conditionally adds a value for the specified column name and value if the condition is met.
    /// </summary>
    /// <typeparam name="TValue">The type of the value.</typeparam>
    /// <param name="columnName">The name of the column.</param>
    /// <param name="parameterValue">The value to insert or update for the column.</param>
    /// <param name="condition">A function that determines whether to add the value, based on the column name and value.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder ValueIf<TValue>(
        string columnName,
        TValue? parameterValue,
        Func<string, TValue?, bool> condition)
    {
        if (condition != null && !condition(columnName, parameterValue))
            return (TBuilder)this;

        return Value(columnName, parameterValue);
    }

    /// <summary>
    /// Adds a key column used to determine whether a row already exists.
    /// </summary>
    /// <param name="columnName">The key column name.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    /// <exception cref="ArgumentException">Thrown if <paramref name="columnName"/> is null or empty.</exception>
    public TBuilder Key(string columnName)
    {
        if (string.IsNullOrWhiteSpace(columnName))
            throw new ArgumentException($"'{nameof(columnName)}' cannot be null or empty.", nameof(columnName));

        KeyExpressions.Add(new ColumnExpression(columnName));

        return (TBuilder)this;
    }

    /// <summary>
    /// Conditionally adds a key column used to determine whether a row already exists.
    /// </summary>
    /// <param name="columnName">The key column name.</param>
    /// <param name="condition">A function that determines whether to add the key column.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder KeyIf(
        string columnName,
        Func<string, bool>? condition = null)
    {
        if (condition != null && !condition(columnName))
            return (TBuilder)this;

        return Key(columnName);
    }

    /// <summary>
    /// Adds an OUTPUT clause for the specified column names.
    /// </summary>
    /// <param name="columnNames">The collection of column names to include in the OUTPUT clause.</param>
    /// <param name="tableAlias">The alias for the table (optional).</param>
    /// <returns>The same builder instance for method chaining.</returns>
    /// <exception cref="ArgumentNullException">Thrown if <paramref name="columnNames"/> is <c>null</c>.</exception>
    public TBuilder Output(
        IEnumerable<string> columnNames,
        string? tableAlias = null)
    {
        if (columnNames is null)
            throw new ArgumentNullException(nameof(columnNames));

        foreach (var column in columnNames)
            Output(column, tableAlias);

        return (TBuilder)this;
    }

    /// <summary>
    /// Adds an OUTPUT clause for the specified column name.
    /// </summary>
    /// <param name="columnName">The name of the column to include in the OUTPUT clause.</param>
    /// <param name="tableAlias">The alias for the table (optional).</param>
    /// <param name="columnAlias">The alias for the output column (optional).</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder Output(
        string columnName,
        string? tableAlias = null,
        string? columnAlias = null)
    {
        var outputClause = new ColumnExpression(columnName, tableAlias, columnAlias);

        OutputExpressions.Add(outputClause);

        return (TBuilder)this;
    }

    /// <summary>
    /// Conditionally adds an OUTPUT clause for the specified column name if the condition is met.
    /// </summary>
    /// <param name="columnName">The name of the column to include in the OUTPUT clause.</param>
    /// <param name="tableAlias">The alias for the table (optional).</param>
    /// <param name="columnAlias">The alias for the output column (optional).</param>
    /// <param name="condition">A function that determines whether to add the OUTPUT clause.</param>
    /// <returns>The same builder instance for method chaining.</returns>
    public TBuilder OutputIf(
        string columnName,
        string? tableAlias = null,
        string? columnAlias = null,
        Func<string, bool>? condition = null)
    {
        if (condition != null && !condition(columnName))
            return (TBuilder)this;

        return Output(columnName, tableAlias, columnAlias);
    }

    /// <summary>
    /// Builds the SQL UPSERT statement using the current configuration.
    /// </summary>
    /// <returns>A <see cref="QueryStatement"/> containing the SQL UPSERT statement and its parameters.</returns>
    public override QueryStatement? BuildStatement()
    {
        if (TableExpression is null)
            throw new InvalidOperationException("Table must be specified before building an upsert statement.");

        if (ValueExpressions.Count == 0)
            throw new InvalidOperationException("Values must be specified before building an upsert statement.");

        if (KeyExpressions.Count == 0)
            throw new InvalidOperationException("Keys must be specified before building an upsert statement.");

        var updateExpressions = BuildUpdateExpressions();
        if (updateExpressions.Count == 0)
            throw new InvalidOperationException("At least one non-key value must be specified before building an upsert statement.");

        var upsertStatement = new UpsertStatement(
            TableExpression,
            ColumnExpressions,
            ValueExpressions,
            KeyExpressions,
            updateExpressions,
            OutputExpressions,
            CommentExpressions);

        var statement = QueryGenerator.BuildUpsert(upsertStatement);

        return new QueryStatement(statement, Parameters);
    }

    private IReadOnlyCollection<UpdateExpression> BuildUpdateExpressions()
    {
        var keyColumns = new HashSet<string>(KeyExpressions.Select(static k => k.ColumnName), StringComparer.OrdinalIgnoreCase);

        return ColumnExpressions
            .Where(c => !keyColumns.Contains(c.ColumnName))
            .Select(c => new UpdateExpression(c.ColumnName, string.Empty, c.TableAlias, c.IsRaw))
            .ToArray();
    }
}

1	using FluentCommand.Query.Generators;
2
3	namespace FluentCommand.Query;
4
5	/// <summary>
6	/// Provides a builder for constructing SQL UPSERT statements with fluent, chainable methods.
7	/// </summary>
8	public class UpsertBuilder : UpsertBuilder<UpsertBuilder>
9	{
10	/// <summary>
11	/// Initializes a new instance of the <see cref="UpsertBuilder"/> class.
12	/// </summary>
13	/// <param name="queryGenerator">The <see cref="IQueryGenerator"/> used to generate SQL expressions.</param>
14	/// <param name="parameters">The list of <see cref="QueryParameter"/> objects for the query.</param>
15	public UpsertBuilder(
16	IQueryGenerator queryGenerator,
17	List<QueryParameter> parameters)
18	: base(queryGenerator, parameters)	10✔
19	{ }	10✔
20	}
21
22	/// <summary>
23	/// Provides a generic base class for building SQL UPSERT statements with fluent, chainable methods.
24	/// </summary>
25	/// <typeparam name="TBuilder">The type of the builder for fluent chaining.</typeparam>
26	public abstract class UpsertBuilder<TBuilder> : StatementBuilder<TBuilder>
27	where TBuilder : UpsertBuilder<TBuilder>
28	{
29	/// <summary>
30	/// Initializes a new instance of the <see cref="UpsertBuilder{TBuilder}"/> class.
31	/// </summary>
32	/// <param name="queryGenerator">The <see cref="IQueryGenerator"/> used to generate SQL expressions.</param>
33	/// <param name="parameters">The list of <see cref="QueryParameter"/> objects for the query.</param>
34	protected UpsertBuilder(
35	IQueryGenerator queryGenerator,
36	List<QueryParameter> parameters)
37	: base(queryGenerator, parameters)	23✔
38	{
39	}	23✔
40
41	/// <summary>
42	/// Gets the collection of column expressions for the UPSERT statement.
43	/// </summary>
44	protected HashSet<ColumnExpression> ColumnExpressions { get; } = new();
45
46	/// <summary>
47	/// Gets the collection of value expressions for the UPSERT statement.
48	/// </summary>
49	protected HashSet<string> ValueExpressions { get; } = new();
50
51	/// <summary>
52	/// Gets the collection of key column expressions for the UPSERT statement.
53	/// </summary>
54	protected HashSet<ColumnExpression> KeyExpressions { get; } = new();
55
56	/// <summary>
57	/// Gets the collection of output column expressions for the UPSERT statement.
58	/// </summary>
59	protected HashSet<ColumnExpression> OutputExpressions { get; } = new();
60
61	/// <summary>
62	/// Gets the target table expression for the UPSERT statement.
63	/// </summary>
64	protected TableExpression? TableExpression { get; private set; }
65
66	/// <summary>
67	/// Sets the target table to insert into or update.
68	/// </summary>
69	/// <param name="tableName">The name of the table.</param>
70	/// <param name="tableSchema">The schema of the table (optional).</param>
71	/// <param name="tableAlias">The alias for the table (optional).</param>
72	/// <returns>The same builder instance for method chaining.</returns>
73	public TBuilder Into(
74	string tableName,
75	string? tableSchema = null,
76	string? tableAlias = null)
77	{
78	TableExpression = new TableExpression(tableName, tableSchema, tableAlias);	23✔
79
80	return (TBuilder)this;	23✔
81	}
82
83	/// <summary>
84	/// Adds a value for the specified column name and value.
85	/// </summary>
86	/// <typeparam name="TValue">The type of the value.</typeparam>
87	/// <param name="columnName">The name of the column.</param>
88	/// <param name="parameterValue">The value to insert or update for the column.</param>
89	/// <returns>The same builder instance for method chaining.</returns>
90	public TBuilder Value<TValue>(
91	string columnName,
92	TValue? parameterValue)
93	{
94	return Value(columnName, parameterValue, typeof(TValue));	47✔
95	}
96
97	/// <summary>
98	/// Adds a value for the specified column name, value, and type.
99	/// </summary>
100	/// <param name="columnName">The name of the column.</param>
101	/// <param name="parameterValue">The value to insert or update for the column.</param>
102	/// <param name="parameterType">The type of the parameter value.</param>
103	/// <returns>The same builder instance for method chaining.</returns>
104	/// <exception cref="ArgumentException">Thrown if <paramref name="columnName"/> is null or empty.</exception>
105	/// <exception cref="ArgumentNullException">Thrown if <paramref name="parameterType"/> is <c>null</c>.</exception>
106	public TBuilder Value(
107	string columnName,
108	object? parameterValue,
109	Type parameterType)
110	{
111	if (string.IsNullOrWhiteSpace(columnName))	158!
NEW 112	throw new ArgumentException($"'{nameof(columnName)}' cannot be null or empty.", nameof(columnName));	×
113
114	if (parameterType is null)	158!
NEW 115	throw new ArgumentNullException(nameof(parameterType));	×
116
117	var paramterName = NextParameter();	158✔
118
119	var columnExpression = new ColumnExpression(columnName);	158✔
120
121	ColumnExpressions.Add(columnExpression);	158✔
122	ValueExpressions.Add(paramterName);	158✔
123
124	Parameters.Add(new QueryParameter(paramterName, parameterValue, parameterType));	158✔
125
126	return (TBuilder)this;	158✔
127	}
128
129	/// <summary>
130	/// Conditionally adds a value for the specified column name and value if the condition is met.
131	/// </summary>
132	/// <typeparam name="TValue">The type of the value.</typeparam>
133	/// <param name="columnName">The name of the column.</param>
134	/// <param name="parameterValue">The value to insert or update for the column.</param>
135	/// <param name="condition">A function that determines whether to add the value, based on the column name and value.</param>
136	/// <returns>The same builder instance for method chaining.</returns>
137	public TBuilder ValueIf<TValue>(
138	string columnName,
139	TValue? parameterValue,
140	Func<string, TValue?, bool> condition)
141	{
NEW 142	if (condition != null && !condition(columnName, parameterValue))	×
NEW 143	return (TBuilder)this;	×
144
NEW 145	return Value(columnName, parameterValue);	×
146	}
147
148	/// <summary>
149	/// Adds a key column used to determine whether a row already exists.
150	/// </summary>
151	/// <param name="columnName">The key column name.</param>
152	/// <returns>The same builder instance for method chaining.</returns>
153	/// <exception cref="ArgumentException">Thrown if <paramref name="columnName"/> is null or empty.</exception>
154	public TBuilder Key(string columnName)
155	{
156	if (string.IsNullOrWhiteSpace(columnName))	30!
NEW 157	throw new ArgumentException($"'{nameof(columnName)}' cannot be null or empty.", nameof(columnName));	×
158
159	KeyExpressions.Add(new ColumnExpression(columnName));	30✔
160
161	return (TBuilder)this;	30✔
162	}
163
164	/// <summary>
165	/// Conditionally adds a key column used to determine whether a row already exists.
166	/// </summary>
167	/// <param name="columnName">The key column name.</param>
168	/// <param name="condition">A function that determines whether to add the key column.</param>
169	/// <returns>The same builder instance for method chaining.</returns>
170	public TBuilder KeyIf(
171	string columnName,
172	Func<string, bool>? condition = null)
173	{
NEW 174	if (condition != null && !condition(columnName))	×
NEW 175	return (TBuilder)this;	×
176
NEW 177	return Key(columnName);	×
178	}
179
180	/// <summary>
181	/// Adds an OUTPUT clause for the specified column names.
182	/// </summary>
183	/// <param name="columnNames">The collection of column names to include in the OUTPUT clause.</param>
184	/// <param name="tableAlias">The alias for the table (optional).</param>
185	/// <returns>The same builder instance for method chaining.</returns>
186	/// <exception cref="ArgumentNullException">Thrown if <paramref name="columnNames"/> is <c>null</c>.</exception>
187	public TBuilder Output(
188	IEnumerable<string> columnNames,
189	string? tableAlias = null)
190	{
NEW 191	if (columnNames is null)	×
NEW 192	throw new ArgumentNullException(nameof(columnNames));	×
193
NEW 194	foreach (var column in columnNames)	×
NEW 195	Output(column, tableAlias);	×
196
NEW 197	return (TBuilder)this;	×
198	}
199
200	/// <summary>
201	/// Adds an OUTPUT clause for the specified column name.
202	/// </summary>
203	/// <param name="columnName">The name of the column to include in the OUTPUT clause.</param>
204	/// <param name="tableAlias">The alias for the table (optional).</param>
205	/// <param name="columnAlias">The alias for the output column (optional).</param>
206	/// <returns>The same builder instance for method chaining.</returns>
207	public TBuilder Output(
208	string columnName,
209	string? tableAlias = null,
210	string? columnAlias = null)
211	{
212	var outputClause = new ColumnExpression(columnName, tableAlias, columnAlias);	10✔
213
214	OutputExpressions.Add(outputClause);	10✔
215
216	return (TBuilder)this;	10✔
217	}
218
219	/// <summary>
220	/// Conditionally adds an OUTPUT clause for the specified column name if the condition is met.
221	/// </summary>
222	/// <param name="columnName">The name of the column to include in the OUTPUT clause.</param>
223	/// <param name="tableAlias">The alias for the table (optional).</param>
224	/// <param name="columnAlias">The alias for the output column (optional).</param>
225	/// <param name="condition">A function that determines whether to add the OUTPUT clause.</param>
226	/// <returns>The same builder instance for method chaining.</returns>
227	public TBuilder OutputIf(
228	string columnName,
229	string? tableAlias = null,
230	string? columnAlias = null,
231	Func<string, bool>? condition = null)
232	{
NEW 233	if (condition != null && !condition(columnName))	×
NEW 234	return (TBuilder)this;	×
235
NEW 236	return Output(columnName, tableAlias, columnAlias);	×
237	}
238
239	/// <summary>
240	/// Builds the SQL UPSERT statement using the current configuration.
241	/// </summary>
242	/// <returns>A <see cref="QueryStatement"/> containing the SQL UPSERT statement and its parameters.</returns>
243	public override QueryStatement? BuildStatement()
244	{
245	if (TableExpression is null)	23!
NEW 246	throw new InvalidOperationException("Table must be specified before building an upsert statement.");	×
247
248	if (ValueExpressions.Count == 0)	23!
NEW 249	throw new InvalidOperationException("Values must be specified before building an upsert statement.");	×
250
251	if (KeyExpressions.Count == 0)	23!
NEW 252	throw new InvalidOperationException("Keys must be specified before building an upsert statement.");	×
253
254	var updateExpressions = BuildUpdateExpressions();	23✔
255	if (updateExpressions.Count == 0)	23!
NEW 256	throw new InvalidOperationException("At least one non-key value must be specified before building an upsert statement.");	×
257
258	var upsertStatement = new UpsertStatement(	23✔
259	TableExpression,	23✔
260	ColumnExpressions,	23✔
261	ValueExpressions,	23✔
262	KeyExpressions,	23✔
263	updateExpressions,	23✔
264	OutputExpressions,	23✔
265	CommentExpressions);	23✔
266
267	var statement = QueryGenerator.BuildUpsert(upsertStatement);	23✔
268
269	return new QueryStatement(statement, Parameters);	23✔
270	}
271
272	private IReadOnlyCollection<UpdateExpression> BuildUpdateExpressions()
273	{
274	var keyColumns = new HashSet<string>(KeyExpressions.Select(static k => k.ColumnName), StringComparer.OrdinalIgnoreCase);	23✔
275
276	return ColumnExpressions	23✔
277	.Where(c => !keyColumns.Contains(c.ColumnName))	23✔
278	.Select(c => new UpdateExpression(c.ColumnName, string.Empty, c.TableAlias, c.IsRaw))	23✔
279	.ToArray();	23✔
280	}
281	}

loresoft / FluentCommand / 26559531993

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous