TTreeSQL.cxx

Go to the documentation of this file.

// @(#)root/tree:$Id$
// Author: Philippe Canal and al. 08/2004

/*************************************************************************
 * Copyright (C) 1995-2000, Rene Brun and Fons Rademakers.               *
 * All rights reserved.                                                  *
 *                                                                       *
 * For the licensing terms see $ROOTSYS/LICENSE.                         *
 * For the list of contributors see $ROOTSYS/README/CREDITS.             *
 *************************************************************************/

/** \class TTreeSQL
\ingroup tree

Implement TTree for a SQL backend
*/

#include <Riostream.h>
#include <vector>
#include <map>
#include <stdlib.h>

#include "TString.h"
#include "TROOT.h"
#include "TSystem.h"
#include "TError.h"
#include "TFile.h"
#include "TTree.h"
#include "TLeaf.h"
#include "TBranch.h"

#include "TSQLRow.h"
#include "TSQLResult.h"
#include "TSQLServer.h"

#include "TTreeSQL.h"
#include "TBasketSQL.h"

ClassImp(TTreeSQL);

////////////////////////////////////////////////////////////////////////////////
/// Constructor with an explicit TSQLServer

TTreeSQL::TTreeSQL(TSQLServer *server, TString DB, const TString& table) :
   TTree(table.Data(), "Database read from table: " + table, 0), fDB(DB),
   fTable(table.Data()),
   fResult(0), fRow(0),
   fServer(server),
   fBranchChecked(kFALSE)
{
   fCurrentEntry = -1;
   fQuery = TString("Select * from " + fTable);
   fEntries = 0;

   if (fServer==0) {
      Error("TTreeSQL","No TSQLServer specified");
      return;
   }
   if (CheckTable(fTable.Data())) {
      Init();
   }
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

TBranch* TTreeSQL::BranchImp(const char *, const char *,
                             TClass *, void *, Int_t ,
                             Int_t )
{
   Fatal("BranchImp","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

TBranch* TTreeSQL::BranchImp(const char *, TClass *,
                             void *, Int_t , Int_t )
{
   Fatal("BranchImp","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

Int_t TTreeSQL::Branch(TCollection *, Int_t,
                       Int_t, const char *)
{
   Fatal("Branch","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

Int_t TTreeSQL::Branch(TList *, Int_t, Int_t)
{
   Fatal("Branch","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

Int_t TTreeSQL::Branch(const char *, Int_t ,
                       Int_t)
{
   Fatal("Branch","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

TBranch* TTreeSQL::Bronch(const char *, const char *, void *,
                          Int_t, Int_t)
{
   Fatal("Bronc","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

TBranch* TTreeSQL::BranchOld(const char *, const char *,
                             void *, Int_t, Int_t)
{
   Fatal("BranchOld","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Not implemented yet

TBranch *TTreeSQL::Branch(const char *, const char *, void *,
                          Int_t, Int_t)
{
   Fatal("Branch","Not implemented yet");
   return 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Create a branch

TBranch * TTreeSQL::Branch(const char *name, void *address,
                           const char *leaflist, Int_t bufsize)
{
   Int_t nb = fBranches.GetEntriesFast();
   TBranch *branch;
   TString brName;

   for (int i=0;i<nb;i++) {
      branch = (TBranch*)fBranches.UncheckedAt(i);
      brName = branch->GetName();
      if (brName.CompareTo(name) == 0) {
         // Now if the branch exists in db, root gives a warning and exit
         // Dealing with duplicate branch has been done, but not tested yet.
         // So if you want to allow duplicate branch, just comment Fatal() line and uncomment commented
         // below Fatal() line

         Fatal("Branch()", "Duplicate branch!!!");

         /* Commented. If uncommented, should comment Fatal line.
         // this is a duplicate branch. So reset data structure memory address and return.
         branch->SetAddress(address);
         return branch;
         */
      }
   }
   return TTree::Branch(name, address, leaflist, bufsize);
}

////////////////////////////////////////////////////////////////////////////////
/// Check if the basket is properly setup

void TTreeSQL::CheckBasket(TBranch *branch)
{
   TBasketSQL* basket = (TBasketSQL *)branch->GetBasket(0);

   if (basket==0) {
      basket = (TBasketSQL*)CreateBasket(branch);
      if (basket==0) return;
      //++(branch->fNBaskets);
      branch->GetListOfBaskets()->AddAtAndExpand(basket,0);
   }
   TBuffer * buffer = basket->GetBufferRef();

   if(buffer == 0){
      std::vector<Int_t> *columns = GetColumnIndice(branch);
      if (columns) basket->CreateBuffer(branch->GetName(),"A", columns, branch, &fResult);
   }

   Int_t nb = branch->GetListOfBranches()->GetEntriesFast();
   for (int i=0;i<nb;i++) {
      TBranch * subbranch = (TBranch*)branch->GetListOfBranches()->UncheckedAt(i);
      if(subbranch) CheckBasket(subbranch);
   }
}

////////////////////////////////////////////////////////////////////////////////
/// Check if the table has a column corresponding the branch
/// and that the resultset are properly setup

Bool_t TTreeSQL::CheckBranch(TBranch * tb)
{
   if (fServer==0) {
      return kFALSE;
   }
   TString leafName;
   TLeaf *leaf;
   Int_t nl;
   TString str = "";
   TString typeName = "";

   if (!tb) return kFALSE;

   TBasketSQL *basket = (TBasketSQL *)tb->GetBasket(0);
   if (!basket) return kFALSE;

   TSQLResult *rs = basket->GetResultSet();
   if (!rs) {
      Error("CheckBranch","%s has basket but no resultset yet",tb->GetName());
      return kFALSE;
   }

   nl = tb->GetNleaves();

   for(int j=0;j<nl;j++) {
      leaf = (TLeaf*)tb->GetListOfLeaves()->UncheckedAt(j);
      typeName = leaf->GetTypeName();
      typeName = ConvertTypeName(leaf->GetTypeName());
      leafName = leaf->GetName();
      str = "";
      str = tb->GetName();
      str += "__";
      str += leafName;

      for (int i=0; i< rs->GetFieldCount(); ++i) {
         if (str.CompareTo(rs->GetFieldName(i),TString::kIgnoreCase) == 0) return kTRUE;
      }
      // We assume that if ONE of the leaf is in the table, then ALL the leaf are in
      // the table.
      // TODO: this assumption is harmful if user changes branch structure while keep its name
      CreateBranch(str, typeName);
   }
   return kFALSE;
}

////////////////////////////////////////////////////////////////////////////////
/// Check the table exist in the database

Bool_t TTreeSQL::CheckTable(const TString &table) const
{
   if (fServer==0) return kFALSE;
   TSQLResult * tables = fServer->GetTables(fDB.Data(),table);
   if (!tables) return kFALSE;
   TSQLRow * row = 0;
   while( (row = tables->Next()) ) {
      if(table.CompareTo(row->GetField(0),TString::kIgnoreCase)==0){
         return kTRUE;
      }
   }
   // The table is a not a permanent table, let's see if it is a 'temporary' table
   Int_t before = gErrorIgnoreLevel;
   gErrorIgnoreLevel = kFatal;
   TSQLResult *res = fServer->GetColumns(fDB.Data(),table);
   if (res) {
      delete res;
      return kTRUE;
   }
   gErrorIgnoreLevel = before;

   return kFALSE;
}

////////////////////////////////////////////////////////////////////////////////
/// Convert from ROOT typename to SQL typename

TString TTreeSQL::ConvertTypeName(const TString& typeName )
{
   TString tn = "";

   if(typeName == "Char_t"){
      tn = "TEXT";
   }
   else if(typeName == "Int_t") {
      tn = "INTEGER";
   }
   else if(typeName == "Short_t") {
      tn = "SMALLINT";
   }
   else if( typeName == "UShort_t") {
      tn = "SMALLINT UNSIGNED";
   }
   else if(typeName == "Float_t"){
      tn = "FLOAT";
   }
   else if(typeName == "Float16_t"){
      tn = "FLOAT";
   }
   else if(typeName == "Double_t"){
      tn = "DOUBLE";
   }
   else if(typeName == "Double32_t"){
      tn = "FLOAT";
   }
   else if(typeName == "UInt_t") {
      tn = "INT UNSIGNED";
   }
   else if( typeName == "Long_t") {
      tn = "INTEGER";
   }
   else if( typeName == "ULong_t") {
      tn = "INTEGER UNSIGNED";
   }
   else if( typeName == "Long64_t") {
      tn = "BIGINT";
   }
   else if( typeName == "ULong64_t") {
      tn = "BIGINT UNSIGNED";
   }
   else if( typeName == "Bool_t") {
      tn = "BOOL";
   }
   else {
      Error("ConvertTypeName","TypeName (%s) not found",typeName.Data());
      return "";
   }

   return tn;
}

////////////////////////////////////////////////////////////////////////////////
/// Create a TBasketSQL

TBasket * TTreeSQL::CreateBasket(TBranch * tb)
{
   if (fServer==0) {
      Error("CreateBasket","No TSQLServer specified");
      return 0;
   }
   std::vector<Int_t> *columnVec = GetColumnIndice(tb);
   if (columnVec) {
      return new TBasketSQL(tb->GetName(), tb->GetName(), tb,
                            &fResult, &fInsertQuery, columnVec, &fRow);
   } else {
      return 0;
   }
}

////////////////////////////////////////////////////////////////////////////////
/// Create the column(s) in the database that correspond to the branch/

void TTreeSQL::CreateBranch(const TString &branchName, const TString &typeName)
{
   if (fServer==0) {
      Error("CreateBranch","No TSQLServer specified");
      return;
   }
   TString alterSQL = "";
   alterSQL = "";
   alterSQL = "ALTER TABLE ";
   alterSQL += fTable.Data();
   alterSQL += " ADD ";
   alterSQL += branchName.Data();;
   alterSQL += " ";
   alterSQL += typeName;
   alterSQL += " ";

   fServer->Query(alterSQL);
}

////////////////////////////////////////////////////////////////////////////////
/// determine leaf description string

TString TTreeSQL::CreateBranches(TSQLResult * rs)
{
   if(!rs) return "";

   Int_t rows;
   TString type;
   TString res;
   TString branchName;
   TString leafName;
   Int_t prec=0;
   TBranch * br = 0;
   rows = rs->GetRowCount();
   TString decl;
   TString prevBranch;

   for( int i=0; i < rows; ++i ) {
      TSQLRow * row = rs->Next();
      if (!row) continue;
      type = row->GetField(1);
      Int_t index = type.First('(');
      if(index>0){
         prec = atoi(type(index+1,type.First(')')-1).Data());
         type = type(0,index);
      }
      branchName = row->GetField(0);
      Int_t pos;
      if ((pos=branchName.Index("__"))!=kNPOS) {
         leafName = branchName(pos+2,branchName.Length());
         branchName.Remove(pos);
      } else {
         leafName = branchName;
      }
      if (prevBranch.Length()) {
         if (prevBranch != branchName) {
            // new branch let's flush.
            if (decl.Length()) decl.Remove(decl.Length()-1);
            br = TTree::Branch(prevBranch,0,decl);
            br->ResetAddress();

            (br->GetBasketEntry())[0] = 0;
            (br->GetBasketEntry())[1] = fEntries;

            br->SetEntries(fEntries);

            //++(br->fNBaskets);
            br->GetListOfBaskets()->AddAtAndExpand(CreateBasket(br),0);

            prevBranch = branchName;
            decl = "";
         }
      } else {
         prevBranch = branchName;
      }

      if(type.CompareTo("varchar",TString::kIgnoreCase)==0 || type.CompareTo("varchar2",TString::kIgnoreCase)==0 || type.CompareTo("char",TString::kIgnoreCase)==0 ) {
         char siz[6];
         snprintf(siz,6,"[%d]",prec);
         decl.Append( leafName+siz+"/C:" );
      }
      else if(type.CompareTo("int",TString::kIgnoreCase)==0){
         decl.Append( leafName+"/I:" );
      }
      else if( type.CompareTo("date",TString::kIgnoreCase)==0 ||
               type.CompareTo("time",TString::kIgnoreCase)==0 ||
               type.CompareTo("timestamp",TString::kIgnoreCase)==0 ) {
         decl.Append( leafName+"/I:" );
      }
      else if(type.CompareTo("bit",TString::kIgnoreCase)==0 ||
              type.CompareTo("tinyint",TString::kIgnoreCase)==0 ||
              type.CompareTo("smallint",TString::kIgnoreCase)==0 ) {
         decl.Append( leafName+"/i:" );
      }
      else if(type.CompareTo("real",TString::kIgnoreCase)==0 || type.CompareTo("longvarchar",TString::kIgnoreCase)==0 || type.CompareTo("longvarbinary",TString::kIgnoreCase)==0 || type.CompareTo("varbinary",TString::kIgnoreCase)==0 ){
         decl.Append( leafName+"/S:" );
      }

      //   case kLONGVARCHAR: // not resolved yet how to handle
      // case kLONGVARBINARY:
      //case kVARBINARY:
      //  break;
      else /*if(type.CompareTo("bigint",TString::kIgnoreCase)==0 || type.CompareTo("decimal",TString::kIgnoreCase)==0 || type.CompareTo("numeric",TString::kIgnoreCase)==0 || type.CompareTo("double",TString::kIgnoreCase)==0 ||
      type.CompareTo("float",TString::kIgnoreCase)==0 )*/{

         decl.Append( leafName+"/F:" );
      }

   }

   // new branch let's flush.
   if (decl.Length()) decl.Remove(decl.Length()-1);
   if (prevBranch.Length()) {
      br = TTree::Branch(prevBranch,0,decl);
      br->ResetAddress();

      (br->GetBasketEntry())[0] = 0;
      (br->GetBasketEntry())[1] = fEntries;
      br->SetEntries(fEntries);
      br->GetListOfBaskets()->AddAtAndExpand(CreateBasket(br),0);
   }

   if(!res.IsNull()) res.Resize(res.Length()-1);   // cut off last ":"
   return res;
}

////////////////////////////////////////////////////////////////////////////////
/// Create the database table corresponding to this TTree.

Bool_t TTreeSQL::CreateTable(const TString &table)
{
   if (fServer==0) {
      Error("CreateTable","No TSQLServer specified");
      return false;
   }
   Int_t i, j;
   TString branchName, leafName, typeName;
   TString createSQL, alterSQL, str;
   Int_t nb = fBranches.GetEntriesFast();
   Int_t nl = 0;

   TBranch *branch;
   TLeaf *leaf;

   for (i=0;i<nb;i++) {
      branch = (TBranch*)fBranches.UncheckedAt(i);
      branchName = branch->GetName();
      nl = branch->GetNleaves();
      for(j=0;j<nl;j++) {
         leaf = (TLeaf*)branch->GetListOfLeaves()->UncheckedAt(j);
         leafName = leaf->GetName();
         typeName = ConvertTypeName(leaf->GetTypeName());
         // length = leaf->GetLenStatic();

         if(i == 0 && j == 0) {
            createSQL = "";
            createSQL += "CREATE TABLE ";
            createSQL += table;
            createSQL += " (";
            createSQL += branchName;
            createSQL += "__";
            createSQL += leafName;
            createSQL += " ";
            createSQL += typeName;
            createSQL += " ";
            createSQL += ")";

            TSQLResult *sres = fServer->Query(createSQL.Data());
            if (!sres) {
               Error("CreateTable","May have failed");
               return false;
            }
         }
         else {
            str = "";
            str = branchName;
            str += "__";
            str += leafName;
            CreateBranch(str, typeName);
         } //else
      }  // inner for loop
   } // outer for loop
   // retrieve table to initialize fResult
   delete fResult;
   fResult = fServer->Query(fQuery.Data());
   return (fResult!=0);
}

////////////////////////////////////////////////////////////////////////////////
/// Initializeation routine

void TTreeSQL::Init()
{
   fCurrentEntry = -1;

   GetEntries();

   delete fResult;
   fResult = fServer->Query(fQuery.Data());
   if(!fResult) return;

   CreateBranches(fServer->GetColumns(fDB,fTable));
}

////////////////////////////////////////////////////////////////////////////////
/// Copy the information from the user object to the TTree

Int_t TTreeSQL::Fill()
{
   Int_t nb = fBranches.GetEntriesFast();
   TString typeName;
   TBranch *branch;

   if (fServer==0) return 0;

   if(!CheckTable(fTable.Data())) {
      if (!CreateTable(fTable.Data())) {
         return -1;
      }
   }

   PrepEntry(fEntries);

   for (int i=0;i<nb;i++) {
      branch = (TBranch*)fBranches.UncheckedAt(i);
      CheckBasket(branch);
   }

   if (!fBranchChecked) {
      for(int i=0;i<nb;i++) {
         branch = (TBranch*)fBranches.UncheckedAt(i);
         if (!CheckBranch(branch)) {
            Error("Fill","CheckBranch for %s failed",branch->GetName());
         }
      }
      fBranchChecked = kTRUE;
   }
   ResetQuery();

   TTree::Fill();

   if (fInsertQuery[fInsertQuery.Length()-1]!='(') {
      fInsertQuery.Remove(fInsertQuery.Length()-1);
      fInsertQuery += ")";
      TSQLResult *res = fServer?fServer->Query(fInsertQuery):0;

      if (res) {
         return res->GetRowCount();
      }
   }
   return -1;
}

////////////////////////////////////////////////////////////////////////////////
/// Return a vector of columns index corresponding to the
/// current SQL table and the branch given as argument
/// Returns 0 if no columns indices is found
/// Otherwise returns a pointer to a vector to be deleted by the caller

std::vector<Int_t> *TTreeSQL::GetColumnIndice(TBranch *branch)
{
   if (!CheckTable(fTable)) return 0;

   std::vector<Int_t> *columns = new std::vector<Int_t>;

   Int_t nl = branch->GetNleaves();

   std::vector<TString> names;

   TSQLResult *rs = fServer->GetColumns(fDB,fTable);
   if (rs==0) { delete columns; return 0; }
   Int_t rows = rs->GetRowCount();

   std::pair<TString,Int_t> value;

   for (Int_t i=0;i<rows;++i) {
      TSQLRow *row = rs->Next();
      names.push_back( row->GetField(0) );
      delete row;
   }
   delete rs;

   for(int j=0;j<nl;j++) {

      Int_t col = -1;
      TLeaf *leaf = (TLeaf*)branch->GetListOfLeaves()->UncheckedAt(j);
      TString leafName = leaf->GetName();
      TString str;

      str = "";
      str = branch->GetName();
      str += "__";
      str += leafName;
      for (Int_t i=0;i<rows;++i) {
         if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {
            col = i;
            break;
         }
      }
      if (col<0) {
         str = leafName;
         for (Int_t i=0;i<rows;++i) {
            if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {
               col = i;
               break;
            }
         }
      }
      if(col>=0){
         columns->push_back(col);
      } else Error("GetColumnIndice","Error finding column %d %s",j,str.Data());
   }
   if (columns->empty()) {
      delete columns; return 0;
   } else
      return columns;
}

////////////////////////////////////////////////////////////////////////////////
/// Get the number of rows in the database

Long64_t  TTreeSQL::GetEntries() const
{
   if (fServer==0) return GetEntriesFast();
   if (!CheckTable(fTable.Data())) return 0;

   TTreeSQL* thisvar = const_cast<TTreeSQL*>(this);

   // What if the user already started to call GetEntry
   // What about the initial value of fEntries is it really 0?

   TString counting = "select count(*) from " + fTable;
   TSQLResult *count = fServer->Query(counting);

   if (count==0) {
      thisvar->fEntries = 0;
   } else {
      TSQLRow * row = count->Next();
      if (row) {
         TString val = row->GetField(0);
         Long_t ret;
         sscanf(val.Data(), "%ld",&(ret) );
         thisvar->fEntries = ret;
      } else {
         thisvar->fEntries = 0;
      }
   }
   return fEntries;
}

////////////////////////////////////////////////////////////////////////////////
/// Return the number of entries as of the last check.
/// Use GetEntries for a more accurate count.

Long64_t  TTreeSQL::GetEntriesFast()    const
{
   return fEntries;
}

////////////////////////////////////////////////////////////////////////////////
/// Load the data for the entry from the database.

Int_t TTreeSQL::GetEntry(Long64_t entry, Int_t getall)
{
   if (PrepEntry(entry)>=0) return TTree::GetEntry(entry,getall);
   else return -1;
}

////////////////////////////////////////////////////////////////////////////////
/// Setup the tree to the load the specified entry.

Long64_t TTreeSQL::LoadTree(Long64_t entry)
{
   fReadEntry = entry;
   return PrepEntry(entry);
}

////////////////////////////////////////////////////////////////////////////////
/// Make sure the server and result set are setup for the requested entry

Long64_t TTreeSQL::PrepEntry(Long64_t entry)
{
   if (entry < 0 || entry >= fEntries || fServer==0) return 0;
   fReadEntry = entry;

   if(entry == fCurrentEntry) return entry;

   if(entry < fCurrentEntry || fResult==0){
      delete fResult;
      fResult = fServer->Query(fQuery.Data());
      fCurrentEntry = -1;
   }

   Bool_t reset = false;
   while ( fResult && fCurrentEntry < entry ) {
      ++fCurrentEntry;
      delete fRow;
      fRow = fResult->Next();
      if (fRow==0 && !reset) {
         delete fResult;
         fResult = fServer->Query(fQuery.Data());
         fCurrentEntry = -1;
         reset = true;
      }
   }
   if (fRow==0) return -1;
   return entry;
}

//______________________________________________________________________________
// void TTreeSQL::LoadNumberEntries()
// {
//    R__ASSERT(0);

//    fResult =    fServer->Query(fQuery.Data());
//    fEntries=0;

//    while(fResult->Next()){
//       fEntries++;
//    }
//    fResult =    fServer->Query(fQuery.Data());
// }

////////////////////////////////////////////////////////////////////////////////
///  Refresh contents of this Tree and its branches from the current
///  Tree status in the database
///  One can call this function in case the Tree on its file is being
///  updated by another process

void TTreeSQL::Refresh()
{
   // Note : something to be done?
   GetEntries(); // Re-load the number of entries
   fCurrentEntry = -1;
   delete fResult; fResult = 0;
   delete fRow; fRow = 0;
}

////////////////////////////////////////////////////////////////////////////////
/// Reset the internal query

void TTreeSQL::ResetQuery()
{
   fInsertQuery = "INSERT INTO " + fTable + " VALUES (";
}