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)unmigrated-wiki-markup
- PingER NODEDETAILS \ [[csv|^nodedetails.csv]\] 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:
...