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

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