/* This Source Code Form is subject to the terms of the Mozilla Public * License, v. 2.0. If a copy of the MPL was not distributed with this * file, You can obtain one at http://mozilla.org/MPL/2.0/. */ use crate::api::places_api::ConnectionType; use crate::bookmark_sync::engine::LAST_SYNC_META_KEY; use crate::error::debug; use crate::storage::bookmarks::{ bookmark_sync::create_synced_bookmark_roots, create_bookmark_roots, }; use crate::types::SyncStatus; use rusqlite::Connection; use sql_support::ConnExt; use super::db::{Pragma, PragmaGuard}; pub const VERSION: u32 = 20; // Shared schema and temp tables for the read-write and Sync connections. const CREATE_SHARED_SCHEMA_SQL: &str = include_str!("../../sql/create_shared_schema.sql"); const CREATE_SHARED_TEMP_TABLES_SQL: &str = include_str!("../../sql/create_shared_temp_tables.sql"); // Sync-specific temp tables and triggers. const CREATE_SYNC_TEMP_TABLES_SQL: &str = include_str!("../../sql/create_sync_temp_tables.sql"); const CREATE_SYNC_TRIGGERS_SQL: &str = include_str!("../../sql/create_sync_triggers.sql"); // Triggers for the main read-write connection only. const CREATE_MAIN_TRIGGERS_SQL: &str = include_str!("../../sql/create_main_triggers.sql"); lazy_static::lazy_static! { // Triggers for the read-write and Sync connections. static ref CREATE_SHARED_TRIGGERS_SQL: String = { format!( include_str!("../../sql/create_shared_triggers.sql"), increase_frecency_stats = update_origin_frecency_stats("+"), decrease_frecency_stats = update_origin_frecency_stats("-"), ) }; } // Keys in the moz_meta table. pub(crate) static MOZ_META_KEY_ORIGIN_FRECENCY_COUNT: &str = "origin_frecency_count"; pub(crate) static MOZ_META_KEY_ORIGIN_FRECENCY_SUM: &str = "origin_frecency_sum"; pub(crate) static MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES: &str = "origin_frecency_sum_of_squares"; fn update_origin_frecency_stats(op: &str) -> String { format!( " INSERT OR REPLACE INTO moz_meta(key, value) SELECT '{frecency_count}', IFNULL((SELECT value FROM moz_meta WHERE key = '{frecency_count}'), 0) {op} CAST (frecency > 0 AS INT) FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host UNION SELECT '{frecency_sum}', IFNULL((SELECT value FROM moz_meta WHERE key = '{frecency_sum}'), 0) {op} MAX(frecency, 0) FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host UNION SELECT '{frecency_sum_of_squares}', IFNULL((SELECT value FROM moz_meta WHERE key = '{frecency_sum_of_squares}'), 0) {op} (MAX(frecency, 0) * MAX(frecency, 0)) FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host", op = op, frecency_count = MOZ_META_KEY_ORIGIN_FRECENCY_COUNT, frecency_sum = MOZ_META_KEY_ORIGIN_FRECENCY_SUM, frecency_sum_of_squares = MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES, ) } pub fn init(conn: &Connection) -> rusqlite::Result<()> { debug!("Initializing schema"); conn.execute_batch(CREATE_SHARED_SCHEMA_SQL)?; create_bookmark_roots(conn)?; Ok(()) } pub fn finish(db: &Connection, conn_type: ConnectionType) -> rusqlite::Result<()> { match conn_type { // Read-only connections don't need temp tables or triggers, as they // can't write anything. ConnectionType::ReadOnly => {} // The main read-write connection needs shared and main-specific // temp tables and triggers (for example, for writing tombstones). ConnectionType::ReadWrite => { // Every writable connection needs these... db.execute_batch(CREATE_SHARED_TEMP_TABLES_SQL)?; db.execute_batch(&CREATE_SHARED_TRIGGERS_SQL)?; db.execute_batch(CREATE_MAIN_TRIGGERS_SQL)?; } // The Sync connection needs shared and its own temp tables and // triggers, for merging. It also bypasses some of the main // triggers, so that we don't write tombstones for synced deletions. ConnectionType::Sync => { db.execute_batch(CREATE_SHARED_TEMP_TABLES_SQL)?; db.execute_batch(&CREATE_SHARED_TRIGGERS_SQL)?; db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)?; db.execute_batch(CREATE_SYNC_TRIGGERS_SQL)?; create_synced_bookmark_roots(db)?; } } Ok(()) } /// Helper for migration - pre-dates MigrationLogic, hence it has slightly strange wiring. /// Intended use: /// /// ```rust,ignore /// migration(db, cur_ver, 2, &[stuff, to, migrate, version2, to, version3], || Ok(()))?; /// migration(db, cur_ver, 3, &[stuff, to, migrate, version3, to, version4], || Ok(()))?; /// migration(db, cur_ver, 4, &[stuff, to, migrate, version4, to, version5], || Ok(()))?; /// ``` /// /// The callback parameter is if any extra logic is needed for the migration /// (for example, creating bookmark roots). In an ideal world, this would be an /// Option, but sadly, that can't typecheck. fn migration( db: &Connection, cur_version: u32, ours: u32, stmts: &[&str], extra_logic: F, ) -> rusqlite::Result<()> where F: FnOnce() -> rusqlite::Result<()>, { if cur_version == ours { debug!("Upgrading schema from {} to {}", cur_version, ours); for stmt in stmts { db.execute_batch(stmt)?; } extra_logic()?; } Ok(()) } pub fn upgrade_from(db: &Connection, from: u32) -> rusqlite::Result<()> { debug!("Upgrading schema from {} to {}", from, VERSION); // Old-style migrations migration(db, from, 2, &[CREATE_SHARED_SCHEMA_SQL], || Ok(()))?; migration( db, from, 3, &[ // Previous versions had an incomplete version of moz_bookmarks. "DROP TABLE moz_bookmarks", CREATE_SHARED_SCHEMA_SQL, ], || create_bookmark_roots(db.conn()), )?; migration(db, from, 4, &[CREATE_SHARED_SCHEMA_SQL], || Ok(()))?; migration(db, from, 5, &[CREATE_SHARED_SCHEMA_SQL], || Ok(()))?; // new tags tables. migration(db, from, 6, &[CREATE_SHARED_SCHEMA_SQL], || Ok(()))?; // bookmark syncing. migration( db, from, 7, &[ // Changing `moz_bookmarks_synced_structure` to store multiple // parents, so we need to re-download all synced bookmarks. &format!("DELETE FROM moz_meta WHERE key = '{}'", LAST_SYNC_META_KEY), "DROP TABLE moz_bookmarks_synced", "DROP TABLE moz_bookmarks_synced_structure", CREATE_SHARED_SCHEMA_SQL, ], || Ok(()), )?; migration( db, from, 8, &[ // Bump change counter of New() items due to bookmarks `reset` // setting the counter to 0 instead of 1 (#1145) &format!( "UPDATE moz_bookmarks SET syncChangeCounter = syncChangeCounter + 1 WHERE syncStatus = {}", SyncStatus::New as u8 ), ], || Ok(()), )?; migration( db, from, 9, &[ // Add an index for synced bookmark URLs. "CREATE INDEX IF NOT EXISTS moz_bookmarks_synced_urls ON moz_bookmarks_synced(placeId)", ], || Ok(()), )?; migration( db, from, 10, &[ // Add a new table to hold synced and migrated search keywords. "CREATE TABLE IF NOT EXISTS moz_keywords( place_id INTEGER PRIMARY KEY REFERENCES moz_places(id) ON DELETE RESTRICT, keyword TEXT NOT NULL UNIQUE )", // Add an index on synced keywords, so that we can search for // mismatched keywords without a table scan. "CREATE INDEX IF NOT EXISTS moz_bookmarks_synced_keywords ON moz_bookmarks_synced(keyword) WHERE keyword NOT NULL", // Migrate synced keywords into their own table, so that they're // available via `bookmarks_get_url_for_keyword` before the next // sync. "INSERT OR IGNORE INTO moz_keywords(keyword, place_id) SELECT keyword, placeId FROM moz_bookmarks_synced WHERE placeId NOT NULL AND keyword NOT NULL", ], || Ok(()), )?; migration( db, from, 11, &[ // Greatly helps the multi-join query in frecency. "CREATE INDEX IF NOT EXISTS visits_from_type_idx ON moz_historyvisits(from_visit, visit_type)", ], || Ok(()), )?; migration( db, from, 12, &[ // Reconciled items didn't end up with the correct syncStatus. // See #3504 "UPDATE moz_bookmarks AS b SET syncStatus = 2 -- SyncStatus::Normal WHERE EXISTS (SELECT 1 FROM moz_bookmarks_synced WHERE guid = b.guid)", ], || Ok(()), )?; migration(db, from, 13, &[CREATE_SHARED_SCHEMA_SQL], || Ok(()))?; // moz_places_metadata. migration( db, from, 14, &[ // Changing `moz_places_metadata` structure, drop and recreate it. "DROP TABLE moz_places_metadata", CREATE_SHARED_SCHEMA_SQL, ], || Ok(()), )?; // End of old style migrations, starting with the 15 -> 16 migration, we just use match // statements match from { // Skip the old style migrations n if n < 15 => (), // New-style migrations start here 15 => { // Add the `unknownFields` column // // This migration was rolled out incorrectly and we need to check if it was already // applied (https://github.com/mozilla/application-services/issues/5464) let exists_sql = "SELECT 1 FROM pragma_table_info('moz_bookmarks_synced') WHERE name = 'unknownFields'"; let add_column_sql = "ALTER TABLE moz_bookmarks_synced ADD COLUMN unknownFields TEXT"; if !db.exists(exists_sql, [])? { db.execute(add_column_sql, [])?; } } 16 => { // Add the `unknownFields` column for history db.execute("ALTER TABLE moz_places ADD COLUMN unknown_fields TEXT", ())?; db.execute( "ALTER TABLE moz_historyvisits ADD COLUMN unknown_fields TEXT", (), )?; } 17 => { // Drop the CHECK and `FOREIGN KEY(parent)` constraints on // `moz_bookmarks`; schemas >= 18 enforce constraints using // TEMP triggers with more informative error messages. // SQLite doesn't support `ALTER TABLE DROP CONSTRAINT`, so // we rewrite the schema. const NEW_SQL: &str = "CREATE TABLE moz_bookmarks ( \ id INTEGER PRIMARY KEY, \ fk INTEGER DEFAULT NULL, \ type INTEGER NOT NULL, \ parent INTEGER, \ position INTEGER NOT NULL, \ title TEXT, \ dateAdded INTEGER NOT NULL DEFAULT 0, \ lastModified INTEGER NOT NULL DEFAULT 0, \ guid TEXT NOT NULL UNIQUE, \ syncStatus INTEGER NOT NULL DEFAULT 0, \ syncChangeCounter INTEGER NOT NULL DEFAULT 1, \ FOREIGN KEY(fk) REFERENCES moz_places(id) ON DELETE RESTRICT)"; let _c = PragmaGuard::new(db, Pragma::IgnoreCheckConstraints, true)?; let _f = PragmaGuard::new(db, Pragma::ForeignKeys, false)?; let _w = PragmaGuard::new(db, Pragma::WritableSchema, true)?; db.execute( "UPDATE sqlite_schema SET sql = ? WHERE type = 'table' AND name = 'moz_bookmarks'", // _Must_ be valid SQL; updating `sqlite_schema.sql` with // invalid SQL will corrupt the database. rusqlite::params![NEW_SQL], )?; } 18 => { // Create the new indexes by just calling the shared schema file // idx_places_outgoing_by_frecency db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?; // Manually call analyze so the planner can start using the indexes immediately db.execute("ANALYZE moz_places", [])?; } 19 => { // Create the new indexes by just calling the shared schema file // top_frecent_cover_idx, idx_visits_place_type db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?; // Manually call analyze so the planner can start using the indexes immediately db.execute("ANALYZE moz_places", [])?; db.execute("ANALYZE moz_historyvisits", [])?; } // Add more migrations here... // Any other from value indicates that something very wrong happened _ => panic!( "Places does not have a v{} -> v{} migration", from, from + 1 ), } Ok(()) } #[cfg(test)] mod tests { use super::*; use crate::db::{db::PlacesInitializer, PlacesDb}; use crate::error::Result; use sql_support::open_database::test_utils::MigratedDatabaseFile; use std::collections::BTreeSet; use sync_guid::Guid as SyncGuid; use url::Url; #[test] fn test_create_schema_twice() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute_batch(CREATE_SHARED_SCHEMA_SQL) .expect("should allow running twice"); } fn has_tombstone(conn: &PlacesDb, guid: &SyncGuid) -> bool { let count: rusqlite::Result> = conn.try_query_row( "SELECT COUNT(*) from moz_places_tombstones WHERE guid = :guid", &[(":guid", guid)], |row| row.get::<_, u32>(0), true, ); count.unwrap().unwrap() == 1 } #[test] fn test_places_no_tombstone() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); let guid = SyncGuid::random(); conn.execute_cached( "INSERT INTO moz_places (guid, url, url_hash) VALUES (:guid, :url, hash(:url))", &[ (":guid", &guid as &dyn rusqlite::ToSql), ( ":url", &String::from(Url::parse("http://example.com").expect("valid url")), ), ], ) .expect("should work"); let place_id = conn.last_insert_rowid(); conn.execute_cached( "DELETE FROM moz_places WHERE id = :id", &[(":id", &place_id)], ) .expect("should work"); // should not have a tombstone. assert!(!has_tombstone(&conn, &guid)); } #[test] fn test_places_tombstone_removal() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); let guid = SyncGuid::random(); conn.execute_cached( "INSERT INTO moz_places_tombstones VALUES (:guid)", &[(":guid", &guid)], ) .expect("should work"); // insert into moz_places - the tombstone should be removed. conn.execute_cached( "INSERT INTO moz_places (guid, url, url_hash, sync_status) VALUES (:guid, :url, hash(:url), :sync_status)", &[ (":guid", &guid as &dyn rusqlite::ToSql), ( ":url", &String::from(Url::parse("http://example.com").expect("valid url")), ), (":sync_status", &SyncStatus::Normal), ], ) .expect("should work"); assert!(!has_tombstone(&conn, &guid)); } #[test] fn test_bookmark_check_constraints() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute_batch( "INSERT INTO moz_places(id, guid, url, frecency) VALUES(1, 'page_guid___', 'https://example.com', -1);", ) .expect("should insert page"); // type==BOOKMARK but null fk { let e = conn .execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (NULL, 1, 0, 0, 1, 1, 'fake_guid___')", [], ) .expect_err("should fail to insert bookmark with NULL fk"); assert!( e.to_string().contains("insert: type=1; fk NULL"), "Expected error, got: {:?}", e, ); conn.execute_batch( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (1, 1, (SELECT id FROM moz_bookmarks WHERE guid = 'root________'), 0, 1, 1, 'bmk_guid____')", ) .expect("should insert bookmark"); let e = conn .execute( "UPDATE moz_bookmarks SET fk = NULL WHERE guid = 'bmk_guid____'", [], ) .expect_err("should fail to update bookmark with NULL fk"); assert!( e.to_string().contains("update: type=1; fk NULL"), "Expected error, got: {:?}", e, ); } // type!=BOOKMARK and non-null fk { let e = conn .execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (1, 2, 0, 0, 1, 1, 'fake_guid___')", [], ) .expect_err("should fail to insert folder with non-NULL fk"); assert!( e.to_string().contains("insert: type=2; fk NOT NULL"), "Expected error, got: {:?}", e, ); conn.execute_batch( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (NULL, 2, (SELECT id FROM moz_bookmarks WHERE guid = 'root________'), 1, 1, 1, 'folder_guid_')", ) .expect("should insert folder"); let e = conn .execute( "UPDATE moz_bookmarks SET fk = 1 WHERE guid = 'folder_guid_'", [], ) .expect_err("should fail to update folder with non-NULL fk"); assert!( e.to_string().contains("update: type=2; fk NOT NULL"), "Expected error, got: {:?}", e, ); } // null parent for item other than the root { let e = conn .execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (NULL, 2, NULL, 0, 1, 1, 'fake_guid___')", [], ) .expect_err("should fail to insert item with NULL parent"); assert!( e.to_string().contains("insert: item without parent"), "Expected error, got: {:?}", e, ); let e = conn .execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (NULL, 2, -1, 0, 1, 1, 'fake_guid___')", [], ) .expect_err("should fail to insert item with nonexistent parent"); assert!( e.to_string().contains("insert: item without parent"), "Expected error, got: {:?}", e, ); let e = conn .execute( "UPDATE moz_bookmarks SET parent = NULL WHERE guid = 'folder_guid_'", [], ) .expect_err("should fail to update folder with NULL parent"); assert!( e.to_string().contains("update: item without parent"), "Expected error, got: {:?}", e, ); // Bug 1941655 - we only guard against NULL parents, not missing ones. /* let e = conn .execute( "UPDATE moz_bookmarks SET parent = -1 WHERE guid = 'folder_guid_'", [], ) .expect_err("should fail to update folder with nonexistent parent"); assert!( e.to_string().contains("update: item without parent"), "Expected error, got: {:?}", e, ); */ } // Invalid length guid { let e = conn .execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (NULL, 2, 0, 0, 1, 1, 'fake_guid')", [], ) .expect_err("should fail"); assert!( e.to_string().contains("insert: len(guid)=9"), "Expected error, got: {:?}", e, ); let e = conn .execute( "UPDATE moz_bookmarks SET guid = 'fake_guid' WHERE guid = 'bmk_guid____'", [], ) .expect_err("should fail to update bookmark with invalid guid"); assert!( e.to_string().contains("update: len(guid)=9"), "Expected error, got: {:?}", e, ); } // Changing the type of an existing item. { let e = conn .execute( "UPDATE moz_bookmarks SET type = 3 WHERE guid = 'folder_guid_'", [], ) .expect_err("should fail to update type of bookmark"); assert!( e.to_string().contains("update: old type=2; new=3"), "Expected error, got: {:?}", e, ); } } fn select_simple_int(conn: &PlacesDb, stmt: &str) -> u32 { let count: Result> = conn.try_query_row(stmt, [], |row| Ok(row.get::<_, u32>(0)?), false); count.unwrap().unwrap() } fn get_foreign_count(conn: &PlacesDb, guid: &SyncGuid) -> u32 { let count: Result> = conn.try_query_row( "SELECT foreign_count from moz_places WHERE guid = :guid", &[(":guid", guid)], |row| Ok(row.get::<_, u32>(0)?), true, ); count.unwrap().unwrap() } #[test] fn test_bookmark_foreign_count_triggers() { // create the place. let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); let guid1 = SyncGuid::random(); let url1 = Url::parse("http://example.com").expect("valid url"); let guid2 = SyncGuid::random(); let url2 = Url::parse("http://example2.com").expect("valid url"); conn.execute_cached( "INSERT INTO moz_places (guid, url, url_hash) VALUES (:guid, :url, hash(:url))", &[ (":guid", &guid1 as &dyn rusqlite::ToSql), (":url", &String::from(url1)), ], ) .expect("should work"); let place_id1 = conn.last_insert_rowid(); conn.execute_cached( "INSERT INTO moz_places (guid, url, url_hash) VALUES (:guid, :url, hash(:url))", &[ (":guid", &guid2 as &dyn rusqlite::ToSql), (":url", &String::from(url2)), ], ) .expect("should work"); let place_id2 = conn.last_insert_rowid(); assert_eq!(get_foreign_count(&conn, &guid1), 0); assert_eq!(get_foreign_count(&conn, &guid2), 0); // record visits for both URLs, otherwise the place itself will be removed with the bookmark conn.execute_cached( "INSERT INTO moz_historyvisits (place_id, visit_date, visit_type, is_local) VALUES (:place, 10000000, 1, 0);", &[(":place", &place_id1)], ) .expect("should work"); conn.execute_cached( "INSERT INTO moz_historyvisits (place_id, visit_date, visit_type, is_local) VALUES (:place, 10000000, 1, 1);", &[(":place", &place_id2)], ) .expect("should work"); // create a bookmark pointing at it. conn.execute_cached( "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES (:place_id, 1, 1, 0, 1, 1, 'fake_guid___')", &[(":place_id", &place_id1)], ) .expect("should work"); assert_eq!(get_foreign_count(&conn, &guid1), 1); assert_eq!(get_foreign_count(&conn, &guid2), 0); // change the bookmark to point at a different place. conn.execute_cached( "UPDATE moz_bookmarks SET fk = :new_place WHERE guid = 'fake_guid___';", &[(":new_place", &place_id2)], ) .expect("should work"); assert_eq!(get_foreign_count(&conn, &guid1), 0); assert_eq!(get_foreign_count(&conn, &guid2), 1); conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'fake_guid___';", []) .expect("should work"); assert_eq!(get_foreign_count(&conn, &guid1), 0); assert_eq!(get_foreign_count(&conn, &guid2), 0); } #[test] fn test_bookmark_synced_foreign_count_triggers() { // create the place. let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); let url = Url::parse("http://example.com").expect("valid url"); conn.execute_cached( "INSERT INTO moz_places (guid, url, url_hash) VALUES ('fake_guid___', :url, hash(:url))", &[(":url", &String::from(url))], ) .expect("should work"); let place_id = conn.last_insert_rowid(); assert_eq!(get_foreign_count(&conn, &"fake_guid___".into()), 0); // create a bookmark pointing at it. conn.execute_cached( "INSERT INTO moz_bookmarks_synced (placeId, guid) VALUES (:place_id, 'fake_guid___')", &[(":place_id", &place_id)], ) .expect("should work"); assert_eq!(get_foreign_count(&conn, &"fake_guid___".into()), 1); // delete it. conn.execute_cached( "DELETE FROM moz_bookmarks_synced WHERE guid = 'fake_guid___';", [], ) .expect("should work"); assert_eq!(get_foreign_count(&conn, &"fake_guid___".into()), 0); } #[test] fn test_bookmark_delete_restrict() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute_all(&[ "INSERT INTO moz_places (guid, url, url_hash) VALUES ('place_guid__', 'http://example.com/', hash('http://example.com/'))", "INSERT INTO moz_bookmarks (type, parent, position, dateAdded, lastModified, guid, fk) VALUES (1, 1, 0, 1, 1, 'fake_guid___', last_insert_rowid())", ]) .expect("should be able to do the inserts"); // Should be impossible to delete the place. conn.execute("DELETE FROM moz_places WHERE guid = 'place_guid__';", []) .expect_err("should fail"); // delete the bookmark. conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'fake_guid___';", []) .expect("should be able to delete the bookmark"); // now we should be able to delete the place. conn.execute("DELETE FROM moz_places WHERE guid = 'place_guid__';", []) .expect("should now be able to delete the place"); } #[test] fn test_bookmark_auto_deletes() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute_all(&[ // A folder to hold a bookmark. "INSERT INTO moz_bookmarks (type, parent, position, dateAdded, lastModified, guid) VALUES (3, 1, 0, 1, 1, 'folder_guid_')", // A place for the bookmark. "INSERT INTO moz_places (guid, url, url_hash) VALUES ('place_guid__', 'http://example.com/', hash('http://example.com/'))", // The bookmark. "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES --fk, type (last_insert_rowid(), 1, -- parent (SELECT id FROM moz_bookmarks WHERE guid = 'folder_guid_'), -- positon, dateAdded, lastModified, guid 0, 1, 1, 'bookmarkguid')", ]) .expect("inserts should work"); // Delete the folder - the bookmark should cascade delete. conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'folder_guid_';", []) .expect("should work"); // folder should be gone. assert_eq!( select_simple_int( &conn, "SELECT count(*) FROM moz_bookmarks WHERE guid = 'folder_guid_'" ), 0 ); // bookmark should be gone. assert_eq!( select_simple_int( &conn, "SELECT count(*) FROM moz_bookmarks WHERE guid = 'bookmarkguid';" ), 0 ); // Place should also be gone as bookmark url had no visits. assert_eq!( select_simple_int( &conn, "SELECT COUNT(*) from moz_places WHERE guid = 'place_guid__';" ), 0 ); } #[test] fn test_bookmark_auto_deletes_place_remains() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute_all(&[ // A folder to hold a bookmark. "INSERT INTO moz_bookmarks (type, parent, position, dateAdded, lastModified, guid) VALUES (3, 1, 0, 1, 1, 'folder_guid_')", // A place for the bookmark. "INSERT INTO moz_places (guid, url, url_hash, foreign_count) -- here we pretend it has a foreign count. VALUES ('place_guid__', 'http://example.com/', hash('http://example.com/'), 1)", // The bookmark. "INSERT INTO moz_bookmarks (fk, type, parent, position, dateAdded, lastModified, guid) VALUES --fk, type (last_insert_rowid(), 1, -- parent (SELECT id FROM moz_bookmarks WHERE guid = 'folder_guid_'), -- positon, dateAdded, lastModified, guid 0, 1, 1, 'bookmarkguid')", ]) .expect("inserts should work"); // Delete the folder - the bookmark should cascade delete. conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'folder_guid_';", []) .expect("should work"); // folder should be gone. assert_eq!( select_simple_int( &conn, "SELECT count(*) FROM moz_bookmarks WHERE guid = 'folder_guid_'" ), 0 ); // bookmark should be gone. assert_eq!( select_simple_int( &conn, "SELECT count(*) FROM moz_bookmarks WHERE guid = 'bookmarkguid';" ), 0 ); // Place should remain as we pretended it has a foreign reference. assert_eq!( select_simple_int( &conn, "SELECT COUNT(*) from moz_places WHERE guid = 'place_guid__';" ), 1 ); } #[test] fn test_bookmark_tombstone_auto_created() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute( &format!( "INSERT INTO moz_bookmarks (syncStatus, type, parent, position, dateAdded, lastModified, guid) VALUES ({}, 3, 1, 0, 1, 1, 'bookmarkguid')", SyncStatus::Normal as u8 ), [], ) .expect("should insert regular bookmark folder"); conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'bookmarkguid'", []) .expect("should delete"); // should have a tombstone. assert_eq!( select_simple_int(&conn, "SELECT COUNT(*) from moz_bookmarks_deleted"), 1 ); conn.execute("DELETE from moz_bookmarks_deleted", []) .expect("should delete"); conn.execute( &format!( "INSERT INTO moz_bookmarks (syncStatus, type, parent, position, dateAdded, lastModified, guid) VALUES ({}, 3, 1, 0, 1, 1, 'bookmarkguid')", SyncStatus::New as u8 ), [], ) .expect("should insert regular bookmark folder"); conn.execute("DELETE FROM moz_bookmarks WHERE guid = 'bookmarkguid'", []) .expect("should delete"); // should not have a tombstone as syncStatus is new. assert_eq!( select_simple_int(&conn, "SELECT COUNT(*) from moz_bookmarks_deleted"), 0 ); } #[test] fn test_bookmark_tombstone_auto_deletes() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); conn.execute( "INSERT into moz_bookmarks_deleted VALUES ('bookmarkguid', 1)", [], ) .expect("should insert tombstone"); assert_eq!( select_simple_int(&conn, "SELECT COUNT(*) from moz_bookmarks_deleted"), 1 ); // create a bookmark with the same guid. conn.execute( "INSERT INTO moz_bookmarks (type, parent, position, dateAdded, lastModified, guid) VALUES (3, 1, 0, 1, 1, 'bookmarkguid')", [], ) .expect("should insert regular bookmark folder"); // tombstone should have vanished. assert_eq!( select_simple_int(&conn, "SELECT COUNT(*) from moz_bookmarks_deleted"), 0 ); } #[test] fn test_bookmark_tombstone_auto_deletes_on_update() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); // check updates do the right thing. conn.execute( "INSERT into moz_bookmarks_deleted VALUES ('bookmarkguid', 1)", [], ) .expect("should insert tombstone"); // create a bookmark with a different guid. conn.execute( "INSERT INTO moz_bookmarks (type, parent, position, dateAdded, lastModified, guid) VALUES (3, 1, 0, 1, 1, 'fake_guid___')", [], ) .expect("should insert regular bookmark folder"); // tombstone should remain. assert_eq!( select_simple_int(&conn, "SELECT COUNT(*) from moz_bookmarks_deleted"), 1 ); // update guid - should fail as we have a trigger with RAISEs. conn.execute( "UPDATE moz_bookmarks SET guid = 'bookmarkguid' WHERE guid = 'fake_guid___'", [], ) .expect_err("changing the guid should fail"); } #[test] fn test_origin_triggers_simple_removal() { let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("no memory db"); let guid = SyncGuid::random(); let url = String::from(Url::parse("http://example.com").expect("valid url")); conn.execute( "INSERT INTO moz_places (guid, url, url_hash) VALUES (:guid, :url, hash(:url))", rusqlite::named_params! { ":guid": &guid, ":url": &url, }, ) .expect("should work"); // origins are maintained via triggers, so make sure they are done. crate::storage::delete_pending_temp_tables(&conn).expect("should work"); // We should have inserted the origin. assert_eq!( select_simple_int( &conn, "SELECT count(*) FROM moz_origins WHERE host = 'example.com'" ), 1 ); // delete the place, ensure triggers have run, and check origin has gone. conn.execute("DELETE FROM moz_places", []) .expect("should work"); crate::storage::delete_pending_temp_tables(&conn).expect("should work"); assert_eq!( select_simple_int(&conn, "SELECT count(*) FROM moz_origins"), 0 ); } const CREATE_V15_DB: &str = include_str!("../../sql/tests/create_v15_db.sql"); #[test] fn test_upgrade_schema_15_16() { let db_file = MigratedDatabaseFile::new(PlacesInitializer::new_for_test(), CREATE_V15_DB); db_file.upgrade_to(16); let db = db_file.open(); // Test the unknownFields column was added assert_eq!( db.conn_ext_query_one::("SELECT type FROM pragma_table_info('moz_bookmarks_synced') WHERE name = 'unknownFields'").unwrap(), "TEXT" ); } #[test] fn test_gh5464() { // Test the gh-5464 error case: A user with the `v16` schema, but with `user_version` set // to 15 let db_file = MigratedDatabaseFile::new(PlacesInitializer::new_for_test(), CREATE_V15_DB); db_file.upgrade_to(16); let db = db_file.open(); db.execute("PRAGMA user_version=15", []).unwrap(); drop(db); db_file.upgrade_to(16); } const CREATE_V17_DB: &str = include_str!("../../sql/tests/create_v17_db.sql"); #[test] fn test_upgrade_schema_17_18() { let db_file = MigratedDatabaseFile::new(PlacesInitializer::new_for_test(), CREATE_V17_DB); db_file.upgrade_to(18); let db = db_file.open(); let sql = db .conn_ext_query_one::( "SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'moz_bookmarks'", ) .expect("should retrieve CREATE TABLE statement"); assert!(!sql.contains("CHECK")); assert!(!sql.contains("FOREIGN KEY(parent)")); // Make sure that we didn't corrupt the database. let integrity_ok = db .query_row("PRAGMA integrity_check", [], |row| { Ok(row.get::<_, String>(0)? == "ok") }) .expect("should perform integrity check"); assert!(integrity_ok); let foreign_keys_ok = db .prepare("PRAGMA foreign_key_check") .and_then(|mut statement| Ok(statement.query([])?.next()?.is_none())) .expect("should perform foreign key check"); assert!(foreign_keys_ok); // ...And that we can read everything we inserted. #[derive(Eq, PartialEq, Debug)] struct BookmarkRow { id: i64, type_: i64, parent: Option, fk: Option, guid: String, } let rows = db .query_rows_and_then( "SELECT id, type, parent, fk, guid FROM moz_bookmarks ORDER BY id", [], |row| -> rusqlite::Result<_> { Ok(BookmarkRow { id: row.get("id")?, type_: row.get("type")?, parent: row.get("parent")?, fk: row.get("fk")?, guid: row.get("guid")?, }) }, ) .expect("should query all bookmark rows"); assert_eq!( rows, &[ BookmarkRow { id: 1, type_: 2, parent: None, fk: None, guid: "root________".into() }, BookmarkRow { id: 2, type_: 2, parent: Some(1), fk: None, guid: "folder_guid_".into() }, BookmarkRow { id: 3, type_: 1, parent: Some(2), fk: Some(1), guid: "bmk_guid____".into() }, BookmarkRow { id: 4, type_: 3, parent: Some(2), fk: None, guid: "sep_guid____".into() } ] ); } #[test] fn test_all_upgrades() { // Test the migration process in general: open a fresh DB and a DB that's gone through the migration // process. Check that the schemas match. let fresh_db = PlacesDb::open_in_memory(ConnectionType::ReadWrite).unwrap(); let db_file = MigratedDatabaseFile::new(PlacesInitializer::new_for_test(), CREATE_V15_DB); db_file.run_all_upgrades(); let upgraded_db = db_file.open(); assert_eq!( fresh_db .conn_ext_query_one::("PRAGMA user_version") .unwrap(), upgraded_db .conn_ext_query_one::("PRAGMA user_version") .unwrap(), ); let all_tables = [ "moz_places", "moz_places_tombstones", "moz_places_stale_frecencies", "moz_historyvisits", "moz_historyvisit_tombstones", "moz_inputhistory", "moz_bookmarks", "moz_bookmarks_deleted", "moz_origins", "moz_meta", "moz_tags", "moz_tags_relation", "moz_bookmarks_synced", "moz_bookmarks_synced_structure", "moz_bookmarks_synced_tag_relation", "moz_keywords", "moz_places_metadata", "moz_places_metadata_search_queries", ]; #[derive(Debug, Ord, PartialOrd, Eq, PartialEq)] struct ColumnInfo { name: String, type_: String, not_null: bool, default_value: Option, pk: bool, } fn get_table_column_info(conn: &Connection, table_name: &str) -> BTreeSet { let mut stmt = conn .prepare("SELECT name, type, `notnull`, dflt_value, pk FROM pragma_table_info(?)") .unwrap(); stmt.query_map((table_name,), |row| { Ok(ColumnInfo { name: row.get(0)?, type_: row.get(1)?, not_null: row.get(2)?, default_value: row.get(3)?, pk: row.get(4)?, }) }) .unwrap() .collect::>>() .unwrap() } for table_name in all_tables { assert_eq!( get_table_column_info(&upgraded_db, table_name), get_table_column_info(&fresh_db, table_name), ); } } }