source: trunk/MagicSoft/Mars/datacenter/macros/setupdb.C@ 7428

Last change on this file since 7428 was 7427, checked in by Daniela Dorner, 19 years ago
*** empty log message ***
File size: 31.6 KB
Line 
1#include <iomanip.h>
2/*
3Statements
4
5SELECT [ DISTINCT ] * | LIST OF COLUMNS, FUNCTIONS, CONSTANTS
6 FROM LIST OF TABLES OR VIEWS
7 [ WHERE CONDITION(S) ]
8 [ ORDER BY ORDERING COLUMN(S) [ ASC | DESC ] ]
9 [ GROUP BY GROUPING COLUMN(S) ]
10 [ HAVING CONDITION(S) ]
11
12DELETE FROM TABLE NAME
13 [ WHERE CONDITION(S) ]
14
15INSERT INTO TABLE NAME
16 [ (COLUMN LIST) ]
17 VALUES (VALUE LIST)
18
19UPDATE TABLE NAME
20 SET COLUMN NAME = VALUE
21 [ WHERE CONDITION ]
22
23Functions
24
25Function Purpose
26SUM Total of the values in a field.
27AVG Average of the values in a field.
28MIN Lowest value in a field.
29MAX Highest value in a field.
30COUNT Number of values in a field, not counting Null (blank) values.
31
32Predicates
33
34Predicate Description
35BETWEEN ... AND Compares a value to a range formed by two values.
36IN Determines whether a value exists in a list of values or a table.
37LIKE Compares, in part or in whole, one value with another.
38JOIN Joins two tables.
39
40Data Definition
41
42CREATE TABLE TABLE_NAME
43 ( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
44 [, other column definitions,...]
45 [, primary key constraint]
46 )
47
48ALTER TABLE TABLE_NAME ADD | DROP | MODIFY
49 ( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
50 [, other column definitions,...]
51 )
52
53DROP TABLE TABLE_NAME
54
55CREATE [UNIQUE] [ASC | DESC] INDEX INDEX_NAME
56 ON TABLE_NAME ( COLUMN_LIST )
57
58DROP INDEX INDEX_NAME ON TABLE_NAME
59
60CREATE VIEW VIEW_NAME AS QUERY_NAME
61
62CONSTRAINT CONSTRAINT_NAME
63 {PRIMARY KEY | UNIQUE | NOT NULL |
64 REFERENCES FOREIGN_TABLE [(FIELD_LIST)]}
65
66 */
67
68void Line(const TArrayI &max)
69{
70 cout << "+" << setfill('-');
71 for (int i=0; i<max.GetSize(); i++)
72 cout << setw(max[i]+1) << "-" << "-+";
73 cout << endl;
74}
75
76void Print(TSQLResult *res)
77{
78 Int_t n = res->GetFieldCount();
79
80 TArrayI max(n);
81
82 for (int i=0; i<n; i++)
83 max[i] = strlen(res->GetFieldName(i));
84
85 TSQLRow *row;
86
87 TList rows;
88 while (row=res->Next())
89 {
90 for (int i=0; i<n; i++)
91 max[i] = TMath::Max(max[i], row->GetFieldLength(i));
92 rows.Add(row);
93 }
94
95 Line(max);
96
97 cout << "|" << setfill(' ');
98 for (int i=0; i<n; i++)
99 cout << setw(max[i]+1) << res->GetFieldName(i) << " |";
100 cout << endl;
101
102 Line(max);
103
104 cout << setfill(' ');
105 TIter Next(&rows);
106 while (row=(TSQLRow*)Next())
107 {
108 cout << "|";
109 for (int i=0; i<n; i++)
110 {
111 char *c = (*row)[i];
112 cout << setw(max[i]+1) << (c?c:"") << " |";
113 }
114 cout << endl;
115 }
116
117 Line(max);
118}
119
120TString GetFields(TSQLServer *serv, const char *db, const char *table)
121{
122 res = serv->GetColumns(db, table);
123 if (!res)
124 return "";
125
126 TString fields;
127
128 TList rows;
129 while (row=res->Next())
130 rows.Add(row);
131
132 TIter Next(&rows);
133 while (row=(TSQLRow*)Next())
134 {
135 fields += (*row)[0];
136 if (row!=rows.Last())
137 fields += ", ";
138 }
139
140 return fields;
141}
142
143void PrintContents(TSQLServer *serv, const char *db, const char *table)
144{
145 TString fields=GetFields(serv, db, table);
146
147 TSQLResult *res = serv->Query(Form("SELECT %s FROM %s", fields.Data(), table));
148 if (res)
149 {
150 Print(res);
151 delete res;
152 }
153}
154
155/*
156 GetTables(db):
157 \u "db"
158 SHOW TABLES;
159
160 GetDataBases();
161 SHOW DATABASES;
162
163 GetColumns(db, table);
164 EXPLAIN table;
165 DESCRIBE table;
166
167 SHOW VARIABLES;
168
169 PRIMARY_KEY impliziert NOT NULL
170
171 */
172
173void CreatePrimaryEntries()
174{
175 TList list;
176 list.SetOwner();
177
178 // Trigger tables
179 list.Add(new TObjString(
180 "INSERT MyMagic.L1TriggerTable (fL1TriggerTableKEY, fL1TriggerTableName, fL1TriggerTable) VALUES "
181 " (1, 'n/a', 'Not available')"));
182
183 list.Add(new TObjString(
184 "INSERT MyMagic.L2TriggerTable (fL2TriggerTableKEY, fL2TriggerTableName, fL2TriggerTable) VALUES "
185 " (1, 'n/a', 'Not available')"));
186
187 // File path
188 list.Add(new TObjString(
189 "INSERT MyMagic.FilePath (fFilePathName, fFilePath) VALUES "
190 " ('n/a', 'Not available in the Data Center')"));
191
192 // Magic number
193 list.Add(new TObjString(
194 "INSERT MyMagic.MagicNumber (fMagicNumber, fMagicNumberName) VALUES "
195 " (0x0001, 'Not available')," //1
196 " (0xc0c0, 'Ok')," //45344
197 " (0xc0c1, 'Not closed')," //45345
198 " (0x0000, 'Wrong')")); //0
199
200 // Run type
201 list.Add(new TObjString(
202 "INSERT MyMagic.RunType (fRunType, fRunTypeName) VALUES "
203 " (0xffff, 'Not available'),"
204 " (0x0000, 'Data'),"
205 " (0x0001, 'Pedestal'),"
206 " (0x0002, 'Calibration'),"
207 " (0x0007, 'Point Run'),"
208 " (0x0100, 'Monte Carlo')"));
209
210 // HvSettings
211 list.Add(new TObjString(
212 "INSERT MyMagic.HvSettings (fHvSettingsKEY, fHvSettingsName, fHvSettings) VALUES "
213 " (1, 'n/a', 'Not available')"));
214
215 //Excluded From DataAnalysis
216 list.Add(new TObjString(
217 "INSERT MyMagic.ExcludedFDA (fExcludedFDAKEY, fExcludedFDAName, fExcludedFDA) VALUES "
218 " (1, \"Not excluded\", \"Not excluded from Data Analysis\")"));
219
220 //Manually Changed
221 list.Add(new TObjString(
222 "INSERT MyMagic.ManuallyChanged (fManuallyChangedKEY, fManuallyChangedName, fManuallyChanged) VALUES "
223 " (1, \"Automatic\", \"This entry was created automatically without user intervention\")");
224
225 // Source type
226 list.Add(new TObjString(
227 "INSERT MyMagic.SourceType (fSourceTypeName) VALUES ('Unknown')"));
228
229 // LightConditions
230 list.Add(new TObjString(
231 "INSERT MyMagic.LightConditions (fLightConditionsKEY, fLightConditionsName, fLightConditions) VALUES "
232 " (1, 'n/a', 'Light conditions are not available')"));
233
234 // Testflag
235 list.Add(new TObjString(
236 "INSERT MyMagic.TestFlag (fTestFlagKEY, fTestFlagName, fTestFlag) VALUES "
237 " (1, 'n/a', 'Testflag is not available')"));
238
239 // Trigger delay table
240 list.Add(new TObjString(
241 "INSERT MyMagic.TriggerDelayTable (fTriggerDelayTableKEY, fTriggerDelayTableName, fTriggerDelayTable) VALUES "
242 " (1, 'n/a', 'Trigger delay table is not available')"));
243
244 // Calibration script
245 list.Add(new TObjString(
246 "INSERT MyMagic.CalibrationScript (fCalibrationScriptKEY, fCalibrationScriptName, fCalibrationScript) VALUES "
247 " (1, 'n/a', 'Calibration script is not available')"));
248
249 // discriminator threshold table
250 list.Add(new TObjString(
251 "INSERT MyMagic.DiscriminatorThresholdTable (fDiscriminatorThresholdTableKEY, fDiscriminatorThresholdTableName, fDiscriminatorThresholdTable) VALUES "
252 " (1, 'n/a', 'Discriminator threshold table is not available')"));
253
254
255 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
256 if (!serv)
257 return;
258
259 TIter Next(&list);
260 TObjString *str;
261
262 cout << "Filling tables..." << endl;
263
264 while ((str=(TObjString*)Next()))
265 {
266 TString q(str->GetString());
267
268 Int_t f = q.First('(');
269 TString out = q(0, f);
270 cout << " - " << out << "... " << flush;
271 TSQLResult *res = serv->Query(q);
272 cout << (res==0 ? "ERROR!" : "Ok.") << endl;
273 if (res)
274 delete res;
275 }
276
277 serv->Close();
278 delete serv;
279}
280
281TObjString *CreateKeyTable(TString name)
282{
283 return new TObjString(Form(
284 "CREATE TABLE MyMagic.%s ("
285 " f%sKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
286 " f%sName VARCHAR(255) NOT NULL UNIQUE,"
287 " f%s VARCHAR(255) NULL"
288 ") MAX_ROWS=65536", name.Data(), name.Data(), name.Data(), name.Data()));
289}
290
291void CreateMagicDataBase()
292{
293 TList list;
294 list.SetOwner();
295
296 TString query =
297 "CREATE TABLE MyMagic.RunData"
298 "("
299 " fRunDataKEY INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,"// COMMENT('The unique key for each run-file'),";
300 " fRunNumber INT UNSIGNED NOT NULL UNIQUE," // PACK_KEYS=1,";// COMMENT('Run number'),";
301 " fMagicNumberKEY TINYINT UNSIGNED NOT NULL," // PACK_KEYS=1,";// COMMENT('The MAGIC Key (first two bytes of a raw data file)'),";
302 " fFormatVersion SMALLINT UNSIGNED NOT NULL," // PACK_KEYS=1,";// COMMENT('File format version of the raw data file)'),";
303 " fRunTypeKEY TINYINT UNSIGNED NOT NULL," // PACK_KEYS=1,";// COMMENT('Run type'),";
304 " fProjectKEY SMALLINT UNSIGNED NOT NULL," // PACK_KEYS=1,";// COMMENT('Name of the project (assigned by the physics comittee)'),";
305 " fSourceKEY SMALLINT UNSIGNED NOT NULL," // PACK_KEYS=1,";// COMMENT('Name of the source observed'),";
306 " fNumEvents MEDIUMINT UNSIGNED NOT NULL," // COMMENT('Number of events in this run-file'),";
307 " fRunStart DATETIME NOT NULL," // COMMENT('Time when the run was started'),";
308 " fRunStop DATETIME NULL,"; // COMMENT('Time when the run has stopped')";
309 query +=
310 " fZenithDistance TINYINT NULL," // COMMENT('Time of last change'),";
311 " fAzimuth SMALLINT NULL," // COMMENT('Time of last change'),";
312 " fL1TriggerTableKEY SMALLINT UNSIGNED NOT NULL," // COMMENT('Time of last change'),";
313 " fL2TriggerTableKEY SMALLINT UNSIGNED NOT NULL," // COMMENT('Time of last change'),";
314 " fHvSettingsKEY SMALLINT UNSIGNED NOT NULL," // COMMENT('Time of last change'),";
315 " fDaqStoreRate SMALLINT UNSIGNED NULL," // COMMENT('Time of last change'),";
316 " fDaqTriggerRate SMALLINT UNSIGNED NULL," // COMMENT('Time of last change'),";
317 " fMeanTriggerRate SMALLINT UNSIGNED NULL," // COMMENT('Time of last change'),";
318 " fL2RatePresc SMALLINT UNSIGNED NULL," // COMMENT('Time of last change'),";
319 " fL2RateUnpresc SMALLINT UNSIGNED NULL," // COMMENT('Time of last change'),";
320 " fExcludedFDAKEY SMALLINT UNSIGNED NOT NULL,"
321 " fSequenceFirst INT UNSIGNED NOT NULL,";
322 query +=
323 " fLastUpdate TIMESTAMP" // COMMENT('Time of last change'),";
324 " fTestFlagKEY SMALLINT UNSIGNED NOT NULL,"
325 " fLightConditionsKEY SMALLINT UNSIGNED NOT NULL,"
326 " fCalibrationScriptKEY SMALLINT UNSIGNED NOT NULL,"
327 " fDiscriminatorThresholdTableKEY SMALLINT UNSIGNED NOT NULL,"
328 " fTriggerDelayTableKEY SMALLINT UNSIGNED NOT NULL,"
329 ")";
330
331 list.Add(new TObjString(query));
332
333 list.Add(new TObjString(
334 "CREATE TABLE MyMagic.RunType ("
335 " fRunTypeKEY TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
336 " fRunType SMALLINT UNSIGNED NOT NULL UNIQUE,"
337 " fRunTypeName VARCHAR(255) NOT NULL UNIQUE,"
338 ") MAX_ROWS=256"));
339
340 list.Add(new TObjString(
341 "CREATE TABLE MyMagic.MagicNumber ("
342 " fMagicNumberKEY TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
343 " fMagicNumber SMALLINT UNSIGNED NOT NULL UNIQUE,"
344 " fMagicNumberName VARCHAR(255) NOT NULL UNIQUE"
345 ") MAX_ROWS=256"));
346
347 list.Add(new TObjString(
348 "CREATE TABLE MyMagic.Project ("
349 " fProjectKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
350 " fProjectName CHAR(22) NOT NULL UNIQUE,"
351 " fProject VARCHAR(255) NOT NULL"
352 ") MAX_ROWS=65536"));
353
354 list.Add(new TObjString(
355 "CREATE TABLE MyMagic.Source ("
356 " fSourceKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
357 " fSourceName CHAR(12) NOT NULL UNIQUE,"
358 " fSourceTypeKEY SMALLINT UNSIGNED NULL,"
359 " fRightAscension DOUBLE NULL,"
360 " fDeclination DOUBLE NULL,"
361 " fEpochChar CHAR NULL,"// DEFAULT='J'
362 " fEpochDate SMALLINT(4) UNSIGNED NULL,"// DEFAULT=2000
363 " fMagnitude SMALLINT NULL," // 82=8.2
364 " fTest ENUM(\"no\",\"yes\") NOT NULL" //default no
365 ") MAX_ROWS=65536"));
366
367 list.Add(new TObjString(
368 "CREATE TABLE MyMagic.SourceType ("
369 " fSourceTypeKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
370 " fSourceTypeName VARCHAR(255) NOT NULL UNIQUE"
371 ") MAX_ROWS=65536"));
372
373 list.Add(new TObjString(
374 "CREATE TABLE MyMagic.Files ("
375 " fRawFileKEY INT UNSIGNED NOT NULL PRIMARY KEY,"
376 " fRepFileKEY INT UNSIGNED NULL UNIQUE,"
377 " fDccFileKEY INT UNSIGNED NULL UNIQUE"
378 ")"));
379
380 list.Add(new TObjString(
381 "CREATE TABLE MyMagic.RawFile ("
382 " fRawFileKEY INT UNSIGNED PRIMARY KEY,"
383 " fRawFileName VARCHAR(64) NOT NULL UNIQUE," // NULL means - Not in Wuerzburg!
384 " fFilePathKEY SMALLINT UNSIGNED NOT NULL"
385 ")"));
386
387 list.Add(new TObjString(
388 "CREATE TABLE MyMagic.RepFile ("
389 " fRepFileKEY INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
390 " fRepFileName VARCHAR(64) NOT NULL UNIQUE," // NULL means - Not in Wuerzburg!
391 " fFilePathKEY SMALLINT UNSIGNED NOT NULL"
392 ")"));
393
394 list.Add(new TObjString(
395 "CREATE TABLE MyMagic.DccFile ("
396 " fDccFileKEY INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
397 " fDccFileName VARCHAR(64) NOT NULL UNIQUE," // NULL means - Not in Wuerzburg!
398 " fFilePathKEY SMALLINT UNSIGNED NOT NULL"
399 ")"));
400
401 list.Add(new TObjString(
402 "CREATE TABLE MyMagic.FilePath ("
403 " fFilePathKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
404 " fFilePathName VARCHAR(255) NOT NULL UNIQUE,"
405 " fFilePath VARCHAR(255) NOT NULL UNIQUE"
406 ") MAX_ROWS=65536"));
407
408 list.Add(CreateKeyTable("L1TriggerTable"));
409 list.Add(CreateKeyTable("L2TriggerTable"));
410 list.Add(CreateKeyTable("HvSettings"));
411// list.Add(CreateKeyTable("ExcludedFDA"));
412 list.Add(CreateKeyTable("ManuallyChanged"));
413 list.Add(CreateKeyTable("TestFlag"));
414 list.Add(CreateKeyTable("LightConditions"));
415 list.Add(CreateKeyTable("CalibrationScript"));
416 list.Add(CreateKeyTable("DiscriminatorThresholdTable"));
417 list.Add(CreateKeyTable("TriggerDelayTable"));
418
419 list.Add(new TObjString(
420 "CREATE TABLE MyMagic.ExcludedFDA ("
421 " fExcludedFDAKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
422 " fExcludedFDAImportance SMALLINT UNSIGNED NULL,"
423 " fExcludedFDAAutomatic ENUM(\"yes\",\"no\") NULL,"
424 " fExcludedFDAName VARCHAR(255) NOT NULL UNIQUE,"
425 " fExcludedFDA VARCHAR(255) NULL"
426 ") MAX_ROWS=65536"));
427
428
429 list.Add(new TObjString(
430 "CREATE TABLE MyMagic.DataCheck ("
431 " fRunNumber INT UNSIGNED PRIMARY KEY,"
432 " fEvents INT UNSIGNED NULL,"
433 " fPositionSignal TINYINT UNSIGNED NULL,"
434 " fPositionFWHM TINYINT UNSIGNED NULL,"
435 " fHeightSignal SMALLINT UNSIGNED NULL,"
436 " fHeightFWHM SMALLINT UNSIGNED NULL,"
437 " fHasSignal ENUM(\"yes\",\"no\") NULL,"
438 " fHasPedestal ENUM(\"yes\",\"no\") NULL,"
439 " fPositionAsym ENUM(\"yes\",\"no\") NULL,"
440 " fHeightAsym ENUM(\"yes\",\"no\") NULL,"
441 " fEventsInterlaced INT UNSIGNED NULL,"
442 " fPositionSignalInterlaced TINYINT UNSIGNED NULL,"
443 " fPositionFWHMInterlaced TINYINT UNSIGNED NULL,"
444 " fHeightSignalInterlaced SMALLINT UNSIGNED NULL,"
445 " fHeightFWHMInterlaced SMALLINT UNSIGNED NULL,"
446 " fHasSignalInterlaced ENUM(\"yes\",\"no\") NULL,"
447 " fHasPedestalInterlaced ENUM(\"yes\",\"no\") NULL,"
448 " fPositionAsymInterlaced ENUM(\"yes\",\"no\") NULL,"
449 " fHeightAsymInterlaced ENUM(\"yes\",\"no\") NULL"
450 ")"));
451
452
453/*
454 list.Add(new TObjString(
455 "CREATE TABLE MyMagic.TriggerTable ("
456 " fTriggerTableKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
457 " fTriggerTableName VARCHAR(255) NOT NULL UNIQUE,"
458 " fTriggerTable VARCHAR(255) NULL"
459 ") MAX_ROWS=65536"));
460
461 list.Add(new TObjString(
462 "CREATE TABLE MyMagic.HvSettings ("
463 " fHvSettingsKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
464 " fHvSettingsName VARCHAR(255) NOT NULL UNIQUE,"
465 " fHvSettings VARCHAR(255) NULL"
466 ") MAX_ROWS=65536"));
467
468 list.Add(new TObjString(
469 "CREATE TABLE MyMagic.ExcludedFDA ("
470 " fExcludedFDAKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
471 " fExcludedFDAName VARCHAR(255) NOT NULL UNIQUE,"
472 " fExcludedFDA VARCHAR(255) NULL"
473 ") MAX_ROWS=65536"));
474
475 list.Add(new TObjString(
476 "CREATE TABLE MyMagic.ManuallyChanged ("
477 " fManuallyChangedKEY SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
478 " fManuallyChangedName VARCHAR(255) NOT NULL UNIQUE,"
479 " fManuallyChanged VARCHAR(255) NULL"
480 ") MAX_ROWS=65536"));
481 */
482 list.Add(new TObjString(
483 "CREATE TABLE MyMagic.Changes ("
484 " fChangesKEY INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
485 " fTimeStamp TIMESTAMP,"
486 " fTableName VARCHAR(64) NOT NULL,"
487 " fRowKEY INT UNSIGNED NOT NULL,"
488 " fColumnName VARCHAR(64) NOT NULL,"
489 " fDescription VARCHAR(255) NOT NULL"
490 ")"));
491
492 list.Add(new TObjString(
493 "CREATE TABLE MyMagic.Comments ("
494 " fCommentsKEY INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
495 " fCommentsName VARCHAR(255) NOT NULL,",
496 " fRunDataKEY INT UNSIGNED NOT NULL"
497 ")"));
498
499 list.Add(new TObjString(
500 "CREATE TABLE MyMagic.RunBook ("
501 " fRunBookKEY INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
502 " fRunBookDate DATETIME NOT NULL,",
503 " fRunBookText TEXT NOT NULL"
504 ")"));
505
506 list.Add(new TObjString(
507 "CREATE TABLE MyMagic.Sequences ("
508 " fSequenceFirst INT UNSIGNED PRIMARY KEY, "
509 " fSequenceLast INT UNSIGNED NOT NULL UNIQUE, "
510 " fProjectKEY SMALLINT UNSIGNED NOT NULL,"
511 " fSourceKEY SMALLINT UNSIGNED NOT NULL,"
512 " fNumEvents MEDIUMINT UNSIGNED NOT NULL,"
513 " fRunTime SMALLINT UNSIGNED NOT NULL,"
514 " fRunStart DATETIME NOT NULL,"
515 " fZenithDistanceMin TINYINT NULL,"
516 " fZenithDistanceMax TINYINT NULL,"
517 " fAzimuthMin SMALLINT NULL,"
518 " fAzimuthMax SMALLINT NULL,"
519 " fL1TriggerTableKEY SMALLINT UNSIGNED NOT NULL,";
520 query +=
521 " fL2TriggerTableKEY SMALLINT UNSIGNED NOT NULL,"
522 " fHvSettingsKEY SMALLINT UNSIGNED NOT NULL,"
523 " fManuallyChanged SMALLINT UNSIGNED NOT NULL,"
524 " fLastUpdate TIMESTAMP" // COMMENT('Time of last change'),";
525 " fTestFlagKEY SMALLINT UNSIGNED NOT NULL,"
526 " fLightConditionsKEY SMALLINT UNSIGNED NOT NULL,"
527 " fDiscriminatorThresholdTableKEY SMALLINT UNSIGNED NOT NULL,"
528 " fTriggerDelayTableKEY SMALLINT UNSIGNED NOT NULL,"
529 ")";
530
531 list.Add(new TObjString(
532 "CREATE TABLE MyMagic.Calibration ("
533 " fSequenceFirst INT UNSIGNED PRIMARY KEY, "
534 " fUnsuitableInner SMALLINT UNSIGNED NOT NULL, "
535 " fUnsuitableOuter SMALLINT UNSIGNED NOT NULL, "
536 " fUnreliableInner SMALLINT UNSIGNED NOT NULL,"
537 " fUnreliableOuter SMALLINT UNSIGNED NOT NULL,"
538 " fIsolatedInner SMALLINT UNSIGNED NOT NULL,"
539 " fIsolatedOuter SMALLINT UNSIGNED NOT NULL,"
540 " fIsolatedMaxCluster SMALLINT UNSIGNED NOT NULL,"
541 " fMeanPedRmsInner FLOAT(6,2) NOT NULL,"
542 " fMeanPedRmsOuter FLOAT(6,2) NOT NULL,"
543 " fMeanSignalInner FLOAT(6,2) NOT NULL,"
544 " fMeanSignalOuter FLOAT(6,2) NOT NULL,"
545 " fArrTimeMeanInner FLOAT(5,1) NOT NULL,"
546 " fArrTimeRmsInner FLOAT(6,2) NOT NULL,"
547 " fArrTimeMeanOuter FLOAT(5,1) NOT NULL,"
548 " fArrTimeRmsOuter FLOAT(6,2) NOT NULL,"
549 " fConvFactorInner FLOAT(6,3) NOT NULL,"
550 " fConvFactorOuter FLOAT(6,3) NOT NULL,"
551 " fPulsePosMean FLOAT(6,2) NOT NULL,"
552 " fPulsePosRms FLOAT(6,2) NOT NULL,"
553 " fPulsePosCheckMean FLOAT(6,2) NULL,"
554 " fPulsePosCheckRms FLOAT(6,2) NULL,"
555 " fLastUpdate TIMESTAMP"
556 ")"));
557
558 list.Add(new TObjString(
559 "CREATE TABLE MyMagic.Star ("
560 " fSequenceFirst INT UNSIGNED PRIMARY KEY, "
561 " fMeanNumberIslands FLOAT(6,2) NOT NULL,"
562 " fPSF FLOAT(5,1) NOT NULL,"
563 " fRatio FLOAT(5,1) NOT NULL,"
564 " fMuonRate FLOAT(6,2) NOT NULL,"
565 " fMuonNumber INT UNSIGNED NOT NULL,"
566 " fEffOnTime INT UNSIGNED NOT NULL,"
567 " fDataRate INT UNSIGNED NOT NULL,"
568 " fMaxHumidity FLOAT(6,1) NOT NULL,"
569 " fInhomogeneity FLOAT(5,1) NOT NULL,"
570 " fLastUpdate TIMESTAMP"
571 ")"));
572
573 list.Add(new TObjString(
574 "CREATE TABLE MyMagic.DataSets ("
575 " fDataSetNumber INT UNSIGNED PRIMARY KEY, "
576 " fSourceKEY SMALLINT UNSIGNED NOT NULL,"
577 " fWobble ENUM('Y','N') NULL,"
578 " fComment VARCHAR(255) NULL,"
579 " fLastUpdate TIMESTAMP"
580 ")"));
581
582 list.Add(new TObjString(
583 "CREATE TABLE MyMagic.Ganymed ("
584 " fDataSetNumber INT UNSIGNED PRIMARY KEY, "
585 " fExcessEvents INT UNSIGNED NOT NULL,"
586 " fBackgroundEvents INT UNSIGNED NOT NULL,"
587 " fSignalEvents INT UNSIGNED NOT NULL,"
588 " fEffOnTime INT UNSIGNED NOT NULL,"
589 " fSignificance FLOAT(5,1) NOT NULL,"
590 " fScaleFactor FLOAT(5,2) NOT NULL,"
591 " fStandard ENUM(\"no\",\"yes\") NOT NULL," //default no
592 " fLastUpdate TIMESTAMP"
593 ")"));
594
595 list.Add(new TObjString(
596 "CREATE TABLE MyMagic.DataSetProcessStatus ("
597 " fDataSetNumber INT UNSIGNED PRIMARY KEY, "
598 " fDataSetInserted DATETIME NULL,"
599 " fStarFilesAvail DATETIME NULL,"
600 " fGanymed DATETIME NULL,"
601 " fFillGanymed DATETIME NULL"
602 ")";
603
604 list.Add(new TObjString(
605 "CREATE TABLE MyMagic.SequenceBuildStatus ("
606 " fDate DATE PRIMARY KEY, "
607 " fCCFilled DATETIME NULL,"
608 " fExclusionsDone DATETIME NULL,"
609 " fSequenceEntriesBuilt DATETIME NULL"
610 ")";
611
612 list.Add(new TObjString(
613 "CREATE TABLE MyMagic.RunProcessStatus ("
614 " fRunNumber INT UNSIGNED PRIMARY KEY, "
615 " fCCFileAvail DATETIME NULL,"
616 " fCaCoFileAvail DATETIME NULL,"
617 " fCaCoFileFound INT UNSIGNED NULL,"
618 " fRawFileAvail DATETIME NULL,"
619 " fDataCheckDone DATETIME NULL,"
620 " fTimingCorrection DATETIME NULL,"
621 " fMerpp DATETIME NULL,"
622 " fMerppCCUpdate DATETIME NULL,"
623 " fMerppCaCoUpdate DATETIME NULL"
624 ")";
625
626 list.Add(new TObjString(
627 "CREATE TABLE MyMagic.SequenceProcessStatus ("
628 " fSequenceFirst INT UNSIGNED PRIMARY KEY, "
629 " fSequenceFileWritten DATETIME NULL,"
630 " fAllFilesAvail DATETIME NULL,"
631 " fCallisto DATETIME NULL,"
632 " fFillCallisto DATETIME NULL,"
633 " fStar DATETIME NULL,"
634 " fFillStar DATETIME NULL"
635 ")";
636
637 list.Add(new TObjString(
638 "CREATE TABLE MyMagic.MarsVersion ("
639 " fMarsVersion SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
640 " fStartDate DATETIME NULL ,"
641 " fMarsVersionName VARCHAR(12) NOT NULL UNIQUE"
642 ") MAX_ROWS=256"));
643
644 TSQLResult *res;
645
646 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
647 if (!serv)
648 return;
649
650 res = serv->DropDataBase("MyMagic");
651 res = serv->CreateDataBase("MyMagic");
652 if (res)
653 {
654 cout << "Error creating Data Base!" << endl;
655 return;
656 }
657
658 TIter Next(&list);
659 TObjString *str;
660
661 cout << "Creating tables..." << endl;
662
663 while ((str=(TObjString*)Next()))
664 {
665 TString q(str->GetString());
666
667 Int_t f = q.First('(');
668 TString out = q(0, f);
669 cout << " - " << out << "... " << flush;
670 res = serv->Query(q);
671 cout << (res==0 ? "ERROR!" : "Ok.") << endl;
672 if (res)
673 delete res;
674 }
675
676 serv->Close();
677 delete serv;
678}
679
680void DisplayMagicDataBase()
681{
682 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
683 if (!serv)
684 return;
685
686 res = serv->GetColumns("MyMagic", "RunData");
687 if (res)
688 Print(res);
689 res = serv->GetColumns("MyMagic", "RunType");
690 if (res)
691 Print(res);
692 res = serv->GetColumns("MyMagic", "MagicNumber");
693 if (res)
694 Print(res);
695 res = serv->GetColumns("MyMagic", "Project");
696 if (res)
697 Print(res);
698 res = serv->GetColumns("MyMagic", "Source");
699 if (res)
700 Print(res);
701 res = serv->GetColumns("MyMagic", "TriggerTable");
702 if (res)
703 Print(res);
704 res = serv->GetColumns("MyMagic", "HvSettings");
705 if (res)
706 Print(res);
707
708 serv->Close();
709}
710
711Bool_t ExistStr(TSQLServer *serv, const char *column, const char *table, const char *test)
712{
713 TString query(Form("SELECT %s FROM %s WHERE %s='%s'", column, table, column, test));
714 TSQLResult *res = serv->Query(query);
715 if (!res)
716 return kFALSE;
717 delete res;
718
719 TSQLRow *row;
720
721 while (row=res->Next())
722 {
723 if ((*row)[0])
724 return kTRUE;
725 }
726
727 return kFALSE;
728}
729/*
730void LoadSourceNames(const char *fname)
731{
732 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
733 if (!serv)
734 return;
735
736 ifstream fin(fname);
737 if (!fin)
738 {
739 cout << "Cannot open " << fname << endl;
740 return;
741 }
742
743 while (1)
744 {
745 TString query, name;
746
747 name.ReadLine(fin);
748 if (!fin)
749 break;
750
751 if (ExistStr(serv, "fSourceName", "MyMagic.Source", name))
752 {
753 cout << "Entry " << name << " exists." << endl;
754 continue;
755 }
756
757 query = "INSERT MyMagic.Source SET ";
758 query += " fSourceName='";
759 query += name;
760 query += "', fSourceTypeKEY=1";
761 if (!serv->Query(query))
762 {
763 cout << query << " - FAILED!" << endl;
764 return;
765 }
766 }
767
768 serv->Close();
769}
770
771void LoadProjectNames(const char *fname)
772{
773 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
774 if (!serv)
775 return;
776
777 ifstream fin(fname);
778 if (!fin)
779 {
780 cout << "Cannot open " << fname << endl;
781 return;
782 }
783
784 while (1)
785 {
786 TString query, name;
787
788 name.ReadLine(fin);
789 if (!fin)
790 break;
791
792 if (ExistStr(serv, "fProjectName", "MyMagic.Project", name))
793 {
794 cout << "Entry " << name << " exists." << endl;
795 continue;
796 }
797
798 query = "INSERT MyMagic.Project SET ";
799 query += " fProjectName='";
800 query += name;
801 query += "', fProject='AUTO: ";
802 query += name;
803 query += "'";
804 if (!serv->Query(query))
805 {
806 cout << query << " - FAILED!" << endl;
807 return;
808 }
809 }
810
811 serv->Close();
812}
813
814void LoadTriggerTableNames(const char *fname)
815{
816 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
817 if (!serv)
818 return;
819
820 ifstream fin(fname);
821 if (!fin)
822 {
823 cout << "Cannot open " << fname << endl;
824 return;
825 }
826
827 while (1)
828 {
829 TString query, name;
830
831 name.ReadLine(fin);
832 if (!fin)
833 break;
834
835 if (ExistStr(serv, "fTriggerTableName", "MyMagic.TriggerTable", name))
836 {
837 cout << "Entry " << name << " exists." << endl;
838 continue;
839 }
840
841 query = "INSERT MyMagic.TriggerTable SET ";
842 query += " fTriggerTableName='";
843 query += name;
844 query += "'";
845 if (!serv->Query(query))
846 {
847 cout << query << " - FAILED!" << endl;
848 return;
849 }
850 }
851
852 serv->Close();
853}
854*/
855void setupdb()
856{
857 CreateMagicDataBase();
858 CreatePrimaryEntries();
859 //LoadSourceNames("sourcenames.txt");
860 //LoadProjectNames("projectnames.txt");
861 //LoadTriggerTableNames("triggertablenames.txt");
862
863
864 return;
865
866 //TSQLServer *serv = TSQLServer::Connect("mysql://magic:3306", "root", "marWin");
867 //TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "database", "ImM9G1CD8");
868 TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
869 if (!serv)
870 return;
871
872 cout << "ServerInfo: " << serv->ServerInfo() << endl;
873 cout << "DBMS: " << serv->GetDBMS() << endl;
874 cout << "Host: " << serv->GetHost() << endl;
875 cout << "Port: " << serv->GetPort() << endl;
876
877 TSQLResult *res;
878
879 cout << endl;
880
881 res = serv->GetDataBases();
882 if (res)
883 Print(res);
884
885 serv->Close();
886 /*
887 TList list;
888 if (!list.FindObject(triggertablename))
889 {
890 TNamed *name = new TNamed(triggertablename, "");
891 list.Add(name);
892 }
893
894 list.Print();
895 */
896}
Note: See TracBrowser for help on using the repository browser.