source: trunk/MagicSoft/Mars/msql/MSQLServer.cc@ 5653

/* ======================================================================== *\
!
! *
! * This file is part of MARS, the MAGIC Analysis and Reconstruction
! * Software. It is distributed to you in the hope that it can be a useful
! * and timesaving tool in analysing Data of imaging Cerenkov telescopes.
! * It is distributed WITHOUT ANY WARRANTY.
! *
! * Permission to use, copy, modify and distribute this software and its
! * documentation for any purpose is hereby granted without fee,
! * provided that the above copyright notice appear in all copies and
! * that both that copyright notice and this permission notice appear
! * in supporting documentation. It is provided "as is" without express
! * or implied warranty.
! *
!
!
!   Author(s): Thomas Bretz 2/2004 <mailto:tbretz@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2004
!
!
\* ======================================================================== */

////////////////////////////////////////////////////////////////////////
//
//  MSQLServer
//
//  Using this instead of a TSQLServer gives the possibility to
//  browse a database server through the TBrowser and it will offer
//  many features usefull working with relational tables.
//
//  Use it like TSQlServer:
//    new MSQLServer("mysql://localhost:3306", "hercules", "stdmagicpassword");
//    // now start your TBrowser
//    new TBrowser;
//
////////////////////////////////////////////////////////////////////////
#include "MSQLServer.h"

#include <iostream>
#include <iomanip>
#include <stdlib.h>

#include <TROOT.h>

#include <TH1.h>
#include <TEnv.h>

#include <TSQLResult.h>
#include <TSQLServer.h>
#include <TSQLRow.h>

#include <TBrowser.h>

ClassImp(MSQLServer);

using namespace std;

