HarpoDB.cxx

Go to the documentation of this file.

// 
// File HarpoDB.cxx
//
#include "HarpoDB.h"
#include "HarpoDebug.h"
#include "TSQLServer.h"
#include "TSQLResult.h"
#include "TSQLRow.h"
#include <TSystem.h>
#include <strings.h>
#include <stdlib.h>
#include <libgen.h>
#include <algorithm>
#include <iostream>
#include <fstream>

using namespace std;


TSQLServer * HarpoDB::db = (TSQLServer *) NULL;
// HarpoDB::dbUrl = TString("mysql://127.0.0.1/harpo","reader", "ro");
TString HarpoDB::dbUrl = TString("mysql://llrharpo.in2p3.fr/harpo");
TString HarpoDB::dbuser = TString("reader");
TString HarpoDB::dbpw  = TString("ro");

const char *HarpoDB::GetDBName()
{
  return basename((char *)dbUrl.Data());
}

void HarpoDB::SetUser(TString u, TString p) {
  dbuser = u;
  dbpw = p;
}

void HarpoDB::list()
{
  // list tables in database "harpo"
  cout <<  "List all tables in database \"" << GetDBName()
            << "\" on server " << db->GetHost()  << endl;
  
  TIter next(fTables);
  TObject* obj;
  while ( (obj = next()) )
    cout << "Table: " << obj->GetName() << endl;
}

Bool_t HarpoDB::Open(const TString *DbUrl)
{
  if(gHarpoDebug>0)
    printf("DB %s\n",__FUNCTION__);

  if ( DbUrl != NULL ) {
    // Url Given
    dbUrl = *DbUrl;
  } else {
    const char *dburl = gSystem->Getenv("HARPO_DB_URL");
    if ( dburl != NULL ) {
      if ( strlen(dburl) > 0 )
        dbUrl = TString(dburl);
    }
  }

  if ( dbUrl.IsNull() )
    cout << " Warn NO Database URL" << endl;

  db = TSQLServer::Connect(dbUrl.Data(),dbuser.Data(), dbpw.Data());

  if ( db == NULL ) { 
    cout << " Error acess a database" << endl;
    return false;
  }
  
  if(gHarpoDebug>0)
    printf("Server info: %s\n", db->ServerInfo());
  
  fTables =  db->GetTablesList();

   return true;
}

Bool_t HarpoDB::Close()
{
  if(gHarpoDebug>0)
    printf("DB %s\n",__FUNCTION__);

  if ( db != NULL) {
    delete fTables;
    delete db;
    db = NULL;
    fTables = NULL;
    return true;
  }
  else
    return false;
}

Bool_t HarpoDB::GetTable(const TString name,HarpoDBTable &table)
{
  if(gHarpoDebug>0)
    printf("DB %s\n",__FUNCTION__);

  const char *nm = name.Data();
  if ( fTables->FindObject(nm) == 0 ) {
    //    printf("No Table %s in DB %s\n",nm,GetDBName());
    return false;
  }
  return table.Open(GetDBName(), nm);
}

Bool_t HarpoDBTable::Open(const char *dbnm, const char *tbl)
{
  //
  if(gHarpoDebug>0)
    printf("%s\n",__FUNCTION__);

  TSQLRow *row;
  TSQLResult *res;

  TSQLServer *db = HarpoDB::GetDBServer();
  if ( db == NULL )
    return false; // server not opened
  
  res = db->GetColumns(dbnm, tbl);
  //  printf("No of field in GetColomns %d\n",res->GetFieldCount());
  while ((row = res->Next())) {
    
    if ( ftypes.find(row->GetField(0)) == ftypes.end() ) {
      ftypes[row->GetField(0)] = row->GetField(1);
    } else {
      //      if(gHarpoDebug>0)
        cout << "key " << row->GetField(0) << " exist" << endl;  
    }
    delete row;
  }
  delete res;
  name = tbl;
  return true;
}

