CYCLUS
sqlite_back.cc
Go to the documentation of this file.
1 #include "sqlite_back.h"
2 
3 #include <iomanip>
4 #include <sstream>
5 
6 #include <boost/lexical_cast.hpp>
7 #include <boost/uuid/uuid_io.hpp>
8 #include <boost/algorithm/string.hpp>
9 #include <boost/archive/tmpdir.hpp>
10 #include <boost/archive/xml_iarchive.hpp>
11 #include <boost/archive/xml_oarchive.hpp>
12 #include <boost/serialization/base_object.hpp>
13 #include <boost/serialization/utility.hpp>
14 #include <boost/serialization/list.hpp>
15 #include <boost/serialization/set.hpp>
16 #include <boost/serialization/vector.hpp>
17 #include <boost/serialization/map.hpp>
18 #include <boost/serialization/assume_abstract.hpp>
19 
20 
21 #include "blob.h"
22 #include "datum.h"
23 #include "error.h"
24 #include "logger.h"
25 
26 namespace cyclus {
27 
28 std::vector<std::string> split(const std::string& s, char delim) {
29  std::vector<std::string> elems;
30  std::stringstream ss(s);
31  std::string item;
32  while (std::getline(ss, item, delim)) {
33  elems.push_back(item);
34  }
35  return elems;
36 }
37 
39  try {
40  Flush();
41  db_.close();
42  } catch (Error err) {
43  CLOG(LEV_ERROR) << "Error in SqliteBack destructor: " << err.what();
44  }
45 }
46 
47 SqliteBack::SqliteBack(std::string path) : db_(path) {
48  path_ = path;
49  db_.open();
50 
51  db_.Execute("PRAGMA synchronous=OFF;");
52  db_.Execute("PRAGMA journal_mode=MEMORY;");
53  db_.Execute("PRAGMA temp_store=MEMORY;");
54 
55  // cache pre-existing table names
56  SqlStatement::Ptr stmt;
57  stmt = db_.Prepare("SELECT name FROM sqlite_master WHERE type='table';");
58 
59  for (int i = 0; stmt->Step(); ++i) {
60  tbl_names_.insert(stmt->GetText(0, NULL));
61  }
62 
63  if (tbl_names_.count("FieldTypes") == 0) {
64  std::string cmd = "CREATE TABLE IF NOT EXISTS FieldTypes";
65  cmd += "(TableName TEXT,Field TEXT,Type INTEGER);";
66  db_.Execute(cmd);
67  }
68 }
69 
71  db_.Execute("BEGIN TRANSACTION;");
72  try {
73  for (DatumList::iterator it = data.begin(); it != data.end(); ++it) {
74  std::string tbl = (*it)->title();
75  if (tbl_names_.count(tbl) == 0) {
76  CreateTable(*it);
77  }
78  if (stmts_.count(tbl) == 0) {
79  BuildStmt(*it);
80  }
81  WriteDatum(*it);
82  }
83  } catch (ValueError err) {
84  db_.Execute("END TRANSACTION;");
85  throw ValueError(err.what());
86  }
87  db_.Execute("END TRANSACTION;");
88  Flush();
89 }
90 
92 
93 QueryResult SqliteBack::Query(std::string table, std::vector<Cond>* conds) {
94  QueryResult q = GetTableInfo(table);
95 
96  std::stringstream sql;
97  sql << "SELECT * FROM " << table;
98  if (conds != NULL) {
99  sql << " WHERE ";
100  for (int i = 0; i < conds->size(); ++i) {
101  if (i > 0) {
102  sql << " AND ";
103  }
104  Cond c = (*conds)[i];
105  sql << c.field << " " << c.op << " ?";
106  }
107  }
108  sql << ";";
109 
110  SqlStatement::Ptr stmt = db_.Prepare(sql.str());
111 
112  if (conds != NULL) {
113  for (int i = 0; i < conds->size(); ++i) {
114  boost::spirit::hold_any v = (*conds)[i].val;
115  Bind(v, Type(v), stmt, i+1);
116  }
117  }
118 
119  for (int i = 0; stmt->Step(); ++i) {
120  QueryRow r;
121  for (int j = 0; j < q.fields.size(); ++j) {
122  r.push_back(ColAsVal(stmt, j, q.types[j]));
123  }
124  q.rows.push_back(r);
125  }
126  return q;
127 }
128 
129 std::map<std::string, DbTypes> SqliteBack::ColumnTypes(std::string table) {
130  QueryResult qr = GetTableInfo(table);
131  std::map<std::string, DbTypes> rtn;
132  for (int i = 0; i < qr.fields.size(); ++i)
133  rtn[qr.fields[i]] = qr.types[i];
134  return rtn;
135 }
136 
137 std::set<std::string> SqliteBack::Tables() {
138  using std::set;
139  using std::string;
140  set<string> rtn;
141  std::string sql = "SELECT name FROM sqlite_master WHERE type='table';";
142  SqlStatement::Ptr stmt;
143  stmt = db_.Prepare(sql);
144  while (stmt->Step()) {
145  rtn.insert(stmt->GetText(0, NULL));
146  }
147  return rtn;
148 }
149 
151  return db_;
152 }
153 
154 QueryResult SqliteBack::GetTableInfo(std::string table) {
155  std::string sql = "SELECT Field,Type FROM FieldTypes WHERE TableName = '" +
156  table + "';";
157  SqlStatement::Ptr stmt;
158  stmt = db_.Prepare(sql);
159 
160  int i = 0;
161  QueryResult info;
162  for (i = 0; stmt->Step(); ++i) {
163  info.fields.push_back(stmt->GetText(0, NULL));
164  info.types.push_back((DbTypes)stmt->GetInt(1));
165  }
166  if (i == 0) {
167  throw ValueError("Invalid table name " + table);
168  }
169  return info;
170 }
171 
172 std::string SqliteBack::Name() {
173  return path_;
174 }
175 
176 void SqliteBack::BuildStmt(Datum* d) {
177  std::string name = d->title();
178  Datum::Vals vals = d->vals();
179  std::vector<DbTypes> schema;
180 
181  schema.push_back(Type(vals[0].second));
182  std::string insert = "INSERT INTO " + name + " VALUES (?";
183  for (int i = 1; i < vals.size(); ++i) {
184  schema.push_back(Type(vals[i].second));
185  insert += ", ?";
186  }
187  insert += ");";
188 
189  schemas_[name] = schema;
190  stmts_[name] = db_.Prepare(insert);
191 }
192 
193 void SqliteBack::CreateTable(Datum* d) {
194  std::string name = d->title();
195  tbl_names_.insert(name);
196 
197  Datum::Vals vals = d->vals();
198  Datum::Vals::iterator it = vals.begin();
199 
200  std::stringstream types;
201  types << "INSERT INTO FieldTypes VALUES ('"
202  << name << "','" << it->first << "','"
203  << Type(it->second) << "');";
204  db_.Execute(types.str());
205 
206  std::string cmd = "CREATE TABLE " + name + " (";
207  cmd += std::string(it->first) + " " + SqlType(it->second);
208  ++it;
209 
210  while (it != vals.end()) {
211  cmd += ", " + std::string(it->first) + " " + SqlType(it->second);
212  std::stringstream types;
213  types << "INSERT INTO FieldTypes VALUES ('"
214  << name << "','" << it->first << "','"
215  << Type(it->second) << "');";
216  db_.Execute(types.str());
217  ++it;
218  }
219 
220  cmd += ");";
221  db_.Execute(cmd);
222 }
223 
224 void SqliteBack::WriteDatum(Datum* d) {
225  Datum::Vals vals = d->vals();
226  SqlStatement::Ptr stmt = stmts_[d->title()];
227  std::vector<DbTypes> schema = schemas_[d->title()];
228 
229  for (int i = 0; i < vals.size(); ++i) {
230  boost::spirit::hold_any v = vals[i].second;
231  Bind(v, schema[i], stmt, i+1);
232  }
233 
234  stmt->Exec();
235 }
236 
237 void SqliteBack::Bind(boost::spirit::hold_any v, DbTypes type, SqlStatement::Ptr stmt,
238  int index) {
239 
240 // serializes the value v of type T and DBType D and binds it to stmt (inside
241 // a case statement
242 #define CYCLUS_COMMA ,
243 #define CYCLUS_BINDVAL(D, T) \
244  case D: { \
245  T vect = v.cast<T>(); \
246  std::stringstream ss; \
247  boost::archive::xml_oarchive ar(ss); \
248  ar & BOOST_SERIALIZATION_NVP(vect); \
249  std::string s = ss.str(); \
250  stmt->BindBlob(index, s.c_str(), s.size()); \
251  break; \
252  }
253 
254  switch (type) {
255  case INT: {
256  stmt->BindInt(index, v.cast<int>());
257  break;
258  }
259  case BOOL: {
260  stmt->BindInt(index, v.cast<bool>());
261  break;
262  }
263  case DOUBLE: {
264  stmt->BindDouble(index, v.cast<double>());
265  break;
266  }
267  case FLOAT: {
268  stmt->BindDouble(index, v.cast<float>());
269  break;
270  }
271  case BLOB: {
272  std::string s = v.cast<Blob>().str();
273  stmt->BindBlob(index, s.c_str(), s.size());
274  break;
275  }
276  case STRING: {
277  stmt->BindText(index, v.cast<std::string>().c_str());
278  break;
279  }
280  case UUID: {
281  boost::uuids::uuid ui = v.cast<boost::uuids::uuid>();
282  stmt->BindBlob(index, ui.data, 16);
283  break;
284  }
285  CYCLUS_BINDVAL(SET_INT, std::set<int>);
286  CYCLUS_BINDVAL(SET_STRING, std::set<std::string>);
287  CYCLUS_BINDVAL(LIST_INT, std::list<int>);
288  CYCLUS_BINDVAL(LIST_STRING, std::list<std::string>);
289  CYCLUS_BINDVAL(VECTOR_INT, std::vector<int>);
290  CYCLUS_BINDVAL(VECTOR_DOUBLE, std::vector<double>);
291  CYCLUS_BINDVAL(VECTOR_STRING, std::vector<std::string>);
292  CYCLUS_BINDVAL(MAP_INT_DOUBLE, std::map<int CYCLUS_COMMA double>);
293  CYCLUS_BINDVAL(MAP_INT_INT, std::map<int CYCLUS_COMMA int>);
294  CYCLUS_BINDVAL(MAP_INT_STRING, std::map<int CYCLUS_COMMA std::string>);
295  CYCLUS_BINDVAL(MAP_STRING_INT, std::map<std::string CYCLUS_COMMA int>);
296  CYCLUS_BINDVAL(MAP_STRING_DOUBLE, std::map<std::string CYCLUS_COMMA double>);
298  std::map<std::string CYCLUS_COMMA std::string>);
300  std::map<std::string CYCLUS_COMMA std::vector<double> >);
303  std::map<std::string CYCLUS_COMMA std::map<int CYCLUS_COMMA double> >);
305  std::map<std::string CYCLUS_COMMA std::pair<
306  double CYCLUS_COMMA std::map<int CYCLUS_COMMA double> > >);
308  std::map<int CYCLUS_COMMA
309  std::map<std::string CYCLUS_COMMA double> >);
312  std::map<std::string CYCLUS_COMMA
313  std::vector<std::pair<int CYCLUS_COMMA
314  std::pair<std::string CYCLUS_COMMA std::string> > > >);
315 
318  std::map<std::string CYCLUS_COMMA
319  std::pair<std::string CYCLUS_COMMA std::vector<double> > > );
320 
321  CYCLUS_BINDVAL(LIST_PAIR_INT_INT, std::list< std::pair<int CYCLUS_COMMA int> >);
322 
325  std::map<std::string CYCLUS_COMMA std::map<std::string CYCLUS_COMMA int> >);
326 
329  std::vector<std::pair<
330  std::pair<double CYCLUS_COMMA double> CYCLUS_COMMA
331  std::map<std::string CYCLUS_COMMA double> > > );
332 
333  default: {
334  throw ValueError("attempted to retrieve unsupported sqlite backend type");
335  }
336  }
337 #undef CYCLUS_BINDVAL
338 #undef CYCLUS_COMMA
339 }
340 
341 boost::spirit::hold_any SqliteBack::ColAsVal(SqlStatement::Ptr stmt,
342  int col,
343  DbTypes type) {
344 
346 
347 // reconstructs from a serialization in stmt of type T and DbType D and
348 // store it in v.
349 #define CYCLUS_COMMA ,
350 #define CYCLUS_LOADVAL(D, T) \
351  case D: { \
352  char* data = stmt->GetText(col, NULL); \
353  std::stringstream ss; \
354  ss << data; \
355  boost::archive::xml_iarchive ar(ss); \
356  T vect; \
357  ar & BOOST_SERIALIZATION_NVP(vect); \
358  v = vect; \
359  break; \
360  }
361 
362  switch (type) {
363  case INT: {
364  v = stmt->GetInt(col);
365  break;
366  } case BOOL: {
367  v = static_cast<bool>(stmt->GetInt(col));
368  break;
369  } case DOUBLE: {
370  v = stmt->GetDouble(col);
371  break;
372  } case FLOAT: {
373  v = static_cast<float>(stmt->GetDouble(col));
374  break;
375  } case STRING: {
376  v = std::string(stmt->GetText(col, NULL));
377  break;
378  } case BLOB: {
379  int n;
380  char* s = stmt->GetText(col, &n);
381  v = Blob(std::string(s, n));
382  break;
383  } case UUID: {
384  boost::uuids::uuid u;
385  memcpy(&u, stmt->GetText(col, NULL), 16);
386  v = u;
387  break;
388  }
389  CYCLUS_LOADVAL(SET_INT, std::set<int>);
390  CYCLUS_LOADVAL(SET_STRING, std::set<std::string>);
391  CYCLUS_LOADVAL(LIST_INT, std::list<int>);
392  CYCLUS_LOADVAL(LIST_STRING, std::list<std::string>);
393  CYCLUS_LOADVAL(VECTOR_INT, std::vector<int>);
394  CYCLUS_LOADVAL(VECTOR_DOUBLE, std::vector<double>);
395  CYCLUS_LOADVAL(VECTOR_STRING, std::vector<std::string>);
396  CYCLUS_LOADVAL(MAP_INT_DOUBLE, std::map<int CYCLUS_COMMA double>);
397  CYCLUS_LOADVAL(MAP_INT_INT, std::map<int CYCLUS_COMMA int>);
398  CYCLUS_LOADVAL(MAP_INT_STRING, std::map<int CYCLUS_COMMA std::string>);
399  CYCLUS_LOADVAL(MAP_STRING_DOUBLE, std::map<std::string CYCLUS_COMMA double>);
400  CYCLUS_LOADVAL(MAP_STRING_INT, std::map<std::string CYCLUS_COMMA int>);
402  std::map<std::string CYCLUS_COMMA std::string>);
404  std::map<std::string CYCLUS_COMMA std::vector<double> >);
407  std::map<std::string CYCLUS_COMMA std::map<int CYCLUS_COMMA double> >);
409  std::map<std::string CYCLUS_COMMA std::pair<
410  double CYCLUS_COMMA std::map<int CYCLUS_COMMA double> > >);
412  std::map<int CYCLUS_COMMA
413  std::map<std::string CYCLUS_COMMA double> >);
416  std::map<std::string CYCLUS_COMMA
417  std::vector<std::pair<int CYCLUS_COMMA
418  std::pair<std::string CYCLUS_COMMA std::string> > > >);
419 
422  std::map<std::string CYCLUS_COMMA
423  std::pair<std::string CYCLUS_COMMA std::vector<double> > > );
424 
425  CYCLUS_LOADVAL(LIST_PAIR_INT_INT, std::list< std::pair<int CYCLUS_COMMA int> >);
428  std::map<std::string CYCLUS_COMMA std::map<std::string CYCLUS_COMMA int> >);
429 
432  std::vector<std::pair<
433  std::pair<double CYCLUS_COMMA double> CYCLUS_COMMA
434  std::map<std::string CYCLUS_COMMA double> > > );
435 
436  default: {
437  throw ValueError("Attempted to retrieve unsupported backend type");
438  }}
439 
440 #undef CYCLUS_LOADVAL
441 #undef CYCLUS_COMMA
442 
443  return v;
444 }
445 
446 std::string SqliteBack::SqlType(boost::spirit::hold_any v) {
447  switch (Type(v)) {
448  case INT: // fallthrough
449  case BOOL:
450  return "INTEGER";
451  case DOUBLE: // fallthrough
452  case FLOAT:
453  return "REAL";
454  case STRING:
455  return "TEXT";
456  case BLOB: // fallthrough
457  case UUID: // fallthrough
458  default: // all templated types
459  return "BLOB";
460  }
461 }
462 
463 struct compare {
464  bool operator()(const std::type_info* a, const std::type_info* b) const {
465  return a->before(*b);
466  }
467 };
468 
469 static std::map<const std::type_info*, DbTypes, compare> type_map;
470 
471 DbTypes SqliteBack::Type(boost::spirit::hold_any v) {
472  if (type_map.size() == 0) {
473  type_map[&typeid(int)] = INT;
474  type_map[&typeid(double)] = DOUBLE;
475  type_map[&typeid(float)] = FLOAT;
476  type_map[&typeid(bool)] = BOOL;
477  type_map[&typeid(Blob)] = BLOB;
478  type_map[&typeid(boost::uuids::uuid)] = UUID;
479  type_map[&typeid(std::string)] = STRING;
480  type_map[&typeid(std::set<int>)] = SET_INT;
481  type_map[&typeid(std::set<std::string>)] = SET_STRING;
482  type_map[&typeid(std::vector<int>)] = VECTOR_INT;
483  type_map[&typeid(std::vector<double>)] = VECTOR_DOUBLE;
484  type_map[&typeid(std::vector<std::string>)] = VECTOR_STRING;
485  type_map[&typeid(std::list<int>)] = LIST_INT;
486  type_map[&typeid(std::list<std::string>)] = LIST_STRING;
487  type_map[&typeid(std::map<int, int>)] = MAP_INT_INT;
488  type_map[&typeid(std::map<int, double>)] = MAP_INT_DOUBLE;
489  type_map[&typeid(std::map<int, std::string>)] = MAP_INT_STRING;
490  type_map[&typeid(std::map<std::string, int>)] = MAP_STRING_INT;
491  type_map[&typeid(std::map<std::string, double>)] = MAP_STRING_DOUBLE;
492  type_map[&typeid(std::map<std::string, std::string>)] = MAP_STRING_STRING;
493  type_map[&typeid(std::map<std::string, std::vector<double> >)] =
495  type_map[&typeid(std::map<std::string, std::map<int, double> >)] =
497  type_map[&typeid(std::map<std::string,
498  std::pair<double, std::map<int, double> > >)] =
500  type_map[&typeid(std::map<int, std::map<std::string, double> >)] =
502  type_map[&typeid(
503  std::map<std::string,
504  std::vector<std::pair<int, std::pair<std::string,
505  std::string> > > >)] =
507 
508  type_map[&typeid(
509  std::map<std::string,
510  std::pair<std::string,
511  std::vector<double> > >)] =
513 
514  type_map[&typeid(std::map<std::string, std::map<std::string,int> >)] =
516 
517  type_map[&typeid(std::list<std::pair<int, int> >)] = LIST_PAIR_INT_INT;
518 
519  type_map[&typeid(
520  std::vector<std::pair<std::pair<double, double>,
521  std::map<std::string, double> > > )] =
523 
524  }
525 
526  const std::type_info* ti = &v.type();
527  if (type_map.count(ti) == 0) {
528  throw ValueError(std::string("unsupported backend type ") + ti->name());
529  }
530  return type_map[ti];
531 }
532 
533 } // namespace cyclus
const Vals & vals()
Returns a vector of all field-value pairs that have been added to this datum.
Definition: datum.cc:44
std::string title()
Returns the datum&#39;s title as specified during the datum&#39;s creation.
Definition: datum.cc:40
std::string op
One of: "<", ">", "<=", ">=", "==", "!=".
double b(int nuc)
Computes the scattering length [cm] from the coherent and incoherent components.
Definition: pyne.cc:11180
A generic mechanism to manually manage exceptions.
Definition: error.h:12
Meta data and results of a query.
An abstraction over the Sqlite native C interface to simplify database creation and data insertion...
Definition: sqlite_db.h:77
#define CYCLUS_LOADVAL(D, T)
For values that are too big, too small, etc.
Definition: error.h:41
std::string Name()
Returns a unique name for this backend.
Definition: sqlite_back.cc:172
std::string name(int nuc)
Definition: pyne.cc:2940
SqlStatement::Ptr Prepare(std::string sql)
Creates a sqlite prepared statement for the given sql.
Definition: sqlite_db.cc:133
DbTypes
This is the master list of all supported database types.
Definition: query_backend.h:26
virtual void Notify(DatumList data)
Writes Datum objects immediately to the database as a single transaction.
Definition: sqlite_back.cc:70
#define CLOG(level)
Definition: logger.h:39
static std::map< const std::type_info *, DbTypes, compare > type_map
Definition: sqlite_back.cc:469
void open()
Opens the sqlite database by either opening/creating a file (default) or creating/overwriting a file ...
Definition: sqlite_db.cc:108
virtual QueryResult Query(std::string table, std::vector< Cond > *conds)
Return a set of rows from the specificed table that match all given conditions.
Definition: sqlite_back.cc:93
SqliteBack(std::string path)
Creates a new sqlite backend that will write to the database file specified by path.
Definition: sqlite_back.cc:47
Used to specify and send a collection of key-value pairs to the Recorder for recording.
Definition: datum.h:15
bool operator()(const std::type_info *a, const std::type_info *b) const
Definition: sqlite_back.cc:464
std::vector< boost::spirit::hold_any > QueryRow
std::vector< std::string > fields
names of each field returned by a query
virtual std::set< std::string > Tables()
Return a set of all table names currently in the database.
Definition: sqlite_back.cc:137
std::vector< Entry > Vals
Definition: datum.h:20
boost::detail::sp_typeinfo const & type() const
Definition: any.hpp:303
void Flush()
Executes all pending commands.
Definition: sqlite_back.cc:91
std::vector< DbTypes > types
types of each field returned by a query.
std::string field
table column name
boost::shared_ptr< SqlStatement > Ptr
Definition: sqlite_db.h:25
virtual const char * what() const
Returns the error message associated with this Error.
Definition: error.cc:9
Code providing rudimentary logging capability for the Cyclus core.
T const & cast() const
Definition: any.hpp:309
taken directly from OsiSolverInterface.cpp on 2/17/14 from https://projects.coin-or.org/Osi/browser/trunk.
Definition: agent.cc:14
A type to represent variable-length array of bytes for dumping to a cyclus output database...
Definition: blob.h:9
std::vector< QueryRow > rows
ordered results of a query
#define CYCLUS_COMMA
Represents a condition used to filter rows returned by a query.
void close()
Finishes any incomplete operations and closes the database.
Definition: sqlite_db.cc:99
#define CYCLUS_BINDVAL(D, T)
std::vector< Datum * > DatumList
Definition: rec_backend.h:12
Use for errors that require agent code or input file modification (use extremely sparingly) ...
Definition: logger.h:51
virtual std::map< std::string, DbTypes > ColumnTypes(std::string table)
Return a map of column names of the specified table to the associated database type.
Definition: sqlite_back.cc:129
virtual ~SqliteBack()
Definition: sqlite_back.cc:38
SqliteDb & db()
Returns the underlying sqlite database.
Definition: sqlite_back.cc:150
void Execute(std::string cmd)
Execute an SQL command.
Definition: sqlite_db.cc:139
std::vector< std::string > split(const std::string &s, char delim)
Definition: sqlite_back.cc:28