코배웹 쿼리

book_ex : 계정생성

create user book_ex identified by book_ex;
grant connect, dba to book_ex;

tbl_board : 게시판

create sequence seq_board;

create table tbl_board(
	bno number(10, 0),
	title varchar2(200) not null, 
	content varchar2(2000) not null,
	writer varchar2(50) not null,
	regdate date default sysdate,
	updatedate date default sysdate
);

alter table tbl_board add constraint pk_board primary key (bno);

insert into tbl_board (bno, title, content, writer) values (seq_board.nextval, '테스트 제목', '테스트 내용', 'user000')

tbl_reply : 댓글

create table tbl_reply(
    rno number(10,0),
    bno number(10,0) not null,
    reply varchar2(1000) not null,
    replyer varchar2(50) not null,
    replyDate date default sysdate,
    updateDate date default sysdate
);

create SEQUENCE seq_reply;

alter table tbl_reply add constraint pk_reply primary key(rno);

alter table tbl_reply add constraint fk_reply_board foreign key(bno) references tbl_board(bno);

insert into tbl_reply(rno, bno, reply, replyer) values(seq_reply.nextval, 9, 'user22', 'test comment');

tbl_attach : 파일첨부

create table tbl_attach(
	uuid varchar2(100) not null,
    uploadPath varchar2(200) not null,
    fileName varchar2(100) not null,
    filetype char(1) default 'I',
    bno number(10,0)
);

alter table tbl_attach add constraint pk_attach primary key(uuid);

alter table tbl_attach add constraint fk_board_attach foreign key(bno) references tbl_board(bno);

시큐리티 : 회원, 권한, 자동로그인

create table tbl_member(
    userid varchar2(50) primary key,
    userpw varchar2(100) not null,
    username varchar2(100) not null,
    regdate date default sysdate,
    updatedate date default sysdate,
    enabled char(1) default '1'
);

create table tbl_member_auth(
    userid varchar2(50) not null,
    auth varchar2(50) not null,
    constraint fk_member_auth foreign key(userid) references tbl_member(userid)
);

create table persistent_logins(
    username varchar2(64) not null,
    series varchar2(64) primary key,
    token varchar2(64) not null,
    last_used timestamp not null
);