// --------------------------------------------------------------------------
//
// Used in virtual function TObject::Browse() to create the
//
void MSQLServer::BrowseColumn(TBrowser *b) /*FOLD00*/
{
    const TString query0(Form("EXPLAIN %s.%s %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
    const TString query1(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));

    //cout << query0 << endl;
    TSQLResult *res = fServ->Query(query0);
    if (!res)
    {
        cout << "query - failed: " << query0 << endl;
        return;
    }

    TSQLRow *row=res->Next();
    const TString desc((*row)[1]);

    delete res;

    const Bool_t isnum =
        !desc.Contains("char", TString::kIgnoreCase) &&
        !desc.Contains("text", TString::kIgnoreCase);

    cout << query1 << endl;
    res = fServ->Query(query1);
    if (!res)
    {
        cout << "query - failed: " << query1 << endl;
        return;
    }

    TArrayD arr(2);
    Int_t num=0;
    Double_t max=0;
    Double_t min=0;
    Double_t sum=0;
    Double_t sqr=0;

    while ((row=res->Next()))
    {
        const TString row0((*row)[0]);

        if (!isnum)
        {
            cout << row0 << endl;
            continue;
        }

        if (num==arr.GetSize())
            arr.Set(arr.GetSize()*2);

        arr[num] = atof(row0.Data());

        if (num==0)
            min=max=arr[0];

        if (arr[num]>max) max = arr[num];
        if (arr[num]<min) min = arr[num];

        sum += arr[num];
        sqr += arr[num]*arr[num];

        num++;
    }

    delete res;

    if (!isnum)
        return;

    if (max==min) max += 1;

    Int_t num0 = 1;

    if (num>0)
    {
        /*
         cout << "Num:   " << num << endl;
         cout << "Mean:  " << sum/num << endl;
         cout << "Range: " << max-min << endl;
         cout << "RMS:   " << TMath::Sqrt(sqr/num-sum*sum/num/num) << endl;
         */

        num0 = (Int_t)((max-min)*40/TMath::Sqrt(sqr/num-sum*sum/num/num));
    }

    const TString title(Form("#splitline{%s}{<%s>}", (const char*)query1, (const char*)desc));

    TH1F *hist=new TH1F(fColumn, title, num0, min, max);
    for (int i=0; i<num; i++)
        hist->Fill(arr[i]);

    //cout << "Done." << endl;

    hist->Draw();
    hist->SetBit(kCanDelete);
}

void MSQLServer::BrowseTable(TBrowser *b) /*FOLD00*/
{
    TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
    if (!res)
        return;

    TSQLRow *row;
    while ((row=res->Next()))
    {
        TString row0((*row)[0]);

        MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s/%s", (const char*)fDataBase, (const char*)fTable, (const char*)row0));
        if (!sql)
        {
            sql = new MSQLColumn(fServ, fDataBase, fTable, row0);
            fList.Add(sql);
        }
        b->Add(sql, row0);
    }
}

void MSQLServer::BrowseDataBase(TBrowser *b) /*FOLD00*/
{
    TSQLResult *res = fServ->GetTables(fDataBase);
    if (!res)
        return;

    TSQLRow *row;
    while ((row=res->Next()))
    {
        TString row0((*row)[0]);

        MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s", (const char*)fDataBase, (const char*)row0));
        if (!sql)
        {
            sql = new MSQLServer(fServ, fDataBase, row0);
            fList.Add(sql);
        }
        b->Add(sql, row0);
    }
}

void MSQLServer::BrowseServer(TBrowser *b) /*FOLD00*/
{
    TSQLResult *res = fServ->GetDataBases();
    if (!res)
        return;

    TSQLRow *row;
    while ((row=res->Next()))
    {
        const TString row0((*row)[0]);

        MSQLServer *sql = (MSQLServer*)fList.FindObject(row0);
        if (!sql)
        {
            sql = new MSQLServer(fServ, row0);
            fList.Add(sql);
        }
        b->Add(sql, row0);
    }
}

void MSQLServer::PrintLine(const TArrayI &max) /*FOLD00*/
{
    cout << "+" << setfill('-');
    for (int i=0; i<max.GetSize(); i++)
        cout << setw(max[i]+1) << "-" << "-+";
    cout << endl;
}

void MSQLServer::PrintTable(TSQLResult &res) /*FOLD00*/
{
    Int_t n = res.GetFieldCount();

    TArrayI max(n);

    for (int i=0; i<n; i++)
        max[i] = strlen(res.GetFieldName(i));

    TSQLRow *row;

    TList rows;
    while ((row=res.Next()))
    {
        for (int i=0; i<n; i++)
            max[i] = TMath::Max((ULong_t)max[i], row->GetFieldLength(i));
        rows.Add(row);
    }

    cout << endl;

    PrintLine(max);

    cout << "|" << setfill(' ');
    for (int i=0; i<n; i++)
        cout << setw(max[i]+1) << res.GetFieldName(i) << " |";
    cout << endl;

    PrintLine(max);

    cout << setfill(' ');
    TIter Next(&rows);
    while ((row=(TSQLRow*)Next()))
    {
        cout << "|";
        for (int i=0; i<n; i++)
        {
            const char *c = (*row)[i];
            cout << setw(max[i]+1) << (c?c:"") << " |";
        }
        cout << endl;
    }

    PrintLine(max);
}

TString MSQLServer::GetFields() const /*FOLD00*/
{
    TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
    if (!res)
        return "";

    TString fields;

    TSQLRow *row;

    TList rows;
    while ((row=res->Next()))
        rows.Add(row);

    TIter Next(&rows);
    while ((row=(TSQLRow*)Next()))
    {
        fields += (*row)[0];
        if (row!=rows.Last())
            fields += ", ";
    }

    return fields;
}

void MSQLServer::PrintQuery(const char *query) const /*FOLD00*/
{
    TSQLResult *res = fServ->Query(query);
    if (res)
    {
        PrintTable(*res);
        delete res;
    }
    else
        cout << "Query failed: " << query << endl;
}

void MSQLServer::Print(Option_t *o) const /*FOLD00*/
{
    switch (fType)
    {
    case kIsServer:
        PrintQuery("SHOW DATABASES");
        break;

    case kIsDataBase:
        PrintQuery(Form("SHOW TABLES FROM %s", (const char*)fDataBase));
        break;

    case kIsTable:
        PrintQuery(Form("SELECT * FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
        break;

    case kIsColumn:
        PrintQuery(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
        break;

    default:
        break;
    }
}

void MSQLServer::ShowColumns() const /*FOLD00*/
{
    switch (fType)
    {
    case kIsTable:
        PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
        break;

    case kIsColumn:
        PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s LIKE %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
        break;

    default:
        //Print();
        break;
    }
}

void MSQLServer::ShowStatus() const /*FOLD00*/
{
    switch (fType)
    {
    case kIsServer:
        PrintQuery("SHOW STATUS");
        break;

    case kIsDataBase:
        PrintQuery(Form("SHOW TABLE STATUS FROM %s", (const char*)fDataBase));
        break;

    case kIsTable:
        PrintQuery(Form("SHOW TABLE STATUS FROM %s LIKE %s", (const char*)fDataBase, (const char*)fTable));
        break;

    default:
        break;
    }
}

void MSQLServer::ShowTableIndex() const /*FOLD00*/
{
    switch (fType)
    {
    case kIsTable:
    case kIsColumn:
        PrintQuery(Form("SHOW INDEX FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
        break;

    default:
        break;
    }
}

void MSQLServer::ShowTableCreate() const /*FOLD00*/
{
    switch (fType)
    {
    case kIsTable:
    case kIsColumn:
        PrintQuery(Form("SHOW CREATE TABLE %s.%s", (const char*)fDataBase, (const char*)fTable));
        break;

    default:
        break;
    }
}

void MSQLServer::Close(Option_t *option) /*FOLD00*/
{
    if (fType==kIsServer)
        fServ->Close(option);
}

// --------------------------------------------------------------------------
//
// Send a SQL query to the SQl server.
//
// If MSQLServer is no server (column, row, ...) NULL is returned and an
//  error message is send to stdout.
//
// If the query failed for some reason an error message is send to stdout
//  and NULL is returned.
//
// If everything works fine a TSQLResult is returned. Make sure that you
// delete it!
//
TSQLResult *MSQLServer::Query(const char *sql) /*FOLD00*/
{
    if (fType!=kIsServer)
    {
        cout << "ERROR: MSQLServer::Query - this is not a server!" << endl;
        return NULL;
    }

    TSQLResult *res = fServ->Query(sql);
    if (!res)
    {
        cout << "ERROR: MSQLServer::Query - Query failed: " << sql << endl;
        return NULL;
    }

    return res;
}

Int_t MSQLServer::SelectDataBase(const char *dbname) /*FOLD00*/
{
    return fType==kIsServer ? fServ->SelectDataBase(dbname) : 0;
}

TSQLResult *MSQLServer::GetDataBases(const char *wild) /*FOLD00*/
{
    return fType==kIsServer ? fServ->GetDataBases(wild) : NULL;
}

TSQLResult *MSQLServer::GetTables(const char *dbname, const char *wild) /*FOLD00*/
{
    return fType==kIsServer ? fServ->GetTables(dbname, wild) : NULL;
}

TSQLResult *MSQLServer::GetColumns(const char *dbname, const char *table, const char *wild) /*FOLD00*/
{
    return fType==kIsServer ? fServ->GetColumns(dbname, table, wild) : NULL;
}

Int_t MSQLServer::CreateDataBase(const char *dbname) /*FOLD00*/
{
    return fType==kIsServer ? fServ->CreateDataBase(dbname) : 0;
}

Int_t MSQLServer::DropDataBase(const char *dbname) /*FOLD00*/
{
    return fType==kIsServer ? fServ->DropDataBase(dbname) : 0;
}

Int_t MSQLServer::Reload() /*FOLD00*/
{
    return fType==kIsServer ? fServ->Reload() : 0;
}

Int_t MSQLServer::Shutdown() /*FOLD00*/
{
    return fType==kIsServer ? fServ->Shutdown() : 0;
}

const char *MSQLServer::ServerInfo() /*FOLD00*/
{
    return fType==kIsServer ? fServ->ServerInfo() : "";
}

Bool_t MSQLServer::IsConnected() const
{
    return fType==kIsServer ? fServ->IsConnected() : kFALSE;
}

const char *MSQLServer::GetName() const
{
    switch (fType)
    {
    case kIsServer:   return Form("%s://%s:%d", fServ->GetDBMS(), fServ->GetHost(), fServ->GetPort());
    case kIsDataBase: return GetNameDataBase();
    case kIsTable:    return GetNameTable();
    case kIsColumn:   return GetNameColumn();
    default:          return "n/a";
    }
}

Bool_t MSQLServer::Split(TString &url, TString &user, TString &pasw) const
{
    const Ssiz_t pos1 = url.First("://")+3;
    const Ssiz_t pos2 = url.Last(':')   +1;
    const Ssiz_t pos3 = url.First('@');

    if (pos1<0 || pos2<0 || pos3<0 || pos1>pos2 || pos2>pos3)
        return kFALSE;

    user = url(pos1, pos2-pos1-1);
    pasw = url(pos2, pos3-pos2);

    url.Remove(pos1, pos3+1-pos1);

    return kTRUE;
}

void MSQLServer::Init(const char *connection, const char *user, const char *password) /*FOLD00*/
{
    fType = kIsZombie;

    fServ = TSQLServer::Connect(connection, user, password);
    if (fServ)
    {
        gROOT->GetListOfBrowsables()->Add(this, connection);
        fType = kIsServer;
    }
    else
        SetBit(kZombie);

    fList.SetOwner();
}

void MSQLServer::InitEnv(TEnv &env, const char *prefix)
{
    TString url; 
    TString user;
    TString pass;

    if (prefix)
    {
        url  = env.GetValue(Form("%s.URL", prefix),      "");
        user = env.GetValue(Form("%s.User", prefix),     "");
        pass = env.GetValue(Form("%s.Password", prefix), "");
    }
    if (url.IsNull())
        url  = env.GetValue("URL",      "");
    if (user.IsNull())
        user = env.GetValue("User",     "");
    if (pass.IsNull())
        pass = env.GetValue("Password", "");

    if (user.IsNull() && pass.IsNull())
    {
        if (!Split(url, user, pass))
        {
            SetBit(kIsZombie);
            return;
        }
    }

    Init(url, user, pass);
}

MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
{
    Init(connection, user, password);
}

MSQLServer::MSQLServer(const char *u) : fType(kIsZombie) /*FOLD00*/
{
    TString url(u);
    TString user, pasw;

    if (!Split(url, user, pasw))
    {
        SetBit(kIsZombie);
        return;
    }
    Init(url, user, pasw);
}

MSQLServer::MSQLServer(TEnv &env, const char *prefix)
{
    InitEnv(env, prefix);
}

MSQLServer::MSQLServer()
{
    if (gEnv)
        InitEnv(*gEnv);
}

MSQLServer::~MSQLServer() /*FOLD00*/
{
    Close();
    if (gDebug>0)
        cout << "Delete: " << GetName() << endl;
}

Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
{
    cout << "Fatal error acessing database: " << txt << endl;
    cout << "Query: " << q << endl;
    return kFALSE;
}

TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
{
    if (!fServ)
        return "";

    if (table==0)
        table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
    if (col==0)
        col = (const char *)fColumn;

    const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));

    TSQLResult *res = fServ->Query(query);
    if (!res)
        return (PrintError("GetEntry - TSQLResult==NULL", query), "");

    if (res->GetFieldCount()!=1)
    {
        delete res;
        return (PrintError("GetEntry - Number of columns != 1", query), "");
    }

    if (res->GetRowCount()>1)
    {
        delete res;
        return (PrintError("GetEntry - Number of rows > 1", query), "");
    }

    if (res->GetRowCount()==0)
    {
        delete res;
        return "";
    }

    const char *fld = res->Next()->GetField(0);
    if (!fld)
    {
        delete res;
        return (PrintError("GetEntry - Entry is empty", query), "");
    }

    const TString rc(fld);
    delete res;
    return rc;
}