client_core/store/
schema.rs

1use rusqlite::Connection;
2
3pub const CURRENT_SCHEMA_VERSION: i64 = 2;
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    if current < 2 {
21        migrate_v2(conn)?;
22    }
23    Ok(())
24}
25
26fn migrate_v1(conn: &Connection) -> rusqlite::Result<()> {
27    conn.execute_batch(
28        r#"
29        CREATE TABLE clips (
30          id           TEXT PRIMARY KEY,
31          source       TEXT NOT NULL,
32          source_key   TEXT,
33          content_type TEXT NOT NULL,
34          content      BLOB,
35          media_path   TEXT,
36          byte_size    INTEGER NOT NULL DEFAULT 0,
37          created_at   INTEGER NOT NULL,
38          pinned       INTEGER NOT NULL DEFAULT 0,
39          pinned_at    INTEGER
40        );
41        CREATE INDEX clips_created_idx ON clips(created_at DESC);
42        CREATE INDEX clips_source_idx  ON clips(source, created_at DESC);
43        CREATE INDEX clips_pinned_idx  ON clips(pinned) WHERE pinned = 1;
44
45        CREATE VIRTUAL TABLE clips_fts USING fts5(
46            content, content='clips', content_rowid='rowid'
47        );
48
49        CREATE TRIGGER clips_ai AFTER INSERT ON clips BEGIN
50          INSERT INTO clips_fts(rowid, content) VALUES (new.rowid, COALESCE(new.content, ''));
51        END;
52        CREATE TRIGGER clips_ad AFTER DELETE ON clips BEGIN
53          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
54        END;
55        CREATE TRIGGER clips_au AFTER UPDATE ON clips BEGIN
56          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
57          INSERT INTO clips_fts(rowid, content)            VALUES (new.rowid, COALESCE(new.content, ''));
58        END;
59
60        CREATE TABLE devices (
61          id           TEXT PRIMARY KEY,
62          hostname     TEXT NOT NULL,
63          nickname     TEXT,
64          source_key   TEXT,
65          machine_id   TEXT,
66          public_key   TEXT,
67          paired_at    INTEGER,
68          last_push_at INTEGER,
69          online       INTEGER NOT NULL DEFAULT 0,
70          refreshed_at INTEGER NOT NULL
71        );
72
73        CREATE TABLE retention_prefs (device_id TEXT PRIMARY KEY, days INTEGER NOT NULL);
74        CREATE TABLE alert_prefs     (source    TEXT PRIMARY KEY, enabled INTEGER NOT NULL);
75
76        INSERT INTO meta(key, value) VALUES('schema_version', '1');
77    "#,
78    )?;
79    Ok(())
80}
81
82fn migrate_v2(conn: &Connection) -> rusqlite::Result<()> {
83    conn.execute_batch(
84        r#"
85        ALTER TABLE clips ADD COLUMN synced INTEGER NOT NULL DEFAULT 1;
86        CREATE INDEX clips_unsynced_idx ON clips(synced, created_at)
87            WHERE synced = 0;
88        UPDATE meta SET value = '2' WHERE key = 'schema_version';
89    "#,
90    )?;
91    Ok(())
92}
93
94#[cfg(test)]
95mod tests {
96    use super::*;
97    use rusqlite::Connection;
98
99    #[test]
100    fn v1_to_v2_adds_synced_column() {
101        let conn = Connection::open_in_memory().unwrap();
102        // Seed meta table that apply_migrations needs.
103        conn.execute_batch("CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);")
104            .unwrap();
105        // Manually run only v1 to simulate a stopped-at-v1 database.
106        migrate_v1(&conn).unwrap();
107
108        // Insert a row before the migration — it must survive and gain synced=1.
109        conn.execute(
110            "INSERT INTO clips (id, source, content_type, created_at) VALUES ('old','s','text',0)",
111            [],
112        )
113        .unwrap();
114
115        // Sanity: synced column does not exist yet.
116        let err = conn.execute(
117            "INSERT INTO clips (id, source, content_type, created_at, synced) VALUES ('x','s','text',0,1)",
118            [],
119        );
120        assert!(err.is_err(), "synced column should not exist in v1 schema");
121
122        // Run the full migration chain.
123        apply_migrations(&conn).unwrap();
124
125        // Pre-existing row picked up the default.
126        let old_synced: i64 = conn
127            .query_row("SELECT synced FROM clips WHERE id='old'", [], |r| r.get(0))
128            .unwrap();
129        assert_eq!(
130            old_synced, 1,
131            "pre-existing rows must get synced=1 after migration"
132        );
133
134        // New row with explicit synced=0 works.
135        conn.execute(
136            "INSERT INTO clips (id, source, content_type, created_at, synced) VALUES ('x','s','text',0,0)",
137            [],
138        )
139        .expect("synced column should exist after v2 migration");
140        let n: i64 = conn
141            .query_row("SELECT synced FROM clips WHERE id='x'", [], |r| r.get(0))
142            .unwrap();
143        assert_eq!(n, 0);
144    }
145
146    #[test]
147    fn fresh_db_has_synced_column_with_default_true() {
148        let conn = Connection::open_in_memory().unwrap();
149        apply_migrations(&conn).unwrap();
150        conn.execute(
151            "INSERT INTO clips (id, source, content_type, created_at) VALUES ('y','s','text',0)",
152            [],
153        )
154        .unwrap();
155        let synced: i64 = conn
156            .query_row("SELECT synced FROM clips WHERE id='y'", [], |r| r.get(0))
157            .unwrap();
158        assert_eq!(synced, 1, "new rows must default to synced=1");
159    }
160}
client_core/store/schema.rs

client_core/store/
schema.rs
