sonichsqldatabase.script

基于struts框架的web开发应用实例

CREATE TABLE STOREFRONT_ADDRESS_BOOK(PROFILE_ID NUMERIC(30) NOT NULL PRIMARY KEY,USER_ID NUMERIC(30),PROFILE_PRIMARY CHAR(5))
CREATE INDEX XIF463STOREFRONT_ADDRESS_BOOK ON STOREFRONT_ADDRESS_BOOK(USER_ID)
CREATE TABLE STOREFRONT_ORDER(ORDER_ID NUMERIC(30) NOT NULL PRIMARY KEY,ORIGINATOR_PROFILE_ID NUMERIC(30),BILLING_PROFILE_ID NUMERIC(30),SHIPPING_PROFILE_ID NUMERIC(30),STATUS_ID NUMERIC(30) NOT NULL,PLACEMENT_DATE VARCHAR(50) NOT NULL,TAX_PRICE NUMERIC(12,2) NOT NULL,SHIPPING_PRICE NUMERIC(12,2) NOT NULL,SUBTOTAL NUMERIC(12,2) NOT NULL,TOTAL NUMERIC(12,2) NOT NULL)
CREATE TABLE STOREFRONT_ORDER_ITEM(ORDER_PRODUCT_ID NUMERIC(30) NOT NULL PRIMARY KEY,PRODUCT_ID NUMERIC(30) NOT NULL,ORDER_ID NUMERIC(30) NOT NULL,QUANTITY NUMERIC(30) NOT NULL,PRODUCT_PRICE NUMERIC(12,2) NOT NULL,DESCRIPTION VARCHAR(2000) NOT NULL,LINE_NUMBER NUMERIC)
CREATE INDEX XIF40STOREFRONT_ORDER_ITEM ON STOREFRONT_ORDER_ITEM(ORDER_ID)
CREATE INDEX XIF460STOREFRONT_ORDER_ITEM ON STOREFRONT_ORDER_ITEM(PRODUCT_ID)
CREATE TABLE STOREFRONT_PROFILE(PROFILE_ID NUMERIC(30) NOT NULL PRIMARY KEY,TITLE VARCHAR(30),L_NAME VARCHAR(30),F_NAME VARCHAR(30),MIDDLE_NAME VARCHAR(30),COMPANY VARCHAR(80),PHONE_1 VARCHAR(30),ADDRESS_1 VARCHAR(50) NOT NULL,ADDRESS_2 VARCHAR(50),CITY VARCHAR(30) NOT NULL,STATE_CODE VARCHAR(30) NOT NULL,COUNTRY VARCHAR(30) NOT NULL,ZIP VARCHAR(30) NOT NULL,EMAIL_1 VARCHAR(255),LANGUAGE_PREF VARCHAR(10))
CREATE TABLE STOREFRONT_SEQUENCE_GENERATOR(SEQUENCE_NAME VARCHAR(30) NOT NULL PRIMARY KEY,CURRENT_VALUE NUMERIC(30) NOT NULL)
CREATE TABLE STOREFRONT_USER(USER_ID NUMERIC(30) NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(30) NOT NULL,PASSWORD VARCHAR(15) NOT NULL)
CREATE UNIQUE INDEX XAK1STOREFRONT_USER ON STOREFRONT_USER(LOGIN_ID)
CREATE TABLE STOREFRONT_ARTIST(ARTIST_ID NUMERIC(30) NOT NULL PRIMARY KEY,ARTIST_NAME VARCHAR(255) NOT NULL)
CREATE INDEX XIE1STOREFRONT_ARTIST ON STOREFRONT_ARTIST(ARTIST_NAME)
CREATE TABLE STOREFRONT_GENRE(GENRE_ID NUMERIC(30) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL)
CREATE TABLE STOREFRONT_ALBUM(PRODUCT_ID NUMERIC(30) NOT NULL PRIMARY KEY,GENRE_ID NUMERIC(30) NOT NULL,ARTIST_ID NUMERIC(30) NOT NULL,NAME VARCHAR(255) NOT NULL,LARGE_IMAGE_URL VARCHAR(512),SMALL_IMAGE_URL VARCHAR(512),LABEL VARCHAR(255) NOT NULL,CONSTRAINT R_265A FOREIGN KEY(ARTIST_ID) REFERENCES STOREFRONT_ARTIST(ARTIST_ID),CONSTRAINT R_270A FOREIGN KEY(GENRE_ID) REFERENCES STOREFRONT_GENRE(GENRE_ID))
CREATE INDEX XIE1STOREFRONT_ALBUM ON STOREFRONT_ALBUM(NAME,PRODUCT_ID)
CREATE INDEX XIF265STOREFRONT_ALBUM ON STOREFRONT_ALBUM(ARTIST_ID)
CREATE INDEX XIF270STOREFRONT_ALBUM ON STOREFRONT_ALBUM(GENRE_ID)
CREATE TABLE STOREFRONT_PRODUCT(PRODUCT_ID NUMERIC(30) NOT NULL PRIMARY KEY,SKU VARCHAR(30) NOT NULL,DESCRIPTION VARCHAR(2000),MSRP_PRICE NUMERIC(12,2),DERIVED_PRICE NUMERIC(12,2),DERIVED_SALES_PRICE NUMERIC(12,2),NAME VARCHAR(255))
CREATE UNIQUE INDEX XAK1STOREFRONT_PRODUCT ON STOREFRONT_PRODUCT(SKU)
GRANT ALL ON CLASS "org.hsqldb.Library" TO PUBLIC
GRANT ALL ON CLASS "java.lang.Math" TO PUBLIC
CREATE USER SA PASSWORD "" ADMIN
CREATE ALIAS DAYNAME FOR "org.hsqldb.Library.dayname"
CREATE ALIAS SPACE FOR "org.hsqldb.Library.space"
CREATE ALIAS SUBSTRING_ FOR "org.hsqldb.Library.substring"
CREATE ALIAS HEXTORAW FOR "org.hsqldb.Library.hexToRaw"
CREATE ALIAS SQRT FOR "java.lang.Math.sqrt"
CREATE ALIAS ABS FOR "org.hsqldb.Library.abs"
CREATE ALIAS POWER FOR "java.lang.Math.pow"
CREATE ALIAS CHAR FOR "org.hsqldb.Library.character"
CREATE ALIAS CONCAT FOR "org.hsqldb.Library.concat"
CREATE ALIAS PI FOR "org.hsqldb.Library.pi"
CREATE ALIAS RAWTOHEX FOR "org.hsqldb.Library.rawToHex"
CREATE ALIAS SECOND FOR "org.hsqldb.Library.second"
CREATE ALIAS TRUNCATE FOR "org.hsqldb.Library.truncate"
CREATE ALIAS MONTH FOR "org.hsqldb.Library.month"
CREATE ALIAS LOWER FOR "org.hsqldb.Library.lcase"
CREATE ALIAS ATAN2 FOR "java.lang.Math.atan2"
CREATE ALIAS REPEAT FOR "org.hsqldb.Library.repeat"
CREATE ALIAS DAYOFMONTH FOR "org.hsqldb.Library.dayofmonth"
CREATE ALIAS TAN FOR "java.lang.Math.tan"
CREATE ALIAS RADIANS FOR "java.lang.Math.toRadians"
CREATE ALIAS FLOOR FOR "java.lang.Math.floor"
CREATE ALIAS NOW FOR "org.hsqldb.Library.now"
CREATE ALIAS ACOS FOR "java.lang.Math.acos"
CREATE ALIAS DAYOFWEEK FOR "org.hsqldb.Library.dayofweek"
CREATE ALIAS CEILING FOR "java.lang.Math.ceil"
CREATE ALIAS DAYOFYEAR FOR "org.hsqldb.Library.dayofyear"
CREATE ALIAS LCASE FOR "org.hsqldb.Library.lcase"
CREATE ALIAS WEEK FOR "org.hsqldb.Library.week"
CREATE ALIAS SOUNDEX FOR "org.hsqldb.Library.soundex"
CREATE ALIAS ASIN FOR "java.lang.Math.asin"
CREATE ALIAS LOCATE FOR "org.hsqldb.Library.locate"
CREATE ALIAS EXP FOR "java.lang.Math.exp"
CREATE ALIAS MONTHNAME FOR "org.hsqldb.Library.monthname"
CREATE ALIAS YEAR FOR "org.hsqldb.Library.year"
CREATE ALIAS LEFT FOR "org.hsqldb.Library.left"
CREATE ALIAS ROUNDMAGIC FOR "org.hsqldb.Library.roundMagic"
CREATE ALIAS BITOR FOR "org.hsqldb.Library.bitor"
CREATE ALIAS LTRIM FOR "org.hsqldb.Library.ltrim"
CREATE ALIAS COT FOR "org.hsqldb.Library.cot"
CREATE ALIAS COS FOR "java.lang.Math.cos"
CREATE ALIAS MOD FOR "org.hsqldb.Library.mod"
CREATE ALIAS SIGN FOR "org.hsqldb.Library.sign"
CREATE ALIAS DEGREES FOR "java.lang.Math.toDegrees"
CREATE ALIAS LOG FOR "java.lang.Math.log"
CREATE ALIAS SIN FOR "java.lang.Math.sin"
CREATE ALIAS CURTIME FOR "org.hsqldb.Library.curtime"
CREATE ALIAS DIFFERENCE FOR "org.hsqldb.Library.difference"
CREATE ALIAS INSERT FOR "org.hsqldb.Library.insert"
CREATE ALIAS SUBSTR FOR "org.hsqldb.Library.substring"
CREATE ALIAS DATABASE FOR "org.hsqldb.Library.database"
CREATE ALIAS MINUTE FOR "org.hsqldb.Library.minute"
CREATE ALIAS HOUR FOR "org.hsqldb.Library.hour"
CREATE ALIAS IDENTITY FOR "org.hsqldb.Library.identity"
CREATE ALIAS QUARTER FOR "org.hsqldb.Library.quarter"
CREATE ALIAS CURDATE FOR "org.hsqldb.Library.curdate"
CREATE ALIAS BITAND FOR "org.hsqldb.Library.bitand"
CREATE ALIAS USER FOR "org.hsqldb.Library.user"
CREATE ALIAS UCASE FOR "org.hsqldb.Library.ucase"
CREATE ALIAS RTRIM FOR "org.hsqldb.Library.rtrim"
CREATE ALIAS LOG10 FOR "org.hsqldb.Library.log10"
CREATE ALIAS RIGHT FOR "org.hsqldb.Library.right"
CREATE ALIAS ATAN FOR "java.lang.Math.atan"
CREATE ALIAS UPPER FOR "org.hsqldb.Library.ucase"
CREATE ALIAS ASCII FOR "org.hsqldb.Library.ascii"
CREATE ALIAS RAND FOR "java.lang.Math.random"
CREATE ALIAS LENGTH FOR "org.hsqldb.Library.length"
CREATE ALIAS ROUND FOR "org.hsqldb.Library.round"
CREATE ALIAS REPLACE FOR "org.hsqldb.Library.replace"
INSERT INTO STOREFRONT_SEQUENCE_GENERATOR VALUES('ORDER',100)
INSERT INTO STOREFRONT_SEQUENCE_GENERATOR VALUES('ORDER_ITEM',100)
INSERT INTO STOREFRONT_SEQUENCE_GENERATOR VALUES('PRODUCT',100)
INSERT INTO STOREFRONT_SEQUENCE_GENERATOR VALUES('USER',100)
INSERT INTO STOREFRONT_SEQUENCE_GENERATOR VALUES('USER_PROFILE',100)
INSERT INTO STOREFRONT_ARTIST VALUES(1,'The Replacements')
INSERT INTO STOREFRONT_ARTIST VALUES(2,'Frank Zappa')
INSERT INTO STOREFRONT_ARTIST VALUES(3,'The Rolling Stones')
INSERT INTO STOREFRONT_ARTIST VALUES(4,'The Who')
INSERT INTO STOREFRONT_ARTIST VALUES(5,'test')
INSERT INTO STOREFRONT_ARTIST VALUES(6,'Nat King Cole')
INSERT INTO STOREFRONT_ARTIST VALUES(7,'Simpsons')
INSERT INTO STOREFRONT_ARTIST VALUES(8,'Rush')
INSERT INTO STOREFRONT_ARTIST VALUES(9,'Captain Beefheart')
INSERT INTO STOREFRONT_ARTIST VALUES(10,'Orchid Spangiafora')
INSERT INTO STOREFRONT_ARTIST VALUES(11,'Mu')
INSERT INTO STOREFRONT_ARTIST VALUES(12,'David Bowie')
INSERT INTO STOREFRONT_ARTIST VALUES(13,'Cindy Bullens')
INSERT INTO STOREFRONT_ARTIST VALUES(14,'Iron Maiden')
INSERT INTO STOREFRONT_ARTIST VALUES(15,'Meg Henteges')
INSERT INTO STOREFRONT_ARTIST VALUES(16,'Bacon Brothers')
INSERT INTO STOREFRONT_GENRE VALUES(21,'Rock')
INSERT INTO STOREFRONT_GENRE VALUES(22,'Alternative')
INSERT INTO STOREFRONT_GENRE VALUES(23,'Classical')
INSERT INTO STOREFRONT_GENRE VALUES(24,'Jazz')
INSERT INTO STOREFRONT_GENRE VALUES(25,'Blues')
INSERT INTO STOREFRONT_GENRE VALUES(26,'Bluegrass')
INSERT INTO STOREFRONT_ALBUM VALUES(1,22,1,'Sorry Ma, Forgot To Take Out The Trash',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(2,22,2,'Sheik Yerboudi',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(3,22,2,'Thing-Fish',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(4,22,2,'You Are What You Is',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(5,22,2,'Shut Up''n Play Yer Guitar',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(6,22,2,'Joe''s Garage',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(7,22,2,'Over-night Sensation',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(8,22,2,'The Man From Utopia',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(9,22,2,'London Syphony Orchestra Vol. 1 and 2',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(10,22,2,'Totally Commercial',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(11,22,2,'Rare Meat',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(12,22,2,'You Can''t Do That on Stage Anymore Vol. 1',NULL,NULL,'Ryko Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(13,22,2,'You Can''t Do That on Stage Anymore Vol. 2',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(14,22,1,'Pleased To Meet Me',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(15,21,1,'Don''t Tell A Sole',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(16,22,1,'All For Nothing/Nothing For All',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(17,22,1,'All Shook Down',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(18,22,1,'Let It Be',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(19,22,1,'Hootenanny',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(20,22,1,'Stink',NULL,NULL,'Medium Cool Records')
INSERT INTO STOREFRONT_ALBUM VALUES(21,22,1,'When The Shit Hits The Fans',NULL,NULL,'Ripoff Records')
INSERT INTO STOREFRONT_ALBUM VALUES(22,24,6,'Early Recordings',NULL,NULL,'Prime Cuts')
INSERT INTO STOREFRONT_ALBUM VALUES(23,22,2,'You Can''t Do That on Stage Anymore Vol. 3',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(24,22,2,'Hot Rats',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(25,22,2,'Weasels Ripped My Flesh',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(26,22,2,'Civilization Phase III',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(27,22,2,'Does Humor Belong In Music',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(28,22,2,'The Best Band You Never Heard In Your Life',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(29,22,2,'Broadway The Hard Way',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(30,22,2,'200 Motels',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(31,22,2,'Absolutely Free',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(32,22,2,'Freak Out',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(33,22,2,'We are Only In It For The Money',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(34,22,2,'Lumpy Gravy',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(35,22,2,'Crusing With Ruben and The Jets',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(36,22,2,'Uncle Meat',NULL,NULL,'Ryco Disc')
INSERT INTO STOREFRONT_ALBUM VALUES(37,22,2,'Burnt Weeny Sandwich',NULL,NULL,'Ryco Disc')