KVSQLiteServer.cxx

// @(#)root/sqlite:$Id$
// Author: o.freyermuth <o.f@cern.ch>, 01/06/2013
 
/*************************************************************************
 * Copyright (C) 1995-2013, Rene Brun and Fons Rademakers.               *
 * All rights reserved.                                                  *
 *                                                                       *
 * For the licensing terms see $ROOTSYS/LICENSE.                         *
 * For the list of contributors see $ROOTSYS/README/CREDITS.             *
 *************************************************************************/
 
#include "KVSQLiteServer.h"
#include "KVSQLiteResult.h"
#include "KVSQLiteStatement.h"
#include "TSQLColumnInfo.h"
#include "TList.h"
#include "TSQLTableInfo.h"
#include "TSQLRow.h"
 
#include <sqlite3.h>
 
#include <thread>
#include <chrono>
using namespace std::chrono_literals;
 
ClassImp(KVSQLiteServer);
 
 
 
 
KVSQLiteServer::KVSQLiteServer(const char* db, const char* /*uid*/, const char* /*pw*/)
{
   fSQLite = nullptr;
   fSrvInfo = "SQLite ";
   fSrvInfo += sqlite3_libversion();
 
   if (strncmp(db, "sqlite://", 9)) {
      TString givenProtocol(db, 9); // this TString-constructor allocs len+1 and does \0 termination already.
      Error("KVSQLiteServer", "protocol in db argument should be sqlite it is %s",
            givenProtocol.Data());
      MakeZombie();
      return;
   }
 
   const char* dbase = db + 9;
 
#ifndef SQLITE_OPEN_URI
#define SQLITE_OPEN_URI 0x00000000
#endif
#if SQLITE_VERSION_NUMBER >= 3005000
   Int_t error = sqlite3_open_v2(dbase, &fSQLite, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);
#else
   Int_t error = sqlite3_open(dbase, &fSQLite);
#endif
 
   if (error == 0) {
      // Set members of the abstract interface
      fType = "SQLite";
      fHost = "";
      fDB = dbase;
      // fPort != -1 means we are 'connected'
      fPort = 0;
   }
   else {
      Error("KVSQLiteServer", "opening of %s failed with error: %d %s", dbase, sqlite3_errcode(fSQLite), sqlite3_errmsg(fSQLite));
      sqlite3_close(fSQLite);
      MakeZombie();
   }
 
}
 
 
 
 
 
KVSQLiteServer::~KVSQLiteServer()
{
   if (IsConnected()) {
      sqlite3_close(fSQLite);
   }
}
 
 
 
 
 
void KVSQLiteServer::Close(Option_t*)
{
   if (!fSQLite) {
      return;
   }
 
   if (IsConnected()) {
      sqlite3_close(fSQLite);
      // Mark as disconnected:
      fPort = -1;
      fSQLite = nullptr;
   }
}
 
 
 
 
 
Bool_t KVSQLiteServer::StartTransaction()
{
   return Exec("BEGIN TRANSACTION");
}
 
 
#if ROOT_VERSION_CODE >= ROOT_VERSION(6,26,0)
 
 
 
Bool_t  KVSQLiteServer::HasTransactionInFlight()
{
   if (!fSQLite)
      return kFALSE;
 
   return sqlite3_get_autocommit(fSQLite) == 0;
}
 
#endif
 
 
 
 
Bool_t KVSQLiteServer::Commit()
{
   return Exec("COMMIT TRANSACTION");
}
 
 
 
 
 
Bool_t KVSQLiteServer::Rollback()
{
   return Exec("ROLLBACK TRANSACTION");
}
 
 
 
 
 
TSQLResult* KVSQLiteServer::Query(const char* sql)
{
   if (!IsConnected()) {
      Error("Query", "not connected");
      return nullptr;
   }
 
   sqlite3_stmt* preparedStmt = nullptr;
   const char* tail = nullptr;
   ClearError();
   // -1 as we read until we encounter a \0.
#if SQLITE_VERSION_NUMBER >= 3005000
   int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
#else
   int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
#endif
   if (retVal != SQLITE_OK) {
      SetError(retVal,sqlite3_errmsg(fSQLite),"Query");
      Info("Query","Query was:%s",sql);
      return nullptr;
   }
   if (tail && tail[0] != '\0')
      Warning("Query", "Don't use multiple queries, '%s' query was ignored", tail);
 
   return new KVSQLiteResult(preparedStmt);
}
 
 
 
 
 
Bool_t KVSQLiteServer::Exec(const char* sql)
{
   if (!IsConnected()) {
      Error("Exec", "not connected");
      return kFALSE;
   }
 
   ClearError();
   int ret = sqlite3_exec(fSQLite, sql, NULL, NULL, NULL);
   if (ret != SQLITE_OK) {
      SetError(ret,sqlite3_errmsg(fSQLite),"Exec");
      return kFALSE;
   }
   return kTRUE;
}
 
 
 
 
 
 
Int_t KVSQLiteServer::SelectDataBase(const char* /*dbname*/)
{
   Error("SelectDataBase", "SelectDataBase command makes no sense for SQLite!");
   return -1;
}
 
 
 
 
 
TSQLResult* KVSQLiteServer::GetDataBases(const char* /*wild*/)
{
   Error("GetDataBases", "GetDataBases command makes no sense for SQLite!");
   return nullptr;
}
 
 
 
 
 
