server-database.sql

drop all objects;

CREATE TABLE `ca_article` (
	`article_id` bigint generated by default as identity	COMMENT '게시글 아이디',
	`board_id`	int(11)	NOT NULL	COMMENT '게시판 아이디',
	`anon_id`	int(11)	NULL,
	`user_id`	int(11)	NOT NULL	COMMENT '사용자 식별자',
	`article_title`	varchar(50)	NOT NULL	COMMENT '제목',
	`article_title_pretty`	varchar(50)	NOT NULL	COMMENT '정리한 제목',
	`article_body`	mediumtext	NOT NULL	COMMENT '본문',
	`article_user_specific`	int(11)	NULL	COMMENT '유저 구별자 (세션 기록 등)',
	`article_ip`	varchar(64)	NOT NULL	COMMENT '작성자 아이피 (해시됨)',
	`article_nickname`	varchar(50)	NULL	COMMENT '작성자 닉네임 (익명,  로그인익명)',
	`article_password`	varchar(255)	NULL	COMMENT '비밀번호 (익명)',
	`article_type`	int(4)	NOT NULL	COMMENT '게시글 타입 (0:익명,1:로그인,2:로그인익명)',
	`article_write_time`	datetime	NOT NULL	COMMENT '작성 시간',
	`article_last_update_time`	datetime	NOT NULL	COMMENT '최종 수정 시간',
	`article_comment_count`	int(11)	NOT NULL	COMMENT '달린 댓글 수',
	`article_notice`	int(11)	NOT NULL	COMMENT '공지사항인가?',
	`article_view`	int(11)	NOT NULL	DEFAULT 0	COMMENT '조회수',
	`article_upvote`	int(11)	NOT NULL	COMMENT '추천수',
	`article_downvote`	int(11)	NOT NULL	COMMENT '비추수',
	`article_device`	int(11)	NOT NULL	COMMENT '어디서 작성했는지?',
	`article_abuse`	int(11)	NOT NULL	COMMENT '신고수',
	`article_del`	int(11)	NOT NULL	COMMENT '삭제여부',
	`article_hide`	int(11)	NOT NULL	COMMENT '숨김처리 여부',
	`article_bookmark_count`	int(11)	NOT NULL	COMMENT '북마크된 수'
);

CREATE TABLE `ca_board` (
	`board_id`	bigint generated by default as identity 	COMMENT '게시판 아이디',
	`board_key`	varchar(50)	NOT NULL	COMMENT '게시판 키',
	`board_name`	varchar(50)	NOT NULL	COMMENT '게시판 이름'
);

CREATE TABLE `ca_article_update_record` (
	`aur_id`	bigint generated by default as identity  NOT NULL,
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디',
	`anon_id`	int(11)	NOT NULL,
	`user_id`	int(11)	NOT NULL	COMMENT '사용자 식별자',
	`aur_time`	datetime	NOT NULL	COMMENT '수정 시간',
	`aur_ip`	varchar(64)	NOT NULL	COMMENT '수정 아이피'
);

CREATE TABLE `ca_article_tag` (
	`tag_id`	int(11)	NOT NULL,
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디'
);

CREATE TABLE `ca_tag` (
	`tag_id`	bigint generated by default as identity 	NOT NULL,
	`tag_name`	varchar(50)	NOT NULL,
	`tag_ref`	int(11)	NOT NULL	DEFAULT 0	COMMENT '태그 참조 게시글 수',
	`Field`	VARCHAR(255)	NULL
);

CREATE TABLE `ca_board_tag` (
	`tag_id`	int(11)	NOT NULL,
	`board_id`	int(11)	NOT NULL	COMMENT '게시판 아이디'
);

CREATE TABLE `ca_article_abuse` (
	`aa_id`	bigint generated by default as identity 	NOT NULL,
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디',
	`aa_body`	mediumtext	NULL	COMMENT '신고 본문',
	`aa_ip`	varchar(64)	NOT NULL	COMMENT '신고자 아이피',
	`Field`	VARCHAR(255)	NULL
);

CREATE TABLE `ca_article_meta` (
	`am_id`	bigint generated by default as identity 	NOT NULL,
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디',
	`ari_id`	int(11)	NULL	COMMENT '리사이즈 이미지 아이디',
	`am_token`	varchar(255)	NOT NULL	COMMENT '파일식별자',
	`am_o_filename`	varchar(255)	NOT NULL	COMMENT '원래 파일이름',
	`am_filename`	varchar(255)	NOT NULL	COMMENT '바뀐 파일이름',
	`am_type`	int(11)	NOT NULL	COMMENT '파일타입 (0:텍스트,1:이미지,2:동영상)',
	`am_size`	bigint	NOT NULL	COMMENT '파일크기',
	`am_ext`	varchar(10)	NOT NULL	COMMENT '파일확장자',
	`am_ip`	varchar(255)	NOT NULL	COMMENT '업로더 IP',
	`am_write_time`	datetime	NOT NULL	COMMENT '업로드 날짜',
	`am_ref`	int(11)	NOT NULL	COMMENT '다운로드 수',
	`am_width`	int(11)	NULL	COMMENT '이미지 너비',
	`am_height`	int(11)	NULL	COMMENT '이미지 높이'
);

