1 /****************************************************************************

     2 **

     3 ** Copyright (C) 2010 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     bool hasNewData = (newRec != QSqlRecord()) || !query.lastError().isValid();

   318 

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

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

   321 

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

   323     if (mustClearModel) {

   324         d->atEnd = true;

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

   326         d->bottom = QModelIndex();

   327     }

   328 

   329     d->error = QSqlError();

   330     d->query = query;

   331     d->rec = newRec;

   332 

   333     if (mustClearModel)

   334         endRemoveRows();

   335 

   336     d->atEnd = false;

   337 

   338     if (columnsChanged && hasNewData)

   339         reset();

   340 

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

   342         d->atEnd = true;

   343         d->bottom = QModelIndex();

   344         if (query.isForwardOnly())

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

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

   347                                  QString(), QSqlError::ConnectionError);

   348         else

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

   350         return;

   351     }

   352     QModelIndex newBottom;

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

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

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

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

   357         d->atEnd = true;

   358         endInsertRows();

   359     } else {

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

   361     }

   362     d->bottom = newBottom;

   363 

   364     queryChange();

   365 

   366     // fetchMore does the rowsInserted stuff for incremental models

   367     fetchMore();

   368 }

   369 

   370 /*! \overload

   371 

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

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

   374 

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

   376     was an error setting the query.

   377 

   378     Example:

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

   380 

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

   382 */

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

   384 {

   385     setQuery(QSqlQuery(query, db));

   386 }

   387 

   388 /*!

   389     Clears the model and releases any acquired resource.

   390 */

   391 void QSqlQueryModel::clear()

   392 {

   393     Q_D(QSqlQueryModel);

   394     d->error = QSqlError();

   395     d->atEnd = true;

   396     d->query.clear();

   397     d->rec.clear();

   398     d->colOffsets.clear();

   399     d->bottom = QModelIndex();

   400     d->headers.clear();

   401 }

   402 

   403 /*!

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

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

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

   407 

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

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

   410     false.

   411 

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

   413     database since the model is read-only.

   414 

   415     \sa data()

   416  */

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

   418                                    const QVariant &value, int role)

   419 {

   420     Q_D(QSqlQueryModel);

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

   422         return false;

   423 

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

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

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

   427     emit headerDataChanged(orientation, section, section);

   428     return true;

   429 }

   430 

   431 /*!

   432     Returns the QSqlQuery associated with this model.

   433 

   434     \sa setQuery()

   435 */

   436 QSqlQuery QSqlQueryModel::query() const

   437 {

   438     Q_D(const QSqlQueryModel);

   439     return d->query;

   440 }

   441 

   442 /*!

   443     Returns information about the last error that occurred on the

   444     database.

   445 

   446     \sa query()

   447 */

   448 QSqlError QSqlQueryModel::lastError() const

   449 {

   450     Q_D(const QSqlQueryModel);

   451     return d->error;

   452 }

   453 

   454 /*!

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

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

   457 

   458    \sa lastError()

   459 */

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

   461 {

   462     Q_D(QSqlQueryModel);

   463     d->error = error;

   464 }

   465 

   466 /*!

   467     Returns the record containing information about the fields of the

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

   469     will be populated with values from that row.

   470 

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

   472     returned.

   473 

   474     \sa QSqlRecord::isEmpty()

   475 */

   476 QSqlRecord QSqlQueryModel::record(int row) const

   477 {

   478     Q_D(const QSqlQueryModel);

   479     if (row < 0)

   480         return d->rec;

   481 

   482     QSqlRecord rec = d->rec;

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

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

   485     return rec;

   486 }

   487 

   488 /*! \overload

   489 

   490     Returns an empty record containing information about the fields

   491     of the current query.

   492 

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

   494     returned.

   495 

   496     \sa QSqlRecord::isEmpty()

   497  */

   498 QSqlRecord QSqlQueryModel::record() const

   499 {

   500     Q_D(const QSqlQueryModel);

   501     return d->rec;

   502 }

   503 

   504 /*!

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

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

   507     the model does not support parent-child relationships.

   508 

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

   510 

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

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

   513 

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

   515 

   516     \sa removeColumns()

   517 */

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

   519 {

   520     Q_D(QSqlQueryModel);

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

   522         return false;

   523 

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

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

   526         QSqlField field;

   527         field.setReadOnly(true);

   528         field.setGenerated(false);

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

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

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

   532             d->colOffsets.append(nVal);

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

   534         }

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

   536             ++d->colOffsets[i];

   537     }

   538     endInsertColumns();

   539     return true;

   540 }

   541 

   542 /*!

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

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

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

   546     relationships.

   547 

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

   549     underlying QSqlQuery.

   550 

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

   552  */

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

   554 {

   555     Q_D(QSqlQueryModel);

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

   557         return false;

   558 

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

   560 

   561     int i;

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

   563         d->rec.remove(column);

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

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

   566 

   567     endRemoveColumns();

   568     return true;

   569 }

   570 

   571 /*!

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

   573     given \a item in the model.

   574 

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

   576     have been inserted, removed, or moved around.

   577 

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

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

   580 

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

   582 */

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

   584 {

   585     Q_D(const QSqlQueryModel);

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

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

   588         return QModelIndex();

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

   590                        item.internalPointer());

   591 }

   592 

   593 QT_END_NAMESPACE