Home | All Classes | Main Classes | Annotated | Grouped Classes | Functions |

[Prev: Creating Custom Widgets] [Home] [Next: Customizing and Integrating Qt Designer]

## Creating Database Applications

This chapter shows you how to use Qt's data-aware widgets from within Qt Designer. It demonstrates `INSERT`, `UPDATE` and `DELETE` in both QDataTables (tables) and QDataBrowsers (forms). It also shows how to code Master-Detail relationships and Drilldown. A simple approach to foreign key handling is presented here; a more sophisticated approach is shown in the online SQL module documentation.

If you wish to run the examples or create your own applications using these widgets you need access to an SQL database and a Qt database driver that can connect to the database. At the time of writing the drivers that Qt supports are QODBC3 (Open Database Connectivity), QOCI8 (Oracle), QPSQL7 (PostgreSQL 6 and 7) and QMYSQL3 (MySQL).

Although you can use the Qt data-aware widgets to browse and edit data in SQL databases without having to write any SQL, a basic understanding of SQL is highly recommended. We assume that you have some familiarity with `SELECT`, `INSERT`, `UPDATE` and `DELETE` statements. We also assume a basic understanding of the concepts of normalisation and of primary and foreign keys. A standard text covering SQL databases is An Introduction to Database Systems (7th ed.) by C. J. Date, ISBN 0201385902.

In the following text we describe the creation of a 'book' database application. The application demonstrates how to use QDataTables including in-place record editing and how to set up master-detail relationships between QDataTables. It also explains how to drill down from a QDataTable to another widget, for example, to a QDataBrowser or a QDataView and how to perform record editing in a QDataBrowser. A great deal of functionality is available from the classes directly in Qt Designer although subclassing is always available for finer control. If you want to build the 'book' examples you will need to create the example schema on your database.

> The Book Application

> The Example Schema

> Note that the examples in this chapter all use the tables, views and records which are defined in the `qt/tools/designer/examples/book/book.sql` file. This file has been tested with PostgreSQL 6 and PostgreSQL 7. You may need to modify the SQL in this file to recreate the example database on your own system.

> Schema `CREATE TABLE` Statements

> The 'book' table is simplified for the purposes of the example. It can only relate a book to a single author (authorid) and lacks an ISBN field. The 'sequence' table is used for generating unique index values for the example tables. Note that SQL databases often provide their own method for creating sequences (for example, using the `CREATE SEQUENCE` command) which is very likely to be a more optimal solution. For the sake of portability the examples will use a 'sequence' table which will work with the vast majority of SQL databases.

### Setting Up Database Connections

There are two aspects of database connections that we must consider. Firstly the connection we wish to use within Qt Designer itself, and secondly the connection we wish to use in the applications that we create.

#### Setting Up Qt Designer's Connections

> Database Connections Dialog

Choose Project|Database Connections from the menu bar. The Database Connections dialog will appear. Click New Connection. For applications that use a single database it will probably be most convenient to use the default connection name of '(default)'. If you use more than one database then each one must be given a unique name. A driver must be chosen from the Driver combo box. The database name may be available in the Database Name combo box or may have to be typed in. The database name, username, password and hostname should be provided by your database system administrator. When the Connection information has been completed click Connect. If the connection is made the connection name will appear in the list box on the left hand side of the dialog. You can now close the dialog; the connection settings will remain in effect until you change or delete them or exit from Qt Designer.

Qt Designer can remember database connection settings in `qmake` project files. Create a new project, e.g. click File|New, then click the 'C++ Project' icon to invoke the Project Settings dialog. Click the ellipsis button to invoke the Save As dialog; navigate to the project's directory (creating it if necessary). Make sure you're in the project's directory, then enter a project name of 'book.pro'. Click the Save button to return to the Project Settings dialog, then click OK. Next time you start Qt Designer instead of opening individual `.ui` files open the `.pro` project file instead and Qt Designer will automatically reload the project's connection settings. To activate the connection click Project|Database Connections. The connections previously saved with the project will be listed in the left hand list box. Click the connection you wish to use and then click Connect. This connection will be used from now on, e.g. for previewing QDataTables. Opening a project file also causes Qt Designer to load in the list of forms associated with the project into the Form List window. In most of the explanation that follows we will assume that you use project files and have clicked Connect so that there is always a connection available when you work in Qt Designer.

#### Setting Up Connections for Applications