CREATE TABLE `ca_article_resize_image` (
	`ari_id`	bigint generated by default as identity	NOT NULL,
	`ari_size`	int(11)	NOT NULL	COMMENT '리사이즈 사이즈',
	`ari_token`	varchar(255)	NOT NULL	COMMENT '리사이즈 식별자',
	`ari_filename`	varchar(255)	NOT NULL	COMMENT '리사이즈 파일이름',
	`ari_ext`	varchar(10)	NOT NULL	COMMENT '리사이즈 확장자'
);

CREATE TABLE `ca_user` (
	`user_id`	bigint generated by default as identity	COMMENT '사용자 식별자',
	`user_idid`	varchar(255)	NULL	COMMENT '사용자 아이디',
	`user_pw`	varchar(512)	NULL	COMMENT '사용자 비밀번호',
	`user_nickname`	varchar(20)	NOT NULL	COMMENT '사용자닉네임',
	`user_signup_time`	datetime	NOT NULL	COMMENT '가입날짜',
	`user_last_login_time`	datetime	NOT NULL	COMMENT '마지막 로그인날짜',
	`user_rest`	int(11)	NOT NULL	COMMENT '휴면 계정 여부'
);

CREATE TABLE `ca_oauth` (
	`oauth_id`	bigint generated by default as identity 	NOT NULL,
	`oauth_type`	int(11)	NOT NULL	COMMENT '타입(0:none,1:구글,2:애플,3:네이버,4:카카오)',
	`oauth_timed`	datetime	NOT NULL	COMMENT '연동날짜',
	`oauth_meta`	mediumtext	NULL	COMMENT '메타데이터'
);

CREATE TABLE `ca_user_oauth` (
	`oauth_id`	int(11)	NOT NULL	COMMENT '하나의 oauth로 여러개 아이디 생성가능',
	`user_id`	int(11)	NOT NULL	COMMENT '사용자 식별자'
);

CREATE TABLE `ca_conn` (
	`conn_id`	bigint generated by default as identity 	NOT NULL,
	`conn_ip`	varchar(64)	NOT NULL	COMMENT '접속 IP',
	`conn_time`	datetime	NOT NULL	COMMENT '접속 시간',
	`conn_ua`	mediumtext	NULL	COMMENT '접속 User Agent'
);

CREATE TABLE `ca_user_login` (
	`login_id`	bigint generated by default as identity 	NOT NULL,
	`user_id`	int(11)	NOT NULL	COMMENT '사용자 식별자',
	`oauth_id`	int(11)	NULL,
	`login_time`	datetime	NOT NULL	COMMENT '로그인 시간',
	`login_ip`	varchar(64)	NOT NULL	COMMENT '로그인 아이피',
	`login_type`	int(11)	NOT NULL	COMMENT '로그인 타입 (0:아이디패스워드,1:OAuth)',
	`login_loc`	int(11)	NOT NULL	COMMENT '로그인 장소 (0:웹,1:앱)',
	`login_succ`	int(11)	NOT NULL	COMMENT '로그인 성공여부 (0:실패,1:성공)'
);

CREATE TABLE `ca_article_read_user` (
	`read_id`	bigint generated by default as identity 	NOT NULL,
	`user_id`	int(11)	NOT NULL	COMMENT '사용자 식별자',
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디',
	`user_id2`	int(11)	NOT NULL	COMMENT '사용자 식별자',
	`read_time`	datetime	NOT NULL	COMMENT '읽은 시간',
	`read_clock`	int(11)	NOT NULL	COMMENT '읽은 시각 (몇 초 읽었는지)'
);

CREATE TABLE `ca_article_read_anon` (
	`reada_id`	bigint generated by default as identity 	NOT NULL,
	`article_id`	int(11)	NOT NULL	COMMENT '게시글 아이디',
	`anon_id2`	int(11)	NULL,
	`reada_time`	datetime	NOT NULL	COMMENT '읽은 시간',
	`reada_clock`	int(11)	NOT NULL	COMMENT '읽은 시각'
);

