-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathselectquerybuildertest.cpp
174 lines (137 loc) · 8.21 KB
/
selectquerybuildertest.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
#define QUERYBUILDER_UNITTEST
#include "testschema.h"
#include "Sql.h"
#include "SqlSelectQueryBuilder.h"
#include <QObject>
#include <QtTest/QtTest>
#include <QSqlError>
#include "test_utils.h"
#include "testbase.h"
#define QL1S(x) QString::fromLatin1(x)
Q_DECLARE_METATYPE( SqlSelectQueryBuilder )
Q_DECLARE_METATYPE( QVector<QVariant> )
using namespace Sql;
class SelectQueryBuilderTest : public TestBase
{
Q_OBJECT
private Q_SLOTS:
void initTestCase() {
openDbTest();
createEmptyDb();
QSqlQuery query;
QVERIFY(query.exec(QLatin1String("CREATE TABLE table1 (col1 VARCHAR(128), col2 VARCHAR(128), col3 VARCHAR(128), col4 VARCHAR(128));")));
QVERIFY(query.exec(QLatin1String("CREATE TABLE table2 (col1_2 VARCHAR(128), col2_2 VARCHAR(128), col3_2 VARCHAR(128), col4_2 VARCHAR(128))")));
QVERIFY(query.exec(QLatin1String("CREATE TABLE table3 (col1_3 VARCHAR(128), col2_3 VARCHAR(128), col3_3 VARCHAR(128), col4_3 VARCHAR(128))")));
}
void testQueryBuilder_data()
{
QTest::addColumn<SqlSelectQueryBuilder>( "qb" );
QTest::addColumn<QString>( "sql" );
QTest::addColumn<QVector<QVariant> >( "bindVals" );
SqlSelectQueryBuilder qb;
qb.setTable( QL1S("table1") );
qb.addColumn( QL1S("col1") );
QTest::newRow( "1 column" ) << qb << "SELECT col1 FROM table1" << QVector<QVariant>();
qb.addColumn( QL1S( "col2" ) );
QTest::newRow( "2 columns" ) << qb << "SELECT col1, col2 FROM table1" << QVector<QVariant>();
qb.addColumn( QL1S( "col3" ), QL1S( "foo" ) );
QTest::newRow( "3 columns with 1 alias" ) << qb << "SELECT col1, col2, col3 AS \"foo\" FROM table1" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( QL1S( "table1" ) );
qb.addSortColumn( QL1S("col1") );
QTest::newRow( "sort 1 col" ) << qb << "SELECT * FROM table1 ORDER BY col1 ASC" << QVector<QVariant>();
qb.addSortColumn( QL1S("col2"), Qt::DescendingOrder );
QTest::newRow( "sort 2 cols" ) << qb << "SELECT * FROM table1 ORDER BY col1 ASC, col2 DESC" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( Workplace );
qb.whereCondition().addValueCondition( Workplace.itemorder, SqlCondition::Equals, 42 );
QTest::newRow( "single where" ) << qb << "SELECT * FROM tblWorkplace WHERE tblWorkplace.itemorder = :0" << (QVector<QVariant>() << 42);
qb.whereCondition().addColumnCondition( Workplace.contact_tel, SqlCondition::Greater, Workplace.contact_fax );
QTest::newRow( "two and where conds" ) << qb << "SELECT * FROM tblWorkplace WHERE (tblWorkplace.itemorder = :0 AND tblWorkplace.contact_tel > tblWorkplace.contact_fax)" << (QVector<QVariant>() << 42);
qb.whereCondition().setLogicOperator( SqlCondition::Or );
QTest::newRow( "two or where conds" ) << qb << "SELECT * FROM tblWorkplace WHERE (tblWorkplace.itemorder = :0 OR tblWorkplace.contact_tel > tblWorkplace.contact_fax)" << (QVector<QVariant>() << 42);
SqlCondition cond( SqlCondition::And );
cond.addValueCondition( Workplace.short_desc, SqlCondition::Is, SqlNull );
cond.addValueCondition( Workplace.description, SqlCondition::LessOrEqual, QString::fromLatin1("foo") );
qb.whereCondition().addCondition( cond );
QTest::newRow( "nested conds" ) << qb << "SELECT * FROM tblWorkplace WHERE (tblWorkplace.itemorder = :0 OR tblWorkplace.contact_tel > tblWorkplace.contact_fax OR (tblWorkplace.short_desc IS NULL AND tblWorkplace.description <= :1))" << (QVector<QVariant>() << 42 << QString::fromLatin1( "foo" ));
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( QL1S( "table1" ) );
qb.addJoin( SqlSelectQueryBuilder::InnerJoin, QL1S( "table2" ), QL1S( "col1" ), QL1S( "col2_2" ) );
QTest::newRow( "1 inner join" ) << qb << "SELECT * FROM table1 INNER JOIN table2 ON col1 = col2_2" << QVector<QVariant>();
qb.addJoin( SqlSelectQueryBuilder::InnerJoin, QL1S( "table3" ), QL1S( "col3" ), QL1S( "col4_3" ) );
QTest::newRow( "2 inner joins" ) << qb << "SELECT * FROM table1 INNER JOIN table2 ON col1 = col2_2 INNER JOIN table3 ON col3 = col4_3" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.setTable( QL1S( "table1" ) );
qb.addColumn( QL1S( "col1" ));
qb.addGroupColumn( QL1S( "table1.col1" ) );
QTest::newRow( "1 group by" ) << qb << "SELECT col1 FROM table1 GROUP BY table1.col1" << QVector<QVariant>();
qb.addColumn( QL1S( "col2" ));
qb.addGroupColumn( QL1S( "table1.col2" ) );
QTest::newRow( "2 group by" ) << qb << "SELECT col1, col2 FROM table1 GROUP BY table1.col1, table1.col2" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( QL1S( "table1" ) );
qb.whereCondition().addPlaceholderCondition( QL1S( "col1" ), SqlCondition::Equals, QL1S( ":p" ) );
QTest::newRow( "placeholder" ) << qb << "SELECT * FROM table1 WHERE col1 = :p" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( Report );
qb.whereCondition().addValueCondition( Report.txt, SqlCondition::Like, QL1S( "%foo" ) );
QTest::newRow( "LIKE condition" ) << qb << "SELECT * FROM tblReport WHERE tblReport.txt LIKE :0" << (QVector<QVariant>() << QL1S( "%foo" ));
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( Workplace );
qb.lockExclusive( Workplace );
QTest::newRow( "LIKE condition" ) << qb << "SELECT * FROM tblWorkplace FOR UPDATE OF tblWorkplace" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.addAllColumns();
qb.setTable( Workplace );
qb.tryLockExclusive( Workplace );
QTest::newRow( "LIKE condition" ) << qb << "SELECT * FROM tblWorkplace FOR UPDATE OF tblWorkplace NOWAIT" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.setDistinct(true);
qb.addColumn(QL1S("col1"));
qb.setTable(QL1S( "table1" ));
QTest::newRow( "DISTINCT" ) << qb << "SELECT DISTINCT col1 FROM table1" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.setDistinctOn(QL1S("col2"));
qb.addColumn(QL1S("col1"));
qb.setTable(QL1S( "table1" ));
QTest::newRow( "DISTINCT ON" ) << qb << "SELECT DISTINCT ON(col2) col1 FROM table1" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.setTable( QL1S("table1") );
qb.addColumn( QL1S("col1") );
SqlSelectQueryBuilder qb2;
qb2.setTable( QL1S("table2") );
qb2.addColumn( QL1S("col2_2") );
SqlSelectQueryBuilder qbCombined;
qbCombined.combineQueries(qb, qb2);
QTest::newRow( "UNION " ) << qbCombined << "SELECT col1 FROM table1 UNION SELECT col2_2 FROM table2" << QVector<QVariant>();
SqlSelectQueryBuilder qbCombinedAll;
qbCombinedAll.combineQueries(qb, qb2, SqlSelectQueryBuilder::UnionAll);
QTest::newRow( "UNION ALL" ) << qbCombinedAll << "SELECT col1 FROM table1 UNION ALL SELECT col2_2 FROM table2" << QVector<QVariant>();
qb = SqlSelectQueryBuilder();
qb.setTable(Report);
qb.addAllColumns();
qb.whereCondition().addValueCondition(Report.ts, SqlCondition::LessOrEqual, SqlNow);
qb.whereCondition().addValueCondition(Report.txt, SqlCondition::Is, SqlNull);
QTest::newRow( "server side time" ) << qb << "SELECT * FROM tblReport WHERE (tblReport.ts <= now() AND tblReport.txt IS NULL)" << QVector<QVariant>();
}
void testQueryBuilder()
{
QFETCH( SqlSelectQueryBuilder, qb );
QFETCH( QString, sql );
QFETCH( QVector<QVariant>, bindVals );
qb.query(); // trigger query assembly
QCOMPARE( qb.m_queryString, sql );
// variant comparission for custom types (here, mostly QUuid) doesn't work, so we have to do this manually...
QCOMPARE( qb.m_bindValues.size(), bindVals.size() );
QVERIFY( std::equal( qb.m_bindValues.begin(), qb.m_bindValues.end(), bindVals.begin(), deepVariantCompare ) );
}
};
QTEST_MAIN( SelectQueryBuilderTest )
#include "selectquerybuildertest.moc"