easyjforum_oracle.sql

论坛系统EasyJForum 是一个基于 Java 技术的免费社区论坛软件系统

DROP SEQUENCE ejf_section_seq;
DROP SEQUENCE ejf_board_seq;
DROP SEQUENCE ejf_topic_seq;
DROP SEQUENCE ejf_reply_seq;
DROP SEQUENCE ejf_attach_seq;

DROP SEQUENCE ejf_short_msg_seq;
DROP SEQUENCE ejf_bookmark_seq;
DROP SEQUENCE ejf_backup_task_seq;

DROP SEQUENCE ejf_moderator_log_seq;
DROP SEQUENCE ejf_report_log_seq;
DROP SEQUENCE ejf_censor_log_seq;
DROP SEQUENCE ejf_credits_log_seq;
DROP SEQUENCE ejf_admin_log_seq;
DROP SEQUENCE ejf_error_log_seq;

CREATE SEQUENCE ejf_section_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_board_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_topic_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_reply_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_attach_seq INCREMENT BY 1 START WITH 1;

CREATE SEQUENCE ejf_short_msg_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_bookmark_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_backup_task_seq INCREMENT BY 1 START WITH 1;

CREATE SEQUENCE ejf_moderator_log_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_report_log_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_censor_log_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_credits_log_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_admin_log_seq INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ejf_error_log_seq INCREMENT BY 1 START WITH 1;

DROP TABLE ejf_backup_task;
DROP TABLE ejf_error_log;
DROP TABLE ejf_admin_log;
DROP TABLE ejf_credits_log;
DROP TABLE ejf_censor_log;
DROP TABLE ejf_report_log;
DROP TABLE ejf_moderator_log;

DROP TABLE ejf_visit_stat;
DROP TABLE ejf_friend;
DROP TABLE ejf_bookmark;
DROP TABLE ejf_short_msg;
DROP TABLE ejf_trash_box;

DROP TABLE ejf_attach;
DROP TABLE ejf_reply;
DROP TABLE ejf_topic;
DROP TABLE ejf_board;
DROP TABLE ejf_section;
DROP TABLE ejf_user;
DROP TABLE ejf_group;

DROP TABLE ejf_archive_reply;
DROP TABLE ejf_archive_topic;

-- **********************************************************
-- * ejf_group table
-- * GroupType: M - Member Group, S - System
-- * GroupID: 1-9 - Normal User, M - Moderator, 
-- *            S - Super Moderator, A - Admin, G - Guest
-- **********************************************************

CREATE TABLE ejf_group(
    groupID         CHAR(1)         NOT NULL,
    groupName       VARCHAR2(15)    NOT NULL,
    groupType       CHAR(1)         DEFAULT 'M',
    minCredits      NUMBER(10)      DEFAULT 0,
    stars           NUMBER(10)      DEFAULT 1,
    rights          VARCHAR2(50)    NOT NULL,
    createTime      DATE,
    updateTime      TIMESTAMP,
    PRIMARY KEY(groupID));

-- **********************************************************
-- * ejf_user table
-- * State: N - Normal, P - Seal Posting, S - Sealed,
-- *        A - Auditing
-- * GroupID: Admin group ID
-- **********************************************************

CREATE TABLE ejf_user(
    userID          VARCHAR2(15)    NOT NULL,
    nickname        VARCHAR2(15)    ,
    pwd             VARCHAR2(32)    NOT NULL,
    email           VARCHAR2(40)    NOT NULL,
    icq             VARCHAR2(40)    ,
    webpage         VARCHAR2(60)    ,
    avatar          VARCHAR2(50)    ,
    gender          CHAR(1)         DEFAULT 'U',
    birth           VARCHAR2(10)    ,
    city            VARCHAR2(20)    ,
    remoteIP        VARCHAR2(25)    ,
    brief           VARCHAR2(200)   ,
    isMailPub       CHAR(1)         DEFAULT 'F',
    posts           NUMBER(10)      DEFAULT 0,
    unreadSMs       NUMBER(10)      DEFAULT 0,
    credits         NUMBER(10)      DEFAULT 0,
    groupID         CHAR(1)         DEFAULT '1',
    lastVisited     DATE,
    visitCount		NUMBER(10)		DEFAULT 1,
    loginCount		NUMBER(2)		DEFAULT 0,
    loginExpire	    DATE, 
    setpwdExpire	DATE, 
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATE,
    updateTime      TIMESTAMP,
    PRIMARY KEY(userID),
    UNIQUE(email));

-- ************************************************
-- * ejf_section table
-- * State: N - Normal
-- ************************************************

CREATE TABLE ejf_section(
    sectionID       NUMBER(10)      NOT NULL,
    sectionName     VARCHAR2(20)    NOT NULL,
    seqno           NUMBER(10)      DEFAULT 1,
    cols            NUMBER(10)      DEFAULT 1,
    moderator       VARCHAR2(60)    ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATE,
    updateTime      TIMESTAMP,
    PRIMARY KEY(sectionID));

