#!/usr/local/bin/pgbash
source "system.conf"
######################################################################
# "create_schema"
#
# Create Schema member.
######################################################################
#
#---------- connect to DATABASE ----------
connect to $PGDATABASE user $PGUSER $PGPASSWORD;
##set client_encoding='SJIS';

#---------------------------------------------------------------------
#  MEMBER table
#---------------------------------------------------------------------
exec_sql -q "drop table member;"

create table member (
   userid       int4 primary key not null,	# User ID
   kind     	varchar(12) 			# Kind of user
                check(kind in ('',' ','A','B')) 
		default ' ',
   name         varchar(32) not null,		# User name
   zip		char(8)     			# zip code
                check(zip between '000-0000' and '999-9999') 
		default '000-0000',
   address	varchar(64) not null,		# User address
   tel		varchar(16) not null,
   email        varchar(64),
   skill	varchar(128),
   new_date	date        default 'now',
   up_date	timestamp   default 'now'
);

grant all    on member to $PGUSER;
grant select on member to $PGUSER2;

#---------------------------------------------------------------------
#  MEMBER table indexes
#---------------------------------------------------------------------
create unique index member_tel on member(tel) ;
create        index member_name on member(name) ;
create        index member_email on member(email) ;

#---------------------------------------------------------------------
#  MEMBER PASSWORD table
#---------------------------------------------------------------------
exec_sql -q "drop table member_passwd;"

create table member_passwd (
   userid       int4 primary key not null
                references member(userid)
                on delete cascade,
   passwd       varchar(17) not null
);

grant all    on member_passwd to $PGUSER;
grant select on member_passwd to $PGUSER2;

#---------------------------------------------------------------------
#  MEMBER SEQUENCE table
#---------------------------------------------------------------------
exec_sql -q "drop sequence member_seq;"

create sequence member_seq start 1301;

#---------------------------------------------------------------------
#  MEMBER LOG table
#---------------------------------------------------------------------
exec_sql -q "drop table member_log;"

create table member_log (
   userid int4, name varchar(32), 
   old_zip char(8), old_address varchar(64),
   old_tel varchar(16), old_email varchar(64), 
   old_skill varchar(128), 
   new_zip char(8), new_address varchar(64), 
   new_tel varchar(16), new_email varchar(64), 
   new_skill varchar(128), 
   up_date TIMESTAMP
);

#
#  Create rule MEMBER_RULE
#
create rule member_rule as on UPDATE to member DO 
   insert into member_log values(
   old.userid, old.name,
   old.zip, old.address, 
   old.tel, old.email, 
   old.skill,
   new.zip, new.address, 
   new.tel, new.email, 
   new.skill, 
   CURRENT_TIMESTAMP
);

#
#  Create view MEMBER_LOG_VIEW 
#
exec_sql -q "drop view member_log_view;"

create view member_log_view as 
   select '' as "stat", a.userid, a.name, 
          a.old_zip as "zip", 
          a.old_address as "address", 
          a.old_tel as "tel", 
          a.old_email as "email",
          a.old_skill as "skill", 
          a.up_date as "up_date"
    from member_log a, member_log b
    union
    select 'update' as "stat", b.userid, b.name,
          b.new_zip as "zip", 
          b.new_address as "address", 
          b.new_tel as "tel", 
          b.new_email as "email",
          b.new_skill as "skill", 
          b.up_date as "up_date"
    from member_log a, member_log b
    order by "up_date";

grant select on member_log_view to $PGUSER2;


#---------- disconnect DATABASE ----------
disconnect all;

