plugins/database/sqlitequery.cpp

   1 /*
   2  * timedate - Displays time and date and daily events
   3  * Copyright (c) <2009>, Intel Corporation.
   4  *
   5  * This program is free software; you can redistribute it and/or modify it
   6  * under the terms and conditions of the GNU Lesser General Public License,
   7  * version 2.1, as published by the Free Software Foundation.
   8  *
   9  * This program is distributed in the hope it will be useful, but WITHOUT
  10  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  11  * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for
  12  * more details.
  13  *
  14  * You should have received a copy of the GNU Lesser General Public License along with
  15  * this program; if not, write to the Free Software Foundation, Inc.,
  16  * 51 Franklin St - Fifth Floor, Boston, MA 02110-1301 USA.
  17  *
  18  */
  19
  20 #include "sqlitequery.h"
  21 #include <stdio.h>
  22 #include <sqlite3.h>
  23 #include <string>
  24 #include "utils.h"
  25
  26 using namespace std;
  27
  28 bool
  29 sqlitequery::init(sqlitedatabase * db)
  30 {
  31         m_db = db;
  32         odb = (db ? db->grabdb() : NULL);
  33         return true;
  34 }
  35
  36 bool
  37 sqlitequery::init(sqlitedatabase * dbin, const std::string & sql)
  38 {
  39         init(dbin);
  40         return execute(sql);
  41 }
  42
  43 sqlitequery::~sqlitequery()
  44 {
  45         if(result)
  46         {
  47                 printf( "sqlite3_finalize in destructor\n");
  48                 sqlite3_finalize(result);
  49         }
  50         if(odb)
  51         {
  52                 m_db->freedb(odb);
  53         }
  54 }
  55
  56 bool sqlitequery::execute(const std::string & sql)
  57 {
  58         std::string sql_sqlite=sql;
  59         m_last_query = sql_sqlite;
  60         if(odb && result)
  61         {
  62                 string err = "execute: query busy: "+sql_sqlite;
  63                 printf("%s\n", err.c_str());
  64         }
  65         if(odb && !result)
  66         {
  67                 const char * s = NULL;
  68                 int err = sqlite3_busy_timeout((sqlite3 *)odb->db, 10000 );
  69
  70                 if(err!= SQLITE_OK)
  71                 {
  72                         printf("execute: busy timeout occured: \n");
  73                         return false;
  74                 }
  75                 int rc = sqlite3_prepare_v2((sqlite3 *)odb->db, sql_sqlite.c_str(), sql_sqlite.size(), &result, &s);
  76
  77                 if(rc != SQLITE_OK)
  78                 {
  79                         string err = "execute: prepare query failed: "+sql_sqlite;
  80                         printf("%s\n", err.c_str());
  81                         result = NULL;
  82                         return false;
  83                 }
  84                 if(!result)
  85                 {
  86                         printf( "execute: query failed\n");
  87                         result = NULL;
  88                         return false;
  89                 }
  90                 rc = sqlite3_step(result);
  91                 sqlite3_finalize(result);
  92                 result = NULL;
  93                 switch(rc)
  94                 {
  95                 case SQLITE_BUSY:
  96                         printf( "execute: database busy\n");
  97                         return false;
  98                 case SQLITE_DONE:
  99                 case SQLITE_ROW:
 100                         return true;
 101                 case SQLITE_SCHEMA:
 102                         printf( "execute: Schema error\n");
 103                         return false;
 104                 case SQLITE_ERROR:
 105                         printf("%s\n", sqlite3_errmsg((sqlite3 *)odb->db));
 106                         return false;
 107                 case SQLITE_MISUSE:
 108                         printf( "execute: database misuse\n");
 109                         return false;
 110                 default:
 111                         printf( "execute: unknown result code\n");
 112                 }
 113         }
 114         return false;
 115 }
 116
 117 bool sqlitequery::fetchRow()
 118 {
 119         rowcount = 0;
 120         row = false;
 121         if(odb && result)
 122         {
 123                 int rc = cache_rc_valid ? cache_rc : sqlite3_step(result);
 124                 cache_rc_valid = false;
 125                 switch(rc)
 126                 {
 127                 case SQLITE_BUSY:
 128                         printf( "execute: database busy\n");
 129                         return false;
 130                 case SQLITE_DONE:
 131                         return false;
 132                 case SQLITE_ROW:
 133                         row = true;
 134                         return true;
 135                 case SQLITE_ERROR:
 136                         printf("%s\n", sqlite3_errmsg((sqlite3 *)odb->db));
 137                         return false;
 138                 case SQLITE_MISUSE:
 139                         printf( "execute: database misuse\n");
 140                         return false;
 141                 default:
 142                         printf( "execute: unknown result code\n");
 143                 }
 144         }
 145         return false;
 146 }
 147
 148 void sqlitequery::freeResult()
 149 {
 150         if(odb && result)
 151         {
 152                 sqlite3_finalize(result);
 153                 result = NULL;
 154                 row = false;
 155                 cache_rc_valid = false;
 156         }
 157         while(m_nmap.size())
 158         {
 159                 std::map<std::string, int>::iterator it = m_nmap.begin();
 160                 m_nmap.erase(it);
 161         }
 162 }
 163
 164 void sqlitequery::resetStatement()
 165 {
 166         if( odb && result )
 167                 sqlite3_reset(result);
 168 }
 169
 170 long sqlitequery::getCount(const std::string & sql)
 171 {
 172         long l(0);
 173         if(getResult(sql))
 174         {
 175                 if(fetchRow())
 176                 {
 177                         l = getVal(rowcount++);
 178                 }
 179                 freeResult();
 180         }
 181         return l;
 182 }
 183
 184 bool sqlitequery::prepareStatement(const std::string & sql)
 185 {
 186         std::string sql_sqlite=sql;
 187         m_last_query = sql_sqlite;
 188         if(odb && result)
 189         {
 190                 string err = "prepareStatement: query busy: "+sql_sqlite;
 191                 printf("%s\n", err.c_str());
 192         }
 193         if(odb && !result)
 194         {
 195                 const char * s = NULL;
 196                 int rc = sqlite3_prepare_v2((sqlite3 *)odb->db, sql_sqlite.c_str(), sql_sqlite.size(), &result, &s);
 197                 if(rc != SQLITE_OK)
 198                 {
 199                         string err = "prepareStatement: prepare query failed: "+sql_sqlite;
 200                         printf("%s\n", err.c_str());
 201                         return false;
 202                 }
 203                 if(!result)
 204                 {
 205                         printf( "prepareStatement: query failed\n");
 206                         return false;
 207                 }
 208         }
 209         return result;
 210 }
 211
 212 bool sqlitequery::getResult(const std::string & sql)
 213 {
 214         if(prepareStatement(sql))
 215         {
 216                 int i(0);
 217                 const char * p = sqlite3_column_name(result, i);
 218                 while(p)
 219                 {
 220                         m_nmap[p] = ++i;
 221                         p = sqlite3_column_name(result, i);
 222                 }
 223                 m_num_cols = i;
 224                 cache_rc = sqlite3_step(result);
 225                 cache_rc_valid = true;
 226                 m_row_count = (cache_rc == SQLITE_ROW) ? 1 : 0;
 227         }
 228         return result;
 229 }
 230
 231 bool sqlitequery::bind(const std::string bindMatch)
 232 {
 233         if(!odb || !result) return false;
 234
 235         int r = sqlite3_bind_text(result,1,bindMatch.c_str(),bindMatch.length(),NULL);
 236
 237         if(r != SQLITE_OK)
 238         {
 239                 printf("sqlitequery::bind - error binding query\n");
 240                 return false;
 241         }
 242
 243         int i(0);
 244         const char * p = sqlite3_column_name(result, i);
 245         while(p)
 246         {
 247                 m_nmap[p] = ++i;
 248                 p = sqlite3_column_name(result, i);
 249         }
 250         m_num_cols = i;
 251         cache_rc = sqlite3_step(result);
 252         cache_rc_valid = true;
 253         m_row_count = (cache_rc == SQLITE_ROW) ? 1 : 0;
 254
 255         return true;
 256 }
 257
 258 bool sqlitequery::bind(const int value)
 259 {
 260         if(!odb || !result) return false;
 261
 262         int r = sqlite3_bind_int(result,1,value);
 263
 264         if(r != SQLITE_OK)
 265         {
 266                 printf("sqlitequery::bind - error binding query\n");
 267                 return false;
 268         }
 269
 270         int i(0);
 271         const char * p = sqlite3_column_name(result, i);
 272         while(p)
 273         {
 274                 m_nmap[p] = ++i;
 275                 p = sqlite3_column_name(result, i);
 276         }
 277
 278         m_num_cols = i;
 279         cache_rc = sqlite3_step(result);
 280         cache_rc_valid = true;
 281         m_row_count = (cache_rc == SQLITE_ROW) ? 1 : 0;
 282
 283         return true;
 284 }
 285
 286 bool sqlitequery::bind(const double value)
 287 {
 288         if(!odb || !result) return false;
 289
 290         int r = sqlite3_bind_double(result,1,value);
 291
 292         if(r != SQLITE_OK)
 293         {
 294                 printf("sqlitequery::bind - error binding query\n");
 295                 return false;
 296         }
 297
 298         int i(0);
 299         const char * p = sqlite3_column_name(result, i);
 300         while(p)
 301         {
 302                 m_nmap[p] = ++i;
 303                 p = sqlite3_column_name(result, i);
 304         }
 305         m_num_cols = i;
 306         cache_rc = sqlite3_step(result);
 307         cache_rc_valid = true;
 308         m_row_count = (cache_rc == SQLITE_ROW) ? 1 : 0;
 309
 310         return cache_rc == SQLITE_OK;
 311 }
 312
 313 int64_t sqlitequery::getBigInt(int x)
 314 {
 315         if(odb && result && row)
 316         {
 317                 return sqlite3_column_int64(result, x);
 318         }
 319         return 0;
 320 }
 321
 322 int sqlitequery::GetErrno()
 323 {
 324         if(odb)
 325         {
 326                 return sqlite3_errcode((sqlite3 *)odb->db);
 327         }
 328         return 0;
 329 }
 330
 331 std::string sqlitequery::GetError()
 332 {
 333         if(odb)
 334         {
 335                 return sqlite3_errmsg((sqlite3 *)odb->db);
 336         }
 337         return "";
 338 }
 339
 340 double sqlitequery::getNum(int x)
 341 {
 342         if(odb && result && row)
 343         {
 344                 return sqlite3_column_double(result, x);
 345         }
 346         return 0;
 347 }
 348
 349 const char * sqlitequery::getStr(int x)
 350 {
 351         if(odb && result && row && x < sqlite3_column_count(result))
 352         {
 353                 const unsigned char * tmp = sqlite3_column_text(result, x);
 354                 return tmp ? (const char *)tmp : "";
 355         }
 356         return "";
 357 }
 358
 359 uint64_t sqlitequery::getUBigInt(int x)
 360 {
 361         if(odb && result && row)
 362         {
 363                 return (uint64_t)sqlite3_column_int64(result, x);
 364         }
 365         return 0;
 366 }
 367
 368 unsigned long sqlitequery::getUVal(int x)
 369 {
 370         if(odb && result && row)
 371         {
 372                 return (unsigned long)sqlite3_column_int(result, x);
 373         }
 374         return 0;
 375 }
 376
 377 long sqlitequery::getVal(int x)
 378 {
 379         if(odb && result && row)
 380         {
 381                 return sqlite3_column_int(result, x);
 382         }
 383         return 0;
 384 }
 385
 386 bool sqlitequery::isNull(int x)
 387 {
 388         if(odb && result && row)
 389         {
 390                 if(sqlite3_column_type(result, x) == SQLITE_NULL)
 391                 {
 392                         return true;
 393                 }
 394         }
 395         return false;
 396 }
 397
 398 long sqlitequery::numRows()
 399 {
 400         return odb && result ? m_row_count : 0;
 401 }
 402