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

Last change on this file since 7758 was 7404, checked in by tbretz, 19 years ago
*** empty log message ***
File size: 15.2 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;
543 TString user;
544 TString pass;
545 TString db;
546
547 if (prefix)
548 {
549 url = env.GetValue(Form("%s.URL", prefix), "");
550 user = env.GetValue(Form("%s.User", prefix), "");
551 pass = env.GetValue(Form("%s.Password", prefix), "");
552 db = env.GetValue(Form("%s.Database", prefix), "");
553 }
554 if (url.IsNull())
555 url = env.GetValue("URL", "");
556 if (user.IsNull())
557 user = env.GetValue("User", "");
558 if (pass.IsNull())
559 pass = env.GetValue("Password", "");
560 if (db.IsNull())
561 db = env.GetValue("Database", "");
562
563 if (user.IsNull() && pass.IsNull())
564 {
565 if (!Split(url, user, pass))
566 {
567 SetBit(kIsZombie);
568 return;
569 }
570 }
571
572 Init(url, user, pass);
573
574 if (IsConnected() && !db.IsNull())
575 SelectDataBase(db);
576}
577
578MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
579{
580 Init(connection, user, password);
581}
582
583MSQLServer::MSQLServer(const char *u) : fType(kIsZombie) /*FOLD00*/
584{
585 TString url(u);
586 TString user, pasw;
587
588 if (!Split(url, user, pasw))
589 {
590 SetBit(kIsZombie);
591 return;
592 }
593 Init(url, user, pasw);
594}
595
596MSQLServer::MSQLServer(TEnv &env, const char *prefix)
597{
598 InitEnv(env, prefix);
599}
600
601MSQLServer::MSQLServer()
602{
603 if (gEnv)
604 InitEnv(*gEnv);
605}
606
607MSQLServer::~MSQLServer() /*FOLD00*/
608{
609 Close();
610 if (gDebug>0)
611 cout << "Delete: " << GetName() << endl;
612}
613
614Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
615{
616 cout << "Fatal error acessing database: " << txt << endl;
617 cout << "Query: " << q << endl;
618 return kFALSE;
619}
620
621TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
622{
623 if (!fServ)
624 return "";
625
626 if (table==0)
627 table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
628 if (col==0)
629 col = (const char *)fColumn;
630
631 const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));
632
633 TSQLResult *res = fServ->Query(query);
634 if (!res)
635 return (PrintError("GetEntry - TSQLResult==NULL", query), "");
636
637 if (res->GetFieldCount()!=1)
638 {
639 delete res;
640 return (PrintError("GetEntry - Number of columns != 1", query), "");
641 }
642
643 if (res->GetRowCount()>1)
644 {
645 delete res;
646 return (PrintError("GetEntry - Number of rows > 1", query), "");
647 }
648
649 if (res->GetRowCount()==0)
650 {
651 delete res;
652 return "";
653 }
654
655 const char *fld = res->Next()->GetField(0);
656 if (!fld)
657 {
658 delete res;
659 return (PrintError("GetEntry - Entry is empty", query), "");
660 }
661
662 const TString rc(fld);
663 delete res;
664 return rc;
665}
Note: See TracBrowser for help on using the repository browser.