Snippets‎ > ‎

C++ using MySql and a Doctor Who Database

posted Sep 15, 2013, 8:54 AM by Al Haines

I have been playing with a Doctor Who database that I have created using data from around the web and have used it to learn how to use MySql with c++.  The data is a programmers nightmare because it contains unformatted lines with line-feeds and quotes.  I did not give much thought into how i would use the data when I collected it.  SO I found that the data was unusable by conventional means in the unformatted file.  MySql spit out several errors when I tried to import the file and apps like Excel and Calc just mangled the data into an unusable mess.    The hunt was on to find a way to convert the data into something that MySql could use. Needless to say I spent many hours reading dozens of books on databases and programming languages before it hit me.  I have to clean up the data first.  The Boost tokenizer code that I found could read a line of code just fine.  All I had to do was write some code to read the unformatted file and write it as a formatted sql file, this was easier then I first thought (see csv2sql.cpp).  I slowly updated my code to import it into MySql  (see csv2mysql.cpp) and found that large file import was a real pain.  I split the INSERT lines into smaller more manageable code, one INSERT per line of data.  This made for a larger sql file but MySql ate it up just fine.    Once I got the data into the database I begin writing code to extract useful information.  The result was dr00.cpp which can read the database, ask the user for a number and display all the episodes that matched the Doctor's number.


Example:

Enter Doctors number : 1
 The following episodes starred Doctor number 1 :

 [ An Unearthly Child] 
 [ The Daleks] 
 [ The Edge of Destruction] 
 [ Marco Polo] 
 [ The Keys of Marinus] 
 [ The Aztecs] 
 [ The Sensorites] 
 [ The Reign of Terror] 
 [ Planet of Giants] 
 [ The Dalek Invasion of Earth] 
 [ The Rescue] 
 [ The Romans] 
 [ The Web Planet] 
 [ The Crusade] 
 [ The Space Museum] 
 [ The Chase] 
 [ The Time Meddler] 
 [ Galaxy 4] 
 [ Mission to the Unknown] 
 [ The Myth Makers] 
 [ The Daleks Masterplan] 
 [ The Massacre] 
 [ The Ark] 
 [ The Celestial Toymaker] 
 [ The Gunfighters] 
 [ The Savages] 
 [ The War Machines] 
 [ The Smugglers] 
 [ The Tenth Planet] 

I wanted a Gui and I have played with Zenity a little so I formatted the output into a command line script using Zenity selector boxes and and information box.  The mysqlZ00.cpp shows how that could present a list/selector of the 11 Doctors and then after the user selected one the Titles that appeared in would request which episode to show a description of using an information box.   The code is my contribution to the c++ world and I place it into Open Source for all to share and modify. 


A short note on compiling my code.


I do not like the `make` method of compiling code.  It drives me crazy!  The g2 script will compile most c++ code just fine and if you find that you need more libraries then add them once!  I save this script in a folder called s in my PATH


I have included examples of how to convert an csv file to an sql file then load it into mysql.

I use the Boost tokenizer to read the csv because data can be funky and needs to be treated as such.

C++ does not have a built-in GUI so I use Zenity which is a great command line extension for creating a nice GUI.

The required file `con.cpp` holds your server password and stuff and is not included.  You to copy the following code into a file and save it.  The test database must exist in MySql.
 
#define SERVER "localhost"
#define USER "root"
#define PASSWORD "password"
#define DATABASE "test"

I hope someone can use this code to learn from it, improve it  and pass it on.

ċ
csv2mysql.cpp
(8k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
csv2sql.cpp
(6k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
dr00.cpp
(2k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
drs.sql
(1k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
drwho.csv
(328k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
drwho.sql
(335k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
g2
(1k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
mysqlZ00.cpp
(4k)
Al Haines,
Sep 15, 2013, 8:54 AM
ċ
sql2mysql.cpp
(5k)
Al Haines,
Sep 15, 2013, 8:54 AM
Comments