#use FileCatalog;

#
# All IDs are named after their respective table. This MUST
# remain like this.
#  eventGeneratorID        -> eventGenerator+ID       in 'EventGenerators'
#  detectorConfigurationID ->detectorConfiguration+ID in 'DetectorConfigurations'
#
# etc...
#

DROP TABLE IF EXISTS EventGenerators;
CREATE TABLE EventGenerators
(
  eventGeneratorID      SMALLINT     NOT NULL    AUTO_INCREMENT,
  eventGeneratorName    VARCHAR(30)        NOT NULL,
  eventGeneratorVersion VARCHAR(10)     NOT NULL,
  eventGeneratorParams  VARCHAR(200),

  eventGeneratorIDate   TIMESTAMP       NOT NULL,
  eventGeneratorCreator CHAR(15)        DEFAULT 'unknown' NOT NULL,
  eventGeneratorCount   INT,
  eventGeneratorComment TEXT,
  UNIQUE        EG_EventGeneratorUnique (eventGeneratorName, eventGeneratorVersion, eventGeneratorParams),
  PRIMARY KEY (eventGeneratorID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS DetectorConfigurations; CREATE TABLE DetectorConfigurations
(
  detectorConfigurationID               INT             NOT NULL        AUTO_INCREMENT,
  detectorConfigurationName             VARCHAR(50)        NULL           UNIQUE,
  dTPC                                  TINYINT,
  dSVT                                  TINYINT,
  dTOF                                  TINYINT,
  dEMC                                  TINYINT,
  dEEMC                                 TINYINT,
  dFPD                                  TINYINT,
  dFTPC                                 TINYINT,
  dPMD                                  TINYINT,
  dRICH                                 TINYINT,
  dSSD                                  TINYINT,
  dBBC                                  TINYINT,
  dBSMD                                 TINYINT,
  dESMD                                 TINYINT,
  PRIMARY KEY (detectorConfigurationID)
) TYPE=MyISAM;


# Trigger related tables
DROP TABLE IF EXISTS TriggerSetups; CREATE TABLE TriggerSetups
(
   triggerSetupID               SMALLINT     NOT NULL    AUTO_INCREMENT,
   triggerSetupName             VARCHAR(50)        NOT NULL       UNIQUE,
   triggerSetupComposition      VARCHAR(255) NOT NULL,

   triggerSetupIDate            TIMESTAMP       NOT NULL,
   triggerSetupCreator          CHAR(15)       DEFAULT 'unknown' NOT NULL,
   triggerSetupCount            INT,
   triggerSetupComment          TEXT,
   PRIMARY KEY                  (triggerSetupID)
) TYPE=MyISAM;


DROP TABLE IF EXISTS TriggerCompositions; CREATE TABLE TriggerCompositions
(
  fileDataID                    BIGINT          NOT NULL,
  triggerWordID                 INT             NOT NULL,
  triggerCount                  MEDIUMINT       DEFAULT 0,
  PRIMARY KEY                   (fileDataID, triggerWordID)
) TYPE=MyISAM;



DROP TABLE IF EXISTS TriggerWords;
CREATE TABLE TriggerWords (
  triggerWordID         mediumint(9)   NOT NULL auto_increment,
  triggerWordName       varchar(50)  NOT NULL default '',
  triggerWordVersion    varchar(6)        NOT NULL default 'V0.0',
  triggerWordBits       varchar(6)   NOT NULL default '',
  triggerWordIDate      timestamp(14)       NOT NULL,
  triggerWordCreator    varchar(15)       NOT NULL default 'unknown',
  triggerWordCount      int(11)     default NULL,
  triggerWordComment    text,
  PRIMARY KEY           (triggerWordID),
  UNIQUE KEY TW_TriggerCharacteristic (triggerWordName,triggerWordVersion,triggerWordBits)
) TYPE=MyISAM;




DROP TABLE IF EXISTS CollisionTypes; CREATE TABLE CollisionTypes
(
  collisionTypeID SMALLINT NOT NULL AUTO_INCREMENT,
  firstParticle VARCHAR(10) NOT NULL,
  secondParticle VARCHAR(10) NOT NULL,
  collisionEnergy FLOAT NOT NULL,
  PRIMARY KEY (collisionTypeID)
) TYPE=MyISAM;


#
# A few dictionary tables
#
DROP TABLE IF EXISTS ProductionConditions; CREATE TABLE ProductionConditions
(
  productionConditionID         SMALLINT       NOT NULL      AUTO_INCREMENT,
  productionTag                 VARCHAR(10)   NOT NULL,
  libraryVersion                VARCHAR(10)   NOT NULL,

  productionConditionIDate      TIMESTAMP       NOT NULL,
  productionConditionCreator    CHAR(15)        DEFAULT 'unknown' NOT NULL,
  productionConditionCount      INT,
  productionConditionComments   TEXT,
  PRIMARY KEY                   (productionConditionID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS StorageSites; CREATE TABLE StorageSites
(
  storageSiteID                 SMALLINT      NOT NULL     AUTO_INCREMENT,
  storageSiteName               VARCHAR(30)  NOT NULL,
  storageSiteLocation           VARCHAR(50),

  storageSiteIDate              TIMESTAMP       NOT NULL,
  storageSiteCreator            CHAR(15)       DEFAULT 'unknown' NOT NULL,
  storageSiteCount              INT,
  storageSiteComment            TEXT,
  PRIMARY KEY                   (storageSiteID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS FileTypes; CREATE TABLE FileTypes
(
  fileTypeID                    SMALLINT NOT NULL        AUTO_INCREMENT,
  fileTypeName                  VARCHAR(30)    NOT NULL   UNIQUE,
  fileTypeExtension             VARCHAR(15)        NOT NULL,

  fileTypeIDate                 TIMESTAMP       NOT NULL,
  fileTypeCreator               CHAR(15) DEFAULT 'unknown' NOT NULL,
  fileTypeCount                 INT,
  fileTypeComment               TEXT,
  PRIMARY KEY                   (fileTypeID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS FilePaths; CREATE TABLE FilePaths
(
  filePathID                    BIGINT         NOT NULL         AUTO_INCREMENT,
  filePathName                  VARCHAR(255)   NOT NULL         UNIQUE,

  filePathIDate                 TIMESTAMP       NOT NULL,
  filePathCreator               CHAR(15) DEFAULT 'unknown' NOT NULL,
  filePathCount                 INT,
  filePathComment               TEXT,
  PRIMARY KEY                   (filePathID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS Hosts; CREATE TABLE Hosts
(
  hostID                        SMALLINT       NOT NULL         AUTO_INCREMENT,
  hostName                      VARCHAR(30)    NOT NULL DEFAULT 'localhost' UNIQUE,

  hostIDate                     TIMESTAMP       NOT NULL,
  hostCreator                   CHAR(15)     DEFAULT 'unknown' NOT NULL,
  hostCount                     INT,
  hostComment                   TEXT,
  PRIMARY KEY                   (hostID)
) TYPE=MyISAM;


DROP TABLE IF EXISTS RunTypes; CREATE TABLE RunTypes
(
  runTypeID                     SMALLINT  NOT NULL AUTO_INCREMENT,
  runTypeName                   VARCHAR(255)    NOT NULL   UNIQUE,

  runTypeIDate                  TIMESTAMP       NOT NULL,
  runTypeCreator                CHAR(15)  DEFAULT 'unknown' NOT NULL,
  runTypeCount                  INT,
  runTypeComment                TEXT,
  PRIMARY KEY                   (runTypeID)
) TYPE=MyISAM;


DROP TABLE IF EXISTS StorageTypes; CREATE TABLE StorageTypes
(
  storageTypeID                 MEDIUMINT       NOT NULL    AUTO_INCREMENT,
  storageTypeName               VARCHAR(6)   NOT NULL  UNIQUE,

  storageTypeIDate              TIMESTAMP       NOT NULL,
  storageTypeCreator            CHAR(15)       DEFAULT 'unknown' NOT NULL,
  storageTypeCount              INT,
  storageTypeComment            TEXT,
  PRIMARY KEY                   (storageTypeID)
) TYPE=MyISAM;





DROP TABLE IF EXISTS SimulationParams; CREATE TABLE SimulationParams
(
  simulationParamsID            INT             NOT NULL     AUTO_INCREMENT,
  eventGeneratorID              SMALLINT    NOT NULL,
  detectorConfigurationID       INT             NOT NULL,
  simulationParamComments       TEXT,
  PRIMARY KEY                   (simulationParamsID),
  INDEX         SP_EventGeneratorIndex          (eventGeneratorID),
  INDEX         SP_DetectorConfigurationIndex   (detectorConfigurationID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS RunParams;
CREATE TABLE RunParams
(
  runParamID                  INT        NOT NULL AUTO_INCREMENT,
  runNumber                   BIGINT     NOT NULL UNIQUE,
  dataTakingStart             TIMESTAMP,
  dataTakingEnd               TIMESTAMP,
  simulationParamsID          INT       NULL,
  runTypeID                   SMALLINT     NOT NULL,
  triggerSetupID              SMALLINT      NOT NULL,
  detectorConfigurationID     INT            NOT NULL,
  collisionTypeID             SMALLINT             NOT NULL,
  magFieldScale               VARCHAR(50)    NOT NULL,
  magFieldValue               FLOAT,
  runComments                 TEXT,
  PRIMARY KEY                          (runParamID),
  INDEX RP_RunNumberIndex              (runNumber),
  INDEX RP_DataTakingStartIndex        (dataTakingStart),
  INDEX RP_DataTakingEndIndex          (dataTakingEnd),
  INDEX RP_MagFieldScaleIndex          (magFieldScale),
  INDEX RP_MagFieldValueIndex          (magFieldValue),
  INDEX RP_SimulationParamsIndex       (simulationParamsID),
  INDEX RP_RunTypeIndex                (runTypeID),
  INDEX RP_TriggerSetupIndex           (triggerSetupID),
  INDEX RP_DetectorConfigurationIndex  (detectorConfigurationID),
  INDEX RP_CollisionTypeIndex          (collisionTypeID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS FileData; CREATE TABLE FileData
(
  fileDataID                    BIGINT          NOT NULL AUTO_INCREMENT,
  runParamID                    INT             NOT NULL,
  fileName                      VARCHAR(255)       NOT NULL,
  baseName                      VARCHAR(255)       NOT NULL COMMENT 'Name without extension',
  sName1                        VARCHAR(255) NOT NULL COMMENT 'Will be used for name+runNumber',
  sName2                        VARCHAR(255) NOT NULL COMMENT 'Will be used for name before runNumber',
  productionConditionID         INT             NULL,
  numEntries                    MEDIUMINT,
  md5sum                        CHAR(32)     DEFAULT 0,
  fileTypeID                    SMALLINT NOT NULL,
  fileSeq                       SMALLINT,
  fileStream                    SMALLINT,
  fileDataComments              TEXT,
  PRIMARY KEY                   (fileDataID),
  INDEX         FD_FileNameIndex                (fileName(40)),
  INDEX         FD_BaseNameIndex                (baseName),
  INDEX         FD_SName1Index                  (sName1),
  INDEX         FS_SName2Index                  (sName2),
  INDEX         FD_RunParamsIndex               (runParamID),
  INDEX         FD_ProductionConditionIndex     (productionConditionID),
  INDEX         FD_FileTypeIndex                (fileTypeID),
  INDEX         FD_FileSeqIndex                 (fileSeq),
  UNIQUE        FD_FileDataUnique               (runParamID, fileName, productionConditionID, fileTypeID, fileSeq)
) TYPE=MyISAM;



# FileParents
DROP TABLE IF EXISTS FileParents; CREATE TABLE FileParents
(
  parentFileID                  BIGINT          NOT NULL,
  childFileID                   BIGINT          NOT NULL,
  PRIMARY KEY                   (parentFileID, childFileID)
) TYPE=MyISAM;

# FileLocations
DROP TABLE IF EXISTS FileLocations; CREATE TABLE FileLocations
(
  fileLocationID                BIGINT          NOT NULL      AUTO_INCREMENT,
  fileDataID                    BIGINT          NOT NULL,
  filePathID                    BIGINT          NOT NULL,
  storageTypeID                 MEDIUMINT       NOT NULL,
  createTime                    TIMESTAMP,
  insertTime                    TIMESTAMP       NOT NULL,
  owner                         VARCHAR(30),
  fsize                         BIGINT,
  storageSiteID                 SMALLINT      NOT NULL,
  protection                    VARCHAR(15),
  hostID                        BIGINT          NOT NULL DEFAULT 1,
  availability                  TINYINT         NOT NULL DEFAULT 1,
  persistent                    TINYINT         NOT NULL DEFAULT 0,
  sanity                        TINYINT         NOT NULL DEFAULT 1,
  PRIMARY KEY                   (fileLocationID),
  INDEX         FL_FilePathIndex                (filePathID),
  INDEX         FL_FileDataIndex                (fileDataID),
  INDEX         FL_StorageTypeIndex             (storageTypeID),
  INDEX         FL_StorageSiteIndex             (storageSiteID),
  INDEX         FL_HostIndex                    (hostID),
  UNIQUE        FL_FileLocationUnique           (fileDataID, storageTypeID, filePathID, storageSiteID, hostID)
) TYPE=MyISAM;