-- ************************************************
-- * ejf_board table
-- * State: N - Normal, I - Invisible
-- ************************************************

CREATE TABLE ejf_board(
    boardID         NUMBER(10)      NOT NULL,
    sectionID       NUMBER(10)      NOT NULL,
    boardName       VARCHAR2(20)    NOT NULL,
    highColor     	VARCHAR2(6)     ,
    seqno           NUMBER(10)      DEFAULT 1,
    brief           VARCHAR2(100)   ,
    keywords        VARCHAR2(100)   ,
    moderator       VARCHAR2(60)    ,
    viewStyle       VARCHAR2(20)    ,
    sortField       VARCHAR2(20)    ,
    isImageOK       CHAR(1)         DEFAULT 'T',
    isMediaOK       CHAR(1)         DEFAULT 'F',
    isGuestPostOK   CHAR(1)         DEFAULT 'F',
    allowGroups     VARCHAR2(20)    ,
    acl			    VARCHAR2(100)   ,
	ruleCode      	CLOB			,
    headAdCode      CLOB		    ,
    footAdCode      CLOB		    ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATE,
    updateTime      TIMESTAMP,
    PRIMARY KEY(boardID),
    FOREIGN KEY(sectionID)
        REFERENCES ejf_section(sectionID));

-- *********************************************************
-- * ejf_topic table
-- * State: N - Normal, C - Closed, R - Recycled
-- * TopScope: 1 - Global, 2 - Section, 3 - Board, N - None
-- * AttachIcon: I - Image, F - Flash, A - Attach
-- *********************************************************

CREATE TABLE ejf_topic(
    topicID         NUMBER(10)      NOT NULL,
    boardID         NUMBER(10)      NOT NULL,
    sectionID       NUMBER(10)      DEFAULT 0,
    userID          VARCHAR2(15)    NOT NULL,
    nickname        VARCHAR2(15)    ,
    remoteIP        VARCHAR2(25)    ,
    title           VARCHAR2(100)   NOT NULL,
    content         CLOB		    ,
    reward          NUMBER(4)       DEFAULT 0,
    visits          NUMBER(10)      DEFAULT 0,
    replies         NUMBER(10)      DEFAULT 0,
    attaches	    NUMBER(2)       DEFAULT 0,
    attachIcon      VARCHAR2(5)     ,
    lastPostUser    VARCHAR2(15)    NOT NULL,
    lastNickname    VARCHAR2(15)    ,
    lastPostTime    DATE	        ,
    isDigest        CHAR(1)         DEFAULT 'F',
    isReplyNotice   CHAR(1)         DEFAULT 'F',
    isHidePost      CHAR(1)         DEFAULT 'F',
    isSolved	    CHAR(1)         DEFAULT 'F',
    topScope        CHAR(1)         DEFAULT 'N',
    topExpireDate   DATE	        ,
    highColor     	VARCHAR2(6)     ,
    highExpireDate 	DATE    	    ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATE	 		,
    updateTime      TIMESTAMP 		,
    updateUser      VARCHAR2(15)    ,
    PRIMARY KEY(topicID),
    FOREIGN KEY(boardID)
        REFERENCES ejf_board(boardID));

CREATE TABLE ejf_archive_topic AS SELECT * FROM ejf_topic;

-- ************************************************
-- * ejf_reply table
-- * State: N - Normal, R - Recycled
-- ************************************************

CREATE TABLE ejf_reply(
    replyID         NUMBER(10)      NOT NULL,
    topicID         NUMBER(10)      NOT NULL,
    userID          VARCHAR2(15)    NOT NULL,
    remoteIP        VARCHAR2(25)    ,
    title           VARCHAR2(100)   ,
    content         CLOB	        NOT NULL,
    attaches	    NUMBER(2)       DEFAULT 0,
    isHidePost      CHAR(1)         DEFAULT 'F',
    isBest		    CHAR(1)         DEFAULT 'F',
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATE,
    updateTime      TIMESTAMP,
    PRIMARY KEY(replyID),
    FOREIGN KEY(topicID)
        REFERENCES ejf_topic(topicID) ON DELETE CASCADE);

CREATE TABLE ejf_archive_reply AS SELECT * FROM ejf_reply;

-- *******************************************************
-- * ejf_attach table
-- * State: N - Normal, I - Image, F - Flash, R - Recycled
-- *******************************************************

CREATE TABLE ejf_attach(
    attachID        NUMBER(10)      NOT NULL,
    topicID         NUMBER(10)      NOT NULL,
    replyID         NUMBER(10)      DEFAULT 0,
    userID          VARCHAR2(15)    NOT NULL,
    localname	    VARCHAR2(50)    NOT NULL,
    localID        	NUMBER(4)    	DEFAULT 0,