CREATE TABLE `ca_anon` (
	`anon_id`	bigint generated by default as identity 	NOT NULL,
	`anon_time`	datetime	NOT NULL	COMMENT '최초 접속 시간',
	`anon_last_time`	datetime	NOT NULL	COMMENT '마지막 접속 시간',
	`anon_sess`	varchar(512)	NULL	COMMENT '세션 식별자',
	`anon_jwt`	varchar(512)	NULL	COMMENT '토큰 식별자',
	`anon_type`	int(11)	NOT NULL	COMMENT '타입(0:세션,1:토큰)'
);

CREATE TABLE `ca_anon_sess` (
	`anonsess_id`	bigint generated by default as identity 	NOT NULL,
	`anon_id`	int(11)	NOT NULL,
	`anonsess_ip`	varchar(64)	NOT NULL	COMMENT '세션 발급 아이피',
	`anonsess_time`	datetime	NOT NULL	COMMENT '세션 발급 시간',
	`anonsess_refresh`	int(11)	NOT NULL	COMMENT '새로고침 여부(0:신규,1:갱신)',
	`anonsess_pre_sess`	varchar(512)	NULL	COMMENT '이전 세션 식별자'
);

CREATE TABLE `ca_anon_jwt` (
	`anonjwt_id`	bigint generated by default as identity 	NOT NULL,
	`anon_id`	int(11)	NOT NULL,
	`anonjwt_ip`	varchar(64)	NOT NULL	COMMENT '토큰 발급 아이피',
	`anonjwt_time`	datetime	NOT NULL	COMMENT '토큰 발급 시간',
	`anonjwt_refresh`	int(11)	NOT NULL	COMMENT '새로고침 여부(0:신규,1:갱신)',
	`anonjwt_pre_jwt`	varchar(512)	NULL	COMMENT '이전 토큰 식별자'
);

ALTER TABLE `ca_article` ADD CONSTRAINT PK_CA_ARTICLE PRIMARY KEY (
	`article_id`
);

ALTER TABLE `ca_board` ADD CONSTRAINT PK_CA_BOARD PRIMARY KEY (
	`board_id`
);

ALTER TABLE `ca_article_update_record` ADD CONSTRAINT PK_CA_ARTICLE_UPDATE_RECORD PRIMARY KEY (
	`aur_id`
);

ALTER TABLE `ca_article_tag` ADD CONSTRAINT PK_CA_ARTICLE_TAG PRIMARY KEY (
	`tag_id`,
	`article_id`
);

ALTER TABLE `ca_tag` ADD CONSTRAINT PK_CA_TAG PRIMARY KEY (
	`tag_id`
);

ALTER TABLE `ca_board_tag` ADD CONSTRAINT PK_CA_BOARD_TAG PRIMARY KEY (
	`tag_id`,
	`board_id`
);

ALTER TABLE `ca_article_abuse` ADD CONSTRAINT PK_CA_ARTICLE_ABUSR PRIMARY KEY (
	`aa_id`
);

ALTER TABLE `ca_article_meta` ADD CONSTRAINT PK_CA_ARTICLE_META PRIMARY KEY (
	`am_id`
);

ALTER TABLE `ca_article_resize_image` ADD CONSTRAINT PK_CA_ARTICLE_RESIZE_IMAGE PRIMARY KEY (
	`ari_id`
);

ALTER TABLE `ca_user` ADD CONSTRAINT PK_CA_USER PRIMARY KEY (
	`user_id`
);

ALTER TABLE `ca_oauth` ADD CONSTRAINT PK_CA_OAUTH PRIMARY KEY (
	`oauth_id`
);

ALTER TABLE `ca_user_oauth` ADD CONSTRAINT PK_CA_USER_OAUTH PRIMARY KEY (
	`oauth_id`,
	`user_id`
);

ALTER TABLE `ca_conn` ADD CONSTRAINT PK_CA_CONN PRIMARY KEY (
	`conn_id`
);

ALTER TABLE `ca_user_login` ADD CONSTRAINT PK_CA_USER_LOGIN PRIMARY KEY (
	`login_id`
);

ALTER TABLE `ca_article_read_user` ADD CONSTRAINT PK_CA_ARTICLE_READ_USER PRIMARY KEY (
	`read_id`
);

ALTER TABLE `ca_article_read_anon` ADD CONSTRAINT PK_CA_ARTICLE_READ_ANON PRIMARY KEY (
	`reada_id`
);

ALTER TABLE `ca_anon` ADD CONSTRAINT PK_CA_ANON PRIMARY KEY (
	`anon_id`
);

ALTER TABLE `ca_anon_sess` ADD CONSTRAINT PK_CA_ANON_SESS PRIMARY KEY (
	`anonsess_id`
);

ALTER TABLE `ca_anon_jwt` ADD CONSTRAINT PK_CA_ANON_JWT PRIMARY KEY (
	`anonjwt_id`
);

