sqlUtils Class Reference

#include <sql_utils.h>

Static Public Member Functions
static bool	addColumns (const std::vector< MDLabel > &columns, sqlite3 *db, const std::string &table)
static void	reportLastError (sqlite3 *db)
static bool	select (size_t rowId, sqlite3 *db, const std::string &table, std::vector< MDObject > &values)
static bool	select (sqlite3 *db, const std::string &table, const std::vector< MDObject > &columns, std::vector< std::vector< MDObject >> &rows)
template<typename T >
static bool	select (const MDLabel &label, sqlite3 *db, const std::string &table, std::vector< T > &values)
static bool	insert (const std::vector< std::vector< const MDObject *>> &records, sqlite3 *db, const std::string &table)
static bool	insert (const std::vector< const MDObject *> &values, sqlite3 *db, const std::string &table)
static bool	update (const std::vector< const MDObject *> &values, sqlite3 *db, const std::string &table, size_t id)

Static Protected Member Functions
static void	beginTrans (sqlite3 *db)
static void	endTrans (sqlite3 *db)
static void	commitTrans (sqlite3 *db)
static bool	checkError (sqlite3 *db)
static std::string	createSelectQuery (const std::vector< MDObject > &values, const std::string &table)
static std::string	createSelectQuery (size_t rowId, const std::vector< MDObject > &values, const std::string &table)
static std::string	createInsertQuery (const std::vector< const MDObject *> &values, const std::string &table)
static std::string	createUpdateQuery (const std::vector< const MDObject *> &values, const std::string &table, size_t id)

Detailed Description

Definition at line 34 of file sql_utils.h.

Member Function Documentation

addColumns()

bool sqlUtils::addColumns ( const std::vector< MDLabel > &  columns, sqlite3 *  db, const std::string &  table  )

static

Add a new column to a DB table. WARNING: this operation is typically 'very slow'. If you can, define all columns of the table at the construction time.

Parameters

columns	to be added
db	to be altered
table	to be altered

Returns

true on success

Definition at line 31 of file sql_utils.cpp.

                                             {
    // it seems that with columns, one cannot use data binding
    auto query = "ALTER TABLE " + table + " ADD COLUMN ";
    // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
    // do our business in a clean transaction and start a new transaction after (not to break the original code)
    commitTrans(db);
    beginTrans(db);

    for (auto c : columns) {
        auto stmt = query + MDL::label2SqlColumn(c) + ";";
        sqlite3_exec(db, stmt.c_str(), nullptr, nullptr, nullptr);
    }

    endTrans(db);
    beginTrans(db);
    return checkError(db);
}

beginTrans()

static void sqlUtils::beginTrans ( sqlite3 *  db )

inlinestaticprotected

Definition at line 150 of file sql_utils.h.

                                               {
        sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, nullptr);
    }

checkError()

bool sqlUtils::checkError ( sqlite3 *  db )

staticprotected

Definition at line 331 of file sql_utils.cpp.

                                     {
        auto err = sqlite3_errcode(db);
        if (0 != err 
            && 100 != err && 101 != err) { // probably not an error in case of the sqlite3_step (fingers crossed)
            auto msg = sqlite3_errmsg(db);
            std::cerr << "SQLite3 error: " << err
                << "\n"
                << msg << std::endl;
            return false;
        }

        return true;
    }

commitTrans()

static void sqlUtils::commitTrans ( sqlite3 *  db )

inlinestaticprotected

Definition at line 158 of file sql_utils.h.

                                                {
        sqlite3_exec(db, "COMMIT TRANSACTION", nullptr, nullptr, nullptr);
    }

createInsertQuery()

std::string sqlUtils::createInsertQuery ( const std::vector< const MDObject *> &  values, const std::string &  table  )

staticprotected

Definition at line 236 of file sql_utils.cpp.

                                {
    std::stringstream cols;
    std::stringstream vals;
    const auto len = values.size();
    for (size_t i = 0; i < len; ++i) {
        cols << MDL::label2StrSql(values.at(i)->label);
        vals << "?";
        if (len != (i + 1)) {
            cols << ", ";
           vals << ", ";
        }
    }
    std::stringstream ss;
    ss << "INSERT INTO " << table
            << " (" << cols.str() << ")"
            << " VALUES "
            << " (" << vals.str() << ");";
    return ss.str();
}

createSelectQuery() [1/2]

