tkdb_table.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 "tkdb_table.h"
#include "tklog.h"
 
namespace tkn {
}
 
using namespace tkn;
 
tkdb_table::tkdb_table(const char *_name, sqlite3 *_db) :
    fName(_name),
    fDataBase(_db)
{
    glog.set_class("db_table");
    //    load();
}
 
tkdb_table::~tkdb_table() = default;
 
void tkdb_table::load()
{
    glog.set_method(tkstring::Form("get_column_list(%s)",fName.data()));
 
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(fDataBase, tkstring::form("PRAGMA table_info('%s');",fName.data()), -1, &stmt, nullptr);
    if( rc!=SQLITE_OK ){
        glog << error_v <<  "SQL error: " << sqlite3_errmsg(fDataBase) << " while loading table '" << fName.data() << "'" << do_endl;
        sqlite3_free(nullptr);
    }
 
    while((sqlite3_step(stmt)) == SQLITE_ROW) {
        tkstring name = sqlite3_column_text(stmt, 1);
        tkstring type = sqlite3_column_text(stmt, 2);
        add_column(name.data(),type.data());
    }
 
    sqlite3_finalize(stmt);
}
 
void tkdb_table::print(const tkstring &_opt)
{
    glog << info << blue << tkstring::form("Table '%s'",fName.data()) << do_endl;
    print_columns(_opt);
}
 
void tkdb_table::print_columns(const tkstring &_opt)
{
//    glog.set_method(tkstring::Form("print_columns(%s)",fName.data()));
 
    if(_opt.contains("verbose")) glog << info << yellow << tkstring::form("[%3d] columns in '%s' table",fColumns.size(),fName.data()) << do_endl;
    int ii = 0;
    for(auto &column : fColumns) {
        if(_opt.contains("notnull")&&column.second.is_empty()) continue;
        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;
    }
}
 
void tkdb_table::print_rows(const char *_selection, const char* _properties)
{
    tkstring sel = _selection;
    tkstring pro = _properties;
 
    std::vector<tkstring> colnames;
 
    if(pro.equal_to("*")) colnames = fOrderedColumnNames;
    else colnames= pro.tokenize(",");
 
    for(auto col : colnames) glog << tkstring::form("%20s",fColumns[col.data()].get_name());
    glog << do_endl;
 
    begin(sel,_properties);
    while(next())
    {
        for(auto col : colnames) glog << tkstring::form("%20s",fColumns[col.data()].get_value().data());
        glog << do_endl;
    }
}
 
void tkdb_table::begin(tkstring _condition, tkstring _selection)
{
    if(fReading)
    {
        glog << warning << "Previous sql statement not yet closed. Closing in to start new statement..." << do_endl;
        end();
    }
 
    tkstring cmd = tkstring::form("select %s from %s where %s",_selection.data(),fName.data(),_condition.data());
 
    int rc = sqlite3_prepare_v2(fDataBase, cmd.data(), -1, &fSQL_stmt, nullptr);
    if( rc!=SQLITE_OK ){
        glog << error <<  "SQL error: " << sqlite3_errmsg(fDataBase)  << "while executing cmd : " << cmd.data() << do_endl;
        print_columns();
        sqlite3_free(nullptr);
    }
    else fReading = true;
}
 
bool tkdb_table::next()
{
    if(!fReading) {
        glog << error << "No sql statement prepared, please call begin() to prepare it " << do_endl;
        return false;
    }
 
    int ret_code = sqlite3_step(fSQL_stmt);
    if(ret_code== SQLITE_ROW) {
        int cols = sqlite3_column_count(fSQL_stmt);
        for (int i=0; i<cols; i++) {
            const unsigned char* tmp = sqlite3_column_text(fSQL_stmt, i);
            if(tmp == nullptr)
                fColumns[sqlite3_column_name(fSQL_stmt, i)].set_value("");
            else
                fColumns[sqlite3_column_name(fSQL_stmt, i)].set_value(sqlite3_column_text(fSQL_stmt, i));
        }
        return true;
    }
    end();
    return false;
}
 
void tkdb_table::end()
{
    if(!fReading) {glog << error << "No sql statement prepared to close..." << do_endl; return;}
    sqlite3_finalize(fSQL_stmt);
    fReading = false;
    reset_column_values();
}
 
//tkstring db_table::get_value(const char *_property, const char *_selection)
//{
//    tkstring sel = _selection;
//    glog.set_class("db_table");
//    glog.set_method(tkstring::Form("get_value('%s',%s)",_property,sel.data()));
 
