Differential D12873 Diff 34134 autotests/OrderByColumnTest.cpp

Changeset View

Standalone View

View Options

autotests/OrderByColumnTest.cpp

  • This file was added.
1​/* This file is part of the KDE project
2​ Copyright (C) 2018 Jarosław Staniek <staniek@kde.org>
3​
4​ This program is free software; you can redistribute it and/or
5​ modify it under the terms of the GNU Library General Public
6​ License as published by the Free Software Foundation; either
7​ version 2 of the License, or (at your option) any later version.
8​
9​ This program is distributed in the hope that it will be useful,
10​ but WITHOUT ANY WARRANTY; without even the implied warranty of
11​ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12​ Library General Public License for more details.
13​
14​ You should have received a copy of the GNU Library General Public License
15​ along with this program; see the file COPYING. If not, write to
16​ the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
17​ * Boston, MA 02110-1301, USA.
18​*/
19​
20​#include "OrderByColumnTest.h"
21​
22​#include <KDbExpression>
23​#include <KDbOrderByColumn>
24​#include <KDbQueryAsterisk>
25​#include <KDbQuerySchema>
26​#include <KDbNativeStatementBuilder>
27​
28​#include <QTest>
29​
30​QTEST_GUILESS_MAIN(OrderByColumnTest)
31​
32​void OrderByColumnTest::initTestCase()
33​{
34​}
35​
36​void OrderByColumnTest::testSelect1Query()
37​{
38​ QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
39​ KDbQuerySchema query;
40​ KDbField *oneField = new KDbField;
41​ oneField->setExpression(KDbConstExpression(KDbToken::CHARACTER_STRING_LITERAL, "foo"));
42​ query.addField(oneField);
43​ KDbOrderByColumnList* orderBy = query.orderByColumnList();
44​ QVERIFY(orderBy);
45​ QVERIFY(orderBy->isEmpty());
46​ QCOMPARE(orderBy->count(), 0);
47​ orderBy->appendField(oneField);
48​ KDbConnection *conn = utils.connection.data();
49​
50​ // automatic alias "expr1"
51​ KDbEscapedString sql;
52​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
53​ QCOMPARE(sql, "SELECT 'foo' AS expr1 ORDER BY expr1");
54​ QVERIFY(!orderBy->isEmpty());
55​ QCOMPARE(orderBy->count(), 1);
56​ const int indexOfField = query.indexOf(*oneField);
57​ QCOMPARE(indexOfField, 0);
58​ const QString alias(query.columnAlias(indexOfField));
59​ QVERIFY(!alias.isEmpty());
60​ KDbOrderByColumn *orderByColumn = orderBy->value(indexOfField);
61​ QVERIFY(orderByColumn);
62​ QVERIFY(!orderByColumn->column());
63​ QCOMPARE(orderByColumn->field(), oneField);
64​ QVERIFY(!orderBy->value(orderBy->count() + 10));
65​ KDbEscapedString orderBySqlOldApi = orderBy->toSqlString(true, conn, KDb::KDbEscaping);
66​ QCOMPARE(orderBySqlOldApi, ""); // alias is not used
67​ KDbEscapedString orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
68​ QCOMPARE(orderBySql, alias); // alias is used to point to the column "'foo'"
69​
70​ // change alias to something other than valid ID
71​ QVERIFY(query.setColumnAlias(indexOfField, "0"));
72​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
73​ QCOMPARE(sql, "SELECT 'foo' AS \"0\" ORDER BY \"0\"");
74​ orderBySqlOldApi = orderBy->toSqlString(true, conn, KDb::KDbEscaping);
75​ QCOMPARE(orderBySqlOldApi, ""); // alias is not used
76​ orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
77​ QCOMPARE(orderBySql, "\"0\""); // alias is used to point to the column "'foo'"
78​}
79​
80​void OrderByColumnTest::testOrderByIndex()
81​{
82​ QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
83​ KDbQuerySchema query;
84​ KDbTableSchema *carsTable = utils.connection->tableSchema("cars");
85​ QVERIFY(carsTable);
86​ query.addTable(carsTable);
87​ query.addAsterisk(new KDbQueryAsterisk(&query));
88​ KDbOrderByColumnList* orderBy = query.orderByColumnList();
89​ KDbConnection *conn = utils.connection.data();
90​
91​ // "SELECT * FROM cars ORDER BY model ASC, owner DESC"
92​ QVERIFY(query.orderByColumnList()->isEmpty());
93​ QVERIFY(orderBy->appendColumn(conn, &query,
94​ KDbOrderByColumn::SortOrder::Ascending, 2));
95​ QVERIFY(orderBy->appendColumn(conn, &query,
96​ KDbOrderByColumn::SortOrder::Descending, 1));
97​ KDbEscapedString sql;
98​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
99​ QCOMPARE(sql, "SELECT cars.* FROM cars ORDER BY 3, 2 DESC");
100​
101​ QVERIFY2(!orderBy->appendColumn(conn, &query,
102​ KDbOrderByColumn::SortOrder::Ascending, 3),
103​ "appendField for null");
104​}
105​
106​void OrderByColumnTest::testOrderByColumnName()
107​{
108​ QVERIFY(utils.testCreateDbWithTables("OrderByColumnTest"));
109​ KDbQuerySchema query;
110​ KDbTableSchema *carsTable = utils.connection->tableSchema("cars");
111​ QVERIFY(carsTable);
112​ query.addTable(carsTable);
113​ query.addAsterisk(new KDbQueryAsterisk(&query));
114​
115​ // "SELECT * FROM cars ORDER BY model, owner"
116​ QVERIFY(query.orderByColumnList());
117​ QVERIFY(query.orderByColumnList()->isEmpty());
118​ QCOMPARE(query.orderByColumnList()->count(), 0);
119​
120​ KDbOrderByColumnList* orderBy = query.orderByColumnList();
121​ QVERIFY(orderBy);
122​ QVERIFY(orderBy->isEmpty());
123​ KDbField *modelField = carsTable->field("model");
124​ QVERIFY(modelField);
125​ KDbField *ownerField = carsTable->field("owner");
126​ QVERIFY(ownerField);
127​ orderBy->appendField(modelField);
128​ orderBy->appendField(ownerField);
129​ KDbConnection *conn = utils.connection.data();
130​ KDbEscapedString orderBySql = orderBy->toSqlString(true, conn, &query, KDb::KDbEscaping);
131​ QCOMPARE(orderBySql, "cars.model, cars.owner");
132​
133​ KDbEscapedString sql;
134​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
135​ QCOMPARE(sql, "SELECT cars.* FROM cars ORDER BY model, owner");
136​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
137​ QCOMPARE(sql, "SELECT [cars].* FROM [cars] ORDER BY [model] COLLATE '', [owner]");
138​
139​ // "SELECT * FROM cars ORDER BY model ASC, owner DESC"
140​ orderBy->clear();
141​ QVERIFY(query.orderByColumnList()->isEmpty());
142​ orderBy->appendField(modelField, KDbOrderByColumn::SortOrder::Ascending);
143​ orderBy->appendField(ownerField, KDbOrderByColumn::SortOrder::Descending);
144​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
145​ const char validSelect1[] = "SELECT cars.* FROM cars ORDER BY model, owner DESC";
146​ QCOMPARE(sql, validSelect1);
147​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
148​ const char validDriverSelect1[] = "SELECT [cars].* FROM [cars] ORDER BY [model] COLLATE '', [owner] DESC";
149​ QCOMPARE(sql, validDriverSelect1);
150​
151​ // The same query, adding null field
152​ orderBy->clear();
153​ QVERIFY(query.orderByColumnList()->isEmpty());
154​ orderBy->appendField(nullptr);
155​ QVERIFY2(query.orderByColumnList()->isEmpty(), "Adding null fields should not affect OREDR BY");
156​ orderBy->appendField(modelField, KDbOrderByColumn::SortOrder::Ascending);
157​ orderBy->appendField(ownerField, KDbOrderByColumn::SortOrder::Descending);
158​ orderBy->appendField(nullptr);
159​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
160​ QCOMPARE(sql, validSelect1);
161​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
162​ QCOMPARE(sql, validDriverSelect1);
163​
164​ // The same query, overload
165​ orderBy->clear();
166​ QVERIFY(query.orderByColumnList()->isEmpty());
167​ QVERIFY(orderBy->appendFields(conn, &query,
168​ "model", KDbOrderByColumn::SortOrder::Ascending,
169​ "owner", KDbOrderByColumn::SortOrder::Descending));
170​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
171​ QCOMPARE(sql, validSelect1);
172​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
173​ QCOMPARE(sql, validDriverSelect1);
174​
175​ // The same query, overload
176​ orderBy->clear();
177​ QVERIFY(query.orderByColumnList()->isEmpty());
178​ QVERIFY(orderBy->appendField(conn, &query, "model", KDbOrderByColumn::SortOrder::Ascending));
179​ QVERIFY(orderBy->appendField(conn, &query, "owner", KDbOrderByColumn::SortOrder::Descending));
180​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
181​ QCOMPARE(sql, validSelect1);
182​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
183​ QCOMPARE(sql, validDriverSelect1);
184​
185​ QCOMPARE(orderBy->count(), 2);
186​ QVERIFY2(!orderBy->appendField(conn, &query, ""), "appendField for null");
187​ QCOMPARE(orderBy->count(), 2);
188​
189​ // The same query, overload
190​ orderBy->clear();
191​ QCOMPARE(orderBy->count(), 0);
192​ QVERIFY(query.orderByColumnList()->isEmpty());
193​ KDbQueryColumnInfo::Vector columns = query.fieldsExpanded(conn);
194​ KDbQueryColumnInfo *ownerColumnInfo = columns.value(1);
195​ QVERIFY(ownerColumnInfo);
196​ KDbQueryColumnInfo *modelColumnInfo = columns.value(2);
197​ QVERIFY(modelColumnInfo);
198​ orderBy->appendColumn(modelColumnInfo, KDbOrderByColumn::SortOrder::Ascending);
199​ orderBy->appendColumn(ownerColumnInfo, KDbOrderByColumn::SortOrder::Descending);
200​ QCOMPARE(orderBy->count(), 2);
201​ QVERIFY(utils.kdbBuilder()->generateSelectStatement(&sql, &query));
202​ QCOMPARE(sql, validSelect1);
203​ QVERIFY(utils.driverBuilder()->generateSelectStatement(&sql, &query));
204​ QCOMPARE(sql, validDriverSelect1);
205​}
206​
207​//! @todo Test KDbQuerySchema::setOrderByColumnList
208​//! @todo Test more KDbOrderByColumnList and KDbOrderByColumn
209​
210​void OrderByColumnTest::cleanupTestCase()
211​{
212​}