1 /*
2 SPDX-FileCopyrightText: 2007 Volker Krause <vkrause@kde.org>
3
4 SPDX-License-Identifier: LGPL-2.0-or-later
5 */
6
7 #include "querybuildertest.h"
8 #include "moc_querybuildertest.cpp"
9
10 #define QUERYBUILDER_UNITTEST
11
12 #include "storage/query.cpp"
13 #include "storage/querybuilder.cpp"
14
15 #include <QTest>
16
17 QTEST_MAIN(QueryBuilderTest)
18
19 Q_DECLARE_METATYPE(QVector<QVariant>)
20
21 using namespace Akonadi::Server;
22
testQueryBuilder_data()23 void QueryBuilderTest::testQueryBuilder_data()
24 {
25 qRegisterMetaType<QVector<QVariant>>();
26 mBuilders.clear();
27 QTest::addColumn<int>("qbId");
28 QTest::addColumn<QString>("sql");
29 QTest::addColumn<QVector<QVariant>>("bindValues");
30
31 QueryBuilder qb(QStringLiteral("table"), QueryBuilder::Select);
32 qb.addColumn(QStringLiteral("col1"));
33 mBuilders << qb;
34 QTest::newRow("simple select") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table") << QVector<QVariant>();
35
36 qb.addColumn(QStringLiteral("col2"));
37 mBuilders << qb;
38 QTest::newRow("simple select 2") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table") << QVector<QVariant>();
39
40 qb.addValueCondition(QStringLiteral("col1"), Query::Equals, QVariant(5));
41 QVector<QVariant> bindVals;
42 bindVals << QVariant(5);
43 mBuilders << qb;
44 QTest::newRow("single where") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 )") << bindVals;
45
46 qb.addColumnCondition(QStringLiteral("col1"), Query::LessOrEqual, QStringLiteral("col2"));
47 mBuilders << qb;
48 QTest::newRow("flat where") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 AND col1 <= col2 )") << bindVals;
49
50 qb.setSubQueryMode(Query::Or);
51 mBuilders << qb;
52 QTest::newRow("flat where 2") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 )") << bindVals;
53
54 Condition subCon;
55 subCon.addColumnCondition(QStringLiteral("col1"), Query::Greater, QStringLiteral("col2"));
56 subCon.addValueCondition(QStringLiteral("col1"), Query::NotEquals, QVariant());
57 qb.addCondition(subCon);
58 mBuilders << qb;
59 QTest::newRow("hierarchical where") << mBuilders.count()
60 << QStringLiteral(
61 "SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 OR ( col1 > col2 AND col1 <> NULL ) )")
62 << bindVals;
63
64 qb = QueryBuilder(QStringLiteral("table"));
65 qb.addAggregation(QStringLiteral("col1"), QStringLiteral("count"));
66 mBuilders << qb;
67 QTest::newRow("single aggregation") << mBuilders.count() << QStringLiteral("SELECT count(col1) FROM table") << QVector<QVariant>();
68
69 qb = QueryBuilder(QStringLiteral("table"));
70 qb.addColumn(QStringLiteral("col1"));
71 qb.addSortColumn(QStringLiteral("col1"));
72 mBuilders << qb;
73 QTest::newRow("single order by") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table ORDER BY col1 ASC") << QVector<QVariant>();
74
75 qb.addSortColumn(QStringLiteral("col2"), Query::Descending);
76 mBuilders << qb;
77 QTest::newRow("multiple order by") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table ORDER BY col1 ASC, col2 DESC") << QVector<QVariant>();
78
79 qb = QueryBuilder(QStringLiteral("table"));
80 qb.addColumn(QStringLiteral("col1"));
81 QStringList vals;
82 vals << QStringLiteral("a") << QStringLiteral("b") << QStringLiteral("c");
83 qb.addValueCondition(QStringLiteral("col1"), Query::In, vals);
84 bindVals.clear();
85 bindVals << QStringLiteral("a") << QStringLiteral("b") << QStringLiteral("c");
86 mBuilders << qb;
87 QTest::newRow("where in") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table WHERE ( col1 IN ( :0, :1, :2 ) )") << bindVals;
88
89 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Select);
90 qb.setDatabaseType(DbType::MySQL);
91 qb.addColumn(QStringLiteral("col1"));
92 qb.setLimit(1);
93 mBuilders << qb;
94 QTest::newRow("SELECT with LIMIT") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table LIMIT 1") << QVector<QVariant>();
95
96 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Update);
97 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
98 bindVals.clear();
99 bindVals << QStringLiteral("bla");
100 mBuilders << qb;
101 QTest::newRow("update") << mBuilders.count() << QStringLiteral("UPDATE table SET col1 = :0") << bindVals;
102
103 qb = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update);
104 qb.setDatabaseType(DbType::MySQL);
105 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table1.id"), QStringLiteral("table2.id"));
106 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.id"));
107 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
108 bindVals.clear();
109 bindVals << QStringLiteral("bla");
110 mBuilders << qb;
111 QTest::newRow("update multi table MYSQL")
112 << mBuilders.count() << QStringLiteral("UPDATE table1, table2, table3 SET col1 = :0 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )")
113 << bindVals;
114
115 qb = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update);
116 qb.setDatabaseType(DbType::PostgreSQL);
117 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table1.id"), QStringLiteral("table2.id"));
118 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.id"));
119 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
120 mBuilders << qb;
121 QTest::newRow("update multi table PSQL")
122 << mBuilders.count()
123 << QStringLiteral("UPDATE table1 SET col1 = :0 FROM table2 JOIN table3 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )") << bindVals;
124 /// TODO: test for subquery in SQLite case
125
126 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert);
127 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
128 mBuilders << qb;
129 QTest::newRow("insert single column") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1) VALUES (:0)") << bindVals;
130
131 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert);
132 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
133 qb.setColumnValue(QStringLiteral("col2"), 5);
134 bindVals << 5;
135 mBuilders << qb;
136 QTest::newRow("insert multi column") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals;
137
138 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert);
139 qb.setDatabaseType(DbType::PostgreSQL);
140 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla"));
141 qb.setColumnValue(QStringLiteral("col2"), 5);
142 mBuilders << qb;
143 QTest::newRow("insert multi column PSQL") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1) RETURNING id") << bindVals;
144
145 qb.setIdentificationColumn(QString());
146 mBuilders << qb;
147 QTest::newRow("insert multi column PSQL without id") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals;
148
149 // test GROUP BY foo
150 bindVals.clear();
151 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Select);
152 qb.addColumn(QStringLiteral("foo"));
153 qb.addGroupColumn(QStringLiteral("id1"));
154 mBuilders << qb;
155 QTest::newRow("select group by single column") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1") << bindVals;
156 // test GROUP BY foo, bar
157 qb.addGroupColumn(QStringLiteral("id2"));
158 mBuilders << qb;
159 QTest::newRow("select group by two columns") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2") << bindVals;
160 // test: HAVING .addValueCondition()
161 qb.addValueCondition(QStringLiteral("bar"), Equals, 1, QueryBuilder::HavingCondition);
162 mBuilders << qb;
163 bindVals << 1;
164 QTest::newRow("select with having valueCond") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 )")
165 << bindVals;
166 // test: HAVING .addColumnCondition()
167 qb.addColumnCondition(QStringLiteral("asdf"), Equals, QStringLiteral("yxcv"), QueryBuilder::HavingCondition);
168 mBuilders << qb;
169 QTest::newRow("select with having columnCond") << mBuilders.count()
170 << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv )") << bindVals;
171 // test: HAVING .addCondition()
172 qb.addCondition(subCon, QueryBuilder::HavingCondition);
173 mBuilders << qb;
174 QTest::newRow("select with having condition")
175 << mBuilders.count()
176 << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )") << bindVals;
177 // test: HAVING and WHERE
178 qb.addValueCondition(QStringLiteral("bla"), Equals, 2, QueryBuilder::WhereCondition);
179 mBuilders << qb;
180 bindVals.clear();
181 bindVals << 2 << 1;
182 QTest::newRow("select with having and where")
183 << mBuilders.count()
184 << QStringLiteral("SELECT foo FROM table WHERE ( bla = :0 ) GROUP BY id1, id2 HAVING ( bar = :1 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )")
185 << bindVals;
186
187 {
188 /// SELECT with JOINS
189 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Select);
190 qbTpl.setDatabaseType(DbType::MySQL);
191 qbTpl.addColumn(QStringLiteral("col"));
192 bindVals.clear();
193
194 QueryBuilder qb = qbTpl;
195 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id"));
196 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.t1_id"));
197 mBuilders << qb;
198 QTest::newRow("select left join and inner join (different tables)")
199 << mBuilders.count()
200 << QStringLiteral("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id ) LEFT JOIN table3 ON ( table1.id = table3.t1_id )")
201 << bindVals;
202
203 qb = qbTpl;
204 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id"));
205 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id"));
206 mBuilders << qb;
207 // join-condition too verbose but should not have any impact on speed
208 QTest::newRow("select left join and inner join (same table)")
209 << mBuilders.count() << QStringLiteral("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id AND ( table2.t1_id = table1.id ) )")
210 << bindVals;
211
212 // order of joins in the query should be the same as we add the joins in code
213 qb = qbTpl;
214 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("b_table"), QStringLiteral("b_table.t1_id"), QStringLiteral("table1.id"));
215 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("a_table"), QStringLiteral("a_table.b_id"), QStringLiteral("b_table.id"));
216 mBuilders << qb;
217 QTest::newRow("select join order")
218 << mBuilders.count()
219 << QStringLiteral("SELECT col FROM table1 INNER JOIN b_table ON ( b_table.t1_id = table1.id ) INNER JOIN a_table ON ( a_table.b_id = b_table.id )")
220 << bindVals;
221 }
222
223 {
224 /// SELECT with CASE
225 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Select);
226 qbTpl.setDatabaseType(DbType::MySQL);
227
228 QueryBuilder qb = qbTpl;
229 qb.addColumn(QStringLiteral("col"));
230 qb.addColumn(Query::Case(QStringLiteral("col1"), Query::Greater, 42, QStringLiteral("1"), QStringLiteral("0")));
231 bindVals.clear();
232 bindVals << 42;
233 mBuilders << qb;
234 QTest::newRow("select case simple") << mBuilders.count() << QStringLiteral("SELECT col, CASE WHEN ( col1 > :0 ) THEN 1 ELSE 0 END FROM table1")
235 << bindVals;
236
237 qb = qbTpl;
238 qb.addAggregation(QStringLiteral("table1.col1"), QStringLiteral("sum"));
239 qb.addAggregation(QStringLiteral("table1.col2"), QStringLiteral("count"));
240 Query::Condition cond(Query::Or);
241 cond.addValueCondition(QStringLiteral("table3.col2"), Query::Equals, "value1");
242 cond.addValueCondition(QStringLiteral("table3.col2"), Query::Equals, "value2");
243 Query::Case caseStmt(cond, QStringLiteral("1"), QStringLiteral("0"));
244 qb.addAggregation(caseStmt, QStringLiteral("sum"));
245 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table2"), QStringLiteral("table1.col3"), QStringLiteral("table2.col1"));
246 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table3"), QStringLiteral("table2.col2"), QStringLiteral("table3.col1"));
247 bindVals.clear();
248 bindVals << QStringLiteral("value1") << QStringLiteral("value2");
249 mBuilders << qb;
250 QTest::newRow("select case, aggregation and joins")
251 << mBuilders.count()
252 << QString(
253 "SELECT sum(table1.col1), count(table1.col2), sum(CASE WHEN ( table3.col2 = :0 OR table3.col2 = :1 ) THEN 1 ELSE 0 END) "
254 "FROM table1 "
255 "LEFT JOIN table2 ON ( table1.col3 = table2.col1 ) "
256 "LEFT JOIN table3 ON ( table2.col2 = table3.col1 )")
257 << bindVals;
258 }
259
260 {
261 /// UPDATE with INNER JOIN
262 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update);
263 qbTpl.setColumnValue(QStringLiteral("col"), 42);
264 qbTpl.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id"));
265 qbTpl.addValueCondition(QStringLiteral("table2.answer"), NotEquals, "foo");
266 bindVals.clear();
267 bindVals << QVariant(42) << QVariant("foo");
268
269 qb = qbTpl;
270 qb.setDatabaseType(DbType::MySQL);
271 mBuilders << qb;
272 QTest::newRow("update inner join MySQL") << mBuilders.count()
273 << QStringLiteral(
274 "UPDATE table1, table2 SET col = :0 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )")
275 << bindVals;
276
277 qb = qbTpl;
278 qb.setDatabaseType(DbType::PostgreSQL);
279 mBuilders << qb;
280 QTest::newRow("update inner join PSQL") << mBuilders.count()
281 << QStringLiteral(
282 "UPDATE table1 SET col = :0 FROM table2 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )")
283 << bindVals;
284
285 qb = qbTpl;
286 qb.setDatabaseType(DbType::Sqlite);
287 mBuilders << qb;
288 QTest::newRow("update inner join SQLite")
289 << mBuilders.count()
290 << QStringLiteral("UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) <> :1 )")
291 << bindVals;
292
293 qb = qbTpl;
294 qb.setDatabaseType(DbType::Sqlite);
295 Query::Condition condition;
296 condition.addValueCondition(QStringLiteral("table2.col2"), Query::Equals, 666);
297 condition.addValueCondition(QStringLiteral("table1.col3"), Query::Equals, "text");
298 qb.addCondition(condition);
299 qb.addValueCondition(QStringLiteral("table1.id"), Query::Equals, 10);
300 mBuilders << qb;
301 bindVals << 666 << "text" << 10;
302 QTest::newRow("update inner join SQLite with subcondition")
303 << mBuilders.count()
304 << QString(
305 "UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE "
306 "( ( table2.t1_id = table1.id ) ) ) <> :1 AND "
307 "( ( SELECT table2.col2 FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) = :2 AND table1.col3 = :3 ) AND "
308 "table1.id = :4 )")
309 << bindVals;
310 }
311 }
312
testQueryBuilder()313 void QueryBuilderTest::testQueryBuilder()
314 {
315 QFETCH(int, qbId);
316 QFETCH(QString, sql);
317 QFETCH(QVector<QVariant>, bindValues);
318
319 --qbId;
320
321 QVERIFY(mBuilders[qbId].exec());
322 QCOMPARE(mBuilders[qbId].mStatement, sql);
323 QCOMPARE(mBuilders[qbId].mBindValues, bindValues);
324 }
325
benchQueryBuilder()326 void QueryBuilderTest::benchQueryBuilder()
327 {
328 const QString table1 = QStringLiteral("Table1");
329 const QString table2 = QStringLiteral("Table2");
330 const QString table3 = QStringLiteral("Table3");
331 const QString table1_id = QStringLiteral("Table1.id");
332 const QString table2_id = QStringLiteral("Table2.id");
333 const QString table3_id = QStringLiteral("Table3.id");
334 const QString aggregate = QStringLiteral("COUNT");
335 const QVariant value = QVariant::fromValue(QStringLiteral("asdf"));
336
337 const QStringList columns = QStringList() << QStringLiteral("Table1.id") << QStringLiteral("Table1.fooAsdf") << QStringLiteral("Table2.barLala")
338 << QStringLiteral("Table3.xyzFsd");
339
340 bool executed = true;
341
342 QBENCHMARK {
343 QueryBuilder builder(table1, QueryBuilder::Select);
344 builder.setDatabaseType(DbType::MySQL);
345 builder.addColumns(columns);
346 builder.addJoin(QueryBuilder::InnerJoin, table2, table2_id, table1_id);
347 builder.addJoin(QueryBuilder::LeftJoin, table3, table1_id, table3_id);
348 builder.addAggregation(columns.first(), aggregate);
349 builder.addColumnCondition(columns.at(1), Query::LessOrEqual, columns.last());
350 builder.addValueCondition(columns.at(3), Query::Equals, value);
351 builder.addSortColumn(columns.at(2));
352 builder.setLimit(10);
353 builder.addGroupColumn(columns.at(3));
354 executed = executed && builder.exec();
355 }
356
357 QVERIFY(executed);
358 }
359