#include <iomanip.h>
/*
Statements

SELECT [ DISTINCT ] * | LIST OF COLUMNS, FUNCTIONS, CONSTANTS
   FROM LIST OF TABLES OR VIEWS
   [ WHERE CONDITION(S) ]
   [ ORDER BY ORDERING COLUMN(S) [ ASC | DESC ] ]
   [ GROUP BY GROUPING COLUMN(S) ]
   [ HAVING CONDITION(S) ]

DELETE FROM TABLE NAME
   [ WHERE CONDITION(S) ]

INSERT INTO TABLE NAME
   [ (COLUMN LIST) ]
   VALUES (VALUE LIST)

UPDATE TABLE NAME
   SET COLUMN NAME = VALUE
   [ WHERE CONDITION ]

Functions

Function 	Purpose
SUM 	Total of the values in a field.
AVG 	Average of the values in a field.
MIN 	Lowest value in a field.
MAX 	Highest value in a field.
COUNT 	Number of values in a field, not counting Null (blank) values.

Predicates

Predicate 	Description
BETWEEN ... AND 	Compares a value to a range formed by two values.
IN 	Determines whether a value exists in a list of values or a table.
LIKE 	Compares, in part or in whole, one value with another.
JOIN 	Joins two tables.

Data Definition

CREATE TABLE TABLE_NAME
   ( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
   [, other column definitions,...]
   [, primary key constraint]
   )

ALTER TABLE TABLE_NAME ADD | DROP | MODIFY
   ( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
   [, other column definitions,...]
   )

DROP TABLE TABLE_NAME

CREATE [UNIQUE] [ASC | DESC] INDEX INDEX_NAME
   ON TABLE_NAME ( COLUMN_LIST )

DROP INDEX INDEX_NAME ON TABLE_NAME

CREATE VIEW VIEW_NAME AS QUERY_NAME

CONSTRAINT CONSTRAINT_NAME
    {PRIMARY KEY | UNIQUE | NOT NULL |
    REFERENCES FOREIGN_TABLE [(FIELD_LIST)]}

    */

void Line(const TArrayI &max)
{
    cout << "+" << setfill('-');
    for (int i=0; i<max.GetSize(); i++)
        cout << setw(max[i]+1) << "-" << "-+";
    cout << endl;
}

void Print(TSQLResult *res)
{
    Int_t n = res->GetFieldCount();

    TArrayI max(n);

    for (int i=0; i<n; i++)
        max[i] = strlen(res->GetFieldName(i));

    TSQLRow *row;

    TList rows;
    while (row=res->Next())
    {
        for (int i=0; i<n; i++)
            max[i] = TMath::Max(max[i], row->GetFieldLength(i));
        rows.Add(row);
    }

    Line(max);

    cout << "|" << setfill(' ');
    for (int i=0; i<n; i++)
        cout << setw(max[i]+1) << res->GetFieldName(i) << " |";
    cout << endl;

    Line(max);

    cout << setfill(' ');
    TIter Next(&rows);
    while (row=(TSQLRow*)Next())
    {
        cout << "|";
        for (int i=0; i<n; i++)
        {
            char *c = (*row)[i];
            cout << setw(max[i]+1) << (c?c:"") << " |";
        }
        cout << endl;
    }

    Line(max);
}

TString GetFields(TSQLServer *serv, const char *db, const char *table)
{
    res = serv->GetColumns(db, table);
    if (!res)
        return "";

    TString fields;

    TList rows;
    while (row=res->Next())
        rows.Add(row);

    TIter Next(&rows);
    while (row=(TSQLRow*)Next())
    {
        fields += (*row)[0];
        if (row!=rows.Last())
            fields += ", ";
    }

    return fields;
}

void PrintContents(TSQLServer *serv, const char *db, const char *table)
{
    TString fields=GetFields(serv, db, table);

    TSQLResult *res = serv->Query(Form("SELECT %s FROM %s", fields.Data(), table));
    if (res)
    {
        Print(res);
        delete res;
    }
}

