TkN 2.1
Toolkit for Nuclei
tkdb_table.cpp
1/********************************************************************************
2 * Copyright (c) : Université de Lyon 1, CNRS/IN2P3, UMR5822, *
3 * IP2I, F-69622 Villeurbanne Cedex, France *
4 * Normandie Université, ENSICAEN, UNICAEN, CNRS/IN2P3, *
5 * LPC Caen, F-14000 Caen, France *
6 * Contibutor(s) : *
7 * Jérémie Dudouet jeremie.dudouet@cnrs.fr [2020] *
8 * Diego Gruyer diego.gruyer@cnrs.fr [2020] *
9 * *
10 * This software is governed by the CeCILL-B license under French law and *
11 * abiding by the rules of distribution of free software. You can use, *
12 * modify and/ or redistribute the software under the terms of the *
13 * CeCILL-B license as circulated by CEA, CNRS and INRIA at the following *
14 * URL \"http://www.cecill.info\". *
15 * *
16 * As a counterpart to the access to the source code and rights to copy, *
17 * modify and redistribute granted by the license, users are provided *
18 * only with a limited warranty and the software's author, the holder of *
19 * the economic rights, and the successive licensors have only limited *
20 * liability. *
21 * *
22 * In this respect, the user's attention is drawn to the risks associated *
23 * with loading, using, modifying and/or developing or reproducing the *
24 * software by the user in light of its specific status of free software, *
25 * that may mean that it is complicated to manipulate, and that also *
26 * therefore means that it is reserved for developers and experienced *
27 * professionals having in-depth computer knowledge. Users are therefore *
28 * encouraged to load and test the software's suitability as regards *
29 * their requirements in conditions enabling the security of their *
30 * systems and/or data to be ensured and, more generally, to use and *
31 * operate it in the same conditions as regards security. *
32 * *
33 * The fact that you are presently reading this means that you have had *
34 * knowledge of the CeCILL-B license and that you accept its terms. *
35 ********************************************************************************/
36
37#include "tkdb_table.h"
38#include "tklog.h"
39
40namespace tkn {
48}
49
50using namespace tkn;
51
52tkdb_table::tkdb_table(const char *_name, sqlite3 *_db) :
53 fName(_name),
54 fDataBase(_db)
55{
56 glog.set_class("db_table");
57 // load();
58}
59
60tkdb_table::~tkdb_table() = default;
61
63{
64 glog.set_method(tkstring::Form("get_column_list(%s)",fName.data()));
65
66 sqlite3_stmt* stmt;
67 int rc = sqlite3_prepare_v2(fDataBase, tkstring::form("PRAGMA table_info('%s');",fName.data()), -1, &stmt, nullptr);
68 if( rc!=SQLITE_OK ){
69 glog << error_v << "SQL error: " << sqlite3_errmsg(fDataBase) << " while loading table '" << fName.data() << "'" << do_endl;
70 sqlite3_free(nullptr);
71 }
72
73 while((sqlite3_step(stmt)) == SQLITE_ROW) {
74 tkstring name = sqlite3_column_text(stmt, 1);
75 tkstring type = sqlite3_column_text(stmt, 2);
76 add_column(name.data(),type.data());
77 }
78
79 sqlite3_finalize(stmt);
80}
81
82void tkdb_table::print(const tkstring &_opt)
83{
84 glog << info << blue << tkstring::form("Table '%s'",fName.data()) << do_endl;
85 print_columns(_opt);
86}
87
89{
90// glog.set_method(tkstring::Form("print_columns(%s)",fName.data()));
91
92 if(_opt.contains("verbose")) glog << info << yellow << tkstring::form("[%3d] columns in '%s' table",fColumns.size(),fName.data()) << do_endl;
93 int ii = 0;
94 for(auto &column : fColumns) {
95 if(_opt.contains("notnull")&&column.second.is_empty()) continue;
96 if(column.first.contains(_opt)) glog << info << tkstring::form("[%3d] %20s %6s %s",ii++,column.second.get_name(),column.second.get_type(),column.second.get_value().data()) << do_endl;
97 }
98}
99
100void tkdb_table::print_rows(const char *_selection, const char* _properties)
101{
102 tkstring sel = _selection;
103 tkstring pro = _properties;
104
105 std::vector<tkstring> colnames;
106
107 if(pro.equal_to("*")) colnames = fOrderedColumnNames;
108 else colnames= pro.tokenize(",");
109
110 for(auto col : colnames) glog << tkstring::form("%20s",fColumns[col.data()].get_name());
111 glog << do_endl;
112
113 begin(sel,_properties);
114 while(next())
115 {
116 for(auto col : colnames) glog << tkstring::form("%20s",fColumns[col.data()].get_value().data());
117 glog << do_endl;
118 }
119}
120
121void tkdb_table::begin(tkstring _condition, tkstring _selection)
122{
123 if(fReading)
124 {
125 glog << warning << "Previous sql statement not yet closed. Closing in to start new statement..." << do_endl;
126 end();
127 }
128
129 tkstring cmd = tkstring::form("select %s from %s where %s",_selection.data(),fName.data(),_condition.data());
130
131 int rc = sqlite3_prepare_v2(fDataBase, cmd.data(), -1, &fSQL_stmt, nullptr);
132 if( rc!=SQLITE_OK ){
133 glog << error << "SQL error: " << sqlite3_errmsg(fDataBase) << "while executing cmd : " << cmd.data() << do_endl;
135 sqlite3_free(nullptr);
136 }
137 else fReading = true;
138}
139
141{
142 if(!fReading) {
143 glog << error << "No sql statement prepared, please call begin() to prepare it " << do_endl;
144 return false;
145 }
146
147 int ret_code = sqlite3_step(fSQL_stmt);
148 if(ret_code== SQLITE_ROW) {
149 int cols = sqlite3_column_count(fSQL_stmt);
150 for (int i=0; i<cols; i++) {
151 const unsigned char* tmp = sqlite3_column_text(fSQL_stmt, i);
152 if(tmp == nullptr)
153 fColumns[sqlite3_column_name(fSQL_stmt, i)].set_value("");
154 else
155 fColumns[sqlite3_column_name(fSQL_stmt, i)].set_value(sqlite3_column_text(fSQL_stmt, i));
156 }
157 return true;
158 }
159 end();
160 return false;
161}
162
164{
165 if(!fReading) {glog << error << "No sql statement prepared to close..." << do_endl; return;}
166 sqlite3_finalize(fSQL_stmt);
167 fReading = false;
169}
170
171//tkstring db_table::get_value(const char *_property, const char *_selection)
172//{
173// tkstring sel = _selection;
174// glog.set_class("db_table");
175// glog.set_method(tkstring::Form("get_value('%s',%s)",_property,sel.data()));
176
177// sqlite3_stmt* stmt;
178// int rc = sqlite3_prepare_v2(fDataBase, tkstring::form("select %s from %s where %s",_property,fName.data(),sel.data()), -1, &stmt, 0);
179// if( rc!=SQLITE_OK ){
180// glog << error_v << "SQL error: " << sqlite3_errmsg(fDataBase) << do_endl;
181// print_columns();
182// sqlite3_free(0);
183// }
184
185// tkstring value = "";
186// int ret_code = 0;
187// while((ret_code = sqlite3_step(stmt)) == SQLITE_ROW) {
188// value = reinterpret_cast< const char* >(sqlite3_column_text(stmt, 0));
189// break;
190// }
191// sqlite3_finalize(stmt);
192// return value;
193//}
194
195void tkdb_table::add_column(const char *_colname, const char *_coltype)
196{
197 tkdb_column col(_colname,_coltype);
198 tkstring col_name(_colname);
199 fColumns.insert(std::make_pair(col_name,col));
200 fOrderedColumnNames.push_back(col_name);
201}
202
203void tkdb_table::add_constraint(const char *_constraint)
204{
205 fConstraint += _constraint;
206 fConstraint += " ";
207}
208
209bool tkdb_table::has_column(const tkstring &_col_name)
210{
211 return fColumns.count(_col_name)>0;
212}
213
215{
216 for (auto &column : fColumns)
217 column.second.set_value_str("");
218}
219
221{
222 tkstring sql = tkstring::form("INSERT INTO %s (", fName.data());
223 tkstring values = "VALUES (";
224 for(auto &column : fColumns) {
225 if(column.second.is_empty()) continue;
226 sql += column.second.get_name();
227 if(column.second.is_text()) values +="'";
228 values += column.second.get_value();
229 if(column.second.is_text()) values +="'";
230 sql +=",";
231 values +=",";
232 }
233 sql.erase(sql.length()-1);
234 values.erase(values.length()-1);
235 sql += ") ";
236 values += ");";
237 sql += values.data();
238 exec_sql(sql.data());
240}
241
243{
244// UPDATE the table. _where should contain a statment returning one column
245// like isotope_id = 27 (to be tested...)
246
247 tkstring sql = tkstring::form("UPDATE %s SET ", fName.data());
248 for(auto &column : fColumns) {
249 if(column.second.is_empty()) continue;
250 sql += column.second.get_name();
251 sql += " = ";
252 if(column.second.is_text()) sql +="'";
253 sql += column.second.get_value();
254 if(column.second.is_text()) sql +="'";
255 sql +=",";
256 }
257 sql.erase(sql.length()-1);
258 sql += " WHERE ";
259 sql += _where.data();
260 sql += ";";
261 exec_sql(sql.data());
263}
264
266{
267 tkstring sql = tkstring::form("CREATE TABLE %s (", fName.data());
268
269 for (auto it: fOrderedColumnNames) {
270 sql += fColumns[it.data()].get_name();
271 sql += " ";
272 sql += fColumns[it.data()].get_type();
273 sql +=",";
274 }
275 sql = sql.substr(0,sql.size()-1);
276 if(!fConstraint.empty()) {
277 sql += ", ";
278 sql += fConstraint.data();
279 }
280 sql +=");";
281 exec_sql(sql.data());
282}
283
285{
286 for (auto &column : fColumns)
287 column.second.set_value_str("0");
288 push_row();
289}
290
291int tkdb_table::exec_sql(const char *_cmd)
292{
293 char *zErrMsg = nullptr;
294 int rc = sqlite3_exec(fDataBase, _cmd, nullptr, nullptr, &zErrMsg);
295
296 if( rc != SQLITE_OK ) {
297 glog << error << "SQL error: " << zErrMsg << " while excuting command: " << _cmd << do_endl;
298 sqlite3_free(zErrMsg);
299 }
300 else {}
301 return rc;
302}
303
304void tkdb_table::read_measure(measure_data_struct &_struct, const tkstring &_col_base_name)
305{
306 if(!fColumns.count(_col_base_name.data())) {
307 glog << warning << "no column named: " << _col_base_name << " in database: " << fName << do_endl;
308 return;
309 }
310 tkdb_column *col = &(*this)[_col_base_name.data()];
311 if(col->is_empty()) {
312 _struct.filled = false;
313 return;
314 }
315 _struct.value = col->get_value().atof();
316 col = &(*this)[tkstring::form("%s_unit",_col_base_name.data())];
317 if(!col->is_empty()) _struct.unit = col->get_value();
318 col = &(*this)[tkstring::form("%s_unc",_col_base_name.data())];
319 if(!col->is_empty()) _struct.err = col->get_value().atof();
320 col = &(*this)[tkstring::form("%s_unc_low",_col_base_name.data())];
321 if(!col->is_empty()) _struct.err_low = col->get_value().atof();
322 col = &(*this)[tkstring::form("%s_unc_high",_col_base_name.data())];
323 if(!col->is_empty()) _struct.err_high = col->get_value().atof();
324 col = &(*this)[tkstring::form("%s_info",_col_base_name.data())];
325 if(!col->is_empty()) _struct.info = col->get_value();
326
327 _struct.filled = true;
328}
329
330
331#ifdef HAS_ROOT
333ClassImp(tkdb_table)
334#endif
335
Simple structure to store a table column.
Definition: tkdb_column.h:49
tkstring get_value() const
Definition: tkdb_column.h:61
Representaiton of a sqlite data table.
Definition: tkdb_table.h:52
void read_measure(measure_data_struct &_struct, const tkstring &_col_base_name)
Definition: tkdb_table.cpp:304
void begin(tkstring _condition, tkstring _selection="*")
Definition: tkdb_table.cpp:121
bool has_column(const tkstring &_col_name)
Definition: tkdb_table.cpp:209
void update_row(const tkstring &_where)
Definition: tkdb_table.cpp:242
void load()
resets column values and ends the select statement
Definition: tkdb_table.cpp:62
bool next()
executes the select statement
Definition: tkdb_table.cpp:140
void print_columns(const tkstring &_opt="")
Definition: tkdb_table.cpp:88
void push_dummy_row()
Definition: tkdb_table.cpp:284
void add_constraint(const char *_constraint)
Definition: tkdb_table.cpp:203
void write_to_database()
Definition: tkdb_table.cpp:265
void print_rows(const char *_selection, const char *_properties="*")
Definition: tkdb_table.cpp:100
void end()
reads the next entry coresponding to the select statement and fills columns
Definition: tkdb_table.cpp:163
void print(const tkstring &_opt="")
Definition: tkdb_table.cpp:82
void add_column(const char *_colname, const char *_coltype)
Definition: tkdb_table.cpp:195
void reset_column_values()
Definition: tkdb_table.cpp:214
virtual ~tkdb_table()
std::string with usefull tricks from TString (ROOT) and KVString (KaliVeda) and more....
Definition: tkstring.h:54
static const char * form(const char *_format,...)
Definition: tkstring.cpp:431
std::vector< tkstring > tokenize(const tkstring &_delim=" ") const
Create a vector of string separated by at least one delimiter.
Definition: tkstring.cpp:254
static tkstring Form(const char *_format,...)
Definition: tkstring.cpp:345
tkstring substr(size_type __pos=0, size_type __n=npos) const
Inlines.
Definition: tkstring.h:179
bool equal_to(const char *_s, ECaseCompare _cmp=kExact) const
Returns true if the string and _s are identical.
Definition: tkstring.cpp:234
bool contains(const char *_pat, ECaseCompare _cmp=kExact) const
Definition: tkstring.h:197
double atof() const
Converts a string to double value.
Definition: tkstring.cpp:191
Definition: tklog.cpp:39
tklog & yellow(tklog &log)
Definition: tklog.h:267
tklog & error_v(tklog &log)
Definition: tklog.h:430
tklog & info(tklog &log)
Definition: tklog.h:336
tklog & error(tklog &log)
Definition: tklog.h:367
tklog & do_endl(tklog &log)
Definition: tklog.h:235
tklog & warning(tklog &log)
Definition: tklog.h:354
tklog & blue(tklog &log)
Definition: tklog.h:270
data structure used to fill a tkmeasure object from the sqlite database
Definition: tkdb_table.h:72