The applications you create must make their own connections to the SQL database. `createConnections()` function

    bool createConnections()
    {
        // create the default database connection
        QSqlDatabase *defaultDB = QSqlDatabase::addDatabase( "QPSQL7" );
        if ( ! defaultDB ) {
            qWarning( "Failed to connect to driver" );
            return FALSE;
        }
        defaultDB->setDatabaseName( "book" );
        defaultDB->setUserName( "bookuser" );
        defaultDB->setPassword( "bookpw" );
        defaultDB->setHostName( "bookhost" );
        if ( ! defaultDB->open() ) {
            qWarning( "Failed to open books database: " +
                      defaultDB->lastError().driverText() );
            qWarning( defaultDB->lastError().databaseText() );
            return FALSE;
        }

        return TRUE;
    }

We call `addDatabase()` passing it the name of the driver we wish to use. We then set the connection information by calling the `set`... functions. Finally we attempt to open the connection. If we succeed we return TRUE, otherwise we output some error information and return FALSE. From `qt/tools/designer/examples/book/book1/main.cpp`

        int main( int argc, char *argv[] )
        {
            QApplication app( argc, argv );

            if ( ! createConnections() )
                return 1;

            BookForm bookForm;
            app.setMainWidget( &bookForm );
            bookForm.show();

            return app.exec();
        }

All the examples presented in this chapter call `createConnections()` after creating the QApplication object in their `main.cpp` file and make use of the default connection. If you need to connect to multiple databases use the two-argument form of `addDatabase()`, passing it both the name of the driver and a unique identifier. This is explained further in the Qt SQL Module documentation.

You do not need to keep a reference to database connections. If you use a single database connection, this becomes the default connection and database functions will use this connection automatically. We can always get a pointer to any of our connections by calling `QSqlDatabase::database()`.

If you create a `main.cpp` file using Qt Designer, this file will not include `createConnections()`. We do not include this function because it needs the username and password for the database connection, and you may prefer to handle these differently from our simple example function. As a result, applications that preview correctly in Qt Designer will not run unless you implement your own database connections function.

### Using QDataTable

QDataTables may be placed on any form to provide browsing of database tables and views. QDataTables can also be used to update or delete records in-place, i.e. inside the cells themselves. Inserting records via a QDataTable usually requires connecting to the `primeInsert()` signal, so that we can generate primary keys for example, or provide default values. If we wish to present records using a form view (perhaps combining data from several tables and views) we might use several QDataBrowsers and QDataViews.

#### Quickly Viewing a Database Table

This example, along with all the other examples in this chapter, has the project name 'book' and uses the database created by the `book.sql` script. As we work through the chapter we will build the 'book' application step by step. Create or copy the `qt/tools/designer/examples/book/book1/main.cpp` file shown earlier. The project file for this first example is `qt/tools/designer/examples/book/book1/book.pro`. Start a new project by clicking File|New, then click the 'C++ Project' icon to invoke the Project Settings dialog. Click the ellipsis button to invoke the Save As dialog; navigate to the project's directory (creating it if necessary). Make sure you're in the project's directory, then enter a project name of 'book.pro'. Click the Save button to return to the Project Settings dialog, then click OK. Now click Project|Database Connections. Fill in the connection information appropriate to your database then press Connect. The connection name should now appear in the left hand list box. (If this doesn't happen you'll need to contact your database systems administrator for help.) Close the dialog.

We will now create a new form with a QDataTable that's connected to one of our database tables.

Click File|New. The New File dialog presents us with a number of form templates to choose from. Choose the 'Dialog' form and click OK. Now click File|Save. You will be prompted for a filename, call it `book.ui`.

##### Setting up a QDataTable

To place a QDataTable widget on the form either click Tools|Views|QDataTable or click the QDataTable toolbar button. Click on the form and the SQL Table Wizard will appear.

1. The Database Connection and Table wizard page is used to set up a connection if one doesn't exist and to choose the table or view for the QDataTable. (See Setting Up Qt Designer's Connections.)

   Click the connection you wish to use, listed in the left hand list box, e.g. "(default)". The available tables and views will appear in the right hand Table list box. Click the 'author' table and then click the Next button.

2. The Displayed Fields wizard page provides a means of selecting which fields should be displayed in the QDataTable and in what order. By default all fields except the primary key (if there is one) are in the Displayed Fields list box. The left- and right-pointing blue arrow buttons can be used to move fields between the Displayed Fields and the Available Fields list boxes. The blue up and down pointing arrow buttons are used to select the display order of the displayed fields.

   The default settings are the ones we want so simply click Next.

3. The Table Properties wizard page provides convenient access to some of the database-related properties of the QDataTable.

   Make sure the Confirm Deletes checkbox is checked, then click Next.

