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

Last change on this file since 7842 was 7842, checked in by tbretz, 18 years ago
*** empty log message ***
File size: 15.5 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 const TString query0(Form("EXPLAIN %s.%s %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
67 const TString query1(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
68
69 //cout << query0 << endl;
70 TSQLResult *res = fServ->Query(query0);
71 if (!res)
72 {
73 cout << "query - failed: " << query0 << endl;
74 return;
75 }
76
77 TSQLRow *row=res->Next();
78 const TString desc((*row)[1]);
79
80 delete res;
81
82 const Bool_t isnum =
83 !desc.Contains("char", TString::kIgnoreCase) &&
84 !desc.Contains("text", TString::kIgnoreCase);
85
86 cout << query1 << endl;
87 res = fServ->Query(query1);
88 if (!res)
89 {
90 cout << "query - failed: " << query1 << endl;
91 return;
92 }
93
94 TArrayD arr(2);
95 Int_t num=0;
96 Double_t max=0;
97 Double_t min=0;
98 Double_t sum=0;
99 Double_t sqr=0;
100
101 while ((row=res->Next()))
102 {
103 const TString row0((*row)[0]);
104
105 if (!isnum)
106 {
107 cout << row0 << endl;
108 continue;
109 }
110
111 if (num==arr.GetSize())
112 arr.Set(arr.GetSize()*2);
113
114 arr[num] = atof(row0.Data());
115
116 if (num==0)
117 min=max=arr[0];
118
119 if (arr[num]>max) max = arr[num];
120 if (arr[num]<min) min = arr[num];
121
122 sum += arr[num];
123 sqr += arr[num]*arr[num];
124
125 num++;
126 }
127
128 delete res;
129
130 if (!isnum)
131 return;
132
133 if (max==min) max += 1;
134
135 Int_t num0 = 1;
136
137 if (num>0)
138 {
139 /*
140 cout << "Num: " << num << endl;
141 cout << "Mean: " << sum/num << endl;
142 cout << "Range: " << max-min << endl;
143 cout << "RMS: " << TMath::Sqrt(sqr/num-sum*sum/num/num) << endl;
144 */
145
146 num0 = (Int_t)((max-min)*40/TMath::Sqrt(sqr/num-sum*sum/num/num));
147 }
148
149 const TString title(Form("#splitline{%s}{<%s>}", (const char*)query1, (const char*)desc));
150
151 TH1F *hist=new TH1F(fColumn, title, num0, min, max);
152 for (int i=0; i<num; i++)
153 hist->Fill(arr[i]);
154
155 //cout << "Done." << endl;
156
157 hist->Draw();
158 hist->SetBit(kCanDelete);
159}
160
161void MSQLServer::BrowseTable(TBrowser *b) /*FOLD00*/
162{
163 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
164 if (!res)
165 return;
166
167 TSQLRow *row;
168 while ((row=res->Next()))
169 {
170 TString row0((*row)[0]);
171
172 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s/%s", (const char*)fDataBase, (const char*)fTable, (const char*)row0));
173 if (!sql)
174 {
175 sql = new MSQLColumn(fServ, fDataBase, fTable, row0);
176 fList.Add(sql);
177 }
178 b->Add(sql, row0);
179 }
180}
181
182void MSQLServer::BrowseDataBase(TBrowser *b) /*FOLD00*/
183{
184 TSQLResult *res = fServ->GetTables(fDataBase);
185 if (!res)
186 return;
187
188 TSQLRow *row;
189 while ((row=res->Next()))
190 {
191 TString row0((*row)[0]);
192
193 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s", (const char*)fDataBase, (const char*)row0));
194 if (!sql)
195 {
196 sql = new MSQLServer(fServ, fDataBase, row0);
197 fList.Add(sql);
198 }
199 b->Add(sql, row0);
200 }
201}
202
203void MSQLServer::BrowseServer(TBrowser *b) /*FOLD00*/
204{
205 TSQLResult *res = fServ->GetDataBases();
206 if (!res)
207 return;
208
209 TSQLRow *row;
210 while ((row=res->Next()))
211 {
212 const TString row0((*row)[0]);
213
214 MSQLServer *sql = (MSQLServer*)fList.FindObject(row0);
215 if (!sql)
216 {
217 sql = new MSQLServer(fServ, row0);
218 fList.Add(sql);
219 }
220 b->Add(sql, row0);
221 }
222}
223
224void MSQLServer::PrintLine(const TArrayI &max) /*FOLD00*/
225{
226 cout << "+" << setfill('-');
227 for (int i=0; i<max.GetSize(); i++)
228 cout << setw(max[i]+1) << "-" << "-+";
229 cout << endl;
230}
231
232void MSQLServer::PrintTable(TSQLResult &res) /*FOLD00*/
233{
234 Int_t n = res.GetFieldCount();
235
236 TArrayI max(n);
237
238 for (int i=0; i<n; i++)
239 max[i] = strlen(res.GetFieldName(i));
240
241 TSQLRow *row;
242
243 TList rows;
244 while ((row=res.Next()))
245 {
246 for (int i=0; i<n; i++)
247 max[i] = TMath::Max((ULong_t)max[i], row->GetFieldLength(i));
248 rows.Add(row);
249 }
250
251 cout << endl;
252
253 PrintLine(max);
254
255 cout << "|" << setfill(' ');
256 for (int i=0; i<n; i++)
257 cout << setw(max[i]+1) << res.GetFieldName(i) << " |";
258 cout << endl;
259
260 PrintLine(max);
261
262 cout << setfill(' ');
263 TIter Next(&rows);
264 while ((row=(TSQLRow*)Next()))
265 {
266 cout << "|";
267 for (int i=0; i<n; i++)
268 {
269 const char *c = (*row)[i];
270 cout << setw(max[i]+1) << (c?c:"") << " |";
271 }
272 cout << endl;
273 }
274
275 PrintLine(max);
276}
277
278TString MSQLServer::GetFields() const /*FOLD00*/
279{
280 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
281 if (!res)
282 return "";
283
284 TString fields;
285
286 TSQLRow *row;
287
288 TList rows;
289 while ((row=res->Next()))
290 rows.Add(row);
291
292 TIter Next(&rows);
293 while ((row=(TSQLRow*)Next()))
294 {
295 fields += (*row)[0];
296 if (row!=rows.Last())
297 fields += ", ";
298 }
299
300 return fields;
301}
302
303void MSQLServer::PrintQuery(const char *query) const /*FOLD00*/
304{
305 TSQLResult *res = fServ->Query(query);
306 if (res)
307 {
308 PrintTable(*res);
309 delete res;
310 }
311 else
312 cout << "Query failed: " << query << endl;
313}
314
315void MSQLServer::Print(Option_t *o) const /*FOLD00*/
316{
317 switch (fType)
318 {
319 case kIsServer:
320 PrintQuery("SHOW DATABASES");
321 break;
322
323 case kIsDataBase:
324 PrintQuery(Form("SHOW TABLES FROM %s", (const char*)fDataBase));
325 break;
326
327 case kIsTable:
328 PrintQuery(Form("SELECT * FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
329 break;
330
331 case kIsColumn:
332 PrintQuery(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
333 break;
334
335 default:
336 break;
337 }
338}
339
340void MSQLServer::ShowColumns() const /*FOLD00*/
341{
342 switch (fType)
343 {
344 case kIsTable:
345 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
346 break;
347
348 case kIsColumn:
349 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s LIKE %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
350 break;
351
352 default:
353 //Print();
354 break;
355 }
356}
357
358void MSQLServer::ShowStatus() const /*FOLD00*/
359{
360 switch (fType)
361 {
362 case kIsServer:
363 PrintQuery("SHOW STATUS");
364 break;
365
366 case kIsDataBase:
367 PrintQuery(Form("SHOW TABLE STATUS FROM %s", (const char*)fDataBase));
368 break;
369
370 case kIsTable:
371 PrintQuery(Form("SHOW TABLE STATUS FROM %s LIKE %s", (const char*)fDataBase, (const char*)fTable));
372 break;
373
374 default:
375 break;
376 }
377}
378
379void MSQLServer::ShowTableIndex() const /*FOLD00*/
380{
381 switch (fType)
382 {
383 case kIsTable:
384 case kIsColumn:
385 PrintQuery(Form("SHOW INDEX FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
386 break;
387
388 default:
389 break;
390 }
391}
392
393void MSQLServer::ShowTableCreate() const /*FOLD00*/
394{
395 switch (fType)
396 {
397 case kIsTable:
398 case kIsColumn:
399 PrintQuery(Form("SHOW CREATE TABLE %s.%s", (const char*)fDataBase, (const char*)fTable));
400 break;
401
402 default:
403 break;
404 }
405}
406
407void MSQLServer::Close(Option_t *option) /*FOLD00*/
408{
409 if (fType==kIsServer)
410 fServ->Close(option);
411}
412
413// --------------------------------------------------------------------------
414//
415// Send a SQL query to the SQl server.
416//
417// If MSQLServer is no server (column, row, ...) NULL is returned and an
418// error message is send to stdout.
419//
420// If the query failed for some reason an error message is send to stdout
421// and NULL is returned.
422//
423// If everything works fine a TSQLResult is returned. Make sure that you
424// delete it!
425//
426TSQLResult *MSQLServer::Query(const char *sql) /*FOLD00*/
427{
428 if (fType!=kIsServer)
429 {
430 cout << "ERROR: MSQLServer::Query - this is not a server!" << endl;
431 return NULL;
432 }
433
434 TSQLResult *res = fServ->Query(sql);
435 if (!res)
436 {
437 cout << "ERROR: MSQLServer::Query - Query failed: " << sql << endl;
438 return NULL;
439 }
440
441 return res;
442}
443
444Int_t MSQLServer::SelectDataBase(const char *dbname) /*FOLD00*/
445{
446 fDataBase = dbname;
447 return fType==kIsServer ? fServ->SelectDataBase(dbname) : 0;
448}
449
450TSQLResult *MSQLServer::GetDataBases(const char *wild) /*FOLD00*/
451{
452 return fType==kIsServer ? fServ->GetDataBases(wild) : NULL;
453}
454
455TSQLResult *MSQLServer::GetTables(const char *dbname, const char *wild) /*FOLD00*/
456{
457 return fType==kIsServer ? fServ->GetTables(dbname, wild) : NULL;
458}
459
460TSQLResult *MSQLServer::GetColumns(const char *dbname, const char *table, const char *wild) /*FOLD00*/
461{
462 return fType==kIsServer ? fServ->GetColumns(dbname, table, wild) : NULL;
463}
464
465Int_t MSQLServer::CreateDataBase(const char *dbname) /*FOLD00*/
466{
467 return fType==kIsServer ? fServ->CreateDataBase(dbname) : 0;
468}
469
470Int_t MSQLServer::DropDataBase(const char *dbname) /*FOLD00*/
471{
472 return fType==kIsServer ? fServ->DropDataBase(dbname) : 0;
473}
474
475Int_t MSQLServer::Reload() /*FOLD00*/
476{
477 return fType==kIsServer ? fServ->Reload() : 0;
478}
479
480Int_t MSQLServer::Shutdown() /*FOLD00*/
481{
482 return fType==kIsServer ? fServ->Shutdown() : 0;
483}
484
485const char *MSQLServer::ServerInfo() /*FOLD00*/
486{
487 return fType==kIsServer ? fServ->ServerInfo() : "";
488}
489
490Bool_t MSQLServer::IsConnected() const
491{
492 return fType==kIsServer ? fServ->IsConnected() : kFALSE;
493}
494
495const char *MSQLServer::GetName() const
496{
497 switch (fType)
498 {
499 case kIsServer: return Form("%s://%s:%d/%s", fServ->GetDBMS(), fServ->GetHost(), fServ->GetPort(), fDataBase.Data());
500 case kIsDataBase: return GetNameDataBase();
501 case kIsTable: return GetNameTable();
502 case kIsColumn: return GetNameColumn();
503 default: return "n/a";
504 }
505}
506
507Bool_t MSQLServer::Split(TString &url, TString &user, TString &pasw) const
508{
509 const Ssiz_t pos1 = url.First("://")+3;
510 const Ssiz_t pos2 = url.Last(':') +1;
511 const Ssiz_t pos3 = url.First('@');
512
513 if (pos1<0 || pos2<0 || pos3<0 || pos1>pos2 || pos2>pos3)
514 return kFALSE;
515
516 user = url(pos1, pos2-pos1-1);
517 pasw = url(pos2, pos3-pos2);
518
519 url.Remove(pos1, pos3+1-pos1);
520
521 return kTRUE;
522}
523
524void MSQLServer::Init(const char *connection, const char *user, const char *password) /*FOLD00*/
525{
526 fType = kIsZombie;
527
528 fServ = TSQLServer::Connect(connection, user, password);
529 if (fServ)
530 {
531 gROOT->GetListOfBrowsables()->Add(this, connection);
532 fType = kIsServer;
533 }
534 else
535 SetBit(kZombie);
536
537 fList.SetOwner();
538}
539
540void MSQLServer::InitEnv(TEnv &env, const char *prefix)
541{
542 TString url = env.GetValue("URL", "");
543 TString db = env.GetValue("Database", "");
544 TString user = env.GetValue("User", "");
545 TString pass = env.GetValue("Password", "");
546
547 user = env.GetValue(Form("%s.User", db.Data()), user);
548
549 pass = env.GetValue(Form("%s.Password", user.Data()), pass);
550 pass = env.GetValue(Form("%s.%s.Password", db.Data(), user.Data()), pass);
551
552 if (prefix)
553 {
554 url = env.GetValue(Form("%s.URL", prefix), url);
555 db = env.GetValue(Form("%s.Database", prefix), db);
556
557 user = env.GetValue(Form("%s.User", prefix), user);
558 user = env.GetValue(Form("%s.%s.User", prefix, db.Data()), user);
559
560 pass = env.GetValue(Form("%s.Password", prefix), pass);
561 pass = env.GetValue(Form("%s.%s.Password", prefix, user.Data()), pass);
562 pass = env.GetValue(Form("%s.%s.%s.Password", prefix, db.Data(), user.Data()), pass);
563 }
564
565 if (user.IsNull() && pass.IsNull())
566 {
567 if (!Split(url, user, pass))
568 {
569 SetBit(kIsZombie);
570 return;
571 }
572 }
573
574 Init(url, user, pass);
575
576 if (IsConnected() && !db.IsNull())
577 SelectDataBase(db);
578}
579
580MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
581{
582 Init(connection, user, password);
583}
584
585MSQLServer::MSQLServer(const char *u) : fType(kIsZombie) /*FOLD00*/
586{
587 TString url(u);
588 TString user, pasw;
589
590 if (!Split(url, user, pasw))
591 {
592 SetBit(kIsZombie);
593 return;
594 }
595 Init(url, user, pasw);
596}
597
598MSQLServer::MSQLServer(TEnv &env, const char *prefix)
599{
600 InitEnv(env, prefix);
601}
602
603MSQLServer::MSQLServer()
604{
605 if (gEnv)
606 InitEnv(*gEnv);
607}
608
609MSQLServer::~MSQLServer() /*FOLD00*/
610{
611 Close();
612 if (gDebug>0)
613 cout << "Delete: " << GetName() << endl;
614}
615
616Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
617{
618 cout << "Fatal error acessing database: " << txt << endl;
619 cout << "Query: " << q << endl;
620 return kFALSE;
621}
622
623TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
624{
625 if (!fServ)
626 return "";
627
628 if (table==0)
629 table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
630 if (col==0)
631 col = (const char *)fColumn;
632
633 const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));
634
635 TSQLResult *res = fServ->Query(query);
636 if (!res)
637 return (PrintError("GetEntry - TSQLResult==NULL", query), "");
638
639 if (res->GetFieldCount()!=1)
640 {
641 delete res;
642 return (PrintError("GetEntry - Number of columns != 1", query), "");
643 }
644
645 if (res->GetRowCount()>1)
646 {
647 delete res;
648 return (PrintError("GetEntry - Number of rows > 1", query), "");
649 }
650
651 if (res->GetRowCount()==0)
652 {
653 delete res;
654 return "";
655 }
656
657 const char *fld = res->Next()->GetField(0);
658 if (!fld)
659 {
660 delete res;
661 return (PrintError("GetEntry - Entry is empty", query), "");
662 }
663
664 const TString rc(fld);
665 delete res;
666 return rc;
667}
Note: See TracBrowser for help on using the repository browser.