1/*
2 Copyright (c) 2007 Volker Krause <vkrause@kde.org>
3
4 This library is free software; you can redistribute it and/or modify it
5 under the terms of the GNU Library General Public License as published by
6 the Free Software Foundation; either version 2 of the License, or (at your
7 option) any later version.
8
9 This library is distributed in the hope that it will be useful, but WITHOUT
10 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
11 FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public
12 License for more details.
13
14 You should have received a copy of the GNU Library General Public License
15 along with this library; see the file COPYING.LIB. If not, write to the
16 Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
17 02110-1301, USA.
18*/
19
20#ifndef AKONADI_QUERYBUILDER_H
21#define AKONADI_QUERYBUILDER_H
22
23#include "query.h"
24#include "dbtype.h"
25
26#include <QtCore/QPair>
27#include <QtCore/QString>
28#include <QtCore/QStringList>
29#include <QtCore/QVariant>
30#include <QtCore/QVector>
31#include <QtSql/QSqlQuery>
32
33#ifdef QUERYBUILDER_UNITTEST
34class QueryBuilderTest;
35#endif
36
37namespace Akonadi {
38namespace Server {
39
40/**
41 Helper class to construct arbitrary SQL queries.
42*/
43class QueryBuilder
44{
45public:
46 enum QueryType {
47 Select,
48 Insert,
49 Update,
50 Delete
51 };
52
53 /**
54 * When the same table gets joined as both, Inner- and LeftJoin,
55 * it will be merged into a single InnerJoin since it is more
56 * restrictive.
57 */
58 enum JoinType {
59 ///NOTE: only supported for UPDATE and SELECT queries.
60 InnerJoin,
61 ///NOTE: only supported for SELECT queries
62 LeftJoin
63 };
64
65 /**
66 * Defines the place at which a condition should be evaluated.
67 */
68 enum ConditionType {
69 /// add condition to WHERE part of the query
70 WhereCondition,
71 /// add condition to HAVING part of the query
72 /// NOTE: only supported for SELECT queries
73 HavingCondition
74 };
75
76 /**
77 Creates a new query builder.
78
79 @param table The main table to operate on.
80 */
81 explicit QueryBuilder(const QString &table, QueryType type = Select);
82
83 /**
84 Sets the database which should execute the query. Unfortunately the SQL "standard"
85 is not interpreted in the same way everywhere...
86 */
87 void setDatabaseType(DbType::Type type);
88
89 /**
90 Join a table to the query.
91
92 NOTE: make sure the @c JoinType is supported by the current @c QueryType
93 @param joinType The type of JOIN you want to add.
94 @param table The table to join.
95 @param condition the ON condition for this join.
96 */
97 void addJoin(JoinType joinType, const QString &table, const Query::Condition &condition);
98
99 /**
100 Join a table to the query.
101 This is a convenience method to create simple joins like e.g. 'LEFT JOIN t ON c1 = c2'.
102
103 NOTE: make sure the @c JoinType is supported by the current @c QueryType
104 @param joinType The type of JOIN you want to add.
105 @param table The table to join.
106 @param col1 The first column for the ON statement.
107 @param col2 The second column for the ON statement.
108 */
109 void addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2);
110
111 /**
112 Adds the given columns to a select query.
113 @param cols The columns you want to select.
114 */
115 void addColumns(const QStringList &cols);
116
117 /**
118 Adds the given column to a select query.
119 @param col The column to add.
120 */
121 void addColumn(const QString &col);
122
123 /**
124 * Adds an aggregation statement.
125 * @param col The column to aggregate on
126 * @param aggregate The aggregation function.
127 */
128 void addAggregation(const QString &col, const QString &aggregate);
129
130 /**
131 Add a WHERE or HAVING condition which compares a column with a given value.
132 @param column The column that should be compared.
133 @param op The operator used for comparison
134 @param value The value @p column is compared to.
135 @param type Defines whether this condition should be part of the WHERE or the HAVING
136 part of the query. Defaults to WHERE.
137 */
138 void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type = WhereCondition);
139
140 /**
141 Add a WHERE or HAVING condition which compares a column with another column.
142 @param column The column that should be compared.
143 @param op The operator used for comparison.
144 @param column2 The column @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 addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type = WhereCondition);
149
150 /**
151 Add a WHERE condition. Use this to build hierarchical conditions.
152 @param condition The condition that the resultset should satisfy.
153 @param type Defines whether this condition should be part of the WHERE or the HAVING
154 part of the query. Defaults to WHERE.
155 */
156 void addCondition(const Query::Condition &condition, ConditionType type = WhereCondition);
157
158 /**
159 Define how WHERE or HAVING conditions are combined.
160 @todo Give this method a better name.
161 @param op The logical operator that should be used to combine the conditions.
162 @param type Defines whether the operator should be used for WHERE or for HAVING
163 conditions. Defaults to WHERE conditions.
164 */
165 void setSubQueryMode(Query::LogicOperator op, ConditionType type = WhereCondition);
166
167 /**
168 Add sort column.
169 @param column Name of the column to sort.
170 @param order Sort order
171 */
172 void addSortColumn(const QString &column, Query::SortOrder order = Query::Ascending);
173
174 /**
175 Add a GROUP BY column.
176 NOTE: Only supported in SELECT queries.
177 @param column Name of the column to use for grouping.
178 */
179 void addGroupColumn(const QString &column);
180
181 /**
182 Add list of columns to GROUP BY.
183 NOTE: Only supported in SELECT queries.
184 @param columns Names of columns to use for grouping.
185 */
186 void addGroupColumns(const QStringList &columns);
187
188 /**
189 Sets a column to the given value (only valid for INSERT and UPDATE queries).
190 @param column Column to change.
191 @param value The value @p column should be set to.
192 */
193 void setColumnValue(const QString &column, const QVariant &value);
194
195 /**
196 * Specify whether duplicates should be included in the result.
197 * @param distinct @c true to remove duplicates, @c false is the default
198 */
199 void setDistinct(bool distinct);
200
201 /**
202 * Limits the amount of retrieved rows.
203 * @param limit the maximum number of rows to retrieve.
204 * @note This has no effect on anything but SELECT queries.
205 */
206 void setLimit(int limit);
207
208 /**
209 * Sets the column used for identification in an INSERT statement.
210 * The default is "id", only change this on tables without such a column
211 * (usually n:m helper tables).
212 * @param column Name of the identification column, empty string to disable this.
213 * @note This only affects PostgreSQL.
214 * @see insertId()
215 */
216 void setIdentificationColumn(const QString &column);
217
218 /**
219 Returns the query, only valid after exec().
220 */
221 QSqlQuery &query();
222
223 /**
224 Executes the query, returns true on success.
225 */
226 bool exec();
227
228 /**
229 Returns the ID of the newly created record (only valid for INSERT queries)
230 @note This will assert when being used with setIdentificationColumn() called
231 with an empty string.
232 @returns -1 if invalid
233 */
234 qint64 insertId();
235
236private:
237 QString buildQuery();
238 QString bindValue(const QVariant &value);
239 QString buildWhereCondition(const Query::Condition &cond);
240
241 /**
242 * SQLite does not support JOINs with UPDATE, so we have to convert it into
243 * subqueries
244 */
245 void sqliteAdaptUpdateJoin(Query::Condition &cond);
246
247 bool retryLastTransaction(bool rollback = false);
248
249private:
250 QString mTable;
251 DbType::Type mDatabaseType;
252 QHash<ConditionType, Query::Condition> mRootCondition;
253 QSqlQuery mQuery;
254 QueryType mType;
255 QStringList mColumns;
256 QList<QVariant> mBindValues;
257 QVector<QPair<QString, Query::SortOrder> > mSortColumns;
258 QStringList mGroupColumns;
259 QVector<QPair<QString, QVariant> > mColumnValues;
260 QString mIdentificationColumn;
261
262 // we must make sure that the tables are joined in the correct order
263 // QMap sorts by key which might invalidate the queries
264 QStringList mJoinedTables;
265 QMap< QString, QPair< JoinType, Query::Condition > > mJoins;
266 int mLimit;
267 bool mDistinct;
268#ifdef QUERYBUILDER_UNITTEST
269 QString mStatement;
270 friend class ::QueryBuilderTest;
271#endif
272};
273
274} // namespace Server
275} // namespace Akonadi
276
277#endif
278