Introduction to MySQL

After you have installed MySQL on your system, you can create a database from its shell by entering mysql as the "root" or admin user, as shown here.[2]

[2] If you did not install MySQL yourself, then you may need to ask a system admin to create a MySQL account for you.

/home/files> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Next, cut and paste the following lines from Example 18.1 into the MySQL shell.

Example 18.1. src/sql/dbcreate.sql

create database mp3db;
use mp3db;
grant all on mp3db.* to 'mp3user'@'localhost' identified by 'mp3dbpw';
grant all on mp3db.* to mp3user identified by 'mp3dbpw';

The last two lines grant user permissions for mp3user, which is the userid we will use for all subsequent operations on the data.

Connecting to MySQL from Qt

Connecting initially to the database requires four pieces of information: user, password, database, and host, as shown in Example 18.2.

Example 18.2. src/sql/mp3db.cpp

[ . . . . ]

bool Mp3db::connect()
{
 QSqlDatabase db;
 db = QSqlDatabase::addDatabase("QMYSQL");
 db.setDatabaseName("mp3user");
 db.setUserName("mp3db");
 db.setPassword("mp3dbpw");
 if (!db.open()) {
 qCritical("Cannot open database: %s (%s)",
 db.lastError().text().toLatin1().data(),
 qt_error_string().toLocal8Bit().data());
 return false;
 }
 return true;
}

After the database connection has been opened, we use a very powerful class called QSqlQuery, which has a member function exec() that enables us to submit standard SQL statements to the database.

Defining a Table

Each database has a collection of tables. A table is very much like an array of struct, where each column corresponds to a data member. To define a table, we must describe each of the columns, which can also be thought of as fields, properties, or data members.

Example 18.3. src/sql/filetagger.sql

CREATE TABLE FileTagger (
 Artist varchar(100),
 TrackTitle varchar(100),
 AlbumTitle varchar(100),
 TrackNumber varchar(10),
 Genre varchar(20),
 Comment varchar(200),
 Preference varchar(20),
 Filename varchar(200),
 PRIMARY KEY(Filename),
 INDEX(Preference),
 INDEX(Genre)
);

Example 18.3 defines a single table in SQL called FileTagger. This table has a structure that includes columns for each of the properties of the FileTagger class. There are three ways you can create this table.

1.	Cut and paste the contents of Example 18.3 into the mysql shell.
2.	Source the file from the mysql shell.   > mysql mp3db -u mp3user -p Enter password: XXXXX mysql> source filetagger.sql
3.	Pass it as a string to QSqlQuery::exec().

If you don't want to keep reentering the db/user/pw each time you run mysql's command line shell, you can set default values in ~/.my.cnf (*nix) or c:mysqlmy.cnf (Win32).

[mysql]
user=mp3user
password=mp3dbpw
database=mp3db

 

Inserting Rows

We wish to extract data from the ID3 tags of MP3 files and import them into the FileTagger table. The first step is to prepare an SQL statement for inserting the data, as shown in Example 18.4. Prepared statements are useful when we must execute the same SQL statement repeatedlythe server only needs to parse the string once.

Example 18.4. src/sql/mp3db.cpp

[ . . . . ]

Mp3db::Mp3db() {
 connect();
 m_insertQuery.prepare("INSERT INTO FileTagger ("
 "Artist, TrackTitle, AlbumTitle,
 TrackNumber, Genre, "
 "Comment, Preference, Filename) VALUES
 (?,?,?,?,?,?,?,?)");
}

We left the ? character in the parts of the SQL prepared statement where we later wish to bind values. When we have a FileTagger object with the desired data to import, we call addFile(), shown in Example 18.5. This method binds the values into the prepared statement.

Example 18.5. src/sql/mp3db.cpp

[ . . . . ]

void Mp3db::addFile(FileTagger* song) {
 m_insertQuery.addBindValue(song->getArtist());
 m_insertQuery.addBindValue(song->getTrackTitle());
 m_insertQuery.addBindValue(song->getAlbumTitle());
 m_insertQuery.addBindValue(song->getTrackNumber());
 m_insertQuery.addBindValue(song->getGenre());
 m_insertQuery.addBindValue(song->getComment());
 m_insertQuery.addBindValue(song->getPreference());
 m_insertQuery.addBindValue(song->getFilename());
 m_insertQuery.exec();
}

Queries and Result Sets