SQLiteDB.h

 
#ifndef __SQLITEDB_H
#define __SQLITEDB_H
 
#include "KVSQLiteServer.h"
#include "TSQLStatement.h"
#include "TSQLResult.h"
#include "KVConfig.h"
#include <utility>
#include <iostream>
#include <unordered_map>
#include <list>
#include <map>
#include <KVNameValueList.h>
#include <KVNumberList.h>
#include "TGraph.h"
 
namespace KVSQLite {
 
   namespace column_type {
      enum types {
         REAL,
         INTEGER,
         TEXT,
         TIMESTAMP,
         BLOB
      };
   }
   namespace insert_mode {
      enum types {
         DEFAULT,
         IGNORE,
         FAIL,
         REPLACE
      };
   }
 
   typedef KVSQLite::insert_mode::types KVSQLite_insert_mode;
   typedef KVSQLite::column_type::types KVSQLite_column_type;
 
   class table;
 
   class column {
      friend class table;
      friend class database;
 
      std::pair<TString, KVSQLite_column_type> fNameType; //name & type of column
      TString fConstraint;//column constraint
      int fIndex;//index of column
      static std::map<KVSQLite::column_type::types, TString> inv_type_map;
      mutable KVNamedParameter fData; // data item in column
      bool has_data;
      mutable void* fBlob;
      mutable Long_t fBlobSize;// size of blob
      bool fIsROWIDAlias;// true if column is INTEGER PRIMARY KEY
      mutable bool fIsNull;//for inserting NULL values
      TString fTable;// name of parent table
 
      void init_type_map();
      TString _type();
 
      column(int idx, const TString& name, KVSQLite_column_type type)
         : fNameType(name, type), fConstraint(""), fIndex(idx), fData(name),
           has_data(false), fBlob(nullptr), fBlobSize(0),
           fIsROWIDAlias(false), fIsNull(false)
      {
         if (!inv_type_map.size()) init_type_map();
      }
   public:
      enum class default_time {
         CURRENT_DATE,
         CURRENT_TIME,
         CURRENT_TIMESTAMP
      };
      virtual ~column()
      {
         if (fBlob) delete[](unsigned char*)fBlob;
      }
 
      const char* name() const
      {
         return fNameType.first;
      }
      TString Name() const
      {
         return fNameType.first;
      }
      KVSQLite_column_type type() const
      {
         return fNameType.second;
      }
      TString TypeName() const
      {
         return const_cast<column*>(this)->_type();
      }
      TString get_declaration() const;
      TString get_table() const
      {
         return fTable;
      }
      void set_table(const TString& name)
      {
         fTable = name;
      }
      int index() const
      {
         return fIndex;
      }
 
      void print() const
      {
         std::cout << fIndex << "\t" << name() << "\t" << TypeName() << "\n";
      }
      template<typename T>
      void set_data(const T& x)
      {
         fData.Set(x);
         fIsNull = false;
         has_data = true;
      }
      template <typename T>
      const column& operator=(const T& x)
      {
         set_data(x);
         return *this;
      }
 
      void set_null()
      {
         fIsNull = true;
      }
      bool is_null() const
      {
         return fIsNull;
      }
 
      template<typename T>
      void set_binary_data(T& x)
      {
         fBlob = (void*)&x;
         fBlobSize = sizeof(x);
         fIsNull = false;
      }
      template<typename T>
      void set_binary_data(T* x)
      {
         fBlob = (void*)x;
         fBlobSize = sizeof(*x);
         fIsNull = false;
      }
 
