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

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