doc/src/sql-programming/sql-programming.qdoc
branchRCL_3
changeset 7 3f74d0d4af4c
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/doc/src/sql-programming/sql-programming.qdoc	Thu Apr 08 14:19:33 2010 +0300
@@ -0,0 +1,616 @@
+/****************************************************************************
+**
+** 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.
+*/