autofill/db/
schema.rs

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

autofill/db/
schema.rs
