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 QtSql module 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 #include "qsqlquerymodel.h"

    43 

    44 #include <qdebug.h>

    45 #include <qsqldriver.h>

    46 #include <qsqlfield.h>

    47 

    48 #include "qsqlquerymodel_p.h"

    49 

    50 QT_BEGIN_NAMESPACE

    51 

    52 #define QSQL_PREFETCH 255

    53 

    54 void QSqlQueryModelPrivate::prefetch(int limit)

    55 {

    56     Q_Q(QSqlQueryModel);

    57 

    58     if (atEnd || limit <= bottom.row() || bottom.column() == -1)

    59         return;

    60 

    61     QModelIndex newBottom;

    62     const int oldBottomRow = qMax(bottom.row(), 0);

    63 

    64     // try to seek directly

    65     if (query.seek(limit)) {

    66         newBottom = q->createIndex(limit, bottom.column());

    67     } else {

    68         // have to seek back to our old position for MS Access

    69         int i = oldBottomRow;

    70         if (query.seek(i)) {

    71             while (query.next())

    72                 ++i;

    73             newBottom = q->createIndex(i, bottom.column());

    74         } else {

    75             // empty or invalid query

    76             newBottom = q->createIndex(-1, bottom.column());

    77         }

    78         atEnd = true; // this is the end.

    79     }

    80     if (newBottom.row() >= 0 && newBottom.row() > bottom.row()) {

    81         q->beginInsertRows(QModelIndex(), bottom.row() + 1, newBottom.row());

    82         bottom = newBottom;

    83         q->endInsertRows();

    84     } else {

    85         bottom = newBottom;

    86     }

    87 }

    88 

    89 QSqlQueryModelPrivate::~QSqlQueryModelPrivate()

    90 {

    91 }

    92 

    93 void QSqlQueryModelPrivate::initColOffsets(int size)

    94 {

    95     colOffsets.resize(size);

    96     memset(colOffsets.data(), 0, colOffsets.size() * sizeof(int));

    97 }

    98 

    99 /*!

   100     \class QSqlQueryModel

   101     \brief The QSqlQueryModel class provides a read-only data model for SQL

   102     result sets.

   103 

   104     \ingroup database

   105     \inmodule QtSql

   106 

   107     QSqlQueryModel is a high-level interface for executing SQL

   108     statements and traversing the result set. It is built on top of

   109     the lower-level QSqlQuery and can be used to provide data to

   110     view classes such as QTableView. For example:

   111 

   112     \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 16

   113 

   114     We set the model's query, then we set up the labels displayed in

   115     the view header.

   116 

   117     QSqlQueryModel can also be used to access a database

   118     programmatically, without binding it to a view:

   119 

   120     \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 21

   121 

   122     The code snippet above extracts the \c salary field from record 4 in

   123     the result set of the query \c{SELECT * from employee}. Assuming

   124     that \c salary is column 2, we can rewrite the last line as follows:

   125 

   126     \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 22

   127 

   128     The model is read-only by default. To make it read-write, you

   129     must subclass it and reimplement setData() and flags(). Another

   130     option is to use QSqlTableModel, which provides a read-write

   131     model based on a single database table.

   132 

   133     The \l{sql/querymodel} example illustrates how to use

   134     QSqlQueryModel to display the result of a query. It also shows

   135     how to subclass QSqlQueryModel to customize the contents of the

   136     data before showing it to the user, and how to create a

   137     read-write model based on QSqlQueryModel.

   138 

   139     If the database doesn't return the amount of selected rows in

   140     a query, the model will fetch rows incrementally.

   141     See fetchMore() for more information.

   142 

   143     \sa QSqlTableModel, QSqlRelationalTableModel, QSqlQuery,

   144         {Model/View Programming}, {Query Model Example}

   145 */

   146 

   147 /*!

   148     Creates an empty QSqlQueryModel with the given \a parent.

   149  */

   150 QSqlQueryModel::QSqlQueryModel(QObject *parent)

   151     : QAbstractTableModel(*new QSqlQueryModelPrivate, parent)

   152 {

   153 }

   154 

   155 /*! \internal

   156  */

   157 QSqlQueryModel::QSqlQueryModel(QSqlQueryModelPrivate &dd, QObject *parent)

   158     : QAbstractTableModel(dd, parent)

   159 {

   160 }

   161 

   162 /*!

   163     Destroys the object and frees any allocated resources.

   164 

   165     \sa clear()

   166 */

   167 QSqlQueryModel::~QSqlQueryModel()

   168 {

   169 }

   170 

   171 /*!

   172     \since 4.1

   173 

   174     Fetches more rows from a database.

   175     This only affects databases that don't report back the size of a query

   176     (see QSqlDriver::hasFeature()).

   177 

   178     To force fetching of the entire database, you can use the following:

   179 

   180     \snippet doc/src/snippets/code/src_sql_models_qsqlquerymodel.cpp 0

   181 

   182     \a parent should always be an invalid QModelIndex.

   183 

   184     \sa canFetchMore()

   185 */

   186 void QSqlQueryModel::fetchMore(const QModelIndex &parent)

   187 {

   188     Q_D(QSqlQueryModel);

   189     if (parent.isValid())

   190         return;

   191     d->prefetch(qMax(d->bottom.row(), 0) + QSQL_PREFETCH);

   192 }

   193 

   194 /*!

   195     \since 4.1

   196 

   197     Returns true if it is possible to read more rows from the database.

   198     This only affects databases that don't report back the size of a query

   199     (see QSqlDriver::hasFeature()).

   200 

   201     \a parent should always be an invalid QModelIndex.

   202 

   203     \sa fetchMore()

   204  */

   205 bool QSqlQueryModel::canFetchMore(const QModelIndex &parent) const

   206 {

   207     Q_D(const QSqlQueryModel);

   208     return (!parent.isValid() && !d->atEnd);

   209 }

   210 

   211 /*! \fn int QSqlQueryModel::rowCount(const QModelIndex &parent) const

   212     \since 4.1

   213 

   214     If the database supports returning the size of a query

   215     (see QSqlDriver::hasFeature()), the amount of rows of the current

   216     query is returned. Otherwise, returns the amount of rows

   217     currently cached on the client.

   218 

   219     \a parent should always be an invalid QModelIndex.

   220 

   221     \sa canFetchMore(), QSqlDriver::hasFeature()

   222  */

   223 int QSqlQueryModel::rowCount(const QModelIndex &index) const

   224 {

   225     Q_D(const QSqlQueryModel);

   226     return index.isValid() ? 0 : d->bottom.row() + 1;

   227 }

   228 

   229 /*! \reimp

   230  */

   231 int QSqlQueryModel::columnCount(const QModelIndex &index) const

   232 {

   233     Q_D(const QSqlQueryModel);

   234     return index.isValid() ? 0 : d->rec.count();

   235 }

   236 

   237 /*!

   238     Returns the value for the specified \a item and \a role.

   239 

   240     If \a item is out of bounds or if an error occurred, an invalid

   241     QVariant is returned.

   242 

   243     \sa lastError()

   244 */

   245 QVariant QSqlQueryModel::data(const QModelIndex &item, int role) const

   246 {

   247     Q_D(const QSqlQueryModel);

   248     if (!item.isValid())

   249         return QVariant();

   250 

   251     QVariant v;

   252     if (role & ~(Qt::DisplayRole | Qt::EditRole))

   253         return v;

   254 

   255     if (!d->rec.isGenerated(item.column()))

   256         return v;

   257     QModelIndex dItem = indexInQuery(item);

   258     if (dItem.row() > d->bottom.row())

   259         const_cast<QSqlQueryModelPrivate *>(d)->prefetch(dItem.row());

   260 

   261     if (!d->query.seek(dItem.row())) {

   262         d->error = d->query.lastError();

   263         return v;

   264     }

   265 

   266     return d->query.value(dItem.column());

   267 }

   268 

   269 /*!

   270     Returns the header data for the given \a role in the \a section

   271     of the header with the specified \a orientation.

   272 */

   273 QVariant QSqlQueryModel::headerData(int section, Qt::Orientation orientation, int role) const

   274 {

   275     Q_D(const QSqlQueryModel);

   276     if (orientation == Qt::Horizontal) {

   277         QVariant val = d->headers.value(section).value(role);

   278         if (role == Qt::DisplayRole && !val.isValid())

   279             val = d->headers.value(section).value(Qt::EditRole);

   280         if (val.isValid())

   281             return val;

   282         if (role == Qt::DisplayRole && d->rec.count() > section)

   283             return d->rec.fieldName(section);

   284     }

   285     return QAbstractItemModel::headerData(section, orientation, role);

   286 }

   287 

   288 /*!

   289     This virtual function is called whenever the query changes. The

   290     default implementation does nothing.

   291 

   292     query() returns the new query.

   293 

   294     \sa query(), setQuery()

   295  */

   296 void QSqlQueryModel::queryChange()

   297 {

   298     // do nothing

   299 }

   300 

   301 /*!

   302     Resets the model and sets the data provider to be the given \a

   303     query. Note that the query must be active and must not be

   304     isForwardOnly().

   305 

   306     lastError() can be used to retrieve verbose information if there

   307     was an error setting the query.

   308 

   309     \sa query(), QSqlQuery::isActive(), QSqlQuery::setForwardOnly(), lastError()

   310 */

   311 void QSqlQueryModel::setQuery(const QSqlQuery &query)

   312 {

   313     Q_D(QSqlQueryModel);

   314     QSqlRecord newRec = query.record();

   315     bool columnsChanged = (newRec != d->rec);

   316     bool hasQuerySize = query.driver()->hasFeature(QSqlDriver::QuerySize);

   317 

   318     if (d->colOffsets.size() != newRec.count() || columnsChanged)

   319         d->initColOffsets(newRec.count());

   320 

   321     bool mustClearModel = d->bottom.isValid();

   322     if (mustClearModel) {

   323         d->atEnd = true;

   324         beginRemoveRows(QModelIndex(), 0, qMax(d->bottom.row(), 0));

   325         d->bottom = QModelIndex();

   326     }

   327 

   328     d->error = QSqlError();

   329     d->query = query;

   330     d->rec = newRec;

   331    

   332     if (mustClearModel)

   333         endRemoveRows();

   334     

   335     d->atEnd = false;    

   336 

   337     if (columnsChanged)

   338         reset();

   339 

   340     if (!query.isActive() || query.isForwardOnly()) {

   341         d->atEnd = true;

   342         d->bottom = QModelIndex();

   343         if (query.isForwardOnly())

   344             d->error = QSqlError(QLatin1String("Forward-only queries "

   345                                                "cannot be used in a data model"),

   346                                  QString(), QSqlError::ConnectionError);

   347         else

   348             d->error = query.lastError();

   349         return;

   350     }

   351     QModelIndex newBottom;

   352     if (hasQuerySize && d->query.size() > 0) {

   353         newBottom = createIndex(d->query.size() - 1, d->rec.count() - 1);

   354         beginInsertRows(QModelIndex(), 0, qMax(0, newBottom.row()));

   355         d->bottom = createIndex(d->query.size() - 1, columnsChanged ? 0 : d->rec.count() - 1);

   356         d->atEnd = true;

   357         endInsertRows();

   358     } else {

   359         newBottom = createIndex(-1, d->rec.count() - 1);

   360     }

   361     d->bottom = newBottom;

   362 

   363     queryChange();

   364 

   365     // fetchMore does the rowsInserted stuff for incremental models

   366     fetchMore();

   367 }

   368 

   369 /*! \overload

   370 

   371     Executes the query \a query for the given database connection \a

   372     db. If no database is specified, the default connection is used.

   373 

   374     lastError() can be used to retrieve verbose information if there

   375     was an error setting the query.

   376 

   377     Example:

   378     \snippet doc/src/snippets/code/src_sql_models_qsqlquerymodel.cpp 1

   379 

   380     \sa query(), queryChange(), lastError()

   381 */

   382 void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)

   383 {

   384     setQuery(QSqlQuery(query, db));

   385 }

   386 

   387 /*!

   388     Clears the model and releases any acquired resource.

   389 */

   390 void QSqlQueryModel::clear()

   391 {

   392     Q_D(QSqlQueryModel);

   393     d->error = QSqlError();

   394     d->atEnd = true;

   395     d->query.clear();

   396     d->rec.clear();

   397     d->colOffsets.clear();

   398     d->bottom = QModelIndex();

   399     d->headers.clear();

   400 }

   401 

   402 /*!

   403     Sets the caption for a horizontal header for the specified \a role to

   404     \a value. This is useful if the model is used to

   405     display data in a view (e.g., QTableView).

   406 

   407     Returns true if \a orientation is Qt::Horizontal and

   408     the \a section refers to a valid section; otherwise returns

   409     false.

   410 

   411     Note that this function cannot be used to modify values in the

   412     database since the model is read-only.

   413 

   414     \sa data()

   415  */

   416 bool QSqlQueryModel::setHeaderData(int section, Qt::Orientation orientation,

   417                                    const QVariant &value, int role)

   418 {

   419     Q_D(QSqlQueryModel);

   420     if (orientation != Qt::Horizontal || section < 0)

   421         return false;

   422 

   423     if (d->headers.size() <= section)

   424         d->headers.resize(qMax(section + 1, 16));

   425     d->headers[section][role] = value;

   426     emit headerDataChanged(orientation, section, section);

   427     return true;

   428 }

   429 

   430 /*!

   431     Returns the QSqlQuery associated with this model.

   432 

   433     \sa setQuery()

   434 */

   435 QSqlQuery QSqlQueryModel::query() const

   436 {

   437     Q_D(const QSqlQueryModel);

   438     return d->query;

   439 }

   440 

   441 /*!

   442     Returns information about the last error that occurred on the

   443     database.

   444 

   445     \sa query()

   446 */

   447 QSqlError QSqlQueryModel::lastError() const

   448 {

   449     Q_D(const QSqlQueryModel);

   450     return d->error;

   451 }

   452 

   453 /*!

   454    Protected function which allows derived classes to set the value of

   455    the last error that occurred on the database to \a error.

   456 

   457    \sa lastError()

   458 */

   459 void QSqlQueryModel::setLastError(const QSqlError &error)

   460 {

   461     Q_D(QSqlQueryModel);

   462     d->error = error;

   463 }

   464 

   465 /*!

   466     Returns the record containing information about the fields of the

   467     current query. If \a row is the index of a valid row, the record

   468     will be populated with values from that row.

   469 

   470     If the model is not initialized, an empty record will be

   471     returned.

   472 

   473     \sa QSqlRecord::isEmpty()

   474 */

   475 QSqlRecord QSqlQueryModel::record(int row) const

   476 {

   477     Q_D(const QSqlQueryModel);

   478     if (row < 0)

   479         return d->rec;

   480 

   481     QSqlRecord rec = d->rec;

   482     for (int i = 0; i < rec.count(); ++i)

   483         rec.setValue(i, data(createIndex(row, i), Qt::EditRole));

   484     return rec;

   485 }

   486 

   487 /*! \overload

   488 

   489     Returns an empty record containing information about the fields

   490     of the current query.

   491 

   492     If the model is not initialized, an empty record will be

   493     returned.

   494 

   495     \sa QSqlRecord::isEmpty()

   496  */

   497 QSqlRecord QSqlQueryModel::record() const

   498 {

   499     Q_D(const QSqlQueryModel);

   500     return d->rec;

   501 }

   502 

   503 /*!

   504     Inserts \a count columns into the model at position \a column. The

   505     \a parent parameter must always be an invalid QModelIndex, since

   506     the model does not support parent-child relationships.

   507 

   508     Returns true if \a column is within bounds; otherwise returns false.

   509 

   510     By default, inserted columns are empty. To fill them with data,

   511     reimplement data() and handle any inserted column separately:

   512 

   513     \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 23

   514 

   515     \sa removeColumns()

   516 */

   517 bool QSqlQueryModel::insertColumns(int column, int count, const QModelIndex &parent)

   518 {

   519     Q_D(QSqlQueryModel);

   520     if (count <= 0 || parent.isValid() || column < 0 || column > d->rec.count())

   521         return false;

   522 

   523     beginInsertColumns(parent, column, column + count - 1);

   524     for (int c = 0; c < count; ++c) {

   525         QSqlField field;

   526         field.setReadOnly(true);

   527         field.setGenerated(false);

   528         d->rec.insert(column, field);

   529         if (d->colOffsets.size() < d->rec.count()) {

   530             int nVal = d->colOffsets.isEmpty() ? 0 : d->colOffsets[d->colOffsets.size() - 1];

   531             d->colOffsets.append(nVal);

   532             Q_ASSERT(d->colOffsets.size() >= d->rec.count());

   533         }

   534         for (int i = column + 1; i < d->colOffsets.count(); ++i)

   535             ++d->colOffsets[i];

   536     }

   537     endInsertColumns();

   538     return true;

   539 }

   540 

   541 /*!

   542     Removes \a count columns from the model starting from position \a

   543     column. The \a parent parameter must always be an invalid

   544     QModelIndex, since the model does not support parent-child

   545     relationships.

   546 

   547     Removing columns effectively hides them. It does not affect the

   548     underlying QSqlQuery.

   549 

   550     Returns true if the columns were removed; otherwise returns false.

   551  */

   552 bool QSqlQueryModel::removeColumns(int column, int count, const QModelIndex &parent)

   553 {

   554     Q_D(QSqlQueryModel);

   555     if (count <= 0 || parent.isValid() || column < 0 || column >= d->rec.count())

   556         return false;

   557 

   558     beginRemoveColumns(parent, column, column + count - 1);

   559 

   560     int i;

   561     for (i = 0; i < count; ++i)

   562         d->rec.remove(column);

   563     for (i = column; i < d->colOffsets.count(); ++i)

   564         d->colOffsets[i] -= count;

   565 

   566     endRemoveColumns();

   567     return true;

   568 }

   569 

   570 /*!

   571     Returns the index of the value in the database result set for the

   572     given \a item in the model.

   573 

   574     The return value is identical to \a item if no columns or rows

   575     have been inserted, removed, or moved around.

   576 

   577     Returns an invalid model index if \a item is out of bounds or if

   578     \a item does not point to a value in the result set.

   579 

   580     \sa QSqlTableModel::indexInQuery(), insertColumns(), removeColumns()

   581 */

   582 QModelIndex QSqlQueryModel::indexInQuery(const QModelIndex &item) const

   583 {

   584     Q_D(const QSqlQueryModel);

   585     if (item.column() < 0 || item.column() >= d->rec.count()

   586         || !d->rec.isGenerated(item.column()))

   587         return QModelIndex();

   588     return createIndex(item.row(), item.column() - d->colOffsets[item.column()],

   589                        item.internalPointer());

   590 }

   591 

   592 QT_END_NAMESPACE