Database Schema
The revised schema of the meta database \ [[nodedetails-schema.sql|^nodedetails-schema.sql] \] is as follows: Wiki Markup
Code Block |
---|
#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
);
|
...
The data loaded into the database was obtained from the following files:
Country List \[ [csv|^country_list.csv]\] (July 2009)Wiki Markup Wiki Markup - PingER NODEDETAILS \ [[csv|^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|^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-country-data-sql.pl]\]unmigrated-wiki-markupWiki Markup - generate-sql-from-pinger-nodedetails-data \ [[.pl|^generate-nodedetails-sql.pl]\]
A copy of all the code is available at:
No Format |
---|
/afs/slac/package/pinger/pinger-archive/src/schema-meta-database/schema.tar.gz |
Downsites
The schema of the database is as follows:
No Format |
---|
#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)
|