37#include "tkdatabase.h"
61 if(g_database)
return g_database;
63 std::lock_guard<std::recursive_mutex> lock(get_mutex());
65 if ( g_database ==
nullptr ) {
74 std::lock_guard<std::recursive_mutex> lock(get_mutex());
76 if(g_database ==
nullptr && opening_db)
open(
tkstring::Form(
"%s/databases/TkN.db",TKN_SYS));
82 glog.set_class(
"database");
83 glog.set_method(
tkstring::Form(
"open(%s,%s)",_db_name.data(),_option.data()));
85 if(TKN_SYS ==
nullptr) {
86 glog <<
error_v <<
" TKN_SYS environment variable not defined, cannot find the repository of the data bases !" <<
do_endl;
98 if(gsystem->exists(fName)) {
99 glog <<
warning <<
"Existing database '" << _db_name <<
"' will be deleted and recreated." <<
do_endl;
100 gsystem->remove_file(fName,
true);
102 fMode = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
104 else if(_option.
equal_to(
"NEW")||_option.
equal_to(
"CREATE")) fMode = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
105 else if(_option.
equal_to(
"OPEN")) fMode = SQLITE_OPEN_READONLY;
106 else if(_option.
equal_to(
"UPDATE")) fMode = SQLITE_OPEN_READWRITE;
110 fMode = SQLITE_OPEN_READONLY;
114 if(sqlite3_open_v2(fName.data(), &db,fMode,
nullptr) != SQLITE_OK) {
115 glog <<
error_v <<
"can't open database: '" << sqlite3_errmsg(db) <<
"'" <<
do_endl;
116 glog <<
info <<
"use tkn-db-update to download the database" <<
do_endl;
122 fFullName = gsystem->get_system_command_output(
tkstring::form(
"ls -l %s",fName.data()));
123 fFullName = fFullName.
tokenize(
" ").back();
125 fFullName = fFullName.
tokenize(
"/").back();
131 exec_sql(
"PRAGMA synchronous = OFF");
132 exec_sql(
"PRAGMA temp_store = MEMORY");
133 exec_sql(
"PRAGMA mmap_size = 30000000000");
134 exec_sql(
"PRAGMA page_size = 32768 ");
142 sqlite3_close(fDataBase);
147 const char *sql =
"SELECT name FROM sqlite_master WHERE type='table'";
149 sqlite3_prepare_v2(fDataBase, sql, -1, &stmt,
nullptr);
151 while(sqlite3_step(stmt) == SQLITE_ROW) {
152 tkstring table_name(sqlite3_column_text(stmt, 0));
153 fTables.insert(std::make_pair(table_name,
tkdb_table(table_name.data(),fDataBase)));
154 fTables[table_name.data()].load();
156 sqlite3_finalize(stmt);
161 const char *sql =
"SELECT name FROM sqlite_master WHERE type='view'";
163 sqlite3_prepare_v2(fDataBase, sql, -1, &stmt,
nullptr);
165 while(sqlite3_step(stmt) == SQLITE_ROW) {
166 tkstring table_name(sqlite3_column_text(stmt, 0));
167 fViews.push_back(table_name);
169 sqlite3_finalize(stmt);
174 fTables.insert(std::make_pair(_table.
get_name(),_table));
179 return fTables.count(_table_name)>0;
184 return std::count(fViews.begin(), fViews.end(), _table_name)>0;
189 fTables.insert(std::make_pair(_table_name,
tkdb_table(_table_name.data(),fDataBase)));
190 return fTables[_table_name];
197 fTables.erase(_table_name.data());
202 return &fTables[_table_name.data()];
207 for(
auto &table : fTables){
208 for(
auto &col : table.second.get_columns()) {
210 if(_opt.
contains(
"notnull")&&!strcmp(col.second.get_value().data(),
""))
continue;
212 glog << col.first <<
"=" << col.second.get_value();
221 glog.set_class(
"database");
222 glog.set_method(
tkstring::Form(
"begin(%s,%s,%s,%s,%s)",_selection.data(),_from.data(),_condition.data(),_loop_name.data(),_extra_cmd.data()));
224 if(fSQLStatement.count(_loop_name)) {
225 glog <<
warning_v <<
"Previous sql statement not yet closed. Closing in to start new statement..." <<
do_endl;
229 if(!_condition.empty()) _condition.
prepend(
"where ");
231 sqlite3_stmt* sqlstm;
232 fSQLStatement.insert(std::make_pair(_loop_name,sqlstm));
234 int rc = sqlite3_prepare_v2(fDataBase,
tkstring::form(
"select %s from %s %s",_selection.data(),_from.data(),_condition.data(),_extra_cmd.data()), -1, &fSQLStatement[_loop_name],
nullptr);
235 if( rc!=SQLITE_OK ) {
236 glog <<
error_v <<
"SQL error: " << sqlite3_errmsg(fDataBase) <<
do_endl;
237 glog <<
tkstring::form(
"select %s from %s %s",_selection.data(),_from.data(),_condition.data()) <<
do_endl;
238 sqlite3_free(
nullptr);
240 else fReading =
true;
247 glog.set_class(
"database");
248 glog.set_method(
"next()");
250 if(!fSQLStatement.count(_loop_name)) {
251 glog <<
error_v <<
"No sql statement name "<< _loop_name <<
" prepared, please call begin() to prepare it " <<
do_endl;
256 int ret_code = sqlite3_step(fSQLStatement[_loop_name]);
257 if(ret_code == SQLITE_ROW) {
258 int cols = sqlite3_column_count(fSQLStatement[_loop_name]);
259 for (
int i=0; i<cols; i++) {
260 const unsigned char* tmp = sqlite3_column_text(fSQLStatement[_loop_name], i);
261 const char* col_name = sqlite3_column_name(fSQLStatement[_loop_name], i);
262 for(
auto &table : fTables){
263 if(table.second.has_column(col_name)) {
265 table.second[col_name].set_value_str(
"");
267 table.second[col_name].set_value_str(
reinterpret_cast<const char*
>(tmp));
282 glog.set_class(
"database");
283 glog.set_method(
"end()");
284 if(!fSQLStatement.count(_loop_name)) {
285 glog <<
error_v <<
"No sql statement prepared to close..." <<
do_endl;
289 sqlite3_finalize(fSQLStatement[_loop_name]);
291 fSQLStatement.erase(_loop_name);
292 for(
auto &table : fTables) table.second.reset_column_values();
299 begin(
"*",_from,_condition,
"count");
300 while(
next(
"count")) counts++;
306 glog.set_class(
"database");
307 glog.set_method(
tkstring::Form(
"get_value(%s,%s,%s)",_selection.data(),_from.data(),_condition.data()));
309 if(_selection.
contains(
",")) glog <<
error_v <<
"Selection should contains only one column name !" <<
do_endl;
310 _condition +=
" LIMIT 1";
312 begin(_selection,_from,_condition,
"get_value");
313 if(
next(
"get_value")) {
315 for(
auto &table : fTables)
if(table.second.has_column(_selection)) val = table.second[_selection.data()].get_value();
316 if(
next(
"get_value")) glog <<
warning_v <<
"More than one entry corresponding to this criteria : only first value returned" <<
do_endl;
317 if(fSQLStatement.count(
"get_value"))
end(
"get_value");
322 glog <<
error_v <<
"No entry corresponding to the asked criteria" <<
do_endl;
329 char *zErrMsg =
nullptr;
330 int rc = sqlite3_exec(fDataBase, _cmd,
nullptr,
nullptr, &zErrMsg);
332 if( rc != SQLITE_OK ) {
333 glog <<
error <<
"SQL error: " << zErrMsg <<
" while excuting command: " << _cmd <<
do_endl;
334 sqlite3_free(zErrMsg);
Interface to the sqlite database.
int count(const tkstring &_from, const tkstring &_condition="")
resets column values and ends the select statement
void end(const tkstring &_loop_name="default")
reads the next entry coresponding to the select statement and fills columns
void remove_table(tkstring _table_name)
void add_table(tkdb_table &_table)
void begin(tkstring _selection, tkstring _from, tkstring _condition="", tkstring _loop_name="default", tkstring _extra_cmd="")
tkstring get_value(tkstring _selection, tkstring _from, tkstring _condition="")
tkdb_table & new_table(tkstring _table_name)
sqlite3 * open(tkstring _db, tkstring _option="OPEN")
bool has_view(const tkstring &_table_name)
tkdatabase(bool opening_db=true)
void print(const tkstring &_properties="", const tkstring &_opt="")
int exec_sql(const char *_cmd)
returns the first value for selection
bool has_table(const tkstring &_table_name)
tkdb_table * get_table(tkstring _table_name)
bool next(const tkstring &_loop_name="default")
executes the select statement
Representaiton of a sqlite data table.
std::string with usefull tricks from TString (ROOT) and KVString (KaliVeda) and more....
static const char * form(const char *_format,...)
std::vector< tkstring > tokenize(const tkstring &_delim=" ") const
Create a vector of string separated by at least one delimiter.
static tkstring Form(const char *_format,...)
tkstring & remove_all(const tkstring &_s1)
bool ends_with(const char *_s, ECaseCompare _cmp=kExact) const
bool equal_to(const char *_s, ECaseCompare _cmp=kExact) const
Returns true if the string and _s are identical.
bool contains(const char *_pat, ECaseCompare _cmp=kExact) const
tkstring & prepend(const tkstring &_st)
tkstring & append(const tkstring &_st)
tkstring & to_upper()
Change all letters to upper case.
tklog & error_v(tklog &log)
tklog & warning_v(tklog &log)
tklog & error(tklog &log)
tklog & do_endl(tklog &log)
tklog & warning(tklog &log)