Riguz：创建页面，内容为“ CREATE TABLE Notes ( id UUID PRIMARY KEY, title TEXT, content JSON NOT NULL, content_checksum TEXT NOT NULL, content_plaintext TEXT NOT NULL, classification CHAR(1) NOT NULL CHECK (classification IN ('C', 'S', 'T')), -- C = Confidential, S = Secret, T = Top Secret created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, d…”

2025-07-01T06:36:37Z

创建页面，内容为“<syntaxhighlight lang="sql"> CREATE TABLE Notes ( id UUID PRIMARY KEY, title TEXT, content JSON NOT NULL, content_checksum TEXT NOT NULL, content_plaintext TEXT NOT NULL, classification CHAR(1) NOT NULL CHECK (classification IN ('C', 'S', 'T')), -- C = Confidential, S = Secret, T = Top Secret created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, d…”

新页面

<syntaxhighlight lang="sql">
CREATE TABLE
Notes (
id UUID PRIMARY KEY,
title TEXT,
content JSON NOT NULL,
content_checksum TEXT NOT NULL,
content_plaintext TEXT NOT NULL,
classification CHAR(1) NOT NULL CHECK (classification IN ('C', 'S', 'T')), -- C = Confidential, S = Secret, T = Top Secret
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
deleted_at DATETIME DEFAULT NULL
);

CREATE TABLE
NoteHistory (
id INTEGER PRIMARY KEY AUTOINCREMENT,
note_id UUID NOT NULL,
content JSON NOT NULL,
content_checksum TEXT NOT NULL,
content_plaintext TEXT NOT NULL,
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (note_id) REFERENCES Notes (id)
);

CREATE VIRTUAL TABLE NoteSearch USING FTS5 (
id UNINDEXED,
title,
abstract,
content_plaintext,
created_at UNINDEXED
);

CREATE TRIGGER UpdateNoteSearch_Insert AFTER INSERT ON Notes WHEN NEW.deleted_at IS NULL BEGIN
INSERT INTO
NoteSearch (
id,
title,
abstract,
content_plaintext,
created_at
)
VALUES
(
NEW.id,
NEW.title,
SUBSTR (
NEW.content_plaintext,
1,
INSTR (
NEW.content_plaintext,
'\n',
INSTR (NEW.content_plaintext, '\n') + 1
) - 1
),
NEW.content_plaintext,
NEW.created_at
);

END;

CREATE TRIGGER UpdateNoteSearch_Update AFTER
UPDATE ON Notes WHEN NEW.deleted_at IS NULL BEGIN
INSERT INTO
NoteSearch (
id,
title,
abstract,
content_plaintext,
created_at
)
VALUES
(
NEW.id,
NEW.title,
SUBSTR (
NEW.content_plaintext,
1,
INSTR (
NEW.content_plaintext,
'\n',
INSTR (NEW.content_plaintext, '\n') + 1
) - 1
),
NEW.content_plaintext,
NEW.created_at
) ON CONFLICT (id) DO
UPDATE
SET
title = NEW.title,
abstract = SUBSTR (
NEW.content_plaintext,
1,
INSTR (
NEW.content_plaintext,
'\n',
INSTR (NEW.content_plaintext, '\n') + 1
) - 1
),
content_plaintext = NEW.content_plaintext,
created_at = NEW.created_at;

END;

CREATE TRIGGER RemoveNoteSearch AFTER
UPDATE ON Notes WHEN NEW.deleted_at IS NOT NULL BEGIN
DELETE FROM NoteSearch
WHERE
id = OLD.id;

END;
</syntaxhighlight>

[[Category:Sqlite3]]
[[Category:Database]]

Sqlite3 advanced feature - 版本历史