cet::sqlite Namespace Reference

Namespaces
	detail
	errors
	ExceptionDetail

Classes
struct	column
struct	column< double, Constraints... >
struct	column< float, Constraints... >
struct	column< int, Constraints... >
struct	column< long long, Constraints... >
struct	column< long, Constraints... >
struct	column< std::string, Constraints... >
struct	column< unsigned int, Constraints... >
struct	column< unsigned long long, Constraints... >
struct	column< unsigned long, Constraints... >
class	column_base
class	Connection
class	ConnectionFactory
struct	IncompleteInsert
struct	IncompleteSelectStmt
class	Ntuple
struct	permissive_column
struct	permissive_column< column< T, Constraints... > >
struct	primary_key
struct	query_result
struct	SelectStmt
class	Transaction

Typedefs
template<size_t N>
using	name_array = std::array< std::string, N >
using	Exception = cet::coded_exception< errors::ErrorCodes, ExceptionDetail::translate >

Functions
template<typename... Cols>
void	create_table (sqlite3 *const db, std::string const &tablename, Cols const &...cols)
void	create_table_as (std::string const &tablename, SelectStmt const &stmt)
void	exec (sqlite3 *db, std::string const &ddl)
std::string	assembleNoLockURI (std::string const &filename)
bool	hasTableWithSchema (sqlite3 *db, std::string const &tablename, std::string expectedSchema)
unsigned	nrows (sqlite3 *db, std::string const &tablename)
void	delete_from (sqlite3 *db, std::string const &tablename)
void	drop_table (sqlite3 *db, std::string const &tablename)
void	drop_table_if_exists (sqlite3 *db, std::string const &tablename)
template<typename... Args>
void	createTableIfNeeded (sqlite3 *db, bool const delete_contents, std::string const &tablename, permissive_column< Args > const &...cols)
auto	insert_into (sqlite3 *const db, std::string const &tablename)
template<typename T >
T	unique_value (query_result< T > const &r)
template<typename... Args>
std::ostream &	operator<< (std::ostream &os, query_result< Args... > const &res)
template<typename... Args>
query_result< Args... >	query (sqlite3 *db, std::string const &ddl)
template<typename... T>
auto	select (T const &...t)
template<typename... T>
auto	select_distinct (T const &...t)
template<typename... Args>
void	operator<< (query_result< Args... > &r, SelectStmt const &cq)
template<typename T = double>
T	min (sqlite3 *const db, std::string const &table_name, std::string const &column_name)
template<typename T = double>
T	max (sqlite3 *const db, std::string const &table_name, std::string const &column_name)
double	mean (sqlite3 *db, std::string const &table_name, std::string const &column_name)
double	median (sqlite3 *db, std::string const &table_name, std::string const &column_name)
double	rms (sqlite3 *db, std::string const &table_name, std::string const &column_name)

Typedef Documentation

using cet::sqlite::Exception = typedef cet::coded_exception<errors::ErrorCodes, ExceptionDetail::translate>

Definition at line 27 of file Exception.h.

template<size_t N>

using cet::sqlite::name_array = typedef std::array<std::string, N>

Definition at line 40 of file column.h.

Function Documentation

std::string cet::sqlite::assembleNoLockURI

(

std::string const &

filename

)

Definition at line 8 of file helpers.cc.

{
  // Arbitrary decision: don't allow users to specify a URI since
  // they may (unintentionally) remove the 'nolock' parameter, thus
  // potentially causing issues with NFS.
  if (filename.substr(0, 5) == "file:") {
    throw Exception{errors::OtherError}
      << "art does not allow an SQLite database filename that starts with "
         "'file:'.\n"
      << "Please contact artists@fnal.gov if you believe this is an error.";
  }
  return "file:" + filename + "?nolock=1";
}

template<typename... Cols>

void cet::sqlite::create_table	(	sqlite3 *const	db,
		std::string const &	tablename,
		Cols const &...	cols
	)

Definition at line 118 of file create_table.h.

  {
    auto const& ddl = detail::create_table_ddl(tablename, cols...);
    sqlite::exec(db, ddl);
  }

