-- Turbine, a Servlet Framework for building Dynamic Websites
-- Copyright (C) 1999  Jon S. Stevens Brett McLaughlin
--
-- For licensing and use, please see the LICENSE.txt in this directory.
--
-- This is a schema for Postgres for
-- the concept of Users/Roles/Permissions
--
--
-- Translation rules:
--
-- integer AUTO_INCREMENT:		serial
-- mediumblob:				varchar (255)
--
-- UNIQUE:                              manually created unique index


-- -------------------------------------------------------------------
drop table Permission;
drop sequence permission_permissionid_seq;

create table Permission
(
    PERMISSIONID        serial,
    PERMISSION          varchar (99) NOT NULL,
    PRIMARY KEY (PERMISSIONID)
);

create unique index Permission_permission on Permission (PERMISSION);

-- -------------------------------------------------------------------
drop table UserRole;
drop sequence userrole_roleid_seq;

create table UserRole
(
    ROLEID		serial,
    ROLENAME		varchar (99) NOT NULL,
    PRIMARY KEY (ROLEID)
);

create unique index UserRole_rolename on UserRole (ROLENAME);

-- -------------------------------------------------------------------
drop table RolePermission;

create table RolePermission
(
    ROLEID              integer NOT NULL,
    PERMISSIONID        integer NOT NULL,
    PRIMARY KEY (ROLEID, PERMISSIONID)
);

create index RolePermission_roleid_permissionid_index on RolePermission ( ROLEID, PERMISSIONID );

-- -------------------------------------------------------------------
drop table Visitor;
drop sequence visitor_visitorid_seq;

create table Visitor
(
    VISITORID		serial,
    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		timestamp,
    LASTLOGIN		timestamp,
    OBJECTDATA		varchar (255),				-- oder doch bytea???
    PRIMARY KEY (VISITORID)
);

alter table Visitor add PREFIX_NAME varchar (16);
alter table Visitor add MIDDLE_NAME varchar (99);
alter table Visitor add SUFFIX_NAME varchar (16);
alter table Visitor add COMPANY     varchar (255);
alter table Visitor add CONFIRM_VALUE varchar (99);

create unique index Visitor_loginid on Visitor (LOGINID);

insert into VISITOR (LOGINID, PASSWORD_VALUE, FIRST_NAME, LAST_NAME, MODIFIED, CREATED ) values ('turbine', 'turbine', 'turbine', 'turbine', null, null );


-- -------------------------------------------------------------------
drop table VisitorRole;

create table VisitorRole
(
    VISITORID		integer NOT NULL,
    ROLEID		integer NOT NULL,
    PRIMARY KEY (VISITORID, ROLEID)
);

create unique index VisitorRole_visitorid_roleid_index on VisitorRole ( VISITORID, ROLEID );
