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 AI日记系统数据库设计

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 Moment {
  + 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
}

' 关系定义
_User "1" *-- "many" Diary : 创作
_User "1" *-- "many" ChatMessage : 发送
_User "1" *-- "many" Moment : 发布
_User "1" *-- "many" Interaction : 发起

Diary "1" *-- "many" ChatMessage : 关联
Diary "1" *-- "many" Moment : 引用
Diary "1" *-- "many" Interaction : 接收

Moment "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" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "nickname" VARCHAR(100),
  "avatar" VARCHAR(255), -- Parse.File存储路径
  "bio" TEXT,
  "lastActiveAt" TIMESTAMP WITH TIME ZONE,
  "privacySettings" JSONB
);

CREATE TABLE "Diary" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP WITH TIME ZONE 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, -- {latitude: xx, longitude: xx}
  "weather" JSONB,
  "aiAnalysis" JSONB
);

CREATE TABLE "ChatMessage" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP WITH TIME ZONE 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 "Moment" (
  "objectId" VARCHAR(36) PRIMARY KEY,
  "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "creator" VARCHAR(36) REFERENCES "_User"("objectId"),
  "content" TEXT,
  "images" JSONB DEFAULT '[]'::JSONB, -- Parse.File路径数组
  "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" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "type" VARCHAR(20) NOT NULL, -- like/comment/share
  "fromUser" VARCHAR(36) REFERENCES "_User"("objectId"),
  "toUser" VARCHAR(36) REFERENCES "_User"("objectId"),
  "targetType" VARCHAR(20) NOT NULL, -- Diary/Moment
  "targetId" VARCHAR(36), -- 多态关联
  "content" TEXT,
  "status" VARCHAR(20) DEFAULT 'unread'
);

测试数据插入语句

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

-- 插入日记
INSERT INTO "Diary" ("objectId", "title", "content", "author", "createdAt") VALUES
('d001', '美好的一天', '今天天气晴朗...', 'usr001', NOW()),
('d002', '项目总结', '完成了数据库设计...', 'usr002', NOW());

-- 插入动态
INSERT INTO "Moment" ("objectId", "creator", "content", "refDiary", "createdAt") VALUES
('m001', 'usr001', '分享我的日记', 'd001', NOW()),
('m002', 'usr002', '技术分享', 'd002', NOW());

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

-- 插入聊天消息
INSERT INTO "ChatMessage" ("objectId", "sender", "receiver", "content", "diaryRef") VALUES
('msg001', 'usr001', 'usr002', '你好，看了你的日记', 'd002'),
('msg002', 'usr002', 'usr001', '谢谢关注！', 'd002');

schema.md 6.7 KB История Директен файл