//    sqlite3_stmt* stmt;
//    int rc = sqlite3_prepare_v2(fDataBase, tkstring::form("select %s from %s where %s",_property,fName.data(),sel.data()), -1, &stmt, 0);
//    if( rc!=SQLITE_OK ){
//        glog << error_v <<  "SQL error: " << sqlite3_errmsg(fDataBase) << do_endl;
//        print_columns();
//        sqlite3_free(0);
//    }
 
//    tkstring value = "";
//    int ret_code = 0;
//    while((ret_code = sqlite3_step(stmt)) == SQLITE_ROW) {
//        value = reinterpret_cast< const char* >(sqlite3_column_text(stmt, 0));
//        break;
//    }
//    sqlite3_finalize(stmt);
//    return value;
//}
 
void tkdb_table::add_column(const char *_colname, const char *_coltype)
{
    tkdb_column col(_colname,_coltype);
    tkstring col_name(_colname);
    fColumns.insert(std::make_pair(col_name,col));
    fOrderedColumnNames.push_back(col_name);
}
 
void tkdb_table::add_constraint(const char *_constraint)
{
    fConstraint += _constraint;
    fConstraint += " ";
}
 
bool tkdb_table::has_column(const tkstring &_col_name)
{
    return fColumns.count(_col_name)>0;
}
 
void tkdb_table::reset_column_values()
{
    for (auto &column : fColumns)
        column.second.set_value_str("");
}
 
void tkdb_table::push_row()
{
    tkstring sql = tkstring::form("INSERT INTO %s (", fName.data());
    tkstring values = "VALUES (";
    for(auto &column : fColumns) {
        if(column.second.is_empty()) continue;
        sql += column.second.get_name();
        if(column.second.is_text()) values +="'";
        values += column.second.get_value();
        if(column.second.is_text()) values +="'";
        sql +=",";
        values +=",";
    }
    sql.erase(sql.length()-1);
    values.erase(values.length()-1);
    sql += ") ";
    values += ");";
    sql += values.data();
    exec_sql(sql.data());
    reset_column_values();
}
 
void tkdb_table::update_row(const tkstring &_where)
{
//    UPDATE the table. _where should contain a statment returning one column
//    like isotope_id = 27 (to be tested...)
 
    tkstring sql = tkstring::form("UPDATE %s SET ", fName.data());
    for(auto &column : fColumns) {
        if(column.second.is_empty()) continue;
        sql += column.second.get_name();
        sql += " = ";
        if(column.second.is_text()) sql +="'";
        sql += column.second.get_value();
        if(column.second.is_text()) sql +="'";
        sql +=",";
    }
    sql.erase(sql.length()-1);
    sql += " WHERE ";
    sql += _where.data();
    sql += ";";
    exec_sql(sql.data());
    reset_column_values();
}
 
void tkdb_table::write_to_database()
{
    tkstring sql = tkstring::form("CREATE TABLE %s (", fName.data());
 
    for (auto it: fOrderedColumnNames) {
        sql += fColumns[it.data()].get_name();
        sql += " ";
        sql += fColumns[it.data()].get_type();
        sql +=",";
    }
    sql = sql.substr(0,sql.size()-1);
    if(!fConstraint.empty()) {
        sql += ", ";
        sql += fConstraint.data();
    }
    sql +=");";
    exec_sql(sql.data());
}
 
void tkdb_table::push_dummy_row()
{
    for (auto &column : fColumns)
        column.second.set_value_str("0");
    push_row();
}
 
int tkdb_table::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;
}
 
void tkdb_table::read_measure(measure_data_struct &_struct, const tkstring &_col_base_name)
{
    if(!fColumns.count(_col_base_name.data())) {
        glog << warning << "no column named: " << _col_base_name << " in database: " << fName << do_endl;
        return;
    }
    tkdb_column *col = &(*this)[_col_base_name.data()];
    if(col->is_empty()) {
        _struct.filled = false;
        return;
    }
    _struct.value = col->get_value().atof();
    col = &(*this)[tkstring::form("%s_unit",_col_base_name.data())];
    if(!col->is_empty()) _struct.unit = col->get_value();
    col = &(*this)[tkstring::form("%s_unc",_col_base_name.data())];
    if(!col->is_empty()) _struct.err = col->get_value().atof();
    col = &(*this)[tkstring::form("%s_unc_low",_col_base_name.data())];
    if(!col->is_empty()) _struct.err_low = col->get_value().atof();
    col = &(*this)[tkstring::form("%s_unc_high",_col_base_name.data())];
    if(!col->is_empty()) _struct.err_high = col->get_value().atof();
    col = &(*this)[tkstring::form("%s_info",_col_base_name.data())];
    if(!col->is_empty()) _struct.info = col->get_value();
 
    _struct.filled = true;
}
 
 
#ifdef HAS_ROOT
ClassImp(tkdb_table)
#endif