1/****************************************************************************
2**
3** Copyright (C) 2016 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the test suite of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:GPL-EXCEPT$
9** Commercial License Usage
10** Licensees holding valid commercial Qt licenses may use this file in
11** accordance with the commercial license agreement provided with the
12** Software or, alternatively, in accordance with the terms contained in
13** a written agreement between you and The Qt Company. For licensing terms
14** and conditions see https://www.qt.io/terms-conditions. For further
15** information use the contact form at https://www.qt.io/contact-us.
16**
17** GNU General Public License Usage
18** Alternatively, this file may be used under the terms of the GNU
19** General Public License version 3 as published by the Free Software
20** Foundation with exceptions as appearing in the file LICENSE.GPL3-EXCEPT
21** included in the packaging of this file. Please review the following
22** information to ensure the GNU General Public License requirements will
23** be met: https://www.gnu.org/licenses/gpl-3.0.html.
24**
25** $QT_END_LICENSE$
26**
27****************************************************************************/
28
29
30#include <QtTest/QtTest>
31#include <QtSql/QtSql>
32
33#include "../../kernel/qsqldatabase/tst_databases.h"
34
35const QString reltest1(qTableName(prefix: "reltest1", __FILE__, db: QSqlDatabase())),
36 reltest2(qTableName(prefix: "reltest2", __FILE__, db: QSqlDatabase())),
37 reltest3(qTableName(prefix: "reltest3", __FILE__, db: QSqlDatabase())),
38 reltest4(qTableName(prefix: "reltest4", __FILE__, db: QSqlDatabase())),
39 reltest5(qTableName(prefix: "reltest5", __FILE__, db: QSqlDatabase()));
40
41class tst_QSqlRelationalTableModel : public QObject
42{
43 Q_OBJECT
44
45public:
46 void recreateTestTables(QSqlDatabase);
47
48 tst_Databases dbs;
49
50public slots:
51 void initTestCase_data();
52 void initTestCase();
53 void cleanupTestCase();
54 void init();
55 void cleanup();
56
57private slots:
58 void data();
59 void setData();
60 void multipleRelation();
61 void insertRecord();
62 void setRecord();
63 void insertWithStrategies();
64 void removeColumn();
65 void filter();
66 void sort();
67 void revert();
68
69 void clearDisplayValuesCache();
70 void insertRecordDuplicateFieldNames();
71 void invalidData();
72 void relationModel();
73 void casing();
74 void escapedRelations();
75 void escapedTableName();
76 void whiteSpaceInIdentifiers();
77 void psqlSchemaTest();
78 void selectAfterUpdate();
79 void relationOnFirstColumn();
80 void setRelation();
81
82private:
83 void dropTestTables( QSqlDatabase db );
84};
85
86
87void tst_QSqlRelationalTableModel::initTestCase_data()
88{
89 QVERIFY(dbs.open());
90 if (dbs.fillTestTable() == 0)
91 QSKIP("No database drivers are available in this Qt configuration");
92}
93
94void tst_QSqlRelationalTableModel::recreateTestTables(QSqlDatabase db)
95{
96 dropTestTables(db);
97
98 QSqlQuery q(db);
99 QVERIFY_SQL( q, exec("create table " + reltest1 +
100 " (id int not null primary key, name varchar(20), title_key int, another_title_key int)"));
101 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(1, 'harry', 1, 2)"));
102 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(2, 'trond', 2, 1)"));
103 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(3, 'vohi', 1, 2)"));
104 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(4, 'boris', 2, 2)"));
105 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(5, 'nat', NULL, NULL)"));
106 QVERIFY_SQL( q, exec("insert into " + reltest1 + " values(6, 'ale', NULL, 2)"));
107
108 QVERIFY_SQL( q, exec("create table " + reltest2 + " (id int not null primary key, title varchar(20))"));
109 QVERIFY_SQL( q, exec("insert into " + reltest2 + " values(1, 'herr')"));
110 QVERIFY_SQL( q, exec("insert into " + reltest2 + " values(2, 'mister')"));
111
112 QVERIFY_SQL( q, exec("create table " + reltest3 + " (id int not null primary key, name varchar(20), city_key int)"));
113 QVERIFY_SQL( q, exec("insert into " + reltest3 + " values(1, 'Gustav', 1)"));
114 QVERIFY_SQL( q, exec("insert into " + reltest3 + " values(2, 'Heidi', 2)"));
115
116 QVERIFY_SQL( q, exec("create table " + reltest4 + " (id int not null primary key, name varchar(20))"));
117 QVERIFY_SQL( q, exec("insert into " + reltest4 + " values(1, 'Oslo')"));
118 QVERIFY_SQL( q, exec("insert into " + reltest4 + " values(2, 'Trondheim')"));
119
120 QVERIFY_SQL( q, exec("create table " + reltest5 + " (title varchar(20) not null primary key, abbrev varchar(20))"));
121 QVERIFY_SQL( q, exec("insert into " + reltest5 + " values('herr', 'Hr')"));
122 QVERIFY_SQL( q, exec("insert into " + reltest5 + " values('mister', 'Mr')"));
123
124 if (testWhiteSpaceNames(name: db.driverName())) {
125 const auto reltest6 = qTableName(prefix: "rel test6", __FILE__, db);
126 QVERIFY_SQL( q, exec("create table " + reltest6 + " (id int not null primary key, " + db.driver()->escapeIdentifier("city key", QSqlDriver::FieldName) +
127 " int, " + db.driver()->escapeIdentifier("extra field", QSqlDriver::FieldName) + " int)"));
128 QVERIFY_SQL( q, exec("insert into " + reltest6 + " values(1, 1,9)"));
129 QVERIFY_SQL( q, exec("insert into " + reltest6 + " values(2, 2,8)"));
130
131 const auto reltest7 = qTableName(prefix: "rel test7", __FILE__, db);
132 QVERIFY_SQL( q, exec("create table " + reltest7 + " (" + db.driver()->escapeIdentifier("city id", QSqlDriver::TableName) + " int not null primary key, " + db.driver()->escapeIdentifier("city name", QSqlDriver::FieldName) + " varchar(20))"));
133 QVERIFY_SQL( q, exec("insert into " + reltest7 + " values(1, 'New York')"));
134 QVERIFY_SQL( q, exec("insert into " + reltest7 + " values(2, 'Washington')"));
135 }
136}
137
138void tst_QSqlRelationalTableModel::initTestCase()
139{
140 foreach (const QString &dbname, dbs.dbNames) {
141 QSqlDatabase db=QSqlDatabase::database(connectionName: dbname);
142 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
143 if (dbType == QSqlDriver::Interbase) {
144 db.exec(query: "SET DIALECT 3");
145 } else if (dbType == QSqlDriver::MSSqlServer) {
146 db.exec(query: "SET ANSI_DEFAULTS ON");
147 db.exec(query: "SET IMPLICIT_TRANSACTIONS OFF");
148 } else if (dbType == QSqlDriver::PostgreSQL) {
149 db.exec(query: "set client_min_messages='warning'");
150 }
151 recreateTestTables(db);
152 }
153}
154
155void tst_QSqlRelationalTableModel::cleanupTestCase()
156{
157 foreach (const QString &dbName, dbs.dbNames) {
158 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
159 CHECK_DATABASE( db );
160 dropTestTables( db: QSqlDatabase::database(connectionName: dbName) );
161 }
162 dbs.close();
163}
164
165void tst_QSqlRelationalTableModel::dropTestTables( QSqlDatabase db )
166{
167 QStringList tableNames;
168 tableNames << reltest1
169 << reltest2
170 << reltest3
171 << reltest4
172 << reltest5
173 << qTableName(prefix: "rel test6", __FILE__, db)
174 << qTableName(prefix: "rel test7", __FILE__, db)
175 << qTableName(prefix: "CASETEST1", db)
176 << qTableName(prefix: "casetest1", db);
177 tst_Databases::safeDropTables( db, tableNames );
178
179 db.exec(query: "DROP SCHEMA " + qTableName(prefix: "QTBUG_5373", __FILE__, db) + " CASCADE");
180 db.exec(query: "DROP SCHEMA " + qTableName(prefix: "QTBUG_5373_s2", __FILE__, db) + " CASCADE");
181}
182
183void tst_QSqlRelationalTableModel::init()
184{
185}
186
187void tst_QSqlRelationalTableModel::cleanup()
188{
189}
190
191void tst_QSqlRelationalTableModel::data()
192{
193 QFETCH_GLOBAL(QString, dbName);
194 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
195 CHECK_DATABASE(db);
196
197 QSqlRelationalTableModel model(0, db);
198
199 model.setTable(reltest1);
200 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
201 QVERIFY_SQL(model, select());
202
203 QCOMPARE(model.columnCount(), 4);
204 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
205 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
206 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
207
208 //try a non-existent index
209 QVERIFY2(model.data(model.index(0,4)).isValid() == false,"Invalid index returned valid QVariant");
210
211 // check row with null relation: they are reported only in LeftJoin mode
212 QCOMPARE(model.rowCount(), 4);
213
214 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
215 QVERIFY_SQL(model, select());
216
217 QCOMPARE(model.data(model.index(4, 0)).toInt(), 5);
218 QCOMPARE(model.data(model.index(4, 1)).toString(), QString("nat"));
219 QVERIFY2(model.data(model.index(4, 2)).isValid() == true, "NULL relation reported with invalid QVariant");
220
221 //check data retrieval when relational key is a non-integer type
222 //in this case a string
223 QSqlRelationalTableModel model2(0,db);
224 model2.setTable(reltest2);
225 model2.setRelation(column: 1, relation: QSqlRelation(reltest5,"title","abbrev"));
226 QVERIFY_SQL(model2, select());
227
228 QCOMPARE(model2.data(model2.index(0, 1)).toString(), QString("Hr"));
229 QCOMPARE(model2.data(model2.index(1, 1)).toString(), QString("Mr"));
230}
231
232void tst_QSqlRelationalTableModel::setData()
233{
234 QFETCH_GLOBAL(QString, dbName);
235 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
236 CHECK_DATABASE(db);
237 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
238
239 // set the values using OnRowChange Strategy
240 {
241 QSqlRelationalTableModel model(0, db);
242
243 model.setTable(reltest1);
244 model.setSort(column: 0, order: Qt::AscendingOrder);
245 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
246 QVERIFY_SQL(model, select());
247
248 QVERIFY(model.setData(model.index(0, 1), QString("harry2")));
249 QVERIFY(model.setData(model.index(0, 2), 2));
250
251 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
252 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
253
254 model.submit();
255
256 QVERIFY(model.setData(model.index(3,1), QString("boris2")));
257 QVERIFY(model.setData(model.index(3, 2), 1));
258
259 QCOMPARE(model.data(model.index(3,1)).toString(), QString("boris2"));
260 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
261
262 model.submit();
263 }
264 { //verify values
265 QSqlRelationalTableModel model(0, db);
266 model.setTable(reltest1);
267 model.setSort(column: 0, order: Qt::AscendingOrder);
268 QVERIFY_SQL(model, select());
269
270 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
271 QCOMPARE(model.data(model.index(0, 2)).toInt(), 2);
272 QCOMPARE(model.data(model.index(3, 1)).toString(), QString("boris2"));
273 QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);
274
275 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
276 QVERIFY_SQL(model, select());
277 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
278 QCOMPARE(model.data(model.index(3,2)).toString(), QString("herr"));
279
280 }
281
282 //set the values using OnFieldChange strategy
283 {
284 QSqlRelationalTableModel model(0, db);
285 model.setTable(reltest1);
286 model.setEditStrategy(QSqlTableModel::OnFieldChange);
287 model.setSort(column: 0, order: Qt::AscendingOrder);
288 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
289 QVERIFY_SQL(model, select());
290
291 QVERIFY(model.setData(model.index(1,1), QString("trond2")));
292 QVERIFY(model.setData(model.index(2,2), 2));
293
294 QCOMPARE(model.data(model.index(1,1)).toString(), QString("trond2"));
295 QCOMPARE(model.data(model.index(2,2)).toString(), QString("mister"));
296 }
297 { //verify values
298 QSqlRelationalTableModel model(0, db);
299 model.setTable(reltest1);
300 model.setSort(column: 0, order: Qt::AscendingOrder);
301 QVERIFY_SQL(model, select());
302
303 QCOMPARE(model.data(model.index(1, 1)).toString(), QString("trond2"));
304 QCOMPARE(model.data(model.index(2, 2)).toInt(), 2);
305
306 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
307 QVERIFY_SQL(model, select());
308 QCOMPARE(model.data(model.index(2, 2)).toString(), QString("mister"));
309 }
310
311 //set values using OnManualSubmit strategy
312 {
313 QSqlRelationalTableModel model(0, db);
314
315 model.setTable(reltest1);
316 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
317
318 //sybase doesn't allow tables with the same alias used twice as col names
319 //so don't set up an identical relation when using the tds driver
320 if (dbType != QSqlDriver::Sybase)
321 model.setRelation(column: 3, relation: QSqlRelation(reltest2, "id", "title"));
322
323 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
324 model.setSort(column: 0, order: Qt::AscendingOrder);
325 QVERIFY_SQL(model, select());
326
327 QVERIFY(model.setData(model.index(2, 1), QString("vohi2")));
328 QVERIFY(model.setData(model.index(3, 2), 1));
329 QVERIFY(model.setData(model.index(0, 3), 1));
330
331 QCOMPARE(model.data(model.index(2, 1)).toString(), QString("vohi2"));
332 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
333 if (dbType != QSqlDriver::Sybase)
334 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));
335 else
336 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
337
338 QVERIFY_SQL(model, submitAll());
339 }
340 { //verify values
341 QSqlRelationalTableModel model(0, db);
342 model.setTable(reltest1);
343 model.setSort(column: 0, order: Qt::AscendingOrder);
344 QVERIFY_SQL(model, select());
345
346 QCOMPARE(model.data(model.index(2, 1)).toString(), QString("vohi2"));
347 QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);
348 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
349
350 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
351 if (dbType != QSqlDriver::Sybase)
352 model.setRelation(column: 3, relation: QSqlRelation(reltest2, "id", "title"));
353 QVERIFY_SQL(model, select());
354 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
355
356 if (dbType != QSqlDriver::Sybase)
357 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));
358 else
359 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
360 }
361
362 //check setting of data when the relational key is a non-integer type
363 //in this case a string.
364 {
365 QSqlRelationalTableModel model(0, db);
366
367 model.setTable(reltest2);
368 model.setRelation(column: 1, relation: QSqlRelation(reltest5, "title", "abbrev"));
369 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
370 QVERIFY_SQL(model, select());
371
372 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Hr"));
373 QVERIFY(model.setData(model.index(0,1), QString("mister")));
374 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));
375 QVERIFY_SQL(model, submitAll());
376
377 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));
378 }
379
380 // Redo same tests, with a LeftJoin
381 {
382 QSqlRelationalTableModel model(0, db);
383
384 model.setTable(reltest2);
385 model.setRelation(column: 1, relation: QSqlRelation(reltest5, "title", "abbrev"));
386 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
387 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
388 model.setSort(column: 0, order: Qt::AscendingOrder);
389 QVERIFY_SQL(model, select());
390
391 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));
392 QVERIFY(model.setData(model.index(0,1), QString("herr")));
393 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Hr"));
394 QVERIFY_SQL(model, submitAll());
395
396 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Hr"));
397 }
398
399}
400
401void tst_QSqlRelationalTableModel::multipleRelation()
402{
403 QFETCH_GLOBAL(QString, dbName);
404 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
405 CHECK_DATABASE(db);
406 recreateTestTables(db);
407
408 QSqlRelationalTableModel model(0, db);
409
410 model.setTable(reltest1);
411 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
412 model.setRelation(column: 3, relation: QSqlRelation(reltest4, "id", "name"));
413 model.setSort(column: 0, order: Qt::AscendingOrder);
414 QVERIFY_SQL(model, select());
415
416 QCOMPARE(model.data(model.index(2, 0)).toInt(), 3);
417
418 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
419 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
420 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
421 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("Trondheim"));
422
423 // Redo same test in the LeftJoin mode
424 model.setTable(reltest1);
425 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
426 model.setRelation(column: 3, relation: QSqlRelation(reltest4, "id", "name"));
427 model.setSort(column: 0, order: Qt::AscendingOrder);
428 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
429 QVERIFY_SQL(model, select());
430
431 QCOMPARE(model.data(model.index(2, 0)).toInt(), 3);
432
433 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
434 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
435 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
436 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("Trondheim"));
437}
438
439void tst_QSqlRelationalTableModel::insertRecord()
440{
441 QFETCH_GLOBAL(QString, dbName);
442 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
443 CHECK_DATABASE(db);
444
445 QSqlRelationalTableModel model(0, db);
446
447 model.setTable(reltest1);
448 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
449 model.setSort(column: 0, order: Qt::AscendingOrder);
450 QVERIFY_SQL(model, select());
451
452 QSqlRecord rec;
453 QSqlField f1("id", QVariant::Int);
454 QSqlField f2("name", QVariant::String);
455 QSqlField f3("title_key", QVariant::Int);
456 QSqlField f4("another_title_key", QVariant::Int);
457
458 f1.setValue(7);
459 f2.setValue("test");
460 f3.setValue(1);
461 f4.setValue(2);
462
463 f1.setGenerated(true);
464 f2.setGenerated(true);
465 f3.setGenerated(true);
466 f4.setGenerated(true);
467
468 rec.append(field: f1);
469 rec.append(field: f2);
470 rec.append(field: f3);
471 rec.append(field: f4);
472
473 QVERIFY_SQL(model, insertRecord(-1, rec));
474
475 QCOMPARE(model.data(model.index(4, 0)).toInt(), 7);
476 QCOMPARE(model.data(model.index(4, 1)).toString(), QString("test"));
477 QCOMPARE(model.data(model.index(4, 2)).toString(), QString("herr"));
478
479 // In LeftJoin mode, two additional rows are fetched
480 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
481 QVERIFY_SQL(model, select());
482
483 QCOMPARE(model.data(model.index(6, 0)).toInt(), 7);
484 QCOMPARE(model.data(model.index(6, 1)).toString(), QString("test"));
485 QCOMPARE(model.data(model.index(6, 2)).toString(), QString("herr"));
486}
487
488void tst_QSqlRelationalTableModel::setRecord()
489{
490 QFETCH_GLOBAL(QString, dbName);
491 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
492 CHECK_DATABASE(db);
493 recreateTestTables(db);
494
495 QSqlRelationalTableModel model(0, db);
496
497 model.setTable(reltest1);
498 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
499 model.setSort(column: 0, order: Qt::AscendingOrder);
500 QVERIFY_SQL(model, select());
501
502 QSqlRecord rec;
503 QSqlField f1("id", QVariant::Int);
504 QSqlField f2("name", QVariant::String);
505 QSqlField f3("title_key", QVariant::Int);
506 QSqlField f4("another_title_key", QVariant::Int);
507
508 f1.setValue(7);
509 f2.setValue("tester");
510 f3.setValue(1);
511 f4.setValue(2);
512
513 f1.setGenerated(true);
514 f2.setGenerated(true);
515 f3.setGenerated(true);
516 f4.setGenerated(true);
517
518 rec.append(field: f1);
519 rec.append(field: f2);
520 rec.append(field: f3);
521 rec.append(field: f4);
522
523 QCOMPARE(model.data(model.index(1, 0)).toInt(), 2);
524 QCOMPARE(model.data(model.index(1, 1)).toString(), QString("trond"));
525 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("mister"));
526
527 QVERIFY_SQL(model, setRecord(1, rec));
528
529 QCOMPARE(model.data(model.index(1, 0)).toInt(), 7);
530 QCOMPARE(model.data(model.index(1, 1)).toString(), QString("tester"));
531 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("herr"));
532
533 model.setSort(column: 0, order: Qt::AscendingOrder);
534 QVERIFY_SQL(model, submit());
535
536 if (model.editStrategy() != QSqlTableModel::OnManualSubmit) {
537 QCOMPARE(model.data(model.index(1, 0)).toInt(), 7);
538 QCOMPARE(model.data(model.index(1, 1)).toString(), QString("tester"));
539 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("herr"));
540 QVERIFY_SQL(model, select());
541 }
542
543 QCOMPARE(model.data(model.index(3, 0)).toInt(), 7);
544 QCOMPARE(model.data(model.index(3, 1)).toString(), QString("tester"));
545 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
546
547}
548
549void tst_QSqlRelationalTableModel::insertWithStrategies()
550{
551 QFETCH_GLOBAL(QString, dbName);
552 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
553 CHECK_DATABASE(db);
554 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
555
556 QSqlRelationalTableModel model(0, db);
557
558 model.setTable(reltest1);
559 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
560 model.setSort(column: 0, order: Qt::AscendingOrder);
561
562 if (dbType != QSqlDriver::Sybase)
563 model.setRelation(column: 3, relation: QSqlRelation(reltest2, "id", "title"));
564 QVERIFY_SQL(model, select());
565
566 QCOMPARE(model.data(model.index(0,0)).toInt(), 1);
567 QCOMPARE(model.data(model.index(0,1)).toString(), QString("harry"));
568 QCOMPARE(model.data(model.index(0,2)).toString(), QString("herr"));
569 if (dbType != QSqlDriver::Sybase)
570 QCOMPARE(model.data(model.index(0,3)).toString(), QString("mister"));
571 else
572 QCOMPARE(model.data(model.index(0,3)).toInt(), 2);
573
574 model.insertRows(row: 0, count: 1);
575 model.setData(item: model.index(row: 0, column: 0), value: 1011);
576 model.setData(item: model.index(row: 0, column: 1), value: "test");
577 model.setData(item: model.index(row: 0, column: 2), value: 2);
578 model.setData(item: model.index(row: 0, column: 3), value: 1);
579
580 QCOMPARE(model.data(model.index(0,0)).toInt(), 1011);
581 QCOMPARE(model.data(model.index(0,1)).toString(), QString("test"));
582 QCOMPARE(model.data(model.index(0,2)).toString(), QString("mister"));
583 if (dbType != QSqlDriver::Sybase)
584 QCOMPARE(model.data(model.index(0,3)).toString(), QString("herr"));
585 else
586 QCOMPARE(model.data(model.index(0,3)).toInt(), 1);
587
588 QCOMPARE(model.data(model.index(1,0)).toInt(), 1);
589 QCOMPARE(model.data(model.index(1,1)).toString(), QString("harry"));
590 QCOMPARE(model.data(model.index(1,2)).toString(), QString("herr"));
591 if (dbType != QSqlDriver::Sybase)
592 QCOMPARE(model.data(model.index(1,3)).toString(), QString("mister"));
593 else
594 QCOMPARE(model.data(model.index(1,3)).toInt(), 2);
595
596
597
598 QVERIFY_SQL(model, submitAll());
599
600 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
601 // The changes were submitted, but there was no automatic select to resort
602 QVERIFY_SQL(model, select());
603
604 QCOMPARE(model.data(model.index(0,0)).toInt(), 1);
605 QCOMPARE(model.data(model.index(0,1)).toString(), QString("harry"));
606 QCOMPARE(model.data(model.index(0,2)).toString(), QString("herr"));
607
608 if (dbType != QSqlDriver::Sybase) {
609 QCOMPARE(model.data(model.index(0,3)).toString(), QString("mister"));
610 model.setData(item: model.index(row: 0,column: 3),value: 1);
611 QCOMPARE(model.data(model.index(0,3)).toString(), QString("herr"));
612 } else {
613 QCOMPARE(model.data(model.index(0,3)).toInt(), 2);
614 model.setData(item: model.index(row: 0,column: 3),value: 1);
615 QCOMPARE(model.data(model.index(0,3)).toInt(), 1);
616 }
617
618 model.insertRows(row: 0, count: 2);
619 model.setData(item: model.index(row: 0, column: 0), value: 1012);
620 model.setData(item: model.index(row: 0, column: 1), value: "george");
621 model.setData(item: model.index(row: 0, column: 2), value: 2);
622 model.setData(item: model.index(row: 0, column: 3), value: 2);
623
624 model.setData(item: model.index(row: 1, column: 0), value: 1013);
625 model.setData(item: model.index(row: 1, column: 1), value: "kramer");
626 model.setData(item: model.index(row: 1, column: 2), value: 2);
627 model.setData(item: model.index(row: 1, column: 3), value: 1);
628
629 QCOMPARE(model.data(model.index(0,0)).toInt(),1012);
630 QCOMPARE(model.data(model.index(0,1)).toString(), QString("george"));
631 QCOMPARE(model.data(model.index(0,2)).toString(), QString("mister"));
632 if (dbType != QSqlDriver::Sybase)
633 QCOMPARE(model.data(model.index(0,3)).toString(), QString("mister"));
634 else
635 QCOMPARE(model.data(model.index(0,3)).toInt(), 2);
636
637
638 QCOMPARE(model.data(model.index(1,0)).toInt(),1013);
639 QCOMPARE(model.data(model.index(1,1)).toString(), QString("kramer"));
640 QCOMPARE(model.data(model.index(1,2)).toString(), QString("mister"));
641 if (dbType != QSqlDriver::Sybase)
642 QCOMPARE(model.data(model.index(1,3)).toString(), QString("herr"));
643 else
644 QCOMPARE(model.data(model.index(1,3)).toInt(), 1);
645
646 QCOMPARE(model.data(model.index(2,0)).toInt(), 1);
647 QCOMPARE(model.data(model.index(2,1)).toString(), QString("harry"));
648 QCOMPARE(model.data(model.index(2,2)).toString(), QString("herr"));
649 if (dbType != QSqlDriver::Sybase)
650 QCOMPARE(model.data(model.index(2,3)).toString(), QString("herr"));
651 else
652 QCOMPARE(model.data(model.index(2,3)).toInt(), 1);
653
654 QVERIFY_SQL(model, submitAll());
655}
656
657void tst_QSqlRelationalTableModel::removeColumn()
658{
659 QFETCH_GLOBAL(QString, dbName);
660 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
661 CHECK_DATABASE(db);
662 recreateTestTables(db);
663
664 QSqlRelationalTableModel model(0, db);
665
666 model.setTable(reltest1);
667 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
668 QVERIFY_SQL(model, select());
669
670 QVERIFY_SQL(model, removeColumn(3));
671 QVERIFY_SQL(model, select());
672
673 QCOMPARE(model.columnCount(), 3);
674
675 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
676 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
677 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
678 QCOMPARE(model.data(model.index(0, 3)), QVariant());
679
680 // try removing more than one column
681 QVERIFY_SQL(model, removeColumns(1, 2));
682 QCOMPARE(model.columnCount(), 1);
683 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
684 QCOMPARE(model.data(model.index(0, 1)), QVariant());
685
686 // try in LeftJoin mode the same tests
687 CHECK_DATABASE(db);
688 recreateTestTables(db);
689
690 QSqlRelationalTableModel lmodel(0, db);
691
692 lmodel.setTable(reltest1);
693 lmodel.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
694 lmodel.setJoinMode(QSqlRelationalTableModel::LeftJoin);
695 QVERIFY_SQL(lmodel, select());
696
697 QVERIFY_SQL(lmodel, removeColumn(3));
698 QVERIFY_SQL(lmodel, select());
699
700 QCOMPARE(lmodel.columnCount(), 3);
701
702 QCOMPARE(lmodel.data(lmodel.index(0, 0)).toInt(), 1);
703 QCOMPARE(lmodel.data(lmodel.index(0, 1)).toString(), QString("harry"));
704 QCOMPARE(lmodel.data(lmodel.index(0, 2)).toString(), QString("herr"));
705 QCOMPARE(lmodel.data(lmodel.index(0, 3)), QVariant());
706
707 // try removing more than one column
708 QVERIFY_SQL(lmodel, removeColumns(1, 2));
709 QCOMPARE(lmodel.columnCount(), 1);
710 QCOMPARE(lmodel.data(lmodel.index(0, 0)).toInt(), 1);
711 QCOMPARE(lmodel.data(lmodel.index(0, 1)), QVariant());
712}
713
714void tst_QSqlRelationalTableModel::filter()
715{
716 QFETCH_GLOBAL(QString, dbName);
717 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
718 CHECK_DATABASE(db);
719 recreateTestTables(db);
720
721 QSqlRelationalTableModel model(0, db);
722
723 model.setTable(reltest1);
724 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
725 model.setFilter("title = 'herr'");
726
727 QVERIFY_SQL(model, select());
728 QCOMPARE(model.rowCount(), 2);
729 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
730 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("herr"));
731
732 // Redo same filter test in LeftJoin mode
733 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
734 QVERIFY_SQL(model,select());
735
736 QCOMPARE(model.rowCount(), 2);
737 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
738 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("herr"));
739}
740
741void tst_QSqlRelationalTableModel::sort()
742{
743 QFETCH_GLOBAL(QString, dbName);
744 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
745 CHECK_DATABASE(db);
746 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
747
748 QSqlRelationalTableModel model(0, db);
749
750 model.setTable(reltest1);
751 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
752 if (dbType != QSqlDriver::Sybase)
753 model.setRelation(column: 3, relation: QSqlRelation(reltest2, "id", "title"));
754
755 model.setSort(column: 2, order: Qt::DescendingOrder);
756 QVERIFY_SQL(model, select());
757
758 QCOMPARE(model.rowCount(), 4);
759 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
760 QCOMPARE(model.data(model.index(1, 2)).toString(), QString("mister"));
761 QCOMPARE(model.data(model.index(2, 2)).toString(), QString("herr"));
762 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
763
764
765 model.setSort(column: 3, order: Qt::AscendingOrder);
766 QVERIFY_SQL(model, select());
767
768 if (dbType != QSqlDriver::Sybase) {
769 QCOMPARE(model.rowCount(), 4);
770 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));
771 QCOMPARE(model.data(model.index(1, 3)).toString(), QString("mister"));
772 QCOMPARE(model.data(model.index(2, 3)).toString(), QString("mister"));
773 QCOMPARE(model.data(model.index(3, 3)).toString(), QString("mister"));
774 } else {
775 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
776 QCOMPARE(model.data(model.index(1, 3)).toInt(), 2);
777 QCOMPARE(model.data(model.index(2, 3)).toInt(), 2);
778 QCOMPARE(model.data(model.index(3, 3)).toInt(), 2);
779 }
780
781 // redo same test in LeftJoin mode
782 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
783 model.setSort(column: 2, order: Qt::DescendingOrder);
784 QVERIFY_SQL(model, select());
785
786 QCOMPARE(model.rowCount(), 6);
787
788 QStringList stringsInDatabaseOrder;
789 // PostgreSQL puts the null ones (from the table with the original value) first in descending order
790 // which translate to empty strings in the related table
791 if (dbType == QSqlDriver::PostgreSQL)
792 stringsInDatabaseOrder << "" << "" << "mister" << "mister" << "herr" << "herr";
793 else
794 stringsInDatabaseOrder << "mister" << "mister" << "herr" << "herr" << "" << "";
795 for (int i = 0; i < 6; ++i)
796 QCOMPARE(model.data(model.index(i, 2)).toString(), stringsInDatabaseOrder.at(i));
797
798 model.setSort(column: 3, order: Qt::AscendingOrder);
799 QVERIFY_SQL(model, select());
800
801 // PostgreSQL puts the null ones (from the table with the original value) first in descending order
802 // which translate to empty strings in the related table
803 stringsInDatabaseOrder.clear();
804 if (dbType == QSqlDriver::PostgreSQL)
805 stringsInDatabaseOrder << "herr" << "mister" << "mister" << "mister" << "mister" << "";
806 else if (dbType != QSqlDriver::Sybase)
807 stringsInDatabaseOrder << "" << "herr" << "mister" << "mister" << "mister" << "mister";
808
809 if (dbType != QSqlDriver::Sybase) {
810 QCOMPARE(model.rowCount(), 6);
811 for (int i = 0; i < 6; ++i)
812 QCOMPARE(model.data(model.index(i, 3)).toString(), stringsInDatabaseOrder.at(i));
813 } else {
814 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
815 QCOMPARE(model.data(model.index(1, 3)).toInt(), 2);
816 QCOMPARE(model.data(model.index(2, 3)).toInt(), 2);
817 QCOMPARE(model.data(model.index(3, 3)).toInt(), 2);
818 }
819}
820
821static void testRevert(QSqlRelationalTableModel &model)
822{
823 /* revert single row */
824 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
825 QVERIFY(model.setData(model.index(0, 2), 2, Qt::EditRole));
826
827 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
828 model.revertRow(row: 0);
829 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
830
831 /* revert all */
832 QVERIFY(model.setData(model.index(0, 2), 2, Qt::EditRole));
833
834 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
835 model.revertAll();
836 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
837
838 // the following only works for OnManualSubmit
839 if (model.editStrategy() != QSqlTableModel::OnManualSubmit)
840 return;
841
842 /* revert inserted rows */
843 int initialRowCount = model.rowCount();
844 QVERIFY(model.insertRows(4, 4));
845 QVERIFY(model.rowCount() == (initialRowCount + 4));
846
847 /* make sure the new rows are initialized to nothing */
848 QVERIFY(model.data(model.index(4, 2)).toString().isEmpty());
849 QVERIFY(model.data(model.index(5, 2)).toString().isEmpty());
850 QVERIFY(model.data(model.index(6, 2)).toString().isEmpty());
851 QVERIFY(model.data(model.index(7, 2)).toString().isEmpty());
852
853 /* Set some values */
854 QVERIFY(model.setData(model.index(4, 0), 42, Qt::EditRole));
855 QVERIFY(model.setData(model.index(5, 0), 43, Qt::EditRole));
856 QVERIFY(model.setData(model.index(6, 0), 44, Qt::EditRole));
857 QVERIFY(model.setData(model.index(7, 0), 45, Qt::EditRole));
858
859 QVERIFY(model.setData(model.index(4, 2), 2, Qt::EditRole));
860 QVERIFY(model.setData(model.index(5, 2), 2, Qt::EditRole));
861 QVERIFY(model.setData(model.index(6, 2), 1, Qt::EditRole));
862 QVERIFY(model.setData(model.index(7, 2), 2, Qt::EditRole));
863
864 /* Now revert the newly inserted rows */
865 model.revertAll();
866 QCOMPARE(model.rowCount(), initialRowCount);
867
868 /* Insert rows again */
869 QVERIFY(model.insertRows(4, 4));
870
871 /* make sure the new rows are initialized to nothing */
872 QVERIFY(model.data(model.index(4, 2)).toString().isEmpty());
873 QVERIFY(model.data(model.index(5, 2)).toString().isEmpty());
874 QVERIFY(model.data(model.index(6, 2)).toString().isEmpty());
875 QVERIFY(model.data(model.index(7, 2)).toString().isEmpty());
876}
877
878void tst_QSqlRelationalTableModel::revert()
879{
880 QFETCH_GLOBAL(QString, dbName);
881 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
882 CHECK_DATABASE(db);
883
884 QSqlRelationalTableModel model(0, db);
885
886 model.setTable(reltest1);
887 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
888 model.setRelation(column: 3, relation: QSqlRelation(reltest4, "id", "name"));
889
890 model.setSort(column: 0, order: Qt::AscendingOrder);
891
892 QVERIFY_SQL(model, select());
893 QCOMPARE(model.data(model.index(0, 0)).toString(), QString("1"));
894
895 testRevert(model);
896 if (QTest::currentTestFailed())
897 return;
898
899 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
900 QVERIFY_SQL(model, select());
901 testRevert(model);
902
903 /* and again with OnManualSubmit */
904 model.setJoinMode(QSqlRelationalTableModel::InnerJoin);
905 QVERIFY_SQL(model, select());
906 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
907 testRevert(model);
908
909 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
910 QVERIFY_SQL(model, select());
911 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
912 testRevert(model);
913}
914
915void tst_QSqlRelationalTableModel::clearDisplayValuesCache()
916{
917 QFETCH_GLOBAL(QString, dbName);
918 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
919 CHECK_DATABASE(db);
920 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
921
922 QSqlRelationalTableModel model(0, db);
923
924 model.setTable(reltest1);
925 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
926
927 if (dbType != QSqlDriver::Sybase)
928 model.setRelation(column: 3, relation: QSqlRelation(reltest2, "id", "title"));
929 model.setSort(column: 1, order: Qt::AscendingOrder);
930 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
931
932 QVERIFY_SQL(model, select());
933
934 QCOMPARE(model.data(model.index(3, 0)).toInt(), 3);
935 QCOMPARE(model.data(model.index(3, 1)).toString(), QString("vohi"));
936 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
937 if (dbType != QSqlDriver::Sybase)
938 QCOMPARE(model.data(model.index(3, 3)).toString(), QString("mister"));
939 else
940 QCOMPARE(model.data(model.index(3, 3)).toInt(), 2 );
941
942 model.insertRow(arow: model.rowCount());
943 QVERIFY(model.setData(model.index(4, 0), 7, Qt::EditRole));
944 QVERIFY(model.setData(model.index(4, 1), "anders", Qt::EditRole));
945 QVERIFY(model.setData(model.index(4, 2), 1, Qt::EditRole));
946 QVERIFY(model.setData(model.index(4, 3), 1, Qt::EditRole));
947 model.submitAll();
948
949 QCOMPARE(model.data(model.index(0, 0)).toInt(), 7);
950 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("anders"));
951 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
952 if (dbType != QSqlDriver::Sybase)
953 QCOMPARE(model.data(model.index(0, 3)).toString(), QString("herr"));
954 else
955 QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
956
957 QCOMPARE(model.data(model.index(4, 0)).toInt(), 3);
958 QCOMPARE(model.data(model.index(4, 1)).toString(), QString("vohi"));
959 QCOMPARE(model.data(model.index(4, 2)).toString(), QString("herr"));
960 if (dbType != QSqlDriver::Sybase)
961 QCOMPARE(model.data(model.index(4, 3)).toString(), QString("mister"));
962 else
963 QCOMPARE(model.data(model.index(4, 3)).toInt(), 2);
964}
965
966// For task 140782 and 176374: If the main table and the related tables uses the same
967// name for a column or display column then insertRecord() would return true though it
968// actually failed.
969void tst_QSqlRelationalTableModel::insertRecordDuplicateFieldNames()
970{
971 QFETCH_GLOBAL(QString, dbName);
972 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
973 CHECK_DATABASE(db);
974 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
975
976 QSqlRelationalTableModel model(0, db);
977 model.setTable(reltest3);
978 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
979 model.setSort(column: 0, order: Qt::AscendingOrder);
980
981 // Duplication of "name", used in both reltest3 and reltest4.
982 model.setRelation(column: 2, relation: QSqlRelation(reltest4, "id", "name"));
983 QVERIFY_SQL(model, select());
984
985 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
986 QCOMPARE(model.record(1).value((reltest4+QLatin1String("_name_2")).toUpper()).toString(),
987 QString("Trondheim"));
988 } else {
989 QCOMPARE(model.record(1).value((reltest4+QLatin1String("_name_2"))).toString(),
990 QString("Trondheim"));
991 }
992
993 QSqlRecord rec = model.record();
994 rec.setValue(i: 0, val: 3);
995 rec.setValue(i: 1, val: "Berge");
996 rec.setValue(i: 2, val: 1); // Must insert the key value
997
998 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
999 QCOMPARE(rec.fieldName(0), QLatin1String("ID"));
1000 QCOMPARE(rec.fieldName(1), QLatin1String("NAME")); // This comes from main table
1001 } else {
1002 QCOMPARE(rec.fieldName(0), QLatin1String("id"));
1003 QCOMPARE(rec.fieldName(1), QLatin1String("name"));
1004 }
1005
1006 // The duplicate field names is aliased because it's comes from the relation's display column.
1007 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2)
1008 QCOMPARE(rec.fieldName(2), (reltest4+QLatin1String("_name_2")).toUpper());
1009 else
1010 QCOMPARE(rec.fieldName(2), reltest4+QLatin1String("_name_2"));
1011
1012 QVERIFY(model.insertRecord(-1, rec));
1013 QCOMPARE(model.data(model.index(2, 2)).toString(), QString("Oslo"));
1014 QVERIFY(model.submitAll());
1015 QCOMPARE(model.data(model.index(2, 2)).toString(), QString("Oslo"));
1016}
1017
1018void tst_QSqlRelationalTableModel::invalidData()
1019{
1020 QFETCH_GLOBAL(QString, dbName);
1021 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1022 CHECK_DATABASE(db);
1023
1024 QSqlRelationalTableModel model(0, db);
1025 model.setTable(reltest1);
1026 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1027 QVERIFY_SQL(model, select());
1028
1029 //try set a non-existent relational key
1030 QVERIFY(model.setData(model.index(0, 2), 3) == false);
1031 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1032
1033 //try to set data in non valid index
1034 QVERIFY(!model.setData(model.index(0,10),5));
1035
1036 //same test with LeftJoin mode
1037 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1038 QVERIFY_SQL(model, select());
1039
1040 //try set a non-existent relational key
1041 QVERIFY(model.setData(model.index(0, 2), 3) == false);
1042 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1043
1044 //try to set data in non valid index
1045 QVERIFY(!model.setData(model.index(0,10),5));
1046}
1047
1048void tst_QSqlRelationalTableModel::relationModel()
1049{
1050 QFETCH_GLOBAL(QString, dbName);
1051 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1052 CHECK_DATABASE(db);
1053
1054 QSqlRelationalTableModel model(0, db);
1055 model.setTable(reltest1);
1056 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1057 QVERIFY_SQL(model, select());
1058
1059 QVERIFY(!model.relationModel(0));
1060 QVERIFY(!model.relationModel(1));
1061 QVERIFY(model.relationModel(2) != NULL);
1062 QVERIFY(!model.relationModel(3));
1063 QVERIFY(!model.relationModel(4));
1064
1065 model.setRelation(column: 3, relation: QSqlRelation(reltest4, "id", "name"));
1066 QVERIFY_SQL(model, select());
1067
1068 QVERIFY(!model.relationModel(0));
1069 QVERIFY(!model.relationModel(1));
1070 QVERIFY(model.relationModel(2) != NULL);
1071 QVERIFY(model.relationModel(3) != NULL);
1072 QVERIFY(!model.relationModel(4));
1073
1074 QSqlTableModel *rel_model = model.relationModel(column: 2);
1075 QCOMPARE(rel_model->data(rel_model->index(0,1)).toString(), QString("herr"));
1076
1077 //same test in JoinMode
1078 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1079 QVERIFY_SQL(model, select());
1080
1081 QVERIFY(!model.relationModel(0));
1082 QVERIFY(!model.relationModel(1));
1083 QVERIFY(model.relationModel(2) != NULL);
1084 QVERIFY(model.relationModel(3) != NULL);
1085 QVERIFY(!model.relationModel(4));
1086
1087 QSqlTableModel *rel_model2 = model.relationModel(column: 2);
1088 QCOMPARE(rel_model2->data(rel_model->index(0,1)).toString(), QString("herr"));
1089}
1090
1091void tst_QSqlRelationalTableModel::casing()
1092{
1093 QFETCH_GLOBAL(QString, dbName);
1094 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1095 CHECK_DATABASE(db);
1096 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1097
1098 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::SQLite || dbType == QSqlDriver::MSSqlServer)
1099 QSKIP("The casing test for this database is irrelevant since this database does not treat different cases as separate entities");
1100
1101 QSqlQuery q(db);
1102 QVERIFY_SQL( q, exec("create table " + qTableName("CASETEST1", db).toUpper() +
1103 " (id int not null primary key, name varchar(20), title_key int, another_title_key int)"));
1104
1105 if (!q.exec(query: "create table " + qTableName(prefix: "casetest1", db) +
1106 " (ident int not null primary key, name varchar(20), title_key int)"))
1107 QSKIP("The casing test for this database is irrelevant since this database does not treat different cases as separate entities");
1108
1109 QVERIFY_SQL( q, exec("insert into " + qTableName("CASETEST1", db).toUpper() + " values(1, 'harry', 1, 2)"));
1110 QVERIFY_SQL( q, exec("insert into " + qTableName("CASETEST1", db).toUpper() + " values(2, 'trond', 2, 1)"));
1111 QVERIFY_SQL( q, exec("insert into " + qTableName("CASETEST1", db).toUpper() + " values(3, 'vohi', 1, 2)"));
1112 QVERIFY_SQL( q, exec("insert into " + qTableName("CASETEST1", db).toUpper() + " values(4, 'boris', 2, 2)"));
1113 QVERIFY_SQL( q, exec("insert into " + qTableName("casetest1", db) + " values(1, 'jerry', 1)"));
1114 QVERIFY_SQL( q, exec("insert into " + qTableName("casetest1", db) + " values(2, 'george', 2)"));
1115 QVERIFY_SQL( q, exec("insert into " + qTableName("casetest1", db) + " values(4, 'kramer', 2)"));
1116
1117 if (dbType == QSqlDriver::Oracle) {
1118 //try an owner that doesn't exist
1119 QSqlRecord rec = db.driver()->record(tableName: "doug." + qTableName(prefix: "CASETEST1", db).toUpper());
1120 QCOMPARE( rec.count(), 0);
1121
1122 //try an owner that does exist
1123 rec = db.driver()->record(tableName: db.userName() + QLatin1Char('.') + qTableName(prefix: "CASETEST1", db).toUpper());
1124 QCOMPARE( rec.count(), 4);
1125 }
1126 QSqlRecord rec = db.driver()->record(tableName: qTableName(prefix: "CASETEST1", db).toUpper());
1127 QCOMPARE( rec.count(), 4);
1128
1129 rec = db.driver()->record(tableName: qTableName(prefix: "casetest1", db));
1130 QCOMPARE( rec.count(), 3);
1131
1132 QSqlTableModel upperCaseModel(0, db);
1133 upperCaseModel.setTable(qTableName(prefix: "CASETEST1", db).toUpper());
1134
1135 QCOMPARE(upperCaseModel.tableName(), qTableName("CASETEST1", db).toUpper());
1136
1137 QVERIFY_SQL(upperCaseModel, select());
1138
1139 QCOMPARE(upperCaseModel.rowCount(), 4);
1140
1141 QSqlTableModel lowerCaseModel(0, db);
1142 lowerCaseModel.setTable(qTableName(prefix: "casetest1", db));
1143 QCOMPARE(lowerCaseModel.tableName(), qTableName("casetest1", db));
1144 QVERIFY_SQL(lowerCaseModel, select());
1145
1146 QCOMPARE(lowerCaseModel.rowCount(), 3);
1147
1148 QSqlRelationalTableModel model(0, db);
1149 model.setTable(qTableName(prefix: "CASETEST1", db).toUpper());
1150 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1151 QVERIFY_SQL(model, select());
1152
1153 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1154 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1155 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1156}
1157
1158void tst_QSqlRelationalTableModel::escapedRelations()
1159{
1160 QFETCH_GLOBAL(QString, dbName);
1161 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1162 CHECK_DATABASE(db);
1163 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1164
1165 recreateTestTables(db);
1166
1167 QSqlRelationalTableModel model(0, db);
1168 model.setTable(reltest1);
1169
1170 //try with relation table name quoted
1171 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1172 model.setRelation(column: 2, relation: QSqlRelation(db.driver()->escapeIdentifier(identifier: reltest2.toUpper(),type: QSqlDriver::TableName),
1173 "id",
1174 "title"));
1175 } else {
1176 model.setRelation(column: 2, relation: QSqlRelation(db.driver()->escapeIdentifier(identifier: reltest2,type: QSqlDriver::TableName),
1177 "id",
1178 "title"));
1179
1180 }
1181 QVERIFY_SQL(model, select());
1182
1183 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1184 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1185 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1186
1187 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1188 QVERIFY_SQL(model, select());
1189
1190 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1191 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1192 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1193
1194 //try with index column quoted
1195 model.setJoinMode(QSqlRelationalTableModel::InnerJoin);
1196 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1197 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1198 db.driver()->escapeIdentifier(identifier: "id", type: QSqlDriver::FieldName).toUpper(),
1199 "title"));
1200 } else {
1201 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1202 db.driver()->escapeIdentifier(identifier: "id", type: QSqlDriver::FieldName),
1203 "title"));
1204 }
1205 QVERIFY_SQL(model, select());
1206
1207 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1208 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1209 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1210
1211 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1212 QVERIFY_SQL(model, select());
1213
1214 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1215 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1216 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1217
1218 //try with display column quoted
1219 model.setJoinMode(QSqlRelationalTableModel::InnerJoin);
1220 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1221
1222 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1223 "id",
1224 db.driver()->escapeIdentifier(identifier: "title", type: QSqlDriver::FieldName).toUpper()));
1225 } else {
1226 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1227 "id",
1228 db.driver()->escapeIdentifier(identifier: "title", type: QSqlDriver::FieldName)));
1229 }
1230
1231 QVERIFY_SQL(model, select());
1232
1233 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1234 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1235 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1236
1237 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1238 QVERIFY_SQL(model, select());
1239
1240 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1241 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1242 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1243
1244 //try with tablename and index and display columns quoted in the relation
1245 model.setJoinMode(QSqlRelationalTableModel::InnerJoin);
1246 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1247 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1248 "id",
1249 db.driver()->escapeIdentifier(identifier: "title", type: QSqlDriver::FieldName).toUpper()));
1250 } else {
1251 model.setRelation(column: 2, relation: QSqlRelation(reltest2,
1252 "id",
1253 db.driver()->escapeIdentifier(identifier: "title", type: QSqlDriver::FieldName)));
1254 }
1255 QVERIFY_SQL(model, select());
1256
1257 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1258 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1259 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1260
1261 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1262 QVERIFY_SQL(model, select());
1263
1264 QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
1265 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry"));
1266 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("herr"));
1267}
1268
1269void tst_QSqlRelationalTableModel::escapedTableName()
1270{
1271 QFETCH_GLOBAL(QString, dbName);
1272 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1273 CHECK_DATABASE(db);
1274 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1275
1276 // set the values using OnRowChange Strategy with an escaped tablename
1277 {
1278 QSqlRelationalTableModel model(0, db);
1279
1280 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1281 model.setTable(db.driver()->escapeIdentifier(identifier: reltest1.toUpper(), type: QSqlDriver::TableName));
1282 } else {
1283 model.setTable(db.driver()->escapeIdentifier(identifier: reltest1, type: QSqlDriver::TableName));
1284 }
1285 model.setSort(column: 0, order: Qt::AscendingOrder);
1286 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1287 QVERIFY_SQL(model, select());
1288
1289 QVERIFY(model.setData(model.index(0, 1), QString("harry2")));
1290 QVERIFY(model.setData(model.index(0, 2), 2));
1291
1292 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
1293 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
1294
1295 model.submit();
1296
1297 QVERIFY(model.setData(model.index(3,1), QString("boris2")));
1298 QVERIFY(model.setData(model.index(3, 2), 1));
1299
1300 QCOMPARE(model.data(model.index(3,1)).toString(), QString("boris2"));
1301 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
1302
1303 model.submit();
1304 }
1305 { //verify values
1306 QSqlRelationalTableModel model(0, db);
1307 model.setTable(reltest1);
1308 model.setSort(column: 0, order: Qt::AscendingOrder);
1309 QVERIFY_SQL(model, select());
1310
1311 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
1312 QCOMPARE(model.data(model.index(0, 2)).toInt(), 2);
1313 QCOMPARE(model.data(model.index(3, 1)).toString(), QString("boris2"));
1314 QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);
1315
1316 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1317 QVERIFY_SQL(model, select());
1318 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
1319 QCOMPARE(model.data(model.index(3,2)).toString(), QString("herr"));
1320
1321 }
1322
1323 //ok, now do same test with LeftJoin
1324 {
1325 QSqlRelationalTableModel model(0, db);
1326
1327 if (dbType == QSqlDriver::Interbase || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::DB2) {
1328 model.setTable(db.driver()->escapeIdentifier(identifier: reltest1.toUpper(), type: QSqlDriver::TableName));
1329 } else {
1330 model.setTable(db.driver()->escapeIdentifier(identifier: reltest1, type: QSqlDriver::TableName));
1331 }
1332 model.setSort(column: 0, order: Qt::AscendingOrder);
1333 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1334 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1335 QVERIFY_SQL(model, select());
1336
1337 QVERIFY(model.setData(model.index(0, 1), QString("harry2")));
1338 QVERIFY(model.setData(model.index(0, 2), 2));
1339
1340 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
1341 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
1342
1343 model.submit();
1344
1345 QVERIFY(model.setData(model.index(3,1), QString("boris2")));
1346 QVERIFY(model.setData(model.index(3, 2), 1));
1347
1348 QCOMPARE(model.data(model.index(3,1)).toString(), QString("boris2"));
1349 QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
1350
1351 model.submit();
1352 }
1353 { //verify values
1354 QSqlRelationalTableModel model(0, db);
1355 model.setTable(reltest1);
1356 model.setSort(column: 0, order: Qt::AscendingOrder);
1357 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1358 QVERIFY_SQL(model, select());
1359
1360 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("harry2"));
1361 QCOMPARE(model.data(model.index(0, 2)).toInt(), 2);
1362 QCOMPARE(model.data(model.index(3, 1)).toString(), QString("boris2"));
1363 QCOMPARE(model.data(model.index(3, 2)).toInt(), 1);
1364
1365 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1366 QVERIFY_SQL(model, select());
1367 QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
1368 QCOMPARE(model.data(model.index(3,2)).toString(), QString("herr"));
1369
1370 }
1371}
1372
1373void tst_QSqlRelationalTableModel::whiteSpaceInIdentifiers()
1374{
1375 QFETCH_GLOBAL(QString, dbName);
1376 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1377 CHECK_DATABASE(db);
1378
1379 if (!testWhiteSpaceNames(name: db.driverName()))
1380 QSKIP("White space test irrelevant for driver");
1381 QSqlRelationalTableModel model(0, db);
1382 model.setTable(qTableName(prefix: "rel test6", __FILE__, db));
1383 model.setSort(column: 0, order: Qt::DescendingOrder);
1384 model.setRelation(column: 1, relation: QSqlRelation(qTableName(prefix: "rel test7", __FILE__, db),
1385 db.driver()->escapeIdentifier(identifier: "city id", type: QSqlDriver::FieldName),
1386 db.driver()->escapeIdentifier(identifier: "city name", type: QSqlDriver::FieldName)));
1387 QVERIFY_SQL(model, select());
1388
1389 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Washington"));
1390 QCOMPARE(model.data(model.index(1,1)).toString(), QString("New York"));
1391
1392 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1393 QVERIFY_SQL(model, select());
1394
1395 QCOMPARE(model.data(model.index(0,1)).toString(), QString("Washington"));
1396 QCOMPARE(model.data(model.index(1,1)).toString(), QString("New York"));
1397
1398 model.setJoinMode(QSqlRelationalTableModel::InnerJoin);
1399 QVERIFY_SQL(model, select());
1400
1401 QSqlRecord rec;
1402 QSqlField f1("id", QVariant::Int);
1403 QSqlField f2(db.driver()->escapeIdentifier(identifier: "city key", type: QSqlDriver::FieldName), QVariant::Int);
1404 QSqlField f3(db.driver()->escapeIdentifier(identifier: "extra field", type: QSqlDriver::FieldName), QVariant::Int);
1405
1406 f1.setValue(3);
1407 f2.setValue(2);
1408 f3.setValue(7);
1409
1410 f1.setGenerated(true);
1411 f2.setGenerated(true);
1412 f3.setGenerated(true);
1413
1414 rec.append(field: f1);
1415 rec.append(field: f2);
1416 rec.append(field: f3);
1417
1418 QVERIFY_SQL(model, insertRecord(-1, rec));
1419 model.submitAll();
1420 if (model.editStrategy() != QSqlTableModel::OnManualSubmit)
1421 QVERIFY_SQL(model, select());
1422
1423 QCOMPARE(model.data(model.index(0, 0)).toInt(), 3);
1424 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("Washington"));
1425 QCOMPARE(model.data(model.index(0, 2)).toInt(), 7);
1426
1427 model.setEditStrategy(QSqlTableModel::OnManualSubmit);
1428
1429 QSqlRecord recNew;
1430 QSqlField f1New("id", QVariant::Int);
1431 QSqlField f2New(db.driver()->escapeIdentifier(identifier: "city key", type: QSqlDriver::FieldName), QVariant::Int);
1432 QSqlField f3New(db.driver()->escapeIdentifier(identifier: "extra field", type: QSqlDriver::FieldName), QVariant::Int);
1433
1434 f1New.setValue(4);
1435 f2New.setValue(1);
1436 f3New.setValue(6);
1437
1438 f1New.setGenerated(true);
1439 f2New.setGenerated(true);
1440 f3New.setGenerated(true);
1441
1442 recNew.append(field: f1New);
1443 recNew.append(field: f2New);
1444 recNew.append(field: f3New);
1445
1446 QVERIFY_SQL(model, setRecord(0, recNew));
1447
1448 QCOMPARE(model.data(model.index(0, 0)).toInt(), 4);
1449 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("New York"));
1450 QCOMPARE(model.data(model.index(0, 2)).toInt(), 6);
1451
1452 QVERIFY_SQL(model, submitAll());
1453 QCOMPARE(model.data(model.index(0, 0)).toInt(), 4);
1454 QCOMPARE(model.data(model.index(0, 1)).toString(), QString("New York"));
1455 QCOMPARE(model.data(model.index(0, 2)).toInt(), 6);
1456}
1457
1458void tst_QSqlRelationalTableModel::psqlSchemaTest()
1459{
1460 QFETCH_GLOBAL(QString, dbName);
1461 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1462 CHECK_DATABASE(db);
1463 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1464
1465 if (dbType != QSqlDriver::PostgreSQL)
1466 QSKIP("Postgresql specific test");
1467
1468 QSqlRelationalTableModel model(0, db);
1469 QSqlQuery q(db);
1470 QVERIFY_SQL(q, exec("create schema " + qTableName("QTBUG_5373", __FILE__, db)));
1471 QVERIFY_SQL(q, exec("create schema " + qTableName("QTBUG_5373_s2", __FILE__, db)));
1472 QVERIFY_SQL(q, exec("create table " + qTableName("QTBUG_5373", __FILE__, db) + QLatin1Char('.') + qTableName("document", __FILE__, db) +
1473 "(document_id int primary key, relatingid int, userid int)"));
1474 QVERIFY_SQL(q, exec("create table " + qTableName("QTBUG_5373_s2", __FILE__, db) + QLatin1Char('.') + qTableName("user", __FILE__, db) +
1475 "(userid int primary key, username char(40))"));
1476 model.setTable(qTableName(prefix: "QTBUG_5373", __FILE__, db) + QLatin1Char('.') + qTableName(prefix: "document", __FILE__, db));
1477 model.setRelation(column: 1, relation: QSqlRelation(qTableName(prefix: "QTBUG_5373_s2", __FILE__, db) + QLatin1Char('.') + qTableName(prefix: "user", __FILE__, db), "userid", "username"));
1478 model.setRelation(column: 2, relation: QSqlRelation(qTableName(prefix: "QTBUG_5373_s2", __FILE__, db) + QLatin1Char('.') + qTableName(prefix: "user", __FILE__, db), "userid", "username"));
1479 QVERIFY_SQL(model, select());
1480
1481 model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
1482 QVERIFY_SQL(model, select());
1483}
1484
1485void tst_QSqlRelationalTableModel::selectAfterUpdate()
1486{
1487 QFETCH_GLOBAL(QString, dbName);
1488 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1489 CHECK_DATABASE(db);
1490
1491 QSqlRelationalTableModel model(0, db);
1492 model.setTable(reltest1);
1493 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1494 QVERIFY_SQL(model, select());
1495 QCOMPARE(model.relationModel(2)->rowCount(), 2);
1496 {
1497 QSqlQuery q(db);
1498 QVERIFY_SQL(q, exec("insert into " + reltest2 + " values(3, 'mrs')"));
1499 model.relationModel(column: 2)->select();
1500 }
1501 QCOMPARE(model.relationModel(2)->rowCount(), 3);
1502 QVERIFY(model.setData(model.index(0,2), 3));
1503 QVERIFY(model.submitAll());
1504 QCOMPARE(model.data(model.index(0,2)), QVariant("mrs"));
1505}
1506
1507/**
1508 This test case verifies bug fix for QTBUG-20038.
1509 */
1510void tst_QSqlRelationalTableModel::relationOnFirstColumn()
1511{
1512 QFETCH_GLOBAL(QString, dbName);
1513 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1514 CHECK_DATABASE(db);
1515
1516 QString testTable1 = qTableName(prefix: "QTBUG_20038_test1", __FILE__, db);
1517 QString testTable2 = qTableName(prefix: "QTBUG_20038_test2", __FILE__, db);
1518 tst_Databases::safeDropTables(db, tableNames: QStringList() << testTable1 << testTable2);
1519
1520 //prepare test1 table
1521 QSqlQuery q(db);
1522 QVERIFY_SQL(q, exec("CREATE TABLE " + testTable1 + " (val1 INTEGER, id1 INTEGER PRIMARY KEY);"));
1523 QVERIFY_SQL(q, exec("DELETE FROM " + testTable1 + QLatin1Char(';')));
1524 QVERIFY_SQL(q, exec("INSERT INTO " + testTable1 + " (id1, val1) VALUES(1, 10);"));
1525 QVERIFY_SQL(q, exec("INSERT INTO " + testTable1 + " (id1, val1) VALUES(2, 20);"));
1526 QVERIFY_SQL(q, exec("INSERT INTO " + testTable1 + " (id1, val1) VALUES(3, 30);"));
1527
1528 //prepare test2 table
1529 QVERIFY_SQL(q, exec("CREATE TABLE " + testTable2 + " (id INTEGER PRIMARY KEY, name TEXT);"));
1530 QVERIFY_SQL(q, exec("DELETE FROM " + testTable2 + QLatin1Char(';')));
1531 QVERIFY_SQL(q, exec("INSERT INTO " + testTable2 + " (id, name) VALUES (10, 'Hervanta');"));
1532 QVERIFY_SQL(q, exec("INSERT INTO " + testTable2 + " (id, name) VALUES (20, 'Keskusta');"));
1533 QVERIFY_SQL(q, exec("INSERT INTO " + testTable2 + " (id, name) VALUES (30, 'Annala');"));
1534 QVERIFY_SQL(q, exec("INSERT INTO " + testTable2 + " (id, name) VALUES (40, 'Tammela');"));
1535 QVERIFY_SQL(q, exec("INSERT INTO " + testTable2 + " (id, name) VALUES (50, 'Amuri');"));
1536
1537 //set test model
1538 QSqlRelationalTableModel model(NULL, db);
1539 model.setTable(testTable1);
1540 model.setRelation(column: 0, relation: QSqlRelation(testTable2, "id", "name"));
1541 QVERIFY_SQL(model, select());
1542
1543 //verify the data
1544 QCOMPARE(model.data(model.index(0, 0)), QVariant("Hervanta"));
1545 QCOMPARE(model.data(model.index(1, 0)), QVariant("Keskusta"));
1546 QCOMPARE(model.data(model.index(2, 0)), QVariant("Annala"));
1547
1548 //modify the model data
1549 QVERIFY_SQL(model, setData(model.index(0, 0), 40));
1550 QVERIFY_SQL(model, submit());
1551 QVERIFY_SQL(model, setData(model.index(1, 0), 50));
1552 QVERIFY_SQL(model, submit());
1553 QVERIFY_SQL(model, setData(model.index(2, 0), 30));
1554
1555 //verify the data after modificaiton
1556 QCOMPARE(model.data(model.index(0, 0)), QVariant("Tammela"));
1557 QCOMPARE(model.data(model.index(1, 0)), QVariant("Amuri"));
1558 QCOMPARE(model.data(model.index(2, 0)), QVariant("Annala"));
1559
1560 tst_Databases::safeDropTables(db, tableNames: QStringList() << testTable1 << testTable2);
1561}
1562
1563void tst_QSqlRelationalTableModel::setRelation()
1564{
1565 QFETCH_GLOBAL(QString, dbName);
1566 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1567 CHECK_DATABASE(db);
1568 recreateTestTables(db);
1569
1570 QSqlRelationalTableModel model(0, db);
1571 model.setTable(reltest1);
1572 QVERIFY_SQL(model, select());
1573 QCOMPARE(model.data(model.index(0, 2)), QVariant(1));
1574
1575 model.setRelation(column: 2, relation: QSqlRelation(reltest2, "id", "title"));
1576 QVERIFY_SQL(model, select());
1577 QCOMPARE(model.data(model.index(0, 2)), QVariant("herr"));
1578
1579 // Check that setting an invalid QSqlRelation() clears the relation
1580 model.setRelation(column: 2, relation: QSqlRelation());
1581 QVERIFY_SQL(model, select());
1582 QCOMPARE(model.data(model.index(0, 2)), QVariant(1));
1583}
1584
1585QTEST_MAIN(tst_QSqlRelationalTableModel)
1586#include "tst_qsqlrelationaltablemodel.moc"
1587

source code of qtbase/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp