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(const tkdb_table &_other) :
    fName(_other.fName),
    fDataBase(_other.fDataBase),
    fColumns(_other.fColumns),
    fOrderedColumnNames(_other.fOrderedColumnNames),
    fConstraint(_other.fConstraint)
{
}
 
tkdb_table &tkdb_table::operator=(const tkdb_table &_other)
{
    if (this == &_other) return *this;
    finalize_statements();
    fName = _other.fName;
    fDataBase = _other.fDataBase;
    fColumns = _other.fColumns;
    fOrderedColumnNames = _other.fOrderedColumnNames;
    fConstraint = _other.fConstraint;
    return *this;
}
 
tkdb_table::tkdb_table(tkdb_table &&_other) noexcept :
    fName(std::move(_other.fName)),
    fDataBase(_other.fDataBase),
    fColumns(std::move(_other.fColumns)),
    fOrderedColumnNames(std::move(_other.fOrderedColumnNames)),
    fSQL_stmt(_other.fSQL_stmt),
    fInsertStatement(_other.fInsertStatement),
    fReading(_other.fReading),
    fConstraint(std::move(_other.fConstraint))
{
    _other.fSQL_stmt = nullptr;
    _other.fInsertStatement = nullptr;
    _other.fReading = false;
}
 
tkdb_table &tkdb_table::operator=(tkdb_table &&_other) noexcept
{
    if (this == &_other) return *this;
    finalize_statements();
    fName = std::move(_other.fName);
    fDataBase = _other.fDataBase;
    fColumns = std::move(_other.fColumns);
    fOrderedColumnNames = std::move(_other.fOrderedColumnNames);
    fSQL_stmt = _other.fSQL_stmt;
    fInsertStatement = _other.fInsertStatement;
    fReading = _other.fReading;
    fConstraint = std::move(_other.fConstraint);
    _other.fSQL_stmt = nullptr;
    _other.fInsertStatement = nullptr;
    _other.fReading = false;
    return *this;
}
 
tkdb_table::~tkdb_table()
{
    finalize_statements();
}
 
void tkdb_table::finalize_statements()
{
    if (fSQL_stmt) sqlite3_finalize(fSQL_stmt);
    if (fInsertStatement) sqlite3_finalize(fInsertStatement);
    fSQL_stmt = nullptr;
    fInsertStatement = nullptr;
    fReading = false;
}
 
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);
    fSQL_stmt = nullptr;
    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;
}
 
tkdb_column *tkdb_table::get_column(const tkstring &_col_name)
{
    const auto column = fColumns.find(_col_name);
    return column == fColumns.end() ? nullptr : &column->second;
}
 
void tkdb_table::reset_column_values()
{
    for (auto &column : fColumns)
        column.second.set_value_str("");
}
 
void tkdb_table::push_row()
{
    if (!fInsertStatement && !prepare_insert_statement()) {
        reset_column_values();
        return;
    }
 
    int parameter = 1;
    bool bindings_ok = true;
    for (const auto &name : fOrderedColumnNames) {
        auto &column = fColumns[name];
        int rc = SQLITE_OK;
        if (column.is_empty())
            rc = sqlite3_bind_null(fInsertStatement, parameter);
        else {
            const tkstring value = column.get_value();
            rc = sqlite3_bind_text(fInsertStatement, parameter, value.data(), -1, SQLITE_TRANSIENT);
        }
        if (rc != SQLITE_OK) {
            glog << error << "SQL bind error while inserting into " << fName << ": "
                 << sqlite3_errmsg(fDataBase) << do_endl;
            bindings_ok = false;
            break;
        }
        ++parameter;
    }
 
    if (bindings_ok) {
        const int rc = sqlite3_step(fInsertStatement);
        if (rc != SQLITE_DONE) {
            glog << error << "SQL insert error in " << fName << ": "
                 << sqlite3_errmsg(fDataBase) << do_endl;
        }
    }
    sqlite3_reset(fInsertStatement);
    sqlite3_clear_bindings(fInsertStatement);
    reset_column_values();
}
 
bool tkdb_table::prepare_insert_statement()
{
    if (fOrderedColumnNames.empty()) return false;
 
    tkstring sql = tkstring::form("INSERT INTO %s (", fName.data());
    tkstring parameters = "VALUES (";
    for (const auto &name : fOrderedColumnNames) {
        sql += name;
        sql += ",";
        parameters += "?,";
    }
    sql.erase(sql.length() - 1);
    parameters.erase(parameters.length() - 1);
    sql += ") ";
    parameters += ")";
    sql += parameters;
 
    const int rc = sqlite3_prepare_v2(fDataBase, sql.data(), -1, &fInsertStatement, nullptr);
    if (rc != SQLITE_OK) {
        glog << error << "SQL prepare error for " << fName << ": "
             << sqlite3_errmsg(fDataBase) << do_endl;
        fInsertStatement = nullptr;
        return false;
    }
    return true;
}
 
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)
{
    _struct.clear();
    const auto columns = get_measure_columns(_col_base_name);
    if(!columns[0]) {
        glog << warning << "no column named: " << _col_base_name << " in database: " << fName << do_endl;
        return;
    }
    read_measure(_struct, columns);
}
 
tkdb_table::measure_columns tkdb_table::get_measure_columns(const tkstring &_col_base_name)
{
    static const std::array<const char *, 6> suffixes = {
        "", "_unit", "_unc", "_unc_low", "_unc_high", "_info"
    };
    measure_columns columns{};
    for (std::size_t index = 0; index < suffixes.size(); ++index) {
        tkstring name = _col_base_name;
        name += suffixes[index];
        columns[index] = get_column(name);
    }
    return columns;
}
 
void tkdb_table::read_measure(measure_data_struct &_struct, const measure_columns &_columns)
{
    // A caller may reuse the same structure for several columns.  Always
    // reset it first so that an SQL NULL cannot leave the previous measure's
    // value, unit, or uncertainty behind.
    _struct.clear();
 
    if (!_columns[0] || _columns[0]->is_empty()) return;
 
    _struct.value = _columns[0]->get_value().atof();
 
    if(_columns[1] && !_columns[1]->is_empty()) _struct.unit = _columns[1]->get_value();
    if(_columns[2] && !_columns[2]->is_empty()) _struct.err = _columns[2]->get_value().atof();
    if(_columns[3] && !_columns[3]->is_empty()) _struct.err_low = _columns[3]->get_value().atof();
    if(_columns[4] && !_columns[4]->is_empty()) _struct.err_high = _columns[4]->get_value().atof();
    if(_columns[5] && !_columns[5]->is_empty()) _struct.info = _columns[5]->get_value();
 
    _struct.filled = true;
}
 
 
#ifdef HAS_ROOT
ClassImp(tkdb_table)
#endif