mcpmem_core/
schema.rs

1//! Portable, non-destructive graph bootstrap shared by every runtime role.
2use rusqlite::Connection;
3
4use crate::errors::Result;
5use crate::events::sql_error;
6use crate::graph::TxGuard;
7
8/// Establish the legacy graph prerequisites before applying ordered migrations.
9///
10/// The caller owns connection tuning and durability; this sets no PRAGMAs.
11/// Bootstrap never clears existing rows or rebuilds derived data. Its transaction
12/// serializes the initial statistics seed; all pending versioned migrations then
13/// run together in their own transaction, so a failure publishes no partial
14/// migration version. Call before readers or workers start using the database.
15pub fn initialize_database(conn: &Connection) -> Result<()> {
16    let tx = TxGuard::begin(conn)?;
17    let relation_existed_before_bootstrap: bool = conn
18        .query_row(
19            "SELECT EXISTS(SELECT 1 FROM sqlite_schema WHERE type='table' AND name='relation')",
20            [],
21            |row| row.get(0),
22        )
23        .map_err(sql_error)?;
24    conn.execute_batch(
25        "CREATE TABLE IF NOT EXISTS entity (
26             id          INTEGER PRIMARY KEY,
27             name_hash   INTEGER NOT NULL,
28             name        TEXT    NOT NULL,
29             type_id     INTEGER NOT NULL,
30             obs_count   INTEGER NOT NULL DEFAULT 0,
31             out_deg     INTEGER NOT NULL DEFAULT 0,
32             in_deg      INTEGER NOT NULL DEFAULT 0,
33             created_us  INTEGER NOT NULL,
34             updated_us  INTEGER NOT NULL,
35             flags       INTEGER NOT NULL DEFAULT 0
36         ) STRICT;
37
38         CREATE INDEX IF NOT EXISTS entity_by_hash
39             ON entity(name_hash, type_id, obs_count, out_deg, in_deg)
40             WHERE flags = 0;
41
42         CREATE INDEX IF NOT EXISTS entity_name_ci
43             ON entity(lower(name))
44             WHERE flags = 0;
45
46         CREATE TABLE IF NOT EXISTS observation (
47             id          INTEGER PRIMARY KEY,
48             entity_id   INTEGER NOT NULL,
49             idx         INTEGER NOT NULL,
50             body        TEXT    NOT NULL,
51             created_us  INTEGER NOT NULL
52         ) STRICT;
53
54         CREATE INDEX IF NOT EXISTS obs_by_entity
55             ON observation(entity_id, idx);
56
57         CREATE TABLE IF NOT EXISTS relation (
58             from_id     INTEGER NOT NULL,
59             to_id       INTEGER NOT NULL,
60             type_id     INTEGER NOT NULL,
61             created_us  INTEGER NOT NULL
62         ) STRICT;
63
64         CREATE INDEX IF NOT EXISTS rel_out
65             ON relation(from_id, type_id, to_id);
66
67         CREATE INDEX IF NOT EXISTS rel_in
68             ON relation(to_id, type_id, from_id);
69
70         CREATE VIRTUAL TABLE IF NOT EXISTS name_fts
71             USING fts5(name, content='entity', content_rowid='id',
72                        tokenize='unicode61 remove_diacritics 2');
73
74         CREATE VIRTUAL TABLE IF NOT EXISTS obs_fts
75             USING fts5(body, content='observation', content_rowid='id',
76                        tokenize='unicode61 remove_diacritics 2');
77
78         CREATE TRIGGER IF NOT EXISTS obs_fts_ai AFTER INSERT ON observation BEGIN
79           INSERT INTO obs_fts(rowid, body) VALUES (new.id, new.body);
80         END;
81
82         CREATE TRIGGER IF NOT EXISTS obs_fts_bd BEFORE DELETE ON observation BEGIN
83           INSERT INTO obs_fts(obs_fts, rowid, body) VALUES ('delete', old.id, old.body);
84         END;
85
86         CREATE TABLE IF NOT EXISTS type_dict (
87             id     INTEGER PRIMARY KEY,
88             kind   INTEGER NOT NULL,
89             name   TEXT    NOT NULL,
90             count  INTEGER NOT NULL DEFAULT 0
91         ) STRICT;
92
93         CREATE INDEX IF NOT EXISTS type_by_name
94             ON type_dict(kind, name);
95
96         CREATE TABLE IF NOT EXISTS graph_stat (
97             key    TEXT NOT NULL PRIMARY KEY,
98             value  INTEGER NOT NULL
99         ) STRICT, WITHOUT ROWID;
100
101         CREATE TABLE IF NOT EXISTS hub_degree (
102             entity_id INTEGER PRIMARY KEY,
103             out_deg   INTEGER NOT NULL,
104             in_deg    INTEGER NOT NULL
105         ) STRICT;
106
107         CREATE TABLE IF NOT EXISTS partition_map (
108             table_name TEXT NOT NULL PRIMARY KEY,
109             role       INTEGER NOT NULL,
110             type_id    INTEGER,
111             row_count  INTEGER NOT NULL DEFAULT 0
112         ) STRICT, WITHOUT ROWID;",
113    )
114    .map_err(sql_error)?;
115    if !relation_existed_before_bootstrap {
116        conn.execute_batch(
117            "CREATE UNIQUE INDEX relation_unique_triple
118             ON relation(from_id, to_id, type_id);",
119        )
120        .map_err(sql_error)?;
121    }
122    let has_stat: bool = conn
123        .query_row("SELECT EXISTS(SELECT 1 FROM graph_stat)", [], |row| {
124            row.get(0)
125        })
126        .map_err(sql_error)?;
127    if !has_stat {
128        conn.execute_batch(
129            "INSERT INTO graph_stat(key, value) VALUES
130             ('entities', 0), ('relations', 0), ('observations', 0),
131             ('entity_seq', 0), ('obs_seq', 0);",
132        )
133        .map_err(sql_error)?;
134    }
135    tx.commit()?;
136    crate::events::migrate(conn)
137}
mcpmem_core/schema.rs

mcpmem_core/
schema.rs
