tkdatabase.cpp

/********************************************************************************
 *   Copyright (c) : Université de Lyon 1, CNRS/IN2P3, UMR5822,                 *
 *                   IP2I, F-69622 Villeurbanne Cedex, France                   *
 *                   Normandie Université, ENSICAEN, UNICAEN, CNRS/IN2P3,       *
 *                   LPC Caen, F-14000 Caen, France                             *
 *   Contibutor(s) :                                                            *
 *      Jérémie Dudouet jeremie.dudouet@cnrs.fr [2020]                          *
 *      Diego Gruyer    diego.gruyer@cnrs.fr    [2020]                          *
 *                                                                              *
 *   Licensed under the MIT License <http://opensource.org/licenses/MIT>.       *   
 *   SPDX-License-Identifier: MIT                                               *
 ********************************************************************************/
 
#include "tkdatabase.h"
 
#include <algorithm>
 
#include "tksystem.h"
 
namespace tkn {
}
 
using namespace tkn;
 
tkdatabase *tkdatabase::g_database = nullptr;
 
tkdatabase *tkdatabase::the_database()
{
    if(g_database) return g_database;
 
    std::lock_guard<std::recursive_mutex> lock(get_mutex());
 
    if ( g_database == nullptr ) {
        g_database = new tkdatabase();
    }
 
    return g_database;
}
 
tkdatabase::tkdatabase(bool opening_db)
{
    std::lock_guard<std::recursive_mutex> lock(get_mutex());
 
    if(g_database == nullptr && opening_db) open(tkstring::Form("%s/databases/TkN.db",TKN_SYS));
}
 
tkdatabase::~tkdatabase()
{
    std::lock_guard<std::recursive_mutex> lock(get_mutex());
    if(g_database == this) g_database = nullptr;
}
 
sqlite3* tkdatabase::open(tkstring _db_name, tkstring _option)
{
    glog.set_class("database");
    glog.set_method(tkstring::Form("open(%s,%s)",_db_name.data(),_option.data()));
 
    if(TKN_SYS == nullptr) {
        glog << error_v << " TKN_SYS environment variable not defined, cannot find the repository of the data bases !" << do_endl;
        glog.clear();
        return nullptr;
    }
 
    if(!_db_name.ends_with(".db")) _db_name.append(".db");
 
    fName = _db_name;
 
    _option.to_upper();
 
    if(_option.equal_to("RECREATE")) {
        if(gsystem->exists(fName)) {
            glog << warning << "Existing database '" << _db_name << "' will be deleted and recreated." << do_endl;
            gsystem->remove_file(fName,true);
        }
        fMode = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
    }
    else if(_option.equal_to("NEW")||_option.equal_to("CREATE")) fMode = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
    else if(_option.equal_to("OPEN")) fMode = SQLITE_OPEN_READONLY;
    else if(_option.equal_to("UPDATE")) fMode = SQLITE_OPEN_READWRITE;
    else {
        glog << warning_v << "'" << _option << "' option not suported." << do_endl;
        glog << warning_v << "'OPEN' option used by default..." << do_endl;
        fMode = SQLITE_OPEN_READONLY;
    }
 
    sqlite3* db;
    if(sqlite3_open_v2(fName.data(), &db,fMode,nullptr) != SQLITE_OK) {
        glog << error_v << "can't open database: '" << sqlite3_errmsg(db)  << "'" << do_endl;
        glog << info << "use tkn-db-update to download the database" << do_endl;
        sqlite3_close(db);
        glog.clear();
        return nullptr;
    }
 
    fFullName = gsystem->get_system_command_output(tkstring::form("ls -l %s",fName.data()));
    fFullName = fFullName.tokenize(" ").back();
    fFullName.remove_all("\n");
    fFullName = fFullName.tokenize("/").back();
 
    fDataBase = db;
    load_tables();
    load_views();
 
    exec_sql("PRAGMA synchronous = OFF");
    exec_sql("PRAGMA temp_store = MEMORY");
    exec_sql("PRAGMA mmap_size = 30000000000");
    exec_sql("PRAGMA page_size = 32768 ");
 
    glog.clear();
    return db;
}
 
