1
/****************************************************************************
3
** Copyright (C) 1992-2005 Trolltech AS. All rights reserved.
5
** This file is part of the sql module of the Qt Toolkit.
7
** This file may be distributed under the terms of the Q Public License
8
** as defined by Trolltech AS of Norway and appearing in the file
9
** LICENSE.QPL included in the packaging of this file.
11
** This file may be distributed and/or modified under the terms of the
12
** GNU General Public License version 2 as published by the Free Software
13
** Foundation and appearing in the file LICENSE.GPL included in the
14
** packaging of this file.
16
** See http://www.trolltech.com/pricing.html or email sales@trolltech.com for
17
** information about Qt Commercial License Agreements.
18
** See http://www.trolltech.com/qpl/ for QPL licensing information.
19
** See http://www.trolltech.com/gpl/ for GPL licensing information.
21
** Contact info@trolltech.com if any conditions of this licensing are
24
** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
25
** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
27
****************************************************************************/
29
#include "qsqlrelationaltablemodel.h"
32
#include "qstringlist.h"
33
#include "qsqldatabase.h"
34
#include "qsqlerror.h"
35
#include "qsqlfield.h"
36
#include "qsqlindex.h"
37
#include "qsqlquery.h"
38
#include "qsqlrecord.h"
40
#include "qsqltablemodel_p.h"
44
\brief The QSqlRelation class stores information about an SQL foreign key.
46
QSqlRelation is a helper class for QSqlRelationalTableModel. See
47
QSqlRelationalTableModel::setRelation() and
48
QSqlRelationalTableModel::relation() for details.
50
\sa QSqlRelationalTableModel, QSqlRelationalDelegate
54
\fn QSqlRelation::QSqlRelation()
56
Constructs an invalid QSqlRelation object.
58
For such an object, the tableName(), indexColumn(), and
59
displayColumn() functions return an empty string.
65
\fn QSqlRelation::QSqlRelation(const QString &tableName, const QString &indexColumn,
66
const QString &displayColumn)
68
Constructs a QSqlRelation object, where \a tableName is the SQL
69
table name to which a foreign key refers, \a indexColumn is the
70
foreign key, and \a displayColumn is the field that should be
71
presented to the user.
73
\sa tableName(), indexColumn(), displayColumn()
77
\fn QString QSqlRelation::tableName() const
79
Returns the name of the table to which a foreign key refers.
83
\fn QString QSqlRelation::indexColumn() const
85
Returns the index column from table tableName() to which a
90
\fn QString QSqlRelation::displayColumn() const
92
Returns the column from table tableName() that should be
93
presented to the user instead of a foreign key.
97
\fn bool QSqlRelation::isValid() const
99
Returns true if the QSqlRelation object is valid; otherwise
105
Relation(): model(0) {}
107
QSqlTableModel *model;
108
QHash<int, QVariant> displayValues;
111
class QSqlRelationalTableModelPrivate: public QSqlTableModelPrivate
114
QSqlRelationalTableModelPrivate()
115
: QSqlTableModelPrivate()
118
mutable QVector<Relation> relations;
119
QSqlRecord baseRec; // the record without relations
123
static void qAppendWhereClause(QString &query, const QString &clause1, const QString &clause2)
125
if (clause1.isEmpty() && clause2.isEmpty())
127
if (clause1.isEmpty() || clause2.isEmpty())
128
query.append(QLatin1String(" WHERE (")).append(clause1).append(clause2);
130
query.append(QLatin1String(" WHERE (")).append(clause1).append(
131
QLatin1String(") AND (")).append(clause2);
132
query.append(QLatin1String(") "));
135
void QSqlRelationalTableModelPrivate::clearChanges()
137
for (int i = 0; i < relations.count(); ++i) {
138
Relation &rel = relations[i];
140
rel.displayValues.clear();
145
\class QSqlRelationalTableModel
146
\brief The QSqlRelationalTableModel class provides an editable
147
data model for a single database table, with foreign key support.
152
QSqlRelationalTableModel acts like QSqlTableModel, but allows
153
columns to be set as foreign keys into other database tables.
156
\row \o \inlineimage noforeignkeys.png
157
\o \inlineimage foreignkeys.png
160
The screenshot on the left shows a plain QSqlTableModel in a
161
QTableView. Foreign keys (\c city and \c country) aren't resolved
162
to human-readable values. The screenshot on the right shows a
163
QSqlRelationalTableModel, with foreign keys resolved into
164
human-readable text strings.
166
The following code snippet shows how the QSqlRelationalTableModel
169
\quotefromfile sql/relationaltablemodel/relationaltablemodel.cpp
170
\skipto model->setTable
171
\printline model->setTable
173
\printline setRelation
174
\printline setRelation
176
The setRelation() function calls establish a relationship between
177
two tables. The first call specifies that column 2 in table \c
178
employee is a foreign key that maps with field \c id of table \c
179
city, and that the view should present the \c{city}'s \c name
180
field to the user. The second call does something similar with
183
If you use a read-write QSqlRelationalTableModel, you probably
184
want to use QSqlRelatinalDelegate on the view. Unlike the default
185
delegate, QSqlRelationalDelegate provides a combobox for fields
186
that are foreign keys into other tables. To use the class, simply
187
call QAbstractItemView::setItemDelegate() on the view with an
188
instance of QSqlRelationalDelegate:
190
\quotefromfile sql/relationaltablemodel/relationaltablemodel.cpp
191
\skipto QTableView *view = new
192
\printuntil setItemDelegate
194
The \l{sql/relationaltablemodel} example illustrates how to use
195
QSqlRelationalTableModel in conjunction with
196
QSqlRelationalDelegate to provide tables with foreigh key
199
\image relationaltable.png
201
Note: The table's primary key may not contain a relation to
204
\sa QSqlRelation, QSqlRelationalDelegate
209
Creates an empty QSqlRelationalTableModel and sets the parent to \a parent
210
and the database connection to \a db. If \a db is not valid, the
211
default database connection will be used.
213
QSqlRelationalTableModel::QSqlRelationalTableModel(QObject *parent, QSqlDatabase db)
214
: QSqlTableModel(*new QSqlRelationalTableModelPrivate, parent, db)
219
Destroys the object and frees any allocated resources.
221
QSqlRelationalTableModel::~QSqlRelationalTableModel()
228
QVariant QSqlRelationalTableModel::data(const QModelIndex &index, int role) const
230
Q_D(const QSqlRelationalTableModel);
231
if (role == Qt::DisplayRole && index.column() > 0 && index.column() < d->relations.count()) {
232
const QVariant v = d->relations.at(index.column()).displayValues.value(index.row());
237
return QSqlTableModel::data(index, role);
241
Sets the data for the \a role in the item with the specified \a
242
index to the \a value given. Depending on the edit strategy, the
243
value might be applied to the database at once, or it may be
246
Returns true if the value could be set, or false on error (for
247
example, if \a index is out of bounds).
249
For relational columns, \a value must be the index, not the
252
\sa editStrategy(), data(), submit(), revertRow()
254
bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value,
257
Q_D(QSqlRelationalTableModel);
258
if (role == Qt::DisplayRole && index.column() > 0 && index.column() < d->relations.count()) {
259
d->relations[index.column()].displayValues[index.row()] = value;
263
return QSqlTableModel::setData(index, value, role);
267
Lets the specified \a column be a foreign index specified by \a relation.
271
\quotefromfile sql/relationaltablemodel/relationaltablemodel.cpp
272
\skipto model->setTable
273
\printline model->setTable
275
\printline setRelation
277
The setRelation() call specifies that column 2 in table \c
278
employee is a foreign key that maps with field \c id of table \c
279
city, and that the view should present the \c{city}'s \c name
284
void QSqlRelationalTableModel::setRelation(int column, const QSqlRelation &relation)
286
Q_D(QSqlRelationalTableModel);
289
if (d->relations.size() <= column)
290
d->relations.resize(column + 1);
291
d->relations[column].rel = relation;
295
Returns the relation for the column \a column, or an invalid
296
relation if no relation is set.
298
\sa setRelation(), QSqlRelation::isValid()
300
QSqlRelation QSqlRelationalTableModel::relation(int column) const
302
Q_D(const QSqlRelationalTableModel);
303
return d->relations.value(column).rel;
309
QString QSqlRelationalTableModel::selectStatement() const
311
Q_D(const QSqlRelationalTableModel);
314
if (tableName().isEmpty())
316
if (d->relations.isEmpty())
317
return QSqlTableModel::selectStatement();
323
QSqlRecord rec = database().record(tableName());
325
const Relation nullRelation;
326
for (int i = 0; i < rec.count(); ++i) {
327
QSqlRelation relation = d->relations.value(i, nullRelation).rel;
328
if (relation.isValid()) {
329
fList.append(relation.tableName()).append(QLatin1Char('.'));
330
fList.append(relation.displayColumn()).append(QLatin1Char(','));
331
if (!tables.contains(relation.tableName()))
332
tables.append(relation.tableName());
333
where.append(tableName()).append(QLatin1Char('.')).append(rec.fieldName(i));
334
where.append(QLatin1Char('=')).append(relation.tableName()).append(QLatin1Char('.'));
335
where.append(relation.indexColumn()).append(QLatin1String(" and "));
337
fList.append(tableName()).append(QLatin1Char('.')).append(rec.fieldName(i)).append(
341
if (!tables.isEmpty())
342
tList.append(tables.join(QLatin1String(","))).append(QLatin1String(","));
345
tList.prepend(QLatin1Char(',')).prepend(tableName());
346
// truncate tailing comma
349
query.append(QLatin1String("SELECT "));
350
query.append(fList).append(QLatin1String(" FROM ")).append(tList);
351
if (!where.isEmpty())
353
qAppendWhereClause(query, where, filter());
355
QString orderBy = orderByClause();
356
if (!orderBy.isEmpty())
357
query.append(QLatin1Char(' ')).append(orderBy);
363
Returns a QSqlTableModel object for accessing the table for which
364
\a column is a foreign key, or 0 if there is no relation for the
367
The returned object is owned by the QSqlRelationalTableModel.
369
\sa setRelation(), relation()
371
QSqlTableModel *QSqlRelationalTableModel::relationModel(int column) const
373
Q_D(const QSqlRelationalTableModel);
374
Relation relation = d->relations.value(column);
375
if (!relation.rel.isValid())
378
QSqlTableModel *childModel = relation.model;
380
childModel = new QSqlTableModel(const_cast<QSqlRelationalTableModel *>(this), database());
381
childModel->setTable(relation.rel.tableName());
382
childModel->select();
383
d->relations[column].model = childModel;
391
void QSqlRelationalTableModel::revertRow(int row)
393
Q_D(QSqlRelationalTableModel);
394
for (int i = 0; i < d->relations.count(); ++i)
395
d->relations[i].displayValues.remove(row);
396
QSqlTableModel::revertRow(row);
402
void QSqlRelationalTableModel::clear()
404
Q_D(QSqlRelationalTableModel);
406
d->relations.clear();
407
QSqlTableModel::clear();
413
bool QSqlRelationalTableModel::select()
415
return QSqlTableModel::select();
421
void QSqlRelationalTableModel::setTable(const QString &table)
423
Q_D(QSqlRelationalTableModel);
425
// memorize the table before applying the relations
426
d->baseRec = d->db.record(table);
428
QSqlTableModel::setTable(table);
434
bool QSqlRelationalTableModel::updateRowInTable(int row, const QSqlRecord &values)
436
Q_D(QSqlRelationalTableModel);
438
QSqlRecord rec = values;
440
// translate the field names
441
for (int i = 0; i < values.count(); ++i) {
442
int realCol = indexInQuery(createIndex(row, i)).column();
443
if (realCol != -1 && d->relations.value(realCol).rel.isValid()) {
444
QVariant v = values.value(i);
445
rec.replace(i, d->baseRec.field(realCol));
449
return QSqlTableModel::updateRowInTable(row, rec);
455
QString QSqlRelationalTableModel::orderByClause() const
457
Q_D(const QSqlRelationalTableModel);
459
const QSqlRelation rel = d->relations.value(d->sortColumn).rel;
461
return QSqlTableModel::orderByClause();
463
QString s = QLatin1String("ORDER BY ");
464
s.append(rel.tableName()).append(QLatin1Char('.')).append(d->rec.field(d->sortColumn).name());
465
s += d->sortOrder == Qt::AscendingOrder ? QLatin1String(" ASC") : QLatin1String(" DESC");