ALTER TABLE `ca_article` ADD CONSTRAINT `FK_ca_board_TO_ca_article_1` FOREIGN KEY (
	`board_id`
)
REFERENCES `ca_board` (
	`board_id`
);

ALTER TABLE `ca_article` ADD CONSTRAINT `FK_ca_anon_TO_ca_article_1` FOREIGN KEY (
	`anon_id`
)
REFERENCES `ca_anon` (
	`anon_id`
);

ALTER TABLE `ca_article` ADD CONSTRAINT `FK_ca_user_TO_ca_article_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `ca_user` (
	`user_id`
);

ALTER TABLE `ca_article_update_record` ADD CONSTRAINT `FK_ca_article_TO_ca_article_update_record_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_article_update_record` ADD CONSTRAINT `FK_ca_article_TO_ca_article_update_record_2` FOREIGN KEY (
	`user_id`
)
REFERENCES `ca_article` (
	`user_id`
);

ALTER TABLE `ca_article_update_record` ADD CONSTRAINT `FK_ca_anon_TO_ca_article_update_record_1` FOREIGN KEY (
	`anon_id`
)
REFERENCES `ca_anon` (
	`anon_id`
);

ALTER TABLE `ca_article_tag` ADD CONSTRAINT `FK_ca_tag_TO_ca_article_tag_1` FOREIGN KEY (
	`tag_id`
)
REFERENCES `ca_tag` (
	`tag_id`
);

ALTER TABLE `ca_article_tag` ADD CONSTRAINT `FK_ca_article_TO_ca_article_tag_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_board_tag` ADD CONSTRAINT `FK_ca_tag_TO_ca_board_tag_1` FOREIGN KEY (
	`tag_id`
)
REFERENCES `ca_tag` (
	`tag_id`
);

ALTER TABLE `ca_board_tag` ADD CONSTRAINT `FK_ca_board_TO_ca_board_tag_1` FOREIGN KEY (
	`board_id`
)
REFERENCES `ca_board` (
	`board_id`
);

ALTER TABLE `ca_article_abuse` ADD CONSTRAINT `FK_ca_article_TO_ca_article_abuse_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_article_meta` ADD CONSTRAINT `FK_ca_article_TO_ca_article_meta_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_article_meta` ADD CONSTRAINT `FK_ca_article_resize_image_TO_ca_article_meta_1` FOREIGN KEY (
	`ari_id`
)
REFERENCES `ca_article_resize_image` (
	`ari_id`
);

ALTER TABLE `ca_user_oauth` ADD CONSTRAINT `FK_ca_oauth_TO_ca_user_oauth_1` FOREIGN KEY (
	`oauth_id`
)
REFERENCES `ca_oauth` (
	`oauth_id`
);

ALTER TABLE `ca_user_oauth` ADD CONSTRAINT `FK_ca_user_TO_ca_user_oauth_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `ca_user` (
	`user_id`
);

ALTER TABLE `ca_user_login` ADD CONSTRAINT `FK_ca_user_TO_ca_user_login_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `ca_user` (
	`user_id`
);

ALTER TABLE `ca_user_login` ADD CONSTRAINT `FK_ca_oauth_TO_ca_user_login_1` FOREIGN KEY (
	`oauth_id`
)
REFERENCES `ca_oauth` (
	`oauth_id`
);

ALTER TABLE `ca_article_read_user` ADD CONSTRAINT `FK_ca_user_TO_ca_article_read_user_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `ca_user` (
	`user_id`
);

ALTER TABLE `ca_article_read_user` ADD CONSTRAINT `FK_ca_article_TO_ca_article_read_user_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_article_read_user` ADD CONSTRAINT `FK_ca_article_TO_ca_article_read_user_2` FOREIGN KEY (
	`user_id2`
)
REFERENCES `ca_article` (
	`user_id`
);

ALTER TABLE `ca_article_read_anon` ADD CONSTRAINT `FK_ca_article_TO_ca_article_read_anon_1` FOREIGN KEY (
	`article_id`
)
REFERENCES `ca_article` (
	`article_id`
);

ALTER TABLE `ca_article_read_anon` ADD CONSTRAINT `FK_ca_anon_TO_ca_article_read_anon_1` FOREIGN KEY (
	`anon_id2`
)
REFERENCES `ca_anon` (
	`anon_id`
);

ALTER TABLE `ca_anon_sess` ADD CONSTRAINT `FK_ca_anon_TO_ca_anon_sess_1` FOREIGN KEY (
	`anon_id`
)
REFERENCES `ca_anon` (
	`anon_id`
);

ALTER TABLE `ca_anon_jwt` ADD CONSTRAINT `FK_ca_anon_TO_ca_anon_jwt_1` FOREIGN KEY (
	`anon_id`
)
REFERENCES `ca_anon` (
	`anon_id`
);