examples/VlibBeans/createDb.sql

CREATE TABLE PROP
(
  NAME VARCHAR(32) NOT NULL,
  PROP_VALUE INTEGER NOT NULL,

  CONSTRAINT PROP_PK PRIMARY KEY(NAME)
)

CREATE TABLE PERSON
(
  PERSON_ID INTEGER NOT NULL,
  LAST_NAME VARCHAR(30) NOT NULL,
  FIRST_NAME VARCHAR(30),
  EMAIL VARCHAR(60) NOT NULL,
  PASSWORD VARCHAR(20) NOT NULL,
  VERIFIED BIT NOT NULL,
  ADMIN BIT NOT NULL,
  LOCKED_OUT BIT NOT NULL,
  AUTH_CODE VARCHAR(30),
  LAST_ACCESS TIMESTAMP,

  CONSTRAINT PERSON_PK PRIMARY KEY (PERSON_ID)
)

CREATE TABLE PUBLISHER
(
  PUBLISHER_ID INTEGER NOT NULL,
  NAME VARCHAR(40) NOT NULL,

  CONSTRAINT PUBLISHER_PK PRIMARY KEY (PUBLISHER_ID),
  UNIQUE (NAME)
)

ALTER TABLE PERSON ADD CONSTRAINT UNIQUE_EMAIL UNIQUE(EMAIL)

CREATE TABLE BOOK
(
  BOOK_ID INTEGER NOT NULL,
  AUTHOR VARCHAR(60) NOT NULL,
  HOLDER_ID INTEGER NOT NULL INDEX_BLIST,
  OWNER_ID INTEGER NOT NULL INDEX_BLIST,
  TITLE VARCHAR(100) NOT NULL,
  DESCRIPTION VARCHAR(2000),
  ISBN VARCHAR(20),
  PUBLISHER_ID INTEGER NOT NULL INDEX_BLIST,
  LENDABLE BIT NOT NULL,
  HIDDEN BIT NOT NULL,
  DATE_ADDED TIMESTAMP NOT NULL,

  CONSTRAINT BOOK_PK PRIMARY KEY (BOOK_ID),
  CONSTRAINT HOLDER_FK FOREIGN KEY (HOLDER_ID) REFERENCES PERSON (PERSON_ID),
  CONSTRAINT OWNER_FK FOREIGN KEY (OWNER_ID) REFERENCES PERSON (PERSON_ID),
  CONSTRAINT PUBLISHER_FK FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID)
)

INSERT INTO PROP (NAME, PROP_VALUE) VALUES ("next-key", 5000)

INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWORD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS)
VALUES (1000, 'Lewis Ship', 'Howard', 'hship@primix.com', 'secret', 1, 1, 0, NULL, DATEOB())

INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWORD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS)
VALUES (1001, 'Queue', 'Suzy', 'squeue@bug.org', 'secret', 1, 0, 0, NULL, DATEOB())

INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWORD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS)
VALUES (1002, 'CubeDweller', 'Dilbert', 'dilbert@bigco.com', 'secret', 1, 0, 0, NULL, DATEOB())