Bool_t HarpoDB::ExecQuery(const TString sql,dbResults *list)
{
  if(gHarpoDebug>0)
    printf("%s\n",__FUNCTION__);
  
  if ( db == NULL ) return false; // server not opened

  TSQLRow *mrow;
  TSQLResult *res = NULL;
  if(gHarpoDebug>0)
    cout << "SQL : " << sql << endl;
  res = db->Query(sql.Data());
   if ( res == NULL ) {
     // res == NULL ==> Query error
     return false;
   }
   int nrows = res->GetRowCount();
   // cout << "nrow " << nrows << " list " << list << endl;
   if  ( (list != NULL ) && (nrows > 0)) {
     int nfields = res->GetFieldCount();
     while ((mrow = res->Next()) != NULL) {;
       dbFields fields;
       for (int j = 0; j < nfields; j++) {
         //      if (mrow->GetFieldLength(j) > 0 ) {
           fields[res->GetFieldName(j)] =mrow->GetField(j);
           //    }
       }
       //       cout << "map size " << fields.size() << endl;
       list->push_back(fields);
       delete mrow;
     }
   }
   delete res;
   return true;
}

Bool_t HarpoDBTable::GetRunRow(Long_t nrun, HarpoDBRow &row )
{
  if(gHarpoDebug>0)
     printf("%s\n",__FUNCTION__);

  // Make empty run row
  row.SetRun(nrun);
  row.GetFields().clear();

  if ( ftypes.empty() ) {
    printf("No table info or DB read error\n");
    return false;
  }

  TSQLServer *db = HarpoDB::GetDBServer();
  if ( db == NULL )
    return false; // server not opened

  TSQLRow *mrow;
  TSQLResult *res = NULL;

  TString sql;

  sql += "SELECT ";
    for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
      if ( it != ftypes.begin()) sql += ", ";
      sql += "`" + it->first + "`";
    }
    sql += " FROM `" + name + "` WHERE `run` = ";
    sql += nrun;
    sql +=";";
      if(gHarpoDebug>0)
        cout << "SQL : " << sql << endl;
   
  // // query database and print results
  // //if ( db->SelectDataBase("harpo") != 0 ) return rec_found;
    res = db->Query(sql.Data());
    if ( res == NULL ) {
      // res == NULL ==> Query error
      return false;
    }

    int nrows = res->GetRowCount();

    //printf("\nGot %d rows in result\n", nrows);
    if (nrows != 1) return false; // Bad query

    int nfields = res->GetFieldCount();
    
    // cout << "N Fields in"  <<  name << " : " << nfields 
    //        << endl;

    dbFields &fields = row.GetFields();
    mrow = res->Next();
    for (int j = 0; j < nfields; j++) {
      if (mrow->GetFieldLength(j) > 0 ) {
        // cout << "f " << res->GetFieldName(j) << " v " <<  mrow->GetField(j)
        //        << endl; 
        fields[res->GetFieldName(j)] = mrow->GetField(j);
        // cout << "map size " << fields.size() << endl;
      }
    }
    delete mrow;
    delete res;
    return true;
}

// List Fields and values
void HarpoDBRow::list()
{
  if ( fields.empty() ) {
    printf("Empty Row.\n");
  } else {
    for (dbFields::iterator it=fields.begin(); it!=fields.end(); ++it) {
      cout << it->first << " = "<< it->second << endl;
    }
  }
}

// List Fileds name and type
void HarpoDBTable::list()
{
  cout <<  " Table : " << name << "======================"  << endl;
  if ( ftypes.empty() ) {
    printf("No table info or DB read error\n");
  } else {
    for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
      cout << it->first << " ("<< it->second  << ")" << endl;
    }
  }
}

//Look if filed exist in row
Bool_t HarpoDBRow::Lookup(const TString field)
{
  return fields.find(field) != fields.end();
}

//Look if filed exist in table
Bool_t HarpoDBTable::Lookup(const TString field)
{
  return ftypes.find(field) != ftypes.end();
}

// Get Value
Bool_t HarpoDBRow::GetVal(const TString field, TString &val)
{
  dbFields::iterator it = fields.find(field);
  if ( it != fields.end() ) {
    val = it->second;
    return true;
  }
  return false;
}

//Set value for existing field
Bool_t HarpoDBRow::SetVal(const TString field, TString val)
{
  dbFields::iterator it = fields.find(field);
  if ( it != fields.end() ) {
    it->second = val;
    return true;
  }
  return false;
}

Bool_t HarpoDBRow::SetVal(const TString field, Long_t val)
{
  TString sval; sval += val;
  return SetVal(field,sval);
}