/*
 GetTables(db):
    \u "db"
    SHOW TABLES;

 GetDataBases();
    SHOW DATABASES;

 GetColumns(db, table);
    EXPLAIN table;
    DESCRIBE table;

 SHOW VARIABLES;

 PRIMARY_KEY impliziert NOT NULL

    */

void CreatePrimaryEntries()
{
    TList list;
    list.SetOwner();

    // Trigger  tables
    list.Add(new TObjString(
        "INSERT MyMagic.L1TriggerTable (fL1TriggerTableKEY, fL1TriggerTableName, fL1TriggerTable) VALUES "
        "  (1, 'n/a', 'Not available')"));

    list.Add(new TObjString(
        "INSERT MyMagic.L2TriggerTable (fL2TriggerTableKEY, fL2TriggerTableName, fL2TriggerTable) VALUES "
        "  (1, 'n/a', 'Not available')"));

    // File path
    list.Add(new TObjString(
        "INSERT MyMagic.FilePath (fFilePathName, fFilePath) VALUES "
        "  ('n/a', 'Not available in the Data Center')"));

    // Magic number
    list.Add(new TObjString(
        "INSERT MyMagic.MagicNumber (fMagicNumber, fMagicNumberName) VALUES "
        "  (0x0001, 'Not available')," //1
        "  (0xc0c0, 'Ok'),"            //45344
        "  (0xc0c1, 'Not closed'),"    //45345
        "  (0x0000, 'Wrong')"));       //0

    // Run type
    list.Add(new TObjString(
        "INSERT MyMagic.RunType (fRunType, fRunTypeName) VALUES "
        "  (0xffff, 'Not available'),"
        "  (0x0000, 'Data'),"
        "  (0x0001, 'Pedestal'),"
        "  (0x0002, 'Calibration'),"
        "  (0x0007, 'Point Run'),"
        "  (0x0100, 'Monte Carlo')"));

    // HvSettings
    list.Add(new TObjString(
        "INSERT MyMagic.HvSettings (fHvSettingsKEY, fHvSettingsName, fHvSettings) VALUES "
        "  (1, 'n/a', 'Not available')"));

    //Excluded From DataAnalysis
    list.Add(new TObjString(
        "INSERT MyMagic.ExcludedFDA (fExcludedFDAKEY, fExcludedFDAName, fExcludedFDA) VALUES "
        "  (1, \"Not excluded\", \"Not excluded from Data Analysis\")"));

    //Manually Changed
    list.Add(new TObjString(
        "INSERT MyMagic.ManuallyChanged (fManuallyChangedKEY, fManuallyChangedName, fManuallyChanged) VALUES "
        "  (1, \"Automatic\", \"This entry was created automatically without user intervention\")");

    // Source type
    list.Add(new TObjString(
        "INSERT MyMagic.SourceType (fSourceTypeName) VALUES ('Unknown')"));

    // LightConditions
    list.Add(new TObjString(
        "INSERT MyMagic.LightConditions (fLightConditionsKEY, fLightConditionsName, fLightConditions) VALUES "
        "  (1, 'n/a', 'Light conditions are not available')"));

    // Testflag
    list.Add(new TObjString(
        "INSERT MyMagic.TestFlag (fTestFlagKEY, fTestFlagName, fTestFlag) VALUES "
        "  (1, 'n/a', 'Testflag is not available')"));

    // Trigger delay table
    list.Add(new TObjString(
        "INSERT MyMagic.TriggerDelayTable (fTriggerDelayTableKEY, fTriggerDelayTableName, fTriggerDelayTable) VALUES "
        "  (1, 'n/a', 'Trigger delay table is not available')"));

    // Calibration script
    list.Add(new TObjString(
        "INSERT MyMagic.CalibrationScript (fCalibrationScriptKEY, fCalibrationScriptName, fCalibrationScript) VALUES "
        "  (1, 'n/a', 'Calibration script is not available')"));

    // discriminator threshold table
    list.Add(new TObjString(
        "INSERT MyMagic.DiscriminatorThresholdTable (fDiscriminatorThresholdTableKEY, fDiscriminatorThresholdTableName, fDiscriminatorThresholdTable) VALUES "
        "  (1, 'n/a', 'Discriminator threshold table is not available')"));


    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    TIter Next(&list);
    TObjString *str;

    cout << "Filling tables..." << endl;

    while ((str=(TObjString*)Next()))
    {
        TString q(str->GetString());

        Int_t f = q.First('(');
        TString out = q(0, f);
        cout << " - " << out << "... " << flush;
        TSQLResult *res = serv->Query(q);
        cout << (res==0 ? "ERROR!" : "Ok.") << endl;
        if (res)
            delete res;
    }

    serv->Close();
    delete serv;
}

TObjString *CreateKeyTable(TString name)
{
    return new TObjString(Form(
      "CREATE TABLE MyMagic.%s ("
      "  f%sKEY    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
      "  f%sName   VARCHAR(255)         NOT NULL UNIQUE,"
      "  f%s       VARCHAR(255)             NULL"
      ") MAX_ROWS=65536", name.Data(), name.Data(), name.Data(), name.Data()));
}

void CreateMagicDataBase()
{
    TList list;
    list.SetOwner();

    TString query =
        "CREATE TABLE MyMagic.RunData"
        "("
        "  fRunDataKEY                      INT       UNSIGNED   PRIMARY KEY AUTO_INCREMENT,"// COMMENT('The unique key for each run-file'),";
        "  fRunNumber                       INT       UNSIGNED   NOT NULL UNIQUE,"  // PACK_KEYS=1,";// COMMENT('Run number'),";
        "  fMagicNumberKEY                  TINYINT   UNSIGNED   NOT NULL,"         // PACK_KEYS=1,";// COMMENT('The MAGIC Key (first two bytes of a raw data file)'),";
        "  fFormatVersion                   SMALLINT  UNSIGNED   NOT NULL,"         // PACK_KEYS=1,";// COMMENT('File format version of the raw data file)'),";
        "  fRunTypeKEY                      TINYINT   UNSIGNED   NOT NULL,"         // PACK_KEYS=1,";// COMMENT('Run type'),";
        "  fProjectKEY                      SMALLINT  UNSIGNED   NOT NULL,"         // PACK_KEYS=1,";// COMMENT('Name of the project (assigned by the physics comittee)'),";
        "  fSourceKEY                       SMALLINT  UNSIGNED   NOT NULL,"         // PACK_KEYS=1,";// COMMENT('Name of the source observed'),";
        "  fNumEvents                       MEDIUMINT UNSIGNED   NOT NULL,"         // COMMENT('Number of events in this run-file'),";
        "  fRunStart                        DATETIME             NOT NULL,"         // COMMENT('Time when the run was started'),";
        "  fRunStop                         DATETIME                 NULL,";        // COMMENT('Time when the run has stopped')";
    query +=
        "  fZenithDistance                  TINYINT                  NULL,"         // COMMENT('Time of last change'),";
        "  fAzimuth                         SMALLINT                 NULL,"         // COMMENT('Time of last change'),";
        "  fL1TriggerTableKEY               SMALLINT  UNSIGNED   NOT NULL,"         // COMMENT('Time of last change'),";
        "  fL2TriggerTableKEY               SMALLINT  UNSIGNED   NOT NULL,"         // COMMENT('Time of last change'),";
        "  fHvSettingsKEY                   SMALLINT  UNSIGNED   NOT NULL,"         // COMMENT('Time of last change'),";
        "  fDaqStoreRate                    SMALLINT  UNSIGNED       NULL,"         // COMMENT('Time of last change'),";
        "  fDaqTriggerRate                  SMALLINT  UNSIGNED       NULL,"         // COMMENT('Time of last change'),";
        "  fMeanTriggerRate                 SMALLINT  UNSIGNED       NULL,"         // COMMENT('Time of last change'),";
        "  fL2RatePresc                     SMALLINT  UNSIGNED       NULL,"         // COMMENT('Time of last change'),";
        "  fL2RateUnpresc                   SMALLINT  UNSIGNED       NULL,"         // COMMENT('Time of last change'),";
        "  fExcludedFDAKEY                  SMALLINT  UNSIGNED   NOT NULL,"
        "  fSequenceFirst                        INT  UNSIGNED   NOT NULL,";
    query +=
        "  fLastUpdate                      TIMESTAMP"                              // COMMENT('Time of last change'),";
        "  fTestFlagKEY                     SMALLINT  UNSIGNED   NOT NULL,"
        "  fLightConditionsKEY              SMALLINT  UNSIGNED   NOT NULL,"
        "  fCalibrationScriptKEY            SMALLINT  UNSIGNED   NOT NULL,"
        "  fDiscriminatorThresholdTableKEY  SMALLINT  UNSIGNED   NOT NULL,"
        "  fTriggerDelayTableKEY            SMALLINT  UNSIGNED   NOT NULL,"
        ")";

    list.Add(new TObjString(query));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.RunType ("
        "  fRunTypeKEY        TINYINT  UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fRunType           SMALLINT UNSIGNED    NOT NULL UNIQUE,"
        "  fRunTypeName       VARCHAR(255)         NOT NULL UNIQUE,"
        ") MAX_ROWS=256"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.MagicNumber ("
        "  fMagicNumberKEY    TINYINT  UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fMagicNumber       SMALLINT UNSIGNED    NOT NULL UNIQUE,"
        "  fMagicNumberName   VARCHAR(255)         NOT NULL UNIQUE"
        ") MAX_ROWS=256"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.Project ("
        "  fProjectKEY        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fProjectName       CHAR(22)             NOT NULL UNIQUE,"
        "  fProject           VARCHAR(255)         NOT NULL"
        ") MAX_ROWS=65536"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.Source ("
        "  fSourceKEY         SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fSourceName        CHAR(12)             NOT NULL UNIQUE,"
        "  fSourceTypeKEY     SMALLINT UNSIGNED        NULL,"
        "  fRightAscension    DOUBLE                   NULL,"
        "  fDeclination       DOUBLE                   NULL,"
        "  fEpochChar         CHAR                     NULL,"// DEFAULT='J'
        "  fEpochDate         SMALLINT(4) UNSIGNED     NULL,"// DEFAULT=2000
        "  fMagnitude         SMALLINT                 NULL," // 82=8.2
        "  fTest              ENUM(\"no\",\"yes\")     NOT NULL"  //default no
        ") MAX_ROWS=65536"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.SourceType ("
        "  fSourceTypeKEY     SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fSourceTypeName    VARCHAR(255)         NOT NULL UNIQUE"
        ") MAX_ROWS=65536"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.Files ("
        "  fRawFileKEY        INT UNSIGNED         NOT NULL PRIMARY KEY,"
        "  fRepFileKEY        INT UNSIGNED             NULL UNIQUE,"
        "  fDccFileKEY        INT UNSIGNED             NULL UNIQUE"
        ")"));

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.RawFile ("
        "  fRawFileKEY        INT UNSIGNED PRIMARY KEY,"
        "  fRawFileName       VARCHAR(64)           NOT NULL UNIQUE," // NULL means - Not in Wuerzburg!
        "  fFilePathKEY       SMALLINT UNSIGNED     NOT NULL"
        ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.RepFile ("
         "  fRepFileKEY        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fRepFileName       VARCHAR(64)          NOT NULL UNIQUE,"           // NULL means - Not in Wuerzburg!
         "  fFilePathKEY       SMALLINT UNSIGNED    NOT NULL"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.DccFile ("
         "  fDccFileKEY        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fDccFileName       VARCHAR(64)          NOT NULL UNIQUE,"           // NULL means - Not in Wuerzburg!
         "  fFilePathKEY       SMALLINT UNSIGNED    NOT NULL"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.FilePath ("
         "  fFilePathKEY       SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fFilePathName      VARCHAR(255)         NOT NULL UNIQUE,"
         "  fFilePath          VARCHAR(255)         NOT NULL UNIQUE"
         ") MAX_ROWS=65536"));

    list.Add(CreateKeyTable("L1TriggerTable"));
    list.Add(CreateKeyTable("L2TriggerTable"));
    list.Add(CreateKeyTable("HvSettings"));
//    list.Add(CreateKeyTable("ExcludedFDA"));
    list.Add(CreateKeyTable("ManuallyChanged"));
    list.Add(CreateKeyTable("TestFlag"));
    list.Add(CreateKeyTable("LightConditions"));
    list.Add(CreateKeyTable("CalibrationScript"));
    list.Add(CreateKeyTable("DiscriminatorThresholdTable"));
    list.Add(CreateKeyTable("TriggerDelayTable"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.ExcludedFDA ("
         "  fExcludedFDAKEY        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fExcludedFDAImportance SMALLINT UNSIGNED        NULL,"
         "  fExcludedFDAAutomatic  ENUM(\"yes\",\"no\")     NULL,"
         "  fExcludedFDAName       VARCHAR(255)         NOT NULL UNIQUE,"
         "  fExcludedFDA           VARCHAR(255)             NULL"
         ") MAX_ROWS=65536"));


    list.Add(new TObjString(
         "CREATE TABLE MyMagic.DataCheck ("
         "  fRunNumber                INT      UNSIGNED PRIMARY KEY,"
         "  fEvents                   INT      UNSIGNED        NULL,"
         "  fPositionSignal           TINYINT  UNSIGNED        NULL,"
         "  fPositionFWHM             TINYINT  UNSIGNED        NULL,"
         "  fHeightSignal             SMALLINT UNSIGNED        NULL,"
         "  fHeightFWHM               SMALLINT UNSIGNED        NULL,"
         "  fHasSignal                ENUM(\"yes\",\"no\")     NULL,"
         "  fHasPedestal              ENUM(\"yes\",\"no\")     NULL,"
         "  fPositionAsym             ENUM(\"yes\",\"no\")     NULL,"
         "  fHeightAsym               ENUM(\"yes\",\"no\")     NULL,"
         "  fEventsInterlaced         INT      UNSIGNED        NULL,"
         "  fPositionSignalInterlaced TINYINT  UNSIGNED        NULL,"
         "  fPositionFWHMInterlaced   TINYINT  UNSIGNED        NULL,"
         "  fHeightSignalInterlaced   SMALLINT UNSIGNED        NULL,"
         "  fHeightFWHMInterlaced     SMALLINT UNSIGNED        NULL,"
         "  fHasSignalInterlaced      ENUM(\"yes\",\"no\")     NULL,"
         "  fHasPedestalInterlaced    ENUM(\"yes\",\"no\")     NULL,"
         "  fPositionAsymInterlaced   ENUM(\"yes\",\"no\")     NULL,"
         "  fHeightAsymInterlaced     ENUM(\"yes\",\"no\")     NULL"
         ")"));


/*
    list.Add(new TObjString(
         "CREATE TABLE MyMagic.TriggerTable ("
         "  fTriggerTableKEY   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fTriggerTableName  VARCHAR(255)         NOT NULL UNIQUE,"
         "  fTriggerTable      VARCHAR(255)             NULL"
         ") MAX_ROWS=65536"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.HvSettings ("
         "  fHvSettingsKEY     SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fHvSettingsName    VARCHAR(255)         NOT NULL UNIQUE,"
         "  fHvSettings        VARCHAR(255)             NULL"
         ") MAX_ROWS=65536"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.ExcludedFDA ("
         "  fExcludedFDAKEY    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fExcludedFDAName   VARCHAR(255)         NOT NULL UNIQUE,"
         "  fExcludedFDA       VARCHAR(255)             NULL"
         ") MAX_ROWS=65536"));
 
    list.Add(new TObjString(
         "CREATE TABLE MyMagic.ManuallyChanged ("
         "  fManuallyChangedKEY    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fManuallyChangedName   VARCHAR(255)         NOT NULL UNIQUE,"
         "  fManuallyChanged       VARCHAR(255)             NULL"
         ") MAX_ROWS=65536"));
 */
    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Changes ("
         "  fChangesKEY        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fTimeStamp         TIMESTAMP,"
         "  fTableName         VARCHAR(64)          NOT NULL,"
         "  fRowKEY            INT UNSIGNED         NOT NULL,"
         "  fColumnName        VARCHAR(64)          NOT NULL,"
         "  fDescription       VARCHAR(255)         NOT NULL"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Comments ("
         "  fCommentsKEY        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fCommentsName       VARCHAR(255)         NOT NULL,",
         "  fRunDataKEY         INT UNSIGNED         NOT NULL"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.RunBook ("
         "  fRunBookKEY        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
         "  fRunBookDate       DATETIME              NOT NULL,",
         "  fRunBookText       TEXT                  NOT NULL"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Sequences ("
         "  fSequenceFirst     INT       UNSIGNED   PRIMARY KEY, "
         "  fSequenceLast      INT       UNSIGNED   NOT NULL UNIQUE, "
         "  fProjectKEY        SMALLINT  UNSIGNED   NOT NULL,"
         "  fSourceKEY         SMALLINT  UNSIGNED   NOT NULL,"
         "  fNumEvents         MEDIUMINT UNSIGNED   NOT NULL,"
         "  fRunTime           SMALLINT  UNSIGNED   NOT NULL,"
         "  fRunStart          DATETIME             NOT NULL,"
         "  fZenithDistanceMin TINYINT                  NULL,"
         "  fZenithDistanceMax TINYINT                  NULL,"
         "  fAzimuthMin        SMALLINT                 NULL,"
         "  fAzimuthMax        SMALLINT                 NULL,"
         "  fL1TriggerTableKEY SMALLINT  UNSIGNED   NOT NULL,";
    query +=
         "  fL2TriggerTableKEY SMALLINT  UNSIGNED   NOT NULL,"
         "  fHvSettingsKEY     SMALLINT  UNSIGNED   NOT NULL,"
         "  fManuallyChanged   SMALLINT  UNSIGNED   NOT NULL,"
         "  fLastUpdate                      TIMESTAMP"                              // COMMENT('Time of last change'),";
         "  fTestFlagKEY                     SMALLINT  UNSIGNED   NOT NULL,"
         "  fLightConditionsKEY              SMALLINT  UNSIGNED   NOT NULL,"
         "  fDiscriminatorThresholdTableKEY  SMALLINT  UNSIGNED   NOT NULL,"
         "  fTriggerDelayTableKEY            SMALLINT  UNSIGNED   NOT NULL,"
         ")";

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Calibration ("
         "  fSequenceFirst      INT        UNSIGNED   PRIMARY KEY, "
         "  fUnsuitableInner    SMALLINT   UNSIGNED   NOT NULL, "
         "  fUnsuitableOuter    SMALLINT   UNSIGNED   NOT NULL, "
         "  fUnreliableInner    SMALLINT   UNSIGNED   NOT NULL,"
         "  fUnreliableOuter    SMALLINT   UNSIGNED   NOT NULL,"
         "  fIsolatedInner      SMALLINT   UNSIGNED   NOT NULL,"
         "  fIsolatedOuter      SMALLINT   UNSIGNED   NOT NULL,"
         "  fIsolatedMaxCluster SMALLINT   UNSIGNED   NOT NULL,"
         "  fMeanPedRmsInner    FLOAT(6,2)            NOT NULL,"
         "  fMeanPedRmsOuter    FLOAT(6,2)            NOT NULL,"
         "  fArrTimeMeanInner   FLOAT(5,1)            NOT NULL,"
         "  fArrTimeRmsInner    FLOAT(5,1)            NOT NULL,"
         "  fArrTimeMeanOuter   FLOAT(5,1)            NOT NULL,"
         "  fArrTimeRmsOuter    FLOAT(5,1)            NOT NULL,"
         "  fConvFactorInner    FLOAT(6,3)            NOT NULL,"
         "  fConvFactorOuter    FLOAT(6,3)            NOT NULL,"
         "  fLastUpdate         TIMESTAMP"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Star ("
         "  fSequenceFirst      INT        UNSIGNED   PRIMARY KEY, "
         "  fMeanNumberIslands  FLOAT(6,2)            NOT NULL,"
         "  fPSF                FLOAT(5,1)            NOT NULL,"
         "  fRatio              FLOAT(5,1)            NOT NULL,"
         "  fMuonRate           FLOAT(6,2)            NOT NULL,"
         "  fMuonNumber         INT        UNSIGNED   NOT NULL,"
         "  fEffOnTime          INT        UNSIGNED   NOT NULL,"
         "  fDataRate           INT        UNSIGNED   NOT NULL,"
         "  fMaxHumidity        FLOAT(6,1)            NOT NULL,"
         "  fLastUpdate         TIMESTAMP"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.DataSets ("
         "  fDataSetNumber      INT           UNSIGNED   PRIMARY KEY, "
         "  fSourceKEY          SMALLINT      UNSIGNED   NOT NULL,"
         "  fWobble             ENUM('Y','N')            NULL,"
         "  fComment            VARCHAR(255)             NULL,"
         "  fLastUpdate         TIMESTAMP"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.Ganymed ("
         "  fDataSetNumber      INT        UNSIGNED   PRIMARY KEY, "
         "  fExcessEvents       INT        UNSIGNED   NOT NULL,"
         "  fBackgroundEvents   INT        UNSIGNED   NOT NULL,"
         "  fSignalEvents       INT        UNSIGNED   NOT NULL,"
         "  fEffOnTime          INT        UNSIGNED   NOT NULL,"
         "  fSignificance       FLOAT(5,1)            NOT NULL,"
         "  fScaleFactor        FLOAT(5,2)            NOT NULL,"
         "  fLastUpdate         TIMESTAMP"
         ")"));

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.DataSetProcessStatus ("
         "  fDataSetNumber    INT       UNSIGNED   PRIMARY KEY, "
         "  fDataSetInserted  DATETIME             NULL,"
         "  fStarFilesAvail   DATETIME             NULL,"
         "  fGanymed          DATETIME             NULL,"
         "  fFillGanymed      DATETIME             NULL"
         ")";

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.SequenceBuildStatus ("
         "  fDate                  DATE                 PRIMARY KEY, "
         "  fCCFilled              DATETIME             NULL,"
         "  fExclusionsDone        DATETIME             NULL,"
         "  fSequenceEntriesBuilt  DATETIME             NULL"
         ")";

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.RunProcessStatus ("
         "  fRunNumber             INT       UNSIGNED   PRIMARY KEY, "
         "  fCCFileAvail           DATETIME             NULL,"
         "  fCaCoFileAvail         DATETIME             NULL,"
         "  fCaCoFileFound         INT       UNSIGNED   NULL,"
         "  fRawFileAvail          DATETIME             NULL,"
         "  fDataCheckDone         DATETIME             NULL,"
         "  fTimingCorrection      DATETIME             NULL,"
         "  fMerpp                 DATETIME             NULL,"
         "  fMerppCCUpdate         DATETIME             NULL,"
         "  fMerppCaCoUpdate       DATETIME             NULL"
         ")";

    list.Add(new TObjString(
         "CREATE TABLE MyMagic.SequenceProcessStatus ("
         "  fSequenceFirst         INT       UNSIGNED   PRIMARY KEY, "
         "  fSequenceFileWritten   DATETIME             NULL,"
         "  fAllFilesAvail         DATETIME             NULL,"
         "  fCallisto              DATETIME             NULL,"
         "  fFillCallisto          DATETIME             NULL,"
         "  fStar                  DATETIME             NULL,"
         "  fFillStar              DATETIME             NULL"
         ")";

    list.Add(new TObjString(
        "CREATE TABLE MyMagic.MarsVersion ("
        "  fMarsVersion        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
        "  fStartDate          DATETIME                NULL ,"
        "  fMarsVersionName    VARCHAR(12)         NOT NULL UNIQUE"
        ") MAX_ROWS=256"));

    TSQLResult *res;

    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    res = serv->DropDataBase("MyMagic");
    res = serv->CreateDataBase("MyMagic");
    if (res)
    {
        cout << "Error creating Data Base!" << endl;
        return;
    }

    TIter Next(&list);
    TObjString *str;

    cout << "Creating tables..." << endl;

    while ((str=(TObjString*)Next()))
    {
        TString q(str->GetString());

        Int_t f = q.First('(');
        TString out = q(0, f);
        cout << " - " << out << "... " << flush;
        res = serv->Query(q);
        cout << (res==0 ? "ERROR!" : "Ok.") << endl;
        if (res)
            delete res;
    }

    serv->Close();
    delete serv;
}

void DisplayMagicDataBase()
{
    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    res = serv->GetColumns("MyMagic", "RunData");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "RunType");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "MagicNumber");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "Project");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "Source");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "TriggerTable");
    if (res)
        Print(res);
    res = serv->GetColumns("MyMagic", "HvSettings");
    if (res)
        Print(res);

    serv->Close();
}

Bool_t ExistStr(TSQLServer *serv, const char *column, const char *table, const char *test)
{
    TString query(Form("SELECT %s FROM %s WHERE %s='%s'", column, table, column, test));
    TSQLResult *res = serv->Query(query);
    if (!res)
        return kFALSE;
    delete res;

    TSQLRow *row;

    while (row=res->Next())
    {
        if ((*row)[0])
            return kTRUE;
    }

    return kFALSE;
}
/*
void LoadSourceNames(const char *fname)
{
    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    ifstream fin(fname);
    if (!fin)
    {
        cout << "Cannot open " << fname << endl;
        return;
    }

    while (1)
    {
        TString query, name;

        name.ReadLine(fin);
        if (!fin)
            break;

        if (ExistStr(serv, "fSourceName", "MyMagic.Source", name))
        {
            cout << "Entry " << name << " exists." << endl;
            continue;
        }

        query  = "INSERT MyMagic.Source SET ";
        query += "  fSourceName='";
        query += name;
        query += "', fSourceTypeKEY=1";
        if (!serv->Query(query))
        {
            cout << query << " - FAILED!" << endl;
            return;
        }
    }

    serv->Close();
}

void LoadProjectNames(const char *fname)
{
    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    ifstream fin(fname);
    if (!fin)
    {
        cout << "Cannot open " << fname << endl;
        return;
    }

    while (1)
    {
        TString query, name;

        name.ReadLine(fin);
        if (!fin)
            break;

        if (ExistStr(serv, "fProjectName", "MyMagic.Project", name))
        {
            cout << "Entry " << name << " exists." << endl;
            continue;
        }

        query  = "INSERT MyMagic.Project SET ";
        query += "  fProjectName='";
        query += name;
        query += "', fProject='AUTO: ";
        query += name;
        query += "'";
        if (!serv->Query(query))
        {
            cout << query << " - FAILED!" << endl;
            return;
        }
    }

    serv->Close();
}

void LoadTriggerTableNames(const char *fname)
{
    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    ifstream fin(fname);
    if (!fin)
    {
        cout << "Cannot open " << fname << endl;
        return;
    }

    while (1)
    {
        TString query, name;

        name.ReadLine(fin);
        if (!fin)
            break;

        if (ExistStr(serv, "fTriggerTableName", "MyMagic.TriggerTable", name))
        {
            cout << "Entry " << name << " exists." << endl;
            continue;
        }

        query  = "INSERT MyMagic.TriggerTable SET ";
        query += "  fTriggerTableName='";
        query += name;
        query += "'";
        if (!serv->Query(query))
        {
            cout << query << " - FAILED!" << endl;
            return;
        }
    }

    serv->Close();
}
*/
void setupdb()
{
    CreateMagicDataBase();
    CreatePrimaryEntries();
    //LoadSourceNames("sourcenames.txt");
    //LoadProjectNames("projectnames.txt");
    //LoadTriggerTableNames("triggertablenames.txt");


    return;

    //TSQLServer *serv = TSQLServer::Connect("mysql://magic:3306", "root", "marWin");
    //TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "database", "ImM9G1CD8");
    TSQLServer *serv = TSQLServer::Connect("mysql://localhost:3306", "hercules", "d99swMT!");
    if (!serv)
        return;

    cout << "ServerInfo: " << serv->ServerInfo() << endl;
    cout << "DBMS:       " << serv->GetDBMS() << endl;
    cout << "Host:       " << serv->GetHost() << endl;
    cout << "Port:       " << serv->GetPort() << endl;

    TSQLResult *res;

    cout << endl;

    res = serv->GetDataBases();
    if (res)
        Print(res);

    serv->Close();
    /*
        TList list;
    if (!list.FindObject(triggertablename))
    {
        TNamed *name = new TNamed(triggertablename, "");
        list.Add(name);
    }

    list.Print();
    */
}