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

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