Chapter Five :- Database Programming With MySQL

Love them or hate them there is one thing that you can pretty much guarantee if you are going to spend any time doing computer programming and that is that sooner or later you are going to have to either create or display data from a database. In this chapter we look at how to use the built in database widgets of QDataTable, QDataBrowser and QDataView, but first of all we need to set up some information to use in our database. For this demonstration we will be using MySQL which comes with Suse Linux and probably given it's popularity every other version of Linux as well.

Setting Up The Database

There seem to be two ways you can go about setting up MySQL. The easy way and the painful way and there seems to be only a slight difference that decides which way your set up is going to go. This way was worked out over several days of testing. If you want to do it another way or you do run into trouble you'll be needing this link. MySQL Documentation

Order

Install MySQL
MySQL_Install_DB
Start MySQL
MySQL_Fix_Privilege_Tables
Change Root Password ( Recommended )
( Optional ) Uninstall MySQL

1. Install My SQL

MySQL is installed with Suse 10 it is just not started as a service, but there are still reasons for checking the install with Yast. Mostly this is because the Graphical User Interface tools for MySQL are not installed by default so unless you set up the system knowing that you would need them you probably wont have them.

To install or to just check if you have everything you need open the Control Center ( Yast ) and select the Software Management option. It should open with the search option, type mysql, it doesn't matter about case and hit return.

The parts you are interested in here are the mysql-administrator and the mysql-query-browser, the query browser is for more advanced use than we will be using here but it can't hurt to have it.

The Yast setup should be familiar if you have setup the system yourself if not, click on the checkbox for the item you are interested in. A black tick will install it, a blue tick means it is already installed, a bin means remove it and a lightening bolt thingy means update it.

It should also be noted that these programs are included on the main menu at System/Service Configuration but if you want to set up the links on your desktop you can find them in /usr/share/applications

You can copy these to your desktop if you think you are going to need them regularly.

2. MySQL_Install_DB

The next thing you need to do is run the install db script which will setup the default MySQL users and databases. These are an anonymous user that has no password. A root user that has no password and the mysql database. There is also a test database that gets set up but this is empty and can just be ignored or deleted later.

To create the database and users open a console as superuser and type MySQL_Install_DB

You should get a print out like the one above. Though of course for the moment you should ignore everything it says, unless it is reporting an error. The reasons for this are that to run the fix privilege tables script the service needs to be started and it has to log into mysql to set the tables which it is not going to be as simple as it could be if you have changed the password.

As you can see from the bottom of the print out any attempt to connect to MySQL at this point in time should be rejected as we haven't started it yet. We do that next.

3. Start MySQL

By default the MySQL server is disabled when you set up Suse so you need to start it yourself. To do this you need to access the Runlevel Services on whatever version of Linux you are using. On Suse this is done through the Control Center/YaST2 Modules/System/System Services ( Runlevel ). You will need Administrator priviledges to access the services list.

Once you enable the MySQL server here it will be started whenever you start your computer, so once you've done it you can just forget about it and use MySQL anytime you want.

4. MySQL_Fix_Privilege_Tables

Once the service is started open Konsole in superuser mode and type MySQL_Fix_Privilege_Tables.

5. Change Root Password

At this point it is recommended that you change the root password. The syntaxt is

mysqladmin -u root password "newpwd"

6. Uninstall MySQL

If you have got the the state where you are just getting nowhere and are having trouble getting answers from the MySQL website, the quickest solution, as long as you aren't using a machine that has a valid production database on it is just to delete MySQL and start again from scratch. You do this by going opening Yast, searching for mysql and setting the mysql option to be uninstalled which should show a bin at the checkbox.

The database files for MySQL on Suse are stored in var/lib/mysql. If you delete this folder you will have completely removed the MySQL information, including all databases and users, so like I say if the computer has ever been used as a MySQL production machine do not touch this folder or you will lose everything.

Administration

To setup MySQL exactly how you want it, start the MySQLAdministrator,

use root as the user and the password that you have set recently.

When it starts it should look something like the above. The important bits here are the users

Where you can add new users by using the button provided and the Catalogs section,

where you can see the databases that are on your system.

Knoda

The default tool of choice for working with MySQL is going to the be the KDE Knoda application for the simple reason that it allows us to set up the database without having to write all the SQL ourselves.

The link to the Knoda application can be found under the main menu Office/Database

When started Knoda will ask which driver that we want to use, we select mysql and connect which gives us the connection dialog.

To setup and use our database we are just going to use the root connection with the password entered when we set up MySQL earlier. Once Knoda connects, assuming everything goes to plan you should have access to the MySQL databases that were installed during the setup process described above. We can ignore these as for our projects for this chapter we are going to setup a simple music database that we can then access through KDevelop and use within our own programs.

To create a completely new MySQL database with Knoda go to the File/New/Database menu.

