gambit is hosted by Hepforge, IPPP Durham
GAMBIT  v1.5.0-2191-ga4742ac
a Global And Modular Bsm Inference Tool
sqlitebase.cpp
Go to the documentation of this file.
1 // GAMBIT: Global and Modular BSM Inference Tool
2 // *********************************************
16 
17 #include <iostream>
18 #include <sstream>
19 #include <chrono>
20 #include <thread>
21 // SQLite3 C interface
22 #include <sqlite3.h>
23 
25 
26 // Activate extra debugging output on error
27 #define SQL_DEBUG
28 
29 namespace Gambit
30 {
31  namespace Printers
32  {
33  /* Callback function for retrieving the column names and types of a table
34  * Called for each row of the results table
35  *
36  * Arguments:
37  *
38  * list - Pointer to a map from column names to types
39  * count - The number of columns in the result set
40  * data - The row's data
41  * columns - The column names
42  */
43  int col_name_callback(void* colmap_in, int /*count*/, char** data, char** /* columns */)
44  {
45  typedef std::map<std::string, std::string, Utils::ci_less> mymaptype;
46  mymaptype *colmap = static_cast<mymaptype*>(colmap_in);
47 
48  // We know that the column name is the second column of the results set, and the
49  // type is the third column
50  std::string column_name(data[1]);
51  std::string column_type(data[2]);
52 
53  //std::cout<<"Reading existing columns: name: "<<column_name<<", type: "<<column_type<<std::endl;
54 
55  // Add to map
56  (*colmap)[column_name] = column_type;
57 
58  return 0;
59  }
60 
61  // Matching of SQL types to C++ types
62  template<> std::string cpp2sql<long long int>(){return "INTEGER";}
63  template<> std::string cpp2sql<double>() {return "REAL";}
64  template<> std::string cpp2sql<std::string>() {return "TEXT";}
65 
66  // Matching of SQLite "type codes" to SQL types
67  // Names should match results of cpp2sql above
68  // Codes should match https://www.sqlite.org/c3ref/c_blob.html
69  std::map<unsigned int,std::string> define_typecodes()
70  {
71  std::map<unsigned int,std::string> out;
72  out[1] = "INTEGER";
73  out[2] = "FLOAT";
74  out[3] = "TEXT";
75  out[4] = "BLOB";
76  out[5] = "NULL";
77  return out;
78  }
79  const std::map<unsigned int,std::string> typecode2sql(define_typecodes());
80 
81  // Map from all SQLite "suggestion" types into the five "base" SQLite types
82  // Used for type comparisons.
83  std::map<std::string,std::string,Utils::ci_less> fill_SQLtype_to_basic()
84  {
85  std::map<std::string,std::string,Utils::ci_less> out;
86 
87  out["INT"] = "INTEGER";
88  out["INTEGER"] = "INTEGER";
89  out["TINYINT"] = "INTEGER";
90  out["SMALLINT"] = "INTEGER";
91  out["MEDIUMINT"] = "INTEGER";
92  out["BIGINT"] = "INTEGER";
93  out["UNSIGNED BIG INT"] = "INTEGER";
94  out["INT2"] = "INTEGER";
95  out["INT8"] = "INTEGER";
96 
97  out["CHARACTER(20)"] = "TEXT";
98  out["VARCHAR(255)"] = "TEXT";
99  out["VARYING CHARACTER(255)"] = "TEXT";
100  out["NCHAR(55)"] = "TEXT";
101  out["NATIVE CHARACTER(70)"] = "TEXT";
102  out["NVARCHAR(100)"] = "TEXT";
103  out["CLOB"] = "TEXT";
104  out["TEXT"] = "TEXT";
105 
106  out["BLOB"] = "NONE";
107  out["NONE"] = "NONE";
108 
109  out["DOUBLE"] = "REAL";
110  out["DOUBLE PRECISION"] = "REAL";
111  out["FLOAT"] = "REAL";
112  out["REAL"] = "REAL";
113 
114  out["DECIMAL(10,5)"] = "NUMERIC";
115  out["BOOLEAN"] = "NUMERIC";
116  out["DATE"] = "NUMERIC";
117  out["DATETIME"] = "NUMERIC";
118  out["NUMERIC"] = "NUMERIC";
119 
120  return out;
121  }
122  const std::map<std::string,std::string,Utils::ci_less> SQLtype_to_basic(fill_SQLtype_to_basic());
123 
124  // Compare SQLite data types to see if they are equivalent to the same basic 'affinity' for a column
125  bool SQLite_equaltypes(const std::string& type1, const std::string& type2)
126  {
127  // There are five "basic" types in SQLite, but many
128  // "aliases" for them. We will need to map each input string
129  // to its "basic" type, and then check if those are the same
130  // for each input type.
131  auto it1 = SQLtype_to_basic.find(type1);
132  auto it2 = SQLtype_to_basic.find(type2);
133  if(it1==SQLtype_to_basic.end())
134  {
135  std::stringstream err;
136  err<<"Could not determine a basic SQLite 'affinity' type for data type named '"<<type1<<"' (first argument to this type checking dunction)";
137  printer_error().raise(LOCAL_INFO,err.str());
138  }
139  if(it2==SQLtype_to_basic.end())
140  {
141  std::stringstream err;
142  err<<"Could not determine a basic SQLite 'affinity' type for data type named '"<<type2<<"' (second argument to this type checking dunction)";
143  printer_error().raise(LOCAL_INFO,err.str());
144  }
145  return (it1->second) == (it2->second);
146  }
147 
148  // SQLite base class for both reader and writer (Constructor)
150  : database_file("uninitialised")
151  , table_name("uninitialised")
152  , db(NULL)
153  , db_is_open(false)
154  , table_exists(false)
155  {}
156 
157  // Destructor
159  {
160  close_db();
161  }
162 
163  // Open database and 'attach' it to this object
164  void SQLiteBase::open_db(const std::string& path, char access)
165  {
166  // Check if we already have an open database
167  if(db!=NULL)
168  {
169  std::stringstream err;
170  err << "Refused to open database file '"<<path<<"'; a database file pointer has already been attached to the SQLite printer!";
171  printer_error().raise(LOCAL_INFO,err.str());
172  }
173 
174  if(db_is_open)
175  {
176  std::stringstream err;
177  err << "Refused to open database file '"<<path<<"'; a database file is already flagged by the SQLite printer as open!";
178  printer_error().raise(LOCAL_INFO,err.str());
179  }
180 
181  int sql_access;
182  switch(access) {
183  case 'r' :
184  sql_access = SQLITE_OPEN_READONLY;
185  break;
186  case 'w':
187  sql_access = SQLITE_OPEN_READWRITE;
188  break;
189  case '+':
190  sql_access = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
191  break;
192  }
193 
194  int rc; // return code
195  //rc = sqlite3_open(path.c_str(), &db);
196  rc = sqlite3_open_v2(path.c_str(), &db, sql_access, NULL);
197 
198  if( rc )
199  {
200  std::stringstream err;
201  err << "Failed to open database file '"<<path<<"' (are you sure it exists?). SQLite error was: " << sqlite3_errmsg(db);
202  printer_error().raise(LOCAL_INFO, err.str());
203  }
204  else
205  {
206  db_is_open = true;
207  database_file = path;
208  }
209  }
210 
212  {
213  sqlite3_close(get_db()); // Check error code?
214  db_is_open = false;
215  }
216 
218  {
219  if(db==NULL)
220  {
221  std::stringstream err;
222  err << "Attempted to access SQLite database pointer, but it is NULL! This means that some SQLitePrinter/Reader routine called 'get_db()' before the database was opened (or after it was closed)! This is a bug, please report it.";
223  printer_error().raise(LOCAL_INFO,err.str());
224  }
225  return db;
226  }
227 
228  // Make sure the outbase database is open and the results table exists
229  // Throws an error if it isn't
231  {
232  if(db==NULL || !db_is_open || !table_exists)
233  {
234  std::stringstream err;
235  // Something was not ready, check further and throw an error
236  if(db==NULL)
237  {
238  err << "Output readiness check failed! Database pointer was NULL!";
239  printer_error().raise(LOCAL_INFO,err.str());
240  }
241 
242  if(!db_is_open)
243  {
244  err << "Output readiness check failed! Database is not flagged as open!";
245  printer_error().raise(LOCAL_INFO,err.str());
246  }
247 
248  if(!table_exists)
249  {
250  err << "Output readiness check failed! Results table is not flagged as existing!";
251  printer_error().raise(LOCAL_INFO,err.str());
252  }
253  }
254  // Else we are good to go!
255  }
256 
257  // Function to repeatedly attempt an SQLite statement if the database is locked/busy
258  int SQLiteBase::submit_sql(const std::string& local_info, const std::string& sqlstr, bool allow_fail, sql_callback_fptr callback, void* data, char **zErrMsg_in)
259  {
260  int rc;
261  char *zErrMsg;
262  char **zErrMsg_ptr;
263  if(zErrMsg_in==NULL)
264  {
265  zErrMsg_ptr = &zErrMsg;
266  }
267  else
268  {
269  zErrMsg_ptr = zErrMsg_in;
270  }
271 
272  do
273  {
274  rc = sqlite3_exec(get_db(), sqlstr.c_str(), callback, data, zErrMsg_ptr);
275  if(rc==SQLITE_BUSY)
276  {
277  // Wait at least a short time to avoid slamming the filesystem too much
278  std::chrono::milliseconds timespan(10);
279  std::this_thread::sleep_for(timespan);
280  }
281  }
282  while (rc == SQLITE_BUSY);
283 
284  // if allow_fail is true then we don't catch this error, we allow the caller of this function to hander it.
285  if( (rc != SQLITE_OK) and not allow_fail ){
286  std::stringstream err;
287  err << "SQL error: " << *zErrMsg_ptr << std::endl;
288 #ifdef SQL_DEBUG
289  err << "The attempted SQL statement was:"<<std::endl;
290  err << sqlstr << std::endl;;
291 #endif
292  sqlite3_free(*zErrMsg_ptr);
293  printer_error().raise(local_info,err.str());
294  }
295  return rc;
296  }
297 
298  // Get names and types for all columns in the target table
299  // (Output is a map from names to types)
300  std::map<std::string, std::string, Utils::ci_less> SQLiteBase::get_column_info()
301  {
302  std::stringstream sql;
303  sql<<"PRAGMA table_info("<<get_table_name()<<");";
304 
305  /* Execute SQL statement */
306  int rc;
307  char *zErrMsg = 0;
308  std::map<std::string, std::string, Utils::ci_less> colnames; // Will be passed to and filled by the callback function
309  rc = submit_sql(LOCAL_INFO, sql.str(), true, &col_name_callback, &colnames, &zErrMsg);
310 
311  if( rc != SQLITE_OK ){
312  std::stringstream err;
313  err << "Failed to retrieve information about SQL column names in target table!"<<std::endl;
314  err << " The attempted SQL statement was:"<<std::endl;
315  err << sql.str() << std::endl;
316  sqlite3_free(zErrMsg);
317  printer_error().raise(LOCAL_INFO,err.str());
318  }
319  return colnames;
320  }
321 
323  std::string SQLiteBase::get_table_name() {return table_name;}
324  void SQLiteBase::set_table_name(const std::string& t) {table_name=t;}
325 
326  // For debugging: dump a row of a results table to std::cout
327  void SQLiteBase::cout_row(sqlite3_stmt* tmp_stmt)
328  {
329  int ncols = sqlite3_data_count(tmp_stmt);
330  for(int i=0; i<ncols; i++)
331  {
332  std::cout<<" "<<sqlite3_column_text(tmp_stmt, i)<<",";
333  }
334  }
335 
336  // Check that the required table exists
337  // Sets 'table_exists' to true if successful, otherwise throws error
339  {
340  std::stringstream sql;
341  sql<<"SELECT name FROM sqlite_master WHERE type='table' AND name='"<<get_table_name()<<"';";
342  std::size_t count(0);
343 
344  /* Execute SQL statement and iterate through results*/
345  sqlite3_stmt *temp_stmt;
346  int rc = sqlite3_prepare_v2(get_db(), sql.str().c_str(), -1, &temp_stmt, NULL);
347  if (rc != SQLITE_OK) {
348  std::stringstream err;
349  err<<"Encountered SQLite error while preparing statement to check if table '"<<get_table_name()<<"' exists in file '"<<get_database_file()<<"': "<<sqlite3_errmsg(get_db());
350  printer_error().raise(LOCAL_INFO, err.str());
351  }
352  while ((rc = sqlite3_step(temp_stmt)) == SQLITE_ROW) {
353  count+=1; // Will be one row for each table with a name matching 'get_table_name()'. Should be 1 or 0.
354  }
355  if (rc != SQLITE_DONE) {
356  std::stringstream err;
357  err<<"Encountered SQLite error while checking if table '"<<get_table_name()<<"' exists in file '"<<get_database_file()<<": "<<sqlite3_errmsg(get_db());
358  printer_error().raise(LOCAL_INFO, err.str());
359  }
360  sqlite3_finalize(temp_stmt);
361 
362  if(count==0)
363  {
364  std::stringstream err;
365  err<<"Requested input table '"<<get_table_name()<<"' could not be found in file '"<<get_database_file()<<"! Please check that the requested table name is correct!";
366  printer_error().raise(LOCAL_INFO, err.str());
367  }
368  else if(count>1)
369  {
370  std::stringstream err;
371  err<<"Weird error encountered while checking that input table '"<<get_table_name()<<"' exists in file '"<<get_database_file()<<". We apparently found "<<count<<" tables with this name! This doesn't make sense, so there is probably a bug in the SQLiteBase class, please report it.";
372  printer_error().raise(LOCAL_INFO, err.str());
373  }
374  // Else the table exists, no problem.
375  set_table_exists();
376  }
377 
378  // Sets the 'table_exists' flag to true
380 
381  }
382 }
383 
void set_table_name(const std::string &table_name)
Definition: sqlitebase.cpp:324
greatScanData data
Definition: great.cpp:38
int submit_sql(const std::string &local_info, const std::string &sqlstr, bool allow_fail=false, sql_callback_fptr callback=NULL, void *data=NULL, char **zErrMsg=NULL)
Definition: sqlitebase.cpp:258
bool SQLite_equaltypes(const std::string &type1, const std::string &type2)
Definition: sqlitebase.cpp:125
EXPORT_SYMBOLS error & printer_error()
Printer errors.
std::string cpp2sql< double >()
Definition: sqlitebase.cpp:63
std::string cpp2sql< long long int >()
Definition: sqlitebase.cpp:62
int sql_callback_fptr(void *, int, char **, char **)
Definition: sqlitebase.hpp:45
SQLite printer/reader base class declaration.
std::map< std::string, std::string, Utils::ci_less > fill_SQLtype_to_basic()
Definition: sqlitebase.cpp:83
#define LOCAL_INFO
Definition: local_info.hpp:34
std::map< unsigned int, std::string > define_typecodes()
Definition: sqlitebase.cpp:69
int col_name_callback(void *colmap_in, int, char **data, char **)
Definition: sqlitebase.cpp:43
const std::map< std::string, std::string, Utils::ci_less > SQLtype_to_basic
std::map< std::string, std::string, Utils::ci_less > get_column_info()
Definition: sqlitebase.cpp:300
void open_db(const std::string &, char access='r')
Definition: sqlitebase.cpp:164
void cout_row(sqlite3_stmt *tmp_stmt)
Definition: sqlitebase.cpp:327
TODO: see if we can use this one:
Definition: Analysis.hpp:33
const std::map< unsigned int, std::string > typecode2sql