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

Last change on this file since 8708 was 8185, checked in by tbretz, 19 years ago
*** empty log message ***
File size: 19.7 KB
Line 
1/* ======================================================================== *\
2! $Name: not supported by cvs2svn $:$Id: MSQLServer.cc,v 1.13 2006-11-01 08:29:45 tbretz Exp $
3! --------------------------------------------------------------------------
4!
5! *
6! * This file is part of MARS, the MAGIC Analysis and Reconstruction
7! * Software. It is distributed to you in the hope that it can be a useful
8! * and timesaving tool in analysing Data of imaging Cerenkov telescopes.
9! * It is distributed WITHOUT ANY WARRANTY.
10! *
11! * Permission to use, copy, modify and distribute this software and its
12! * documentation for any purpose is hereby granted without fee,
13! * provided that the above copyright notice appear in all copies and
14! * that both that copyright notice and this permission notice appear
15! * in supporting documentation. It is provided "as is" without express
16! * or implied warranty.
17! *
18!
19!
20! Author(s): Thomas Bretz 2/2004 <mailto:tbretz@astro.uni-wuerzburg.de>
21!
22! Copyright: MAGIC Software Development, 2000-2006
23!
24!
25\* ======================================================================== */
26
27////////////////////////////////////////////////////////////////////////
28//
29// MSQLServer
30//
31// Using this instead of a TSQLServer gives the possibility to
32// browse a database server through the TBrowser and it will offer
33// many features usefull working with relational tables.
34//
35// Use it like TSQlServer:
36// new MSQLServer("mysql://localhost:3306", "hercules", "stdmagicpassword");
37// // now start your TBrowser
38// new TBrowser;
39//
40////////////////////////////////////////////////////////////////////////
41#include "MSQLServer.h"
42
43#include <iostream>
44#include <iomanip>
45#include <stdlib.h>
46
47#include <TROOT.h>
48
49#include <TH1.h>
50#include <TEnv.h>
51#include <TPRegexp.h>
52
53#include <TSQLResult.h>
54#include <TSQLServer.h>
55#include <TSQLRow.h>
56
57#include <TBrowser.h>
58
59ClassImp(MSQLServer);
60
61using namespace std;
62
63// --------------------------------------------------------------------------
64//
65// Used in virtual function TObject::Browse() to create the
66//
67void MSQLServer::BrowseColumn(TBrowser *b) /*FOLD00*/
68{
69 if (!fServ)
70 return;
71
72 const TString query0(Form("EXPLAIN %s.%s %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
73 const TString query1(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
74
75 //cout << query0 << endl;
76 TSQLResult *res = fServ->Query(query0);
77 if (!res)
78 {
79 cout << "query - failed: " << query0 << endl;
80 return;
81 }
82
83 TSQLRow *row=res->Next();
84 const TString desc((*row)[1]);
85
86 delete res;
87
88 const Bool_t isnum =
89 !desc.Contains("char", TString::kIgnoreCase) &&
90 !desc.Contains("text", TString::kIgnoreCase);
91
92 cout << query1 << endl;
93 res = fServ->Query(query1);
94 if (!res)
95 {
96 cout << "query - failed: " << query1 << endl;
97 return;
98 }
99
100 TArrayD arr(2);
101 Int_t num=0;
102 Double_t max=0;
103 Double_t min=0;
104 Double_t sum=0;
105 Double_t sqr=0;
106
107 while ((row=res->Next()))
108 {
109 const TString row0((*row)[0]);
110
111 if (!isnum)
112 {
113 cout << row0 << endl;
114 continue;
115 }
116
117 if (num==arr.GetSize())
118 arr.Set(arr.GetSize()*2);
119
120 arr[num] = atof(row0.Data());
121
122 if (num==0)
123 min=max=arr[0];
124
125 if (arr[num]>max) max = arr[num];
126 if (arr[num]<min) min = arr[num];
127
128 sum += arr[num];
129 sqr += arr[num]*arr[num];
130
131 num++;
132 }
133
134 delete res;
135
136 if (!isnum)
137 return;
138
139 if (max==min) max += 1;
140
141 Int_t num0 = 1;
142
143 if (num>0)
144 {
145 /*
146 cout << "Num: " << num << endl;
147 cout << "Mean: " << sum/num << endl;
148 cout << "Range: " << max-min << endl;
149 cout << "RMS: " << TMath::Sqrt(sqr/num-sum*sum/num/num) << endl;
150 */
151
152 num0 = (Int_t)((max-min)*40/TMath::Sqrt(sqr/num-sum*sum/num/num));
153 }
154
155 const TString title(Form("#splitline{%s}{<%s>}", (const char*)query1, (const char*)desc));
156
157 TH1F *hist=new TH1F(fColumn, title, num0, min, max);
158 for (int i=0; i<num; i++)
159 hist->Fill(arr[i]);
160
161 //cout << "Done." << endl;
162
163 hist->Draw();
164 hist->SetBit(kCanDelete);
165}
166
167void MSQLServer::BrowseTable(TBrowser *b) /*FOLD00*/
168{
169 if (!fServ)
170 return;
171
172 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
173 if (!res)
174 return;
175
176 TSQLRow *row;
177 while ((row=res->Next()))
178 {
179 TString row0((*row)[0]);
180
181 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s/%s", (const char*)fDataBase, (const char*)fTable, (const char*)row0));
182 if (!sql)
183 {
184 sql = new MSQLColumn(fServ, fDataBase, fTable, row0);
185 fList.Add(sql);
186 }
187 b->Add(sql, row0);
188 }
189}
190
191void MSQLServer::BrowseDataBase(TBrowser *b) /*FOLD00*/
192{
193 if (!fServ)
194 return;
195
196 TSQLResult *res = fServ->GetTables(fDataBase);
197 if (!res)
198 return;
199
200 TSQLRow *row;
201 while ((row=res->Next()))
202 {
203 TString row0((*row)[0]);
204
205 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s", (const char*)fDataBase, (const char*)row0));
206 if (!sql)
207 {
208 sql = new MSQLServer(fServ, fDataBase, row0);
209 fList.Add(sql);
210 }
211 b->Add(sql, row0);
212 }
213}
214
215void MSQLServer::BrowseServer(TBrowser *b) /*FOLD00*/
216{
217 if (!fServ)
218 return;
219
220 TSQLResult *res = fServ->GetDataBases();
221 if (!res)
222 return;
223
224 TSQLRow *row;
225 while ((row=res->Next()))
226 {
227 const TString row0((*row)[0]);
228
229 MSQLServer *sql = (MSQLServer*)fList.FindObject(row0);
230 if (!sql)
231 {
232 sql = new MSQLServer(fServ, row0);
233 fList.Add(sql);
234 }
235 b->Add(sql, row0);
236 }
237}
238
239void MSQLServer::PrintLine(const TArrayI &max) /*FOLD00*/
240{
241 cout << "+" << setfill('-');
242 for (int i=0; i<max.GetSize(); i++)
243 cout << setw(max[i]+1) << "-" << "-+";
244 cout << endl;
245}
246
247void MSQLServer::PrintTable(TSQLResult &res) /*FOLD00*/
248{
249 Int_t n = res.GetFieldCount();
250
251 TArrayI max(n);
252
253 for (int i=0; i<n; i++)
254 max[i] = strlen(res.GetFieldName(i));
255
256 TSQLRow *row;
257
258 TList rows;
259 while ((row=res.Next()))
260 {
261 for (int i=0; i<n; i++)
262 max[i] = TMath::Max((ULong_t)max[i], row->GetFieldLength(i));
263 rows.Add(row);
264 }
265
266 cout << endl;
267
268 PrintLine(max);
269
270 cout << "|" << setfill(' ');
271 for (int i=0; i<n; i++)
272 cout << setw(max[i]+1) << res.GetFieldName(i) << " |";
273 cout << endl;
274
275 PrintLine(max);
276
277 cout << setfill(' ');
278 TIter Next(&rows);
279 while ((row=(TSQLRow*)Next()))
280 {
281 cout << "|";
282 for (int i=0; i<n; i++)
283 {
284 const char *c = (*row)[i];
285 cout << setw(max[i]+1) << (c?c:"") << " |";
286 }
287 cout << endl;
288 }
289
290 PrintLine(max);
291}
292
293TString MSQLServer::GetFields() const /*FOLD00*/
294{
295 if (!fServ)
296 return "";
297
298 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
299 if (!res)
300 return "";
301
302 TString fields;
303
304 TSQLRow *row;
305
306 TList rows;
307 while ((row=res->Next()))
308 rows.Add(row);
309
310 TIter Next(&rows);
311 while ((row=(TSQLRow*)Next()))
312 {
313 fields += (*row)[0];
314 if (row!=rows.Last())
315 fields += ", ";
316 }
317
318 return fields;
319}
320
321void MSQLServer::PrintQuery(const char *query) const /*FOLD00*/
322{
323 if (!fServ)
324 return;
325
326 TSQLResult *res = fServ->Query(query);
327 if (res)
328 {
329 PrintTable(*res);
330 delete res;
331 }
332 else
333 cout << "Query failed: " << query << endl;
334}
335
336void MSQLServer::Print(Option_t *o) const /*FOLD00*/
337{
338 switch (fType)
339 {
340 case kIsServer:
341 PrintQuery("SHOW DATABASES");
342 break;
343
344 case kIsDataBase:
345 PrintQuery(Form("SHOW TABLES FROM %s", (const char*)fDataBase));
346 break;
347
348 case kIsTable:
349 PrintQuery(Form("SELECT * FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
350 break;
351
352 case kIsColumn:
353 PrintQuery(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
354 break;
355
356 default:
357 break;
358 }
359}
360
361void MSQLServer::ShowColumns() const /*FOLD00*/
362{
363 switch (fType)
364 {
365 case kIsTable:
366 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
367 break;
368
369 case kIsColumn:
370 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s LIKE %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
371 break;
372
373 default:
374 //Print();
375 break;
376 }
377}
378
379void MSQLServer::ShowStatus() const /*FOLD00*/
380{
381 switch (fType)
382 {
383 case kIsServer:
384 PrintQuery("SHOW STATUS");
385 break;
386
387 case kIsDataBase:
388 PrintQuery(Form("SHOW TABLE STATUS FROM %s", (const char*)fDataBase));
389 break;
390
391 case kIsTable:
392 PrintQuery(Form("SHOW TABLE STATUS FROM %s LIKE %s", (const char*)fDataBase, (const char*)fTable));
393 break;
394
395 default:
396 break;
397 }
398}
399
400void MSQLServer::ShowTableIndex() const /*FOLD00*/
401{
402 switch (fType)
403 {
404 case kIsTable:
405 case kIsColumn:
406 PrintQuery(Form("SHOW INDEX FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
407 break;
408
409 default:
410 break;
411 }
412}
413
414void MSQLServer::ShowTableCreate() const /*FOLD00*/
415{
416 switch (fType)
417 {
418 case kIsTable:
419 case kIsColumn:
420 PrintQuery(Form("SHOW CREATE TABLE %s.%s", (const char*)fDataBase, (const char*)fTable));
421 break;
422
423 default:
424 break;
425 }
426}
427
428void MSQLServer::Close(Option_t *option) /*FOLD00*/
429{
430 if (fType==kIsServer && fServ)
431 {
432 fServ->Close(option);
433 if (TestBit(kIsOwner))
434 {
435 delete fServ;
436 fServ=0;
437 ResetBit(kIsOwner);
438 fType=kIsZombie;
439 }
440 }
441}
442
443// --------------------------------------------------------------------------
444//
445// Send a SQL query to the SQL server.
446//
447// If MSQLServer is no server (column, row, ...) NULL is returned and an
448// error message is send to stdout.
449//
450// If the query failed for some reason an error message is send to stdout
451// and NULL is returned.
452//
453// If everything works fine a TSQLResult is returned. Make sure that you
454// delete it!
455//
456TSQLResult *MSQLServer::Query(const char *sql) /*FOLD00*/
457{
458 if (!fServ)
459 return NULL;
460
461 if (fType!=kIsServer)
462 {
463 cout << "ERROR: MSQLServer::Query - this is not a server!" << endl;
464 return NULL;
465 }
466
467 TSQLResult *res = fServ->Query(sql);
468 if (!res)
469 {
470 cout << /*"ERROR: MSQLServer::Query - Query failed: " <<*/ sql << endl;
471 return NULL;
472 }
473
474 return res;
475}
476
477// --------------------------------------------------------------------------
478//
479// Send a SQL query to the SQL server.
480//
481// If MSQLServer is no server (column, row, ...) NULL is returned and an
482// error message is send to stdout.
483//
484// If the query failed kFALSE is returned.
485//
486// On success kTRUE is returned.
487//
488Bool_t MSQLServer::Exec(const char* sql)
489{
490 if (!fServ)
491 return kFALSE;
492
493#if ROOT_VERSION_CODE < ROOT_VERSION(5,12,00)
494 TSQLResult *res = fServ->Query(sql);
495 if (!res)
496 {
497 cout << "ERROR: MSQLServer::Exec - Query failed: " << sql << endl;
498 return kFALSE;
499 }
500 delete res;
501 return kTRUE;
502#else
503 if (fType!=kIsServer)
504 {
505 cout << "ERROR: MSQLServer::Exec - this is not a server!" << endl;
506 return kFALSE;
507 }
508
509 return fServ->Exec(sql);
510#endif
511}
512
513Int_t MSQLServer::SelectDataBase(const char *dbname) /*FOLD00*/
514{
515 fDataBase = dbname;
516 return fType==kIsServer && fServ ? fServ->SelectDataBase(dbname) : 0;
517}
518
519TSQLResult *MSQLServer::GetDataBases(const char *wild) /*FOLD00*/
520{
521 return fType==kIsServer && fServ ? fServ->GetDataBases(wild) : NULL;
522}
523
524TSQLResult *MSQLServer::GetTables(const char *wild, const char *dbname) /*FOLD00*/
525{
526 return fType==kIsServer && fServ ? fServ->GetTables(dbname?dbname:fDataBase.Data(), wild) : NULL;
527}
528
529TSQLResult *MSQLServer::GetColumns(const char *table, const char *wild, const char *dbname) /*FOLD00*/
530{
531 return fType==kIsServer && fServ ? fServ->GetColumns(dbname?dbname:fDataBase.Data(), table, wild) : NULL;
532}
533
534Int_t MSQLServer::CreateDataBase(const char *dbname) /*FOLD00*/
535{
536 return fType==kIsServer && fServ ? fServ->CreateDataBase(dbname) : 0;
537}
538
539Int_t MSQLServer::DropDataBase(const char *dbname) /*FOLD00*/
540{
541 return fType==kIsServer && fServ ? fServ->DropDataBase(dbname) : 0;
542}
543
544Int_t MSQLServer::Reload() /*FOLD00*/
545{
546 return fType==kIsServer && fServ ? fServ->Reload() : 0;
547}
548
549Int_t MSQLServer::Shutdown() /*FOLD00*/
550{
551 return fType==kIsServer && fServ ? fServ->Shutdown() : 0;
552}
553
554const char *MSQLServer::ServerInfo() /*FOLD00*/
555{
556 return fType==kIsServer && fServ ? fServ->ServerInfo() : "";
557}
558
559Bool_t MSQLServer::IsConnected() const
560{
561 return fType==kIsServer && fServ ? fServ->IsConnected() : kFALSE;
562}
563
564const char *MSQLServer::GetName() const
565{
566 if (!fServ)
567 return "Unconnected!";
568
569 switch (fType)
570 {
571 case kIsServer: return Form("%s://%s:%d/%s", fServ->GetDBMS(), fServ->GetHost(), fServ->GetPort(), fDataBase.Data());
572 case kIsDataBase: return GetNameDataBase();
573 case kIsTable: return GetNameTable();
574 case kIsColumn: return GetNameColumn();
575 default: return "n/a";
576 }
577}
578
579Bool_t MSQLServer::Split(TString &url, TString &user, TString &pasw) const
580{
581 const Ssiz_t pos1 = url.First("://")+3;
582 const Ssiz_t pos2 = url.Last(':') +1;
583 const Ssiz_t pos3 = url.First('@');
584
585 if (pos1<0 || pos2<0 || pos3<0 || pos1>pos2 || pos2>pos3)
586 return kFALSE;
587
588 user = url(pos1, pos2-pos1-1);
589 pasw = url(pos2, pos3-pos2);
590
591 url.Remove(pos1, pos3+1-pos1);
592
593 return kTRUE;
594}
595
596void MSQLServer::Init(const char *connection, const char *user, const char *password) /*FOLD00*/
597{
598 fType = kIsZombie;
599
600 fServ = TSQLServer::Connect(connection, user, password);
601 if (fServ)
602 {
603 gROOT->GetListOfBrowsables()->Add(this, connection);
604 fType = kIsServer;
605 SetBit(kIsOwner);
606 SetBit(kMustCleanup);
607 }
608 else
609 fType = kIsZombie;
610
611 fList.SetOwner();
612}
613
614void MSQLServer::InitEnv(TEnv &env, const char *prefix)
615{
616 TString url = env.GetValue("URL", "");
617 TString db = env.GetValue("Database", "");
618 TString user = env.GetValue("User", "");
619 TString pass = env.GetValue("Password", "");
620
621 user = env.GetValue(Form("%s.User", db.Data()), user);
622
623 pass = env.GetValue(Form("%s.Password", user.Data()), pass);
624 pass = env.GetValue(Form("%s.%s.Password", db.Data(), user.Data()), pass);
625
626 if (prefix)
627 {
628 url = env.GetValue(Form("%s.URL", prefix), url);
629 db = env.GetValue(Form("%s.Database", prefix), db);
630
631 user = env.GetValue(Form("%s.User", prefix), user);
632 user = env.GetValue(Form("%s.%s.User", prefix, db.Data()), user);
633
634 pass = env.GetValue(Form("%s.Password", prefix), pass);
635 pass = env.GetValue(Form("%s.%s.Password", prefix, user.Data()), pass);
636 pass = env.GetValue(Form("%s.%s.%s.Password", prefix, db.Data(), user.Data()), pass);
637 }
638
639 if (user.IsNull() && pass.IsNull())
640 {
641 if (!Split(url, user, pass))
642 {
643 fType = kIsZombie;
644 return;
645 }
646 }
647
648 Init(url, user, pass);
649
650 if (IsConnected() && !db.IsNull())
651 SelectDataBase(db);
652}
653
654MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
655{
656 Init(connection, user, password);
657}
658
659MSQLServer::MSQLServer(const char *u) : fType(kIsZombie) /*FOLD00*/
660{
661 TString url(u);
662 TString user, pasw;
663
664 if (!Split(url, user, pasw))
665 {
666 fType = kIsZombie;
667 return;
668 }
669 Init(url, user, pasw);
670}
671
672MSQLServer::MSQLServer(TEnv &env, const char *prefix)
673{
674 InitEnv(env, prefix);
675}
676
677MSQLServer::MSQLServer()
678{
679 if (gEnv)
680 InitEnv(*gEnv);
681}
682
683MSQLServer::MSQLServer(MSQLServer &serv)
684{
685 fServ = serv.fServ;
686
687 fDataBase = serv.fDataBase;
688 fTable = serv.fTable;
689 fColumn = serv.fColumn;
690
691 fType = serv.fType;
692}
693
694MSQLServer::~MSQLServer() /*FOLD00*/
695{
696 if (gDebug>0)
697 cout << "Delete: " << GetName() << endl;
698 Close();
699}
700
701Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
702{
703 cout << "Fatal error acessing database: " << txt << endl;
704 cout << "Query: " << q << endl;
705 return kFALSE;
706}
707
708TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
709{
710 if (!fServ)
711 return "";
712
713 if (table==0)
714 table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
715 if (col==0)
716 col = (const char *)fColumn;
717
718 const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));
719
720 TSQLResult *res = fServ->Query(query);
721 if (!res)
722 return (PrintError("GetEntry - TSQLResult==NULL", query), "");
723
724 if (res->GetFieldCount()!=1)
725 {
726 delete res;
727 return (PrintError("GetEntry - Number of columns != 1", query), "");
728 }
729
730 if (res->GetRowCount()>1)
731 {
732 delete res;
733 return (PrintError("GetEntry - Number of rows > 1", query), "");
734 }
735
736 if (res->GetRowCount()==0)
737 {
738 delete res;
739 return "";
740 }
741
742 const char *fld = res->Next()->GetField(0);
743 if (!fld)
744 {
745 delete res;
746 return (PrintError("GetEntry - Entry is empty", query), "");
747 }
748
749 const TString rc(fld);
750 delete res;
751 return rc;
752}
753
754// --------------------------------------------------------------------------
755//
756// Return the name of the (first) column with a primary key
757//
758TString MSQLServer::GetPrimaryKey(const char *table)
759{
760 TSQLResult *res = GetColumns(table);
761 if (!res)
762 return "";
763
764 TString rc;
765
766 TSQLRow *row = 0;
767 while ((row=res->Next()))
768 {
769 TString key = (*row)[3];
770 if (key!="PRI")
771 continue;
772
773 rc = (*row)[0];
774 break;
775 }
776
777 delete res;
778 return rc;
779}
780
781// --------------------------------------------------------------------------
782//
783// Searches in the text for patterns like "Table.Column". If such a pettern
784// is found the primary key of the table is requested a "LEFT JOIN"
785// with this Table is added ON the identity of the primary key of Table
786// with the given table.
787//
788TString MSQLServer::GetJoins(const char *table, const TString text)
789{
790 Int_t p=0;
791
792 TString mods;
793 TArrayI pos;
794
795 // Find all Table.Column expression. Because also floating point
796 // numbers can contain a dot the result has to be checked carefully
797 TString joins;
798 TPRegexp reg = TPRegexp("\\w+[.]\\w+");
799 while (1)
800 {
801 // Check whether expression is found
802 if (reg.Match(text, mods, p, 130, &pos)==0)
803 break;
804
805 // Get expression from text
806 const TString expr = text(pos[0], pos[1]-pos[0]);
807 p = pos[1];
808
809 if (expr.IsFloat())
810 continue;
811
812 const TString tab = expr(0, expr.First('.'));
813 const TString var = expr(expr.First('.')+1, expr.Length());
814
815 // If the table found is the primary table itself skip it.
816 if (tab==table)
817 continue;
818
819 // If this join has already be set, skip it.
820 if (joins.Contains(Form(" %s ", tab.Data())))
821 continue;
822
823 // Now get the primary key of the table to be joined
824 const TString prim = GetPrimaryKey(tab);
825 if (prim.IsNull())
826 continue;
827
828 joins += Form("LEFT JOIN %s ON %s.%s=%s.%s ", tab.Data(),
829 table, prim.Data(), tab.Data(), prim.Data());
830 }
831
832 if (!joins.IsNull())
833 joins += " ";
834
835 return joins;
836}
837
838void MSQLServer::RecursiveRemove(TObject *obj)
839{
840 if (fServ==obj)
841 {
842 fServ=NULL;
843 fType = kIsZombie;
844 ResetBit(kIsOwner);
845 }
846}
Note: See TracBrowser for help on using the repository browser.