hrm_auth.sql

很全面的hrm管理。提供通用的企业人力资源管理。

/*==============================================================*/
/* DBMS name:      ORACLE Version 9i                            */
/* Created on:     2008-9-25 20:17:49                           */
/*==============================================================*/


alter table action
   drop constraint FK_ACTION_REFERENCE_MENU;

alter table role_action
   drop constraint FK_ROLE_ACT_REFERENCE_ACTION;

alter table role_action
   drop constraint FK_ROLE_ACT_REFERENCE_ROLE;

alter table user_role
   drop constraint FK_USER_ROL_REFERENCE_USER;

alter table user_role
   drop constraint FK_USER_ROL_REFERENCE_ROLE;

drop table action cascade constraints;

drop table menu cascade constraints;

drop table role cascade constraints;

drop table role_action cascade constraints;

drop table "user" cascade constraints;

drop table user_role cascade constraints;

/*==============================================================*/
/* Table: action                                                */
/*==============================================================*/
create table action  (
   id                   VARCHAR2(32)                    not null,
   name                 VARCHAR2(100)                   not null,
   "from"               VARCHAR(500)                    not null,
   "to"                 VARCHAR2(500)                   not null,
   page                 VARCHAR(500)                    not null,
   statusbar            VARCHAR2(500),
   menu_id              VARCHAR2(32)                    not null,
   type                 VARCHAR2(50),
   form                 VARCHAR2(50),
   javascript           VARCHAR2(100),
   constraint PK_ACTION primary key (id)
);

/*==============================================================*/
/* Table: menu                                                  */
/*==============================================================*/
create table menu  (
   id                   VARCHAR2(32)                    not null,
   name                 VARCHAR2(100)                   not null,
   memo                 VARCHAR2(200),
   url                  VARCHAR2(500)                   not null,
   welcome              INT                             not null
      constraint CKC_WELCOME_MENU check (welcome in (0,1)),
   introduce            VARCHAR2(500),
   constraint PK_MENU primary key (id)
);

/*==============================================================*/
/* Table: role                                                  */
/*==============================================================*/
create table role  (
   id                   VARCHAR2(32)                    not null,
   name                 VARCHAR2(100)                   not null,
   memo                 VARCHAR2(200),
   constraint PK_ROLE primary key (id)
);

/*==============================================================*/
/* Table: role_action                                           */
/*==============================================================*/
create table role_action  (
   action_id            VARCHAR2(32)                    not null,
   role_id              VARCHAR2(32)                    not null,
   constraint PK_ROLE_ACTION primary key (action_id, role_id)
);

/*==============================================================*/
/* Table: "user"                                                */
/*==============================================================*/
create table "user"  (
   id                   VARCHAR2(32)                    not null,
   password             VARCHAR2(32)                    not null,
   name                 VARCHAR2(100)                   not null,
   real_name            VARCHAR2(100)                   not null,
   last_login_time      DATE                            not null,
   login_status         INT                             not null
      constraint CKC_LOGIN_STATUS_USER check (login_status in (0,1)),
   is_concurrent        INT                            
      constraint CKC_IS_CONCURRENT_USER check (is_concurrent is null or (is_concurrent in (0,1))),
   constraint PK_USER primary key (id)
);

/*==============================================================*/
/* Table: user_role                                             */
/*==============================================================*/
create table user_role  (
   user_id              VARCHAR2(32)                    not null,
   role_id              VARCHAR2(32)                    not null,
   constraint PK_USER_ROLE primary key (user_id, role_id)
);

alter table action
   add constraint FK_ACTION_REFERENCE_MENU foreign key (menu_id)
      references menu (id);

alter table role_action
   add constraint FK_ROLE_ACT_REFERENCE_ACTION foreign key (action_id)
      references action (id);

alter table role_action
   add constraint FK_ROLE_ACT_REFERENCE_ROLE foreign key (role_id)
      references role (id);

alter table user_role
   add constraint FK_USER_ROL_REFERENCE_USER foreign key (user_id)
      references "user" (id);

alter table user_role
   add constraint FK_USER_ROL_REFERENCE_ROLE foreign key (role_id)
      references role (id);