[Instantrip] 사용자, 메시지 테이블 생성

 

# 사용자
CREATE TABLE user_ (
	user_id	NUMBER		NOT NULL,
    login_id	VARCHAR2(16)		NOT NULL,
	login_pw	VARCHAR2(256)		NOT NULL,
	user_name	VARCHAR2(12)		NOT NULL,
	email	VARCHAR2(50)		NOT NULL,
	join_date	TIMESTAMP	DEFAULT sysdate	NULL,
	active_status	CHAR(1)	DEFAULT 'T'	NULL,
	role	VARCHAR2(30)		NULL
);

COMMENT ON COLUMN user_.user_id IS '고객 식별 ID  (시퀀스)';

COMMENT ON COLUMN user_.login_id IS '고객 로그인ID';

COMMENT ON COLUMN user_.login_pw IS '비밀번호';

COMMENT ON COLUMN user_.user_name IS '사용자 닉네임';

COMMENT ON COLUMN user_.email IS '사용자 이메일';

COMMENT ON COLUMN user_.join_date IS '가입일';

COMMENT ON COLUMN user_.active_status IS '계정 활성화 여부 T/F';

COMMENT ON COLUMN user_.role IS '역할';

# 메시지
CREATE TABLE message (
	message_id  NUMBER		NOT NULL,
	user_id	NUMBER		NOT NULL,
	create_time	TIMESTAMP	DEFAULT sysdate	NULL,
	duration	NUMBER		NOT NULL,
	expire_time	TIMESTAMP		NULL,
	contents	VARCHAR2(300)		NOT NULL,
	message_type	VARCHAR2(30)	DEFAULT 'text'	NULL,
	active_status	CHAR(1)		NOT NULL,
	latitude	NUMBER		NULL,
	longitude	NUMBER		NULL
);

COMMENT ON COLUMN message.message_id IS '내부용 메시지 식별자 (시퀀스 사용)';

COMMENT ON COLUMN message.user_id IS '고객 식별 ID  (시퀀스)';

COMMENT ON COLUMN message.create_time IS '메시지 생성 시간';

COMMENT ON COLUMN message.duration IS '메시지 지속 시간 (분단위)';

COMMENT ON COLUMN message.expire_time IS '메시지 만료 시간';

COMMENT ON COLUMN message.content IS '메시지 내용';

COMMENT ON COLUMN message.message_type IS '메시지 타입 (text, image, video, secret 등)';

COMMENT ON COLUMN message.active_status IS '메시지 활성화 여부';

COMMENT ON COLUMN message.latitude IS '위도';

COMMENT ON COLUMN message.longitude IS '경도';

ALTER TABLE user_ ADD CONSTRAINT PK_USER_ PRIMARY KEY (
	user_id
);

ALTER TABLE message ADD CONSTRAINT PK_MESSAGE PRIMARY KEY (
	message_id
);
commit;

 

그리고 ID값 자동 증가를 위해 시퀀스를 두개 설정해주었다.

create sequence user_id_seq
increment by 1
start with 1
minvalue 1
nocycle
nocache
noorder;

create sequence message_id_seq
increment by 1
start with 1
minvalue 1
nocycle
nocache
noorder;

 

system계정에서 생성하였기 때문에 다른 계정에서 접근할 수 있도록 synonym 및 grant 설정을 주었다.

create public synonym
user_ for system.user_;

create public synonym
message for system.message;

grant select, insert, update, delete
on system.user_
to instantrip;
commit;
grant select, insert, update, delete
on system.message
to instantrip;