File indexing completed on 2024-11-10 04:40:21
0001 /* 0002 SPDX-FileCopyrightText: 2007 Volker Krause <vkrause@kde.org> 0003 0004 SPDX-License-Identifier: LGPL-2.0-or-later 0005 */ 0006 0007 #include "querybuildertest.h" 0008 #include "moc_querybuildertest.cpp" 0009 0010 #define QUERYBUILDER_UNITTEST 0011 0012 #include "storage/query.cpp" 0013 #include "storage/querybuilder.cpp" 0014 0015 #include <QTest> 0016 0017 QTEST_MAIN(QueryBuilderTest) 0018 0019 Q_DECLARE_METATYPE(QList<QVariant>) 0020 0021 using namespace Akonadi::Server; 0022 0023 void QueryBuilderTest::testQueryBuilder_data() 0024 { 0025 qRegisterMetaType<QList<QVariant>>(); 0026 mBuilders.clear(); 0027 QTest::addColumn<int>("qbId"); 0028 QTest::addColumn<QString>("sql"); 0029 QTest::addColumn<QList<QVariant>>("bindValues"); 0030 0031 QueryBuilder qb(QStringLiteral("table"), QueryBuilder::Select); 0032 qb.addColumn(QStringLiteral("col1")); 0033 mBuilders << qb; 0034 QTest::newRow("simple select") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table") << QList<QVariant>(); 0035 0036 qb.addColumn(QStringLiteral("col2")); 0037 mBuilders << qb; 0038 QTest::newRow("simple select 2") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table") << QList<QVariant>(); 0039 0040 qb.addValueCondition(QStringLiteral("col1"), Query::Equals, QVariant(5)); 0041 QList<QVariant> bindVals; 0042 bindVals << QVariant(5); 0043 mBuilders << qb; 0044 QTest::newRow("single where") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 )") << bindVals; 0045 0046 qb.addColumnCondition(QStringLiteral("col1"), Query::LessOrEqual, QStringLiteral("col2")); 0047 mBuilders << qb; 0048 QTest::newRow("flat where") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 AND col1 <= col2 )") << bindVals; 0049 0050 qb.setSubQueryMode(Query::Or); 0051 mBuilders << qb; 0052 QTest::newRow("flat where 2") << mBuilders.count() << QStringLiteral("SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 )") << bindVals; 0053 0054 Condition subCon; 0055 subCon.addColumnCondition(QStringLiteral("col1"), Query::Greater, QStringLiteral("col2")); 0056 subCon.addValueCondition(QStringLiteral("col1"), Query::NotEquals, QVariant()); 0057 qb.addCondition(subCon); 0058 mBuilders << qb; 0059 QTest::newRow("hierarchical where") << mBuilders.count() 0060 << QStringLiteral( 0061 "SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 OR ( col1 > col2 AND col1 <> NULL ) )") 0062 << bindVals; 0063 0064 qb = QueryBuilder(QStringLiteral("table")); 0065 qb.addAggregation(QStringLiteral("col1"), QStringLiteral("count")); 0066 mBuilders << qb; 0067 QTest::newRow("single aggregation") << mBuilders.count() << QStringLiteral("SELECT count(col1) FROM table") << QList<QVariant>(); 0068 0069 qb = QueryBuilder(QStringLiteral("table")); 0070 qb.addColumn(QStringLiteral("col1")); 0071 qb.addSortColumn(QStringLiteral("col1")); 0072 mBuilders << qb; 0073 QTest::newRow("single order by") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table ORDER BY col1 ASC") << QList<QVariant>(); 0074 0075 qb.addSortColumn(QStringLiteral("col2"), Query::Descending); 0076 mBuilders << qb; 0077 QTest::newRow("multiple order by") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table ORDER BY col1 ASC, col2 DESC") << QList<QVariant>(); 0078 0079 qb = QueryBuilder(QStringLiteral("table")); 0080 qb.addColumn(QStringLiteral("col1")); 0081 QStringList vals; 0082 vals << QStringLiteral("a") << QStringLiteral("b") << QStringLiteral("c"); 0083 qb.addValueCondition(QStringLiteral("col1"), Query::In, vals); 0084 bindVals.clear(); 0085 bindVals << QStringLiteral("a") << QStringLiteral("b") << QStringLiteral("c"); 0086 mBuilders << qb; 0087 QTest::newRow("where in") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table WHERE ( col1 IN ( :0, :1, :2 ) )") << bindVals; 0088 0089 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Select); 0090 qb.setDatabaseType(DbType::MySQL); 0091 qb.addColumn(QStringLiteral("col1")); 0092 qb.setLimit(1); 0093 mBuilders << qb; 0094 QTest::newRow("SELECT with LIMIT") << mBuilders.count() << QStringLiteral("SELECT col1 FROM table LIMIT 1") << QList<QVariant>(); 0095 0096 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Update); 0097 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0098 bindVals.clear(); 0099 bindVals << QStringLiteral("bla"); 0100 mBuilders << qb; 0101 QTest::newRow("update") << mBuilders.count() << QStringLiteral("UPDATE table SET col1 = :0") << bindVals; 0102 0103 qb = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update); 0104 qb.setDatabaseType(DbType::MySQL); 0105 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table1.id"), QStringLiteral("table2.id")); 0106 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.id")); 0107 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0108 bindVals.clear(); 0109 bindVals << QStringLiteral("bla"); 0110 mBuilders << qb; 0111 QTest::newRow("update multi table MYSQL") 0112 << mBuilders.count() << QStringLiteral("UPDATE table1, table2, table3 SET col1 = :0 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )") 0113 << bindVals; 0114 0115 qb = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update); 0116 qb.setDatabaseType(DbType::PostgreSQL); 0117 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table1.id"), QStringLiteral("table2.id")); 0118 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.id")); 0119 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0120 mBuilders << qb; 0121 QTest::newRow("update multi table PSQL") 0122 << mBuilders.count() 0123 << QStringLiteral("UPDATE table1 SET col1 = :0 FROM table2 JOIN table3 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )") << bindVals; 0124 /// TODO: test for subquery in SQLite case 0125 0126 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert); 0127 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0128 mBuilders << qb; 0129 QTest::newRow("insert single column") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1) VALUES (:0)") << bindVals; 0130 0131 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert); 0132 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0133 qb.setColumnValue(QStringLiteral("col2"), 5); 0134 bindVals << 5; 0135 mBuilders << qb; 0136 QTest::newRow("insert multi column") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals; 0137 0138 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Insert); 0139 qb.setDatabaseType(DbType::PostgreSQL); 0140 qb.setColumnValue(QStringLiteral("col1"), QStringLiteral("bla")); 0141 qb.setColumnValue(QStringLiteral("col2"), 5); 0142 mBuilders << qb; 0143 QTest::newRow("insert multi column PSQL") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1) RETURNING id") << bindVals; 0144 0145 qb.setIdentificationColumn(QString()); 0146 mBuilders << qb; 0147 QTest::newRow("insert multi column PSQL without id") << mBuilders.count() << QStringLiteral("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals; 0148 0149 // test GROUP BY foo 0150 bindVals.clear(); 0151 qb = QueryBuilder(QStringLiteral("table"), QueryBuilder::Select); 0152 qb.addColumn(QStringLiteral("foo")); 0153 qb.addGroupColumn(QStringLiteral("id1")); 0154 mBuilders << qb; 0155 QTest::newRow("select group by single column") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1") << bindVals; 0156 // test GROUP BY foo, bar 0157 qb.addGroupColumn(QStringLiteral("id2")); 0158 mBuilders << qb; 0159 QTest::newRow("select group by two columns") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2") << bindVals; 0160 // test: HAVING .addValueCondition() 0161 qb.addValueCondition(QStringLiteral("bar"), Equals, 1, QueryBuilder::HavingCondition); 0162 mBuilders << qb; 0163 bindVals << 1; 0164 QTest::newRow("select with having valueCond") << mBuilders.count() << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 )") 0165 << bindVals; 0166 // test: HAVING .addColumnCondition() 0167 qb.addColumnCondition(QStringLiteral("asdf"), Equals, QStringLiteral("yxcv"), QueryBuilder::HavingCondition); 0168 mBuilders << qb; 0169 QTest::newRow("select with having columnCond") << mBuilders.count() 0170 << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv )") << bindVals; 0171 // test: HAVING .addCondition() 0172 qb.addCondition(subCon, QueryBuilder::HavingCondition); 0173 mBuilders << qb; 0174 QTest::newRow("select with having condition") 0175 << mBuilders.count() 0176 << QStringLiteral("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )") << bindVals; 0177 // test: HAVING and WHERE 0178 qb.addValueCondition(QStringLiteral("bla"), Equals, 2, QueryBuilder::WhereCondition); 0179 mBuilders << qb; 0180 bindVals.clear(); 0181 bindVals << 2 << 1; 0182 QTest::newRow("select with having and where") 0183 << mBuilders.count() 0184 << QStringLiteral("SELECT foo FROM table WHERE ( bla = :0 ) GROUP BY id1, id2 HAVING ( bar = :1 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )") 0185 << bindVals; 0186 0187 { 0188 /// SELECT with JOINS 0189 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Select); 0190 qbTpl.setDatabaseType(DbType::MySQL); 0191 qbTpl.addColumn(QStringLiteral("col")); 0192 bindVals.clear(); 0193 0194 QueryBuilder qb = qbTpl; 0195 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id")); 0196 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table3"), QStringLiteral("table1.id"), QStringLiteral("table3.t1_id")); 0197 mBuilders << qb; 0198 QTest::newRow("select left join and inner join (different tables)") 0199 << mBuilders.count() 0200 << QStringLiteral("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id ) LEFT JOIN table3 ON ( table1.id = table3.t1_id )") 0201 << bindVals; 0202 0203 qb = qbTpl; 0204 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id")); 0205 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id")); 0206 mBuilders << qb; 0207 // join-condition too verbose but should not have any impact on speed 0208 QTest::newRow("select left join and inner join (same table)") 0209 << mBuilders.count() << QStringLiteral("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id AND ( table2.t1_id = table1.id ) )") 0210 << bindVals; 0211 0212 // order of joins in the query should be the same as we add the joins in code 0213 qb = qbTpl; 0214 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("b_table"), QStringLiteral("b_table.t1_id"), QStringLiteral("table1.id")); 0215 qb.addJoin(QueryBuilder::InnerJoin, QStringLiteral("a_table"), QStringLiteral("a_table.b_id"), QStringLiteral("b_table.id")); 0216 mBuilders << qb; 0217 QTest::newRow("select join order") 0218 << mBuilders.count() 0219 << 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 )") 0220 << bindVals; 0221 } 0222 0223 { 0224 /// SELECT with CASE 0225 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Select); 0226 qbTpl.setDatabaseType(DbType::MySQL); 0227 0228 QueryBuilder qb = qbTpl; 0229 qb.addColumn(QStringLiteral("col")); 0230 qb.addColumn(Query::Case(QStringLiteral("col1"), Query::Greater, 42, QStringLiteral("1"), QStringLiteral("0"))); 0231 bindVals.clear(); 0232 bindVals << 42; 0233 mBuilders << qb; 0234 QTest::newRow("select case simple") << mBuilders.count() << QStringLiteral("SELECT col, CASE WHEN ( col1 > :0 ) THEN 1 ELSE 0 END FROM table1") 0235 << bindVals; 0236 0237 qb = qbTpl; 0238 qb.addAggregation(QStringLiteral("table1.col1"), QStringLiteral("sum")); 0239 qb.addAggregation(QStringLiteral("table1.col2"), QStringLiteral("count")); 0240 Query::Condition cond(Query::Or); 0241 cond.addValueCondition(QStringLiteral("table3.col2"), Query::Equals, "value1"); 0242 cond.addValueCondition(QStringLiteral("table3.col2"), Query::Equals, "value2"); 0243 Query::Case caseStmt(cond, QStringLiteral("1"), QStringLiteral("0")); 0244 qb.addAggregation(caseStmt, QStringLiteral("sum")); 0245 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table2"), QStringLiteral("table1.col3"), QStringLiteral("table2.col1")); 0246 qb.addJoin(QueryBuilder::LeftJoin, QStringLiteral("table3"), QStringLiteral("table2.col2"), QStringLiteral("table3.col1")); 0247 bindVals.clear(); 0248 bindVals << QStringLiteral("value1") << QStringLiteral("value2"); 0249 mBuilders << qb; 0250 QTest::newRow("select case, aggregation and joins") 0251 << mBuilders.count() 0252 << QString( 0253 "SELECT sum(table1.col1), count(table1.col2), sum(CASE WHEN ( table3.col2 = :0 OR table3.col2 = :1 ) THEN 1 ELSE 0 END) " 0254 "FROM table1 " 0255 "LEFT JOIN table2 ON ( table1.col3 = table2.col1 ) " 0256 "LEFT JOIN table3 ON ( table2.col2 = table3.col1 )") 0257 << bindVals; 0258 } 0259 0260 { 0261 /// UPDATE with INNER JOIN 0262 QueryBuilder qbTpl = QueryBuilder(QStringLiteral("table1"), QueryBuilder::Update); 0263 qbTpl.setColumnValue(QStringLiteral("col"), 42); 0264 qbTpl.addJoin(QueryBuilder::InnerJoin, QStringLiteral("table2"), QStringLiteral("table2.t1_id"), QStringLiteral("table1.id")); 0265 qbTpl.addValueCondition(QStringLiteral("table2.answer"), NotEquals, "foo"); 0266 bindVals.clear(); 0267 bindVals << QVariant(42) << QVariant("foo"); 0268 0269 qb = qbTpl; 0270 qb.setDatabaseType(DbType::MySQL); 0271 mBuilders << qb; 0272 QTest::newRow("update inner join MySQL") << mBuilders.count() 0273 << QStringLiteral( 0274 "UPDATE table1, table2 SET col = :0 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )") 0275 << bindVals; 0276 0277 qb = qbTpl; 0278 qb.setDatabaseType(DbType::PostgreSQL); 0279 mBuilders << qb; 0280 QTest::newRow("update inner join PSQL") << mBuilders.count() 0281 << QStringLiteral( 0282 "UPDATE table1 SET col = :0 FROM table2 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )") 0283 << bindVals; 0284 0285 qb = qbTpl; 0286 qb.setDatabaseType(DbType::Sqlite); 0287 mBuilders << qb; 0288 QTest::newRow("update inner join SQLite") 0289 << mBuilders.count() 0290 << QStringLiteral("UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) <> :1 )") 0291 << bindVals; 0292 0293 qb = qbTpl; 0294 qb.setDatabaseType(DbType::Sqlite); 0295 Query::Condition condition; 0296 condition.addValueCondition(QStringLiteral("table2.col2"), Query::Equals, 666); 0297 condition.addValueCondition(QStringLiteral("table1.col3"), Query::Equals, "text"); 0298 qb.addCondition(condition); 0299 qb.addValueCondition(QStringLiteral("table1.id"), Query::Equals, 10); 0300 mBuilders << qb; 0301 bindVals << 666 << "text" << 10; 0302 QTest::newRow("update inner join SQLite with subcondition") 0303 << mBuilders.count() 0304 << QString( 0305 "UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE " 0306 "( ( table2.t1_id = table1.id ) ) ) <> :1 AND " 0307 "( ( SELECT table2.col2 FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) = :2 AND table1.col3 = :3 ) AND " 0308 "table1.id = :4 )") 0309 << bindVals; 0310 } 0311 } 0312 0313 void QueryBuilderTest::testQueryBuilder() 0314 { 0315 QFETCH(int, qbId); 0316 QFETCH(QString, sql); 0317 QFETCH(QList<QVariant>, bindValues); 0318 0319 --qbId; 0320 0321 QVERIFY(mBuilders[qbId].exec()); 0322 QCOMPARE(mBuilders[qbId].mStatement, sql); 0323 QCOMPARE(mBuilders[qbId].mBindValues, bindValues); 0324 } 0325 0326 void QueryBuilderTest::benchQueryBuilder() 0327 { 0328 const QString table1 = QStringLiteral("Table1"); 0329 const QString table2 = QStringLiteral("Table2"); 0330 const QString table3 = QStringLiteral("Table3"); 0331 const QString table1_id = QStringLiteral("Table1.id"); 0332 const QString table2_id = QStringLiteral("Table2.id"); 0333 const QString table3_id = QStringLiteral("Table3.id"); 0334 const QString aggregate = QStringLiteral("COUNT"); 0335 const QVariant value = QVariant::fromValue(QStringLiteral("asdf")); 0336 0337 const QStringList columns = QStringList() << QStringLiteral("Table1.id") << QStringLiteral("Table1.fooAsdf") << QStringLiteral("Table2.barLala") 0338 << QStringLiteral("Table3.xyzFsd"); 0339 0340 bool executed = true; 0341 0342 QBENCHMARK { 0343 QueryBuilder builder(table1, QueryBuilder::Select); 0344 builder.setDatabaseType(DbType::MySQL); 0345 builder.addColumns(columns); 0346 builder.addJoin(QueryBuilder::InnerJoin, table2, table2_id, table1_id); 0347 builder.addJoin(QueryBuilder::LeftJoin, table3, table1_id, table3_id); 0348 builder.addAggregation(columns.first(), aggregate); 0349 builder.addColumnCondition(columns.at(1), Query::LessOrEqual, columns.last()); 0350 builder.addValueCondition(columns.at(3), Query::Equals, value); 0351 builder.addSortColumn(columns.at(2)); 0352 builder.setLimit(10); 0353 builder.addGroupColumn(columns.at(3)); 0354 executed = executed && builder.exec(); 0355 } 0356 0357 QVERIFY(executed); 0358 }