xbmc/dbwrappers/sqlitedataset.cpp

   1 /**********************************************************************
   2  *  Copyright (C) 2004, Leo Seib, Hannover
   3  *
   4  *  Project:SQLiteDataset C++ Dynamic Library
   5  *  Module: SQLiteDataset class realisation file
   6  *  Author: Leo Seib      E-Mail: leoseib@web.de
   7  *  Begin: 5/04/2002
   8  *
   9  *  SPDX-License-Identifier: MIT
  10  *  See LICENSES/README.md for more information.
  11  */
  12
  13 #include "sqlitedataset.h"
  14
  15 #include "utils/StringUtils.h"
  16 #include "utils/URIUtils.h"
  17 #include "utils/XTimeUtils.h"
  18 #include "utils/log.h"
  19
  20 #include <iostream>
  21 #include <map>
  22 #include <sstream>
  23 #include <string>
  24
  25 using namespace std::chrono_literals;
  26
  27 namespace
  28 {
  29 #define X(VAL) std::make_pair(VAL, #VAL)
  30 //!@todo Remove ifdefs when sqlite version requirement has been bumped to at least 3.26.0
  31 const std::map<int, const char*> g_SqliteErrorStrings = {
  32     X(SQLITE_OK),
  33     X(SQLITE_ERROR),
  34     X(SQLITE_INTERNAL),
  35     X(SQLITE_PERM),
  36     X(SQLITE_ABORT),
  37     X(SQLITE_BUSY),
  38     X(SQLITE_LOCKED),
  39     X(SQLITE_NOMEM),
  40     X(SQLITE_READONLY),
  41     X(SQLITE_INTERRUPT),
  42     X(SQLITE_IOERR),
  43     X(SQLITE_CORRUPT),
  44     X(SQLITE_NOTFOUND),
  45     X(SQLITE_FULL),
  46     X(SQLITE_CANTOPEN),
  47     X(SQLITE_PROTOCOL),
  48     X(SQLITE_EMPTY),
  49     X(SQLITE_SCHEMA),
  50     X(SQLITE_TOOBIG),
  51     X(SQLITE_CONSTRAINT),
  52     X(SQLITE_MISMATCH),
  53     X(SQLITE_MISUSE),
  54     X(SQLITE_NOLFS),
  55     X(SQLITE_AUTH),
  56     X(SQLITE_FORMAT),
  57     X(SQLITE_RANGE),
  58     X(SQLITE_NOTADB),
  59     X(SQLITE_NOTICE),
  60     X(SQLITE_WARNING),
  61     X(SQLITE_ROW),
  62     X(SQLITE_DONE),
  63 #if defined(SQLITE_ERROR_MISSING_COLLSEQ)
  64     X(SQLITE_ERROR_MISSING_COLLSEQ),
  65 #endif
  66 #if defined(SQLITE_ERROR_RETRY)
  67     X(SQLITE_ERROR_RETRY),
  68 #endif
  69 #if defined(SQLITE_ERROR_SNAPSHOT)
  70     X(SQLITE_ERROR_SNAPSHOT),
  71 #endif
  72     X(SQLITE_IOERR_READ),
  73     X(SQLITE_IOERR_SHORT_READ),
  74     X(SQLITE_IOERR_WRITE),
  75     X(SQLITE_IOERR_FSYNC),
  76     X(SQLITE_IOERR_DIR_FSYNC),
  77     X(SQLITE_IOERR_TRUNCATE),
  78     X(SQLITE_IOERR_FSTAT),
  79     X(SQLITE_IOERR_UNLOCK),
  80     X(SQLITE_IOERR_RDLOCK),
  81     X(SQLITE_IOERR_DELETE),
  82     X(SQLITE_IOERR_BLOCKED),
  83     X(SQLITE_IOERR_NOMEM),
  84     X(SQLITE_IOERR_ACCESS),
  85     X(SQLITE_IOERR_CHECKRESERVEDLOCK),
  86     X(SQLITE_IOERR_LOCK),
  87     X(SQLITE_IOERR_CLOSE),
  88     X(SQLITE_IOERR_DIR_CLOSE),
  89     X(SQLITE_IOERR_SHMOPEN),
  90     X(SQLITE_IOERR_SHMSIZE),
  91     X(SQLITE_IOERR_SHMLOCK),
  92     X(SQLITE_IOERR_SHMMAP),
  93     X(SQLITE_IOERR_SEEK),
  94     X(SQLITE_IOERR_DELETE_NOENT),
  95     X(SQLITE_IOERR_MMAP),
  96     X(SQLITE_IOERR_GETTEMPPATH),
  97     X(SQLITE_IOERR_CONVPATH),
  98 #if defined(SQLITE_IOERR_VNODE)
  99     X(SQLITE_IOERR_VNODE),
 100 #endif
 101 #if defined(SQLITE_IOERR_AUTH)
 102     X(SQLITE_IOERR_AUTH),
 103 #endif
 104 #if defined(SQLITE_IOERR_BEGIN_ATOMIC)
 105     X(SQLITE_IOERR_BEGIN_ATOMIC),
 106 #endif
 107 #if defined(SQLITE_IOERR_COMMIT_ATOMIC)
 108     X(SQLITE_IOERR_COMMIT_ATOMIC),
 109 #endif
 110 #if defined(SQLITE_IOERR_ROLLBACK_ATOMIC)
 111     X(SQLITE_IOERR_ROLLBACK_ATOMIC),
 112 #endif
 113     X(SQLITE_LOCKED_SHAREDCACHE),
 114 #if defined(SQLITE_LOCKED_VTAB)
 115     X(SQLITE_LOCKED_VTAB),
 116 #endif
 117     X(SQLITE_BUSY_RECOVERY),
 118     X(SQLITE_BUSY_SNAPSHOT),
 119     X(SQLITE_CANTOPEN_NOTEMPDIR),
 120     X(SQLITE_CANTOPEN_ISDIR),
 121     X(SQLITE_CANTOPEN_FULLPATH),
 122     X(SQLITE_CANTOPEN_CONVPATH),
 123 #if defined(SQLITE_CANTOPEN_DIRTYWAL)
 124     X(SQLITE_CANTOPEN_DIRTYWAL),
 125 #endif
 126     X(SQLITE_CORRUPT_VTAB),
 127 #if defined(SQLITE_CORRUPT_SEQUENCE)
 128     X(SQLITE_CORRUPT_SEQUENCE),
 129 #endif
 130     X(SQLITE_READONLY_RECOVERY),
 131     X(SQLITE_READONLY_CANTLOCK),
 132     X(SQLITE_READONLY_ROLLBACK),
 133     X(SQLITE_READONLY_DBMOVED),
 134 #if defined(SQLITE_READONLY_CANTINIT)
 135     X(SQLITE_READONLY_CANTINIT),
 136 #endif
 137 #if defined(SQLITE_READONLY_DIRECTORY)
 138     X(SQLITE_READONLY_DIRECTORY),
 139 #endif
 140     X(SQLITE_ABORT_ROLLBACK),
 141     X(SQLITE_CONSTRAINT_CHECK),
 142     X(SQLITE_CONSTRAINT_COMMITHOOK),
 143     X(SQLITE_CONSTRAINT_FOREIGNKEY),
 144     X(SQLITE_CONSTRAINT_FUNCTION),
 145     X(SQLITE_CONSTRAINT_NOTNULL),
 146     X(SQLITE_CONSTRAINT_PRIMARYKEY),
 147     X(SQLITE_CONSTRAINT_TRIGGER),
 148     X(SQLITE_CONSTRAINT_UNIQUE),
 149     X(SQLITE_CONSTRAINT_VTAB),
 150     X(SQLITE_CONSTRAINT_ROWID),
 151     X(SQLITE_NOTICE_RECOVER_WAL),
 152     X(SQLITE_NOTICE_RECOVER_ROLLBACK),
 153     X(SQLITE_WARNING_AUTOINDEX),
 154     X(SQLITE_AUTH_USER),
 155 #if defined(SQLITE_OK_LOAD_PERMANENTLY)
 156     X(SQLITE_OK_LOAD_PERMANENTLY),
 157 #endif
 158 };
 159 #undef X
 160 } // namespace
 161
 162 namespace dbiplus
 163 {
 164 //************* Callback function ***************************
 165
 166 int callback(void* res_ptr, int ncol, char** result, char** cols)
 167 {
 168   result_set* r = static_cast<result_set*>(res_ptr);
 169
 170   if (!r->record_header.size())
 171   {
 172     r->record_header.reserve(ncol);
 173     for (int i = 0; i < ncol; i++)
 174     {
 175       field_prop header;
 176       header.name = cols[i];
 177       r->record_header.push_back(header);
 178     }
 179   }
 180
 181   if (result != NULL)
 182   {
 183     sql_record* rec = new sql_record;
 184     rec->resize(ncol);
 185     for (int i = 0; i < ncol; i++)
 186     {
 187       field_value& v = rec->at(i);
 188       if (result[i] == NULL)
 189       {
 190         v.set_asString("");
 191         v.set_isNull();
 192       }
 193       else
 194       {
 195         v.set_asString(result[i]);
 196       }
 197     }
 198     r->records.push_back(rec);
 199   }
 200   return 0;
 201 }
 202
 203 static int busy_callback(void*, int busyCount)
 204 {
 205   KODI::TIME::Sleep(100ms);
 206   return 1;
 207 }
 208
 209 //************* SqliteDatabase implementation ***************
 210
 211 SqliteDatabase::SqliteDatabase()
 212 {
 213
 214   active = false;
 215   _in_transaction = false; // for transaction
 216
 217   error = "Unknown database error"; //S_NO_CONNECTION;
 218   host = "localhost";
 219   port = "";
 220   db = "sqlite.db";
 221   login = "root";
 222   passwd = "";
 223 }
 224
 225 SqliteDatabase::~SqliteDatabase()
 226 {
 227   disconnect();
 228 }
 229
 230 Dataset* SqliteDatabase::CreateDataset() const
 231 {
 232   return new SqliteDataset(const_cast<SqliteDatabase*>(this));
 233 }
 234
 235 void SqliteDatabase::setHostName(const char* newHost)
 236 {
 237   host = newHost;
 238
 239   // hostname is the relative folder to the database, ensure it's slash terminated
 240   if (host[host.length() - 1] != '/' && host[host.length() - 1] != '\\')
 241     host += "/";
 242
 243   // ensure the fully qualified path has slashes in the correct direction
 244   if ((host[1] == ':') && isalpha(host[0]))
 245   {
 246     size_t pos = 0;
 247     while ((pos = host.find('/', pos)) != std::string::npos)
 248       host.replace(pos++, 1, "\\");
 249   }
 250   else
 251   {
 252     size_t pos = 0;
 253     while ((pos = host.find('\\', pos)) != std::string::npos)
 254       host.replace(pos++, 1, "/");
 255   }
 256 }
 257
 258 void SqliteDatabase::setDatabase(const char* newDb)
 259 {
 260   db = newDb;
 261
 262   // db is the filename for the database, ensure it's not slash prefixed
 263   if (newDb[0] == '/' || newDb[0] == '\\')
 264     db = db.substr(1);
 265
 266   // ensure the ".db" extension is appended to the end
 267   if (db.find(".db") != (db.length() - 3))
 268     db += ".db";
 269 }
 270
 271 int SqliteDatabase::status(void)
 272 {
 273   if (active == false)
 274     return DB_CONNECTION_NONE;
 275   return DB_CONNECTION_OK;
 276 }
 277
 278 int SqliteDatabase::setErr(int err_code, const char* qry)
 279 {
 280   std::stringstream ss;
 281   ss << "[" << db << "] ";
 282   auto errorIt = g_SqliteErrorStrings.find(err_code);
 283   if (errorIt != g_SqliteErrorStrings.end())
 284   {
 285     ss << "SQLite error " << errorIt->second;
 286   }
 287   else
 288   {
 289     ss << "Undefined SQLite error " << err_code;
 290   }
 291   if (conn)
 292     ss << " (" << sqlite3_errmsg(conn) << ")";
 293   ss << "\nQuery: " << qry;
 294   error = ss.str();
 295   return err_code;
 296 }
 297
 298 const char* SqliteDatabase::getErrorMsg()
 299 {
 300   return error.c_str();
 301 }
 302
 303 static int AlphaNumericCollation(
 304     void* not_used, int nKey1, const void* pKey1, int nKey2, const void* pKey2)
 305 {
 306   return StringUtils::AlphaNumericCollation(nKey1, pKey1, nKey2, pKey2);
 307 }
 308
 309 int SqliteDatabase::connect(bool create)
 310 {
 311   if (host.empty() || db.empty())
 312     return DB_CONNECTION_NONE;
 313
 314   //CLog::Log(LOGDEBUG, "Connecting to sqlite:{}:{}", host, db);
 315
 316   std::string db_fullpath = URIUtils::AddFileToFolder(host, db);
 317
 318   try
 319   {
 320     disconnect();
 321     int flags = SQLITE_OPEN_READWRITE;
 322     if (create)
 323       flags |= SQLITE_OPEN_CREATE;
 324     int errorCode = sqlite3_open_v2(db_fullpath.c_str(), &conn, flags, NULL);
 325     if (create && errorCode == SQLITE_CANTOPEN)
 326     {
 327       CLog::Log(LOGFATAL, "SqliteDatabase: can't open {}", db_fullpath);
 328       throw std::runtime_error("SqliteDatabase: can't open " + db_fullpath);
 329     }
 330     else if (errorCode == SQLITE_OK)
 331     {
 332       sqlite3_extended_result_codes(conn, 1);
 333       sqlite3_busy_handler(conn, busy_callback, NULL);
 334       if (setErr(sqlite3_exec(getHandle(), "PRAGMA empty_result_callbacks=ON", NULL, NULL, NULL),
 335                  "PRAGMA empty_result_callbacks=ON") != SQLITE_OK)
 336       {
 337         throw DbErrors("%s", getErrorMsg());
 338       }
 339       else if (sqlite3_db_readonly(conn, nullptr) == 1)
 340       {
 341         CLog::Log(LOGFATAL, "SqliteDatabase: {} is read only", db_fullpath);
 342         throw std::runtime_error("SqliteDatabase: " + db_fullpath + " is read only");
 343       }
 344       errorCode = sqlite3_create_collation(conn, "ALPHANUM", SQLITE_UTF8, 0, AlphaNumericCollation);
 345       if (errorCode != SQLITE_OK)
 346       {
 347         CLog::Log(LOGFATAL, "SqliteDatabase: can not register collation");
 348         throw std::runtime_error("SqliteDatabase: can not register collation " + db_fullpath);
 349       }
 350       active = true;
 351       return DB_CONNECTION_OK;
 352     }
 353   }
 354   catch (const DbErrors&)
 355   {
 356   }
 357
 358   sqlite3_close(conn);
 359
 360   return DB_CONNECTION_NONE;
 361 }
 362
 363 bool SqliteDatabase::exists(void)
 364 {
 365   bool bRet = false;
 366   if (!active)
 367     return bRet;
 368   result_set res;
 369   char sqlcmd[512];
 370
 371   // performing a select all on the sqlite_master will return rows if there are tables
 372   // defined indicating it's not empty and therefore must "exist".
 373   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT * FROM sqlite_master");
 374   if ((last_err = sqlite3_exec(getHandle(), sqlcmd, &callback, &res, NULL)) == SQLITE_OK)
 375   {
 376     bRet = (res.records.size() > 0);
 377   }
 378
 379   return bRet;
 380 }
 381
 382 void SqliteDatabase::disconnect(void)
 383 {
 384   if (active == false)
 385     return;
 386   sqlite3_close(conn);
 387   active = false;
 388 }
 389
 390 int SqliteDatabase::create()
 391 {
 392   return connect(true);
 393 }
 394
 395 int SqliteDatabase::copy(const char* backup_name)
 396 {
 397   if (active == false)
 398     throw DbErrors("Can't copy database: no active connection...");
 399
 400   CLog::Log(LOGDEBUG, "Copying from {} to {} at {}", db, backup_name, host);
 401
 402   int rc;
 403   std::string backup_db = backup_name;
 404
 405   sqlite3* pFile; /* Database connection opened on zFilename */
 406   sqlite3_backup* pBackup; /* Backup object used to copy data */
 407
 408   //
 409   if (backup_name[0] == '/' || backup_name[0] == '\\')
 410     backup_db = backup_db.substr(1);
 411
 412   // ensure the ".db" extension is appended to the end
 413   if (backup_db.find(".db") != (backup_db.length() - 3))
 414     backup_db += ".db";
 415
 416   std::string backup_path = host + backup_db;
 417
 418   /* Open the database file identified by zFilename. Exit early if this fails
 419   ** for any reason. */
 420   rc = sqlite3_open(backup_path.c_str(), &pFile);
 421   if (rc == SQLITE_OK)
 422   {
 423     pBackup = sqlite3_backup_init(pFile, "main", getHandle(), "main");
 424
 425     if (pBackup)
 426     {
 427       (void)sqlite3_backup_step(pBackup, -1);
 428       (void)sqlite3_backup_finish(pBackup);
 429     }
 430
 431     rc = sqlite3_errcode(pFile);
 432   }
 433
 434   (void)sqlite3_close(pFile);
 435
 436   if (rc != SQLITE_OK)
 437     throw DbErrors("Can't copy database. (%d)", rc);
 438
 439   return rc;
 440 }
 441
 442 int SqliteDatabase::drop_analytics(void)
 443 {
 444   // SqliteDatabase::copy used a full database copy, so we have a new version
 445   // with all the analytics stuff. We should clean database from everything but data
 446   if (active == false)
 447     throw DbErrors("Can't drop extras database: no active connection...");
 448
 449   char sqlcmd[4096];
 450   result_set res;
 451
 452   CLog::Log(LOGDEBUG, "Cleaning indexes from database {} at {}", db, host);
 453   snprintf(sqlcmd, sizeof(sqlcmd),
 454            "SELECT name FROM sqlite_master WHERE type == 'index' AND sql IS NOT NULL");
 455   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 456     return DB_UNEXPECTED_RESULT;
 457
 458   for (size_t i = 0; i < res.records.size(); i++)
 459   {
 460     snprintf(sqlcmd, sizeof(sqlcmd), "DROP INDEX '%s'",
 461              res.records[i]->at(0).get_asString().c_str());
 462     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 463       return DB_UNEXPECTED_RESULT;
 464   }
 465   res.clear();
 466
 467   CLog::Log(LOGDEBUG, "Cleaning views from database {} at {}", db, host);
 468   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT name FROM sqlite_master WHERE type == 'view'");
 469   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 470     return DB_UNEXPECTED_RESULT;
 471
 472   for (size_t i = 0; i < res.records.size(); i++)
 473   {
 474     snprintf(sqlcmd, sizeof(sqlcmd), "DROP VIEW '%s'",
 475              res.records[i]->at(0).get_asString().c_str());
 476     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 477       return DB_UNEXPECTED_RESULT;
 478   }
 479   res.clear();
 480
 481   CLog::Log(LOGDEBUG, "Cleaning triggers from database {} at {}", db, host);
 482   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT name FROM sqlite_master WHERE type == 'trigger'");
 483   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 484     return DB_UNEXPECTED_RESULT;
 485
 486   for (size_t i = 0; i < res.records.size(); i++)
 487   {
 488     snprintf(sqlcmd, sizeof(sqlcmd), "DROP TRIGGER '%s'",
 489              res.records[i]->at(0).get_asString().c_str());
 490     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 491       return DB_UNEXPECTED_RESULT;
 492   }
 493   // res would be cleared on destruct
 494
 495   return DB_COMMAND_OK;
 496 }
 497
 498 int SqliteDatabase::drop()
 499 {
 500   if (active == false)
 501     throw DbErrors("Can't drop database: no active connection...");
 502   disconnect();
 503   if (!unlink(db.c_str()))
 504   {
 505     throw DbErrors("Can't drop database: can't unlink the file %s,\nError: %s", db.c_str(),
 506                    strerror(errno));
 507   }
 508   return DB_COMMAND_OK;
 509 }
 510
 511 long SqliteDatabase::nextid(const char* sname)
 512 {
 513   if (!active)
 514     return DB_UNEXPECTED_RESULT;
 515   int id; /*,nrow,ncol;*/
 516   result_set res;
 517   char sqlcmd[512];
 518   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT nextid FROM %s WHERE seq_name = '%s'",
 519            sequence_table.c_str(), sname);
 520   if ((last_err = sqlite3_exec(getHandle(), sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 521   {
 522     return DB_UNEXPECTED_RESULT;
 523   }
 524   if (res.records.empty())
 525   {
 526     id = 1;
 527     snprintf(sqlcmd, sizeof(sqlcmd), "INSERT INTO %s (nextid,seq_name) VALUES (%d,'%s')",
 528              sequence_table.c_str(), id, sname);
 529     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 530       return DB_UNEXPECTED_RESULT;
 531     return id;
 532   }
 533   else
 534   {
 535     id = res.records[0]->at(0).get_asInt() + 1;
 536     snprintf(sqlcmd, sizeof(sqlcmd), "UPDATE %s SET nextid=%d WHERE seq_name = '%s'",
 537              sequence_table.c_str(), id, sname);
 538     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 539       return DB_UNEXPECTED_RESULT;
 540     return id;
 541   }
 542   return DB_UNEXPECTED_RESULT;
 543 }
 544
 545 // methods for transactions
 546 // ---------------------------------------------
 547 void SqliteDatabase::start_transaction()
 548 {
 549   if (active)
 550   {
 551     sqlite3_exec(conn, "begin IMMEDIATE", NULL, NULL, NULL);
 552     _in_transaction = true;
 553   }
 554 }
 555
 556 void SqliteDatabase::commit_transaction()
 557 {
 558   if (active)
 559   {
 560     sqlite3_exec(conn, "commit", NULL, NULL, NULL);
 561     _in_transaction = false;
 562   }
 563 }
 564
 565 void SqliteDatabase::rollback_transaction()
 566 {
 567   if (active)
 568   {
 569     sqlite3_exec(conn, "rollback", NULL, NULL, NULL);
 570     _in_transaction = false;
 571   }
 572 }
 573
 574 // methods for formatting
 575 // ---------------------------------------------
 576 std::string SqliteDatabase::vprepare(const char* format, va_list args)
 577 {
 578   std::string strFormat = format;
 579   std::string strResult = "";
 580   char* p;
 581   size_t pos;
 582
 583   //  %q is the sqlite format string for %s.
 584   //  Any bad character, like "'", will be replaced with a proper one
 585   pos = 0;
 586   while ((pos = strFormat.find("%s", pos)) != std::string::npos)
 587     strFormat.replace(pos++, 2, "%q");
 588
 589   //  the %I64 enhancement is not supported by sqlite3_vmprintf
 590   //  must be %ll instead
 591   pos = 0;
 592   while ((pos = strFormat.find("%I64", pos)) != std::string::npos)
 593     strFormat.replace(pos++, 4, "%ll");
 594
 595   p = sqlite3_vmprintf(strFormat.c_str(), args);
 596   if (p)
 597   {
 598     strResult = p;
 599     sqlite3_free(p);
 600   }
 601
 602   // Strip SEPARATOR from all GROUP_CONCAT statements:
 603   // before: GROUP_CONCAT(field SEPARATOR '; ')
 604   // after:  GROUP_CONCAT(field, '; ')
 605   // Can not specify separator when have DISTINCT, comma used by default
 606   pos = strResult.find("GROUP_CONCAT(");
 607   while (pos != std::string::npos)
 608   {
 609     size_t pos2 = strResult.find(" SEPARATOR ", pos + 1);
 610     if (pos2 != std::string::npos)
 611       strResult.replace(pos2, 10, ",");
 612     pos = strResult.find("GROUP_CONCAT(", pos + 1);
 613   }
 614   // Replace CONCAT with || to concatenate text fields:
 615   // before: CONCAT(field1, field2, field3)
 616   // after: field1 || field2 || field3
 617   // Avoid commas in substatements and within single quotes
 618   // before: CONCAT(field1, ',', REPLACE(field2, ',', '-'), field3)
 619   // after: field1 || ',' || REPLACE(field2, ',', '-') || field3
 620   pos = strResult.find("CONCAT(");
 621   while (pos != std::string::npos)
 622   {
 623     if (pos == 0 || strResult[pos - 1] == ' ') // Not GROUP_CONCAT
 624     {
 625       // Check each char for other bracket or single quote pairs
 626       unsigned int brackets = 1;
 627       bool quoted = false;
 628       size_t index = pos + 7; // start after "CONCAT("
 629       while (index < strResult.size() && brackets != 0)
 630       {
 631         if (strResult[index] == '(')
 632           brackets++;
 633         else if (strResult[index] == ')')
 634         {
 635           brackets--;
 636           if (brackets == 0)
 637             strResult.erase(index, 1); //Remove closing bracket of CONCAT
 638         }
 639         else if (strResult[index] == '\'')
 640           quoted = !quoted;
 641         else if (strResult[index] == ',' && brackets == 1 && !quoted)
 642           strResult.replace(index, 1, "||");
 643         index++;
 644       }
 645       strResult.erase(pos, 7); //Remove "CONCAT("
 646     }
 647     pos = strResult.find("CONCAT(", pos + 1);
 648   }
 649
 650   return strResult;
 651 }
 652
 653 //************* SqliteDataset implementation ***************
 654
 655 SqliteDataset::SqliteDataset() : Dataset()
 656 {
 657   haveError = false;
 658   db = NULL;
 659   autorefresh = false;
 660 }
 661
 662 SqliteDataset::SqliteDataset(SqliteDatabase* newDb) : Dataset(newDb)
 663 {
 664   haveError = false;
 665   db = newDb;
 666   autorefresh = false;
 667 }
 668
 669 SqliteDataset::~SqliteDataset()
 670 {
 671 }
 672
 673 void SqliteDataset::set_autorefresh(bool val)
 674 {
 675   autorefresh = val;
 676 }
 677
 678 //--------- protected functions implementation -----------------//
 679
 680 sqlite3* SqliteDataset::handle()
 681 {
 682   if (db != NULL)
 683   {
 684     return static_cast<SqliteDatabase*>(db)->getHandle();
 685   }
 686   else
 687     return NULL;
 688 }
 689
 690 void SqliteDataset::make_query(StringList& _sql)
 691 {
 692   std::string query;
 693   if (db == NULL)
 694     throw DbErrors("No Database Connection");
 695
 696   try
 697   {
 698
 699     if (autocommit)
 700       db->start_transaction();
 701
 702     for (const std::string& i : _sql)
 703     {
 704       query = i;
 705       char* err = NULL;
 706       Dataset::parse_sql(query);
 707       if (db->setErr(sqlite3_exec(this->handle(), query.c_str(), NULL, NULL, &err),
 708                      query.c_str()) != SQLITE_OK)
 709       {
 710         std::string message = db->getErrorMsg();
 711         if (err)
 712         {
 713           message.append(" (");
 714           message.append(err);
 715           message.append(")");
 716           sqlite3_free(err);
 717         }
 718         throw DbErrors("%s", message.c_str());
 719       }
 720     } // end of for
 721
 722     if (db->in_transaction() && autocommit)
 723       db->commit_transaction();
 724
 725     active = true;
 726     ds_state = dsSelect;
 727     if (autorefresh)
 728       refresh();
 729
 730   } // end of try
 731   catch (...)
 732   {
 733     if (db->in_transaction())
 734       db->rollback_transaction();
 735     throw;
 736   }
 737 }
 738
 739 void SqliteDataset::make_insert()
 740 {
 741   make_query(insert_sql);
 742   last();
 743 }
 744
 745 void SqliteDataset::make_edit()
 746 {
 747   make_query(update_sql);
 748 }
 749
 750 void SqliteDataset::make_deletion()
 751 {
 752   make_query(delete_sql);
 753 }
 754
 755 void SqliteDataset::fill_fields()
 756 {
 757   //cout <<"rr "<<result.records.size()<<"|" << frecno <<"\n";
 758   if ((db == NULL) || (result.record_header.empty()) ||
 759       (result.records.size() < (unsigned int)frecno))
 760     return;
 761
 762   if (fields_object->size() == 0) // Filling columns name
 763   {
 764     const unsigned int ncols = result.record_header.size();
 765     fields_object->resize(ncols);
 766     for (unsigned int i = 0; i < ncols; i++)
 767     {
 768       (*fields_object)[i].props = result.record_header[i];
 769       std::string name = result.record_header[i].name;
 770       name2indexMap.insert({str_toLower(name.data()), i});
 771     }
 772   }
 773
 774   //Filling result
 775   if (result.records.size() != 0)
 776   {
 777     const sql_record* row = result.records[frecno];
 778     if (row)
 779     {
 780       const unsigned int ncols = row->size();
 781       fields_object->resize(ncols);
 782       for (unsigned int i = 0; i < ncols; i++)
 783         (*fields_object)[i].val = row->at(i);
 784       return;
 785     }
 786   }
 787   const unsigned int ncols = result.record_header.size();
 788   fields_object->resize(ncols);
 789   for (unsigned int i = 0; i < ncols; i++)
 790     (*fields_object)[i].val = "";
 791 }
 792
 793 //------------- public functions implementation -----------------//
 794 bool SqliteDataset::dropIndex(const char* table, const char* index)
 795 {
 796   std::string sql;
 797
 798   sql = static_cast<SqliteDatabase*>(db)->prepare("DROP INDEX IF EXISTS %s", index);
 799
 800   return (exec(sql) == SQLITE_OK);
 801 }
 802
 803 int SqliteDataset::exec(const std::string& sql)
 804 {
 805   if (!handle())
 806     throw DbErrors("No Database Connection");
 807   std::string qry = sql;
 808   int res;
 809   exec_res.clear();
 810
 811   // Strip size constraints from indexes (not supported in sqlite)
 812   //
 813   // Example:
 814   //   before: CREATE UNIQUE INDEX ixPath ON path ( strPath(255) )
 815   //   after:  CREATE UNIQUE INDEX ixPath ON path ( strPath )
 816   //
 817   // NOTE: unexpected results occur if brackets are not matched
 818   if (qry.find("CREATE UNIQUE INDEX") != std::string::npos ||
 819       (qry.find("CREATE INDEX") != std::string::npos))
 820   {
 821     size_t pos = 0;
 822     size_t pos2 = 0;
 823
 824     if ((pos = qry.find('(')) != std::string::npos)
 825     {
 826       pos++;
 827       while ((pos = qry.find('(', pos)) != std::string::npos)
 828       {
 829         if ((pos2 = qry.find(')', pos)) != std::string::npos)
 830         {
 831           qry.replace(pos, pos2 - pos + 1, "");
 832           pos = pos2;
 833         }
 834       }
 835     }
 836   }
 837   // Strip ON table from DROP INDEX statements:
 838   // before: DROP INDEX foo ON table
 839   // after:  DROP INDEX foo
 840   size_t pos = qry.find("DROP INDEX ");
 841   if (pos != std::string::npos)
 842   {
 843     pos = qry.find(" ON ", pos + 1);
 844
 845     if (pos != std::string::npos)
 846       qry.resize(pos);
 847   }
 848
 849   char* errmsg;
 850   if ((res = db->setErr(sqlite3_exec(handle(), qry.c_str(), &callback, &exec_res, &errmsg),
 851                         qry.c_str())) == SQLITE_OK)
 852     return res;
 853   else
 854   {
 855     if (errmsg)
 856     {
 857       DbErrors err("%s (%s)", db->getErrorMsg(), errmsg);
 858       sqlite3_free(errmsg);
 859       throw err;
 860     }
 861     else
 862     {
 863       throw DbErrors("%s", db->getErrorMsg());
 864     }
 865   }
 866 }
 867
 868 int SqliteDataset::exec()
 869 {
 870   return exec(sql);
 871 }
 872
 873 const void* SqliteDataset::getExecRes()
 874 {
 875   return &exec_res;
 876 }
 877
 878 bool SqliteDataset::query(const std::string& query)
 879 {
 880   if (!handle())
 881     throw DbErrors("No Database Connection");
 882   const std::string& qry = query;
 883   int fs = qry.find("select");
 884   int fS = qry.find("SELECT");
 885   if (!(fs >= 0 || fS >= 0))
 886     throw DbErrors("MUST be select SQL!");
 887
 888   close();
 889
 890   sqlite3_stmt* stmt = NULL;
 891   if (db->setErr(sqlite3_prepare_v2(handle(), query.c_str(), -1, &stmt, NULL), query.c_str()) !=
 892       SQLITE_OK)
 893     throw DbErrors("%s", db->getErrorMsg());
 894
 895   // column headers
 896   const unsigned int numColumns = sqlite3_column_count(stmt);
 897   result.record_header.resize(numColumns);
 898   for (unsigned int i = 0; i < numColumns; i++)
 899     result.record_header[i].name = sqlite3_column_name(stmt, i);
 900
 901   // returned rows
 902   while (sqlite3_step(stmt) == SQLITE_ROW)
 903   { // have a row of data
 904     sql_record* res = new sql_record;
 905     res->resize(numColumns);
 906     for (unsigned int i = 0; i < numColumns; i++)
 907     {
 908       field_value& v = res->at(i);
 909       switch (sqlite3_column_type(stmt, i))
 910       {
 911         case SQLITE_INTEGER:
 912           v.set_asInt64(sqlite3_column_int64(stmt, i));
 913           break;
 914         case SQLITE_FLOAT:
 915           v.set_asDouble(sqlite3_column_double(stmt, i));
 916           break;
 917         case SQLITE_TEXT:
 918           v.set_asString(reinterpret_cast<const char*>(sqlite3_column_text(stmt, i)),
 919                          sqlite3_column_bytes(stmt, i));
 920           break;
 921         case SQLITE_BLOB:
 922           v.set_asString(reinterpret_cast<const char*>(sqlite3_column_text(stmt, i)),
 923                          sqlite3_column_bytes(stmt, i));
 924           break;
 925         case SQLITE_NULL:
 926         default:
 927           v.set_asString("");
 928           v.set_isNull();
 929           break;
 930       }
 931     }
 932     result.records.push_back(res);
 933   }
 934   if (db->setErr(sqlite3_finalize(stmt), query.c_str()) == SQLITE_OK)
 935   {
 936     active = true;
 937     ds_state = dsSelect;
 938     this->first();
 939     return true;
 940   }
 941   else
 942   {
 943     throw DbErrors("%s", db->getErrorMsg());
 944   }
 945 }
 946
 947 void SqliteDataset::open(const std::string& sql)
 948 {
 949   set_select_sql(sql);
 950   open();
 951 }
 952
 953 void SqliteDataset::open()
 954 {
 955   if (select_sql.size())
 956   {
 957     query(select_sql);
 958   }
 959   else
 960   {
 961     ds_state = dsInactive;
 962   }
 963 }
 964
 965 void SqliteDataset::close()
 966 {
 967   Dataset::close();
 968   result.clear();
 969   edit_object->clear();
 970   fields_object->clear();
 971   ds_state = dsInactive;
 972   active = false;
 973 }
 974
 975 void SqliteDataset::cancel()
 976 {
 977   if ((ds_state == dsInsert) || (ds_state == dsEdit))
 978   {
 979     if (result.record_header.size())
 980       ds_state = dsSelect;
 981     else
 982       ds_state = dsInactive;
 983   }
 984 }
 985
 986 int SqliteDataset::num_rows()
 987 {
 988   return result.records.size();
 989 }
 990
 991 bool SqliteDataset::eof()
 992 {
 993   return feof;
 994 }
 995
 996 bool SqliteDataset::bof()
 997 {
 998   return fbof;
 999 }
1000
1001 void SqliteDataset::first()
1002 {
1003   Dataset::first();
1004   this->fill_fields();
1005 }
1006
1007 void SqliteDataset::last()
1008 {
1009   Dataset::last();
1010   fill_fields();
1011 }
1012
1013 void SqliteDataset::prev(void)
1014 {
1015   Dataset::prev();
1016   fill_fields();
1017 }
1018
1019 void SqliteDataset::next(void)
1020 {
1021   Dataset::next();
1022   if (!eof())
1023     fill_fields();
1024 }
1025
1026 void SqliteDataset::free_row(void)
1027 {
1028   if (frecno < 0 || (unsigned int)frecno >= result.records.size())
1029     return;
1030
1031   sql_record* row = result.records[frecno];
1032   if (row)
1033   {
1034     delete row;
1035     result.records[frecno] = NULL;
1036   }
1037 }
1038
1039 bool SqliteDataset::seek(int pos)
1040 {
1041   if (ds_state == dsSelect)
1042   {
1043     Dataset::seek(pos);
1044     fill_fields();
1045     return true;
1046   }
1047   return false;
1048 }
1049
1050 int64_t SqliteDataset::lastinsertid()
1051 {
1052   if (!handle())
1053     throw DbErrors("No Database Connection");
1054   return sqlite3_last_insert_rowid(handle());
1055 }
1056
1057 long SqliteDataset::nextid(const char* seq_name)
1058 {
1059   if (handle())
1060     return db->nextid(seq_name);
1061   else
1062     return DB_UNEXPECTED_RESULT;
1063 }
1064
1065 void SqliteDataset::interrupt()
1066 {
1067   sqlite3_interrupt(handle());
1068 }
1069 } // namespace dbiplus