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

    CREATE TABLE author
    ( id integer primary key,
    forename varchar(40),
    surname varchar(40) );

    CREATE TABLE book
    ( id integer primary key,
    title varchar(40),
    price numeric(10,2),
    authorid integer,
    notes varchar(255) );

    CREATE TABLE sequence
    ( tablename varchar(10),
    sequence numeric);

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. We provide an example function, createConnections(), that you can use as a basis for your own code.

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:

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:

    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:

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:

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.

  5. Before we perform an update we need to know who the book's author is, and we need to update the combobox.

        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;
            }
        }
    }

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]

Copyright © 2002 TrolltechTrademarks
Qt version 3.0.5