
--
-- Table structure for table `ROCs`
-- Only the admins of the application (GOC admins) can modify
-- the 'admin' field below. It contains a certificate subject
-- (or a list of certificate subjects). The people that owns
-- this certificate can modify the other fields of the record (ROC),
-- can validate country and site admins etc.
--

CREATE TABLE ROCs (
  ROC_id varchar(10),     -- SEE-GRID etc.
  name varchar(100),      -- South-East Europe GRID 
  admin text,             -- a list of certificate subjects
  email varchar(255),     -- can be a list of emails
  phone varchar(255),     -- can be a list of phones
  comments text,          -- general comments
  timestamp varchar(255), -- last modified
  PRIMARY KEY  (ROC_id)
);

--
-- Table structure for table `countries`
-- The field 'admin' can be modified only by a ROC admin (of the
-- corresponding ROC). It contains a certificate subject
-- (or a list of certificate subjects). The people that owns
-- this certificate can modify the other fields of the record (country),
-- can add/delete sites, can validate site admins etc.
--

CREATE TABLE countries (
  country_id varchar(5),  -- AL, BG, etc.
  ROC_id varchar(10),
  name varchar(100),      -- Albania, Bullgaria, etc.
  admin varchar(100),     -- a list of certificate subjects
  email varchar(255),     -- can be a list of emails
  phone varchar(255),     -- can be a list of phones
  comments text,          -- general comments
  timestamp varchar(255), -- last modified
  PRIMARY KEY  (country_id)
);

--
-- Table structure for table `sites`
--

CREATE TABLE sites (
  site_id varchar(20),    -- like INIMA, HR-01-RBI, etc.
  country_id varchar(5),
  admin text,             -- a list of certificate subjects

  name varchar(100),      -- like: Institute of Informatics
  homepage VARCHAR(100),  -- http://see-grid.inima.al or http://www.inima.al
  giis_url VARCHAR(100),
  -- maybe other links to monitoring pages?

  monitoring INTEGER,
  status INTEGER,
  type INTEGER,

  email VARCHAR(30),      -- grid@inima.al
  phone VARCHAR(30),
  emergency_phone VARCHAR(30),
  csirt_email VARCHAR(30),
  csirt_phone VARCHAR(30),
  hours_available VARCHAR(30),
  timezone INTEGER,

  -- resources
  OS VARCHAR(30),
  middleware VARCHAR(30),
  batch_system VARCHAR(30),
  VOs_supported VARCHAR(255),  -- DTEAM, SEEGRID
  apps_supported VARCHAR(255), -- VIVE, SE4SEE, ATLAS
  WN_count INTEGER,
  storage_type varchar(30),
  storage_size INTEGER,  -- in GB
  lan_connection FLOAT,
  wan_connecion FLOAT,
  comments text,

  timestamp varchar(255), -- last modified
  PRIMARY KEY  (site_id)
);

--
-- Table structure for table `contacts`
--

CREATE TABLE site_contacts (
  contact_id int(10) unsigned NOT NULL auto_increment,
  site_id varchar(20),
  name varchar(100),   -- Dashamir Hoxha
  email varchar(30),   -- dhoxha@inima.al
  phone varchar(30),
  role varchar(100),   -- System Administrator of INIMA
  hours_available varchar(100),
  PRIMARY KEY (contact_id)
);

--
-- Table structure for table `downtimes`
--

CREATE TABLE site_downtimes (
  downtime_id int(10) unsigned NOT NULL auto_increment,
  site_id varchar(20),
  description varchar(100),
  start varchar(100),
  endtime varchar(100),
  PRIMARY KEY (downtime_id)
);

--
-- Table structure for table `nodes`
--

CREATE TABLE site_nodes (
  node_id int(10) unsigned NOT NULL auto_increment,
  site_id varchar(20),
  type varchar(30),           -- CE+SE+MON
  machine_name varchar(100),  -- prof.inima.al
  comments varchar(100),

  -- maybe these can be retrived automatically from information services
  os varchar(100),            -- SL304
  arch varchar(20),           -- i386
  middleware varchar(2)       -- LCG-2_6_0
  cpu varchar(100),
  ram varchar(100),
  storage varchar(100),
  PRIMARY KEY  (node_id)
);


-------------------------------------------------------------------
--  AUXILIARY TABLES
-------------------------------------------------------------------

CREATE TABLE timezones (
  timezone_id VARCHAR(10) NOT NULL,
  offset 	INTEGER,
  PRIMARY KEY (timezone_id)
);

CREATE TABLE VOs (
  VO_id VARCHAR(10) NOT NULL,
  name VARCHAR(30),
  PRIMARY KEY (VO_id)
);

CREATE TABLE nodetypes (
  node_type VARCHAR(10) NOT NULL,
  PRIMARY KEY (node_type)
);
