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

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