scr.mysql

# This file defines the database tables used for MySQL logging.
# This feature is considered to be at beta-level support.  The
# following instructions should work, however, they have not
# been well tested.  If you find any problems, please notify
# the SCR developers.
#
# To use this feature, one must run a MySQL server on a host
# accessible to the compute nodes as well as the nodes that run
# the batch job scripts.  One must also have the libmysqlclient
# library installed.  Both the SCR library and the SCR commands
# insert records into the database.
#
# To set up the database, first define passwords in this file for
# the "scr" and "scr_insert" users in the "IDENTIFIED BY" fields
# in the lines below, e.g.,
#
#   GRANT ALL
#     ON scr.* TO `scr`
#     IDENTIFIED BY 'MySecretPassword1'
#   GRANT INSERT,SELECT
#     ON scr.* TO `scr_insert`
#     IDENTIFIED BY 'MySecretPassword2'
#
# Then provide this file as input to a mysql command on the host
# running the MySQL server, e.g.,.
#
#   mysql -u root -p < scr.mysql
#
# The SCR library and the SCR commands both read and write records
# to this database.  Thus, the database must be accessible to both
# the compute nodes and the nodes where the batch scripts run.
#
#   (There may be a step necessary here to adjust the "users" table
#   in the "mysql" database to enable remote access to the scr and
#   scr_insert users from the compute nodes and the nodes that run
#   the batch job script.)
#
# After creating the database, one can then specify the hostname,
# database name, username, and password in the system scr.conf file.
# For the time being, only the scr_insert user is used, e.g.,
#
#   >>: cat /etc/scr.conf
#   SCR_LOG_ENABLE=1
#   SCR_LOG_DB_ENABLE=1
#   SCR_LOG_DB_HOST=host.running.mysql.server
#   SCR_LOG_DB_NAME=scr
#   SCR_LOG_DB_USER=scr_insert
#   SCR_LOG_DB_PASS=MySecretPassword2

# !!!! WARNING !!!!
# Currently all users have full read and write access to the
# SCR logging database, so users can see data on jobs from
# other users.
# !!!! WARNING !!!!

CREATE DATABASE IF NOT EXISTS `scr`;

# !!!! TODO !!!!
# Define passwords in the IDENTIFIED BY fields before creating the
# database.
# !!!! TODO !!!!

# !!!! WARNING !!!!
# Be sure no passwords are recorded in this file when checking it
# into a revision control system.
# !!!! WARNING !!!!

GRANT ALL
  ON scr.* TO `scr`
  IDENTIFIED BY '';
GRANT INSERT,SELECT
  ON scr.* TO `scr_insert`
  IDENTIFIED BY '';

USE `scr`;

DROP TABLE IF EXISTS `types`;
DROP TABLE IF EXISTS `usernames`;
DROP TABLE IF EXISTS `jobnames`;
DROP TABLE IF EXISTS `jobs`;
DROP TABLE IF EXISTS `transfers`;
DROP TABLE IF EXISTS `events`;

# maps a type string to an id
CREATE TABLE IF NOT EXISTS `types` (
        `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(512) NOT NULL,
        UNIQUE INDEX `name` (`name`(512))
);

# maps a username string to an id
CREATE TABLE IF NOT EXISTS `usernames` (
        `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(512) NOT NULL,
        UNIQUE INDEX `name` (`name`(512))
);

# maps a jobname string to an id
CREATE TABLE IF NOT EXISTS `jobnames` (
        `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(512) NOT NULL,
        UNIQUE INDEX `name` (`name`(512))
);

# records username, jobname, and starttime of a job
# the same username and jobname maps to the same "job"
# which enables one to track data across a series of runs
CREATE TABLE IF NOT EXISTS `jobs` (
        `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `username_id` INT UNSIGNED NOT NULL, # id of user running the job
        `jobname_id`  INT UNSIGNED NOT NULL, # id of the name of the job
        `start`       DATETIME     NOT NULL, # time that job started
        UNIQUE INDEX `name` (`username_id`,`jobname_id`)
);

# records checkpoint, fetch, and flush operation times and directories
CREATE TABLE IF NOT EXISTS `transfers` (
        `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `job_id`        INT          NOT NULL, # id to jobs table
        `type_id`       INT UNSIGNED NOT NULL, # fetch, flush, or drain?
        `dset_id`       INT UNSIGNED NOT NULL, # SCR dataset number
        `dset_name`     VARCHAR(512) NOT NULL, # dataset name
        `start`         DATETIME     NOT NULL, # start time of operation
        `end`           DATETIME     NOT NULL, # end time of operation
        `secs`          FLOAT        NOT NULL, # total time of operation
        `bytes`         FLOAT        NOT NULL, # total bytes moved
        `files`         INT UNSIGNED NOT NULL, # total files moved
        `bw`            FLOAT        NOT NULL, # bandwidth of operation
        `from`          BLOB         NOT NULL, # source directory
        `to`            BLOB         NOT NULL, # target directory
        INDEX `jobid_start` (`job_id`,`start`),
        INDEX `jobid_typeid_start` (`job_id`,`type_id`,`start`),
        INDEX `typeid_start` (`type_id`,`start`)
);

# records various events, including failed nodes
CREATE TABLE IF NOT EXISTS `events` (
        `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `job_id`        INT          NOT NULL, # id to jobs table
        `type_id`       INT UNSIGNED NOT NULL, # fetch, flush, or drain?
        `dset_id`       INT UNSIGNED     NULL, # SCR dataset number
        `dset_name`     VARCHAR(512)     NULL, # dataset name
        `start`         DATETIME         NULL, # start time of operation
        `secs`          FLOAT            NULL, # total time of operation
        `note`          BLOB             NULL, # source directory
        INDEX `jobid_start` (`job_id`,`start`),
        INDEX `jobid_typeid_start` (`job_id`,`type_id`,`start`),
        INDEX `typeid_start` (`type_id`,`start`)
);