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

KDE's Doxygen guidelines are available online.