select.h
Go to the documentation of this file.
1 #ifndef cetlib_sqlite_select_h
2 #define cetlib_sqlite_select_h
3 
4 // ====================================================================
5 // The facilities presented here provide two ways of querying an
6 // SQLite database without having to use the C API. Both approach
7 // involve using a cet::sqlite::query_result template object.
8 //
9 // Type-safe interface
10 // -------------------
11 //
12 // The type-safe interface is the first approach to be used when
13 // needing to query the database. Instead of explicitly typing a
14 // lengthy character string as the entire select statement, the string
15 // is assembled for you via function calls. For example (e.g.):
16 //
17 // auto stmt = select("id","name").from(db,"workers");
18 //
19 // results in the creation of a SelectStmt object (here 'stmt'), which
20 // can then be used to query the database. The encouraged usage
21 // pattern is to create the query_result object into which the results
22 // of the query will be inserted. Then, the query itself is executed:
23 //
24 // query_result<int,string> res;
25 // res << select("id","name").from(db,"workers");
26 //
27 // The query is NOT executed until, the operator<< function is called.
28 // If a user attempts to execute a query with an incorrectly formed select
29 // statement (e.g.):
30 //
31 // res << select("id","name");
32 //
33 // a compile-time error will be triggered. In this way, using the
34 // 'select' facility, and its associated subsequent function calls
35 // (e.g. 'from') ensure a safer, type-safe approach to making SQLite
36 // queries that avoid typographical errors.
37 //
38 // One can assemble more complicated statements (e.g.):
39 //
40 // select().from().where().order_by().limit();
41 //
42 // It is incumbent on the user to know how select statements can be
43 // meaningfully formed in SQLite to inform which functions can be
44 // called after the initial 'select()' call.
45 //
46 // N.B. It is still possible to make typographical errors with the
47 // above interface since the required arguments to some of the
48 // functions are strings. However, it is less likely that one
49 // will introduce an error using this approach.
50 //
51 // String-based interface
52 // ----------------------
53 //
54 // For complicated querying statements that cannot be represented by
55 // the type-safe interface, the cet::sqlite::query can be used (e.g.):
56 //
57 // auto r = query<double, int>(db, "SELECT ... "); // ==>
58 // query_result<double,int>
59 //
60 // Although quite flexible, use of query is prone to typographical
61 // errors that are less likely when using the type-safe interface.
62 // ====================================================================
63 
66 
67 #include <string>
68 
69 #include "sqlite3.h"
70 
71 namespace cet::sqlite {
72 
73  template <typename... Args>
74  query_result<Args...>
75  query(sqlite3* db, std::string const& ddl)
76  {
77  query_result<Args...> res;
78  char* errmsg{nullptr};
79  if (sqlite3_exec(
80  db, ddl.c_str(), detail::get_result<Args...>, &res, &errmsg) !=
81  SQLITE_OK) {
82  std::string msg{errmsg};
83  sqlite3_free(errmsg);
85  }
86  return res;
87  }
88 
89  struct SelectStmt {
90  SelectStmt(std::string&& ddl, sqlite3* const db)
91  : ddl_{std::move(ddl)}, db_{db}
92  {}
94  sqlite3* db_;
95 
96  auto
97  where(std::string const& cond) &&
98  {
99  ddl_ += " where ";
100  ddl_ += cond;
101  return SelectStmt{std::move(ddl_), db_};
102  }
103 
104  auto
105  order_by(std::string const& column, std::string const& clause = {}) &&
106  {
107  ddl_ += " order by ";
108  ddl_ += column;
109  ddl_ += " " + clause;
110  return SelectStmt{std::move(ddl_), db_};
111  }
112 
113  auto
114  limit(int const num) &&
115  {
116  ddl_ += " limit ";
117  ddl_ += std::to_string(num);
118  return SelectStmt{std::move(ddl_), db_};
119  }
120  };
121 
123  IncompleteSelectStmt(std::string&& ddl) : ddl_{std::move(ddl)} {}
124 
125  auto
126  from(sqlite3* const db, std::string const& tablename) &&
127  {
128  ddl_ += " from ";
129  ddl_ += tablename;
130  return SelectStmt{std::move(ddl_), db};
131  }
133  };
134 
135  namespace detail {
136  template <typename H, typename... T>
138  concatenate(H const& h, T const&... t)
139  {
140  return (std::string{h} + ... + ("," + std::string{t}));
141  }
142  }
143 
144  template <typename... T>
145  auto
146  select(T const&... t)
147  {
148  std::string result{"select " + detail::concatenate(t...)};
149  return IncompleteSelectStmt{std::move(result)};
150  }
151 
152  template <typename... T>
153  auto
154  select_distinct(T const&... t)
155  {
156  std::string result{"select distinct " + detail::concatenate(t...)};
157  return IncompleteSelectStmt{std::move(result)};
158  }
159 
160  template <typename... Args>
161  void
163  {
164  r = query<Args...>(cq.db_, cq.ddl_ + ";");
165  }
166 
167 } // cet::sqlite
168 
169 #endif /* cetlib_sqlite_select_h */
170 
171 // Local variables:
172 // mode: c++
173 // End:
auto order_by(std::string const &column, std::string const &clause={})&&
Definition: select.h:105
auto select_distinct(T const &...t)
Definition: select.h:154
cet::coded_exception< errors::ErrorCodes, ExceptionDetail::translate > Exception
Definition: Exception.h:27
std::string concatenate(H const &h, T const &...t)
Definition: select.h:138
SelectStmt(std::string &&ddl, sqlite3 *const db)
Definition: select.h:90
std::string ddl_
Definition: select.h:93
auto select(T const &...t)
Definition: select.h:146
std::ostream & operator<<(std::ostream &os, query_result< Args... > const &res)
Definition: query_result.h:105
auto limit(int const num)&&
Definition: select.h:114
int num
Definition: f2_nu.C:119
int get_result(void *data, int ncols, char **results, char **cnames)
Definition: get_result.h:34
TRandom3 r(0)
auto from(sqlite3 *const db, std::string const &tablename)&&
Definition: select.h:126
double T
Definition: Xdiff_gwt.C:5
query_result< Args... > query(sqlite3 *db, std::string const &ddl)
Definition: select.h:75
std::string to_string(ModuleType const mt)
Definition: ModuleType.h:34
IncompleteSelectStmt(std::string &&ddl)
Definition: select.h:123
auto where(std::string const &cond)&&
Definition: select.h:97
enum BeamMode string