...
Database Schema
The revised schema of the meta database \[ [nodedetails-schema.sql|^nodedetails-schema.sql] \] is as follows:
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 ); |
Scripts - Transforming raw data to SQL commands
The data loaded into the database was obtained from the following files:
...
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].
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)
|