std::string sqlUtils::createSelectQuery ( const std::vector< MDObject > &  values, const std::string &  table  )

staticprotected

Create a query for selecting multiple values from a table

Definition at line 168 of file sql_utils.cpp.

                                {
    std::stringstream cols;
    const auto len = values.size();
    for (size_t i = 0; i < len; ++i) {
        cols << MDL::label2StrSql(values.at(i).label);
        if (len != (i + 1)) {
            cols << ", ";
        }
    }
    std::stringstream ss;
    ss << "SELECT "
        << cols.str()
        << " FROM " << table << ";";
    return ss.str();
}

createSelectQuery() [2/2]

std::string sqlUtils::createSelectQuery ( size_t  rowId, const std::vector< MDObject > &  values, const std::string &  table  )

staticprotected

Create a query for selecting multiple values from a specific row

Definition at line 149 of file sql_utils.cpp.

                                {
    std::stringstream cols;
    const auto len = values.size();
    for (size_t i = 0; i < len; ++i) {
        cols << MDL::label2StrSql(values.at(i).label);
        if (len != (i + 1)) {
            cols << ", ";
        }
    }
    std::stringstream ss;
    ss << "SELECT "
        << cols.str()
        << " FROM " << table
        << " WHERE objID=" << id << ";";
    return ss.str();
}

createUpdateQuery()

std::string sqlUtils::createUpdateQuery ( const std::vector< const MDObject *> &  values, const std::string &  table, size_t  id  )

staticprotected

Create an update query for setting multiple values from a specific row

Definition at line 186 of file sql_utils.cpp.

                   {
    std::stringstream cols;
    const auto len = values.size();
    for (size_t i = 0; i < len; ++i) {
        cols << MDL::label2StrSql(values.at(i)->label);
        cols << "=?";
        if (len != (i + 1)) {
            cols << ", ";
        }
    }
    std::stringstream ss;
    ss << "UPDATE " << table << " SET "
            << cols.str()
            << " WHERE objID=" << id << ";";
    return ss.str();
}

endTrans()

static void sqlUtils::endTrans ( sqlite3 *  db )

inlinestaticprotected

Definition at line 154 of file sql_utils.h.

                                             {
        sqlite3_exec(db, "END TRANSACTION", nullptr, nullptr, nullptr);
    }

insert() [1/2]

bool sqlUtils::insert ( const std::vector< std::vector< const MDObject *>> &  records, sqlite3 *  db, const std::string &  table  )

static

Add multiple new rows into the table

Parameters

records	to be added (respective columns are expected to exists in the table)
db	to be altered
table	to be altered

Returns

true on success

Definition at line 258 of file sql_utils.cpp.

                                             {
    if (0 == records.size()) {
        return true;
    }
    // assuming all records are the same
    const auto &rec = records.at(0);
    auto query = createInsertQuery(rec, table);
    sqlite3_stmt *stmt = nullptr;
    sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);
    // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
    // do our business in a clean transaction and start a new transaction after (not to break the original code)
    commitTrans(db);
    beginTrans(db);

    const auto len = rec.size();
    for (const auto &r : records) {
        // bind proper values
        for (size_t i = 0; i < len; ++i) {
            bindValue(stmt, i + 1, *r.at(i));
        }
        // execute
        sqlite3_step(stmt);
        sqlite3_clear_bindings(stmt);
        sqlite3_reset(stmt);
    }
    sqlite3_finalize(stmt);
    endTrans(db);
    beginTrans(db);

    return checkError(db);
}

insert() [2/2]

bool sqlUtils::insert ( const std::vector< const MDObject *> &  values, sqlite3 *  db, const std::string &  table  )

static

Add a single new row into the table

Parameters

values	in the row to be added (respective columns are expected to exists in the table)
db	to be altered
table	to be altered

Returns

true on success

Definition at line 291 of file sql_utils.cpp.

                                             {
    return insert(std::vector<std::vector<const MDObject*>>{values}, db, table);
}

reportLastError()

static void sqlUtils::reportLastError ( sqlite3 *  db )

inlinestatic

Report last error registered by the db to std::cerr

Parameters

db	where the error occured

Definition at line 52 of file sql_utils.h.

                                             {

    }

select() [1/3]

bool sqlUtils::select ( size_t  rowId, sqlite3 *  db, const std::string &  table, std::vector< MDObject > &  values  )

static

