SQL
-- CAMPAIGN Table Create SQL
CREATE SEQUENCE CI_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- CAMPAIGN_USER Table Create SQL
CREATE TABLE "KNOCK2"."CAMPAIGN_USER"
( "CI_IDX" NUMBER,
"U_IDX" NUMBER,
"CI_EMBLEM_STATUS" VARCHAR2(20 BYTE)
) ;
-- CAMPAIGN_CATEGORY Table Create SQL
CREATE TABLE "CAMPAIGN_CATEGORY"
( "CC_IDX" NUMBER,
"CC_NAME" VARCHAR2(50 BYTE)
);
CREATE SEQUENCE CAMPAIGN_CATEGORY_SEQ START WITH 1 INCREMENT BY 1;
-- BOARD Table Create SQL
CREATE TABLE BOARD(
B_IDX NUMBER NOT NULL,
B_SUBJECT VARCHAR2(100) NOT NULL,
B_CONTENT VARCHAR2(3000) NOT NULL,
U_IDX NUMBER NOT NULL,
B_REGDATE DATE DEFAULT SYSDATE NOT NULL,
B_HIT NUMBER DEFAULT 0 NOT NULL,
B_VIEWS NUMBER DEFAULT 0 NOT NULL,
B_CATEGORY NUMBER NOT NULL,
B_FILE VARCHAR2(3000) NULL,
CI_IDX NUMBER NULL,
SB_IDX NUMBER NOT NULL,
B_ACTIVE VARCHAR2(20) DEFAULT 1 NOT NULL,
CONSTRAINT PK_BOARD PRIMARY KEY (B_IDX)
);
CREATE SEQUENCE B_IDX_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE BOARD_CATEGORY(
BC_IDX NUMBER NOT NULL,
BC_NAME VARCHAR2(50) NOT NULL,
BC_CATEGORY NUMBER NOT NULL
);
CREATE SEQUENCE BOARD_CATEGORY_SEQ START WITH 1 INCREMENT BY 1;
-- COMMENTS Table Create SQL
CREATE TABLE COMMENTS(
M_IDX NUMBER NOT NULL,
C_CONTENT VARCHAR2(1000) NOT NULL,
B_IDX NUMBER NOT NULL,
U_IDX NUMBER NOT NULL,
C_REGDATE TIMESTAMP DEFAULT SYSDATE NOT NULL,
CONSTRAINT PK_COMMENTS PRIMARY KEY (M_IDX)
);
CREATE SEQUENCE M_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- SUBTITLE Table Create SQL
CREATE TABLE SUBTITLE(
SB_IDX NUMBER NOT NULL,
SB_NAME VARCHAR2(200) NOT NULL,
CONSTRAINT PK_SUBTITLE PRIMARY KEY (SB_IDX)
);
CREATE SEQUENCE SB_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- USERS Table Create SQL
CREATE TABLE USERS(
U_IDX NUMBER NOT NULL,
U_ID VARCHAR2(30) NOT NULL,
U_NAME VARCHAR2(30) NOT NULL,
U_PWD VARCHAR2(20) NOT NULL,
U_NICKNAME VARCHAR2(20) NOT NULL,
U_EMAIL VARCHAR2(30) NOT NULL,
U_PHONE VARCHAR2(15) NOT NULL,
U_BIRTH VARCHAR2(20) NOT NULL,
U_TOT_POINT NUMBER DEFAULT 1000 NOT NULL ,
U_TYPE VARCHAR2(20) DEFAULT 1 NOT NULL,
U_ACTIVE VARCHAR2(20) DEFAULT 1 NOT NULL,
U_LASTLOGIN DATE DEFAULT SYSDATE NULL,
U_GENDER VARCHAR2(20) NOT NULL,
U_ADDRESS VARCHAR2(300) NOT NULL,
U_JOINDATE DATE DEFAULT SYSDATE NOT NULL,
U_IMG VARCHAR2(1000) NULL,
U_IMGPATH VARCHAR2(1000) NULL,
U_USED_POINT NUMBER NULL,
CONSTRAINT PK_USERS PRIMARY KEY (U_IDX)
);
CREATE SEQUENCE U_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- PROPOSAL Table Create SQL
CREATE TABLE "KNOCK2"."PROPOSAL"
( "CP_IDX" NUMBER,
"CP_CONTENT" VARCHAR2(3000 BYTE),
"CP_GOAL" VARCHAR2(1000 BYTE),
"CP_TITLE" VARCHAR2(50 BYTE),
"CP_REGDATE" DATE DEFAULT SYSDATE,
"CP_GOALPOINT" NUMBER,
"CP_FILE" VARCHAR2(3000 BYTE),
"U_IDX" NUMBER,
"CP_KEYWORD1" VARCHAR2(30 BYTE),
"CP_KEYWORD2" VARCHAR2(30 BYTE),
"CP_KEYWORD3" VARCHAR2(30 BYTE)
) ;
CREATE SEQUENCE CP_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- FUNDING Table Create SQL
CREATE SEQUENCE CF_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- FUNDING_USER Table Create SQL
CREATE TABLE "KNOCK2"."FUNDING"
( "CF_IDX" NUMBER,
"CF_TITLE" VARCHAR2(50 BYTE),
"CF_CONTENT" VARCHAR2(3000 BYTE),
"CF_ENDDATE" DATE DEFAULT SYSDATE,
"CF_STARTDATE" DATE DEFAULT SYSDATE,
"CF_COLLECTED" NUMBER,
"CF_STATUS" VARCHAR2(20 BYTE),
"CF_FILE" VARCHAR2(3000 BYTE),
"CC_NAME" VARCHAR2(20 BYTE),
"CF_GOALPOINT" NUMBER,
"CF_GOAL" VARCHAR2(1000 BYTE),
"CP_IDX" VARCHAR2(20 BYTE),
"U_IDX" NUMBER,
"CF_KEYWORD1" VARCHAR2(20 BYTE),
"CF_KEYWORD2" VARCHAR2(20 BYTE),
"CF_KEYWORD3" VARCHAR2(20 BYTE)
) ;
-- KEYWORD Table Create SQL
CREATE TABLE KEYWORD(
K_IDX NUMBER NULL,
K_CONTENT VARCHAR2(30) NOT NULL,
K_COUNT NUMBER DEFAULT 0 NOT NULL,
CONSTRAINT PK_KEYWORD PRIMARY KEY (K_IDX)
);
CREATE SEQUENCE K_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- CONTACT Table Create SQL
CREATE TABLE CONTACT(
CT_IDX NUMBER NOT NULL,
CT_TITLE VARCHAR2(100) NOT NULL,
CT_CONTENT VARCHAR2(3000) NOT NULL,
U_IDX NUMBER NOT NULL,
CT_WRITEDATE DATE DEFAULT SYSDATE NOT NULL,
CT_RESP VARCHAR2(20) DEFAULT 0 NOT NULL,
CTC_IDX NUMBER NOT NULL,
PRIMARY KEY (CT_IDX)
);
CREATE SEQUENCE CT_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- CONTACT_CATEGORY Table Create SQL
CREATE TABLE CONTACT_CATEGORY(
CTC_IDX NUMBER NOT NULL,
CTC_NAME VARCHAR2(300) NOT NULL,
PRIMARY KEY (CTC_IDX)
);
CREATE SEQUENCE CTC_IDX_SEQ START WITH 1 INCREMENT BY 1;
-- CONTACT_COMMENT Table Create SQL
CREATE TABLE CONTACT_COMMENT(
CM_IDX NUMBER NOT NULL,
CM_CONTENT VARCHAR2(3000) NOT NULL,
CT_IDX NUMBER NOT NULL,
U_IDX VARCHAR2(20) NOT NULL,
CM_WRITEDATE DATE DEFAULT SYSDATE NOT NULL,
PRIMARY KEY (CM_IDX)
);
CREATE SEQUENCE CM_IDX_SEQ START WITH 1 INCREMENT BY 1;
샘플데이터
USERS
--users
INSERT INTO USERS(U_IDX, U_ID, U_NAME, U_PWD, U_NICKNAME,U_EMAIL, U_PHONE, U_BIRTH, U_TOT_POINT, U_TYPE,
U_ACTIVE, U_GENDER, U_ADDRESS, U_JOINDATE)
VALUES(U_IDX_SEQ.nextval, 'test', '존도', 'whseh111', '존도에욤', 'john@do.com', '010-2222-3333', '1985-01-01', default, 1, default, 0, '서울시 마포구 신수동', sysdate);
INSERT INTO USERS(U_IDX, U_ID, U_NAME, U_PWD, U_NICKNAME,U_EMAIL, U_PHONE, U_BIRTH, U_TOT_POINT, U_TYPE,
U_ACTIVE, U_GENDER, U_ADDRESS, U_JOINDATE)
VALUES(U_IDX_SEQ.nextval, 'hong', '홍길동', 'test12345', '길동이', 'gildong@test.com', '010-4567-8899', '1999-11-01', default, 1, default, 0, '서울시 마포구 대흥동', sysdate);
INSERT INTO USERS(U_IDX, U_ID, U_NAME, U_PWD, U_NICKNAME,U_EMAIL, U_PHONE, U_BIRTH, U_TOT_POINT, U_TYPE,
U_ACTIVE, U_GENDER, U_ADDRESS, U_JOINDATE)
VALUES(U_IDX_SEQ.nextval, 'admin', '관리자', 'admin1111', '대장', 'admin@knock.com', '010-1588-1588', '1995-12-15', 0, 0, default, 1, '서울시 강남구 서초동', sysdate);
INSERT INTO USERS(U_IDX, U_ID, U_NAME, U_PWD, U_NICKNAME,U_EMAIL, U_PHONE, U_BIRTH, U_TOT_POINT, U_TYPE,
U_ACTIVE, U_GENDER, U_ADDRESS, U_JOINDATE)
VALUES(U_IDX_SEQ.nextval, 'dobby', '김도비', 'dobby', '도비이즈프리', 'dobby@test.com', '010-6666-4444', '1993-04-09', default, 1, default, 1, '서울시 송파구 잠실동', sysdate);
select * from users;
BOARD
--board
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '첫번째게시물','첫-내용',
1,sysdate, 0,0,1000,'파일경로',1,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '두번째게시물','둘-내용',
2,sysdate, 0,0,1000,'파일경로',2,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '세번째게시물','셋-내용',
3,sysdate, 0,0,1000,'파일경로',3,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '네번째게시물','넷-내용',
4,sysdate, 0,0,1000,'파일경로',4,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '다섯번째게시물','다섯-내용',
5,sysdate, 0,0,1000,'파일경로',5,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '여섯번째게시물','여섯-내용',
6,sysdate, 0,0,1000,'파일경로',6,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '일곱번째게시물','일곱-내용',
7,sysdate, 0,0,1000,'파일경로',7,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '여덟번째게시물','여덟-내용',
8,sysdate, 0,0,1000,'파일경로',8,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '아홉번째게시물','아홉-내용',
9,sysdate, 0,0,1000,'파일경로',9,3000,1);
INSERT INTO BOARD VALUES (B_IDX_SEQ.nextval, '열번째게시물','열-내용',
10,sysdate, 0,0,1000,'파일경로',10,3000,1);
BOARD_CATEGORY
-- BOARD_CATEGORY
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름1', 1000);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름2', 1010);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름3', 1000);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름4', 1010);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름5', 1000);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름6', 1010);
INSERT INTO BOARD_CATEGORY VALUES (BOARD_CATEGORY_SEQ.nextval, '카테고리이름7', 1000);
CAMAPAIGN
-- CAMPAIGN
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'줍깅','한강공원을 달리며 걷고 뛰면서 쓰레기를 주워봐요','조깅하며 주운 쓰레기 3개 인증',to_date('21/07/31','RR/MM/DD'),to_date('21/06/26','RR/MM/DD'),100,'1','/resource/img/campaign/running.png','/resource/img/campaign/emblem/emblem01.png',1,3000,'환경보호','플로깅','도시공원',3000,2,'플로깅');
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'Meat Free Monday!','하루 고기를 먹지 않는 것만으로 한 사람당 13만 2천4백리터의 물을 절약할 수 있어요.','일주일에 하루, 월요일엔 고기 없이',to_date('21/06/11','RR/MM/DD'),to_date('21/06/29','RR/MM/DD'),100,'3','/resource/img/campaign/vegan.png','/resource/img/campaign/emblem/emblem02.png',1,20100,'채식','비건','친환경소비',20000,5,'채식');
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'재활용하기','우리가 모두 재활용하는 그날까지','1. 플라스틱 라벨 떼고 버리기.2. 깨끗하게 씻고 재활용하기',to_date('21/07/14','RR/MM/DD'),to_date('21/06/29','RR/MM/DD'),500,'1','/resource/img/campaign/recycle.png','/resource/img/campaign/emblem/emblem03.png',1,26500,'재활용','캔','플라스틱',26000,6,'제로플라스틱');
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'낭비는 노노','잠깐!정말 버리실건가요?','쓰레기 줄이기',to_date('22/02/02','RR/MM/DD'),to_date('21/06/10','RR/MM/DD'),500,'1','/resource/img/campaign/less.png','/resource/img/campaign/emblem/emblem04.png',1,65500,'휴지통','쓰레기','휴지통찾기',50000,7,'제로플라스틱');
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'텀블러 애용하기','플라스틱을 줄여보아요','카페가서 텀블러쓰고 인증',to_date('21/08/19','RR/MM/DD'),to_date('21/06/25','RR/MM/DD'),1800,'1','/resource/img/campaign/green.png','/resource/img/campaign/emblem/emblem05.png',1,43400,'텀블러','환경보호','카페',32000,8,'제로플라스틱');
Insert into KNOCK2.CAMPAIGN (CI_IDX,CI_TITLE,CI_CONTENT,CI_GOAL,CI_ENDDATE,CI_STARTDATE,CI_ESTIMATEDPOINT,CI_STATUS,CI_FILE,CI_EMBLEM,U_IDX,C_TOTPOINT,CI_KEYWORD1,CI_KEYWORD2,CI_KEYWORD3,CI_BASEPOINT,CF_IDX,CC_NAME) values (CI_IDX_SEQ.NEXTVAL,'줍깅','한강공원을 달리며 걷고 뛰면서 쓰레기를 주워봐요','조깅하며 주운 쓰레기 3개 인증',to_date('21/07/06','RR/MM/DD'),to_date('21/06/19','RR/MM/DD'),100,'3','/resource/img/campaign/green.png','/resource/img/campaign/emblem/emblem06.png',2,41700,'환경보호','걷기','줍기',32000,9,'플로깅');
CAMPAIGN_CATEGORY
--CAMPAIGN_CATEHORY
INSERT INTO CAMPAIGN_CATEGORY (CC_IDX, CC_NAME) VALUES ( CAMPAIGN_CATEGORY_SEQ.NEXTVAL, '생활' );
INSERT INTO CAMPAIGN_CATEGORY (CC_IDX, CC_NAME) VALUES ( CAMPAIGN_CATEGORY_SEQ.NEXTVAL, '분리수거' );
INSERT INTO CAMPAIGN_CATEGORY (CC_IDX, CC_NAME) VALUES ( CAMPAIGN_CATEGORY_SEQ.NEXTVAL, '공기오염' );
INSERT INTO CAMPAIGN_CATEGORY (CC_IDX, CC_NAME) VALUES ( CAMPAIGN_CATEGORY_SEQ.NEXTVAL, '대기오염' );
INSERT INTO CAMPAIGN_CATEGORY (CC_IDX, CC_NAME) VALUES ( CAMPAIGN_CATEGORY_SEQ.NEXTVAL, '환경미화' );
CAMAPAIGN_USER
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(1, 1, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(1, 2, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(1, 3, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(2, 1, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(2, 3, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(2, 4, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(3, 1, 1);
INSERT INTO CAMPAIGN_USER(CI_IDX, U_IDX, CI_EMBLEM_STATUS)
VALUES(3, 2, 1);
COMMENTS
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용1', 1, 1, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용2', 2, 2, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용3', 3, 3, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용4', 4, 4, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용5', 5, 5, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용6', 6, 6, SYSDATE);
INSERT INTO COMMENTS VALUES (M_IDX_SEQ.nextval, '댓글내용7', 7, 7, SYSDATE);