/* * JsTree 추적 로그 테이블 * 트리거 Log를 저장합니다. */ CREATE TABLE T_JSTREE_AGGREGATE_RESULT_LOG ( C_ID NUMBER NOT NULL, C_PARENTID NUMBER NOT NULL, C_POSITION NUMBER NOT NULL, C_LEFT NUMBER NOT NULL, C_RIGHT NUMBER NOT NULL, C_LEVEL NUMBER NOT NULL, C_TITLE VARCHAR2(4000 BYTE), C_TYPE VARCHAR2(4000 BYTE), C_METHOD VARCHAR2(4000 BYTE), C_STATE VARCHAR2(4000 BYTE), C_DATE DATE NOT NULL ); COMMENT ON TABLE T_JSTREE_AGGREGATE_RESULT_LOG IS '기본 트리 스키마 트리거 로그'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_ID IS '노드 아이디'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_PARENTID IS '부모 노드 아이디'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_POSITION IS '노드 포지션'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_LEFT IS '노드 좌측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_RIGHT IS '노드 우측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_LEVEL IS '노드 DEPTH '; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_TITLE IS '노드 명'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_TYPE IS '노드 타입'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_METHOD IS '노드 변경 행위'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_STATE IS '노드 상태값 ( 이전인지. 이후인지)'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT_LOG.C_DATE IS '노드 변경 시'; /* * JsTree */ CREATE TABLE T_JSTREE_AGGREGATE_RESULT ( C_ID NUMBER NOT NULL, C_PARENTID NUMBER NOT NULL, C_POSITION NUMBER NOT NULL, C_LEFT NUMBER NOT NULL, C_RIGHT NUMBER NOT NULL, C_LEVEL NUMBER NOT NULL, C_TITLE VARCHAR2(4000 BYTE), C_TYPE VARCHAR2(4000 BYTE), CAL_TIME VARCHAR2(4000 BYTE), TOP_NAME VARCHAR2(4000 BYTE), TOP_NUMBER_OF_ADVANTAGES NUMBER, TOP_LIKE_COUNT NUMBER, TOP_TOTAL_REGISTERED_POSTS NUMBER, TOP_REGISTERED_HASH_TAG NUMBER, TOP_GRAPH_PERCENT NUMBER, TOP_VERSUS_SCORE NUMBER, MID_NAME VARCHAR2(4000 BYTE), MID_NUMBER_OF_ADVANTAGES NUMBER, MID_LIKE_COUNT NUMBER, MID_TOTAL_REGISTERED_POSTS NUMBER, MID_REGISTERED_HASH_TAG NUMBER, MID_GRAPH_PERCENT NUMBER, MID_VERSUS_SCORE NUMBER, BOT_NAME VARCHAR2(4000 BYTE), BOT_NUMBER_OF_ADVANTAGES NUMBER, BOT_LIKE_COUNT NUMBER, BOT_TOTAL_REGISTERED_POSTS NUMBER, BOT_REGISTERED_HASH_TAG NUMBER, BOT_GRAPH_PERCENT NUMBER, BOT_VERSUS_SCORE NUMBER, TOTAL_TRAFFIC NUMBER, UNIQUE_VISIT NUMBER, REVISIT_COUNT NUMBER, PAGE_VIEW NUMBER, TOTAL_ARTICLE NUMBER, TOTAL_LIKE_COUNT NUMBER, TOTAL_SPEC_COUNT NUMBER, TOTAL_HASH_COUNT NUMBER, STANDARD_ERROR NUMBER(31,7), EQUILIBRIUM_ASSUMPTION NUMBER(31,7), PROBABILITY NUMBER(31,7), LOWER_LIMIT NUMBER(31,7), HIGHER_LIMIT NUMBER(31,7), DISTRIBUTION_T_RESULT NUMBER(31,7), CONSTRAINT T_JSTREE_AGGREGATE_RESULT_PK PRIMARY KEY (C_ID) /* * CONSTRAINT T_JSTREE_AGGREGATE_RESULT_FK1 FOREIGN KEY (OTHER_ID) REFERENCES OTHER T_JSTREE_AGGREGATE_RESULT(C_ID) ON DELETE CASCADE */ ); COMMENT ON TABLE T_JSTREE_AGGREGATE_RESULT IS '기본 트리 스키마'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_ID IS '노드 아이디'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_PARENTID IS '부모 노드 아이디'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_POSITION IS '노드 포지션'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_LEFT IS '노드 좌측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_RIGHT IS '노드 우측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_LEVEL IS '노드 DEPTH '; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_TITLE IS '노드 명'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.C_TYPE IS '노드 타입'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.CAL_TIME IS '누적 집계 시간'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_NAME IS '우위 이름'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_NUMBER_OF_ADVANTAGES IS '우위 스펙 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_LIKE_COUNT IS '우위 좋아요 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_TOTAL_REGISTERED_POSTS IS '우위 등록 글 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_REGISTERED_HASH_TAG IS '우위 등록 해시태그 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_GRAPH_PERCENT IS '상위 그래프 퍼센트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOP_VERSUS_SCORE IS '상위 비교 스코어'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_NAME IS '중위 이름'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_NUMBER_OF_ADVANTAGES IS '중위 스펙 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_LIKE_COUNT IS '중위 좋아요 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_TOTAL_REGISTERED_POSTS IS '중위 등록 글 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_REGISTERED_HASH_TAG IS '중위 등록 해시태그 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_GRAPH_PERCENT IS '중위 그래프 퍼센트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.MID_VERSUS_SCORE IS '중위 비교 스코어'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_NAME IS '하위 이름'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_NUMBER_OF_ADVANTAGES IS '하위 스펙 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_LIKE_COUNT IS '하위 좋아요 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_TOTAL_REGISTERED_POSTS IS '하위 등록 글 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_REGISTERED_HASH_TAG IS '하위 등록 해시태그 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_GRAPH_PERCENT IS '하위 그래프 퍼센트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.BOT_VERSUS_SCORE IS '하위 비교 스코어'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOTAL_TRAFFIC IS '총 트래픽'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.UNIQUE_VISIT IS '유니크 방문 카운트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.REVISIT_COUNT IS '재 방문 카운트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.PAGE_VIEW IS '페이지 뷰 카운트'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOTAL_ARTICLE IS '총 등록 글 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOTAL_LIKE_COUNT IS '총 좋아요 등록 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOTAL_SPEC_COUNT IS '총 스펙 등록 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.TOTAL_HASH_COUNT IS '총 해시 태그 등록 개수'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.STANDARD_ERROR IS '표준 오차'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.EQUILIBRIUM_ASSUMPTION IS '등분상 가정 유의확률'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.PROBABILITY IS '유의 확률'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.LOWER_LIMIT IS '신뢰구간 하한'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.HIGHER_LIMIT IS '신뢰구간 상한'; COMMENT ON COLUMN T_JSTREE_AGGREGATE_RESULT.DISTRIBUTION_T_RESULT IS 'T분포결과'; /* * 인덱스는 되도록 걸지 말것. * CREATE UNIQUE INDEX I_COMPREHENSIVETREE ON T_JSTREE_AGGREGATE_RESULT * ("C_ID" ASC) * DROP SEQUENCE S_JSTREE_AGGREGATE_RESULT; */ CREATE SEQUENCE S_JSTREE_AGGREGATE_RESULT START WITH 10 MAXVALUE 999999999999999999999999999 MINVALUE 0 NOCYCLE CACHE 20 NOORDER; /* * JsTree 트리거 */ CREATE OR REPLACE TRIGGER "TG_JSTREE_AGGREGATE_RESULT" BEFORE DELETE OR INSERT OR UPDATE ON T_JSTREE_AGGREGATE_RESULT REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE tmpVar NUMBER; /****************************************************************************** NAME: TRIGGER_COMPREHENSIVETREE PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2012-08-29 1. Created this trigger. NOTES: Automatically available Auto Replace Keywords: Object Name: TRIGGER_COMPREHENSIVETREE Sysdate: 2012-08-29 Date and Time: 2012-08-29, 오후 5:26:44, and 2012-08-29 오후 5:26:44 Username: (set in TOAD Options, Proc Templates) Table Name: T_JSTREE_AGGREGATE_RESULT (set in the "New PL/SQL Object" dialog) Trigger Options: (set in the "New PL/SQL Object" dialog) ******************************************************************************/ BEGIN tmpVar := 0; IF UPDATING THEN insert into T_JSTREE_AGGREGATE_RESULT_LOG values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'update','변경이전데이터',sysdate); insert into T_JSTREE_AGGREGATE_RESULT_LOG values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'update','변경이후데이터',sysdate); END IF; IF DELETING THEN insert into T_JSTREE_AGGREGATE_RESULT_LOG values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'delete','삭제된데이터',sysdate); END IF; IF INSERTING THEN insert into T_JSTREE_AGGREGATE_RESULT_LOG values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'insert','삽입된데이터',sysdate); END IF; EXCEPTION WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END TG_JSTREE_AGGREGATE_RESULT; /** PK - FK **/ ALTER TABLE T_JSTREE_MENU ADD( CONSTRAINT T_JSTREE_MENU_FK_AR FOREIGN KEY (AGGREGATE_RESULT_ID) REFERENCES T_JSTREE_AGGREGATE_RESULT (C_ID)); /