diff options
Diffstat (limited to 'xbmc/dbwrappers')
| -rw-r--r-- | xbmc/dbwrappers/CMakeLists.txt | 18 | ||||
| -rw-r--r-- | xbmc/dbwrappers/Database.cpp | 858 | ||||
| -rw-r--r-- | xbmc/dbwrappers/Database.h | 321 | ||||
| -rw-r--r-- | xbmc/dbwrappers/DatabaseQuery.cpp | 571 | ||||
| -rw-r--r-- | xbmc/dbwrappers/DatabaseQuery.h | 150 | ||||
| -rw-r--r-- | xbmc/dbwrappers/dataset.cpp | 625 | ||||
| -rw-r--r-- | xbmc/dbwrappers/dataset.h | 470 | ||||
| -rw-r--r-- | xbmc/dbwrappers/mysqldataset.cpp | 2099 | ||||
| -rw-r--r-- | xbmc/dbwrappers/mysqldataset.h | 167 | ||||
| -rw-r--r-- | xbmc/dbwrappers/qry_dat.cpp | 902 | ||||
| -rw-r--r-- | xbmc/dbwrappers/qry_dat.h | 265 | ||||
| -rw-r--r-- | xbmc/dbwrappers/sqlitedataset.cpp | 1063 | ||||
| -rw-r--r-- | xbmc/dbwrappers/sqlitedataset.h | 161 |
13 files changed, 7670 insertions, 0 deletions
diff --git a/xbmc/dbwrappers/CMakeLists.txt b/xbmc/dbwrappers/CMakeLists.txt new file mode 100644 index 0000000..16a03f2 --- /dev/null +++ b/xbmc/dbwrappers/CMakeLists.txt @@ -0,0 +1,18 @@ +set(SOURCES Database.cpp + DatabaseQuery.cpp + dataset.cpp + qry_dat.cpp + sqlitedataset.cpp) + +set(HEADERS Database.h + DatabaseQuery.h + dataset.h + qry_dat.h + sqlitedataset.h) + +if(MYSQLCLIENT_FOUND OR MARIADBCLIENT_FOUND) + list(APPEND SOURCES mysqldataset.cpp) + list(APPEND HEADERS mysqldataset.h) +endif() + +core_add_library(dbwrappers) diff --git a/xbmc/dbwrappers/Database.cpp b/xbmc/dbwrappers/Database.cpp new file mode 100644 index 0000000..1c2c48c --- /dev/null +++ b/xbmc/dbwrappers/Database.cpp @@ -0,0 +1,858 @@ +/* + * Copyright (C) 2005-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#include "Database.h" + +#include "DatabaseManager.h" +#include "DbUrl.h" +#include "ServiceBroker.h" +#include "filesystem/SpecialProtocol.h" +#include "profiles/ProfileManager.h" +#include "settings/AdvancedSettings.h" +#include "settings/SettingsComponent.h" +#include "sqlitedataset.h" +#include "utils/SortUtils.h" +#include "utils/StringUtils.h" +#include "utils/log.h" + +#if defined(HAS_MYSQL) || defined(HAS_MARIADB) +#include "mysqldataset.h" +#endif + +#ifdef TARGET_POSIX +#include "platform/posix/ConvUtils.h" +#endif + +using namespace dbiplus; + +#define MAX_COMPRESS_COUNT 20 + +void CDatabase::Filter::AppendField(const std::string& strField) +{ + if (strField.empty()) + return; + + if (fields.empty() || fields == "*") + fields = strField; + else + fields += ", " + strField; +} + +void CDatabase::Filter::AppendJoin(const std::string& strJoin) +{ + if (strJoin.empty()) + return; + + if (join.empty()) + join = strJoin; + else + join += " " + strJoin; +} + +void CDatabase::Filter::AppendWhere(const std::string& strWhere, bool combineWithAnd /* = true */) +{ + if (strWhere.empty()) + return; + + if (where.empty()) + where = strWhere; + else + { + where = "(" + where + ") "; + where += combineWithAnd ? "AND" : "OR"; + where += " (" + strWhere + ")"; + } +} + +void CDatabase::Filter::AppendOrder(const std::string& strOrder) +{ + if (strOrder.empty()) + return; + + if (order.empty()) + order = strOrder; + else + order += ", " + strOrder; +} + +void CDatabase::Filter::AppendGroup(const std::string& strGroup) +{ + if (strGroup.empty()) + return; + + if (group.empty()) + group = strGroup; + else + group += ", " + strGroup; +} + +void CDatabase::ExistsSubQuery::AppendJoin(const std::string& strJoin) +{ + if (strJoin.empty()) + return; + + if (join.empty()) + join = strJoin; + else + join += " " + strJoin; +} + +void CDatabase::ExistsSubQuery::AppendWhere(const std::string& strWhere, + bool combineWithAnd /* = true */) +{ + if (strWhere.empty()) + return; + + if (where.empty()) + where = strWhere; + else + { + where += combineWithAnd ? " AND " : " OR "; + where += strWhere; + } +} + +bool CDatabase::ExistsSubQuery::BuildSQL(std::string& strSQL) +{ + if (tablename.empty()) + return false; + strSQL = "EXISTS (SELECT 1 FROM " + tablename; + if (!join.empty()) + strSQL += " " + join; + std::string strWhere; + if (!param.empty()) + strWhere = param; + if (!where.empty()) + { + if (!strWhere.empty()) + strWhere += " AND "; + strWhere += where; + } + if (!strWhere.empty()) + strSQL += " WHERE " + strWhere; + + strSQL += ")"; + return true; +} + +CDatabase::DatasetLayout::DatasetLayout(size_t totalfields) +{ + m_fields.resize(totalfields, DatasetFieldInfo(false, false, -1)); +} + +void CDatabase::DatasetLayout::SetField(int fieldNo, + const std::string& strField, + bool bOutput /*= false*/) +{ + if (fieldNo >= 0 && fieldNo < static_cast<int>(m_fields.size())) + { + m_fields[fieldNo].strField = strField; + m_fields[fieldNo].fetch = true; + m_fields[fieldNo].output = bOutput; + } +} + +void CDatabase::DatasetLayout::AdjustRecordNumbers(int offset) +{ + int recno = 0; + for (auto& field : m_fields) + { + if (field.fetch) + { + field.recno = recno + offset; + ++recno; + } + } +} + +bool CDatabase::DatasetLayout::GetFetch(int fieldno) +{ + if (fieldno >= 0 && fieldno < static_cast<int>(m_fields.size())) + return m_fields[fieldno].fetch; + return false; +} + +void CDatabase::DatasetLayout::SetFetch(int fieldno, bool bFetch /*= true*/) +{ + if (fieldno >= 0 && fieldno < static_cast<int>(m_fields.size())) + m_fields[fieldno].fetch = bFetch; +} + +bool CDatabase::DatasetLayout::GetOutput(int fieldno) +{ + if (fieldno >= 0 && fieldno < static_cast<int>(m_fields.size())) + return m_fields[fieldno].output; + return false; +} + +int CDatabase::DatasetLayout::GetRecNo(int fieldno) +{ + if (fieldno >= 0 && fieldno < static_cast<int>(m_fields.size())) + return m_fields[fieldno].recno; + return -1; +} + +const std::string CDatabase::DatasetLayout::GetFields() +{ + std::string strSQL; + for (const auto& field : m_fields) + { + if (!field.strField.empty() && field.fetch) + { + if (strSQL.empty()) + strSQL = field.strField; + else + strSQL += ", " + field.strField; + } + } + + return strSQL; +} + +bool CDatabase::DatasetLayout::HasFilterFields() +{ + for (const auto& field : m_fields) + { + if (field.fetch) + return true; + } + return false; +} + +CDatabase::CDatabase() + : m_profileManager(*CServiceBroker::GetSettingsComponent()->GetProfileManager()) +{ + m_openCount = 0; + m_sqlite = true; + m_multipleExecute = false; +} + +CDatabase::~CDatabase(void) +{ + Close(); +} + +void CDatabase::Split(const std::string& strFileNameAndPath, + std::string& strPath, + std::string& strFileName) +{ + strFileName = ""; + strPath = ""; + int i = strFileNameAndPath.size() - 1; + while (i > 0) + { + char ch = strFileNameAndPath[i]; + if (ch == ':' || ch == '/' || ch == '\\') + break; + else + i--; + } + strPath = strFileNameAndPath.substr(0, i); + strFileName = strFileNameAndPath.substr(i); +} + +std::string CDatabase::PrepareSQL(std::string strStmt, ...) const +{ + std::string strResult = ""; + + if (nullptr != m_pDB) + { + va_list args; + va_start(args, strStmt); + strResult = m_pDB->vprepare(strStmt.c_str(), args); + va_end(args); + } + + return strResult; +} + +std::string CDatabase::GetSingleValue(const std::string& query, std::unique_ptr<Dataset>& ds) +{ + std::string ret; + try + { + if (!m_pDB || !ds) + return ret; + + if (ds->query(query) && ds->num_rows() > 0) + ret = ds->fv(0).get_asString(); + + ds->close(); + } + catch (...) + { + CLog::Log(LOGERROR, "{} - failed on query '{}'", __FUNCTION__, query); + } + return ret; +} + +std::string CDatabase::GetSingleValue(const std::string& strTable, + const std::string& strColumn, + const std::string& strWhereClause /* = std::string() */, + const std::string& strOrderBy /* = std::string() */) +{ + std::string query = PrepareSQL("SELECT %s FROM %s", strColumn.c_str(), strTable.c_str()); + if (!strWhereClause.empty()) + query += " WHERE " + strWhereClause; + if (!strOrderBy.empty()) + query += " ORDER BY " + strOrderBy; + query += " LIMIT 1"; + return GetSingleValue(query, m_pDS); +} + +std::string CDatabase::GetSingleValue(const std::string& query) +{ + return GetSingleValue(query, m_pDS); +} + +int CDatabase::GetSingleValueInt(const std::string& query, std::unique_ptr<Dataset>& ds) +{ + int ret = 0; + try + { + if (!m_pDB || !ds) + return ret; + + if (ds->query(query) && ds->num_rows() > 0) + ret = ds->fv(0).get_asInt(); + + ds->close(); + } + catch (...) + { + CLog::Log(LOGERROR, "{} - failed on query '{}'", __FUNCTION__, query); + } + return ret; +} + +int CDatabase::GetSingleValueInt(const std::string& strTable, + const std::string& strColumn, + const std::string& strWhereClause /* = std::string() */, + const std::string& strOrderBy /* = std::string() */) +{ + std::string strResult = GetSingleValue(strTable, strColumn, strWhereClause, strOrderBy); + return static_cast<int>(strtol(strResult.c_str(), NULL, 10)); +} + +int CDatabase::GetSingleValueInt(const std::string& query) +{ + return GetSingleValueInt(query, m_pDS); +} + +bool CDatabase::DeleteValues(const std::string& strTable, const Filter& filter /* = Filter() */) +{ + std::string strQuery; + BuildSQL(PrepareSQL("DELETE FROM %s ", strTable.c_str()), filter, strQuery); + return ExecuteQuery(strQuery); +} + +bool CDatabase::BeginMultipleExecute() +{ + m_multipleExecute = true; + m_multipleQueries.clear(); + return true; +} + +bool CDatabase::CommitMultipleExecute() +{ + m_multipleExecute = false; + BeginTransaction(); + for (const auto& i : m_multipleQueries) + { + if (!ExecuteQuery(i)) + { + RollbackTransaction(); + return false; + } + } + m_multipleQueries.clear(); + return CommitTransaction(); +} + +bool CDatabase::ExecuteQuery(const std::string& strQuery) +{ + if (m_multipleExecute) + { + m_multipleQueries.push_back(strQuery); + return true; + } + + bool bReturn = false; + + try + { + if (nullptr == m_pDB) + return bReturn; + if (nullptr == m_pDS) + return bReturn; + m_pDS->exec(strQuery); + bReturn = true; + } + catch (...) + { + CLog::Log(LOGERROR, "{} - failed to execute query '{}'", __FUNCTION__, strQuery); + } + + return bReturn; +} + +bool CDatabase::ResultQuery(const std::string& strQuery) const +{ + bool bReturn = false; + + try + { + if (nullptr == m_pDB) + return bReturn; + if (nullptr == m_pDS) + return bReturn; + + std::string strPreparedQuery = PrepareSQL(strQuery); + + bReturn = m_pDS->query(strPreparedQuery); + } + catch (...) + { + CLog::Log(LOGERROR, "{} - failed to execute query '{}'", __FUNCTION__, strQuery); + } + + return bReturn; +} + +bool CDatabase::QueueInsertQuery(const std::string& strQuery) +{ + if (strQuery.empty()) + return false; + + if (!m_bMultiInsert) + { + if (nullptr == m_pDB) + return false; + if (nullptr == m_pDS2) + return false; + + m_bMultiInsert = true; + m_pDS2->insert(); + } + + m_pDS2->add_insert_sql(strQuery); + + return true; +} + +bool CDatabase::CommitInsertQueries() +{ + bool bReturn = true; + + if (m_bMultiInsert) + { + try + { + m_bMultiInsert = false; + m_pDS2->post(); + m_pDS2->clear_insert_sql(); + } + catch (...) + { + bReturn = false; + CLog::Log(LOGERROR, "{} - failed to execute queries", __FUNCTION__); + } + } + + return bReturn; +} + +size_t CDatabase::GetInsertQueriesCount() +{ + return m_pDS2->insert_sql_count(); +} + +bool CDatabase::QueueDeleteQuery(const std::string& strQuery) +{ + if (strQuery.empty() || !m_pDB || !m_pDS) + return false; + + m_bMultiDelete = true; + m_pDS->del(); + m_pDS->add_delete_sql(strQuery); + return true; +} + +bool CDatabase::CommitDeleteQueries() +{ + bool bReturn = true; + + if (m_bMultiDelete) + { + try + { + m_bMultiDelete = false; + m_pDS->deletion(); + m_pDS->clear_delete_sql(); + } + catch (...) + { + bReturn = false; + CLog::Log(LOGERROR, "{} - failed to execute queries", __FUNCTION__); + } + } + + return bReturn; +} + +size_t CDatabase::GetDeleteQueriesCount() +{ + return m_pDS->delete_sql_count(); +} + +bool CDatabase::Open() +{ + DatabaseSettings db_fallback; + return Open(db_fallback); +} + +bool CDatabase::Open(const DatabaseSettings& settings) +{ + if (IsOpen()) + { + m_openCount++; + return true; + } + + // check our database manager to see if this database can be opened + if (!CServiceBroker::GetDatabaseManager().CanOpen(GetBaseDBName())) + return false; + + DatabaseSettings dbSettings = settings; + InitSettings(dbSettings); + + std::string dbName = dbSettings.name; + dbName += std::to_string(GetSchemaVersion()); + return Connect(dbName, dbSettings, false); +} + +void CDatabase::InitSettings(DatabaseSettings& dbSettings) +{ + m_sqlite = true; + +#if defined(HAS_MYSQL) || defined(HAS_MARIADB) + if (dbSettings.type == "mysql") + { + // check we have all information before we cancel the fallback + if (!(dbSettings.host.empty() || dbSettings.user.empty() || dbSettings.pass.empty())) + m_sqlite = false; + else + CLog::Log(LOGINFO, "Essential mysql database information is missing. Require at least host, " + "user and pass defined."); + } + else +#else + if (dbSettings.type == "mysql") + CLog::Log( + LOGERROR, + "MySQL library requested but MySQL support is not compiled in. Falling back to sqlite3."); +#endif + { + dbSettings.type = "sqlite3"; + if (dbSettings.host.empty()) + dbSettings.host = CSpecialProtocol::TranslatePath(m_profileManager.GetDatabaseFolder()); + } + + // use separate, versioned database + if (dbSettings.name.empty()) + dbSettings.name = GetBaseDBName(); +} + +void CDatabase::CopyDB(const std::string& latestDb) +{ + m_pDB->copy(latestDb.c_str()); +} + +void CDatabase::DropAnalytics() +{ + m_pDB->drop_analytics(); +} + +bool CDatabase::Connect(const std::string& dbName, const DatabaseSettings& dbSettings, bool create) +{ + // create the appropriate database structure + if (dbSettings.type == "sqlite3") + { + m_pDB.reset(new SqliteDatabase()); + } +#if defined(HAS_MYSQL) || defined(HAS_MARIADB) + else if (dbSettings.type == "mysql") + { + m_pDB.reset(new MysqlDatabase()); + } +#endif + else + { + CLog::Log(LOGERROR, "Unable to determine database type: {}", dbSettings.type); + return false; + } + + // host name is always required + m_pDB->setHostName(dbSettings.host.c_str()); + + if (!dbSettings.port.empty()) + m_pDB->setPort(dbSettings.port.c_str()); + + if (!dbSettings.user.empty()) + m_pDB->setLogin(dbSettings.user.c_str()); + + if (!dbSettings.pass.empty()) + m_pDB->setPasswd(dbSettings.pass.c_str()); + + // database name is always required + m_pDB->setDatabase(dbName.c_str()); + + // set configuration regardless if any are empty + m_pDB->setConfig(dbSettings.key.c_str(), dbSettings.cert.c_str(), dbSettings.ca.c_str(), + dbSettings.capath.c_str(), dbSettings.ciphers.c_str(), dbSettings.compression); + + // create the datasets + m_pDS.reset(m_pDB->CreateDataset()); + m_pDS2.reset(m_pDB->CreateDataset()); + + if (m_pDB->connect(create) != DB_CONNECTION_OK) + return false; + + try + { + // test if db already exists, if not we need to create the tables + if (!m_pDB->exists() && create) + { + if (dbSettings.type == "sqlite3") + { + // Modern file systems have a cluster/block size of 4k. + // To gain better performance when performing write + // operations to the database, set the page size of the + // database file to 4k. + // This needs to be done before any table is created. + m_pDS->exec("PRAGMA page_size=4096\n"); + + // Also set the memory cache size to 16k + m_pDS->exec("PRAGMA default_cache_size=4096\n"); + } + CreateDatabase(); + } + + // sqlite3 post connection operations + if (dbSettings.type == "sqlite3") + { + m_pDS->exec("PRAGMA cache_size=4096\n"); + m_pDS->exec("PRAGMA synchronous='NORMAL'\n"); + m_pDS->exec("PRAGMA count_changes='OFF'\n"); + } + } + catch (DbErrors& error) + { + CLog::Log(LOGERROR, "{} failed with '{}'", __FUNCTION__, error.getMsg()); + m_openCount = 1; // set to open so we can execute Close() + Close(); + return false; + } + + m_openCount = 1; // our database is open + return true; +} + +int CDatabase::GetDBVersion() +{ + m_pDS->query("SELECT idVersion FROM version\n"); + if (m_pDS->num_rows() > 0) + return m_pDS->fv("idVersion").get_asInt(); + return 0; +} + +bool CDatabase::IsOpen() +{ + return m_openCount > 0; +} + +void CDatabase::Close() +{ + if (m_openCount == 0) + return; + + if (m_openCount > 1) + { + m_openCount--; + return; + } + + m_openCount = 0; + m_multipleExecute = false; + + if (nullptr == m_pDB) + return; + if (nullptr != m_pDS) + m_pDS->close(); + m_pDB->disconnect(); + m_pDB.reset(); + m_pDS.reset(); + m_pDS2.reset(); +} + +bool CDatabase::Compress(bool bForce /* =true */) +{ + if (!m_sqlite) + return true; + + try + { + if (nullptr == m_pDB) + return false; + if (nullptr == m_pDS) + return false; + if (!bForce) + { + m_pDS->query("select iCompressCount from version"); + if (!m_pDS->eof()) + { + int iCount = m_pDS->fv(0).get_asInt(); + if (iCount > MAX_COMPRESS_COUNT) + iCount = -1; + m_pDS->close(); + std::string strSQL = PrepareSQL("update version set iCompressCount=%i\n", ++iCount); + m_pDS->exec(strSQL); + if (iCount != 0) + return true; + } + } + + if (!m_pDS->exec("vacuum\n")) + return false; + } + catch (...) + { + CLog::Log(LOGERROR, "{} - Compressing the database failed", __FUNCTION__); + return false; + } + return true; +} + +void CDatabase::Interrupt() +{ + m_pDS->interrupt(); +} + +void CDatabase::BeginTransaction() +{ + try + { + if (nullptr != m_pDB) + m_pDB->start_transaction(); + } + catch (...) + { + CLog::Log(LOGERROR, "database:begintransaction failed"); + } +} + +bool CDatabase::CommitTransaction() +{ + try + { + if (nullptr != m_pDB) + m_pDB->commit_transaction(); + } + catch (...) + { + CLog::Log(LOGERROR, "database:committransaction failed"); + return false; + } + return true; +} + +void CDatabase::RollbackTransaction() +{ + try + { + if (nullptr != m_pDB) + m_pDB->rollback_transaction(); + } + catch (...) + { + CLog::Log(LOGERROR, "database:rollbacktransaction failed"); + } +} + +bool CDatabase::CreateDatabase() +{ + BeginTransaction(); + try + { + CLog::Log(LOGINFO, "creating version table"); + m_pDS->exec("CREATE TABLE version (idVersion integer, iCompressCount integer)\n"); + std::string strSQL = PrepareSQL("INSERT INTO version (idVersion,iCompressCount) values(%i,0)\n", + GetSchemaVersion()); + m_pDS->exec(strSQL); + + CreateTables(); + CreateAnalytics(); + } + catch (...) + { + CLog::Log(LOGERROR, "{} unable to create database:{}", __FUNCTION__, (int)GetLastError()); + RollbackTransaction(); + return false; + } + + return CommitTransaction(); +} + +void CDatabase::UpdateVersionNumber() +{ + std::string strSQL = PrepareSQL("UPDATE version SET idVersion=%i\n", GetSchemaVersion()); + m_pDS->exec(strSQL); +} + +bool CDatabase::BuildSQL(const std::string& strQuery, const Filter& filter, std::string& strSQL) +{ + strSQL = strQuery; + + if (!filter.join.empty()) + strSQL += filter.join; + if (!filter.where.empty()) + strSQL += " WHERE " + filter.where; + if (!filter.group.empty()) + strSQL += " GROUP BY " + filter.group; + if (!filter.order.empty()) + strSQL += " ORDER BY " + filter.order; + if (!filter.limit.empty()) + strSQL += " LIMIT " + filter.limit; + + return true; +} + +bool CDatabase::BuildSQL(const std::string& strBaseDir, + const std::string& strQuery, + Filter& filter, + std::string& strSQL, + CDbUrl& dbUrl) +{ + SortDescription sorting; + return BuildSQL(strBaseDir, strQuery, filter, strSQL, dbUrl, sorting); +} + +bool CDatabase::BuildSQL(const std::string& strBaseDir, + const std::string& strQuery, + Filter& filter, + std::string& strSQL, + CDbUrl& dbUrl, + SortDescription& sorting /* = SortDescription() */) +{ + // parse the base path to get additional filters + dbUrl.Reset(); + if (!dbUrl.FromString(strBaseDir) || !GetFilter(dbUrl, filter, sorting)) + return false; + + return BuildSQL(strQuery, filter, strSQL); +} diff --git a/xbmc/dbwrappers/Database.h b/xbmc/dbwrappers/Database.h new file mode 100644 index 0000000..2f3d35d --- /dev/null +++ b/xbmc/dbwrappers/Database.h @@ -0,0 +1,321 @@ +/* + * Copyright (C) 2005-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#pragma once + +namespace dbiplus +{ +class Database; +class Dataset; +} // namespace dbiplus + +#include <memory> +#include <string> +#include <vector> + +class DatabaseSettings; // forward +class CDbUrl; +class CProfileManager; +struct SortDescription; + +class CDatabase +{ +public: + class Filter + { + public: + Filter() : fields("*") {} + explicit Filter(const char* w) : fields("*"), where(w) {} + explicit Filter(const std::string& w) : fields("*"), where(w) {} + + void AppendField(const std::string& strField); + void AppendJoin(const std::string& strJoin); + void AppendWhere(const std::string& strWhere, bool combineWithAnd = true); + void AppendOrder(const std::string& strOrder); + void AppendGroup(const std::string& strGroup); + + std::string fields; + std::string join; + std::string where; + std::string order; + std::string group; + std::string limit; + }; + + struct DatasetFieldInfo + { + DatasetFieldInfo(bool fetch, bool output, int recno) + : fetch(fetch), output(output), recno(recno) + { + } + + bool fetch; + bool output; + int recno; + std::string strField; + }; + + class DatasetLayout + { + public: + DatasetLayout(size_t totalfields); + void SetField(int fieldNo, const std::string& strField, bool bOutput = false); + void AdjustRecordNumbers(int offset); + bool GetFetch(int fieldno); + void SetFetch(int fieldno, bool bFetch = true); + bool GetOutput(int fieldno); + int GetRecNo(int fieldno); + const std::string GetFields(); + bool HasFilterFields(); + + private: + std::vector<DatasetFieldInfo> m_fields; + }; + + class ExistsSubQuery + { + public: + explicit ExistsSubQuery(const std::string& table) : tablename(table) {} + ExistsSubQuery(const std::string& table, const std::string& parameter) + : tablename(table), param(parameter) + { + } + void AppendJoin(const std::string& strJoin); + void AppendWhere(const std::string& strWhere, bool combineWithAnd = true); + bool BuildSQL(std::string& strSQL); + + std::string tablename; + std::string param; + std::string join; + std::string where; + }; + + CDatabase(); + virtual ~CDatabase(void); + bool IsOpen(); + virtual void Close(); + bool Compress(bool bForce = true); + void Interrupt(); + + bool Open(const DatabaseSettings& db); + + void BeginTransaction(); + virtual bool CommitTransaction(); + void RollbackTransaction(); + void CopyDB(const std::string& latestDb); + void DropAnalytics(); + + std::string PrepareSQL(std::string strStmt, ...) const; + + /*! + * @brief Get a single value from a table. + * @remarks The values of the strWhereClause and strOrderBy parameters have to be FormatSQL'ed when used. + * @param strTable The table to get the value from. + * @param strColumn The column to get. + * @param strWhereClause If set, use this WHERE clause. + * @param strOrderBy If set, use this ORDER BY clause. + * @return The requested value or an empty string if it wasn't found. + */ + std::string GetSingleValue(const std::string& strTable, + const std::string& strColumn, + const std::string& strWhereClause = std::string(), + const std::string& strOrderBy = std::string()); + std::string GetSingleValue(const std::string& query); + + /*! \brief Get a single value from a query on a dataset. + \param query the query in question. + \param ds the dataset to use for the query. + \return the value from the query, empty on failure. + */ + std::string GetSingleValue(const std::string& query, std::unique_ptr<dbiplus::Dataset>& ds); + + /*! + * @brief Get a single integer value from a table. + * @remarks The values of the strWhereClause and strOrderBy parameters have to be FormatSQL'ed when used. + * @param strTable The table to get the value from. + * @param strColumn The column to get. + * @param strWhereClause If set, use this WHERE clause. + * @param strOrderBy If set, use this ORDER BY clause. + * @return The requested value or 0 if it wasn't found. + */ + int GetSingleValueInt(const std::string& strTable, + const std::string& strColumn, + const std::string& strWhereClause = std::string(), + const std::string& strOrderBy = std::string()); + int GetSingleValueInt(const std::string& query); + + /*! \brief Get a single integer value from a query on a dataset. + \param query the query in question. + \param ds the dataset to use for the query. + \return the value from the query, 0 on failure. + */ + int GetSingleValueInt(const std::string& query, std::unique_ptr<dbiplus::Dataset>& ds); + + /*! + * @brief Delete values from a table. + * @param strTable The table to delete the values from. + * @param filter The Filter to apply to this query. + * @return True if the query was executed successfully, false otherwise. + */ + bool DeleteValues(const std::string& strTable, const Filter& filter = Filter()); + + /*! + * @brief Execute a query that does not return any result. + * Note that if BeginMultipleExecute() has been called, the + * query will be queued until CommitMultipleExecute() is called. + * @param strQuery The query to execute. + * @return True if the query was executed successfully, false otherwise. + * @sa BeginMultipleExecute, CommitMultipleExecute + */ + bool ExecuteQuery(const std::string& strQuery); + + /*! + * @brief Execute a query that returns a result. + * @remarks Call m_pDS->close(); to clean up the dataset when done. + * @param strQuery The query to execute. + * @return True if the query was executed successfully, false otherwise. + */ + bool ResultQuery(const std::string& strQuery) const; + + /*! + * @brief Start a multiple execution queue. Any ExecuteQuery() function + * following this call will be queued rather than executed until + * CommitMultipleExecute() is performed. + * NOTE: Queries that rely on any queued execute query will not + * function as expected during this period! + * @return true if we could start a multiple execution queue, false otherwise. + * @sa CommitMultipleExecute, ExecuteQuery + */ + bool BeginMultipleExecute(); + + /*! + * @brief Commit the multiple execution queue to the database. + * Queries are performed within a transaction, and the transaction + * is rolled back should any one query fail. + * @return True if the queries were executed successfully, false otherwise. + * @sa BeginMultipleExecute, ExecuteQuery + */ + bool CommitMultipleExecute(); + + /*! + * @brief Put an INSERT or REPLACE query in the queue. + * @param strQuery The query to queue. + * @return True if the query was added successfully, false otherwise. + */ + bool QueueInsertQuery(const std::string& strQuery); + + /*! + * @brief Commit all queries in the queue. + * @return True if all queries were executed successfully, false otherwise. + */ + bool CommitInsertQueries(); + + /*! + * @brief Get the number of INSERT queries in the queue. + * @return The number of queries. + */ + size_t GetInsertQueriesCount(); + + /*! + * @brief Put a DELETE query in the queue. + * @param strQuery The query to queue. + * @return True if the query was added successfully, false otherwise. + */ + bool QueueDeleteQuery(const std::string& strQuery); + + /*! + * @brief Commit all queued DELETE queries. + * @return True if all queries were executed successfully, false otherwise. + */ + bool CommitDeleteQueries(); + + /*! + * @brief Get the number of DELETE queries in the queue. + * @return The number of queries. + */ + size_t GetDeleteQueriesCount(); + + virtual bool GetFilter(CDbUrl& dbUrl, Filter& filter, SortDescription& sorting) { return true; } + virtual bool BuildSQL(const std::string& strBaseDir, + const std::string& strQuery, + Filter& filter, + std::string& strSQL, + CDbUrl& dbUrl); + virtual bool BuildSQL(const std::string& strBaseDir, + const std::string& strQuery, + Filter& filter, + std::string& strSQL, + CDbUrl& dbUrl, + SortDescription& sorting); + + bool Connect(const std::string& dbName, const DatabaseSettings& db, bool create); + +protected: + friend class CDatabaseManager; + + void Split(const std::string& strFileNameAndPath, std::string& strPath, std::string& strFileName); + + virtual bool Open(); + + /*! \brief Create database tables and analytics as needed. + Calls CreateTables() and CreateAnalytics() on child classes. + */ + bool CreateDatabase(); + + /* \brief Create tables for the current database schema. + Will be called on database creation. + */ + virtual void CreateTables() = 0; + + /* \brief Create views, indices and triggers for the current database schema. + Will be called on database creation and database update. + */ + virtual void CreateAnalytics() = 0; + + /* \brief Update database tables to the current version. + Note that analytics (views, indices, triggers) are not present during this + function, so don't rely on them. + */ + virtual void UpdateTables(int version) {} + + /* \brief The minimum schema version that we support updating from. + */ + virtual int GetMinSchemaVersion() const { return 0; } + + /* \brief The current schema version. + */ + virtual int GetSchemaVersion() const = 0; + virtual const char* GetBaseDBName() const = 0; + + int GetDBVersion(); + + bool BuildSQL(const std::string& strQuery, const Filter& filter, std::string& strSQL); + + bool m_sqlite; ///< \brief whether we use sqlite (defaults to true) + + std::unique_ptr<dbiplus::Database> m_pDB; + std::unique_ptr<dbiplus::Dataset> m_pDS; + std::unique_ptr<dbiplus::Dataset> m_pDS2; + +protected: + // Construction parameters + const CProfileManager& m_profileManager; + +private: + void InitSettings(DatabaseSettings& dbSettings); + void UpdateVersionNumber(); + + bool m_bMultiInsert = + false; /*!< True if there are any queries in the insert queue, false otherwise */ + bool m_bMultiDelete = + false; /*!< True if there are any queries in the delete queue, false otherwise */ + unsigned int m_openCount; + + bool m_multipleExecute; + std::vector<std::string> m_multipleQueries; +}; diff --git a/xbmc/dbwrappers/DatabaseQuery.cpp b/xbmc/dbwrappers/DatabaseQuery.cpp new file mode 100644 index 0000000..c1432be --- /dev/null +++ b/xbmc/dbwrappers/DatabaseQuery.cpp @@ -0,0 +1,571 @@ +/* + * Copyright (C) 2013-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#include "DatabaseQuery.h" + +#include "Database.h" +#include "XBDateTime.h" +#include "guilib/LocalizeStrings.h" +#include "utils/CharsetConverter.h" +#include "utils/StringUtils.h" +#include "utils/Variant.h" +#include "utils/XBMCTinyXML.h" + +typedef struct +{ + char string[15]; + CDatabaseQueryRule::SEARCH_OPERATOR op; + int localizedString; +} operatorField; + +static const operatorField operators[] = { + {"contains", CDatabaseQueryRule::OPERATOR_CONTAINS, 21400}, + {"doesnotcontain", CDatabaseQueryRule::OPERATOR_DOES_NOT_CONTAIN, 21401}, + {"is", CDatabaseQueryRule::OPERATOR_EQUALS, 21402}, + {"isnot", CDatabaseQueryRule::OPERATOR_DOES_NOT_EQUAL, 21403}, + {"startswith", CDatabaseQueryRule::OPERATOR_STARTS_WITH, 21404}, + {"endswith", CDatabaseQueryRule::OPERATOR_ENDS_WITH, 21405}, + {"greaterthan", CDatabaseQueryRule::OPERATOR_GREATER_THAN, 21406}, + {"lessthan", CDatabaseQueryRule::OPERATOR_LESS_THAN, 21407}, + {"after", CDatabaseQueryRule::OPERATOR_AFTER, 21408}, + {"before", CDatabaseQueryRule::OPERATOR_BEFORE, 21409}, + {"inthelast", CDatabaseQueryRule::OPERATOR_IN_THE_LAST, 21410}, + {"notinthelast", CDatabaseQueryRule::OPERATOR_NOT_IN_THE_LAST, 21411}, + {"true", CDatabaseQueryRule::OPERATOR_TRUE, 20122}, + {"false", CDatabaseQueryRule::OPERATOR_FALSE, 20424}, + {"between", CDatabaseQueryRule::OPERATOR_BETWEEN, 21456}}; + +CDatabaseQueryRule::CDatabaseQueryRule() +{ + m_field = 0; + m_operator = OPERATOR_CONTAINS; +} + +bool CDatabaseQueryRule::Load(const TiXmlNode* node, const std::string& encoding /* = "UTF-8" */) +{ + if (node == NULL) + return false; + + const TiXmlElement* element = node->ToElement(); + if (element == NULL) + return false; + + // format is: + // <rule field="Genre" operator="contains">parameter</rule> + // where parameter can either be a string or a list of + // <value> tags containing a string + const char* field = element->Attribute("field"); + const char* oper = element->Attribute("operator"); + if (field == NULL || oper == NULL) + return false; + + m_field = TranslateField(field); + m_operator = TranslateOperator(oper); + + if (m_operator == OPERATOR_TRUE || m_operator == OPERATOR_FALSE) + return true; + + const TiXmlNode* parameter = element->FirstChild(); + if (parameter == NULL) + return false; + + if (parameter->Type() == TiXmlNode::TINYXML_TEXT) + { + std::string utf8Parameter; + if (encoding.empty()) // utf8 + utf8Parameter = parameter->ValueStr(); + else + g_charsetConverter.ToUtf8(encoding, parameter->ValueStr(), utf8Parameter); + + if (!utf8Parameter.empty()) + m_parameter.push_back(utf8Parameter); + } + else if (parameter->Type() == TiXmlNode::TINYXML_ELEMENT) + { + const TiXmlNode* valueNode = element->FirstChild("value"); + while (valueNode != NULL) + { + const TiXmlNode* value = valueNode->FirstChild(); + if (value != NULL && value->Type() == TiXmlNode::TINYXML_TEXT) + { + std::string utf8Parameter; + if (encoding.empty()) // utf8 + utf8Parameter = value->ValueStr(); + else + g_charsetConverter.ToUtf8(encoding, value->ValueStr(), utf8Parameter); + + if (!utf8Parameter.empty()) + m_parameter.push_back(utf8Parameter); + } + + valueNode = valueNode->NextSibling("value"); + } + } + else + return false; + + return true; +} + +bool CDatabaseQueryRule::Load(const CVariant& obj) +{ + if (!obj.isMember("field") || !obj["field"].isString() || !obj.isMember("operator") || + !obj["operator"].isString()) + return false; + + m_field = TranslateField(obj["field"].asString().c_str()); + m_operator = TranslateOperator(obj["operator"].asString().c_str()); + + if (m_operator == OPERATOR_TRUE || m_operator == OPERATOR_FALSE) + return true; + + if (!obj.isMember("value") || (!obj["value"].isString() && !obj["value"].isArray())) + return false; + + const CVariant& value = obj["value"]; + if (value.isString()) + m_parameter.push_back(value.asString()); + else if (value.isArray()) + { + for (CVariant::const_iterator_array val = value.begin_array(); val != value.end_array(); ++val) + { + if (val->isString() && !val->asString().empty()) + m_parameter.push_back(val->asString()); + } + if (m_parameter.empty()) + m_parameter.emplace_back(""); + } + else + return false; + + return true; +} + +bool CDatabaseQueryRule::Save(TiXmlNode* parent) const +{ + if (parent == NULL || + (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE)) + return false; + + TiXmlElement rule("rule"); + rule.SetAttribute("field", TranslateField(m_field).c_str()); + rule.SetAttribute("operator", TranslateOperator(m_operator).c_str()); + + for (const auto& it : m_parameter) + { + TiXmlElement value("value"); + TiXmlText text(it); + value.InsertEndChild(text); + rule.InsertEndChild(value); + } + + parent->InsertEndChild(rule); + + return true; +} + +bool CDatabaseQueryRule::Save(CVariant& obj) const +{ + if (obj.isNull() || + (m_parameter.empty() && m_operator != OPERATOR_TRUE && m_operator != OPERATOR_FALSE)) + return false; + + obj["field"] = TranslateField(m_field); + obj["operator"] = TranslateOperator(m_operator); + obj["value"] = m_parameter; + + return true; +} + +CDatabaseQueryRule::SEARCH_OPERATOR CDatabaseQueryRule::TranslateOperator(const char* oper) +{ + for (const operatorField& o : operators) + if (StringUtils::EqualsNoCase(oper, o.string)) + return o.op; + return OPERATOR_CONTAINS; +} + +std::string CDatabaseQueryRule::TranslateOperator(SEARCH_OPERATOR oper) +{ + for (const operatorField& o : operators) + if (oper == o.op) + return o.string; + return "contains"; +} + +std::string CDatabaseQueryRule::GetLocalizedOperator(SEARCH_OPERATOR oper) +{ + for (const operatorField& o : operators) + if (oper == o.op) + return g_localizeStrings.Get(o.localizedString); + return g_localizeStrings.Get(16018); +} + +void CDatabaseQueryRule::GetAvailableOperators(std::vector<std::string>& operatorList) +{ + for (const operatorField& o : operators) + operatorList.emplace_back(o.string); +} + +std::string CDatabaseQueryRule::GetParameter() const +{ + return StringUtils::Join(m_parameter, DATABASEQUERY_RULE_VALUE_SEPARATOR); +} + +void CDatabaseQueryRule::SetParameter(const std::string& value) +{ + m_parameter = StringUtils::Split(value, DATABASEQUERY_RULE_VALUE_SEPARATOR); +} + +void CDatabaseQueryRule::SetParameter(const std::vector<std::string>& values) +{ + m_parameter.assign(values.begin(), values.end()); +} + +std::string CDatabaseQueryRule::ValidateParameter(const std::string& parameter) const +{ + if ((GetFieldType(m_field) == REAL_FIELD || GetFieldType(m_field) == NUMERIC_FIELD || + GetFieldType(m_field) == SECONDS_FIELD) && + parameter.empty()) + return "0"; // interpret empty fields as 0 + return parameter; +} + +std::string CDatabaseQueryRule::FormatParameter(const std::string& operatorString, + const std::string& param, + const CDatabase& db, + const std::string& strType) const +{ + std::string parameter; + if (GetFieldType(m_field) == TEXTIN_FIELD) + { + std::vector<std::string> split = StringUtils::Split(param, ','); + for (std::string& itIn : split) + { + if (!parameter.empty()) + parameter += ","; + parameter += db.PrepareSQL("'%s'", StringUtils::Trim(itIn).c_str()); + } + parameter = " IN (" + parameter + ")"; + } + else + parameter = db.PrepareSQL(operatorString, ValidateParameter(param).c_str()); + + if (GetFieldType(m_field) == DATE_FIELD) + { + if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST) + { // translate time period + CDateTime date = CDateTime::GetCurrentDateTime(); + CDateTimeSpan span; + span.SetFromPeriod(param); + date -= span; + parameter = db.PrepareSQL(operatorString, date.GetAsDBDate().c_str()); + } + } + return parameter; +} + +std::string CDatabaseQueryRule::GetOperatorString(SEARCH_OPERATOR op) const +{ + std::string operatorString; + if (GetFieldType(m_field) != TEXTIN_FIELD) + { + // the comparison piece + switch (op) + { + case OPERATOR_CONTAINS: + operatorString = " LIKE '%%%s%%'"; + break; + case OPERATOR_DOES_NOT_CONTAIN: + operatorString = " LIKE '%%%s%%'"; + break; + case OPERATOR_EQUALS: + if (GetFieldType(m_field) == REAL_FIELD || GetFieldType(m_field) == NUMERIC_FIELD || + GetFieldType(m_field) == SECONDS_FIELD) + operatorString = " = %s"; + else + operatorString = " LIKE '%s'"; + break; + case OPERATOR_DOES_NOT_EQUAL: + if (GetFieldType(m_field) == REAL_FIELD || GetFieldType(m_field) == NUMERIC_FIELD || + GetFieldType(m_field) == SECONDS_FIELD) + operatorString = " != %s"; + else + operatorString = " LIKE '%s'"; + break; + case OPERATOR_STARTS_WITH: + operatorString = " LIKE '%s%%'"; + break; + case OPERATOR_ENDS_WITH: + operatorString = " LIKE '%%%s'"; + break; + case OPERATOR_AFTER: + case OPERATOR_GREATER_THAN: + case OPERATOR_IN_THE_LAST: + operatorString = " > "; + if (GetFieldType(m_field) == REAL_FIELD || GetFieldType(m_field) == NUMERIC_FIELD || + GetFieldType(m_field) == SECONDS_FIELD) + operatorString += "%s"; + else + operatorString += "'%s'"; + break; + case OPERATOR_BEFORE: + case OPERATOR_LESS_THAN: + case OPERATOR_NOT_IN_THE_LAST: + operatorString = " < "; + if (GetFieldType(m_field) == REAL_FIELD || GetFieldType(m_field) == NUMERIC_FIELD || + GetFieldType(m_field) == SECONDS_FIELD) + operatorString += "%s"; + else + operatorString += "'%s'"; + break; + case OPERATOR_TRUE: + operatorString = " = 1"; + break; + case OPERATOR_FALSE: + operatorString = " = 0"; + break; + default: + break; + } + } + return operatorString; +} + +std::string CDatabaseQueryRule::GetWhereClause(const CDatabase& db, + const std::string& strType) const +{ + SEARCH_OPERATOR op = GetOperator(strType); + + std::string operatorString = GetOperatorString(op); + std::string negate; + if (op == OPERATOR_DOES_NOT_CONTAIN || op == OPERATOR_FALSE || + (op == OPERATOR_DOES_NOT_EQUAL && GetFieldType(m_field) != REAL_FIELD && + GetFieldType(m_field) != NUMERIC_FIELD && GetFieldType(m_field) != SECONDS_FIELD)) + negate = " NOT "; + + // boolean operators don't have any values in m_parameter, they work on the operator + if (m_operator == OPERATOR_FALSE || m_operator == OPERATOR_TRUE) + return GetBooleanQuery(negate, strType); + + // Process boolean field with (not) EQUAL/CONTAINS "true"/"false" parameter too + if (GetFieldType(m_field) == BOOLEAN_FIELD && + (m_parameter[0] == "true" || m_parameter[0] == "false") && + (op == OPERATOR_CONTAINS || op == OPERATOR_EQUALS || op == OPERATOR_DOES_NOT_CONTAIN || + op == OPERATOR_DOES_NOT_EQUAL)) + { + if (m_parameter[0] == "false") + { + if (!negate.empty()) + negate.clear(); + else + negate = " NOT "; + } + return GetBooleanQuery(negate, strType); + } + + // The BETWEEN operator is handled special + if (op == OPERATOR_BETWEEN) + { + if (m_parameter.size() != 2) + return ""; + + FIELD_TYPE fieldType = GetFieldType(m_field); + if (fieldType == REAL_FIELD) + return db.PrepareSQL("%s BETWEEN %s AND %s", GetField(m_field, strType).c_str(), + m_parameter[0].c_str(), m_parameter[1].c_str()); + else if (fieldType == NUMERIC_FIELD) + return db.PrepareSQL("CAST(%s as DECIMAL(5,1)) BETWEEN %s AND %s", + GetField(m_field, strType).c_str(), m_parameter[0].c_str(), + m_parameter[1].c_str()); + else if (fieldType == SECONDS_FIELD) + return db.PrepareSQL("CAST(%s as INTEGER) BETWEEN %s AND %s", + GetField(m_field, strType).c_str(), m_parameter[0].c_str(), + m_parameter[1].c_str()); + else + return db.PrepareSQL("%s BETWEEN '%s' AND '%s'", GetField(m_field, strType).c_str(), + m_parameter[0].c_str(), m_parameter[1].c_str()); + } + + // now the query parameter + std::string wholeQuery; + for (std::vector<std::string>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); + ++it) + { + std::string query = '(' + FormatWhereClause(negate, operatorString, *it, db, strType) + ')'; + + if (it + 1 != m_parameter.end()) + { + if (negate.empty()) + query += " OR "; + else + query += " AND "; + } + + wholeQuery += query; + } + + return wholeQuery; +} + +std::string CDatabaseQueryRule::FormatWhereClause(const std::string& negate, + const std::string& oper, + const std::string& param, + const CDatabase& db, + const std::string& strType) const +{ + std::string parameter = FormatParameter(oper, param, db, strType); + + std::string query; + if (m_field != 0) + { + std::string fmt = "{}"; + if (GetFieldType(m_field) == NUMERIC_FIELD) + fmt = "CAST({} as DECIMAL(6,1))"; + else if (GetFieldType(m_field) == SECONDS_FIELD) + fmt = "CAST({} as INTEGER)"; + + query = StringUtils::Format(fmt, GetField(m_field, strType)); + query += negate + parameter; + + // special case for matching parameters in fields that might be either empty or NULL. + if ((param.empty() && negate.empty()) || (!param.empty() && !negate.empty())) + query += " OR " + GetField(m_field, strType) + " IS NULL"; + } + + if (query == negate + parameter) + query = "1"; + return query; +} + +void CDatabaseQueryRuleCombination::clear() +{ + m_combinations.clear(); + m_rules.clear(); + m_type = CombinationAnd; +} + +std::string CDatabaseQueryRuleCombination::GetWhereClause(const CDatabase& db, + const std::string& strType) const +{ + std::string rule; + + // translate the combinations into SQL + for (CDatabaseQueryRuleCombinations::const_iterator it = m_combinations.begin(); + it != m_combinations.end(); ++it) + { + if (it != m_combinations.begin()) + rule += m_type == CombinationAnd ? " AND " : " OR "; + rule += "(" + (*it)->GetWhereClause(db, strType) + ")"; + } + + // translate the rules into SQL + for (const auto& it : m_rules) + { + if (!rule.empty()) + rule += m_type == CombinationAnd ? " AND " : " OR "; + rule += "("; + std::string currentRule = it->GetWhereClause(db, strType); + // if we don't get a rule, we add '1' or '0' so the query is still valid and doesn't fail + if (currentRule.empty()) + currentRule = m_type == CombinationAnd ? "'1'" : "'0'"; + rule += currentRule; + rule += ")"; + } + + return rule; +} + +bool CDatabaseQueryRuleCombination::Load(const CVariant& obj, + const IDatabaseQueryRuleFactory* factory) +{ + if (!obj.isObject() && !obj.isArray()) + return false; + + CVariant child; + if (obj.isObject()) + { + if (obj.isMember("and") && obj["and"].isArray()) + { + m_type = CombinationAnd; + child = obj["and"]; + } + else if (obj.isMember("or") && obj["or"].isArray()) + { + m_type = CombinationOr; + child = obj["or"]; + } + else + return false; + } + else + child = obj; + + for (CVariant::const_iterator_array it = child.begin_array(); it != child.end_array(); ++it) + { + if (!it->isObject()) + continue; + + if (it->isMember("and") || it->isMember("or")) + { + std::shared_ptr<CDatabaseQueryRuleCombination> combo(factory->CreateCombination()); + if (combo && combo->Load(*it, factory)) + m_combinations.push_back(combo); + } + else + { + std::shared_ptr<CDatabaseQueryRule> rule(factory->CreateRule()); + if (rule && rule->Load(*it)) + m_rules.push_back(rule); + } + } + + return true; +} + +bool CDatabaseQueryRuleCombination::Save(TiXmlNode* parent) const +{ + for (const auto& it : m_rules) + it->Save(parent); + return true; +} + +bool CDatabaseQueryRuleCombination::Save(CVariant& obj) const +{ + if (!obj.isObject() || (m_combinations.empty() && m_rules.empty())) + return false; + + CVariant comboArray(CVariant::VariantTypeArray); + if (!m_combinations.empty()) + { + for (const auto& combo : m_combinations) + { + CVariant comboObj(CVariant::VariantTypeObject); + if (combo->Save(comboObj)) + comboArray.push_back(comboObj); + } + } + if (!m_rules.empty()) + { + for (const auto& rule : m_rules) + { + CVariant ruleObj(CVariant::VariantTypeObject); + if (rule->Save(ruleObj)) + comboArray.push_back(ruleObj); + } + } + + obj[TranslateCombinationType()] = comboArray; + + return true; +} + +std::string CDatabaseQueryRuleCombination::TranslateCombinationType() const +{ + return m_type == CombinationAnd ? "and" : "or"; +} diff --git a/xbmc/dbwrappers/DatabaseQuery.h b/xbmc/dbwrappers/DatabaseQuery.h new file mode 100644 index 0000000..a15f377 --- /dev/null +++ b/xbmc/dbwrappers/DatabaseQuery.h @@ -0,0 +1,150 @@ +/* + * Copyright (C) 2013-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#pragma once + +#include <memory> +#include <set> +#include <string> +#include <vector> + +#define DATABASEQUERY_RULE_VALUE_SEPARATOR " / " + +class CDatabase; +class CVariant; +class TiXmlNode; + +class CDatabaseQueryRule +{ +public: + CDatabaseQueryRule(); + virtual ~CDatabaseQueryRule() = default; + + enum SEARCH_OPERATOR + { + OPERATOR_START = 0, + OPERATOR_CONTAINS, + OPERATOR_DOES_NOT_CONTAIN, + OPERATOR_EQUALS, + OPERATOR_DOES_NOT_EQUAL, + OPERATOR_STARTS_WITH, + OPERATOR_ENDS_WITH, + OPERATOR_GREATER_THAN, + OPERATOR_LESS_THAN, + OPERATOR_AFTER, + OPERATOR_BEFORE, + OPERATOR_IN_THE_LAST, + OPERATOR_NOT_IN_THE_LAST, + OPERATOR_TRUE, + OPERATOR_FALSE, + OPERATOR_BETWEEN, + OPERATOR_END + }; + + enum FIELD_TYPE + { + TEXT_FIELD = 0, + REAL_FIELD, + NUMERIC_FIELD, + DATE_FIELD, + PLAYLIST_FIELD, + SECONDS_FIELD, + BOOLEAN_FIELD, + TEXTIN_FIELD + }; + + virtual bool Load(const TiXmlNode* node, const std::string& encoding = "UTF-8"); + virtual bool Load(const CVariant& obj); + virtual bool Save(TiXmlNode* parent) const; + virtual bool Save(CVariant& obj) const; + + static std::string GetLocalizedOperator(SEARCH_OPERATOR oper); + static void GetAvailableOperators(std::vector<std::string>& operatorList); + + std::string GetParameter() const; + void SetParameter(const std::string& value); + void SetParameter(const std::vector<std::string>& values); + + virtual std::string GetWhereClause(const CDatabase& db, const std::string& strType) const; + + int m_field; + SEARCH_OPERATOR m_operator; + std::vector<std::string> m_parameter; + +protected: + virtual std::string GetField(int field, const std::string& type) const = 0; + virtual FIELD_TYPE GetFieldType(int field) const = 0; + virtual int TranslateField(const char* field) const = 0; + virtual std::string TranslateField(int field) const = 0; + std::string ValidateParameter(const std::string& parameter) const; + virtual std::string FormatParameter(const std::string& negate, + const std::string& oper, + const CDatabase& db, + const std::string& type) const; + virtual std::string FormatWhereClause(const std::string& negate, + const std::string& oper, + const std::string& param, + const CDatabase& db, + const std::string& type) const; + virtual SEARCH_OPERATOR GetOperator(const std::string& type) const { return m_operator; } + virtual std::string GetOperatorString(SEARCH_OPERATOR op) const; + virtual std::string GetBooleanQuery(const std::string& negate, const std::string& strType) const + { + return ""; + } + + static SEARCH_OPERATOR TranslateOperator(const char* oper); + static std::string TranslateOperator(SEARCH_OPERATOR oper); +}; + +class CDatabaseQueryRuleCombination; + +typedef std::vector<std::shared_ptr<CDatabaseQueryRule>> CDatabaseQueryRules; +typedef std::vector<std::shared_ptr<CDatabaseQueryRuleCombination>> CDatabaseQueryRuleCombinations; + +class IDatabaseQueryRuleFactory +{ +public: + virtual ~IDatabaseQueryRuleFactory() = default; + virtual CDatabaseQueryRule* CreateRule() const = 0; + virtual CDatabaseQueryRuleCombination* CreateCombination() const = 0; +}; + +class CDatabaseQueryRuleCombination +{ +public: + virtual ~CDatabaseQueryRuleCombination() = default; + + typedef enum + { + CombinationOr = 0, + CombinationAnd + } Combination; + + void clear(); + virtual bool Load(const TiXmlNode* node, const std::string& encoding = "UTF-8") { return false; } + virtual bool Load(const CVariant& obj, const IDatabaseQueryRuleFactory* factory); + virtual bool Save(TiXmlNode* parent) const; + virtual bool Save(CVariant& obj) const; + + std::string GetWhereClause(const CDatabase& db, const std::string& strType) const; + std::string TranslateCombinationType() const; + + Combination GetType() const { return m_type; } + void SetType(Combination combination) { m_type = combination; } + + bool empty() const { return m_combinations.empty() && m_rules.empty(); } + +protected: + friend class CGUIDialogSmartPlaylistEditor; + friend class CGUIDialogMediaFilter; + + Combination m_type = CombinationAnd; + CDatabaseQueryRuleCombinations m_combinations; + CDatabaseQueryRules m_rules; +}; diff --git a/xbmc/dbwrappers/dataset.cpp b/xbmc/dbwrappers/dataset.cpp new file mode 100644 index 0000000..4b4acb4 --- /dev/null +++ b/xbmc/dbwrappers/dataset.cpp @@ -0,0 +1,625 @@ +/* + * Copyright (C) 2004, Leo Seib, Hannover + * + * Project: C++ Dynamic Library + * Module: Dataset abstraction layer realisation file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +#include "dataset.h" + +#include "utils/StringUtils.h" +#include "utils/log.h" + +#include <algorithm> +#include <cstring> + +#ifndef __GNUC__ +#pragma warning(disable : 4800) +#endif + +namespace dbiplus +{ +//************* Database implementation *************** + +Database::Database() + : error(), //S_NO_CONNECTION, + host(), + port(), + db(), + login(), + passwd(), + sequence_table("db_sequence") +{ + active = false; // No connection yet + compression = false; +} + +Database::~Database() +{ + disconnect(); // Disconnect if connected to database +} + +int Database::connectFull(const char* newHost, + const char* newPort, + const char* newDb, + const char* newLogin, + const char* newPasswd, + const char* newKey, + const char* newCert, + const char* newCA, + const char* newCApath, + const char* newCiphers, + bool newCompression) +{ + host = newHost; + port = newPort; + db = newDb; + login = newLogin; + passwd = newPasswd; + key = newKey; + cert = newCert; + ca = newCA; + capath = newCApath; + ciphers = newCiphers; + compression = newCompression; + return connect(true); +} + +std::string Database::prepare(const char* format, ...) +{ + va_list args; + va_start(args, format); + std::string result = vprepare(format, args); + va_end(args); + + return result; +} + +//************* Dataset implementation *************** + +Dataset::Dataset() : select_sql("") +{ + + db = NULL; + haveError = active = false; + frecno = 0; + fbof = feof = true; + autocommit = true; + + fields_object = new Fields(); + + edit_object = new Fields(); +} + +Dataset::Dataset(Database* newDb) : select_sql("") +{ + + db = newDb; + haveError = active = false; + frecno = 0; + fbof = feof = true; + autocommit = true; + + fields_object = new Fields(); + + edit_object = new Fields(); +} + +Dataset::~Dataset() +{ + update_sql.clear(); + insert_sql.clear(); + delete_sql.clear(); + + delete fields_object; + delete edit_object; +} + +void Dataset::setSqlParams(sqlType t, const char* sqlFrmt, ...) +{ + va_list ap; + char sqlCmd[DB_BUFF_MAX + 1]; + + va_start(ap, sqlFrmt); +#ifndef TARGET_POSIX + _vsnprintf(sqlCmd, DB_BUFF_MAX - 1, sqlFrmt, ap); +#else + vsnprintf(sqlCmd, DB_BUFF_MAX - 1, sqlFrmt, ap); +#endif + va_end(ap); + + switch (t) + { + case sqlSelect: + set_select_sql(sqlCmd); + break; + case sqlUpdate: + add_update_sql(sqlCmd); + break; + case sqlInsert: + add_insert_sql(sqlCmd); + break; + case sqlDelete: + add_delete_sql(sqlCmd); + break; + case sqlExec: + sql = sqlCmd; + break; + } +} + +void Dataset::set_select_sql(const char* sel_sql) +{ + select_sql = sel_sql; +} + +void Dataset::set_select_sql(const std::string& sel_sql) +{ + select_sql = sel_sql; +} + +void Dataset::parse_sql(std::string& sql) +{ + std::string fpattern, by_what; + for (unsigned int i = 0; i < fields_object->size(); i++) + { + fpattern = ":OLD_" + (*fields_object)[i].props.name; + by_what = "'" + (*fields_object)[i].val.get_asString() + "'"; + int idx = 0; + int next_idx = 0; + while ((idx = sql.find(fpattern, next_idx)) >= 0) + { + next_idx = idx + fpattern.size(); + if (sql.length() > ((unsigned int)next_idx)) + if (isalnum(sql[next_idx]) || sql[next_idx] == '_') + { + continue; + } + sql.replace(idx, fpattern.size(), by_what); + } //while + } //for + + for (unsigned int i = 0; i < edit_object->size(); i++) + { + fpattern = ":NEW_" + (*edit_object)[i].props.name; + by_what = "'" + (*edit_object)[i].val.get_asString() + "'"; + int idx = 0; + int next_idx = 0; + while ((idx = sql.find(fpattern, next_idx)) >= 0) + { + next_idx = idx + fpattern.size(); + if (sql.length() > ((unsigned int)next_idx)) + if (isalnum(sql[next_idx]) || sql[next_idx] == '_') + { + continue; + } + sql.replace(idx, fpattern.size(), by_what); + } //while + } //for +} + +void Dataset::close(void) +{ + haveError = false; + frecno = 0; + fbof = feof = true; + active = false; + + name2indexMap.clear(); +} + +bool Dataset::seek(int pos) +{ + frecno = (pos < num_rows() - 1) ? pos : num_rows() - 1; + frecno = (frecno < 0) ? 0 : frecno; + fbof = feof = (num_rows() == 0) ? true : false; + return ((bool)frecno); +} + +void Dataset::refresh() +{ + int row = frecno; + if ((row != 0) && active) + { + close(); + open(); + seek(row); + } + else + open(); +} + +void Dataset::first() +{ + if (ds_state == dsSelect) + { + frecno = 0; + feof = fbof = (num_rows() > 0) ? false : true; + } +} + +void Dataset::next() +{ + if (ds_state == dsSelect) + { + fbof = false; + if (frecno < num_rows() - 1) + { + frecno++; + feof = false; + } + else + feof = true; + if (num_rows() <= 0) + fbof = feof = true; + } +} + +void Dataset::prev() +{ + if (ds_state == dsSelect) + { + feof = false; + if (frecno) + { + frecno--; + fbof = false; + } + else + fbof = true; + if (num_rows() <= 0) + fbof = feof = true; + } +} + +void Dataset::last() +{ + if (ds_state == dsSelect) + { + frecno = (num_rows() > 0) ? num_rows() - 1 : 0; + feof = fbof = (num_rows() > 0) ? false : true; + } +} + +bool Dataset::goto_rec(int pos) +{ + if (ds_state == dsSelect) + { + return seek(pos - 1); + } + return false; +} + +void Dataset::insert() +{ + edit_object->resize(field_count()); + for (int i = 0; i < field_count(); i++) + { + (*fields_object)[i].val = ""; + (*edit_object)[i].val = ""; + (*edit_object)[i].props = (*fields_object)[i].props; + } + ds_state = dsInsert; +} + +void Dataset::edit() +{ + if (ds_state != dsSelect) + { + throw DbErrors("Editing is possible only when query exists!"); + } + edit_object->resize(field_count()); + for (unsigned int i = 0; i < fields_object->size(); i++) + { + (*edit_object)[i].props = (*fields_object)[i].props; + (*edit_object)[i].val = (*fields_object)[i].val; + } + ds_state = dsEdit; +} + +void Dataset::post() +{ + if (ds_state == dsInsert) + make_insert(); + else if (ds_state == dsEdit) + make_edit(); +} + +void Dataset::del() +{ + ds_state = dsDelete; +} + +void Dataset::deletion() +{ + if (ds_state == dsDelete) + make_deletion(); +} + +bool Dataset::set_field_value(const char* f_name, const field_value& value) +{ + if ((ds_state == dsInsert) || (ds_state == dsEdit)) + { + const int idx = fieldIndex(f_name); + if (idx >= 0) + { + (*edit_object)[idx].val = value; + return true; + } + throw DbErrors("Field not found: %s", f_name); + } + throw DbErrors("Not in Insert or Edit state"); + // return false; +} + +const field_value Dataset::get_field_value(const char* f_name) +{ + if (ds_state != dsInactive) + { + if (ds_state == dsEdit || ds_state == dsInsert) + { + const int idx = fieldIndex(f_name); + if (idx >= 0) + return (*edit_object)[idx].val; + + throw DbErrors("Field not found: %s", f_name); + } + else + { + int idx = fieldIndex(f_name); + if (idx < 0) + { + const char* name = strstr(f_name, "."); + if (name) + name++; + + if (name) + idx = fieldIndex(name); + } + + if (idx >= 0) + return (*fields_object)[idx].val; + + throw DbErrors("Field not found: %s", f_name); + } + } + throw DbErrors("Dataset state is Inactive"); +} + +const field_value Dataset::get_field_value(int index) +{ + if (ds_state != dsInactive) + { + if (ds_state == dsEdit || ds_state == dsInsert) + { + if (index < 0 || index >= field_count()) + throw DbErrors("Field index not found: %d", index); + + return (*edit_object)[index].val; + } + else + { + if (index < 0 || index >= field_count()) + throw DbErrors("Field index not found: %d", index); + + return (*fields_object)[index].val; + } + } + throw DbErrors("Dataset state is Inactive"); +} + +const sql_record* Dataset::get_sql_record() +{ + if (result.records.empty() || frecno >= (int)result.records.size()) + return NULL; + + return result.records[frecno]; +} + +const field_value Dataset::f_old(const char* f_name) +{ + if (ds_state != dsInactive) + for (int unsigned i = 0; i < fields_object->size(); i++) + if ((*fields_object)[i].props.name == f_name) + return (*fields_object)[i].val; + field_value fv; + return fv; +} + +void Dataset::setParamList(const ParamList& params) +{ + plist = params; +} + +bool Dataset::locate() +{ + bool result; + if (plist.empty()) + return false; + + std::map<std::string, field_value>::const_iterator i; + first(); + while (!eof()) + { + result = true; + for (i = plist.begin(); i != plist.end(); ++i) + if (fv(i->first.c_str()).get_asString() == i->second.get_asString()) + { + continue; + } + else + { + result = false; + break; + } + if (result) + { + return result; + } + next(); + } + return false; +} + +bool Dataset::locate(const ParamList& params) +{ + plist = params; + return locate(); +} + +bool Dataset::findNext(void) +{ + bool result; + if (plist.empty()) + return false; + + std::map<std::string, field_value>::const_iterator i; + while (!eof()) + { + result = true; + for (i = plist.begin(); i != plist.end(); ++i) + if (fv(i->first.c_str()).get_asString() == i->second.get_asString()) + { + continue; + } + else + { + result = false; + break; + } + if (result) + { + return result; + } + next(); + } + return false; +} + +void Dataset::add_update_sql(const char* upd_sql) +{ + std::string s = upd_sql; + update_sql.push_back(s); +} + +void Dataset::add_update_sql(const std::string& upd_sql) +{ + update_sql.push_back(upd_sql); +} + +void Dataset::add_insert_sql(const char* ins_sql) +{ + std::string s = ins_sql; + insert_sql.push_back(s); +} + +void Dataset::add_insert_sql(const std::string& ins_sql) +{ + insert_sql.push_back(ins_sql); +} + +void Dataset::add_delete_sql(const char* del_sql) +{ + std::string s = del_sql; + delete_sql.push_back(s); +} + +void Dataset::add_delete_sql(const std::string& del_sql) +{ + delete_sql.push_back(del_sql); +} + +void Dataset::clear_update_sql() +{ + update_sql.clear(); +} + +void Dataset::clear_insert_sql() +{ + insert_sql.clear(); +} + +void Dataset::clear_delete_sql() +{ + delete_sql.clear(); +} + +size_t Dataset::insert_sql_count() +{ + return insert_sql.size(); +} + +size_t Dataset::delete_sql_count() +{ + return delete_sql.size(); +} + +int Dataset::field_count() +{ + return fields_object->size(); +} +int Dataset::fieldCount() +{ + return fields_object->size(); +} + +const char* Dataset::fieldName(int n) +{ + if (n < field_count() && n >= 0) + return (*fields_object)[n].props.name.c_str(); + else + return NULL; +} + +char* Dataset::str_toLower(char* s) +{ + for (char* p = s; *p; p++) + *p = std::tolower(*p); + + return s; +} + +int Dataset::fieldIndex(const char* fn) +{ + std::string name(fn); + const auto it = name2indexMap.find(str_toLower(name.data())); + if (it != name2indexMap.end()) + return (*it).second; + else + return -1; +} + +//************* DbErrors implementation *************** + +DbErrors::DbErrors() : msg_("Unknown Database Error") +{ +} + +DbErrors::DbErrors(const char* msg, ...) +{ + va_list vl; + va_start(vl, msg); + char buf[DB_BUFF_MAX] = ""; +#ifndef TARGET_POSIX + _vsnprintf(buf, DB_BUFF_MAX - 1, msg, vl); +#else + vsnprintf(buf, DB_BUFF_MAX - 1, msg, vl); +#endif + va_end(vl); + msg_ = "SQL: "; + msg_ += buf; + + CLog::Log(LOGERROR, "{}", msg_); +} + +const char* DbErrors::getMsg() +{ + return msg_.c_str(); +} + +} // namespace dbiplus diff --git a/xbmc/dbwrappers/dataset.h b/xbmc/dbwrappers/dataset.h new file mode 100644 index 0000000..5e420b1 --- /dev/null +++ b/xbmc/dbwrappers/dataset.h @@ -0,0 +1,470 @@ +/* + * Copyright (C) 2002, Leo Seib, Hannover + * + * Project:Dataset C++ Dynamic Library + * Module: Dataset abstraction layer header file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +#pragma once + +#include "qry_dat.h" + +#include <cstdio> +#include <list> +#include <map> +#include <stdarg.h> +#include <string> +#include <unordered_map> + +namespace dbiplus +{ +class Dataset; // forward declaration of class Dataset + +#define S_NO_CONNECTION "No active connection"; + +#define DB_BUFF_MAX 8 * 1024 // Maximum buffer's capacity + +#define DB_CONNECTION_NONE 0 +#define DB_CONNECTION_OK 1 +#define DB_CONNECTION_BAD 2 + +#define DB_COMMAND_OK 0 // OK - command executed +#define DB_EMPTY_QUERY 1 // Query didn't return tuples +#define DB_TUPLES_OK 2 // Query returned tuples +#define DB_ERROR 5 +#define DB_BAD_RESPONSE 6 +#define DB_UNEXPECTED 7 // This shouldn't ever happen +#define DB_UNEXPECTED_RESULT -1 //For integer functions + +/******************* Class Database definition ******************** + + represents connection with database server; + +******************************************************************/ +class Database +{ +protected: + bool active; + bool compression; + std::string error, // Error description + host, port, db, login, passwd, //Login info + sequence_table, //Sequence table for nextid + default_charset, //Default character set + key, cert, ca, capath, ciphers; //SSL - Encryption info + +public: + /* constructor */ + Database(); + /* destructor */ + virtual ~Database(); + virtual Dataset* CreateDataset() const = 0; + /* sets a new host name */ + virtual void setHostName(const char* newHost) { host = newHost; } + /* gets a host name */ + const char* getHostName(void) const { return host.c_str(); } + /* sets a new port */ + void setPort(const char* newPort) { port = newPort; } + /* gets a port */ + const char* getPort(void) const { return port.c_str(); } + /* sets a new database name */ + virtual void setDatabase(const char* newDb) { db = newDb; } + /* gets a database name */ + const char* getDatabase(void) const { return db.c_str(); } + /* sets a new login to database */ + void setLogin(const char* newLogin) { login = newLogin; } + /* gets a login */ + const char* getLogin(void) const { return login.c_str(); } + /* sets a password */ + void setPasswd(const char* newPasswd) { passwd = newPasswd; } + /* gets a password */ + const char* getPasswd(void) const { return passwd.c_str(); } + /* active status is OK state */ + virtual bool isActive(void) const { return active; } + /* Set new name of sequence table */ + void setSequenceTable(const char* new_seq_table) { sequence_table = new_seq_table; } + /* Get name of sequence table */ + const char* getSequenceTable(void) { return sequence_table.c_str(); } + /* Get the default character set */ + const char* getDefaultCharset(void) { return default_charset.c_str(); } + /* Sets configuration */ + virtual void setConfig(const char* newKey, + const char* newCert, + const char* newCA, + const char* newCApath, + const char* newCiphers, + bool newCompression) + { + key = newKey; + cert = newCert; + ca = newCA; + capath = newCApath; + ciphers = newCiphers; + compression = newCompression; + } + + /* virtual methods that must be overloaded in derived classes */ + + virtual int init(void) { return DB_COMMAND_OK; } + virtual int status(void) { return DB_CONNECTION_NONE; } + virtual int setErr(int err_code, const char* qry) = 0; + virtual const char* getErrorMsg(void) { return error.c_str(); } + + virtual int connect(bool create) { return DB_COMMAND_OK; } + virtual int connectFull(const char* newDb, + const char* newHost = NULL, + const char* newLogin = NULL, + const char* newPasswd = NULL, + const char* newPort = NULL, + const char* newKey = NULL, + const char* newCert = NULL, + const char* newCA = NULL, + const char* newCApath = NULL, + const char* newCiphers = NULL, + bool newCompression = false); + virtual void disconnect(void) { active = false; } + virtual int reset(void) { return DB_COMMAND_OK; } + virtual int create(void) { return DB_COMMAND_OK; } + virtual int drop(void) { return DB_COMMAND_OK; } + virtual long nextid(const char* seq_name) = 0; + + /* \brief copy database */ + virtual int copy(const char* new_name) { return -1; } + + /* \brief drop all extra analytics from database */ + virtual int drop_analytics(void) { return -1; } + + virtual bool exists(void) { return false; } + + /* virtual methods for transaction */ + + virtual void start_transaction() {} + virtual void commit_transaction() {} + virtual void rollback_transaction() {} + + /* virtual methods for formatting */ + + /*! \brief Prepare a SQL statement for execution or querying using C printf nomenclature. + \param format - C printf compliant format string + \param ... - optional comma separated list of variables for substitution in format string placeholders. + \return escaped and formatted string. + */ + virtual std::string prepare(const char* format, ...); + + /*! \brief Prepare a SQL statement for execution or querying using C printf nomenclature + \param format - C printf compliant format string + \param args - va_list of variables for substitution in format string placeholders. + \return escaped and formatted string. + */ + virtual std::string vprepare(const char* format, va_list args) = 0; + + virtual bool in_transaction() { return false; } +}; + +/******************* Class Dataset definition ********************* + + global abstraction for using Databases + +******************************************************************/ + +// define Dataset States type +enum dsStates +{ + dsSelect, + dsInsert, + dsEdit, + dsUpdate, + dsDelete, + dsInactive +}; +enum sqlType +{ + sqlSelect, + sqlUpdate, + sqlInsert, + sqlDelete, + sqlExec +}; + +typedef std::list<std::string> StringList; +typedef std::map<std::string, field_value> ParamList; + +class Dataset +{ +protected: + /* char *Host = ""; //WORK_HOST; + char *Database = ""; //WORK_DATABASE; + char *User = ""; //WORK_USER; + char *Password = ""; //WORK_PASSWORD; +*/ + + Database* db; // info about db connection + dsStates ds_state; // current state + Fields *fields_object, *edit_object; + std::unordered_map<std::string, unsigned int> + name2indexMap; // Lower case field name -> database index + + /* query results*/ + result_set result; + result_set exec_res; + bool autorefresh; + + bool active; // Is Query Opened? + bool haveError; + int frecno; // number of current row bei bewegung + std::string sql; + + ParamList plist; // Paramlist for locate + bool fbof, feof; + bool autocommit; // for transactions + + /* Variables to store SQL statements */ + std::string empty_sql; // Executed when result set is empty + std::string select_sql; // May be only single string variable + + StringList update_sql; // May be an array in complex queries + /* Field values for updating must has prefix :NEW_ and :OLD_ and field name + Example: + update wt_story set idobject set idobject=:NEW_idobject,body=:NEW_body + where idobject=:OLD_idobject + Essentially fields idobject and body must present in the + result set (select_sql statement) */ + + StringList insert_sql; // May be an array in complex queries + /* Field values for inserting must has prefix :NEW_ and field name + Example: + insert into wt_story (idobject, body) values (:NEW_idobject, :NEW_body) + Essentially fields idobject and body must present in the + result set (select_sql statement) */ + + StringList delete_sql; // May be an array in complex queries + /* Field values for deleing must has prefix :OLD_ and field name + Example: + delete from wt_story where idobject=:OLD_idobject + Essentially field idobject must present in the + result set (select_sql statement) */ + + /* Arrays for searching */ + // StringList names, values; + + /* Makes direct inserts into database via mysql_query function */ + virtual void make_insert() = 0; + /* Edit SQL */ + virtual void make_edit() = 0; + /* Delete SQL */ + virtual void make_deletion() = 0; + + /* This function works only with MySQL database + Filling the fields information from select statement */ + virtual void fill_fields(void) = 0; + + /* Parse Sql - replacing fields with prefixes :OLD_ and :NEW_ with current values of OLD or NEW field. */ + void parse_sql(std::string& sql); + + /* Returns old field value (for :OLD) */ + virtual const field_value f_old(const char* f); + + /* fast string tolower helper */ + char* str_toLower(char* s); + +public: + /* constructor */ + Dataset(); + explicit Dataset(Database* newDb); + + /* destructor */ + virtual ~Dataset(); + + /* sets a new value of connection to database */ + void setDatabase(Database* newDb) { db = newDb; } + /* retrieves a database which connected */ + Database* getDatabase(void) { return db; } + + /* sets a new query string to database server */ + void setExecSql(const char* newSql) { sql = newSql; } + /* retrieves a query string */ + const char* getExecSql(void) { return sql.c_str(); } + + /* status active is OK query */ + virtual bool isActive(void) { return active; } + + virtual void setSqlParams(sqlType t, const char* sqlFrmt, ...); + + /* error handling */ + // virtual void halt(const char *msg); + + /* last inserted id */ + virtual int64_t lastinsertid() = 0; + /* sequence numbers */ + virtual long nextid(const char* seq_name) = 0; + /* sequence numbers */ + virtual int num_rows() = 0; + + /* Open SQL query */ + virtual void open(const std::string& sql) = 0; + virtual void open() = 0; + /* func. executes a query without results to return */ + virtual int exec(const std::string& sql) = 0; + virtual int exec() = 0; + virtual const void* getExecRes() = 0; + /* as open, but with our query exec Sql */ + virtual bool query(const std::string& sql) = 0; + /* Close SQL Query*/ + virtual void close(); + /* This function looks for field Field_name with value equal Field_value + Returns true if found (position of dataset is set to founded position) + and false another way (position is not changed). */ + // virtual bool lookup(char *field_name, char*field_value); + /* Refresh dataset (reopen it and set the same cursor position) */ + virtual void refresh(); + + /*! \brief Drop an index from the database table, provided it exists. + \param table - name of the table the index to be dropped is associated with + \param index - name of the index to be dropped + \return true when the index is guaranteed to no longer exist in the database. + */ + virtual bool dropIndex(const char* table, const char* index) { return false; } + + /* Go to record No (starting with 0) */ + virtual bool seek(int pos = 0); + /* Go to record No (starting with 1) */ + virtual bool goto_rec(int pos = 1); + /* Go to the first record in dataset */ + virtual void first(); + /* Go to next record in dataset */ + virtual void next(); + /* Go to previous record */ + virtual void prev(); + /* Go to last record in dataset */ + virtual void last(); + + /* Check for Ending dataset */ + virtual bool eof(void) { return feof; } + /* Check for Beginning dataset */ + virtual bool bof(void) { return fbof; } + + /* Start the insert mode */ + virtual void insert(); + /* Start the insert mode (alias for insert() function) */ + virtual void append() { insert(); } + /* Start the edit mode */ + virtual void edit(); + /* Start the delete mode */ + virtual void del(); + + /* Add changes, that were made during insert or edit states of dataset into the database */ + virtual void post(); + /* Delete statements from database */ + virtual void deletion(); + /* Cancel changes, made in insert or edit states of dataset */ + virtual void cancel() {} + /* interrupt any pending database operation */ + virtual void interrupt() {} + + virtual void setParamList(const ParamList& params); + virtual bool locate(); + virtual bool locate(const ParamList& params); + virtual bool findNext(); + + /* func. retrieves a number of fields */ + /* Number of fields in a record */ + virtual int field_count(); + virtual int fieldCount(); + /* func. retrieves a field name with 'n' index */ + virtual const char* fieldName(int n); + /* func. retrieves a field index with 'fn' field name,return -1 when field name not found */ + virtual int fieldIndex(const char* fn); + + /* Set field value */ + virtual bool set_field_value(const char* f_name, const field_value& value); + /* alias for set_field_value */ + virtual bool sf(const char* f, const field_value& v) { return set_field_value(f, v); } + + /* Return field name by it index */ + // virtual char *field_name(int f_index) { return field_by_index(f_index)->get_field_name(); } + + /* Getting value of field for current record */ + virtual const field_value get_field_value(const char* f_name); + virtual const field_value get_field_value(int index); + /* Alias to get_field_value */ + const field_value fv(const char* f) { return get_field_value(f); } + const field_value fv(int index) { return get_field_value(index); } + + /* ------------ for transaction ------------------- */ + void set_autocommit(bool v) { autocommit = v; } + bool get_autocommit() { return autocommit; } + + /* ----------------- for debug -------------------- */ + Fields* get_fields_object() { return fields_object; } + Fields* get_edit_object() { return edit_object; } + + /* --------------- for fast access ---------------- */ + const result_set& get_result_set() { return result; } + const sql_record* get_sql_record(); + +private: + Dataset(const Dataset&) = delete; + Dataset& operator=(const Dataset&) = delete; + + /* Get the column index from a string field_value request */ + bool get_index_map_entry(const char* f_name); + + void set_ds_state(dsStates new_state) { ds_state = new_state; } + +public: + /* return ds_state value */ + dsStates get_state() { return ds_state; } + + /*add a new value to select_sql*/ + void set_select_sql(const char* sel_sql); + void set_select_sql(const std::string& select_sql); + /*add a new value to update_sql*/ + void add_update_sql(const char* upd_sql); + void add_update_sql(const std::string& upd_sql); + /*add a new value to insert_sql*/ + void add_insert_sql(const char* ins_sql); + void add_insert_sql(const std::string& ins_sql); + /*add a new value to delete_sql*/ + void add_delete_sql(const char* del_sql); + void add_delete_sql(const std::string& del_sql); + + /*clear update_sql*/ + void clear_update_sql(); + /*clear insert_sql*/ + void clear_insert_sql(); + /*clear delete_sql*/ + void clear_delete_sql(); + + /* size of insert_sql*/ + size_t insert_sql_count(); + /* size of delete_sql*/ + size_t delete_sql_count(); + + /*get value of select_sql*/ + const char* get_select_sql(); +}; + +/******************** Class DbErrors definition ********************* + + error handling + +******************************************************************/ +class DbErrors +{ + +public: + /* constructor */ + DbErrors(); + DbErrors(const char* msg, ...); + + const char* getMsg(); + +private: + std::string msg_; +}; + +} // namespace dbiplus diff --git a/xbmc/dbwrappers/mysqldataset.cpp b/xbmc/dbwrappers/mysqldataset.cpp new file mode 100644 index 0000000..a1a2d9d --- /dev/null +++ b/xbmc/dbwrappers/mysqldataset.cpp @@ -0,0 +1,2099 @@ +/* + * Copyright (C) 2005-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#include "mysqldataset.h" + +#include "Util.h" +#include "network/DNSNameCache.h" +#include "network/WakeOnAccess.h" +#include "utils/StringUtils.h" +#include "utils/log.h" + +#include <algorithm> +#include <array> +#include <iostream> +#include <set> +#include <string> +#ifdef HAS_MYSQL +#include <mysql/errmsg.h> +#elif defined(HAS_MARIADB) +#include <mariadb/errmsg.h> +#endif + +#ifdef TARGET_POSIX +#include "platform/posix/ConvUtils.h" +#endif + +#define MYSQL_OK 0 +#define ER_BAD_DB_ERROR 1049 + +namespace dbiplus +{ + +//************* MysqlDatabase implementation *************** + +MysqlDatabase::MysqlDatabase() +{ + + active = false; + _in_transaction = false; // for transaction + + error = "Unknown database error"; //S_NO_CONNECTION; + host = "localhost"; + port = "3306"; + db = "mysql"; + login = "root"; + passwd = "null"; + conn = NULL; + default_charset = ""; +} + +MysqlDatabase::~MysqlDatabase() +{ + disconnect(); +} + +Dataset* MysqlDatabase::CreateDataset() const +{ + return new MysqlDataset(const_cast<MysqlDatabase*>(this)); +} + +int MysqlDatabase::status(void) +{ + if (active == false) + return DB_CONNECTION_NONE; + return DB_CONNECTION_OK; +} + +int MysqlDatabase::setErr(int err_code, const char* qry) +{ + switch (err_code) + { + case MYSQL_OK: + error = "Successful result"; + break; + case CR_COMMANDS_OUT_OF_SYNC: + error = "Commands were executed in an improper order"; + break; + case CR_SERVER_GONE_ERROR: + error = "The MySQL server has gone away"; + break; + case CR_SERVER_LOST: + error = "The connection to the server was lost during this query"; + break; + case CR_UNKNOWN_ERROR: + error = "An unknown error occurred"; + break; + case 1146: /* ER_NO_SUCH_TABLE */ + error = "The table does not exist"; + break; + default: + char err[256]; + snprintf(err, 256, "Undefined MySQL error: Code (%d)", err_code); + error = err; + } + error = "[" + db + "] " + error; + error += "\nQuery: "; + error += qry; + error += "\n"; + return err_code; +} + +const char* MysqlDatabase::getErrorMsg() +{ + return error.c_str(); +} + +void MysqlDatabase::configure_connection() +{ + char sqlcmd[512]; + int ret; + + // MySQL 5.7.5+: See #8393 + strcpy(sqlcmd, + "SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))"); + if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) != MYSQL_OK) + throw DbErrors("Can't disable sql_mode ONLY_FULL_GROUP_BY: '%s' (%d)", db.c_str(), ret); + + // MySQL 5.7.6+: See #8393. Non-fatal if error, as not supported by MySQL 5.0.x + strcpy(sqlcmd, "SELECT @@SESSION.optimizer_switch"); + if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) == MYSQL_OK) + { + MYSQL_RES* res = mysql_store_result(conn); + MYSQL_ROW row; + + if (res) + { + if ((row = mysql_fetch_row(res)) != NULL) + { + std::string column = row[0]; + std::vector<std::string> split = StringUtils::Split(column, ','); + + for (std::string& itIn : split) + { + if (StringUtils::Trim(itIn) == "derived_merge=on") + { + strcpy(sqlcmd, "SET SESSION optimizer_switch = 'derived_merge=off'"); + if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) != MYSQL_OK) + throw DbErrors("Can't set optimizer_switch = '%s': '%s' (%d)", + StringUtils::Trim(itIn).c_str(), db.c_str(), ret); + break; + } + } + } + mysql_free_result(res); + } + } + else + CLog::Log(LOGWARNING, "Unable to query optimizer_switch: '{}' ({})", db, ret); +} + +int MysqlDatabase::connect(bool create_new) +{ + if (host.empty() || db.empty()) + return DB_CONNECTION_NONE; + + std::string resolvedHost; + if (!StringUtils::EqualsNoCase(host, "localhost") && CDNSNameCache::Lookup(host, resolvedHost)) + { + CLog::Log(LOGDEBUG, "{} replacing configured host {} with resolved host {}", __FUNCTION__, host, + resolvedHost); + host = resolvedHost; + } + + try + { + disconnect(); + + if (conn == NULL) + { + conn = mysql_init(conn); + mysql_ssl_set(conn, key.empty() ? NULL : key.c_str(), cert.empty() ? NULL : cert.c_str(), + ca.empty() ? NULL : ca.c_str(), capath.empty() ? NULL : capath.c_str(), + ciphers.empty() ? NULL : ciphers.c_str()); + } + + if (!CWakeOnAccess::GetInstance().WakeUpHost(host, "MySQL : " + db)) + return DB_CONNECTION_NONE; + + // establish connection with just user credentials + if (mysql_real_connect(conn, host.c_str(), login.c_str(), passwd.c_str(), NULL, + atoi(port.c_str()), NULL, compression ? CLIENT_COMPRESS : 0) != NULL) + { + static bool showed_ver_info = false; + if (!showed_ver_info) + { + std::string version_string = mysql_get_server_info(conn); + CLog::Log(LOGINFO, "MYSQL: Connected to version {}", version_string); + showed_ver_info = true; + unsigned long version = mysql_get_server_version(conn); + // Minimum for MySQL: 5.6 (5.5 is EOL) + unsigned long min_version = 50600; + if (version_string.find("MariaDB") != std::string::npos) + { + // Minimum for MariaDB: 5.5 (still supported) + min_version = 50500; + } + + if (version < min_version) + { + CLog::Log( + LOGWARNING, + "MYSQL: Your database server version {} is very old and might not be supported in " + "future Kodi versions. Please consider upgrading to MySQL 5.7 or MariaDB 10.2.", + version_string); + } + } + + // disable mysql autocommit since we handle it + //mysql_autocommit(conn, false); + + // enforce utf8 charset usage + default_charset = mysql_character_set_name(conn); + if (mysql_set_character_set(conn, "utf8")) // returns 0 on success + { + CLog::Log(LOGERROR, "Unable to set utf8 charset: {} [{}]({})", db, mysql_errno(conn), + mysql_error(conn)); + } + + configure_connection(); + + // check existence + if (exists()) + { + // nothing to see here + } + else if (create_new) + { + char sqlcmd[512]; + int ret; + + snprintf(sqlcmd, sizeof(sqlcmd), + "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", db.c_str()); + if ((ret = query_with_reconnect(sqlcmd)) != MYSQL_OK) + { + throw DbErrors("Can't create new database: '%s' (%d)", db.c_str(), ret); + } + } + + if (mysql_select_db(conn, db.c_str()) == 0) + { + active = true; + return DB_CONNECTION_OK; + } + } + + // if we failed above, either credentials were incorrect or the database didn't exist + if (mysql_errno(conn) == ER_BAD_DB_ERROR && create_new) + { + + if (create() == MYSQL_OK) + { + active = true; + + return DB_CONNECTION_OK; + } + } + + CLog::Log(LOGERROR, "Unable to open database: {} [{}]({})", db, mysql_errno(conn), + mysql_error(conn)); + + return DB_CONNECTION_NONE; + } + catch (...) + { + CLog::Log(LOGERROR, "Unable to open database: {} ({})", db, GetLastError()); + } + return DB_CONNECTION_NONE; +} + +void MysqlDatabase::disconnect(void) +{ + if (conn != NULL) + { + mysql_close(conn); + conn = NULL; + } + + active = false; +} + +int MysqlDatabase::create() +{ + return connect(true); +} + +int MysqlDatabase::drop() +{ + if (!active) + throw DbErrors("Can't drop database: no active connection..."); + char sqlcmd[512]; + int ret; + snprintf(sqlcmd, sizeof(sqlcmd), "DROP DATABASE `%s`", db.c_str()); + if ((ret = query_with_reconnect(sqlcmd)) != MYSQL_OK) + { + throw DbErrors("Can't drop database: '%s' (%d)", db.c_str(), ret); + } + disconnect(); + return DB_COMMAND_OK; +} + +int MysqlDatabase::copy(const char* backup_name) +{ + if (!active || conn == NULL) + throw DbErrors("Can't copy database: no active connection..."); + + char sql[4096]; + int ret; + + // ensure we're connected to the db we are about to copy + if ((ret = mysql_select_db(conn, db.c_str())) != MYSQL_OK) + throw DbErrors("Can't connect to source database: '%s'", db.c_str()); + + // grab a list of base tables only (no views) + sprintf(sql, "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + throw DbErrors("Can't determine base tables for copy."); + + // get list of all tables from old DB + MYSQL_RES* res = mysql_store_result(conn); + + if (res) + { + if (mysql_num_rows(res) == 0) + { + mysql_free_result(res); + throw DbErrors("The source database was unexpectedly empty."); + } + + // create the new database + snprintf(sql, sizeof(sql), "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", + backup_name); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't create database for copy: '%s' (%d)", db.c_str(), ret); + } + + MYSQL_ROW row; + + // duplicate each table from old db to new db + while ((row = mysql_fetch_row(res)) != NULL) + { + // copy the table definition + snprintf(sql, sizeof(sql), "CREATE TABLE `%s`.%s LIKE %s", backup_name, row[0], row[0]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't copy schema for table '%s'\nError: %d", row[0], ret); + } + + // copy the table data + snprintf(sql, sizeof(sql), "INSERT INTO `%s`.%s SELECT * FROM %s", backup_name, row[0], + row[0]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't copy data for table '%s'\nError: %d", row[0], ret); + } + } + mysql_free_result(res); + + // we don't recreate views, indices, or triggers on copy + // as we'll be dropping and recreating them anyway + } + + return 1; +} + +int MysqlDatabase::drop_analytics(void) +{ + if (!active || conn == NULL) + throw DbErrors("Can't clean database: no active connection..."); + + char sql[4096]; + int ret; + + // ensure we're connected to the db we are about to clean from stuff + if ((ret = mysql_select_db(conn, db.c_str())) != MYSQL_OK) + throw DbErrors("Can't connect to database: '%s'", db.c_str()); + + // getting a list of indexes in the database + snprintf(sql, sizeof(sql), + "SELECT DISTINCT table_name, index_name " + "FROM information_schema.statistics " + "WHERE index_name != 'PRIMARY' AND table_schema = '%s'", + db.c_str()); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + throw DbErrors("Can't determine list of indexes to drop."); + + // we will acquire lists here + MYSQL_RES* res = mysql_store_result(conn); + MYSQL_ROW row; + + if (res) + { + while ((row = mysql_fetch_row(res)) != NULL) + { + snprintf(sql, sizeof(sql), "ALTER TABLE `%s`.%s DROP INDEX %s", db.c_str(), row[0], row[1]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't drop index '%s'\nError: %d", row[0], ret); + } + } + mysql_free_result(res); + } + + // next topic is a views list + snprintf(sql, sizeof(sql), + "SELECT table_name FROM information_schema.views WHERE table_schema = '%s'", db.c_str()); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + throw DbErrors("Can't determine list of views to drop."); + + res = mysql_store_result(conn); + + if (res) + { + while ((row = mysql_fetch_row(res)) != NULL) + { + /* we do not need IF EXISTS because these views are exist */ + snprintf(sql, sizeof(sql), "DROP VIEW `%s`.%s", db.c_str(), row[0]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't drop view '%s'\nError: %d", row[0], ret); + } + } + mysql_free_result(res); + } + + // triggers + snprintf(sql, sizeof(sql), + "SELECT trigger_name FROM information_schema.triggers WHERE event_object_schema = '%s'", + db.c_str()); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + throw DbErrors("Can't determine list of triggers to drop."); + + res = mysql_store_result(conn); + + if (res) + { + while ((row = mysql_fetch_row(res)) != NULL) + { + snprintf(sql, sizeof(sql), "DROP TRIGGER `%s`.%s", db.c_str(), row[0]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't drop trigger '%s'\nError: %d", row[0], ret); + } + } + mysql_free_result(res); + } + + // Native functions + snprintf(sql, sizeof(sql), + "SELECT routine_name FROM information_schema.routines " + "WHERE routine_type = 'FUNCTION' and routine_schema = '%s'", + db.c_str()); + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + throw DbErrors("Can't determine list of routines to drop."); + + res = mysql_store_result(conn); + + if (res) + { + while ((row = mysql_fetch_row(res)) != NULL) + { + snprintf(sql, sizeof(sql), "DROP FUNCTION `%s`.%s", db.c_str(), row[0]); + + if ((ret = query_with_reconnect(sql)) != MYSQL_OK) + { + mysql_free_result(res); + throw DbErrors("Can't drop function '%s'\nError: %d", row[0], ret); + } + } + mysql_free_result(res); + } + + return 1; +} + +int MysqlDatabase::query_with_reconnect(const char* query) +{ + int attempts = 5; + int result; + + // try to reconnect if server is gone + while (((result = mysql_real_query(conn, query, strlen(query))) != MYSQL_OK) && + ((result = mysql_errno(conn)) == CR_SERVER_GONE_ERROR || result == CR_SERVER_LOST) && + (attempts-- > 0)) + { + CLog::Log(LOGINFO, "MYSQL server has gone. Will try {} more attempt(s) to reconnect.", + attempts); + active = false; + connect(true); + } + + return result; +} + +long MysqlDatabase::nextid(const char* sname) +{ + CLog::Log(LOGDEBUG, "MysqlDatabase::nextid for {}", sname); + if (!active) + return DB_UNEXPECTED_RESULT; + const char* seq_table = "sys_seq"; + int id; /*,nrow,ncol;*/ + MYSQL_RES* res; + char sqlcmd[512]; + snprintf(sqlcmd, sizeof(sqlcmd), "SELECT nextid FROM %s WHERE seq_name = '%s'", seq_table, sname); + CLog::Log(LOGDEBUG, "MysqlDatabase::nextid will request"); + if ((last_err = query_with_reconnect(sqlcmd)) != 0) + { + return DB_UNEXPECTED_RESULT; + } + res = mysql_store_result(conn); + if (res) + { + if (mysql_num_rows(res) == 0) + { + id = 1; + snprintf(sqlcmd, sizeof(sqlcmd), "INSERT INTO %s (nextid,seq_name) VALUES (%d,'%s')", + seq_table, id, sname); + mysql_free_result(res); + if ((last_err = query_with_reconnect(sqlcmd)) != 0) + return DB_UNEXPECTED_RESULT; + return id; + } + else + { + id = -1; + snprintf(sqlcmd, sizeof(sqlcmd), "UPDATE %s SET nextid=%d WHERE seq_name = '%s'", seq_table, + id, sname); + mysql_free_result(res); + if ((last_err = query_with_reconnect(sqlcmd)) != 0) + return DB_UNEXPECTED_RESULT; + return id; + } + } + return DB_UNEXPECTED_RESULT; +} + +// methods for transactions +// --------------------------------------------- +void MysqlDatabase::start_transaction() +{ + if (active) + { + mysql_autocommit(conn, false); + CLog::Log(LOGDEBUG, "Mysql Start transaction"); + _in_transaction = true; + } +} + +void MysqlDatabase::commit_transaction() +{ + if (active) + { + mysql_commit(conn); + mysql_autocommit(conn, true); + CLog::Log(LOGDEBUG, "Mysql commit transaction"); + _in_transaction = false; + } +} + +void MysqlDatabase::rollback_transaction() +{ + if (active) + { + mysql_rollback(conn); + mysql_autocommit(conn, true); + CLog::Log(LOGDEBUG, "Mysql rollback transaction"); + _in_transaction = false; + } +} + +bool MysqlDatabase::exists(void) +{ + bool ret = false; + + if (conn == NULL || mysql_ping(conn)) + { + CLog::Log(LOGERROR, "Not connected to database, test of existence is not possible."); + return ret; + } + + MYSQL_RES* result = mysql_list_dbs(conn, db.c_str()); + if (result == NULL) + { + CLog::Log(LOGERROR, "Database is not present, does the user has CREATE DATABASE permission"); + return false; + } + + ret = (mysql_num_rows(result) > 0); + mysql_free_result(result); + + // Check if there is some tables ( to permit user with no create database rights + if (ret) + { + result = mysql_list_tables(conn, NULL); + if (result != NULL) + ret = (mysql_num_rows(result) > 0); + + mysql_free_result(result); + } + + return ret; +} + +// methods for formatting +// --------------------------------------------- +std::string MysqlDatabase::vprepare(const char* format, va_list args) +{ + std::string strFormat = format; + std::string strResult = ""; + size_t pos; + + // %q is the sqlite format string for %s. + // Any bad character, like "'", will be replaced with a proper one + pos = 0; + while ((pos = strFormat.find("%s", pos)) != std::string::npos) + strFormat.replace(pos++, 2, "%q"); + + strResult = mysql_vmprintf(strFormat.c_str(), args); + // RAND() is the mysql form of RANDOM() + pos = 0; + while ((pos = strResult.find("RANDOM()", pos)) != std::string::npos) + { + strResult.replace(pos++, 8, "RAND()"); + pos += 6; + } + + // Replace some dataypes in CAST statements: + // before: CAST(iFoo AS TEXT), CAST(foo AS INTEGER) + // after: CAST(iFoo AS CHAR), CAST(foo AS SIGNED INTEGER) + pos = strResult.find("CAST("); + while (pos != std::string::npos) + { + size_t pos2 = strResult.find(" AS TEXT)", pos + 1); + if (pos2 != std::string::npos) + strResult.replace(pos2, 9, " AS CHAR)"); + else + { + pos2 = strResult.find(" AS INTEGER)", pos + 1); + if (pos2 != std::string::npos) + strResult.replace(pos2, 12, " AS SIGNED INTEGER)"); + } + pos = strResult.find("CAST(", pos + 1); + } + + // Remove COLLATE NOCASE the SQLite case insensitive collation. + // In MySQL all tables are defined with case insensitive collation utf8_general_ci + pos = 0; + while ((pos = strResult.find(" COLLATE NOCASE", pos)) != std::string::npos) + strResult.erase(pos++, 15); + + // Remove COLLATE ALPHANUM the SQLite custom collation. + pos = 0; + while ((pos = strResult.find(" COLLATE ALPHANUM", pos)) != std::string::npos) + strResult.erase(pos++, 15); + + return strResult; +} + +/* vsprintf() functionality is based on sqlite3.c functions */ + +/* +** Conversion types fall into various categories as defined by the +** following enumeration. +*/ +#define etRADIX 1 /* Integer types. %d, %x, %o, and so forth */ +#define etFLOAT 2 /* Floating point. %f */ +#define etEXP 3 /* Exponential notation. %e and %E */ +#define etGENERIC 4 /* Floating or exponential, depending on exponent. %g */ +#define etSIZE 5 /* Return number of characters processed so far. %n */ +#define etSTRING 6 /* Strings. %s */ +#define etDYNSTRING 7 /* Dynamically allocated strings. %z */ +#define etPERCENT 8 /* Percent symbol. %% */ +#define etCHARX 9 /* Characters. %c */ +/* The rest are extensions, not normally found in printf() */ +#define etSQLESCAPE 10 /* Strings with '\'' doubled. Strings with '\\' escaped. %q */ +#define etSQLESCAPE2 \ + 11 /* Strings with '\'' doubled and enclosed in '', + NULL pointers replaced by SQL NULL. %Q */ +#define etPOINTER 14 /* The %p conversion */ +#define etSQLESCAPE3 15 /* %w -> Strings with '\"' doubled */ + +#define etINVALID 0 /* Any unrecognized conversion type */ + +/* +** An "etByte" is an 8-bit unsigned value. +*/ +typedef unsigned char etByte; + +/* +** Each builtin conversion character (ex: the 'd' in "%d") is described +** by an instance of the following structure +*/ +typedef struct et_info +{ /* Information about each format field */ + char fmttype; /* The format field code letter */ + etByte base; /* The base for radix conversion */ + etByte flags; /* One or more of FLAG_ constants below */ + etByte type; /* Conversion paradigm */ + etByte charset; /* Offset into aDigits[] of the digits string */ + etByte prefix; /* Offset into aPrefix[] of the prefix string */ +} et_info; + +/* +** An objected used to accumulate the text of a string where we +** do not necessarily know how big the string will be in the end. +*/ +struct StrAccum +{ + char* zBase; /* A base allocation. Not from malloc. */ + char* zText; /* The string collected so far */ + int nChar; /* Length of the string so far */ + int nAlloc; /* Amount of space allocated in zText */ + int mxAlloc; /* Maximum allowed string length */ + bool mallocFailed; /* Becomes true if any memory allocation fails */ + bool tooBig; /* Becomes true if string size exceeds limits */ +}; + +/* +** Allowed values for et_info.flags +*/ +#define FLAG_SIGNED 1 /* True if the value to convert is signed */ +#define FLAG_INTERN 2 /* True if for internal use only */ +#define FLAG_STRING 4 /* Allow infinity precision */ + +/* +** The following table is searched linearly, so it is good to put the +** most frequently used conversion types first. +*/ +static const char aDigits[] = "0123456789ABCDEF0123456789abcdef"; +static const char aPrefix[] = "-x0\000X0"; +// clang-format off +constexpr std::array<et_info, 20> fmtinfo = {{ + {'d', 10, 1, etRADIX, 0, 0}, + {'s', 0, 4, etSTRING, 0, 0}, + {'g', 0, 1, etGENERIC, 30, 0}, + {'z', 0, 4, etDYNSTRING, 0, 0}, + {'q', 0, 4, etSQLESCAPE, 0, 0}, + {'Q', 0, 4, etSQLESCAPE2, 0, 0}, + {'w', 0, 4, etSQLESCAPE3, 0, 0}, + {'c', 0, 0, etCHARX, 0, 0}, + {'o', 8, 0, etRADIX, 0, 2}, + {'u', 10, 0, etRADIX, 0, 0}, + {'x', 16, 0, etRADIX, 16, 1}, + {'X', 16, 0, etRADIX, 0, 4}, + {'f', 0, 1, etFLOAT, 0, 0}, + {'e', 0, 1, etEXP, 30, 0}, + {'E', 0, 1, etEXP, 14, 0}, + {'G', 0, 1, etGENERIC, 14, 0}, + {'i', 10, 1, etRADIX, 0, 0}, + {'n', 0, 0, etSIZE, 0, 0}, + {'%', 0, 0, etPERCENT, 0, 0}, + {'p', 16, 0, etPOINTER, 0, 1}, +}}; +// clang-format on + +/* +** "*val" is a double such that 0.1 <= *val < 10.0 +** Return the ascii code for the leading digit of *val, then +** multiply "*val" by 10.0 to renormalize. +** +** Example: +** input: *val = 3.14159 +** output: *val = 1.4159 function return = '3' +** +** The counter *cnt is incremented each time. After counter exceeds +** 16 (the number of significant digits in a 64-bit float) '0' is +** always returned. +*/ +char MysqlDatabase::et_getdigit(double* val, int* cnt) +{ + int digit; + double d; + if ((*cnt)++ >= 16) + return '0'; + digit = (int)*val; + d = digit; + digit += '0'; + *val = (*val - d) * 10.0; + return (char)digit; +} + +/* +** Append N space characters to the given string buffer. +*/ +void MysqlDatabase::appendSpace(StrAccum* pAccum, int N) +{ + static const char zSpaces[] = " "; + while (N >= (int)sizeof(zSpaces) - 1) + { + mysqlStrAccumAppend(pAccum, zSpaces, sizeof(zSpaces) - 1); + N -= sizeof(zSpaces) - 1; + } + if (N > 0) + { + mysqlStrAccumAppend(pAccum, zSpaces, N); + } +} + +#ifndef MYSQL_PRINT_BUF_SIZE +#define MYSQL_PRINT_BUF_SIZE 350 +#endif + +#define etBUFSIZE MYSQL_PRINT_BUF_SIZE /* Size of the output buffer */ + +/* +** The maximum length of a TEXT or BLOB in bytes. This also +** limits the size of a row in a table or index. +** +** The hard limit is the ability of a 32-bit signed integer +** to count the size: 2^31-1 or 2147483647. +*/ +#ifndef MYSQL_MAX_LENGTH +#define MYSQL_MAX_LENGTH 1000000000 +#endif + +/* +** The root program. All variations call this core. +** +** INPUTS: +** func This is a pointer to a function taking three arguments +** 1. A pointer to anything. Same as the "arg" parameter. +** 2. A pointer to the list of characters to be output +** (Note, this list is NOT null terminated.) +** 3. An integer number of characters to be output. +** (Note: This number might be zero.) +** +** arg This is the pointer to anything which will be passed as the +** first argument to "func". Use it for whatever you like. +** +** fmt This is the format string, as in the usual print. +** +** ap This is a pointer to a list of arguments. Same as in +** vfprint. +** +** OUTPUTS: +** The return value is the total number of characters sent to +** the function "func". Returns -1 on a error. +** +** Note that the order in which automatic variables are declared below +** seems to make a big difference in determining how fast this beast +** will run. +*/ +void MysqlDatabase::mysqlVXPrintf(StrAccum* pAccum, /* Accumulate results here */ + int useExtended, /* Allow extended %-conversions */ + const char* fmt, /* Format string */ + va_list ap /* arguments */ +) +{ + int c; /* Next character in the format string */ + char* bufpt; /* Pointer to the conversion buffer */ + int precision; /* Precision of the current field */ + int length; /* Length of the field */ + int idx; /* A general purpose loop counter */ + int width; /* Width of the current field */ + etByte flag_leftjustify; /* True if "-" flag is present */ + etByte flag_plussign; /* True if "+" flag is present */ + etByte flag_blanksign; /* True if " " flag is present */ + etByte flag_alternateform; /* True if "#" flag is present */ + etByte flag_altform2; /* True if "!" flag is present */ + etByte flag_zeropad; /* True if field width constant starts with zero */ + etByte flag_long; /* True if "l" flag is present */ + etByte flag_longlong; /* True if the "ll" flag is present */ + etByte done; /* Loop termination flag */ + uint64_t longvalue; /* Value for integer types */ + double realvalue; /* Value for real types */ + const et_info* infop; /* Pointer to the appropriate info structure */ + char buf[etBUFSIZE]; /* Conversion buffer */ + char prefix; /* Prefix character. "+" or "-" or " " or '\0'. */ + etByte xtype = 0; /* Conversion paradigm */ + char* zExtra; /* Extra memory used for etTCLESCAPE conversions */ + int exp, e2; /* exponent of real numbers */ + double rounder; /* Used for rounding floating point values */ + etByte flag_dp; /* True if decimal point should be shown */ + etByte flag_rtz; /* True if trailing zeros should be removed */ + etByte flag_exp; /* True to force display of the exponent */ + int nsd; /* Number of significant digits returned */ + + length = 0; + bufpt = 0; + for (; (c = (*fmt)) != 0; ++fmt) + { + bool isLike = false; + if (c != '%') + { + int amt; + bufpt = const_cast<char*>(fmt); + amt = 1; + while ((c = (*++fmt)) != '%' && c != 0) + amt++; + isLike = mysqlStrAccumAppend(pAccum, bufpt, amt); + if (c == 0) + break; + } + if ((c = (*++fmt)) == 0) + { + mysqlStrAccumAppend(pAccum, "%", 1); + break; + } + /* Find out what flags are present */ + flag_leftjustify = flag_plussign = flag_blanksign = flag_alternateform = flag_altform2 = + flag_zeropad = 0; + done = 0; + do + { + switch (c) + { + case '-': + flag_leftjustify = 1; + break; + case '+': + flag_plussign = 1; + break; + case ' ': + flag_blanksign = 1; + break; + case '#': + flag_alternateform = 1; + break; + case '!': + flag_altform2 = 1; + break; + case '0': + flag_zeropad = 1; + break; + default: + done = 1; + break; + } + } while (!done && (c = (*++fmt)) != 0); + /* Get the field width */ + width = 0; + if (c == '*') + { + width = va_arg(ap, int); + if (width < 0) + { + flag_leftjustify = 1; + width = -width; + } + c = *++fmt; + } + else + { + while (c >= '0' && c <= '9') + { + width = width * 10 + c - '0'; + c = *++fmt; + } + } + if (width > etBUFSIZE - 10) + { + width = etBUFSIZE - 10; + } + /* Get the precision */ + if (c == '.') + { + precision = 0; + c = *++fmt; + if (c == '*') + { + precision = va_arg(ap, int); + if (precision < 0) + precision = -precision; + c = *++fmt; + } + else + { + while (c >= '0' && c <= '9') + { + precision = precision * 10 + c - '0'; + c = *++fmt; + } + } + } + else + { + precision = -1; + } + /* Get the conversion type modifier */ + if (c == 'l') + { + flag_long = 1; + c = *++fmt; + if (c == 'l') + { + flag_longlong = 1; + c = *++fmt; + } + else + { + flag_longlong = 0; + } + } + else + { + flag_long = flag_longlong = 0; + } + /* Fetch the info entry for the field */ + infop = fmtinfo.data(); + xtype = etINVALID; + + for (const auto& info : fmtinfo) + { + if (c != info.fmttype) + continue; + + infop = &info; + + if (useExtended || (infop->flags & FLAG_INTERN) == 0) + { + xtype = infop->type; + } + else + { + return; + } + + break; + } + + zExtra = 0; + + /* Limit the precision to prevent overflowing buf[] during conversion */ + if (precision > etBUFSIZE - 40 && (infop->flags & FLAG_STRING) == 0) + { + precision = etBUFSIZE - 40; + } + + /* + ** At this point, variables are initialized as follows: + ** + ** flag_alternateform TRUE if a '#' is present. + ** flag_altform2 TRUE if a '!' is present. + ** flag_plussign TRUE if a '+' is present. + ** flag_leftjustify TRUE if a '-' is present or if the + ** field width was negative. + ** flag_zeropad TRUE if the width began with 0. + ** flag_long TRUE if the letter 'l' (ell) prefixed + ** the conversion character. + ** flag_longlong TRUE if the letter 'll' (ell ell) prefixed + ** the conversion character. + ** flag_blanksign TRUE if a ' ' is present. + ** width The specified field width. This is + ** always non-negative. Zero is the default. + ** precision The specified precision. The default + ** is -1. + ** xtype The class of the conversion. + ** infop Pointer to the appropriate info struct. + */ + switch (xtype) + { + case etPOINTER: + flag_longlong = sizeof(char*) == sizeof(int64_t); + flag_long = sizeof(char*) == sizeof(long int); + /* Fall through into the next case */ + [[fallthrough]]; + case etRADIX: + if (infop->flags & FLAG_SIGNED) + { + int64_t v; + if (flag_longlong) + { + v = va_arg(ap, int64_t); + } + else if (flag_long) + { + v = va_arg(ap, long int); + } + else + { + v = va_arg(ap, int); + } + if (v < 0) + { + longvalue = -v; + prefix = '-'; + } + else + { + longvalue = v; + if (flag_plussign) + prefix = '+'; + else if (flag_blanksign) + prefix = ' '; + else + prefix = 0; + } + } + else + { + if (flag_longlong) + { + longvalue = va_arg(ap, uint64_t); + } + else if (flag_long) + { + longvalue = va_arg(ap, unsigned long int); + } + else + { + longvalue = va_arg(ap, unsigned int); + } + prefix = 0; + } + if (longvalue == 0) + flag_alternateform = 0; + if (flag_zeropad && precision < width - (prefix != 0)) + { + precision = width - (prefix != 0); + } + bufpt = &buf[etBUFSIZE - 1]; + { + const char* cset; + int base; + cset = &aDigits[infop->charset]; + base = infop->base; + do + { /* Convert to ascii */ + *(--bufpt) = cset[longvalue % base]; + longvalue = longvalue / base; + } while (longvalue > 0); + } + length = (int)(&buf[etBUFSIZE - 1] - bufpt); + for (idx = precision - length; idx > 0; idx--) + { + *(--bufpt) = '0'; /* Zero pad */ + } + if (prefix) + *(--bufpt) = prefix; /* Add sign */ + if (flag_alternateform && infop->prefix) + { /* Add "0" or "0x" */ + const char* pre; + char x; + pre = &aPrefix[infop->prefix]; + for (; (x = (*pre)) != 0; pre++) + *(--bufpt) = x; + } + length = (int)(&buf[etBUFSIZE - 1] - bufpt); + bufpt[length] = 0; + break; + case etFLOAT: + case etEXP: + case etGENERIC: + realvalue = va_arg(ap, double); + if (precision < 0) + precision = 6; /* Set default precision */ + if (precision > etBUFSIZE / 2 - 10) + precision = etBUFSIZE / 2 - 10; + if (realvalue < 0.0) + { + realvalue = -realvalue; + prefix = '-'; + } + else + { + if (flag_plussign) + prefix = '+'; + else if (flag_blanksign) + prefix = ' '; + else + prefix = 0; + } + if (xtype == etGENERIC && precision > 0) + precision--; + /* It makes more sense to use 0.5 */ + for (idx = precision, rounder = 0.5; idx > 0; idx--, rounder *= 0.1) + { + } + if (xtype == etFLOAT) + realvalue += rounder; + /* Normalize realvalue to within 10.0 > realvalue >= 1.0 */ + exp = 0; +#if 0 + if( mysqlIsNaN((double)realvalue) ){ + bufpt = "NaN"; + length = 3; + break; + } +#endif + if (realvalue > 0.0) + { + while (realvalue >= 1e32 && exp <= 350) + { + realvalue *= 1e-32; + exp += 32; + } + while (realvalue >= 1e8 && exp <= 350) + { + realvalue *= 1e-8; + exp += 8; + } + while (realvalue >= 10.0 && exp <= 350) + { + realvalue *= 0.1; + exp++; + } + while (realvalue < 1e-8) + { + realvalue *= 1e8; + exp -= 8; + } + while (realvalue < 1.0) + { + realvalue *= 10.0; + exp--; + } + if (exp > 350) + { + if (prefix == '-') + { + bufpt = const_cast<char*>("-Inf"); + } + else if (prefix == '+') + { + bufpt = const_cast<char*>("+Inf"); + } + else + { + bufpt = const_cast<char*>("Inf"); + } + length = strlen(bufpt); + break; + } + } + bufpt = buf; + /* + ** If the field type is etGENERIC, then convert to either etEXP + ** or etFLOAT, as appropriate. + */ + flag_exp = xtype == etEXP; + if (xtype != etFLOAT) + { + realvalue += rounder; + if (realvalue >= 10.0) + { + realvalue *= 0.1; + exp++; + } + } + if (xtype == etGENERIC) + { + flag_rtz = !flag_alternateform; + if (exp < -4 || exp > precision) + { + xtype = etEXP; + } + else + { + precision = precision - exp; + xtype = etFLOAT; + } + } + else + { + flag_rtz = 0; + } + if (xtype == etEXP) + { + e2 = 0; + } + else + { + e2 = exp; + } + nsd = 0; + flag_dp = (precision > 0 ? 1 : 0) | flag_alternateform | flag_altform2; + /* The sign in front of the number */ + if (prefix) + { + *(bufpt++) = prefix; + } + /* Digits prior to the decimal point */ + if (e2 < 0) + { + *(bufpt++) = '0'; + } + else + { + for (; e2 >= 0; e2--) + { + *(bufpt++) = et_getdigit(&realvalue, &nsd); + } + } + /* The decimal point */ + if (flag_dp) + { + *(bufpt++) = '.'; + } + /* "0" digits after the decimal point but before the first + ** significant digit of the number */ + for (e2++; e2 < 0; precision--, e2++) + { + //ASSERT( precision>0 ); + *(bufpt++) = '0'; + } + /* Significant digits after the decimal point */ + while ((precision--) > 0) + { + *(bufpt++) = et_getdigit(&realvalue, &nsd); + } + /* Remove trailing zeros and the "." if no digits follow the "." */ + if (flag_rtz && flag_dp) + { + while (bufpt[-1] == '0') + *(--bufpt) = 0; + //ASSERT( bufpt>buf ); + if (bufpt[-1] == '.') + { + if (flag_altform2) + { + *(bufpt++) = '0'; + } + else + { + *(--bufpt) = 0; + } + } + } + /* Add the "eNNN" suffix */ + if (flag_exp || xtype == etEXP) + { + *(bufpt++) = aDigits[infop->charset]; + if (exp < 0) + { + *(bufpt++) = '-'; + exp = -exp; + } + else + { + *(bufpt++) = '+'; + } + if (exp >= 100) + { + *(bufpt++) = (char)((exp / 100) + '0'); /* 100's digit */ + exp %= 100; + } + *(bufpt++) = (char)(exp / 10 + '0'); /* 10's digit */ + *(bufpt++) = (char)(exp % 10 + '0'); /* 1's digit */ + } + *bufpt = 0; + + /* The converted number is in buf[] and zero terminated. Output it. + ** Note that the number is in the usual order, not reversed as with + ** integer conversions. */ + length = (int)(bufpt - buf); + bufpt = buf; + + /* Special case: Add leading zeros if the flag_zeropad flag is + ** set and we are not left justified */ + if (flag_zeropad && !flag_leftjustify && length < width) + { + int i; + int nPad = width - length; + for (i = width; i >= nPad; i--) + { + bufpt[i] = bufpt[i - nPad]; + } + i = prefix != 0; + while (nPad--) + bufpt[i++] = '0'; + length = width; + } + break; + case etSIZE: + *(va_arg(ap, int*)) = pAccum->nChar; + length = width = 0; + break; + case etPERCENT: + buf[0] = '%'; + bufpt = buf; + length = 1; + break; + case etCHARX: + c = va_arg(ap, int); + buf[0] = (char)c; + if (precision >= 0) + { + for (idx = 1; idx < precision; idx++) + buf[idx] = (char)c; + length = precision; + } + else + { + length = 1; + } + bufpt = buf; + break; + case etSTRING: + case etDYNSTRING: + bufpt = va_arg(ap, char*); + if (bufpt == 0) + { + bufpt = const_cast<char*>(""); + } + else if (xtype == etDYNSTRING) + { + zExtra = bufpt; + } + if (precision >= 0) + { + for (length = 0; length < precision && bufpt[length]; length++) + { + } + } + else + { + length = strlen(bufpt); + } + break; + case etSQLESCAPE: + case etSQLESCAPE2: + case etSQLESCAPE3: + { + int i, j, k, n, isnull; + int needQuote; + char ch; + char q = ((xtype == etSQLESCAPE3) ? '"' : '\''); /* Quote character */ + std::string arg = va_arg(ap, char*); + if (isLike) + StringUtils::Replace(arg, "\\", "\\\\"); + const char* escarg = arg.c_str(); + + isnull = escarg == 0; + if (isnull) + escarg = (xtype == etSQLESCAPE2 ? "NULL" : "(NULL)"); + k = precision; + for (i = 0; k != 0 && (ch = escarg[i]) != 0; i++, k--) + ; + needQuote = !isnull && xtype == etSQLESCAPE2; + n = i * 2 + 1 + needQuote * 2; + if (n > etBUFSIZE) + { + bufpt = zExtra = (char*)malloc(n); + if (bufpt == 0) + { + pAccum->mallocFailed = true; + return; + } + } + else + { + bufpt = buf; + } + j = 0; + if (needQuote) + bufpt[j++] = q; + k = i; + j += mysql_real_escape_string(conn, bufpt, escarg, k); + if (needQuote) + bufpt[j++] = q; + bufpt[j] = 0; + length = j; + /* The precision in %q and %Q means how many input characters to + ** consume, not the length of the output... + ** if( precision>=0 && precision<length ) length = precision; */ + break; + } + default: + { + return; + } + } /* End switch over the format type */ + /* + ** The text of the conversion is pointed to by "bufpt" and is + ** "length" characters long. The field width is "width". Do + ** the output. + */ + if (!flag_leftjustify) + { + int nspace; + nspace = width - length; + if (nspace > 0) + { + appendSpace(pAccum, nspace); + } + } + if (length > 0) + { + mysqlStrAccumAppend(pAccum, bufpt, length); + } + if (flag_leftjustify) + { + int nspace; + nspace = width - length; + if (nspace > 0) + { + appendSpace(pAccum, nspace); + } + } + if (zExtra) + { + free(zExtra); + } + } /* End for loop over the format string */ +} /* End of function */ + +/* +** Append N bytes of text from z to the StrAccum object. +*/ +bool MysqlDatabase::mysqlStrAccumAppend(StrAccum* p, const char* z, int N) +{ + if (p->tooBig | p->mallocFailed) + { + return false; + } + if (N < 0) + { + N = strlen(z); + } + if (N == 0 || z == 0) + { + return false; + } + if (p->nChar + N >= p->nAlloc) + { + char* zNew; + int szNew = p->nChar; + szNew += N + 1; + if (szNew > p->mxAlloc) + { + mysqlStrAccumReset(p); + p->tooBig = true; + return false; + } + else + { + p->nAlloc = szNew; + } + zNew = (char*)malloc(p->nAlloc); + if (zNew) + { + memcpy(zNew, p->zText, p->nChar); + mysqlStrAccumReset(p); + p->zText = zNew; + } + else + { + p->mallocFailed = true; + mysqlStrAccumReset(p); + return false; + } + } + + bool isLike = false; + std::string testString(z, N); + if (testString.find("LIKE") != std::string::npos || testString.find("like") != std::string::npos) + { + CLog::Log(LOGDEBUG, + "This query part contains a like, we will double backslash in the next field: {}", + testString); + isLike = true; + } + + memcpy(&p->zText[p->nChar], z, N); + p->nChar += N; + return isLike; +} + +/* +** Finish off a string by making sure it is zero-terminated. +** Return a pointer to the resulting string. Return a NULL +** pointer if any kind of error was encountered. +*/ +char* MysqlDatabase::mysqlStrAccumFinish(StrAccum* p) +{ + if (p->zText) + { + p->zText[p->nChar] = 0; + if (p->zText == p->zBase) + { + p->zText = (char*)malloc(p->nChar + 1); + if (p->zText) + { + memcpy(p->zText, p->zBase, p->nChar + 1); + } + else + { + p->mallocFailed = true; + } + } + } + return p->zText; +} + +/* +** Reset an StrAccum string. Reclaim all malloced memory. +*/ +void MysqlDatabase::mysqlStrAccumReset(StrAccum* p) +{ + if (p->zText != p->zBase) + { + free(p->zText); + } + p->zText = 0; +} + +/* +** Initialize a string accumulator +*/ +void MysqlDatabase::mysqlStrAccumInit(StrAccum* p, char* zBase, int n, int mx) +{ + p->zText = p->zBase = zBase; + p->nChar = 0; + p->nAlloc = n; + p->mxAlloc = mx; + p->tooBig = false; + p->mallocFailed = false; +} + +/* +** Print into memory obtained from mysql_malloc(). Omit the internal +** %-conversion extensions. +*/ +std::string MysqlDatabase::mysql_vmprintf(const char* zFormat, va_list ap) +{ + char zBase[MYSQL_PRINT_BUF_SIZE]; + StrAccum acc; + + mysqlStrAccumInit(&acc, zBase, sizeof(zBase), MYSQL_MAX_LENGTH); + mysqlVXPrintf(&acc, 0, zFormat, ap); + return mysqlStrAccumFinish(&acc); +} + +//************* MysqlDataset implementation *************** + +MysqlDataset::MysqlDataset() : Dataset() +{ + haveError = false; + db = NULL; + autorefresh = false; +} + +MysqlDataset::MysqlDataset(MysqlDatabase* newDb) : Dataset(newDb) +{ + haveError = false; + db = newDb; + autorefresh = false; +} + +MysqlDataset::~MysqlDataset() +{ +} + +void MysqlDataset::set_autorefresh(bool val) +{ + autorefresh = val; +} + +//--------- protected functions implementation -----------------// + +MYSQL* MysqlDataset::handle() +{ + if (db != NULL) + { + return static_cast<MysqlDatabase*>(db)->getHandle(); + } + + return NULL; +} + +void MysqlDataset::make_query(StringList& _sql) +{ + std::string query; + if (db == NULL) + throw DbErrors("No Database Connection"); + try + { + if (autocommit) + db->start_transaction(); + + for (const std::string& i : _sql) + { + query = i; + Dataset::parse_sql(query); + if ((static_cast<MysqlDatabase*>(db)->query_with_reconnect(query.c_str())) != MYSQL_OK) + { + throw DbErrors(db->getErrorMsg()); + } + } // end of for + + if (db->in_transaction() && autocommit) + db->commit_transaction(); + + active = true; + ds_state = dsSelect; + if (autorefresh) + refresh(); + } // end of try + catch (...) + { + if (db->in_transaction()) + db->rollback_transaction(); + throw; + } +} + +void MysqlDataset::make_insert() +{ + make_query(insert_sql); + last(); +} + +void MysqlDataset::make_edit() +{ + make_query(update_sql); +} + +void MysqlDataset::make_deletion() +{ + make_query(delete_sql); +} + +void MysqlDataset::fill_fields() +{ + if ((db == NULL) || (result.record_header.empty()) || + (result.records.size() < (unsigned int)frecno)) + return; + + if (fields_object->size() == 0) // Filling columns name + { + const unsigned int ncols = result.record_header.size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + { + (*fields_object)[i].props = result.record_header[i]; + std::string name = result.record_header[i].name; + name2indexMap.insert({str_toLower(name.data()), i}); + } + } + + //Filling result + if (result.records.size() != 0) + { + const sql_record* row = result.records[frecno]; + if (row) + { + const unsigned int ncols = row->size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + (*fields_object)[i].val = row->at(i); + return; + } + } + const unsigned int ncols = result.record_header.size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + (*fields_object)[i].val = ""; +} + +//------------- public functions implementation -----------------// +bool MysqlDataset::dropIndex(const char* table, const char* index) +{ + std::string sql; + std::string sql_prepared; + + sql = "SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE() AND " + "table_name='%s' AND index_name='%s'"; + sql_prepared = static_cast<MysqlDatabase*>(db)->prepare(sql.c_str(), table, index); + + if (!query(sql_prepared)) + return false; + + if (num_rows()) + { + sql = "ALTER TABLE %s DROP INDEX %s"; + sql_prepared = static_cast<MysqlDatabase*>(db)->prepare(sql.c_str(), table, index); + + if (exec(sql_prepared) != MYSQL_OK) + return false; + } + + return true; +} + +static bool ci_test(char l, char r) +{ + return tolower(l) == tolower(r); +} + +static size_t ci_find(const std::string& where, const std::string& what) +{ + std::string::const_iterator loc = + std::search(where.begin(), where.end(), what.begin(), what.end(), ci_test); + if (loc == where.end()) + return std::string::npos; + else + return loc - where.begin(); +} + +int MysqlDataset::exec(const std::string& sql) +{ + if (!handle()) + throw DbErrors("No Database Connection"); + std::string qry = sql; + int res = 0; + exec_res.clear(); + + // enforce the "auto_increment" keyword to be appended to "integer primary key" + size_t loc; + + if ((loc = ci_find(qry, "integer primary key")) != std::string::npos) + { + qry = qry.insert(loc + 19, " auto_increment "); + } + + // force the charset and collation to UTF-8 + if (ci_find(qry, "CREATE TABLE") != std::string::npos || + ci_find(qry, "CREATE TEMPORARY TABLE") != std::string::npos) + { + // If CREATE TABLE ... SELECT Syntax is used we need to add the encoding after the table before the select + // e.g. CREATE TABLE x CHARACTER SET utf8 COLLATE utf8_general_ci [AS] SELECT * FROM y + if ((loc = qry.find(" AS SELECT ")) != std::string::npos || + (loc = qry.find(" SELECT ")) != std::string::npos) + { + qry = qry.insert(loc, " CHARACTER SET utf8 COLLATE utf8_general_ci"); + } + else + qry += " CHARACTER SET utf8 COLLATE utf8_general_ci"; + } + + CLog::Log(LOGDEBUG, "Mysql execute: {}", qry); + + if (db->setErr(static_cast<MysqlDatabase*>(db)->query_with_reconnect(qry.c_str()), qry.c_str()) != + MYSQL_OK) + { + throw DbErrors(db->getErrorMsg()); + } + else + { + //! @todo collect results and store in exec_res + return res; + } +} + +int MysqlDataset::exec() +{ + return exec(sql); +} + +const void* MysqlDataset::getExecRes() +{ + return &exec_res; +} + +bool MysqlDataset::query(const std::string& query) +{ + if (!handle()) + throw DbErrors("No Database Connection"); + std::string qry = query; + int fs = qry.find("select"); + int fS = qry.find("SELECT"); + if (!(fs >= 0 || fS >= 0)) + throw DbErrors("MUST be select SQL!"); + + close(); + + size_t loc; + + // mysql doesn't understand CAST(foo as integer) => change to CAST(foo as signed integer) + while ((loc = ci_find(qry, "as integer)")) != std::string::npos) + qry = qry.insert(loc + 3, "signed "); + + MYSQL_RES* stmt = NULL; + + if (static_cast<MysqlDatabase*>(db)->setErr( + static_cast<MysqlDatabase*>(db)->query_with_reconnect(qry.c_str()), qry.c_str()) != + MYSQL_OK) + throw DbErrors(db->getErrorMsg()); + + MYSQL* conn = handle(); + stmt = mysql_store_result(conn); + if (stmt == NULL) + throw DbErrors("Missing result set!"); + + // column headers + const unsigned int numColumns = mysql_num_fields(stmt); + MYSQL_FIELD* fields = mysql_fetch_fields(stmt); + MYSQL_ROW row; + result.record_header.resize(numColumns); + for (unsigned int i = 0; i < numColumns; i++) + result.record_header[i].name = fields[i].name; + + // returned rows + while ((row = mysql_fetch_row(stmt))) + { // have a row of data + sql_record* res = new sql_record; + res->resize(numColumns); + for (unsigned int i = 0; i < numColumns; i++) + { + field_value& v = res->at(i); + switch (fields[i].type) + { + case MYSQL_TYPE_LONGLONG: + if (row[i] != nullptr) + { + v.set_asInt64(strtoll(row[i], nullptr, 10)); + } + else + { + v.set_asInt64(0); + } + break; + case MYSQL_TYPE_DECIMAL: + case MYSQL_TYPE_NEWDECIMAL: + case MYSQL_TYPE_TINY: + case MYSQL_TYPE_SHORT: + case MYSQL_TYPE_INT24: + case MYSQL_TYPE_LONG: + if (row[i] != NULL) + { + v.set_asInt(atoi(row[i])); + } + else + { + v.set_asInt(0); + } + break; + case MYSQL_TYPE_FLOAT: + case MYSQL_TYPE_DOUBLE: + if (row[i] != NULL) + { + v.set_asDouble(atof(row[i])); + } + else + { + v.set_asDouble(0); + } + break; + case MYSQL_TYPE_STRING: + case MYSQL_TYPE_VAR_STRING: + case MYSQL_TYPE_VARCHAR: + if (row[i] != NULL) + v.set_asString((const char*)row[i]); + break; + case MYSQL_TYPE_TINY_BLOB: + case MYSQL_TYPE_MEDIUM_BLOB: + case MYSQL_TYPE_LONG_BLOB: + case MYSQL_TYPE_BLOB: + if (row[i] != NULL) + v.set_asString((const char*)row[i]); + break; + case MYSQL_TYPE_NULL: + default: + CLog::Log(LOGDEBUG, "MYSQL: Unknown field type: {}", fields[i].type); + v.set_asString(""); + v.set_isNull(); + break; + } + } + result.records.push_back(res); + } + mysql_free_result(stmt); + active = true; + ds_state = dsSelect; + this->first(); + return true; +} + +void MysqlDataset::open(const std::string& sql) +{ + set_select_sql(sql); + open(); +} + +void MysqlDataset::open() +{ + if (select_sql.size()) + { + query(select_sql); + } + else + { + ds_state = dsInactive; + } +} + +void MysqlDataset::close() +{ + Dataset::close(); + result.clear(); + edit_object->clear(); + fields_object->clear(); + ds_state = dsInactive; + active = false; +} + +void MysqlDataset::cancel() +{ + if ((ds_state == dsInsert) || (ds_state == dsEdit)) + { + if (result.record_header.size()) + ds_state = dsSelect; + else + ds_state = dsInactive; + } +} + +int MysqlDataset::num_rows() +{ + return result.records.size(); +} + +bool MysqlDataset::eof() +{ + return feof; +} + +bool MysqlDataset::bof() +{ + return fbof; +} + +void MysqlDataset::first() +{ + Dataset::first(); + this->fill_fields(); +} + +void MysqlDataset::last() +{ + Dataset::last(); + fill_fields(); +} + +void MysqlDataset::prev(void) +{ + Dataset::prev(); + fill_fields(); +} + +void MysqlDataset::next(void) +{ + Dataset::next(); + if (!eof()) + fill_fields(); +} + +void MysqlDataset::free_row(void) +{ + if (frecno < 0 || (unsigned int)frecno >= result.records.size()) + return; + + sql_record* row = result.records[frecno]; + if (row) + { + delete row; + result.records[frecno] = NULL; + } +} + +bool MysqlDataset::seek(int pos) +{ + if (ds_state == dsSelect) + { + Dataset::seek(pos); + fill_fields(); + return true; + } + + return false; +} + +int64_t MysqlDataset::lastinsertid() +{ + if (!handle()) + throw DbErrors("No Database Connection"); + return mysql_insert_id(handle()); +} + +long MysqlDataset::nextid(const char* seq_name) +{ + if (handle()) + return db->nextid(seq_name); + + return DB_UNEXPECTED_RESULT; +} + +void MysqlDataset::interrupt() +{ + // Impossible +} + +} // namespace dbiplus diff --git a/xbmc/dbwrappers/mysqldataset.h b/xbmc/dbwrappers/mysqldataset.h new file mode 100644 index 0000000..552368d --- /dev/null +++ b/xbmc/dbwrappers/mysqldataset.h @@ -0,0 +1,167 @@ +/* + * Copyright (C) 2005-2018 Team Kodi + * This file is part of Kodi - https://kodi.tv + * + * SPDX-License-Identifier: GPL-2.0-or-later + * See LICENSES/README.md for more information. + */ + +#pragma once + +#include "dataset.h" + +#include <stdio.h> +#ifdef HAS_MYSQL +#include <mysql/mysql.h> +#elif defined(HAS_MARIADB) +#include <mariadb/mysql.h> +#endif + +namespace dbiplus +{ +/***************** Class MysqlDatabase definition ****************** + + class 'MysqlDatabase' connects with MySQL-server + +******************************************************************/ +class MysqlDatabase : public Database +{ +protected: + /* connect descriptor */ + MYSQL* conn; + bool _in_transaction; + int last_err; + +public: + /* default constructor */ + MysqlDatabase(); + /* destructor */ + ~MysqlDatabase() override; + + Dataset* CreateDataset() const override; + + /* func. returns connection handle with MySQL-server */ + MYSQL* getHandle() { return conn; } + /* func. returns current status about MySQL-server connection */ + int status() override; + int setErr(int err_code, const char* qry) override; + /* func. returns error message if error occurs */ + const char* getErrorMsg() override; + + /* func. connects to database-server */ + int connect(bool create) override; + /* func. disconnects from database-server */ + void disconnect() override; + /* func. creates new database */ + int create() override; + /* func. deletes database */ + int drop() override; + /* check if database exists (ie has tables/views defined) */ + bool exists() override; + + /* \brief copy database */ + int copy(const char* backup_name) override; + + /* \brief drop all extra analytics from database */ + int drop_analytics(void) override; + + long nextid(const char* seq_name) override; + + /* virtual methods for transaction */ + + void start_transaction() override; + void commit_transaction() override; + void rollback_transaction() override; + + /* virtual methods for formatting */ + std::string vprepare(const char* format, va_list args) override; + + bool in_transaction() override { return _in_transaction; } + int query_with_reconnect(const char* query); + void configure_connection(); + +private: + typedef struct StrAccum StrAccum; + + char et_getdigit(double* val, int* cnt); + void appendSpace(StrAccum* pAccum, int N); + void mysqlVXPrintf(StrAccum* pAccum, int useExtended, const char* fmt, va_list ap); + bool mysqlStrAccumAppend(StrAccum* p, const char* z, int N); + char* mysqlStrAccumFinish(StrAccum* p); + void mysqlStrAccumReset(StrAccum* p); + void mysqlStrAccumInit(StrAccum* p, char* zBase, int n, int mx); + std::string mysql_vmprintf(const char* zFormat, va_list ap); +}; + +/***************** Class MysqlDataset definition ******************* + + class 'MysqlDataset' does a query to MySQL-server + +******************************************************************/ + +class MysqlDataset : public Dataset +{ +protected: + MYSQL* handle(); + + /* Makes direct queries to database */ + virtual void make_query(StringList& _sql); + /* Makes direct inserts into database */ + void make_insert() override; + /* Edit SQL */ + void make_edit() override; + /* Delete SQL */ + void make_deletion() override; + + /* This function works only with MySQL database + Filling the fields information from select statement */ + void fill_fields() override; + /* Changing field values during dataset navigation */ + virtual void free_row(); // free the memory allocated for the current row + +public: + /* constructor */ + MysqlDataset(); + explicit MysqlDataset(MysqlDatabase* newDb); + + /* destructor */ + ~MysqlDataset() override; + + /* set autorefresh boolean value (if true - refresh the data after edit() +or insert() operations default = false) */ + void set_autorefresh(bool val); + + /* opens a query & then sets a query results */ + void open() override; + void open(const std::string& sql) override; + /* func. executes a query without results to return */ + int exec() override; + int exec(const std::string& sql) override; + const void* getExecRes() override; + /* as open, but with our query exec Sql */ + bool query(const std::string& query) override; + /* func. closes a query */ + void close(void) override; + /* Cancel changes, made in insert or edit states of dataset */ + void cancel() override; + /* last insert id */ + int64_t lastinsertid() override; + /* sequence numbers */ + long nextid(const char* seq_name) override; + /* sequence numbers */ + int num_rows() override; + /* interrupt any pending database operation */ + void interrupt() override; + + bool bof() override; + bool eof() override; + void first() override; + void last() override; + void prev() override; + void next() override; + /* Go to record No (starting with 0) */ + bool seek(int pos = 0) override; + + bool dropIndex(const char* table, const char* index) override; +}; +} // namespace dbiplus diff --git a/xbmc/dbwrappers/qry_dat.cpp b/xbmc/dbwrappers/qry_dat.cpp new file mode 100644 index 0000000..1abee3f --- /dev/null +++ b/xbmc/dbwrappers/qry_dat.cpp @@ -0,0 +1,902 @@ +/* + * Copyright (C) 2004, Leo Seib, Hannover + * + * Project: C++ Dynamic Library + * Module: FieldValue class realisation file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +/********************************************************************** + * 2005-03-29 - Minor modifications to allow get_asBool to function on + * on string values that are 1 or 0 + **********************************************************************/ + +#include "qry_dat.h" + +#include <inttypes.h> +#include <stdio.h> +#include <stdlib.h> + +#ifndef __GNUC__ +#pragma warning(disable : 4800) +#pragma warning(disable : 4715) +#endif + +namespace dbiplus +{ + +//Constructors +field_value::field_value() +{ + field_type = ft_String; + is_null = false; +} + +field_value::field_value(const char* s) : str_value(s) +{ + field_type = ft_String; + is_null = false; +} + +field_value::field_value(const bool b) +{ + bool_value = b; + field_type = ft_Boolean; + is_null = false; +} + +field_value::field_value(const char c) +{ + char_value = c; + field_type = ft_Char; + is_null = false; +} + +field_value::field_value(const short s) +{ + short_value = s; + field_type = ft_Short; + is_null = false; +} + +field_value::field_value(const unsigned short us) +{ + ushort_value = us; + field_type = ft_UShort; + is_null = false; +} + +field_value::field_value(const int i) +{ + int_value = i; + field_type = ft_Int; + is_null = false; +} + +field_value::field_value(const unsigned int ui) +{ + uint_value = ui; + field_type = ft_UInt; + is_null = false; +} + +field_value::field_value(const float f) +{ + float_value = f; + field_type = ft_Float; + is_null = false; +} + +field_value::field_value(const double d) +{ + double_value = d; + field_type = ft_Double; + is_null = false; +} + +field_value::field_value(const int64_t i) +{ + int64_value = i; + field_type = ft_Int64; + is_null = false; +} + +field_value::field_value(const field_value& fv) +{ + switch (fv.get_fType()) + { + case ft_String: + { + set_asString(fv.get_asString()); + break; + } + case ft_Boolean: + { + set_asBool(fv.get_asBool()); + break; + } + case ft_Char: + { + set_asChar(fv.get_asChar()); + break; + } + case ft_Short: + { + set_asShort(fv.get_asShort()); + break; + } + case ft_UShort: + { + set_asUShort(fv.get_asUShort()); + break; + } + case ft_Int: + { + set_asInt(fv.get_asInt()); + break; + } + case ft_UInt: + { + set_asUInt(fv.get_asUInt()); + break; + } + case ft_Float: + { + set_asFloat(fv.get_asFloat()); + break; + } + case ft_Double: + { + set_asDouble(fv.get_asDouble()); + break; + } + case ft_Int64: + { + set_asInt64(fv.get_asInt64()); + break; + } + default: + break; + } + is_null = fv.get_isNull(); +} + +//empty destructor +field_value::~field_value() = default; + +//Conversations functions +std::string field_value::get_asString() const +{ + std::string tmp; + switch (field_type) + { + case ft_String: + { + tmp = str_value; + return tmp; + } + case ft_Boolean: + { + if (bool_value) + return tmp = "True"; + else + return tmp = "False"; + } + case ft_Char: + { + return tmp = char_value; + } + case ft_Short: + { + char t[10]; + sprintf(t, "%i", short_value); + return tmp = t; + } + case ft_UShort: + { + char t[10]; + sprintf(t, "%i", ushort_value); + return tmp = t; + } + case ft_Int: + { + char t[12]; + sprintf(t, "%d", int_value); + return tmp = t; + } + case ft_UInt: + { + char t[12]; + sprintf(t, "%u", uint_value); + return tmp = t; + } + case ft_Float: + { + char t[16]; + sprintf(t, "%f", static_cast<double>(float_value)); + return tmp = t; + } + case ft_Double: + { + char t[32]; + sprintf(t, "%f", double_value); + return tmp = t; + } + case ft_Int64: + { + char t[23]; + sprintf(t, "%" PRId64, int64_value); + return tmp = t; + } + default: + return tmp = ""; + } +} + +bool field_value::get_asBool() const +{ + switch (field_type) + { + case ft_String: + { + if (str_value == "True" || str_value == "true" || str_value == "1") + return true; + else + return false; + } + case ft_Boolean: + { + return bool_value; + } + case ft_Char: + { + if (char_value == 'T' || char_value == 't') + return true; + else + return false; + } + case ft_Short: + { + return (bool)short_value; + } + case ft_UShort: + { + return (bool)ushort_value; + } + case ft_Int: + { + return (bool)int_value; + } + case ft_UInt: + { + return (bool)uint_value; + } + case ft_Float: + { + return (bool)float_value; + } + case ft_Double: + { + return (bool)double_value; + } + case ft_Int64: + { + return (bool)int64_value; + } + default: + return false; + } +} + +char field_value::get_asChar() const +{ + switch (field_type) + { + case ft_String: + { + return str_value[0]; + } + case ft_Boolean: + { + if (bool_value) + return 'T'; + else + return 'F'; + } + case ft_Char: + { + return char_value; + } + case ft_Short: + { + char t[10]; + sprintf(t, "%i", short_value); + return t[0]; + } + case ft_UShort: + { + char t[10]; + sprintf(t, "%i", ushort_value); + return t[0]; + } + case ft_Int: + { + char t[12]; + sprintf(t, "%d", int_value); + return t[0]; + } + case ft_UInt: + { + char t[12]; + sprintf(t, "%u", uint_value); + return t[0]; + } + case ft_Float: + { + char t[16]; + sprintf(t, "%f", static_cast<double>(float_value)); + return t[0]; + } + case ft_Double: + { + char t[32]; + sprintf(t, "%f", double_value); + return t[0]; + } + case ft_Int64: + { + char t[24]; + sprintf(t, "%" PRId64, int64_value); + return t[0]; + } + default: + return '\0'; + } +} + +short field_value::get_asShort() const +{ + switch (field_type) + { + case ft_String: + { + return (short)atoi(str_value.c_str()); + } + case ft_Boolean: + { + return (short)bool_value; + } + case ft_Char: + { + return (short)char_value; + } + case ft_Short: + { + return short_value; + } + case ft_UShort: + { + return (short)ushort_value; + } + case ft_Int: + { + return (short)int_value; + } + case ft_UInt: + { + return (short)uint_value; + } + case ft_Float: + { + return (short)float_value; + } + case ft_Double: + { + return (short)double_value; + } + case ft_Int64: + { + return (short)int64_value; + } + default: + return 0; + } +} + +unsigned short field_value::get_asUShort() const +{ + switch (field_type) + { + case ft_String: + { + return (unsigned short)atoi(str_value.c_str()); + } + case ft_Boolean: + { + return (unsigned short)bool_value; + } + case ft_Char: + { + return (unsigned short)char_value; + } + case ft_Short: + { + return (unsigned short)short_value; + } + case ft_UShort: + { + return ushort_value; + } + case ft_Int: + { + return (unsigned short)int_value; + } + case ft_UInt: + { + return (unsigned short)uint_value; + } + case ft_Float: + { + return (unsigned short)float_value; + } + case ft_Double: + { + return (unsigned short)double_value; + } + case ft_Int64: + { + return (unsigned short)int64_value; + } + default: + return 0; + } +} + +int field_value::get_asInt() const +{ + switch (field_type) + { + case ft_String: + { + return atoi(str_value.c_str()); + } + case ft_Boolean: + { + return (int)bool_value; + } + case ft_Char: + { + return (int)char_value; + } + case ft_Short: + { + return (int)short_value; + } + case ft_UShort: + { + return (int)ushort_value; + } + case ft_Int: + { + return int_value; + } + case ft_UInt: + { + return (int)uint_value; + } + case ft_Float: + { + return (int)float_value; + } + case ft_Double: + { + return (int)double_value; + } + case ft_Int64: + { + return (int)int64_value; + } + default: + return 0; + } +} + +unsigned int field_value::get_asUInt() const +{ + switch (field_type) + { + case ft_String: + { + return (unsigned int)atoi(str_value.c_str()); + } + case ft_Boolean: + { + return (unsigned int)bool_value; + } + case ft_Char: + { + return (unsigned int)char_value; + } + case ft_Short: + { + return (unsigned int)short_value; + } + case ft_UShort: + { + return (unsigned int)ushort_value; + } + case ft_Int: + { + return (unsigned int)int_value; + } + case ft_UInt: + { + return uint_value; + } + case ft_Float: + { + return (unsigned int)float_value; + } + case ft_Double: + { + return (unsigned int)double_value; + } + case ft_Int64: + { + return (unsigned int)int64_value; + } + default: + return 0; + } +} + +float field_value::get_asFloat() const +{ + switch (field_type) + { + case ft_String: + { + return (float)atof(str_value.c_str()); + } + case ft_Boolean: + { + return (float)bool_value; + } + case ft_Char: + { + return (float)char_value; + } + case ft_Short: + { + return (float)short_value; + } + case ft_UShort: + { + return (float)ushort_value; + } + case ft_Int: + { + return (float)int_value; + } + case ft_UInt: + { + return (float)uint_value; + } + case ft_Float: + { + return float_value; + } + case ft_Double: + { + return (float)double_value; + } + case ft_Int64: + { + return (float)int64_value; + } + default: + return 0.0; + } +} + +double field_value::get_asDouble() const +{ + switch (field_type) + { + case ft_String: + { + return atof(str_value.c_str()); + } + case ft_Boolean: + { + return (double)bool_value; + } + case ft_Char: + { + return (double)char_value; + } + case ft_Short: + { + return (double)short_value; + } + case ft_UShort: + { + return (double)ushort_value; + } + case ft_Int: + { + return (double)int_value; + } + case ft_UInt: + { + return (double)uint_value; + } + case ft_Float: + { + return (double)float_value; + } + case ft_Double: + { + return (double)double_value; + } + case ft_Int64: + { + return (double)int64_value; + } + default: + return 0.0; + } +} + +int64_t field_value::get_asInt64() const +{ + switch (field_type) + { + case ft_String: + { + return std::atoll(str_value.c_str()); + } + case ft_Boolean: + { + return (int64_t)bool_value; + } + case ft_Char: + { + return (int64_t)char_value; + } + case ft_Short: + { + return (int64_t)short_value; + } + case ft_UShort: + { + return (int64_t)ushort_value; + } + case ft_Int: + { + return (int64_t)int_value; + } + case ft_UInt: + { + return (int64_t)uint_value; + } + case ft_Float: + { + return (int64_t)float_value; + } + case ft_Double: + { + return (int64_t)double_value; + } + case ft_Int64: + { + return int64_value; + } + default: + return 0; + } +} + +field_value& field_value::operator=(const field_value& fv) +{ + if (this == &fv) + return *this; + + is_null = fv.get_isNull(); + + switch (fv.get_fType()) + { + case ft_String: + { + set_asString(fv.get_asString()); + return *this; + break; + } + case ft_Boolean: + { + set_asBool(fv.get_asBool()); + return *this; + break; + } + case ft_Char: + { + set_asChar(fv.get_asChar()); + return *this; + break; + } + case ft_Short: + { + set_asShort(fv.get_asShort()); + return *this; + break; + } + case ft_UShort: + { + set_asUShort(fv.get_asUShort()); + return *this; + break; + } + case ft_Int: + { + set_asInt(fv.get_asInt()); + return *this; + break; + } + case ft_UInt: + { + set_asUInt(fv.get_asUInt()); + return *this; + break; + } + case ft_Float: + { + set_asFloat(fv.get_asFloat()); + return *this; + break; + } + case ft_Double: + { + set_asDouble(fv.get_asDouble()); + return *this; + break; + } + case ft_Int64: + { + set_asInt64(fv.get_asInt64()); + return *this; + break; + } + default: + return *this; + } +} + +//Set functions +void field_value::set_asString(const char* s) +{ + str_value = s; + field_type = ft_String; +} + +void field_value::set_asString(const std::string& s) +{ + str_value = s; + field_type = ft_String; +} + +void field_value::set_asBool(const bool b) +{ + bool_value = b; + field_type = ft_Boolean; +} + +void field_value::set_asChar(const char c) +{ + char_value = c; + field_type = ft_Char; +} + +void field_value::set_asShort(const short s) +{ + short_value = s; + field_type = ft_Short; +} + +void field_value::set_asUShort(const unsigned short us) +{ + ushort_value = us; + field_type = ft_UShort; +} + +void field_value::set_asInt(const int i) +{ + int_value = i; + field_type = ft_Int; +} + +void field_value::set_asUInt(const unsigned int ui) +{ + int_value = ui; + field_type = ft_UInt; +} + +void field_value::set_asFloat(const float f) +{ + float_value = f; + field_type = ft_Float; +} + +void field_value::set_asDouble(const double d) +{ + double_value = d; + field_type = ft_Double; +} + +void field_value::set_asInt64(const int64_t i) +{ + int64_value = i; + field_type = ft_Int64; +} + +fType field_value::get_field_type() +{ + return field_type; +} + +std::string field_value::gft() +{ + std::string tmp; + switch (field_type) + { + case ft_String: + { + tmp.assign("string"); + return tmp; + } + case ft_Boolean: + { + tmp.assign("bool"); + return tmp; + } + case ft_Char: + { + tmp.assign("char"); + return tmp; + } + case ft_Short: + { + tmp.assign("short"); + return tmp; + } + case ft_Int: + { + tmp.assign("int"); + return tmp; + } + case ft_Float: + { + tmp.assign("float"); + return tmp; + } + case ft_Double: + { + tmp.assign("double"); + return tmp; + } + case ft_Int64: + { + tmp.assign("int64"); + return tmp; + } + default: + break; + } + + return tmp; +} + +} // namespace dbiplus diff --git a/xbmc/dbwrappers/qry_dat.h b/xbmc/dbwrappers/qry_dat.h new file mode 100644 index 0000000..150de2d --- /dev/null +++ b/xbmc/dbwrappers/qry_dat.h @@ -0,0 +1,265 @@ +/* + * Copyright (C) 2004, Leo Seib, Hannover + * + * Project:Dataset C++ Dynamic Library + * Module: FieldValue class and result sets classes header file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +#pragma once + +#include <iostream> +#include <stdint.h> +#include <string> +#include <vector> + +namespace dbiplus +{ + +enum fType +{ + ft_String, + ft_Boolean, + ft_Char, + ft_WChar, + ft_WideString, + ft_Short, + ft_UShort, + ft_Int, + ft_UInt, + ft_Float, + ft_Double, + ft_LongDouble, + ft_Int64, + ft_Object +}; + +#ifdef TARGET_WINDOWS_STORE +#pragma pack(push) +#pragma pack(8) +#endif + +class field_value +{ +private: + fType field_type; + std::string str_value; + union + { + bool bool_value; + char char_value; + short short_value; + unsigned short ushort_value; + int int_value; + unsigned int uint_value; + float float_value; + double double_value; + int64_t int64_value; + void* object_value; + }; + + bool is_null; + +public: + field_value(); + explicit field_value(const char* s); + explicit field_value(const bool b); + explicit field_value(const char c); + explicit field_value(const short s); + explicit field_value(const unsigned short us); + explicit field_value(const int l); + explicit field_value(const unsigned int ul); + explicit field_value(const float f); + explicit field_value(const double d); + explicit field_value(const int64_t i); + field_value(const field_value& fv); + ~field_value(); + + fType get_fType() const { return field_type; } + bool get_isNull() const { return is_null; } + std::string get_asString() const; + bool get_asBool() const; + char get_asChar() const; + short get_asShort() const; + unsigned short get_asUShort() const; + int get_asInt() const; + unsigned int get_asUInt() const; + float get_asFloat() const; + double get_asDouble() const; + int64_t get_asInt64() const; + + field_value& operator=(const char* s) + { + set_asString(s); + return *this; + } + field_value& operator=(const std::string& s) + { + set_asString(s); + return *this; + } + field_value& operator=(const bool b) + { + set_asBool(b); + return *this; + } + field_value& operator=(const short s) + { + set_asShort(s); + return *this; + } + field_value& operator=(const unsigned short us) + { + set_asUShort(us); + return *this; + } + field_value& operator=(const int l) + { + set_asInt(l); + return *this; + } + field_value& operator=(const unsigned int l) + { + set_asUInt(l); + return *this; + } + field_value& operator=(const float f) + { + set_asFloat(f); + return *this; + } + field_value& operator=(const double d) + { + set_asDouble(d); + return *this; + } + field_value& operator=(const int64_t i) + { + set_asInt64(i); + return *this; + } + field_value& operator=(const field_value& fv); + + //class ostream; + friend std::ostream& operator<<(std::ostream& os, const field_value& fv) + { + switch (fv.get_fType()) + { + case ft_String: + { + return os << fv.get_asString(); + break; + } + case ft_Boolean: + { + return os << fv.get_asBool(); + break; + } + case ft_Char: + { + return os << fv.get_asChar(); + break; + } + case ft_Short: + { + return os << fv.get_asShort(); + break; + } + case ft_UShort: + { + return os << fv.get_asUShort(); + break; + } + case ft_Int: + { + return os << fv.get_asInt(); + break; + } + case ft_UInt: + { + return os << fv.get_asUInt(); + break; + } + case ft_Float: + { + return os << fv.get_asFloat(); + break; + } + case ft_Double: + { + return os << fv.get_asDouble(); + break; + } + case ft_Int64: + { + return os << fv.get_asInt64(); + break; + } + default: + { + return os; + break; + } + } + } + + void set_isNull() { is_null = true; } + void set_asString(const char* s); + void set_asString(const std::string& s); + void set_asBool(const bool b); + void set_asChar(const char c); + void set_asShort(const short s); + void set_asUShort(const unsigned short us); + void set_asInt(const int l); + void set_asUInt(const unsigned int l); + void set_asFloat(const float f); + void set_asDouble(const double d); + void set_asInt64(const int64_t i); + + fType get_field_type(); + std::string gft(); +}; + +struct field_prop +{ + std::string name; +}; + +struct field +{ + field_prop props; + field_value val; +}; + +typedef std::vector<field> Fields; +typedef std::vector<field_value> sql_record; +typedef std::vector<field_prop> record_prop; +typedef std::vector<sql_record*> query_data; +typedef field_value variant; + +class result_set +{ +public: + result_set() = default; + ~result_set() { clear(); }; + void clear() + { + for (unsigned int i = 0; i < records.size(); i++) + if (records[i]) + delete records[i]; + records.clear(); + record_header.clear(); + }; + + record_prop record_header; + query_data records; +}; + +#ifdef TARGET_WINDOWS_STORE +#pragma pack(pop) +#endif +} // namespace dbiplus diff --git a/xbmc/dbwrappers/sqlitedataset.cpp b/xbmc/dbwrappers/sqlitedataset.cpp new file mode 100644 index 0000000..ef9c927 --- /dev/null +++ b/xbmc/dbwrappers/sqlitedataset.cpp @@ -0,0 +1,1063 @@ +/********************************************************************** + * Copyright (C) 2004, Leo Seib, Hannover + * + * Project:SQLiteDataset C++ Dynamic Library + * Module: SQLiteDataset class realisation file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +#include "sqlitedataset.h" + +#include "utils/StringUtils.h" +#include "utils/URIUtils.h" +#include "utils/XTimeUtils.h" +#include "utils/log.h" + +#include <iostream> +#include <map> +#include <sstream> +#include <string> + +using namespace std::chrono_literals; + +namespace +{ +#define X(VAL) std::make_pair(VAL, #VAL) +//!@todo Remove ifdefs when sqlite version requirement has been bumped to at least 3.26.0 +const std::map<int, const char*> g_SqliteErrorStrings = { + X(SQLITE_OK), + X(SQLITE_ERROR), + X(SQLITE_INTERNAL), + X(SQLITE_PERM), + X(SQLITE_ABORT), + X(SQLITE_BUSY), + X(SQLITE_LOCKED), + X(SQLITE_NOMEM), + X(SQLITE_READONLY), + X(SQLITE_INTERRUPT), + X(SQLITE_IOERR), + X(SQLITE_CORRUPT), + X(SQLITE_NOTFOUND), + X(SQLITE_FULL), + X(SQLITE_CANTOPEN), + X(SQLITE_PROTOCOL), + X(SQLITE_EMPTY), + X(SQLITE_SCHEMA), + X(SQLITE_TOOBIG), + X(SQLITE_CONSTRAINT), + X(SQLITE_MISMATCH), + X(SQLITE_MISUSE), + X(SQLITE_NOLFS), + X(SQLITE_AUTH), + X(SQLITE_FORMAT), + X(SQLITE_RANGE), + X(SQLITE_NOTADB), + X(SQLITE_NOTICE), + X(SQLITE_WARNING), + X(SQLITE_ROW), + X(SQLITE_DONE), +#if defined(SQLITE_ERROR_MISSING_COLLSEQ) + X(SQLITE_ERROR_MISSING_COLLSEQ), +#endif +#if defined(SQLITE_ERROR_RETRY) + X(SQLITE_ERROR_RETRY), +#endif +#if defined(SQLITE_ERROR_SNAPSHOT) + X(SQLITE_ERROR_SNAPSHOT), +#endif + X(SQLITE_IOERR_READ), + X(SQLITE_IOERR_SHORT_READ), + X(SQLITE_IOERR_WRITE), + X(SQLITE_IOERR_FSYNC), + X(SQLITE_IOERR_DIR_FSYNC), + X(SQLITE_IOERR_TRUNCATE), + X(SQLITE_IOERR_FSTAT), + X(SQLITE_IOERR_UNLOCK), + X(SQLITE_IOERR_RDLOCK), + X(SQLITE_IOERR_DELETE), + X(SQLITE_IOERR_BLOCKED), + X(SQLITE_IOERR_NOMEM), + X(SQLITE_IOERR_ACCESS), + X(SQLITE_IOERR_CHECKRESERVEDLOCK), + X(SQLITE_IOERR_LOCK), + X(SQLITE_IOERR_CLOSE), + X(SQLITE_IOERR_DIR_CLOSE), + X(SQLITE_IOERR_SHMOPEN), + X(SQLITE_IOERR_SHMSIZE), + X(SQLITE_IOERR_SHMLOCK), + X(SQLITE_IOERR_SHMMAP), + X(SQLITE_IOERR_SEEK), + X(SQLITE_IOERR_DELETE_NOENT), + X(SQLITE_IOERR_MMAP), + X(SQLITE_IOERR_GETTEMPPATH), + X(SQLITE_IOERR_CONVPATH), +#if defined(SQLITE_IOERR_VNODE) + X(SQLITE_IOERR_VNODE), +#endif +#if defined(SQLITE_IOERR_AUTH) + X(SQLITE_IOERR_AUTH), +#endif +#if defined(SQLITE_IOERR_BEGIN_ATOMIC) + X(SQLITE_IOERR_BEGIN_ATOMIC), +#endif +#if defined(SQLITE_IOERR_COMMIT_ATOMIC) + X(SQLITE_IOERR_COMMIT_ATOMIC), +#endif +#if defined(SQLITE_IOERR_ROLLBACK_ATOMIC) + X(SQLITE_IOERR_ROLLBACK_ATOMIC), +#endif + X(SQLITE_LOCKED_SHAREDCACHE), +#if defined(SQLITE_LOCKED_VTAB) + X(SQLITE_LOCKED_VTAB), +#endif + X(SQLITE_BUSY_RECOVERY), + X(SQLITE_BUSY_SNAPSHOT), + X(SQLITE_CANTOPEN_NOTEMPDIR), + X(SQLITE_CANTOPEN_ISDIR), + X(SQLITE_CANTOPEN_FULLPATH), + X(SQLITE_CANTOPEN_CONVPATH), +#if defined(SQLITE_CANTOPEN_DIRTYWAL) + X(SQLITE_CANTOPEN_DIRTYWAL), +#endif + X(SQLITE_CORRUPT_VTAB), +#if defined(SQLITE_CORRUPT_SEQUENCE) + X(SQLITE_CORRUPT_SEQUENCE), +#endif + X(SQLITE_READONLY_RECOVERY), + X(SQLITE_READONLY_CANTLOCK), + X(SQLITE_READONLY_ROLLBACK), + X(SQLITE_READONLY_DBMOVED), +#if defined(SQLITE_READONLY_CANTINIT) + X(SQLITE_READONLY_CANTINIT), +#endif +#if defined(SQLITE_READONLY_DIRECTORY) + X(SQLITE_READONLY_DIRECTORY), +#endif + X(SQLITE_ABORT_ROLLBACK), + X(SQLITE_CONSTRAINT_CHECK), + X(SQLITE_CONSTRAINT_COMMITHOOK), + X(SQLITE_CONSTRAINT_FOREIGNKEY), + X(SQLITE_CONSTRAINT_FUNCTION), + X(SQLITE_CONSTRAINT_NOTNULL), + X(SQLITE_CONSTRAINT_PRIMARYKEY), + X(SQLITE_CONSTRAINT_TRIGGER), + X(SQLITE_CONSTRAINT_UNIQUE), + X(SQLITE_CONSTRAINT_VTAB), + X(SQLITE_CONSTRAINT_ROWID), + X(SQLITE_NOTICE_RECOVER_WAL), + X(SQLITE_NOTICE_RECOVER_ROLLBACK), + X(SQLITE_WARNING_AUTOINDEX), + X(SQLITE_AUTH_USER), +#if defined(SQLITE_OK_LOAD_PERMANENTLY) + X(SQLITE_OK_LOAD_PERMANENTLY), +#endif +}; +#undef X +} // namespace + +namespace dbiplus +{ +//************* Callback function *************************** + +int callback(void* res_ptr, int ncol, char** result, char** cols) +{ + result_set* r = static_cast<result_set*>(res_ptr); + + if (!r->record_header.size()) + { + r->record_header.reserve(ncol); + for (int i = 0; i < ncol; i++) + { + field_prop header; + header.name = cols[i]; + r->record_header.push_back(header); + } + } + + if (result != NULL) + { + sql_record* rec = new sql_record; + rec->resize(ncol); + for (int i = 0; i < ncol; i++) + { + field_value& v = rec->at(i); + if (result[i] == NULL) + { + v.set_asString(""); + v.set_isNull(); + } + else + { + v.set_asString(result[i]); + } + } + r->records.push_back(rec); + } + return 0; +} + +static int busy_callback(void*, int busyCount) +{ + KODI::TIME::Sleep(100ms); + return 1; +} + +//************* SqliteDatabase implementation *************** + +SqliteDatabase::SqliteDatabase() +{ + + active = false; + _in_transaction = false; // for transaction + + error = "Unknown database error"; //S_NO_CONNECTION; + host = "localhost"; + port = ""; + db = "sqlite.db"; + login = "root"; + passwd = ""; +} + +SqliteDatabase::~SqliteDatabase() +{ + disconnect(); +} + +Dataset* SqliteDatabase::CreateDataset() const +{ + return new SqliteDataset(const_cast<SqliteDatabase*>(this)); +} + +void SqliteDatabase::setHostName(const char* newHost) +{ + host = newHost; + + // hostname is the relative folder to the database, ensure it's slash terminated + if (host[host.length() - 1] != '/' && host[host.length() - 1] != '\\') + host += "/"; + + // ensure the fully qualified path has slashes in the correct direction + if ((host[1] == ':') && isalpha(host[0])) + { + size_t pos = 0; + while ((pos = host.find('/', pos)) != std::string::npos) + host.replace(pos++, 1, "\\"); + } + else + { + size_t pos = 0; + while ((pos = host.find('\\', pos)) != std::string::npos) + host.replace(pos++, 1, "/"); + } +} + +void SqliteDatabase::setDatabase(const char* newDb) +{ + db = newDb; + + // db is the filename for the database, ensure it's not slash prefixed + if (newDb[0] == '/' || newDb[0] == '\\') + db = db.substr(1); + + // ensure the ".db" extension is appended to the end + if (db.find(".db") != (db.length() - 3)) + db += ".db"; +} + +int SqliteDatabase::status(void) +{ + if (active == false) + return DB_CONNECTION_NONE; + return DB_CONNECTION_OK; +} + +int SqliteDatabase::setErr(int err_code, const char* qry) +{ + std::stringstream ss; + ss << "[" << db << "] "; + auto errorIt = g_SqliteErrorStrings.find(err_code); + if (errorIt != g_SqliteErrorStrings.end()) + { + ss << "SQLite error " << errorIt->second; + } + else + { + ss << "Undefined SQLite error " << err_code; + } + if (conn) + ss << " (" << sqlite3_errmsg(conn) << ")"; + ss << "\nQuery: " << qry; + error = ss.str(); + return err_code; +} + +const char* SqliteDatabase::getErrorMsg() +{ + return error.c_str(); +} + +static int AlphaNumericCollation( + void* not_used, int nKey1, const void* pKey1, int nKey2, const void* pKey2) +{ + return StringUtils::AlphaNumericCollation(nKey1, pKey1, nKey2, pKey2); +} + +int SqliteDatabase::connect(bool create) +{ + if (host.empty() || db.empty()) + return DB_CONNECTION_NONE; + + //CLog::Log(LOGDEBUG, "Connecting to sqlite:{}:{}", host, db); + + std::string db_fullpath = URIUtils::AddFileToFolder(host, db); + + try + { + disconnect(); + int flags = SQLITE_OPEN_READWRITE; + if (create) + flags |= SQLITE_OPEN_CREATE; + int errorCode = sqlite3_open_v2(db_fullpath.c_str(), &conn, flags, NULL); + if (create && errorCode == SQLITE_CANTOPEN) + { + CLog::Log(LOGFATAL, "SqliteDatabase: can't open {}", db_fullpath); + throw std::runtime_error("SqliteDatabase: can't open " + db_fullpath); + } + else if (errorCode == SQLITE_OK) + { + sqlite3_extended_result_codes(conn, 1); + sqlite3_busy_handler(conn, busy_callback, NULL); + if (setErr(sqlite3_exec(getHandle(), "PRAGMA empty_result_callbacks=ON", NULL, NULL, NULL), + "PRAGMA empty_result_callbacks=ON") != SQLITE_OK) + { + throw DbErrors("%s", getErrorMsg()); + } + else if (sqlite3_db_readonly(conn, nullptr) == 1) + { + CLog::Log(LOGFATAL, "SqliteDatabase: {} is read only", db_fullpath); + throw std::runtime_error("SqliteDatabase: " + db_fullpath + " is read only"); + } + errorCode = sqlite3_create_collation(conn, "ALPHANUM", SQLITE_UTF8, 0, AlphaNumericCollation); + if (errorCode != SQLITE_OK) + { + CLog::Log(LOGFATAL, "SqliteDatabase: can not register collation"); + throw std::runtime_error("SqliteDatabase: can not register collation " + db_fullpath); + } + active = true; + return DB_CONNECTION_OK; + } + } + catch (const DbErrors&) + { + } + + sqlite3_close(conn); + + return DB_CONNECTION_NONE; +} + +bool SqliteDatabase::exists(void) +{ + bool bRet = false; + if (!active) + return bRet; + result_set res; + char sqlcmd[512]; + + // performing a select all on the sqlite_master will return rows if there are tables + // defined indicating it's not empty and therefore must "exist". + sprintf(sqlcmd, "SELECT * FROM sqlite_master"); + if ((last_err = sqlite3_exec(getHandle(), sqlcmd, &callback, &res, NULL)) == SQLITE_OK) + { + bRet = (res.records.size() > 0); + } + + return bRet; +} + +void SqliteDatabase::disconnect(void) +{ + if (active == false) + return; + sqlite3_close(conn); + active = false; +} + +int SqliteDatabase::create() +{ + return connect(true); +} + +int SqliteDatabase::copy(const char* backup_name) +{ + if (active == false) + throw DbErrors("Can't copy database: no active connection..."); + + CLog::Log(LOGDEBUG, "Copying from {} to {} at {}", db, backup_name, host); + + int rc; + std::string backup_db = backup_name; + + sqlite3* pFile; /* Database connection opened on zFilename */ + sqlite3_backup* pBackup; /* Backup object used to copy data */ + + // + if (backup_name[0] == '/' || backup_name[0] == '\\') + backup_db = backup_db.substr(1); + + // ensure the ".db" extension is appended to the end + if (backup_db.find(".db") != (backup_db.length() - 3)) + backup_db += ".db"; + + std::string backup_path = host + backup_db; + + /* Open the database file identified by zFilename. Exit early if this fails + ** for any reason. */ + rc = sqlite3_open(backup_path.c_str(), &pFile); + if (rc == SQLITE_OK) + { + pBackup = sqlite3_backup_init(pFile, "main", getHandle(), "main"); + + if (pBackup) + { + (void)sqlite3_backup_step(pBackup, -1); + (void)sqlite3_backup_finish(pBackup); + } + + rc = sqlite3_errcode(pFile); + } + + (void)sqlite3_close(pFile); + + if (rc != SQLITE_OK) + throw DbErrors("Can't copy database. (%d)", rc); + + return rc; +} + +int SqliteDatabase::drop_analytics(void) +{ + // SqliteDatabase::copy used a full database copy, so we have a new version + // with all the analytics stuff. We should clean database from everything but data + if (active == false) + throw DbErrors("Can't drop extras database: no active connection..."); + + char sqlcmd[4096]; + result_set res; + + CLog::Log(LOGDEBUG, "Cleaning indexes from database {} at {}", db, host); + sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'index' AND sql IS NOT NULL"); + if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + + for (size_t i = 0; i < res.records.size(); i++) + { + sprintf(sqlcmd, "DROP INDEX '%s'", res.records[i]->at(0).get_asString().c_str()); + if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + } + res.clear(); + + CLog::Log(LOGDEBUG, "Cleaning views from database {} at {}", db, host); + sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'view'"); + if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + + for (size_t i = 0; i < res.records.size(); i++) + { + sprintf(sqlcmd, "DROP VIEW '%s'", res.records[i]->at(0).get_asString().c_str()); + if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + } + res.clear(); + + CLog::Log(LOGDEBUG, "Cleaning triggers from database {} at {}", db, host); + sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'trigger'"); + if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + + for (size_t i = 0; i < res.records.size(); i++) + { + sprintf(sqlcmd, "DROP TRIGGER '%s'", res.records[i]->at(0).get_asString().c_str()); + if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + } + // res would be cleared on destruct + + return DB_COMMAND_OK; +} + +int SqliteDatabase::drop() +{ + if (active == false) + throw DbErrors("Can't drop database: no active connection..."); + disconnect(); + if (!unlink(db.c_str())) + { + throw DbErrors("Can't drop database: can't unlink the file %s,\nError: %s", db.c_str(), + strerror(errno)); + } + return DB_COMMAND_OK; +} + +long SqliteDatabase::nextid(const char* sname) +{ + if (!active) + return DB_UNEXPECTED_RESULT; + int id; /*,nrow,ncol;*/ + result_set res; + char sqlcmd[512]; + snprintf(sqlcmd, sizeof(sqlcmd), "SELECT nextid FROM %s WHERE seq_name = '%s'", + sequence_table.c_str(), sname); + if ((last_err = sqlite3_exec(getHandle(), sqlcmd, &callback, &res, NULL)) != SQLITE_OK) + { + return DB_UNEXPECTED_RESULT; + } + if (res.records.empty()) + { + id = 1; + snprintf(sqlcmd, sizeof(sqlcmd), "INSERT INTO %s (nextid,seq_name) VALUES (%d,'%s')", + sequence_table.c_str(), id, sname); + if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + return id; + } + else + { + id = res.records[0]->at(0).get_asInt() + 1; + snprintf(sqlcmd, sizeof(sqlcmd), "UPDATE %s SET nextid=%d WHERE seq_name = '%s'", + sequence_table.c_str(), id, sname); + if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) + return DB_UNEXPECTED_RESULT; + return id; + } + return DB_UNEXPECTED_RESULT; +} + +// methods for transactions +// --------------------------------------------- +void SqliteDatabase::start_transaction() +{ + if (active) + { + sqlite3_exec(conn, "begin IMMEDIATE", NULL, NULL, NULL); + _in_transaction = true; + } +} + +void SqliteDatabase::commit_transaction() +{ + if (active) + { + sqlite3_exec(conn, "commit", NULL, NULL, NULL); + _in_transaction = false; + } +} + +void SqliteDatabase::rollback_transaction() +{ + if (active) + { + sqlite3_exec(conn, "rollback", NULL, NULL, NULL); + _in_transaction = false; + } +} + +// methods for formatting +// --------------------------------------------- +std::string SqliteDatabase::vprepare(const char* format, va_list args) +{ + std::string strFormat = format; + std::string strResult = ""; + char* p; + size_t pos; + + // %q is the sqlite format string for %s. + // Any bad character, like "'", will be replaced with a proper one + pos = 0; + while ((pos = strFormat.find("%s", pos)) != std::string::npos) + strFormat.replace(pos++, 2, "%q"); + + // the %I64 enhancement is not supported by sqlite3_vmprintf + // must be %ll instead + pos = 0; + while ((pos = strFormat.find("%I64", pos)) != std::string::npos) + strFormat.replace(pos++, 4, "%ll"); + + p = sqlite3_vmprintf(strFormat.c_str(), args); + if (p) + { + strResult = p; + sqlite3_free(p); + } + + // Strip SEPARATOR from all GROUP_CONCAT statements: + // before: GROUP_CONCAT(field SEPARATOR '; ') + // after: GROUP_CONCAT(field, '; ') + // Can not specify separator when have DISTINCT, comma used by default + pos = strResult.find("GROUP_CONCAT("); + while (pos != std::string::npos) + { + size_t pos2 = strResult.find(" SEPARATOR ", pos + 1); + if (pos2 != std::string::npos) + strResult.replace(pos2, 10, ","); + pos = strResult.find("GROUP_CONCAT(", pos + 1); + } + // Replace CONCAT with || to concatenate text fields: + // before: CONCAT(field1, field2, field3) + // after: field1 || field2 || field3 + // Avoid commas in substatements and within single quotes + // before: CONCAT(field1, ',', REPLACE(field2, ',', '-'), field3) + // after: field1 || ',' || REPLACE(field2, ',', '-') || field3 + pos = strResult.find("CONCAT("); + while (pos != std::string::npos) + { + if (pos == 0 || strResult[pos - 1] == ' ') // Not GROUP_CONCAT + { + // Check each char for other bracket or single quote pairs + unsigned int brackets = 1; + bool quoted = false; + size_t index = pos + 7; // start after "CONCAT(" + while (index < strResult.size() && brackets != 0) + { + if (strResult[index] == '(') + brackets++; + else if (strResult[index] == ')') + { + brackets--; + if (brackets == 0) + strResult.erase(index, 1); //Remove closing bracket of CONCAT + } + else if (strResult[index] == '\'') + quoted = !quoted; + else if (strResult[index] == ',' && brackets == 1 && !quoted) + strResult.replace(index, 1, "||"); + index++; + } + strResult.erase(pos, 7); //Remove "CONCAT(" + } + pos = strResult.find("CONCAT(", pos + 1); + } + + return strResult; +} + +//************* SqliteDataset implementation *************** + +SqliteDataset::SqliteDataset() : Dataset() +{ + haveError = false; + db = NULL; + autorefresh = false; +} + +SqliteDataset::SqliteDataset(SqliteDatabase* newDb) : Dataset(newDb) +{ + haveError = false; + db = newDb; + autorefresh = false; +} + +SqliteDataset::~SqliteDataset() +{ +} + +void SqliteDataset::set_autorefresh(bool val) +{ + autorefresh = val; +} + +//--------- protected functions implementation -----------------// + +sqlite3* SqliteDataset::handle() +{ + if (db != NULL) + { + return static_cast<SqliteDatabase*>(db)->getHandle(); + } + else + return NULL; +} + +void SqliteDataset::make_query(StringList& _sql) +{ + std::string query; + if (db == NULL) + throw DbErrors("No Database Connection"); + + try + { + + if (autocommit) + db->start_transaction(); + + for (const std::string& i : _sql) + { + query = i; + char* err = NULL; + Dataset::parse_sql(query); + if (db->setErr(sqlite3_exec(this->handle(), query.c_str(), NULL, NULL, &err), + query.c_str()) != SQLITE_OK) + { + std::string message = db->getErrorMsg(); + if (err) + { + message.append(" ("); + message.append(err); + message.append(")"); + sqlite3_free(err); + } + throw DbErrors("%s", message.c_str()); + } + } // end of for + + if (db->in_transaction() && autocommit) + db->commit_transaction(); + + active = true; + ds_state = dsSelect; + if (autorefresh) + refresh(); + + } // end of try + catch (...) + { + if (db->in_transaction()) + db->rollback_transaction(); + throw; + } +} + +void SqliteDataset::make_insert() +{ + make_query(insert_sql); + last(); +} + +void SqliteDataset::make_edit() +{ + make_query(update_sql); +} + +void SqliteDataset::make_deletion() +{ + make_query(delete_sql); +} + +void SqliteDataset::fill_fields() +{ + //cout <<"rr "<<result.records.size()<<"|" << frecno <<"\n"; + if ((db == NULL) || (result.record_header.empty()) || + (result.records.size() < (unsigned int)frecno)) + return; + + if (fields_object->size() == 0) // Filling columns name + { + const unsigned int ncols = result.record_header.size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + { + (*fields_object)[i].props = result.record_header[i]; + std::string name = result.record_header[i].name; + name2indexMap.insert({str_toLower(name.data()), i}); + } + } + + //Filling result + if (result.records.size() != 0) + { + const sql_record* row = result.records[frecno]; + if (row) + { + const unsigned int ncols = row->size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + (*fields_object)[i].val = row->at(i); + return; + } + } + const unsigned int ncols = result.record_header.size(); + fields_object->resize(ncols); + for (unsigned int i = 0; i < ncols; i++) + (*fields_object)[i].val = ""; +} + +//------------- public functions implementation -----------------// +bool SqliteDataset::dropIndex(const char* table, const char* index) +{ + std::string sql; + + sql = static_cast<SqliteDatabase*>(db)->prepare("DROP INDEX IF EXISTS %s", index); + + return (exec(sql) == SQLITE_OK); +} + +int SqliteDataset::exec(const std::string& sql) +{ + if (!handle()) + throw DbErrors("No Database Connection"); + std::string qry = sql; + int res; + exec_res.clear(); + + // Strip size constraints from indexes (not supported in sqlite) + // + // Example: + // before: CREATE UNIQUE INDEX ixPath ON path ( strPath(255) ) + // after: CREATE UNIQUE INDEX ixPath ON path ( strPath ) + // + // NOTE: unexpected results occur if brackets are not matched + if (qry.find("CREATE UNIQUE INDEX") != std::string::npos || + (qry.find("CREATE INDEX") != std::string::npos)) + { + size_t pos = 0; + size_t pos2 = 0; + + if ((pos = qry.find('(')) != std::string::npos) + { + pos++; + while ((pos = qry.find('(', pos)) != std::string::npos) + { + if ((pos2 = qry.find(')', pos)) != std::string::npos) + { + qry.replace(pos, pos2 - pos + 1, ""); + pos = pos2; + } + } + } + } + // Strip ON table from DROP INDEX statements: + // before: DROP INDEX foo ON table + // after: DROP INDEX foo + size_t pos = qry.find("DROP INDEX "); + if (pos != std::string::npos) + { + pos = qry.find(" ON ", pos + 1); + + if (pos != std::string::npos) + qry = qry.substr(0, pos); + } + + char* errmsg; + if ((res = db->setErr(sqlite3_exec(handle(), qry.c_str(), &callback, &exec_res, &errmsg), + qry.c_str())) == SQLITE_OK) + return res; + else + { + if (errmsg) + { + DbErrors err("%s (%s)", db->getErrorMsg(), errmsg); + sqlite3_free(errmsg); + throw err; + } + else + { + throw DbErrors("%s", db->getErrorMsg()); + } + } +} + +int SqliteDataset::exec() +{ + return exec(sql); +} + +const void* SqliteDataset::getExecRes() +{ + return &exec_res; +} + +bool SqliteDataset::query(const std::string& query) +{ + if (!handle()) + throw DbErrors("No Database Connection"); + const std::string& qry = query; + int fs = qry.find("select"); + int fS = qry.find("SELECT"); + if (!(fs >= 0 || fS >= 0)) + throw DbErrors("MUST be select SQL!"); + + close(); + + sqlite3_stmt* stmt = NULL; + if (db->setErr(sqlite3_prepare_v2(handle(), query.c_str(), -1, &stmt, NULL), query.c_str()) != + SQLITE_OK) + throw DbErrors("%s", db->getErrorMsg()); + + // column headers + const unsigned int numColumns = sqlite3_column_count(stmt); + result.record_header.resize(numColumns); + for (unsigned int i = 0; i < numColumns; i++) + result.record_header[i].name = sqlite3_column_name(stmt, i); + + // returned rows + while (sqlite3_step(stmt) == SQLITE_ROW) + { // have a row of data + sql_record* res = new sql_record; + res->resize(numColumns); + for (unsigned int i = 0; i < numColumns; i++) + { + field_value& v = res->at(i); + switch (sqlite3_column_type(stmt, i)) + { + case SQLITE_INTEGER: + v.set_asInt64(sqlite3_column_int64(stmt, i)); + break; + case SQLITE_FLOAT: + v.set_asDouble(sqlite3_column_double(stmt, i)); + break; + case SQLITE_TEXT: + v.set_asString((const char*)sqlite3_column_text(stmt, i)); + break; + case SQLITE_BLOB: + v.set_asString((const char*)sqlite3_column_text(stmt, i)); + break; + case SQLITE_NULL: + default: + v.set_asString(""); + v.set_isNull(); + break; + } + } + result.records.push_back(res); + } + if (db->setErr(sqlite3_finalize(stmt), query.c_str()) == SQLITE_OK) + { + active = true; + ds_state = dsSelect; + this->first(); + return true; + } + else + { + throw DbErrors("%s", db->getErrorMsg()); + } +} + +void SqliteDataset::open(const std::string& sql) +{ + set_select_sql(sql); + open(); +} + +void SqliteDataset::open() +{ + if (select_sql.size()) + { + query(select_sql); + } + else + { + ds_state = dsInactive; + } +} + +void SqliteDataset::close() +{ + Dataset::close(); + result.clear(); + edit_object->clear(); + fields_object->clear(); + ds_state = dsInactive; + active = false; +} + +void SqliteDataset::cancel() +{ + if ((ds_state == dsInsert) || (ds_state == dsEdit)) + { + if (result.record_header.size()) + ds_state = dsSelect; + else + ds_state = dsInactive; + } +} + +int SqliteDataset::num_rows() +{ + return result.records.size(); +} + +bool SqliteDataset::eof() +{ + return feof; +} + +bool SqliteDataset::bof() +{ + return fbof; +} + +void SqliteDataset::first() +{ + Dataset::first(); + this->fill_fields(); +} + +void SqliteDataset::last() +{ + Dataset::last(); + fill_fields(); +} + +void SqliteDataset::prev(void) +{ + Dataset::prev(); + fill_fields(); +} + +void SqliteDataset::next(void) +{ + Dataset::next(); + if (!eof()) + fill_fields(); +} + +void SqliteDataset::free_row(void) +{ + if (frecno < 0 || (unsigned int)frecno >= result.records.size()) + return; + + sql_record* row = result.records[frecno]; + if (row) + { + delete row; + result.records[frecno] = NULL; + } +} + +bool SqliteDataset::seek(int pos) +{ + if (ds_state == dsSelect) + { + Dataset::seek(pos); + fill_fields(); + return true; + } + return false; +} + +int64_t SqliteDataset::lastinsertid() +{ + if (!handle()) + throw DbErrors("No Database Connection"); + return sqlite3_last_insert_rowid(handle()); +} + +long SqliteDataset::nextid(const char* seq_name) +{ + if (handle()) + return db->nextid(seq_name); + else + return DB_UNEXPECTED_RESULT; +} + +void SqliteDataset::interrupt() +{ + sqlite3_interrupt(handle()); +} +} // namespace dbiplus diff --git a/xbmc/dbwrappers/sqlitedataset.h b/xbmc/dbwrappers/sqlitedataset.h new file mode 100644 index 0000000..4695dfd --- /dev/null +++ b/xbmc/dbwrappers/sqlitedataset.h @@ -0,0 +1,161 @@ +/* + * Copyright (C) 2004, Leo Seib, Hannover + * + * Project:SQLiteDataset C++ Dynamic Library + * Module: SQLiteDataset class header file + * Author: Leo Seib E-Mail: leoseib@web.de + * Begin: 5/04/2002 + * + * SPDX-License-Identifier: MIT + * See LICENSES/README.md for more information. + */ + +#pragma once + +#include "dataset.h" + +#include <stdio.h> + +#include <sqlite3.h> + +namespace dbiplus +{ +/***************** Class SqliteDatabase definition ****************** + + class 'SqliteDatabase' connects with Sqlite-server + +******************************************************************/ +class SqliteDatabase : public Database +{ +protected: + /* connect descriptor */ + sqlite3* conn; + bool _in_transaction; + int last_err; + +public: + /* default constructor */ + SqliteDatabase(); + /* destructor */ + ~SqliteDatabase() override; + + Dataset* CreateDataset() const override; + + /* func. returns connection handle with SQLite-server */ + sqlite3* getHandle() { return conn; } + /* func. returns current status about SQLite-server connection */ + int status() override; + int setErr(int err_code, const char* qry) override; + /* func. returns error message if error occurs */ + const char* getErrorMsg() override; + /* sets a new host name */ + void setHostName(const char* newHost) override; + /* sets a database name */ + void setDatabase(const char* newDb) override; + + /* func. connects to database-server */ + + int connect(bool create) override; + /* func. disconnects from database-server */ + void disconnect() override; + /* func. creates new database */ + int create() override; + /* func. deletes database */ + int drop() override; + /* check if database exists (ie has tables/views defined) */ + bool exists() override; + + /* \brief copy database */ + int copy(const char* backup_name) override; + + /* \brief drop all extra analytics from database */ + int drop_analytics(void) override; + + long nextid(const char* seq_name) override; + + /* virtual methods for transaction */ + + void start_transaction() override; + void commit_transaction() override; + void rollback_transaction() override; + + /* virtual methods for formatting */ + std::string vprepare(const char* format, va_list args) override; + + bool in_transaction() override { return _in_transaction; } +}; + +/***************** Class SqliteDataset definition ******************* + + class 'SqliteDataset' does a query to SQLite-server + +******************************************************************/ + +class SqliteDataset : public Dataset +{ +protected: + sqlite3* handle(); + + /* Makes direct queries to database */ + virtual void make_query(StringList& _sql); + /* Makes direct inserts into database */ + void make_insert() override; + /* Edit SQL */ + void make_edit() override; + /* Delete SQL */ + void make_deletion() override; + + //static int sqlite_callback(void* res_ptr,int ncol, char** result, char** cols); + + /* This function works only with MySQL database + Filling the fields information from select statement */ + void fill_fields() override; + /* Changing field values during dataset navigation */ + virtual void free_row(); // free the memory allocated for the current row + +public: + /* constructor */ + SqliteDataset(); + explicit SqliteDataset(SqliteDatabase* newDb); + + /* destructor */ + ~SqliteDataset() override; + + /* set autorefresh boolean value (if true - refresh the data after edit() +or insert() operations default = false) */ + void set_autorefresh(bool val); + + /* opens a query & then sets a query results */ + void open() override; + void open(const std::string& sql) override; + /* func. executes a query without results to return */ + int exec() override; + int exec(const std::string& sql) override; + const void* getExecRes() override; + /* as open, but with our query exec Sql */ + bool query(const std::string& query) override; + /* func. closes a query */ + void close(void) override; + /* Cancel changes, made in insert or edit states of dataset */ + void cancel() override; + /* last inserted id */ + int64_t lastinsertid() override; + /* sequence numbers */ + long nextid(const char* seq_name) override; + /* sequence numbers */ + int num_rows() override; + /* interrupt any pending database operation */ + void interrupt() override; + + bool bof() override; + bool eof() override; + void first() override; + void last() override; + void prev() override; + void next() override; + /* Go to record No (starting with 0) */ + bool seek(int pos = 0) override; + + bool dropIndex(const char* table, const char* index) override; +}; +} // namespace dbiplus |