summaryrefslogtreecommitdiffstats
path: root/src/sql/doc/src/sql-programming.qdoc
blob: 56bb48b27ba9d440c2675c8ca04666e43534b6c5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
/****************************************************************************
**
** Copyright (C) 2016 The Qt Company Ltd.
** Contact: https://www.qt.io/licensing/
**
** This file is part of the documentation of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:FDL$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see https://www.qt.io/terms-conditions. For further
** information use the contact form at https://www.qt.io/contact-us.
**
** GNU Free Documentation License Usage
** Alternatively, this file may be used under the terms of the GNU Free
** Documentation License version 1.3 as published by the Free Software
** Foundation and appearing in the file included in the packaging of
** this file. Please review the following information to ensure
** the GNU Free Documentation License version 1.3 requirements
** will be met: https://www.gnu.org/licenses/fdl-1.3.html.
** $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
    \li \l{Database Classes}
    \li \l{Connecting to Databases}
        \list
        \li \l{SQL Database Drivers}
        \endlist
    \li \l{Executing SQL Statements}
        \list
        \li \l{Data Types for Qt-supported Database Systems}
        \endlist
    \li \l{Using the SQL Model Classes}
    \li \l{Presenting Data in a Table View}
    \li \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,
    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 a QCoreApplication object must be instantiated before
    using any of these classes.
*/

/*!
    \page sql-connecting.html
    \title Connecting to Databases

    \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 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 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 \c 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 sqldatabase/sqldatabase.cpp 28
    \snippet sqldatabase/sqldatabase.cpp 29
    \snippet 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
    \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 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 \c 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 \c false. Here's a
    typical loop that iterates over all the records in order:

    \snippet 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 for use with Qt-supported
    Databases, please refer to \l{Data Types for Qt-supported Database
    Systems} {this table}.

    You can navigate within the dataset 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 available 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 sqldatabase/sqldatabase.cpp 33

    If you navigate within a result set, and use next() and seek()
    only for browsing forward, 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 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 sqldatabase/sqldatabase.cpp 35

    Here's an example of positional binding:

    \snippet 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 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 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 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
    \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 \li QSqlQueryModel
         \li A read-only model based on an arbitrary SQL query.
    \row \li QSqlTableModel
         \li A read-write model that works on a single table.
    \row \li QSqlRelationalTableModel
         \li 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 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 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 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 sqldatabase/sqldatabase.cpp 43

    Here's how to insert a row and populate it:

    \snippet sqldatabase/sqldatabase.cpp 44

    Here's how to delete five consecutive rows:

    \snippet sqldatabase/sqldatabase.cpp 45
    \snippet 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
    \li Without any caching, performance may drop significantly.
    \li 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 \li \inlineimage noforeignkeys.png
         \li \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 relationaltablemodel/relationaltablemodel.cpp 0
    \codeline
    \snippet relationaltablemodel/relationaltablemodel.cpp 1
    \snippet 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
    \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 sqldatabase/sqldatabase_snippet.cpp 17
    \snippet sqldatabase/sqldatabase_snippet.cpp 18
    \snippet sqldatabase/sqldatabase_snippet.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 sqldatabase/sqldatabase_snippet.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{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 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 querymodel/editablesqlmodel.cpp 0
    \codeline
    \snippet querymodel/editablesqlmodel.cpp 1

    The setFirstName() helper function is defined as follows:

    \snippet querymodel/editablesqlmodel.cpp 2

    The setLastName() function is similar. See the
    \l{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{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

    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 an SQL model is as simple as using it with
    any other table model.

    \image qdatawidgetmapper-simple.png

    The \l{books}{Books} example shows how information can
    be presented for easy access by using QDataWidgetMapper and a set of
    simple input widgets.
*/