client_core/store/
schema.rs

1use rusqlite::Connection;
2
3pub const CURRENT_SCHEMA_VERSION: i64 = 1;
4
5pub fn apply_migrations(conn: &Connection) -> rusqlite::Result<()> {
6    conn.execute_batch(
7        "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);",
8    )?;
9    let current: i64 = conn
10        .query_row(
11            "SELECT CAST(value AS INTEGER) FROM meta WHERE key='schema_version'",
12            [],
13            |r| r.get(0),
14        )
15        .unwrap_or(0);
16
17    if current < 1 {
18        migrate_v1(conn)?;
19    }
20    // Future: if current < 2 { migrate_v2(conn)?; }
21    Ok(())
22}
23
24fn migrate_v1(conn: &Connection) -> rusqlite::Result<()> {
25    conn.execute_batch(
26        r#"
27        CREATE TABLE clips (
28          id           TEXT PRIMARY KEY,
29          source       TEXT NOT NULL,
30          source_key   TEXT,
31          content_type TEXT NOT NULL,
32          content      BLOB,
33          media_path   TEXT,
34          byte_size    INTEGER NOT NULL DEFAULT 0,
35          created_at   INTEGER NOT NULL,
36          pinned       INTEGER NOT NULL DEFAULT 0,
37          pinned_at    INTEGER
38        );
39        CREATE INDEX clips_created_idx ON clips(created_at DESC);
40        CREATE INDEX clips_source_idx  ON clips(source, created_at DESC);
41        CREATE INDEX clips_pinned_idx  ON clips(pinned) WHERE pinned = 1;
42
43        CREATE VIRTUAL TABLE clips_fts USING fts5(
44            content, content='clips', content_rowid='rowid'
45        );
46
47        CREATE TRIGGER clips_ai AFTER INSERT ON clips BEGIN
48          INSERT INTO clips_fts(rowid, content) VALUES (new.rowid, COALESCE(new.content, ''));
49        END;
50        CREATE TRIGGER clips_ad AFTER DELETE ON clips BEGIN
51          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
52        END;
53        CREATE TRIGGER clips_au AFTER UPDATE ON clips BEGIN
54          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
55          INSERT INTO clips_fts(rowid, content)            VALUES (new.rowid, COALESCE(new.content, ''));
56        END;
57
58        CREATE TABLE devices (
59          id           TEXT PRIMARY KEY,
60          hostname     TEXT NOT NULL,
61          nickname     TEXT,
62          source_key   TEXT,
63          machine_id   TEXT,
64          public_key   TEXT,
65          paired_at    INTEGER,
66          last_push_at INTEGER,
67          online       INTEGER NOT NULL DEFAULT 0,
68          refreshed_at INTEGER NOT NULL
69        );
70
71        CREATE TABLE retention_prefs (device_id TEXT PRIMARY KEY, days INTEGER NOT NULL);
72        CREATE TABLE alert_prefs     (source    TEXT PRIMARY KEY, enabled INTEGER NOT NULL);
73
74        INSERT INTO meta(key, value) VALUES('schema_version', '1');
75    "#,
76    )?;
77    Ok(())
78}
client_core/store/schema.rs

client_core/store/
schema.rs