4. The SQL wizard page is used to set the QDataTable's Filter and Sort properties. The Filter is an SQL `WHERE` clause (without the word 'WHERE'). For example, to only list authors whose surnames begin with 'P', we would enter `title LIKE 'P%'`. We'll leave the filter empty. The Available Fields list box lists all the fields. The Sort By list box lists the fields that the QDataTable is to sort by and the direction of their sorting (ASCending or DESCending). The left and right blue arrows are used to move fields between the two list boxes. The up and down blue arrows move fields up and down within the Sort By list box. The ASC or DESC setting is changed with the 'sort order' toolbar button.

   Move the surname and forename fields into the Sort By list box and click Next.

5. The Finish wizard page gives us the opportunity to go back and change any of our settings. We will be able to change them later through the QDataTable's properties so we can finish with the wizard.

   Click Finish.

The table will appear on the form with each column labelled with a default column name. If you wish to change the settings then most of them are available in the property window. The display names, the fields they are based upon, and the order of appearance of the columns can be changed using the Edit Table dialog (explained later) by right clicking the QDataTable and left clicking Edit.

##### Laying out the Form

Click on the form and click the Lay Out Vertically toolbar button. Now click Preview|Preview Form; the form will run and the table will automatically display all the records.

To turn the form we've created into an executable application we must add the `main.cpp` file to the project file and make the project. We should also do some renaming to make things easier to understand.

1. Click on the form and change its name to 'BookForm' and its caption to 'Book'. Click on the QDataTable and change its name to 'AuthorDataTable'.

2. Click File|Save All.

3. Open the project file, e.g. `book.pro`, in a plain text editor and add the line: `SOURCES += main.cpp` at the end of the file.

4. Run `qmake` to generate the make file, e.g. `qmake -o Makefile book.pro`, then make and run the `book` program.

This example shows how easy it is to use QDataTable to show the contents of a database table or view. You can use the application we've just built to update and delete author records. In the examples that follow we will cover insertions, setting up master-detail relationships, drilldown and foreign key lookups.

> A Note on Foreign Keys

> In most relational databases tables contain fields which are foreign keys into other tables. In our 'book' database example the authorid in the book table is a foreign key into the author table. When we present a form to the end user we do not usually want the foreign key itself to be visible but rather the text associated with it. Thus, we would want the author's name to appear rather than the author id when we show book information. In many databases, this can be achieved by using a view. See your database's documentation for details.

#### Inserting Records in QDataTables

Record insertion into a relational database usually requires the generation of a primary key value which uniquely identifies the record in the table. Also we often want to create default values for some fields to minimize the user's work. We will create a slot to capture the QDataTables `primeInsert()` signal and populate the QSqlRecord insertion buffer with a unique primary key.

1. Click Edit|Slots to invoke the Edit Slots dialog. Click New Slot, then enter the slot name `primeInsertAuthor(QSqlRecord*)` into the Slot Properties' Slot line edit box. Click OK.

2. Click the Connect Signals/Slots toolbar button, then click the AuthorDataTable, drag to the form and release the mouse. The Edit Connections dialog will now appear. Click the `primeInsert()` signal and then the `primeInsertAuthor()` slot to make the connection. Now click OK.

3. Click the Source tab of the Object Hierarchy window (click Window|Object Hierarchy to make the window visible if necessary). Click the `primeInsertAuthor()` slot and an editor window will appear.