void tkdatabase::close()
{
    sqlite3_close(fDataBase);
}
 
void tkdatabase::load_tables()
{
    const char *sql = "SELECT name FROM sqlite_master WHERE type='table'";
    sqlite3_stmt* stmt;
    sqlite3_prepare_v2(fDataBase, sql, -1, &stmt, nullptr);
 
    while(sqlite3_step(stmt) == SQLITE_ROW) {
        tkstring table_name(sqlite3_column_text(stmt, 0));
        fTables.insert(std::make_pair(table_name,tkdb_table(table_name.data(),fDataBase)));
        fTables[table_name.data()].load();
    }
    sqlite3_finalize(stmt);
}
 
void tkdatabase::load_views()
{
    const char *sql = "SELECT name FROM sqlite_master WHERE type='view'";
    sqlite3_stmt* stmt;
    sqlite3_prepare_v2(fDataBase, sql, -1, &stmt, nullptr);
 
    while(sqlite3_step(stmt) == SQLITE_ROW) {
        tkstring table_name(sqlite3_column_text(stmt, 0));
        fViews.push_back(table_name);
    }
    sqlite3_finalize(stmt);
}
 
void tkdatabase::add_table(tkdb_table &_table)
{
    fTables.insert(std::make_pair(_table.get_name(),_table));
}
 
bool tkdatabase::has_table(const tkstring &_table_name)
{
    return fTables.count(_table_name)>0;
}
 
bool tkdatabase::has_view(const tkstring &_table_name)
{
    return std::count(fViews.begin(), fViews.end(), _table_name)>0;
}
 
tkdb_table &tkdatabase::new_table(tkstring _table_name)
{
    fTables.insert(std::make_pair(_table_name,tkdb_table(_table_name.data(),fDataBase)));
    return fTables[_table_name];
}
 
void tkdatabase::remove_table(tkstring _table_name)
{
    tkstring sql = tkstring::form("DROP TABLE %s ", _table_name.data());
    exec_sql(sql.data());
    fTables.erase(_table_name.data());
}
 
tkdb_table* tkdatabase::get_table(tkstring _table_name)
{
    return &fTables[_table_name.data()];
}
 
void tkdatabase::print(const tkstring &_properties, const tkstring &_opt)
{
    for(auto &table : fTables){
        for(auto &col : table.second.get_columns()) {
            if(_properties.contains(col.first.data())||_properties.equal_to("*")) {
                if(_opt.contains("notnull")&&!strcmp(col.second.get_value().data(),"")) continue;
                glog << "|";
                glog << col.first << "=" << col.second.get_value();
            }
        }
    }
    glog << do_endl;
}
 
void tkdatabase::begin(tkstring _selection, tkstring _from, tkstring _condition, tkstring _loop_name, tkstring _extra_cmd)
{
    glog.set_class("database");
    glog.set_method(tkstring::Form("begin(%s,%s,%s,%s,%s)",_selection.data(),_from.data(),_condition.data(),_loop_name.data(),_extra_cmd.data()));
 
    if(fSQLStatement.count(_loop_name)) {
        glog << warning_v << "Previous sql statement not yet closed. Closing in to start new statement..." << do_endl;
        end(_loop_name);
    }
 
    if(!_condition.empty()) _condition.prepend("where ");
 
    sqlite3_stmt* sqlstm = nullptr;
    int rc = sqlite3_prepare_v2(fDataBase, tkstring::form("select %s from %s %s",_selection.data(),_from.data(),_condition.data(),_extra_cmd.data()), -1, &sqlstm, nullptr);
    if( rc!=SQLITE_OK ) {
        glog << error_v <<  "SQL error: " << sqlite3_errmsg(fDataBase) << do_endl;
        glog << tkstring::form("select %s from %s %s",_selection.data(),_from.data(),_condition.data()) << do_endl;
        if(sqlstm) sqlite3_finalize(sqlstm);
        sqlite3_free(nullptr);
    }
    else {
        fSQLStatement.emplace(_loop_name, sqlstm);
        fReading = true;
    }
 
    glog.clear();
}
 
