Subversion Repositories Programming Utils

#include <stdlib.h>
#include <stdio.h>
#include <QString>
#include <QDebug>

#include "dbaccess.h"
#include "dbexception.h"

DBAccess::DBAccess( const char* dbLocation, bool create )
{
    int error = 0;
    sqlite3_stmt* res;
    int result;
    int count;
    int flags = SQLITE_OPEN_READWRITE;

    if( create ){
        flags |= SQLITE_OPEN_CREATE;
    }

    error = sqlite3_open_v2( dbLocation, &m_sql, flags, NULL );
    if( error ){
        QString exc = QString( "Can't open database: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    if( create ){
        error = sqlite3_exec( m_sql, "CREATE TABLE \"Trains\" (train_id INTEGER PRIMARY KEY NOT NULL,\n Reporting_Mark TEXT,\n Road_Num INTEGER,\n Type TEXT,\n Cost INTEGER,\n Notes TEXT )",
                NULL, NULL, NULL );
        if( error ){
            QString exc = QString( "Can't create database: %1" ).arg( sqlite3_errmsg( m_sql ) );
            throw DBException( exc.toStdString() );
        }
    }

    records = getNumberOfRecords();
    allRecords.resize( records );

    if( sqlite3_prepare_v2( m_sql, "SELECT train_id, Reporting_Mark, Road_Num, Type, Cost, Notes FROM Trains",
                            -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }


    count = 0;
    while( result = sqlite3_step( res ), result == SQLITE_ROW ){
        TrainRecord* newRecord = new TrainRecord(
                    sqlite3_column_int( res, 0 ),
                    (const char*)sqlite3_column_text( res, 1 ),
                    sqlite3_column_int( res, 2 ),
                    (const char*)sqlite3_column_text( res, 3 ),
                    sqlite3_column_int( res, 4 ),
                    (const char*)sqlite3_column_text( res, 5 )
                    );
        allRecords[ count ] = newRecord;
        count++;
    }
    if( result != SQLITE_DONE || count != records ){
        QString exc = QString( "Can't read from database: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

}

DBAccess::~DBAccess(){
    for( uint32_t x = 0; x < allRecords.size(); x++ ){
        delete( allRecords[ x ] );
    }

    sqlite3_close_v2( m_sql );
}

int DBAccess::getNumberOfRecords(){
    sqlite3_stmt* res;
    int result;

    if( sqlite3_prepare_v2( m_sql, "SELECT COUNT( * ) FROM Trains", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    result = sqlite3_step( res );
    if( result == SQLITE_ROW ){
        return sqlite3_column_int( res, 0 );
    }else{
        throw DBException( "No records" );
    }
}

int DBAccess::getTotalCost(){
    sqlite3_stmt* res;
    int result;

    if( sqlite3_prepare_v2( m_sql, "SELECT SUM( Cost ) FROM Trains", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    result = sqlite3_step( res );
    if( result == SQLITE_ROW ){
        return sqlite3_column_int( res, 0 );
    }else{
        throw DBException( "No cost" );
    }
}

TrainRecord* DBAccess::getRecord(int num){
    if( num < 0 || (unsigned)num > allRecords.size() ){
        qDebug() << "RETURN NULL";
        return NULL;
    }

    return allRecords[ num ];
}

void DBAccess::updateRecord( TrainRecord * rec ){
    sqlite3_stmt* res;
    int result;
    char str[ 1024 ];
    size_t copy_len;
    if( rec == NULL ){
        return;
    }

    //set the reporting mark
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Reporting_Mark = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //note: for some reason on Windows, we can't just pass in the c_str that we get from std::string
    //copy the c_str to a char array
    copy_len = rec->getReportingMark().length() + 1;
    if( copy_len > 1024 ) copy_len = 1024;
    memcpy( str, rec->getReportingMark().c_str(), copy_len );
    sqlite3_bind_text( res, 1, str, -1, NULL );
    sqlite3_bind_int( res, 2, rec->getKey() );

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //set the road number
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Road_Num = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }
    sqlite3_bind_int( res, 1, rec->getRoadNumber() );
    sqlite3_bind_int( res, 2, rec->getKey() );

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //set the type
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Type = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }
    copy_len = rec->getStockType().length() + 1;
    if( copy_len > 1024 ) copy_len = 1024;
    memcpy( str, rec->getStockType().c_str(), copy_len );
    sqlite3_bind_text( res, 1, str, -1, NULL );
    sqlite3_bind_int( res, 2, rec->getKey() );

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //set the cost
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Cost = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }
    sqlite3_bind_int( res, 1, rec->getCost() );
    sqlite3_bind_int( res, 2, rec->getKey() );

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //set the notes
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Notes = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }
    copy_len = rec->getNotes().length() + 1;
    if( copy_len > 1024 ) copy_len = 1024;
    memcpy( str, rec->getNotes().c_str(), copy_len );
    sqlite3_bind_text( res, 1, str, -1, NULL );
    sqlite3_bind_int( res, 2, rec->getKey() );

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }
}

TrainRecord* DBAccess::newRecord(){
    sqlite3_stmt* res;
    int result;

    //set the reporting mark to be NEW
    if( sqlite3_prepare_v2( m_sql, "INSERT INTO Trains ( Reporting_Mark ) VALUES ( 'NEW' )", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //get the new record and return a pointer to it.
    if( sqlite3_prepare_v2( m_sql, "SELECT train_id, reporting_mark, road_num, type, cost FROM Trains ORDER BY train_id DESC LIMIT 1",
                            -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    result = sqlite3_step( res );
    if( result != SQLITE_ROW){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    TrainRecord* newRecord = new TrainRecord(
                sqlite3_column_int( res, 0 ),
                (const char*)sqlite3_column_text( res, 1 ),
                sqlite3_column_int( res, 2 ),
                (const char*)sqlite3_column_text( res, 3 ),
                sqlite3_column_int( res, 4 ),
                (const char*)sqlite3_column_text( res, 5 )
                );

    allRecords.push_back( newRecord );

    return newRecord;
}

void DBAccess::deleteRecord( TrainRecord * toDelete ){
    int result;
    if( toDelete == NULL ){
        return;
    }

    sqlite3_stmt* res;
    std::vector<TrainRecord*>::iterator it;
    if( sqlite3_prepare_v2( m_sql, "DELETE FROM Trains WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    sqlite3_bind_int( res, 1, toDelete->getKey() );
    result = sqlite3_step( res );
    if( result != SQLITE_DONE ){
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
        throw DBException( exc.toStdString() );
    }

    //find this in our local cache
    for( it = allRecords.begin(); it != allRecords.end(); it++ ){
        if( *it == toDelete ){
            break;
        }
    }

    if( it != allRecords.end() ){
        allRecords.erase( it );
        delete toDelete;
    }
}