-- 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 sequence visitor_visitorid_seq;


-- -------------------------------------------------------------------
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 );
