/// \file
/// \ingroup tutorial_sql
/// This is slight modification of ntuple1.C example.
/// A canvas with sub-pads is produced, stored to an sql data base and read back
/// To run this macro, you need the hsimple.root file, produced by the hsimple.C macro
///
/// \macro_code
///
/// \author Sergey Linev

// example configuration for MySQL 4.1
const char* dbname = "mysql://host.domain/test";
const char* username = "user";
const char* userpass = "pass";

// example configuration for Oracle 9i
//const char* dbname = "oracle://host.domain/db-test";
//const char* username = "user";
//const char* userpass = "pass";

void sqlcanvas()
{
   canvas_write();
   canvas_read();
}

void canvas_write()
{
   //just in case this script is executed multiple times
   delete gROOT->GetListOfFiles()->FindObject("hsimple.root");
   delete gROOT->GetListOfCanvases()->FindObject("c1");

   gBenchmark->Start("ntuple1");
   //
   // Connect ROOT histogram/ntuple demonstration file
   // generated by example hsimple.C.
   TFile *f1 = new TFile("hsimple.root");
   //
   // Create a canvas, with 4 pads
   //
   TCanvas *c1 = new TCanvas("c1","The Ntuple canvas",200,10,700,780);
   TPad *pad1 = new TPad("pad1","This is pad1",0.02,0.52,0.48,0.98,21);
   TPad *pad2 = new TPad("pad2","This is pad2",0.52,0.52,0.98,0.98,21);
   TPad *pad3 = new TPad("pad3","This is pad3",0.02,0.02,0.48,0.48,21);
   TPad *pad4 = new TPad("pad4","This is pad4",0.52,0.02,0.98,0.48,1);
   pad1->Draw();
   pad2->Draw();
   pad3->Draw();
   pad4->Draw();
   //
   // Change default style for the statistics box
   gStyle->SetStatW(0.30);
   gStyle->SetStatH(0.20);
   gStyle->SetStatColor(42);
   //
   // Display a function of one ntuple column imposing a condition
   // on another column.
   pad1->cd();
   pad1->SetGrid();
   pad1->SetLogy();
   pad1->GetFrame()->SetFillColor(15);
   TNtuple *ntuple = (TNtuple*)f1->Get("ntuple");
   ntuple->SetLineColor(1);
   ntuple->SetFillStyle(1001);
   ntuple->SetFillColor(45);
   ntuple->Draw("3*px+2","px**2+py**2>1");
   ntuple->SetFillColor(38);
   ntuple->Draw("2*px+2","pz>2","same");
   ntuple->SetFillColor(5);
   ntuple->Draw("1.3*px+2","(px^2+py^2>4) && py>0","same");
   pad1->RedrawAxis();
   //
   // Display the profile of two columns
   // The profile histogram produced is saved in the current directory with
   // the name hprofs
   pad2->cd();
   pad2->SetGrid();
   pad2->GetFrame()->SetFillColor(32);
   ntuple->Draw("pz:px>>hprofs","","goffprofs");
   TProfile *hprofs = (TProfile*)gDirectory->Get("hprofs");
   hprofs->SetMarkerColor(5);
   hprofs->SetMarkerSize(0.7);
   hprofs->SetMarkerStyle(21);
   hprofs->Fit("pol2");
   // Get pointer to fitted function and modify its attributes
   TF1 *fpol2 = hprofs->GetFunction("pol2");
   fpol2->SetLineWidth(4);
   fpol2->SetLineColor(2);
   //
   // Display a scatter plot of two columns with a selection.
   // Superimpose the result of another cut with a different marker color
   pad3->cd();
   pad3->GetFrame()->SetFillColor(38);
   pad3->GetFrame()->SetBorderSize(8);
   ntuple->SetMarkerColor(1);
   ntuple->Draw("py:px","pz>1");
   ntuple->SetMarkerColor(2);
   ntuple->Draw("py:px","pz<1","same");
   //
   // Display a 3-D scatter plot of 3 columns. Superimpose a different selection.
   pad4->cd();
   ntuple->Draw("pz:py:px","(pz<10 && pz>6)+(pz<4 && pz>3)");
   ntuple->SetMarkerColor(4);
   ntuple->Draw("pz:py:px","pz<6 && pz>4","same");
   ntuple->SetMarkerColor(5);
   ntuple->Draw("pz:py:px","pz<4 && pz>3","same");
   TPaveText *l4 = new TPaveText(-0.9,0.5,0.9,0.95);
   l4->SetFillColor(42);
   l4->SetTextAlign(12);
   l4->AddText("You can interactively rotate this view in 2 ways:");
   l4->AddText("  - With the RotateCube in clicking in this pad");
   l4->AddText("  - Selecting View with x3d in the View menu");
   l4->Draw();
   //
   c1->cd();
   c1->Update();
   gStyle->SetStatColor(19);
   gBenchmark->Show("ntuple1");

   TSQLFile* fsql1 = new TSQLFile(dbname, "recreate", username, userpass);
   if (fsql1->IsZombie()) { delete fsql1; return; }

//  changing TSQLFile configuration, you may improve speed
//  of reading or writing object to/from sql database

//   fsql1->SetUseSuffixes(kFALSE);
//   fsql1->SetArrayLimit(1000);
//   fsql1->SetUseIndexes(1);
//   fsql1->SetTablesType("ISAM");
//   fsql1->SetUseTransactions(kFALSE);


   //  Unncomment this line to see all SQL commands in log file
   //  fsql1->StartLogFile("canvas.log");

   gBenchmark->Start("writeSQL");
   c1->Write("Canvas");
   gBenchmark->Show("writeSQL");
   delete fsql1;
}

void canvas_read()
{
   TFile* f2 = new TSQLFile(dbname, "open", username, userpass);
   if (f2->IsZombie()) { delete f2; return; }

   f2->ls();
   gBenchmark->Start("readSQL");
   TCanvas* cc = (TCanvas*) f2->Get("Canvas");
   gBenchmark->Show("readSQL");
   if (cc!=0) cc->Draw();

   delete f2;
}