TSQLResult* KVSQLiteServer::GetTables(const char* /*dbname*/, const char* wild)
{
   if (!IsConnected()) {
      Error("GetTables", "not connected");
      return nullptr;
   }
 
   TString sql = "SELECT name FROM sqlite_master where type='table'";
   if (wild)
      sql += Form(" AND name LIKE '%s'", wild);
 
   return Query(sql);
}
 
 
 
 
TList* KVSQLiteServer::GetTablesList(const char* wild)
{
   auto l = TSQLServer::GetTablesList(wild);
   if(!l)
   {
      // if database is locked, try again until it works
      if(IsLocked())
      {
         int i=2;
         do
         {
            Info("GetTablesList","Database is locked. Try again in 50ms...");
            std::this_thread::sleep_for(50ms);
            Info("GetTablesList", "Try number %d...",i);
            l = TSQLServer::GetTablesList(wild);
         }
         while(!l && IsLocked() && (i<6));
         if(l && !IsLocked())
            Info("GetTablesList","...database access successful!");
      }
   }
   return l;
}
 
 
 
 
 
TSQLResult* KVSQLiteServer::GetColumns(const char* /*dbname*/, const char* table,
                                      const char* wild)
{
   if (!IsConnected()) {
      Error("GetColumns", "not connected");
      return nullptr;
   }
 
   if (wild) {
      Error("GetColumns", "Not implementable for SQLite as a query with wildcard, use GetFieldNames() after SELECT instead!");
      return nullptr;
   }
   else {
      TString sql = Form("PRAGMA table_info('%s')", table);
      return Query(sql);
   }
}
 
 
 
 
 
TSQLTableInfo* KVSQLiteServer::GetTableInfo(const char* tablename)
{
   if (!IsConnected()) {
      Error("GetTableInfo", "not connected");
      return nullptr;
   }
 
   if (!tablename || (*tablename == 0)) return nullptr;
 
   TSQLResult* columnRes = GetColumns("", tablename);
 
   if (columnRes == nullptr) {
      Error("GetTableInfo", "could not query columns");
      return nullptr;
   }
 
   TList* lst = nullptr;
 
   TSQLRow* columnRow;
 
   while ((columnRow = columnRes->Next()) != nullptr) {
      if (!lst) {
         lst = new TList();
      }
 
      // Field 3 is 'notnull', i.e. if it is 0, column is nullable
      Bool_t isNullable = (strcmp(columnRow->GetField(3), "0") == 0);
 
      lst->Add(new TSQLColumnInfo(columnRow->GetField(1), // column name
                                  columnRow->GetField(2), // column type name
                                  isNullable,  // isNullable defined above
                                  -1,   // SQLite is totally free about types
                                  -1,   // SQLite imposes no declarable size-limits
                                  -1,   // Field length only available querying the field
                                  -1,   // no data scale in SQLite
                                  -1)); // SQLite does not enforce any sign(s)
      delete columnRow;
   }
   delete columnRes;
 
   // lst == NULL is ok as TSQLTableInfo accepts and handles this
   TSQLTableInfo*  info = new TSQLTableInfo(tablename,
         lst);
 
   return info;
}
 
 
 
 
 
Int_t KVSQLiteServer::CreateDataBase(const char* /*dbname*/)
{
   Error("CreateDataBase", "CreateDataBase command makes no sense for SQLite!");
   return -1;
}
 
 
 
 
 
Int_t KVSQLiteServer::DropDataBase(const char* /*dbname*/)
{
   Error("DropDataBase", "DropDataBase command makes no sense for SQLite!");
   return -1;
}
 
 
 
 
 
Int_t KVSQLiteServer::Reload()
{
   if (!IsConnected()) {
      Error("Reload", "not connected");
      return -1;
   }
 
   Error("Reload", "not implemented");
   return 0;
}
 
 
 
 
 
Int_t KVSQLiteServer::Shutdown()
{
   if (!IsConnected()) {
      Error("Shutdown", "not connected");
      return -1;
   }
 
   Error("Shutdown", "not implemented");
   return -1;
}
 
 
 
 
 
Bool_t KVSQLiteServer::HasStatement() const
{
   return kTRUE;
}
 
 
 
 
 
TSQLStatement* KVSQLiteServer::Statement(const char* sql, Int_t)
{
   if (!sql || !*sql) {
      SetError(-1, "no query string specified", "Statement");
      return nullptr;
   }
 
   if (!IsConnected()) {
      Error("Statement", "not connected");
      return nullptr;
   }
 
   sqlite3_stmt* preparedStmt = nullptr;
   const char* tail = nullptr;
 
   ClearError();
 
   // -1 as we read until we encounter a \0.
#if SQLITE_VERSION_NUMBER >= 3005000
   int retVal = sqlite3_prepare_v2(fSQLite, sql, -1, &preparedStmt, &tail);
#else
   int retVal = sqlite3_prepare(fSQLite, sql, -1, &preparedStmt, &tail);
#endif
   if (retVal != SQLITE_OK) {
      SetError(retVal,sqlite3_errmsg(fSQLite),"Statement");
      return nullptr;
   }
   if (tail && tail[0] != '\0')
      Warning("Statement", "Don't use multiple statements, '%s' statement was ignored", tail);
 
   SQLite3_Stmt_t* stmt = new SQLite3_Stmt_t;
   stmt->fConn = fSQLite;
   stmt->fRes  = preparedStmt;
 
   return new KVSQLiteStatement(stmt);
}
 
 
 
 
 
const char* KVSQLiteServer::ServerInfo()
{
   if (!IsConnected()) {
      Error("ServerInfo", "not connected");
      return nullptr;
   }
 
   return fSrvInfo.Data();
}
 
 
 
 
bool KVSQLiteServer::IsLocked() const
{
   return GetErrorCode()==SQLITE_BUSY;
}
 
 
 
 
bool KVSQLiteServer::IsOK() const
{
   return GetErrorCode()==SQLITE_OK;
}