/*
## This is a schema for Postgres for 
## the concept of Users/Roles/Permissions
##
## Please see the Html documentation for more information 
## on this SQL. Equivalent contributions of this for other 
## databases are welcome.

## The default database should be named "Turbine"
## This is created with the command "mysqladmin create Turbine"
#drop database if exists Turbine;
#create database Turbine;

## This schema is imported into Postgres with the command:
## mysql Turbine < Postgres_users_roles_permissions.sql
*/

drop table Permission;

create table Permission  (
    PERMISSIONID        SERIAL,
    PERMISSION          varchar (99) NOT NULL,
    UNIQUE (PERMISSION)
);

CREATE UNIQUE INDEX PERMISSION_NAME ON PERMISSION(PERMISSION);


drop table UserRole;

create table UserRole  (
    ROLEID      SERIAL,
    ROLENAME    varchar (99) NOT NULL,
    UNIQUE (ROLENAME)
);

CREATE UNIQUE INDEX ROLE_NAME ON USERROLE(ROLENAME);


drop table RolePermission;

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

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

drop table VisitorRole;

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

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


