查看“︁Sqlite3 advanced feature”︁的源代码

<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]]