remote_settings/
schema.rs

1/* This Source Code Form is subject to the terms of the Mozilla Public
2 * License, v. 2.0. If a copy of the MPL was not distributed with this
3 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
4 */
5
6use rusqlite::{Connection, Transaction};
7use sql_support::open_database::{self, ConnectionInitializer};
8
9/// The current gatabase schema version.
10///
11/// For any changes to the schema [`SQL`], please make sure to:
12///
13///  1. Bump this version.
14///  2. Add a migration from the old version to the new version in
15///     [`RemoteSettingsConnectionInitializer::upgrade_from`].
16pub const VERSION: u32 = 2;
17
18/// The current remote settings database schema.
19pub const SQL: &str = r#"
20CREATE TABLE IF NOT EXISTS records (
21    id TEXT PRIMARY KEY,
22    collection_url TEXT NOT NULL,
23    data BLOB NOT NULL);
24CREATE TABLE IF NOT EXISTS attachments (
25    id TEXT PRIMARY KEY,
26    collection_url TEXT NOT NULL,
27    data BLOB NOT NULL);
28CREATE TABLE IF NOT EXISTS collection_metadata (
29    collection_url TEXT PRIMARY KEY,
30    last_modified INTEGER, bucket TEXT, signatures TEXT);
31"#;
32
33/// Initializes an SQLite connection to the Remote Settings database, performing
34/// migrations as needed.
35#[derive(Default)]
36pub struct RemoteSettingsConnectionInitializer;
37
38impl ConnectionInitializer for RemoteSettingsConnectionInitializer {
39    const NAME: &'static str = "remote_settings";
40    const END_VERSION: u32 = 4;
41
42    fn prepare(&self, conn: &Connection, _db_empty: bool) -> open_database::Result<()> {
43        let initial_pragmas = "
44            -- Use in-memory storage for TEMP tables.
45            PRAGMA temp_store = 2;
46            PRAGMA journal_mode = WAL;
47        ";
48        conn.execute_batch(initial_pragmas)?;
49        sql_support::debug_tools::define_debug_functions(conn)?;
50
51        Ok(())
52    }
53
54    fn init(&self, db: &Transaction<'_>) -> open_database::Result<()> {
55        db.execute_batch(SQL)?;
56        Ok(())
57    }
58
59    fn upgrade_from(&self, tx: &Transaction<'_>, version: u32) -> open_database::Result<()> {
60        match version {
61            // Upgrade from a database created before this crate used sql-support.
62            0 => {
63                tx.execute("ALTER TABLE collection_metadata DROP column fetched", ())?;
64                Ok(())
65            }
66            1 => {
67                tx.execute("ALTER TABLE collection_metadata ADD COLUMN bucket TEXT", ())?;
68                tx.execute(
69                    "ALTER TABLE collection_metadata ADD COLUMN signature TEXT",
70                    (),
71                )?;
72                tx.execute("ALTER TABLE collection_metadata ADD COLUMN x5u TEXT", ())?;
73                Ok(())
74            }
75            2 => {
76                tx.execute(
77                    "ALTER TABLE collection_metadata ADD COLUMN signatures TEXT",
78                    (),
79                )?;
80                tx.execute(
81                    r#"
82                    UPDATE collection_metadata
83                    SET signatures = CASE
84                        -- Replace empty signatures with empty arrays.
85                        WHEN COALESCE(signature, '') = '' OR COALESCE(x5u, '') = ''
86                            THEN json_array()
87                        -- Add the existing signature as array with one element.
88                        ELSE json_array(
89                            json_object(
90                                'signature', signature,
91                                'x5u', x5u
92                            )
93                        )
94                    END
95                    "#,
96                    (),
97                )?;
98                tx.execute("ALTER TABLE collection_metadata DROP COLUMN signature", ())?;
99                tx.execute("ALTER TABLE collection_metadata DROP COLUMN x5u", ())?;
100                Ok(())
101            }
102            3 => {
103                // Clean up orphaned attachment blobs that are no longer referenced
104                // by any current record. A bug (FXIOS-15181) caused these to accumulate over time,
105                // leading a database to grow to 1+ GB (where the expected size was ~11 MB).
106                tx.execute(
107                    "DELETE FROM attachments
108                    WHERE NOT EXISTS (
109                        SELECT 1 FROM records
110                        WHERE json_extract(records.data, '$.attachment.location') = attachments.id
111                    )",
112                    (),
113                )?;
114                Ok(())
115            }
116            _ => Err(open_database::Error::IncompatibleVersion(version)),
117        }
118    }
119}
120
121#[cfg(test)]
122mod test {
123    use super::*;
124    use sql_support::open_database::test_utils::MigratedDatabaseFile;
125
126    // Snapshot of the v0 schema.  We use this to test that we can migrate from there to the
127    // current schema.
128    const V0_SCHEMA: &str = r#"
129CREATE TABLE IF NOT EXISTS records (
130    id TEXT PRIMARY KEY,
131    collection_url TEXT NOT NULL,
132    data BLOB NOT NULL);
133CREATE TABLE IF NOT EXISTS attachments (
134    id TEXT PRIMARY KEY,
135    collection_url TEXT NOT NULL,
136    data BLOB NOT NULL);
137CREATE TABLE IF NOT EXISTS collection_metadata (
138    collection_url TEXT PRIMARY KEY,
139    last_modified INTEGER,
140    fetched BOOLEAN);
141PRAGMA user_version=0;
142"#;
143
144    /// Test running all schema upgrades from V0, which was the first schema with a "real"
145    /// migration.
146    ///
147    /// If an upgrade fails, then this test will fail with a panic.
148    #[test]
149    fn test_all_upgrades() {
150        let db_file = MigratedDatabaseFile::new(RemoteSettingsConnectionInitializer, V0_SCHEMA);
151        db_file.run_all_upgrades();
152        db_file.assert_schema_matches_new_database();
153    }
154
155    #[test]
156    fn test_2_to_3_signatures() {
157        let db_file = MigratedDatabaseFile::new(RemoteSettingsConnectionInitializer, V0_SCHEMA);
158        db_file.upgrade_to(2);
159        let mut conn = db_file.open();
160        let tx = conn.transaction().unwrap();
161        tx.execute(
162            "INSERT INTO collection_metadata (collection_url, last_modified, bucket, signature, x5u) VALUES (?, ?, ?, ?, ?)",
163            ("a", 123, "main", "sig1", "uri1"),
164        ).unwrap();
165        tx.execute(
166            "INSERT INTO collection_metadata (collection_url, last_modified, bucket, signature, x5u) VALUES (?, ?, ?, ?, ?)",
167            ("b", 456, "main", "sig2", "uri2"),
168        ).unwrap();
169        tx.commit().unwrap();
170
171        db_file.upgrade_to(3);
172
173        let mut stmt = conn
174            .prepare("SELECT signatures FROM collection_metadata WHERE collection_url = 'a'")
175            .unwrap();
176        let signatures1: String = stmt.query_row([], |row| row.get(0)).unwrap();
177        assert_eq!(signatures1, r#"[{"signature":"sig1","x5u":"uri1"}]"#);
178
179        stmt = conn
180            .prepare("SELECT signatures FROM collection_metadata WHERE collection_url = 'b'")
181            .unwrap();
182        let signatures2: String = stmt.query_row([], |row| row.get(0)).unwrap();
183        assert_eq!(signatures2, r#"[{"signature":"sig2","x5u":"uri2"}]"#)
184    }
185
186    #[test]
187    fn test_3_to_4_orphaned_attachments_cleanup() {
188        let db_file = MigratedDatabaseFile::new(RemoteSettingsConnectionInitializer, V0_SCHEMA);
189        db_file.upgrade_to(3);
190        let mut conn = db_file.open();
191        let tx = conn.transaction().unwrap();
192
193        let record = serde_json::json!({
194            "id": "sponsored-suggestions-us-phone",
195            "last_modified": 200,
196            "attachment": {
197                "filename": "sponsored-suggestions-us-phone.json",
198                "mimetype": "application/json",
199                "location": "main-workspace/quicksuggest-amp/b.json",
200                "hash": "abc123",
201                "size": 1000
202            },
203            "type": "amp"
204        });
205        tx.execute(
206            "INSERT INTO records (id, collection_url, data) VALUES (?, ?, ?)",
207            rusqlite::params![
208                "sponsored-suggestions-us-phone",
209                "https://firefox.settings.services.mozilla.com/v1/buckets/main/collections/quicksuggest-amp",
210                serde_json::to_vec(&record).unwrap(),
211            ],
212        ).unwrap();
213
214        // Insert the current attachment (referenced by the record)
215        tx.execute(
216            "INSERT INTO attachments (id, collection_url, data) VALUES (?, ?, ?)",
217            rusqlite::params![
218                "main-workspace/quicksuggest-amp/b.json",
219                "https://firefox.settings.services.mozilla.com/v1/buckets/main/collections/quicksuggest-amp",
220                b"current attachment data",
221            ],
222        ).unwrap();
223
224        // Insert an orphaned attachment (old version, no record references it)
225        tx.execute(
226            "INSERT INTO attachments (id, collection_url, data) VALUES (?, ?, ?)",
227            rusqlite::params![
228                "main-workspace/quicksuggest-amp/a.json",
229                "https://firefox.settings.services.mozilla.com/v1/buckets/main/collections/quicksuggest-amp",
230                b"orphaned attachment data that should be cleaned up",
231            ],
232        ).unwrap();
233
234        tx.commit().unwrap();
235
236        // Verify that both attachments exist before migration
237        let count: i64 = conn
238            .query_row("SELECT COUNT(*) FROM attachments", [], |row| row.get(0))
239            .unwrap();
240        assert_eq!(count, 2, "Should have 2 attachments before migration");
241        drop(conn);
242
243        db_file.upgrade_to(4);
244        db_file.assert_schema_matches_new_database();
245
246        let conn = db_file.open();
247
248        // Only the referenced attachment should remain
249        let count: i64 = conn
250            .query_row("SELECT COUNT(*) FROM attachments", [], |row| row.get(0))
251            .unwrap();
252        assert_eq!(
253            count, 1,
254            "Should have 1 attachment after migration (orphan cleaned up)"
255        );
256
257        // Verify that the surviving attachment is the current one
258        let surviving_id: String = conn
259            .query_row("SELECT id FROM attachments", [], |row| row.get(0))
260            .unwrap();
261        assert_eq!(
262            surviving_id, "main-workspace/quicksuggest-amp/b.json",
263            "The referenced attachment should survive the migration"
264        );
265    }
266}
remote_settings/schema.rs

remote_settings/
schema.rs
