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|^groups.sql]\]. Wiki Markup
...
- generate-sql-from-country-data \ [[.pl|^generate-country-data-sql.pl]\]
Wiki 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) | | | | | and /afs/slac/package/pinger/pinger-archive/src/schema-meta-database/groups.sql+-------------------+----------------+------+-----+---------+-------+ 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) |