Akonadi

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

KDE's Doxygen guidelines are available online.