SQL Module

    #include <qapplication.h>
    #include <qsqldatabase.h>
    #include <qsqlcursor.h>
    #include <qdatatable.h>
    #include "../connection.h"

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

        if ( createConnections() ) {
            QSqlCursor invoiceItemCursor( "invoiceitem" );

            QDataTable *invoiceItemTable = new QDataTable( &invoiceItemCursor );

            app.setMainWidget( invoiceItemTable );

            invoiceItemTable->addColumn( "pricesid", "PriceID" );
            invoiceItemTable->addColumn( "quantity", "Quantity" );
            invoiceItemTable->addColumn( "paiddate", "Paid" );

            invoiceItemTable->refresh();
            invoiceItemTable->show();

            return app.exec();
        }

        return 1;
    }

From sql/overview/subclass1/main.cpp

This example is very similar to the table1 example presented earlier. We create a cursor, add the fields and their display labels to a QDataTable, call refresh() to load the data and call show() to show the widget.

Unfortunately this example is unsatisfactory. It is tedious to set the table name and any custom characteristics for the fields every time we need a cursor over this table. And it would be far better if we displayed the name of the product rather than its pricesid. Since we know the price of the product and the quantity we could also show the product cost and the cost of each invoiceitem. Finally it would be useful (or even essential for primary keys) if we could default some of the values when the user adds a new record.

    class InvoiceItemCursor : public QSqlCursor
    {
        public:
            InvoiceItemCursor();
    };
    

From sql/overview/subclass2/main.h

We have created a separate header file and subclassed QSqlCursor.

    InvoiceItemCursor::InvoiceItemCursor() :
        QSqlCursor( "invoiceitem" )
    {
        // NOOP
    }

From sql/overview/subclass2/main.cpp

In our class's constructor we call the QSqlCursor constructor with the name of the table. We don't have any other characteristics to add at this stage.

            InvoiceItemCursor invoiceItemCursor;

Whenever we require a cursor over the invoiceitem table we can create an InvoiceItemCursor instead of a generic QSqlCursor.

We still need to show the product name rather than the pricesid.

        protected:
            QVariant calculateField( const QString & name );

From sql/overview/subclass3/main.h

The change in the header file is minimal: we simply add the signature of the calculateField() function since we will be reimplementing it.

    InvoiceItemCursor::InvoiceItemCursor() :
        QSqlCursor( "invoiceitem" )
    {
        QSqlFieldInfo productName( "productname", QVariant::String );
        append( productName );
        setCalculated( productName.name(), TRUE );
    }

    QVariant InvoiceItemCursor::calculateField( const QString & name )
    {
        if ( name == "productname" ) {
            QSqlQuery query( "SELECT name FROM prices WHERE id=" +
                         field( "pricesid" )->value().toString() + ";" );
            if ( query.next() )
                return query.value( 0 );
        }

        return QVariant( QString::null );
    }

From sql/overview/subclass3/main.cpp

We have changed the InvoiceItemCursor constructor. We now create a new QSqlField called productname and append this to the InvoiceItemCursor's set of fields. We call setCalculated() on productname to identify it as a calculated field. The first argument to setCalculated() is the field name, the second a bool which if TRUE signifies that calculateField() must be called to get the field's value.

            invoiceItemTable->addColumn( "productname", "Product" );

We add our new fields with addColumn() which adds them to the form and sets their display names.

We have to define our own calculateField() function. In our example database the pricesid in the invoiceitem table is a foreign key into the prices table. We find the name of the product by executing a query on the prices table using the pricesid. This returns the product's name.

We are now able to extend the example to include calculated fields which perform real calculations.

The header file, sql/overview/subclass4/main.h, remains unchanged from the previous example, but the constructor and calculateField() function require some simple expansion. We'll look at each in turn.

    InvoiceItemCursor::InvoiceItemCursor() :
        QSqlCursor( "invoiceitem" )
    {
        QSqlFieldInfo productName( "productname", QVariant::String );
        append( productName );
        setCalculated( productName.name(), TRUE );

        QSqlFieldInfo productPrice( "price", QVariant::Double );
        append( productPrice );
        setCalculated( productPrice.name(), TRUE );

        QSqlFieldInfo productCost( "cost", QVariant::Double );
        append( productCost );
        setCalculated( productCost.name(), TRUE );
    }

From sql/overview/subclass4/main.cpp

We create two extra fields, price and cost, and append them to the cursor's set of fields. Both are registered as calculated fields with calls to setCalculated().

    QVariant InvoiceItemCursor::calculateField( const QString & name )
    {

        if ( name == "productname" ) {
            QSqlQuery query( "SELECT name FROM prices WHERE id=" +
                         field( "pricesid" )->value().toString() + ";" );
            if ( query.next() )
                return query.value( 0 );
        }
        else if ( name == "price" ) {
            QSqlQuery query( "SELECT price FROM prices WHERE id=" +
                         field( "pricesid" )->value().toString() + ";" );
            if ( query.next() )
                return query.value( 0 );
        }
        else if ( name == "cost" ) {
            QSqlQuery query( "SELECT price FROM prices WHERE id=" +
                         field( "pricesid" )->value().toString() + ";" );
            if ( query.next() )
                return QVariant( query.value( 0 ).toDouble() *
                                 value( "quantity").toDouble() );
        }

        return QVariant( QString::null );
    }

From sql/overview/subclass4/main.cpp

The calculateField() function has expanded slightly because now we must calculate the value of three different fields. The productname and price fields are produced by looking up the corresponding values in the prices table keyed by pricesid. The cost field is calculated simply by multiplying the price by the quantity. Note that we cast the cost to a QVariant since that is the type that calculateField() must return.

We've written three separate queries rather than one to make the example more like a real application where it is more likely that each calculated field would be a lookup against a different table or view.

The last feature that we need to add is defaulting values when the user attempts to insert a new record.

            QSqlRecord *primeInsert();

From sql/overview/subclass5/main.h

We declare our own primeInsert() function since we will need to reimplement this.

The constructor and the calculateField() function remain unchanged.

    QSqlRecord *InvoiceItemCursor::primeInsert()
    {
        QSqlRecord *buffer = editBuffer();
        QSqlQuery query( "SELECT NEXTVAL( 'invoiceitem_seq' );" );
        if ( query.next() )
            buffer->setValue( "id", query.value( 0 ) );
        buffer->setValue( "paiddate", QDate::currentDate() );
        buffer->setValue( "quantity", 1 );

        return buffer;
    }

From sql/overview/subclass5/main.cpp

We get a pointer to the internal edit buffer that the cursor uses for inserts and updates. The id field is a unique integer that we generate using the invoiceitem_seq. We default the value of the paiddate field to today's date and default the quantity to 1. Finally we return a pointer to the buffer. The rest of the code is unchanged from the previous version.

The Example Tables

create table people (id integer primary key, name char(40))

create table staff (id integer primary key, forename char(40),
                    surname char(40), salary float, statusid integer)

create table status (id integer primary key, name char(30))

create table creditors (id integer primary key, forename char(40),
                        surname char(40), city char(30))

create table prices (id integer primary key, name char(40), price float)

create table invoiceitem (id integer primary key, 
                          pricesid integer, quantity integer,
                          paiddate date)

create sequence invoiceitem_seq