void cet::sqlite::create_table_as ( std::string const &  tablename, SelectStmt const &  stmt  )

inline

Definition at line 127 of file create_table.h.

  {
    std::string ddl{detail::create_table_as_ddl(tablename, stmt)};
    sqlite::exec(stmt.db_, ddl);
  }

template<typename... Args>

void cet::sqlite::createTableIfNeeded	(	sqlite3 *	db,
		bool const	delete_contents,
		std::string const &	tablename,
		permissive_column< Args > const &...	cols
	)

Definition at line 46 of file helpers.h.

{
  auto const& sqlddl = detail::create_table_ddl(tablename, cols...);
  if (hasTableWithSchema(db, tablename, sqlddl)) {
    if (delete_contents) {
      delete_from(db, tablename); // Prefer drop_table, but failure-to-prepare
                                  // exception ends up being thrown.
    }
  } else {
    exec(db, sqlddl);
  }
}

void cet::sqlite::delete_from	(	sqlite3 *	db,
		std::string const &	tablename
	)

Definition at line 63 of file helpers.cc.

{
  exec(db, "delete from "s + tablename + ';');
}

void cet::sqlite::drop_table	(	sqlite3 *	db,
		std::string const &	tablename
	)

Definition at line 69 of file helpers.cc.

{
  exec(db, "drop table "s + tablename + ';');
}

void cet::sqlite::drop_table_if_exists	(	sqlite3 *	db,
		std::string const &	tablename
	)

Definition at line 75 of file helpers.cc.

{
  exec(db, "drop table if exists "s + tablename + ';');
}

void cet::sqlite::exec	(	sqlite3 *	db,
		std::string const &	ddl
	)

Definition at line 5 of file exec.cc.

{
  char* errmsg{nullptr};
  if (sqlite3_exec(db, ddl.c_str(), nullptr, nullptr, &errmsg) != SQLITE_OK) {
    std::string msg{errmsg};
    sqlite3_free(errmsg);
    throw Exception{sqlite::errors::SQLExecutionError} << msg;
  }
}

bool cet::sqlite::hasTableWithSchema	(	sqlite3 *	db,
		std::string const &	tablename,
		std::string	expectedSchema
	)

Definition at line 29 of file helpers.cc.

{
  query_result<std::string> res;
  res << select("sql")
           .from(db, "sqlite_master")
           .where("type=\"table\" and name=\""s + name + '"');

  if (res.empty()) {
    return false;
  }

  // This is a somewhat fragile way of validating schemas.  A better
  // way would be to rely on sqlite3's insertion facilities to
  // determine if an insert of in-memory data would be compatible with
  // the on-disk schema.  This would require creating a temporary
  // table (so as to avoid inserting then deleting a dummy row into
  // the desired table)according to the on-disk schema, and inserting
  // some default values according to the requested schema.
  std::string retrievedSchema{unique_value(res)};
  detail::normalize_statement(retrievedSchema);
  detail::normalize_statement(expectedSchema);
  if (retrievedSchema == expectedSchema) {
    return true;
  }

  throw sqlite::Exception(sqlite::errors::SQLExecutionError)
    << "Existing database table name does not match the expected schema:\n"
    << "   Schema on disk : " << retrievedSchema << '\n'
    << "   Expected schema: " << expectedSchema << '\n';
}

auto cet::sqlite::insert_into ( sqlite3 *const  db, std::string const &  tablename  )

inline

Definition at line 110 of file insert.h.

  {
    std::string result{"insert into " + tablename};
    return IncompleteInsert{db, std::move(result)};
  }

template<typename T = double>

T cet::sqlite::max	(	sqlite3 *const	db,
		std::string const &	table_name,
		std::string const &	column_name
	)

Definition at line 66 of file statistics.h.

{
  query_result<T> r;
  r << select("max(" + column_name + ")").from(db, table_name);
  return unique_value(r);
}

double cet::sqlite::mean	(	sqlite3 *	db,
		std::string const &	table_name,
		std::string const &	column_name
	)

