/***********************************************************************
* connection.h
*
* Provides a helper function to generate a database connection
* and populate the database. You don't need this particular
* function, but you'll have to do something like this.
*
* Written by Paul Bonamy - 30 July 2010
************************************************************************/

#ifndef CONNECTION_H
#define CONNECTION_H

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QVariant>     // you need this if you're using bindValue()

static bool createConnection()
{
    /*
     * Qt employs what amounts to an application global database manager.
     * The manager is capable of supporting many different databases at
     * once, but you definitely need to tell it what sort of database
     * it's linked to. We'll do that here:
     *
     * addDatabase adds a new database to the manager. You must specify
     * a database drive (in this case, the one for SQLite), and may
     * specify a name for the database. If no name is specified (as here)
     * the database is made the default for all future interactions.
     *
     * setDatabaseName provides connection information for the database.
     * for SQLite, this should receive either a file name or the special
     * string ":memory:", which specifies a database to be held in ram.
     * If the database file exists, it will be connected to, if not,
     * it will be created.
     *
     * Calling open() is the final step of setting up the connection.
     * You cannot use the database connection until it's been open()'d
     * so make sure this actually gets called. It'll return false if
     * something goes wrong and prevents it from openning the database.
     */
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("inventory.sqlite");
    if (!db.open()) {
        return false;   // this is only error we check for
    }

    /*
     * The QSqlQuery object lets you run arbitrary queries on the
     * database. It provides much the same functionality as SQLite's
     * exec and prepare/step/finalize systems.
     */
    QSqlQuery query;
    
    /*
     * Calling exec() and passing in a string will automatically
     * try and execute the query on the database. If no database
     * name is specifies, the default database will be used. You
     * may specify any sort of query you like.
     *
     * exec() will return true or false, depending on whether the
     * the query executed successfully or not.
     */
    query.exec("CREATE TABLE items (id INTEGER PRIMARY KEY, "
               "description TEXT, quantity INTEGER)");
    
    query.exec("INSERT INTO items VALUES(101, 'Pens', 10)");
    query.exec("INSERT INTO items VALUES(102, 'Pencils', 75)");
    query.exec("INSERT INTO items VALUES(103, 'Paper (reems)', 5)");
    
    /*
     * Prepare is mostly used in the context of a prepared statement
     * with placeholders into which we will bind values. The call
     * to prepare 'compiles' the query, and gets it ready to run.
     */
    query.prepare("INSERT INTO items VALUES(:id, :desc, :qty)");
    
    /*
     * There are two variants of bindValue. One takes a placeholder
     * number, as with SQLite's bind() functions. The other (used
     * here), takes in the placeholder and the value to bind into
     * that place.
     *
     * Note that you'll get really weird compiler errors if you
     * don't include the QVariant library. (QVariant provides
     * a way of converting arbitrary types for use in bind)
     */
    query.bindValue(":id", 104);
    query.bindValue(":desc", "Paper (sheets)");
    query.bindValue(":qty", 0);
    
    /*
     * Calling exec() on a query without a parameter will cause
     * the query to execute a prepared and bound query.
     */
    query.exec();
    
    query.bindValue(":id", 105);
    query.bindValue(":desc", "Paper Clips");
    query.bindValue(":qty", 17);
    query.exec();
    
    return true;
}

#endif