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