Changeset 15419 for trunk/Mars/msql/MTreeSQL.cc

Timestamp:

04/24/13 19:11:15 (11 years ago)

Author:

tbretz

Message:

Removed support for writing to the table; added support for left joins

File:

• trunk/Mars/msql/MTreeSQL.cc (modified) (22 diffs)

trunk/Mars/msql/MTreeSQL.cc

@@ -19 +19 @@
 
 #include <Riostream.h>
-#include <vector>
 #include <map>
 #include <stdlib.h>
@@ -31 +30 @@
 #include "TLeaf.h"
 #include "TBranch.h"
+#include "TPRegexp.h"
 
 #include "TSQLRow.h"
@@ -45 +45 @@
 //______________________________________________________________________________
 MTreeSQL::MTreeSQL(TSQLServer *server, TString DB, const TString& table, const TString &addon) :
-   TTree(table.Data(), "Database read from table: " + table, 0), fDB(DB),
-   fTable(table.Data()),
-   fResult(0), fRow(0),
-   fServer(server),
-   fBranchChecked(kFALSE)
-{
-   // Constructor with an explicit TSQLServer
-
-   fCurrentEntry = -1;
-   fQuery = TString("Select * from " + fTable + " " + addon);
-   fEntries = 0;
-
-   if (fServer==0) {
-      Error("MTreeSQL","No TSQLServer specified");
-      return;
-   }
-   if (CheckTable(fTable.Data())) {
-      Init();
-   }
+    TTree(table.Data(), "Database read from table: " + table, 0),
+    fCurrentEntry(-1), fDB(DB), fQuery(table+" "+addon),fResult(0), fRow(0), 
+    fServer(server)
+{
+    fEntries = 0;
+
+    if (fServer==0)
+    {
+        Error("MTreeSQL","No TSQLServer specified");
+        return;
+    }
+
+    fTables.push_back(table);
+
+    // Constructor with an explicit TSQLServer
+    TPRegexp reg("LEFT +JOIN +([a-zA-Z0-9_.]+)");
+
+    Int_t p = -1;
+    while (p<addon.Length())
+    {
+        TArrayI pos;
+        if (reg.Match(addon, "ig", ++p, 100, &pos)!=2)
+            continue;
+
+        if (pos[2] >= 0 && pos[3] >= 0)
+        {
+            const TString subStr = addon(pos[2], pos[3]-pos[2]);
+            fTables.push_back(subStr);
+        }
+
+        p = pos[3];
+    }
+
+    for (auto it=fTables.begin(); it!=fTables.end(); it++)
+    {
+        cout << *it << endl;
+        if (!CheckTable(*it))
+            return;
+    }
+
+    Init();
 }
 
@@ -166 +188 @@
 
          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);
-}
-
-//______________________________________________________________________________
-void MTreeSQL::CheckBasket(TBranch *branch)
-{
-   // Check if the basket is properly setup
-
-   MBasketSQL* basket = (MBasketSQL *)branch->GetBasket(0);
-
-   if (basket==0) {
-      basket = (MBasketSQL*)CreateBasket(branch);
-      if (basket==0) return;
-      //++(branch->fNBaskets);
-      branch->GetListOfBaskets()->AddAtAndExpand(basket,0);
-   }
-   TBuffer * buffer = basket->GetBufferRef();
-
-   if(buffer == 0){
-      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);
-   }
-}
-
-//______________________________________________________________________________
-Bool_t MTreeSQL::CheckBranch(TBranch * tb)
-{
-   // Check if the table has a column corresponding the branch
-   // and that the resultset are properly setup
-
-   if (fServer==0) {
-      return kFALSE;
-   }
-   TString leafName;
-   TLeaf *leaf;
-   Int_t nl;
-   TString str = "";
-   TString typeName = "";
-
-   if (!tb) return kFALSE;
-
-   MBasketSQL *basket = (MBasketSQL *)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;
 }
 
@@ -281 +221 @@
 
 //______________________________________________________________________________
-TString MTreeSQL::ConvertTypeName(const TString& typeName )
-{
-   // Convert from ROOT typename to SQL 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;
-}
-
-//______________________________________________________________________________
 TBasket * MTreeSQL::CreateBasket(TBranch * tb)
 {
@@ -346 +229 @@
       return 0;
    }
+
+   static TString dummy;
+
    vector<Int_t> *columnVec = GetColumnIndice(tb);
    if (columnVec) {
       return new MBasketSQL(tb->GetName(), tb->GetName(), tb,
-                            &fResult, &fInsertQuery, columnVec, &fRow);
+                            &fResult, &dummy, columnVec, &fRow);
    } else {
       return 0;
@@ -355 +241 @@
 }
 
-//______________________________________________________________________________
-void MTreeSQL::CreateBranch(const TString &branchName, const TString &typeName)
-{
-   // Create the column(s) in the database that correspond to the branch/
-
-   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);
-}
-
 //_________________________________________________________________________
-TString MTreeSQL::CreateBranches(TSQLResult * rs)
+TString MTreeSQL::CreateBranches(TSQLResult * rs, const TString &table)
 {
    // determine leaf description string
@@ -404 +268 @@
          type = type(0,index);
       }
