#--------------------------------------------------------------------------------------------------------------
#
# The mysql schema of the Failure Trace Archive Format
# (change database name FTA as needed)
#
# Contact: Derrick Kondo derrick.kondo :: inria fr
#
# Copyright 2009 by Failure Trace Archive site (http://fta.inria.fr)
# 
#--------------------------------------------------------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS fta;
use fta;

DROP TABLE IF EXISTS platform;
DROP TABLE IF EXISTS node;
DROP TABLE IF EXISTS creator;
DROP TABLE IF EXISTS node_perf;
DROP TABLE IF EXISTS component;
DROP TABLE IF EXISTS event_trace;
DROP TABLE IF EXISTS event_state;


# Platform Table Entries: platform
# platform_id	# unique number identifiying this platform.  Allows one to differentiate or pool together different platforms.  
# platform_name	# name of the platform (e.g. "Berkeley NOW Lab Fall 1998")
# platform_location  # location name of the trace platform source (e.g. "Berkeley NOW Lab - Soda Hall 2nd Floor, USA, Planet Earth")
# platform_type	# type of the trace source (from single_machine, cluster, multicluster, grid, desktop_grid, volunteer_computing)
# misc_notes	# other notes relating to the trace platform

CREATE TABLE platform (
    platform_id INT(10) UNSIGNED,
    platform_name VARCHAR(254),
    platform_location VARCHAR(254),
    platform_type VARCHAR(254),
    notes VARCHAR(254),
    PRIMARY KEY (platform_id)
);

# Node Table Entries: node
# platform_id	# id of the parent trace description table
# node_id	# unique ID for this node
# node_name	# name of node
# node_ip	# IP address
# node_location # location of the node (e.g. country, geographic coordinates)
# timezone	# time zone of the resource (second offset from GMT)
# proc_model	# processor name, model, version number
# os_name	# name and version of the resource OS
# cores_per_proc # number of cores per processor
# num_procs	# number of processors for this resource
# mem_size	# number of bytes of memory
# disk_size	# number of bytes of disk space
# up_bw		# number of bytes/sec of upload speed
# down_bw	# number of bytes/sec of download speed
# metric_id	# unique ID for performance metric (benchmark)
# notes	    	# other notes related to this resource

CREATE TABLE node (
    node_id INT(11) UNSIGNED,
    platform_id INT(11) UNSIGNED,
    node_name VARCHAR(254),
    node_ip VARCHAR(254),
    node_location VARCHAR(254),
    timezone INT(10),
    proc_model VARCHAR(254),
    os_name VARCHAR(254),
    cores_per_proc INT(11) UNSIGNED,
    num_procs INT(11) UNSIGNED,
    mem_size DOUBLE, 
    disk_size DOUBLE,
    up_bw DOUBLE,
    down_bw DOUBLE,
    metric_id DOUBLE,
    notes VARCHAR(254),	   
    PRIMARY KEY (node_id, platform_id)
);

# Node Performance Table Entires: node_perf
# metric_id	# unique ID for performance metric (benchmark)
# node_id 	# unique ID for this node
# platform_id	# ID of measurement platform that is the node parent
# sfpop_speed	# maximum single precision floating point speed (ops/sec)
# dfpop_speed	# maximum double precision floating point speed (ops/sec)
# iop_speed	# integer operation speed (ops/sec)
# i_val		# integer
# f_val		# float
# s_val		# string

CREATE TABLE node_perf (
    metric_id INT(11) UNSIGNED,
    node_id INT(11) UNSIGNED, 	
    platform_id INT(11) UNSIGNED,	
    sfpop_speed DOUBLE,
    dfpop_speed DOUBLE,
    iop_speed DOUBLE,
    i_val INT(11), 
    f_val DOUBLE,
    s_val VARCHAR(254),
    PRIMARY KEY (metric_id, node_id, platform_id)
);


# Trace Description Table Entries: component
# component_id	# unique ID for this trace description platform
# node_id		# ID of the parent resource corresponding to this trace description
# platform_id		# ID of measurement platform that is the node parent
# creator_id	# ID of creator of this component trace data
# node_name	# node name
# component_type	# type of this availability trace (0 -> host availability, network, CPU, client, memory, etc)
# trace_start	# when the trace event first appeared (UNIX time). Can be NULL if component was always available.
# trace_end	# when the trace event last appeared (UNIX time).  Can be NULL if component was always available.
# resolution	# resolution of the traces in seconds

CREATE TABLE component (
    component_id INT(11) UNSIGNED,
    node_id INT(11) UNSIGNED,	
    platform_id INT(11) UNSIGNED,	
    creator_id INT(11) UNSIGNED,
    node_name VARCHAR(254),
    component_type INT(11) UNSIGNED,
    trace_start	DOUBLE,
    trace_end DOUBLE,
    resolution DOUBLE,
    PRIMARY KEY (component_id, node_id, platform_id)
);


# Creator Table Entries: creator
# creator_id	# ID of creator of this component trace data
# component_id	# unique ID for this component trace data
# node_id	# ID of the node corresponding to this trace 
# platform_id	# ID of platform that is the node parent
# creator	# name(s) of the person(s) who recorded the event traces
# cite		# citation (bibtex, etc) for using the data from the event traces
# copyright	# copyright statement about how data set can be used

CREATE TABLE creator (
    creator_id INT(11) UNSIGNED,
    component_id INT(11) UNSIGNED,	
    node_id INT(11) UNSIGNED,
    platform_id INT(11) UNSIGNED,	
    creator VARCHAR(254),
    cite VARCHAR(254), 
    copyright VARCHAR(254),
    PRIMARY KEY (creator_id, component_id, node_id, platform_id)
);







# Event Trace Table Entries: event_trace
# event_id	# unique ID of event
# component_id	# unique ID for this trace description platform
# node_id		# ID of the parent resource corresponding to this trace description
# event_state	# state of event (for example, 0% - 100% for CPU availability)
# node_name	# name of node
# event_type	# type of event (0 -> unavailability, 1-> availability).  Event id's up to 10,000 are reserved; the rest can be user defined
# event_start_time	# start of this event (UNIX time)
# event_end_time	# end of this event (UNIX time)
# event_end_reason	# reason the event type or state changed at the end of this trace (for example, reason that CPU became unavailable: 0=undefined, 1=miscellaneous, 2=mouse_activity, 3=keyboard_activity, 4=scheduled_downtime, 5=graceful_shutdown, 6=hard_shutdown)

CREATE TABLE event_trace (
    event_id INT(11) UNSIGNED,
    component_id INT(11) UNSIGNED,	
    node_id INT(11) UNSIGNED,
    platform_id INT(11) UNSIGNED,		
    node_name VARCHAR(254),
    event_type INT(11) UNSIGNED,
    event_start_time DOUBLE,
    event_end_time DOUBLE,
    event_end_reason INT(11) UNSIGNED,
    PRIMARY KEY (event_id, component_id, node_id, platform_id)
    );


# Event State Table Entries: event_state
# event_id	# unique ID of event state
# component_id	# unique ID for this trace description platform
# node_id		# ID of the parent resource corresponding to this trace description
# platform_id		# ID of measurement platform that is the node parent
# i_val		# integer
# f_val		# float
# s_val		# string

CREATE TABLE event_state  (
    event_id INT(11) UNSIGNED,
    component_id INT(11) UNSIGNED,
    node_id INT(11) UNSIGNED,		
    platform_id INT(11) UNSIGNED,
    i_val INT(11),
    f_val DOUBLE,
    s_val VARCHAR(254),
    PRIMARY KEY (event_id, component_id, node_id, platform_id)
    );

