• Created by Unknown User (umar.kalim), last modified on Oct 30, 2009

Database Schema

The revised schema of the meta database [nodedetails-schema.sql] is as follows:

#DROP DATABASE IF EXISTS `IEPM`;
#CREATE DATABASE `IEPM`;

DROP TABLE IF EXISTS `USERS`;
CREATE TABLE `USERS`
(
  `username`   varchar(10) NOT NULL,
  `password`   VARCHAR(32) NOT NULL,
  `email`      VARCHAR(50) NOT NULL,
  PRIMARY KEY (`username`)
);

INSERT INTO `USERS` VALUES ('kalim', md5('password'), 'kalim@slac.stanford.edu');
INSERT INTO `USERS` VALUES ('cottrell', md5('password'), 'cottrell@slac.stanford.edu');
INSERT INTO `USERS` VALUES ('fahad', md5('password'), 'fahad@slac.stanford.edu');

DROP TABLE IF EXISTS `COUNTRY`;
CREATE TABLE `COUNTRY`
(
  `COUNTRY_ID`  SMALLINT NOT NULL,
  `COUNTRY`     VARCHAR(80) NOT NULL,
  `CONTINENT`   VARCHAR(20) NOT NULL,
  `TLD`         VARCHAR(10) NOT NULL,
  `REMARK`      VARCHAR(256),
  UNIQUE (`COUNTRY`),
  PRIMARY KEY (`COUNTRY_ID`)
);

#DROP INDEX `IDX_COUNTRY` on `COUNTRY`;
CREATE INDEX `IDX_COUNTRY` on `COUNTRY` (`COUNTRY`);

DROP TABLE IF EXISTS `NODEDETAILS`;
CREATE TABLE `NODEDETAILS`
(
  `NODENAME`     VARCHAR(100) NOT NULL,
  `IPADDRESS`    VARCHAR(15) NOT NULL,
  `SITENAME`     VARCHAR(100) NOT NULL,
  `NICKNAME`     VARCHAR(35) NOT NULL,
  `FULLNAME`     VARCHAR(100) NOT NULL,
  `LOCATION`     VARCHAR(100) NOT NULL,
  `COUNTRY_ID`   SMALLINT NOT NULL,
  `LAT`          FLOAT NOT NULL,
  `LONG`         FLOAT NOT NULL,
  `PROJECTTYPE`  VARCHAR(10) NOT NULL,
  `PINGSERVER`   VARCHAR(100) NOT NULL,
  `TRACESERVER`  VARCHAR(100) NOT NULL,
  `DATASERVER`   VARCHAR(100) NOT NULL,
  `URL`          VARCHAR(100),
  `GMT`          VARCHAR(10),
  `COMMENTS`     VARCHAR(4000),
  `APP_USER`     VARCHAR(20),
  `CONTACTS`     VARCHAR(150),
  `PING_SIZE`    SMALLINT,
#   PRIMARY KEY (`NODENAME`),
   FOREIGN KEY (`COUNTRY_ID`) REFERENCES COUNTRY(COUNTRY_ID)
);

#DROP INDEX `IDX_NODEDETAILS` on `NODEDETAILS`;
CREATE INDEX `IDX_NODEDETAILS` on `NODEDETAILS` (`NODENAME`);

DROP TABLE IF EXISTS `GROUPS`;
CREATE TABLE `GROUPS`
(
  `nodenm`     VARCHAR(100) NOT NULL,
  `groupname`  VARCHAR(100) NOT NULL
);

DROP TABLE IF EXISTS `PINGEDFROM`;
CREATE TABLE `PINGEDFROM`
(
  `nodenm`     VARCHAR(100) NOT NULL,
  `sitename`   VARCHAR(100) NOT NULL
);

Scripts - Transforming raw data to SQL commands

The data loaded into the database was obtained from the following files:

  • Country List [csv] (July 2009)
  • PingER NODEDETAILS [csv]  (March 2009)

I wrote two scripts (listed below) to generate SQL commands from this raw data. The resulting SQL commands were then coalesced into a single sql file [nodedetails-schema.sql] which was used to setup the server at NUST. The GROUPS and PINGEDFROM tables are populated separately [groups.sql].

  • generate-sql-from-country-data [.pl]
  • generate-sql-from-pinger-nodedetails-data [.pl]

A copy of all the code is available at:

/afs/slac/package/pinger/pinger-archive/src/schema-meta-database/schema.tar.gz

Downsites

The schema of the database is as follows:

#DROP DATABASE IF EXISTS `downsites`;
#CREATE DATABASE `downsites`;

DROP TABLE IF EXISTS `downsites`;
CREATE TABLE `downsites`
(
  `down_timestamp`             INT(10) UNSIGNED DEFAULT 0,
  `up_timestamp`               INT(10) UNSIGNED DEFAULT 0,
  `monitoring_site`            VARCHAR(50),
  `remote_site`                VARCHAR(50),
  `site_status`                VARCHAR(50),
  `downdays`                   INT(10) UNSIGNED DEFAULT 0,
  `monitoring_country`         VARCHAR(50),
  `remote_country`             VARCHAR(50),
  `monitoring_region`          VARCHAR(50),
  `remote_region`              VARCHAR(50),
  `remote_node`                VARCHAR(50),
  `beacon_status`              VARCHAR(50),
  PRIMARY KEY (`down_timestamp`, `monitoring_site`, `remote_site`, `downdays`)
);

DROP TABLE IF EXISTS `beacons`;
CREATE TABLE `beacons`
(
  `site`                       VARCHAR(50),
  `beacon_status`              VARCHAR(50),
  PRIMARY KEY (`site`, `beacon_status`)
);

mysql> desc dbminrtt;
+-------------------+----------------+------+-----+---------+-------+
| Field             | Type           | Null | Key | Default | Extra |
+-------------------+----------------+------+-----+---------+-------+
| MonitoringSite    | varchar(50)    |      | PRI |         |       |
| RemoteSite        | varchar(50)    |      | PRI |         |       |
| minrtt            | float unsigned |      |     | 0       |       |
| regminrtt         | float unsigned |      |     | 0       |       |
| sdrtt             | float unsigned |      |     | 0       |       |
| anomalydate       | varchar(50)    |      | PRI |         |       |
| MonitoringCountry | varchar(50)    |      |     |         |       |
| RemoteCountry     | varchar(50)    |      |     |         |       |
| MonitoringRegion  | varchar(50)    |      |     |         |       |
| RemoteRegion      | varchar(50)    |      |     |         |       |
+-------------------+----------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

mysql> desc downsites_new;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| down_timestamp     | int(10)     |      | PRI | 0       |       |
| up_timestamp       | int(10)     |      |     | 0       |       |
| monitoring_site    | varchar(50) |      | PRI |         |       |
| remote_site        | varchar(50) |      | PRI |         |       |
| site_status        | varchar(50) |      |     |         |       |
| downdays           | int(10)     |      | PRI | 0       |       |
| monitoring_country | varchar(50) |      |     |         |       |
| remote_country     | varchar(50) |      |     |         |       |
| monitoring_region  | varchar(50) |      |     |         |       |
| remote_region      | varchar(50) |      |     |         |       |
| remote_node        | varchar(50) |      |     |         |       |
| beacon_status      | varchar(50) |      |     |         |       |
| remote_node_type   | varchar(5)  | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
  • No labels