Subversion Repositories Programming Utils

Rev

Rev 90 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
87 rm5248 1
#include <stdlib.h>
2
#include <stdio.h>
3
#include <QString>
90 rm5248 4
#include <QDebug>
87 rm5248 5
 
6
#include "dbaccess.h"
7
#include "dbexception.h"
8
 
9
DBAccess::DBAccess( const char* dbLocation, bool create )
10
{
11
    int error = 0;
12
    sqlite3_stmt* res;
13
    int result;
14
    int count;
15
    int flags = SQLITE_OPEN_READWRITE;
16
 
17
    if( create ){
18
        flags |= SQLITE_OPEN_CREATE;
19
    }
20
 
21
    error = sqlite3_open_v2( dbLocation, &m_sql, flags, NULL );
22
    if( error ){
88 rm5248 23
        QString exc = QString( "Can't open database: %1" ).arg( sqlite3_errmsg( m_sql ) );
24
        throw DBException( exc.toStdString() );
87 rm5248 25
    }
26
 
27
    if( create ){
90 rm5248 28
        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 )",
87 rm5248 29
                NULL, NULL, NULL );
30
        if( error ){
88 rm5248 31
            QString exc = QString( "Can't create database: %1" ).arg( sqlite3_errmsg( m_sql ) );
32
            throw DBException( exc.toStdString() );
87 rm5248 33
        }
34
    }
35
 
36
    records = getNumberOfRecords();
37
    allRecords.resize( records );
38
 
88 rm5248 39
    if( sqlite3_prepare_v2( m_sql, "SELECT train_id, Reporting_Mark, Road_Num, Type, Cost, Notes FROM Trains",
87 rm5248 40
                            -1, &res, NULL ) != SQLITE_OK ){
41
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
42
        throw DBException( exc.toStdString() );
43
    }
44
 
45
 
46
    count = 0;
47
    while( result = sqlite3_step( res ), result == SQLITE_ROW ){
48
        TrainRecord* newRecord = new TrainRecord(
88 rm5248 49
                    sqlite3_column_int( res, 0 ),
50
                    (const char*)sqlite3_column_text( res, 1 ),
51
                    sqlite3_column_int( res, 2 ),
52
                    (const char*)sqlite3_column_text( res, 3 ),
53
                    sqlite3_column_int( res, 4 ),
54
                    (const char*)sqlite3_column_text( res, 5 )
87 rm5248 55
                    );
56
        allRecords[ count ] = newRecord;
57
        count++;
58
    }
59
    if( result != SQLITE_DONE || count != records ){
60
        QString exc = QString( "Can't read from database: %1" ).arg( sqlite3_errmsg( m_sql ) );
61
        throw DBException( exc.toStdString() );
62
    }
63
 
64
}
65
 
66
DBAccess::~DBAccess(){
67
    for( uint32_t x = 0; x < allRecords.size(); x++ ){
68
        delete( allRecords[ x ] );
69
    }
70
 
71
    sqlite3_close_v2( m_sql );
72
}
73
 
74
int DBAccess::getNumberOfRecords(){
75
    sqlite3_stmt* res;
76
    int result;
77
 
78
    if( sqlite3_prepare_v2( m_sql, "SELECT COUNT( * ) FROM Trains", -1, &res, NULL ) != SQLITE_OK ){
79
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
80
        throw DBException( exc.toStdString() );
81
    }
82
 
83
    result = sqlite3_step( res );
84
    if( result == SQLITE_ROW ){
85
        return sqlite3_column_int( res, 0 );
86
    }else{
87
        throw DBException( "No records" );
88
    }
89
}
90
 
91
int DBAccess::getTotalCost(){
92
    sqlite3_stmt* res;
93
    int result;
94
 
95
    if( sqlite3_prepare_v2( m_sql, "SELECT SUM( Cost ) FROM Trains", -1, &res, NULL ) != SQLITE_OK ){
96
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
97
        throw DBException( exc.toStdString() );
98
    }
99
 
100
    result = sqlite3_step( res );
101
    if( result == SQLITE_ROW ){
102
        return sqlite3_column_int( res, 0 );
103
    }else{
104
        throw DBException( "No cost" );
105
    }
106
}
107
 
108
TrainRecord* DBAccess::getRecord(int num){
91 rm5248 109
    if( num < 0 || (unsigned)num > allRecords.size() ){
110
        qDebug() << "RETURN NULL";
87 rm5248 111
        return NULL;
112
    }
91 rm5248 113
 
87 rm5248 114
    return allRecords[ num ];
115
}
88 rm5248 116
 
117
void DBAccess::updateRecord( TrainRecord * rec ){
118
    sqlite3_stmt* res;
119
    int result;
90 rm5248 120
    char str[ 1024 ];
121
    size_t copy_len;
88 rm5248 122
    if( rec == NULL ){
123
        return;
124
    }
125
 
126
    //set the reporting mark
127
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Reporting_Mark = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
128
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
129
        throw DBException( exc.toStdString() );
130
    }
90 rm5248 131
 
132
    //note: for some reason on Windows, we can't just pass in the c_str that we get from std::string
133
    //copy the c_str to a char array
134
    copy_len = rec->getReportingMark().length() + 1;
