autofill/db/
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
5use crate::db::sql_fns;
6use crate::sync::address::name_utils::{join_name_parts, NameParts};
7use error_support::debug;
8use rusqlite::{functions::FunctionFlags, Connection, Transaction};
9use sql_support::open_database::{ConnectionInitializer, Error, Result};
10
11pub const ADDRESS_COMMON_COLS: &str = "
12    guid,
13    name,
14    organization,
15    street_address,
16    address_level3,
17    address_level2,
18    address_level1,
19    postal_code,
20    country,
21    tel,
22    email,
23    time_created,
24    time_last_used,
25    time_last_modified,
26    times_used";
27
28pub const ADDRESS_COMMON_VALS: &str = "
29    :guid,
30    :name,
31    :organization,
32    :street_address,
33    :address_level3,
34    :address_level2,
35    :address_level1,
36    :postal_code,
37    :country,
38    :tel,
39    :email,
40    :time_created,
41    :time_last_used,
42    :time_last_modified,
43    :times_used";
44
45pub const CREDIT_CARD_COMMON_COLS: &str = "
46    guid,
47    cc_name,
48    cc_number_enc,
49    cc_number_last_4,
50    cc_exp_month,
51    cc_exp_year,
52    cc_type,
53    time_created,
54    time_last_used,
55    time_last_modified,
56    times_used";
57
58pub const CREDIT_CARD_COMMON_VALS: &str = "
59    :guid,
60    :cc_name,
61    :cc_number_enc,
62    :cc_number_last_4,
63    :cc_exp_month,
64    :cc_exp_year,
65    :cc_type,
66    :time_created,
67    :time_last_used,
68    :time_last_modified,
69    :times_used";
70
71pub const PASSPORT_COMMON_COLS: &str = "
72    guid,
73    name,
74    country,
75    passport_number,
76    issue_date_month,
77    issue_date_day,
78    issue_date_year,
79    expiry_date_month,
80    expiry_date_day,
81    expiry_date_year,
82    time_created,
83    time_last_used,
84    time_last_modified,
85    times_used";
86
87pub const PASSPORT_COMMON_VALS: &str = "
88    :guid,
89    :name,
90    :country,
91    :passport_number,
92    :issue_date_month,
93    :issue_date_day,
94    :issue_date_year,
95    :expiry_date_month,
96    :expiry_date_day,
97    :expiry_date_year,
98    :time_created,
99    :time_last_used,
100    :time_last_modified,
101    :times_used";
102
103const CREATE_SHARED_SCHEMA_SQL: &str = include_str!("../../sql/create_shared_schema.sql");
104const CREATE_SHARED_TRIGGERS_SQL: &str = include_str!("../../sql/create_shared_triggers.sql");
105const CREATE_SYNC_TEMP_TABLES_SQL: &str = include_str!("../../sql/create_sync_temp_tables.sql");
106
107pub struct AutofillConnectionInitializer;
108
109impl ConnectionInitializer for AutofillConnectionInitializer {
110    const NAME: &'static str = "autofill db";
111    const END_VERSION: u32 = 5;
112
113    fn prepare(&self, conn: &Connection, _db_empty: bool) -> Result<()> {
114        define_functions(conn)?;
115
116        let initial_pragmas = "
117            -- use in-memory storage
118            PRAGMA temp_store = 2;
119            -- use write-ahead logging
120            PRAGMA journal_mode = WAL;
121            -- autofill does not use foreign keys at present but this is probably a good pragma to set
122            PRAGMA foreign_keys = ON;
123        ";
124        conn.execute_batch(initial_pragmas)?;
125
126        conn.set_prepared_statement_cache_capacity(128);
127        Ok(())
128    }
129
130    fn init(&self, db: &Transaction<'_>) -> Result<()> {
131        Ok(db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?)
132    }
133
134    fn upgrade_from(&self, db: &Transaction<'_>, version: u32) -> Result<()> {
135        match version {
136            // AutofillDB has a slightly strange version history, so we start on v0.  See
137            // upgrade_from_v0() for more details.
138            0 => upgrade_from_v0(db),
139            1 => upgrade_from_v1(db),
140            2 => upgrade_from_v2(db),
141            3 => upgrade_from_v3(db),
142            4 => upgrade_from_v4(db),
143            _ => Err(Error::IncompatibleVersion(version)),
144        }
145    }
146
147    fn finish(&self, db: &Connection) -> Result<()> {
148        Ok(db.execute_batch(CREATE_SHARED_TRIGGERS_SQL)?)
149    }
150}
151
152fn define_functions(c: &Connection) -> Result<()> {
153    c.create_scalar_function(
154        "generate_guid",
155        0,
156        FunctionFlags::SQLITE_UTF8,
157        sql_fns::generate_guid,
158    )?;
159    c.create_scalar_function("now", 0, FunctionFlags::SQLITE_UTF8, sql_fns::now)?;
160
161    Ok(())
162}
163
164fn upgrade_from_v0(db: &Connection) -> Result<()> {
165    // This is a bit painful - there are (probably 3) databases out there
166    // that have a schema of 0.
167    // These databases have a `cc_number` but we need them to have a
168    // `cc_number_enc` and `cc_number_last_4`.
169    // This was so very early in the Fenix nightly cycle, and before any
170    // real UI existed to create cards, so we don't bother trying to
171    // migrate them, we just drop the table and re-create it with the
172    // correct schema.
173    db.execute_batch(
174        "
175        DROP TABLE IF EXISTS credit_cards_data;
176        CREATE TABLE credit_cards_data (
177            guid                TEXT NOT NULL PRIMARY KEY CHECK(length(guid) != 0),
178            cc_name             TEXT NOT NULL,
179            cc_number_enc       TEXT NOT NULL CHECK(length(cc_number_enc) > 20),
180            cc_number_last_4    TEXT NOT NULL CHECK(length(cc_number_last_4) <= 4),
181            cc_exp_month        INTEGER,
182            cc_exp_year         INTEGER,
183            cc_type             TEXT NOT NULL,
184            time_created        INTEGER NOT NULL,
185            time_last_used      INTEGER,
186            time_last_modified  INTEGER NOT NULL,
187            times_used          INTEGER NOT NULL,
188            sync_change_counter INTEGER NOT NULL
189        );
190        ",
191    )?;
192    Ok(())
193}
194
195fn upgrade_from_v1(db: &Connection) -> Result<()> {
196    // Alter cc_number_enc using the 12-step generalized procedure described here:
197    // https://sqlite.org/lang_altertable.html
198    // Note that all our triggers are TEMP triggers so do not exist when
199    // this is called (except possibly by tests which do things like
200    // downgrade the version after they are created etc.)
201    db.execute_batch(
202        "
203        CREATE TABLE new_credit_cards_data (
204            guid                TEXT NOT NULL PRIMARY KEY CHECK(length(guid) != 0),
205            cc_name             TEXT NOT NULL,
206            cc_number_enc       TEXT NOT NULL CHECK(length(cc_number_enc) > 20 OR cc_number_enc == ''),
207            cc_number_last_4    TEXT NOT NULL CHECK(length(cc_number_last_4) <= 4),
208            cc_exp_month        INTEGER,
209            cc_exp_year         INTEGER,
210            cc_type             TEXT NOT NULL,
211            time_created        INTEGER NOT NULL,
212            time_last_used      INTEGER,
213            time_last_modified  INTEGER NOT NULL,
214            times_used          INTEGER NOT NULL,
215            sync_change_counter INTEGER NOT NULL
216        );
217        INSERT INTO new_credit_cards_data(guid, cc_name, cc_number_enc, cc_number_last_4, cc_exp_month,
218        cc_exp_year, cc_type, time_created, time_last_used, time_last_modified, times_used,
219        sync_change_counter)
220        SELECT guid, cc_name, cc_number_enc, cc_number_last_4, cc_exp_month, cc_exp_year, cc_type,
221            time_created, time_last_used, time_last_modified, times_used, sync_change_counter
222        FROM credit_cards_data;
223        DROP TABLE credit_cards_data;
224        ALTER TABLE new_credit_cards_data RENAME to credit_cards_data;
225        ")?;
226    Ok(())
227}
228
229fn upgrade_from_v2(db: &Connection) -> Result<()> {
230    db.execute_batch("ALTER TABLE addresses_data ADD COLUMN name TEXT NOT NULL DEFAULT ''")?;
231
232    let mut stmt =
233        db.prepare("SELECT guid, given_name, additional_name, family_name FROM addresses_data")?;
234    let rows = stmt.query_map([], |row| {
235        Ok((
236            row.get::<_, String>("guid")?,
237            row.get::<_, String>("given_name")?,
238            row.get::<_, String>("additional_name")?,
239            row.get::<_, String>("family_name")?,
240        ))
241    })?;
242
243    for row in rows {
244        let (guid, given, middle, family) = row?;
245        let full_name = join_name_parts(&NameParts {
246            given,
247            middle,
248            family,
249        });
250
251        db.execute(
252            "UPDATE addresses_data SET name = (:name) WHERE guid = (:guid)",
253            rusqlite::named_params! { ":name": full_name, ":guid": guid},
254        )?;
255    }
256
257    db.execute_batch(
258        "
259        ALTER TABLE addresses_data DROP COLUMN given_name;
260        ALTER TABLE addresses_data DROP COLUMN additional_name;
261        ALTER TABLE addresses_data DROP COLUMN family_name;
262        ",
263    )?;
264
265    Ok(())
266}
267
268fn upgrade_from_v3(db: &Connection) -> Result<()> {
269    let migration_string: &str = include_str!("../../sql/migrations/v4_migration.sql");
270    db.execute_batch(migration_string)?;
271    Ok(())
272}
273
274fn upgrade_from_v4(db: &Connection) -> Result<()> {
275    // v4 -> v5 only adds new tables (the passports_* tables), so we can just
276    // re-run the shared schema, which uses `CREATE TABLE IF NOT EXISTS`. This
277    // is the approach used by most other components and avoids duplicating the
278    // table definitions in a separate migration file.
279    db.execute_batch(CREATE_SHARED_SCHEMA_SQL)?;
280    Ok(())
281}
282
283pub fn create_empty_sync_temp_tables(db: &Connection) -> Result<()> {
284    debug!("Initializing sync temp tables");
285    db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)?;
286    Ok(())
287}
288
289#[cfg(test)]
290mod tests {
291    use super::*;
292    use crate::db::addresses::get_address;
293    use crate::db::credit_cards::get_credit_card;
294    use crate::db::test::new_mem_db;
295    use sql_support::open_database::test_utils::MigratedDatabaseFile;
296    use sync_guid::Guid;
297    use types::Timestamp;
298
299    const CREATE_V0_DB: &str = include_str!("../../sql/tests/create_v0_db.sql");
300    const CREATE_V1_DB: &str = include_str!("../../sql/tests/create_v1_db.sql");
301    const CREATE_V2_DB: &str = include_str!("../../sql/tests/create_v2_db.sql");
302    const CREATE_V3_DB: &str = include_str!("../../sql/tests/create_v3_db.sql");
303    const CREATE_V4_DB: &str = include_str!("../../sql/tests/create_v4_db.sql");
304
305    #[test]
306    fn test_create_schema_twice() {
307        let db = new_mem_db();
308        db.execute_batch(CREATE_SHARED_SCHEMA_SQL)
309            .expect("should allow running main schema creation twice");
310        // sync tables aren't created by default, so do it twice here.
311        db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)
312            .expect("should allow running sync temp tables first time");
313        db.execute_batch(CREATE_SYNC_TEMP_TABLES_SQL)
314            .expect("should allow running sync temp tables second time");
315    }
316
317    #[test]
318    fn test_all_upgrades() {
319        // Let's start with v1, since the v0 upgrade deletes data
320        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V1_DB);
321        db_file.run_all_upgrades();
322        let conn = db_file.open();
323
324        // Test that the data made it through
325        let cc = get_credit_card(&conn, &Guid::new("A")).unwrap();
326        assert_eq!(cc.guid, "A");
327        assert_eq!(cc.cc_name, "Jane Doe");
328        assert_eq!(cc.cc_number_enc, "012345678901234567890");
329        assert_eq!(cc.cc_number_last_4, "1234");
330        assert_eq!(cc.cc_exp_month, 1);
331        assert_eq!(cc.cc_exp_year, 2020);
332        assert_eq!(cc.cc_type, "visa");
333        assert_eq!(cc.metadata.time_created, Timestamp(0));
334        assert_eq!(cc.metadata.time_last_used, Timestamp(1));
335        assert_eq!(cc.metadata.time_last_modified, Timestamp(2));
336        assert_eq!(cc.metadata.times_used, 3);
337        assert_eq!(cc.metadata.sync_change_counter, 0);
338
339        let address = get_address(&conn, &Guid::new("A")).unwrap();
340        assert_eq!(address.guid, "A");
341        assert_eq!(address.name, "Jane JaneDoe2 Doe");
342        assert_eq!(address.organization, "Mozilla");
343        assert_eq!(address.street_address, "123 Maple lane");
344        assert_eq!(address.address_level3, "Shelbyville");
345        assert_eq!(address.address_level2, "Springfield");
346        assert_eq!(address.address_level1, "MA");
347        assert_eq!(address.postal_code, "12345");
348        assert_eq!(address.country, "US");
349        assert_eq!(address.tel, "01-234-567-8000");
350        assert_eq!(address.email, "jane@hotmail.com");
351        assert_eq!(address.metadata.time_created, Timestamp(0));
352        assert_eq!(address.metadata.time_last_used, Timestamp(1));
353        assert_eq!(address.metadata.time_last_modified, Timestamp(2));
354        assert_eq!(address.metadata.times_used, 3);
355        assert_eq!(address.metadata.sync_change_counter, 0);
356    }
357
358    #[test]
359    fn test_upgrade_version_0() {
360        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V0_DB);
361        // Just to test what we think we are testing, select a field that
362        // doesn't exist now but will after we recreate the table.
363        let select_cc_number_enc = "SELECT cc_number_enc from credit_cards_data";
364        db_file
365            .open()
366            .execute_batch(select_cc_number_enc)
367            .expect_err("select should fail due to bad field name");
368
369        db_file.upgrade_to(1);
370
371        db_file
372            .open()
373            .execute_batch(select_cc_number_enc)
374            .expect("select should now work");
375    }
376
377    #[test]
378    fn test_upgrade_version_1() {
379        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V1_DB);
380
381        db_file.upgrade_to(2);
382        let db = db_file.open();
383
384        // Test the upgraded check constraint
385        db.execute("UPDATE credit_cards_data SET cc_number_enc=''", [])
386            .expect("blank cc_number_enc should be valid");
387        db.execute("UPDATE credit_cards_data SET cc_number_enc='x'", [])
388            .expect_err("cc_number_enc should be invalid");
389    }
390
391    #[test]
392    fn test_upgrade_version_2() {
393        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V2_DB);
394        let db = db_file.open();
395
396        db.execute_batch("SELECT name from addresses_data")
397            .expect_err("select should fail");
398        db.execute_batch("SELECT street_address from addresses_data")
399            .expect("street_address should work");
400        db.execute_batch("SELECT additional_name from addresses_data")
401            .expect("additional_name should work");
402        db.execute_batch("SELECT family_name from addresses_data")
403            .expect("family_name should work");
404
405        db_file.upgrade_to(3);
406
407        db.execute_batch("SELECT name from addresses_data")
408            .expect("select name should now work");
409        db.execute_batch("SELECT given_name from addresses_data")
410            .expect_err("given_name should fail");
411        db.execute_batch("SELECT additional_name from addresses_data")
412            .expect_err("additional_name should fail");
413        db.execute_batch("SELECT family_name from addresses_data")
414            .expect_err("family_name should fail");
415
416        let mut address = get_address(&db, &Guid::new("A")).unwrap();
417        assert_eq!(address.guid, "A");
418        assert_eq!(address.name, "Jane John Doe");
419
420        address = get_address(&db, &Guid::new("B")).unwrap();
421        assert_eq!(address.guid, "B");
422
423        // Record B has no given_name, additional_name or family_name, so name should also be empty.
424        assert_eq!(address.name, "");
425    }
426
427    #[test]
428    fn test_upgrade_version_3() {
429        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V3_DB);
430        let db = db_file.open();
431
432        // Assert that the existing addresses have the fully qualified address_level1 name.
433        let mut address = get_address(&db, &Guid::new("A")).unwrap();
434        assert_eq!(address.guid, "A");
435        assert_eq!(address.name, "Jane John Doe");
436        assert_eq!(address.address_level1, "Massachusetts");
437
438        address = get_address(&db, &Guid::new("B")).unwrap();
439        assert_eq!(address.guid, "B");
440        assert_eq!(address.address_level1, "Ontario");
441
442        db_file.upgrade_to(4);
443
444        // Assert that the addresses have been migrated to use the subregion keys.
445        address = get_address(&db, &Guid::new("A")).unwrap();
446        assert_eq!(address.guid, "A");
447        assert_eq!(address.name, "Jane John Doe");
448        assert_eq!(address.address_level1, "MA");
449
450        address = get_address(&db, &Guid::new("B")).unwrap();
451        assert_eq!(address.guid, "B");
452        assert_eq!(address.address_level1, "ON");
453    }
454
455    #[test]
456    fn test_upgrade_version_4() {
457        let db_file = MigratedDatabaseFile::new(AutofillConnectionInitializer, CREATE_V4_DB);
458        let db = db_file.open();
459
460        // passports_data must not exist yet at v4.
461        db.execute_batch("SELECT guid FROM passports_data")
462            .expect_err("passports_data should not exist at v4");
463
464        db_file.upgrade_to(5);
465
466        // After upgrading to v5 the table exists.
467        db.execute_batch("SELECT guid FROM passports_data")
468            .expect("passports_data should exist at v5");
469    }
470}
autofill/db/schema.rs

autofill/db/
schema.rs
