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

autofill/db/
schema.rs
