Re: [ROOT] MySql and Ntuple

From: Christian Holm Christensen (cholm@hehi03.nbi.dk)
Date: Fri Jun 22 2001 - 21:05:40 MEST


On Fri, 22 Jun 2001 18:48:31 +0200
Fabrice Podlyski <podlyski@clermont.in2p3.fr> wrote
concerning ": [ROOT] MySql and Ntuple":
> Hello,
> 
> I try to put the results of the query in an Ntuple
> but i didn't succeed : the method GetField (applied
> on a TSQLRow object) return a pointer of type character!

This is the standard way MySQL return results in the C API, and
therefore ROOT (naturally) does so too.  Remember, MySQL is a
relational database, not on object database. 
 
> must i use some c/c++ specific functions ? (to manipulate the string)
> I search for an example but i don't find it !

Suppose you had the table Track in your database: 

       Table Track
  field     type         other 
  id        INT NOT NULL UNIQUE
  vertexX   FLOAT      
  vertexY   FLOAT      
  vertexZ   FLOAT      
  vertexT   FLOAT      
  momentumX FLOAT      
  momentumY FLOAT      
  momentumZ FLOAT      
  momentumE FLOAT      
  
Then a nice way of representing that in ROOT would be to make the
class DbTrack, as 

  class DbTrack : public TObject { 
  private: 
    Float_t   fVertexX;      
    Float_t   fVertexY;      
    Float_t   fVertexZ;      
    Float_t   fVertexT;      
    Float_t fMomentumX;      
    Float_t fMomentumY;      
    Float_t fMomentumZ;      
    Float_t fMomentumE;      
  public: 
    DbTrack(TSQLRow* row) { 
      SetUniqueID(strtol(row->GetField(0), NULL, 0));
      fVertexX    = strtod(row->GetField(1), NULL);
      fVertexY    = strtod(row->GetField(2), NULL);   
      fVertexZ    = strtod(row->GetField(3), NULL);
      fVertexT    = strtod(row->GetField(4), NULL);
      fMomentumX  = strtod(row->GetField(5), NULL);
      fMomentumY  = strtod(row->GetField(6), NULL);
      fMomentumZ  = strtod(row->GetField(7), NULL);      
      fMomentumE  = strtod(row->GetField(8), NULL);
    }
    TString* Insert() { 
      TString* q = new TString("INSERT INTO Track "); 
      *q += "(id, vertexX, vertexY, vertexZ, vertexT, "; 
      *q += "momentumX, momentumY, momentumZ, momentumE) VALUES";
      *q += Form("(%d, %f, %f, %f, %f, %f, %f, %f, %f)", 
		 GetUniqueID(), fVertexX, fVertexY, fVertexZ, fVertexT, 
                 fMomentumX, fMomentumY, fMomentumZ, fMomentumE);
      return q; 
    }              
    ...
  };
     
Then you can so something like: 

   TSQLServer* serv = ...; 
   TSQLResult* res  = serv->Query("SELECT * FROM Track WHERE vertexT < 10");
   TSQLRow*    row; 

   TClonesArray* tracks = new TClonesArray("DbTrack"); 
   Int_t noTracks = 0;
   while ((row = res->Next())) 
     new((*array)[noTracks++]) DbTrack(row); 

to read out the tracks, and you can do 

   TClonesArray* tracks = new TClonesArray("DbTrack"); 
   ...    // Fill array 

   TSQLServer* serv = ...; 

   TIter next(tracks); 
   DbTrack* track = 0; 
   while ((track = (DbTrack*)next())) { 
     TString* q = track->Insert(); 
     serv->Query(q->Data()); 
     delete q; 
   }

to insert tracks into the database.  

Obviously, you should not use TNtuple for this.  Instead use a TTree
with a branch holding the TClonesArray of DbTracks: 

  TClonesArray* tracks = new TClonesArray("DbTrack"); 
  TTree*        tree   = new TTree("T", "A Tree"); 
  tree->Branch("tracks", &tracks); 

If you want to do JOIN views on the database tables, I suggest you
make a class for the JOINS you do often. 

Oh, I just saw that Fons beat me to it, but I hope I provided you with
a few useful hints. 

That said, you can in many ways obtain the same result by using ROOT
files with TTree's in them, and possible serve them over TCP/IP using
rootd.  To this end, it would be really nice if TTree::Query
understood (a subset) of SQL, so that you could do 

  TTree* tree = (TTree*)remoteFile->Get("T"); 
  TSQLResult* res = tree->Query("SELECT * FROM tracks WHERE fVertexT < 10");

As far as I can tell, there's also a problem with defining
many-to-many relations in between different branches of a TTree.
Suppose you had 

  TClonesArray* hits   = new TClonesArray("Hits");
  TClonesArray* tracks = new TClonesArray("Tracks");
  TTree* tree = new TTree("T", "T"); 
  tree->Branch("hits", &hits); 
  tree->Branch("tracks", &tracks); 

with 

  class Hit : public TObject {
  private: 
    TClonesArray* fTracks;  // Tracks this hit contributes to 
    ...
  };

  class Track : public TObject {
  private: 
    TClonesArray* fHits;  // Hits that make up this track
    ...
  };

If you write something like this to disk, you get _different_ Track
objects on the hits branch than on the tracks branch, and vice versa.
I might be wrong though (I do hope I am). 

The only real solution to this, if you insist on using TTrees, is to
have a third branch to define the many-to-many relation ship, like in
a traditional RDB - which sort of defies the purpose of persistent
object store.  

The other alternative, is to put everything in containers and just
write it serially to disk.  Then, however, you miss out on the
powerfull query functionallity of TTrees.  What we need, is for
TCollection/TDirectory to be able to queries on class members!
Perhaps a speciallised TCollection (say TSQLCollection) could do this,
using the CINT ERTTI?  Also, such a query should be able to span over
many files, since 2GB may not be enough - This really sound like TTree
and TChain, but alas, it isn't. 

Now, I'm not much of a fan of Objectivity, but it does seem that they
have solved the problem in the later way, _and_ provided the query
functionality.  It would be nice for ROOT to say - "ha, we can do that
as well!", especially since it may persuade the disbelievers to use
ROOT for thier major database storage of thier future mega project. 

If I've overlooked something in the ROOT, I do apologise for pestering
you with this comments - and also, could you please tell me how to
do it?  Thanks. 

Yours, 

Christian  -----------------------------------------------------------
Holm Christensen                             Phone:  (+45) 35 35 96 91 
  Sankt Hansgade 23, 1. th.                  Office: (+45) 353  25 305 
  DK-2200 Copenhagen N                       Web:    www.nbi.dk/~cholm    
  Denmark                                    Email:       cholm@nbi.dk



This archive was generated by hypermail 2b29 : Tue Jan 01 2002 - 17:50:50 MET