Rev 90 |
Blame |
Compare with Previous |
Last modification |
View Log
| RSS feed
#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;
}
}