Introduction to MySQL

Table of contents:

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

Part I: Introduction to C++ and Qt 4

C++ Introduction

Classes

Introduction to Qt

Lists

Functions

Inheritance and Polymorphism

Part II: Higher-Level Programming

Libraries

Introduction to Design Patterns

QObject

Generics and Containers

Qt GUI Widgets

Concurrency

Validation and Regular Expressions

Parsing XML

Meta Objects, Properties, and Reflective Programming

More Design Patterns

Models and Views

Qt SQL Classes

Part III: C++ Language Reference

Types and Expressions

Scope and Storage Class

Statements and Control Structures

Memory Access

Chapter Summary

Inheritance in Detail

Miscellaneous Topics

Part IV: Programming Assignments

MP3 Jukebox Assignments

Part V: Appendices

MP3 Jukebox Assignments

Bibliography

MP3 Jukebox Assignments



An Introduction to Design Patterns in C++ with Qt 4
An Introduction to Design Patterns in C++ with Qt 4
ISBN: 0131879057
EAN: 2147483647
Year: 2004
Pages: 268

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net