kdb/autotests/OrderByColumnTest.cpp

0001 /* This file is part of the KDE project
0002    Copyright (C) 2018 Jarosław Staniek <staniek@kde.org>
0003
0004    This program is free software; you can redistribute it and/or
0005    modify it under the terms of the GNU Library General Public
0006    License as published by the Free Software Foundation; either
0007    version 2 of the License, or (at your option) any later version.
0008
0009    This program is distributed in the hope that it will be useful,
0010    but WITHOUT ANY WARRANTY; without even the implied warranty of
0011    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
0012    Library General Public License for more details.
0013
0014    You should have received a copy of the GNU Library General Public License
0015    along with this program; see the file COPYING.  If not, write to
0016    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
0017  * Boston, MA 02110-1301, USA.
0018 */
0019
0020 #include "OrderByColumnTest.h"
0021
0022 #include <KDbExpression>
0023 #include <KDbOrderByColumn>
0024 #include <KDbQueryAsterisk>
0025 #include <KDbQuerySchema>
0026 #include <KDbNativeStatementBuilder>
0027
0028 #include <QTest>
0029
0030 QTEST_GUILESS_MAIN(OrderByColumnTest)
0031
0032 void OrderByColumnTest::initTestCase()
0033 {
0034 }
0035
0036 void OrderByColumnTest::testSelect1Query()
0037 {
0038     QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
0039     KDbQuerySchema query;
0040     KDbField *oneField = new KDbField;
0041     oneField->setExpression(KDbConstExpression(KDbToken::CHARACTER_STRING_LITERAL, "foo"));
0042     query.addField(oneField);
0043     KDbOrderByColumnList* orderBy = query.orderByColumnList();
0044     QVERIFY(orderBy);
0045     QVERIFY(orderBy->isEmpty());
0046     QCOMPARE(orderBy->count(), 0);
0047     orderBy->appendField(oneField);
0048     KDbConnection *conn = utils.connection();
0049
0050     // automatic alias "expr1"
0051     KDbEscapedString sql;
0052     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0053     QCOMPARE(sql, "SELECT 'foo' AS expr1 ORDER BY expr1");
0054     QVERIFY(!orderBy->isEmpty());
0055     QCOMPARE(orderBy->count(), 1);
0056     const int indexOfField = query.indexOf(*oneField);
0057     QCOMPARE(indexOfField, 0);
0058     const QString alias(query.columnAlias(indexOfField));
0059     QVERIFY(!alias.isEmpty());
0060     KDbOrderByColumn *orderByColumn = orderBy->value(indexOfField);
0061     QVERIFY(orderByColumn);
0062     QVERIFY(!orderByColumn->column());
0063     QCOMPARE(orderByColumn->field(), oneField);
0064     QVERIFY(!orderBy->value(orderBy->count() + 10));
0065     KDbEscapedString orderBySqlOldApi = orderBy->toSqlString(true, conn, KDb::KDbEscaping);
0066     QCOMPARE(orderBySqlOldApi, ""); // alias is not used
0067     KDbEscapedString orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
0068     QCOMPARE(orderBySql, alias); // alias is used to point to the column "'foo'"
0069
0070     // change alias to something other than valid ID
0071     QVERIFY(query.setColumnAlias(indexOfField, "0"));
0072     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0073     QCOMPARE(sql, "SELECT 'foo' AS \"0\" ORDER BY \"0\"");
0074     orderBySqlOldApi = orderBy->toSqlString(true, conn, KDb::KDbEscaping);
0075     QCOMPARE(orderBySqlOldApi, ""); // alias is not used
0076     orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
0077     QCOMPARE(orderBySql, "\"0\""); // alias is used to point to the column "'foo'"
0078 }
0079
0080 void OrderByColumnTest::testOrderByIndex()
0081 {
0082     QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
0083     KDbQuerySchema query;
0084     KDbTableSchema *carsTable = utils.connection()->tableSchema("cars");
0085     QVERIFY(carsTable);
0086     query.addTable(carsTable);
0087     query.addAsterisk(new KDbQueryAsterisk(&query));
0088     KDbOrderByColumnList* orderBy = query.orderByColumnList();
0089     KDbConnection *conn = utils.connection();
0090
0091     // "SELECT * FROM cars ORDER BY model ASC, owner DESC"
0092     QVERIFY(query.orderByColumnList()->isEmpty());
0093     QVERIFY(orderBy->appendColumn(conn, &query,
0094                                   KDbOrderByColumn::SortOrder::Ascending, 2));
0095     QVERIFY(orderBy->appendColumn(conn, &query,
0096                                   KDbOrderByColumn::SortOrder::Descending, 1));
0097     KDbEscapedString sql;
0098     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0099     QCOMPARE(sql, "SELECT cars.* FROM cars ORDER BY 3, 2 DESC");
0100
0101     QVERIFY2(!orderBy->appendColumn(conn, &query,
0102                                     KDbOrderByColumn::SortOrder::Ascending, 3),
0103              "appendField for null");
0104 }
0105
0106 void OrderByColumnTest::testOrderByColumnName()
0107 {
0108     QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
0109     KDbQuerySchema query;
0110     KDbTableSchema *carsTable = utils.connection()->tableSchema("cars");
0111     QVERIFY(carsTable);
0112     query.addTable(carsTable);
0113     query.addAsterisk(new KDbQueryAsterisk(&query));
0114
0115     // "SELECT * FROM cars ORDER BY model, owner"
0116     QVERIFY(query.orderByColumnList());
0117     QVERIFY(query.orderByColumnList()->isEmpty());
0118     QCOMPARE(query.orderByColumnList()->count(), 0);
0119
0120     KDbOrderByColumnList* orderBy = query.orderByColumnList();
0121     QVERIFY(orderBy);
0122     QVERIFY(orderBy->isEmpty());
0123     KDbField *modelField = carsTable->field("model");
0124     QVERIFY(modelField);
0125     KDbField *ownerField = carsTable->field("owner");
0126     QVERIFY(ownerField);
0127     orderBy->appendField(modelField);
0128     orderBy->appendField(ownerField);
0129     KDbConnection *conn = utils.connection();
0130     KDbEscapedString orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
0131     QCOMPARE(orderBySql, "cars.model, cars.owner");
0132
0133     KDbEscapedString sql;
0134     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0135     QCOMPARE(sql, "SELECT cars.* FROM cars ORDER BY model, owner");
0136     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0137     QCOMPARE(sql, "SELECT [cars].* FROM [cars] ORDER BY [model] COLLATE '', [owner]");
0138
0139     // "SELECT * FROM cars ORDER BY model ASC, owner DESC"
0140     orderBy->clear();
0141     QVERIFY(query.orderByColumnList()->isEmpty());
0142     orderBy->appendField(modelField, KDbOrderByColumn::SortOrder::Ascending);
0143     orderBy->appendField(ownerField, KDbOrderByColumn::SortOrder::Descending);
0144     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0145     const char validSelect1[] = "SELECT cars.* FROM cars ORDER BY model, owner DESC";
0146     QCOMPARE(sql, validSelect1);
0147     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0148     const char validDriverSelect1[] = "SELECT [cars].* FROM [cars] ORDER BY [model] COLLATE '', [owner] DESC";
0149     QCOMPARE(sql, validDriverSelect1);
0150
0151     // The same query, adding null field
0152     orderBy->clear();
0153     QVERIFY(query.orderByColumnList()->isEmpty());
0154     orderBy->appendField(nullptr);
0155     QVERIFY2(query.orderByColumnList()->isEmpty(), "Adding null fields should not affect OREDR BY");
0156     orderBy->appendField(modelField, KDbOrderByColumn::SortOrder::Ascending);
0157     orderBy->appendField(ownerField, KDbOrderByColumn::SortOrder::Descending);
0158     orderBy->appendField(nullptr);
0159     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0160     QCOMPARE(sql, validSelect1);
0161     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0162     QCOMPARE(sql, validDriverSelect1);
0163
0164     // The same query, overload
0165     orderBy->clear();
0166     QVERIFY(query.orderByColumnList()->isEmpty());
0167     QVERIFY(orderBy->appendFields(conn, &query,
0168                                   "model", KDbOrderByColumn::SortOrder::Ascending,
0169                                   "owner", KDbOrderByColumn::SortOrder::Descending));
0170     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0171     QCOMPARE(sql, validSelect1);
0172     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0173     QCOMPARE(sql, validDriverSelect1);
0174
0175     // The same query, overload
0176     orderBy->clear();
0177     QVERIFY(query.orderByColumnList()->isEmpty());
0178     QVERIFY(orderBy->appendField(conn, &query, "model", KDbOrderByColumn::SortOrder::Ascending));
0179     QVERIFY(orderBy->appendField(conn, &query, "owner", KDbOrderByColumn::SortOrder::Descending));
0180     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0181     QCOMPARE(sql, validSelect1);
0182     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0183     QCOMPARE(sql, validDriverSelect1);
0184
0185     QCOMPARE(orderBy->count(), 2);
0186     QTest::ignoreMessage(QtWarningMsg, "no such field \"\"");
0187     QVERIFY2(!orderBy->appendField(conn, &query, ""), "appendField for null");
0188     QCOMPARE(orderBy->count(), 2);
0189
0190     // The same query, overload
0191     orderBy->clear();
0192     QCOMPARE(orderBy->count(), 0);
0193     QVERIFY(query.orderByColumnList()->isEmpty());
0194     KDbQueryColumnInfo::Vector columns = query.fieldsExpanded(conn);
0195     KDbQueryColumnInfo *ownerColumnInfo = columns.value(1);
0196     QVERIFY(ownerColumnInfo);
0197     KDbQueryColumnInfo *modelColumnInfo = columns.value(2);
0198     QVERIFY(modelColumnInfo);
0199     orderBy->appendColumn(modelColumnInfo, KDbOrderByColumn::SortOrder::Ascending);
0200     orderBy->appendColumn(ownerColumnInfo, KDbOrderByColumn::SortOrder::Descending);
0201     QCOMPARE(orderBy->count(), 2);
0202     QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
0203     QCOMPARE(sql, validSelect1);
0204     QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
0205     QCOMPARE(sql, validDriverSelect1);
0206 }
0207
0208 //! @todo Test KDbQuerySchema::setOrderByColumnList
0209 //! @todo Test more KDbOrderByColumnList and KDbOrderByColumn
0210
0211 void OrderByColumnTest::cleanupTestCase()
0212 {
0213 }