Bool_t HarpoDBRow::SetVal(const TString field, Double_t val)
{
  TString sval; sval += val;
  return SetVal(field,sval);
}

// Set Map of fields types
void HarpoDBRow::SetFields(dbFields f)
{
  fields = f;
  for (dbFields::iterator it=fields.begin(); it!=fields.end();++it) {
       if ( it->second.Contains("date") || it->second.Contains("time")
           || it->second.Contains("char") )
        { // need quote
          it->second = ""; // replces "string" types with empy string
        } else {
          it->second = "0"; // replces other types with )
       }
  }
}

// Get Filed type
Bool_t HarpoDBTable::GetType(const TString field, TString &type)
{
  dbFields::iterator it = ftypes.find(field);
  if ( it != ftypes.end() ) {
    type = it->second;
    return true;
  }
  return false;
}

Bool_t HarpoDBTable::TypeExpression(const TString field)
{
  dbFields::iterator it = ftypes.find(field);
  if ( it != ftypes.end() ) {
    it->second = "expression"; // not a mysql type , but will not quite
    return true;
  }
  return false;
}


// Update DB record base on class data
Bool_t HarpoDBTable::DeleteRun(Long_t run)
{
  if(gHarpoDebug>0)
    printf("%s\n",__FUNCTION__);
  
  TSQLServer *db = HarpoDB::GetDBServer();
  if ( db == NULL )
    return false; // server not opened

  TString sql;

  sql += "DELETE FROM `" + name + "` WHERE `run` = " + run + ";";

  //  TSQLRow *mrow;
  TSQLResult *res = NULL;
  res = db->Query(sql.Data());
  if ( res == NULL ) {
    // res == NULL ==> Query error
    return false;
  }
  delete res;
  return true;
}

// Update DB record base on class data
Bool_t HarpoDBTable::UpdateRunRow(HarpoDBRow row,const TString option)
{
  if(gHarpoDebug>0)
    printf("%s\n",__FUNCTION__);

  if ( ftypes.empty() ) {
    printf("No table info or DB read error\n");
    return false;
  }

  if ( row.GetFields().empty() ) {
    printf("Can't update empty Row\n");
    return false;
  }

  Bool_t verbose = option.Contains("verbose"); // verbose show query string

  // nap::erase invalidate iterator !!!
  dbFields &rfields = row.GetFields();
  dbFields fields;  // local copy
  dbFields::iterator itype;
  for (dbFields::iterator it=rfields.begin(); it!=rfields.end();++it) {
    //    cout << " Processing : " << it->first << endl;;
    if (ftypes.find(it->first) != ftypes.end()) {
          fields[it->first] = it->second;
    }
  }

  for (dbFields::iterator it=fields.begin(); it!=fields.end(); ++it) {
    // cout << " Quoting : " << it->first << endl;;
    if ((itype =ftypes.find(it->first)) != ftypes.end() ) {
       if ( itype->second.Contains("date") || itype->second.Contains("time")
           || itype->second.Contains("char") )
        { // need quote
          it->second.Prepend('"');
          it->second.Append('"');
        } 
    }
  }

  TSQLServer *db = HarpoDB::GetDBServer();
  if ( db == NULL )
    return false; // server not opened

  TString sql;

  sql += "UPDATE `" + name + "` SET ";
    for (dbFields::iterator it=fields.begin(); it!=fields.end(); ++it) {
      if ( it->first.EqualTo("run")) continue;  // skeep run field
      if ( it != fields.begin()) sql += ", ";
      sql += "`" + it->first + "` = " + it->second;
    }
    sql += " WHERE `run` = ";
    sql += row.GetNrun();
    sql +=";";
    if (verbose)
      cout << "SQL : " << sql << endl;

   TSQLResult *res;
   res = db->Query(sql.Data());
   if ( res == NULL ) {
     // res == NULL ==> Query error
     return false;
   }
   delete res;
  return true;
}

