autofill/db/

schema.rs

/* 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::db::sql_fns;
use crate::sync::address::name_utils::{join_name_parts, NameParts};
use error_support::debug;
use rusqlite::{functions::FunctionFlags, Connection, Transaction};
use sql_support::open_database::{ConnectionInitializer, Error, Result};

pub const ADDRESS_COMMON_COLS: &str = "
    guid,
    name,
    organization,
    street_address,
    address_level3,
    address_level2,
    address_level1,
    postal_code,
    country,
    tel,
    email,
    time_created,
    time_last_used,
    time_last_modified,
    times_used";

pub const ADDRESS_COMMON_VALS: &str = "
    :guid,
    :name,
    :organization,
    :street_address,
    :address_level3,
    :address_level2,
    :address_level1,
    :postal_code,
    :country,
    :tel,
    :email,
    :time_created,
    :time_last_used,
    :time_last_modified,
    :times_used";

pub const CREDIT_CARD_COMMON_COLS: &str = "
    guid,
    cc_name,
    cc_number_enc,
    cc_number_last_4,
    cc_exp_month,
    cc_exp_year,
    cc_type,
    time_created,
    time_last_used,
    time_last_modified,
    times_used";

pub const CREDIT_CARD_COMMON_VALS: &str = "
    :guid,
    :cc_name,
    :cc_number_enc,
    :cc_number_last_4,
    :cc_exp_month,
    :cc_exp_year,
    :cc_type,
    :time_created,
    :time_last_used,
    :time_last_modified,
    :times_used";

pub const PASSPORT_COMMON_COLS: &str = "
    guid,
    name,
    country,
    passport_number,
    issue_date_month,
    issue_date_day,
    issue_date_year,
    expiry_date_month,
    expiry_date_day,
    expiry_date_year,
    time_created,
    time_last_used,
    time_last_modified,
    times_used";

pub const PASSPORT_COMMON_VALS: &str = "
    :guid,
    :name,
    :country,
    :passport_number,
    :issue_date_month,
    :issue_date_day,
    :issue_date_year,
    :expiry_date_month,
    :expiry_date_day,
    :expiry_date_year,
    :time_created,
    :time_last_used,
    :time_last_modified,
    :times_used";

const CREATE_SHARED_SCHEMA_SQL: &str = include_str!("../../sql/create_shared_schema.sql");
const CREATE_SHARED_TRIGGERS_SQL: &str = include_str!("../../sql/create_shared_triggers.sql");
const CREATE_SYNC_TEMP_TABLES_SQL: &str = include_str!("../../sql/create_sync_temp_tables.sql");

pub struct AutofillConnectionInitializer;

impl ConnectionInitializer for AutofillConnectionInitializer {
    const NAME: &'static str = "autofill db";
    const END_VERSION: u32 = 5;

    fn prepare(&self, conn: &Connection, _db_empty: bool) -> Result<()> {
        define_functions(conn)?;

        // Set up default SQLite pragmas to truncate the -wal file.
        sql_support::setup_sqlite_defaults(conn)?;

        let initial_pragmas = "
            -- autofill does not use foreign keys at present but this is probably a good pragma to set
            PRAGMA foreign_keys = ON;
        ";
        conn.execute_batch(initial_pragmas)?;

        conn.set_prepared_statement_cache_capacity(128);
        Ok(())
    }

    fn init(&self, db: &Transaction<'_>) -> Result<()> {
        Ok(db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?)
    }

    fn upgrade_from(&self, db: &Transaction<'_>, version: u32) -> Result<()> {
        match version {
            // AutofillDB has a slightly strange version history, so we start on v0.  See
            // upgrade_from_v0() for more details.
            0 => upgrade_from_v0(db),
            1 => upgrade_from_v1(db),
            2 => upgrade_from_v2(db),
            3 => upgrade_from_v3(db),
            4 => upgrade_from_v4(db),
            _ => Err(Error::IncompatibleVersion(version)),
        }
    }

    fn finish(&self, db: &Connection) -> Result<()> {
        Ok(db.execute_batch(CREATE_SHARED_TRIGGERS_SQL)?)
    }
}

fn define_functions(c: &Connection) -> Result<()> {
    c.create_scalar_function(
        "generate_guid",
        0,
        FunctionFlags::SQLITE_UTF8,
        sql_fns::generate_guid,
    )?;
    c.create_scalar_function("now", 0, FunctionFlags::SQLITE_UTF8, sql_fns::now)?;

    Ok(())
}

fn upgrade_from_v0(db: &Connection) -> Result<()> {
    // This is a bit painful - there are (probably 3) databases out there
    // that have a schema of 0.
    // These databases have a `cc_number` but we need them to have a
    // `cc_number_enc` and `cc_number_last_4`.
    // This was so very early in the Fenix nightly cycle, and before any
    // real UI existed to create cards, so we don't bother trying to
    // migrate them, we just drop the table and re-create it with the
    // correct schema.
    db.execute_batch(
        "
        DROP TABLE IF EXISTS credit_cards_data;
        CREATE TABLE credit_cards_data (
            guid                TEXT NOT NULL PRIMARY KEY CHECK(length(guid) != 0),
            cc_name             TEXT NOT NULL,
            cc_number_enc       TEXT NOT NULL CHECK(length(cc_number_enc) > 20),
            cc_number_last_4    TEXT NOT NULL CHECK(length(cc_number_last_4) <= 4),
            cc_exp_month        INTEGER,
            cc_exp_year         INTEGER,
            cc_type             TEXT NOT NULL,
            time_created        INTEGER NOT NULL,
            time_last_used      INTEGER,
            time_last_modified  INTEGER NOT NULL,
            times_used          INTEGER NOT NULL,
            sync_change_counter INTEGER NOT NULL
        );
        ",
    )?;
    Ok(())
}

fn upgrade_from_v1(db: &Connection) -> Result<()> {
    // Alter cc_number_enc using the 12-step generalized procedure described here:
    // https://sqlite.org/lang_altertable.html
    // Note that all our triggers are TEMP triggers so do not exist when
    // this is called (except possibly by tests which do things like
    // downgrade the version after they are created etc.)
    db.execute_batch(
        "
        CREATE TABLE new_credit_cards_data (
            guid                TEXT NOT NULL PRIMARY KEY CHECK(length(guid) != 0),
            cc_name             TEXT NOT NULL,
            cc_number_enc       TEXT NOT NULL CHECK(length(cc_number_enc) > 20 OR cc_number_enc == ''),
            cc_number_last_4    TEXT NOT NULL CHECK(length(cc_number_last_4) <= 4),
            cc_exp_month        INTEGER,
            cc_exp_year         INTEGER,
            cc_type             TEXT NOT NULL,
            time_created        INTEGER NOT NULL,
            time_last_used      INTEGER,
            time_last_modified  INTEGER NOT NULL,
            times_used          INTEGER NOT NULL,
            sync_change_counter INTEGER NOT NULL
        );
        INSERT INTO new_credit_cards_data(guid, cc_name, cc_number_enc, cc_number_last_4, cc_exp_month,
        cc_exp_year, cc_type, time_created, time_last_used, time_last_modified, times_used,
        sync_change_counter)
        SELECT guid, cc_name, cc_number_enc, cc_number_last_4, cc_exp_month, cc_exp_year, cc_type,
            time_created, time_last_used, time_last_modified, times_used, sync_change_counter
        FROM credit_cards_data;
        DROP TABLE credit_cards_data;
        ALTER TABLE new_credit_cards_data RENAME to credit_cards_data;
        ")?;
    Ok(())
}

fn upgrade_from_v2(db: &Connection) -> Result<()> {
    db.execute_batch("ALTER TABLE addresses_data ADD COLUMN name TEXT NOT NULL DEFAULT ''")?;

    let mut stmt =
        db.prepare("SELECT guid, given_name, additional_name, family_name FROM addresses_data")?;
    let rows = stmt.query_map([], |row| {
        Ok((
            row.get::<_, String>("guid")?,
            row.get::<_, String>("given_name")?,
            row.get::<_, String>("additional_name")?,
            row.get::<_, String>("family_name")?,
        ))
    })?;

    for row in rows {
        let (guid, given, middle, family) = row?;
        let full_name = join_name_parts(&NameParts {
            given,
            middle,
            family,
        });

        db.execute(
            "UPDATE addresses_data SET name = (:name) WHERE guid = (:guid)",
            rusqlite::named_params! { ":name": full_name, ":guid": guid},
        )?;
    }

    db.execute_batch(
        "
        ALTER TABLE addresses_data DROP COLUMN given_name;
        ALTER TABLE addresses_data DROP COLUMN additional_name;
        ALTER TABLE addresses_data DROP COLUMN family_name;
        ",
    )?;

    Ok(())
}

fn upgrade_from_v3(db: &Connection) -> Result<()> {
    let migration_string: &str = include_str!("../../sql/migrations/v4_migration.sql");
    db.execute_batch(migration_string)?;
    Ok(())
}

fn upgrade_from_v4(db: &Connection) -> Result<()> {
    // v4 -> v5 only adds new tables (the passports_* tables), so we can just
    // re-run the shared schema, which uses `CREATE TABLE IF NOT EXISTS`. This
    // is the approach used by most other components and avoids duplicating the
    // table definitions in a separate migration file.
    db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?;
    Ok(())
}

pub fn create_empty_sync_temp_tables(db: &Connection) -> Result<()> {
    debug!("Initializing sync temp tables");
    db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)?;
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::addresses::get_address;
    use crate::db::credit_cards::get_credit_card;
    use crate::db::test::new_mem_db;
    use crate::db::AutofillDb;
    use sql_support::open_database::test_utils::MigratedDatabaseFile;
    use sync_guid::Guid;
    use types::Timestamp;

    const CREATE_V0_DB: &str = include_str!("../../sql/tests/create_v0_db.sql");
    const CREATE_V1_DB: &str = include_str!("../../sql/tests/create_v1_db.sql");
    const CREATE_V2_DB: &str = include_str!("../../sql/tests/create_v2_db.sql");
    const CREATE_V3_DB: &str = include_str!("../../sql/tests/create_v3_db.sql");
    const CREATE_V4_DB: &str = include_str!("../../sql/tests/create_v4_db.sql");

    #[test]
    fn test_wal_size_is_bounded() {
        // A memory database has no -wal file, so open a real one.
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, "");
        let db = AutofillDb::new(&db_file.path).expect("should open the database");

        let journal_mode: String = db
            .query_row("PRAGMA journal_mode", [], |row| row.get(0))
            .unwrap();
        assert_eq!(journal_mode, "wal");

        let autocheckpoint: i64 = db
            .query_row("PRAGMA wal_autocheckpoint", [], |row| row.get(0))
            .unwrap();
        assert!(autocheckpoint > 0, "auto-checkpointing is disabled");

        let journal_size_limit: i64 = db
            .query_row("PRAGMA journal_size_limit", [], |row| row.get(0))
            .unwrap();
        assert!(
            journal_size_limit > 0,
            "journal_size_limit is {journal_size_limit}, so the -wal file is never truncated"
        );
    }

    #[test]
    fn test_create_schema_twice() {
        let db = new_mem_db();
        db.execute_batch(CREATE_SHARED_SCHEMA_SQL)
            .expect("should allow running main schema creation twice");
        // sync tables aren't created by default, so do it twice here.
        db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)
            .expect("should allow running sync temp tables first time");
        db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)
            .expect("should allow running sync temp tables second time");
    }

    #[test]
    fn test_all_upgrades() {
        // Let's start with v1, since the v0 upgrade deletes data
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V1_DB);
        db_file.run_all_upgrades();
        let conn = db_file.open();

        // Test that the data made it through
        let cc = get_credit_card(&conn, &Guid::new("A")).unwrap();
        assert_eq!(cc.guid, "A");
        assert_eq!(cc.cc_name, "Jane Doe");
        assert_eq!(cc.cc_number_enc, "012345678901234567890");
        assert_eq!(cc.cc_number_last_4, "1234");
        assert_eq!(cc.cc_exp_month, 1);
        assert_eq!(cc.cc_exp_year, 2020);
        assert_eq!(cc.cc_type, "visa");
        assert_eq!(cc.metadata.time_created, Timestamp(0));
        assert_eq!(cc.metadata.time_last_used, Timestamp(1));
        assert_eq!(cc.metadata.time_last_modified, Timestamp(2));
        assert_eq!(cc.metadata.times_used, 3);
        assert_eq!(cc.metadata.sync_change_counter, 0);

        let address = get_address(&conn, &Guid::new("A")).unwrap();
        assert_eq!(address.guid, "A");
        assert_eq!(address.name, "Jane JaneDoe2 Doe");
        assert_eq!(address.organization, "Mozilla");
        assert_eq!(address.street_address, "123 Maple lane");
        assert_eq!(address.address_level3, "Shelbyville");
        assert_eq!(address.address_level2, "Springfield");
        assert_eq!(address.address_level1, "MA");
        assert_eq!(address.postal_code, "12345");
        assert_eq!(address.country, "US");
        assert_eq!(address.tel, "01-234-567-8000");
        assert_eq!(address.email, "jane@hotmail.com");
        assert_eq!(address.metadata.time_created, Timestamp(0));
        assert_eq!(address.metadata.time_last_used, Timestamp(1));
        assert_eq!(address.metadata.time_last_modified, Timestamp(2));
        assert_eq!(address.metadata.times_used, 3);
        assert_eq!(address.metadata.sync_change_counter, 0);
    }

    #[test]
    fn test_upgrade_version_0() {
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V0_DB);
        // Just to test what we think we are testing, select a field that
        // doesn't exist now but will after we recreate the table.
        let select_cc_number_enc = "SELECT cc_number_enc from credit_cards_data";
        db_file
            .open()
            .execute_batch(select_cc_number_enc)
            .expect_err("select should fail due to bad field name");

        db_file.upgrade_to(1);

        db_file
            .open()
            .execute_batch(select_cc_number_enc)
            .expect("select should now work");
    }

    #[test]
    fn test_upgrade_version_1() {
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V1_DB);

        db_file.upgrade_to(2);
        let db = db_file.open();

        // Test the upgraded check constraint
        db.execute("UPDATE credit_cards_data SET cc_number_enc=''", [])
            .expect("blank cc_number_enc should be valid");
        db.execute("UPDATE credit_cards_data SET cc_number_enc='x'", [])
            .expect_err("cc_number_enc should be invalid");
    }

    #[test]
    fn test_upgrade_version_2() {
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V2_DB);
        let db = db_file.open();

        db.execute_batch("SELECT name from addresses_data")
            .expect_err("select should fail");
        db.execute_batch("SELECT street_address from addresses_data")
            .expect("street_address should work");
        db.execute_batch("SELECT additional_name from addresses_data")
            .expect("additional_name should work");
        db.execute_batch("SELECT family_name from addresses_data")
            .expect("family_name should work");

        db_file.upgrade_to(3);

        db.execute_batch("SELECT name from addresses_data")
            .expect("select name should now work");
        db.execute_batch("SELECT given_name from addresses_data")
            .expect_err("given_name should fail");
        db.execute_batch("SELECT additional_name from addresses_data")
            .expect_err("additional_name should fail");
        db.execute_batch("SELECT family_name from addresses_data")
            .expect_err("family_name should fail");

        let mut address = get_address(&db, &Guid::new("A")).unwrap();
        assert_eq!(address.guid, "A");
        assert_eq!(address.name, "Jane John Doe");

        address = get_address(&db, &Guid::new("B")).unwrap();
        assert_eq!(address.guid, "B");

        // Record B has no given_name, additional_name or family_name, so name should also be empty.
        assert_eq!(address.name, "");
    }

    #[test]
    fn test_upgrade_version_3() {
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V3_DB);
        let db = db_file.open();

        // Assert that the existing addresses have the fully qualified address_level1 name.
        let mut address = get_address(&db, &Guid::new("A")).unwrap();
        assert_eq!(address.guid, "A");
        assert_eq!(address.name, "Jane John Doe");
        assert_eq!(address.address_level1, "Massachusetts");

        address = get_address(&db, &Guid::new("B")).unwrap();
        assert_eq!(address.guid, "B");
        assert_eq!(address.address_level1, "Ontario");

        db_file.upgrade_to(4);

        // Assert that the addresses have been migrated to use the subregion keys.
        address = get_address(&db, &Guid::new("A")).unwrap();
        assert_eq!(address.guid, "A");
        assert_eq!(address.name, "Jane John Doe");
        assert_eq!(address.address_level1, "MA");

        address = get_address(&db, &Guid::new("B")).unwrap();
        assert_eq!(address.guid, "B");
        assert_eq!(address.address_level1, "ON");
    }

    #[test]
    fn test_upgrade_version_4() {
        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V4_DB);
        let db = db_file.open();

        // passports_data must not exist yet at v4.
        db.execute_batch("SELECT guid FROM passports_data")
            .expect_err("passports_data should not exist at v4");

        db_file.upgrade_to(5);

        // After upgrading to v5 the table exists.
        db.execute_batch("SELECT guid FROM passports_data")
            .expect("passports_data should exist at v5");
    }
}