Select the New Database menu item and we get this dialog.

As you can see to setup a new database all we have to do is type in the name and select OK. At this point absolutely nothing will appear to happen as Knoda will not automatically change to the newly created database, nor does it ask you if you want to. This is not a major problem though as changing to the new database is easy.

Select the Database combobox and select from any of the available databases. We want KDevelopMusic which once selected Knoda will load our new, empty database. First of all we want to create three tables.

As you can see creating a new item in the table be it a table, Query, etc. Is as simple as right clicking and selecting new. For this example music database we are going to create three tables, these being the Artist table, the Album table and the Songs table.

We can see here the preliminary setup for the Artist Table which contains two fields the Artist_ID and the Artist fields. To add a field to a table click on the New Field button and the fill out the options for the field in the options boxes on the top right. You can experiment with fields and tables here but you should really have a good idea of want your tables are going to contain and how they are going to work together before you start.

One of the worst ways you can possibly design a database is by trying to cram everything into a single table. This is just asking for trouble because you will then me making the job of accessing the table far more difficult, for example if we know the id of an artist with this table we can use a pseudo sql statement along the lines of Select * from Artist where Artist_ID = knownvalue. If however, we also add the album names to this table we are going to end up trying to seperate out the individual album titles from the results of our select statement.

The tables created here are for a simple music database basically we are going to be storing the names of the artist the titles of the albums and the songs that each album contains. The database will be created in such a way that we can get the artist name from the song or the album name from the song but we wont include stuff like running times of songs, release dates or publishers. If you really want all those details you can always add them or record the album in amaroK.

Once you have created the tables you can start adding information to them. This is done in Knoda by right clicking the table and selecting start.

For now we will add some initial testing information in Knoda so that we can make sure that everything works as expected from this end. We can also play around with the data here so that when we get to developing programs with KDevelop we can concentrate on the programming side of things and have some sql statements already if we need them.

For test purposes I've added Three Bands with Four albums, containing sixty two songs between them. The test tables look like this, First the Artist Table,

Followed by the Album table,

and a snippet from the Songs table,

In order to view this as a the result of a proper database query we need to create a new query by right clicking on Queries and selecting new.

This is the Knoda graphical development for an sql query and the query itself is the standard query for this database. It basically gets all the songs according to album and artist, when you run it, like so,

I've called it the Standard Query because any other queries that are used will basically be an edit to this query. There are two ways that you can develop SQL queries with Knoda the most obvious is through the graphical interface as shown above and the other for those who can write SQL off the top of their heads you can type in the SQL.

To get to the SQL editor select the View menu,

and select use QBE this will turn of the graphical development and allow you to type the SQL straight in to the editor. The SQL for the Standard Query is,

SELECT "Artist0"."Artist" , "Album1"."Album_Title" , "Songs2"."Song_Title" FROM "Artist" "Artist0" , "Album" "Album1" , "Songs" "Songs2" WHERE ("Album1"."Artist_ID"="Artist0"."Artist_ID") AND ("Songs2"."Album_ID"="Album1"."Album_ID")

Though seeing as the graphical interface is there and it opens by default it seems kinda silly not to use it if it is going to make life easier.

When we first start a new query the tables shown in the picture above are not there so we need to add them we do this by right clicking in the empty space above the comboboxes.

this will give you a menu with one option “Add datasource”. When you select this a dialog box will open showing you the tables that are available in the currently selected database.

You are free to add as many of the tables as you want and in the case of this example we add all of them before closing the dialog.

The first thing you'll want to do when you have added the tables to the query is set up the links between the tables. This is done by selecting the item in the table to link and dragging it to the appropriate item in another table.

for example in the above the Artist_ID in the Artist table should map to the Artist_ID in the Album table. It is good practice to have any links between tables to be named the same in each table, the idea being that anyone looking at the tables can then easily see what links to what. Whilst on the subject of names the alert ones will have noticed that Knoda has changed the table names adding a number beginning with zero to the table names as they were added. These are just aliases for the tables which are resolved in the from part of the SQL statement.

FROM "Artist" "Artist0" , "Album" "Album1" , "Songs" "Songs2"

which tells the database that “Artist0” is an alias for “Artist”, etc.

When you release the mouse over the table that you wish to establish a link with you will get a dialog.

which shows you the link that Knoda thinks you are trying to make and allows you to change it if you meant to link to another item in the table.

Once you have established the links all you need to do is setup exactly what it is that you want to display. This is done through the drop down boxes at the bottom of the screen. If you are a complete beginner it can take a few attempts to get this to display exactly the way that you want to but as a basic rule of thumb you should have tables that display the least information on the left and work right with the tables displaying more information as you go.

As you can see once the tables have been added to the query they will be selectable from the drop down box for the table. Once we have selected the table we can select any fields from that table that we want to display.

