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::postconnect()
 391 {
 392   if (!active)
 393     throw DbErrors("Cannot execute postconnect actions: no active connection...");
 394
 395   const std::string cmd{
 396       "PRAGMA cache_size=4096; PRAGMA synchronous='NORMAL'; PRAGMA count_changes='OFF';"};
 397
 398   if (setErr(sqlite3_exec(getHandle(), cmd.c_str(), NULL, NULL, NULL), cmd.c_str()) != SQLITE_OK)
 399   {
 400     throw DbErrors("%s", getErrorMsg());
 401   }
 402
 403   return DB_COMMAND_OK;
 404 }
 405
 406 int SqliteDatabase::create()
 407 {
 408   return connect(true);
 409 }
 410
 411 int SqliteDatabase::copy(const char* backup_name)
 412 {
 413   if (active == false)
 414     throw DbErrors("Can't copy database: no active connection...");
 415
 416   CLog::LogF(LOGDEBUG, "Copying from {} to {} at {}", db, backup_name, host);
 417
 418   int rc;
 419   std::string backup_db = backup_name;
 420
 421   sqlite3* pFile; /* Database connection opened on zFilename */
 422   sqlite3_backup* pBackup; /* Backup object used to copy data */
 423
 424   //
 425   if (backup_name[0] == '/' || backup_name[0] == '\\')
 426     backup_db = backup_db.substr(1);
 427
 428   // ensure the ".db" extension is appended to the end
 429   if (backup_db.find(".db") != (backup_db.length() - 3))
 430     backup_db += ".db";
 431
 432   std::string backup_path = host + backup_db;
 433
 434   /* Open the database file identified by zFilename. Exit early if this fails
 435   ** for any reason. */
 436   rc = sqlite3_open(backup_path.c_str(), &pFile);
 437   if (rc == SQLITE_OK)
 438   {
 439     pBackup = sqlite3_backup_init(pFile, "main", getHandle(), "main");
 440
 441     if (pBackup)
 442     {
 443       (void)sqlite3_backup_step(pBackup, -1);
 444       (void)sqlite3_backup_finish(pBackup);
 445     }
 446
 447     rc = sqlite3_errcode(pFile);
 448   }
 449
 450   (void)sqlite3_close(pFile);
 451
 452   if (rc != SQLITE_OK)
 453     throw DbErrors("Can't copy database. (%d)", rc);
 454
 455   return rc;
 456 }
 457
 458 int SqliteDatabase::drop_analytics(void)
 459 {
 460   // SqliteDatabase::copy used a full database copy, so we have a new version
 461   // with all the analytics stuff. We should clean database from everything but data
 462   if (active == false)
 463     throw DbErrors("Can't drop extras database: no active connection...");
 464
 465   char sqlcmd[4096];
 466   result_set res;
 467
 468   CLog::LogFC(LOGDEBUG, LOGDATABASE, "Cleaning indexes from database {} at {}", db, host);
 469   snprintf(sqlcmd, sizeof(sqlcmd),
 470            "SELECT name FROM sqlite_master WHERE type == 'index' AND sql IS NOT NULL");
 471   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 472     return DB_UNEXPECTED_RESULT;
 473
 474   for (size_t i = 0; i < res.records.size(); i++)
 475   {
 476     snprintf(sqlcmd, sizeof(sqlcmd), "DROP INDEX '%s'",
 477              res.records[i]->at(0).get_asString().c_str());
 478     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 479       return DB_UNEXPECTED_RESULT;
 480   }
 481   res.clear();
 482
 483   CLog::LogFC(LOGDEBUG, LOGDATABASE, "Cleaning views from database {} at {}", db, host);
 484   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT name FROM sqlite_master WHERE type == 'view'");
 485   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 486     return DB_UNEXPECTED_RESULT;
 487
 488   for (size_t i = 0; i < res.records.size(); i++)
 489   {
 490     snprintf(sqlcmd, sizeof(sqlcmd), "DROP VIEW '%s'",
 491              res.records[i]->at(0).get_asString().c_str());
 492     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 493       return DB_UNEXPECTED_RESULT;
 494   }
 495   res.clear();
 496
 497   CLog::LogFC(LOGDEBUG, LOGDATABASE, "Cleaning triggers from database {} at {}", db, host);
 498   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT name FROM sqlite_master WHERE type == 'trigger'");
 499   if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 500     return DB_UNEXPECTED_RESULT;
 501
 502   for (size_t i = 0; i < res.records.size(); i++)
 503   {
 504     snprintf(sqlcmd, sizeof(sqlcmd), "DROP TRIGGER '%s'",
 505              res.records[i]->at(0).get_asString().c_str());
 506     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 507       return DB_UNEXPECTED_RESULT;
 508   }
 509   // res would be cleared on destruct
 510
 511   return DB_COMMAND_OK;
 512 }
 513
 514 int SqliteDatabase::drop()
 515 {
 516   if (active == false)
 517     throw DbErrors("Can't drop database: no active connection...");
 518   disconnect();
 519   if (!unlink(db.c_str()))
 520   {
 521     throw DbErrors("Can't drop database: can't unlink the file %s,\nError: %s", db.c_str(),
 522                    strerror(errno));
 523   }
 524   return DB_COMMAND_OK;
 525 }
 526
 527 long SqliteDatabase::nextid(const char* sname)
 528 {
 529   CLog::LogFC(LOGDEBUG, LOGDATABASE, "nextid for {}", sname);
 530
 531   if (!active)
 532     return DB_UNEXPECTED_RESULT;
 533   int id; /*,nrow,ncol;*/
 534   result_set res;
 535   char sqlcmd[512];
 536   snprintf(sqlcmd, sizeof(sqlcmd), "SELECT nextid FROM %s WHERE seq_name = '%s'",
 537            sequence_table.c_str(), sname);
 538   if ((last_err = sqlite3_exec(getHandle(), sqlcmd, &callback, &res, NULL)) != SQLITE_OK)
 539   {
 540     return DB_UNEXPECTED_RESULT;
 541   }
 542   if (res.records.empty())
 543   {
 544     id = 1;
 545     snprintf(sqlcmd, sizeof(sqlcmd), "INSERT INTO %s (nextid,seq_name) VALUES (%d,'%s')",
 546              sequence_table.c_str(), id, sname);
 547     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 548       return DB_UNEXPECTED_RESULT;
 549     return id;
 550   }
 551   else
 552   {
 553     id = res.records[0]->at(0).get_asInt() + 1;
 554     snprintf(sqlcmd, sizeof(sqlcmd), "UPDATE %s SET nextid=%d WHERE seq_name = '%s'",
 555              sequence_table.c_str(), id, sname);
 556     if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK)
 557       return DB_UNEXPECTED_RESULT;
 558     return id;
 559   }
 560   return DB_UNEXPECTED_RESULT;
 561 }
 562
 563 // methods for transactions
 564 // ---------------------------------------------
 565 void SqliteDatabase::start_transaction()
 566 {
 567   if (active)
 568   {
 569     assert(!_in_transaction);
 570     sqlite3_exec(conn, "begin IMMEDIATE", NULL, NULL, NULL);
 571     CLog::LogFC(LOGDEBUG, LOGDATABASE, "Sqlite start transaction");
 572
 573     if (_in_transaction)
 574       CLog::LogF(LOGERROR, "error: nested transactions are not supported.");
 575     else
 576       _in_transaction = true;
 577   }
 578 }
 579
 580 void SqliteDatabase::commit_transaction()
 581 {
 582   if (active)
 583   {
 584     assert(_in_transaction);
 585     sqlite3_exec(conn, "commit", NULL, NULL, NULL);
 586     CLog::LogFC(LOGDEBUG, LOGDATABASE, "Sqlite commit transaction");
 587     _in_transaction = false;
 588   }
 589 }
 590
 591 void SqliteDatabase::rollback_transaction()
 592 {
 593   if (active)
 594   {
 595     assert(_in_transaction);
 596     sqlite3_exec(conn, "rollback", NULL, NULL, NULL);
 597     CLog::LogFC(LOGDEBUG, LOGDATABASE, "Sqlite rollback transaction");
 598     _in_transaction = false;
 599   }
 600 }
 601
 602 // methods for formatting
 603 // ---------------------------------------------
 604 std::string SqliteDatabase::vprepare(const char* format, va_list args)
 605 {
 606   std::string strFormat = format;
 607   std::string strResult = "";
 608   char* p;
 609   size_t pos;
 610
 611   //  %q is the sqlite format string for %s.
 612   //  Any bad character, like "'", will be replaced with a proper one
 613   pos = 0;
 614   while ((pos = strFormat.find("%s", pos)) != std::string::npos)
 615     strFormat.replace(pos++, 2, "%q");
 616
 617   //  the %I64 enhancement is not supported by sqlite3_vmprintf
 618   //  must be %ll instead
 619   pos = 0;
 620   while ((pos = strFormat.find("%I64", pos)) != std::string::npos)
 621     strFormat.replace(pos++, 4, "%ll");
 622
 623   p = sqlite3_vmprintf(strFormat.c_str(), args);
 624   if (p)
 625   {
 626     strResult = p;
 627     sqlite3_free(p);
 628   }
 629
 630   // Strip SEPARATOR from all GROUP_CONCAT statements:
 631   // before: GROUP_CONCAT(field SEPARATOR '; ')
 632   // after:  GROUP_CONCAT(field, '; ')
 633   // Can not specify separator when have DISTINCT, comma used by default
 634   pos = strResult.find("GROUP_CONCAT(");
 635   while (pos != std::string::npos)
 636   {
 637     size_t pos2 = strResult.find(" SEPARATOR ", pos + 1);
 638     if (pos2 != std::string::npos)
 639       strResult.replace(pos2, 10, ",");
 640     pos = strResult.find("GROUP_CONCAT(", pos + 1);
 641   }
 642   // Replace CONCAT with || to concatenate text fields:
 643   // before: CONCAT(field1, field2, field3)
 644   // after: field1 || field2 || field3
 645   // Avoid commas in substatements and within single quotes
 646   // before: CONCAT(field1, ',', REPLACE(field2, ',', '-'), field3)
 647   // after: field1 || ',' || REPLACE(field2, ',', '-') || field3
 648   pos = strResult.find("CONCAT(");
 649   while (pos != std::string::npos)
 650   {
 651     if (pos == 0 || strResult[pos - 1] == ' ') // Not GROUP_CONCAT
 652     {
 653       // Check each char for other bracket or single quote pairs
 654       unsigned int brackets = 1;
 655       bool quoted = false;
 656       size_t index = pos + 7; // start after "CONCAT("
 657       while (index < strResult.size() && brackets != 0)
 658       {
 659         if (strResult[index] == '(')
 660           brackets++;
 661         else if (strResult[index] == ')')
 662         {
 663           brackets--;
 664           if (brackets == 0)
 665             strResult.erase(index, 1); //Remove closing bracket of CONCAT
 666         }
 667         else if (strResult[index] == '\'')
 668           quoted = !quoted;
 669         else if (strResult[index] == ',' && brackets == 1 && !quoted)
 670           strResult.replace(index, 1, "||");
 671         index++;
 672       }
 673       strResult.erase(pos, 7); //Remove "CONCAT("
 674     }
 675     pos = strResult.find("CONCAT(", pos + 1);
 676   }
 677
 678   return strResult;
 679 }
 680
 681 //************* SqliteDataset implementation ***************
 682
 683 SqliteDataset::SqliteDataset() : Dataset()
 684 {
 685   haveError = false;
 686   db = NULL;
 687   autorefresh = false;
 688 }
 689
 690 SqliteDataset::SqliteDataset(SqliteDatabase* newDb) : Dataset(newDb)
 691 {
 692   haveError = false;
 693   db = newDb;
 694   autorefresh = false;
 695 }
 696
 697 SqliteDataset::~SqliteDataset()
 698 {
 699 }
 700
 701 void SqliteDataset::set_autorefresh(bool val)
 702 {
 703   autorefresh = val;
 704 }
 705
 706 //--------- protected functions implementation -----------------//
 707
 708 sqlite3* SqliteDataset::handle()
 709 {
 710   if (db != NULL)
 711   {
 712     return static_cast<SqliteDatabase*>(db)->getHandle();
 713   }
 714   else
 715     return NULL;
 716 }
 717
 718 void SqliteDataset::make_query(StringList& _sql)
 719 {
 720   std::string query;
 721   if (db == NULL)
 722     throw DbErrors("No Database Connection");
 723
 724   try
 725   {
 726
 727     if (autocommit)
 728       db->start_transaction();
 729
 730     for (const std::string& i : _sql)
 731     {
 732       query = i;
 733       char* err = NULL;
 734       Dataset::parse_sql(query);
 735       if (db->setErr(sqlite3_exec(this->handle(), query.c_str(), NULL, NULL, &err),
 736                      query.c_str()) != SQLITE_OK)
 737       {
 738         std::string message = db->getErrorMsg();
 739         if (err)
 740         {
 741           message.append(" (");
 742           message.append(err);
 743           message.append(")");
 744           sqlite3_free(err);
 745         }
 746         throw DbErrors("%s", message.c_str());
 747       }
 748     } // end of for
 749
 750     if (db->in_transaction() && autocommit)
 751       db->commit_transaction();
 752
 753     active = true;
 754     ds_state = dsSelect;
 755     if (autorefresh)
 756       refresh();
 757
 758   } // end of try
 759   catch (...)
 760   {
 761     if (db->in_transaction())
 762       db->rollback_transaction();
 763     throw;
 764   }
 765 }
 766
 767 void SqliteDataset::make_insert()
 768 {
 769   make_query(insert_sql);
 770   last();
 771 }
 772
 773 void SqliteDataset::make_edit()
 774 {
 775   make_query(update_sql);
 776 }
 777
 778 void SqliteDataset::make_deletion()
 779 {
 780   make_query(delete_sql);
 781 }
 782
 783 void SqliteDataset::fill_fields()
 784 {
 785   //cout <<"rr "<<result.records.size()<<"|" << frecno <<"\n";
 786   if ((db == NULL) || (result.record_header.empty()) ||
 787       (result.records.size() < (unsigned int)frecno))
 788     return;
 789
 790   if (fields_object->size() == 0) // Filling columns name
 791   {
 792     const unsigned int ncols = result.record_header.size();
 793     fields_object->resize(ncols);
 794     for (unsigned int i = 0; i < ncols; i++)
 795     {
 796       (*fields_object)[i].props = result.record_header[i];
 797       std::string name = result.record_header[i].name;
 798       name2indexMap.insert({str_toLower(name.data()), i});
 799     }
 800   }
 801
 802   //Filling result
 803   if (result.records.size() != 0)
 804   {
 805     const sql_record* row = result.records[frecno];
 806     if (row)
 807     {
 808       const unsigned int ncols = row->size();
 809       fields_object->resize(ncols);
 810       for (unsigned int i = 0; i < ncols; i++)
 811         (*fields_object)[i].val = row->at(i);
 812       return;
 813     }
 814   }
 815   const unsigned int ncols = result.record_header.size();
 816   fields_object->resize(ncols);
 817   for (unsigned int i = 0; i < ncols; i++)
 818     (*fields_object)[i].val = "";
 819 }
 820
 821 //------------- public functions implementation -----------------//
 822 bool SqliteDataset::dropIndex(const char* table, const char* index)
 823 {
 824   std::string sql;
 825
 826   sql = static_cast<SqliteDatabase*>(db)->prepare("DROP INDEX IF EXISTS %s", index);
 827
 828   return (exec(sql) == SQLITE_OK);
 829 }
 830
 831 int SqliteDataset::exec(const std::string& sql)
 832 {
 833   if (!handle())
 834     throw DbErrors("No Database Connection");
 835   std::string qry = sql;
 836
 837   exec_res.clear();
 838
 839   // Strip size constraints from indexes (not supported in sqlite)
 840   //
 841   // Example:
 842   //   before: CREATE UNIQUE INDEX ixPath ON path ( strPath(255) )
 843   //   after:  CREATE UNIQUE INDEX ixPath ON path ( strPath )
 844   //
 845   // NOTE: unexpected results occur if brackets are not matched
 846   if (qry.find("CREATE UNIQUE INDEX") != std::string::npos ||
 847       (qry.find("CREATE INDEX") != std::string::npos))
 848   {
 849     size_t pos = 0;
 850     size_t pos2 = 0;
 851
 852     if ((pos = qry.find('(')) != std::string::npos)
 853     {
 854       pos++;
 855       while ((pos = qry.find('(', pos)) != std::string::npos)
 856       {
 857         if ((pos2 = qry.find(')', pos)) != std::string::npos)
 858         {
 859           qry.replace(pos, pos2 - pos + 1, "");
 860           pos = pos2;
 861         }
 862       }
 863     }
 864   }
 865   // Strip ON table from DROP INDEX statements:
 866   // before: DROP INDEX foo ON table
 867   // after:  DROP INDEX foo
 868   size_t pos = qry.find("DROP INDEX ");
 869   if (pos != std::string::npos)
 870   {
 871     pos = qry.find(" ON ", pos + 1);
 872
 873     if (pos != std::string::npos)
 874       qry.resize(pos);
 875   }
 876
 877   const auto start = std::chrono::steady_clock::now();
 878
 879   char* errmsg;
 880   const int res =
 881       db->setErr(sqlite3_exec(handle(), qry.c_str(), &callback, &exec_res, &errmsg), qry.c_str());
 882
 883   const auto end = std::chrono::steady_clock::now();
 884   const auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start);
 885
 886   CLog::LogFC(LOGDEBUG, LOGDATABASE, "{} ms for query: {}", duration.count(), qry);
 887
 888   if (res == SQLITE_OK)
 889   {
 890     return res;
 891   }
 892   else
 893   {
 894     if (errmsg)
 895     {
 896       DbErrors err("%s (%s)", db->getErrorMsg(), errmsg);
 897       sqlite3_free(errmsg);
 898       throw err;
 899     }
 900     else
 901     {
 902       throw DbErrors("%s", db->getErrorMsg());
 903     }
 904   }
 905 }
 906
 907 int SqliteDataset::exec()
 908 {
 909   return exec(sql);
 910 }
 911
 912 const void* SqliteDataset::getExecRes()
 913 {
 914   return &exec_res;
 915 }
 916
 917 bool SqliteDataset::query(const std::string& query)
 918 {
 919   if (!handle())
 920     throw DbErrors("No Database Connection");
 921   const std::string& qry = query;
 922   int fs = qry.find("select");
 923   int fS = qry.find("SELECT");
 924   if (!(fs >= 0 || fS >= 0))
 925     throw DbErrors("MUST be select SQL!");
 926
 927   close();
 928
 929   sqlite3_stmt* stmt = NULL;
 930   if (db->setErr(sqlite3_prepare_v2(handle(), query.c_str(), -1, &stmt, NULL), query.c_str()) !=
 931       SQLITE_OK)
 932     throw DbErrors("%s", db->getErrorMsg());
 933
 934   // column headers
 935   const unsigned int numColumns = sqlite3_column_count(stmt);
 936   result.record_header.resize(numColumns);
 937   for (unsigned int i = 0; i < numColumns; i++)
 938     result.record_header[i].name = sqlite3_column_name(stmt, i);
 939
 940   // returned rows
 941   while (sqlite3_step(stmt) == SQLITE_ROW)
 942   { // have a row of data
 943     sql_record* res = new sql_record;
 944     res->resize(numColumns);
 945     for (unsigned int i = 0; i < numColumns; i++)
 946     {
 947       field_value& v = res->at(i);
 948       switch (sqlite3_column_type(stmt, i))
 949       {
 950         case SQLITE_INTEGER:
 951           v.set_asInt64(sqlite3_column_int64(stmt, i));
 952           break;
 953         case SQLITE_FLOAT:
 954           v.set_asDouble(sqlite3_column_double(stmt, i));
 955           break;
 956         case SQLITE_TEXT:
 957           v.set_asString(reinterpret_cast<const char*>(sqlite3_column_text(stmt, i)),
 958                          sqlite3_column_bytes(stmt, i));
 959           break;
 960         case SQLITE_BLOB:
 961           v.set_asString(reinterpret_cast<const char*>(sqlite3_column_text(stmt, i)),
 962                          sqlite3_column_bytes(stmt, i));
 963           break;
 964         case SQLITE_NULL:
 965         default:
 966           v.set_asString("");
 967           v.set_isNull();
 968           break;
 969       }
 970     }
 971     result.records.push_back(res);
 972   }
 973   if (db->setErr(sqlite3_finalize(stmt), query.c_str()) == SQLITE_OK)
 974   {
 975     active = true;
 976     ds_state = dsSelect;
 977     this->first();
 978     return true;
 979   }
 980   else
 981   {
 982     throw DbErrors("%s", db->getErrorMsg());
 983   }
 984 }
 985
 986 void SqliteDataset::open(const std::string& sql)
 987 {
 988   set_select_sql(sql);
 989   open();
 990 }
 991
 992 void SqliteDataset::open()
 993 {
 994   if (select_sql.size())
 995   {
 996     query(select_sql);
 997   }
 998   else
 999   {
1000     ds_state = dsInactive;
1001   }
1002 }
1003
1004 void SqliteDataset::close()
1005 {
1006   Dataset::close();
1007   result.clear();
1008   edit_object->clear();
1009   fields_object->clear();
1010   ds_state = dsInactive;
1011   active = false;
1012 }
1013
1014 void SqliteDataset::cancel()
1015 {
1016   if ((ds_state == dsInsert) || (ds_state == dsEdit))
1017   {
1018     if (result.record_header.size())
1019       ds_state = dsSelect;
1020     else
1021       ds_state = dsInactive;
1022   }
1023 }
1024
1025 int SqliteDataset::num_rows()
1026 {
1027   return result.records.size();
1028 }
1029
1030 bool SqliteDataset::eof()
1031 {
1032   return feof;
1033 }
1034
1035 bool SqliteDataset::bof()
1036 {
1037   return fbof;
1038 }
1039
1040 void SqliteDataset::first()
1041 {
1042   Dataset::first();
1043   this->fill_fields();
1044 }
1045
1046 void SqliteDataset::last()
1047 {
1048   Dataset::last();
1049   fill_fields();
1050 }
1051
1052 void SqliteDataset::prev(void)
1053 {
1054   Dataset::prev();
1055   fill_fields();
1056 }
1057
1058 void SqliteDataset::next(void)
1059 {
1060   Dataset::next();
1061   if (!eof())
1062     fill_fields();
1063 }
1064
1065 void SqliteDataset::free_row(void)
1066 {
1067   if (frecno < 0 || (unsigned int)frecno >= result.records.size())
1068     return;
1069
1070   sql_record* row = result.records[frecno];
1071   if (row)
1072   {
1073     delete row;
1074     result.records[frecno] = NULL;
1075   }
1076 }
1077
1078 bool SqliteDataset::seek(int pos)
1079 {
1080   if (ds_state == dsSelect)
1081   {
1082     Dataset::seek(pos);
1083     fill_fields();
1084     return true;
1085   }
1086   return false;
1087 }
1088
1089 int64_t SqliteDataset::lastinsertid()
1090 {
1091   if (!handle())
1092     throw DbErrors("No Database Connection");
1093   return sqlite3_last_insert_rowid(handle());
1094 }
1095
1096 long SqliteDataset::nextid(const char* seq_name)
1097 {
1098   if (handle())
1099     return db->nextid(seq_name);
1100   else
1101     return DB_UNEXPECTED_RESULT;
1102 }
1103
1104 void SqliteDataset::interrupt()
1105 {
1106   sqlite3_interrupt(handle());
1107 }
1108 } // namespace dbiplus