      void set_data_in_statement(TSQLStatement*, int idx = -1) const;
      void set_data_from_statement(TSQLStatement* s, int idx = -1) const;
      void set_constraint(const TString& c)
      {
         fConstraint = c;
      }
      column& UNIQUE()
      {
         if (fConstraint.IsNull()) fConstraint = "UNIQUE";
         else fConstraint += " UNIQUE";
         return *this;
      }
      column& NOT_NULL()
      {
         if (fConstraint.IsNull()) fConstraint = "NOT NULL";
         else fConstraint += " NOT NULL";
         return *this;
      }
      column& DEFAULT(default_time t)
      {
         TString defs = "DEFAULT ";
         switch (t) {
            case default_time::CURRENT_DATE:
               defs += "CURRENT_DATE";
               break;
            case default_time::CURRENT_TIME:
               defs += "CURRENT_TIME";
               break;
            case default_time::CURRENT_TIMESTAMP:
               defs += "CURRENT_TIMESTAMP";
         }
         if (fConstraint.IsNull()) fConstraint = defs;
         else fConstraint += " " + defs;
         return *this;
      }
      column& DEFAULT(int i)
      {
         TString defs = Form("DEFAULT %d", i);
         if (fConstraint.IsNull()) fConstraint = defs;
         else fConstraint += " " + defs;
         return *this;
      }
      column& DEFAULT(double d)
      {
         TString defs = Form("DEFAULT %f", d);
         if (fConstraint.IsNull()) fConstraint = defs;
         else fConstraint += " " + defs;
         return *this;
      }
      column& DEFAULT(const TString& s)
      {
         TString defs = Form("DEFAULT %s", s.Data());
         if (fConstraint.IsNull()) fConstraint = defs;
         else fConstraint += " " + defs;
         return *this;
      }
      const KVNamedParameter& data() const
      {
         return fData;
      }
      template <typename T>
      T get_data() const
      {
         return fData.Get<T>();
      }
      template<typename T>
      T* binary_data() const
      {
         return static_cast<T*>(fBlob);
      }
      bool rowid_alias() const
      {
         return fIsROWIDAlias;
      }
      void set_rowidalias()
      {
         fIsROWIDAlias = true;
      }
      TString get_data_string_for_insert();
 
      ClassDef(column, 0) //Column in an SQLite database table
   };
   template<> void column::set_data(const KVNamedParameter&);
 
   class database;
 
   class foreign_key_constraint {
      friend class table;
      friend class database;
 
      TString child_key, parent_table, parent_key;
      bool back_reference = false;
   public:
      foreign_key_constraint() = default;
      foreign_key_constraint(foreign_key_constraint&&) = default;
      foreign_key_constraint(const foreign_key_constraint&) = default;
      foreign_key_constraint(const TString& chk, const TString& pt, const TString& pk = "", bool back_ref = false)
         : child_key(chk), parent_table(pt), parent_key(pk), back_reference(back_ref)
      {}
      bool is_back_reference() const
      {
         return back_reference;
      }
      ClassDef(foreign_key_constraint, 0)
   };
 
   class table {
      friend class database;
      database* fDB = nullptr;
      TString fName;//name of table
      KVSQLite_insert_mode fInsert;//insert mode
      mutable std::vector<KVSQLite::column> fColumns;//list of columns
      mutable std::unordered_map<std::string, int> fColMap; //map name of column to index
      static std::map<TString, KVSQLite::column_type::types> type_map;
      TString fPrimaryKey; // primary key column(s) if required
      std::vector<foreign_key_constraint> fForeignKeys;
      bool fTemp;//temporary table?
 
      void init_type_map();
      void set_foreign_key_back_references();
 
   public:
      table(const TString& Name, const std::vector<KVSQLite::column>& cols)
         : fName(Name), fInsert(KVSQLite::insert_mode::DEFAULT), fColumns(cols), fColMap(), fTemp(false)
      {
         if (!type_map.size()) init_type_map();
         for (auto& c : fColumns) c.set_table(Name);
      }
      table(const TString& Name = "")
         : table(Name, {})
      {}
      table(const table& other)
         : fDB(other.fDB), fName(other.fName), fInsert(other.fInsert), fColumns(other.fColumns), fColMap(other.fColMap),
           fPrimaryKey(other.fPrimaryKey), fForeignKeys(other.fForeignKeys), fTemp(other.fTemp)
      {}
      virtual ~table() {}
 
      const char* name() const
      {
         return fName;
      }
      void set_name(const TString& name)
      {
         fName = name;
      }
      TString get_table_creation_command() const;
 
      void show_columns() const;
      void set_insert_mode(KVSQLite_insert_mode i)
      {
         fInsert = i;
      }
      const char* get_insert_command() const;
      void set_temporary(bool temp = true)
      {
         fTemp = temp;
      }
      bool is_temporary() const
      {
         return fTemp;
      }
 