4. We must change the `BookForm::primeInsertAuthor()` slot to specify the parameter name and perform the necessary action:

           void BookForm::primeInsertAuthor( QSqlRecord * buffer )
           {
               QSqlQuery query;
               query.exec( "UPDATE sequence SET sequence = sequence + 1 WHERE tablename='author';" );
               query.exec( "SELECT sequence FROM sequence WHERE tablename='author';" );
               if ( query.next() ) {
                   buffer->setValue( "id", query.value( 0 ) );
               }
           }

   A QSqlQuery object is used to increment and retrieve a unique 'sequence' number for the author table. The signal passed us a pointer to the insertion buffer and we then put the value we've retrieved, i.e. the next sequence number, into the buffer's id field. (Again, note that SQL databases often support a native 'sequence' function. The method used here is inappropriate for production systems, and is for example purposes only. See your database's documentation for details on how to generate unique keys in code. In many cases, the database can generate them automatically, or the database may provide a special syntax for dealing with sequences.)

If we rebuild the application it will now support `INSERT` as well as `UPDATE` and `DELETE`. We could easily have added additional code to insert default values, e.g. today's date into a date field, if necessary.

Browsing is supported by clicking records and by using the arrow keys. Once a record is active (highlighted) we can edit the it. Press the Insert key to `INSERT` a new record; press F2 to `UPDATE` the current record; press the Del key to `DELETE` the current record. All these operations take place immediately. Users can be given the opportunity to confirm their edits by setting the QDataTable's confirmEdits property to True. If the confirmEdits property is True then user confirmation will be required for all insertions, updates and deletes. For finer control you can set the confirmInsert, confirmUpdate and confirmDelete properties individually.

> QDataTable User Interface Interaction

> The default user-interface behaviour for QDataTables is as follows:

> - Users can move to records by clicking the scrollbar and clicking records with the mouse. They can also use the keyboard's navigation keys, e.g. Left Arrow, Right Arrow, Up Arrow, Down Arrow, Page Up, Page Down, Home and End.

> - `INSERT` is initiated by right-clicking the record and clicking Insert or by pressing the Ins (Insert) key. The user moves between fields using Tab and Shift+Tab. The `INSERT` will take place if the user presses Enter or Tabs off the last field. If autoEdit is TRUE the insert will take place if the user navigates to another record. `INSERT` is cancelled by pressing Esc (Escape). If autoEdit is FALSE navigating to another record also cancels the `INSERT`. Setting confirmInsert to TRUE will require the user to confirm each `INSERT`.

> - `UPDATE` is initiated by right-clicking the record and clicking Update or by pressing F2. The update will take place if the user presses Enter or Tabs off the last field. If autoEdit is TRUE the update will take place if the user navigates to another record. `UPDATE` is cancelled by pressing Esc. If autoEdit is FALSE navigating to another record also cancels the `UPDATE`. Setting confirmUpdate to TRUE will require the user to confirm each `UPDATE`.

> - `DELETE` is achieved by right-clicking the record and clicking Delete or by pressing the Del (Delete) key. Setting confirmDelete to TRUE will require the user to confirm each `DELETE`.

> You can change this default behaviour programmatically if required.

#### Relating Two Tables Together (Master-Detail)

Databases often have pairs of tables that are related. For example, an invoice table might list the numbers, dates and customers for invoices, but not the actual invoice items, which an invoice item table might store. In the 'book' application we wish to have a QDataTable that we can use to browse through the authors table and a second QDataTable to show the books they've written.

Open the book project if it isn't already open Qt Designer. We will modify this project to show two QDataTables that relate the author table to the book table.

1. Click the author QDataTable and then click the Break Layout toolbutton.

2. Resize the QDataTable so that it only occupies the top half of the form.

3. Now click on the QDataTable toolbutton and click on the bottom half of the form. The SQL Table Wizard will appear. (This Wizard is explained in Quickly Viewing a Database Table.)

   1. Click the connection you're using and click the book table. Click the Next button.

   2. Since we do not want them visible, make sure the authorid and id fields are moved to the Available Fields list box by using the arrow buttons. Move the title field to the top of the Displayed Fields, and move the price field above the notes field. Click the Next button.

   3. On the Table Properties page click the Read Only checkbox then click the Next button.

   4. On the SQL page we will leave the Filter (`WHERE` clause) empty. Move the title field to the Sort By list box and click Next. Now click Finish.

   5. Change this QDataTable's name to "BookDataTable".

4. Shift+Click the top QDataTable so that both QDataTables are selected and then click the Lay Out Vertically (in Splitter) toolbar button.

5. Click on the form and click the Lay Out Vertically toolbar button.

Run the form by clicking Preview|Preview Form. All the authors are displayed in the top QDataTable and all the books are displayed in the bottom QDataTable. However we only want the books of the currently selected author showing in the bottom QDataTable. We will deal with this by filtering the records in the book table according to the author selected in the author table.

> Using the Table Editor

> > Edit Table Dialog

> QDataTables are created and set up using the SQL Table Wizard. Like any other Qt Designer widget their properties may be changed in the Properties window. Some of the column and row based properties can also be be changed using the Edit Table dialog. This dialog is invoked by right clicking the QDataTable and left clicking the Edit menu item. The right hand half of the Edit Table dialog is where we choose the fields we wish to display, their order and their labels. The procedure for creating columns is as follows:

> 1. Click the New Column button.

> 2. Drop down the Field combobox to list the available fields.

> 3. Click the field you wish to include at this point.

> 4. Optionally edit the Label if the default isn't appropriate.

> 5. Optionally click the Pixmap ellipsis (...) button to choose a pixmap to be displayed to the left of the column's label. (The ellipsis button appears when you click in the Value part of the Properties list by a pixmap or iconSet property.)

> Repeat the steps listed above for each column you wish to add. Once all the fields have been added you can change their ordering by using the blue up and down arrow buttons. At any point you can press Apply to see how the table will look. Finally click the OK button to save the properties you have set. You can always return to the table editor to change these settings later.

##### Filtering One QDataTable by Another

To filter the book table's records we need to capture the author QDataTable's `currentChanged()` signal and change the BookDataTable's filter accordingly.

1. Click Edit|Slots. In the Edit Slots dialog click New Slot and enter a slot name of `newCurrentAuthor(QSqlRecord*)`. Click OK.

2. Click Connect Signals/Slots, then click the AuthorDataTable QDataTable and drag to the form; release the mouse on the form. The Edit Connections dialog will appear. Click the `currentChanged()` signal and the `newCurrentAuthor` slot. Click OK.

3. Click the Source tab of the Object Hierarchy window (click Window|Object Hierarchy to make the window visible if necessary). Click the `newCurrentAuthor()` slot and an editor window will appear.

4. We must change the `BookForm::newCurrentAuthor()` slot to specify the parameter name and perform the necessary action:

           void BookForm::newCurrentAuthor( QSqlRecord *author )
           {
               BookDataTable->setFilter( "authorid=" + author->value( "id" ).toString() );
               BookDataTable->refresh();
           }

   All that's required now is to change the BookDataTable's filter and refresh the QDataTable to show the results of the filter.

##### Preparing the Interface for Drilldown

We can now browse and edit authors and see their books in the BookDataTable. In the next section we explore QDataBrowser, which will allow us to drill down to a dialog through which we can edit books. For now we will add some buttons to the main BookForm which we will use to invoke the book editing dialog.

1. Click the form, then click the Break Layout toolbar button. Resize the form to make room for some buttons at the bottom.

2. Add two buttons to the bottom of the form. Change their names and labels to the following:

   - EditPushButton -- &Edit Books

   - QuitPushButton -- &Quit

   Hold down the Shift key and Click both buttons (i.e. Shift+Click the buttons) and click the Lay Out Horizontally toolbar button. Click the form and click the Lay Out Vertically toolbar button.

3. We will provide the Quit button with functionality now and work on the rest shortly. Click Connect Signals/Slots, then click the Quit button and drag to the form; release the mouse on the form. The Edit Connections dialog will appear. Click the `clicked()` signal and the `accept()` slot. Click OK.

### Using QDataBrowser and QDataView

> The Book Application's Edit Books Dialog

#### Drilling Down to a Form using QDataBrowser

##### Setting up a QDataBrowser

We will now create a new form to allow users to edit book records. Click the New toolbar button, click the Dialog template from the New File dialog and click OK. Change the name of the form to EditBookForm and its caption to 'Edit Books'. Click the Save toolbar button and call the file `editbook.ui`. Now that we have the form we can add a QDataBrowser to show the book records.

1. Click the Data Browser toolbar button, then click the form. The Data Browser Wizard will appear.

2. The Database Connection and Table wizard page is used to set up a connection if one doesn't exist and to choose the table or view for the QDataBrowser. (See Setting Up Qt Designer's Connections.)

   Click the connection you wish to use, listed in the Connection list box, e.g. "(default)". The available tables and views will appear in the Table list box. Click the book table and then click the Next button.

