Wiki Markup |
---|
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('pa55wordpassword'), 'kalim@slac.stanford.edu'); INSERT INTO `USERS` VALUES ('cottrell', md5('pa55wordpassword'), 'cottrell@slac.stanford.edu'); INSERT INTO `USERS` VALUES ('fahad', md5('z3brapassword'), '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`); |
The data loaded into the database was obtained from the following files:
- Country List csv (July 2009)
- PingER NODEDETAILS csv (March 2009)
_END_