Examples and other documentation

A few examples will be left here to guide users and installer.

#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;

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<!DOCTYPE SCATALOG [
   <!ELEMENT SCATALOG (SITE*)>
       <!ATTLIST SCATALOG VERSION CDATA #REQUIRED>
   <!ELEMENT SITE (SERVER+)>
       <!ATTLIST SITE name (BNL | LBL) #REQUIRED>
       <!ATTLIST SITE description CDATA #IMPLIED>
       <!ATTLIST SITE URI CDATA #IMPLIED>
   <!ELEMENT SERVER (HOST+)>
       <!ATTLIST SERVER SCOPE (Master | Admin | User) #REQUIRED>
   <!ELEMENT HOST (ACCESS+)>
       <!ATTLIST HOST NAME CDATA #REQUIRED>
       <!ATTLIST HOST DBTYPE CDATA #IMPLIED>
       <!ATTLIST HOST DBNAME CDATA #REQUIRED>
       <!ATTLIST HOST PORT CDATA #IMPLIED>
   <!ELEMENT ACCESS EMPTY>
       <!ATTLIST ACCESS USER CDATA #IMPLIED>
       <!ATTLIST ACCESS PASS CDATA #IMPLIED>
]>



<SCATALOG VERSION="1.0.1">
        <SITE name="BNL">
                <SERVER SCOPE="Master">
                        <HOST NAME="mafata.wherever.net" DBNAME="Catalog_XXX" PORT="1234">
                                <ACCESS USER="Moi" PASS="HelloWorld"/>
                        </HOST>
                        <HOST NAME="mafata.wherever.net" DBNAME="Catalog_YYY" PORT="1235">
                                <ACCESS USER="Moi" PASS="HelloWorld"/>
                        </HOST>
                        <HOST NAME="duvall.star.bnl.gov" DBNAME="FileCatalog" PORT="">
                                <ACCESS USER="FC_master" PASS="AllAccess"/>
                        </HOST>
                </SERVER>
                <SERVER SCOPE="Admin">
                        <HOST NAME="duvall.star.bnl.gov" DBNAME="FileCatalog_BNL" PORT="">
                                <ACCESS USER="FC_admin" PASS="ExamplePassword"/>
                        </HOST>
                </SERVER>
                <SERVER SCOPE="User">
                        <HOST NAME="duvall.star.bnl.gov" DBNAME="FileCatalog_BNL" PORT="">
                                <ACCESS USER="FC_user" PASS="FCatalog"/>
                        </HOST>
                </SERVER>
        </SITE>
</SCATALOG>

Migration and notes from V01.265 to V01.275

This document is intended for FileCatalog managers only who have previously deployed an earlier version of API and older database table layout. It is NOT intended for users.

Reasoning for this upgrade and core of the upgrade

One of the major problem with the preceding database layout started to show itself when we reached 4 Million entries (for some reason, we seem to have magic numbers). A dire restriction was the presence of the field 'path' and 'nodename' in the FileLocations table. This table became unnecessarily large (of the order of GB) and sorting and queries would become slow and IO demanding (regardless of our careful indexing). The main action was to move both field to separate tables. This change requires a two step modification :

1. reshape of the database (leaving the old field), deployment of the database API in cross mode support
2. run the normalization scripts filling the new table and fields, deployment of the final API and drop of the obsolete columns (+ index rebuild)

The steps are more carefully described below ...

• Step by steps (general)
• Steps in Phase I
• Steps in Phase II
• Notes

Step by step migration instructions

Has to be made in several steps for safety a least interruption of service (although a pain to the manager). Note that you can do that much faster by cutting the Master/slave relationship, disabling all daemons auto-updating the database, proceed with table reshape and normalization script execution, drop and rebuild index, deploy the point-of-no-return API and restore Master/slave relation).

This upgrade is best if you have perl 5.8 or upper. Note that this transition will be the LAST one using perl 5.6 (get ready for a perl upgrade on your cluster).

We will assume you know how to connect to your database from an account able to manipulate and create any tables in the FileCatalog database.

Steps in Phase I

1. (0) Create the following tables

     DROP TABLE IF EXISTS FilePaths; CREATE TABLE FilePaths
     (
       filePathID                    BIGINT         NOT NULL         AUTO_INCREMENT,
       filePathName                  VARCHAR(255)   NOT NULL         UNIQUE,
       filePathCount                 INT,
       PRIMARY KEY                   (filePathID)
     ) TYPE=MyISAM;
   
     DROP TABLE IF EXISTS Hosts; CREATE TABLE Hosts 
    (
       hostID      smallint(6) NOT NULL auto_increment,
       hostName    varchar(30) NOT NULL default 'localhost',
       hostIDate   timestamp(14) NOT NULL,
       hostCreator varchar(15) NOT NULL default 'unknown',
       hostCount   int(11) default NULL,
       hostComment text,
       PRIMARY KEY (hostID),
       UNIQUE KEY  hostName (hostName)
     ) TYPE=MyISAM;
   
   