3. The Displayed Fields wizard page provides a means of selecting which fields should be displayed in the QDataBrowser and in what order. By default all fields except the primary key (if there is one) are in the right hand Displayed Fields list box. The left and right blue arrow buttons can be used to move fields between the Displayed Fields and the Available Fields list boxes. The blue up and down arrow buttons are used to select the display order of the displayed fields.

   We don't want to see the authorid foreign key field on the form, so move it to the Available Fields list box. Also, move the title field to the top of the Displayed Fields list. Click the Next button.

4. The Navigation and Editing wizard page allows us to choose which navigation and editing buttons should appear on the form.

   We will accept the defaults and simply click the Next button.

5. The SQL wizard page is used to set the QDataBrowser's Filter and Sort properties. The Filter is an SQL `WHERE` clause (without the word 'WHERE'). For example, to only list books that cost less than 50 (of some currency, e.g. dollars), we would enter `price < 50`. We will leave the filter empty. The Available Fields list box lists all the fields. The Sort By list box lists the fields that the QDataBrowser is to sort by and the direction of their sorting (ASCending or DESCending). The left and right blue arrows are used to move fields between the two list boxes. The up and down blue arrows move fields up and down within the Sort By list box. The ASC or DESC setting is changed with the sort order button.

   Move the title field into the Sort By list box and click Next.