In the Standard Query that we have set up in this example we only show the information that is relevant to the topic. All the items that end in ID are designed for us to use from within the database and are therefore not really important to anyone who is looking at the database simply to see what tracks are on a specific album.

KDevelop Database Support

KDevelop has three gui widgets that can be used for displaying database information these are the Qt class widgets DataTable, DataBrowser and DataView.

Using a DataTable

Unfortuanetly database programming isn't as straight forward as it could be at the moment so it is better to start with the idea that we are going to have to do most of the work by hand. The main source of the problems being that while the DataTable will attempt to set up a database connection if you go through the wizard to set it up. It doesn't save the information that you use to log into the database which means that if you try to just use the wizards and run the code you will be told that there was an error connecting to the database.

The way to get the QDataTable to work is to drop it onto the form and then just cancel the wizard. This will add a blank QDataTable to you form. We can work with this.

First of all we are going to have to add a dialog so that we can get the user name and the password from the user. We could hard code these but it's just plain nasty so in an effort not to encourage bad habits we'll do it properly from the start.

Adding A Dialog

To add a new dialog to the project click on the “New File” tab on the left side of KDevelop and select the appropriate dialog,

for our simple user and password dialog we'll select the standard Dialog with Buttons ( bottom ). When we select the dialog we are given a setup dialog,

This just asks what the name of the file is to be and gives a final opportunity to change our minds about which dialog we want.

We are then presented with an automake dialog that asks which of the current projects we want to add this to. If you just want to add the dialog to the current project you are working on then accept the defaults by clicking OK.

Although we now have a new ui file we still do not have any class files to implement the new user interface, in this case our password dialog. There is however a need to be careful about class names here so don't go rushing in creating files called PasswordDialog.h and PasswordDialog.cpp or you could start making life really complicated and annoying. If however you remember what was said in earlier chapters about how the meta object compiler works and how it will create a header and .cpp file for a .ui file even though you never see it and it is this file that controls the class declarations for the form widgets. Any code implementation of a form needs to inherit from these classes, which admittedly is a bit tricky at the moment as they don't exist yet.

So first of all we need to get the meta object compiler to generate our class and header file. This is done by running Automake and friends followed by configure from the build menu and then building the project. Once the project is built you will see the newly created files in the projectname/debug/src directory. In this case they are the passworddialog.h which looks like

#ifndef PASSWORDDIALOG_H
#define PASSWORDDIALOG_H

#include <qvariant.h>
#include <qdialog.h>

class QVBoxLayout;
class QHBoxLayout;
class QGridLayout;
class QSpacerItem;
class QPushButton;

class passwordDialog : public QDialog
{
    Q_OBJECT

public:
    passwordDialog( QWidget* parent = 0, const char* name = 0, bool modal = FALSE, WFlags fl = 0 );
    ~passwordDialog();

    QPushButton* buttonHelp;
    QPushButton* buttonOk;
    QPushButton* buttonCancel;

protected:
    QHBoxLayout* Layout1;
    QSpacerItem* Horizontal_Spacing2;

protected slots:
    virtual void languageChange();

};

and the passworddialog.cpp file.

#include <kdialog.h>
#include <klocale.h>
/****************************************************************************
** Form implementation generated from reading ui file '/home/pseudonym67/Dev/KDE/BeginningKDEProgramming/chapterfivedatatable/src/PasswordDialog.ui'
**
** Created: Fri Mar 3 18:09:34 2006
**      by: The User Interface Compiler ($Id: qt/main.cpp   3.3.4   edited Nov 24 2003 $)
**
** WARNING! All changes made in this file will be lost!
****************************************************************************/

#include "PasswordDialog.h"

#include <qvariant.h>
#include <qpushbutton.h>
#include <qlayout.h>
#include <qtooltip.h>
#include <qwhatsthis.h>

/*
 *  Constructs a passwordDialog as a child of 'parent', with the
 *  name 'name' and widget flags set to 'f'.
 *
 *  The dialog will by default be modeless, unless you set 'modal' to
 *  TRUE to construct a modal dialog.
 */