Retrieve all values within a specific row

Parameters

rowId	of the row
db	to be read
table	to be read
values	will be stored here

Returns

true on success

Definition at line 87 of file sql_utils.cpp.

                                     {
    // assuming all records are the same
    auto query = createSelectQuery(rowId, values, table);

    // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
    // do our business in a clean transaction and start a new transaction after (not to break the original code)
    sqlite3_mutex_enter(sqlite3_db_mutex(db)); // FIXME this should be only done on demand or not at all ...
    commitTrans(db);
    beginTrans(db);

    sqlite3_stmt *stmt = nullptr;
    sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);

    // execute
    sqlite3_step(stmt);
    for (size_t i = 0; i < values.size(); ++i) {
        extractValue(stmt, i, values.at(i));
    }

    sqlite3_reset(stmt);

    sqlite3_finalize(stmt);
    endTrans(db);
    beginTrans(db);
    sqlite3_mutex_leave(sqlite3_db_mutex(db)); // FIXME this should be only done on demand or not at all ...

    return checkError(db);
}

select() [2/3]

bool sqlUtils::select ( sqlite3 *  db, const std::string &  table, const std::vector< MDObject > &  columns, std::vector< std::vector< MDObject >> &  rows  )

static

Retrieve all rows from a table All MDObjects are expected to be in the same order

Parameters

db	to be read
table	to be read to be read
rows	will be stored here

Returns

true on success

Definition at line 118 of file sql_utils.cpp.

                                              {
    // assuming all records are the same
    auto query = createSelectQuery(columns, table);
    sqlite3_stmt *stmt = nullptr;
    // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
    // do our business in a clean transaction and start a new transaction after (not to break the original code)
    commitTrans(db);
    beginTrans(db);
    sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);

    // execute, extract value from each row
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        rows.emplace_back(columns);
        auto &r = rows.back();
        for (size_t i = 0; i < columns.size(); ++i) {
            extractValue(stmt, i, r.at(i));
        }
    }
    sqlite3_reset(stmt);

    sqlite3_finalize(stmt);
    endTrans(db);
    beginTrans(db);

    return checkError(db);
}

select() [3/3]

template<typename T >

static bool sqlUtils::select ( const MDLabel &  label, sqlite3 *  db, const std::string &  table, std::vector< T > &  values  )

inlinestatic

Retrieve all values within a single column

Parameters

label	of the column
db	to be read
table	to be read
values	will be stored here (appended to the end)

Returns

true on success

Definition at line 90 of file sql_utils.h.

                                  {
        // assuming all records are the same
        auto query = createSelectQuery({label}, table);

        sqlite3_stmt *stmt = nullptr;
        // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
        // do our business in a clean transaction and start a new transaction after (not to break the original code)
        commitTrans(db);
        beginTrans(db);
        sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);

        MDObject obj(label);
        // execute, extract value from each row
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            extractValue(stmt, 0, obj);
            values.emplace_back(obj.getValue2(T()));
        }

        sqlite3_reset(stmt);
        sqlite3_finalize(stmt);
        endTrans(db);
        beginTrans(db);

        return checkError(db);
    }

update()

bool sqlUtils::update ( const std::vector< const MDObject *> &  values, sqlite3 *  db, const std::string &  table, size_t  id  )

static

Update a single row in the table

Parameters

values	in the row to be updated (respective columns are expected to exists in the table)
db	to be altered
table	to be altered
id	of the row

Returns

true on success

Definition at line 206 of file sql_utils.cpp.

                                                            {
    if (values.empty()) {
        return true;
    }
    // assuming all records are the same
    auto query = createUpdateQuery(values, table, id);
    sqlite3_stmt *stmt = nullptr;
    sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);
    // FIXME currently, whole db is in one huge transaction. Finish whatever might be pending,
    // do our business in a clean transaction and start a new transaction after (not to break the original code)
    commitTrans(db);
    beginTrans(db);

    // bind proper values
    for (auto i = 0; i < values.size(); ++i) {
        bindValue(stmt, i + 1, *values.at(i));
    }
    // execute
    sqlite3_step(stmt);
    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    sqlite3_finalize(stmt);
    endTrans(db);
    beginTrans(db);

    return checkError(db);
}

---

The documentation for this class was generated from the following files:

• xmippCore/core/utils/sql_utils.h
• xmippCore/core/utils/sql_utils.cpp