src/DAL/Query.cpp

   1 /*
   2  **     Query.cpp
   3  **
   4  **     Published / author: 2005-08-12 / grymse@alhem.net
   5  **/
   6
   7 /*
   8 Copyright (C) 2001-2006  Anders Hedstrom
   9
  10 This program is free software; you can redistribute it and/or
  11 modify it under the terms of the GNU General Public License
  12 as published by the Free Software Foundation; either version 2
  13 of the License, or (at your option) any later version.
  14
  15 This program is distributed in the hope that it will be useful,
  16 but WITHOUT ANY WARRANTY; without even the implied warranty of
  17 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18 GNU General Public License for more details.
  19
  20 You should have received a copy of the GNU General Public License
  21 along with this program; if not, write to the Free Software
  22 Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  23 */
  24 #ifdef _WIN32
  25 #ifndef __MINGW32__
  26 #pragma warning(disable:4786)
  27 #endif
  28 #endif
  29
  30 #include <map>
  31 #include <string>
  32 #include <stdexcept>
  33
  34 #include <stdio.h>
  35 #include <stdlib.h>
  36 #include <string.h>
  37 #include <sqlite3.h>
  38
  39 #include "Assert.h"
  40 #include "Database.h"
  41 #include "Query.h"
  42
  43
  44 #ifdef SQLITEW_NAMESPACE
  45 namespace SQLITEW_NAMESPACE {
  46 #endif
  47
  48
  49 Query::Query(Database& dbin)
  50 : m_db(dbin)
  51 ,odb(dbin.grabdb())
  52 ,res(NULL)
  53 ,row(false)
  54 ,cache_rc(0)
  55 ,cache_rc_valid(false)
  56 ,m_row_count(0)
  57 ,m_num_cols(0)
  58 {
  59 }
  60
  61
  62 Query::Query(Database& dbin,const std::string& sql)
  63 : m_db(dbin)
  64 ,odb(dbin.grabdb())
  65 ,res(NULL)
  66 ,row(false)
  67 ,cache_rc(0)
  68 ,cache_rc_valid(false)
  69 ,m_row_count(0)
  70 ,m_num_cols(0)
  71 {
  72         execute(sql);
  73 }
  74
  75
  76 Query::~Query()
  77 {
  78         Assert(!res);
  79
  80         if (odb)
  81         {
  82                 m_db.freedb(odb);
  83         }
  84 }
  85
  86
  87 Database& Query::GetDatabase() const
  88 {
  89         return m_db;
  90 }
  91
  92
  93 /*
  94 The sqlite3_finalize() routine deallocates a prepared SQL statement.
  95 All prepared statements must be finalized before the database can be closed.
  96 */
  97 bool Query::execute(const std::string& sql)
  98 {               // query, no result
  99         m_last_query = sql;
 100         Assert(!(odb && res));
 101         if (odb && !res)
 102         {
 103                 const char *s = NULL;
 104                 size_t rc = sqlite3_prepare(odb -> db, sql.c_str(), (int)sql.size(), &res, &s);
 105                 if (rc != SQLITE_OK)
 106                         throw std::runtime_error("execute: prepare query failed");
 107                 if (!res)
 108                         throw std::runtime_error("execute: query failed");
 109                 rc = sqlite3_step(res); // execute
 110                 sqlite3_finalize(res); // deallocate statement
 111                 res = NULL;
 112                 switch (rc)
 113                 {
 114                 case SQLITE_BUSY:
 115                         throw std::runtime_error("execute: database busy");
 116                 case SQLITE_DONE:
 117                 case SQLITE_ROW:
 118                         return true;
 119                 case SQLITE_ERROR:
 120                         throw std::runtime_error(sqlite3_errmsg(odb->db));
 121                 case SQLITE_MISUSE:
 122                         throw std::runtime_error("execute: database misuse");
 123                 }
 124                 throw std::runtime_error("execute: unknown result code");
 125         }
 126         return false;
 127 }
 128
 129
 130
 131 // methods using db specific api calls
 132
 133 sqlite3_stmt *Query::get_result(const std::string& sql)
 134 {       // query, result
 135         Assert(!(odb && res));
 136         if (odb && !res)
 137         {
 138                 const char *s = NULL;
 139                 int rc = sqlite3_prepare(odb -> db, sql.c_str(), (int)sql.size(), &res, &s);
 140                 if (rc != SQLITE_OK)
 141                         throw std::runtime_error("get_result: prepare query failed");
 142                 if (!res)
 143                         throw std::runtime_error("get_result: query failed");
 144                 // get column names from result
 145                 {
 146                         int i = 0;
 147                         do
 148                         {
 149                                 const char *p = sqlite3_column_name(res, i);
 150                                 if (!p)
 151                                         break;
 152                                 m_nmap[p] = ++i;
 153                         } while (true);
 154                         m_num_cols = i + 1;
 155                 }
 156                 cache_rc = sqlite3_step(res);
 157                 cache_rc_valid = true;
 158                 m_row_count = (cache_rc == SQLITE_ROW) ? 1 : 0;
 159         }
 160         return res;
 161 }
 162
 163
 164 void Query::free_result()
 165 {
 166         if (odb && res)
 167         {
 168                 sqlite3_finalize(res);
 169                 res = NULL;
 170                 row = false;
 171                 cache_rc_valid = false;
 172         }
 173         // clear column names
 174         while (m_nmap.size())
 175         {
 176                 std::map<std::string,int>::iterator it = m_nmap.begin();
 177                 m_nmap.erase(it);
 178         }
 179 }
 180
 181
 182 bool Query::fetch_row()
 183 {
 184         rowcount = 0;
 185         row = false;
 186         if (odb && res)
 187         {
 188                 int rc = cache_rc_valid ? cache_rc : sqlite3_step(res); // execute
 189                 cache_rc_valid = false;
 190                 switch (rc)
 191                 {
 192                 case SQLITE_BUSY:
 193                         throw std::runtime_error("execute: database busy");
 194                 case SQLITE_DONE:
 195                         return false;
 196                 case SQLITE_ROW:
 197                         row = true;
 198                         return true;
 199                 case SQLITE_ERROR:
 200                         throw std::runtime_error(sqlite3_errmsg(odb->db));
 201                         return false;
 202                 case SQLITE_MISUSE:
 203                         throw std::runtime_error("execute: database misuse");
 204                         return false;
 205                 }
 206                 throw std::runtime_error("execute: unknown result code");
 207         }
 208         return false;
 209 }
 210
 211
 212 sqlite_int64 Query::insert_id()
 213 {
 214         if (odb)
 215         {
 216                 return sqlite3_last_insert_rowid(odb -> db);
 217         }
 218         else
 219         {
 220                 return 0;
 221         }
 222 }
 223
 224
 225 long Query::num_rows()
 226 {
 227         return odb && res ? m_row_count : 0;
 228 }
 229
 230
 231 bool Query::is_null(int x)
 232 {
 233         if (odb && res && row)
 234         {
 235                 if (sqlite3_column_type(res, x) == SQLITE_NULL)
 236                         return true;
 237         }
 238         return false; // ...
 239 }
 240
 241
 242 const char *Query::getstr(const std::string& x)
 243 {
 244         int index = m_nmap[x] - 1;
 245         if(index < 0)
 246                 throw std::runtime_error("Column name lookup failure: " + x);
 247         return getstr(index);
 248 }
 249
 250
 251 const char *Query::getstr(int x)
 252 {
 253         if (odb && res && row && x < sqlite3_column_count(res) )
 254         {
 255                 const unsigned char *tmp = sqlite3_column_text(res, x);
 256                 return tmp ? (const char *)tmp : "";
 257         }
 258         return "";
 259 }
 260
 261
 262 const char *Query::getstr()
 263 {
 264         return getstr(rowcount++);
 265 }
 266
 267
 268 double Query::getnum(const std::string& x)
 269 {
 270         int index = m_nmap[x] - 1;
 271         if(index < 0)
 272                 throw std::runtime_error("Column name lookup failure: " + x);
 273         return getnum(index);
 274 }
 275
 276
 277 double Query::getnum(int x)
 278 {
 279         if (odb && res && row)
 280         {
 281                 return sqlite3_column_double(res, x);
 282         }
 283         return 0;
 284 }
 285
 286
 287 long Query::getval(const std::string& x)
 288 {
 289         int index = m_nmap[x] - 1;
 290         if(index < 0)
 291                 throw std::runtime_error("Column name lookup failure: " + x);
 292         return getval(index);
 293 }
 294
 295
 296 long Query::getval(int x)
 297 {
 298         if (odb && res && row)
 299         {
 300                 return sqlite3_column_int(res, x);
 301         }
 302         return 0;
 303 }
 304
 305
 306 double Query::getnum()
 307 {
 308         return getnum(rowcount++);
 309 }
 310
 311
 312 long Query::getval()
 313 {
 314         return getval(rowcount++);
 315 }
 316
 317
 318 unsigned long Query::getuval(const std::string& x)
 319 {
 320         int index = m_nmap[x] - 1;
 321         if(index < 0)
 322                 throw std::runtime_error("Column name lookup failure: " + x);
 323         return getuval(index);
 324 }
 325
 326
 327 unsigned long Query::getuval(int x)
 328 {
 329         unsigned long l = 0;
 330         if (odb && res && row)
 331         {
 332                 l = sqlite3_column_int(res, x);
 333         }
 334         return l;
 335 }
 336
 337
 338 unsigned long Query::getuval()
 339 {
 340         return getuval(rowcount++);
 341 }
 342
 343
 344 int64_t Query::getbigint(const std::string& x)
 345 {
 346         int index = m_nmap[x] - 1;
 347         if(index < 0)
 348                 throw std::runtime_error("Column name lookup");
 349         return getbigint(index);
 350 }
 351
 352
 353 int64_t Query::getbigint(int x)
 354 {
 355         if (odb && res && row)
 356         {
 357                 return sqlite3_column_int64(res, x);
 358         }
 359         return 0;
 360 }
 361
 362
 363 int64_t Query::getbigint()
 364 {
 365         return getbigint(rowcount++);
 366 }
 367
 368
 369 uint64_t Query::getubigint(const std::string& x)
 370 {
 371         int index = m_nmap[x] - 1;
 372         if(index < 0)
 373                 throw std::runtime_error("Column name lookup failure: " + x);
 374         return getubigint(index);
 375 }
 376
 377
 378 uint64_t Query::getubigint(int x)
 379 {
 380         uint64_t l = 0;
 381         if (odb && res && row)
 382         {
 383                 l = sqlite3_column_int64(res, x);
 384         }
 385         return l;
 386 }
 387
 388
 389 uint64_t Query::getubigint()
 390 {
 391         return getubigint(rowcount++);
 392 }
 393
 394
 395 double Query::get_num(const std::string& sql)
 396 {
 397         double l = 0;
 398         if (get_result(sql))
 399         {
 400                 if (fetch_row())
 401                 {
 402                         l = getnum();
 403                 }
 404                 free_result();
 405         }
 406         return l;
 407 }
 408
 409
 410 long Query::get_count(const std::string& sql)
 411 {
 412         long l = 0;
 413         if (get_result(sql))
 414         {
 415                 if (fetch_row())
 416                         l = getval();
 417                 free_result();
 418         }
 419         return l;
 420 }
 421
 422
 423 const char *Query::get_string(const std::string& sql)
 424 {
 425         bool found = false;
 426         m_tmpstr = "";
 427         if (get_result(sql))
 428         {
 429                 if (fetch_row())
 430                 {
 431                         m_tmpstr = getstr();
 432                         found = true;
 433                 }
 434                 free_result();
 435         }
 436         return m_tmpstr.c_str(); // %! changed from 1.0 which didn't return NULL on failed query
 437 }
 438
 439
 440 const std::string& Query::GetLastQuery()
 441 {
 442         return m_last_query;
 443 }
 444
 445
 446 std::string Query::GetError()
 447 {
 448         if (odb)
 449                 return sqlite3_errmsg(odb -> db);
 450         return "";
 451 }
 452
 453
 454 int Query::GetErrno()
 455 {
 456         if (odb)
 457                 return sqlite3_errcode(odb -> db);
 458         return 0;
 459 }
 460
 461
 462 bool Query::Connected()
 463 {
 464         return odb ? true : false;
 465 }
 466
 467
 468
 469
 470 void Query::ViewRes()
 471 {
 472         if (!res)
 473         {
 474                 printf("no result stored\n");
 475                 return;
 476         }
 477         printf("result column count = %d\n", sqlite3_column_count(res));
 478         for (int i = 0; i < sqlite3_column_count(res); i++)
 479         {
 480                 printf(" %2d   type %d   name '%s'", i, sqlite3_column_type(res, i), sqlite3_column_name(res, i));
 481                 printf("  / '%s'", (char *)sqlite3_column_text(res, i));
 482                 printf("  / %d", sqlite3_column_int(res, i));
 483                 printf("  / %f", sqlite3_column_double(res, i));
 484                 printf("\n");
 485         }
 486 }
 487
 488 #ifdef SQLITEW_NAMESPACE
 489 } // namespace SQLITEW_NAMESPACE {
 490 #endif
 491