#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
);
|