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:
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:
/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` ( `MonitoringSite` varchar(50) NOT NULL, `RemoteSite` VARCHAR(50) NOT NULL, `minrtt` FLOAT NOT NULL, `regminrtt` FLOAT NOT NULL, `sdrtt` FLOAT NOT NULL, `anomalydate` DATE NOT NULL, `MonitoringCountry` VARCHAR(50) NOT NULL, `RemoteCountry` VARCHAR(50) NOT NULL, `MonitoringRegion` VARCHAR(50) NOT NULL, `RemoteRegion` VARCHAR(50) NOT NULL, PRIMARY KEY (`username`) );