DatabaseTopicWriter.cpp
Go to the documentation of this file.
1 /*
2 * This file is part of ArmarX.
3 *
4 * ArmarX is free software; you can redistribute it and/or modify
5 * it under the terms of the GNU General Public License version 2 as
6 * published by the Free Software Foundation.
7 *
8 * ArmarX is distributed in the hope that it will be useful, but
9 * WITHOUT ANY WARRANTY; without even the implied warranty of
10 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 * GNU General Public License for more details.
12 *
13 * You should have received a copy of the GNU General Public License
14 * along with this program. If not, see <http://www.gnu.org/licenses/>.
15 *
16 * @package ArmarX
17 * @author Philipp Schmidt( ufedv at student dot kit dot edu)
18 * @date 2016
19 * @copyright http://www.gnu.org/licenses/gpl-2.0.txt
20 * GNU General Public License
21 */
22 #include "DatabaseTopicWriter.h"
23 #include "ArmarXCore/core/logging/Logging.h"
24 
25 namespace armarx
26 {
27 
28  DatabaseTopicWriter::DatabaseTopicWriter(const std::filesystem::path& path) :
29  filepath(path), db(nullptr), stmt(nullptr), database_open(true)
30  {
31  //Overwrite file if it exists
32  std::filesystem::remove(filepath);
33 
34  //Open database
35  int error_code = sqlite3_open(filepath.c_str(), &db);
36  if (error_code != SQLITE_OK)
37  {
38  ARMARX_ERROR_S << "Error opening database: " << sqlite3_errmsg(db);
39  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
40  sqlite3_close(db);
41  database_open = false;
42  return;
43  }
44 
45  //Create data table (will store all log data)
46  char* zErrMsg = nullptr;
47  std::string sql_create_table = "CREATE TABLE TopicData(" \
48  "ID INTEGER PRIMARY KEY," \
49  "TIME BIGINTEGER," \
50  "TOPIC TEXT," \
51  "OPERATIONNAME TEXT," \
52  "DATA BLOB);";
53  error_code = sqlite3_exec(db, sql_create_table.c_str(), nullptr, nullptr, &zErrMsg);
54  if (error_code != SQLITE_OK)
55  {
56  ARMARX_ERROR_S << "Can't create table in database";
57  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
58  ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
59  ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
60  sqlite3_free(zErrMsg);
61  sqlite3_close(db);
62  database_open = false;
63  return;
64  }
65  sqlite3_free(zErrMsg);
66 
67  //Create topic table (will store list of recorded topics)
68  zErrMsg = nullptr;
69  sql_create_table = "CREATE TABLE Topics(TOPIC TEXT);";
70  error_code = sqlite3_exec(db, sql_create_table.c_str(), nullptr, nullptr, &zErrMsg);
71  if (error_code != SQLITE_OK)
72  {
73  ARMARX_ERROR_S << "Can't create table in database";
74  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
75  ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
76  ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
77  sqlite3_free(zErrMsg);
78  sqlite3_close(db);
79  database_open = false;
80  return;
81  }
82  sqlite3_free(zErrMsg);
83 
84  //Prepare a (https://oracle-base.com/blog/2015/01/02/a-sql-or-an-sql/) SQL statement
85  std::string sql_insert_into = "INSERT INTO TopicData (ID, TIME, TOPIC, OPERATIONNAME, DATA) VALUES (?, ?, ?, ?, ?);";
86  error_code = sqlite3_prepare_v2(db, sql_insert_into.c_str(), -1, &stmt, nullptr);
87  if (error_code != SQLITE_OK)
88  {
89  ARMARX_ERROR_S << "Can not prepare sql statement: " << sqlite3_errmsg(db);
90  ARMARX_ERROR_S << "SQL Query: " << sql_create_table;
91  sqlite3_finalize(stmt);
92  sqlite3_close(db);
93  database_open = false;
94  }
95 
96  //Start transaction to improve performance
97  //See http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite for further info
98  zErrMsg = nullptr;
99  error_code = sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, &zErrMsg);
100  if (error_code != SQLITE_OK)
101  {
102  ARMARX_ERROR_S << "Can't start transaction";
103  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
104  ARMARX_ERROR_S << "SQL Query: BEGIN TRANSACTION";
105  ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
106  sqlite3_free(zErrMsg);
107  sqlite3_close(db);
108  database_open = false;
109  return;
110  }
111  sqlite3_free(zErrMsg);
112  }
113 
114  DatabaseTopicWriter::~DatabaseTopicWriter()
115  {
116  if (database_open)
117  {
118  //ARMARX_WARNING << "Ending transaction";
119  //Finish transaction
120  //See http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite for further info
121  char* zErrMsg = nullptr;
122  int error_code = sqlite3_exec(db, "END TRANSACTION", nullptr, nullptr, &zErrMsg);
123  if (error_code != SQLITE_OK)
124  {
125  ARMARX_ERROR_S << "Can't end transaction";
126  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
127  ARMARX_ERROR_S << "SQL Query: END TRANSACTION";
128  ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
129  }
130  sqlite3_free(zErrMsg);
131  }
132  //NULL pointer is a harmless op
133  sqlite3_finalize(stmt);
134  sqlite3_close(db);
135  }
136 
137  std::filesystem::path DatabaseTopicWriter::getFilepath() const
138  {
139  return filepath;
140  }
141 
142  void DatabaseTopicWriter::write(const TopicUtil::TopicData& topicData)
143  {
144  if (!database_open)
145  {
146  //ARMARX_ERROR << "Can not write when database is not open or table is not initialized";
147  return;
148  }
149 
150  //Check if we already have logged this sort of topic (in O(1))
151  if (!topics.count(topicData.topicName))
152  {
153  //Remember for next time
154  topics.insert(topicData.topicName);
155 
156  //Add entry in topic table
157  char* zErrMsg = nullptr;
158  //
159  //I do not know what happens if topicName contains ' chars......
160  //
161  std::string sql_insert_table = "INSERT INTO Topics VALUES ('" + topicData.topicName + "');";
162  int error_code = sqlite3_exec(db, sql_insert_table.c_str(), nullptr, nullptr, &zErrMsg);
163  if (error_code != SQLITE_OK)
164  {
165  ARMARX_ERROR_S << "Can't insert values into database";
166  ARMARX_ERROR_S << "Database Path: " << filepath.c_str();
167  ARMARX_ERROR_S << "SQL Query: " << sql_insert_table;
168  ARMARX_ERROR_S << "SQL Error: " << zErrMsg;
169  sqlite3_free(zErrMsg);
170  sqlite3_finalize(stmt);
171  sqlite3_close(db);
172  database_open = false;
173  return;
174  }
175  sqlite3_free(zErrMsg);
176  }
177 
178  //The following is based on the life-cycle of a sqlite3 statement found here:
179  //https://www.sqlite.org/c3ref/stmt.html
180 
181  //Bind all our parameters
182  //This should be safe, because the caller of this function can't lose ownership
183  //of topicData while we work on a reference of it
184  int error_code_ID = sqlite3_bind_null(stmt, 1);
185  int error_code_time = sqlite3_bind_int64(stmt, 2, (sqlite3_int64) topicData.timestamp.toMicroSeconds());
186  int error_code_topic = sqlite3_bind_text(stmt, 3, topicData.topicName.c_str(), -1, SQLITE_STATIC);
187  int error_code_operation = sqlite3_bind_text(stmt, 4, topicData.operationName.c_str(), -1, SQLITE_STATIC);
188  int error_code_data = sqlite3_bind_blob(stmt, 5, topicData.inParams.data(), topicData.inParams.size(), SQLITE_STATIC);
189 
190  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)
191  {
192  ARMARX_ERROR_S << "Can not bind data: " << sqlite3_errmsg(db);
193  sqlite3_finalize(stmt);
194  sqlite3_close(db);
195  database_open = false;
196  return;
197  }
198 
199  //Execute our bound sql query
200  int error_code_step = sqlite3_step(stmt);
201  if (error_code_step != SQLITE_DONE)
202  {
203  ARMARX_ERROR_S << "Can not execute sql statement: " << sqlite3_errmsg(db);
204  sqlite3_finalize(stmt);
205  sqlite3_close(db);
206  database_open = false;
207  return;
208  }
209 
210  //Resetting and unbinding the statement
211  //No need to check the error codes, possible errors only occur earlier during binding and step
212  //Clearing the bindings is not necessary, better safe than sorry though
213  sqlite3_reset(stmt);
214  sqlite3_clear_bindings(stmt);
215  }
216 
217 } // namespace armarx
armarx::DatabaseTopicWriter::write
void write(const TopicUtil::TopicData &topicData) override
Definition: DatabaseTopicWriter.cpp:142
armarx::TopicUtil::TopicData::inParams
std::vector< Ice::Byte > inParams
Definition: TopicUtil.h:45
armarx::TopicUtil::TopicData
Definition: TopicUtil.h:34
armarx::TopicUtil::TopicData::operationName
std::string operationName
Definition: TopicUtil.h:44
ARMARX_ERROR_S
#define ARMARX_ERROR_S
Definition: Logging.h:209
armarx::DatabaseTopicWriter::DatabaseTopicWriter
DatabaseTopicWriter(const std::filesystem::path &path)
Definition: DatabaseTopicWriter.cpp:28
armarx::TopicUtil::TopicData::topicName
std::string topicName
Definition: TopicUtil.h:42
armarx::DatabaseTopicWriter::getFilepath
std::filesystem::path getFilepath() const
Definition: DatabaseTopicWriter.cpp:137
armarx::TopicUtil::TopicData::timestamp
IceUtil::Time timestamp
Definition: TopicUtil.h:43
Logging.h
DatabaseTopicWriter.h
armarx
This file offers overloads of toIce() and fromIce() functions for STL container types.
Definition: ArmarXTimeserver.cpp:28
armarx::DatabaseTopicWriter::~DatabaseTopicWriter
~DatabaseTopicWriter() override
Definition: DatabaseTopicWriter.cpp:114