      column& add_column(const KVSQLite::column& c);
      column& add_column(const TString& name, KVSQLite_column_type type)
      {
         return add_column(KVSQLite::column(fColumns.size(), name, type));
      }
      column& add_column(const TString& name, const TString& type);
      void foreign_key(const TString& child_key, const TString& parent_table, const TString& parent_key);
      const KVSQLite::column& operator[](int i) const
      {
         return fColumns[i];
      }
      KVSQLite::column& operator[](int i)
      {
         return fColumns[i];
      }
      KVSQLite::column& get_column(int i)
      {
         return fColumns[i];
      }
      bool has_column(const TString& name) const
      {
         return fColMap.count(name.Data());
      }
 
      KVSQLite::column& operator[](const TString& n)
      {
         return get_column(n);
      }
      const KVSQLite::column& operator[](const TString& n) const
      {
         return const_cast<table*>(this)->get_column(n);
      }
      KVSQLite::column& get_column(const TString& n)
      {
         if (!has_column(n)) {
            TString what = "Error in <KVSQLite::table::get_column(const TString&)> : "
                           + n + " is not a column of table " + name();
            throw std::runtime_error(what.Data());
         }
         return fColumns[fColMap[n.Data()]];
      }
 
      void print() const
      {
         std::cout << name() << "\n";
         for (std::vector<KVSQLite::column>::const_iterator it = fColumns.begin(); it != fColumns.end(); ++it) it->print();
      }
      int number_of_columns() const
      {
         return fColumns.size();
      }
      int check_columns(const KVNameValueList&);
      void prepare_data(const KVNameValueList&, const KVNamedParameter* = nullptr);
      void set_all_columns_null();
 
      TString get_column_names(const TString& exclude = "", const TString& delim = ",") const;
 
      void primary_key(const TString& cols)
      {
 
         fPrimaryKey = cols.Strip(TString::kBoth, ' ');
         if (!fPrimaryKey.Contains(",")) {
            if (get_column(fPrimaryKey).type() == KVSQLite_column_type::INTEGER)
               get_column(fPrimaryKey).set_rowidalias();
         }
      }
      bool has_primary_key() const
      {
         return !fPrimaryKey.IsNull();
      }
      bool has_foreign_keys(bool ignore_back_references = true) const
      {
         if (!ignore_back_references) return !fForeignKeys.empty();
         for (auto& f : fForeignKeys)
            if (!f.back_reference) // ignore back-references from foreign keys in other tables
               return true;
         return false;
      }
 
      void prepare_insert_single_row();
      void insert_single_row();
 
      ClassDef(table, 0) //Table in an SQLite database
   };
 
   class database {
      std::unique_ptr<KVSQLiteServer> fDBserv;       //connection to database
      mutable std::unordered_map<std::string, KVSQLite::table> fTables; //map of tables in database
      mutable std::unique_ptr<TSQLStatement> fSQLstmt;     //used for bulk operations
      mutable std::list<const column*> fSQLstmtCols; // columns used in SQL statement
      mutable table* fBulkTable;            //pointer to table currently used with fSQLstmt
      mutable bool fInserting;
      mutable bool fSelecting;
      mutable bool fEmptyResultSet;
      bool fIsValid;
      mutable TString fSelectedColumns;
 
      void PrintResults(TSQLResult* tabent, int column_width = 20) const;
      std::unique_ptr<TSQLResult> SelectRowsFromTable(
         const TString& table,
         const TString& columns = "*",
         const TString& condition = "") const;
 
      void read_table_infos();
      std::list<const table*> generate_table_selection_string(const TString& tables, KVString& table_selection) const;
      KVString double_quote_all_tables_and_columns(const TString&) const;
 
