easyjforum_hsqldb.sql

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

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

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

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

DROP TABLE IF EXISTS ejf_archive_reply;
DROP TABLE IF EXISTS ejf_archive_topic;

SET IGNORECASE TRUE;

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

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

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

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

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

CREATE TABLE ejf_section(
    sectionID       INT             GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
    sectionName     VARCHAR(20)     NOT NULL,
    seqno           INT             DEFAULT 1,
    cols            INT             DEFAULT 1,
    moderator       VARCHAR(60)     ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATETIME,
    updateTime      TIMESTAMP,
    PRIMARY KEY(sectionID));

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

CREATE TABLE ejf_board(
    boardID         INT             GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
    sectionID       INT             NOT NULL,
    boardName       VARCHAR(20)     NOT NULL,
    highColor     	VARCHAR(6)      ,
    seqno           INT             DEFAULT 1,
    brief           VARCHAR(100)    ,
    keywords        VARCHAR(100)    ,
    moderator       VARCHAR(60)     ,
    viewStyle       VARCHAR(20)     ,
    sortField       VARCHAR(20)     ,
    isImageOK       CHAR(1)         DEFAULT 'T',
    isMediaOK       CHAR(1)         DEFAULT 'F',
    isGuestPostOK   CHAR(1)         DEFAULT 'F',
    allowGroups     VARCHAR(20)     ,
    acl			    VARCHAR(100)    ,
	ruleCode      	LONGVARCHAR		,
    headAdCode      LONGVARCHAR     ,
    footAdCode      LONGVARCHAR     ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATETIME,
    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 CACHED TABLE ejf_topic(
    topicID         INT             GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
    boardID         INT             NOT NULL,
    sectionID       INT             DEFAULT 0,
    userID          VARCHAR(15)     NOT NULL,
    nickname        VARCHAR(15)     ,
    remoteIP        VARCHAR(25)     ,
    title           VARCHAR(100)    NOT NULL,
    content         LONGVARCHAR     ,
    reward          SMALLINT        DEFAULT 0,
    visits          INT             DEFAULT 0,
    replies         INT             DEFAULT 0,
    attaches	    TINYINT         DEFAULT 0,
    attachIcon      VARCHAR(5)      ,
    lastPostUser    VARCHAR(15)     NOT NULL,
    lastNickname    VARCHAR(15)     ,
    lastPostTime    DATETIME        ,
    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   DATETIME        ,
    highColor     	VARCHAR(6)      ,
    highExpireDate 	DATETIME        ,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATETIME 		,		
    updateTime      TIMESTAMP		,		
    updateUser      VARCHAR(15)     ,
    PRIMARY KEY(topicID),
    FOREIGN KEY(boardID)
        REFERENCES ejf_board(boardID));

SELECT * INTO CACHED ejf_archive_topic FROM ejf_topic;

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

CREATE CACHED TABLE ejf_reply(
    replyID         INT             GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
    topicID         INT             NOT NULL,
    userID          VARCHAR(15)     NOT NULL,
    remoteIP        VARCHAR(25)     ,
    title           VARCHAR(100)    ,
    content         LONGVARCHAR     NOT NULL,
    attaches	    TINYINT         DEFAULT 0,
    isHidePost      CHAR(1)         DEFAULT 'F',
    isBest		    CHAR(1)         DEFAULT 'F',
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATETIME,
    updateTime      TIMESTAMP,
    PRIMARY KEY(replyID),
    FOREIGN KEY(topicID)
        REFERENCES ejf_topic(topicID) ON DELETE CASCADE);

SELECT * INTO CACHED ejf_archive_reply FROM ejf_reply;

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

CREATE CACHED TABLE ejf_attach(
    attachID        INT             GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
    topicID         INT             NOT NULL,
    replyID         INT             DEFAULT 0,
    userID          VARCHAR(15)     NOT NULL,
    localname	    VARCHAR(50)    	NOT NULL,
    localID        	SMALLINT    	DEFAULT 0,
    filename        VARCHAR(50)    	NOT NULL,
    filesize        INT             DEFAULT 0,
    credits         INT             DEFAULT 0,
    title        	VARCHAR(50)     ,
    downloads       INT             DEFAULT 0,
    state           CHAR(1)         DEFAULT 'N',
    createTime      DATETIME,
    updateTime      TIMESTAMP,
    PRIMARY KEY(attachID),
    FOREIGN KEY(topicID)
        REFERENCES ejf_topic(topicID) ON DELETE CASCADE);

-- ************************************************
-- * ejf_trash_box table
-- ************************************************

CREATE CACHED TABLE ejf_trash_box(
    topicID         INT             NOT NULL,