// Update DB record base on class data
Bool_t HarpoDBTable::InsertRunRow(HarpoDBRow row,const TString option)
{
  if(gHarpoDebug>0)
    printf("%s\n",__FUNCTION__);

  if ( ftypes.empty() ) {
    printf("No table info or DB read error\n");
    return false;
  }

  if ( row.GetFields().empty() ) {
    printf("Can't update empty Row\n");
    return false;
  }

  Bool_t verbose = option.Contains("verbose"); // verbose show query string
  Bool_t update = option.Contains("update");  // update on duplicated key
  
  dbFields &rfields = row.GetFields();
  dbFields fields;  // local copy
  dbFields::iterator itype;
  for (dbFields::iterator it=rfields.begin(); it!=rfields.end();++it) {
    //    cout << " Processing : " << it->first << endl;;
    if (ftypes.find(it->first) != ftypes.end()) {
          fields[it->first] = it->second;
    }
  }

  for (dbFields::iterator it=fields.begin(); it!=fields.end(); ++it) {
    //  cout << " Quoting : " << it->first << endl;;
    if ((itype =ftypes.find(it->first)) != ftypes.end() ) {
       if ( itype->second.Contains("date") || itype->second.Contains("time")
           || itype->second.Contains("char") )
        { // need quote
          it->second.Prepend('"');
          it->second.Append('"');
        } 
    }
  }

  TSQLServer *db = HarpoDB::GetDBServer();
  if ( db == NULL )
    return false; // server not opened

  TString sql;
  TString data;
  TString ondup;

  sql += "INSERT INTO `" + name + "` (";
  for (dbFields::iterator it=fields.begin(); it!=fields.end(); ++it) {
    if ( it != fields.begin()) {
      sql += ", ";
      data += ", ";
    }
    sql += "`" + it->first + "`";
    data += it->second;
    if (update &&  (! it->first.EqualTo("run"))) {
      if (it != fields.begin() ) ondup += ", "; 
      ondup += "`" + it->first + "`=VALUES(`" + it->first + "`)" ;
    }
  }
  sql +=") VALUES (";
  sql += data;
  sql += ")";
  if (update)
    sql += " ON DUPLICATE KEY UPDATE " + ondup;
  sql += ";";
  if (verbose)
    cout << "SQL : " << sql << endl;
  TSQLResult *res;
  res = db->Query(sql.Data());
  if ( res == NULL ) {
    // res == NULL ==> Query error
    return false;
  }
  delete res;
  return true;
}
// line
#define wl(line) \
  fout << #line << endl;
// quoted lines
#define wq(line) \
  fout << line << endl;
// line with class name
#define wlc(pre,class,post) \
  fout << #pre + class + #post << endl;

#define wqc(pre,class,post) \
  fout << pre + class + post << endl;
