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 <filesystem>
 
#include "tksystem.h"
#include "tksqlite_runtime.h"
 
namespace tkn {
}
 
using namespace tkn;
 
tkdatabase *tkdatabase::the_database()
{
    static tkdatabase *instance = new tkdatabase();
    return instance;
}
 
tkdatabase::tkdatabase(bool opening_db)
{
    if(opening_db) open(tkstring::Form("%s/TkN.db", TKN_DB_DIR));
}
 
tkdatabase::~tkdatabase()
{
    if (fDataBase) close();
}
 
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_USE_SYSTEM_SQLITE
    const int runtime_version = sqlite3_libversion_number();
    const auto compatibility = detail::check_sqlite_runtime(
        runtime_version, SQLITE_VERSION_NUMBER, sqlite3_threadsafe());
    if (compatibility != detail::sqlite_compatibility::compatible) {
        const char *reason = compatibility == detail::sqlite_compatibility::too_old ?
            "SQLite " TKN_SQLITE_MIN_VERSION " or newer is required" :
            compatibility == detail::sqlite_compatibility::older_than_headers ?
            "loaded SQLite is older than the compile-time headers" :
            "loaded SQLite was built without mutex support";
        glog << error << "System SQLite rejected: " << reason
             << " (runtime=" << runtime_version << ", headers=" << SQLITE_VERSION_NUMBER << ")" << do_endl;
        glog.clear();
        return nullptr;
    }
#endif
 
    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;
    }
 
    std::error_code path_error;
    const auto resolved_path = std::filesystem::canonical(fName.data(), path_error);
    const auto displayed_path = path_error ? std::filesystem::path(fName.data()) : resolved_path;
    fFullName = displayed_path.filename().string();
 
    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()
{
    for (auto &statement : fSQLStatement) sqlite3_finalize(statement.second);
    fSQLStatement.clear();
    fSQLColumnBindings.clear();
    fReading = false;
    fTables.clear();
    fViews.clear();
    sqlite3_close(fDataBase);
    fDataBase = nullptr;
}
 
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);
        auto &column_bindings = fSQLColumnBindings[_loop_name];
        const int column_count = sqlite3_column_count(sqlstm);
        column_bindings.resize(column_count);
        for (int column_index = 0; column_index < column_count; ++column_index) {
            const tkstring column_name = sqlite3_column_name(sqlstm, column_index);
            for (auto &table : fTables) {
                if (auto *column = table.second.get_column(column_name))
                    column_bindings[column_index].push_back(column);
            }
        }
        fReading = true;
    }
 
    glog.clear();
}
 
bool tkdatabase::next(const tkstring &_loop_name)
{
    const auto statement_entry = fSQLStatement.find(_loop_name);
    if(statement_entry == fSQLStatement.end()) {
        glog.set_class("database");
        glog.set_method("next()");
        glog << error_v << "No sql statement name "<< _loop_name << " prepared, please call begin() to prepare it " << do_endl;
        glog.clear();
        return false;
    }
 
    sqlite3_stmt *statement = statement_entry->second;
    int ret_code = sqlite3_step(statement);
    if(ret_code == SQLITE_ROW) {
        const auto &column_bindings = fSQLColumnBindings.at(_loop_name);
        for (int column_index = 0; column_index < static_cast<int>(column_bindings.size()); ++column_index) {
            const auto *value = sqlite3_column_text(statement, column_index);
            const char *text = value ? reinterpret_cast<const char *>(value) : "";
            for (auto *column : column_bindings[column_index])
                column->set_value_str(text);
        }
        return true;
    }
    end(_loop_name);
    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);
    fSQLColumnBindings.erase(_loop_name);
    for(auto &table : fTables) table.second.reset_column_values();
    glog.clear();
}
 
int tkdatabase::count(const tkstring &_from, const tkstring &_condition)
{
    tkstring sql = tkstring::form("SELECT COUNT(*) FROM %s", _from.data());
    if (!_condition.empty()) sql += tkstring::form(" WHERE %s", _condition.data());
 
    sqlite3_stmt *statement = nullptr;
    const int prepare_rc = sqlite3_prepare_v2(fDataBase, sql.data(), -1, &statement, nullptr);
    if (prepare_rc != SQLITE_OK) {
        glog << error << "SQL count error: " << sqlite3_errmsg(fDataBase) << do_endl;
        if (statement) sqlite3_finalize(statement);
        return 0;
    }
 
    int counts = 0;
    if (sqlite3_step(statement) == SQLITE_ROW) counts = sqlite3_column_int(statement, 0);
    sqlite3_finalize(statement);
    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