Author: klichong Date: Mon Nov 16 17:44:52 2009 New Revision: 880870 URL: http://svn.apache.org/viewvc?rev=880870&view=rev Log: updating schema.sql to match the schema file in the $WORKLOAD/bin directory
Modified: incubator/olio/webapp/java/trunk/etc/schema.sql Modified: incubator/olio/webapp/java/trunk/etc/schema.sql URL: http://svn.apache.org/viewvc/incubator/olio/webapp/java/trunk/etc/schema.sql?rev=880870&r1=880869&r2=880870&view=diff ============================================================================== --- incubator/olio/webapp/java/trunk/etc/schema.sql (original) +++ incubator/olio/webapp/java/trunk/etc/schema.sql Mon Nov 16 17:44:52 2009 @@ -16,36 +16,121 @@ * limitations under the License. * */ -SET FOREIGN_KEY_CHECKS = 0; +-- MySQL dump 10.13 Distrib 5.1.25-rc, for sun-solaris2.10 (sparc) +-- +-- Host: localhost Database: bpwebapp +-- ------------------------------------------------------ +-- Server version 5.1.25-rc-standard-log + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `ADDRESS` +-- DROP TABLE IF EXISTS `ADDRESS`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `ADDRESS` ( - `ADDRESSID` int(11) NOT NULL, - `STATE` varchar(255) DEFAULT NULL, - `COUNTRY` varchar(255) DEFAULT NULL, + `ADDRESSID` INTEGER NOT NULL AUTO_INCREMENT, + `STATE` varchar(25) DEFAULT NULL, + `COUNTRY` varchar(55) DEFAULT NULL, `LATITUDE` double DEFAULT NULL, `LONGITUDE` double DEFAULT NULL, - `CITY` varchar(255) DEFAULT NULL, - `ZIP` varchar(255) DEFAULT NULL, - `STREET1` varchar(255) DEFAULT NULL, - `STREET2` varchar(255) DEFAULT NULL, + `CITY` varchar(55) DEFAULT NULL, + `ZIP` varchar(12) DEFAULT NULL, + `STREET1` varchar(55) DEFAULT NULL, + `STREET2` varchar(55) DEFAULT NULL, PRIMARY KEY (`ADDRESSID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; +-- +-- Table structure for table `PERSON` +-- + +DROP TABLE IF EXISTS `PERSON`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `PERSON` ( + `USERID` INTEGER NOT NULL AUTO_INCREMENT, + `USERNAME` varchar(25) NOT NULL, + `PASSWORD` varchar(25) DEFAULT NULL, + `SUMMARY` text, + `TELEPHONE` varchar(25) DEFAULT NULL, + `IMAGETHUMBURL` varchar(100) DEFAULT NULL, + `IMAGEURL` varchar(100) DEFAULT NULL, + `FIRSTNAME` varchar(25) DEFAULT NULL, + `LASTNAME` varchar(25) DEFAULT NULL, + `EMAIL` varchar(90) DEFAULT NULL, + `TIMEZONE` varchar(100) DEFAULT NULL, + `ADDRESS_ADDRESSID` INTEGER DEFAULT NULL, + PRIMARY KEY (`USERID`), + KEY `FK_PERSON_ADDRESS_ADDRESSID` (`ADDRESS_ADDRESSID`), + CONSTRAINT `FK_PERSON_ADDRESS_ADDRESSID` FOREIGN KEY (`ADDRESS_ADDRESSID`) REFERENCES `ADDRESS` (`ADDRESSID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +SET character_set_client = @saved_cs_client; + +CREATE UNIQUE INDEX PERSON_USER_IDX on PERSON (username); + +-- +-- Table structure for table `SOCIALEVENT` +-- + +DROP TABLE IF EXISTS `SOCIALEVENT`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `SOCIALEVENT` ( + `SOCIALEVENTID` INTEGER NOT NULL AUTO_INCREMENT, + `DESCRIPTION` VARCHAR(500) NOT NULL, + `TITLE` varchar(100) DEFAULT NULL, + `SUBMITTERUSERNAME` varchar(25) DEFAULT NULL, + `SUMMARY` varchar(100) DEFAULT NULL, + `TELEPHONE` varchar(20) DEFAULT NULL, + `IMAGETHUMBURL` varchar(100) DEFAULT NULL, + `IMAGEURL` varchar(100) DEFAULT NULL, + `LITERATUREURL` varchar(100) DEFAULT NULL, + `EVENTTIMESTAMP` datetime DEFAULT NULL, + `TOTALSCORE` INTEGER DEFAULT NULL, + `NUMBEROFVOTES` INTEGER DEFAULT NULL, + `DISABLED` INTEGER DEFAULT NULL, + `CREATEDTIMESTAMP` datetime DEFAULT NULL, + `ADDRESS_ADDRESSID` INTEGER DEFAULT NULL, + PRIMARY KEY (`SOCIALEVENTID`), + KEY `FK_SOCIALEVENT_ADDRESS_ADDRESSID` (`ADDRESS_ADDRESSID`), + CONSTRAINT `FK_SOCIALEVENT_ADDRESS_ADDRESSID` FOREIGN KEY (`ADDRESS_ADDRESSID`) REFERENCES `ADDRESS` (`ADDRESSID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +SET character_set_client = @saved_cs_client; + +-- CREATE INDEX SOCIALEVENT_DATE_IDX on SOCIALEVENT (EVENTTIMESTAMP); +CREATE INDEX SOCIALEVENT_EVENTTIMESTAMP_IDX on SOCIALEVENT (eventtimestamp); +CREATE INDEX SOCIALEVENT_CREATEDTIMESTAMP_IDX on SOCIALEVENT (createdtimestamp); +CREATE INDEX SOCIALEVENT_SUBMITTERUSERNAME_IDX on SOCIALEVENT (submitterUserName); + +-- +-- Table structure for table `COMMENTS_RATING` +-- + DROP TABLE IF EXISTS `COMMENTS_RATING`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `COMMENTS_RATING` ( - `commentsid` int(11) NOT NULL, + `commentsid` INTEGER NOT NULL AUTO_INCREMENT, `CREATIONTIME` datetime DEFAULT NULL, `COMMENTS` text, - `RATING` int(11) DEFAULT NULL, - `USERNAME_USERNAME` varchar(255) DEFAULT NULL, - `SOCIALEVENT_SOCIALEVENTID` int(11) DEFAULT NULL, + `RATING` INTEGER DEFAULT NULL, + `USERNAME_USERNAME` varchar(25) DEFAULT NULL, + `SOCIALEVENT_SOCIALEVENTID` INTEGER DEFAULT NULL, PRIMARY KEY (`commentsid`), KEY `FK_COMMENTS_RATING_SOCIALEVENT_SOCIALEVENTID` (`SOCIALEVENT_SOCIALEVENTID`), KEY `FK_COMMENTS_RATING_USERNAME_USERNAME` (`USERNAME_USERNAME`), @@ -55,6 +140,13 @@ SET character_set_client = @saved_cs_client; +/*!40000 ALTER TABLE `COMMENTS_RATING` DISABLE KEYS */; +/*!40000 ALTER TABLE `COMMENTS_RATING` ENABLE KEYS */; + +-- +-- Table structure for table `ID_GEN` +-- + DROP TABLE IF EXISTS `ID_GEN`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; @@ -66,14 +158,18 @@ SET character_set_client = @saved_cs_client; +-- +-- Table structure for table `INVITATION` +-- + DROP TABLE IF EXISTS `INVITATION`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `INVITATION` ( - `INVITATIONID` int(11) NOT NULL, + `INVITATIONID` INTEGER NOT NULL AUTO_INCREMENT, `ISACCEPTED` tinyint(1) DEFAULT '0', - `REQUESTOR_USERNAME` varchar(255) DEFAULT NULL, - `CANDIDATE_USERNAME` varchar(255) DEFAULT NULL, + `REQUESTOR_USERNAME` varchar(25) DEFAULT NULL, + `CANDIDATE_USERNAME` varchar(25) DEFAULT NULL, PRIMARY KEY (`INVITATIONID`), KEY `FK_INVITATION_CANDIDATE_USERNAME` (`CANDIDATE_USERNAME`), KEY `FK_INVITATION_REQUESTOR_USERNAME` (`REQUESTOR_USERNAME`), @@ -82,46 +178,36 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; -DROP TABLE IF EXISTS `PERSON`; -SET @saved_cs_client = @@character_set_client; -SET character_set_client = utf8; -CREATE TABLE `PERSON` ( - `USERNAME` varchar(255) NOT NULL, - `PASSWORD` varchar(255) DEFAULT NULL, - `SUMMARY` text, - `TELEPHONE` varchar(255) DEFAULT NULL, - `IMAGETHUMBURL` varchar(255) DEFAULT NULL, - `IMAGEURL` varchar(255) DEFAULT NULL, - `FIRSTNAME` varchar(255) DEFAULT NULL, - `LASTNAME` varchar(255) DEFAULT NULL, - `EMAIL` varchar(255) DEFAULT NULL, - `TIMEZONE` varchar(255) DEFAULT NULL, - `ADDRESS_ADDRESSID` int(11) DEFAULT NULL, - PRIMARY KEY (`USERNAME`), - KEY `FK_PERSON_ADDRESS_ADDRESSID` (`ADDRESS_ADDRESSID`), - CONSTRAINT `FK_PERSON_ADDRESS_ADDRESSID` FOREIGN KEY (`ADDRESS_ADDRESSID`) REFERENCES `ADDRESS` (`ADDRESSID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -SET character_set_client = @saved_cs_client; + + +-- +-- Table structure for table `PERSON_PERSON` +-- DROP TABLE IF EXISTS `PERSON_PERSON`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `PERSON_PERSON` ( - `Person_USERNAME` varchar(255) NOT NULL, - `friends_USERNAME` varchar(255) NOT NULL, - PRIMARY KEY (`Person_USERNAME`,`friends_USERNAME`), - KEY `FK_PERSON_PERSON_friends_USERNAME` (`friends_USERNAME`), - CONSTRAINT `FK_PERSON_PERSON_Person_USERNAME` FOREIGN KEY (`Person_USERNAME`) REFERENCES `PERSON` (`USERNAME`), - CONSTRAINT `FK_PERSON_PERSON_friends_USERNAME` FOREIGN KEY (`friends_USERNAME`) REFERENCES `PERSON` (`USERNAME`) + `Person_USERID` INTEGER NOT NULL, + `friends_USERID` INTEGER NOT NULL, + PRIMARY KEY (`Person_USERID`,`friends_USERID`), + KEY `FK_PERSON_PERSON_friends_USERID` (`friends_USERID`), + CONSTRAINT `FK_PERSON_PERSON_Person_USERID` FOREIGN KEY (`Person_USERID`) REFERENCES `PERSON` (`USERID`), + CONSTRAINT `FK_PERSON_PERSON_friends_USERID` FOREIGN KEY (`friends_USERID`) REFERENCES `PERSON` (`USERID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; + +-- +-- Table structure for table `PERSON_SOCIALEVENT` +-- + DROP TABLE IF EXISTS `PERSON_SOCIALEVENT`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `PERSON_SOCIALEVENT` ( - `SOCIALEVENTID` int(11) NOT NULL, - `USERNAME` varchar(255) NOT NULL, + `SOCIALEVENTID` INTEGER NOT NULL, + `USERNAME` varchar(25) NOT NULL, PRIMARY KEY (`SOCIALEVENTID`,`USERNAME`), KEY `FK_PERSON_SOCIALEVENT_USERNAME` (`USERNAME`), CONSTRAINT `FK_PERSON_SOCIALEVENT_USERNAME` FOREIGN KEY (`USERNAME`) REFERENCES `PERSON` (`USERNAME`), @@ -129,53 +215,32 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; -DROP TABLE IF EXISTS `SOCIALEVENT`; -SET @saved_cs_client = @@character_set_client; -SET character_set_client = utf8; -CREATE TABLE `SOCIALEVENT` ( - `SOCIALEVENTID` int(11) NOT NULL, - `DESCRIPTION` text, - `TITLE` varchar(255) DEFAULT NULL, - `SUBMITTERUSERNAME` varchar(255) DEFAULT NULL, - `SUMMARY` varchar(255) DEFAULT NULL, - `TELEPHONE` varchar(255) DEFAULT NULL, - `IMAGETHUMBURL` varchar(255) DEFAULT NULL, - `IMAGEURL` varchar(255) DEFAULT NULL, - `LITERATUREURL` varchar(255) DEFAULT NULL, - `EVENTTIMESTAMP` datetime DEFAULT NULL, - `TOTALSCORE` int(11) DEFAULT NULL, - `NUMBEROFVOTES` int(11) DEFAULT NULL, - `DISABLED` int(11) DEFAULT NULL, - `CREATEDTIMESTAMP` datetime DEFAULT NULL, - `ADDRESS_ADDRESSID` int(11) DEFAULT NULL, - PRIMARY KEY (`SOCIALEVENTID`), - KEY `FK_SOCIALEVENT_ADDRESS_ADDRESSID` (`ADDRESS_ADDRESSID`), - CONSTRAINT `FK_SOCIALEVENT_ADDRESS_ADDRESSID` FOREIGN KEY (`ADDRESS_ADDRESSID`) REFERENCES `ADDRESS` (`ADDRESSID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -SET character_set_client = @saved_cs_client; - --- CREATE INDEX SOCIALEVENT_DATE_IDX on SOCIALEVENT (EVENTTIMESTAMP); -CREATE INDEX SOCIALEVENT_EVENTTIMESTAMP_IDX on SOCIALEVENT (eventtimestamp); -CREATE INDEX SOCIALEVENT_CREATEDTIMESTAMP_IDX on SOCIALEVENT (createdtimestamp); -CREATE INDEX SOCIALEVENT_SUBMITTERUSERNAME_IDX on SOCIALEVENT (submitterUserName); + +-- +-- Table structure for table `SOCIALEVENTTAG` +-- DROP TABLE IF EXISTS `SOCIALEVENTTAG`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `SOCIALEVENTTAG` ( - `SOCIALEVENTTAGID` int(11) NOT NULL, - `TAG` varchar(255) DEFAULT NULL, - `REFCOUNT` int(11) DEFAULT NULL, + `SOCIALEVENTTAGID` INTEGER NOT NULL AUTO_INCREMENT, + `TAG` varchar(30) DEFAULT NULL, + `REFCOUNT` INTEGER DEFAULT NULL, PRIMARY KEY (`SOCIALEVENTTAGID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; +-- +-- Table structure for table `SOCIALEVENTTAG_SOCIALEVENT` +-- + DROP TABLE IF EXISTS `SOCIALEVENTTAG_SOCIALEVENT`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `SOCIALEVENTTAG_SOCIALEVENT` ( - `SOCIALEVENTTAGID` int(11) NOT NULL, - `SOCIALEVENTID` int(11) NOT NULL, + `SOCIALEVENTTAGID` INTEGER NOT NULL, + `SOCIALEVENTID` INTEGER NOT NULL, PRIMARY KEY (`SOCIALEVENTTAGID`,`SOCIALEVENTID`), KEY `FK_SOCIALEVENTTAG_SOCIALEVENT_SOCIALEVENTID` (`SOCIALEVENTID`), CONSTRAINT `FK_SOCIALEVENTTAG_SOCIALEVENT_SOCIALEVENTID` FOREIGN KEY (`SOCIALEVENTID`) REFERENCES `SOCIALEVENT` (`SOCIALEVENTID`), @@ -185,5 +250,8 @@ CREATE INDEX SOCIALEVENT_TAG on SOCIALEVENTTAG_SOCIALEVENT (socialeventid); +/*!40103 SET time_zo...@old_time_zone */; + CREATE INDEX SOCIALEVENT_COMMENTS on COMMENTS_RATING (socialevent_socialeventid); -SET FOREIGN_KEY_CHECKS = 1; + +-- Dump completed on 2008-12-04 20:45:41