You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
106 lines
5.0 KiB
106 lines
5.0 KiB
import { integer, sqliteTable, text, real } from "drizzle-orm/sqlite-core";
|
|
import { sql } from "drizzle-orm";
|
|
|
|
// ─── 分类表 ───────────────────────────────────────────
|
|
export const categories = sqliteTable("categories", {
|
|
id: integer().primaryKey({ autoIncrement: true }),
|
|
name: text().notNull(),
|
|
icon: text().notNull().default("📁"),
|
|
color: text().default("#6B7280"),
|
|
parentId: integer("parent_id").references(() => categories.id, { onDelete: "set null" }),
|
|
sortOrder: integer("sort_order").notNull().default(0),
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.notNull(),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.$onUpdate(() => new Date())
|
|
.notNull(),
|
|
});
|
|
|
|
// ─── 标签表 ───────────────────────────────────────────
|
|
export const tags = sqliteTable("tags", {
|
|
id: integer().primaryKey({ autoIncrement: true }),
|
|
name: text().notNull().unique(),
|
|
color: text().default("#6B7280"),
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.notNull(),
|
|
});
|
|
|
|
// ─── 收藏主表 ─────────────────────────────────────────
|
|
export const items = sqliteTable("items", {
|
|
id: integer().primaryKey({ autoIncrement: true }),
|
|
userId: integer("user_id").notNull(),
|
|
type: text({ enum: ["web", "text", "image", "file", "video"] }).notNull(),
|
|
title: text().notNull(),
|
|
description: text(),
|
|
content: text(), // 完整正文（FTS 用）
|
|
url: text(), // 原始链接
|
|
coverUrl: text("cover_url"),
|
|
faviconUrl: text("favicon_url"),
|
|
sourceHost: text("source_host"),
|
|
filePath: text("file_path"),
|
|
fileSize: integer("file_size"),
|
|
fileMime: text("file_mime"),
|
|
|
|
categoryId: integer("category_id").references(() => categories.id, { onDelete: "set null" }),
|
|
|
|
note: text(), // 个人备注
|
|
rating: integer(), // 1-5
|
|
starred: integer({ mode: "boolean" }).notNull().default(false),
|
|
isArchived: integer("is_archived", { mode: "boolean" }).notNull().default(false),
|
|
|
|
aiSummary: text("ai_summary"),
|
|
publishedAt: text("published_at"),
|
|
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.notNull(),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.$onUpdate(() => new Date())
|
|
.notNull(),
|
|
});
|
|
|
|
// ─── 收藏-标签 多对多 ─────────────────────────────────
|
|
export const itemTags = sqliteTable("item_tags", {
|
|
itemId: integer("item_id").notNull().references(() => items.id, { onDelete: "cascade" }),
|
|
tagId: integer("tag_id").notNull().references(() => tags.id, { onDelete: "cascade" }),
|
|
});
|
|
|
|
// ─── 文章表 ───────────────────────────────────────────
|
|
export const articles = sqliteTable("articles", {
|
|
id: integer().primaryKey({ autoIncrement: true }),
|
|
userId: integer("user_id").notNull(),
|
|
title: text().notNull().default("无标题文章"),
|
|
content: text().notNull().default(""),
|
|
coverUrl: text("cover_url"),
|
|
status: text({ enum: ["draft", "published"] }).notNull().default("draft"),
|
|
wordCount: integer("word_count").notNull().default(0),
|
|
shareToken: text("share_token").unique(),
|
|
shareExpiresAt: text("share_expires_at"),
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.notNull(),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.$onUpdate(() => new Date())
|
|
.notNull(),
|
|
});
|
|
|
|
// ─── 文章-收藏 关联表 ─────────────────────────────────
|
|
export const articleItems = sqliteTable("article_items", {
|
|
articleId: integer("article_id").notNull().references(() => articles.id, { onDelete: "cascade" }),
|
|
itemId: integer("item_id").notNull().references(() => items.id, { onDelete: "cascade" }),
|
|
sortOrder: integer("sort_order").notNull().default(0),
|
|
});
|
|
|
|
// ─── 设置表 ───────────────────────────────────────────
|
|
export const settings = sqliteTable("settings", {
|
|
key: text().primaryKey(),
|
|
value: text({ mode: "json" }),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.default(sql`(cast((julianday('now') - 2440587.5)*86400000 as integer))`)
|
|
.notNull(),
|
|
});
|
|
|