Subversion Repositories Programming Utils

Rev

Rev 88 | Go to most recent revision | 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){
109
    if( num < 0 || num > records ){
110
        printf( "RETURN NULL" );
111
        return NULL;
112
    }
113
    return allRecords[ num ];
114
}
88 rm5248 115
 
116
void DBAccess::updateRecord( TrainRecord * rec ){
117
    sqlite3_stmt* res;
118
    int result;
90 rm5248 119
    char str[ 1024 ];
120
    size_t copy_len;
88 rm5248 121
    if( rec == NULL ){
122
        return;
123
    }
124
 
125
    //set the reporting mark
126
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Reporting_Mark = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
127
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
128
        throw DBException( exc.toStdString() );
129
    }
90 rm5248 130
 
131
    //note: for some reason on Windows, we can't just pass in the c_str that we get from std::string
132
    //copy the c_str to a char array
133
    copy_len = rec->getReportingMark().length() + 1;
134
    if( copy_len > 1024 ) copy_len = 1024;
135
    memcpy( str, rec->getReportingMark().c_str(), copy_len );
136
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 137
    sqlite3_bind_int( res, 2, rec->getKey() );
138
 
139
    result = sqlite3_step( res );
140
    if( result != SQLITE_DONE ){
141
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
142
        throw DBException( exc.toStdString() );
143
    }
144
 
145
    //set the road number
146
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Road_Num = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
147
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
148
        throw DBException( exc.toStdString() );
149
    }
150
    sqlite3_bind_int( res, 1, rec->getRoadNumber() );
151
    sqlite3_bind_int( res, 2, rec->getKey() );
152
 
153
    result = sqlite3_step( res );
154
    if( result != SQLITE_DONE ){
155
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
156
        throw DBException( exc.toStdString() );
157
    }
158
 
159
    //set the type
160
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Type = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
161
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
162
        throw DBException( exc.toStdString() );
163
    }
90 rm5248 164
    copy_len = rec->getStockType().length() + 1;
165
    if( copy_len > 1024 ) copy_len = 1024;
166
    memcpy( str, rec->getStockType().c_str(), copy_len );
167
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 168
    sqlite3_bind_int( res, 2, rec->getKey() );
169
 
170
    result = sqlite3_step( res );
171
    if( result != SQLITE_DONE ){
172
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
173
        throw DBException( exc.toStdString() );
174
    }
175
 
176
    //set the cost
177
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Cost = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
178
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
179
        throw DBException( exc.toStdString() );
180
    }
181
    sqlite3_bind_int( res, 1, rec->getCost() );
182
    sqlite3_bind_int( res, 2, rec->getKey() );
183
 
184
    result = sqlite3_step( res );
185
    if( result != SQLITE_DONE ){
186
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
187
        throw DBException( exc.toStdString() );
188
    }
189
 
190
    //set the notes
191
    if( sqlite3_prepare_v2( m_sql, "UPDATE Trains SET Notes = ? WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
192
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
193
        throw DBException( exc.toStdString() );
194
    }
90 rm5248 195
    copy_len = rec->getNotes().length() + 1;
196
    if( copy_len > 1024 ) copy_len = 1024;
197
    memcpy( str, rec->getNotes().c_str(), copy_len );
198
    sqlite3_bind_text( res, 1, str, -1, NULL );
88 rm5248 199
    sqlite3_bind_int( res, 2, rec->getKey() );
200
 
201
    result = sqlite3_step( res );
202
    if( result != SQLITE_DONE ){
203
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
204
        throw DBException( exc.toStdString() );
205
    }
206
}
207
 
208
TrainRecord* DBAccess::newRecord(){
209
    sqlite3_stmt* res;
210
    int result;
211
 
212
    //set the reporting mark to be NEW
213
    if( sqlite3_prepare_v2( m_sql, "INSERT INTO Trains ( Reporting_Mark ) VALUES ( 'NEW' )", -1, &res, NULL ) != SQLITE_OK ){
214
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
215
        throw DBException( exc.toStdString() );
216
    }
217
 
218
    result = sqlite3_step( res );
219
    if( result != SQLITE_DONE ){
220
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
221
        throw DBException( exc.toStdString() );
222
    }
223
 
224
    //get the new record and return a pointer to it.
225
    if( sqlite3_prepare_v2( m_sql, "SELECT train_id, reporting_mark, road_num, type, cost FROM Trains ORDER BY train_id DESC LIMIT 1",
226
                            -1, &res, NULL ) != SQLITE_OK ){
227
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
228
        throw DBException( exc.toStdString() );
229
    }
230
 
231
    result = sqlite3_step( res );
232
    if( result != SQLITE_ROW){
233
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
234
        throw DBException( exc.toStdString() );
235
    }
236
 
237
    TrainRecord* newRecord = new TrainRecord(
238
                sqlite3_column_int( res, 0 ),
239
                (const char*)sqlite3_column_text( res, 1 ),
240
                sqlite3_column_int( res, 2 ),
241
                (const char*)sqlite3_column_text( res, 3 ),
242
                sqlite3_column_int( res, 4 ),
243
                (const char*)sqlite3_column_text( res, 5 )
244
                );
245
    allRecords.push_back( newRecord );
246
 
247
    return newRecord;
248
}
249
 
250
void DBAccess::deleteRecord( TrainRecord * toDelete ){
251
    int result;
252
    if( toDelete == NULL ){
253
        return;
254
    }
255
 
256
    sqlite3_stmt* res;
257
    std::vector<TrainRecord*>::iterator it;
258
    if( sqlite3_prepare_v2( m_sql, "DELETE FROM Trains WHERE train_id = ?", -1, &res, NULL ) != SQLITE_OK ){
259
        QString exc = QString( "Can't create prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
260
        throw DBException( exc.toStdString() );
261
    }
262
 
263
    sqlite3_bind_int( res, 1, toDelete->getKey() );
264
    result = sqlite3_step( res );
265
    if( result != SQLITE_DONE ){
266
        QString exc = QString( "Can't execute prepared statement: %1" ).arg( sqlite3_errmsg( m_sql ) );
267
        throw DBException( exc.toStdString() );
268
    }
269
 
270
    //find this in our local cache
271
    for( it = allRecords.begin(); it != allRecords.end(); it++ ){
272
        if( *it == toDelete ){
273
            break;
274
        }
275
    }
276
 
277
    if( it != allRecords.end() ){
278
        allRecords.erase( it );
279
        delete toDelete;
280
    }
281
}