134 | | |
135 | | CBR::CBR(string _filename, string _tablename, string _cols[], uint32_t _len) |
136 | | { |
137 | | /* Store database properties. */ |
138 | | filename = _filename; |
139 | | tablename = _tablename; |
140 | | numColumns = _len; |
141 | | |
142 | | /* Create the database (or open it if it already exists). */ |
143 | | OpenDatabase(); |
144 | | |
145 | | /* Generate the command that will create the initial table within the |
146 | | * CROSS database. */ |
147 | | command = "CREATE TABLE " + tablename + "("; |
148 | | for(size_t i = 0; i < numColumns; i++) { |
149 | | command += _cols[i] + " FLOAT"; |
150 | | |
151 | | /* If this column is not the last entry, add a comma to the command in |
152 | | * preperation for the next entry. */ |
153 | | if(i != numColumns - 1) |
154 | | command += ", "; |
155 | | } |
156 | | command += ");"; |
157 | | |
158 | | /* Execute the generated command. At this point, the database is ready for |
159 | | * use. */ |
160 | | ExecuteCommand(); |
161 | | } |
162 | | |
163 | | |
164 | | CBR::CBR(string _filename, string _tablename, string _cols[], \ |
165 | | string _primcols[], uint32_t _len, uint32_t _primlen) |
166 | | { |
167 | | /* Store database properties. */ |
168 | | filename = _filename; |
169 | | tablename = _tablename; |
170 | | numColumns = _len; |
171 | | |
172 | | /* Create the database (or open it if it already exists). */ |
173 | | OpenDatabase(); |
174 | | |
175 | | /* Generate the command that will create the initial table within the |
176 | | * CROSS database with primary keys. */ |
177 | | command = "CREATE TABLE " + tablename + "("; |
178 | | for(size_t i = 0; i < numColumns; i++) { |
179 | | command += _cols[i] + " FLOAT, "; |
180 | | } |
181 | | |
182 | | command += "PRIMARY KEY ("; |
183 | | for(size_t j = 0; j < _primlen; j++) { |
184 | | command += _primcols[j]; |
185 | | |
186 | | /* If this column is not the last entry, add a comma to the command in |
187 | | * preperation for the next entry. */ |
188 | | if(j != _primlen - 1) |
189 | | command += ", "; |
190 | | } |
191 | | command += "));"; |
192 | | |
193 | | /* Execute the generated command. At this point, the database is ready for |
194 | | * use. */ |
195 | | ExecuteCommand(); |
196 | | } |
197 | | |
198 | | |
199 | | CBR::~CBR() |
200 | | { |
201 | | /* Generate the sqlite command to delete a table and all of its contents, |
202 | | * and then execute it. */ |
203 | | command = "drop table " + tablename; |
204 | | ExecuteCommand(); |
205 | | |
206 | | /* Tell sqlite to clean up the database. */ |
207 | | command = "vacuum"; |
208 | | ExecuteCommand(); |
209 | | } |
210 | | |
211 | | |
212 | | int32_t |
213 | | CBR::OpenDatabase() |
214 | | { |
215 | | int32_t rc = sqlite3_open(filename.c_str(), &db); |
216 | | if(rc) { |
217 | | WARNING("Can't open database: %s\n", sqlite3_errmsg(db)); |
218 | | sqlite3_close(db); |
219 | | exit(1); |
220 | | } |
221 | | |
222 | | return rc; |
223 | | } |
224 | | |
225 | | |
226 | | int32_t |
227 | | CBR::ExecuteCommand() |
228 | | { |
229 | | char *zErrMsg = 0; |
230 | | |
231 | | int32_t rc = sqlite3_exec(db, command.c_str(), callback, 0, &zErrMsg); |
232 | | if(rc != SQLITE_OK) { |
233 | | WARNING("SQL error: %s: %s\n", zErrMsg, command.c_str()); |
234 | | sqlite3_free(zErrMsg); |
235 | | } |
236 | | |
237 | | return rc; |
238 | | } |
239 | | |
240 | | |
241 | | int32_t |
242 | | CBR::ExecuteSearchCommand(float *_retvals) |
243 | | { |
244 | | sqlite3_stmt *pStatement; |
245 | | |
246 | | int32_t rc = sqlite3_prepare_v2(db, command.c_str(), -1, &pStatement, NULL); |
247 | | if(rc == SQLITE_OK) { |
248 | | if(sqlite3_step(pStatement) == SQLITE_ROW) { |
249 | | for(size_t i = 0; i < numColumns; ++i) { |
250 | | _retvals[i] = sqlite3_column_double(pStatement, i); |
251 | | } |
252 | | } else { |
253 | | LOG("CBR:: No matched results returning default.\n"); |
254 | | rc = 31337; |
255 | | } |
256 | | } else { |
257 | | WARNING("CBR:: Error executing SQL statement. rc = %i\n%s\n", rc, command.c_str()); |
258 | | } |
259 | | |
260 | | sqlite3_finalize(pStatement); |
261 | | |
262 | | return rc; |
263 | | } |
264 | | |
265 | | |
266 | | void |
267 | | CBR::Print() |
268 | | { |
269 | | /* Generate the sqlite command to print the database, which is effectively a |
270 | | * 'select all elements' command, and then execute it. */ |
271 | | command = "select " + tablename + ".* from " + tablename + ";"; |
272 | | |
273 | | ExecuteCommand(); |
274 | | LOG("database %s, table %s:\n", filename.c_str(), tablename.c_str()); |
275 | | } |
276 | | |
277 | | |
278 | | int32_t |
279 | | CBR::Search(string _names[], int32_t *_ops, float *_vals, uint32_t _n, \ |
280 | | float *_retvals) |
281 | | { |
282 | | char str_buffer[64]; |
283 | | const string ops_str[] = {"==", "!=", ">", ">=", "<", "<="}; |
284 | | |
285 | | command = "select " + tablename + ".* from " + tablename + " where "; |
286 | | |
287 | | for(size_t i = 0; i < _n; i++) { |
288 | | /* Make sure that the passed ops value is valid. */ |
289 | | if((_ops[i] < 0) || (_ops[i] > 5)) { |
290 | | ERROR(1, "Error: cbr_search(), invalid ops id : %d\n", _ops[i]); |
291 | | } |
292 | | |
293 | | command += _names[i] + ops_str[_ops[i]]; |
294 | | |
295 | | |
296 | | sprintf(str_buffer, "%E", _vals[i]); |
297 | | command += string(str_buffer); |
298 | | |
299 | | if(i < _n - 1) |
300 | | command += " AND "; |
301 | | else |
302 | | command += " order by utility desc;"; |
303 | | |
304 | | } |
305 | | //LOG("CBR::Search - command: %s\n", command.c_str()); |
306 | | |
307 | | return ExecuteSearchCommand(_retvals); |
308 | | } |
309 | | |
310 | | |
311 | | int32_t |
312 | | CBR::SearchSum(string _name, float *_retvals) |
313 | | { |
314 | | command = "select SUM(" + tablename + "." + _name + ") from " + tablename + ";"; |
315 | | |
316 | | return ExecuteSearchCommand(_retvals); |
317 | | } |
318 | | |
319 | | |
320 | | int32_t |
321 | | CBR::SearchRand(string _names[], int32_t *_ops, float *_vals, uint32_t _n, \ |
322 | | float *_retvals) |
323 | | { |
324 | | char str_buffer[64]; |
325 | | const char *ops_str[] = {"==", "!=", ">", ">=", "<", "<="}; |
326 | | |
327 | | command = "select " + tablename + ".* from " + tablename + " where "; |
328 | | |
329 | | for(size_t i = 0; i < _n; i++) { |
330 | | /* Make sure that the passed ops value is valid. */ |
331 | | if((_ops[i] < 0) || (_ops[i] > 5)) { |
332 | | ERROR(1, "Error: cbr_search(), invalid ops id : %d\n", _ops[i]); |
333 | | } |
334 | | |
335 | | command += _names[i] + ops_str[_ops[i]]; |
336 | | |
337 | | sprintf(str_buffer, "%E", _vals[i]); |
338 | | command += str_buffer; |
339 | | |
340 | | if(i < _n - 1) |
341 | | command += " AND "; |
342 | | else |
343 | | command += " order by RAND();"; |
344 | | } |
345 | | |
346 | | return ExecuteSearchCommand(_retvals); |
347 | | } |
348 | | |
349 | | |
350 | | int32_t |
351 | | CBR::Update(string _where[], string _set[], float *_wherevals, float *_setvals, |
352 | | uint32_t _wherelen, uint32_t _setlen) |
353 | | { |
354 | | char str_buffer[64]; |
355 | | |
356 | | /* Generate the command to update the table. */ |
357 | | command = "UPDATE " + tablename + " SET "; |
358 | | |
359 | | for(size_t i = 0; i < _setlen; i++) { |
360 | | command += _set[i] + " = "; |
361 | | sprintf(str_buffer, "%f", _setvals[i]); |
362 | | command += string(str_buffer) + " "; |
363 | | |
364 | | if(i != _setlen - 1) |
365 | | command += ", "; |
366 | | } |
367 | | command += " WHERE "; |
368 | | |
369 | | for(size_t j = 0; j < _wherelen; j++) { |
370 | | command += _where[j] + " = "; |
371 | | sprintf(str_buffer, "%f", _wherevals[j]); |
372 | | command += string(str_buffer) + " "; |
373 | | |
374 | | if(j != _wherelen - 1) |
375 | | command += "AND "; |
376 | | } |
377 | | command += ";"; |
378 | | |
379 | | return ExecuteCommand(); |
380 | | } |
381 | | |
382 | | |
383 | | int32_t |
384 | | CBR::AddRow(string _cols[], float *_vals, uint32_t _len) |
385 | | { |
386 | | char str_buffer[64]; |
387 | | |
388 | | command = "insert into " + tablename + " ("; |
389 | | |
390 | | for(size_t i = 0; i < _len; i++) { |
391 | | command += _cols[i]; |
392 | | |
393 | | if(i != numColumns - 1) |
394 | | command += ", "; |
395 | | } |
396 | | command += ") values("; |
397 | | |
398 | | for(size_t j = 0; j < _len; j++) { |
399 | | // TODO I have no idea what the below question is about. |
400 | | // ???? how to fill the values if numColumns != _len |
401 | | // assume = in the following |
402 | | sprintf(str_buffer, "%f", _vals[j]); |
403 | | command += str_buffer; |
404 | | |
405 | | if(j != numColumns - 1) |
406 | | command += ", "; |
407 | | } |
408 | | command += ");"; |
409 | | |
410 | | return ExecuteCommand(); |
411 | | } |
412 | | |