Page MenuHomec4science
Files F61396949

sql_query.hh
No OneTemporary
Actions

File Metadata

Created
Mon, May 6, 10:06

sql_query.hh
View Options

/*
author : Nicolas RICHART <nicolas.richart@epfl.ch>
author : Till JUNGE <till.junge@epfl.ch>
*/
#ifndef __BLACKDYNAMITE_SQL_QUERY__
#define __BLACKDYNAMITE_SQL_QUERY__
#include <pqxx/pqxx>
#include <vector>
#include <tuple>
#include <cmath>
#include <algorithm>
#include <random>
#include <unistd.h>
namespace BlackDynamite {
/* -------------------------------------------------------------------------- */
template <typename num> inline std::string nan_avoider(const num & val) {
return pqxx::to_string(val);
}
/* -------------------------------------------------------------------------- */
template <> inline std::string nan_avoider(const double & val) {
if (val != val) {
#if PQXX_VERSION_MAJOR < 4
return "('nan')::double precision";
#else
return "NaN";
#endif
} else {
/* Postgres has its own range definitions */
double fval = std::abs(val);
if (fval <= 1e-307) {
#if PQXX_VERSION_MAJOR < 4
return "(0.0)::double precision";
#else
return "0.";
#endif
} else if (fval >= 1e308) {
if (val > 0.) {
#if PQXX_VERSION_MAJOR < 4
return "('infinity')::double precision";
#else
return "Infinity";
#endif
} else {
#if PQXX_VERSION_MAJOR < 4
return "('-infinity')::double precision";
#else
return "-Infinity";
#endif
}
}
std::stringstream val_rep;
val_rep.precision(16);
val_rep << std::scientific << val;
return val_rep.str();
}
}
/**
* Simple query handler as a secured transaction
*/
template <class transaction = pqxx::transaction<pqxx::read_committed> >
class SQLQuery : public pqxx::transactor<transaction> {
public:
SQLQuery(const std::string & sql_query, const std::string & name)
: pqxx::transactor<transaction>(name), sql_query(sql_query) {}
protected:
SQLQuery(const std::string & name) : pqxx::transactor<transaction>(name) {}
public:
virtual void operator()(transaction & trans) {
this->result = this->execute(trans);
}
void on_abort(const char msg[]) {
std::cerr << "Request: " << this->Name() << " aborted with message: " << msg
<< std::endl;
std::random_device rd;
std::uniform_int_distribution<int> dist(0, 5);
usleep(100 + dist(rd)*100);
}
void on_doubt() {
std::cerr << "Request: " << this->Name() << " is on_doubt" << std::endl;
// sleep(2);
}
protected:
virtual pqxx::result execute(pqxx::transaction_base & trans) {
return trans.exec(sql_query);
}
private:
std::string sql_query;
pqxx::result result;
};
/* ------------------------------------------------------------------------ */
/**
* Simple prepared query this query rely on sql queries previously prepared,
* the SQLQueryPrepareHelper is done to help in this process
*/
template <class transaction = pqxx::transaction<pqxx::read_committed>,
typename... Arguments>
class SQLPreparedQuery : public SQLQuery<transaction> {
public:
SQLPreparedQuery(const std::string & query_name, Arguments... parameters)
: SQLQuery<bd_transaction>(query_name), query_name(query_name),
parameters(std::make_tuple(parameters...)) {}
/* ---------------------------------------------------------------------- */
/// execute a prepared function
protected:
virtual pqxx::result execute(pqxx::transaction_base & trans) {
pqxx::prepare::invocation invoc = trans.prepared(query_name);
return this->prepared<0, Arguments...>(invoc).exec();
}
private:
template <size_t pos>
inline pqxx::prepare::invocation &
prepared(pqxx::prepare::invocation & invoc) {
return invoc;
}
template <size_t pos, typename T, typename... Tail>
inline pqxx::prepare::invocation &
prepared(pqxx::prepare::invocation & invoc) {
return this->prepared<pos + 1, Tail...>(
this->addParameter(invoc, std::get<pos>(this->parameters)));
}
template <typename T>
inline pqxx::prepare::invocation &
addParameter(pqxx::prepare::invocation & invoc, const T & param) {
return invoc(param);
}
template <typename T> struct NanAvoiderIT {
const std::string operator()(typename std::vector<T>::iterator & it) {
return nan_avoider(*it);
}
};
template <typename T>
inline pqxx::prepare::invocation &
addParameter(pqxx::prepare::invocation & invoc,
const std::vector<T> & param) {
std::string join = nan_avoider(param[0]);
for (auto it = param.begin() + 1; it != param.end(); ++it) {
join += ", ";
join += nan_avoider(*it);
}
return invoc("{" + join + "}");
// return invoc("ARRAY [" + pqxx::separated_list(",", param.begin(),
// param.end(), NanAvoiderIT<T>*() + "]");
}
private:
std::string query_name;
std::tuple<Arguments...> parameters;
};
/* ------------------------------------------------------------------------ */
#if PQXX_VERSION_MAJOR < 4
/* ---------------------------------------------------------------------- */
// Convertion functions
/// Function to convert a c++ type in a SQL type for the preparation
template <typename T> struct InternalPrepareHelper {};
/// convert std::string to "varchar"
template <> struct InternalPrepareHelper<std::string> {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("varchar", pqxx::prepare::treat_string);
}
};
/// convert double to "real"
template <> struct InternalPrepareHelper<double> {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("double precision");
}
};
/// convert std::vector<double> to "double precision[]"
template <> struct InternalPrepareHelper<std::vector<double> > {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("double precision[]");
}
};
/// convert std::vector<int> to "interger[]"
template <> struct InternalPrepareHelper<std::vector<int> > {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("integer[]");
}
};
/// convert std::vector<unsigned int> to "interger[]"
template <> struct InternalPrepareHelper<std::vector<UInt> > {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("integer[]");
}
};
/// convert int to "interger"
template <> struct InternalPrepareHelper<int> {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("integer");
}
};
/// convert unsigned int to "interger"
template <> struct InternalPrepareHelper<UInt> {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("integer");
}
};
/// convert bool to "boolean"
template <> struct InternalPrepareHelper<bool> {
static const pqxx::prepare::declaration &
addArgumentType(const pqxx::prepare::declaration & decl) {
return decl("boolean");
}
};
#endif
/**
* SQLQueryPrepareHelper
*/
class SQLQueryPrepareHelper {
/* ---------------------------------------------------------------------- */
// preparation function
public:
/// prepare the request in the connection
static void prepare(pqxx::connection_base & connection,
const std::string & request_name,
const std::string & sql_request) {
connection.prepare(request_name, sql_request);
}
/// prepare the request in the connection
template <typename... Arguments>
static void prepare(pqxx::connection_base & connection,
const std::string & request_name,
const std::string & sql_request) {
#if PQXX_VERSION_MAJOR < 4
pqxx::prepare::declaration decl =
connection.prepare(request_name, sql_request);
prepare<sizeof...(Arguments), Arguments...>(decl);
#else
connection.prepare(request_name, sql_request);
#endif
}
static void unprepare(pqxx::connection_base & connection,
const std::string & request_name) {
connection.unprepare(request_name);
}
#if PQXX_VERSION_MAJOR < 4
private:
template <size_t nb_param>
static const pqxx::prepare::declaration &
prepare(const pqxx::prepare::declaration & decl) {
return decl;
}
template <size_t nb_param, typename T, typename... Tail>
static const pqxx::prepare::declaration &
prepare(const pqxx::prepare::declaration & decl) {
return prepare<sizeof...(Tail), Tail...>(
InternalPrepareHelper<T>::addArgumentType(decl));
}
#endif
};
}
#endif //__BLACKDYNAMITE_SQL_QUERY__

Event Timeline

c4science · Help