places/storage/
history_metadata.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::{PlacesDb, PlacesTransaction};
6use crate::error::*;
7use crate::RowId;
8use error_support::{breadcrumb, redact_url};
9use rusqlite::types::{FromSql, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
10use sql_support::ConnExt;
11use std::vec::Vec;
12use sync_guid::Guid as SyncGuid;
13use types::Timestamp;
14use url::Url;
15
16use lazy_static::lazy_static;
17
18#[derive(Copy, Clone, Debug, PartialEq, Eq)]
19pub enum DocumentType {
20    Regular = 0,
21    Media = 1,
22}
23
24impl FromSql for DocumentType {
25    #[inline]
26    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
27        Ok(match value.as_i64()? {
28            0 => DocumentType::Regular,
29            1 => DocumentType::Media,
30            other => {
31                // seems safe to ignore?
32                warn!("invalid DocumentType {}", other);
33                DocumentType::Regular
34            }
35        })
36    }
37}
38
39impl ToSql for DocumentType {
40    #[inline]
41    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
42        Ok(ToSqlOutput::from(*self as u32))
43    }
44}
45
46#[derive(Clone)]
47pub struct HistoryHighlightWeights {
48    pub view_time: f64,
49    pub frequency: f64,
50}
51
52#[derive(Clone)]
53pub struct HistoryHighlight {
54    pub score: f64,
55    pub place_id: i32,
56    pub url: String,
57    pub title: Option<String>,
58    pub preview_image_url: Option<String>,
59}
60
61impl HistoryHighlight {
62    pub(crate) fn from_row(row: &rusqlite::Row<'_>) -> Result<Self> {
63        Ok(Self {
64            score: row.get("score")?,
65            place_id: row.get("place_id")?,
66            url: row.get("url")?,
67            title: row.get("title")?,
68            preview_image_url: row.get("preview_image_url")?,
69        })
70    }
71}
72
73#[derive(Clone, Debug, PartialEq, Eq)]
74pub struct HistoryMetadataObservation {
75    pub url: String,
76    pub view_time: Option<i32>,
77    pub search_term: Option<String>,
78    pub document_type: Option<DocumentType>,
79    pub referrer_url: Option<String>,
80    pub title: Option<String>,
81}
82
83#[derive(Clone, Copy, Debug, PartialEq, Eq)]
84pub enum HistoryMetadataPageMissingBehavior {
85    InsertPage,
86    IgnoreObservation,
87}
88
89#[derive(Clone, Debug, PartialEq, Eq)]
90pub struct NoteHistoryMetadataObservationOptions {
91    pub if_page_missing: HistoryMetadataPageMissingBehavior,
92}
93
94impl Default for NoteHistoryMetadataObservationOptions {
95    fn default() -> Self {
96        Self::new()
97    }
98}
99
100impl NoteHistoryMetadataObservationOptions {
101    pub fn new() -> Self {
102        Self {
103            if_page_missing: HistoryMetadataPageMissingBehavior::IgnoreObservation,
104        }
105    }
106
107    pub fn if_page_missing(self, if_page_missing: HistoryMetadataPageMissingBehavior) -> Self {
108        Self { if_page_missing }
109    }
110}
111
112#[derive(Clone, Debug, PartialEq, Eq)]
113pub struct HistoryMetadata {
114    pub url: String,
115    pub title: Option<String>,
116    pub preview_image_url: Option<String>,
117    pub created_at: i64,
118    pub updated_at: i64,
119    pub total_view_time: i32,
120    pub search_term: Option<String>,
121    pub document_type: DocumentType,
122    pub referrer_url: Option<String>,
123}
124
125impl HistoryMetadata {
126    pub(crate) fn from_row(row: &rusqlite::Row<'_>) -> Result<Self> {
127        let created_at: Timestamp = row.get("created_at")?;
128        let updated_at: Timestamp = row.get("updated_at")?;
129
130        // Guard against invalid data in the db.
131        // Certain client bugs allowed accumulating values that are too large to fit into i32,
132        // leading to overflow failures. While this data will expire and will be deleted
133        // by clients via `delete_older_than`, we still want to ensure we won't crash in case of
134        // encountering it.
135        // See `apply_metadata_observation` for where we guard against observing invalid view times.
136        let total_view_time: i64 = row.get("total_view_time")?;
137        let total_view_time = match i32::try_from(total_view_time) {
138            Ok(tvt) => tvt,
139            Err(_) => i32::MAX,
140        };
141
142        Ok(Self {
143            url: row.get("url")?,
144            title: row.get("title")?,
145            preview_image_url: row.get("preview_image_url")?,
146            created_at: created_at.0 as i64,
147            updated_at: updated_at.0 as i64,
148            total_view_time,
149            search_term: row.get("search_term")?,
150            document_type: row.get("document_type")?,
151            referrer_url: row.get("referrer_url")?,
152        })
153    }
154}
155
156enum PlaceEntry {
157    Existing(i64),
158    CreateFor(Url, Option<String>),
159}
160
161trait WhereArg {
162    fn to_where_arg(&self, db_field: &str) -> String;
163}
164
165impl PlaceEntry {
166    fn fetch(url: &str, tx: &PlacesTransaction<'_>, title: Option<String>) -> Result<Self> {
167        let url = Url::parse(url).inspect_err(|_e| {
168            breadcrumb!(
169                "PlaceEntry::fetch -- Error parsing url: {}",
170                redact_url(url)
171            );
172        })?;
173        let place_id = tx.try_query_one(
174            "SELECT id FROM moz_places WHERE url_hash = hash(:url) AND url = :url",
175            &[(":url", &url.as_str())],
176            true,
177        )?;
178
179        Ok(match place_id {
180            Some(id) => PlaceEntry::Existing(id),
181            None => PlaceEntry::CreateFor(url, title),
182        })
183    }
184}
185
186impl WhereArg for PlaceEntry {
187    fn to_where_arg(&self, db_field: &str) -> String {
188        match self {
189            PlaceEntry::Existing(id) => format!("{} = {}", db_field, id),
190            PlaceEntry::CreateFor(_, _) => panic!("WhereArg: place entry must exist"),
191        }
192    }
193}
194
195impl WhereArg for Option<PlaceEntry> {
196    fn to_where_arg(&self, db_field: &str) -> String {
197        match self {
198            Some(entry) => entry.to_where_arg(db_field),
199            None => format!("{} IS NULL", db_field),
200        }
201    }
202}
203
204trait DatabaseId {
205    fn get_or_insert(&self, tx: &PlacesTransaction<'_>) -> Result<i64>;
206}
207
208impl DatabaseId for PlaceEntry {
209    fn get_or_insert(&self, tx: &PlacesTransaction<'_>) -> Result<i64> {
210        Ok(match self {
211            PlaceEntry::Existing(id) => *id,
212            PlaceEntry::CreateFor(url, title) => {
213                let sql = "INSERT INTO moz_places (guid, url, title, url_hash)
214                VALUES (:guid, :url, :title, hash(:url))";
215
216                let guid = SyncGuid::random();
217
218                tx.execute_cached(
219                    sql,
220                    &[
221                        (":guid", &guid as &dyn rusqlite::ToSql),
222                        (":title", &title),
223                        (":url", &url.as_str()),
224                    ],
225                )?;
226                tx.conn().last_insert_rowid()
227            }
228        })
229    }
230}
231
232enum SearchQueryEntry {
233    Existing(i64),
234    CreateFor(String),
235}
236
237impl DatabaseId for SearchQueryEntry {
238    fn get_or_insert(&self, tx: &PlacesTransaction<'_>) -> Result<i64> {
239        Ok(match self {
240            SearchQueryEntry::Existing(id) => *id,
241            SearchQueryEntry::CreateFor(term) => {
242                tx.execute_cached(
243                    "INSERT INTO moz_places_metadata_search_queries(term) VALUES (:term)",
244                    &[(":term", &term)],
245                )?;
246                tx.conn().last_insert_rowid()
247            }
248        })
249    }
250}
251
252impl SearchQueryEntry {
253    fn from(search_term: &str, tx: &PlacesTransaction<'_>) -> Result<Self> {
254        let lowercase_term = search_term.to_lowercase();
255        Ok(
256            match tx.try_query_one(
257                "SELECT id FROM moz_places_metadata_search_queries WHERE term = :term",
258                &[(":term", &lowercase_term)],
259                true,
260            )? {
261                Some(id) => SearchQueryEntry::Existing(id),
262                None => SearchQueryEntry::CreateFor(lowercase_term),
263            },
264        )
265    }
266}
267
268impl WhereArg for SearchQueryEntry {
269    fn to_where_arg(&self, db_field: &str) -> String {
270        match self {
271            SearchQueryEntry::Existing(id) => format!("{} = {}", db_field, id),
272            SearchQueryEntry::CreateFor(_) => panic!("WhereArg: search query entry must exist"),
273        }
274    }
275}
276
277impl WhereArg for Option<SearchQueryEntry> {
278    fn to_where_arg(&self, db_field: &str) -> String {
279        match self {
280            Some(entry) => entry.to_where_arg(db_field),
281            None => format!("{} IS NULL", db_field),
282        }
283    }
284}
285
286struct HistoryMetadataCompoundKey {
287    place_entry: PlaceEntry,
288    referrer_entry: Option<PlaceEntry>,
289    search_query_entry: Option<SearchQueryEntry>,
290}
291
292struct MetadataObservation {
293    document_type: Option<DocumentType>,
294    view_time: Option<i32>,
295}
296
297impl HistoryMetadataCompoundKey {
298    fn can_debounce(&self) -> Option<i64> {
299        match self.place_entry {
300            PlaceEntry::Existing(id) => {
301                if (match self.search_query_entry {
302                    None | Some(SearchQueryEntry::Existing(_)) => true,
303                    Some(SearchQueryEntry::CreateFor(_)) => false,
304                } && match self.referrer_entry {
305                    None | Some(PlaceEntry::Existing(_)) => true,
306                    Some(PlaceEntry::CreateFor(_, _)) => false,
307                }) {
308                    Some(id)
309                } else {
310                    None
311                }
312            }
313            _ => None,
314        }
315    }
316
317    // Looks up matching metadata records, by the compound key and time window.
318    fn lookup(&self, tx: &PlacesTransaction<'_>, newer_than: i64) -> Result<Option<i64>> {
319        Ok(match self.can_debounce() {
320            Some(id) => {
321                let search_query_id = match self.search_query_entry {
322                    None | Some(SearchQueryEntry::CreateFor(_)) => None,
323                    Some(SearchQueryEntry::Existing(id)) => Some(id),
324                };
325
326                let referrer_place_id = match self.referrer_entry {
327                    None | Some(PlaceEntry::CreateFor(_, _)) => None,
328                    Some(PlaceEntry::Existing(id)) => Some(id),
329                };
330
331                tx.try_query_one::<i64, _>(
332                    "SELECT id FROM moz_places_metadata
333                        WHERE
334                            place_id IS :place_id AND
335                            referrer_place_id IS :referrer_place_id AND
336                            search_query_id IS :search_query_id AND
337                            updated_at >= :newer_than
338                        ORDER BY updated_at DESC LIMIT 1",
339                    rusqlite::named_params! {
340                        ":place_id": id,
341                        ":search_query_id": search_query_id,
342                        ":referrer_place_id": referrer_place_id,
343                        ":newer_than": newer_than
344                    },
345                    true,
346                )?
347            }
348            None => None,
349        })
350    }
351}
352
353const DEBOUNCE_WINDOW_MS: i64 = 2 * 60 * 1000; // 2 minutes
354const MAX_QUERY_RESULTS: i32 = 1000;
355
356const COMMON_METADATA_SELECT: &str = "
357SELECT
358    m.id as metadata_id, p.url as url, p.title as title, p.preview_image_url as preview_image_url,
359    m.created_at as created_at, m.updated_at as updated_at, m.total_view_time as total_view_time,
360    m.document_type as document_type, o.url as referrer_url, s.term as search_term
361FROM moz_places_metadata m
362LEFT JOIN moz_places p ON m.place_id = p.id
363LEFT JOIN moz_places_metadata_search_queries s ON m.search_query_id = s.id
364LEFT JOIN moz_places o ON o.id = m.referrer_place_id";
365
366// Highlight query returns moz_places entries ranked by a "highlight score".
367// This score takes into account two factors:
368// 1) frequency of visits to a page,
369// 2) cumulative view time of a page.
370//
371// Eventually, we could consider combining this with `moz_places.frecency` as a basis for (1), that assumes we have a populated moz_historyvisits table.
372// Currently, iOS doesn't use 'places' library to track visits, so iOS clients won't have meaningful frecency scores.
373//
374// Instead, we use moz_places_metadata entries to compute both (1) and (2).
375// This has several nice properties:
376// - it works on clients that only use 'metadata' APIs, not 'places'
377// - since metadata is capped by clients to a certain time window (via `delete_older_than`), the scores will be computed for the same time window
378// - we debounce metadata observations to the same "key" if they're close in time.
379// -- this is an equivalent of saying that if a page was visited multiple times in quick succession, treat that as a single visit while accumulating the view time
380// -- the assumption we're making is that this better matches user perception of their browsing activity
381//
382// The score is computed as a weighted sum of two probabilities:
383// - at any given moment in my browsing sessions for the past X days, how likely am I to be looking at a page?
384// - for any given visit during my browsing sessions for the past X days, how likely am I to visit a page?
385//
386// This kind of scoring is fairly intuitive and simple to reason about at the product level.
387//
388// An alternative way to arrive at the same ranking would be to normalize the values to compare data of different dimensions, time vs frequency.
389// We can normalize view time and frequency into a 0-1 scale before computing weighted scores.
390// (select place_id, (normal_frequency * 1.0 + normal_view_time * 1.0) as score from
391//     (select place_id, cast(count(*) - min_f as REAL) / cast(range_f as REAL) as normal_frequency, cast(sum(total_view_time) - min_v as REAL) / cast(max_v as REAL) as normal_view_time from moz_places_metadata,
392//     (select min(frequency) as min_f, max(frequency) as max_f, max(frequency) - min(frequency) as range_f
393//         from (select count(*) as frequency from moz_places_metadata group by place_id)
394//     ),
395//     (select min(view_time) as min_v, max(view_time) as max_v, max(view_time) - min(view_time) as range_v
396//         from (select sum(total_view_time) as view_time from moz_places_metadata where total_view_time > 0 group by place_id)
397//     ) where total_view_time > 0 group by place_id)) ranked
398//
399// Note that while it's tempting to use built-in window functions such percent_rank, they're not sufficient.
400// The built-in functions concern themselves with absolute ranking, not taking into account magnitudes of differences between values.
401// For example, given two entries we'll know that one is larger than another, but not by how much.
402const HIGHLIGHTS_QUERY: &str = "
403SELECT
404    IFNULL(ranked.score, 0.0) AS score, p.id AS place_id, p.url AS url, p.title AS title, p.preview_image_url AS preview_image_url
405FROM moz_places p
406INNER JOIN
407    (
408        SELECT place_id, :view_time_weight * view_time_prob + :frequency_weight * frequency_prob AS score FROM (
409            SELECT
410                place_id,
411                CAST(count(*) AS REAL) / total_count AS frequency_prob,
412                CAST(sum(total_view_time) AS REAL) / all_view_time AS view_time_prob
413                FROM (
414                    SELECT place_id, count(*) OVER () AS total_count, total_view_time, sum(total_view_time) OVER () AS all_view_time FROM moz_places_metadata
415                )
416            GROUP BY place_id
417        )
418    ) ranked
419ON p.id = ranked.place_id
420ORDER BY ranked.score DESC
421LIMIT :limit";
422
423lazy_static! {
424    static ref GET_LATEST_SQL: String = format!(
425        "{common_select_sql}
426        WHERE p.url_hash = hash(:url) AND p.url = :url
427        ORDER BY updated_at DESC, metadata_id DESC
428        LIMIT 1",
429        common_select_sql = COMMON_METADATA_SELECT
430    );
431    static ref GET_BETWEEN_SQL: String = format!(
432        "{common_select_sql}
433        WHERE updated_at BETWEEN :start AND :end
434        ORDER BY updated_at DESC
435        LIMIT {max_limit}",
436        common_select_sql = COMMON_METADATA_SELECT,
437        max_limit = MAX_QUERY_RESULTS
438    );
439    static ref GET_SINCE_SQL: String = format!(
440        "{common_select_sql}
441        WHERE updated_at >= :start
442        ORDER BY updated_at DESC
443        LIMIT {max_limit}",
444        common_select_sql = COMMON_METADATA_SELECT,
445        max_limit = MAX_QUERY_RESULTS
446    );
447    static ref QUERY_SQL: String = format!(
448        "{common_select_sql}
449        WHERE
450            p.url LIKE :query OR
451            p.title LIKE :query OR
452            search_term LIKE :query
453        ORDER BY total_view_time DESC
454        LIMIT :limit",
455        common_select_sql = COMMON_METADATA_SELECT
456    );
457}
458
459pub fn get_latest_for_url(db: &PlacesDb, url: &Url) -> Result<Option<HistoryMetadata>> {
460    let metadata = db.try_query_row(
461        GET_LATEST_SQL.as_str(),
462        &[(":url", &url.as_str())],
463        HistoryMetadata::from_row,
464        true,
465    )?;
466    Ok(metadata)
467}
468
469pub fn get_between(db: &PlacesDb, start: i64, end: i64) -> Result<Vec<HistoryMetadata>> {
470    db.query_rows_and_then_cached(
471        GET_BETWEEN_SQL.as_str(),
472        rusqlite::named_params! {
473            ":start": start,
474            ":end": end,
475        },
476        HistoryMetadata::from_row,
477    )
478}
479
480pub fn get_since(db: &PlacesDb, start: i64) -> Result<Vec<HistoryMetadata>> {
481    db.query_rows_and_then_cached(
482        GET_SINCE_SQL.as_str(),
483        rusqlite::named_params! {
484            ":start": start
485        },
486        HistoryMetadata::from_row,
487    )
488}
489
490pub fn get_highlights(
491    db: &PlacesDb,
492    weights: HistoryHighlightWeights,
493    limit: i32,
494) -> Result<Vec<HistoryHighlight>> {
495    db.query_rows_and_then_cached(
496        HIGHLIGHTS_QUERY,
497        rusqlite::named_params! {
498            ":view_time_weight": weights.view_time,
499            ":frequency_weight": weights.frequency,
500            ":limit": limit
501        },
502        HistoryHighlight::from_row,
503    )
504}
505
506pub fn query(db: &PlacesDb, query: &str, limit: i32) -> Result<Vec<HistoryMetadata>> {
507    db.query_rows_and_then_cached(
508        QUERY_SQL.as_str(),
509        rusqlite::named_params! {
510            ":query": format!("%{}%", query),
511            ":limit": limit
512        },
513        HistoryMetadata::from_row,
514    )
515}
516
517pub fn delete_older_than(db: &PlacesDb, older_than: i64) -> Result<()> {
518    db.execute_cached(
519        "DELETE FROM moz_places_metadata
520         WHERE updated_at < :older_than",
521        &[(":older_than", &older_than)],
522    )?;
523    Ok(())
524}
525
526pub fn delete_between(db: &PlacesDb, start: i64, end: i64) -> Result<()> {
527    db.execute_cached(
528        "DELETE FROM moz_places_metadata
529        WHERE updated_at > :start and updated_at < :end",
530        &[(":start", &start), (":end", &end)],
531    )?;
532    Ok(())
533}
534
535/// Delete all metadata for the specified place id.
536pub fn delete_all_metadata_for_page(db: &PlacesDb, place_id: RowId) -> Result<()> {
537    db.execute_cached(
538        "DELETE FROM moz_places_metadata
539         WHERE place_id = :place_id",
540        &[(":place_id", &place_id)],
541    )?;
542    Ok(())
543}
544
545pub fn delete_metadata(
546    db: &PlacesDb,
547    url: &Url,
548    referrer_url: Option<&Url>,
549    search_term: Option<&str>,
550) -> Result<()> {
551    let tx = db.begin_transaction()?;
552
553    // Only delete entries that exactly match the key (url+referrer+search_term) we were passed-in.
554    // Do nothing if we were asked to delete a key which doesn't match what's in the database.
555    // e.g. referrer_url.is_some(), but a correspodning moz_places entry doesn't exist.
556    // In practice this shouldn't happen, or it may imply API misuse, but in either case we shouldn't
557    // delete things we were not asked to delete.
558    let place_entry = PlaceEntry::fetch(url.as_str(), &tx, None)?;
559    let place_entry = match place_entry {
560        PlaceEntry::Existing(_) => place_entry,
561        PlaceEntry::CreateFor(_, _) => {
562            tx.rollback()?;
563            return Ok(());
564        }
565    };
566    let referrer_entry = match referrer_url {
567        Some(referrer_url) if !referrer_url.as_str().is_empty() => {
568            Some(PlaceEntry::fetch(referrer_url.as_str(), &tx, None)?)
569        }
570        _ => None,
571    };
572    let referrer_entry = match referrer_entry {
573        Some(PlaceEntry::Existing(_)) | None => referrer_entry,
574        Some(PlaceEntry::CreateFor(_, _)) => {
575            tx.rollback()?;
576            return Ok(());
577        }
578    };
579    let search_query_entry = match search_term {
580        Some(search_term) if !search_term.is_empty() => {
581            Some(SearchQueryEntry::from(search_term, &tx)?)
582        }
583        _ => None,
584    };
585    let search_query_entry = match search_query_entry {
586        Some(SearchQueryEntry::Existing(_)) | None => search_query_entry,
587        Some(SearchQueryEntry::CreateFor(_)) => {
588            tx.rollback()?;
589            return Ok(());
590        }
591    };
592
593    let sql = format!(
594        "DELETE FROM moz_places_metadata WHERE {} AND {} AND {}",
595        place_entry.to_where_arg("place_id"),
596        referrer_entry.to_where_arg("referrer_place_id"),
597        search_query_entry.to_where_arg("search_query_id")
598    );
599
600    tx.execute_cached(&sql, [])?;
601    tx.commit()?;
602
603    Ok(())
604}
605
606pub fn apply_metadata_observation(
607    db: &PlacesDb,
608    observation: HistoryMetadataObservation,
609    options: NoteHistoryMetadataObservationOptions,
610) -> Result<()> {
611    if let Some(view_time) = observation.view_time {
612        // Consider any view_time observations that are higher than 24hrs to be invalid.
613        // This guards against clients passing us wildly inaccurate view_time observations,
614        // likely resulting from some measurement bug. If we detect such cases, we fail so
615        // that the client has a chance to discover its mistake.
616        // When recording a view time, we increment the stored value directly in SQL, which
617        // doesn't allow for error detection unless we run an additional SELECT statement to
618        // query current cumulative view time and see if incrementing it will result in an
619        // overflow. This check is a simpler way to achieve the same goal (detect invalid inputs).
620        if view_time > 1000 * 60 * 60 * 24 {
621            return Err(InvalidMetadataObservation::ViewTimeTooLong.into());
622        }
623    }
624
625    // Begin a write transaction. We do this before any other work (e.g. SELECTs) to avoid racing against
626    // other writers. Even though we expect to only have a single application writer, a sync writer
627    // can come in at any time and change data we depend on, such as moz_places
628    // and moz_origins, leaving us in a potentially inconsistent state.
629    let tx = db.begin_transaction()?;
630
631    let place_entry = PlaceEntry::fetch(&observation.url, &tx, observation.title.clone())?;
632    let result = apply_metadata_observation_impl(&tx, place_entry, observation, options);
633
634    // Inserting into moz_places has side-effects (temp tables are populated via triggers and need to be flushed).
635    // This call "finalizes" these side-effects.
636    super::delete_pending_temp_tables(db)?;
637    match result {
638        Ok(_) => tx.commit()?,
639        Err(_) => tx.rollback()?,
640    };
641
642    result
643}
644
645fn apply_metadata_observation_impl(
646    tx: &PlacesTransaction<'_>,
647    place_entry: PlaceEntry,
648    observation: HistoryMetadataObservation,
649    options: NoteHistoryMetadataObservationOptions,
650) -> Result<()> {
651    let referrer_entry = match observation.referrer_url {
652        Some(referrer_url) if !referrer_url.is_empty() => {
653            Some(PlaceEntry::fetch(&referrer_url, tx, None)?)
654        }
655        Some(_) | None => None,
656    };
657    let search_query_entry = match observation.search_term {
658        Some(search_term) if !search_term.is_empty() => {
659            Some(SearchQueryEntry::from(&search_term, tx)?)
660        }
661        Some(_) | None => None,
662    };
663
664    let compound_key = HistoryMetadataCompoundKey {
665        place_entry,
666        referrer_entry,
667        search_query_entry,
668    };
669
670    let observation = MetadataObservation {
671        document_type: observation.document_type,
672        view_time: observation.view_time,
673    };
674
675    let now = Timestamp::now().as_millis() as i64;
676    let newer_than = now - DEBOUNCE_WINDOW_MS;
677    let matching_metadata = compound_key.lookup(tx, newer_than)?;
678
679    // If a matching record exists, update it; otherwise, insert a new one.
680    match matching_metadata {
681        Some(metadata_id) => {
682            // If document_type isn't part of the observation, make sure we don't accidentally erase what's currently set.
683            match observation {
684                MetadataObservation {
685                    document_type: Some(dt),
686                    view_time,
687                } => {
688                    tx.execute_cached(
689                        "UPDATE
690                            moz_places_metadata
691                        SET
692                            document_type = :document_type,
693                            total_view_time = total_view_time + :view_time_delta,
694                            updated_at = :updated_at
695                        WHERE id = :id",
696                        rusqlite::named_params! {
697                            ":id": metadata_id,
698                            ":document_type": dt,
699                            ":view_time_delta": view_time.unwrap_or(0),
700                            ":updated_at": now
701                        },
702                    )?;
703                }
704                MetadataObservation {
705                    document_type: None,
706                    view_time,
707                } => {
708                    tx.execute_cached(
709                        "UPDATE
710                            moz_places_metadata
711                        SET
712                            total_view_time = total_view_time + :view_time_delta,
713                            updated_at = :updated_at
714                        WHERE id = :id",
715                        rusqlite::named_params! {
716                            ":id": metadata_id,
717                            ":view_time_delta": view_time.unwrap_or(0),
718                            ":updated_at": now
719                        },
720                    )?;
721                }
722            }
723            Ok(())
724        }
725        None => insert_metadata_in_tx(tx, compound_key, observation, options),
726    }
727}
728
729fn insert_metadata_in_tx(
730    tx: &PlacesTransaction<'_>,
731    key: HistoryMetadataCompoundKey,
732    observation: MetadataObservation,
733    options: NoteHistoryMetadataObservationOptions,
734) -> Result<()> {
735    let now = Timestamp::now();
736
737    let referrer_place_id = match key.referrer_entry {
738        None => None,
739        Some(entry) => Some(entry.get_or_insert(tx)?),
740    };
741
742    let search_query_id = match key.search_query_entry {
743        None => None,
744        Some(entry) => Some(entry.get_or_insert(tx)?),
745    };
746
747    // Heavy lifting around moz_places inserting (e.g. updating moz_origins, frecency, etc) is performed via triggers.
748    // This lets us simply INSERT here without worrying about the rest.
749    let place_id = match (key.place_entry, options.if_page_missing) {
750        (PlaceEntry::Existing(id), _) => id,
751        (PlaceEntry::CreateFor(_, _), HistoryMetadataPageMissingBehavior::IgnoreObservation) => {
752            return Ok(())
753        }
754        (
755            ref entry @ PlaceEntry::CreateFor(_, _),
756            HistoryMetadataPageMissingBehavior::InsertPage,
757        ) => entry.get_or_insert(tx)?,
758    };
759
760    let sql = "INSERT INTO moz_places_metadata
761        (place_id, created_at, updated_at, total_view_time, search_query_id, document_type, referrer_place_id)
762    VALUES
763        (:place_id, :created_at, :updated_at, :total_view_time, :search_query_id, :document_type, :referrer_place_id)";
764
765    tx.execute_cached(
766        sql,
767        &[
768            (":place_id", &place_id as &dyn rusqlite::ToSql),
769            (":created_at", &now),
770            (":updated_at", &now),
771            (":search_query_id", &search_query_id),
772            (":referrer_place_id", &referrer_place_id),
773            (
774                ":document_type",
775                &observation.document_type.unwrap_or(DocumentType::Regular),
776            ),
777            (":total_view_time", &observation.view_time.unwrap_or(0)),
778        ],
779    )?;
780
781    Ok(())
782}
783
784#[cfg(test)]
785mod tests {
786    use super::*;
787    use crate::api::places_api::ConnectionType;
788    use crate::observation::VisitObservation;
789    use crate::storage::bookmarks::{
790        get_raw_bookmark, insert_bookmark, BookmarkPosition, BookmarkRootGuid, InsertableBookmark,
791        InsertableItem,
792    };
793    use crate::storage::fetch_page_info;
794    use crate::storage::history::{
795        apply_observation, delete_everything, delete_visits_between, delete_visits_for,
796        get_visit_count, url_to_guid,
797    };
798    use crate::types::VisitType;
799    use crate::VisitTransitionSet;
800    use std::{thread, time};
801
802    macro_rules! assert_table_size {
803        ($conn:expr, $table:expr, $count:expr) => {
804            assert_eq!(
805                $count,
806                $conn
807                    .try_query_one::<i64, _>(
808                        format!("SELECT count(*) FROM {table}", table = $table).as_str(),
809                        [],
810                        true
811                    )
812                    .expect("select works")
813                    .expect("got count")
814            );
815        };
816    }
817
818    macro_rules! assert_history_metadata_record {
819        ($record:expr, url $url:expr, total_time $tvt:expr, search_term $search_term:expr, document_type $document_type:expr, referrer_url $referrer_url:expr, title $title:expr, preview_image_url $preview_image_url:expr) => {
820            assert_eq!(String::from($url), $record.url, "url must match");
821            assert_eq!($tvt, $record.total_view_time, "total_view_time must match");
822            assert_eq!($document_type, $record.document_type, "is_media must match");
823
824            let meta = $record.clone(); // ugh... not sure why this `clone` is necessary.
825
826            match $search_term as Option<&str> {
827                Some(t) => assert_eq!(
828                    String::from(t),
829                    meta.search_term.expect("search_term must be Some"),
830                    "search_term must match"
831                ),
832                None => assert_eq!(
833                    true,
834                    meta.search_term.is_none(),
835                    "search_term expected to be None"
836                ),
837            };
838            match $referrer_url as Option<&str> {
839                Some(t) => assert_eq!(
840                    String::from(t),
841                    meta.referrer_url.expect("referrer_url must be Some"),
842                    "referrer_url must match"
843                ),
844                None => assert_eq!(
845                    true,
846                    meta.referrer_url.is_none(),
847                    "referrer_url expected to be None"
848                ),
849            };
850            match $title as Option<&str> {
851                Some(t) => assert_eq!(
852                    String::from(t),
853                    meta.title.expect("title must be Some"),
854                    "title must match"
855                ),
856                None => assert_eq!(true, meta.title.is_none(), "title expected to be None"),
857            };
858            match $preview_image_url as Option<&str> {
859                Some(t) => assert_eq!(
860                    String::from(t),
861                    meta.preview_image_url
862                        .expect("preview_image_url must be Some"),
863                    "preview_image_url must match"
864                ),
865                None => assert_eq!(
866                    true,
867                    meta.preview_image_url.is_none(),
868                    "preview_image_url expected to be None"
869                ),
870            };
871        };
872    }
873
874    macro_rules! assert_total_after_observation {
875        ($conn:expr, total_records_after $total_records:expr, total_view_time_after $total_view_time:expr, url $url:expr, view_time $view_time:expr, search_term $search_term:expr, document_type $document_type:expr, referrer_url $referrer_url:expr, title $title:expr) => {
876            note_observation!($conn,
877                url $url,
878                view_time $view_time,
879                search_term $search_term,
880                document_type $document_type,
881                referrer_url $referrer_url,
882                title $title
883            );
884
885            assert_table_size!($conn, "moz_places_metadata", $total_records);
886            let updated = get_latest_for_url($conn, &Url::parse($url).unwrap()).unwrap().unwrap();
887            assert_eq!($total_view_time, updated.total_view_time, "total view time must match");
888        }
889    }
890
891    macro_rules! note_observation {
892        ($conn:expr, url $url:expr, view_time $view_time:expr, search_term $search_term:expr, document_type $document_type:expr, referrer_url $referrer_url:expr, title $title:expr) => {
893            note_observation!(
894                $conn,
895                NoteHistoryMetadataObservationOptions::new()
896                    .if_page_missing(HistoryMetadataPageMissingBehavior::InsertPage),
897                url $url,
898                view_time $view_time,
899                search_term $search_term,
900                document_type $document_type,
901                referrer_url $referrer_url,
902                title $title
903            )
904        };
905        ($conn:expr, $options:expr, url $url:expr, view_time $view_time:expr, search_term $search_term:expr, document_type $document_type:expr, referrer_url $referrer_url:expr, title $title:expr) => {
906            apply_metadata_observation(
907                $conn,
908                HistoryMetadataObservation {
909                    url: String::from($url),
910                    view_time: $view_time,
911                    search_term: $search_term.map(|s: &str| s.to_string()),
912                    document_type: $document_type,
913                    referrer_url: $referrer_url.map(|s: &str| s.to_string()),
914                    title: $title.map(|s: &str| s.to_string()),
915                },
916                $options,
917            )
918            .unwrap();
919        };
920    }
921
922    macro_rules! assert_after_observation {
923        ($conn:expr, total_records_after $total_records:expr, total_view_time_after $total_view_time:expr, url $url:expr, view_time $view_time:expr, search_term $search_term:expr, document_type $document_type:expr, referrer_url $referrer_url:expr, title $title:expr, assertion $assertion:expr) => {
924            // can set title on creating a new record
925            assert_total_after_observation!($conn,
926                total_records_after $total_records,
927                total_view_time_after $total_view_time,
928                url $url,
929                view_time $view_time,
930                search_term $search_term,
931                document_type $document_type,
932                referrer_url $referrer_url,
933                title $title
934            );
935
936            let m = get_latest_for_url(
937                $conn,
938                &Url::parse(&String::from($url)).unwrap(),
939            )
940            .unwrap()
941            .unwrap();
942            #[allow(clippy::redundant_closure_call)]
943            $assertion(m);
944        }
945    }
946
947    #[test]
948    fn test_note_observation() {
949        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).unwrap();
950
951        assert_table_size!(&conn, "moz_places_metadata", 0);
952
953        assert_total_after_observation!(&conn,
954            total_records_after 1,
955            total_view_time_after 1500,
956            url "http://mozilla.com/",
957            view_time Some(1500),
958            search_term None,
959            document_type Some(DocumentType::Regular),
960            referrer_url None,
961            title None
962        );
963
964        // debounced! total time was updated
965        assert_total_after_observation!(&conn,
966            total_records_after 1,
967            total_view_time_after 2500,
968            url "http://mozilla.com/",
969            view_time Some(1000),
970            search_term None,
971            document_type Some(DocumentType::Regular),
972            referrer_url None,
973            title None
974        );
975
976        // different document type, record updated
977        assert_total_after_observation!(&conn,
978            total_records_after 1,
979            total_view_time_after 3500,
980            url "http://mozilla.com/",
981            view_time Some(1000),
982            search_term None,
983            document_type Some(DocumentType::Media),
984            referrer_url None,
985            title None
986        );
987
988        // referrer set
989        assert_total_after_observation!(&conn,
990            total_records_after 2,
991            total_view_time_after 2000,
992            url "http://mozilla.com/",
993            view_time Some(2000),
994            search_term None,
995            document_type Some(DocumentType::Media),
996            referrer_url Some("https://news.website"),
997            title None
998        );
999
1000        // search term and referrer are set
1001        assert_total_after_observation!(&conn,
1002            total_records_after 3,
1003            total_view_time_after 1100,
1004            url "http://mozilla.com/",
1005            view_time Some(1100),
1006            search_term Some("firefox"),
1007            document_type Some(DocumentType::Media),
1008            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=firefox"),
1009            title None
1010        );
1011
1012        // debounce!
1013        assert_total_after_observation!(&conn,
1014            total_records_after 3,
1015            total_view_time_after 6100,
1016            url "http://mozilla.com/",
1017            view_time Some(5000),
1018            search_term Some("firefox"),
1019            document_type Some(DocumentType::Media),
1020            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=firefox"),
1021            title None
1022        );
1023
1024        // different url now
1025        assert_total_after_observation!(&conn,
1026            total_records_after 4,
1027            total_view_time_after 3000,
1028            url "http://mozilla.com/another",
1029            view_time Some(3000),
1030            search_term None,
1031            document_type Some(DocumentType::Regular),
1032            referrer_url Some("https://news.website/tech"),
1033            title None
1034        );
1035
1036        // shared origin for both url and referrer
1037        assert_total_after_observation!(&conn,
1038            total_records_after 5,
1039            total_view_time_after 100000,
1040            url "https://www.youtube.com/watch?v=tpiyEe_CqB4",
1041            view_time Some(100000),
1042            search_term Some("cute cat"),
1043            document_type Some(DocumentType::Media),
1044            referrer_url Some("https://www.youtube.com/results?search_query=cute+cat"),
1045            title None
1046        );
1047
1048        // empty search term/referrer url are treated the same as None
1049        assert_total_after_observation!(&conn,
1050            total_records_after 6,
1051            total_view_time_after 80000,
1052            url "https://www.youtube.com/watch?v=daff43jif3",
1053            view_time Some(80000),
1054            search_term Some(""),
1055            document_type Some(DocumentType::Media),
1056            referrer_url Some(""),
1057            title None
1058        );
1059
1060        assert_total_after_observation!(&conn,
1061            total_records_after 6,
1062            total_view_time_after 90000,
1063            url "https://www.youtube.com/watch?v=daff43jif3",
1064            view_time Some(10000),
1065            search_term None,
1066            document_type Some(DocumentType::Media),
1067            referrer_url None,
1068            title None
1069        );
1070
1071        // document type recording
1072        assert_total_after_observation!(&conn,
1073            total_records_after 7,
1074            total_view_time_after 0,
1075            url "https://www.youtube.com/watch?v=fds32fds",
1076            view_time None,
1077            search_term None,
1078            document_type Some(DocumentType::Media),
1079            referrer_url None,
1080            title None
1081        );
1082
1083        // now, update the view time as a separate call
1084        assert_total_after_observation!(&conn,
1085            total_records_after 7,
1086            total_view_time_after 1338,
1087            url "https://www.youtube.com/watch?v=fds32fds",
1088            view_time Some(1338),
1089            search_term None,
1090            document_type None,
1091            referrer_url None,
1092            title None
1093        );
1094
1095        // and again, bump the view time
1096        assert_total_after_observation!(&conn,
1097            total_records_after 7,
1098            total_view_time_after 2000,
1099            url "https://www.youtube.com/watch?v=fds32fds",
1100            view_time Some(662),
1101            search_term None,
1102            document_type None,
1103            referrer_url None,
1104            title None
1105        );
1106
1107        // now try the other way - record view time first, document type after.
1108        // and again, bump the view time
1109        assert_after_observation!(&conn,
1110            total_records_after 8,
1111            total_view_time_after 662,
1112            url "https://www.youtube.com/watch?v=dasdg34d",
1113            view_time Some(662),
1114            search_term None,
1115            document_type None,
1116            referrer_url None,
1117            title None,
1118            assertion |m: HistoryMetadata| { assert_eq!(DocumentType::Regular, m.document_type) }
1119        );
1120
1121        assert_after_observation!(&conn,
1122            total_records_after 8,
1123            total_view_time_after 662,
1124            url "https://www.youtube.com/watch?v=dasdg34d",
1125            view_time None,
1126            search_term None,
1127            document_type Some(DocumentType::Media),
1128            referrer_url None,
1129            title None,
1130            assertion |m: HistoryMetadata| { assert_eq!(DocumentType::Media, m.document_type) }
1131        );
1132
1133        // document type not overwritten (e.g. remains 1, not default 0).
1134        assert_after_observation!(&conn,
1135            total_records_after 8,
1136            total_view_time_after 675,
1137            url "https://www.youtube.com/watch?v=dasdg34d",
1138            view_time Some(13),
1139            search_term None,
1140            document_type None,
1141            referrer_url None,
1142            title None,
1143            assertion |m: HistoryMetadata| { assert_eq!(DocumentType::Media, m.document_type) }
1144        );
1145
1146        // can set title on creating a new record
1147        assert_after_observation!(&conn,
1148            total_records_after 9,
1149            total_view_time_after 13,
1150            url "https://www.youtube.com/watch?v=dasdsada",
1151            view_time Some(13),
1152            search_term None,
1153            document_type None,
1154            referrer_url None,
1155            title Some("hello!"),
1156            assertion |m: HistoryMetadata| { assert_eq!(Some(String::from("hello!")), m.title) }
1157        );
1158
1159        // can not update title after
1160        assert_after_observation!(&conn,
1161            total_records_after 9,
1162            total_view_time_after 26,
1163            url "https://www.youtube.com/watch?v=dasdsada",
1164            view_time Some(13),
1165            search_term None,
1166            document_type None,
1167            referrer_url None,
1168            title Some("world!"),
1169            assertion |m: HistoryMetadata| { assert_eq!(Some(String::from("hello!")), m.title) }
1170        );
1171    }
1172
1173    #[test]
1174    fn test_note_observation_invalid_view_time() {
1175        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1176
1177        note_observation!(&conn,
1178            url "https://www.mozilla.org/",
1179            view_time None,
1180            search_term None,
1181            document_type Some(DocumentType::Regular),
1182            referrer_url None,
1183            title None
1184        );
1185
1186        // 48 hrs is clearly a bad view to observe.
1187        assert!(apply_metadata_observation(
1188            &conn,
1189            HistoryMetadataObservation {
1190                url: String::from("https://www.mozilla.org"),
1191                view_time: Some(1000 * 60 * 60 * 24 * 2),
1192                search_term: None,
1193                document_type: None,
1194                referrer_url: None,
1195                title: None
1196            },
1197            NoteHistoryMetadataObservationOptions::new(),
1198        )
1199        .is_err());
1200
1201        // 12 hrs is assumed to be "plausible".
1202        assert!(apply_metadata_observation(
1203            &conn,
1204            HistoryMetadataObservation {
1205                url: String::from("https://www.mozilla.org"),
1206                view_time: Some(1000 * 60 * 60 * 12),
1207                search_term: None,
1208                document_type: None,
1209                referrer_url: None,
1210                title: None
1211            },
1212            NoteHistoryMetadataObservationOptions::new(),
1213        )
1214        .is_ok());
1215    }
1216
1217    #[test]
1218    fn test_get_between() {
1219        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1220
1221        assert_eq!(0, get_between(&conn, 0, 0).unwrap().len());
1222
1223        let beginning = Timestamp::now().as_millis() as i64;
1224        note_observation!(&conn,
1225            url "http://mozilla.com/another",
1226            view_time Some(3000),
1227            search_term None,
1228            document_type Some(DocumentType::Regular),
1229            referrer_url Some("https://news.website/tech"),
1230            title None
1231        );
1232        let after_meta1 = Timestamp::now().as_millis() as i64;
1233
1234        assert_eq!(0, get_between(&conn, 0, beginning - 1).unwrap().len());
1235        assert_eq!(1, get_between(&conn, 0, after_meta1).unwrap().len());
1236
1237        thread::sleep(time::Duration::from_millis(10));
1238
1239        note_observation!(&conn,
1240            url "http://mozilla.com/video/",
1241            view_time Some(1000),
1242            search_term None,
1243            document_type Some(DocumentType::Media),
1244            referrer_url None,
1245            title None
1246        );
1247        let after_meta2 = Timestamp::now().as_millis() as i64;
1248
1249        assert_eq!(1, get_between(&conn, beginning, after_meta1).unwrap().len());
1250        assert_eq!(2, get_between(&conn, beginning, after_meta2).unwrap().len());
1251        assert_eq!(
1252            1,
1253            get_between(&conn, after_meta1, after_meta2).unwrap().len()
1254        );
1255        assert_eq!(
1256            0,
1257            get_between(&conn, after_meta2, after_meta2 + 1)
1258                .unwrap()
1259                .len()
1260        );
1261    }
1262
1263    #[test]
1264    fn test_get_since() {
1265        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1266
1267        assert_eq!(0, get_since(&conn, 0).unwrap().len());
1268
1269        let beginning = Timestamp::now().as_millis() as i64;
1270        note_observation!(&conn,
1271            url "http://mozilla.com/another",
1272            view_time Some(3000),
1273            search_term None,
1274            document_type Some(DocumentType::Regular),
1275            referrer_url Some("https://news.website/tech"),
1276            title None
1277        );
1278        let after_meta1 = Timestamp::now().as_millis() as i64;
1279
1280        assert_eq!(1, get_since(&conn, 0).unwrap().len());
1281        assert_eq!(1, get_since(&conn, beginning).unwrap().len());
1282        assert_eq!(0, get_since(&conn, after_meta1).unwrap().len());
1283
1284        // thread::sleep(time::Duration::from_millis(50));
1285
1286        note_observation!(&conn,
1287            url "http://mozilla.com/video/",
1288            view_time Some(1000),
1289            search_term None,
1290            document_type Some(DocumentType::Media),
1291            referrer_url None,
1292            title None
1293        );
1294        let after_meta2 = Timestamp::now().as_millis() as i64;
1295        assert_eq!(2, get_since(&conn, beginning).unwrap().len());
1296        assert_eq!(1, get_since(&conn, after_meta1).unwrap().len());
1297        assert_eq!(0, get_since(&conn, after_meta2).unwrap().len());
1298    }
1299
1300    #[test]
1301    fn test_get_highlights() {
1302        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1303
1304        // Empty database is fine.
1305        assert_eq!(
1306            0,
1307            get_highlights(
1308                &conn,
1309                HistoryHighlightWeights {
1310                    view_time: 1.0,
1311                    frequency: 1.0
1312                },
1313                10
1314            )
1315            .unwrap()
1316            .len()
1317        );
1318
1319        // Database with "normal" history but no metadata observations is fine.
1320        apply_observation(
1321            &conn,
1322            VisitObservation::new(
1323                Url::parse("https://www.reddit.com/r/climbing").expect("Should parse URL"),
1324            )
1325            .with_visit_type(VisitType::Link)
1326            .with_at(Timestamp::now()),
1327        )
1328        .expect("Should apply observation");
1329        assert_eq!(
1330            0,
1331            get_highlights(
1332                &conn,
1333                HistoryHighlightWeights {
1334                    view_time: 1.0,
1335                    frequency: 1.0
1336                },
1337                10
1338            )
1339            .unwrap()
1340            .len()
1341        );
1342
1343        // three observation to url1, each recording a second of view time.
1344        note_observation!(&conn,
1345            url "http://mozilla.com/1",
1346            view_time Some(1000),
1347            search_term None,
1348            document_type Some(DocumentType::Regular),
1349            referrer_url Some("https://news.website/tech"),
1350            title None
1351        );
1352
1353        note_observation!(&conn,
1354            url "http://mozilla.com/1",
1355            view_time Some(1000),
1356            search_term None,
1357            document_type Some(DocumentType::Regular),
1358            referrer_url Some("https://news.website/tech"),
1359            title None
1360        );
1361
1362        note_observation!(&conn,
1363            url "http://mozilla.com/1",
1364            view_time Some(1000),
1365            search_term None,
1366            document_type Some(DocumentType::Regular),
1367            referrer_url Some("https://news.website/tech"),
1368            title None
1369        );
1370
1371        // one observation to url2 for 3.5s of view time.
1372        note_observation!(&conn,
1373            url "http://mozilla.com/2",
1374            view_time Some(3500),
1375            search_term None,
1376            document_type Some(DocumentType::Regular),
1377            referrer_url Some("https://news.website/tech"),
1378            title None
1379        );
1380
1381        // The three visits to /2 got "debounced" into a single metadata entry (since they were made in quick succession).
1382        // We'll calculate the scoring as follows:
1383        // - for /1: 1.0 * 1/2 + 1.0 * 3000/6500 = 0.9615...
1384        // - for /2: 1.0 * 1/2 + 1.0 * 3500/6500 = 1.0384...
1385        // (above, 1/2 means 1 entry out of 2 entries total).
1386
1387        let even_weights = HistoryHighlightWeights {
1388            view_time: 1.0,
1389            frequency: 1.0,
1390        };
1391        let highlights1 = get_highlights(&conn, even_weights.clone(), 10).unwrap();
1392        assert_eq!(2, highlights1.len());
1393        assert_eq!("http://mozilla.com/2", highlights1[0].url);
1394
1395        // Since we have an equal amount of metadata entries, providing a very high view_time weight won't change the ranking.
1396        let frequency_heavy_weights = HistoryHighlightWeights {
1397            view_time: 1.0,
1398            frequency: 100.0,
1399        };
1400        let highlights2 = get_highlights(&conn, frequency_heavy_weights, 10).unwrap();
1401        assert_eq!(2, highlights2.len());
1402        assert_eq!("http://mozilla.com/2", highlights2[0].url);
1403
1404        // Now, make an observation for url /1, but with a different metadata key.
1405        // It won't debounce, producing an additional entry for /1.
1406        // Total view time for /1 is now 3100 (vs 3500 for /2).
1407        note_observation!(&conn,
1408            url "http://mozilla.com/1",
1409            view_time Some(100),
1410            search_term Some("test search"),
1411            document_type Some(DocumentType::Regular),
1412            referrer_url Some("https://news.website/tech"),
1413            title None
1414        );
1415
1416        // Since we now have 2 metadata entries for /1, it ranks higher with even weights.
1417        let highlights3 = get_highlights(&conn, even_weights, 10).unwrap();
1418        assert_eq!(2, highlights3.len());
1419        assert_eq!("http://mozilla.com/1", highlights3[0].url);
1420
1421        // With a high-enough weight for view_time, we can flip this order.
1422        // Even though we had 2x entries for /1, it now ranks second due to its lower total view time (3100 vs 3500).
1423        let view_time_heavy_weights = HistoryHighlightWeights {
1424            view_time: 6.0,
1425            frequency: 1.0,
1426        };
1427        let highlights4 = get_highlights(&conn, view_time_heavy_weights, 10).unwrap();
1428        assert_eq!(2, highlights4.len());
1429        assert_eq!("http://mozilla.com/2", highlights4[0].url);
1430    }
1431
1432    #[test]
1433    fn test_get_highlights_no_viewtime() {
1434        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1435
1436        // Make sure we work if the only observations for a URL have a view time of zero.
1437        note_observation!(&conn,
1438            url "http://mozilla.com/1",
1439            view_time Some(0),
1440            search_term None,
1441            document_type Some(DocumentType::Regular),
1442            referrer_url Some("https://news.website/tech"),
1443            title None
1444        );
1445        let highlights = get_highlights(
1446            &conn,
1447            HistoryHighlightWeights {
1448                view_time: 1.0,
1449                frequency: 1.0,
1450            },
1451            2,
1452        )
1453        .unwrap();
1454        assert_eq!(highlights.len(), 1);
1455        assert_eq!(highlights[0].score, 0.0);
1456    }
1457
1458    #[test]
1459    fn test_query() {
1460        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1461        let now = Timestamp::now();
1462
1463        // need a history observation to get a title query working.
1464        let observation1 = VisitObservation::new(Url::parse("https://www.cbc.ca/news/politics/federal-budget-2021-freeland-zimonjic-1.5991021").unwrap())
1465                .with_at(now)
1466                .with_title(Some(String::from("Budget vows to build &#x27;for the long term&#x27; as it promises child care cash, projects massive deficits | CBC News")))
1467                .with_preview_image_url(Some(Url::parse("https://i.cbc.ca/1.5993583.1618861792!/cpImage/httpImage/image.jpg_gen/derivatives/16x9_620/fedbudget-20210419.jpg").unwrap()))
1468                .with_is_remote(false)
1469                .with_visit_type(VisitType::Link);
1470        apply_observation(&conn, observation1).unwrap();
1471
1472        note_observation!(
1473            &conn,
1474            url "https://www.cbc.ca/news/politics/federal-budget-2021-freeland-zimonjic-1.5991021",
1475            view_time Some(20000),
1476            search_term Some("cbc federal budget 2021"),
1477            document_type Some(DocumentType::Regular),
1478            referrer_url Some("https://yandex.ru/search/?text=cbc%20federal%20budget%202021&lr=21512"),
1479            title None
1480        );
1481
1482        note_observation!(
1483            &conn,
1484            url "https://stackoverflow.com/questions/37777675/how-to-create-a-formatted-string-out-of-a-literal-in-rust",
1485            view_time Some(20000),
1486            search_term Some("rust string format"),
1487            document_type Some(DocumentType::Regular),
1488            referrer_url Some("https://yandex.ru/search/?lr=21512&text=rust%20string%20format"),
1489            title None
1490        );
1491
1492        note_observation!(
1493            &conn,
1494            url "https://www.sqlite.org/lang_corefunc.html#instr",
1495            view_time Some(20000),
1496            search_term Some("sqlite like"),
1497            document_type Some(DocumentType::Regular),
1498            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=sqlite+like"),
1499            title None
1500        );
1501
1502        note_observation!(
1503            &conn,
1504            url "https://www.youtube.com/watch?v=tpiyEe_CqB4",
1505            view_time Some(100000),
1506            search_term Some("cute cat"),
1507            document_type Some(DocumentType::Media),
1508            referrer_url Some("https://www.youtube.com/results?search_query=cute+cat"),
1509            title None
1510        );
1511
1512        // query by title
1513        let meta = query(&conn, "child care", 10).expect("query should work");
1514        assert_eq!(1, meta.len(), "expected exactly one result");
1515        assert_history_metadata_record!(meta[0],
1516            url "https://www.cbc.ca/news/politics/federal-budget-2021-freeland-zimonjic-1.5991021",
1517            total_time 20000,
1518            search_term Some("cbc federal budget 2021"),
1519            document_type DocumentType::Regular,
1520            referrer_url Some("https://yandex.ru/search/?text=cbc%20federal%20budget%202021&lr=21512"),
1521            title Some("Budget vows to build &#x27;for the long term&#x27; as it promises child care cash, projects massive deficits | CBC News"),
1522            preview_image_url Some("https://i.cbc.ca/1.5993583.1618861792!/cpImage/httpImage/image.jpg_gen/derivatives/16x9_620/fedbudget-20210419.jpg")
1523        );
1524
1525        // query by search term
1526        let meta = query(&conn, "string format", 10).expect("query should work");
1527        assert_eq!(1, meta.len(), "expected exactly one result");
1528        assert_history_metadata_record!(meta[0],
1529            url "https://stackoverflow.com/questions/37777675/how-to-create-a-formatted-string-out-of-a-literal-in-rust",
1530            total_time 20000,
1531            search_term Some("rust string format"),
1532            document_type DocumentType::Regular,
1533            referrer_url Some("https://yandex.ru/search/?lr=21512&text=rust%20string%20format"),
1534            title None,
1535            preview_image_url None
1536        );
1537
1538        // query by url
1539        let meta = query(&conn, "instr", 10).expect("query should work");
1540        assert_history_metadata_record!(meta[0],
1541            url "https://www.sqlite.org/lang_corefunc.html#instr",
1542            total_time 20000,
1543            search_term Some("sqlite like"),
1544            document_type DocumentType::Regular,
1545            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=sqlite+like"),
1546            title None,
1547            preview_image_url None
1548        );
1549
1550        // by url, referrer domain is different
1551        let meta = query(&conn, "youtube", 10).expect("query should work");
1552        assert_history_metadata_record!(meta[0],
1553            url "https://www.youtube.com/watch?v=tpiyEe_CqB4",
1554            total_time 100000,
1555            search_term Some("cute cat"),
1556            document_type DocumentType::Media,
1557            referrer_url Some("https://www.youtube.com/results?search_query=cute+cat"),
1558            title None,
1559            preview_image_url None
1560        );
1561    }
1562
1563    #[test]
1564    fn test_delete_metadata() {
1565        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1566
1567        // url  |   search_term |   referrer
1568        // 1    |    1          |   1
1569        // 1    |    1          |   0
1570        // 1    |    0          |   1
1571        // 1    |    0          |   0
1572
1573        note_observation!(&conn,
1574            url "http://mozilla.com/1",
1575            view_time Some(20000),
1576            search_term Some("1 with search"),
1577            document_type Some(DocumentType::Regular),
1578            referrer_url Some("http://mozilla.com/"),
1579            title None
1580        );
1581
1582        note_observation!(&conn,
1583            url "http://mozilla.com/1",
1584            view_time Some(20000),
1585            search_term Some("1 with search"),
1586            document_type Some(DocumentType::Regular),
1587            referrer_url None,
1588            title None
1589        );
1590
1591        note_observation!(&conn,
1592            url "http://mozilla.com/1",
1593            view_time Some(20000),
1594            search_term None,
1595            document_type Some(DocumentType::Regular),
1596            referrer_url Some("http://mozilla.com/"),
1597            title None
1598        );
1599
1600        note_observation!(&conn,
1601            url "http://mozilla.com/1",
1602            view_time Some(20000),
1603            search_term None,
1604            document_type Some(DocumentType::Regular),
1605            referrer_url None,
1606            title None
1607        );
1608
1609        note_observation!(&conn,
1610            url "http://mozilla.com/2",
1611            view_time Some(20000),
1612            search_term None,
1613            document_type Some(DocumentType::Regular),
1614            referrer_url None,
1615            title None
1616        );
1617
1618        note_observation!(&conn,
1619            url "http://mozilla.com/2",
1620            view_time Some(20000),
1621            search_term None,
1622            document_type Some(DocumentType::Regular),
1623            referrer_url Some("http://mozilla.com/"),
1624            title None
1625        );
1626
1627        thread::sleep(time::Duration::from_millis(10));
1628        // same observation a bit later:
1629        note_observation!(&conn,
1630            url "http://mozilla.com/2",
1631            view_time Some(20000),
1632            search_term None,
1633            document_type Some(DocumentType::Regular),
1634            referrer_url Some("http://mozilla.com/"),
1635            title None
1636        );
1637
1638        assert_eq!(6, get_since(&conn, 0).expect("get worked").len());
1639        delete_metadata(
1640            &conn,
1641            &Url::parse("http://mozilla.com/1").unwrap(),
1642            None,
1643            None,
1644        )
1645        .expect("delete metadata");
1646        assert_eq!(5, get_since(&conn, 0).expect("get worked").len());
1647
1648        delete_metadata(
1649            &conn,
1650            &Url::parse("http://mozilla.com/1").unwrap(),
1651            Some(&Url::parse("http://mozilla.com/").unwrap()),
1652            None,
1653        )
1654        .expect("delete metadata");
1655        assert_eq!(4, get_since(&conn, 0).expect("get worked").len());
1656
1657        delete_metadata(
1658            &conn,
1659            &Url::parse("http://mozilla.com/1").unwrap(),
1660            Some(&Url::parse("http://mozilla.com/").unwrap()),
1661            Some("1 with search"),
1662        )
1663        .expect("delete metadata");
1664        assert_eq!(3, get_since(&conn, 0).expect("get worked").len());
1665
1666        delete_metadata(
1667            &conn,
1668            &Url::parse("http://mozilla.com/1").unwrap(),
1669            None,
1670            Some("1 with search"),
1671        )
1672        .expect("delete metadata");
1673        assert_eq!(2, get_since(&conn, 0).expect("get worked").len());
1674
1675        // key doesn't match, do nothing
1676        delete_metadata(
1677            &conn,
1678            &Url::parse("http://mozilla.com/2").unwrap(),
1679            Some(&Url::parse("http://wrong-referrer.com").unwrap()),
1680            Some("2 with search"),
1681        )
1682        .expect("delete metadata");
1683        assert_eq!(2, get_since(&conn, 0).expect("get worked").len());
1684    }
1685
1686    #[test]
1687    fn test_delete_older_than() {
1688        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1689
1690        let beginning = Timestamp::now().as_millis() as i64;
1691
1692        note_observation!(&conn,
1693            url "http://mozilla.com/1",
1694            view_time Some(20000),
1695            search_term None,
1696            document_type Some(DocumentType::Regular),
1697            referrer_url None,
1698            title None
1699        );
1700        let after_meta1 = Timestamp::now().as_millis() as i64;
1701
1702        thread::sleep(time::Duration::from_millis(10));
1703
1704        note_observation!(&conn,
1705            url "http://mozilla.com/2",
1706            view_time Some(20000),
1707            search_term None,
1708            document_type Some(DocumentType::Regular),
1709            referrer_url None,
1710            title None
1711        );
1712
1713        thread::sleep(time::Duration::from_millis(10));
1714
1715        note_observation!(&conn,
1716            url "http://mozilla.com/3",
1717            view_time Some(20000),
1718            search_term None,
1719            document_type Some(DocumentType::Regular),
1720            referrer_url None,
1721            title None
1722        );
1723        let after_meta3 = Timestamp::now().as_millis() as i64;
1724
1725        // deleting nothing.
1726        delete_older_than(&conn, beginning).expect("delete worked");
1727        assert_eq!(3, get_since(&conn, beginning).expect("get worked").len());
1728
1729        // boundary condition, should only delete the first one.
1730        delete_older_than(&conn, after_meta1).expect("delete worked");
1731        assert_eq!(2, get_since(&conn, beginning).expect("get worked").len());
1732        assert_eq!(
1733            None,
1734            get_latest_for_url(&conn, &Url::parse("http://mozilla.com/1").expect("url"))
1735                .expect("get")
1736        );
1737
1738        // delete everything now.
1739        delete_older_than(&conn, after_meta3).expect("delete worked");
1740        assert_eq!(0, get_since(&conn, beginning).expect("get worked").len());
1741    }
1742
1743    #[test]
1744    fn test_delete_between() {
1745        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1746
1747        let beginning = Timestamp::now().as_millis() as i64;
1748        thread::sleep(time::Duration::from_millis(10));
1749
1750        note_observation!(&conn,
1751            url "http://mozilla.com/1",
1752            view_time Some(20000),
1753            search_term None,
1754            document_type Some(DocumentType::Regular),
1755            referrer_url None,
1756            title None
1757        );
1758
1759        thread::sleep(time::Duration::from_millis(10));
1760
1761        note_observation!(&conn,
1762            url "http://mozilla.com/2",
1763            view_time Some(20000),
1764            search_term None,
1765            document_type Some(DocumentType::Regular),
1766            referrer_url None,
1767            title None
1768        );
1769        let after_meta2 = Timestamp::now().as_millis() as i64;
1770
1771        thread::sleep(time::Duration::from_millis(10));
1772
1773        note_observation!(&conn,
1774            url "http://mozilla.com/3",
1775            view_time Some(20000),
1776            search_term None,
1777            document_type Some(DocumentType::Regular),
1778            referrer_url None,
1779            title None
1780        );
1781        let after_meta3 = Timestamp::now().as_millis() as i64;
1782
1783        // deleting meta 3
1784        delete_between(&conn, after_meta2, after_meta3).expect("delete worked");
1785        assert_eq!(2, get_since(&conn, beginning).expect("get worked").len());
1786        assert_eq!(
1787            None,
1788            get_latest_for_url(&conn, &Url::parse("http://mozilla.com/3").expect("url"))
1789                .expect("get")
1790        );
1791    }
1792
1793    #[test]
1794    fn test_metadata_deletes_do_not_affect_places() {
1795        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1796
1797        note_observation!(
1798            &conn,
1799            url "https://www.mozilla.org/first/",
1800            view_time Some(20000),
1801            search_term None,
1802            document_type Some(DocumentType::Regular),
1803            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1804            title None
1805        );
1806
1807        note_observation!(
1808            &conn,
1809            url "https://www.mozilla.org/",
1810            view_time Some(20000),
1811            search_term None,
1812            document_type Some(DocumentType::Regular),
1813            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1814            title None
1815        );
1816        let after_meta_added = Timestamp::now().as_millis() as i64;
1817
1818        // Delete all metadata.
1819        delete_older_than(&conn, after_meta_added).expect("delete older than worked");
1820
1821        // Query places. Records there should not have been affected by the delete above.
1822        // 2 for metadata entries + 1 for referrer url.
1823        assert_table_size!(&conn, "moz_places", 3);
1824    }
1825
1826    #[test]
1827    fn test_delete_history_also_deletes_metadata_bookmarked() {
1828        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1829        // Item 1 - bookmarked with regular visits and history metadata
1830        let url = Url::parse("https://www.mozilla.org/bookmarked").unwrap();
1831        let bm_guid: SyncGuid = "bookmarkAAAA".into();
1832        let bm = InsertableBookmark {
1833            parent_guid: BookmarkRootGuid::Unfiled.into(),
1834            position: BookmarkPosition::Append,
1835            date_added: None,
1836            last_modified: None,
1837            guid: Some(bm_guid.clone()),
1838            url: url.clone(),
1839            title: Some("bookmarked page".to_string()),
1840        };
1841        insert_bookmark(&conn, InsertableItem::Bookmark { b: bm }).expect("bookmark should insert");
1842        let obs = VisitObservation::new(url.clone()).with_visit_type(VisitType::Link);
1843        apply_observation(&conn, obs).expect("Should apply visit");
1844        note_observation!(
1845            &conn,
1846            url url.to_string(),
1847            view_time Some(20000),
1848            search_term None,
1849            document_type Some(DocumentType::Regular),
1850            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1851            title None
1852        );
1853
1854        // Check the DB is what we expect before deleting.
1855        assert_eq!(
1856            get_visit_count(&conn, VisitTransitionSet::empty()).unwrap(),
1857            1
1858        );
1859        let place_guid = url_to_guid(&conn, &url)
1860            .expect("is valid")
1861            .expect("should exist");
1862
1863        delete_visits_for(&conn, &place_guid).expect("should work");
1864        // bookmark must still exist.
1865        assert!(get_raw_bookmark(&conn, &bm_guid).unwrap().is_some());
1866        // place exists but has no visits.
1867        let pi = fetch_page_info(&conn, &url)
1868            .expect("should work")
1869            .expect("should exist");
1870        assert!(pi.last_visit_id.is_none());
1871        // and no metadata observations.
1872        assert!(get_latest_for_url(&conn, &url)
1873            .expect("should work")
1874            .is_none());
1875    }
1876
1877    #[test]
1878    fn test_delete_history_also_deletes_metadata_not_bookmarked() {
1879        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1880        // Item is not bookmarked, but has regular visit and a metadata observation.
1881        let url = Url::parse("https://www.mozilla.org/not-bookmarked").unwrap();
1882        let obs = VisitObservation::new(url.clone()).with_visit_type(VisitType::Link);
1883        apply_observation(&conn, obs).expect("Should apply visit");
1884        note_observation!(
1885            &conn,
1886            url url.to_string(),
1887            view_time Some(20000),
1888            search_term None,
1889            document_type Some(DocumentType::Regular),
1890            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1891            title None
1892        );
1893
1894        // Check the DB is what we expect before deleting.
1895        assert_eq!(
1896            get_visit_count(&conn, VisitTransitionSet::empty()).unwrap(),
1897            1
1898        );
1899        let place_guid = url_to_guid(&conn, &url)
1900            .expect("is valid")
1901            .expect("should exist");
1902
1903        delete_visits_for(&conn, &place_guid).expect("should work");
1904        // place no longer exists.
1905        assert!(fetch_page_info(&conn, &url).expect("should work").is_none());
1906        assert!(get_latest_for_url(&conn, &url)
1907            .expect("should work")
1908            .is_none());
1909    }
1910
1911    #[test]
1912    fn test_delete_history_also_deletes_metadata_no_visits() {
1913        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1914        // Item is not bookmarked, no regular visits but a metadata observation.
1915        let url = Url::parse("https://www.mozilla.org/no-visits").unwrap();
1916        note_observation!(
1917            &conn,
1918            url url.to_string(),
1919            view_time Some(20000),
1920            search_term None,
1921            document_type Some(DocumentType::Regular),
1922            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1923            title None
1924        );
1925
1926        // Check the DB is what we expect before deleting.
1927        assert_eq!(
1928            get_visit_count(&conn, VisitTransitionSet::empty()).unwrap(),
1929            0
1930        );
1931        let place_guid = url_to_guid(&conn, &url)
1932            .expect("is valid")
1933            .expect("should exist");
1934
1935        delete_visits_for(&conn, &place_guid).expect("should work");
1936        // place no longer exists.
1937        assert!(fetch_page_info(&conn, &url).expect("should work").is_none());
1938        assert!(get_latest_for_url(&conn, &url)
1939            .expect("should work")
1940            .is_none());
1941    }
1942
1943    #[test]
1944    fn test_delete_between_also_deletes_metadata() -> Result<()> {
1945        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1946
1947        let now = Timestamp::now();
1948        let url = Url::parse("https://www.mozilla.org/").unwrap();
1949        let other_url =
1950            Url::parse("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox")
1951                .unwrap();
1952        let start_timestamp = Timestamp(now.as_millis() - 1000_u64);
1953        let end_timestamp = Timestamp(now.as_millis() + 1000_u64);
1954        let observation1 = VisitObservation::new(url.clone())
1955            .with_at(start_timestamp)
1956            .with_title(Some(String::from("Test page 0")))
1957            .with_is_remote(false)
1958            .with_visit_type(VisitType::Link);
1959
1960        let observation2 = VisitObservation::new(other_url)
1961            .with_at(end_timestamp)
1962            .with_title(Some(String::from("Test page 1")))
1963            .with_is_remote(false)
1964            .with_visit_type(VisitType::Link);
1965
1966        apply_observation(&conn, observation1).expect("Should apply visit");
1967        apply_observation(&conn, observation2).expect("Should apply visit");
1968
1969        note_observation!(
1970            &conn,
1971            url "https://www.mozilla.org/",
1972            view_time Some(20000),
1973            search_term Some("mozilla firefox"),
1974            document_type Some(DocumentType::Regular),
1975            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
1976            title None
1977        );
1978        assert_eq!(
1979            "https://www.mozilla.org/",
1980            get_latest_for_url(&conn, &url)?.unwrap().url
1981        );
1982        delete_visits_between(&conn, start_timestamp, end_timestamp)?;
1983        assert_eq!(None, get_latest_for_url(&conn, &url)?);
1984        Ok(())
1985    }
1986
1987    #[test]
1988    fn test_places_delete_triggers_with_bookmarks() {
1989        // The cleanup functionality lives as a TRIGGER in `create_shared_triggers`.
1990        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
1991
1992        let now = Timestamp::now();
1993        let url = Url::parse("https://www.mozilla.org/").unwrap();
1994        let parent_url =
1995            Url::parse("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox")
1996                .unwrap();
1997
1998        let observation1 = VisitObservation::new(url.clone())
1999            .with_at(now)
2000            .with_title(Some(String::from("Test page 0")))
2001            .with_is_remote(false)
2002            .with_visit_type(VisitType::Link);
2003
2004        let observation2 = VisitObservation::new(parent_url.clone())
2005            .with_at(now)
2006            .with_title(Some(String::from("Test page 1")))
2007            .with_is_remote(false)
2008            .with_visit_type(VisitType::Link);
2009
2010        apply_observation(&conn, observation1).expect("Should apply visit");
2011        apply_observation(&conn, observation2).expect("Should apply visit");
2012
2013        assert_table_size!(&conn, "moz_bookmarks", 5);
2014
2015        // add bookmark for the page we have a metadata entry
2016        insert_bookmark(
2017            &conn,
2018            InsertableItem::Bookmark {
2019                b: InsertableBookmark {
2020                    parent_guid: BookmarkRootGuid::Unfiled.into(),
2021                    position: BookmarkPosition::Append,
2022                    date_added: None,
2023                    last_modified: None,
2024                    guid: Some(SyncGuid::from("cccccccccccc")),
2025                    url,
2026                    title: None,
2027                },
2028            },
2029        )
2030        .expect("bookmark insert worked");
2031
2032        // add another bookmark to the "parent" of our metadata entry
2033        insert_bookmark(
2034            &conn,
2035            InsertableItem::Bookmark {
2036                b: InsertableBookmark {
2037                    parent_guid: BookmarkRootGuid::Unfiled.into(),
2038                    position: BookmarkPosition::Append,
2039                    date_added: None,
2040                    last_modified: None,
2041                    guid: Some(SyncGuid::from("ccccccccccca")),
2042                    url: parent_url,
2043                    title: None,
2044                },
2045            },
2046        )
2047        .expect("bookmark insert worked");
2048
2049        assert_table_size!(&conn, "moz_bookmarks", 7);
2050        assert_table_size!(&conn, "moz_origins", 2);
2051
2052        note_observation!(
2053            &conn,
2054            url "https://www.mozilla.org/",
2055            view_time Some(20000),
2056            search_term Some("mozilla firefox"),
2057            document_type Some(DocumentType::Regular),
2058            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2059            title None
2060        );
2061
2062        assert_table_size!(&conn, "moz_origins", 2);
2063
2064        // this somehow deletes 1 origin record, and our metadata
2065        delete_everything(&conn).expect("places wipe succeeds");
2066
2067        assert_table_size!(&conn, "moz_places_metadata", 0);
2068        assert_table_size!(&conn, "moz_places_metadata_search_queries", 0);
2069    }
2070
2071    #[test]
2072    fn test_places_delete_triggers() {
2073        // The cleanup functionality lives as a TRIGGER in `create_shared_triggers`.
2074        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
2075
2076        let now = Timestamp::now();
2077        let observation1 = VisitObservation::new(Url::parse("https://www.mozilla.org/").unwrap())
2078            .with_at(now)
2079            .with_title(Some(String::from("Test page 1")))
2080            .with_is_remote(false)
2081            .with_visit_type(VisitType::Link);
2082        let observation2 =
2083            VisitObservation::new(Url::parse("https://www.mozilla.org/another/").unwrap())
2084                .with_at(Timestamp(now.as_millis() + 10000))
2085                .with_title(Some(String::from("Test page 3")))
2086                .with_is_remote(false)
2087                .with_visit_type(VisitType::Link);
2088        let observation3 =
2089            VisitObservation::new(Url::parse("https://www.mozilla.org/first/").unwrap())
2090                .with_at(Timestamp(now.as_millis() - 10000))
2091                .with_title(Some(String::from("Test page 0")))
2092                .with_is_remote(true)
2093                .with_visit_type(VisitType::Link);
2094        apply_observation(&conn, observation1).expect("Should apply visit");
2095        apply_observation(&conn, observation2).expect("Should apply visit");
2096        apply_observation(&conn, observation3).expect("Should apply visit");
2097
2098        note_observation!(
2099            &conn,
2100            url "https://www.mozilla.org/first/",
2101            view_time Some(20000),
2102            search_term None,
2103            document_type Some(DocumentType::Regular),
2104            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2105            title None
2106        );
2107
2108        note_observation!(
2109            &conn,
2110            url "https://www.mozilla.org/",
2111            view_time Some(20000),
2112            search_term None,
2113            document_type Some(DocumentType::Regular),
2114            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2115            title None
2116        );
2117
2118        note_observation!(
2119            &conn,
2120            url "https://www.mozilla.org/",
2121            view_time Some(20000),
2122            search_term Some("mozilla"),
2123            document_type Some(DocumentType::Regular),
2124            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2125            title None
2126        );
2127
2128        note_observation!(
2129            &conn,
2130            url "https://www.mozilla.org/",
2131            view_time Some(25000),
2132            search_term Some("firefox"),
2133            document_type Some(DocumentType::Media),
2134            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2135            title None
2136        );
2137
2138        note_observation!(
2139            &conn,
2140            url "https://www.mozilla.org/another/",
2141            view_time Some(20000),
2142            search_term Some("mozilla"),
2143            document_type Some(DocumentType::Regular),
2144            referrer_url Some("https://www.google.com/search?client=firefox-b-d&q=mozilla+firefox"),
2145            title None
2146        );
2147
2148        // double-check that we have the 'firefox' search query entry.
2149        assert!(conn
2150            .try_query_one::<i64, _>(
2151                "SELECT id FROM moz_places_metadata_search_queries WHERE term = :term",
2152                rusqlite::named_params! { ":term": "firefox" },
2153                true
2154            )
2155            .expect("select works")
2156            .is_some());
2157
2158        // Delete our first page & its visits. Note that /another/ page will remain in place.
2159        delete_visits_between(
2160            &conn,
2161            Timestamp(now.as_millis() - 1000),
2162            Timestamp(now.as_millis() + 1000),
2163        )
2164        .expect("delete worked");
2165
2166        let meta1 =
2167            get_latest_for_url(&conn, &Url::parse("https://www.mozilla.org/").expect("url"))
2168                .expect("get worked");
2169        let meta2 = get_latest_for_url(
2170            &conn,
2171            &Url::parse("https://www.mozilla.org/another/").expect("url"),
2172        )
2173        .expect("get worked");
2174
2175        assert!(meta1.is_none(), "expected metadata to have been deleted");
2176        // Verify that if a history metadata entry was entered **after** the visit
2177        // then we delete the range of the metadata, and not the visit. The metadata
2178        // is still deleted
2179        assert!(meta2.is_none(), "expected metadata to been deleted");
2180
2181        // The 'mozilla' search query entry is deleted since the delete cascades.
2182        assert!(
2183            conn.try_query_one::<i64, _>(
2184                "SELECT id FROM moz_places_metadata_search_queries WHERE term = :term",
2185                rusqlite::named_params! { ":term": "mozilla" },
2186                true
2187            )
2188            .expect("select works")
2189            .is_none(),
2190            "search_query records with related metadata should have been deleted"
2191        );
2192
2193        // don't have the 'firefox' search query entry either, nothing points to it.
2194        assert!(
2195            conn.try_query_one::<i64, _>(
2196                "SELECT id FROM moz_places_metadata_search_queries WHERE term = :term",
2197                rusqlite::named_params! { ":term": "firefox" },
2198                true
2199            )
2200            .expect("select works")
2201            .is_none(),
2202            "search_query records without related metadata should have been deleted"
2203        );
2204
2205        // now, let's wipe places, and make sure none of the metadata stuff remains.
2206        delete_everything(&conn).expect("places wipe succeeds");
2207
2208        assert_table_size!(&conn, "moz_places_metadata", 0);
2209        assert_table_size!(&conn, "moz_places_metadata_search_queries", 0);
2210    }
2211
2212    #[test]
2213    fn test_if_page_missing_behavior() {
2214        let conn = PlacesDb::open_in_memory(ConnectionType::ReadWrite).expect("memory db");
2215
2216        note_observation!(
2217            &conn,
2218            NoteHistoryMetadataObservationOptions::new()
2219                .if_page_missing(HistoryMetadataPageMissingBehavior::IgnoreObservation),
2220            url "https://www.example.com/",
2221            view_time None,
2222            search_term None,
2223            document_type Some(DocumentType::Regular),
2224            referrer_url None,
2225            title None
2226        );
2227
2228        let observations = get_since(&conn, 0).expect("should get all metadata observations");
2229        assert_eq!(observations, &[]);
2230
2231        let visit_observation =
2232            VisitObservation::new(Url::parse("https://www.example.com/").unwrap())
2233                .with_at(Timestamp::now());
2234        apply_observation(&conn, visit_observation).expect("should apply visit observation");
2235
2236        note_observation!(
2237            &conn,
2238            NoteHistoryMetadataObservationOptions::new()
2239                .if_page_missing(HistoryMetadataPageMissingBehavior::IgnoreObservation),
2240            url "https://www.example.com/",
2241            view_time None,
2242            search_term None,
2243            document_type Some(DocumentType::Regular),
2244            referrer_url None,
2245            title None
2246        );
2247
2248        let observations = get_since(&conn, 0).expect("should get all metadata observations");
2249        assert_eq!(
2250            observations
2251                .into_iter()
2252                .map(|m| m.url)
2253                .collect::<Vec<String>>(),
2254            &["https://www.example.com/"]
2255        );
2256
2257        note_observation!(
2258            &conn,
2259            NoteHistoryMetadataObservationOptions::new()
2260                .if_page_missing(HistoryMetadataPageMissingBehavior::InsertPage),
2261            url "https://www.example.org/",
2262            view_time None,
2263            search_term None,
2264            document_type Some(DocumentType::Regular),
2265            referrer_url None,
2266            title None
2267        );
2268
2269        let observations = get_since(&conn, 0).expect("should get all metadata observations");
2270        assert_eq!(
2271            observations
2272                .into_iter()
2273                .map(|m| m.url)
2274                .collect::<Vec<String>>(),
2275            &[
2276                "https://www.example.org/", // Newest first.
2277                "https://www.example.com/",
2278            ],
2279        );
2280    }
2281}