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