1 // Copyright 2020 The Chromium Authors. All rights reserved.
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
4 
5 #include "chrome/browser/media/history/media_history_feeds_table.h"
6 
7 #include "base/metrics/histogram_functions.h"
8 #include "base/strings/strcat.h"
9 #include "base/strings/string_split.h"
10 #include "base/strings/string_util.h"
11 #include "base/strings/stringprintf.h"
12 #include "base/unguessable_token.h"
13 #include "base/updateable_sequenced_task_runner.h"
14 #include "chrome/browser/media/feeds/media_feeds.pb.h"
15 #include "chrome/browser/media/feeds/media_feeds_utils.h"
16 #include "chrome/browser/media/history/media_history_origin_table.h"
17 #include "chrome/browser/media/history/media_history_store.h"
18 #include "sql/statement.h"
19 #include "url/gurl.h"
20 
21 namespace media_history {
22 
23 namespace {
24 
25 // The maximum number of logos to allow.
26 const int kMaxLogoCount = 5;
27 
ProtoToUnguessableToken(const media_feeds::FeedResetToken & proto)28 base::UnguessableToken ProtoToUnguessableToken(
29     const media_feeds::FeedResetToken& proto) {
30   return base::UnguessableToken::Deserialize(proto.high(), proto.low());
31 }
32 
AssignStatement(sql::Statement * statement,sql::Database * db,const sql::StatementID & id,const std::vector<std::string> & sql)33 void AssignStatement(sql::Statement* statement,
34                      sql::Database* db,
35                      const sql::StatementID& id,
36                      const std::vector<std::string>& sql) {
37   statement->Assign(
38       db->GetCachedStatement(id, base::JoinString(sql, " ").c_str()));
39 }
40 
41 }  // namespace
42 
43 const char MediaHistoryFeedsTable::kTableName[] = "mediaFeed";
44 
45 const char MediaHistoryFeedsTable::kFeedReadResultHistogramName[] =
46     "Media.Feeds.Feed.ReadResult";
47 
MediaHistoryFeedsTable(scoped_refptr<base::UpdateableSequencedTaskRunner> db_task_runner)48 MediaHistoryFeedsTable::MediaHistoryFeedsTable(
49     scoped_refptr<base::UpdateableSequencedTaskRunner> db_task_runner)
50     : MediaHistoryTableBase(std::move(db_task_runner)) {}
51 
52 MediaHistoryFeedsTable::~MediaHistoryFeedsTable() = default;
53 
CreateTableIfNonExistent()54 sql::InitStatus MediaHistoryFeedsTable::CreateTableIfNonExistent() {
55   if (!CanAccessDatabase())
56     return sql::INIT_FAILURE;
57 
58   bool success = DB()->Execute(
59       base::StringPrintf("CREATE TABLE IF NOT EXISTS %s("
60                          "id INTEGER PRIMARY KEY AUTOINCREMENT,"
61                          "origin_id INTEGER NOT NULL UNIQUE,"
62                          "url TEXT NOT NULL, "
63                          "last_discovery_time_s INTEGER, "
64                          "last_fetch_time_s INTEGER, "
65                          "user_status INTEGER DEFAULT 0, "
66                          "last_fetch_result INTEGER DEFAULT 0, "
67                          "fetch_failed_count INTEGER, "
68                          "last_fetch_time_not_cache_hit_s INTEGER, "
69                          "last_fetch_item_count INTEGER, "
70                          "last_fetch_safe_item_count INTEGER, "
71                          "last_fetch_play_next_count INTEGER, "
72                          "last_fetch_content_types INTEGER, "
73                          "logo BLOB, "
74                          "display_name TEXT, "
75                          "user_identifier BLOB, "
76                          "last_display_time_s INTEGER, "
77                          "reset_reason INTEGER DEFAULT 0, "
78                          "reset_token BLOB, "
79                          "cookie_name_filter TEXT, "
80                          "safe_search_result INTEGER DEFAULT 0, "
81                          "CONSTRAINT fk_origin "
82                          "FOREIGN KEY (origin_id) "
83                          "REFERENCES origin(id) "
84                          "ON DELETE CASCADE"
85                          ")",
86                          kTableName)
87           .c_str());
88 
89   if (success) {
90     success = DB()->Execute(
91         base::StringPrintf(
92             "CREATE INDEX IF NOT EXISTS mediaFeed_origin_id_index ON "
93             "%s (origin_id)",
94             kTableName)
95             .c_str());
96   }
97 
98   if (success) {
99     success = DB()->Execute(
100         "CREATE INDEX IF NOT EXISTS mediaFeed_fetch_time_index ON "
101         "mediaFeed (last_fetch_time_s)");
102   }
103 
104   if (success) {
105     success = DB()->Execute(
106         "CREATE INDEX IF NOT EXISTS mediaFeed_safe_search_result ON "
107         "mediaFeed (safe_search_result)");
108   }
109 
110   if (success) {
111     success = DB()->Execute(
112         "CREATE INDEX IF NOT EXISTS mediaFeed_last_fetch_content_types_index "
113         "ON "
114         "mediaFeed (last_fetch_content_types)");
115   }
116 
117   if (success) {
118     success = DB()->Execute(
119         "CREATE INDEX IF NOT EXISTS mediaFeed_top_feeds_index ON "
120         "mediaFeed (last_fetch_content_types, safe_search_result)");
121   }
122 
123   if (success) {
124     success = DB()->Execute(
125         "CREATE INDEX IF NOT EXISTS mediaFeed_user_status_index ON "
126         "mediaFeed (user_status)");
127   }
128 
129   if (!success) {
130     ResetDB();
131     LOG(ERROR) << "Failed to create media history feeds table.";
132     return sql::INIT_FAILURE;
133   }
134 
135   return sql::INIT_OK;
136 }
137 
DiscoverFeed(const GURL & url)138 bool MediaHistoryFeedsTable::DiscoverFeed(const GURL& url) {
139   DCHECK_LT(0, DB()->transaction_nesting());
140   if (!CanAccessDatabase())
141     return false;
142 
143   const auto origin =
144       MediaHistoryOriginTable::GetOriginForStorage(url::Origin::Create(url));
145   const auto now = base::Time::Now().ToDeltaSinceWindowsEpoch().InSeconds();
146 
147   base::Optional<GURL> feed_url;
148   base::Optional<int64_t> feed_id;
149 
150   {
151     // Check if we already have a feed for the current origin;
152     sql::Statement statement(DB()->GetCachedStatement(
153         SQL_FROM_HERE,
154         "SELECT id, url FROM mediaFeed WHERE origin_id = (SELECT id FROM "
155         "origin WHERE origin = ?)"));
156     statement.BindString(0, origin);
157 
158     while (statement.Step()) {
159       DCHECK(!feed_id);
160       DCHECK(!feed_url);
161 
162       feed_id = statement.ColumnInt64(0);
163       feed_url = GURL(statement.ColumnString(1));
164     }
165   }
166 
167   if (!feed_url || url != feed_url) {
168     // If the feed does not exist or exists and has a different URL then we
169     // should replace the feed.
170     sql::Statement statement(DB()->GetCachedStatement(
171         SQL_FROM_HERE,
172         "INSERT OR REPLACE INTO mediaFeed "
173         "(origin_id, url, last_discovery_time_s) VALUES "
174         "((SELECT id FROM origin WHERE origin = ?), ?, ?)"));
175     statement.BindString(0, origin);
176     statement.BindString(1, url.spec());
177     statement.BindInt64(2, now);
178     return statement.Run() && DB()->GetLastChangeCount() == 1;
179   } else {
180     // If the feed already exists in the database with the same URL we should
181     // just update the last discovery time so we don't delete the old entry.
182     sql::Statement statement(DB()->GetCachedStatement(
183         SQL_FROM_HERE,
184         "UPDATE mediaFeed SET last_discovery_time_s = ? WHERE id = ?"));
185     statement.BindInt64(0, now);
186     statement.BindInt64(1, *feed_id);
187     return statement.Run() && DB()->GetLastChangeCount() == 1;
188   }
189 }
190 
GetRows(const MediaHistoryKeyedService::GetMediaFeedsRequest & request)191 std::vector<media_feeds::mojom::MediaFeedPtr> MediaHistoryFeedsTable::GetRows(
192     const MediaHistoryKeyedService::GetMediaFeedsRequest& request) {
193   std::vector<media_feeds::mojom::MediaFeedPtr> feeds;
194   if (!CanAccessDatabase())
195     return feeds;
196 
197   base::Optional<double> origin_count;
198   double rank = 0;
199 
200   const bool top_feeds =
201       request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::Type::
202                           kTopFeedsForFetch ||
203       request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::Type::
204                           kTopFeedsForDisplay;
205 
206   std::vector<std::string> sql;
207   sql.push_back(
208       "SELECT "
209       "mediaFeed.id, "
210       "mediaFeed.url, "
211       "mediaFeed.last_discovery_time_s, "
212       "mediaFeed.last_fetch_time_s, "
213       "mediaFeed.user_status, "
214       "mediaFeed.last_fetch_result, "
215       "mediaFeed.fetch_failed_count, "
216       "mediaFeed.last_fetch_time_not_cache_hit_s, "
217       "mediaFeed.last_fetch_item_count, "
218       "mediaFeed.last_fetch_safe_item_count, "
219       "mediaFeed.last_fetch_play_next_count, "
220       "mediaFeed.last_fetch_content_types, "
221       "mediaFeed.logo, "
222       "mediaFeed.display_name, "
223       "mediaFeed.last_display_time_s, "
224       "mediaFeed.reset_reason, "
225       "mediaFeed.user_identifier, "
226       "mediaFeed.cookie_name_filter, "
227       "mediaFeed.safe_search_result, "
228       "mediaFeed.reset_token ");
229 
230   sql::Statement statement;
231 
232   if (top_feeds) {
233     // Check the request has the right parameters.
234     DCHECK(request.limit.has_value());
235 
236     if (request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::Type::
237                             kTopFeedsForDisplay) {
238       DCHECK(request.fetched_items_min.has_value());
239     } else if (request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::
240                                    Type::kTopFeedsForFetch) {
241       DCHECK(request.audio_video_watchtime_min.has_value());
242     }
243 
244     // If we need the top feeds we should select rows from the origin table and
245     // LEFT JOIN mediaFeed. This means there should be a row for each origin
246     // and if there is a media feed that will be included.
247     sql.push_back(
248         ",origin.aggregate_watchtime_audio_video_s "
249         "FROM origin "
250         "LEFT JOIN mediaFeed "
251         "ON origin.id = mediaFeed.origin_id");
252 
253     // If we have an audio/video watchtime requirement we should add that.
254     if (request.audio_video_watchtime_min.has_value())
255       sql.push_back("WHERE origin.aggregate_watchtime_audio_video_s >= ?");
256 
257     // If we have a content type filter then we should add that.
258     if (request.filter_by_type.has_value())
259       sql.push_back("WHERE mediaFeed.last_fetch_content_types & ?");
260 
261     // Finally, order the results by watchtime.
262     sql.push_back("ORDER BY origin.aggregate_watchtime_audio_video_s DESC");
263 
264     // Get the total count of the origins so we can calculate a percentile.
265     sql::Statement origin_statement(DB()->GetCachedStatement(
266         SQL_FROM_HERE, "SELECT COUNT(id) FROM origin"));
267 
268     while (origin_statement.Step()) {
269       origin_count = origin_statement.ColumnDouble(0);
270       rank = *origin_count;
271     }
272 
273     DCHECK(origin_count.has_value());
274 
275     // For each different query combination we should have an assign statement
276     // call that will generate a unique SQL_FROM_HERE value.
277     if (request.audio_video_watchtime_min.has_value() &&
278         request.filter_by_type) {
279       AssignStatement(&statement, DB(), SQL_FROM_HERE, sql);
280     } else if (request.audio_video_watchtime_min.has_value()) {
281       AssignStatement(&statement, DB(), SQL_FROM_HERE, sql);
282     } else if (request.filter_by_type) {
283       AssignStatement(&statement, DB(), SQL_FROM_HERE, sql);
284     } else {
285       AssignStatement(&statement, DB(), SQL_FROM_HERE, sql);
286     }
287 
288     // Now bind all the parameters to the query.
289     int bind_index = 0;
290 
291     if (request.audio_video_watchtime_min.has_value()) {
292       statement.BindInt64(bind_index++,
293                           request.audio_video_watchtime_min->InSeconds());
294     }
295 
296     if (request.filter_by_type.has_value()) {
297       statement.BindInt64(bind_index++,
298                           static_cast<int>(*request.filter_by_type));
299     }
300   } else if (request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::
301                                  Type::kSelectedFeedsForFetch) {
302     sql.push_back("FROM mediaFeed WHERE user_status = ?");
303 
304     statement.Assign(DB()->GetCachedStatement(
305         SQL_FROM_HERE, base::JoinString(sql, " ").c_str()));
306 
307     statement.BindInt64(
308         0, static_cast<int>(media_feeds::mojom::FeedUserStatus::kEnabled));
309   } else {
310     sql.push_back("FROM mediaFeed");
311 
312     statement.Assign(DB()->GetCachedStatement(
313         SQL_FROM_HERE, base::JoinString(sql, " ").c_str()));
314   }
315 
316   while (statement.Step()) {
317     rank--;
318 
319     // If there is no mediaFeed data then skip this.
320     if (statement.GetColumnType(0) == sql::ColumnType::kNull)
321       continue;
322 
323     auto feed = media_feeds::mojom::MediaFeed::New();
324     feed->last_fetch_item_count = statement.ColumnInt64(8);
325     feed->last_fetch_safe_item_count = statement.ColumnInt64(9);
326 
327     // If we are getting the top feeds for display then we should filter by
328     // the number of fetched items or fetched safe search items.
329     if (request.type == MediaHistoryKeyedService::GetMediaFeedsRequest::Type::
330                             kTopFeedsForDisplay) {
331       if (request.fetched_items_min_should_be_safe) {
332         if (feed->last_fetch_safe_item_count < *request.fetched_items_min)
333           continue;
334       } else {
335         if (feed->last_fetch_item_count < *request.fetched_items_min)
336           continue;
337       }
338     }
339 
340     feed->user_status = static_cast<media_feeds::mojom::FeedUserStatus>(
341         statement.ColumnInt64(4));
342     feed->last_fetch_result =
343         static_cast<media_feeds::mojom::FetchResult>(statement.ColumnInt64(5));
344     feed->reset_reason =
345         static_cast<media_feeds::mojom::ResetReason>(statement.ColumnInt64(15));
346     feed->safe_search_result =
347         static_cast<media_feeds::mojom::SafeSearchResult>(
348             statement.ColumnInt64(18));
349 
350     if (!IsKnownEnumValue(feed->user_status)) {
351       base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
352                                     FeedReadResult::kBadUserStatus);
353       continue;
354     }
355 
356     if (!IsKnownEnumValue(feed->last_fetch_result)) {
357       base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
358                                     FeedReadResult::kBadFetchResult);
359       continue;
360     }
361 
362     if (!IsKnownEnumValue(feed->reset_reason)) {
363       base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
364                                     FeedReadResult::kBadResetReason);
365       continue;
366     }
367 
368     if (!IsKnownEnumValue(feed->safe_search_result)) {
369       base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
370                                     FeedReadResult::kBadSafeSearchResult);
371       continue;
372     }
373 
374     if (statement.GetColumnType(12) == sql::ColumnType::kBlob) {
375       media_feeds::ImageSet image_set;
376       if (!GetProto(statement, 12, image_set)) {
377         base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
378                                       FeedReadResult::kBadLogo);
379 
380         continue;
381       }
382 
383       feed->logos = media_feeds::ProtoToMediaImages(image_set, kMaxLogoCount);
384     }
385 
386     base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
387                                   FeedReadResult::kSuccess);
388 
389     feed->id = statement.ColumnInt64(0);
390     feed->url = GURL(statement.ColumnString(1));
391     feed->last_discovery_time = base::Time::FromDeltaSinceWindowsEpoch(
392         base::TimeDelta::FromSeconds(statement.ColumnInt64(2)));
393 
394     if (statement.GetColumnType(3) == sql::ColumnType::kInteger) {
395       feed->last_fetch_time = base::Time::FromDeltaSinceWindowsEpoch(
396           base::TimeDelta::FromSeconds(statement.ColumnInt64(3)));
397     }
398 
399     feed->fetch_failed_count = statement.ColumnInt64(6);
400 
401     if (statement.GetColumnType(7) == sql::ColumnType::kInteger) {
402       feed->last_fetch_time_not_cache_hit =
403           base::Time::FromDeltaSinceWindowsEpoch(
404               base::TimeDelta::FromSeconds(statement.ColumnInt64(7)));
405     }
406 
407     feed->last_fetch_play_next_count = statement.ColumnInt64(10);
408     feed->last_fetch_content_types = statement.ColumnInt64(11);
409     feed->display_name = statement.ColumnString(13);
410 
411     if (statement.GetColumnType(14) == sql::ColumnType::kInteger) {
412       feed->last_display_time = base::Time::FromDeltaSinceWindowsEpoch(
413           base::TimeDelta::FromSeconds(statement.ColumnInt64(14)));
414     }
415 
416     if (top_feeds && origin_count > 1) {
417       feed->origin_audio_video_watchtime_percentile =
418           (rank / (*origin_count - 1)) * 100;
419     } else if (top_feeds) {
420       DCHECK_EQ(1, *origin_count);
421       feed->origin_audio_video_watchtime_percentile = 100;
422     }
423 
424     if (statement.GetColumnType(16) == sql::ColumnType::kBlob) {
425       media_feeds::UserIdentifier identifier;
426       if (!GetProto(statement, 16, identifier)) {
427         base::UmaHistogramEnumeration(kFeedReadResultHistogramName,
428                                       FeedReadResult::kBadUserIdentifier);
429 
430         continue;
431       }
432 
433       feed->user_identifier = media_feeds::mojom::UserIdentifier::New();
434       feed->user_identifier->name = identifier.name();
435       feed->user_identifier->email = identifier.email();
436 
437       auto image_url = GURL(identifier.image().url());
438 
439       if (image_url.is_valid())
440         feed->user_identifier->image = ProtoToMediaImage(identifier.image());
441     }
442 
443     if (statement.GetColumnType(17) == sql::ColumnType::kText)
444       feed->cookie_name_filter = statement.ColumnString(17);
445 
446     if (statement.GetColumnType(19) == sql::ColumnType::kBlob) {
447       media_feeds::FeedResetToken token;
448       if (GetProto(statement, 19, token))
449         feed->reset_token = ProtoToUnguessableToken(token);
450     }
451 
452     if (top_feeds) {
453       feed->aggregate_watchtime =
454           base::TimeDelta::FromSeconds(statement.ColumnInt64(20));
455     }
456 
457     feeds.push_back(std::move(feed));
458 
459     // If we are returning top feeds then we should apply a limit here.
460     if (top_feeds && feeds.size() >= *request.limit)
461       break;
462   }
463 
464   DCHECK(statement.Succeeded());
465   return feeds;
466 }
467 
UpdateFeedFromFetch(const int64_t feed_id,const media_feeds::mojom::FetchResult result,const bool was_fetched_from_cache,const int item_count,const int item_play_next_count,const int item_content_types,const std::vector<media_feeds::mojom::MediaImagePtr> & logos,const media_feeds::mojom::UserIdentifier * user_identifier,const std::string & display_name,const int item_safe_count,const std::string & cookie_name_filter)468 bool MediaHistoryFeedsTable::UpdateFeedFromFetch(
469     const int64_t feed_id,
470     const media_feeds::mojom::FetchResult result,
471     const bool was_fetched_from_cache,
472     const int item_count,
473     const int item_play_next_count,
474     const int item_content_types,
475     const std::vector<media_feeds::mojom::MediaImagePtr>& logos,
476     const media_feeds::mojom::UserIdentifier* user_identifier,
477     const std::string& display_name,
478     const int item_safe_count,
479     const std::string& cookie_name_filter) {
480   DCHECK_LT(0, DB()->transaction_nesting());
481   if (!CanAccessDatabase())
482     return false;
483 
484   int fetch_failed_count = 0;
485 
486   {
487     if (result != media_feeds::mojom::FetchResult::kSuccess) {
488       // See how many times we have failed to fetch the feed.
489       sql::Statement statement(DB()->GetCachedStatement(
490           SQL_FROM_HERE,
491           "SELECT fetch_failed_count FROM mediaFeed WHERE id = ?"));
492       statement.BindInt64(0, feed_id);
493 
494       while (statement.Step()) {
495         DCHECK(!fetch_failed_count);
496         fetch_failed_count = statement.ColumnInt64(0) + 1;
497       }
498     }
499   }
500 
501   sql::Statement statement;
502   if (was_fetched_from_cache) {
503     statement.Assign(DB()->GetCachedStatement(
504         SQL_FROM_HERE,
505         "UPDATE mediaFeed SET last_fetch_time_s = ?, last_fetch_result = ?, "
506         "fetch_failed_count = ?, last_fetch_item_count = ?, "
507         "last_fetch_play_next_count = ?, last_fetch_content_types = ?, "
508         "logo = ?, display_name = ?, last_fetch_safe_item_count = ?, "
509         "user_identifier = ?, cookie_name_filter = ? WHERE id = ?"));
510   } else {
511     statement.Assign(DB()->GetCachedStatement(
512         SQL_FROM_HERE,
513         "UPDATE mediaFeed SET last_fetch_time_s = ?, last_fetch_result = ?, "
514         "fetch_failed_count = ?, last_fetch_item_count = ?, "
515         "last_fetch_play_next_count = ?, last_fetch_content_types = ?, "
516         "logo = ?, display_name = ?, last_fetch_safe_item_count = ?, "
517         "user_identifier = ?, cookie_name_filter = ?, "
518         "last_fetch_time_not_cache_hit_s = ? "
519         "WHERE id = ?"));
520   }
521 
522   statement.BindInt64(0,
523                       base::Time::Now().ToDeltaSinceWindowsEpoch().InSeconds());
524   statement.BindInt64(1, static_cast<int>(result));
525   statement.BindInt64(2, fetch_failed_count);
526   statement.BindInt64(3, item_count);
527   statement.BindInt64(4, item_play_next_count);
528   statement.BindInt64(5, item_content_types);
529 
530   if (!logos.empty()) {
531     BindProto(statement, 6,
532               media_feeds::MediaImagesToProto(logos, kMaxLogoCount));
533   } else {
534     statement.BindNull(6);
535   }
536 
537   statement.BindString(7, display_name);
538   statement.BindInt64(8, item_safe_count);
539 
540   if (user_identifier) {
541     media_feeds::UserIdentifier proto_id;
542     proto_id.set_name(user_identifier->name);
543     if (user_identifier->email.has_value())
544       proto_id.set_email(user_identifier->email.value());
545 
546     media_feeds::MediaImageToProto(proto_id.mutable_image(),
547                                    user_identifier->image);
548 
549     BindProto(statement, 9, proto_id);
550   } else {
551     statement.BindNull(9);
552   }
553 
554   if (!cookie_name_filter.empty()) {
555     statement.BindString(10, cookie_name_filter);
556   } else {
557     statement.BindNull(10);
558   }
559 
560   if (was_fetched_from_cache) {
561     statement.BindInt64(11, feed_id);
562   } else {
563     statement.BindInt64(
564         11, base::Time::Now().ToDeltaSinceWindowsEpoch().InSeconds());
565     statement.BindInt64(12, feed_id);
566   }
567 
568   return statement.Run() && DB()->GetLastChangeCount() == 1;
569 }
570 
UpdateDisplayTime(const int64_t feed_id)571 bool MediaHistoryFeedsTable::UpdateDisplayTime(const int64_t feed_id) {
572   DCHECK_LT(0, DB()->transaction_nesting());
573   if (!CanAccessDatabase())
574     return false;
575 
576   sql::Statement statement(DB()->GetCachedStatement(
577       SQL_FROM_HERE,
578       "UPDATE mediaFeed SET last_display_time_s = ? WHERE id = ?"));
579 
580   statement.BindInt64(0,
581                       base::Time::Now().ToDeltaSinceWindowsEpoch().InSeconds());
582   statement.BindInt64(1, feed_id);
583   return statement.Run() && DB()->GetLastChangeCount() == 1;
584 }
585 
RecalculateSafeSearchItemCount(const int64_t feed_id)586 bool MediaHistoryFeedsTable::RecalculateSafeSearchItemCount(
587     const int64_t feed_id) {
588   DCHECK_LT(0, DB()->transaction_nesting());
589   if (!CanAccessDatabase())
590     return false;
591 
592   sql::Statement statement(DB()->GetCachedStatement(
593       SQL_FROM_HERE,
594       "UPDATE mediaFeed SET last_fetch_safe_item_count = (SELECT COUNT(id) "
595       "FROM mediaFeedItem WHERE safe_search_result = ? AND feed_id = ?) WHERE "
596       "id = ?"));
597   statement.BindInt64(
598       0, static_cast<int>(media_feeds::mojom::SafeSearchResult::kSafe));
599   statement.BindInt64(1, feed_id);
600   statement.BindInt64(2, feed_id);
601   return statement.Run() && DB()->GetLastChangeCount() == 1;
602 }
603 
Reset(const int64_t feed_id,const media_feeds::mojom::ResetReason reason)604 bool MediaHistoryFeedsTable::Reset(
605     const int64_t feed_id,
606     const media_feeds::mojom::ResetReason reason) {
607   DCHECK_LT(0, DB()->transaction_nesting());
608   if (!CanAccessDatabase())
609     return false;
610 
611   sql::Statement statement(DB()->GetCachedStatement(
612       SQL_FROM_HERE,
613       "UPDATE mediaFeed SET last_fetch_time_s = NULL, last_fetch_result = 0, "
614       "fetch_failed_count = 0, last_fetch_time_not_cache_hit_s = NULL, "
615       "last_fetch_item_count = 0, last_fetch_safe_item_count = 0, "
616       "last_fetch_play_next_count = 0, last_fetch_content_types = 0, "
617       "logo = NULL, display_name = NULL, user_identifier = NULL, "
618       "reset_reason = ?, reset_token = ? WHERE id = ?"));
619 
620   statement.BindInt64(0, static_cast<int>(reason));
621 
622   // Store a new feed reset token to invalidate any fetches.
623   auto token = base::UnguessableToken::Create();
624   media_feeds::FeedResetToken proto_token;
625   proto_token.set_high(token.GetHighForSerialization());
626   proto_token.set_low(token.GetLowForSerialization());
627   BindProto(statement, 1, proto_token);
628 
629   statement.BindInt64(2, feed_id);
630 
631   return statement.Run() && DB()->GetLastChangeCount() == 1;
632 }
633 
634 base::Optional<MediaHistoryKeyedService::MediaFeedFetchDetails>
GetFetchDetails(const int64_t feed_id)635 MediaHistoryFeedsTable::GetFetchDetails(const int64_t feed_id) {
636   if (!CanAccessDatabase())
637     return base::nullopt;
638 
639   sql::Statement statement(
640       DB()->GetCachedStatement(SQL_FROM_HERE,
641                                "SELECT url, last_fetch_result, reset_token "
642                                "FROM mediaFeed WHERE id = ?"));
643   statement.BindInt64(0, feed_id);
644 
645   while (statement.Step()) {
646     MediaHistoryKeyedService::MediaFeedFetchDetails details;
647     details.url = GURL(statement.ColumnString(0));
648 
649     if (!details.url.is_valid())
650       return base::nullopt;
651 
652     details.last_fetch_result =
653         static_cast<media_feeds::mojom::FetchResult>(statement.ColumnInt64(1));
654     if (!IsKnownEnumValue(details.last_fetch_result))
655       return base::nullopt;
656 
657     if (statement.GetColumnType(2) == sql::ColumnType::kBlob) {
658       media_feeds::FeedResetToken token;
659       if (!GetProto(statement, 2, token))
660         return base::nullopt;
661       details.reset_token = ProtoToUnguessableToken(token);
662     }
663 
664     return details;
665   }
666 
667   return base::nullopt;
668 }
669 
Delete(const int64_t feed_id)670 bool MediaHistoryFeedsTable::Delete(const int64_t feed_id) {
671   sql::Statement statement(DB()->GetCachedStatement(
672       SQL_FROM_HERE, "DELETE FROM mediaFeed WHERE id = ?"));
673   statement.BindInt64(0, feed_id);
674   return statement.Run() && DB()->GetLastChangeCount() >= 1;
675 }
676 
ClearResetReason(const int64_t feed_id)677 bool MediaHistoryFeedsTable::ClearResetReason(const int64_t feed_id) {
678   sql::Statement statement(DB()->GetCachedStatement(
679       SQL_FROM_HERE, "UPDATE mediaFeed SET reset_reason = ? WHERE id = ?"));
680   statement.BindInt64(0,
681                       static_cast<int>(media_feeds::mojom::ResetReason::kNone));
682   statement.BindInt64(1, feed_id);
683   return statement.Run() && DB()->GetLastChangeCount() == 1;
684 }
685 
GetCookieNameFilter(const int64_t feed_id)686 std::string MediaHistoryFeedsTable::GetCookieNameFilter(const int64_t feed_id) {
687   DCHECK_LT(0, DB()->transaction_nesting());
688   if (!CanAccessDatabase())
689     return std::string();
690 
691   sql::Statement statement(DB()->GetCachedStatement(
692       SQL_FROM_HERE, "SELECT cookie_name_filter FROM mediaFeed WHERE id = ?"));
693   statement.BindInt64(0, feed_id);
694 
695   while (statement.Step())
696     return statement.ColumnString(0);
697 
698   return std::string();
699 }
700 
GetFeedsForOriginSubdomain(const url::Origin & origin)701 std::set<int64_t> MediaHistoryFeedsTable::GetFeedsForOriginSubdomain(
702     const url::Origin& origin) {
703   std::set<int64_t> feeds;
704   if (!CanAccessDatabase())
705     return feeds;
706 
707   sql::Statement statement(DB()->GetCachedStatement(
708       SQL_FROM_HERE,
709       "SELECT id, url FROM mediaFeed WHERE url LIKE ? AND (last_fetch_result > "
710       "0 OR reset_reason > 0)"));
711 
712   std::vector<std::string> wildcard_parts = base::SplitString(
713       MediaHistoryOriginTable::GetOriginForStorage(origin),
714       url::kStandardSchemeSeparator, base::WhitespaceHandling::TRIM_WHITESPACE,
715       base::SPLIT_WANT_NONEMPTY);
716 
717   if (wildcard_parts.size() != 2)
718     return feeds;
719 
720   statement.BindString(
721       0, base::StrCat({wildcard_parts[0], url::kStandardSchemeSeparator, "%.",
722                        wildcard_parts[1], "/%"}));
723 
724   while (statement.Step()) {
725     // This shouldn't happen but is a backup so we don't accidentally reset
726     // feeds that we should not.
727     auto url = GURL(statement.ColumnString(1));
728     if (!url.DomainIs(origin.host()))
729       continue;
730 
731     feeds.insert(statement.ColumnInt64(0));
732   }
733 
734   return feeds;
735 }
736 
GetFeedForOrigin(const url::Origin & origin)737 base::Optional<int64_t> MediaHistoryFeedsTable::GetFeedForOrigin(
738     const url::Origin& origin) {
739   if (!CanAccessDatabase())
740     return base::nullopt;
741 
742   sql::Statement statement(DB()->GetCachedStatement(
743       SQL_FROM_HERE,
744       "SELECT mediaFeed.id FROM origin LEFT JOIN mediaFeed ON "
745       "mediaFeed.origin_id = origin.id WHERE origin.origin = ? AND "
746       "(mediaFeed.last_fetch_result > 0 OR mediaFeed.reset_reason > 0)"));
747   statement.BindString(0, MediaHistoryOriginTable::GetOriginForStorage(origin));
748 
749   while (statement.Step())
750     return statement.ColumnInt64(0);
751 
752   return base::nullopt;
753 }
754 
755 MediaHistoryKeyedService::PendingSafeSearchCheckList
GetPendingSafeSearchCheckItems()756 MediaHistoryFeedsTable::GetPendingSafeSearchCheckItems() {
757   MediaHistoryKeyedService::PendingSafeSearchCheckList items;
758 
759   if (!CanAccessDatabase())
760     return items;
761 
762   sql::Statement statement(DB()->GetUniqueStatement(
763       "SELECT id, url FROM mediaFeed WHERE safe_search_result = ?"));
764   statement.BindInt64(
765       0, static_cast<int>(media_feeds::mojom::SafeSearchResult::kUnknown));
766 
767   DCHECK(statement.is_valid());
768 
769   while (statement.Step()) {
770     auto check =
771         std::make_unique<MediaHistoryKeyedService::PendingSafeSearchCheck>(
772             MediaHistoryKeyedService::SafeSearchCheckedType::kFeed,
773             statement.ColumnInt64(0));
774 
775     GURL url(statement.ColumnString(1));
776     if (url.is_valid())
777       check->urls.insert(url);
778 
779     if (!check->urls.empty())
780       items.push_back(std::move(check));
781   }
782 
783   return items;
784 }
785 
StoreSafeSearchResult(int64_t feed_id,media_feeds::mojom::SafeSearchResult result)786 bool MediaHistoryFeedsTable::StoreSafeSearchResult(
787     int64_t feed_id,
788     media_feeds::mojom::SafeSearchResult result) {
789   sql::Statement statement(DB()->GetCachedStatement(
790       SQL_FROM_HERE,
791       "UPDATE mediaFeed SET safe_search_result = ? WHERE id = ?"));
792   statement.BindInt64(0, static_cast<int>(result));
793   statement.BindInt64(1, feed_id);
794   return statement.Run();
795 }
796 
UpdateFeedUserStatus(const int64_t feed_id,media_feeds::mojom::FeedUserStatus status)797 bool MediaHistoryFeedsTable::UpdateFeedUserStatus(
798     const int64_t feed_id,
799     media_feeds::mojom::FeedUserStatus status) {
800   sql::Statement statement(DB()->GetCachedStatement(
801       SQL_FROM_HERE, "UPDATE mediaFeed SET user_status = ? WHERE id = ?"));
802   statement.BindInt64(0, static_cast<int>(status));
803   statement.BindInt64(1, feed_id);
804   return statement.Run();
805 }
806 
807 }  // namespace media_history
808