Homec4science
Diffusion blackdynamite (multiuser) Browse

R3127/src/sql_query.hh
0d59d01f2d0cmultiuser

Last Change
Raw File

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>
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) : sql_query(sql_query) { }
protected:
SQLQuery() {}
public:
virtual void operator() (transaction & trans) {
UInt retry = 10000;
bool success = false;
std::string message;
while (retry != 0){
try {
this->result = this->execute(trans);
retry = 0;
success = true;
} catch (std::runtime_error & e) {
--retry;
message = e.what();
}
}
if (!success){
std::cerr << "Failed to execute query after " << retry << " retries "
<< sql_query << std::endl << " with message: " << message;
throw std::runtime_error(message);
}
}
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) : 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__
c4science · Help