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 
class _User {
  .. 系统预留字段 ..
  + objectId: String
  + username: String
  + email: String
  + emailVerified: bool
  + authData: Object
  + password: String
  + createdAt: Date
  + updatedAt: Date
  .. 自定义字段 ..
  + nickname: String
  + avatar: File
  + bio: String
  + lastActiveAt: Date
  + privacySettings: Object
}

class Diary {
  + objectId: String
  + createdAt: Date
  + updatedAt: Date
  + title: String
  + content: String
  + author: Pointer<_User>
  + mood: String
  + tags: Array
  + isPublic: bool
  + location: GeoPoint
  + weather: Object
  + aiAnalysis: Object
}

class ChatMessage {
  + objectId: String
  + createdAt: Date
  + updatedAt: Date
  + sender: Pointer<_User>
  + receiver: Pointer<_User>
  + content: String
  + diaryRef: Pointer<Diary>
  + isRead: bool
  + messageType: String
  + attachments: Array<File>
}

class Dynamic {
  + objectId: String
  + createdAt: Date
  + updatedAt: Date
  + creator: Pointer<_User>
  + content: String
  + images: Array<File>
  + refDiary: Pointer<Diary>
  + location: GeoPoint
  + tags: Array
  + interactionCount: Number
}

class Interaction {
  + objectId: String
  + createdAt: Date
  + updatedAt: Date
  + type: String
  + fromUser: Pointer<_User>
  + toUser: Pointer<_User>
  + targetType: String
  + targetId: Pointer
  + content: String
  + status: String
}

class ThanksType {
  + objectId: String
  + createdAt: Date
  + updatedAt: Date
  + creator: Pointer<_User>
  + title: String
  + items: Array<{
    content: String
    category: String
    stars: Number
  }>
  + themeColor: String
  + isShared: bool
  + weeklyGoal: Number
}

' 核心关系定义
_User "1" *-- "many" Diary : 创作
_User "1" *-- "many" ChatMessage : 发送
_User "1" *-- "many" Dynamic : 发布
_User "1" *-- "many" Interaction : 发起
_User "1" *-- "many" ThanksType : 创建
Diary "1" *-- "many" Dynamic : 引用 
Dynamic "1" *-- "many" Interaction : 接收 
@enduml

SQL语句

建表语句

-- 用户表（ParseServer 内置 _User 表结构）
CREATE TABLE "_User" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "username" VARCHAR(255) UNIQUE NOT NULL,
  "email" VARCHAR(255) UNIQUE,
  "emailVerified" BOOLEAN DEFAULT false,
  "authData" JSONB,
  "password" VARCHAR(255),
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "nickname" VARCHAR(100),
  "avatar" VARCHAR(255),
  "bio" TEXT,
  "lastActiveAt" TIMESTAMPTZ,
  "privacySettings" JSONB
);

-- 日记表
CREATE TABLE "Diary" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "title" VARCHAR(200) NOT NULL,
  "content" TEXT NOT NULL,
  "author" VARCHAR(36) REFERENCES "_User"("objectId"),
  "mood" VARCHAR(50),
  "tags" JSONB DEFAULT '[]'::JSONB,
  "isPublic" BOOLEAN DEFAULT false,
  "location" JSONB,
  "weather" JSONB,
  "aiAnalysis" JSONB
);

-- 聊天消息表
CREATE TABLE "ChatMessage" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "sender" VARCHAR(36) REFERENCES "_User"("objectId"),
  "receiver" VARCHAR(36) REFERENCES "_User"("objectId"),
  "content" TEXT,
  "diaryRef" VARCHAR(36) REFERENCES "Diary"("objectId"),
  "isRead" BOOLEAN DEFAULT false,
  "messageType" VARCHAR(20) DEFAULT 'text',
  "attachments" JSONB DEFAULT '[]'::JSONB
);

-- 动态表
CREATE TABLE "Dynamic" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "creator" VARCHAR(36) REFERENCES "_User"("objectId"),
  "content" TEXT,
  "images" JSONB DEFAULT '[]'::JSONB,
  "refDiary" VARCHAR(36) REFERENCES "Diary"("objectId"),
  "location" JSONB,
  "tags" JSONB DEFAULT '[]'::JSONB,
  "interactionCount" INTEGER DEFAULT 0
);

