QuickDBD-export.sql

-- Exported from QuickDBD: https://www.quickdatabasediagrams.com/
-- NOTE! If you have used non-SQL datatypes in your design, you will have to change these here.


-- WX User
CREATE TABLE "User" (
    "UserID"  SERIAL  NOT NULL,
    -- User Name
    -- WX 昵称（不是群昵称）
    "Name" string   NOT NULL,
    CONSTRAINT "pk_User" PRIMARY KEY (
        "UserID"
     )
);

-- WX 群
CREATE TABLE "Group" (
    "GroupID"  SERIAL  NOT NULL,
    -- Group Name
    "Name" string   NOT NULL,
    -- Group Comment
    "Comment" string   NOT NULL,
    -- Day range for reporting of being active
    "ActivePeriod" int  DEFAULT 15 NOT NULL,
    -- Day range for reporting of being sleep
    "QuietPeriod" int  DEFAULT 30 NOT NULL,
    -- Number of active users to report
    "ActiveNum" int  DEFAULT 10 NOT NULL,
    -- Number of quiet users to report
    "QuietNum" int  DEFAULT 10 NOT NULL,
    CONSTRAINT "pk_Group" PRIMARY KEY (
        "GroupID"
     )
);

CREATE TABLE "ActivityType" (
    "ActivityTypeID"  SERIAL  NOT NULL,
    "Name" string   NOT NULL,
    -- 贡献	发言总字数
    -- 爱心	发言后又撤回重新修改的总字数
    -- 分享	除纯文字以外，任何其他的分享
    -- 热闹	贴表情包图
    -- .
    -- .
    -- .
    -- .
    -- .
    -- .
    -- .
    "Comment" string   NOT NULL,
    CONSTRAINT "pk_ActivityType" PRIMARY KEY (
        "ActivityTypeID"
     )
);

CREATE TABLE "ActivityLog" (
    "ActivityLogID"  SERIAL  NOT NULL,
    "UserID" int   NOT NULL,
    "GroupID" int   NOT NULL,
    "ActivityTypeID" int   NOT NULL,
    "ActivityDate" dateTime   NOT NULL,
    "Stat" int   NOT NULL,
    CONSTRAINT "pk_ActivityLog" PRIMARY KEY (
        "ActivityLogID"
     )
);

-- User Statistic (View structure)
CREATE TABLE "UserStat" (
    "UserID" int   NOT NULL,
    "GroupID" int   NOT NULL,
    -- 贡献指数	发言总字数累计
    "WordCount" int   NOT NULL,
    -- 爱心指数	发言后又撤回重新修改的总字数累计
    "CorrectionCount" int   NOT NULL,
    -- 分享指数	除纯文字以外，任何其他分享的次数累计
    "ShareCount" int   NOT NULL,
    -- 热闹指数	贴表情包图的次数累计（表情包不统计在分享指数之中）
    "MemeCount" int   NOT NULL
);

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_UserID" FOREIGN KEY("UserID")
REFERENCES "User" ("UserID");

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_GroupID" FOREIGN KEY("GroupID")
REFERENCES "Group" ("GroupID");

ALTER TABLE "ActivityLog" ADD CONSTRAINT "fk_ActivityLog_ActivityTypeID" FOREIGN KEY("ActivityTypeID")
REFERENCES "ActivityType" ("ActivityTypeID");

ALTER TABLE "UserStat" ADD CONSTRAINT "fk_UserStat_UserID" FOREIGN KEY("UserID")
REFERENCES "User" ("UserID");

ALTER TABLE "UserStat" ADD CONSTRAINT "fk_UserStat_GroupID" FOREIGN KEY("GroupID")
REFERENCES "Group" ("GroupID");

CREATE INDEX "idx_User_Name"
ON "User" ("Name");

CREATE INDEX "idx_Group_Name"
ON "Group" ("Name");

QuickDBD-export.svg