2. Modify some table and recreate one

        
        ALTER TABLE `FileLocations` ADD `filePathID` bigint(20) NOT NULL default '0' AFTER `fileDataID`;
        ALTER TABLE `FileLocations` ADD `hostID` bigint(20) NOT NULL default '1' AFTER `protection`;
        UPDATE TABLE `FileLocations` SET hostID=0;
   
        # note that I did that one from the Web interface (TBC)
        INSERT INTO Hosts VALUES(0,'localhost',NOW()+0,'',0,'Any unspecified node'); 
   
        ALTER TABLE `FileLocations` ADD INDEX ( `filePathID` )  
   
        ALTER TABLE `FilePaths` ADD `filePathIDate` TIMESTAMP NOT NULL AFTER `filePathName` ;
        ALTER TABLE `FilePaths` ADD `filePathCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `filePathIDate` ;
        ALTER TABLE `FilePaths` ADD `filePathComment` TEXT AFTER `filePathCount`;
   
        ALTER TABLE `StorageSites` ADD  `storageSiteIDate` TIMESTAMP NOT NULL AFTER `storageSiteLocation` ;
        ALTER TABLE `StorageSites` ADD  `storageSiteCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `storageSiteIDate` ;
        ALTER TABLE `StorageSites` DROP `storageComment`;
        ALTER TABLE `StorageSites` ADD  `storageSiteComment` TEXT AFTER `storageSiteCount`;
   
        ALTER TABLE `StorageTypes` ADD `storageTypeIDate` TIMESTAMP NOT NULL AFTER `storageTypeName` ;
        ALTER TABLE `StorageTypes` ADD `storageTypeCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `storageTypeIDate` ;
   
   
        ALTER TABLE `FileTypes` ADD `fileTypeIDate` TIMESTAMP NOT NULL AFTER `fileTypeExtension` ;
        ALTER TABLE `FileTypes` ADD `fileTypeCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `fileTypeIDate` ;
        ALTER TABLE `FileTypes` ADD `fileTypeComment` TEXT AFTER `fileTypeCount`;
   
   
        ALTER TABLE `TriggerSetups` ADD `triggerSetupIDate` TIMESTAMP NOT NULL AFTER `triggerSetupComposition` ;
        ALTER TABLE `TriggerSetups` ADD `triggerSetupCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `triggerSetupIDate`;
        ALTER TABLE `TriggerSetups` ADD `triggerSetupCount`   INT AFTER `triggerSetupCreator`;
        ALTER TABLE `TriggerSetups` ADD `triggerSetupComment` TEXT  AFTER `triggerSetupCount`;
   
        ALTER TABLE `EventGenerators` ADD `eventGeneratorIDate` TIMESTAMP NOT NULL AFTER `eventGeneratorParams` ;
        ALTER TABLE `EventGenerators` ADD `eventGeneratorCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `eventGeneratorIDate` ;
        ALTER TABLE `EventGenerators` ADD `eventGeneratorCount`   INT AFTER `eventGeneratorCreator`;
   
        ALTER TABLE `RunTypes` ADD `runTypeIDate` TIMESTAMP NOT NULL AFTER `runTypeName` ;
        ALTER TABLE `RunTypes` ADD `runTypeCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `runTypeIDate` ;
   
        ALTER TABLE `ProductionConditions` DROP `productionComments`; 
        ALTER TABLE `ProductionConditions` ADD  `productionConditionIDate`   TIMESTAMP NOT NULL AFTER `libraryVersion`;
        ALTER TABLE `ProductionConditions` ADD  `productionConditionCreator` CHAR( 15 ) DEFAULT 'unknown' NOT NULL AFTER `productionConditionIDate`;
        ALTER TABLE `ProductionConditions` ADD  `productionConditionComment` TEXT AFTER `productionConditionCount`;
   
   
   
        #
        # This table was not shaped as a dictionary so needs to be re-created
        # Hopefully, was not filled prior (but will be this year)
        #
        DROP TABLE IF EXISTS TriggerWords; CREATE TABLE TriggerWords
        (
           triggerWordID           MEDIUMINT       NOT NULL        AUTO_INCREMENT,
           triggerWordName         VARCHAR(50)     NOT NULL,
           triggerWordVersion      CHAR(6)         NOT NULL DEFAULT "V0.0",
           triggerWordBits         CHAR(6)         NOT NULL,  
           triggerWordIDate        TIMESTAMP       NOT NULL,
           triggerWordCreator      CHAR(15)        DEFAULT 'unknown' NOT NULL,
           triggerWordCount        INT,
           triggerWordComment      TEXT,
           UNIQUE   TW_TriggerCharacteristic (triggerWordName, triggerWordVersion, triggerWordBits),
           PRIMARY KEY             (triggerWordID)
        ) TYPE=MyISAM;

3. Deploy the new API CVS version 1.62 of FileCatalog.pm

4. Run the following utility scripts

   util/path_convert.pl
   util/host_convert.pl

   Note that those scripts use a new method $fC->connect_as("Admin"); which assumes that the Master Catalog will be accessed using the XML connection description. Also, it should be obvious that

   use lib "/WhereverYourModulAPIisInstalled"; should be replaced by the appropriate path for your site (or test area). Finally, it uses API CVS version 1.62 which supports Xpath and Xnode transitional keywords allowing us to transfer the information from one field to one table.

5. Check that Hosts table was filled properly and automatically with Creator/IDate
6. Paranoia step : Re-run the scripts mentioned 2 steps ago
   
   At this stage and ideally, nothing should happen (as you have already modified the records).
   A few tips prior from doing that
   • % fC_cleanup.pl -modif node=localhost -cond node='' -doit
     would hopefully do nothing but if you have messed something up in the past, hostName would be NULL and the above would be necessary.
   • After a full update, the following queries should return NOTHING
     % get_file_list.pl -keys flid -cond rfpid=0 -all -alls -as Admin
     % get_file_list.pl -keys flid -cond rhid=0 -all -alls -as Admin
     
     Those are equivalent to the SQL statements

     >SELECT FileLocations.fileLocationID FROM FileLocations WHERE FileLocations.filePathID = 0 LIMIT 0, 100
     >SELECT FileLocations.fileLocationID FROM FileLocations WHERE FileLocations.hostID = 0 LIMIT 0, 100 

   If it does return anything, contact me for further investigation and database repairs. As a side note, the -as keyword was introduced recently and you should update your get_file_list.pl script if not available.
7. Make a backup copy of the database for security (optional but safer) Backup can be done by easer a dump of mysql or more trivially, a cp -r of the database directory.
8. Leave it running for a few days (should be fine) for confidence consolidation ;-)

You are ready for phase II. Hang on tight now ...

Steps in Phase II

Those steps are no VERY intrusive and potentially destructive. Be careful from here on ...

1. Stop all daemons, be sure that during the rest of the operations, NO command attempts to manipulate the database. If you want to shield your users from the upgrade, stop all Master/slave relations.
2. Connect to the master FileCatalog as administrator for that database and execute the following SQL commands

     > ALTER TABLE `FileLocations` ADD INDEX FL_HostIndex (hostID);
     > ALTER TABLE `FileLocations` DROP INDEX `FL_FileLocationUnique`, ADD UNIQUE (fileDataID, storageTypeID, filePathID, storageSiteID, hostID);
   
     # drop the columns not in use anymore / should also get rid of the associated
     # indexes.
     > ALTER TABLE `FileLocations` DROP COLUMN nodeName;
     > ALTER TABLE `FileLocations` DROP COLUMN filePath;
   
     # "rename" index / was created with a name difference to avoid clash for transition
     # now renamed for consistency
     > ALTER TABLE `FileLocations` DROP INDEX `filePathID`, ADD INDEX  FL_FilePathIndex (filePathID);

3. OK, you should be done. Deploy either CVS version 1.63 which correspond to the FileCatalog API version V01.275 and above ... (by the way, get_file_list.pl -V gives the API version).

A few notes

• The new API is XML connection aware via a non-mandatory module named XML::Simple . You should install that module but there are some limitations if you are using perl 5.6 i.e., you MUST use the schema with ONLY one choice per category (Admin, Master or User).
• Your scripts will likely need to change if your Database Master and Slave are not on the same node (i.e. the administration account for the FileCatalog can be used only on the database Master and the regular user account on the Slave). There are a few forms of this such as the one below

  # Get connection fills the blanks while reading from XML
  # However, USER/PASSWORD presence are re-checked
  #$fC->debug_on();
  ($USER,$PASSWD,$PORT,$HOST,$DB) = $fC->get_connection("Admin");
  $port = $PORT if ( defined($PORT) );
  $host = $HOST if ( defined($HOST) );
  $db   = $DB   if ( defined($DB) );
  
  
  if ( defined($USER) ){   $user = $USER;}
  else {                   $user = "FC_admin";}
  
  if ( defined($PASSWD) ){ $passwd = $PASSWD;}
  else {                   print "Password for $user : ";
                           chomp($passwd = );}
  
  #
  # Now connect using a fully specified user/passwd/port/host/db
  #
  $fC->connect($user,$passwd,$port,$host,$db);

  or counting on the full definition in the XML file

  $fC    = FileCatalog->new();
  $fC->connect_as("Admin");

• Note a small future convenience when XML is ON. connect_as() does not only select as who you want to connect to but where as well. In fact, the proper syntax is intent=SITE::User (for example BNL::Admin is valid as well as LBL::User). This is only partly supported however.
• The new version of the API automatically add information in dictionary tables. Especially, the account under which a new dictionary value was inserted (Creator) and the insertion date (IDate) are filled automatically. A side effect being that the new API is NOT compatible with previous database table layout (no backward support will be attempted).

Migration and notes from V01.275 to V01.280

This document is intended for FileCatalog managers only who have previously deployed an earlier version of API and older database table layout. It is NOT intended for users.

Reasoning for this upgrade and core of the upgrade

This upgrade is a minor one, making support for two more detector sub-systems. The new API supports this modification. You need to alter the table DetectorConfigurations and add two columns. API are always forward compatible in that regard so it is completely safe to alter the table and deploy the API later.

ALTER TABLE `DetectorConfigurations` ADD dBSMD TINYINT;
ALTER TABLE `DetectorConfigurations` ADD dESMD TINYINT;
UPDATE `DetectorConfigurations` SET dBSMD=0;
UPDATE `DetectorConfigurations` SET dESMD=0;

And deploy the API V01.280 or later. You are done.

User manual

The command line interface to the FileCatalog

The FileCatalog database contains information about all files used in production for the STAR experiment. The database itself and the PERL module used to manipulate it are described later in this document. Below You'll find the description of the command line utility to access data in this database.
The utility is called get_file_list.pl. If issued without any arguments it will print the following usage message:

% get_file_list.pl [-all] -keys keyword[,keyword,...]              \    
  [-cond keyword=value[,keyword=value,...]] [-start #] [-limit #] [-delim $St] \    
  [-onefile] [-o outputfile]

Command line options

The command line options are described below:

Supported comparison or selection operators

Logical operators

The following logical operators can also be used in a query. The usage scope in this case is in a -cond context as keyword=Value1 LogicalOperator Value2 {LogicalOperator Value3 ...}

Note that the use of the logical AND operator will return no selection in most cases (for example, a runnumber cannot be of Value1 and Value2 at the same time) but was added for later extension of the database : selections based on meta data such as triggered-events (many triggers in a file) would be case where this operator would be used.

The aggregate functions

These are special aggregate functions. They can be used in conjunction with any keyword that describes some data. Note that most of them only make sense for numerical values. See examples for the description on how to use them.

Keyword list

Here are the keywords that can be used in the context. There is a color scheme to those keywords as follow
Keywords in blue are currently supported by the database schema but unused by the production scripts and therefore are not filled (or ill-filled).
Keywords in aqua are automatically updated (there is no need to reset)
Keywords in magenta are filled but update may be needed (do not strongly rely on their value)
 

keyword	Notes	Meaning
site		The site where the data is stored, eg. BNL, LBL
sitecmt		The site comment string
siteloc		A full string describing the site location in the world
storage		The storage medium, eg. HPSS, NFS, local disk. Note that the local disk storage does not allow for a unique file location. One must also select on node
node		The name of the node where data is stored (necessary to locate local disk storage)
path		the path to a specific copy of the file
filename		The name of the data file
sname1		The (short) name of the data file with the extensions removed. E.G. "st_physics_12114010_raw_4040002"
sname2		The (short) name of the data file with only the file name prefix remaining. E.G. "st_physics". Useful, for example, to isolate only st_physics files and rejecting "st_physics_adc" files.
filetype		The type of the file, e.g. "daq_reco_dst", "MC_fzd" etc ...
extension		The extension of the file - directly connected to type (each file type has an associated extension)
events		Number of events or entries in the file
size		The size of the data file
fileseq		The file sequence as determined during data taking by DAQ. Arbitrary for simulation and processed files.
stream		The file stream if applicable (defaut is 0)
md5sum	Early stage db fill did not update this field. It may return 0.	The file's md5 checksum
production		The production tag with which a given file was produced. Can also be "raw" or "simulation"
library		The library version this file was produced with
trgsetupname	Used in to encode the path in production	The name of the online trigger setup name
trgname		The name of one trigger in a collection of triggers associated to a runumber.
trgcount		The event count having the associated trgname for a given runnumber
trgword	This is available for Year4 data and beyond for DAQ files	The trigger word associated to one trigger in a collection
trgversion		The trigger word version associated to a trgname
trgdefinition		The trigger definition of one trigger in a collection
runtype		the type of the run - eg. "physics", "laser" , “pulser”, “pedestal”, “test” but also "simulation" for simulated datasets
configuration		The detector configuration name. A detector configuration is a combination of detectors that were present during data taking in a given run. Note tha the combination configuration/ geometry is unique (but not any of the two alone)
geometry		The geometry definition for a given simulation set.
runnumber		The number of the run. Arbitrary for simulations.
runcomments		The comments for a given run.
collision		The collision type. Specified in the form of <first particle><second particle><collision energy>, eg. "AuAu200"
datetaken	Format was messed up at conversion old->new Catalog. Can be (and will be) recovered.	The date the data was taken. Arbitrary for simulation.
magscale		The name of the magnetic field scale, e.g. FullField
magvalue		The actual magnetic field value
filecomment		The comment to the file.
owner		The owner of the file.
protection	Subject to changes	The protection or read/write permissions, given in a format similar to UNIX 'ls -l'
available		is the file available ? (0 if one cannot get it from HPSS or the file disappeared from disk)
persistent		is the file persistent ?
createtime	Only HPSS files have a createtime which is not subject to changes	the time a file was created. Format is YYYYmmddHHMMSS
inserttime		the time a file data was inserted into the database.
simcomment		The comments for the simulation
generator		The event generator name
genversion		Event generator version
gencomment		Event generator comments
genparams		Event generator params
tpc		was the TPC in the data stream when specific data was taken?
svt		was the SVT in the data stream when specific data was taken?
tof		was the TOF in the data stream when specific data was taken?
emc		was the B-EMC in the data stream when specific data was taken?
eemc		was the E-EMC in the data stream when specific data was taken?
fpd		was the FPD in the data stream when specific data was taken?
ftpc		was the FTPC in the data stream when specific data was taken?
pmd		was the PMD in the data stream when specific data was taken?
rich		was the RICH in the data stream when specific data was taken?
ssd		was the SSD in the data stream when specific data was taken?
bbc		was the BBC in the data stream when specific data was taken?
bsmd		was the Barrel EMC SMD in the data stream when specific data was taken?
esmd		was the End-Cap SMD in the data stream when specific data was taken?
zdc		was the Zero-Degree Calorimeter in the data stream when specific data was taken?
tpx		was the tpx (tpc-X) information in the data stream when data was taken?
fgt		was the Forward Gem Tracker information saved in this data stream?

The following keywords are for either internal use or specific management purposes. They have no meaning to users (but are unique).

flid	Access the FileLocation ID of the FileLocation table
fdid	Access the FileData ID of the FileData table
rfdid	Access the FileData ID of the FileLocation table
pcid	Access the ProductionCondition ID of the ProductionConditions table
rpcid	Access the ProductionCondition ID of the FileData table
rpid	Access the runParam ID of the runParams table
rrpid	Access the runParam ID of the FileData table
ftid	Access the FileType ID of the FileTypes table
rftid	Access the FileType ID of the FileData table
stid	Access the storageType ID of the StorageTypes table
rtid	Access the storageType ID of the FileLocations table
ssid	Access the storageSite ID of the StorageSites table
rssid	Access the storageSite ID of the FileLocations table
tcfdid	Access the FileData ID of the TriggerCompositions table
tctwid	Access the TriggerWords ID of the TriggerCompositions table
twid	Access the TriggerWords ID of the TriggerWords table
dcid	Access the detectorConfiguration ID of the DetectorConfigurations table
rdcid	Access the detectorConfiguration ID o the RunParams table
lgnm	An aggregate keyword returning an equivalence to the logical name
lgpth	An aggregate keyword returning a logical path (a string which uniquely characterize the file's location)
fulld	An aggregate keyword returning a string completely defining all meta-data for real data
fulls	An aggregate keyword returning a string completely defining all meta-data for simulation data

Here are the keywords not connected to a specific field in the database. They change the behaviour of the module itself.