-      branchName = row->GetField(0);
+      branchName = table+"."+row->GetField(0);
+      //cout << table << "." << branchName << endl;
       Int_t pos;
       if ((pos=branchName.Index("__"))!=kNPOS) {
@@ -460 +325 @@
       //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 )*/{
+      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:" );
@@ -485 +352 @@
 
 //______________________________________________________________________________
-Bool_t MTreeSQL::CreateTable(const TString &table)
-{
-   // Create the database table corresponding to this TTree.
-
-   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
+void MTreeSQL::Init()
+{
+   // Initializeation routine
+
+   fCurrentEntry = -1;
+
+   GetEntries();
+
    delete fResult;
-   fResult = fServer->Query(fQuery.Data());
-   return (fResult!=0);
-}
-
-//______________________________________________________________________________
-void MTreeSQL::Init()
-{
-   // Initializeation routine
-
-   fCurrentEntry = -1;
-
-   GetEntries();
-
-   delete fResult;
-   fResult = fServer->Query(fQuery.Data());
+   fResult = fServer->Query(("SELECT * FROM "+fQuery).Data());
    if(!fResult) return;
 
-   CreateBranches(fServer->GetColumns(fDB,fTable));
+    for (auto it=fTables.begin(); it!=fTables.end(); it++)
+        CreateBranches(fServer->GetColumns(fDB, *it), *it);
 }
 
@@ -565 +371 @@
 Int_t MTreeSQL::Fill()
 {
-   // Copy the information from the user object to the TTree
-
-   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();
-      }
-   }
+   Fatal("Fill","Not implemented yet");
    return -1;
 }
@@ -619 +383 @@
    // Otherwise returns a pointer to a vector to be deleted by the caller
 
-   if (!CheckTable(fTable)) return 0;
+   //if (!CheckTable(fTable)) return 0;
 
    vector<Int_t> *columns = new vector<Int_t>;
@@ -626 +390 @@
 
    vector<TString> names;
-
-   TSQLResult *rs = fServer->GetColumns(fDB,fTable);
-   if (rs==0) { delete columns; return 0; }
-   Int_t rows = rs->GetRowCount();
-
    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 (auto it=fTables.begin(); it!=fTables.end(); it++)
+    {
+        TSQLResult *rs = fServer->GetColumns(fDB, *it);
+        if (rs==0) { delete columns; return 0; }
+        Int_t rows = rs->GetRowCount();
+
+        for (Int_t i=0;i<rows;++i) {
+            TSQLRow *row = rs->Next();
+            names.push_back( *it+"."+row->GetField(0) );
+            delete row;
+        }
+        delete rs;
+    }
 
    for(int j=0;j<nl;j++) {
@@ -651 +417 @@
       str += "__";
       str += leafName;
-      for (Int_t i=0;i<rows;++i) {
+      for (UInt_t i=0;i<names.size();++i) {
          if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {
             col = i;
@@ -659 +425 @@
       if (col<0) {
          str = leafName;
-         for (Int_t i=0;i<rows;++i) {
-            if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {
+         for (UInt_t i=0;i<names.size();++i) {
+             if (str.CompareTo(names[i],TString::kIgnoreCase)==0) {
                col = i;
                break;
@@ -682 +448 @@
 
    if (fServer==0) return GetEntriesFast();
-   if (!CheckTable(fTable.Data())) return 0;
+   //if (!CheckTable(fTable.Data())) return 0;
 
    MTreeSQL* thisvar = const_cast<MTreeSQL*>(this);
@@ -689 +455 @@
    // What about the initial value of fEntries is it really 0?
 
-   TString counting = "select count(*) from " + fTable;
+   const TString counting = "SELECT COUNT(*) FROM " + fQuery;
    TSQLResult *count = fServer->Query(counting);
 
@@ -747 +513 @@
    if(entry < fCurrentEntry || fResult==0){
       delete fResult;
-      fResult = fServer->Query(fQuery.Data());
+      fResult = fServer->Query(("SELECT * FROM "+fQuery).Data());
       fCurrentEntry = -1;
    }
@@ -758 +524 @@
       if (fRow==0 && !reset) {
          delete fResult;
-         fResult = fServer->Query(fQuery.Data());
+         fResult = fServer->Query(("SELECT * FROM "+fQuery).Data());
          fCurrentEntry = -1;
          reset = true;
@@ -766 +532 @@
    return entry;
 }
-
+/*
 //______________________________________________________________________________
 // void MTreeSQL::LoadNumberEntries()
@@ -780 +546 @@
 //    fResult =    fServer->Query(fQuery.Data());
 // }
-
+*/
 //______________________________________________________________________________
 void MTreeSQL::Refresh()
@@ -795 +561 @@
    delete fRow; fRow = 0;
 }
-
-//______________________________________________________________________________
-void MTreeSQL::ResetQuery()
-{
-   // Reset the internal query
-
-   fInsertQuery = "INSERT INTO " + fTable + " VALUES (";
-}
-
-