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

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