bool tkdatabase::next(const tkstring &_loop_name)
{
    glog.set_class("database");
    glog.set_method("next()");
 
    if(!fSQLStatement.count(_loop_name)) {
        glog << error_v << "No sql statement name "<< _loop_name << " prepared, please call begin() to prepare it " << do_endl;
        glog.clear();
        return false;
    }
 
    int ret_code = sqlite3_step(fSQLStatement[_loop_name]);
    if(ret_code == SQLITE_ROW) {
        int cols = sqlite3_column_count(fSQLStatement[_loop_name]);
        for (int i=0; i<cols; i++) {
            const unsigned char* tmp = sqlite3_column_text(fSQLStatement[_loop_name], i);
            const char* col_name = sqlite3_column_name(fSQLStatement[_loop_name], i);
            for(auto &table : fTables){
                if(table.second.has_column(col_name)) {
                    if(tmp == nullptr)
                        table.second[col_name].set_value_str("");
                    else
                        table.second[col_name].set_value_str(reinterpret_cast<const char*>(tmp));
                    break;
                }
            }
        }
        glog.clear();
        return true;
    }
    end(_loop_name);
    glog.clear();
    return false;
}
 
void tkdatabase::end(const tkstring &_loop_name)
{
    glog.set_class("database");
    glog.set_method("end()");
    if(!fSQLStatement.count(_loop_name)) {
        glog << error_v << "No sql statement prepared to close..." << do_endl;
        glog.clear();
        return;
    }
    sqlite3_finalize(fSQLStatement[_loop_name]);
    fReading = false;
    fSQLStatement.erase(_loop_name);
    for(auto &table : fTables) table.second.reset_column_values();
    glog.clear();
}
 
int tkdatabase::count(const tkstring &_from, const tkstring &_condition)
{
    int counts = 0;
    begin("*",_from,_condition,"count");
    while(next("count")) counts++;
    return counts;
}
 
tkstring tkdatabase::get_value(tkstring _selection, tkstring _from, tkstring _condition)
{
    glog.set_class("database");
    glog.set_method(tkstring::Form("get_value(%s,%s,%s)",_selection.data(),_from.data(),_condition.data()));
 
    if(_selection.contains(",")) glog << error_v << "Selection should contains only one column name !" << do_endl;
    _condition += " LIMIT 1";
 
    begin(_selection,_from,_condition,"get_value");
    if(next("get_value")) {
        tkstring val = "";
        for(auto &table : fTables) if(table.second.has_column(_selection)) val = table.second[_selection.data()].get_value();
        if(next("get_value")) glog << warning_v << "More than one entry corresponding to this criteria : only first value returned" << do_endl;
        if(fSQLStatement.count("get_value")) end("get_value");
        glog.clear();
        return val;
    }
 
    glog << error_v << "No entry corresponding to the asked criteria" << do_endl;
    glog.clear();
    return "";
}
 
int tkdatabase::exec_sql(const char *_cmd)
{
    char *zErrMsg = nullptr;
    int rc = sqlite3_exec(fDataBase, _cmd, nullptr, nullptr, &zErrMsg);
 
    if( rc != SQLITE_OK ) {
        glog << error << "SQL error: " << zErrMsg << " while excuting command: " <<  _cmd << do_endl;
        sqlite3_free(zErrMsg);
    }
    else {}
    return rc;
}
 
#ifdef HAS_ROOT
ClassImp(tkdatabase);
#endif