6. The Layout wizard page is used to specify the initial layout of the form.

   Change the Number of Columns to 1, then click Next. Now click Finish.

7. The QDataBrowser will now appear on the form. Resize the form to make it shorter. Click the QDataBrowser then click the Break Layout toolbar button. Click the buttons then click the Break Layout toolbar button. Add another button called 'PushButtonClose' with the text '&Close' and place it to the right of the Delete button.

8. Shift+Click the Insert, Update, Delete and Close buttons, then click the Lay Out Horizontally toolbar button. Click the QDataBrowser, then click the Lay Out in a Grid toolbar button. Finally click the form and click the Lay Out Vertically toolbar button. Now click the QDataBrowser and rename it 'BookDataBrowser'.

9. Qt Designer will generate the necessary code to make the browser operational (including generating the appropriate cursor, sort and filter code).

   For finer control over the form, we will be creating our own database cursor. Therefore, set the BookDataBrowser's frameworkCode property to FALSE in the Properties window to prevent Qt Designer from generating redundant code for the cursor.

> QDataBrowser User Interface Interaction

> The user-interface behaviour for QDataBrowsers is created by connecting slots and signals. The slots provided are:

> - `insert()`, `update()` and `del()` for editing;

> - `first()`, `next()`, `prev()`, and `last()` for navigation;

> - `refresh()` to refresh the cursor from the database;

> - `readFields()` to read data from the cursor's edit buffer and `writeFields()` to write the form's data to the cursor's edit buffer;

> - `clearValues()` to clear the form's values.

> If you use Qt Designer's QDataBrowser wizard you will be given the option of creating a default set of buttons for navigation and editing. The behaviour of these buttons is set up using the slots described above to provide the following functionality:

> - `INSERT` is initiated by pressing the Ins (Insert) button. The user moves between fields using Tab and Shift+Tab. If the user presses the Update button the `INSERT` will take place and the user will be taken to the record they have just inserted. If the user presses the Insert button (i.e. a second time) the `INSERT` will take place and a new insertion will be initiated. If autoEdit is TRUE the `INSERT` will take place if the user navigates to another record. `INSERT` is cancelled by pressing the Esc key or by pressing the Del (Delete) button. If autoEdit is FALSE then navigating to another record also cancels the `INSERT`. Setting confirmInsert to TRUE will require the user to confirm each `INSERT`.

> - `UPDATE` is automatically initiated whenever the user navigates to a record. An update will take place if the user presses the Update button. If autoEdit is TRUE the update will take place if the user navigates to another record. `UPDATE` is cancelled by pressing the Esc key or by pressing the Del button. If autoEdit is FALSE then navigating to another record also cancels the `UPDATE`. Setting confirmUpdate to TRUE will require the user to confirm each `UPDATE`.

> - `DELETE` is achieved by pressing the Del button. Setting confirmDelete to TRUE will require the user to confirm each `DELETE`.

##### Performing the Drilldown

We now have a working form for editing book records. We need to start the form when the user clicks our 'Edit Books' button, and to navigate to the record they have selected in the BookDataTable. We also need to provide a means of editing the foreign keys, e.g. authorid.

1. We need to make a new slot to connect the Edit Books' button's `clicked()` signal to. Click on the Book form to make it Qt Designer's active form. Invoke the Edit Slots dialog and create a new slot called `editClicked()`. Now click the Connect Signals/Slots toolbar button. Click the Edit Books button and drag to the form; release the mouse on the form. In the Edit Connections dialog connect the `clicked()` signal to the `editClicked()` slot. Click OK to leave the dialog.

2. In the Object Hierarchy window click Source and then click the `editClicked` function. We need to change it to the following:

           void BookForm::editClicked()
           {
               EditBookForm *dialog = new EditBookForm( this, "Edit Book Form", TRUE );
               QSqlCursor cur( "book" );
               dialog->BookDataBrowser->setSqlCursor( &cur );
               dialog->BookDataBrowser->setFilter( BookDataTable->filter() );
               dialog->BookDataBrowser->setSort(QSqlIndex::fromStringList(
                   BookDataTable->sort(), &cur ) );
               dialog->BookDataBrowser->refresh();
               int i = BookDataTable->currentRow();
               if ( i == -1 ) i = 0; // Always use the first row
               dialog->BookDataBrowser->seek( i );
               dialog->exec();
               delete dialog;
               BookDataTable->refresh();
           }

   We create our dialog as before. We also create a cursor over the book table and set the dialog's QDataBrowser, BookDataBrowser, to use this new cursor. We set the QDataBrowser's filter and sort to those that applied to the main form's book QDataTable. We refresh the QDataBrowser and seek to the same record the user was viewing on the main form. Then we exec the dialog and delete it when the user has finished with it. Finally we update the BookDataTable in the main form to reflect any changes that were made in the dialog.

