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 {
72  namespace sqlite {
73 
74  template <typename... Args>
75  query_result<Args...>
76  query(sqlite3* db, std::string const& ddl)
77  {
78  query_result<Args...> res;
79  char* errmsg{nullptr};
80  if (sqlite3_exec(
81  db, ddl.c_str(), detail::get_result<Args...>, &res, &errmsg) !=
82  SQLITE_OK) {
83  std::string msg{errmsg};
84  sqlite3_free(errmsg);
86  }
87  return res;
88  }
89 
90  struct SelectStmt {
91  SelectStmt(std::string&& ddl, sqlite3* const db)
92  : ddl_{std::move(ddl)}, db_{db}
93  {}
95  sqlite3* db_;
96 
97  auto
98  where(std::string const& cond) &&
99  {
100  ddl_ += " where ";
101  ddl_ += cond;
102  return SelectStmt{std::move(ddl_), db_};
103  }
104 
105  auto
106  order_by(std::string const& column, std::string const& clause = {}) &&
107  {
108  ddl_ += " order by ";
109  ddl_ += column;
110  ddl_ += " " + clause;
111  return SelectStmt{std::move(ddl_), db_};
112  }
113 
114  auto
115  limit(int const num) &&
116  {
117  ddl_ += " limit ";
118  ddl_ += std::to_string(num);
119  return SelectStmt{std::move(ddl_), db_};
120  }
121  };
122 
124 
125  IncompleteSelectStmt(std::string&& ddl) : ddl_{std::move(ddl)} {}
126 
127  auto
128  from(sqlite3* const db, std::string const& tablename) &&
129  {
130  ddl_ += " from ";
131  ddl_ += tablename;
132  return SelectStmt{std::move(ddl_), db};
133  }
135  };
136 
137  namespace detail {
138  inline std::string
140  {
141  return "";
142  }
143 
144  template <typename H, typename... T>
146  concatenate(H const& h, T const&... t)
147  {
148  return sizeof...(t) != 0u ? std::string{h} + "," + concatenate(t...) :
149  std::string{h};
150  }
151  }
152 
153  template <typename... T>
154  auto
155  select(T const&... t)
156  {
157  std::string result{"select " + detail::concatenate(t...)};
158  return IncompleteSelectStmt{std::move(result)};
159  }
160 
161  template <typename... T>
162  auto
163  select_distinct(T const&... t)
164  {
165  std::string result{"select distinct " + detail::concatenate(t...)};
166  return IncompleteSelectStmt{std::move(result)};
167  }
168 
169  template <typename... Args>
170  void
172  {
173  r = query<Args...>(cq.db_, cq.ddl_ + ";");
174  }
175 
176  } // sqlite
177 } // cet
178 
179 #endif /* cetlib_sqlite_select_h */
180 
181 // Local variables:
182 // mode: c++
183 // End:
auto order_by(std::string const &column, std::string const &clause={})&&
Definition: select.h:106
auto select_distinct(T const &...t)
Definition: select.h:163
cet::coded_exception< errors::ErrorCodes, ExceptionDetail::translate > Exception
Definition: Exception.h:33
std::string concatenate(H const &h, T const &...t)
Definition: select.h:146
SelectStmt(std::string &&ddl, sqlite3 *const db)
Definition: select.h:91
std::string ddl_
Definition: select.h:94
std::string concatenate()
Definition: select.h:139
auto select(T const &...t)
Definition: select.h:155
std::ostream & operator<<(std::ostream &os, query_result< Args... > const &res)
Definition: query_result.h:112
auto limit(int const num)&&
Definition: select.h:115
int num
Definition: f2_nu.C:119
int get_result(void *data, int ncols, char **results, char **cnames)
Definition: get_result.h:36
TRandom3 r(0)
auto from(sqlite3 *const db, std::string const &tablename)&&
Definition: select.h:128
double T
Definition: Xdiff_gwt.C:5
std::string to_string(ModuleType mt)
Definition: ModuleType.h:32
query_result< Args... > query(sqlite3 *db, std::string const &ddl)
Definition: select.h:76
IncompleteSelectStmt(std::string &&ddl)
Definition: select.h:125
auto where(std::string const &cond)&&
Definition: select.h:98
enum BeamMode string