passwordDialog::passwordDialog( QWidget* parent, const char* name, bool modal, WFlags fl )
    : QDialog( parent, name, modal, fl )
{
    if ( !name )
        setName( "passwordDialog" );
    setSizeGripEnabled( TRUE );

    QWidget* privateLayoutWidget = new QWidget( this, "Layout1" );
    privateLayoutWidget->setGeometry( QRect( 20, 240, 476, 33 ) );
    Layout1 = new QHBoxLayout( privateLayoutWidget, 0, 6, "Layout1"); 

    buttonHelp = new QPushButton( privateLayoutWidget, "buttonHelp" );
    buttonHelp->setAutoDefault( TRUE );
    Layout1->addWidget( buttonHelp );
    Horizontal_Spacing2 = new QSpacerItem( 20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
    Layout1->addItem( Horizontal_Spacing2 );

    buttonOk = new QPushButton( privateLayoutWidget, "buttonOk" );
    buttonOk->setAutoDefault( TRUE );
    buttonOk->setDefault( TRUE );
    Layout1->addWidget( buttonOk );

    buttonCancel = new QPushButton( privateLayoutWidget, "buttonCancel" );
    buttonCancel->setAutoDefault( TRUE );
    Layout1->addWidget( buttonCancel );
    languageChange();
    resize( QSize(511, 282).expandedTo(minimumSizeHint()) );
    clearWState( WState_Polished );

    // signals and slots connections
    connect( buttonOk, SIGNAL( clicked() ), this, SLOT( accept() ) );
    connect( buttonCancel, SIGNAL( clicked() ), this, SLOT( reject() ) );
}

/*
 *  Destroys the object and frees any allocated resources
 */
passwordDialog::~passwordDialog()
{
    // no need to delete child widgets, Qt does it all for us
}

/*
 *  Sets the strings of the subwidgets using the current
 *  language.
 */
void passwordDialog::languageChange()
{
    setCaption( tr2i18n( "UserName And Password" ) );
    buttonHelp->setText( tr2i18n( "&Help" ) );
    buttonHelp->setAccel( QKeySequence( tr2i18n( "F1" ) ) );
    buttonOk->setText( tr2i18n( "O&K" ) );
    buttonOk->setAccel( QKeySequence( tr2i18n( "Alt+K" ) ) );
    buttonCancel->setText( tr2i18n( "Ca&ncel" ) );
    buttonCancel->setAccel( QKeySequence( tr2i18n( "Alt+N" ) ) );
}

#include "PasswordDialog.moc"

Now we can set about getting our dialog up an running. Right click on the the .ui file in the Automake Manager.

In order to create the classes to implement the PasswordDialog we need to select either the “Subclassing Wizard” or the “Create or Select Implementation”, the acutal file creation is the same in either one,

The main difference is that with the Subclassing Wizard as you can see you can choose to specialise the implementation of some of the buttons in this case we are going to need to implement the accept function so that we can later save the strings added for the username and the password and use them to access the database. In order to avoid naming issues I've named the class PasswordDialogImpl to make it clear that this is the implementation of the password dialog.

The final dialog that we get is the Automake Manager dialog again,

Here we can see it adding the .cpp and .h files and if we look at the .h file we can see,

#ifndef PASSWORDDIALOGIMPL_H
#define PASSWORDDIALOGIMPL_H

#include "PasswordDialog.h"

class PasswordDialogImpl : public passwordDialog
{
  Q_OBJECT

public:
  PasswordDialogImpl(QWidget* parent = 0, const char* name = 0, bool modal = FALSE, WFlags fl = 0 );
  ~PasswordDialogImpl();
  /*$PUBLIC_FUNCTIONS$*/

public slots:
  /*$PUBLIC_SLOTS$*/

protected:
  /*$PROTECTED_FUNCTIONS$*/

protected slots:
  /*$PROTECTED_SLOTS$*/
  virtual void          accept();

};

not only that it inherits from the MOC created passwordDialog class but provides and implementation of the accept() function. As we have just added new files to the project run automake and friends from the build menu followed by configure and then run build to make sure it all builds correctly. Now we can get on with implementing the dialog.

Implementing The Dialog

From the Dialog point of view the implementation is straightforward we declare a couple of strings, one for the user name and one for the password,

private:
   /** Store the UserName
   **/
   QString strUserName;
   /** Store the Password
   **/
   QString strPassword;

and add the get and set functionality, and then we add the following code to the overridden accept function,

setPassword( passwordLineEdit->text() );
setUserName( userLineEdit->text() );
QDialog::accept();

The technical bit appears when we realise that we want to have the dialog appear as the application starts but preferably before it displayed. This way when the application starts it will show the database table data as we want it without it obviously drawing it after the application has started. To do this we need to override the polish function. The polish function performs the initialisation of the widget before it is shown but the documentation comes with the warning that you should call the base class polish function before implementing your own code. This ensures that the default code for the widget is completed before you make your changes, as making the changes before the base classes have called polish can trigger further calls to polish, ending up with infinite recursion.

So our overriding of the polish function looks like this,

void ChapterFiveDataTableWidget::polish()
{
    QWidget::polish();
        
    PasswordDialogImpl *dlg = new PasswordDialogImpl();
        
    if( dlg->exec() == QDialog::Accepted )
    {
       loadDatabase( dlg->userName(), dlg->password() );                                                               
    }       
}

Here we call the base class polish and then create a new dialog and execute it modally with the exec function. Note that calling show would display a modeless dialog that would have the main application widget displaying in the background which is exactly what we dont want it to do.

Query The Database

Now that we have the username and the password entered as the application starts there are three more parameters that we need to be able to connect to the database. These are the host computer and the database name and the driver that we are going to use to connect to the database. These are the parameters that are entered into the Knoda connection dialog so they should be familiar by now. I've set up strings to hold the variables in the ChapterFiveDataTableWidget class and provided the appropriate access functions although this class is not accessed from anywhere so they could just be left as private variables that are intialised in the constructor as,

strName = "KDevelopMusic";
strDriver = "QMYSQL3";
strHost = "localhost";

These variables are all used in the loadDatabase function which is called at the end of our overridden polish function

The driver variable starts things off and is used with the line,

dbConnection = QSqlDatabase::addDatabase( strDriver );

This call initialises our database connection variable that is declared in the ChapterFiveDataTableWidiget class declaration as,

QSqlDatabase *dbConnection;

The detailled listings of database drivers that can be used with Qt are in the SQL Module Drivers section of the help.

Now we set up the connection parameters to the database,

dbConnection->setDatabaseName( name() );
dbConnection->setUserName( user );
dbConnection->setPassword( password );
dbConnection->setHostName( host() );
        
if( dbConnection->open() == true )

As you can see we are simply using code to set exactly the same parameters we used for Knoda, so as long as we can get into Knoda there should be no reason for the dbConnection->open call to return false.

We are now in the position where we are ready to actually query the database and display it and when using a datatable as long as we have done our preparation properly the actual querying of the database is disgustingly easy. First of all a browse through the QSqlCursor class help file and it's derived class QSqlSelectCursor shows us that we can pass a table name to QSqlCursor or an SQL statement to QSqlSelectCursor constructor, along with our database collection and this will give us the results of our query in our newly constructed cursor object which we can then pass to the QDataTable object and let it do all the drawing and displaying for us. In code this looks like this,

///QSqlCursor *cursor = new QSqlCursor( "Artist", true, dbConnection );
 
QSqlSelectCursor *cursor = new QSqlSelectCursor( "SELECT Artist FROM Artist", dbConnection );
                
                
///     QSqlSelectCursor *cursor = new QSqlSelectCursor( strSqlStatement, dbConnection );
                
                
dataTable1->setSqlCursor( cursor, true, false );
dataTable1->refresh();
                
for( int i=0; i<dataTable1->numCols(); i++ )
{
   dataTable1->adjustColumn( i );
}

The first call we use which is blanked out in the above code is the creation of a QSqlCursor object that takes the Artist table as a parameter and an autopopulate value of true along with the database connection pointer.

We then pass the cursor to the QDataTable by calling the setSqlCursor function, passing in the cursor itself and the value true for the autopopulate option which tells the table to automatically display the data. The final parameter is for the autodelete and as we don't want the table to be edited here we set it to false, which is the default. The following call to QDataTable refresh tells the table to redraw itself and it will use the now current cursor to do this.

The final piece of code just gets the number of columns and then tells all the table columns to adjust their width to that of the largest entry in the column.

The resulting view of the table is,

which is fine and is exactly what we asked for as it shows all the data in the table, the only problem being that it is not really what we want as the Artist_ID is a key value and is of no interest to anyone who is simply trying to view the records in the database so we'll switch to the QSqlSelectCursor and see if we can get something a little more appropriate.

If we use the SQL statement

"SELECT Artist FROM Artist"

in the QSqlSelect constructor we get,

which is much more along the lines of what we want. But of course now we have to play just a little more and see what the StandardQuery we did in Knoda would look like,

Tip Of The Day

On rare occaisions the KDevelop environment can apparently get itself into an endless loop when trying to load a .ui on startup. If this happens it can make the project unaccessible and the only way to shut down the KDevelop to close is to force it by ending the process with KSysGuard which is found in System/Monitor/Performance Monitor (KSysGuard). To do this select the Process Table. Then find the KDevelop process and highlight it by clicking on it and then hit the kill button.

If this does happen and you can't get KDevelop to load the project then we can fix it with a quick bit of editing. KDevelop keeps track of what's in each project through a file with the extension .kdevses, in this case chapterfivedatatable.kdevses and it looks like,

<?xml version = '1.0' encoding = 'UTF-8'?>

<!DOCTYPE KDevPrjSession>

</Doc0>

</Doc1>

</Doc2>

</Doc3>

</DocsAndViews>

</kdevdebugger>

</kdevbookmarks>

</kdevvalgrind>

</pluginList>

</KDevPrjSession>

As you can see this is a standard .xml file that KDevelop uses to keep track of the current files, breakpoints etc. that are being used by the project. The documents being viewed have the format of the name of the document and the viewtype that KDevelop is to render them in, so if for example the chapterfivedatatablewidgetbase.ui file was refusing to load properly then all we would have to is remove the information telling KDevelop to load that document which in this case is remove the xml for loading Doc3 and then restart KDevelop. The project should then load fine and you will be able to load the .ui file normally from the Automake Manager once KDevelop has started.

Using the DataBrowser

Setting up the DataBrowser project can be a little tricky so here's the least painful way to do it. Create the using the Simple Designer based KDE Application as with all the others and remove the label and the button as well as all the code. Then break the layout on the form and resize it so that you can fit the QDataBrowser object onto it. Then do a complete build of the project, you will need to run automake and friends and then configure, just to make sure that everything is satisfactory. Then shut down KDevelop. The object of this is so that when you load KDevelop this project is loaded on startup. Restart KDevelop and then add the QDataBrowser to the form. If the Database Connection wizard starts up,

Then everything is fine, otherwise you are just going to have to restart KDevelop and try again.

For this project we will want to run through the wizard as although it does not retain the database connection information and we will have to code that by hand it does give us a gui for our query results and that saves us the task of having to do it.

First of all we click on the Setup Database Connections button and fill out the connection dialog,

as you can see the only change here is that we have provided a name for the connection, the rest of the input is as it has been throughout this chapter. Clicking on the connect button gives us,

the Music connection has been established so we can click the close button,

We used the artist table last time so this time we'll select the album table and click Next.

I'll admit at this point that this database is probably not ideal for demonstrating the browser as it would be more effective if the tables were to contain more items for display but even with one displayed field you should get the idea and the programming principals will apply no matter how many fields are present in the database table.

The selection of fields is controlled by the buttons in the middle which are not very clear here but they are from the top down,

Once we have the fields set up that we want we click next.

As you can see the Include Edit Buttons is disabled here as allowing people to add an album without being able to add the songs for the album.

The next screen in the wizard allows us to add a where clause to the SQL,

and to set the sort order for the displayed fields. Then we set the display options,

The final option allows us to set if we want the wizard to generate the SQL for updates,

This gives us a gui that looks like

and a running program that looks like,

Reusing A Dialog

The next thing to do is add the polish function to our class that inherits from the “projectname”base class we do this in the usual way by adding

public slots:
    /*$PUBLIC_SLOTS$*/
        virtual void polish();

to the header file and then adding the implementation to the cpp file. There is some code from the the wizard generated file that we can use. The implementation of the polish function in the ChapterFiveDataBrowserWidgetBase class is,

if ( dataBrowser3 ) {
   if ( !dataBrowser3->sqlCursor() ) {
      QSqlCursor* cursor = new QSqlCursor( "Album", TRUE, MusicConnection );
      dataBrowser3->setSqlCursor( cursor, TRUE );
      dataBrowser3->refresh();
      dataBrowser3->first();
   }
}

which is useful to us later but it wont work as it is because the necessary parameters for the MusicConnection which is the name given by the wizard to the QSqlDatabase, have not been set yet.

First of all we need to get the user name and password using the dialog that we used in the chapterfivedatatable project. We do this by right clicking on the project in the Automake Manager,

and selecting Add Existing Files.. which gives us this dialog.

Select the .ui file and the implementation header and definition file and click Add Selected. As long as you don't start changing the names of things the implementation should work without a hitch as the MOC will generate “projectname”widgetbase classes exactly as it did with the original project.

The next screen you will get will be.

You will be asked if you want to link or copy the files. In the normal scheme of things you would have the classes that you reuse in a standard directory and link to them rather than copy them this then means that if you do need to make any changes to the class files then they are automatically propagated through any projects and you don't need to start searching your hard disk for different implementations of the same class.

In this case though they have been copied to the project as in this case the project is a stand alone project that should be distributed in such a way as it can be built with as few issues as possible.

Now add the dialog to the polish function,

// Notice calling QWidget polish here as we
// are overriding the behaviour added by the wizard
// which initially overrode polish in the base class.
//
QWidget::polish();
        
PasswordDialogImpl *dlg = new PasswordDialogImpl();
        
if( dlg->exec() == QDialog::Accepted )
{

and build the project making sure that you have all the required headers in place. Then add the setup code for the QSqlDatabase,

MusicConnection = QSqlDatabase::addDatabase( driver() );
if( MusicConnection == 0 )
{
    KMessageBox::error( this, "The database connection is invalid", "Data Browser Demo" );
    return;
}
                
MusicConnection->setDatabaseName( name() );
MusicConnection->setHostName( host() );
MusicConnection->setUserName( dlg->userName() );
MusicConnection->setPassword( dlg->password() );
                
if( MusicConnection->open() == true )
{

Now the problem is that this code still doesn't work because of this,

As far as the debugger is concerned at this moment in time the gui objects have not been completely constructed which means that any tests for the data browsers objects are pretty hit or miss in fact on my computer the line,

if( !dataBrowser3->sqlCursor() )

fails everytime because it does not give a false value for the sqlCursor, so what we do is remove this line and continue to generate the new QSqlCursor that points to our data. At the very worst here with the QSqlCursor inheriting from QObject we should only have an extra QSqlCursor hanging around until the end of the function.

When we remove the test on the QSqlCursor we get,

if( MusicConnection->open() == true )
{
    if( dataBrowser3 ) 
    {
        QSqlCursor* cursor = new QSqlCursor( "Album", TRUE, MusicConnection );
        dataBrowser3->setSqlCursor( cursor, TRUE );
        dataBrowser3->refresh();
        dataBrowser3->first();
    }
}

and a program that gives us.

On a final note you should remember that we accepted the default setting for autoediting which is on. This means that any changes you make to the titles while running this program will be made to the database.

Using The DataView

The QDataView is a cut down version of the QDataBrowser without the inbuilt ability to move through the records. It is designed more for paying attention to a particular section of a database rather than browsing through all the records in it. For say looking at personal records where the next record in the database doesn't necessarily have any connection with what you are doing with this record. For this reason the Music database just wasn't adequate to display the idea of how the QDataView works so I put together a quick fictional people database using Knoda using the same methods as described above.

The Knoda table looks like,

As you can see it is a simple enough database containing nothing more than the names and addresses of fictional people, well, the addresses are fictional anyway. It does however, give us enough fields to make our dataview project look a bit more respectable.

To start the wizard for creating the chapterfivedataview project, create the Simple Designer based KDE Application as before and remove the widgets and button_clicked functionality save and build the project and then add a DataView to the form,

The wizard is almost identical to the DataBrowser wizard only slightly shorter.

With the only differences in the setup from previous projects being minor such as the change ot the database name,

though there are more fields to display when we get to the fields dialog.

When we get to the code we notice that things are pretty much the same as thye have been in recent projects. The basic structure is to set up a QSqlForm and add all the display widgets to the form. As a user of the project you don't directly use the QSqlForm yourself although if you wish to hand code a database form, looking at the wizard implementation is the way to do it as it sets the names for the widgets to table columns of the database and then maps the record you pass to the form to the appropriate columns. This is also how the previous QDataBrowser project works although in that project the QDataBrowser wraps the QSqlForm and controls things for us.

One thing you will notice if you look at the generated code is that the polish function is not overloaded automatically here. This is because the design of the QDataView isn't really suited to accessing the database immeadiately on start up and is why the chapterfivedataview project is developed the way it is.

We start with the idea of a separate application that contacts the database when it needs to and displays a single entry from the database. Technically we could have overridden the polish function and connected to the database from there but I decided to seperate everything out. So know we have a seperate database function that is activated by clicking the button and at start up what is an empty QComboBox. The idea for the project being that the user connects to the database and a list of the available people is placed in the QComboBox. The user then selects the name of the person they wish to view the details of in the QComboBox and then the form is filled out.

The database connection starts when the button is clicked,

PasswordDialogImpl *dlg = new PasswordDialogImpl();
        
if( dlg->exec() == QDialog::Accepted )
{
    FictionalPeopleConnection = QSqlDatabase::addDatabase( databaseDriver() );
    FictionalPeopleConnection->setDatabaseName( databaseName() );
    FictionalPeopleConnection->setHostName( host() );
    FictionalPeopleConnection->setUserName( dlg->userName() );
    FictionalPeopleConnection->setPassword( dlg->password() );
                
    if( FictionalPeopleConnection->open() == true )
    {
        QSqlSelectCursor *cursor = new QSqlSelectCursor( "SELECT FictionalPeople.First_Name, FictionalPeople.Middle_Names, FictionalPeople.Surname FROM FictionalPeople", FictionalPeopleConnection );
                        
        cursor->select();
        QString strName;
                        
        while( cursor->next() == true )
        {
            strName = cursor->value( "First_Name" ).toString() + " ";
                                
            if( cursor->value( "Middle_Names" ).toString().isEmpty() == false )
            {
                strName += cursor->value( "Middle_Names" ).toString() + " ";
            }
                                
            strName += cursor->value( "Surname" ).toString();
                                
            namesComboBox->insertItem( strName );
        }
    }
}

As you can see there is nothing that should surprise you in this function, the password and database access is exactly the same as it has been in the previous two projects it is just initialised differently. The sql statement,

SELECT FictionalPeople.First_Name, FictionalPeople.Middle_Names, FictionalPeople.Surname FROM FictionalPeople

Gets the first name, the middle names and the last name from all the entries in the database. We then build up a name string by using the value function from the QSqlRecord class, we can do this because if you follow the class hierarchy in the help, you will see that the QSqlSelectCursor class inherits from the QSqlCursor class which in turn is derived from the QSqlRecord and the QSqlQuery classes. When this function has run we get,

which shows the three entries in the database in the QComboBox. You could call the fact that it doesn't automatically fill in the form as either a bug or a feature depending on your mood but when you think about if there is a long list of names why would you specifically want to see the first one any more than the others. Even when they are sorted.

The way to do this is to receive the activated signal from the QComboBox when a new item is selected in the list and displayed in the viewable area.

Adding the Signal Handler here adds the function to our widget class and then we need to add a variable name for the QString so that we can use it.

virtual void namesComboBox_activated( const QString& name );

and the function is,

void chapterfivedataviewWidget::namesComboBox_activated( const QString& name )
{
   int nSurname = name.findRev( ' ' );
   //      KMessageBox::information( this, "The space for the surname is at " + QString::number( nSurname ) + "\nin name " + name );
   int nLength = name.length();
   setSurname( name.mid( nSurname+1, nLength ) );
   // KMessageBox::information( this, "The surname is " + surname() + " \nFrom the name " + name );
   int nFirstName = name.find( ' ' );
   setFirstName( name.left( nFirstName ) );
   // KMessageBox::information( this, "The first name is " + firstName() + "\nWith the space at " + QString::number( nFirstName ) );
   if( nFirstName != nSurname )
   {
       setMiddleName( name.mid( nFirstName+1, nSurname - ( nFirstName+1 ) ) ); 
       // KMessageBox::information( this, "The middle names are " + middleName() + "\n Starting from " + QString::number( nFirstName+1 ) + "\nEnding at " + QString::number( nSurname ) + "\nFrom a length of " + QString::number( nLength ) );
   }
        
   QString strSqlString = "SELECT * FROM FictionalPeople WHERE FictionalPeople.First_Name = '" + firstName() + "' ";
   if( middleName() != 0 && middleName().isEmpty() == false )
       strSqlString += "AND FictionalPeople.Middle_Names = '" + middleName() + "' ";
   strSqlString += "AND FictionalPeople.Surname = '" + surname() + "'";
        
   // KMessageBox::information( this, strSqlString );
        
   QSqlSelectCursor *cursor = new QSqlSelectCursor( strSqlString, FictionalPeopleConnection );
   cursor->select();
   cursor->next();
        
   dataView2->refresh( cursor );
        
   // clear the middle name
   strMiddleName.truncate( 0 );

}

A bit of a blob so we'll break it down into it's three sections. The first section is the processing of the string that is passed to us from the QComboBox. This string is the full name of the person we want so we will have to break it up again. Without the KMessageBox calls that show the progress through the string it looks like this,

int nSurname = name.findRev( ' ' );
int nLength = name.length();
setSurname( name.mid( nSurname+1, nLength ) );
int nFirstName = name.find( ' ' );
setFirstName( name.left( nFirstName ) );
if( nFirstName != nSurname )
{
    setMiddleName( name.mid( nFirstName+1, nSurname - ( nFirstName+1 ) ) ); 
}

here we use a few of the QString functions to break up the string starting with the findRev function which starts at the back of the string and moves towards the start until it finds what you have specified in the call to the function. We then store the length of the string for future use and have also declared a few string variables in the class to keep the information. These take the, hopefully obvious names of FirstName, MiddleName and Surname. We then store the surname using the value plus one returned by findRev because we don't want the space. We then find the first space from the start of the string using the find function and then store the first name using the left function. Once we have the end and the beginning anything else falls into the middle names category so we get it or them using the mid function.

QString strSqlString = "SELECT * FROM FictionalPeople WHERE FictionalPeople.First_Name = '" + firstName() + "' ";
if( middleName() != 0 && middleName().isEmpty() == false )
    strSqlString += "AND FictionalPeople.Middle_Names = '" + middleName() + "' ";
strSqlString += "AND FictionalPeople.Surname = '" + surname() + "'";

The next section of code builds the sql statement using the name values that we have just extracted from the name QString. It should be noted that the variable values that we add are surrounded by ' marks. This is a small detail but essential as the query wont work without them.

The final section is,

QSqlSelectCursor *cursor = new QSqlSelectCursor( strSqlString, FictionalPeopleConnection );
cursor->select();
cursor->next();
        
dataView2->refresh( cursor );
        
// clear the middle name
strMiddleName.truncate( 0 );

Here we run the query and make sure that we are at the start of the results and that they or it is ready to be read. Then we pass the cursor straight to the QDataview refresh function which takes a QSqlResult and automatically fills out the form.

One final tip for database development is never presume that the sql statement has worked. If something has failed it is more often than not a fault in the sql statement. When your sure that works check everything else.

Summary

This Chapter has been a brief look at database development with KDevelop focusing on the available widgets. It has tried to cover a lot of ground but there is much left for the reader to experiment with. This chapter could go on for a few hundred more pages about transactions and saving data etc, but hopefully it will be enough to get people started.

Beginning KDevelop Programming Version 3.x

Appendices

Downloads

PDF

ZIP

Contributions

Contacts

Author

Page Designer