3. Because our code refers to a class declared in `editbook.h` and to a QDataBrowser we need to add two additional include files. Click on the BookForm, then click on the Source tab of the Object Hierarchy window. Right click the 'Includes (In Declaration)' item and click New. Type in `"editbook.h"`. Now add a second include, this time, ``<qdatabrowser.h>.

Now when we navigate through the author and book records in the BookForm we can click the Edit Books button to launch our Edit Books dialog. Although the dialog supports `UPDATE`, `DELETE` and navigation over the book table, we cannot edit the foreign keys nor perform inserts. We will deal with insertion in the same way as we did with the QDataTable, then we will handle the foreign key relationship to author.

##### Inserting into a QDataBrowser

We will create a slot to receive the Edit Books form's `primeInsert()` signal so that we can insert a unique primary key.

1. Click on the Edit Books form, then create a new Slot called `primeInsertBook(QSqlRecord*)`.

   Click Edit|Slots, then click the New Slot button and type the new slot name in the Slot Properties Slot edit box. Click OK.

2. Connect the BookDataBrowser's `primeInsert()` signal to the `primeInsertBook()` slot.

   Click the Connect Signals/Slots toolbar button, then click the BookDataBrowser and drag to the form; release the mouse on the form. Now click the `primeInsert()` signal and the primeInsertBook slot. Click OK.

3. In the Object Hierarchy window click Source and then click the `primeInsertBook` slot. We need to change it to the following:

           void EditBookForm::primeInsertBook( QSqlRecord * buffer )
           {
               QSqlQuery query;
               query.exec( "UPDATE sequence SET sequence = sequence + 1 WHERE tablename='book';" );
               query.exec( "SELECT sequence FROM sequence WHERE tablename='book';" );
               if ( query.next() ) {
                   buffer->setValue( "id", query.value( 0 ) );
               }
           }

4. We will also tidy up the user interface slightly. Click the Update button and set its default property to True. Connect the Close button's `clicked()` signal to the EditBookForm's `accept()` slot.

##### Handling Foreign Keys in a QDataBrowser

Qt's SQL module provides two approaches to dealing with foreign keys. The most powerful and flexible is to subclass widgets and use property maps to relate the widgets to the database. This approach is described in the Qt SQL Module documentation, particularly the StatusPicker example. A simpler approach that can be taken wholly within Qt Designer is presented here.

We will add a new field to the EditBookForm so that authors can be edited along with the title and price. Once we've handled the visual design we'll write the code to make it all work.

1. First we'll add the new widgets. Click the BookDataBrowser and click the Break Layout toolbar button. Resize the form to make it larger and drag each set of buttons down to make some room below the title and price QLineEdits. Click the Text Label toolbar button and click on the form beneath the Price label. Click the Text Label and change its text to 'Author'. Click the ComboBox toolbar button and click on the form beneath the price QLineEdit. In the Property Window change the ComboBox's name to ComboBoxAuthor and change its sizePolicy hSizeType to Expanding.

2. Now we'll lay out the dialog. Shift+Click the Author label and the ComboBox then click the Lay Out Horizontally toolbar button. Now click the BookDataBrowser and click the Lay Out in a Grid toolbar button.

We need to write some code so that the ComboBox will be populated with author names and scroll to the current book's author. We also need to ensure that we put the author's id into the book table's authorid field when a book record is inserted or updated. We'll ensure the code is executed at the right time by putting it in slots and connecting signals to our slots.

1. Create two new slots called `beforeUpdateBook(QSqlRecord *buffer)` and `primeUpdateBook(QSqlRecord *buffer)`. (Click Edit|Slots, then in the Edit Slots dialog click New Slot and enter the first new slot. Click New Slot again and enter the second slot then click OK.)

2. When the user navigates through the dialog, each time they move to a new record, a `primeUpdate()` signal is emitted. We connect to this so that we can update the ComboBox's display. Just before a record is updated or inserted into the database a `beforeUpdate()` or `beforeInsert()` signal is emitted. We connect our `beforeUpdateBook()` slot to both these signals so that we can ensure that the book's authorid field is correctly populated.

   Click the BookDataBrowser and drag the mouse to the form; release the mouse and the Edit Connections dialog will appear. Connect the `beforeUpdate()` signal to our `beforeUpdateBook()` slot. Connect the `beforeInsert()` signal to our `beforeUpdateBook()` slot. Finally connect the `primeUpdate()` signal to our `primeUpdateBook()` slot.