Definition at line 16 of file statistics.cc.

{
  query_result<double> r;
  r << select("avg(" + column_name + ")").from(db, table_name);
  return unique_value(r);
}

double cet::sqlite::median	(	sqlite3 *	db,
		std::string const &	table_name,
		std::string const &	column_name
	)

Definition at line 26 of file statistics.cc.

{
  auto r = query<double>(
    db,
    "select avg("s + column_name + ")" + " from (select " + column_name +
      " from " + table_name + " order by " + column_name +
      " limit 2 - (select count(*) from " + table_name + ") % 2" +
      " offset (select (count(*) - 1) / 2" + " from " + table_name + "))");
  return unique_value(r);
}

template<typename T = double>

T cet::sqlite::min	(	sqlite3 *const	db,
		std::string const &	table_name,
		std::string const &	column_name
	)

Definition at line 55 of file statistics.h.

{
  query_result<T> r;
  r << select("min(" + column_name + ")").from(db, table_name);
  return unique_value(r);
}

unsigned cet::sqlite::nrows	(	sqlite3 *	db,
		std::string const &	tablename
	)

Definition at line 82 of file helpers.cc.

{
  query_result<unsigned> r;
  r << select("count(*)").from(db, tablename);
  return unique_value(r);
}

template<typename... Args>

std::ostream& cet::sqlite::operator<<	(	std::ostream &	os,
		query_result< Args... > const &	res
	)

Definition at line 105 of file query_result.h.

  {
    using size_t = decltype(res.columns.size());
    auto const ncolumns = res.columns.size();
    for (size_t i{}; i != ncolumns; ++i) {
      os << res.columns[i] << ' ';
    }
    os << "\n--------------------------------\n";
    for (auto const& row : res.data) {
      os << row.str() << '\n';
    }
    return os;
  }

template<typename... Args>

void cet::sqlite::operator<<	(	query_result< Args... > &	r,
		SelectStmt const &	cq
	)

Definition at line 162 of file select.h.

  {
    r = query<Args...>(cq.db_, cq.ddl_ + ";");
  }

template<typename... Args>

query_result<Args...> cet::sqlite::query	(	sqlite3 *	db,
		std::string const &	ddl
	)

Definition at line 75 of file select.h.

  {
    query_result<Args...> res;
    char* errmsg{nullptr};
    if (sqlite3_exec(
          db, ddl.c_str(), detail::get_result<Args...>, &res, &errmsg) !=
        SQLITE_OK) {
      std::string msg{errmsg};
      sqlite3_free(errmsg);
      throw sqlite::Exception{sqlite::errors::SQLExecutionError} << msg;
    }
    return res;
  }

double cet::sqlite::rms	(	sqlite3 *	db,
		std::string const &	table_name,
		std::string const &	column_name
	)

Definition at line 40 of file statistics.cc.

{
  auto r = query<double>(db,
                         "select sum("s + "(" + column_name + "-(select avg(" +
                           column_name + ") from " + table_name + "))" + "*" +
                           "(" + column_name + "-(select avg(" + column_name +
                           ") from " + table_name + "))" + " ) /" + "(count(" +
                           column_name + ")) from " + table_name);
  return std::sqrt(unique_value(r));
}

template<typename... T>

auto cet::sqlite::select

(

T const &...

t

)

Definition at line 146 of file select.h.

  {
    std::string result{"select " + detail::concatenate(t...)};
    return IncompleteSelectStmt{std::move(result)};
  }

template<typename... T>

auto cet::sqlite::select_distinct

(

T const &...

t

)

Definition at line 154 of file select.h.

  {
    std::string result{"select distinct " + detail::concatenate(t...)};
    return IncompleteSelectStmt{std::move(result)};
  }

template<typename T >

T cet::sqlite::unique_value ( query_result< T > const &  r )

inline

Definition at line 94 of file query_result.h.

  {
    if (r.data.size() != 1ull) {
      throw sqlite::Exception{sqlite::errors::SQLExecutionError}
        << "unique_value expected of non-unique query.";
    }
    return std::get<T>(r.data[0]);
  }