void HarpoDBTable::MakeClass(TString cname,TString /* option */)
{
  TString clname;
  if (cname.IsNull()) {
    clname += "HarpoDB" + name; // From Table Name
  } else {
    clname = cname;
  }
  TString cluname = clname;
  cluname.ToUpper();
  TString fname;
  // LinkDef.h
  fname = clname + "LinkDef.h";
  cout << "Writing " << fname << endl;
  ofstream fout;
  fout.open(fname.Data());
  wl(#ifdef __CINT__)
  wl(#pragma link off all globals;)
  wl(#pragma link off all classes;)
  wl(#pragma link off all functions;)
  wl()
  wqc("#pragma link C++ class ",clname,"+;")
  wl()
  wl(#endif)
  fout.close();
    
  //.h
  fname = clname + ".h";
  cout << "Writing " << fname << endl;
  fout.open(fname.Data());
  wqc("#ifndef ",cluname,"_H")
  wqc("#define ",cluname,"_H")
  wq("//////////////////////////////////////////////////////////////////////////")
  wq("//")
  wqc("// ",clname," (Generated by HarpoDBTable::MakeClass())")
  wq("//")
  wq("//////////////////////////////////////////////////////////////////////////")
  wl(#include "TObject.h")
  wl(#include "TString.h")
  wl(#include "HarpoDB.h")
  wl()
  wqc("class ",clname," : public TObject {")
  wl( public:)
  wqc("  ",clname,"();")
  wqc("  virtual ~",clname,"();")
  wl()
// List of varibles
  TString vartype;
  for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
    if (it->second.Contains("bool") ) {
      //      vartype = "Bool_t";
      vartype = "Long_t";
    } else if (it->second.Contains("int") ) {
      vartype = "Long_t";
    } else if (it->second.Contains("dec") || it->second.Contains("float") ||
               it->second.Contains("real") || it->second.Contains("double") ) {
      vartype = "Double_t";
    } else if ( it->second.Contains("date") || it->second.Contains("time")
         || it->second.Contains("char") ) { // need quote
      vartype = "TString";
    } else {
      vartype = "TString";
    }
    fout << "  " << vartype << " " << it->first << "; // " <<
      it->second << endl;
  }
  wl()
  wq("  // Import From Raw")
  wq("  void Import(HarpoDBRow &row);")
  wl()
  wq("  // Export to Raw")
  wq("  void Export(HarpoDBRow &row);")
  wl()
  wq("  // Reset")
  wq("  void Reset();")
  wl()
  wq("  // Print")
  wq("  void print();")
  wl()
  wl( protected:)
  wl()
  wl( private:)
  wl()
  wqc("ClassDef(",clname,",1) //! ROOT CINT declaration")
  wl(};)
  wl()
  wl()
  wqc("#endif // ",cluname,"_H")
  wq("// Local Variables:")
  wq("// mode: c++")
  wq("// End:")
  fout.close();

  // .cxx
  fname = clname + ".cxx";
  cout << "Writing " << fname << endl;
  fout.open(fname.Data());
  wq("//////////////////////////////////////////////////////////////////////////")
  wq("//")
  wqc("
  wq("//")
  wqc("
  wq("//")
  wq("
  wl()
  wqc("#include \"",clname,".h\"")
  wl(#include <iostream>)
  wl()
  wqc("ClassImp(",clname,")")
  wl()
  wq("// Constructor")
  wqc("",clname + "::" + clname,"()")
  wl({)
  wq("  // Empy default constructor for ROOT IO")
  wl(})
  wl()
  wq("// Distructor")
  wqc("",clname + "::~" + clname,"()")
  wl({)
  wq("  //...")
  wl(})
  wl()
  wq("// Import From Row")
  wqc("void ",clname,"::Import(HarpoDBRow &row)")
  wl({)
  //  Import function
  wq("  TString var;")
  TString func;
  for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
    if (it->second.Contains("bool") ) {
      func = ".Atoll();";
    } else if (it->second.Contains("int") ) {
      func = ".Atoll();";
    } else if (it->second.Contains("dec") || it->second.Contains("float") ||
               it->second.Contains("real") || it->second.Contains("double") ) {
      func = ".Atof();";
    } else if ( it->second.Contains("date") || it->second.Contains("time")
         || it->second.Contains("char") ) { // need quote
      func = ";";
    } else {
      func = ";";
    }
    fout << "  if (row.GetVal(" << '"' << it->first << '"' << ",var))" << endl;
      fout << "    " << it->first << " = var" << func << endl;
  }
  wl(})
  wl()
  wq("// Export to Row")
  wqc("void ",clname,"::Export(HarpoDBRow &row)")
  wl({)
  //  Import function
  for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
    fout << "  if (row.Lookup(" << '"' << it->first << '"' << "))" << endl;
      fout << "    row.SetVal(" << '"' << it->first << '"' <<
        ", "<< it->first << ");"<< endl;
  }
  wl(})
  wl()
  wq("// Reset to zero values")
  wqc("void ",clname,"::Reset()")
  wl({)
  TString def;
  for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
    if (it->second.Contains("bool") ) {
      def = "false";
    } else if (it->second.Contains("int") ) {
      def = "0";
    } else if (it->second.Contains("dec") || it->second.Contains("float") ||
               it->second.Contains("real") || it->second.Contains("double") ) {
      def = "0.0";
    } else if ( it->second.Contains("date") || it->second.Contains("time")
         || it->second.Contains("char") ) { // need quote
      def = "TString()";
    } else {
      def = "TString()";
    }
    fout << "    " << it->first << " = " << def << ";"<< endl;
  }
  wl(})
  wl()
  wq("// Print")
  wqc("void ",clname,"::print()")
  wl({)
  for (dbFields::iterator it=ftypes.begin(); it!=ftypes.end(); ++it) {
    fout << "    std::cout << ";
    fout << "\"" << it->first << " = \" << " << it->first ;
    fout << " << std::endl;" << endl;
  }
  wl(})
  wl()
  wqc("// EOF ",clname,".cxx")
  fout.close();

}
//EoF