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

Last change on this file since 7128 was 7108, checked in by tbretz, 20 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 return fType==kIsServer ? fServ->SelectDataBase(dbname) : 0;
447}
448
449TSQLResult *MSQLServer::GetDataBases(const char *wild) /*FOLD00*/
450{
451 return fType==kIsServer ? fServ->GetDataBases(wild) : NULL;
452}
453
454TSQLResult *MSQLServer::GetTables(const char *dbname, const char *wild) /*FOLD00*/
455{
456 return fType==kIsServer ? fServ->GetTables(dbname, wild) : NULL;
457}
458
459TSQLResult *MSQLServer::GetColumns(const char *dbname, const char *table, const char *wild) /*FOLD00*/
460{
461 return fType==kIsServer ? fServ->GetColumns(dbname, table, wild) : NULL;
462}
463
464Int_t MSQLServer::CreateDataBase(const char *dbname) /*FOLD00*/
465{
466 return fType==kIsServer ? fServ->CreateDataBase(dbname) : 0;
467}
468
469Int_t MSQLServer::DropDataBase(const char *dbname) /*FOLD00*/
470{
471 return fType==kIsServer ? fServ->DropDataBase(dbname) : 0;
472}
473
474Int_t MSQLServer::Reload() /*FOLD00*/
475{
476 return fType==kIsServer ? fServ->Reload() : 0;
477}
478
479Int_t MSQLServer::Shutdown() /*FOLD00*/
480{
481 return fType==kIsServer ? fServ->Shutdown() : 0;
482}
483
484const char *MSQLServer::ServerInfo() /*FOLD00*/
485{
486 return fType==kIsServer ? fServ->ServerInfo() : "";
487}
488
489Bool_t MSQLServer::IsConnected() const
490{
491 return fType==kIsServer ? fServ->IsConnected() : kFALSE;
492}
493
494const char *MSQLServer::GetName() const
495{
496 switch (fType)
497 {
498 case kIsServer: return Form("%s://%s:%d", fServ->GetDBMS(), fServ->GetHost(), fServ->GetPort());
499 case kIsDataBase: return GetNameDataBase();
500 case kIsTable: return GetNameTable();
501 case kIsColumn: return GetNameColumn();
502 default: return "n/a";
503 }
504}
505
506Bool_t MSQLServer::Split(TString &url, TString &user, TString &pasw) const
507{
508 const Ssiz_t pos1 = url.First("://")+3;
509 const Ssiz_t pos2 = url.Last(':') +1;
510 const Ssiz_t pos3 = url.First('@');
511
512 if (pos1<0 || pos2<0 || pos3<0 || pos1>pos2 || pos2>pos3)
513 return kFALSE;
514
515 user = url(pos1, pos2-pos1-1);
516 pasw = url(pos2, pos3-pos2);
517
518 url.Remove(pos1, pos3+1-pos1);
519
520 return kTRUE;
521}
522
523void MSQLServer::Init(const char *connection, const char *user, const char *password) /*FOLD00*/
524{
525 fType = kIsZombie;
526
527 fServ = TSQLServer::Connect(connection, user, password);
528 if (fServ)
529 {
530 gROOT->GetListOfBrowsables()->Add(this, connection);
531 fType = kIsServer;
532 }
533 else
534 SetBit(kZombie);
535
536 fList.SetOwner();
537}
538
539void MSQLServer::InitEnv(TEnv &env, const char *prefix)
540{
541 TString url;
542 TString user;
543 TString pass;
544 TString db;
545
546 if (prefix)
547 {
548 url = env.GetValue(Form("%s.URL", prefix), "");
549 user = env.GetValue(Form("%s.User", prefix), "");
550 pass = env.GetValue(Form("%s.Password", prefix), "");
551 db = env.GetValue(Form("%s.Database", prefix), "");
552 }
553 if (url.IsNull())
554 url = env.GetValue("URL", "");
555 if (user.IsNull())
556 user = env.GetValue("User", "");
557 if (pass.IsNull())
558 pass = env.GetValue("Password", "");
559 if (db.IsNull())
560 db = env.GetValue("Database", "");
561
562 if (user.IsNull() && pass.IsNull())
563 {
564 if (!Split(url, user, pass))
565 {
566 SetBit(kIsZombie);
567 return;
568 }
569 }
570
571 Init(url, user, pass);
572
573 if (IsConnected() && !db.IsNull())
574 SelectDataBase(db);
575}
576
577MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
578{
579 Init(connection, user, password);
580}
581
582MSQLServer::MSQLServer(const char *u) : fType(kIsZombie) /*FOLD00*/
583{
584 TString url(u);
585 TString user, pasw;
586
587 if (!Split(url, user, pasw))
588 {
589 SetBit(kIsZombie);
590 return;
591 }
592 Init(url, user, pasw);
593}
594
595MSQLServer::MSQLServer(TEnv &env, const char *prefix)
596{
597 InitEnv(env, prefix);
598}
599
600MSQLServer::MSQLServer()
601{
602 if (gEnv)
603 InitEnv(*gEnv);
604}
605
606MSQLServer::~MSQLServer() /*FOLD00*/
607{
608 Close();
609 if (gDebug>0)
610 cout << "Delete: " << GetName() << endl;
611}
612
613Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
614{
615 cout << "Fatal error acessing database: " << txt << endl;
616 cout << "Query: " << q << endl;
617 return kFALSE;
618}
619
620TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
621{
622 if (!fServ)
623 return "";
624
625 if (table==0)
626 table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
627 if (col==0)
628 col = (const char *)fColumn;
629
630 const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));
631
632 TSQLResult *res = fServ->Query(query);
633 if (!res)
634 return (PrintError("GetEntry - TSQLResult==NULL", query), "");
635
636 if (res->GetFieldCount()!=1)
637 {
638 delete res;
639 return (PrintError("GetEntry - Number of columns != 1", query), "");
640 }
641
642 if (res->GetRowCount()>1)
643 {
644 delete res;
645 return (PrintError("GetEntry - Number of rows > 1", query), "");
646 }
647
648 if (res->GetRowCount()==0)
649 {
650 delete res;
651 return "";
652 }
653
654 const char *fld = res->Next()->GetField(0);
655 if (!fld)
656 {
657 delete res;
658 return (PrintError("GetEntry - Entry is empty", query), "");
659 }
660
661 const TString rc(fld);
662 delete res;
663 return rc;
664}
Note: See TracBrowser for help on using the repository browser.