3. All that remains is to write the underlying code. All the code snippets are taken from `qt/tools/designer/examples/book/book7/editbook.ui`.

   1. We start with the `init()` function; this is called after the dialog is constructed and we will use it to populate the ComboBox with author names.

              void EditBookForm::init()
              {
                  QSqlQuery query( "SELECT surname FROM author ORDER BY surname;" );
                  while ( query.next() )
                      ComboBoxAuthor->insertItem( query.value( 0 ).toString());
              }

      Here we execute a query to get a list of author names and insert each one into the ComboBox.

   2. We next write the code which will be executed just before a record is updated (or inserted) in the database.

              void EditBookForm::beforeUpdateBook( QSqlRecord * buffer )
              {
                  QSqlQuery query( "SELECT id FROM author WHERE surname ='" +
                      ComboBoxAuthor->currentText() + "';" );
                  if ( query.next() )
                      buffer->setValue( "authorid", query.value( 0 ) );
              }

      We look up the id of the ComboBox's current author and place it in the update (or insert) buffer's authorid field.

   3. As the user navigates through the records we ensure that the ComboBox reflects the current author.

              void EditBookForm::primeUpdateBook( QSqlRecord * buffer )
              {
                  // Who is this book's author?
                  QSqlQuery query( "SELECT surname FROM author WHERE id='" +
                      buffer->value( "authorid" ).toString() + "';" );
                  QString author = "";
                  if ( query.next() )
                      author = query.value( 0 ).toString();
                  // Set the ComboBox to the right author
                  for ( int i = 0; i < ComboBoxAuthor->count(); i++ ) {
                      if ( ComboBoxAuthor->text( i ) == author ) {
                          ComboBoxAuthor->setCurrentItem( i ) ;
                          break;
                      }
                  }
              }

      Firstly we look up the book's author and secondly we iterate through the ComboBox's items until we find the author and set the ComboBox's current item to the matching author.

If the author name has changed or been deleted the query will fail and no author id will be inserted into the buffer causing the `INSERT` to fail. An alternative is to record the author id's as we populate the ComboBox and store them in a QMap which we can then look up as required. This approach requires changes to the `init()`, `beforeUpdateBook()` and `primeInsertBook()` functions and the addition of a new function, `mapAuthor()`. The relevant code from `qt/tools/designer/examples/book/book8/editbook.ui` is shown below.

1. First we need to create a class variable to map author names to author id's. Click in the Source tab of the Object Hierarchy, then right click the Class Variables item and click New. Type in 'QMap<QString,int> authorMap;'.

2. We now record the author id's in the `init()` function.

           void EditBookForm::init()
           {
               QSqlQuery query( "SELECT surname, id FROM author ORDER BY surname;" );
               while ( query.next() ) {
                   ComboBoxAuthor->insertItem( query.value( 0 ).toString() );
                   int id = query.value( 1 ).toInt();
                   mapAuthor( query.value( 0 ).toString(), id, TRUE );
               }
           }

   After inserting each author's name into the ComboBox we populate a QMap with the author's name and id.

3. Instead of looking up the author's id in the database we look it up in the QMap.

           void EditBookForm::beforeUpdateBook( QSqlRecord * buffer )
           {
               int id;
               mapAuthor( ComboBoxAuthor->currentText(), id, FALSE );
               buffer->setValue( "authorid", id );
           }

4. We use a single function for storing author id's and returning them so that we can use a static data structure.

           void EditBookForm::mapAuthor( const QString & name, int & id, bool populate )
           {
               if ( populate )
                   authorMap[ name ] = id;
               else
                   id = authorMap[ name ];
           }

   If the populate flag is TRUE, we store the author's name and id in the QMap, otherwise we look up the given author name and set id appropriately.

Another approach which is especially useful if the same foreign key lookups are required in different parts of the application is to subclass a cursor and use this for our lookups. This is described in the Qt SQL Module documentation, particulary the section on subclassing QSqlCursor.

The 'book' example demonstrates the basic techniques needed for SQL programming with Qt. Additional information on the Qt SQL classes, especially the QSqlQuery and QSqlCursor classes is provided in the Qt SQL Module documentation.

[Prev: Creating Custom Widgets] [Home] [Next: Customizing and Integrating Qt Designer]