135
    if( copy_len > 1024 ) copy_len = 1024;
136
    memcpy( str, rec->getReportingMark().c_str(), copy_len );
137
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 138
    sqlite3_bind_int( res, 2, rec->getKey() );
139
 
140
    result = sqlite3_step( res );
141
    if( result != SQLITE_DONE ){
142
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
143
        throw DBException( exc.toStdString() );
144
    }
145
 
146
    //set the road number
147
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Road_Num = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
148
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
149
        throw DBException( exc.toStdString() );
150
    }
151
    sqlite3_bind_int( res, 1, rec->getRoadNumber() );
152
    sqlite3_bind_int( res, 2, rec->getKey() );
153
 
154
    result = sqlite3_step( res );
155
    if( result != SQLITE_DONE ){
156
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
157
        throw DBException( exc.toStdString() );
158
    }
159
 
160
    //set the type
161
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Type = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
162
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
163
        throw DBException( exc.toStdString() );
164
    }
90 rm5248 165
    copy_len = rec->getStockType().length() + 1;
166
    if( copy_len > 1024 ) copy_len = 1024;
167
    memcpy( str, rec->getStockType().c_str(), copy_len );
168
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 169
    sqlite3_bind_int( res, 2, rec->getKey() );
170
 
171
    result = sqlite3_step( res );
172
    if( result != SQLITE_DONE ){
173
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
174
        throw DBException( exc.toStdString() );
175
    }
176
 
177
    //set the cost
178
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Cost = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
179
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
180
        throw DBException( exc.toStdString() );
181
    }
182
    sqlite3_bind_int( res, 1, rec->getCost() );
183
    sqlite3_bind_int( res, 2, rec->getKey() );
184
 
185
    result = sqlite3_step( res );
186
    if( result != SQLITE_DONE ){
187
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
188
        throw DBException( exc.toStdString() );
189
    }
190
 
191
    //set the notes
192
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Notes = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
193
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
194
        throw DBException( exc.toStdString() );
195
    }
90 rm5248 196
    copy_len = rec->getNotes().length() + 1;
197
    if( copy_len > 1024 ) copy_len = 1024;
198
    memcpy( str, rec->getNotes().c_str(), copy_len );
199
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 200
    sqlite3_bind_int( res, 2, rec->getKey() );
201
 
202
    result = sqlite3_step( res );
203
    if( result != SQLITE_DONE ){
204
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
205
        throw DBException( exc.toStdString() );
206
    }
207
}
208
 
209
TrainRecord* DBAccess::newRecord(){
210
    sqlite3_stmt* res;
211
    int result;
212
 
213
    //set the reporting mark to be NEW
214
    if( sqlite3_prepare_v2( m_sql, "INSERT INTO Trains ( Reporting_Mark ) VALUES ( 'NEW' )", -1, &res, NULL ) != SQLITE_OK ){
215
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
216
        throw DBException( exc.toStdString() );
217
    }
218
 
219
    result = sqlite3_step( res );
220
    if( result != SQLITE_DONE ){
221
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
222
        throw DBException( exc.toStdString() );
223
    }
224
 
225
    //get the new record and return a pointer to it.
226
    if( sqlite3_prepare_v2( m_sql, "SELECT train_id, reporting_mark, road_num, type, cost FROM Trains ORDER BY train_id DESC LIMIT 1",
227
                            -1, &res, NULL ) != SQLITE_OK ){
228
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
229
        throw DBException( exc.toStdString() );
230
    }
231
 
232
    result = sqlite3_step( res );
233
    if( result != SQLITE_ROW){
234
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
235
        throw DBException( exc.toStdString() );
236
    }
237
 
238
    TrainRecord* newRecord = new TrainRecord(
239
                sqlite3_column_int( res, 0 ),
240
                (const char*)sqlite3_column_text( res, 1 ),
241
                sqlite3_column_int( res, 2 ),
242
                (const char*)sqlite3_column_text( res, 3 ),
243
                sqlite3_column_int( res, 4 ),
244
                (const char*)sqlite3_column_text( res, 5 )
245
                );
91 rm5248 246
 
88 rm5248 247
    allRecords.push_back( newRecord );
248
 
249
    return newRecord;
250
}
251
 
252
void DBAccess::deleteRecord( TrainRecord * toDelete ){
253
    int result;
254
    if( toDelete == NULL ){
255
        return;
256
    }
257
 
258
    sqlite3_stmt* res;
259
    std::vector<TrainRecord*>::iterator it;
260
    if( sqlite3_prepare_v2( m_sql, "DELETE FROM Trains WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
261
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
262
        throw DBException( exc.toStdString() );
263
    }
264
 
265
    sqlite3_bind_int( res, 1, toDelete->getKey() );
266
    result = sqlite3_step( res );
267
    if( result != SQLITE_DONE ){
268
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
269
        throw DBException( exc.toStdString() );
270
    }
271
 
272
    //find this in our local cache
273
    for( it = allRecords.begin(); it != allRecords.end(); it++ ){
274
        if( *it == toDelete ){
275
            break;
276
        }
277
    }
278
 
279
    if( it != allRecords.end() ){
280
        allRecords.erase( it );
281
        delete toDelete;
282
    }
283
}