/*

mysql.cpp
---------

Compile with
     /usr/local/qt-4.6.3/bin/qmake -project -o mysql.pro
     /usr/local/qt-4.6.3/bin/qmake
     make

*/


#include <QtSql>
#include <iostream>
#include <cstdlib>

using std::cout;
using std::cerr;
using std::endl;


int main()
{
     QString sql;
     bool status;

     QSqlDatabase connection = QSqlDatabase::addDatabase("QMYSQL");
     connection.setHostName("localhost");
     connection.setPort(3306);
     connection.setDatabaseName("test");
     if (!connection.open())
     {
          cerr << "cannot connect to database!" << connection.lastError().text().toStdString() << endl;
          exit(EXIT_FAILURE);
     }

     QSqlQuery query(connection);

     status = query.exec("DROP TABLE IF EXISTS phone");
     if (!status)
          cerr << "cannot drop 'phone' table!" << endl;

     status = query.exec("DROP TABLE IF EXISTS person");
     if (!status)
          cerr << "cannot drop 'person' table!" << endl;

     sql =
          "CREATE TABLE person"
          "("
               "id INT AUTO_INCREMENT PRIMARY KEY, "
               "surname VARCHAR(32), "
               "name VARCHAR(32), "
               "nickname varchar(16)"
          ") "
          "ENGINE=InnoDB DEFAULT CHARSET=utf8";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot create 'person' table!" << endl;

     sql =
          "CREATE TABLE phone"
          "("
               "id INT AUTO_INCREMENT PRIMARY KEY, "
               "person INT, "
               "phone_number VARCHAR(32) NOT NULL, "
               "FOREIGN KEY(person) REFERENCES person(id)"
          ") "
          "ENGINE=InnoDB";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot create 'phone' table!" << endl;

     sql = "INSERT INTO person(surname, name, nickname) VALUES('Smith', 'Jonathan', 'Joe')";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot insert into 'person' table!" << endl;

     sql = "INSERT INTO person(surname, name, nickname) VALUES('Duval', 'Jean-Luc', '')";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot insert into 'person' table!" << endl;

     sql = QString("INSERT INTO person(surname, name, nickname) VALUES('Petrovi") + QChar(0x107) + QString("', 'Petar', NULL)");
     status = query.exec(sql);
     if (!status)
          cerr << "cannot insert into 'person' table!" << endl;

     sql = "SELECT surname, name, nickname FROM person";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot select from 'person' table!" << endl;
     else
          while (query.next())
               cout << query.value(0).toString().toStdString() << "," << query.value(1).toString().toStdString() << "," <<
                    query.value(2).toString().toStdString() << endl;

     sql = "INSERT INTO phone(person, phone_number) VALUES(3, '38111987654')";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot insert into 'phone' table!" << endl;

     sql = "SELECT person, phone_number FROM phone";
     status = query.exec(sql);
     if (!status)
          cerr << "cannot select from 'phone' table!" << endl;
     else
          while (query.next())
               cout << query.value(0).toString().toStdString() << "," << query.value(1).toString().toStdString() << "," << endl;

     return EXIT_SUCCESS;
}