pixhsql.sql

来自「Beginning Spring 2 源代码」· SQL 代码 · 共 107 行

create table ALBUM_SEQUENCE (SEQ_ID identity);
create table PICTURE_SEQUENCE (SEQ_ID identity);
create table COMMENT_SEQUENCE (SEQ_ID identity);

INSERT INTO ALBUM_SEQUENCE VALUES(0);
INSERT INTO PICTURE_SEQUENCE VALUES(0);

CREATE TABLE "AFFILIATE"
(
   USERNAME VARCHAR(255) PRIMARY KEY,
   COMPANYNAME VARCHAR(255),
   FAXNUMBER VARCHAR(255),
   WEBSITEURL VARCHAR(255)
);
CREATE TABLE "ALBUM"
(
   DTYPE VARCHAR(31) not null,
   ID INTEGER PRIMARY KEY,
   NAME VARCHAR(255),
   DESCRIPTION VARCHAR(255),
   CREATIONDATE TIMESTAMP,
   LABELS VARBINARY,
   USER_USERNAME VARCHAR(255)
);
CREATE TABLE "ALBUM_PICTURE"
(
   ALBUM_ID INTEGER not null,
   PICTURES_ID INTEGER not null
);

CREATE TABLE "COMMENT"
(
   ID INTEGER PRIMARY KEY,
   COMMENT VARCHAR(255)
);

CREATE TABLE "PICTURE"
(
   ID INTEGER PRIMARY KEY,
   NAME VARCHAR(255),
   DESCRIPTION VARCHAR(255),
   SIZE BIGINT not null
);
CREATE TABLE "PICTURE_COMMENT"
(
   PICTURE_ID INTEGER not null,
   COMMENTS_ID INTEGER not null,
   CONSTRAINT "PICTURE_COMMENT" PRIMARY KEY (PICTURE_ID,COMMENTS_ID)
);

CREATE TABLE "PIXUSER"
(
   USERNAME VARCHAR(255) PRIMARY KEY,
   FIRSTNAME VARCHAR(255),
   LASTNAME VARCHAR(255),
   EMAIL VARCHAR(255),
   PASSWORD VARCHAR(255)
);
CREATE TABLE "PIXUSER_COMMENT"
(
   PIXUSER_USERNAME VARCHAR(255) not null,
   COMMENTS_ID INTEGER not null
);

ALTER TABLE AFFILIATE
ADD CONSTRAINT FKD623A52DB262B417
FOREIGN KEY (USERNAME)
REFERENCES PIXUSER(USERNAME) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ALBUM
ADD CONSTRAINT FK3C68E4FB5660E0B
FOREIGN KEY (USER_USERNAME)
REFERENCES PIXUSER(USERNAME) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX SYS_IDX_1216 ON ALBUM(USER_USERNAME);
ALTER TABLE ALBUM_PICTURE
ADD CONSTRAINT FK61CF6AEE13D70A51
FOREIGN KEY (ALBUM_ID)
REFERENCES ALBUM(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ALBUM_PICTURE
ADD CONSTRAINT FK61CF6AEE7552C49A
FOREIGN KEY (PICTURES_ID)
REFERENCES PICTURE(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX SYS_IDX_1220 ON ALBUM_PICTURE(ALBUM_ID);
CREATE INDEX SYS_IDX_SYS_CT_1198_1200 ON ALBUM_PICTURE(PICTURES_ID);
CREATE INDEX SYS_IDX_1218 ON ALBUM_PICTURE(PICTURES_ID);
ALTER TABLE PICTURE_COMMENT
ADD CONSTRAINT FKEE5B63DE63D66A31
FOREIGN KEY (PICTURE_ID)
REFERENCES PICTURE(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE PICTURE_COMMENT
ADD CONSTRAINT FKEE5B63DE99A44EDC
FOREIGN KEY (COMMENTS_ID)
REFERENCES COMMENT(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX SYS_IDX_1222 ON PICTURE_COMMENT(PICTURE_ID);
CREATE INDEX SYS_IDX_SYS_CT_1205_1208 ON PICTURE_COMMENT(COMMENTS_ID);
CREATE INDEX SYS_IDX_1224 ON PICTURE_COMMENT(COMMENTS_ID);
ALTER TABLE PIXUSER_COMMENT
ADD CONSTRAINT FK868D64EA202C36CC
FOREIGN KEY (PIXUSER_USERNAME)
REFERENCES PIXUSER(USERNAME) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE PIXUSER_COMMENT
ADD CONSTRAINT FK868D64EA99A44EDC
FOREIGN KEY (COMMENTS_ID)
REFERENCES COMMENT(ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX SYS_IDX_1228 ON PIXUSER_COMMENT(COMMENTS_ID);
CREATE INDEX SYS_IDX_SYS_CT_1211_1213 ON PIXUSER_COMMENT(COMMENTS_ID);
CREATE INDEX SYS_IDX_1226 ON PIXUSER_COMMENT(PIXUSER_USERNAME);