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.
100 lines
3.7 KiB
100 lines
3.7 KiB
CREATE TABLE `categories` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`icon` text DEFAULT '📁' NOT NULL,
|
|
`color` text DEFAULT '#6B7280',
|
|
`parent_id` integer,
|
|
`sort_order` integer DEFAULT 0 NOT NULL,
|
|
`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `item_tags` (
|
|
`item_id` integer NOT NULL,
|
|
`tag_id` integer NOT NULL,
|
|
FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON UPDATE no action ON DELETE cascade,
|
|
FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON UPDATE no action ON DELETE cascade
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `items` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`user_id` integer NOT NULL,
|
|
`type` text NOT NULL,
|
|
`title` text NOT NULL,
|
|
`description` text,
|
|
`content` text,
|
|
`url` text,
|
|
`cover_url` text,
|
|
`favicon_url` text,
|
|
`source_host` text,
|
|
`file_path` text,
|
|
`file_size` integer,
|
|
`file_mime` text,
|
|
`category_id` integer,
|
|
`note` text,
|
|
`rating` integer,
|
|
`starred` integer DEFAULT false NOT NULL,
|
|
`is_archived` integer DEFAULT false NOT NULL,
|
|
`ai_summary` text,
|
|
`published_at` text,
|
|
`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
|
|
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `settings` (
|
|
`key` text PRIMARY KEY NOT NULL,
|
|
`value` text,
|
|
`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `tags` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`color` text DEFAULT '#6B7280',
|
|
`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `tags_name_unique` ON `tags` (`name`);--> statement-breakpoint
|
|
|
|
-- FTS5 全文搜索虚拟表
|
|
CREATE VIRTUAL TABLE items_fts USING fts5(
|
|
id UNINDEXED,
|
|
title,
|
|
description,
|
|
content,
|
|
note,
|
|
content=items,
|
|
content_rowid=id,
|
|
tokenize='unicode61 remove_diacritics 1'
|
|
);--> statement-breakpoint
|
|
|
|
-- FTS5 同步触发器：INSERT
|
|
CREATE TRIGGER items_fts_ai AFTER INSERT ON items BEGIN
|
|
INSERT INTO items_fts(rowid, title, description, content, note)
|
|
VALUES (new.id, new.title, new.description, new.content, new.note);
|
|
END;--> statement-breakpoint
|
|
|
|
-- FTS5 同步触发器：DELETE
|
|
CREATE TRIGGER items_fts_ad AFTER DELETE ON items BEGIN
|
|
INSERT INTO items_fts(items_fts, rowid, title, description, content, note)
|
|
VALUES ('delete', old.id, old.title, old.description, old.content, old.note);
|
|
END;--> statement-breakpoint
|
|
|
|
-- FTS5 同步触发器：UPDATE
|
|
CREATE TRIGGER items_fts_au AFTER UPDATE ON items BEGIN
|
|
INSERT INTO items_fts(items_fts, rowid, title, description, content, note)
|
|
VALUES ('delete', old.id, old.title, old.description, old.content, old.note);
|
|
INSERT INTO items_fts(rowid, title, description, content, note)
|
|
VALUES (new.id, new.title, new.description, new.content, new.note);
|
|
END;--> statement-breakpoint
|
|
|
|
-- 常用查询索引
|
|
CREATE INDEX idx_items_user ON items(user_id);
|
|
CREATE INDEX idx_items_type ON items(type);
|
|
CREATE INDEX idx_items_category ON items(category_id);
|
|
CREATE INDEX idx_items_starred ON items(starred);
|
|
CREATE INDEX idx_items_created ON items(created_at DESC);
|
|
CREATE INDEX idx_item_tags_tag ON item_tags(tag_id);
|
|
CREATE UNIQUE INDEX idx_item_tags_unique ON item_tags(item_id, tag_id);
|