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