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