SQLiteDB.h

 
#ifndef __SQLITEDB_H
#define __SQLITEDB_H
 
#include "TSQLiteServer.h"
#include "TSQLStatement.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,
         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;
 
      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
      mutable void* fBlob;
      mutable Long_t fBlobSize;// size of blob
      bool fPrimaryKey;
      bool fForeignKey;
      TString fFKtable;//table for foreign key
      TString fFKcolumn;//column for foreign key
      mutable bool fIsNull;//for inserting NULL values
      TString fTable;// name of parent table
 
      void init_type_map();
      const char* _type();
 
      column(int idx, const TString& name, KVSQLite_column_type type)
         : fNameType(name, type), fConstraint(""), fIndex(idx), fData(name),
           fBlob(nullptr), fBlobSize(0),
           fPrimaryKey(false), fForeignKey(false),
           fFKtable(""), fFKcolumn(""), fIsNull(false)
      {
         if (!inv_type_map.size()) init_type_map();
      }
   public:
      virtual ~column()
      {
         if (fBlob) delete[](unsigned char*)fBlob;
      }
 
      const char* name() const
      {
         return fNameType.first;
      }
      KVSQLite_column_type type() const
      {
         return fNameType.second;
      }
      const char* type_name() const
      {
         return const_cast<column*>(this)->_type();
      }
      const char* get_declaration() const;
      const char* 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" << type_name() << "\n";
      }
      template<typename T>
      void set_data(const T& x)
      {
         fData.Set(x);
         fIsNull = false;
      }
      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;
         if (c == "PRIMARY KEY") fPrimaryKey = true;
      }
      void set_foreign_key(const TString& _table, const TString& _column);
      void set_foreign_key(const table& _table, const column& _column);
      bool primary_key() const
      {
         return fPrimaryKey;
      }
      bool foreign_key() const
      {
         return fForeignKey;
      }
      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);
      }
 
      ClassDef(column, 0) //Column in an SQLite database table
   };
   template<> void column::set_data(const KVNamedParameter&);
 
   class table {
     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;
      bool fTemp;//temporary table?
 
      void init_type_map();
 
   public:
      table(const TString& Name = "")
         : fName(Name), fInsert(KVSQLite::insert_mode::DEFAULT), fColumns(), fColMap(), fTemp(false)
      {
         if (!type_map.size()) init_type_map();
      }
      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);
      }
      virtual ~table() {}
 
      const char* name() const
      {
         return fName;
      }
      void set_name(const TString& name)
      {
         fName = name;
      }
 
      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);
      const column& add_primary_key(const TString& name);
      const column& add_foreign_key(const TString& other_table, const TString& other_column);
      const column& add_foreign_key(const table& other_table, const column& other_column);
      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)) {
            std::cout << "Error in <KVSQLite::table::get_column(const TString&)> : "
                      << n << " is not a column of table " << name() << std::endl;
            return fColumns[0];
         }
         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;
 
      ClassDef(table, 0) //Table in an SQLite database
   };
 
   class database {
      std::unique_ptr<TSQLiteServer> 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();
 
   public:
      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;
 
      void 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 prepare_data_insertion(const TString&);
      void insert_data_row();
      void end_data_insertion();
 
      bool select_data(const TString& tables, const TString& columns = "*", const TString& selection = "",
                       bool distinct = false, const TString& anything_else = "") const;
      bool get_next_result() const;
      KVNumberList get_integer_list(const TString& table, const TString& column,
                                    const TString& selection = "", const TString& anything_else = "");
      TString get_string_list(const TString& table, 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 = "");
      TGraph* create_graph(const TString& tablename, const TString& Xcolumn, const TString& Ycolumn, const TString& selection = "");
 
      void clear_table(const TString& name);
 
      int count(const TString& table, const TString& column = "*", const TString& selection = "", bool distinct = false) const;
      bool update(const TString& table, const TString& columns, const TString& selection = "");
      void delete_data(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 = "");
 
      ClassDef(database, 0) //Interface to ROOT SQLite database backend
   };
}
 
#endif