Akonadi

querybuilder.h
1/*
2 SPDX-FileCopyrightText: 2007 Volker Krause <vkrause@kde.org>
3
4 SPDX-License-Identifier: LGPL-2.0-or-later
5*/
6
7#pragma once
8
9#include "dbtype.h"
10#include "query.h"
11
12#include <QList>
13#include <QPair>
14#include <QSqlQuery>
15#include <QString>
16#include <QStringList>
17#include <QVariant>
18#include <optional>
19
20#ifdef QUERYBUILDER_UNITTEST
21class QueryBuilderTest;
22#endif
23
24namespace Akonadi
25{
26namespace Server
27{
28class DataStore;
29/**
30 Helper class to construct arbitrary SQL queries.
31*/
33{
34 Q_DISABLE_COPY(QueryBuilder)
35
36public:
37 enum QueryType {
38 Select,
39 Insert,
40 Update,
41 Delete,
42 };
43
44 /**
45 * When the same table gets joined as both, Inner- and LeftJoin,
46 * it will be merged into a single InnerJoin since it is more
47 * restrictive.
48 */
49 enum JoinType {
50 /// NOTE: only supported for UPDATE and SELECT queries.
52 /// NOTE: only supported for SELECT queries
54 /// NOTE: only supported for SELECT queries
56 };
57
58 /**
59 * Defines the place at which a condition should be evaluated.
60 */
62 /// add condition to WHERE part of the query
64 /// add condition to HAVING part of the query
65 /// NOTE: only supported for SELECT queries
67
68 NUM_CONDITIONS
69 };
70
71 /**
72 Creates a new query builder.
73
74 @param table The main table to operate on.
75 */
76 explicit QueryBuilder(const QString &table, QueryType type = Select);
77 QueryBuilder(DataStore *store, const QString &table, QueryType type = Select);
78
79 /**
80 Creates a new query builder with subquery in FROM clause for SELECT queries.
81 @param tableQuery must be a valid select query
82 @param tableQueryAlias alias name for table query
83 */
84 explicit QueryBuilder(const QSqlQuery &tableQuery, const QString &tableQueryAlias);
85 QueryBuilder(DataStore *store, const QSqlQuery &tableQuery, const QString &tableQueryAlias);
86
87 QueryBuilder(QueryBuilder &&) noexcept;
88 QueryBuilder &operator=(QueryBuilder &&) noexcept;
89
90 ~QueryBuilder();
91
92 /**
93 Sets the database which should execute the query. Unfortunately the SQL "standard"
94 is not interpreted in the same way everywhere...
95 */
96 void setDatabaseType(DbType::Type type);
97
98 /**
99 Join a table to the query.
100
101 NOTE: make sure the @c JoinType is supported by the current @c QueryType
102 @param joinType The type of JOIN you want to add.
103 @param table The table to join.
104 @param condition the ON condition for this join.
105 */
106 void addJoin(JoinType joinType, const QString &table, const Query::Condition &condition);
107
108 /**
109 Join a table to the query.
110 This is a convenience method to create simple joins like e.g. 'LEFT JOIN t ON c1 = c2'.
111
112 NOTE: make sure the @c JoinType is supported by the current @c QueryType
113 @param joinType The type of JOIN you want to add.
114 @param table The table to join.
115 @param col1 The first column for the ON statement.
116 @param col2 The second column for the ON statement.
117 */
118 void addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2);
119
120 /**
121 Adds the given columns to a select query.
122 @param cols The columns you want to select.
123 */
124 void addColumns(const QStringList &cols);
125
126 /**
127 Adds the given column to a select query.
128 @param col The column to add.
129 */
130 void addColumn(const QString &col);
131
132 /**
133 * Adds the given case statement to a select query.
134 * @param caseStmt The case statement to add.
135 */
136 void addColumn(const Query::Case &caseStmt);
137
138 /**
139 * Adds an aggregation statement.
140 * @param col The column to aggregate on
141 * @param aggregate The aggregation function.
142 */
143 void addAggregation(const QString &col, const QString &aggregate);
144
145 /**
146 * Adds and aggregation statement with CASE
147 * @param caseStmt The case statement to aggregate on
148 * @param aggregate The aggregation function.
149 */
150 void addAggregation(const Query::Case &caseStmt, const QString &aggregate);
151
152 /**
153 Add a WHERE or HAVING condition which compares a column with a given value.
154 @param column The column that should be compared.
155 @param op The operator used for comparison.
156 @param value The value @p column is compared to.
157 @param type Defines whether this condition should be part of the WHERE or the HAVING
158 part of the query. Defaults to WHERE.
159 */
160 void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type = WhereCondition);
161
162 /**
163 * Add a WHERE or HAVING condition which compares a column with a given value.
164 *
165 * This is an overload specially for passing a list of IDs, which is a fairly common case in Akonadi.
166 *
167 * @param column The column that should be compared.
168 * @param op The operator used for comparison.
169 * @param value The value @p column is compared to.
170 * @param type Defines whether this condition should be part of the WHERE or the HAVING
171 * part of the query. Defaults to WHERE.
172 */
173 void addValueCondition(const QString &column, Query::CompareOperator op, const QList<qint64> &value, ConditionType type = WhereCondition);
174
175 /**
176 * Add a WHERE or HAVING condition which compares a column with a given value.
177 *
178 * This is an overload specially for passing a set of IDs, which is a fairly common case in Akonadi.
179 *
180 * @param column The column that should be compared.
181 * @param op The operator used for comparison.
182 * @param value The value @p column is compared to.
183 * @param type Defines whether this condition should be part of the WHERE or the HAVING
184 * part of the query. Defaults to WHERE.
185 */
186 void addValueCondition(const QString &column, Query::CompareOperator op, const QSet<qint64> &value, ConditionType type = WhereCondition);
187
188 /**
189 Add a WHERE or HAVING condition which compares a column with another column.
190 @param column The column that should be compared.
191 @param op The operator used for comparison.
192 @param column2 The column @p column is compared to.
193 @param type Defines whether this condition should be part of the WHERE or the HAVING
194 part of the query. Defaults to WHERE.
195 */
196 void addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type = WhereCondition);
197
198 /**
199 Add a WHERE condition. Use this to build hierarchical conditions.
200 @param condition The condition that the resultset should satisfy.
201 @param type Defines whether this condition should be part of the WHERE or the HAVING
202 part of the query. Defaults to WHERE.
203 */
204 void addCondition(const Query::Condition &condition, ConditionType type = WhereCondition);
205
206 /**
207 Define how WHERE or HAVING conditions are combined.
208 @todo Give this method a better name.
209 @param op The logical operator that should be used to combine the conditions.
210 @param type Defines whether the operator should be used for WHERE or for HAVING
211 conditions. Defaults to WHERE conditions.
212 */
213 void setSubQueryMode(Query::LogicOperator op, ConditionType type = WhereCondition);
214
215 /**
216 Add sort column.
217 @param column Name of the column to sort.
218 @param order Sort order
219 */
220 void addSortColumn(const QString &column, Query::SortOrder order = Query::Ascending);
221
222 /**
223 Add a GROUP BY column.
224 NOTE: Only supported in SELECT queries.
225 @param column Name of the column to use for grouping.
226 */
227 void addGroupColumn(const QString &column);
228
229 /**
230 Add list of columns to GROUP BY.
231 NOTE: Only supported in SELECT queries.
232 @param columns Names of columns to use for grouping.
233 */
234 void addGroupColumns(const QStringList &columns);
235
236 /**
237 Sets a column to the given value (only valid for INSERT and UPDATE queries).
238
239 Calling this function resets any values set by setColumnValues().
240
241 @param column Column to change.
242 @param value The value @p column should be set to.
243 */
244 void setColumnValue(const QString &column, const QVariant &value);
245
246 /**
247 * @brief Set column to given values (only valid for INSERT query).
248 *
249 * This will result in the query inserting multiple rows. The values must contain
250 * the same number of elements for each column otherwise the query will fail.
251 *
252 * Calling this function resets any values set by setColumnValue().
253 *
254 * @param column Column to insert into.
255 * @param values Values to be set for the @p column.
256 */
257 template<typename T>
258 void setColumnValues(const QString &column, const QList<T> &values)
259 {
260 setColumnValues(column, QVariant::fromValue(values));
261 }
262
263 /**
264 * Specify whether duplicates should be included in the result.
265 * @param distinct @c true to remove duplicates, @c false is the default
266 */
267 void setDistinct(bool distinct);
268
269 /**
270 * Limits the amount of retrieved rows.
271 * @param limit the maximum number of rows to retrieve.
272 * @param offset offset of the first row to retrieve.
273 * The default value for @p offset is -1, indicating no offset.
274 * @note This has no effect on anything but SELECT queries.
275 */
276 void setLimit(int limit, int offset = -1);
277
278 /**
279 * Sets the column used for identification in an INSERT statement.
280 * The default is "id", only change this on tables without such a column
281 * (usually n:m helper tables).
282 * @param column Name of the identification column, empty string to disable this.
283 * @note This only affects PostgreSQL.
284 * @see insertId()
285 */
286 void setIdentificationColumn(const QString &column);
287
288 /**
289 Returns the query, only valid after exec().
290 */
291 inline QSqlQuery &query()
292 {
293 return mQuery;
294 }
295
296 /**
297 Executes the query, returns true on success.
298 */
299 bool exec();
300
301 /**
302 Returns the ID of the newly created record (only valid for INSERT queries)
303 @note This will assert when being used with setIdentificationColumn() called
304 with an empty string.
305 @returns -1 if invalid
306 */
307 qint64 insertId();
308
309 /**
310 Indicate to the database to acquire an exclusive lock on the rows already during
311 SELECT statement.
312
313 Only makes sense in SELECT queries.
314 */
315 void setForUpdate(bool forUpdate = true);
316
317 /**
318 Returns the name of the main table or subquery.
319 */
320 QString getTable() const;
321
322 /**
323 * Returns concatenated table name with column name.
324 * @param column Column name.
325 * @note Pass only @p column that are not prefixed by table name.
326 */
327 QString getTableWithColumn(const QString &column) const;
328
329private:
330 void setColumnValues(const QString &column, const QVariant &values);
331
332 void buildQuery(QString *query);
333 void bindValue(QString *query, const QVariant &value);
334 void buildWhereCondition(QString *query, const Query::Condition &cond);
335 void buildCaseStatement(QString *query, const Query::Case &caseStmt);
336 void buildInsertColumns(QString *query);
337 void buildInsertValues(QString *query);
338 QString getTableQuery(const QSqlQuery &query, const QString &alias);
339
340 /**
341 * SQLite does not support JOINs with UPDATE, so we have to convert it into
342 * subqueries
343 */
344 void sqliteAdaptUpdateJoin(Query::Condition &cond);
345
346protected:
347 DataStore *dataStore() const
348 {
349 return mDataStore;
350 }
351
352private:
353 QString mTable;
354 std::optional<std::reference_wrapper<const QSqlQuery>> mTableSubQuery;
355 DataStore *mDataStore = nullptr;
356 DbType::Type mDatabaseType;
357 Query::Condition mRootCondition[NUM_CONDITIONS];
358 QSqlQuery mQuery;
359 QueryType mType;
360 QStringList mColumns;
361 QList<QVariant> mBindValues;
363 QStringList mGroupColumns;
364 QList<QPair<QString, QVariant>> mColumnValues;
365 QList<QPair<QString, QVariant>> mColumnMultiValues;
366 QString mIdentificationColumn;
367
368 // we must make sure that the tables are joined in the correct order
369 // QMap sorts by key which might invalidate the queries
370 QStringList mJoinedTables;
372 int mLimit;
373 int mOffset;
374 bool mDistinct;
375 bool mForUpdate = false;
376#ifdef QUERYBUILDER_UNITTEST
377 QString mStatement;
378 friend class ::QueryBuilderTest;
379#endif
380};
381
382} // namespace Server
383} // namespace Akonadi
This class handles all the database access.
Definition datastore.h:95
Helper class to construct arbitrary SQL queries.
void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type=WhereCondition)
Add a WHERE or HAVING condition which compares a column with a given value.
void setDatabaseType(DbType::Type type)
Sets the database which should execute the query.
void addGroupColumn(const QString &column)
Add a GROUP BY column.
void addSortColumn(const QString &column, Query::SortOrder order=Query::Ascending)
Add sort column.
QString getTableWithColumn(const QString &column) const
Returns concatenated table name with column name.
void addJoin(JoinType joinType, const QString &table, const Query::Condition &condition)
Join a table to the query.
bool exec()
Executes the query, returns true on success.
void addColumns(const QStringList &cols)
Adds the given columns to a select query.
void addCondition(const Query::Condition &condition, ConditionType type=WhereCondition)
Add a WHERE condition.
void setColumnValue(const QString &column, const QVariant &value)
Sets a column to the given value (only valid for INSERT and UPDATE queries).
void setColumnValues(const QString &column, const QList< T > &values)
Set column to given values (only valid for INSERT query).
void setDistinct(bool distinct)
Specify whether duplicates should be included in the result.
void addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type=WhereCondition)
Add a WHERE or HAVING condition which compares a column with another column.
void addGroupColumns(const QStringList &columns)
Add list of columns to GROUP BY.
void setForUpdate(bool forUpdate=true)
Indicate to the database to acquire an exclusive lock on the rows already during SELECT statement.
ConditionType
Defines the place at which a condition should be evaluated.
@ HavingCondition
add condition to HAVING part of the query NOTE: only supported for SELECT queries
@ WhereCondition
add condition to WHERE part of the query
QSqlQuery & query()
Returns the query, only valid after exec().
void setIdentificationColumn(const QString &column)
Sets the column used for identification in an INSERT statement.
qint64 insertId()
Returns the ID of the newly created record (only valid for INSERT queries)
void setSubQueryMode(Query::LogicOperator op, ConditionType type=WhereCondition)
Define how WHERE or HAVING conditions are combined.
void addAggregation(const QString &col, const QString &aggregate)
Adds an aggregation statement.
void addColumn(const QString &col)
Adds the given column to a select query.
QueryBuilder(const QString &table, QueryType type=Select)
Creates a new query builder.
JoinType
When the same table gets joined as both, Inner- and LeftJoin, it will be merged into a single InnerJo...
@ LeftOuterJoin
NOTE: only supported for SELECT queries.
@ InnerJoin
NOTE: only supported for UPDATE and SELECT queries.
@ LeftJoin
NOTE: only supported for SELECT queries.
QString getTable() const
Returns the name of the main table or subquery.
void setLimit(int limit, int offset=-1)
Limits the amount of retrieved rows.
Represents a WHERE condition tree.
Definition query.h:62
Type
Supported database types.
Definition dbtype.h:19
Helper integration between Akonadi and Qt.
QVariant fromValue(T &&value)
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Fri Oct 4 2024 11:57:21 by doxygen 1.12.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.