DatabaseTopicWriter.cpp

Go to the documentation of this file.

/*
* This file is part of ArmarX.
*
* ArmarX is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License version 2 as
* published by the Free Software Foundation.
*
* ArmarX is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* @package    ArmarX
* @author     Philipp Schmidt( ufedv at student dot kit dot edu)
* @date       2016
* @copyright  http://www.gnu.org/licenses/gpl-2.0.txt
*             GNU General Public License
*/
#include "DatabaseTopicWriter.h"
 
#include "ArmarXCore/core/logging/Logging.h"
 
namespace armarx
{
 
    DatabaseTopicWriter::DatabaseTopicWriter(const std::filesystem::path& path) :
        filepath(path), db(nullptr), stmt(nullptr), database_open(true)
    {
        //Overwrite file if it exists
        std::filesystem::remove(filepath);
 
        //Open database
        int error_code = sqlite3_open(filepath.c_str(), &db);
        if (error_code != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Error opening database: " << sqlite3_errmsg(db);
            ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
            sqlite3_close(db);
            database_open = false;
            return;
        }
 
        //Create data table (will store all log data)
        char* zErrMsg = nullptr;
        std::string sql_create_table = "CREATE TABLE TopicData("
                                       "ID             INTEGER PRIMARY KEY,"
                                       "TIME           BIGINTEGER,"
                                       "TOPIC          TEXT,"
                                       "OPERATIONNAME  TEXT,"
                                       "DATA           BLOB);";
        error_code = sqlite3_exec(db, sql_create_table.c_str(), nullptr, nullptr, &zErrMsg);
        if (error_code != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Can't create table in database";
            ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
            ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
            ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
            sqlite3_free(zErrMsg);
            sqlite3_close(db);
            database_open = false;
            return;
        }
        sqlite3_free(zErrMsg);
 
        //Create topic table (will store list of recorded topics)
        zErrMsg = nullptr;
        sql_create_table = "CREATE TABLE Topics(TOPIC TEXT);";
        error_code = sqlite3_exec(db, sql_create_table.c_str(), nullptr, nullptr, &zErrMsg);
        if (error_code != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Can't create table in database";
            ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
            ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
            ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
            sqlite3_free(zErrMsg);
            sqlite3_close(db);
            database_open = false;
            return;
        }
        sqlite3_free(zErrMsg);
 
        //Prepare a (https://oracle-base.com/blog/2015/01/02/a-sql-or-an-sql/) SQL statement
        std::string sql_insert_into =
            "INSERT INTO TopicData (ID, TIME, TOPIC, OPERATIONNAME, DATA) VALUES (?, ?, ?, ?, ?);";
        error_code = sqlite3_prepare_v2(db, sql_insert_into.c_str(), -1, &stmt, nullptr);
        if (error_code != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Can not prepare sql statement: " << sqlite3_errmsg(db);
            ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            database_open = false;
        }
 
        //Start transaction to improve performance
        //See http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite for further info
        zErrMsg = nullptr;
        error_code = sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, &zErrMsg);
        if (error_code != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Can't start transaction";
            ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
            ARMARX_ERROR_S << "SQL Query: BEGIN TRANSACTION";
            ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
            sqlite3_free(zErrMsg);
            sqlite3_close(db);
            database_open = false;
            return;
        }
        sqlite3_free(zErrMsg);
    }
 
    DatabaseTopicWriter::~DatabaseTopicWriter()
    {
        if (database_open)
        {
            //ARMARX_WARNING << "Ending transaction";
            //Finish transaction
            //See http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite for further info
            char* zErrMsg = nullptr;
            int error_code = sqlite3_exec(db, "END TRANSACTION", nullptr, nullptr, &zErrMsg);
            if (error_code != SQLITE_OK)
            {
                ARMARX_ERROR_S << "Can't end transaction";
                ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
                ARMARX_ERROR_S << "SQL Query: END TRANSACTION";
                ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
            }
            sqlite3_free(zErrMsg);
        }
        //NULL pointer is a harmless op
        sqlite3_finalize(stmt);
        sqlite3_close(db);
    }
 
    std::filesystem::path
    DatabaseTopicWriter::getFilepath() const
    {
        return filepath;
    }
 
    void
    DatabaseTopicWriter::write(const TopicUtil::TopicData& topicData)
    {
        if (!database_open)
        {
            //ARMARX_ERROR << "Can not write when database is not open or table is not initialized";
            return;
        }
 
        //Check if we already have logged this sort of topic (in O(1))
        if (!topics.count(topicData.topicName))
        {
            //Remember for next time
            topics.insert(topicData.topicName);
 
            //Add entry in topic table
            char* zErrMsg = nullptr;
            //
            //I do not know what happens if topicName contains ' chars......
            //
            std::string sql_insert_table =
                "INSERT INTO Topics VALUES ('" + topicData.topicName + "');";
            int error_code = sqlite3_exec(db, sql_insert_table.c_str(), nullptr, nullptr, &zErrMsg);
            if (error_code != SQLITE_OK)
            {
                ARMARX_ERROR_S << "Can't insert values into database";
                ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
                ARMARX_ERROR_S << "SQL Query: " << sql_insert_table;
                ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
                sqlite3_free(zErrMsg);
                sqlite3_finalize(stmt);
                sqlite3_close(db);
                database_open = false;
                return;
            }
            sqlite3_free(zErrMsg);
        }
 
        //The following is based on the life-cycle of a sqlite3 statement found here:
        //https://www.sqlite.org/c3ref/stmt.html
 
        //Bind all our parameters
        //This should be safe, because the caller of this function can't lose ownership
        //of topicData while we work on a reference of it
        int error_code_ID = sqlite3_bind_null(stmt, 1);
        int error_code_time =
            sqlite3_bind_int64(stmt, 2, (sqlite3_int64)topicData.timestamp.toMicroSeconds());
        int error_code_topic =
            sqlite3_bind_text(stmt, 3, topicData.topicName.c_str(), -1, SQLITE_STATIC);
        int error_code_operation =
            sqlite3_bind_text(stmt, 4, topicData.operationName.c_str(), -1, SQLITE_STATIC);
        int error_code_data = sqlite3_bind_blob(
            stmt, 5, topicData.inParams.data(), topicData.inParams.size(), SQLITE_STATIC);
 
        if (error_code_ID != SQLITE_OK || error_code_time != SQLITE_OK ||
            error_code_topic != SQLITE_OK || error_code_operation != SQLITE_OK ||
            error_code_data != SQLITE_OK)
        {
            ARMARX_ERROR_S << "Can not bind data: " << sqlite3_errmsg(db);
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            database_open = false;
            return;
        }
 
        //Execute our bound sql query
        int error_code_step = sqlite3_step(stmt);
        if (error_code_step != SQLITE_DONE)
        {
            ARMARX_ERROR_S << "Can not execute sql statement: " << sqlite3_errmsg(db);
            sqlite3_finalize(stmt);
            sqlite3_close(db);
            database_open = false;
            return;
        }
 
        //Resetting and unbinding the statement
        //No need to check the error codes, possible errors only occur earlier during binding and step
        //Clearing the bindings is not necessary, better safe than sorry though
        sqlite3_reset(stmt);
        sqlite3_clear_bindings(stmt);
    }
 
} // namespace armarx