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