-- 互动表
CREATE TABLE "Interaction" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "type" VARCHAR(20) NOT NULL,
  "fromUser" VARCHAR(36) REFERENCES "_User"("objectId"),
  "toUser" VARCHAR(36) REFERENCES "_User"("objectId"),
  "targetType" VARCHAR(20) NOT NULL,
  "targetId" VARCHAR(36),
  "content" TEXT,
  "status" VARCHAR(20) DEFAULT 'unread'
);

-- 感恩清单表（新增）
CREATE TABLE "ThanksType" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  "creator" VARCHAR(36) REFERENCES "_User"("objectId"),
  "title" VARCHAR(200) NOT NULL,
  "items" JSONB NOT NULL DEFAULT '[]'::JSONB,
  "themeColor" VARCHAR(20) DEFAULT '#FFD700',
  "isShared" BOOLEAN DEFAULT false,
  "weeklyGoal" INTEGER CHECK ("weeklyGoal" > 0)
);

-- 创建索引
CREATE INDEX "idx_diary_author" ON "Diary" ("author");
CREATE INDEX "idx_message_sender" ON "ChatMessage" ("sender");
CREATE INDEX "idx_message_receiver" ON "ChatMessage" ("receiver");
CREATE INDEX "idx_moment_creator" ON "Dynamic" ("creator");
CREATE INDEX "idx_interaction_target" ON "Interaction" ("targetType", "targetId");
CREATE INDEX "idx_thanks_creator" ON "ThanksType" ("creator");

-- 插入测试用户
INSERT INTO "_User" ("objectId", "username", "email", "nickname", "createdAt") VALUES
('usr001', 'alice_wonder', 'alice@example.com', 'Alice', NOW()),
('usr002', 'bob_builder', 'bob@example.com', 'Bob', NOW()),
('usr003', 'charlie_dev', 'charlie@example.com', 'Charlie', NOW());

-- 插入日记
INSERT INTO "Diary" ("objectId", "title", "content", "author", "mood", "tags", "createdAt") VALUES
('d001', '美好的一天', '今天天气晴朗，心情愉快...', 'usr001', 'happy', '["日常", "好天气"]'::JSONB, NOW()),
('d002', '项目总结', '完成了数据库设计工作...', 'usr002', 'proud', '["工作", "成就"]'::JSONB, NOW()),
('d003', '感恩日记', '感谢生命中所有的小确幸...', 'usr003', 'grateful', '["感恩", "生活"]'::JSONB, NOW());

-- 插入感恩清单
INSERT INTO "ThanksType" ("objectId", "creator", "title", "items", "weeklyGoal", "createdAt") VALUES
('thanks001', 'usr001', '每日感恩', 
'[{"content": "家人的支持", "category": "家庭", "stars": 5}, 
  {"content": "美味的午餐", "category": "生活", "stars": 3}]'::JSONB, 
5, NOW()),
('thanks002', 'usr002', '工作感恩', 
'[{"content": "同事的帮助", "category": "工作", "stars": 4}, 
  {"content": "项目顺利完成", "category": "成就", "stars": 5}]'::JSONB, 
3, NOW());

-- 插入动态
INSERT INTO "Dynamic" ("objectId", "creator", "content", "refDiary", "images", "createdAt") VALUES
('m001', 'usr001', '分享今天的快乐时刻', 'd001', '["image1.jpg", "image2.jpg"]'::JSONB, NOW()),
('m002', 'usr002', '技术分享：数据库设计', 'd002', '["tech1.png"]'::JSONB, NOW());

-- 插入互动
INSERT INTO "Interaction" ("objectId", "type", "fromUser", "toUser", "targetType", "targetId", "content", "createdAt") VALUES
('i001', 'like', 'usr002', 'usr001', 'Diary', 'd001', NULL, NOW()),
('i002', 'comment', 'usr001', 'usr002', 'Dynamic', 'm002', '写得真好！', NOW()),
('i003', 'like', 'usr003', 'usr001', 'ThanksType', 'thanks001', NULL, NOW());

-- 插入聊天消息
INSERT INTO "ChatMessage" ("objectId", "sender", "receiver", "content", "diaryRef", "createdAt") VALUES
('msg001', 'usr001', 'usr002', '你好，看了你的日记', 'd002', NOW()),
('msg002', 'usr002', 'usr001', '谢谢关注！', 'd002', NOW()),
('msg003', 'usr003', 'usr001', '你的感恩清单很棒！', NULL, NOW());

schema.md 8.6 KB Bezpośredni odnośnik Historia Czysty

AI日记

数据范式设计

UML类图

SQL语句

建表语句

schema.md 8.6 KB

Bezpośredni odnośnik Historia Czysty