AI/docs/schema.md

AI日记

数据范式设计

您是一名专业的数据库工程师，然悉PostgreSQL和ParseServer请注意表名用大驼峰。字段小驼峰。有预留字段:objectId、updatedAt、createdAt 关于ParseServer中数据类的描述，字段的主要类型有： String => String Number => Number Bool => bool Array => JSON Array Object => JSON Object Date => Date File => Parse.File Pointer => other Parse.Object //指针类型的字段不需要用×××Id形式命名，直接写×××即可， Relation => Parse.Relation Null => null GeoPoint =>{latitude: 40.0, longitude: -30.0}

项目需求 AI日记的辅助AI应用，用户(User)、日记、聊天消息、动态、互动（点赞、评论、分享等 ），请您根据行业经验，设计以上五张表、用户直接用预留的_User表即可 输出结果（UML类图） 请您帮我用plantuml的类图描述设计好的几张表及其关系

输出结果（信息结构图） 请您帮我用markmap式表示上面的信息结构图

输出结果（SQL语句） 请您帮我用sql格式给我建表语句和测试数据插入语句，注意字段请使用小驼峰用""引起来。

UML类图

@startuml
' 设置主题
skinparam class {
    BackgroundColor White
    ArrowColor #0078D7
    BorderColor #0078D7
}

' 用户表（使用Parse预留表）
class _User {
    + objectId: String [PK]
    + username: String
    + email: String
    + authData: Object
    + emailVerified: bool
    + createdAt: Date
    + updatedAt: Date
    + profilePicture: File
    + lastActiveAt: Date
}

' 动态表
class Dynamic {
    + objectId: String [PK]
    + content: String
    + images: Array<File>
    + location: GeoPoint
    + mood: String
    + tags: Array<String>
    + isPublic: bool
    + likeCount: Number
    + commentCount: Number
    + shareCount: Number
    + createdAt: Date
    + updatedAt: Date
    + author: Pointer<_User>
}

' 互动表（包含点赞、评论、分享）
class Interaction {
    + objectId: String [PK]
    + type: String // 'like'|'comment'|'share'
    + content: String // 评论内容
    + createdAt: Date
    + updatedAt: Date
    + user: Pointer<_User>
    + diary: Pointer<Dynamic>
    + targetUser: Pointer<_User> // 互动目标用户（分享给谁/回复谁）
}

' 关系定义
_User "1" --> "*" Dynamic : 发布
_User "1" --> "*" Interaction : 发起
Dynamic "1" --> "*" Interaction : 拥有
_User "1" --> "*" Interaction : 接收互动(targetUser)
@enduml

SQL语句

建表语句

-- 用户表（基于ParseServer的_User表结构扩展）
CREATE TABLE "_User" (
    "objectId" VARCHAR(36) PRIMARY KEY,
    "username" VARCHAR(50) UNIQUE NOT NULL,
    "email" VARCHAR(255) UNIQUE,
    "authData" JSONB,
    "emailVerified" BOOLEAN DEFAULT false,
    "profilePicture" TEXT, -- Parse.File存储的URL
    "lastActiveAt" TIMESTAMP WITH TIME ZONE,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 动态表
CREATE TABLE "Dynamic" (
    "objectId" VARCHAR(36) PRIMARY KEY,
    "content" TEXT NOT NULL,
    "images" JSONB, -- 存储File URL数组
    "location" JSONB, -- {latitude: xx, longitude: yy}
    "mood" VARCHAR(20),
    "tags" JSONB, -- 字符串数组
    "isPublic" BOOLEAN DEFAULT true,
    "likeCount" INTEGER DEFAULT 0,
    "commentCount" INTEGER DEFAULT 0,
    "shareCount" INTEGER DEFAULT 0,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "author" VARCHAR(36) NOT NULL REFERENCES "_User"("objectId")
);

-- 互动表
CREATE TABLE "Interaction" (
    "objectId" VARCHAR(36) PRIMARY KEY,
    "type" VARCHAR(10) CHECK ("type" IN ('like', 'comment', 'share')),
    "content" TEXT,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" VARCHAR(36) NOT NULL REFERENCES "_User"("objectId"),
    "diary" VARCHAR(36) NOT NULL REFERENCES "Dynamic"("objectId"),
    "targetUser" VARCHAR(36) REFERENCES "_User"("objectId")
);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updatedAt" = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为每张表添加更新时间触发器
CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON "_User"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_diary_timestamp
BEFORE UPDATE ON "Diary"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_interaction_timestamp
BEFORE UPDATE ON "Interaction"
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- 测试数据插入
INSERT INTO "_User" ("objectId", "username", "email", "emailVerified", "profilePicture", "lastActiveAt")
VALUES 
    ('usr001', 'testUser1', 'user1@example.com', true, 'https://example.com/pic1.jpg', NOW()),
    ('usr002', 'testUser2', 'user2@example.com', true, 'https://example.com/pic2.jpg', NOW());

INSERT INTO "Dynamic" ("objectId", "content", "images", "location", "mood", "tags", "isPublic", "author")
VALUES
    ('d001', '今天天气真好！', '["https://example.com/img1.jpg"]', '{"latitude": 39.9042, "longitude": 116.4074}', 'happy', '["天气","户外"]', true, 'usr001'),
    ('d002', '学习PostgreSQL很有趣', '[]', NULL, 'excited', '["学习","数据库"]', true, 'usr002');

INSERT INTO "Interaction" ("objectId", "type", "content", "user", "diary", "targetUser")
VALUES
    ('i001', 'like', NULL, 'usr002', 'd001', NULL),
    ('i002', 'comment', '我也觉得天气不错！', 'usr002', 'd001', NULL),
    ('i003', 'share', NULL, 'usr001', 'd002', 'usr002');