      column count_column{0, "COUNT", column_type::INTEGER};
   public:
      static bool debug;
      database() : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false) {}
      database(const TString& dbfile) : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false)
      {
         open(dbfile);
      }
      database(const database& db) : fDBserv(nullptr), fTables(), fSQLstmt(nullptr), fBulkTable(nullptr), fInserting(false), fSelecting(false), fIsValid(false)
      {
         if (db.good()) open(db.fDBserv->GetDB());
      }
      database& operator= (const database& db)
      {
         if (&db != this && db.good()) open(db.fDBserv->GetDB());
         return *this;
      }
      void show_tables() const;
      int get_number_of_tables() const
      {
         return fTables.size();
      }
      virtual ~database()
      {
         close();
      }
      bool is_inserting() const
      {
         return fInserting;
      }
 
      void open(const TString& dbfile);
      void close()
      {
         fDBserv->Close();
         fTables.clear();
      }
      bool good() const
      {
         return fIsValid;
      }
 
      bool is_open() const
      {
         return fDBserv->IsConnected();
      }
 
      void Dump() const;
 
      table& add_table(const table&);
      bool has_table(const TString& table)
      {
         return fTables.count(table.Data());
      }
 
      KVSQLite::table& operator[](const TString& name)
      {
         return get_table(name);
      }
      const KVSQLite::table& operator[](const TString& name) const
      {
         return const_cast<database*>(this)->get_table(name);
      }
      KVSQLite::table& get_table(const TString& name)
      {
         if (!fTables.count(name.Data())) {
            std::cout << "Error in <KVSQLite::database::get_table(const TString&)> : "
                      << name << " is not a table of database" << std::endl;
            return fTables.begin()->second;
         }
         return fTables[name.Data()];
      }
 
      bool execute(const TString& sql_statement)
      {
         auto ret = fDBserv->Exec(sql_statement);
         if (!ret && debug) {
            std::cerr << "database::execute : Exec(" << sql_statement <<
                      ") failed with code=" << fDBserv->GetErrorCode()
                      << " : " << fDBserv->GetErrorMsg() << std::endl;
         }
         return ret;
      }
      bool prepare_statement(const TString& query) const
      {
         auto stat = fDBserv->Statement(query);
         if (!stat && debug) {
            std::cerr << "database::prepare_statement : Statement(" << query <<
                      ") failed with code=" << fDBserv->GetErrorCode()
                      << " : " << fDBserv->GetErrorMsg() << std::endl;
            fSQLstmt.reset(nullptr);
            return false;
         }
         else
            fSQLstmt.reset(stat);
         return true;
      }
      std::unique_ptr<TSQLResult> query(const TString& query) const
      {
         auto result = std::unique_ptr<TSQLResult>(fDBserv->Query(query));
         if (!result && debug) {
            std::cerr << "database::query : Query(" << query <<
                      ") failed with code=" << fDBserv->GetErrorCode()
                      << " : " << fDBserv->GetErrorMsg() << std::endl;
         }
         return result;
      }
      bool prepare_data_insertion(const TString&);
      void insert_data_row();
      void end_data_insertion();
 
      bool get_next_result() const;
      KVNumberList get_integer_list(const TString& tables, const TString& column,
                                    const TString& selection = "", const TString& anything_else = "");
      TString get_string_list(const TString& tables, const TString& column,
                              const TString& selection = "", const TString& anything_else = "");
      KVNameValueList get_name_value_list(const TString& table, const TString& name_column, const TString& value_column,
                                          const TString& selection = "", const TString& anything_else = "");
      void get_name_value_list_for_each_row(const TString& table, std::function<bool(const KVNameValueList&)> callback,
                                            const TString& selection = "", const TString& anything_else = "");
      TGraph* create_graph(const TString& tablename, const TString& Xcolumn, const TString& Ycolumn, const TString& selection = "");
 
      void clear_table(const TString& name);
 
      bool select_data(const TString& tables, const TString& columns = "*", const TString& selection = "",
                       bool distinct = false, const TString& anything_else = "") const;
      bool select_data_in_list(const TString& tables, const KVNameValueList& selection, const TString& columns = "*",
                               bool distinct = false, const TString& anything_else = "") const;
      int count(const TString& tables, const TString& column = "*", const TString& selection = "", bool distinct = false, const TString& anything_else = "") const;
      bool update(const TString& table, const TString& columns, const TString& selection = "");
      void delete_rows_in_table(const TString& table, const TString& selection = "");
 
      column& add_column(const TString& table, const TString& name, const TString& type);
      void add_missing_columns(const TString& table, const KVNameValueList& l);
 
      void copy_table_data(const TString& source, const TString& destination, const TString& columns = "*", const TString& selection = "");
      void print_selection(const TString& table, const TString& columns, const TString& condition, int column_width = 20) const;
      void print_selected_data(const TString& tables, const TString& columns = "*", const TString& selection = "", bool distinct = false, const TString& anything_else = "");
 
      void delete_table(const TString& table);
      bool prepare_data_insertion_selected_columns(const TString& table);
      void insert_data_row_selected_columns();
 
      int get_count_column_value() const
      {
         return count_column.get_data<int>();
      }
      ClassDef(database, 0) //Interface to ROOT SQLite database backend
   };
}
 
#endif