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

Last change on this file since 3381 was 3328, checked in by tbretz, 22 years ago
*** empty log message ***
File size: 12.4 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////////////////////////////////////////////////////////////////////////
34#include "MSQLServer.h"
35
36#include <iostream>
37#include <iomanip>
38
39#include <TROOT.h>
40
41#include <TH1.h>
42
43#include <TSQLResult.h>
44#include <TSQLServer.h>
45#include <TSQLRow.h>
46
47#include <TBrowser.h>
48
49ClassImp(MSQLServer);
50
51using namespace std;
52
53// --------------------------------------------------------------------------
54//
55// Used in virtual function TObject::Browse() to create the
56//
57void MSQLServer::BrowseColumn(TBrowser *b) /*FOLD00*/
58{
59 const TString query0(Form("EXPLAIN %s.%s %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
60 const TString query1(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
61
62 //cout << query0 << endl;
63 TSQLResult *res = fServ->Query(query0);
64 if (!res)
65 {
66 cout << "query - failed: " << query0 << endl;
67 return;
68 }
69
70 TSQLRow *row=res->Next();
71 const TString desc((*row)[1]);
72
73 //cout << "Column Type: " << desc << endl;
74
75 const Bool_t isnum =
76 !desc.Contains("char", TString::kIgnoreCase) &&
77 !desc.Contains("text", TString::kIgnoreCase);
78
79 cout << query1 << endl;
80 res = fServ->Query(query1);
81 if (!res)
82 {
83 cout << "query - failed: " << query1 << endl;
84 return;
85 }
86
87 TArrayD arr(2);
88 Int_t num=0;
89 Double_t max=0;
90 Double_t min=0;
91 Double_t sum=0;
92 Double_t sqr=0;
93
94 while ((row=res->Next()))
95 {
96 const TString row0((*row)[0]);
97
98 if (!isnum)
99 {
100 cout << row0 << endl;
101 continue;
102 }
103
104 if (num==arr.GetSize())
105 arr.Set(arr.GetSize()*2);
106
107 arr[num] = atof(row0.Data());
108
109 if (num==0)
110 min=max=arr[0];
111
112 if (arr[num]>max) max = arr[num];
113 if (arr[num]<min) min = arr[num];
114
115 sum += arr[num];
116 sqr += arr[num]*arr[num];
117
118 num++;
119 }
120
121 if (!isnum)
122 return;
123
124 if (max==min) max += 1;
125
126 Int_t num0 = 1;
127
128 if (num>0)
129 {
130 /*
131 cout << "Num: " << num << endl;
132 cout << "Mean: " << sum/num << endl;
133 cout << "Range: " << max-min << endl;
134 cout << "RMS: " << TMath::Sqrt(sqr/num-sum*sum/num/num) << endl;
135 */
136
137 num0 = (Int_t)((max-min)*40/TMath::Sqrt(sqr/num-sum*sum/num/num));
138 }
139
140 const TString title(Form("#splitline{%s}{<%s>}", (const char*)query1, (const char*)desc));
141
142 TH1F *hist=new TH1F(fColumn, title, num0, min, max);
143 for (int i=0; i<num; i++)
144 hist->Fill(arr[i]);
145
146 //cout << "Done." << endl;
147
148 hist->Draw();
149 hist->SetBit(kCanDelete);
150}
151
152void MSQLServer::BrowseTable(TBrowser *b) /*FOLD00*/
153{
154 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
155 if (!res)
156 return;
157
158 TSQLRow *row;
159 while ((row=res->Next()))
160 {
161 TString row0((*row)[0]);
162
163 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s/%s", (const char*)fDataBase, (const char*)fTable, (const char*)row0));
164 if (!sql)
165 {
166 sql = new MSQLColumn(fServ, fDataBase, fTable, row0);
167 fList.Add(sql);
168 }
169 b->Add(sql, row0);
170 }
171}
172
173void MSQLServer::BrowseDataBase(TBrowser *b) /*FOLD00*/
174{
175 TSQLResult *res = fServ->GetTables(fDataBase);
176 if (!res)
177 return;
178
179 TSQLRow *row;
180 while ((row=res->Next()))
181 {
182 TString row0((*row)[0]);
183
184 MSQLServer *sql = (MSQLServer*)fList.FindObject(Form("%s/%s", (const char*)fDataBase, (const char*)row0));
185 if (!sql)
186 {
187 sql = new MSQLServer(fServ, fDataBase, row0);
188 fList.Add(sql);
189 }
190 b->Add(sql, row0);
191 }
192}
193
194void MSQLServer::BrowseServer(TBrowser *b) /*FOLD00*/
195{
196 TSQLResult *res = fServ->GetDataBases();
197 if (!res)
198 return;
199
200 TSQLRow *row;
201 while ((row=res->Next()))
202 {
203 const TString row0((*row)[0]);
204
205 MSQLServer *sql = (MSQLServer*)fList.FindObject(row0);
206 if (!sql)
207 {
208 sql = new MSQLServer(fServ, row0);
209 fList.Add(sql);
210 }
211 b->Add(sql, row0);
212 }
213}
214
215void MSQLServer::PrintLine(const TArrayI &max) const /*FOLD00*/
216{
217 cout << "+" << setfill('-');
218 for (int i=0; i<max.GetSize(); i++)
219 cout << setw(max[i]+1) << "-" << "-+";
220 cout << endl;
221}
222
223void MSQLServer::PrintTable(TSQLResult *res) const /*FOLD00*/
224{
225 Int_t n = res->GetFieldCount();
226
227 TArrayI max(n);
228
229 for (int i=0; i<n; i++)
230 max[i] = strlen(res->GetFieldName(i));
231
232 TSQLRow *row;
233
234 TList rows;
235 while ((row=res->Next()))
236 {
237 for (int i=0; i<n; i++)
238 max[i] = TMath::Max((ULong_t)max[i], row->GetFieldLength(i));
239 rows.Add(row);
240 }
241
242 cout << endl;
243
244 PrintLine(max);
245
246 cout << "|" << setfill(' ');
247 for (int i=0; i<n; i++)
248 cout << setw(max[i]+1) << res->GetFieldName(i) << " |";
249 cout << endl;
250
251 PrintLine(max);
252
253 cout << setfill(' ');
254 TIter Next(&rows);
255 while ((row=(TSQLRow*)Next()))
256 {
257 cout << "|";
258 for (int i=0; i<n; i++)
259 {
260 const char *c = (*row)[i];
261 cout << setw(max[i]+1) << (c?c:"") << " |";
262 }
263 cout << endl;
264 }
265
266 PrintLine(max);
267}
268
269TString MSQLServer::GetFields() const /*FOLD00*/
270{
271 TSQLResult *res = fServ->GetColumns(fDataBase, fTable);
272 if (!res)
273 return "";
274
275 TString fields;
276
277 TSQLRow *row;
278
279 TList rows;
280 while ((row=res->Next()))
281 rows.Add(row);
282
283 TIter Next(&rows);
284 while ((row=(TSQLRow*)Next()))
285 {
286 fields += (*row)[0];
287 if (row!=rows.Last())
288 fields += ", ";
289 }
290
291 return fields;
292}
293
294void MSQLServer::PrintQuery(const char *query) const /*FOLD00*/
295{
296 TSQLResult *res = fServ->Query(query);
297 if (res)
298 PrintTable(res);
299 else
300 cout << "Query failed: " << query << endl;
301}
302
303void MSQLServer::Print(Option_t *o) const /*FOLD00*/
304{
305 switch (fType)
306 {
307 case kIsServer:
308 PrintQuery("SHOW DATABASES");
309 break;
310
311 case kIsDataBase:
312 PrintQuery(Form("SHOW TABLES FROM %s", (const char*)fDataBase));
313 break;
314
315 case kIsTable:
316 PrintQuery(Form("SELECT * FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
317 break;
318
319 case kIsColumn:
320 PrintQuery(Form("SELECT %s FROM %s.%s", (const char*)fColumn, (const char*)fDataBase, (const char*)fTable));
321 break;
322
323 default:
324 break;
325 }
326}
327
328void MSQLServer::ShowColumns() const /*FOLD00*/
329{
330 switch (fType)
331 {
332 case kIsTable:
333 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
334 break;
335
336 case kIsColumn:
337 PrintQuery(Form("SHOW FULl COLUMNS FROM %s.%s LIKE %s", (const char*)fDataBase, (const char*)fTable, (const char*)fColumn));
338 break;
339
340 default:
341 //Print();
342 break;
343 }
344}
345
346void MSQLServer::ShowStatus() const /*FOLD00*/
347{
348 switch (fType)
349 {
350 case kIsServer:
351 PrintQuery("SHOW STATUS");
352 break;
353
354 case kIsDataBase:
355 PrintQuery(Form("SHOW TABLE STATUS FROM %s", (const char*)fDataBase));
356 break;
357
358 case kIsTable:
359 PrintQuery(Form("SHOW TABLE STATUS FROM %s LIKE %s", (const char*)fDataBase, (const char*)fTable));
360 break;
361
362 default:
363 break;
364 }
365}
366
367void MSQLServer::ShowTableIndex() const /*FOLD00*/
368{
369 switch (fType)
370 {
371 case kIsTable:
372 case kIsColumn:
373 PrintQuery(Form("SHOW INDEX FROM %s.%s", (const char*)fDataBase, (const char*)fTable));
374 break;
375
376 default:
377 break;
378 }
379}
380
381void MSQLServer::ShowTableCreate() const /*FOLD00*/
382{
383 switch (fType)
384 {
385 case kIsTable:
386 case kIsColumn:
387 PrintQuery(Form("SHOW CREATE TABLE %s.%s", (const char*)fDataBase, (const char*)fTable));
388 break;
389
390 default:
391 break;
392 }
393}
394
395void MSQLServer::Close(Option_t *option) /*FOLD00*/
396{
397 if (fType==kIsServer)
398 fServ->Close(option);
399}
400
401TSQLResult *MSQLServer::Query(const char *sql) /*FOLD00*/
402{
403 return fType==kIsServer ? fServ->Query(sql) : NULL;
404}
405
406Int_t MSQLServer::SelectDataBase(const char *dbname) /*FOLD00*/
407{
408 return fType==kIsServer ? fServ->SelectDataBase(dbname) : 0;
409}
410
411TSQLResult *MSQLServer::GetDataBases(const char *wild) /*FOLD00*/
412{
413 return fType==kIsServer ? fServ->GetDataBases(wild) : NULL;
414}
415
416TSQLResult *MSQLServer::GetTables(const char *dbname, const char *wild) /*FOLD00*/
417{
418 return fType==kIsServer ? fServ->GetTables(dbname, wild) : NULL;
419}
420
421TSQLResult *MSQLServer::GetColumns(const char *dbname, const char *table, const char *wild) /*FOLD00*/
422{
423 return fType==kIsServer ? fServ->GetColumns(dbname, table, wild) : NULL;
424}
425
426Int_t MSQLServer::CreateDataBase(const char *dbname) /*FOLD00*/
427{
428 return fType==kIsServer ? fServ->CreateDataBase(dbname) : 0;
429}
430
431Int_t MSQLServer::DropDataBase(const char *dbname) /*FOLD00*/
432{
433 return fType==kIsServer ? fServ->DropDataBase(dbname) : 0;
434}
435
436Int_t MSQLServer::Reload() /*FOLD00*/
437{
438 return fType==kIsServer ? fServ->Reload() : 0;
439}
440
441Int_t MSQLServer::Shutdown() /*FOLD00*/
442{
443 return fType==kIsServer ? fServ->Shutdown() : 0;
444}
445
446const char *MSQLServer::ServerInfo() /*FOLD00*/
447{
448 return fType==kIsServer ? fServ->ServerInfo() : "";
449}
450
451void MSQLServer::Init(const char *connection, const char *user, const char *password) /*FOLD00*/
452{
453 fServ = TSQLServer::Connect(connection, user, password);
454 if (fServ)
455 {
456 gROOT->GetListOfBrowsables()->Add(this, connection);
457 fType = kIsServer;
458 }
459 else
460 fType = kIsZombie;
461
462 fList.SetOwner();
463}
464
465MSQLServer::MSQLServer(const char *connection, const char *user, const char *password) /*FOLD00*/
466{
467 Init(connection, user, password);
468}
469
470MSQLServer::MSQLServer(const char *u) /*FOLD00*/
471{
472 TString url(u);
473
474 const Ssiz_t pos1 = url.First("://")+3;
475 const Ssiz_t pos2 = url.Last(':') +1;
476 const Ssiz_t pos3 = url.First('@');
477
478 if (pos1<0 || pos2<0 || pos3<0 || pos1>pos2 || pos2>pos3)
479 return;
480
481 const TString user = url(pos1, pos2-pos1-1);
482 const TString pasw = url(pos2, pos3-pos2);
483
484 url.Remove(pos1, pos3+1-pos1);
485
486 Init(url, user, pasw);
487
488}
489
490MSQLServer::~MSQLServer() /*FOLD00*/
491{
492 Close();
493 cout << "Delete: " << GetName() << endl;
494}
495
496Bool_t MSQLServer::PrintError(const char *txt, const char *q) const /*FOLD00*/
497{
498 cout << "Fatal error acessing database: " << txt << endl;
499 cout << "Query: " << q << endl;
500 return kFALSE;
501}
502
503TString MSQLServer::GetEntry(const char *where, const char *col, const char *table) const /*FOLD00*/
504{
505 if (!fServ)
506 return "";
507
508 if (table==0)
509 table = Form("%s.%s", (const char *)fDataBase, (const char*)fTable);
510 if (col==0)
511 col = (const char *)fColumn;
512
513 const TString query(Form("SELECT %s FROM %s WHERE %s", col, table, where));
514
515 TSQLResult *res = fServ->Query(query);
516 if (!res)
517 return (PrintError("GetEntry - TSQLResult==NULL", query), "");
518
519 if (res->GetFieldCount()!=1)
520 return (PrintError("GetEntry - Number of columns != 1", query), "");
521
522 if (res->GetRowCount()>1)
523 return (PrintError("GetEntry - Number of rows > 1", query), "");
524
525 if (res->GetRowCount()==0)
526 return "";
527
528 const char *fld = res->Next()->GetField(0);
529 if (!fld)
530 return (PrintError("GetEntry - Entry is empty", query), "");
531
532 return TString(fld);
533}
Note: See TracBrowser for help on using the repository browser.