|
1 /**************************************************************************** |
|
2 ** |
|
3 ** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies). |
|
4 ** All rights reserved. |
|
5 ** Contact: Nokia Corporation (qt-info@nokia.com) |
|
6 ** |
|
7 ** This file is part of the test suite of the Qt Toolkit. |
|
8 ** |
|
9 ** $QT_BEGIN_LICENSE:LGPL$ |
|
10 ** No Commercial Usage |
|
11 ** This file contains pre-release code and may not be distributed. |
|
12 ** You may use this file in accordance with the terms and conditions |
|
13 ** contained in the Technology Preview License Agreement accompanying |
|
14 ** this package. |
|
15 ** |
|
16 ** GNU Lesser General Public License Usage |
|
17 ** Alternatively, this file may be used under the terms of the GNU Lesser |
|
18 ** General Public License version 2.1 as published by the Free Software |
|
19 ** Foundation and appearing in the file LICENSE.LGPL included in the |
|
20 ** packaging of this file. Please review the following information to |
|
21 ** ensure the GNU Lesser General Public License version 2.1 requirements |
|
22 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html. |
|
23 ** |
|
24 ** In addition, as a special exception, Nokia gives you certain additional |
|
25 ** rights. These rights are described in the Nokia Qt LGPL Exception |
|
26 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package. |
|
27 ** |
|
28 ** If you have questions regarding the use of this file, please contact |
|
29 ** Nokia at qt-info@nokia.com. |
|
30 ** |
|
31 ** |
|
32 ** |
|
33 ** |
|
34 ** |
|
35 ** |
|
36 ** |
|
37 ** |
|
38 ** $QT_END_LICENSE$ |
|
39 ** |
|
40 ****************************************************************************/ |
|
41 |
|
42 |
|
43 #include <QtTest/QtTest> |
|
44 #include <q3sqlcursor.h> |
|
45 #include <qsqlfield.h> |
|
46 #include <qsqldriver.h> |
|
47 |
|
48 |
|
49 #include "../qsqldatabase/tst_databases.h" |
|
50 |
|
51 |
|
52 //TESTED_FILES= |
|
53 |
|
54 QT_FORWARD_DECLARE_CLASS(QSqlDatabase) |
|
55 |
|
56 class tst_Q3SqlCursor : public QObject |
|
57 { |
|
58 Q_OBJECT |
|
59 |
|
60 public: |
|
61 tst_Q3SqlCursor(); |
|
62 virtual ~tst_Q3SqlCursor(); |
|
63 |
|
64 |
|
65 public slots: |
|
66 void initTestCase(); |
|
67 void cleanupTestCase(); |
|
68 void init(); |
|
69 void cleanup(); |
|
70 private slots: |
|
71 void copyConstructor_data() { generic_data(); } |
|
72 void copyConstructor(); |
|
73 |
|
74 void value_data() { generic_data(); } |
|
75 void value(); |
|
76 void primaryIndex_data() { generic_data(); } |
|
77 void primaryIndex(); |
|
78 void insert_data() { generic_data(); } |
|
79 void insert(); |
|
80 void select_data() { generic_data(); } |
|
81 void select(); |
|
82 void setFilter_data() { generic_data(); } |
|
83 void setFilter(); |
|
84 void setName_data() { generic_data(); } |
|
85 void setName(); |
|
86 |
|
87 // problem specific tests |
|
88 void unicode_data() { generic_data(); } |
|
89 void unicode(); |
|
90 void precision_data() { generic_data(); } |
|
91 void precision(); |
|
92 void insertORA_data() { generic_data("QOCI"); } |
|
93 void insertORA(); |
|
94 void batchInsert_data() { generic_data(); } |
|
95 void batchInsert(); |
|
96 void insertSpecial_data() { generic_data(); } |
|
97 void insertSpecial(); |
|
98 void updateNoPK_data() { generic_data(); } |
|
99 void updateNoPK(); |
|
100 void insertFieldNameContainsWS_data() { generic_data(); } |
|
101 void insertFieldNameContainsWS(); // For task 117996 |
|
102 |
|
103 private: |
|
104 void generic_data(const QString &engine=QString()); |
|
105 void createTestTables( QSqlDatabase db ); |
|
106 void dropTestTables( QSqlDatabase db ); |
|
107 void populateTestTables( QSqlDatabase db ); |
|
108 |
|
109 tst_Databases dbs; |
|
110 }; |
|
111 |
|
112 tst_Q3SqlCursor::tst_Q3SqlCursor() |
|
113 { |
|
114 } |
|
115 |
|
116 tst_Q3SqlCursor::~tst_Q3SqlCursor() |
|
117 { |
|
118 } |
|
119 |
|
120 void tst_Q3SqlCursor::generic_data(const QString &engine) |
|
121 { |
|
122 if ( dbs.fillTestTable(engine) == 0 ) { |
|
123 if(engine.isEmpty()) |
|
124 QSKIP( "No database drivers are available in this Qt configuration", SkipAll ); |
|
125 else |
|
126 QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit(), SkipAll ); |
|
127 } |
|
128 } |
|
129 |
|
130 void tst_Q3SqlCursor::createTestTables( QSqlDatabase db ) |
|
131 { |
|
132 if ( !db.isValid() ) |
|
133 return; |
|
134 QSqlQuery q( db ); |
|
135 |
|
136 if (tst_Databases::isSqlServer(db)) { |
|
137 QVERIFY_SQL(q, exec("SET ANSI_DEFAULTS ON")); |
|
138 QVERIFY_SQL(q, exec("SET IMPLICIT_TRANSACTIONS OFF")); |
|
139 } |
|
140 else if(tst_Databases::isPostgreSQL(db)) |
|
141 QVERIFY_SQL( q, exec("set client_min_messages='warning'")); |
|
142 |
|
143 // please never ever change this table; otherwise fix all tests ;) |
|
144 if ( tst_Databases::isMSAccess( db ) ) { |
|
145 QVERIFY_SQL(q, exec( "create table " + qTableName( "qtest" ) + " ( id int not null, t_varchar varchar(40) not null," |
|
146 "t_char char(40), t_numeric number, primary key (id, t_varchar) )" )); |
|
147 } else { |
|
148 QVERIFY_SQL(q, exec( "create table " + qTableName( "qtest" ) + " ( id int not null, t_varchar varchar(40) not null," |
|
149 "t_char char(40), t_numeric numeric(6, 3), primary key (id, t_varchar) )" )); |
|
150 } |
|
151 |
|
152 if ( tst_Databases::isSqlServer( db ) ) { |
|
153 //workaround for SQL SERVER since he can store unicode only in nvarchar fields |
|
154 QVERIFY_SQL(q, exec("create table " + qTableName("qtest_unicode") + " (id int not null, " |
|
155 "t_varchar nvarchar(80) not null, t_char nchar(80) )" )); |
|
156 } else { |
|
157 QVERIFY_SQL(q, exec("create table " + qTableName("qtest_unicode") + " (id int not null, " |
|
158 "t_varchar varchar(100) not null," "t_char char(100))" )); |
|
159 } |
|
160 |
|
161 if (tst_Databases::isMSAccess(db)) { |
|
162 QVERIFY_SQL(q, exec("create table " + qTableName("qtest_precision") + " (col1 number)")); |
|
163 } else { |
|
164 QVERIFY_SQL(q, exec("create table " + qTableName("qtest_precision") + " (col1 numeric(15, 14))")); |
|
165 } |
|
166 } |
|
167 |
|
168 void tst_Q3SqlCursor::dropTestTables( QSqlDatabase db ) |
|
169 { |
|
170 if ( !db.isValid() ) |
|
171 return; |
|
172 QStringList tableNames; |
|
173 tableNames << qTableName( "qtest" ) |
|
174 << qTableName( "qtest_unicode" ) |
|
175 << qTableName( "qtest_precision" ) |
|
176 << qTableName( "qtest_ovchar" ) |
|
177 << qTableName( "qtest_onvchar" ) |
|
178 << qTableName( "qtestPK" ); |
|
179 tst_Databases::safeDropTables( db, tableNames ); |
|
180 } |
|
181 |
|
182 void tst_Q3SqlCursor::populateTestTables( QSqlDatabase db ) |
|
183 { |
|
184 if (!db.isValid()) |
|
185 return; |
|
186 QSqlQuery q( db ); |
|
187 |
|
188 q.exec( "delete from " + qTableName( "qtest" ) ); //not fatal |
|
189 QVERIFY_SQL(q, prepare("insert into " + qTableName( "qtest" ) + " (id, t_varchar, t_char, t_numeric) values (?, ?, ?, ?)")); |
|
190 q.addBindValue(QVariantList() << 0 << 1 << 2 << 3); |
|
191 q.addBindValue(QVariantList() << "VarChar0" << "VarChar1" << "VarChar2" << "VarChar3"); |
|
192 q.addBindValue(QVariantList() << "Char0" << "Char1" << "Char2" << "Char3"); |
|
193 q.addBindValue(QVariantList() << 1.1 << 2.2 << 3.3 << 4.4); |
|
194 QVERIFY_SQL(q, execBatch()); |
|
195 } |
|
196 |
|
197 void tst_Q3SqlCursor::initTestCase() |
|
198 { |
|
199 dbs.open(); |
|
200 |
|
201 for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) { |
|
202 QSqlDatabase db = QSqlDatabase::database( (*it) ); |
|
203 CHECK_DATABASE( db ); |
|
204 |
|
205 dropTestTables( db ); //in case of leftovers |
|
206 createTestTables( db ); |
|
207 populateTestTables( db ); |
|
208 } |
|
209 } |
|
210 |
|
211 void tst_Q3SqlCursor::cleanupTestCase() |
|
212 { |
|
213 for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) { |
|
214 QSqlDatabase db = QSqlDatabase::database( (*it) ); |
|
215 CHECK_DATABASE( db ); |
|
216 dropTestTables( db ); |
|
217 } |
|
218 |
|
219 dbs.close(); |
|
220 } |
|
221 |
|
222 void tst_Q3SqlCursor::init() |
|
223 { |
|
224 } |
|
225 |
|
226 void tst_Q3SqlCursor::cleanup() |
|
227 { |
|
228 QFETCH( QString, dbName ); |
|
229 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
230 CHECK_DATABASE( db ); |
|
231 if ( QTest::currentTestFailed() ) { |
|
232 //since Oracle ODBC totally craps out on error, we init again |
|
233 db.close(); |
|
234 db.open(); |
|
235 } |
|
236 } |
|
237 |
|
238 void tst_Q3SqlCursor::copyConstructor() |
|
239 { |
|
240 QFETCH( QString, dbName ); |
|
241 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
242 CHECK_DATABASE( db ); |
|
243 |
|
244 Q3SqlCursor cur2; |
|
245 { |
|
246 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
247 QVERIFY_SQL(cur, select( cur.index( QString("id") ) )); |
|
248 cur2 = Q3SqlCursor( cur ); |
|
249 // let "cur" run out of scope... |
|
250 } |
|
251 |
|
252 QSqlRecord* rec = cur2.primeUpdate(); |
|
253 Q_ASSERT( rec ); |
|
254 QCOMPARE( (int)rec->count(), 4 ); |
|
255 |
|
256 int i = 0; |
|
257 while ( cur2.next() ) { |
|
258 QVERIFY( cur2.value("id").toInt() == i ); |
|
259 i++; |
|
260 } |
|
261 } |
|
262 |
|
263 void tst_Q3SqlCursor::value() |
|
264 { |
|
265 QFETCH( QString, dbName ); |
|
266 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
267 CHECK_DATABASE( db ); |
|
268 |
|
269 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
270 QVERIFY_SQL(cur, select( cur.index( QString("id") ) )); |
|
271 int i = 0; |
|
272 while ( cur.next() ) { |
|
273 QCOMPARE(cur.value("id").toInt(), i); |
|
274 i++; |
|
275 } |
|
276 } |
|
277 |
|
278 void tst_Q3SqlCursor::primaryIndex() |
|
279 { |
|
280 QFETCH( QString, dbName ); |
|
281 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
282 CHECK_DATABASE( db ); |
|
283 |
|
284 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
285 QSqlIndex index = cur.primaryIndex(); |
|
286 if ( tst_Databases::isMSAccess( db ) ) { |
|
287 QCOMPARE( index.fieldName(1).upper(), QString( "ID" ) ); |
|
288 QCOMPARE( index.fieldName(0).upper(), QString( "T_VARCHAR" ) ); |
|
289 } else { |
|
290 QCOMPARE( index.fieldName(0).upper(), QString( "ID" ) ); |
|
291 QCOMPARE( index.fieldName(1).upper(), QString( "T_VARCHAR" ) ); |
|
292 } |
|
293 QVERIFY(!index.isDescending(0)); |
|
294 QVERIFY(!index.isDescending(1)); |
|
295 } |
|
296 |
|
297 void tst_Q3SqlCursor::insert() |
|
298 { |
|
299 QFETCH( QString, dbName ); |
|
300 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
301 CHECK_DATABASE( db ); |
|
302 |
|
303 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
304 QSqlRecord* irec = cur.primeInsert(); |
|
305 QVERIFY( irec != 0 ); |
|
306 |
|
307 // check that primeInsert returns a valid QSqlRecord |
|
308 QCOMPARE( (int)irec->count(), 4 ); |
|
309 if ( ( irec->field( 0 ).type() != QVariant::Int ) && |
|
310 ( irec->field( 0 ).type() != QVariant::String ) && |
|
311 ( irec->field( 0 ).type() != QVariant::Double ) ) { |
|
312 QFAIL( QString( "Wrong datatype %1 for field 'ID'" |
|
313 " (expected Int or String)" ).arg( QVariant::typeToName( irec->field( 0 ).type() ) ) ); |
|
314 } |
|
315 QCOMPARE( QVariant::typeToName( irec->field( 1 ).type() ), QVariant::typeToName( QVariant::String ) ); |
|
316 QCOMPARE( QVariant::typeToName( irec->field( 2 ).type() ), QVariant::typeToName( QVariant::String ) ); |
|
317 QVERIFY((QVariant::typeToName(irec->field(3).type()) == QVariant::typeToName(QVariant::Double)) || |
|
318 (QVariant::typeToName(irec->field(3).type()) == QVariant::typeToName(QVariant::String))); |
|
319 QCOMPARE( irec->field( 0 ).name().upper(), QString( "ID" ) ); |
|
320 QCOMPARE( irec->field( 1 ).name().upper(), QString( "T_VARCHAR" ) ); |
|
321 QCOMPARE( irec->field( 2 ).name().upper(), QString( "T_CHAR" ) ); |
|
322 QCOMPARE( irec->field( 3 ).name().upper(), QString( "T_NUMERIC" ) ); |
|
323 |
|
324 irec->setValue( "id", 400 ); |
|
325 irec->setValue( "t_varchar", "SomeVarChar" ); |
|
326 irec->setValue( "t_char", "SomeChar" ); |
|
327 irec->setValue( "t_numeric", 400.400 ); |
|
328 |
|
329 QCOMPARE( cur.insert(), 1 ); |
|
330 |
|
331 // restore old test-tables |
|
332 populateTestTables( db ); |
|
333 } |
|
334 |
|
335 void tst_Q3SqlCursor::insertSpecial() |
|
336 { |
|
337 QFETCH( QString, dbName ); |
|
338 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
339 CHECK_DATABASE( db ); |
|
340 |
|
341 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
342 QSqlRecord* irec = cur.primeInsert(); |
|
343 QVERIFY( irec != 0 ); |
|
344 |
|
345 QStringList strings; |
|
346 strings << "StringWith'ATick" << "StringWith\"Doublequote" << "StringWith\\Backslash" << "StringWith~Tilde"; |
|
347 strings << "StringWith%Percent" << "StringWith_Underscore" << "StringWith[SquareBracket" << "StringWith{Brace"; |
|
348 strings << "StringWith''DoubleTick" << "StringWith\\Lot\\of\\Backslash" << "StringWith\"lot\"of\"quotes\""; |
|
349 strings << "'StartsAndEndsWithTick'" << "\"StartsAndEndsWithQuote\""; |
|
350 strings << "StringWith\nCR" << "StringWith\n\rCRLF"; |
|
351 |
|
352 int i = 800; |
|
353 |
|
354 // INSERT the strings |
|
355 QStringList::Iterator it; |
|
356 for ( it = strings.begin(); it != strings.end(); ++it ) { |
|
357 QSqlRecord* irec = cur.primeInsert(); |
|
358 QVERIFY( irec != 0 ); |
|
359 irec->setValue( "id", i ); |
|
360 irec->setValue( "t_varchar", (*it) ); |
|
361 irec->setValue( "t_char", (*it) ); |
|
362 irec->setValue( "t_numeric", (double)i ); |
|
363 ++i; |
|
364 QCOMPARE( cur.insert(), 1 ); |
|
365 } |
|
366 |
|
367 QVERIFY( cur.select( "id >= 800 and id < 900" ) ); |
|
368 |
|
369 int i2 = 800; |
|
370 while( cur.next() ) { |
|
371 QCOMPARE( cur.value( "id" ).toInt(), i2 ); |
|
372 QCOMPARE( cur.value( "t_varchar" ).toString().stripWhiteSpace(), strings.at( i2 - 800 ) ); |
|
373 QCOMPARE( cur.value( "t_char" ).toString().stripWhiteSpace(), strings.at( i2 - 800 ) ); |
|
374 QCOMPARE( cur.value( "t_numeric" ).toDouble(), (double)i2 ); |
|
375 ++i2; |
|
376 } |
|
377 QCOMPARE( i, i2 ); |
|
378 |
|
379 populateTestTables( db ); |
|
380 } |
|
381 |
|
382 void tst_Q3SqlCursor::batchInsert() |
|
383 { |
|
384 QFETCH( QString, dbName ); |
|
385 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
386 CHECK_DATABASE( db ); |
|
387 |
|
388 QSqlQuery q( db ); |
|
389 q.exec( "delete from " + qTableName( "qtest" ) ); |
|
390 |
|
391 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
392 |
|
393 int i = 0; |
|
394 for ( ; i < 100; ++i ) { |
|
395 QSqlRecord* irec = cur.primeInsert(); |
|
396 Q_ASSERT( irec ); |
|
397 irec->setValue( "id", i ); |
|
398 irec->setValue( "t_varchar", "blah" ); |
|
399 irec->setValue( "t_char", "blah" ); |
|
400 irec->setValue( "t_numeric", 1.1 ); |
|
401 if ( db.driverName().startsWith( "QSQLITE" ) ) { |
|
402 QVERIFY( cur.insert( true ) ); |
|
403 } else { |
|
404 QCOMPARE( cur.insert( true ), 1 ); |
|
405 } |
|
406 } |
|
407 |
|
408 for ( ; i < 200; ++i ) { |
|
409 QSqlRecord* irec = cur.primeInsert(); |
|
410 Q_ASSERT( irec ); |
|
411 irec->setValue( "id", i ); |
|
412 irec->setValue( "t_varchar", "blah" ); |
|
413 irec->setValue( "t_char", "blah" ); |
|
414 irec->setValue( "t_numeric", 1.1 ); |
|
415 if ( db.driverName().startsWith( "QSQLITE" ) ) { |
|
416 QVERIFY( cur.insert( false ) ); |
|
417 } else { |
|
418 QCOMPARE( cur.insert( false ), 1 ); |
|
419 } |
|
420 } |
|
421 |
|
422 i = 0; |
|
423 QVERIFY_SQL(q, exec( "select * from " + qTableName( "qtest" ) + " order by id" )); |
|
424 while ( q.next() ) { |
|
425 QCOMPARE( q.value( 0 ).toInt(), i ); |
|
426 i++; |
|
427 } |
|
428 |
|
429 QCOMPARE( i, 200 ); |
|
430 |
|
431 populateTestTables( db ); |
|
432 } |
|
433 |
|
434 static QString dumpUtf8( const QString& str ) |
|
435 { |
|
436 QString res; |
|
437 for ( int i = 0; i < (int)str.length(); ++i ) { |
|
438 res += "0x" + QString::number( str[ i ].unicode(), 16 ) + ' '; |
|
439 } |
|
440 return res; |
|
441 } |
|
442 |
|
443 void tst_Q3SqlCursor::insertORA() |
|
444 { |
|
445 QFETCH( QString, dbName ); |
|
446 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
447 CHECK_DATABASE( db ); |
|
448 |
|
449 if (tst_Databases::getOraVersion(db) < 9) |
|
450 QSKIP("Need Oracle >= 9", SkipSingle); |
|
451 |
|
452 /****** CHARSET TEST ******/ |
|
453 |
|
454 QSqlQuery q( db ); |
|
455 QVERIFY_SQL(q, exec( "create table " + qTableName( "qtest_ovchar" ) + " ( id int primary key, t_char varchar(40) )" )); |
|
456 |
|
457 static const QString val1( "blah1" ); |
|
458 |
|
459 Q3SqlCursor cur ( qTableName( "qtest_ovchar" ), true, db ); |
|
460 QSqlRecord* irec = cur.primeInsert(); |
|
461 irec->setValue( "id", 1 ); |
|
462 irec->setValue( "t_char", val1 ); |
|
463 QVERIFY( cur.insert() ); |
|
464 |
|
465 QVERIFY_SQL(cur, select()); |
|
466 QVERIFY( cur.next() ); |
|
467 if ( cur.value( "t_char" ).toString() != val1 ) |
|
468 qDebug( QString( "Wrong value for t_char: expected '%1', got '%2'" ).arg( val1 ).arg( |
|
469 cur.value( "t_char" ).toString() ) ); |
|
470 |
|
471 static const unsigned short utf8arr[] = { 0xd792,0xd79c,0xd792,0xd79c,0xd799,0x00 }; |
|
472 static const QString utf8str = QString::fromUcs2( utf8arr ); |
|
473 |
|
474 irec = cur.primeInsert(); |
|
475 irec->setValue( "id", 2 ); |
|
476 irec->setValue( "t_char", utf8str ); |
|
477 QVERIFY( cur.insert() ); |
|
478 |
|
479 QVERIFY_SQL(cur, select( "id=2" )); |
|
480 QVERIFY( cur.next() ); |
|
481 |
|
482 // until qtest knows non-fatal errors we use qDebug instead |
|
483 if ( cur.value( "t_char" ).toString() != utf8str ) |
|
484 qDebug( QString( "Wrong value for t_char: expected '%1', got '%2'" ).arg( dumpUtf8 ( utf8str ) ).arg( |
|
485 dumpUtf8( cur.value( "t_char" ).toString() ) ) ); |
|
486 |
|
487 /****** NCHARSET TEST ********/ |
|
488 |
|
489 QVERIFY_SQL(q, exec( "create table " + qTableName( "qtest_onvchar" ) + " ( id int primary key, t_nchar nvarchar2(40) )" )); |
|
490 |
|
491 Q3SqlCursor cur2 ( qTableName( "qtest_onvchar" ), true, db ); |
|
492 irec = cur2.primeInsert(); |
|
493 irec->setValue( "id", 1 ); |
|
494 irec->setValue( "t_nchar", val1 ); |
|
495 QVERIFY( cur2.insert() ); |
|
496 |
|
497 QVERIFY_SQL(cur2, select()); |
|
498 QVERIFY( cur2.next() ); |
|
499 if ( cur2.value( "t_nchar" ).toString() != val1 ) |
|
500 qDebug( QString( "Wrong value for t_nchar: expected '%1', got '%2'" ).arg( val1 ).arg( |
|
501 cur2.value( "t_nchar" ).toString() ) ); |
|
502 |
|
503 irec = cur2.primeInsert(); |
|
504 irec->setValue( "id", 2 ); |
|
505 irec->setValue( "t_nchar", utf8str ); |
|
506 QVERIFY( cur2.insert() ); |
|
507 |
|
508 QVERIFY_SQL(cur2, select( "id=2" )); |
|
509 QVERIFY( cur2.next() ); |
|
510 |
|
511 // until qtest knows non-fatal errors we use qDebug instead |
|
512 if ( cur2.value( "t_nchar" ).toString() != utf8str ) |
|
513 qDebug( QString( "Wrong value for t_nchar: expected '%1', got '%2'" ).arg( dumpUtf8( utf8str ) ).arg( |
|
514 dumpUtf8( cur2.value( "t_nchar" ).toString() ) ) ); |
|
515 |
|
516 } |
|
517 |
|
518 void tst_Q3SqlCursor::unicode() |
|
519 { |
|
520 QFETCH( QString, dbName ); |
|
521 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
522 CHECK_DATABASE( db ); |
|
523 |
|
524 static const QString utf8str = QString::fromUtf8( "ὕαλον ϕαγεῖν δύναμαι· τοῦτο οὔ με βλάπτει." ); |
|
525 if ( !db.driver()->hasFeature( QSqlDriver::Unicode ) ) { |
|
526 QSKIP( "DBMS not Unicode capable", SkipSingle ); |
|
527 } |
|
528 // ascii in the data storage, can't transliterate properly. invalid test. |
|
529 if(db.driverName().startsWith("QIBASE") && (db.databaseName() == "silence.nokia.troll.no:c:\\ibase\\testdb_ascii" || db.databaseName() == "/opt/interbase/qttest.gdb")) |
|
530 QSKIP("Can't transliterate extended unicode to ascii", SkipSingle); |
|
531 |
|
532 Q3SqlCursor cur( qTableName( "qtest_unicode" ), true, db ); |
|
533 QSqlRecord* irec = cur.primeInsert(); |
|
534 irec->setValue( 0, 500 ); |
|
535 irec->setValue( 1, utf8str ); |
|
536 irec->setValue( 2, utf8str ); |
|
537 QVERIFY_SQL(cur, insert()); |
|
538 QVERIFY_SQL(cur, select( "id=500" )); |
|
539 QVERIFY_SQL(cur, next()); |
|
540 QString res = cur.value( 1 ).asString(); |
|
541 cur.primeDelete(); |
|
542 cur.del(); |
|
543 |
|
544 if ( res != utf8str ) { |
|
545 int i; |
|
546 for ( i = 0; i < (int)res.length(); ++i ) { |
|
547 if ( res[ i ] != utf8str[ i ] ) |
|
548 break; |
|
549 } |
|
550 if(db.driverName().startsWith("QMYSQL") || db.driverName().startsWith("QDB2")) |
|
551 qWarning() << "Needs someone with more Unicode knowledge than I have to fix:" << QString( "Strings differ at position %1: orig: %2, db: %3" ).arg( i ).arg( utf8str[ i ].unicode(), 0, 16 ).arg( res[ i ].unicode(), 0, 16 ); |
|
552 else |
|
553 QFAIL( QString( "Strings differ at position %1: orig: %2, db: %3" ).arg( i ).arg( utf8str[ i ].unicode(), 0, 16 ).arg( res[ i ].unicode(), 0, 16 ) ); |
|
554 } |
|
555 if((db.driverName().startsWith("QMYSQL") || db.driverName().startsWith("QDB2")) && res != utf8str) |
|
556 QEXPECT_FAIL("", "See above message", Continue); |
|
557 QVERIFY( res == utf8str ); |
|
558 } |
|
559 |
|
560 void tst_Q3SqlCursor::precision() |
|
561 { |
|
562 static const QString precStr = QLatin1String("1.23456789012345"); |
|
563 static const double precDbl = 2.23456789012345; |
|
564 |
|
565 QFETCH( QString, dbName ); |
|
566 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
567 CHECK_DATABASE( db ); |
|
568 |
|
569 Q3SqlCursor cur( qTableName( "qtest_precision" ), true, db ); |
|
570 cur.setTrimmed( "col1", true ); |
|
571 QSqlRecord* irec = cur.primeInsert(); |
|
572 irec->setValue( 0, precStr ); |
|
573 QVERIFY( cur.insert() ); |
|
574 |
|
575 irec = cur.primeInsert(); |
|
576 irec->setValue( 0, precDbl ); |
|
577 QVERIFY( cur.insert() ); |
|
578 |
|
579 QVERIFY_SQL(cur, select()); |
|
580 QVERIFY( cur.next() ); |
|
581 QCOMPARE( cur.value( 0 ).asString(), precStr ); |
|
582 QVERIFY( cur.next() ); |
|
583 QCOMPARE( cur.value( 0 ).asDouble(), precDbl ); |
|
584 } |
|
585 |
|
586 void tst_Q3SqlCursor::setFilter() |
|
587 { |
|
588 QFETCH( QString, dbName ); |
|
589 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
590 CHECK_DATABASE( db ); |
|
591 |
|
592 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
593 cur.setFilter( "id = 2" ); |
|
594 |
|
595 QVERIFY_SQL(cur, select()); |
|
596 QVERIFY( cur.next() ); |
|
597 QCOMPARE( cur.value( "id" ).toInt(), 2 ); |
|
598 QVERIFY( !cur.next() ); |
|
599 |
|
600 QVERIFY_SQL(cur, select()); |
|
601 QVERIFY( cur.next() ); |
|
602 QCOMPARE( cur.value( "id" ).toInt(), 2 ); |
|
603 QVERIFY( !cur.next() ); |
|
604 |
|
605 QVERIFY_SQL(cur, select( "id = 3" )); |
|
606 QVERIFY( cur.next() ); |
|
607 QCOMPARE( cur.value( "id" ).toInt(), 3 ); |
|
608 QVERIFY( !cur.next() ); |
|
609 |
|
610 QVERIFY_SQL(cur, select()); |
|
611 QVERIFY( cur.next() ); |
|
612 QCOMPARE( cur.value( "id" ).toInt(), 3 ); |
|
613 QVERIFY( !cur.next() ); |
|
614 } |
|
615 |
|
616 void tst_Q3SqlCursor::select() |
|
617 { |
|
618 QFETCH( QString, dbName ); |
|
619 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
620 CHECK_DATABASE( db ); |
|
621 |
|
622 Q3SqlCursor cur( qTableName( "qtest" ), true, db ); |
|
623 QVERIFY_SQL(cur, select()); |
|
624 QVERIFY( cur.next() ); |
|
625 QVERIFY( cur.next() ); |
|
626 |
|
627 Q3SqlCursor cur2( qTableName( "qtest" ), true, db ); |
|
628 QVERIFY_SQL(cur2, select( "id = 1" )); |
|
629 QVERIFY( cur2.next() ); |
|
630 QCOMPARE( cur2.value( 0 ).toInt(), 1 ); |
|
631 |
|
632 Q3SqlCursor cur3( qTableName( "qtest" ), true, db ); |
|
633 QVERIFY_SQL(cur3, select( cur3.primaryIndex( false ) )); |
|
634 QVERIFY( cur3.next() ); |
|
635 QVERIFY( cur3.next() ); |
|
636 QCOMPARE( cur3.value( 0 ).toInt(), 1 ); |
|
637 |
|
638 Q3SqlCursor cur4( qTableName( "qtest" ), true, db ); |
|
639 QSqlIndex idx = cur4.primaryIndex( false ); |
|
640 QCOMPARE( (int)idx.count(), 2 ); |
|
641 if ( tst_Databases::isMSAccess( db ) ) { |
|
642 QCOMPARE( idx.field( 1 ).name().upper(), QString("ID") ); |
|
643 QCOMPARE( idx.field( 0 ).name().upper(), QString("T_VARCHAR") ); |
|
644 } else { |
|
645 QCOMPARE( idx.field( 0 ).name().upper(), QString("ID") ); |
|
646 QCOMPARE( idx.field( 1 ).name().upper(), QString("T_VARCHAR") ); |
|
647 } |
|
648 |
|
649 #ifdef QT_DEBUG |
|
650 // Ignore debugging message advising users of a potential pitfall. |
|
651 QTest::ignoreMessage(QtDebugMsg, "Q3SqlCursor::setValue(): This will not affect actual database values. Use primeInsert(), primeUpdate() or primeDelete()."); |
|
652 #endif |
|
653 cur4.setValue( "id", 1 ); |
|
654 #ifdef QT_DEBUG |
|
655 QTest::ignoreMessage(QtDebugMsg, "Q3SqlCursor::setValue(): This will not affect actual database values. Use primeInsert(), primeUpdate() or primeDelete()."); |
|
656 #endif |
|
657 cur4.setValue( "t_varchar", "VarChar1" ); |
|
658 |
|
659 QVERIFY_SQL(cur4, select( idx, cur4.primaryIndex( false ) )); |
|
660 QVERIFY( cur4.next() ); |
|
661 QCOMPARE( cur4.value( 0 ).toInt(), 1 ); |
|
662 } |
|
663 |
|
664 void tst_Q3SqlCursor::setName() |
|
665 { |
|
666 QFETCH( QString, dbName ); |
|
667 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
668 CHECK_DATABASE( db ); |
|
669 |
|
670 Q3SqlCursor c2( qTableName( "qtest" ), true, db ); |
|
671 QCOMPARE( c2.name(), qTableName( "qtest" ) ); |
|
672 QCOMPARE( c2.fieldName( 0 ).lower(), QString( "id" ) ); |
|
673 |
|
674 Q3SqlCursor c( QString(), true, db ); |
|
675 c.setName( qTableName( "qtest" ) ); |
|
676 QCOMPARE( c.name(), qTableName( "qtest" ) ); |
|
677 QCOMPARE( c.fieldName( 0 ).lower(), QString( "id" ) ); |
|
678 |
|
679 c.setName( qTableName( "qtest_precision" ) ); |
|
680 QCOMPARE( c.name(), qTableName( "qtest_precision" ) ); |
|
681 QCOMPARE( c.fieldName( 0 ).lower(), QString( "col1" ) ); |
|
682 } |
|
683 |
|
684 /* Database independent test */ |
|
685 void tst_Q3SqlCursor::updateNoPK() |
|
686 { |
|
687 QFETCH( QString, dbName ); |
|
688 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
689 CHECK_DATABASE( db ); |
|
690 |
|
691 QSqlQuery q(db); |
|
692 QVERIFY_SQL(q, exec("create table " + qTableName( "qtestPK" ) + " (id int, name varchar(20), num numeric)")); |
|
693 |
|
694 Q3SqlCursor cur(qTableName("qtestPK"), true, db); |
|
695 QSqlRecord* rec = cur.primeInsert(); |
|
696 Q_ASSERT(rec); |
|
697 rec->setNull(0); |
|
698 rec->setNull(1); |
|
699 rec->setNull(2); |
|
700 QVERIFY_SQL(cur, insert() == 1); |
|
701 if (!db.driver()->hasFeature(QSqlDriver::PreparedQueries)) { |
|
702 |
|
703 // Only QPSQL, QMYSQL, QODBC and QOCI drivers currently use escape identifiers for column names |
|
704 if (db.driverName().startsWith("QPSQL") || |
|
705 db.driverName().startsWith("QMYSQL") || |
|
706 db.driverName().startsWith("QODBC") || |
|
707 db.driverName().startsWith("QOCI")) { |
|
708 QString query = QString::fromLatin1("insert into " + qTableName("qtestPK") + |
|
709 " (" + db.driver()->escapeIdentifier("id", QSqlDriver::FieldName) + ',' |
|
710 + db.driver()->escapeIdentifier("name", QSqlDriver::FieldName) + ',' |
|
711 + db.driver()->escapeIdentifier("num", QSqlDriver::FieldName) + ')' |
|
712 + " values (NULL,NULL,NULL)"); |
|
713 QCOMPARE(cur.lastQuery(), query); |
|
714 } else { |
|
715 QCOMPARE(cur.lastQuery(), QString::fromLatin1("insert into " + qTableName("qtestPK") + |
|
716 " (\"id\",\"name\",\"num\") values (NULL,NULL,NULL)")); |
|
717 } |
|
718 } |
|
719 |
|
720 rec = cur.primeUpdate(); |
|
721 Q_ASSERT(rec); |
|
722 rec->setValue(0, 1); |
|
723 rec->setNull(1); |
|
724 rec->setNull(2); |
|
725 // Sqlite returns 2, don't ask why. |
|
726 QVERIFY(cur.update() != 0); |
|
727 QString expect = "update " + qTableName("qtestPK") + |
|
728 " set "+db.driver()->escapeIdentifier("id", QSqlDriver::FieldName)+" = 1 , " |
|
729 +db.driver()->escapeIdentifier("name", QSqlDriver::FieldName)+" = NULL , " |
|
730 +db.driver()->escapeIdentifier("num", QSqlDriver::FieldName)+" = NULL where " + qTableName("qtestPK") + ".id" |
|
731 " IS NULL and " + qTableName("qtestPK") + ".name IS NULL and " + |
|
732 qTableName("qtestPK") + ".num IS NULL"; |
|
733 if (!db.driver()->hasFeature(QSqlDriver::PreparedQueries)) { |
|
734 if (!db.driverName().startsWith("QSQLITE")) { |
|
735 QCOMPARE(cur.lastQuery(), expect); |
|
736 } |
|
737 } |
|
738 QVERIFY(cur.select(cur.index(QString("id")))); |
|
739 QVERIFY(cur.next()); |
|
740 QCOMPARE(cur.value("id").toInt(), 1); |
|
741 QVERIFY(cur.isNull("name")); |
|
742 QVERIFY(cur.isNull("num")); |
|
743 } |
|
744 |
|
745 // For task 117996: Q3SqlCursor::insert() should not fail even if field names |
|
746 // contain white spaces. |
|
747 void tst_Q3SqlCursor::insertFieldNameContainsWS() { |
|
748 |
|
749 QFETCH( QString, dbName ); |
|
750 QSqlDatabase db = QSqlDatabase::database( dbName ); |
|
751 CHECK_DATABASE( db ); |
|
752 |
|
753 // The bugfix (and this test) depends on QSqlDriver::escapeIdentifier(...) |
|
754 // to be implemented, which is currently only the case for the |
|
755 // QPSQL, QODBC and QOCI drivers. |
|
756 if (!db.driverName().startsWith("QPSQL") && |
|
757 !db.driverName().startsWith("QODBC") && |
|
758 !db.driverName().startsWith("QOCI")) { |
|
759 QSKIP("PSQL, QODBC or QOCI specific test", SkipSingle); |
|
760 return; |
|
761 } |
|
762 |
|
763 QString tableName = qTableName("qtestws"); |
|
764 |
|
765 QSqlQuery q(db); |
|
766 tst_Databases::safeDropTable(db, tableName); |
|
767 QString query = "CREATE TABLE %1 (id int, " |
|
768 + db.driver()->escapeIdentifier("first Name", QSqlDriver::FieldName) |
|
769 + " varchar(20), lastName varchar(20))"; |
|
770 QVERIFY_SQL(q, exec(query.arg(tableName))); |
|
771 |
|
772 Q3SqlCursor cur(tableName, true, db); |
|
773 cur.select(); |
|
774 |
|
775 QSqlRecord *r = cur.primeInsert(); |
|
776 r->setValue("id", 1); |
|
777 r->setValue("firsT NaMe", "Kong"); |
|
778 r->setValue("lastNaMe", "Harald"); |
|
779 |
|
780 QVERIFY(cur.insert() == 1); |
|
781 |
|
782 cur.select(); |
|
783 cur.next(); |
|
784 |
|
785 QVERIFY(cur.value(0) == 1); |
|
786 QCOMPARE(cur.value(1).toString(), QString("Kong")); |
|
787 QCOMPARE(cur.value(2).toString(), QString("Harald")); |
|
788 |
|
789 tst_Databases::safeDropTable(db, tableName); |
|
790 |
|
791 } |
|
792 |
|
793 QTEST_MAIN(tst_Q3SqlCursor) |
|
794 #include "tst_q3sqlcursor.moc" |