-- This is a schema for Hypersonic SQL for the concept of Users/Roles/Permissions
-- (Note: These instructions are written to conform with v. 1.42.)
--
-- Note: The process for creating a database in Hypersonic SQL is a tad
-- convoluted. These instructions are written to match. ;)
--
-- The following steps use the DatabaseManger program harness contained in 
-- the HypersonicSQL distribution. 
--
-- 1. Create a directory to store the database files (e.g., C:\TurbineDB).
-- 2. Launch the Hypersonic Database Manager. See the Hypersonic SQL 
--    documentation for details.
-- 3. In the Connect dialog box, choose 'HypersonicSQL Standalone' for the 
--    Type and set the URL to 'jdbc:HypersonicSQL:C:\TurbineDB\turbine'.
-- 4. Paste the contents of this file (after these comments) into the Database
--    Manager Command Area (the upper right part of the main window) and click
--    the Execute button.
-- 5. Set the following in TurbineResources.properties:
--      database.default.driver=org.hsql.jdbcDriver
--      database.default.url=jdbc:HypersonicSQL:C:\TurbineDB\turbine
--      database.default.username=sa
--      database.default.password=
-- 6. Make sure the Hypersonic SQL jar is in the CLASSPATH of your servlet 
--    engine.

CREATE TABLE Permission (
    PERMISSIONID        INTEGER NOT NULL IDENTITY PRIMARY KEY,
    PERMISSION          VARCHAR(99) NOT NULL
);

CREATE TABLE UserRole (
    ROLEID      INTEGER NOT NULL IDENTITY PRIMARY KEY,
    ROLENAME    VARCHAR(99) NOT NULL
);

CREATE TABLE RolePermission (
    ROLEID              INTEGER NOT NULL,
    PERMISSIONID        INTEGER NOT NULL,
    PRIMARY KEY (ROLEID, PERMISSIONID)
);

CREATE UNIQUE INDEX roleID_permissionID_index ON RolePermission ( ROLEID, PERMISSIONID );

CREATE TABLE Visitor (
    VISITORID   INTEGER NOT NULL IDENTITY PRIMARY KEY,
    LOGINID     VARCHAR(32) NOT NULL,
    PASSWORD_VALUE    VARCHAR(32) NOT NULL,
    FIRST_NAME  VARCHAR(99) NOT NULL,
    LAST_NAME   VARCHAR(99) NOT NULL,
    ADDRESS1    VARCHAR(255),
    ADDRESS2    VARCHAR(255),
    CITY        VARCHAR(255),
    STATE       VARCHAR(32),
    POSTALCODE  VARCHAR(32),
    COUNTRY     VARCHAR(99),
    CITIZENSHIP VARCHAR(32),
    PHONE       VARCHAR(32),
    ALTPHONE    VARCHAR(32),
    FAX         VARCHAR(32),
    CELL        VARCHAR(32),
    PAGER       VARCHAR(32),
    EMAIL       VARCHAR(99),
    MODIFIED    TIMESTAMP,
    CREATED     DATE,
    LASTLOGIN   TIMESTAMP,
    OBJECTDATA  BINARY,
    PREFIX_NAME VARCHAR(16),
    MIDDLE_NAME VARCHAR(99),
    SUFFIX_NAME VARCHAR(16),
    COMPANY     VARCHAR(255),
    CONFIRM_VALUE VARCHAR(99)
);

CREATE TABLE VisitorRole (
    VISITORID   INTEGER NOT NULL,
    ROLEID      INTEGER NOT NULL,
    PRIMARY KEY (VISITORID, ROLEID)
);

CREATE UNIQUE INDEX visitorID_roleID_index ON VisitorRole ( VISITORID, ROLEID );

-- FIXME: IDB doesn't like -1 as a default value
-- CREATE TABLE Jobentry (
--    JOB_ID	          BIGINT NOT NULL PRIMARY KEY,
--    MINUTE        INTEGER default -1 NOT NULL,
--    HOUR          INTEGER default -1 NOT NULL,
--    WEEKDAY       INTEGER default -1 NOT NULL,
--    DAY_OF_MONTH  INTEGER default -1 NOT NULL,
--    TASK          VARCHAR(99) NOT NULL,
--    EMAIL         VARCHAR(99)
-- );

-- 
-- This table provides the primary keys for all
-- other tables in the system.  Should be used
-- with util.db.IDBroker.
--
CREATE TABLE ID_TABLE 
(
  ID_TABLE_ID INTEGER NOT NULL PRIMARY KEY,
  TABLE_NAME VARCHAR(255) NOT NULL,
  NEXT_ID INTEGER,
  QUANTITY INTEGER
);

INSERT INTO ID_TABLE (id_table_id, table_name, next_id, quantity)
 VALUES (1, 'Permission', 20, 10);
INSERT INTO ID_TABLE (id_table_id, table_name, next_id, quantity)
 VALUES (2, 'UserRole', 20, 10);
INSERT INTO ID_TABLE (id_table_id, table_name, next_id, quantity)
 VALUES (3, 'Visitor', 20, 10);
INSERT INTO ID_TABLE (id_table_id, table_name, next_id, quantity)
 VALUES (4, 'Jobentry', 20, 10);
