Tables creation and attributes
#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;