#!/usr/local/bin/pgbash
######################################################################
# Create Schema member.
#
# ex) ./member.ddl postgres@db.ppp.com postgres NULL
#
PGDATABASE='$1'
PGUSER='$2'
PGPASSWORD='$3'
PGUSER2='admin3'
######################################################################
#---------- 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,
kind varchar(12)
check(kind in ('*','net','post'))
default '*',
staff varchar(24) default '',
name varchar(32) not null,
zip char(8)
check(zip between '000-0000' and '999-9999')
default '000-0000',
address1 varchar(64) not null,
address2 varchar(64),
tel varchar(16) not null,
email varchar(64),
org 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_address1 varchar(64), old_address2 varchar(64),
old_tel varchar(16), old_email varchar(64),
old_org varchar(128),
new_zip char(8), new_address1 varchar(64), new_address2 varchar(64),
new_tel varchar(16), new_email varchar(64),
new_org 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.address1, old.address2,
old.tel, old.email,
old.org,
new.zip, new.address1, new.address2,
new.tel, new.email,
new.org,
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_address1 as "address1",
a.old_address2 as "address2",
a.old_tel as "tel",
a.old_email as "email",
a.old_org as "org",
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_address1 as "address1",
b.new_address2 as "address2",
b.new_tel as "tel",
b.new_email as "email",
b.new_org as "org",
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;
|