KaliVeda  1.13/01
Heavy-Ion Analysis Toolkit
SQLiteDB.h
Go to the documentation of this file.
1 
4 #ifndef __SQLITEDB_H
5 #define __SQLITEDB_H
6 
7 #include "TSQLiteServer.h"
8 #include "TSQLStatement.h"
9 
10 #include "KVConfig.h"
11 #include <utility>
12 #include <iostream>
13 #include <unordered_map>
14 #include <list>
15 #include <map>
16 #include <KVNameValueList.h>
17 #include <KVNumberList.h>
18 #include "TGraph.h"
19 
20 namespace KVSQLite {
21 
90  namespace column_type {
91  enum types {
95  BLOB
96  };
97  }
98  namespace insert_mode {
99  enum types {
103  REPLACE
104  };
105  }
106 
109 
110  class table;
111 
112  class column {
113 
114  friend class table;
115 
116  std::pair<TString, KVSQLite_column_type> fNameType; //name & type of column
117  TString fConstraint;//column constraint
118  int fIndex;//index of column
119  static std::map<KVSQLite::column_type::types, TString> inv_type_map;
120  mutable KVNamedParameter fData; // data item in column
121  mutable void* fBlob;
122  mutable Long_t fBlobSize;// size of blob
125  TString fFKtable;//table for foreign key
126  TString fFKcolumn;//column for foreign key
127  mutable bool fIsNull;//for inserting NULL values
128  TString fTable;// name of parent table
129 
130  void init_type_map();
131  const char* _type();
132 
134  : fNameType(name, type), fConstraint(""), fIndex(idx), fData(name),
135  fBlob(nullptr), fBlobSize(0),
136  fPrimaryKey(false), fForeignKey(false),
137  fFKtable(""), fFKcolumn(""), fIsNull(false)
138  {
139  if (!inv_type_map.size()) init_type_map();
140  }
141  public:
142  virtual ~column()
143  {
145  if (fBlob) delete[](unsigned char*)fBlob;
146  }
147 
148  const char* name() const
149  {
150  return fNameType.first;
151  }
153  {
154  return fNameType.second;
155  }
156  const char* type_name() const
157  {
158  return const_cast<column*>(this)->_type();
159  }
160  const char* get_declaration() const;
161  const char* get_table() const
162  {
164  return fTable;
165  }
166  void set_table(const TString& name)
167  {
169  fTable = name;
170  }
171  int index() const
172  {
173  return fIndex;
174  }
175 
176  void print() const
177  {
178  std::cout << fIndex << "\t" << name() << "\t" << type_name() << "\n";
179  }
180  template<typename T>
181  void set_data(const T& x)
182  {
183  fData.Set(x);
184  fIsNull = false;
185  }
186  template <typename T>
187  const column& operator=(const T& x)
188  {
190  set_data(x);
191  return *this;
192  }
193 
194  void set_null()
195  {
196  fIsNull = true;
197  }
198  bool is_null() const
199  {
200  return fIsNull;
201  }
202 
203  template<typename T>
204  void set_binary_data(T& x)
205  {
206  fBlob = (void*)&x;
207  fBlobSize = sizeof(x);
208  fIsNull = false;
209  }
210  template<typename T>
211  void set_binary_data(T* x)
212  {
213  fBlob = (void*)x;
214  fBlobSize = sizeof(*x);
215  fIsNull = false;
216  }
217 
218  void set_data_in_statement(TSQLStatement*, int idx = -1) const;
219  void set_data_from_statement(TSQLStatement* s, int idx = -1) const;
220  void set_constraint(const TString& c)
221  {
230  fConstraint = c;
231  if (c == "PRIMARY KEY") fPrimaryKey = true;
232  }
233  void set_foreign_key(const TString& _table, const TString& _column);
234  void set_foreign_key(const table& _table, const column& _column);
235  bool primary_key() const
236  {
237  return fPrimaryKey;
238  }
239  bool foreign_key() const
240  {
241  return fForeignKey;
242  }
243  const KVNamedParameter& data() const
244  {
245  return fData;
246  }
247  template <typename T>
248  T get_data() const
249  {
250  return fData.Get<T>();
251  }
252  template<typename T>
253  T* binary_data() const
254  {
255  return static_cast<T*>(fBlob);
256  }
257 
258  ClassDef(column, 0) //Column in an SQLite database table
259  };
260  template<> void column::set_data(const KVNamedParameter&);
261 
262  class table {
263  TString fName;//name of table
265  mutable std::vector<KVSQLite::column> fColumns;//list of columns
266  mutable std::unordered_map<std::string, int> fColMap; //map name of column to index
267  static std::map<TString, KVSQLite::column_type::types> type_map;
268  bool fTemp;//temporary table?
269 
270  void init_type_map();
271 
272  public:
273  table(const TString& Name = "")
274  : fName(Name), fInsert(KVSQLite::insert_mode::DEFAULT), fColumns(), fColMap(), fTemp(false)
275  {
276  if (!type_map.size()) init_type_map();
277  }
278  table(const TString& Name, const std::vector<KVSQLite::column>& cols)
279  : fName(Name), fInsert(KVSQLite::insert_mode::DEFAULT), fColumns(cols), fColMap(), fTemp(false)
280  {
281  if (!type_map.size()) init_type_map();
282  for (auto& c : fColumns) c.set_table(Name);
283  }
284  virtual ~table() {}
285 
286  const char* name() const
287  {
288  return fName;
289  }
290  void set_name(const TString& name)
291  {
292  fName = name;
293  }
294 
295  void show_columns() const;
297  {
312  fInsert = i;
313  }
314  const char* get_insert_command() const;
315  void set_temporary(bool temp = true)
316  {
318  fTemp = temp;
319  }
320  bool is_temporary() const
321  {
322  return fTemp;
323  }
324 
327  {
329  return add_column(KVSQLite::column(fColumns.size(), name, type));
330  }
331  column& add_column(const TString& name, const TString& type);
332  const column& add_primary_key(const TString& name);
333  const column& add_foreign_key(const TString& other_table, const TString& other_column);
334  const column& add_foreign_key(const table& other_table, const column& other_column);
335  const KVSQLite::column& operator[](int i) const
336  {
337  return fColumns[i];
338  }
340  {
341  return fColumns[i];
342  }
344  {
345  return fColumns[i];
346  }
347  bool has_column(const TString& name) const
348  {
350  return fColMap.count(name.Data());
351  }
352 
354  {
355  return get_column(n);
356  }
357  const KVSQLite::column& operator[](const TString& n) const
358  {
359  return const_cast<table*>(this)->get_column(n);
360  }
362  {
363  if (!has_column(n)) {
364  std::cout << "Error in <KVSQLite::table::get_column(const TString&)> : "
365  << n << " is not a column of table " << name() << std::endl;
366  return fColumns[0];
367  }
368  return fColumns[fColMap[n.Data()]];
369  }
370 
371  void print() const
372  {
373  std::cout << name() << "\n";
374  for (std::vector<KVSQLite::column>::const_iterator it = fColumns.begin(); it != fColumns.end(); ++it) it->print();
375  }
376  int number_of_columns() const
377  {
378  return fColumns.size();
379  }
380  int check_columns(const KVNameValueList&);
381  void prepare_data(const KVNameValueList&, const KVNamedParameter* = nullptr);
382  void set_all_columns_null();
383 
384  TString get_column_names(const TString& exclude = "", const TString& delim = ",") const;
385 
386  ClassDef(table, 0) //Table in an SQLite database
387  };
388 
389  class database {
390  std::unique_ptr<TSQLiteServer> fDBserv; //connection to database
391  mutable std::unordered_map<std::string, KVSQLite::table> fTables; //map of tables in database
392  mutable std::unique_ptr<TSQLStatement> fSQLstmt; //used for bulk operations
393  mutable std::list<const column*> fSQLstmtCols; // columns used in SQL statement
394  mutable table* fBulkTable; //pointer to table currently used with fSQLstmt
395  mutable bool fInserting;
396  mutable bool fSelecting;
397  mutable bool fEmptyResultSet;
398  bool fIsValid;
400 
401  void PrintResults(TSQLResult* tabent, int column_width = 20) const;
402  std::unique_ptr<TSQLResult> SelectRowsFromTable(
403  const TString& table,
404  const TString& columns = "*",
405  const TString& condition = "") const;
406 
407  void read_table_infos();
408 
409  public:
410  database() : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false) {}
411  database(const TString& dbfile) : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false)
412  {
413  open(dbfile);
414  }
415  database(const database& db) : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false)
416  {
422  if (db.good()) open(db.fDBserv->GetDB());
423  }
425  {
426  if (&db != this && db.good()) open(db.fDBserv->GetDB());
427  return *this;
428  }
429  void show_tables() const;
431  {
432  return fTables.size();
433  }
434  virtual ~database()
435  {
436  close();
437  }
438  bool is_inserting() const
439  {
443  return fInserting;
444  }
445 
446  void open(const TString& dbfile);
447  void close()
448  {
449  fDBserv->Close();
450  fTables.clear();
451  }
452  bool good() const
453  {
454  return fIsValid;
455  }
456 
457  bool is_open() const
458  {
459  return fDBserv->IsConnected();
460  }
461 
462  void Dump() const;
463 
464  void add_table(const table&);
465  bool has_table(const TString& table)
466  {
468  return fTables.count(table.Data());
469  }
470 
472  {
473  return get_table(name);
474  }
475  const KVSQLite::table& operator[](const TString& name) const
476  {
477  return const_cast<database*>(this)->get_table(name);
478  }
480  {
481  if (!fTables.count(name.Data())) {
482  std::cout << "Error in <KVSQLite::database::get_table(const TString&)> : "
483  << name << " is not a table of database" << std::endl;
484  return fTables.begin()->second;
485  }
486  return fTables[name.Data()];
487  }
488 
489  bool prepare_data_insertion(const TString&);
490  void insert_data_row();
491  void end_data_insertion();
492 
493  bool select_data(const TString& tables, const TString& columns = "*", const TString& selection = "",
494  bool distinct = false, const TString& anything_else = "") const;
495  bool get_next_result() const;
497  const TString& selection = "", const TString& anything_else = "");
499  const TString& selection = "", const TString& anything_else = "");
500  KVNameValueList get_name_value_list(const TString& table, const TString& name_column, const TString& value_column,
501  const TString& selection = "", const TString& anything_else = "");
502  TGraph* create_graph(const TString& tablename, const TString& Xcolumn, const TString& Ycolumn, const TString& selection = "");
503 
504  void clear_table(const TString& name);
505 
506  int count(const TString& table, const TString& column = "*", const TString& selection = "", bool distinct = false) const;
507  bool update(const TString& table, const TString& columns, const TString& selection = "");
508  void delete_data(const TString& table, const TString& selection = "");
509 
510  column& add_column(const TString& table, const TString& name, const TString& type);
511  void add_missing_columns(const TString& table, const KVNameValueList& l);
512 
513  void copy_table_data(const TString& source, const TString& destination, const TString& columns = "*", const TString& selection = "");
514  void print_selection(const TString& table, const TString& columns, const TString& condition, int column_width = 20) const;
515  void print_selected_data(const TString& tables, const TString& columns = "*", const TString& selection = "", bool distinct = false, const TString& anything_else = "");
516 
517  ClassDef(database, 0) //Interface to ROOT SQLite database backend
518  };
519 }
520 
521 #endif
long Long_t
#define c(i)
#define ClassDef(name, id)
int type
Handles lists of named parameters with different types, a list of KVNamedParameter objects.
A generic named parameter storing values of different types.
void Set(const char *, const char *)
Strings used to represent a set of ranges of values.
Definition: KVNumberList.h:83
Long_t fBlobSize
binary data
Definition: SQLiteDB.h:122
void init_type_map()
Definition: SQLiteDB.cpp:932
bool primary_key() const
Definition: SQLiteDB.h:235
bool is_null() const
Definition: SQLiteDB.h:198
void set_null()
Definition: SQLiteDB.h:194
void set_binary_data(T &x)
Definition: SQLiteDB.h:204
void set_data_from_statement(TSQLStatement *s, int idx=-1) const
Definition: SQLiteDB.cpp:1010
void set_table(const TString &name)
Definition: SQLiteDB.h:166
const char * _type()
Definition: SQLiteDB.cpp:944
const char * type_name() const
Definition: SQLiteDB.h:156
std::pair< TString, KVSQLite_column_type > fNameType
Definition: SQLiteDB.h:116
const column & operator=(const T &x)
Definition: SQLiteDB.h:187
static std::map< KVSQLite::column_type::types, TString > inv_type_map
Definition: SQLiteDB.h:119
void set_data_in_statement(TSQLStatement *, int idx=-1) const
Definition: SQLiteDB.cpp:967
const char * name() const
Definition: SQLiteDB.h:148
void set_foreign_key(const TString &_table, const TString &_column)
Definition: SQLiteDB.cpp:1054
TString fFKcolumn
Definition: SQLiteDB.h:126
KVNamedParameter fData
Definition: SQLiteDB.h:120
const char * get_declaration() const
return declaration for column, including type & constraint
Definition: SQLiteDB.cpp:360
T get_data() const
Definition: SQLiteDB.h:248
KVSQLite_column_type type() const
Definition: SQLiteDB.h:152
int index() const
Definition: SQLiteDB.h:171
TString fFKtable
Definition: SQLiteDB.h:125
void set_binary_data(T *x)
Definition: SQLiteDB.h:211
T * binary_data() const
Definition: SQLiteDB.h:253
TString fConstraint
Definition: SQLiteDB.h:117
const KVNamedParameter & data() const
Definition: SQLiteDB.h:243
void set_data(const T &x)
Definition: SQLiteDB.h:181
bool foreign_key() const
Definition: SQLiteDB.h:239
TString fTable
Definition: SQLiteDB.h:128
void print() const
Definition: SQLiteDB.h:176
virtual ~column()
Definition: SQLiteDB.h:142
column(int idx, const TString &name, KVSQLite_column_type type)
Definition: SQLiteDB.h:133
void set_constraint(const TString &c)
Definition: SQLiteDB.h:220
const char * get_table() const
Definition: SQLiteDB.h:161
Interface to ROOT SQLite database backend ,.
Definition: SQLiteDB.h:389
int get_number_of_tables() const
Definition: SQLiteDB.h:430
void copy_table_data(const TString &source, const TString &destination, const TString &columns="*", const TString &selection="")
Definition: SQLiteDB.cpp:903
std::list< const column * > fSQLstmtCols
Definition: SQLiteDB.h:393
int count(const TString &table, const TString &column="*", const TString &selection="", bool distinct=false) const
Definition: SQLiteDB.cpp:739
database(const TString &dbfile)
Definition: SQLiteDB.h:411
TGraph * create_graph(const TString &tablename, const TString &Xcolumn, const TString &Ycolumn, const TString &selection="")
Definition: SQLiteDB.cpp:699
std::unique_ptr< TSQLResult > SelectRowsFromTable(const TString &table, const TString &columns="*", const TString &condition="") const
Definition: SQLiteDB.cpp:34
std::unique_ptr< TSQLStatement > fSQLstmt
Definition: SQLiteDB.h:392
bool update(const TString &table, const TString &columns, const TString &selection="")
Definition: SQLiteDB.cpp:776
void delete_data(const TString &table, const TString &selection="")
Definition: SQLiteDB.cpp:834
void show_tables() const
print list of tables
Definition: SQLiteDB.cpp:84
const KVSQLite::table & operator[](const TString &name) const
Definition: SQLiteDB.h:475
column & add_column(const TString &table, const TString &name, const TString &type)
Definition: SQLiteDB.cpp:855
void print_selection(const TString &table, const TString &columns, const TString &condition, int column_width=20) const
Print on stdout contents of database.
Definition: SQLiteDB.cpp:192
bool good() const
Definition: SQLiteDB.h:452
std::unique_ptr< TSQLiteServer > fDBserv
Definition: SQLiteDB.h:390
database & operator=(const database &db)
Definition: SQLiteDB.h:424
KVSQLite::table & operator[](const TString &name)
Definition: SQLiteDB.h:471
bool is_inserting() const
Definition: SQLiteDB.h:438
void insert_data_row()
Definition: SQLiteDB.cpp:393
void open(const TString &dbfile)
Definition: SQLiteDB.cpp:110
bool has_table(const TString &table)
Definition: SQLiteDB.h:465
void end_data_insertion()
Definition: SQLiteDB.cpp:433
bool select_data(const TString &tables, const TString &columns="*", const TString &selection="", bool distinct=false, const TString &anything_else="") const
Definition: SQLiteDB.cpp:493
KVNameValueList get_name_value_list(const TString &table, const TString &name_column, const TString &value_column, const TString &selection="", const TString &anything_else="")
Definition: SQLiteDB.cpp:675
virtual ~database()
Definition: SQLiteDB.h:434
bool get_next_result() const
Definition: SQLiteDB.cpp:595
TString get_string_list(const TString &table, const TString &column, const TString &selection="", const TString &anything_else="")
Definition: SQLiteDB.cpp:651
void Dump() const
Print on stdout contents of database.
Definition: SQLiteDB.cpp:171
std::unordered_map< std::string, KVSQLite::table > fTables
Definition: SQLiteDB.h:391
database(const database &db)
Definition: SQLiteDB.h:415
void add_table(const table &)
Definition: SQLiteDB.cpp:219
void PrintResults(TSQLResult *tabent, int column_width=20) const
Definition: SQLiteDB.cpp:143
KVSQLite::table & get_table(const TString &name)
Definition: SQLiteDB.h:479
bool prepare_data_insertion(const TString &)
Definition: SQLiteDB.cpp:269
TString fSelectedColumns
Definition: SQLiteDB.h:399
void clear_table(const TString &name)
Delete all data from table.
Definition: SQLiteDB.cpp:723
void print_selected_data(const TString &tables, const TString &columns="*", const TString &selection="", bool distinct=false, const TString &anything_else="")
Print out results of a call to select_data().
Definition: SQLiteDB.cpp:458
table * fBulkTable
Definition: SQLiteDB.h:394
void read_table_infos()
initialise map of database tables from existing database
Definition: SQLiteDB.cpp:52
bool is_open() const
Definition: SQLiteDB.h:457
void add_missing_columns(const TString &table, const KVNameValueList &l)
Definition: SQLiteDB.cpp:870
KVNumberList get_integer_list(const TString &table, const TString &column, const TString &selection="", const TString &anything_else="")
Definition: SQLiteDB.cpp:630
static std::map< TString, KVSQLite::column_type::types > type_map
Definition: SQLiteDB.h:267
const char * name() const
Definition: SQLiteDB.h:286
bool is_temporary() const
Definition: SQLiteDB.h:320
KVSQLite::column & operator[](int i)
Definition: SQLiteDB.h:339
void print() const
Definition: SQLiteDB.h:371
const column & add_primary_key(const TString &name)
Definition: SQLiteDB.cpp:1152
void set_all_columns_null()
set the value of all columns in the table to NULL
Definition: SQLiteDB.cpp:1266
std::vector< KVSQLite::column > fColumns
Definition: SQLiteDB.h:265
void set_temporary(bool temp=true)
Definition: SQLiteDB.h:315
void show_columns() const
print list of columns
Definition: SQLiteDB.cpp:1095
void init_type_map()
Definition: SQLiteDB.cpp:1082
table(const TString &Name, const std::vector< KVSQLite::column > &cols)
Definition: SQLiteDB.h:278
TString get_column_names(const TString &exclude="", const TString &delim=",") const
Definition: SQLiteDB.cpp:1282
table(const TString &Name="")
Definition: SQLiteDB.h:273
column & add_column(const KVSQLite::column &c)
Definition: SQLiteDB.cpp:1115
void set_name(const TString &name)
Definition: SQLiteDB.h:290
TString fName
Definition: SQLiteDB.h:263
KVSQLite::column & get_column(const TString &n)
Definition: SQLiteDB.h:361
KVSQLite::column & operator[](const TString &n)
Definition: SQLiteDB.h:353
virtual ~table()
Definition: SQLiteDB.h:284
int check_columns(const KVNameValueList &)
Definition: SQLiteDB.cpp:1221
void set_insert_mode(KVSQLite_insert_mode i)
Definition: SQLiteDB.h:296
int number_of_columns() const
Definition: SQLiteDB.h:376
const KVSQLite::column & operator[](const TString &n) const
Definition: SQLiteDB.h:357
bool has_column(const TString &name) const
Definition: SQLiteDB.h:347
const column & add_foreign_key(const TString &other_table, const TString &other_column)
Definition: SQLiteDB.cpp:1177
KVSQLite::column & get_column(int i)
Definition: SQLiteDB.h:343
column & add_column(const TString &name, KVSQLite_column_type type)
Definition: SQLiteDB.h:326
void prepare_data(const KVNameValueList &, const KVNamedParameter *=nullptr)
Definition: SQLiteDB.cpp:1244
const char * get_insert_command() const
Definition: SQLiteDB.cpp:336
KVSQLite_insert_mode fInsert
Definition: SQLiteDB.h:264
const KVSQLite::column & operator[](int i) const
Definition: SQLiteDB.h:335
std::unordered_map< std::string, int > fColMap
Definition: SQLiteDB.h:266
const char * Data() const
RooCmdArg Name(const char *name)
Double_t x[n]
const Int_t n
KVSQLite::column_type::types KVSQLite_column_type
Definition: SQLiteDB.h:108
KVSQLite::insert_mode::types KVSQLite_insert_mode
Definition: SQLiteDB.h:107
const long double s
Definition: KVUnits.h:94
double T(double x)