You are viewing a plain text version of this content. The canonical link for it is here.
Posted to olio-commits@incubator.apache.org by kl...@apache.org on 2009/11/16 17:44:52 UTC
svn commit: r880870 - /incubator/olio/webapp/java/trunk/etc/schema.sql
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_ZONE=@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