Changeset 15419 for trunk/Mars
- Timestamp:
- 04/24/13 19:11:15 (12 years ago)
- Location:
- trunk/Mars/msql
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/Mars/msql/MTreeSQL.cc
r15417 r15419 19 19 20 20 #include <Riostream.h> 21 #include <vector>22 21 #include <map> 23 22 #include <stdlib.h> … … 31 30 #include "TLeaf.h" 32 31 #include "TBranch.h" 32 #include "TPRegexp.h" 33 33 34 34 #include "TSQLRow.h" … … 45 45 //______________________________________________________________________________ 46 46 MTreeSQL::MTreeSQL(TSQLServer *server, TString DB, const TString& table, const TString &addon) : 47 TTree(table.Data(), "Database read from table: " + table, 0), fDB(DB), 48 fTable(table.Data()), 49 fResult(0), fRow(0), 50 fServer(server), 51 fBranchChecked(kFALSE) 52 { 53 // Constructor with an explicit TSQLServer 54 55 fCurrentEntry = -1; 56 fQuery = TString("Select * from " + fTable + " " + addon); 57 fEntries = 0; 58 59 if (fServer==0) { 60 Error("MTreeSQL","No TSQLServer specified"); 61 return; 62 } 63 if (CheckTable(fTable.Data())) { 64 Init(); 65 } 47 TTree(table.Data(), "Database read from table: " + table, 0), 48 fCurrentEntry(-1), fDB(DB), fQuery(table+" "+addon),fResult(0), fRow(0), 49 fServer(server) 50 { 51 fEntries = 0; 52 53 if (fServer==0) 54 { 55 Error("MTreeSQL","No TSQLServer specified"); 56 return; 57 } 58 59 fTables.push_back(table); 60 61 // Constructor with an explicit TSQLServer 62 TPRegexp reg("LEFT +JOIN +([a-zA-Z0-9_.]+)"); 63 64 Int_t p = -1; 65 while (p<addon.Length()) 66 { 67 TArrayI pos; 68 if (reg.Match(addon, "ig", ++p, 100, &pos)!=2) 69 continue; 70 71 if (pos[2] >= 0 && pos[3] >= 0) 72 { 73 const TString subStr = addon(pos[2], pos[3]-pos[2]); 74 fTables.push_back(subStr); 75 } 76 77 p = pos[3]; 78 } 79 80 for (auto it=fTables.begin(); it!=fTables.end(); it++) 81 { 82 cout << *it << endl; 83 if (!CheckTable(*it)) 84 return; 85 } 86 87 Init(); 66 88 } 67 89 … … 166 188 167 189 Fatal("Branch()", "Duplicate branch!!!"); 168 169 /* Commented. If uncommented, should comment Fatal line.170 // this is a duplicate branch. So reset data structure memory address and return.171 branch->SetAddress(address);172 return branch;173 */174 190 } 175 191 } 176 192 return TTree::Branch(name, address, leaflist, bufsize); 177 }178 179 //______________________________________________________________________________180 void MTreeSQL::CheckBasket(TBranch *branch)181 {182 // Check if the basket is properly setup183 184 MBasketSQL* basket = (MBasketSQL *)branch->GetBasket(0);185 186 if (basket==0) {187 basket = (MBasketSQL*)CreateBasket(branch);188 if (basket==0) return;189 //++(branch->fNBaskets);190 branch->GetListOfBaskets()->AddAtAndExpand(basket,0);191 }192 TBuffer * buffer = basket->GetBufferRef();193 194 if(buffer == 0){195 vector<Int_t> *columns = GetColumnIndice(branch);196 if (columns) basket->CreateBuffer(branch->GetName(),"A", columns, branch, &fResult);197 }198 199 Int_t nb = branch->GetListOfBranches()->GetEntriesFast();200 for (int i=0;i<nb;i++) {201 TBranch * subbranch = (TBranch*)branch->GetListOfBranches()->UncheckedAt(i);202 if(subbranch) CheckBasket(subbranch);203 }204 }205 206 //______________________________________________________________________________207 Bool_t MTreeSQL::CheckBranch(TBranch * tb)208 {209 // Check if the table has a column corresponding the branch210 // and that the resultset are properly setup211 212 if (fServer==0) {213 return kFALSE;214 }215 TString leafName;216 TLeaf *leaf;217 Int_t nl;218 TString str = "";219 TString typeName = "";220 221 if (!tb) return kFALSE;222 223 MBasketSQL *basket = (MBasketSQL *)tb->GetBasket(0);224 if (!basket) return kFALSE;225 226 TSQLResult *rs = basket->GetResultSet();227 if (!rs) {228 Error("CheckBranch","%s has basket but no resultset yet",tb->GetName());229 return kFALSE;230 }231 232 nl = tb->GetNleaves();233 234 for(int j=0;j<nl;j++) {235 leaf = (TLeaf*)tb->GetListOfLeaves()->UncheckedAt(j);236 typeName = leaf->GetTypeName();237 typeName = ConvertTypeName(leaf->GetTypeName());238 leafName = leaf->GetName();239 str = "";240 str = tb->GetName();241 str += "__";242 str += leafName;243 244 for (int i=0; i< rs->GetFieldCount(); ++i) {245 if (str.CompareTo(rs->GetFieldName(i),TString::kIgnoreCase) == 0) return kTRUE;246 }247 // We assume that if ONE of the leaf is in the table, then ALL the leaf are in248 // the table.249 // TODO: this assumption is harmful if user changes branch structure while keep its name250 CreateBranch(str, typeName);251 }252 return kFALSE;253 193 } 254 194 … … 281 221 282 222 //______________________________________________________________________________ 283 TString MTreeSQL::ConvertTypeName(const TString& typeName )284 {285 // Convert from ROOT typename to SQL typename286 287 TString tn = "";288 289 if(typeName == "Char_t"){290 tn = "TEXT";291 }292 else if(typeName == "Int_t") {293 tn = "INTEGER";294 }295 else if(typeName == "Short_t") {296 tn = "SMALLINT";297 }298 else if( typeName == "UShort_t") {299 tn = "SMALLINT UNSIGNED";300 }301 else if(typeName == "Float_t"){302 tn = "FLOAT";303 }304 else if(typeName == "Float16_t"){305 tn = "FLOAT";306 }307 else if(typeName == "Double_t"){308 tn = "DOUBLE";309 }310 else if(typeName == "Double32_t"){311 tn = "FLOAT";312 }313 else if(typeName == "UInt_t") {314 tn = "INT UNSIGNED";315 }316 else if( typeName == "Long_t") {317 tn = "INTEGER";318 }319 else if( typeName == "ULong_t") {320 tn = "INTEGER UNSIGNED";321 }322 else if( typeName == "Long64_t") {323 tn = "BIGINT";324 }325 else if( typeName == "ULong64_t") {326 tn = "BIGINT UNSIGNED";327 }328 else if( typeName == "Bool_t") {329 tn = "BOOL";330 }331 else {332 Error("ConvertTypeName","TypeName (%s) not found",typeName.Data());333 return "";334 }335 336 return tn;337 }338 339 //______________________________________________________________________________340 223 TBasket * MTreeSQL::CreateBasket(TBranch * tb) 341 224 { … … 346 229 return 0; 347 230 } 231 232 static TString dummy; 233 348 234 vector<Int_t> *columnVec = GetColumnIndice(tb); 349 235 if (columnVec) { 350 236 return new MBasketSQL(tb->GetName(), tb->GetName(), tb, 351 &fResult, & fInsertQuery, columnVec, &fRow);237 &fResult, &dummy, columnVec, &fRow); 352 238 } else { 353 239 return 0; … … 355 241 } 356 242 357 //______________________________________________________________________________358 void MTreeSQL::CreateBranch(const TString &branchName, const TString &typeName)359 {360 // Create the column(s) in the database that correspond to the branch/361 362 if (fServer==0) {363 Error("CreateBranch","No TSQLServer specified");364 return;365 }366 TString alterSQL = "";367 alterSQL = "";368 alterSQL = "ALTER TABLE ";369 alterSQL += fTable.Data();370 alterSQL += " ADD ";371 alterSQL += branchName.Data();;372 alterSQL += " ";373 alterSQL += typeName;374 alterSQL += " ";375 376 fServer->Query(alterSQL);377 }378 379 243 //_________________________________________________________________________ 380 TString MTreeSQL::CreateBranches(TSQLResult * rs )244 TString MTreeSQL::CreateBranches(TSQLResult * rs, const TString &table) 381 245 { 382 246 // determine leaf description string … … 404 268 type = type(0,index); 405 269 } 406 branchName = row->GetField(0); 270 branchName = table+"."+row->GetField(0); 271 //cout << table << "." << branchName << endl; 407 272 Int_t pos; 408 273 if ((pos=branchName.Index("__"))!=kNPOS) { … … 460 325 //case kVARBINARY: 461 326 // break; 462 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 || 463 type.CompareTo("float",TString::kIgnoreCase)==0 )*/{ 327 else 328 //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 || 329 //type.CompareTo("float",TString::kIgnoreCase)==0 ) 330 { 464 331 465 332 decl.Append( leafName+"/F:" ); … … 485 352 486 353 //______________________________________________________________________________ 487 Bool_t MTreeSQL::CreateTable(const TString &table) 488 { 489 // Create the database table corresponding to this TTree. 490 491 if (fServer==0) { 492 Error("CreateTable","No TSQLServer specified"); 493 return false; 494 } 495 Int_t i, j; 496 TString branchName, leafName, typeName; 497 TString createSQL, alterSQL, str; 498 Int_t nb = fBranches.GetEntriesFast(); 499 Int_t nl = 0; 500 501 TBranch *branch; 502 TLeaf *leaf; 503 504 for (i=0;i<nb;i++) { 505 branch = (TBranch*)fBranches.UncheckedAt(i); 506 branchName = branch->GetName(); 507 nl = branch->GetNleaves(); 508 for(j=0;j<nl;j++) { 509 leaf = (TLeaf*)branch->GetListOfLeaves()->UncheckedAt(j); 510 leafName = leaf->GetName(); 511 typeName = ConvertTypeName(leaf->GetTypeName()); 512 // length = leaf->GetLenStatic(); 513 514 if(i == 0 && j == 0) { 515 createSQL = ""; 516 createSQL += "CREATE TABLE "; 517 createSQL += table; 518 createSQL += " ("; 519 createSQL += branchName; 520 createSQL += "__"; 521 createSQL += leafName; 522 createSQL += " "; 523 createSQL += typeName; 524 createSQL += " "; 525 createSQL += ")"; 526 527 TSQLResult *sres = fServer->Query(createSQL.Data()); 528 if (!sres) { 529 Error("CreateTable","May have failed"); 530 return false; 531 } 532 } 533 else { 534 str = ""; 535 str = branchName; 536 str += "__"; 537 str += leafName; 538 CreateBranch(str, typeName); 539 } //else 540 } // inner for loop 541 } // outer for loop 542 // retrieve table to initialize fResult 354 void MTreeSQL::Init() 355 { 356 // Initializeation routine 357 358 fCurrentEntry = -1; 359 360 GetEntries(); 361 543 362 delete fResult; 544 fResult = fServer->Query(fQuery.Data()); 545 return (fResult!=0); 546 } 547 548 //______________________________________________________________________________ 549 void MTreeSQL::Init() 550 { 551 // Initializeation routine 552 553 fCurrentEntry = -1; 554 555 GetEntries(); 556 557 delete fResult; 558 fResult = fServer->Query(fQuery.Data()); 363 fResult = fServer->Query(("SELECT * FROM "+fQuery).Data()); 559 364 if(!fResult) return; 560 365 561 CreateBranches(fServer->GetColumns(fDB,fTable)); 366 for (auto it=fTables.begin(); it!=fTables.end(); it++) 367 CreateBranches(fServer->GetColumns(fDB, *it), *it); 562 368 } 563 369 … … 565 371 Int_t MTreeSQL::Fill() 566 372 { 567 // Copy the information from the user object to the TTree 568 569 Int_t nb = fBranches.GetEntriesFast(); 570 TString typeName; 571 TBranch *branch; 572 573 if (fServer==0) return 0; 574 575 if(!CheckTable(fTable.Data())) { 576 if (!CreateTable(fTable.Data())) { 577 return -1; 578 } 579 } 580 581 PrepEntry(fEntries); 582 583 for (int i=0;i<nb;i++) { 584 branch = (TBranch*)fBranches.UncheckedAt(i); 585 CheckBasket(branch); 586 } 587 588 if (!fBranchChecked) { 589 for(int i=0;i<nb;i++) { 590 branch = (TBranch*)fBranches.UncheckedAt(i); 591 if (!CheckBranch(branch)) { 592 Error("Fill","CheckBranch for %s failed",branch->GetName()); 593 } 594 } 595 fBranchChecked = kTRUE; 596 } 597 ResetQuery(); 598 599 TTree::Fill(); 600 601 if (fInsertQuery[fInsertQuery.Length()-1]!='(') { 602 fInsertQuery.Remove(fInsertQuery.Length()-1); 603 fInsertQuery += ")"; 604 TSQLResult *res = fServer?fServer->Query(fInsertQuery):0; 605 606 if (res) { 607 return res->GetRowCount(); 608 } 609 } 373 Fatal("Fill","Not implemented yet"); 610 374 return -1; 611 375 } … … 619 383 // Otherwise returns a pointer to a vector to be deleted by the caller 620 384 621 if (!CheckTable(fTable)) return 0;385 //if (!CheckTable(fTable)) return 0; 622 386 623 387 vector<Int_t> *columns = new vector<Int_t>; … … 626 390 627 391 vector<TString> names; 628 629 TSQLResult *rs = fServer->GetColumns(fDB,fTable);630 if (rs==0) { delete columns; return 0; }631 Int_t rows = rs->GetRowCount();632 633 392 pair<TString,Int_t> value; 634 393 635 for (Int_t i=0;i<rows;++i) { 636 TSQLRow *row = rs->Next(); 637 names.push_back( row->GetField(0) ); 638 delete row; 639 } 640 delete rs; 394 for (auto it=fTables.begin(); it!=fTables.end(); it++) 395 { 396 TSQLResult *rs = fServer->GetColumns(fDB, *it); 397 if (rs==0) { delete columns; return 0; } 398 Int_t rows = rs->GetRowCount(); 399 400 for (Int_t i=0;i<rows;++i) { 401 TSQLRow *row = rs->Next(); 402 names.push_back( *it+"."+row->GetField(0) ); 403 delete row; 404 } 405 delete rs; 406 } 641 407 642 408 for(int j=0;j<nl;j++) { … … 651 417 str += "__"; 652 418 str += leafName; 653 for ( Int_t i=0;i<rows;++i) {419 for (UInt_t i=0;i<names.size();++i) { 654 420 if (str.CompareTo(names[i],TString::kIgnoreCase)==0) { 655 421 col = i; … … 659 425 if (col<0) { 660 426 str = leafName; 661 for ( Int_t i=0;i<rows;++i) {662 if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {427 for (UInt_t i=0;i<names.size();++i) { 428 if (str.CompareTo(names[i],TString::kIgnoreCase)==0) { 663 429 col = i; 664 430 break; … … 682 448 683 449 if (fServer==0) return GetEntriesFast(); 684 if (!CheckTable(fTable.Data())) return 0;450 //if (!CheckTable(fTable.Data())) return 0; 685 451 686 452 MTreeSQL* thisvar = const_cast<MTreeSQL*>(this); … … 689 455 // What about the initial value of fEntries is it really 0? 690 456 691 TString counting = "select count(*) from " + fTable;457 const TString counting = "SELECT COUNT(*) FROM " + fQuery; 692 458 TSQLResult *count = fServer->Query(counting); 693 459 … … 747 513 if(entry < fCurrentEntry || fResult==0){ 748 514 delete fResult; 749 fResult = fServer->Query( fQuery.Data());515 fResult = fServer->Query(("SELECT * FROM "+fQuery).Data()); 750 516 fCurrentEntry = -1; 751 517 } … … 758 524 if (fRow==0 && !reset) { 759 525 delete fResult; 760 fResult = fServer->Query( fQuery.Data());526 fResult = fServer->Query(("SELECT * FROM "+fQuery).Data()); 761 527 fCurrentEntry = -1; 762 528 reset = true; … … 766 532 return entry; 767 533 } 768 534 /* 769 535 //______________________________________________________________________________ 770 536 // void MTreeSQL::LoadNumberEntries() … … 780 546 // fResult = fServer->Query(fQuery.Data()); 781 547 // } 782 548 */ 783 549 //______________________________________________________________________________ 784 550 void MTreeSQL::Refresh() … … 795 561 delete fRow; fRow = 0; 796 562 } 797 798 //______________________________________________________________________________799 void MTreeSQL::ResetQuery()800 {801 // Reset the internal query802 803 fInsertQuery = "INSERT INTO " + fTable + " VALUES (";804 }805 806 -
trunk/Mars/msql/MTreeSQL.h
r15417 r15419 39 39 class TSQLServer; 40 40 class TSQLRow; 41 class MBasketSQL;42 41 43 42 class MTreeSQL : public TTree { … … 46 45 Int_t fCurrentEntry; 47 46 TString fDB; 48 TString fInsertQuery;49 47 TString fQuery; 50 TString fTable;51 48 TSQLResult *fResult; 52 49 TSQLRow *fRow; 53 50 TSQLServer *fServer; 54 Bool_t fBranchChecked;51 std::vector<TString> fTables; 55 52 56 void CheckBasket(TBranch * tb);57 Bool_t CheckBranch(TBranch * tb);58 53 Bool_t CheckTable(const TString &table) const; 59 TString CreateBranches(TSQLResult * rs );54 TString CreateBranches(TSQLResult * rs, const TString &table); 60 55 std::vector<Int_t> *GetColumnIndice(TBranch *branch); 61 void Init(); 62 void ResetQuery(); 63 TString ConvertTypeName(const TString& typeName ); 64 virtual void CreateBranch(const TString& branchName,const TString &typeName); 65 Bool_t CreateTable(const TString& table); 66 virtual TBasket *CreateBasket(TBranch * br); 56 void Init(); 57 virtual TBasket *CreateBasket(TBranch * br); 67 58 68 59 virtual TBranch *BranchImp(const char *branchname, const char *classname, TClass *ptrClass, void *addobj, Int_t bufsize, Int_t splitlevel); 69 60 virtual TBranch *BranchImp(const char *branchname, TClass *ptrClass, void *addobj, Int_t bufsize, Int_t splitlevel); 70 61 71 62 public: 72 MTreeSQL(TSQLServer * server, TString DB, const TString& table, const TString &addon );63 MTreeSQL(TSQLServer * server, TString DB, const TString& table, const TString &addon=""); 73 64 74 65 virtual Int_t Branch(TCollection *list, Int_t bufsize=32000, Int_t splitlevel=99, const char *name=""); … … 88 79 virtual Long64_t GetEntries(const char *sel) { return TTree::GetEntries(sel); } 89 80 virtual Long64_t GetEntriesFast()const; 90 TString GetTableName(){ return fTable; }91 81 virtual Long64_t LoadTree(Long64_t entry); 92 82 virtual Long64_t PrepEntry(Long64_t entry);
Note:
See TracChangeset
for help on using the changeset viewer.