Lightweight 0.20250627.0
Loading...
Searching...
No Matches
Core.hpp
1// SPDX-License-Identifier: Apache-2.0
2
3#pragma once
4
5#include "../Api.hpp"
6#include "../SqlQueryFormatter.hpp"
7
8#include <concepts>
9#include <ranges>
10
11/// @defgroup QueryBuilder Query Builder
12///
13/// @brief The query builder is a high level API for building SQL queries using high level C++ syntax.
14
15/// @brief SqlWildcardType is a placeholder for an explicit wildcard input parameter in a SQL query.
16///
17/// Use this in the SqlQueryBuilder::Where method to insert a '?' placeholder for a wildcard.
18///
19/// @ingroup QueryBuilder
21{
22};
23
24/// @brief SqlWildcard is a placeholder for an explicit wildcard input parameter in a SQL query.
25static constexpr inline auto SqlWildcard = SqlWildcardType {};
26
27namespace detail
28{
29
30struct RawSqlCondition
31{
32 std::string condition;
33};
34
35} // namespace detail
36
37/// @brief SqlQualifiedTableColumnName represents a column name qualified with a table name.
38/// @ingroup QueryBuilder
40{
41 std::string_view tableName;
42 std::string_view columnName;
43};
44
45namespace detail
46{
47
48template <typename ColumnName>
49std::string MakeSqlColumnName(ColumnName const& columnName)
50{
51 using namespace std::string_view_literals;
52 std::string output;
53
54 if constexpr (std::is_same_v<ColumnName, SqlQualifiedTableColumnName>)
55 {
56 output.reserve(columnName.tableName.size() + columnName.columnName.size() + 5);
57 output += '"';
58 output += columnName.tableName;
59 output += R"(".")"sv;
60 output += columnName.columnName;
61 output += '"';
62 }
63 else if constexpr (std::is_same_v<ColumnName, SqlRawColumnNameView>)
64 {
65 output += columnName.value;
66 }
67 else if constexpr (std::is_same_v<ColumnName, SqlWildcardType>)
68 {
69 output += '?';
70 }
71 else
72 {
73 output += '"';
74 output += columnName;
75 output += '"';
76 }
77 return output;
78}
79
80template <typename T>
81std::string MakeEscapedSqlString(T const& value)
82{
83 std::string escapedValue;
84 escapedValue += '\'';
85
86 for (auto const ch: value)
87 {
88 // In SQL strings, single quotes are escaped by doubling them.
89 if (ch == '\'')
90 escapedValue += '\'';
91 escapedValue += ch;
92 }
93 escapedValue += '\'';
94 return escapedValue;
95}
96
97} // namespace detail
98
99struct [[nodiscard]] SqlSearchCondition
100{
101 std::string tableName;
102 std::string tableAlias;
103 std::string tableJoins;
104 std::string condition;
105 std::vector<SqlVariant>* inputBindings = nullptr;
106};
107
108/// @brief Query builder for building JOIN conditions.
109/// @ingroup QueryBuilder
111{
112 public:
113 explicit SqlJoinConditionBuilder(std::string_view referenceTable, std::string* condition) noexcept:
114 _referenceTable { referenceTable },
115 _condition { *condition }
116 {
117 }
118
119 SqlJoinConditionBuilder& On(std::string_view joinColumnName, SqlQualifiedTableColumnName onOtherColumn)
120 {
121 return Operator(joinColumnName, onOtherColumn, "AND");
122 }
123
124 SqlJoinConditionBuilder& OrOn(std::string_view joinColumnName, SqlQualifiedTableColumnName onOtherColumn)
125 {
126 return Operator(joinColumnName, onOtherColumn, "OR");
127 }
128
129 SqlJoinConditionBuilder& Operator(std::string_view joinColumnName,
130 SqlQualifiedTableColumnName onOtherColumn,
131 std::string_view op)
132 {
133 if (_firstCall)
134 _firstCall = !_firstCall;
135 else
136 _condition += std::format(" {} ", op);
137
138 _condition += '"';
139 _condition += _referenceTable;
140 _condition += "\".\"";
141 _condition += joinColumnName;
142 _condition += "\" = \"";
143 _condition += onOtherColumn.tableName;
144 _condition += "\".\"";
145 _condition += onOtherColumn.columnName;
146 _condition += '"';
147
148 return *this;
149 }
150
151 private:
152 std::string_view _referenceTable;
153 std::string& _condition;
154 bool _firstCall = true;
155};
156
157/// Helper CRTP-based class for building WHERE clauses.
158///
159/// This class is inherited by the SqlSelectQueryBuilder, SqlUpdateQueryBuilder, and SqlDeleteQueryBuilder
160///
161/// @ingroup QueryBuilder
162template <typename Derived>
163class [[nodiscard]] SqlWhereClauseBuilder
164{
165 public:
166 /// Indicates, that the next WHERE clause should be AND-ed (default).
167 [[nodiscard]] Derived& And() noexcept;
168
169 /// Indicates, that the next WHERE clause should be OR-ed.
170 [[nodiscard]] Derived& Or() noexcept;
171
172 /// Indicates, that the next WHERE clause should be negated.
173 [[nodiscard]] Derived& Not() noexcept;
174
175 /// Constructs or extends a raw WHERE clause.
176 [[nodiscard]] Derived& WhereRaw(std::string_view sqlConditionExpression);
177
178 /// Constructs or extends a WHERE clause to test for a binary operation.
179 template <typename ColumnName, typename T>
180 [[nodiscard]] Derived& Where(ColumnName const& columnName, std::string_view binaryOp, T const& value);
181
182 /// Constructs or extends a WHERE clause to test for a binary operation for RHS as sub-select query.
183 template <typename ColumnName, typename SubSelectQuery>
184 requires(std::is_invocable_r_v<std::string, decltype(&SubSelectQuery::ToSql), SubSelectQuery const&>)
185 [[nodiscard]] Derived& Where(ColumnName const& columnName, std::string_view binaryOp, SubSelectQuery const& value);
186
187 /// Constructs or extends a WHERE/OR clause to test for a binary operation.
188 template <typename ColumnName, typename T>
189 [[nodiscard]] Derived& OrWhere(ColumnName const& columnName, std::string_view binaryOp, T const& value);
190
191 /// Constructs or extends a WHERE clause to test for a binary operation for RHS as string literal.
192 template <typename ColumnName, std::size_t N>
193 Derived& Where(ColumnName const& columnName, std::string_view binaryOp, char const (&value)[N]);
194
195 /// Constructs or extends a WHERE clause to test for equality.
196 template <typename ColumnName, typename T>
197 [[nodiscard]] Derived& Where(ColumnName const& columnName, T const& value);
198
199 /// Constructs or extends an WHERE/OR clause to test for equality.
200 template <typename ColumnName, typename T>
201 [[nodiscard]] Derived& OrWhere(ColumnName const& columnName, T const& value);
202
203 /// Constructs or extends a WHERE/AND clause to test for a group of values.
204 template <typename Callable>
205 requires std::invocable<Callable, SqlWhereClauseBuilder<Derived>&>
206 [[nodiscard]] Derived& Where(Callable const& callable);
207
208 /// Constructs or extends an WHERE/OR clause to test for a group of values.
209 template <typename Callable>
210 requires std::invocable<Callable, SqlWhereClauseBuilder<Derived>&>
211 [[nodiscard]] Derived& OrWhere(Callable const& callable);
212
213 /// Constructs or extends an WHERE/OR clause to test for a value, satisfying std::ranges::input_range.
214 template <typename ColumnName, std::ranges::input_range InputRange>
215 [[nodiscard]] Derived& WhereIn(ColumnName const& columnName, InputRange const& values);
216
217 /// Constructs or extends an WHERE/OR clause to test for a value, satisfying std::initializer_list.
218 template <typename ColumnName, typename T>
219 [[nodiscard]] Derived& WhereIn(ColumnName const& columnName, std::initializer_list<T> const& values);
220
221 /// Constructs or extends an WHERE/OR clause to test for a value, satisfying a sub-select query.
222 template <typename ColumnName, typename SubSelectQuery>
223 requires(std::is_invocable_r_v<std::string, decltype(&SubSelectQuery::ToSql), SubSelectQuery const&>)
224 [[nodiscard]] Derived& WhereIn(ColumnName const& columnName, SubSelectQuery const& subSelectQuery);
225
226 /// Constructs or extends an WHERE/OR clause to test for a value to be NULL.
227 template <typename ColumnName>
228 [[nodiscard]] Derived& WhereNull(ColumnName const& columnName);
229
230 /// Constructs or extends a WHERE clause to test for a value being not null.
231 template <typename ColumnName>
232 [[nodiscard]] Derived& WhereNotNull(ColumnName const& columnName);
233
234 /// Constructs or extends a WHERE clause to test for a value being equal to another column.
235 template <typename ColumnName, typename T>
236 [[nodiscard]] Derived& WhereNotEqual(ColumnName const& columnName, T const& value);
237
238 /// Constructs or extends a WHERE clause to test for a value being true.
239 template <typename ColumnName>
240 [[nodiscard]] Derived& WhereTrue(ColumnName const& columnName);
241
242 /// Constructs or extends a WHERE clause to test for a value being false.
243 template <typename ColumnName>
244 [[nodiscard]] Derived& WhereFalse(ColumnName const& columnName);
245
246 /// Construts or extends a WHERE clause to test for a binary operation between two columns.
247 template <typename LeftColumn, typename RightColumn>
248 [[nodiscard]] Derived& WhereColumn(LeftColumn const& left, std::string_view binaryOp, RightColumn const& right);
249
250 /// Constructs an INNER JOIN clause.
251 [[nodiscard]] Derived& InnerJoin(std::string_view joinTable,
252 std::string_view joinColumnName,
253 SqlQualifiedTableColumnName onOtherColumn);
254
255 /// Constructs an INNER JOIN clause.
256 [[nodiscard]] Derived& InnerJoin(std::string_view joinTable,
257 std::string_view joinColumnName,
258 std::string_view onMainTableColumn);
259
260 /// Constructs an INNER JOIN clause with a custom ON clause.
261 template <typename OnChainCallable>
262 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
263 [[nodiscard]] Derived& InnerJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder);
264
265 /// Constructs an `INNER JOIN` clause given two fields from different records
266 /// using the field name as join column.
267 ///
268 /// @tparam LeftField The field name to join on, such as `JoinTestB::a_id`, which will join on table `JoinTestB` with
269 /// the column `a_id` to be compared against right field's column.
270 /// @tparam RightField The other column to compare and join against.
271 ///
272 /// Example:
273 /// @code
274 /// InnerJoin<&JoinTestB::a_id, &JoinTestA::id>()
275 /// // This will generate a INNER JOIN "JoinTestB" ON "InnerTestB"."a_id" = "JoinTestA"."id"
276 template <auto LeftField, auto RightField>
277 [[nodiscard]] Derived& InnerJoin();
278
279 /// Constructs an LEFT OUTER JOIN clause.
280 [[nodiscard]] Derived& LeftOuterJoin(std::string_view joinTable,
281 std::string_view joinColumnName,
282 SqlQualifiedTableColumnName onOtherColumn);
283
284 /// Constructs an LEFT OUTER JOIN clause.
285 [[nodiscard]] Derived& LeftOuterJoin(std::string_view joinTable,
286 std::string_view joinColumnName,
287 std::string_view onMainTableColumn);
288
289 /// Constructs an LEFT OUTER JOIN clause with a custom ON clause.
290 template <typename OnChainCallable>
291 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
292 [[nodiscard]] Derived& LeftOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder);
293
294 /// Constructs an RIGHT OUTER JOIN clause.
295 [[nodiscard]] Derived& RightOuterJoin(std::string_view joinTable,
296 std::string_view joinColumnName,
297 SqlQualifiedTableColumnName onOtherColumn);
298
299 /// Constructs an RIGHT OUTER JOIN clause.
300 [[nodiscard]] Derived& RightOuterJoin(std::string_view joinTable,
301 std::string_view joinColumnName,
302 std::string_view onMainTableColumn);
303
304 /// Constructs an RIGHT OUTER JOIN clause with a custom ON clause.
305 template <typename OnChainCallable>
306 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
307 [[nodiscard]] Derived& RightOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder);
308
309 /// Constructs an FULL OUTER JOIN clause.
310 [[nodiscard]] Derived& FullOuterJoin(std::string_view joinTable,
311 std::string_view joinColumnName,
312 SqlQualifiedTableColumnName onOtherColumn);
313
314 /// Constructs an FULL OUTER JOIN clause.
315 [[nodiscard]] Derived& FullOuterJoin(std::string_view joinTable,
316 std::string_view joinColumnName,
317 std::string_view onMainTableColumn);
318
319 /// Constructs an FULL OUTER JOIN clause with a custom ON clause.
320 template <typename OnChainCallable>
321 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
322 [[nodiscard]] Derived& FullOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder);
323
324 private:
325 SqlSearchCondition& SearchCondition() noexcept;
326 [[nodiscard]] SqlQueryFormatter const& Formatter() const noexcept;
327
328 enum class WhereJunctor : uint8_t
329 {
330 Null,
331 Where,
332 And,
333 Or,
334 };
335
336 WhereJunctor m_nextWhereJunctor = WhereJunctor::Where;
337 bool m_nextIsNot = false;
338
339 void AppendWhereJunctor();
340
341 template <typename ColumnName>
342 requires(std::same_as<ColumnName, SqlQualifiedTableColumnName> || std::convertible_to<ColumnName, std::string_view>
343 || std::same_as<ColumnName, SqlRawColumnNameView> || std::convertible_to<ColumnName, std::string>)
344 void AppendColumnName(ColumnName const& columnName);
345
346 template <typename LiteralType>
347 void AppendLiteralValue(LiteralType const& value);
348
349 template <typename LiteralType, typename TargetType>
350 void PopulateLiteralValueInto(LiteralType const& value, TargetType& target);
351
352 template <typename LiteralType>
353 detail::RawSqlCondition PopulateSqlSetExpression(LiteralType const& values);
354
355 enum class JoinType : uint8_t
356 {
357 INNER,
358 LEFT,
359 RIGHT,
360 FULL
361 };
362
363 /// Constructs a JOIN clause.
364 [[nodiscard]] Derived& Join(JoinType joinType,
365 std::string_view joinTable,
366 std::string_view joinColumnName,
367 SqlQualifiedTableColumnName onOtherColumn);
368
369 /// Constructs a JOIN clause.
370 [[nodiscard]] Derived& Join(JoinType joinType,
371 std::string_view joinTable,
372 std::string_view joinColumnName,
373 std::string_view onMainTableColumn);
374
375 /// Constructs a JOIN clause.
376 template <typename OnChainCallable>
377 [[nodiscard]] Derived& Join(JoinType joinType, std::string_view joinTable, OnChainCallable const& onClauseBuilder);
378};
379
380enum class SqlResultOrdering : uint8_t
381{
382 ASCENDING,
383 DESCENDING
384};
385
386namespace detail
387{
388enum class SelectType : std::uint8_t
389{
390 Undefined,
391 Count,
392 All,
393 First,
394 Range
395};
396
397struct ComposedQuery
398{
399 SelectType selectType = SelectType::Undefined;
400 SqlQueryFormatter const* formatter = nullptr;
401
402 bool distinct = false;
403 SqlSearchCondition searchCondition {};
404
405 std::string fields;
406
407 std::string orderBy;
408 std::string groupBy;
409
410 size_t offset = 0;
411 size_t limit = (std::numeric_limits<size_t>::max)();
412
413 [[nodiscard]] LIGHTWEIGHT_API std::string ToSql() const;
414};
415} // namespace detail
416
417template <typename Derived>
418class [[nodiscard]] SqlBasicSelectQueryBuilder: public SqlWhereClauseBuilder<Derived>
419{
420 public:
421 /// Adds a DISTINCT clause to the SELECT query.
422 Derived& Distinct() noexcept;
423
424 /// Constructs or extends a ORDER BY clause.
425 Derived& OrderBy(SqlQualifiedTableColumnName const& columnName,
426 SqlResultOrdering ordering = SqlResultOrdering::ASCENDING);
427
428 /// Constructs or extends a ORDER BY clause.
429 Derived& OrderBy(std::string_view columnName, SqlResultOrdering ordering = SqlResultOrdering::ASCENDING);
430
431 /// Constructs or extends a GROUP BY clause.
432 Derived& GroupBy(std::string_view columnName);
433
434 using ComposedQuery = detail::ComposedQuery;
435
436 protected:
437 ComposedQuery _query {}; // NOLINT(cppcoreguidelines-non-private-member-variables-in-classes)
438};
439
440template <typename Derived>
441inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlBasicSelectQueryBuilder<Derived>::Distinct() noexcept
442{
443 _query.distinct = true;
444 return static_cast<Derived&>(*this);
445}
446
447template <typename Derived>
448inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlBasicSelectQueryBuilder<Derived>::OrderBy(std::string_view columnName,
449 SqlResultOrdering ordering)
450{
451 if (_query.orderBy.empty())
452 _query.orderBy += "\n ORDER BY ";
453 else
454 _query.orderBy += ", ";
455
456 _query.orderBy += '"';
457 _query.orderBy += columnName;
458 _query.orderBy += '"';
459
460 if (ordering == SqlResultOrdering::DESCENDING)
461 _query.orderBy += " DESC";
462 else if (ordering == SqlResultOrdering::ASCENDING)
463 _query.orderBy += " ASC";
464
465 return static_cast<Derived&>(*this);
466}
467
468template <typename Derived>
469inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlBasicSelectQueryBuilder<Derived>::OrderBy(
470 SqlQualifiedTableColumnName const& columnName, SqlResultOrdering ordering)
471{
472 if (_query.orderBy.empty())
473 _query.orderBy += "\n ORDER BY ";
474 else
475 _query.orderBy += ", ";
476
477 _query.orderBy += '"';
478 _query.orderBy += columnName.tableName;
479 _query.orderBy += "\".\"";
480 _query.orderBy += columnName.columnName;
481 _query.orderBy += '"';
482
483 if (ordering == SqlResultOrdering::DESCENDING)
484 _query.orderBy += " DESC";
485 else if (ordering == SqlResultOrdering::ASCENDING)
486 _query.orderBy += " ASC";
487
488 return static_cast<Derived&>(*this);
489}
490
491template <typename Derived>
492inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlBasicSelectQueryBuilder<Derived>::GroupBy(std::string_view columnName)
493{
494 if (_query.groupBy.empty())
495 _query.groupBy += "\n GROUP BY ";
496 else
497 _query.groupBy += ", ";
498
499 _query.groupBy += '"';
500 _query.groupBy += columnName;
501 _query.groupBy += '"';
502
503 return static_cast<Derived&>(*this);
504}
505
506template <typename Derived>
507inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::And() noexcept
508{
509 m_nextWhereJunctor = WhereJunctor::And;
510 return static_cast<Derived&>(*this);
511}
512
513template <typename Derived>
514inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Or() noexcept
515{
516 m_nextWhereJunctor = WhereJunctor::Or;
517 return static_cast<Derived&>(*this);
518}
519
520template <typename Derived>
521inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Not() noexcept
522{
523 m_nextIsNot = !m_nextIsNot;
524 return static_cast<Derived&>(*this);
525}
526
527template <typename Derived>
528template <typename ColumnName, typename T>
529inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Where(ColumnName const& columnName, T const& value)
530{
531 if constexpr (detail::OneOf<T, SqlNullType, std::nullopt_t>)
532 {
533 if (m_nextIsNot)
534 {
535 m_nextIsNot = false;
536 return Where(columnName, "IS NOT", value);
537 }
538 else
539 return Where(columnName, "IS", value);
540 }
541 else
542 return Where(columnName, "=", value);
543}
544
545template <typename Derived>
546template <typename ColumnName, typename T>
547inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::OrWhere(ColumnName const& columnName,
548 T const& value)
549{
550 return Or().Where(columnName, value);
551}
552
553template <typename Derived>
554template <typename Callable>
555 requires std::invocable<Callable, SqlWhereClauseBuilder<Derived>&>
556inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::OrWhere(Callable const& callable)
557{
558 return Or().Where(callable);
559}
560
561template <typename Derived>
562template <typename Callable>
563 requires std::invocable<Callable, SqlWhereClauseBuilder<Derived>&>
564inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Where(Callable const& callable)
565{
566 auto& condition = SearchCondition().condition;
567
568 auto const originalSize = condition.size();
569
570 AppendWhereJunctor();
571 m_nextWhereJunctor = WhereJunctor::Null;
572 condition += '(';
573
574 auto const sizeBeforeCallable = condition.size();
575
576 (void) callable(*this);
577
578 if (condition.size() == sizeBeforeCallable)
579 condition.resize(originalSize);
580 else
581 condition += ')';
582
583 return static_cast<Derived&>(*this);
584}
585
586template <typename Derived>
587template <typename ColumnName, std::ranges::input_range InputRange>
588inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereIn(ColumnName const& columnName,
589 InputRange const& values)
590{
591 return Where(columnName, "IN", PopulateSqlSetExpression(values));
592}
593
594template <typename Derived>
595template <typename ColumnName, typename T>
596inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereIn(ColumnName const& columnName,
597 std::initializer_list<T> const& values)
598{
599 return Where(columnName, "IN", PopulateSqlSetExpression(values));
600}
601
602template <typename Derived>
603template <typename ColumnName, typename SubSelectQuery>
604 requires(std::is_invocable_r_v<std::string, decltype(&SubSelectQuery::ToSql), SubSelectQuery const&>)
605inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereIn(ColumnName const& columnName,
606 SubSelectQuery const& subSelectQuery)
607{
608 return Where(columnName, "IN", detail::RawSqlCondition { "(" + subSelectQuery.ToSql() + ")" });
609}
610
611template <typename Derived>
612template <typename ColumnName>
613inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereNotNull(ColumnName const& columnName)
614{
615 return Where(columnName, "IS NOT", detail::RawSqlCondition { "NULL" });
616}
617
618template <typename Derived>
619template <typename ColumnName>
620inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereNull(ColumnName const& columnName)
621{
622 return Where(columnName, "IS", detail::RawSqlCondition { "NULL" });
623}
624
625template <typename Derived>
626template <typename ColumnName, typename T>
627inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereNotEqual(ColumnName const& columnName,
628 T const& value)
629{
630 if constexpr (detail::OneOf<T, SqlNullType, std::nullopt_t>)
631 return Where(columnName, "IS NOT", value);
632 else
633 return Where(columnName, "!=", value);
634}
635
636template <typename Derived>
637template <typename ColumnName>
638inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereTrue(ColumnName const& columnName)
639{
640 return Where(columnName, "=", true);
641}
642
643template <typename Derived>
644template <typename ColumnName>
645inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereFalse(ColumnName const& columnName)
646{
647 return Where(columnName, "=", false);
648}
649
650template <typename Derived>
651template <typename LeftColumn, typename RightColumn>
652inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereColumn(LeftColumn const& left,
653 std::string_view binaryOp,
654 RightColumn const& right)
655{
656 AppendWhereJunctor();
657
658 AppendColumnName(left);
659 SearchCondition().condition += ' ';
660 SearchCondition().condition += binaryOp;
661 SearchCondition().condition += ' ';
662 AppendColumnName(right);
663
664 return static_cast<Derived&>(*this);
665}
666
667template <typename T>
668struct WhereConditionLiteralType
669{
670 constexpr static bool needsQuotes = !std::is_integral_v<T> && !std::is_floating_point_v<T> && !std::same_as<T, bool>
671 && !std::same_as<T, SqlWildcardType>;
672};
673
674template <typename Derived>
675template <typename ColumnName, std::size_t N>
676inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Where(ColumnName const& columnName,
677 std::string_view binaryOp,
678 char const (&value)[N])
679{
680 return Where(columnName, binaryOp, std::string_view { value, N - 1 });
681}
682
683template <typename Derived>
684template <typename ColumnName, typename T>
685inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Where(ColumnName const& columnName,
686 std::string_view binaryOp,
687 T const& value)
688{
689 auto& searchCondition = SearchCondition();
690
691 AppendWhereJunctor();
692 AppendColumnName(columnName);
693 searchCondition.condition += ' ';
694 searchCondition.condition += binaryOp;
695 searchCondition.condition += ' ';
696 AppendLiteralValue(value);
697
698 return static_cast<Derived&>(*this);
699}
700
701template <typename Derived>
702template <typename ColumnName, typename SubSelectQuery>
703 requires(std::is_invocable_r_v<std::string, decltype(&SubSelectQuery::ToSql), SubSelectQuery const&>)
704inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Where(ColumnName const& columnName,
705 std::string_view binaryOp,
706 SubSelectQuery const& value)
707{
708 return Where(columnName, binaryOp, detail::RawSqlCondition { "(" + value.ToSql() + ")" });
709}
710
711template <typename Derived>
712template <typename ColumnName, typename T>
713inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::OrWhere(ColumnName const& columnName,
714 std::string_view binaryOp,
715 T const& value)
716{
717 return Or().Where(columnName, binaryOp, value);
718}
719
720template <typename Derived>
721inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::InnerJoin(std::string_view joinTable,
722 std::string_view joinColumnName,
723 SqlQualifiedTableColumnName onOtherColumn)
724{
725 return Join(JoinType::INNER, joinTable, joinColumnName, onOtherColumn);
726}
727
728template <typename Derived>
729inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::InnerJoin(std::string_view joinTable,
730 std::string_view joinColumnName,
731 std::string_view onMainTableColumn)
732{
733 return Join(JoinType::INNER, joinTable, joinColumnName, onMainTableColumn);
734}
735
736template <typename Derived>
737template <auto LeftField, auto RightField>
738Derived& SqlWhereClauseBuilder<Derived>::InnerJoin()
739{
740 return Join(
741 JoinType::INNER,
742 RecordTableName<Reflection::MemberClassType<LeftField>>,
743 FieldNameOf<LeftField>,
744 SqlQualifiedTableColumnName { RecordTableName<Reflection::MemberClassType<RightField>>, FieldNameOf<RightField> });
745}
746
747template <typename Derived>
748template <typename OnChainCallable>
749 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
750Derived& SqlWhereClauseBuilder<Derived>::InnerJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder)
751{
752 return Join(JoinType::INNER, joinTable, onClauseBuilder);
753}
754
755template <typename Derived>
756inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::LeftOuterJoin(
757 std::string_view joinTable, std::string_view joinColumnName, SqlQualifiedTableColumnName onOtherColumn)
758{
759 return Join(JoinType::LEFT, joinTable, joinColumnName, onOtherColumn);
760}
761
762template <typename Derived>
763inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::LeftOuterJoin(std::string_view joinTable,
764 std::string_view joinColumnName,
765 std::string_view onMainTableColumn)
766{
767 return Join(JoinType::LEFT, joinTable, joinColumnName, onMainTableColumn);
768}
769
770template <typename Derived>
771template <typename OnChainCallable>
772 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
773Derived& SqlWhereClauseBuilder<Derived>::LeftOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder)
774{
775 return Join(JoinType::LEFT, joinTable, onClauseBuilder);
776}
777
778template <typename Derived>
779inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::RightOuterJoin(
780 std::string_view joinTable, std::string_view joinColumnName, SqlQualifiedTableColumnName onOtherColumn)
781{
782 return Join(JoinType::RIGHT, joinTable, joinColumnName, onOtherColumn);
783}
784
785template <typename Derived>
786inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::RightOuterJoin(std::string_view joinTable,
787 std::string_view joinColumnName,
788 std::string_view onMainTableColumn)
789{
790 return Join(JoinType::RIGHT, joinTable, joinColumnName, onMainTableColumn);
791}
792
793template <typename Derived>
794template <typename OnChainCallable>
795 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
796Derived& SqlWhereClauseBuilder<Derived>::RightOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder)
797{
798 return Join(JoinType::RIGHT, joinTable, onClauseBuilder);
799}
800
801template <typename Derived>
802inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::FullOuterJoin(
803 std::string_view joinTable, std::string_view joinColumnName, SqlQualifiedTableColumnName onOtherColumn)
804{
805 return Join(JoinType::FULL, joinTable, joinColumnName, onOtherColumn);
806}
807
808template <typename Derived>
809inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::FullOuterJoin(std::string_view joinTable,
810 std::string_view joinColumnName,
811 std::string_view onMainTableColumn)
812{
813 return Join(JoinType::FULL, joinTable, joinColumnName, onMainTableColumn);
814}
815
816template <typename Derived>
817template <typename OnChainCallable>
818 requires std::invocable<OnChainCallable, SqlJoinConditionBuilder>
819Derived& SqlWhereClauseBuilder<Derived>::FullOuterJoin(std::string_view joinTable, OnChainCallable const& onClauseBuilder)
820{
821 return Join(JoinType::FULL, joinTable, onClauseBuilder);
822}
823
824template <typename Derived>
825inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::WhereRaw(std::string_view sqlConditionExpression)
826{
827 AppendWhereJunctor();
828
829 auto& condition = SearchCondition().condition;
830 condition += sqlConditionExpression;
831
832 return static_cast<Derived&>(*this);
833}
834
835template <typename Derived>
836inline LIGHTWEIGHT_FORCE_INLINE SqlSearchCondition& SqlWhereClauseBuilder<Derived>::SearchCondition() noexcept
837{
838 return static_cast<Derived*>(this)->SearchCondition();
839}
840
841template <typename Derived>
842inline LIGHTWEIGHT_FORCE_INLINE SqlQueryFormatter const& SqlWhereClauseBuilder<Derived>::Formatter() const noexcept
843{
844 return static_cast<Derived const*>(this)->Formatter();
845}
846
847template <typename Derived>
848inline LIGHTWEIGHT_FORCE_INLINE void SqlWhereClauseBuilder<Derived>::AppendWhereJunctor()
849{
850 using namespace std::string_view_literals;
851
852 auto& condition = SearchCondition().condition;
853
854 switch (m_nextWhereJunctor)
855 {
856 case WhereJunctor::Null:
857 break;
858 case WhereJunctor::Where:
859 condition += "\n WHERE "sv;
860 break;
861 case WhereJunctor::And:
862 condition += " AND "sv;
863 break;
864 case WhereJunctor::Or:
865 condition += " OR "sv;
866 break;
867 }
868
869 if (m_nextIsNot)
870 {
871 condition += "NOT "sv;
872 m_nextIsNot = false;
873 }
874
875 m_nextWhereJunctor = WhereJunctor::And;
876}
877
878template <typename Derived>
879template <typename ColumnName>
880 requires(std::same_as<ColumnName, SqlQualifiedTableColumnName> || std::convertible_to<ColumnName, std::string_view>
881 || std::same_as<ColumnName, SqlRawColumnNameView> || std::convertible_to<ColumnName, std::string>)
882inline LIGHTWEIGHT_FORCE_INLINE void SqlWhereClauseBuilder<Derived>::AppendColumnName(ColumnName const& columnName)
883{
884 SearchCondition().condition += detail::MakeSqlColumnName(columnName);
885}
886
887template <typename Derived>
888template <typename LiteralType>
889inline LIGHTWEIGHT_FORCE_INLINE void SqlWhereClauseBuilder<Derived>::AppendLiteralValue(LiteralType const& value)
890{
891 auto& searchCondition = SearchCondition();
892
893 if constexpr (std::is_same_v<LiteralType, SqlQualifiedTableColumnName>
894 || detail::OneOf<LiteralType, SqlNullType, std::nullopt_t> || std::is_same_v<LiteralType, SqlWildcardType>
895 || std::is_same_v<LiteralType, detail::RawSqlCondition>)
896 {
897 PopulateLiteralValueInto(value, searchCondition.condition);
898 }
899 else if (searchCondition.inputBindings)
900 {
901 searchCondition.condition += '?';
902 searchCondition.inputBindings->emplace_back(value);
903 }
904 else if constexpr (std::is_same_v<LiteralType, bool>)
905 {
906 searchCondition.condition += Formatter().BooleanLiteral(value);
907 }
908 else if constexpr (!WhereConditionLiteralType<LiteralType>::needsQuotes)
909 {
910 searchCondition.condition += std::format("{}", value);
911 }
912 else
913 {
914 searchCondition.condition += detail::MakeEscapedSqlString(std::format("{}", value));
915 }
916}
917
918template <typename Derived>
919template <typename LiteralType, typename TargetType>
920inline LIGHTWEIGHT_FORCE_INLINE void SqlWhereClauseBuilder<Derived>::PopulateLiteralValueInto(LiteralType const& value,
921 TargetType& target)
922{
923 if constexpr (std::is_same_v<LiteralType, SqlQualifiedTableColumnName>)
924 {
925 target += '"';
926 target += value.tableName;
927 target += "\".\"";
928 target += value.columnName;
929 target += '"';
930 }
931 else if constexpr (detail::OneOf<LiteralType, SqlNullType, std::nullopt_t>)
932 {
933 target += "NULL";
934 }
935 else if constexpr (std::is_same_v<LiteralType, SqlWildcardType>)
936 {
937 target += '?';
938 }
939 else if constexpr (std::is_same_v<LiteralType, detail::RawSqlCondition>)
940 {
941 target += value.condition;
942 }
943 else if constexpr (std::is_same_v<LiteralType, bool>)
944 {
945 target += Formatter().BooleanLiteral(value);
946 }
947 else if constexpr (!WhereConditionLiteralType<LiteralType>::needsQuotes)
948 {
949 target += std::format("{}", value);
950 }
951 else
952 {
953 target += detail::MakeEscapedSqlString(std::format("{}", value));
954 }
955}
956
957template <typename Derived>
958template <typename LiteralType>
959detail::RawSqlCondition SqlWhereClauseBuilder<Derived>::PopulateSqlSetExpression(LiteralType const& values)
960{
961 using namespace std::string_view_literals;
962 std::ostringstream fragment;
963 fragment << '(';
964 for (auto const&& [index, value]: values | std::views::enumerate)
965 {
966 if (index > 0)
967 fragment << ", "sv;
968
969 std::string valueString;
970 PopulateLiteralValueInto(value, valueString);
971 fragment << valueString;
972 }
973 fragment << ')';
974 return detail::RawSqlCondition { fragment.str() };
975}
976
977template <typename Derived>
978inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Join(JoinType joinType,
979 std::string_view joinTable,
980 std::string_view joinColumnName,
981 SqlQualifiedTableColumnName onOtherColumn)
982{
983 static constexpr std::array<std::string_view, 4> JoinTypeStrings = {
984 "INNER",
985 "LEFT OUTER",
986 "RIGHT OUTER",
987 "FULL OUTER",
988 };
989
990 SearchCondition().tableJoins += std::format("\n"
991 R"( {0} JOIN "{1}" ON "{1}"."{2}" = "{3}"."{4}")",
992 JoinTypeStrings[static_cast<std::size_t>(joinType)],
993 joinTable,
994 joinColumnName,
995 onOtherColumn.tableName,
996 onOtherColumn.columnName);
997 return static_cast<Derived&>(*this);
998}
999
1000template <typename Derived>
1001inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Join(JoinType joinType,
1002 std::string_view joinTable,
1003 std::string_view joinColumnName,
1004 std::string_view onMainTableColumn)
1005{
1006 return Join(joinType,
1007 joinTable,
1008 joinColumnName,
1009 SqlQualifiedTableColumnName { .tableName = SearchCondition().tableName, .columnName = onMainTableColumn });
1010}
1011
1012template <typename Derived>
1013template <typename Callable>
1014inline LIGHTWEIGHT_FORCE_INLINE Derived& SqlWhereClauseBuilder<Derived>::Join(JoinType joinType,
1015 std::string_view joinTable,
1016 Callable const& onClauseBuilder)
1017{
1018 static constexpr std::array<std::string_view, 4> JoinTypeStrings = {
1019 "INNER",
1020 "LEFT OUTER",
1021 "RIGHT OUTER",
1022 "FULL OUTER",
1023 };
1024
1025 size_t const originalSize = SearchCondition().tableJoins.size();
1026 SearchCondition().tableJoins +=
1027 std::format("\n {0} JOIN \"{1}\" ON ", JoinTypeStrings[static_cast<std::size_t>(joinType)], joinTable);
1028 size_t const sizeBefore = SearchCondition().tableJoins.size();
1029 onClauseBuilder(SqlJoinConditionBuilder { joinTable, &SearchCondition().tableJoins });
1030 size_t const sizeAfter = SearchCondition().tableJoins.size();
1031 if (sizeBefore == sizeAfter)
1032 SearchCondition().tableJoins.resize(originalSize);
1033
1034 return static_cast<Derived&>(*this);
1035}
Query builder for building JOIN conditions.
Definition Core.hpp:111
API to format SQL queries for different SQL dialects.
constexpr std::string_view RecordTableName
Holds the SQL tabl ename for the given record type.
Definition Utils.hpp:175
LIGHTWEIGHT_API std::vector< std::string > ToSql(SqlQueryFormatter const &formatter, SqlMigrationPlanElement const &element)
SqlQualifiedTableColumnName represents a column name qualified with a table name.
Definition Core.hpp:40
SqlWildcardType is a placeholder for an explicit wildcard input parameter in a SQL query.
Definition Core.hpp:21