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

KDE's Doxygen guidelines are available online.