tabs/
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// Tabs is a bit special - it's a trivial SQL schema and is only used as a persistent
6// cache, and the semantics of the "tabs" collection means there's no need for
7// syncChangeCounter/syncStatus nor a mirror etc.
8
9use rusqlite::{Connection, Transaction};
10use sql_support::open_database::{
11    ConnectionInitializer as MigrationLogic, Error as MigrationError, Result as MigrationResult,
12};
13
14// The record is the TabsRecord struct in json and this module doesn't need to deserialize, so we just
15// store each client as its own row.
16const CREATE_TABS_TABLE_SQL: &str = "
17    CREATE TABLE IF NOT EXISTS tabs (
18        guid            TEXT NOT NULL PRIMARY KEY,
19        record          TEXT NOT NULL,
20        last_modified   INTEGER NOT NULL
21    );
22";
23
24const CREATE_META_TABLE_SQL: &str = "
25    CREATE TABLE IF NOT EXISTS moz_meta (
26        key    TEXT PRIMARY KEY,
27        value  NOT NULL
28    )
29";
30
31const CREATE_PENDING_REMOTE_DELETE_TABLE_SQL: &str = "
32    CREATE TABLE IF NOT EXISTS remote_tab_commands (
33        id                      INTEGER PRIMARY KEY,
34        device_id               TEXT NOT NULL,
35        command                 INTEGER NOT NULL, -- a CommandKind value
36        url                     TEXT,
37        time_requested          INTEGER NOT NULL, -- local timestamp when this was initially written.
38        time_sent               INTEGER -- local timestamp, non-null == no longer pending.
39    );
40
41    CREATE UNIQUE INDEX IF NOT EXISTS remote_tab_commands_index ON remote_tab_commands(device_id, command, url);
42";
43
44pub(crate) static LAST_SYNC_META_KEY: &str = "last_sync_time";
45pub(crate) static GLOBAL_SYNCID_META_KEY: &str = "global_sync_id";
46pub(crate) static COLLECTION_SYNCID_META_KEY: &str = "tabs_sync_id";
47// Tabs stores this in the meta table due to a unique requirement that we only know the list
48// of connected clients when syncing, however getting the list of tabs could be called at anytime
49// so we store it so we can translate from the tabs sync record ID to the FxA device id for the client
50pub(crate) static REMOTE_CLIENTS_KEY: &str = "remote_clients";
51
52fn init_schema(db: &Connection) -> rusqlite::Result<()> {
53    db.execute_batch(CREATE_TABS_TABLE_SQL)?;
54    db.execute_batch(CREATE_META_TABLE_SQL)?;
55    db.execute_batch(CREATE_PENDING_REMOTE_DELETE_TABLE_SQL)?;
56    Ok(())
57}
58
59pub struct TabsMigrationLogic;
60
61impl MigrationLogic for TabsMigrationLogic {
62    const NAME: &'static str = "tabs storage db";
63    const END_VERSION: u32 = 5;
64
65    fn prepare(&self, conn: &Connection, _db_empty: bool) -> MigrationResult<()> {
66        let initial_pragmas = "
67            -- We don't care about temp tables being persisted to disk.
68            PRAGMA temp_store = 2;
69            -- we unconditionally want write-ahead-logging mode.
70            PRAGMA journal_mode=WAL;
71            -- foreign keys seem worth enforcing (and again, we don't care in practice)
72            PRAGMA foreign_keys = ON;
73        ";
74        conn.execute_batch(initial_pragmas)?;
75        // This is where we'd define our sql functions if we had any!
76        conn.set_prepared_statement_cache_capacity(128);
77        Ok(())
78    }
79
80    fn init(&self, db: &Transaction<'_>) -> MigrationResult<()> {
81        error_support::debug!("Creating schemas");
82        init_schema(db)?;
83        Ok(())
84    }
85
86    fn upgrade_from(&self, db: &Transaction<'_>, version: u32) -> MigrationResult<()> {
87        match version {
88            3 | 4 => upgrade_simple_commands_drop(db),
89            2 => upgrade_from_v2(db),
90            1 => upgrade_from_v1(db),
91            _ => Err(MigrationError::IncompatibleVersion(version)),
92        }
93    }
94}
95
96// while we can get away with this, we should :)
97fn upgrade_simple_commands_drop(db: &Connection) -> MigrationResult<()> {
98    // v3 changed the table schema. v5 changed the name.
99    db.execute_batch("DROP TABLE IF EXISTS pending_remote_tab_closures;")?;
100    db.execute_batch("DROP TABLE IF EXISTS remote_tab_commands;")?;
101    db.execute_batch(CREATE_PENDING_REMOTE_DELETE_TABLE_SQL)?;
102    Ok(())
103}
104
105fn upgrade_from_v2(db: &Connection) -> MigrationResult<()> {
106    db.execute_batch(CREATE_PENDING_REMOTE_DELETE_TABLE_SQL)?;
107    Ok(())
108}
109
110fn upgrade_from_v1(db: &Connection) -> MigrationResult<()> {
111    // The previous version stored the entire payload in one row
112    // and cleared on each sync -- it's fine to just drop it
113    db.execute_batch("DROP TABLE tabs;")?;
114    db.execute_batch(CREATE_TABS_TABLE_SQL)?;
115    db.execute_batch(CREATE_META_TABLE_SQL)?;
116    Ok(())
117}
118
119#[cfg(test)]
120mod tests {
121    use super::*;
122    use crate::storage::TabsStorage;
123    use rusqlite::OptionalExtension;
124    use serde_json::json;
125    use sql_support::open_database::test_utils::MigratedDatabaseFile;
126
127    const CREATE_V1_SCHEMA_SQL: &str = "
128        CREATE TABLE IF NOT EXISTS tabs (
129            payload TEXT NOT NULL
130        );
131        PRAGMA user_version=1;
132    ";
133
134    #[test]
135    fn test_create_schema_twice() {
136        let mut db = TabsStorage::new_with_mem_path("test");
137        let conn = db.open_or_create().unwrap();
138        init_schema(conn).expect("should allow running twice");
139        init_schema(conn).expect("should allow running thrice");
140    }
141
142    #[test]
143    fn test_tabs_db_upgrade_from_v1() {
144        let db_file = MigratedDatabaseFile::new(TabsMigrationLogic, CREATE_V1_SCHEMA_SQL);
145        db_file.run_all_upgrades();
146        // Verify we can open the DB just fine, since migration is essentially a drop
147        // we don't need to check any data integrity
148        let mut storage = TabsStorage::new(db_file.path);
149        storage.open_or_create().unwrap();
150        assert!(storage.open_if_exists().unwrap().is_some());
151
152        let test_payload = json!({
153            "id": "device-with-a-tab",
154            "clientName": "device with a tab",
155            "tabs": [{
156                "title": "the title",
157                "urlHistory": [
158                    "https://mozilla.org/"
159                ],
160                "icon": "https://mozilla.org/icon",
161                "lastUsed": 1643764207,
162            }]
163        });
164        let db = storage.open_if_exists().unwrap().unwrap();
165        // We should be able to insert without a SQL error after upgrade
166        db.execute(
167            "INSERT INTO tabs (guid, record, last_modified) VALUES (:guid, :record, :last_modified);",
168            rusqlite::named_params! {
169                ":guid": "my-device",
170                ":record": serde_json::to_string(&test_payload).unwrap(),
171                ":last_modified": "1643764207"
172            },
173        )
174        .unwrap();
175
176        let row: Option<String> = db
177            .query_row("SELECT guid FROM tabs;", [], |row| row.get(0))
178            .optional()
179            .unwrap();
180        // Verify we can query for a valid guid now
181        assert_eq!(row.unwrap(), "my-device");
182    }
183
184    #[test]
185    fn test_commands_unique() {
186        let mut db = TabsStorage::new_with_mem_path("test_commands_unique");
187        let conn = db.open_or_create().unwrap();
188        conn.execute(
189            "INSERT INTO remote_tab_commands
190                (device_id, command, url, time_requested, time_sent)
191                VALUES ('d', 'close', 'url', 1, null)",
192            [],
193        )
194        .unwrap();
195        conn.execute(
196            "INSERT INTO remote_tab_commands
197                (device_id, command, url, time_requested, time_sent)
198                VALUES ('d', 'close', 'url', 1, null)",
199            [],
200        )
201        .expect_err("identical command should fail");
202    }
203}