/****************************************************************************
**
** Copyright (C) 2010 Nokia Corporation and/or its subsidiary(-ies).
** All rights reserved.
** Contact: Nokia Corporation (qt-info@nokia.com)
**
** This file is part of the documentation of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:LGPL$
** No Commercial Usage
** This file contains pre-release code and may not be distributed.
** You may use this file in accordance with the terms and conditions
** contained in the Technology Preview License Agreement accompanying
** this package.
**
** GNU Lesser General Public License Usage
** Alternatively, this file may be used under the terms of the GNU Lesser
** General Public License version 2.1 as published by the Free Software
** Foundation and appearing in the file LICENSE.LGPL included in the
** packaging of this file. Please review the following information to
** ensure the GNU Lesser General Public License version 2.1 requirements
** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** In addition, as a special exception, Nokia gives you certain additional
** rights. These rights are described in the Nokia Qt LGPL Exception
** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
**
** If you have questions regarding the use of this file, please contact
** Nokia at qt-info@nokia.com.
**
**
**
**
**
**
**
**
** $QT_END_LICENSE$
**
****************************************************************************/
/*!
\group database
\title Database Classes
\brief Database related classes, e.g. for SQL databases.
*/
/*!
\page sql-programming.html
\title SQL Programming
\nextpage Connecting to Databases
\brief Database integration for Qt applications.
This overview assumes that you have at least a basic knowledge of
SQL. You should be able to understand simple \c SELECT, \c
INSERT, \c UPDATE, and \c DELETE statements. Although the \l
QSqlTableModel class provides an interface to database browsing
and editing that does not require a knowledge of SQL, a basic
understanding of SQL is highly recommended. A standard text
covering SQL databases is \e {An Introduction to Database Systems}
(7th Ed.) by C. J. Date, ISBN 0201385902.
\section1 Topics:
\list
\o \l{Database Classes}
\o \l{Connecting to Databases}
\list
\o \l{SQL Database Drivers}
\endlist
\o \l{Executing SQL Statements}
\list
\o \l{Recommended Use of Data Types in Databases}
\endlist
\o \l{Using the SQL Model Classes}
\o \l{Presenting Data in a Table View}
\o \l{Creating Data-Aware Forms}
\endlist
\section1 Database Classes
These classes provide access to SQL databases.
\annotatedlist database
The SQL classes are divided into three layers:
\section2 Driver Layer
This comprises the classes QSqlDriver, QSqlDriverCreator<T>,
QSqlDriverCreatorBase, QSqlDriverPlugin, and QSqlResult.
This layer provides the low-level bridge between the specific databases
and the SQL API layer. See \l{SQL Database Drivers} for more information.
\section2 SQL API Layer
These classes provide access to databases. Connections
are made using the QSqlDatabase class. Database
interaction is achieved by using the QSqlQuery class.
In addition to QSqlDatabase and QSqlQuery, the SQL API
layer is supported by QSqlError, QSqlField, QSqlIndex,
and QSqlRecord.
\section2 User Interface Layer
These classes link the data from a database to data-aware widgets.
They include QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel.
These classes are designed to work with Qt's
\l{Model/View Programming}{model/view framework}.
Note that to use any of these classes, a QCoreApplication object
must have been instantiated first.
*/
/*!
\page sql-connecting.html
\title Connecting to Databases
\contentspage SQL Programming
\previouspage SQL Programming
\nextpage Executing SQL Statements
To access a database with QSqlQuery or QSqlQueryModel, create and
open one or more database connections. Database connections are
normally identified by connection name, \e{not} by database name.
You can have multiple connections to the same database.
QSqlDatabase also supports the concept of a \e{default}
connection, which is an unnamed connection. When calling QSqlQuery
or QSqlQueryModel member functions that take a connection name
argument, if you don't pass a connection name, the default
connection will be used. Creating a default connection is
convenient when your application only requires one database
connection.
Note the difference between creating a connection and opening it.
Creating a connection involves creating an instance of class
QSqlDatabase. The connection is not usable until it is opened. The
following snippet shows how to create a \e{default} connection
and then open it:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 26
The first line creates the connection object, and the last line
opens it for use. In between, we initialize some connection
information, including the \l{QSqlDatabase::setDatabaseName()}
{database name}, the \l{QSqlDatabase::setHostName()} {host name},
the \l{QSqlDatabase::setUserName()} {user name}, and the
\l{QSqlDatabase::setPassword()} {password}. In this case, we are
connecting to the MySQL database \c{flightdb} on the host
\c{bigblue}. The \c{"QMYSQL"} argument to
\l{QSqlDatabase::addDatabase()} {addDatabase()} specifies the type
of database driver to use for the connection. The set of database
drivers included with Qt are shown in the table of \l{SQL Database
Drivers#Supported Databases} {supported database drivers}.
The connection in the snippet will be the \e{default} connection,
because we don't pass the second argument to
\l{QSqlDatabase::addDatabase()} {addDatabase()}, which is the
connection name. For example, here we establish two MySQL database
connections named \c{"first"} and \c{"second"}:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 27
After these connections have been initialized, \l{QSqlDatabase::}
{open()} for each one to establish the live connections. If the
\l{QSqlDatabase::} {open()} fails, it returns false. In that case,
call QSqlDatabase::lastError() to get error information.
Once a connection is established, we can call the static function
QSqlDatabase::database() from anywhere with a connection name to
get a pointer to that database connection. If we don't pass a
connection name, it will return the default connection. For
example:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 28
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 29
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 30
To remove a database connection, first close the database using
QSqlDatabase::close(), then remove it using the static method
QSqlDatabase::removeDatabase().
*/
/*!
\page sql-sqlstatements.html
\title Executing SQL Statements
\previouspage Connecting to Databases
\contentspage SQL Programming
\nextpage Using the SQL Model Classes
The QSqlQuery class provides an interface for executing SQL
statements and navigating through the result set of a query.
The QSqlQueryModel and QSqlTableModel classes described in the
next section provide a higher-level interface for accessing
databases. If you are unfamiliar with SQL, you might want to skip
directly to the next section (\l{Using the SQL Model Classes}).
\section2 Executing a Query
To execute an SQL statement, simply create a QSqlQuery object and
call QSqlQuery::exec() like this:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 31
The QSqlQuery constructor accepts an optional QSqlDatabase object
that specifies which database connection to use. In the example
above, we don't specify any connection, so the default connection
is used.
If an error occurs, \l{QSqlQuery::exec()}{exec()} returns false.
The error is then available as QSqlQuery::lastError().
\section2 Navigating the Result Set
QSqlQuery provides access to the result set one record at a time.
After the call to \l{QSqlQuery::exec()}{exec()}, QSqlQuery's
internal pointer is located one position \e{before} the first
record. We must call QSqlQuery::next() once to advance to the
first record, then \l{QSqlQuery::next()}{next()} again repeatedly
to access the other records, until it returns false. Here's a
typical loop that iterates over all the records in order:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 32
The QSqlQuery::value() function returns the value of a field in
the current record. Fields are specified as zero-based indexes.
QSqlQuery::value() returns a QVariant, a type that can hold
various C++ and core Qt data types such as \c int, QString, and
QByteArray. The different database types are automatically mapped
into the closest Qt equivalent. In the code snippet, we call
QVariant::toString() and QVariant::toInt() to convert
variants to QString and \c int.
For an overview of the recommended types used with Qt supported
Databases, please refer to \l{Recommended Use of Data Types in Databases}{this table}.
You can iterate back and forth using QSqlQuery::next(),
QSqlQuery::previous(), QSqlQuery::first(), QSqlQuery::last(), and
QSqlQuery::seek(). The current row index is returned by
QSqlQuery::at(), and the total number of rows in the result set
is avaliable as QSqlQuery::size() for databases that support it.
To determine whether a database driver supports a given feature,
use QSqlDriver::hasFeature(). In the following example, we call
QSqlQuery::size() to determine the size of a result set of
the underlying database supports that feature; otherwise, we
navigate to the last record and use the query's position to tell
us how many records there are.
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 33
If you iterate through a result set only using next() and seek()
with positive values, you can call
QSqlQuery::setForwardOnly(true) before calling exec(). This is an
easy optimization that will speed up the query significantly when
operating on large result sets.
\section2 Inserting, Updating, and Deleting Records
QSqlQuery can execute arbitrary SQL statements, not just
\c{SELECT}s. The following example inserts a record into a table
using \c{INSERT}:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 34
If you want to insert many records at the same time, it is often
more efficient to separate the query from the actual values being
inserted. This can be done using placeholders. Qt supports two
placeholder syntaxes: named binding and positional binding.
Here's an example of named binding:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 35
Here's an example of positional binding:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 36
Both syntaxes work with all database drivers provided by Qt. If
the database supports the syntax natively, Qt simply forwards the
query to the DBMS; otherwise, Qt simulates the placeholder syntax
by preprocessing the query. The actual query that ends up being
executed by the DBMS is available as QSqlQuery::executedQuery().
When inserting multiple records, you only need to call
QSqlQuery::prepare() once. Then you call
\l{QSqlQuery::bindValue()}{bindValue()} or
\l{QSqlQuery::addBindValue()}{addBindValue()} followed by
\l{QSqlQuery::exec()}{exec()} as many times as necessary.
Besides performance, one advantage of placeholders is that you
can easily specify arbitrary values without having to worry about
escaping special characters.
Updating a record is similar to inserting it into a table:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 37
You can also use named or positional binding to associate
parameters to actual values.
Finally, here's an example of a \c DELETE statement:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 38
\section2 Transactions
If the underlying database engine supports transactions,
QSqlDriver::hasFeature(QSqlDriver::Transactions) will return
true. You can use QSqlDatabase::transaction() to initiate a
transaction, followed by the SQL commands you want to execute
within the context of the transaction, and then either
QSqlDatabase::commit() or QSqlDatabase::rollback(). When
using transactions you must start the transaction before you
create your query.
Example:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 39
Transactions can be used to ensure that a complex operation is
atomic (for example, looking up a foreign key and creating a
record), or to provide a means of canceling a complex change in
the middle.
\omit
It would be useful to mention transactions, and the fact that
some databases don't support them.
\endomit
*/
/*!
\page sql-model.html
\title Using the SQL Model Classes
\previouspage Executing SQL Statements
\contentspage SQL Programming
\nextpage Presenting Data in a Table View
In addition to QSqlQuery, Qt offers three higher-level classes
for accessing databases. These classes are QSqlQueryModel,
QSqlTableModel, and QSqlRelationalTableModel.
\table
\row \o QSqlQueryModel
\o A read-only model based on an arbitrary SQL query.
\row \o QSqlTableModel
\o A read-write model that works on a single table.
\row \o QSqlRelationalTableModel
\o A QSqlTableModel subclass with foreign key support.
\endtable
These classes derive from QAbstractTableModel (which in turn
inherits from QAbstractItemModel) and make it easy to present
data from a database in an item view class such as QListView and
QTableView. This is explained in detail in the \l{Presenting Data
in a Table View} section.
Another advantage of using these classes is that it can make your
code easier to adapt to other data sources. For example, if you
use QSqlTableModel and later decide to use XML files to store
data instead of a database, it is essentially just a matter of
replacing one data model with another.
\section2 The SQL Query Model
QSqlQueryModel offers a read-only model based on an SQL query.
Example:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 40
After setting the query using QSqlQueryModel::setQuery(), you can
use QSqlQueryModel::record(int) to access the individual records.
You can also use QSqlQueryModel::data() and any of the other
functions inherited from QAbstractItemModel.
There's also a \l{QSqlQueryModel::setQuery()}{setQuery()}
overload that takes a QSqlQuery object and operates on its result
set. This enables you to use any features of QSqlQuery to set up
the query (e.g., prepared queries).
\section2 The SQL Table Model
QSqlTableModel offers a read-write model that works on a single
SQL table at a time.
Example:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 41
QSqlTableModel is a high-level alternative to QSqlQuery for
navigating and modifying individual SQL tables. It typically
results in less code and requires no knowledge of SQL syntax.
Use QSqlTableModel::record() to retrieve a row in the table, and
QSqlTableModel::setRecord() to modify the row. For example, the
following code will increase every employee's salary by 10 per
cent:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 42
You can also use QSqlTableModel::data() and
QSqlTableModel::setData(), which are inherited from
QAbstractItemModel, to access the data. For example, here's how
to update a record using
\l{QSqlTableModel::setData()}{setData()}:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 43
Here's how to insert a row and populate it:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 44
Here's how to delete five consecutive rows:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 45
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 46
The first argument to QSqlTableModel::removeRows() is the index
of the first row to delete.
When you're finished changing a record, you should always call
QSqlTableModel::submitAll() to ensure that the changes are
written to the database.
When and whether you actually \e need to call submitAll() depends
on the table's \l{QSqlTableModel::editStrategy()}{edit strategy}.
The default strategy is QSqlTableModel::OnRowChange, which
specifies that pending changes are applied to the database when
the user selects a different row. Other strategies are
QSqlTableModel::OnManualSubmit (where all changes are cached in
the model until you call submitAll()) and
QSqlTableModel::OnFieldChange (where no changes are cached).
These are mostly useful when QSqlTableModel is used with a view.
QSqlTableModel::OnFieldChange seems to deliver the promise that
you never need to call submitAll() explicitly. There are two
pitfalls, though:
\list
\o Without any caching, performance may drop significantly.
\o If you modify a primary key, the record might slip through
your fingers while you are trying to populate it.
\endlist
\section2 The SQL Relational Table Model
QSqlRelationalTableModel extends QSqlTableModel to provide
support for foreign keys. A foreign key is a 1-to-1 mapping
between a field in one table and the primary key field of another
table. For example, if a \c book table has a field called \c
authorid that refers to the author table's \c id field, we say
that \c authorid is a foreign key.
\table
\row \o \inlineimage noforeignkeys.png
\o \inlineimage foreignkeys.png
\endtable
The screenshot on the left shows a plain QSqlTableModel in a
QTableView. Foreign keys (\c city and \c country) aren't resolved
to human-readable values. The screenshot on the right shows a
QSqlRelationalTableModel, with foreign keys resolved into
human-readable text strings.
The following code snippet shows how the QSqlRelationalTableModel
was set up:
\snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 0
\codeline
\snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 1
\snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 2
See the QSqlRelationalTableModel documentation for details.
*/
/*!
\page sql-presenting.html
\title Presenting Data in a Table View
\previouspage Using the SQL Model Classes
\contentspage SQL Programming
\nextpage Creating Data-Aware Forms
The QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel
classes can be used as a data source for Qt's view classes such
as QListView, QTableView, and QTreeView. In practice, QTableView
is by far the most common choice, because an SQL result set is
essentially a two-dimensional data structure.
\image relationaltable.png A table view displaying a QSqlTableModel
The following example creates a view based on an SQL data model:
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 17
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 18
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 19
If the model is a read-write model (e.g., QSqlTableModel), the
view lets the user edit the fields. You can disable this by
calling
\snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 20
You can use the same model as a data source for multiple views.
If the user edits the model through one of the views, the other
views will reflect the changes immediately. The
\l{sql/tablemodel}{Table Model} example shows how it works.
View classes display a header at the top to label the columns. To
change the header texts, call
\l{QAbstractItemModel::setHeaderData()}{setHeaderData()} on the
model. The header's labels default to the table's field names.
For example:
\snippet examples/sql/relationaltablemodel/relationaltablemodel.cpp 3
QTableView also has a vertical header on the left with numbers
identifying the rows. If you insert rows programmatically using
QSqlTableModel::insertRows(), the new rows will be marked with an
asterisk (*) until they are submitted using
\l{QSqlTableModel::submitAll()}{submitAll()} or automatically
when the user moves to another record (assuming the
\l{QSqlTableModel::EditStrategy}{edit strategy} is
QSqlTableModel::OnRowChange).
\image insertrowinmodelview.png Inserting a row in a model
Likewise, if you remove rows using
\l{QSqlTableModel::removeRows()}{removeRows()}, the rows will be
marked with an exclamation mark (!) until the change is
submitted.
The items in the view are rendered using a delegate. The default
delegate, QItemDelegate, handles the most common data types (\c
int, QString, QImage, etc.). The delegate is also responsible for
providing editor widgets (e.g., a combobox) when the user starts
editing an item in the view. You can create your own delegates by
subclassing QAbstractItemDelegate or QItemDelegate. See
\l{Model/View Programming} for more information.
QSqlTableModel is optimized to operate on a single table at a
time. If you need a read-write model that operates on an
arbitrary result set, you can subclass QSqlQueryModel and
reimplement \l{QAbstractItemModel::flags()}{flags()} and
\l{QAbstractItemModel::setData()}{setData()} to make it
read-write. The following two functions make fields 1 and 2 of a
query model editable:
\snippet examples/sql/querymodel/editablesqlmodel.cpp 0
\codeline
\snippet examples/sql/querymodel/editablesqlmodel.cpp 1
The setFirstName() helper function is defined as follows:
\snippet examples/sql/querymodel/editablesqlmodel.cpp 2
The setLastName() function is similar. See the
\l{sql/querymodel}{Query Model} example for the complete source code.
Subclassing a model makes it possible to customize it in many
ways: You can provide tooltips for the items, change the
background color, provide calculated values, provide different
values for viewing and editing, handle null values specially, and
more. See \l{Model/View Programming} as well as the \l
QAbstractItemView reference documentation for details.
If all you need is to resolve a foreign key to a more
human-friendly string, you can use QSqlRelationalTableModel. For
best results, you should also use QSqlRelationalDelegate, a
delegate that provides combobox editors for editing foreign keys.
\image relationaltable.png Editing a foreign key in a relational table
The \l{sql/relationaltablemodel}{Relational Table Model} example
illustrates how to use QSqlRelationalTableModel in conjunction with
QSqlRelationalDelegate to provide tables with foreign key
support.
*/
/*!
\page sql-forms.html
\title Creating Data-Aware Forms
\previouspage Presenting Data in a Table View
\contentspage SQL Programming
Using the SQL models described above, the contents of a database can
be presented to other model/view components. For some applications,
it is sufficient to present this data using a standard item view,
such as QTableView. However, users of record-based applications often
require a form-based user interface in which data from a specific
row or column in a database table is used to populate editor widgets
on a form.
Such data-aware forms can be created with the QDataWidgetMapper class,
a generic model/view component that is used to map data from a model
to specific widgets in a user interface.
QDataWidgetMapper operates on a specific database table, mapping items
in the table on a row-by-row or column-by-column basis. As a result,
using QDataWidgetMapper with a SQL model is as simple as using it with
any other table model.
\image qdatawidgetmapper-simple.png
The \l{demos/books}{Books} demonstration shows how information can
be presented for easy access by using QDataWidgetMapper and a set of
simple input widgets.
*/