/** * @file CSQLiteStatement.h * @ingroup CSQLite * @brief A prepared SQLite Statement is a compiled SQL query ready to be executed, pointing to a row of result. * 修改自SQLiteCPP的Statement类。 1. Ptr类改用_Statemen和QSharedPointer<_Statement>实现 2. 增加errCode和errMsg保存错误信息 3. 增加不同数据类型的tryBind()，不抛出异常，出错生成errCode和errMsg 4. Bind类函数改成用模板函数实现index版和列名称版两套函数 5. Column相关的函数去掉了checkRow()/checkIndex(aIndex)判断和抛出异常，由调用者确保有记录并且index不超界; 6. bind一个u32型数据内部转成int，适用于不需要排序的u32列。如果需要排序，强制转成int64再bind */ #pragma once #include "CSQLiteException.h" #include "CSQLiteColumn.h" #include // For INT_MAX #include #include "_Statement.h" #include "CSQLiteColumn.h" #include "GdCPP_Exports.h" #pragma comment(lib, "sqlite3.lib") // Forward declaration class CSQLiteDB; /** * @brief RAII encapsulation of a prepared SQLite Statement. * * A Statement is a compiled SQL query ready to be executed step by step * to provide results one row at a time. * * Resource Acquisition Is Initialization (RAII) means that the Statement * is compiled in the constructor and finalized in the destructor, so that there is * no need to worry about memory management or the validity of the underlying SQLite Statement. * * Thread-safety: a Statement object shall not be shared by multiple threads, because : * 1) in the SQLite "Thread Safe" mode, "SQLite can be safely used by multiple threads * provided that no single database connection is used simultaneously in two or more threads." * 2) the SQLite "Serialized" mode is not supported by SQLiteC++, * because of the way it shares the underling SQLite precompiled statement * in a custom shared pointer (See the inner class "Statement::Ptr"). */ class GDCPP_API CSQLiteStatement { // friend class CSQLiteColumn; // For access to inner member public: /** * @brief Compile and register the SQL query for the provided SQLite Database Connection * * @param[in] aDatabase the SQLite Database Connection * @param[in] apQuery an UTF-8 encoded query string * * Exception is thrown in case of error, then the Statement object is NOT constructed. */ CSQLiteStatement(CSQLiteDB& aDatabase, const char* apQuery); /** * @brief Compile and register the SQL query for the provided SQLite Database Connection * * @param[in] aDatabase the SQLite Database Connection * @param[in] aQuery an UTF-8 encoded query string * * Exception is thrown in case of error, then the Statement object is NOT constructed. */ CSQLiteStatement(CSQLiteDB& aDatabase, const std::string& aQuery); /** * @brief Move an SQLite statement. * * @param[in] aStatement Statement to move */ CSQLiteStatement(CSQLiteStatement&& aStatement) noexcept; /// Finalize and unregister the SQL query from the SQLite Database Connection. ~CSQLiteStatement(); /// Reset the statement to make it ready for a new execution. Throws an exception on error. inline void reset() { const int ret = tryReset(); check(ret); } /// Reset the statement. Returns the sqlite result code instead of throwing an exception on error. inline int tryReset() noexcept { mbHasRow = false; mbDone = false; return sqlite3_reset(*mStmtPtr); } /** * @brief Clears away all the bindings of a prepared statement. * * Contrary to the intuition of many, reset() does not reset the bindings on a prepared statement. * Use this routine to reset all parameters to NULL. */ inline void clearBindings() // throw(SQLite::Exception) { const int ret = sqlite3_clear_bindings(*mStmtPtr); check(ret); } //////////////////////////////////////////////////////////////////////////// // Bind a value to a parameter of the SQL statement, // in the form "?" (unnamed), "?NNN", ":VVV", "@VVV" or "$VVV". // // Can use the parameter index, starting from "1", to the higher NNN value, // or the complete parameter name "?NNN", ":VVV", "@VVV" or "$VVV" // (prefixed with the corresponding sign "?", ":", "@" or "$") // // Note that for text and blob values, the SQLITE_TRANSIENT flag is used, // which tell the sqlite library to make its own copy of the data before the bind() call returns. // This choice is done to prevent any common misuses, like passing a pointer to a // dynamic allocated and temporary variable (a std::string for instance). // This is under-optimized for static data (a static text define in code) // as well as for dynamic allocated buffer which could be transfer to sqlite // instead of being copied. // => if you know what you are doing, use bindNoCopy() instead of bind() /** * @brief Bind an int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) */ inline int tryBind(const int aIndex, const int aValue) { errCode = sqlite3_bind_int(*mStmtPtr, aIndex, aValue); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with int value "; errMsg += std::to_string(aValue); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a 32bits unsigned int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) */ inline int tryBind(const int aIndex, const unsigned aValue) { errCode = sqlite3_bind_int(*mStmtPtr, aIndex, int(aValue)); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with uint value "; errMsg += std::to_string(aValue); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a 64bits int value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) */ inline int tryBind(const int aIndex, const int64_t aValue) { errCode = sqlite3_bind_int64(*mStmtPtr, aIndex, aValue); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with int64_t value "; errMsg += std::to_string(aValue); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a double (64bits float) value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) */ inline int tryBind(const int aIndex, const double aValue) { errCode = sqlite3_bind_double(*mStmtPtr, aIndex, aValue); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with double value "; errMsg += std::to_string(aValue); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a string value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * @note Uses the SQLITE_TRANSIENT flag, making a copy of the data, for SQLite internal use */ inline int tryBind(const int aIndex, const std::string& aValue) { errCode = sqlite3_bind_text(*mStmtPtr, aIndex, aValue.c_str(), static_cast(aValue.size()), SQLITE_TRANSIENT); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with string "; errMsg += aValue; errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a text value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * @note Uses the SQLITE_TRANSIENT flag, making a copy of the data, for SQLite internal use */ inline int tryBind(const int aIndex, const char* apValue) { errCode = sqlite3_bind_text(*mStmtPtr, aIndex, apValue, -1, SQLITE_TRANSIENT); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with const char * "; errMsg += apValue; errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a binary blob value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * @note Uses the SQLITE_TRANSIENT flag, making a copy of the data, for SQLite internal use */ inline int tryBind(const int aIndex, const void* apValue, const int aSize) { errCode = sqlite3_bind_blob(*mStmtPtr, aIndex, apValue, aSize, SQLITE_TRANSIENT); if (SQLITE_OK != errCode) { errMsg ="Fail to bind index "; errMsg += std::to_string(aIndex); errMsg += " with blob "; // errMsg += std::to_string(aValue); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a string value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1). * * The string can contain null characters as it is binded using its size. * * @warning Uses the SQLITE_STATIC flag, avoiding a copy of the data. The string must remains unchanged while executing the statement. */ int tryBindNoCopy(const int aIndex, const std::string& aValue) { errCode = sqlite3_bind_text(*mStmtPtr, aIndex, aValue.c_str(), static_cast(aValue.size()), SQLITE_STATIC); if (SQLITE_OK != errCode) { errMsg ="Fail to bindNoCopy index "; errMsg += std::to_string(aIndex); errMsg += " with string "; errMsg += aValue; errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a text value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * Main usage is with null-terminated literal text (aka in code static strings) * * @warning Uses the SQLITE_STATIC flag, avoiding a copy of the data. The string must remains unchanged while executing the statement. */ int tryBindNoCopy(const int aIndex, const char* apValue) { errCode = sqlite3_bind_text(*mStmtPtr, aIndex, apValue, -1, SQLITE_STATIC); if (SQLITE_OK != errCode) { errMsg ="Fail to bindNoCopy index "; errMsg += std::to_string(aIndex); errMsg += " with const char*: "; errMsg += apValue; errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } /** * @brief Bind a binary blob value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * @warning Uses the SQLITE_STATIC flag, avoiding a copy of the data. The string must remains unchanged while executing the statement. */ int tryBindNoCopy(const int aIndex, const void* apValue, const int aSize) { errCode = sqlite3_bind_blob(*mStmtPtr, aIndex, apValue, aSize, SQLITE_STATIC); if (SQLITE_OK != errCode) { errMsg ="Fail to bindNoCopy blob to index "; errMsg += std::to_string(aIndex); errMsg += " with void *, size "; errMsg += std::to_string(aSize); errMsg += ", errCode="; errMsg += std::to_string(errCode); } return errCode; } template inline void bind(const int aIndex, T aValue) { const int ret = tryBind(aIndex, aValue); if (SQLITE_OK != ret) { throw CSQLiteException(errMsg); } } template inline void bindNoCopy(const int aIndex, T aValue) { const int ret = tryBindNoCopy(aIndex, aValue); if (SQLITE_OK != ret) { throw CSQLiteException(errMsg); } } template inline void bind(const char* apName, T aValue) { const int index = sqlite3_bind_parameter_index(*mStmtPtr, apName); if(index==0){ std::string errstr("sqlite3_bind_parameter_index fail for "); errstr += apName; throw CSQLiteException(errstr); } bind(index, aValue); } template inline void bindNoCopy(const char* apName, T aValue) { const int index = sqlite3_bind_parameter_index(*mStmtPtr, apName); if(index==0){ std::string errstr("sqlite3_bind_parameter_index fail for "); errstr += apName; throw CSQLiteException(errstr); } bind(index, aValue); } template inline void bind(const std::string& aName, T aValue) { bind(aName.c_str(), aValue); } /** * @brief Bind a NULL value to a parameter "?", "?NNN", ":VVV", "@VVV" or "$VVV" in the SQL prepared statement (aIndex >= 1) * * @see clearBindings() to set all bound parameters to NULL. */ inline void bindNull(const int aIndex) { const int ret = sqlite3_bind_null(*mStmtPtr, aIndex); check(ret); } inline void bindNull(const char* apName) { const int index = sqlite3_bind_parameter_index(*mStmtPtr, apName); if(index==0){ std::string errstr("sqlite3_bind_parameter_index fail for "); errstr += apName; throw CSQLiteException(errstr); } bindNull(index); } inline void bindNull(const std::string& aName) { bindNull(aName.c_str()); } //////////////////////////////////////////////////////////////////////////// /** * @brief Execute a step of the prepared query to fetch one row of results. * * While true is returned, a row of results is available, and can be accessed * thru the getColumn() method * * @see exec() execute a one-step prepared statement with no expected result * @see tryExecuteStep() try to execute a step of the prepared query to fetch one row of results, returning the sqlite result code. * @see CSQLiteDB::exec() is a shortcut to execute one or multiple statements without results * * @return - true (SQLITE_ROW) if there is another row ready : you can call getColumn(N) to get it * then you have to call executeStep() again to fetch more rows until the query is finished * - false (SQLITE_DONE) if the query has finished executing : there is no (more) row of result * (case of a query with no result, or after N rows fetched successfully) * * @throw SQLite::Exception in case of error */ bool executeStep(); /** * @brief Try to execute a step of the prepared query to fetch one row of results, returning the sqlite result code. * * * * @see exec() execute a one-step prepared statement with no expected result * @see executeStep() execute a step of the prepared query to fetch one row of results * @see CSQLiteDB::exec() is a shortcut to execute one or multiple statements without results * * @return the sqlite result code. */ int tryExecuteStep() noexcept; /** * @brief Execute a one-step query with no expected result. * * This method is useful for any kind of statements other than the Data Query Language (DQL) "SELECT" : * - Data Definition Language (DDL) statements "CREATE", "ALTER" and "DROP" * - Data Manipulation Language (DML) statements "INSERT", "UPDATE" and "DELETE" * - Data Control Language (DCL) statements "GRANT", "REVOKE", "COMMIT" and "ROLLBACK" * * It is similar to CSQLiteDB::exec(), but using a precompiled statement, it adds : * - the ability to bind() arguments to it (best way to insert data), * - reusing it allows for better performances (efficient for multiple insertion). * * @see executeStep() execute a step of the prepared query to fetch one row of results * @see tryExecuteStep() try to execute a step of the prepared query to fetch one row of results, returning the sqlite result code. * @see CSQLiteDB::exec() is a shortcut to execute one or multiple statements without results * * @return number of row modified by this SQL statement (INSERT, UPDATE or DELETE) * * @throw SQLite::Exception in case of error, or if row of results are returned ! */ int exec(); //////////////////////////////////////////////////////////////////////////// /** * @brief Return a copy of the column data specified by its index * * Can be used to access the data of the current row of result when applicable, * while the executeStep() method returns true. * * Throw an exception if there is no row to return a Column from: * - if provided index is out of bound * - before any executeStep() call * - after the last executeStep() returned false * - after a reset() call * * Throw an exception if the specified index is out of the [0, getColumnCount()) range. * * @param[in] aIndex Index of the column, starting at 0 * * @note This method is not const, reflecting the fact that the returned Column object will * share the ownership of the underlying sqlite3_stmt. * * @warning The resulting Column object must not be memorized "as-is". * Is is only a wrapper around the current result row, so it is only valid * while the row from the Statement remains valid, that is only until next executeStep() call. * Thus, you should instead extract immediately its data (getInt(), getText()...) * and use or copy this data for any later usage. * * 注意调用者必须保证有一行数据，且index在[0, getColumnCount())范围内. * 本类只想做简单的封装，不想到处抛出异常 */ inline CSQLiteColumn getColumn(const int aIndex) { // checkRow(); // checkIndex(aIndex); // Share the Statement Object handle with the new Column created return CSQLiteColumn(mStmtPtr, aIndex); } /** * @brief Return a copy of the column data specified by its column name (less efficient than using an index) * * Can be used to access the data of the current row of result when applicable, * while the executeStep() method returns true. * * Throw an exception if there is no row to return a Column from : * - if provided name is not one of the aliased column names * - before any executeStep() call * - after the last executeStep() returned false * - after a reset() call * * Throw an exception if the specified name is not an on of the aliased name of the columns in the result. * * @param[in] apName Aliased name of the column, that is, the named specified in the query (not the original name) * * @note Uses a map of column names to indexes, build on first call. * * @note This method is not const, reflecting the fact that the returned Column object will * share the ownership of the underlying sqlite3_stmt. * * @warning The resulting Column object must not be memorized "as-is". * Is is only a wrapper around the current result row, so it is only valid * while the row from the Statement remains valid, that is only until next executeStep() call. * Thus, you should instead extract immediately its data (getInt(), getText()...) * and use or copy this data for any later usage. * * Throw an exception if the specified name is not one of the aliased name of the columns in the result. * 注意调用者必须保证有一行数据，且名称是有效的. * 本类只想做简单的封装，不想到处抛出异常 */ inline CSQLiteColumn getColumn(const char* apName) { // checkRow(); const int index = getColumnIndex(apName); //注意名字不对会抛异常 // Share the Statement Object handle with the new Column created return CSQLiteColumn(mStmtPtr, index); } #if __cplusplus >= 201402L || (defined(_MSC_VER) && _MSC_VER >= 1900) /** * @brief Return an instance of T constructed from copies of the first N columns * * Can be used to access the data of the current row of result when applicable, * while the executeStep() method returns true. * * Throw an exception if there is no row to return a Column from: * - if provided column count is out of bound * - before any executeStep() call * - after the last executeStep() returned false * - after a reset() call * * Throw an exception if the specified column count is out of the [0, getColumnCount()) range. * * @tparam T Object type to construct * @tparam N Number of columns * * @note Requires std=C++14 */ template T getColumns(); private: /** * @brief Helper function used by getColumns to expand an integer_sequence used to generate * the required Column objects */ template T getColumns(const std::integer_sequence); public: #endif /** * @brief Test if the column value is NULL * * @param[in] aIndex Index of the column, starting at 0 * * @return true if the column value is NULL * * 注意调用者必须保证有一行数据，且index在[0, getColumnCount())范围内，避免抛出异常 */ bool isColumnNull(const int aIndex) const { // checkRow(); // checkIndex(aIndex); return (SQLITE_NULL == sqlite3_column_type(*mStmtPtr, aIndex)); } /** * @brief Test if the column value is NULL * * @param[in] apName Aliased name of the column, that is, the named specified in the query (not the original name) * * @return true if the column value is NULL * * 注意调用者必须保证有一行数据，且列名称是有效的，避免抛出异常 */ bool isColumnNull(const char* apName) const { // checkRow(); const int index = getColumnIndex(apName); // return (SQLITE_NULL == sqlite3_column_type(*mStmtPtr, index)); } /** * @brief Return a pointer to the named assigned to the specified result column (potentially aliased) * * @param[in] aIndex Index of the column in the range [0, getColumnCount()). * * @see getColumnOriginName() to get original column name (not aliased) * * Throw an exception if the specified index is out of the [0, getColumnCount()) range. */ const char* getColumnName(const int aIndex) const; #ifdef SQLITE_ENABLE_COLUMN_METADATA /** * @brief Return a pointer to the table column name that is the origin of the specified result column * * Require definition of the SQLITE_ENABLE_COLUMN_METADATA preprocessor macro : * - when building the SQLite library itself (which is the case for the Debian libsqlite3 binary for instance), * - and also when compiling this wrapper. * * Throw an exception if the specified index is out of the [0, getColumnCount()) range. */ const char* getColumnOriginName(const int aIndex) const; #endif /** * @brief Return the index of the specified (potentially aliased) column name * * @param[in] apName Aliased name of the column, that is, the named specified in the query (not the original name) * * @note Uses a map of column names to indexes, build on first call. * * Throw an exception if the specified name is not known. */ int getColumnIndex(const char* apName) const; //////////////////////////////////////////////////////////////////////////// /// Return the UTF-8 SQL Query. inline const std::string& getQuery() const { return mQuery; } // Return a UTF-8 string containing the SQL text of prepared statement with bound parameters expanded. std::string getExpandedSQL(); /// Return the number of columns in the result set returned by the prepared statement inline int getColumnCount() const { return mColumnCount; } /// true when a row has been fetched with executeStep() inline bool hasRow() const { return mbHasRow; } /// true when the last executeStep() had no more row to fetch inline bool isDone() const { return mbDone; } inline bool isValid() { return bool(mStmtPtr); } /// Return the number of bind parameters in the statement int getBindParameterCount() const noexcept; /// Return the numeric result code for the most recent failed API call (if any). int getErrorCode() const noexcept; // nothrow /// Return the extended numeric result code for the most recent failed API call (if any). int getExtendedErrorCode() const noexcept; // nothrow /// Return UTF-8 encoded English language explanation of the most recent failed API call (if any). const char* getErrorMsg() const noexcept; // nothrow int finalize() noexcept { return sqlite3_finalize(*mStmtPtr); } private: /// @{ CSQLiteStatement must be non-copyable CSQLiteStatement(const CSQLiteStatement&); CSQLiteStatement& operator=(const CSQLiteStatement&); /// @} /** * @brief Check if a return code equals SQLITE_OK, else throw a SQLite::Exception with the SQLite error message * * @param[in] aRet SQLite return code to test against the SQLITE_OK expected value */ inline void check(const int aRet) const { if (SQLITE_OK != aRet) { throw CSQLiteException(*mStmtPtr, aRet); } } /** * @brief Check if there is a row of result returned by executeStep(), else throw a SQLite::Exception. */ inline void checkRow() const { if (false == mbHasRow) { throw CSQLiteException("No row to get a column from. executeStep() was not called, or returned false."); } } /** * @brief Check if there is a Column index is in the range of columns in the result. */ inline void checkIndex(const int aIndex) const { if ((aIndex < 0) || (aIndex >= mColumnCount)) { throw CSQLiteException("Column index out of range."); } } private: /// Map of columns index by name (mutable so getColumnIndex can be const) typedef std::map TColumnNames; private: std::string mQuery; //!< UTF-8 SQL Query _StatementPtr mStmtPtr; //!< Shared Pointer to the prepared SQLite Statement Object int mColumnCount; //!< Number of columns in the result of the prepared statement mutable TColumnNames mColumnNames; //!< Map of columns index by name (mutable so getColumnIndex can be const) bool mbHasRow; //!< true when a row has been fetched with executeStep() bool mbDone; //!< true when the last executeStep() had no more row to fetch public: int errCode; std::string errMsg; };