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 |
34 | class QueryBuilderTest; |
35 | #endif |
36 | |
37 | namespace Akonadi { |
38 | namespace Server { |
39 | |
40 | /** |
41 | Helper class to construct arbitrary SQL queries. |
42 | */ |
43 | class QueryBuilder |
44 | { |
45 | public: |
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 | |
236 | private: |
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 | |
249 | private: |
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 | |