KaliVeda
Toolkit for HIC analysis
KVSQLiteServer.cxx
1 // @(#)root/sqlite:$Id$
2 // Author: o.freyermuth <o.f@cern.ch>, 01/06/2013
3 
4 /*************************************************************************
5  * Copyright (C) 1995-2013, Rene Brun and Fons Rademakers. *
6  * All rights reserved. *
7  * *
8  * For the licensing terms see $ROOTSYS/LICENSE. *
9  * For the list of contributors see $ROOTSYS/README/CREDITS. *
10  *************************************************************************/
11 
12 #include "KVSQLiteServer.h"
13 #include "KVSQLiteResult.h"
14 #include "KVSQLiteStatement.h"
15 #include "TSQLColumnInfo.h"
16 #include "TList.h"
17 #include "TSQLTableInfo.h"
18 #include "TSQLRow.h"
19 
20 #include <sqlite3.h>
21 
22 #include <thread>
23 #include <chrono>
24 using namespace std::chrono_literals;
25 
26 ClassImp(KVSQLiteServer);
27 
36 
37 
39 
40 KVSQLiteServer::KVSQLiteServer(const char* db, const char* /*uid*/, const char* /*pw*/)
41 {
42  fSQLite = nullptr;
43  fSrvInfo = "SQLite ";
44  fSrvInfo += sqlite3_libversion();
45 
46  if (strncmp(db, "sqlite://", 9)) {
47  TString givenProtocol(db, 9); // this TString-constructor allocs len+1 and does \0 termination already.
48  Error("KVSQLiteServer", "protocol in db argument should be sqlite it is %s",
49  givenProtocol.Data());
50  MakeZombie();
51  return;
52  }
53 
54  const char* dbase = db + 9;
55 
56 #ifndef SQLITE_OPEN_URI
57 #define SQLITE_OPEN_URI 0x00000000
58 #endif
59 #if SQLITE_VERSION_NUMBER >= 3005000
60  Int_t error = sqlite3_open_v2(dbase, &fSQLite, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);
61 #else
62  Int_t error = sqlite3_open(dbase, &fSQLite);
63 #endif
64 
65  if (error == 0) {
66  // Set members of the abstract interface
67  fType = "SQLite";
68  fHost = "";
69  fDB = dbase;
70  // fPort != -1 means we are 'connected'
71  fPort = 0;
72  }
73  else {
74  Error("KVSQLiteServer", "opening of %s failed with error: %d %s", dbase, sqlite3_errcode(fSQLite), sqlite3_errmsg(fSQLite));
75  sqlite3_close(fSQLite);
76  MakeZombie();
77  }
78 
79 }
80 
81 
84 
85 
87 
88 KVSQLiteServer::~KVSQLiteServer()
89 {
90  if (IsConnected()) {
91  sqlite3_close(fSQLite);
92  }
93 }
94 
95 
98 
99 
101 
102 void KVSQLiteServer::Close(Option_t*)
103 {
104  if (!fSQLite) {
105  return;
106  }
107 
108  if (IsConnected()) {
109  sqlite3_close(fSQLite);
110  // Mark as disconnected:
111  fPort = -1;
112  fSQLite = nullptr;
113  }
114 }
115 
116 
120 
121 
123 
124 Bool_t KVSQLiteServer::StartTransaction()
125 {
126  return Exec("BEGIN TRANSACTION");
127 }
128 
129 
130 #if ROOT_VERSION_CODE >= ROOT_VERSION(6,26,0)
133 
134 
136 
137 Bool_t KVSQLiteServer::HasTransactionInFlight()
138 {
139  if (!fSQLite)
140  return kFALSE;
141 
142  return sqlite3_get_autocommit(fSQLite) == 0;
143 }
144 
145 #endif
146 
150 
151 
153 
154 Bool_t KVSQLiteServer::Commit()
155 {
156  return Exec("COMMIT TRANSACTION");
157 }
158 
159 
163 
164 
166 
167 Bool_t KVSQLiteServer::Rollback()
168 {
169  return Exec("ROLLBACK TRANSACTION");
170 }
171 
172 
177 
178 
180 
181 TSQLResult* KVSQLiteServer::Query(const char* sql)
182 {
183  if (!IsConnected()) {
184  Error("Query", "not connected");
185  return nullptr;
186  }
187 
188  sqlite3_stmt* preparedStmt = nullptr;
189  const char* tail = nullptr;
190  ClearError();
191  // -1 as we read until we encounter a \0.
192 #if SQLITE_VERSION_NUMBER >= 3005000
193  int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
194 #else
195  int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
196 #endif
197  if (retVal != SQLITE_OK) {
198  SetError(retVal,sqlite3_errmsg(fSQLite),"Query");
199  Info("Query","Query was:%s",sql);
200  return nullptr;
201  }
202  if (tail && tail[0] != '\0')
203  Warning("Query", "Don't use multiple queries, '%s' query was ignored", tail);
204 
205  return new KVSQLiteResult(preparedStmt);
206 }
207 
208 
212 
213 
215 
216 Bool_t KVSQLiteServer::Exec(const char* sql)
217 {
218  if (!IsConnected()) {
219  Error("Exec", "not connected");
220  return kFALSE;
221  }
222 
223  ClearError();
224  int ret = sqlite3_exec(fSQLite, sql, NULL, NULL, NULL);
225  if (ret != SQLITE_OK) {
226  SetError(ret,sqlite3_errmsg(fSQLite),"Exec");
227  return kFALSE;
228  }
229  return kTRUE;
230 }
231 
232 
233 
237 
238 
240 
241 Int_t KVSQLiteServer::SelectDataBase(const char* /*dbname*/)
242 {
243  Error("SelectDataBase", "SelectDataBase command makes no sense for SQLite!");
244  return -1;
245 }
246 
247 
251 
252 
254 
255 TSQLResult* KVSQLiteServer::GetDataBases(const char* /*wild*/)
256 {
257  Error("GetDataBases", "GetDataBases command makes no sense for SQLite!");
258  return nullptr;
259 }
260 
261 
267 
268 
270 
271 TSQLResult* KVSQLiteServer::GetTables(const char* /*dbname*/, const char* wild)
272 {
273  if (!IsConnected()) {
274  Error("GetTables", "not connected");
275  return nullptr;
276  }
277 
278  TString sql = "SELECT name FROM sqlite_master where type='table'";
279  if (wild)
280  sql += Form(" AND name LIKE '%s'", wild);
281 
282  return Query(sql);
283 }
284 
285 
286 
288 
289 TList* KVSQLiteServer::GetTablesList(const char* wild)
290 {
291  auto l = TSQLServer::GetTablesList(wild);
292  if(!l)
293  {
294  // if database is locked, try again until it works
295  if(IsLocked())
296  {
297  int i=2;
298  do
299  {
300  Info("GetTablesList","Database is locked. Try again in 50ms...");
301  std::this_thread::sleep_for(50ms);
302  Info("GetTablesList", "Try number %d...",i);
304  }
305  while(!l && IsLocked() && (i<6));
306  if(l && !IsLocked())
307  Info("GetTablesList","...database access successful!");
308  }
309  }
310  return l;
311 }
312 
313 
322 
323 
325 
326 TSQLResult* KVSQLiteServer::GetColumns(const char* /*dbname*/, const char* table,
327  const char* wild)
328 {
329  if (!IsConnected()) {
330  Error("GetColumns", "not connected");
331  return nullptr;
332  }
333 
334  if (wild) {
335  Error("GetColumns", "Not implementable for SQLite as a query with wildcard, use GetFieldNames() after SELECT instead!");
336  return nullptr;
337  }
338  else {
339  TString sql = Form("PRAGMA table_info('%s')", table);
340  return Query(sql);
341  }
342 }
343 
344 
348 
349 
351 
352 TSQLTableInfo* KVSQLiteServer::GetTableInfo(const char* tablename)
353 {
354  if (!IsConnected()) {
355  Error("GetTableInfo", "not connected");
356  return nullptr;
357  }
358 
359  if (!tablename || (*tablename == 0)) return nullptr;
360 
361  TSQLResult* columnRes = GetColumns("", tablename);
362 
363  if (columnRes == nullptr) {
364  Error("GetTableInfo", "could not query columns");
365  return nullptr;
366  }
367 
368  TList* lst = nullptr;
369 
370  TSQLRow* columnRow;
371 
372  while ((columnRow = columnRes->Next()) != nullptr) {
373  if (!lst) {
374  lst = new TList();
375  }
376 
377  // Field 3 is 'notnull', i.e. if it is 0, column is nullable
378  Bool_t isNullable = (strcmp(columnRow->GetField(3), "0") == 0);
379 
380  lst->Add(new TSQLColumnInfo(columnRow->GetField(1), // column name
381  columnRow->GetField(2), // column type name
382  isNullable, // isNullable defined above
383  -1, // SQLite is totally free about types
384  -1, // SQLite imposes no declarable size-limits
385  -1, // Field length only available querying the field
386  -1, // no data scale in SQLite
387  -1)); // SQLite does not enforce any sign(s)
388  delete columnRow;
389  }
390  delete columnRes;
391 
392  // lst == NULL is ok as TSQLTableInfo accepts and handles this
393  TSQLTableInfo* info = new TSQLTableInfo(tablename,
394  lst);
395 
396  return info;
397 }
398 
399 
403 
404 
406 
407 Int_t KVSQLiteServer::CreateDataBase(const char* /*dbname*/)
408 {
409  Error("CreateDataBase", "CreateDataBase command makes no sense for SQLite!");
410  return -1;
411 }
412 
413 
417 
418 
420 
421 Int_t KVSQLiteServer::DropDataBase(const char* /*dbname*/)
422 {
423  Error("DropDataBase", "DropDataBase command makes no sense for SQLite!");
424  return -1;
425 }
426 
427 
431 
432 
434 
435 Int_t KVSQLiteServer::Reload()
436 {
437  if (!IsConnected()) {
438  Error("Reload", "not connected");
439  return -1;
440  }
441 
442  Error("Reload", "not implemented");
443  return 0;
444 }
445 
446 
450 
451 
453 
454 Int_t KVSQLiteServer::Shutdown()
455 {
456  if (!IsConnected()) {
457  Error("Shutdown", "not connected");
458  return -1;
459  }
460 
461  Error("Shutdown", "not implemented");
462  return -1;
463 }
464 
465 
471 
472 
474 
475 Bool_t KVSQLiteServer::HasStatement() const
476 {
477  return kTRUE;
478 }
479 
480 
483 
484 
486 
487 TSQLStatement* KVSQLiteServer::Statement(const char* sql, Int_t)
488 {
489  if (!sql || !*sql) {
490  SetError(-1, "no query string specified", "Statement");
491  return nullptr;
492  }
493 
494  if (!IsConnected()) {
495  Error("Statement", "not connected");
496  return nullptr;
497  }
498 
499  sqlite3_stmt* preparedStmt = nullptr;
500  const char* tail = nullptr;
501 
502  ClearError();
503 
504  // -1 as we read until we encounter a \0.
505 #if SQLITE_VERSION_NUMBER >= 3005000
506  int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
507 #else
508  int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
509 #endif
510  if (retVal != SQLITE_OK) {
511  SetError(retVal,sqlite3_errmsg(fSQLite),"Statement");
512  return nullptr;
513  }
514  if (tail && tail[0] != '\0')
515  Warning("Statement", "Don't use multiple statements, '%s' statement was ignored", tail);
516 
517  SQLite3_Stmt_t* stmt = new SQLite3_Stmt_t;
518  stmt->fConn = fSQLite;
519  stmt->fRes = preparedStmt;
520 
521  return new KVSQLiteStatement(stmt);
522 }
523 
524 
527 
528 
530 
531 const char* KVSQLiteServer::ServerInfo()
532 {
533  if (!IsConnected()) {
534  Error("ServerInfo", "not connected");
535  return nullptr;
536  }
537 
538  return fSrvInfo.Data();
539 }
540 
541 
542 
544 
545 bool KVSQLiteServer::IsLocked() const
546 {
547  return GetErrorCode()==SQLITE_BUSY;
548 }
549 
550 
551 
553 
554 bool KVSQLiteServer::IsOK() const
555 {
556  return GetErrorCode()==SQLITE_OK;
557 }
558 
559 
int Int_t
bool Bool_t
constexpr Bool_t kFALSE
constexpr Bool_t kTRUE
const char Option_t
char * Form(const char *fmt,...)
void Add(TObject *obj) override
virtual TSQLRow * Next()=0
virtual const char * GetField(Int_t field)=0
virtual TList * GetTablesList(const char *wild=nullptr)
CPYCPPYY_EXTERN bool Exec(const std::string &cmd)
void Error(const char *location, const char *fmt,...)
void Info(const char *location, const char *fmt,...)
void Warning(const char *location, const char *fmt,...)
sqlite3_stmt * fRes
sqlite3 * fConn
TLine l
ClassImp(TPyArg)