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