--------------------------------------------------------
--  File created - Tuesday-November-25-2008   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence WIKI_COMMENT_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_COMMENT_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence WIKI_PAGE_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_PAGE_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence WIKI_SEARCH_RESULTS_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_SEARCH_RESULTS_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence WIKI_SEARCH_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_SEARCH_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence WIKI_SECTION_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_SECTION_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence WIKI_TEXT_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "WIKI_TEXT_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Table WIKI_COMMENT
--------------------------------------------------------

  CREATE TABLE "WIKI_COMMENT" 
   (	"COMMENT_ID" NUMBER(10,0), 
	"COMMENT_TYPE_CODE" VARCHAR2(10) DEFAULT 'TOPIC', 
	"PAGE_ID" NUMBER(10,0), 
	"COMMENT_TITLE" VARCHAR2(100), 
	"COMMENT_TEXT" VARCHAR2(500), 
	"REPLY_TO_COMMENT_ID" NUMBER(10,0), 
	"CREATED_BY" VARCHAR2(10), 
	"CREATED_ON" DATE DEFAULT SYSDATE, 
	"ACTIVE_FLAG" VARCHAR2(1) DEFAULT 'Y'
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_COMMENT_TYPE
--------------------------------------------------------

  CREATE TABLE "WIKI_COMMENT_TYPE" 
   (	"COMMENT_TYPE_CODE" VARCHAR2(10), 
	"COMMENT_TYPE_NAME" VARCHAR2(20), 
	"ACTIVE_FLAG" VARCHAR2(1) DEFAULT 'Y'
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_PAGE
--------------------------------------------------------

  CREATE TABLE "WIKI_PAGE" 
   (	"PAGE_ID" NUMBER(10,0), 
	"PAGE_NAME" VARCHAR2(100), 
	"ACTIVE_FLAG" VARCHAR2(1) DEFAULT 'Y', 
	"PAGE_OWNER" VARCHAR2(10), 
	"MODERATION" VARCHAR2(1) DEFAULT 'N'
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_SEARCH
--------------------------------------------------------

  CREATE TABLE "WIKI_SEARCH" 
   (	"SEARCH_ID" NUMBER(10,0), 
	"SEARCH_FOR" VARCHAR2(100), 
	"CREATED_BY" VARCHAR2(10), 
	"CREATED_ON" DATE DEFAULT SYSDATE
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_SEARCH_RESULTS
--------------------------------------------------------

  CREATE TABLE "WIKI_SEARCH_RESULTS" 
   (	"SEARCH_RESULT_ID" NUMBER(10,0), 
	"SEARCH_ID" NUMBER(10,0), 
	"PAGE_ID" NUMBER(10,0), 
	"SCORE" NUMBER(4,0) DEFAULT 10
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_SECTION
--------------------------------------------------------

  CREATE TABLE "WIKI_SECTION" 
   (	"SECTION_ID" NUMBER(10,0), 
	"PAGE_ID" NUMBER(10,0), 
	"SECTION_NAME" VARCHAR2(100) DEFAULT 'new section', 
	"POSITION" NUMBER(4,0), 
	"ACTIVE_FLAG" VARCHAR2(1) DEFAULT 'Y'
   ) ;
--------------------------------------------------------
--  DDL for Table WIKI_TEXT
--------------------------------------------------------

  CREATE TABLE "WIKI_TEXT" 
   (	"TEXT_ID" NUMBER(10,0), 
	"TEXT" VARCHAR2(3000), 
	"UPDATED_BY" VARCHAR2(10), 
	"UPDATED_ON" DATE DEFAULT SYSDATE, 
	"APPROVED_BY" VARCHAR2(10), 
	"APPROVED_ON" DATE, 
	"ACTIVE_FLAG" VARCHAR2(1) DEFAULT 'Y', 
	"SECTION_ID" NUMBER(10,0)
   ) ;
--------------------------------------------------------
--  Constraints for Table WIKI_COMMENT
--------------------------------------------------------

  ALTER TABLE "WIKI_COMMENT" ADD CONSTRAINT "WIKI_COMMENT_PK" PRIMARY KEY ("COMMENT_ID")
  USING INDEX ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_COMMENT_TYPE
--------------------------------------------------------

  ALTER TABLE "WIKI_COMMENT_TYPE" ADD CONSTRAINT "WIKI_COMMENT_TYPE_PK" PRIMARY KEY ("COMMENT_TYPE_CODE")
  USING INDEX  ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_PAGE
--------------------------------------------------------

  ALTER TABLE "WIKI_PAGE" ADD CONSTRAINT "WIKI_PAGE_NAME_UK" UNIQUE ("PAGE_NAME")
  USING INDEX ENABLE;
 
  ALTER TABLE "WIKI_PAGE" ADD CONSTRAINT "WIKI_PAGE_PK" PRIMARY KEY ("PAGE_ID")
  USING INDEX ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_SEARCH
--------------------------------------------------------

  ALTER TABLE "WIKI_SEARCH" ADD CONSTRAINT "WIKI_SEARCH_PK" PRIMARY KEY ("SEARCH_ID")
  USING INDEX ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_SEARCH_RESULTS
--------------------------------------------------------

  ALTER TABLE "WIKI_SEARCH_RESULTS" ADD CONSTRAINT "WIKI_SEARCH_RESULTS_PK" PRIMARY KEY ("SEARCH_RESULT_ID")
  USING INDEX ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_SECTION
--------------------------------------------------------

  ALTER TABLE "WIKI_SECTION" ADD CONSTRAINT "WIKI_SECTION_PK" PRIMARY KEY ("SECTION_ID")
  USING INDEX ENABLE;
--------------------------------------------------------
--  Constraints for Table WIKI_TEXT
--------------------------------------------------------

  ALTER TABLE "WIKI_TEXT" ADD CONSTRAINT "WIKI_TEXT_PK" PRIMARY KEY ("TEXT_ID")
  USING INDEX ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table WIKI_COMMENT
--------------------------------------------------------

  ALTER TABLE "WIKI_COMMENT" ADD CONSTRAINT "WIKI_COMMENT_PAGE_FK" FOREIGN KEY ("PAGE_ID")
	  REFERENCES "WIKI_PAGE" ("PAGE_ID") ENABLE;
 
  ALTER TABLE "WIKI_COMMENT" ADD CONSTRAINT "WIKI_COMMENT_COMMENT_TYPE_FK" FOREIGN KEY ("COMMENT_TYPE_CODE")
	  REFERENCES "WIKI_COMMENT_TYPE" ("COMMENT_TYPE_CODE") ENABLE;
 
  ALTER TABLE "WIKI_COMMENT" ADD CONSTRAINT "WIKI_COMMENT_PARENT_COMMENT_FK" FOREIGN KEY ("COMMENT_ID")
	  REFERENCES "WIKI_COMMENT" ("COMMENT_ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table WIKI_SEARCH_RESULTS
--------------------------------------------------------

  ALTER TABLE "WIKI_SEARCH_RESULTS" ADD CONSTRAINT "WIKI_SEARCH_RESULTS_WIKI__FK1" FOREIGN KEY ("SEARCH_ID")
	  REFERENCES "WIKI_SEARCH" ("SEARCH_ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table WIKI_SECTION
--------------------------------------------------------

  ALTER TABLE "WIKI_SECTION" ADD CONSTRAINT "WIKI_SECTION_WIKI_PAGE_FK1" FOREIGN KEY ("PAGE_ID")
	  REFERENCES "WIKI_PAGE" ("PAGE_ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table WIKI_TEXT
--------------------------------------------------------

  ALTER TABLE "WIKI_TEXT" ADD CONSTRAINT "WIKI_TEXT_SECTION_FK" FOREIGN KEY ("SECTION_ID")
	  REFERENCES "WIKI_SECTION" ("SECTION_ID") ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_COMMENT_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_COMMENT_TRG" 

before insert on wiki_comment
for each row 
BEGIN
  SELECT WIKI_COMMENT_SEQ.NEXTVAL INTO :NEW.COMMENT_ID FROM DUAL;
END;

/
ALTER TRIGGER "WIKI_COMMENT_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_PAGE_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_PAGE_TRG" 

before insert on wiki_page
for each row 
BEGIN
  if :NEW.PAGE_ID is null then
    SELECT WIKI_PAGE_SEQ.NEXTVAL INTO :NEW.PAGE_ID FROM DUAL;
  end if;
END;

/
ALTER TRIGGER "WIKI_PAGE_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_SEARCH_RESULTS_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_SEARCH_RESULTS_TRG" 

before insert on wiki_search_results
for each row 
BEGIN
  SELECT WIKI_SEARCH_RESULTS_SEQ.NEXTVAL INTO :NEW.SEARCH_RESULT_ID FROM DUAL;
END;

/
ALTER TRIGGER "WIKI_SEARCH_RESULTS_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_SEARCH_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_SEARCH_TRG" 

before insert on wiki_search
for each row 
BEGIN
  SELECT WIKI_SEARCH_SEQ.NEXTVAL INTO :NEW.SEARCH_ID FROM DUAL;
END;

/
ALTER TRIGGER "WIKI_SEARCH_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_SECTION_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_SECTION_TRG" 

before insert on wiki_section
for each row 
BEGIN
  if :NEW.SECTION_ID is null then 
    SELECT WIKI_SECTION_SEQ.NEXTVAL INTO :NEW.SECTION_ID FROM DUAL;
  end if;
  if :NEW.POSITION is null then
    declare
      v_count number;
    begin
      select count(*) into v_count from wiki_section where page_id = :NEW.PAGE_ID;
      if v_count > 0 then
        select max(position) + 1 into v_count from wiki_section where page_id = :NEW.PAGE_ID;
      else
        v_count := 1 ;
      end if;
      
      :NEW.POSITION := v_count;
    end;
  end if;
END;

/
ALTER TRIGGER "WIKI_SECTION_TRG" ENABLE;
--------------------------------------------------------
--  DDL for Trigger WIKI_TEXT_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "WIKI_TEXT_TRG" 

before insert on wiki_text
for each row 
BEGIN
  SELECT WIKI_TEXT_SEQ.NEXTVAL INTO :NEW.TEXT_ID FROM DUAL;
END;

/
ALTER TRIGGER "WIKI_TEXT_TRG" ENABLE;
--------------------------------------------------------
--  DDL for View WIKI_USERS_VIEW
--------------------------------------------------------

CREATE OR REPLACE VIEW "WIKI_USERS_VIEW" ("USER_ID", "USER_NAME") AS 
  SELECT 
    'guest' user_id, 'guest' user_name
FROM
   dual;

insert into wiki_page (page_name) values ('Welcome');

insert into wiki_comment_type (comment_type_code, comment_type_name) 
  values ('TOPIC', 'Topic');
insert into wiki_comment_type (comment_type_code, comment_type_name) 
  values ('COMMENT', 'Comment');
insert into wiki_comment_type (comment_type_code, comment_type_name) 
  values ('SUPPORT', 'Support');
insert into wiki_comment_type (comment_type_code, comment_type_name) 
  values ('OPPOSE', 'Oppose');

commit;