407 lines
25 KiB
C
407 lines
25 KiB
C
/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*-
|
|
* vim: sw=2 ts=2 et lcs=trail\:.,tab\:>~ :
|
|
* This Source Code Form is subject to the terms of the Mozilla Public
|
|
* License, v. 2.0. If a copy of the MPL was not distributed with this
|
|
* file, You can obtain one at http://mozilla.org/MPL/2.0/. */
|
|
|
|
#include "nsPlacesTables.h"
|
|
|
|
#ifndef __nsPlacesTriggers_h__
|
|
# define __nsPlacesTriggers_h__
|
|
|
|
/**
|
|
* These visit types are excluded from visit_count:
|
|
* 0 - invalid
|
|
* 4 - EMBED
|
|
* 7 - DOWNLOAD
|
|
* 8 - FRAMED_LINK
|
|
* 9 - RELOAD
|
|
**/
|
|
# define VISIT_COUNT_INC(field) \
|
|
"(CASE WHEN " field " IN (0, 4, 7, 8, 9) THEN 0 ELSE 1 END) "
|
|
|
|
/**
|
|
* This triggers update visit_count and last_visit_date based on historyvisits
|
|
* table changes.
|
|
*/
|
|
# define CREATE_HISTORYVISITS_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_historyvisits_afterinsert_v2_trigger " \
|
|
"AFTER INSERT ON moz_historyvisits FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT invalidate_days_of_history();" \
|
|
"SELECT store_last_inserted_id('moz_historyvisits', NEW.id); " \
|
|
"UPDATE moz_places SET " \
|
|
"visit_count = visit_count + " VISIT_COUNT_INC("NEW.visit_type") ", " \
|
|
"recalc_frecency = 1, " \
|
|
"recalc_alt_frecency = 1, " \
|
|
"last_visit_date = MAX(IFNULL(last_visit_date, 0), NEW.visit_date) " \
|
|
"WHERE id = NEW.place_id;" \
|
|
"END")
|
|
|
|
# define CREATE_HISTORYVISITS_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_historyvisits_afterdelete_v2_trigger " \
|
|
"AFTER DELETE ON moz_historyvisits FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT invalidate_days_of_history();" \
|
|
"UPDATE moz_places SET " \
|
|
"visit_count = visit_count - " VISIT_COUNT_INC("OLD.visit_type") ", " \
|
|
"recalc_frecency = (frecency <> 0), " \
|
|
"recalc_alt_frecency = (frecency <> 0), " \
|
|
"last_visit_date = (SELECT visit_date FROM moz_historyvisits " \
|
|
"WHERE place_id = OLD.place_id " \
|
|
"ORDER BY visit_date DESC LIMIT 1) " \
|
|
"WHERE id = OLD.place_id;" \
|
|
"END")
|
|
|
|
// This trigger stores the last_inserted_id, and inserts entries in moz_origins
|
|
// when pages are added.
|
|
# define CREATE_PLACES_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_afterinsert_trigger " \
|
|
"AFTER INSERT ON moz_places FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT store_last_inserted_id('moz_places', NEW.id); " \
|
|
"INSERT INTO moz_origins " \
|
|
" (prefix, host, frecency, recalc_frecency, recalc_alt_frecency) " \
|
|
"VALUES (get_prefix(NEW.url), get_host_and_port(NEW.url), " \
|
|
" NEW.frecency, 1, 1) " \
|
|
"ON CONFLICT(prefix, host) DO UPDATE " \
|
|
" SET recalc_frecency = 1, recalc_alt_frecency = 1 " \
|
|
" WHERE EXCLUDED.recalc_frecency = 0 OR " \
|
|
" EXCLUDED.recalc_alt_frecency = 0; " \
|
|
"UPDATE moz_places SET origin_id = ( " \
|
|
" SELECT id " \
|
|
" FROM moz_origins " \
|
|
" WHERE prefix = get_prefix(NEW.url) " \
|
|
" AND host = get_host_and_port(NEW.url) " \
|
|
") " \
|
|
"WHERE id = NEW.id; " \
|
|
"END")
|
|
|
|
// This trigger is a workaround for the lack of FOR EACH STATEMENT in Sqlite.
|
|
// While doing deletes into moz_places, we accumulate the affected origins into
|
|
// a temp table. Afterwards, we delete everything from the temp table, causing
|
|
// the AFTER DELETE trigger to fire for it, which will then update moz_origins.
|
|
//
|
|
// Note this way we lose atomicity, crashing between the 2 queries may break the
|
|
// tables' coherency. So it's better to run those DELETE queries in the same
|
|
// transaction as the original change.
|
|
# define CREATE_PLACES_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \
|
|
"AFTER DELETE ON moz_places FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"INSERT OR IGNORE INTO moz_updateoriginsdelete_temp (prefix, host) " \
|
|
"VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url)); " \
|
|
"UPDATE moz_origins SET recalc_frecency = 1, recalc_alt_frecency = 1 " \
|
|
"WHERE id = OLD.origin_id; " \
|
|
"END ")
|
|
|
|
// This is an alternate version of CREATE_PLACES_AFTERDELETE_TRIGGER, with
|
|
// support for previews tombstones. Only one of these should be used at the
|
|
// same time
|
|
# define CREATE_PLACES_AFTERDELETE_WPREVIEWS_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_afterdelete_wpreviews_trigger " \
|
|
"AFTER DELETE ON moz_places FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"INSERT OR IGNORE INTO moz_updateoriginsdelete_temp (prefix, host) " \
|
|
"VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url)); " \
|
|
"UPDATE moz_origins SET recalc_frecency = 1, recalc_alt_frecency = 1 " \
|
|
"WHERE id = OLD.origin_id; " \
|
|
"INSERT OR IGNORE INTO moz_previews_tombstones VALUES " \
|
|
"(sha256hex(OLD.url));" \
|
|
"END ")
|
|
|
|
// This is the supporting table for the "AFTER DELETE ON moz_places" triggers.
|
|
# define CREATE_UPDATEORIGINSDELETE_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_updateoriginsdelete_afterdelete_trigger " \
|
|
"AFTER DELETE ON moz_updateoriginsdelete_temp FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_origins " \
|
|
"WHERE prefix = OLD.prefix AND host = OLD.host " \
|
|
"AND NOT EXISTS ( " \
|
|
" SELECT id FROM moz_places " \
|
|
" WHERE origin_id = moz_origins.id " \
|
|
"); " \
|
|
"END")
|
|
|
|
// This trigger runs on updates to moz_places.frecency.
|
|
//
|
|
// However, we skip this when frecency changes are due to frecency decay
|
|
// since (1) decay updates all frecencies at once, so this trigger would
|
|
// run for each moz_place, which would be expensive; and (2) decay does
|
|
// not change the ordering of frecencies since all frecencies decay by
|
|
// the same percentage.
|
|
# define CREATE_PLACES_AFTERUPDATE_FRECENCY_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_afterupdate_frecency_trigger " \
|
|
"AFTER UPDATE OF frecency ON moz_places FOR EACH ROW " \
|
|
"WHEN NOT is_frecency_decaying() " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places SET recalc_frecency = 0 WHERE id = NEW.id; " \
|
|
"UPDATE moz_origins SET recalc_frecency = 1, recalc_alt_frecency = 1 " \
|
|
"WHERE id = NEW.origin_id; " \
|
|
"END ")
|
|
|
|
// Runs when recalc_frecency is set to 1
|
|
# define CREATE_PLACES_AFTERUPDATE_RECALC_FRECENCY_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_afterupdate_recalc_frecency_trigger " \
|
|
"AFTER UPDATE OF recalc_frecency ON moz_places FOR EACH ROW " \
|
|
"WHEN NEW.recalc_frecency = 1 " \
|
|
"BEGIN " \
|
|
" SELECT set_should_start_frecency_recalculation();" \
|
|
"END")
|
|
# define CREATE_ORIGINS_AFTERUPDATE_RECALC_FRECENCY_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_origins_afterupdate_recalc_frecency_trigger " \
|
|
"AFTER UPDATE OF recalc_frecency ON moz_origins FOR EACH ROW " \
|
|
"WHEN NEW.recalc_frecency = 1 " \
|
|
"BEGIN " \
|
|
" SELECT set_should_start_frecency_recalculation();" \
|
|
"END")
|
|
|
|
// Runs when origin frecency is set to 0.
|
|
// This is in addition to moz_updateoriginsdelete_afterdelete_trigger, as a
|
|
// sanity check to ensure orphan origins don't stay around. We cannot just rely
|
|
// on this because it runs delayed and in the meanwhile the existence of the
|
|
// origin may impact user's privacy.
|
|
# define CREATE_ORIGINS_AFTERUPDATE_FRECENCY_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_origins_afterupdate_frecency_trigger " \
|
|
"AFTER UPDATE OF recalc_frecency ON moz_origins FOR EACH ROW " \
|
|
"WHEN NEW.frecency = 0 AND OLD.frecency > 0 " \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_origins " \
|
|
"WHERE id = NEW.id AND NOT EXISTS ( " \
|
|
" SELECT id FROM moz_places WHERE origin_id = NEW.id " \
|
|
"); " \
|
|
"END")
|
|
|
|
/**
|
|
* This trigger removes a row from moz_openpages_temp when open_count
|
|
* reaches 0.
|
|
*
|
|
* @note this should be kept up-to-date with the definition in
|
|
* nsPlacesAutoComplete.js
|
|
*/
|
|
# define CREATE_REMOVEOPENPAGE_CLEANUP_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMPORARY TRIGGER moz_openpages_temp_afterupdate_trigger " \
|
|
"AFTER UPDATE OF open_count ON moz_openpages_temp FOR EACH ROW " \
|
|
"WHEN NEW.open_count = 0 " \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_openpages_temp " \
|
|
"WHERE url = NEW.url " \
|
|
"AND userContextId = NEW.userContextId;" \
|
|
"END")
|
|
|
|
/**
|
|
* Any bookmark operation should recalculate frecency, apart from place:
|
|
* queries.
|
|
*/
|
|
# define IS_PLACE_QUERY \
|
|
" url_hash BETWEEN hash('place', 'prefix_lo') " \
|
|
" AND hash('place', 'prefix_hi') "
|
|
|
|
# define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterdelete_trigger " \
|
|
"AFTER DELETE ON moz_bookmarks FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count - 1 " \
|
|
", recalc_frecency = NOT " IS_PLACE_QUERY \
|
|
", recalc_alt_frecency = NOT " IS_PLACE_QUERY \
|
|
"WHERE id = OLD.fk;" \
|
|
"END")
|
|
|
|
/**
|
|
* Currently expiration skips anything with frecency = -1, since that is
|
|
* the default value for new page insertions. Unfortunately adding and
|
|
* immediately removing a bookmark will generate a page with frecency =
|
|
* -1 that would never be expired until visited. As a temporary
|
|
* workaround we set frecency to 1 on bookmark addition if it was set to
|
|
* -1. This is not elegant, but it will be fixed by Bug 1475582 once
|
|
* removing bookmarks will immediately take care of removing orphan
|
|
* pages. Note setting frecency resets recalc_frecency, so do it first.
|
|
*/
|
|
# define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterinsert_trigger " \
|
|
"AFTER INSERT ON moz_bookmarks FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT store_last_inserted_id('moz_bookmarks', NEW.id); " \
|
|
"SELECT note_sync_change() WHERE NEW.syncChangeCounter > 0; " \
|
|
"UPDATE moz_places " \
|
|
"SET frecency = (CASE WHEN " IS_PLACE_QUERY \
|
|
" THEN 0 ELSE 1 END) " \
|
|
"WHERE frecency = -1 AND id = NEW.fk;" \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count + 1 " \
|
|
", hidden = " IS_PLACE_QUERY \
|
|
", recalc_frecency = NOT " IS_PLACE_QUERY \
|
|
", recalc_alt_frecency = NOT " IS_PLACE_QUERY \
|
|
"WHERE id = NEW.fk;" \
|
|
"END")
|
|
|
|
# define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterupdate_trigger " \
|
|
"AFTER UPDATE OF fk, syncChangeCounter ON moz_bookmarks FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT note_sync_change() " \
|
|
"WHERE NEW.syncChangeCounter <> OLD.syncChangeCounter; " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count + 1 " \
|
|
", hidden = " IS_PLACE_QUERY \
|
|
", recalc_frecency = NOT " IS_PLACE_QUERY \
|
|
", recalc_alt_frecency = NOT " IS_PLACE_QUERY \
|
|
"WHERE OLD.fk <> NEW.fk AND id = NEW.fk;" \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count - 1 " \
|
|
", recalc_frecency = NOT " IS_PLACE_QUERY \
|
|
", recalc_alt_frecency = NOT " IS_PLACE_QUERY \
|
|
"WHERE OLD.fk <> NEW.fk AND id = OLD.fk;" \
|
|
"END")
|
|
|
|
# define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_keywords_foreign_count_afterdelete_trigger " \
|
|
"AFTER DELETE ON moz_keywords FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count - 1 " \
|
|
"WHERE id = OLD.place_id;" \
|
|
"END")
|
|
|
|
# define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_keywords_foreign_count_afterinsert_trigger " \
|
|
"AFTER INSERT ON moz_keywords FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count + 1 " \
|
|
"WHERE id = NEW.place_id;" \
|
|
"END")
|
|
|
|
# define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_keywords_foreign_count_afterupdate_trigger " \
|
|
"AFTER UPDATE OF place_id ON moz_keywords FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count + 1 " \
|
|
"WHERE id = NEW.place_id; " \
|
|
"UPDATE moz_places " \
|
|
"SET foreign_count = foreign_count - 1 " \
|
|
"WHERE id = OLD.place_id; " \
|
|
"END")
|
|
|
|
# define CREATE_ICONS_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_icons_afterinsert_v1_trigger " \
|
|
"AFTER INSERT ON moz_icons FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT store_last_inserted_id('moz_icons', NEW.id); " \
|
|
"END")
|
|
|
|
# define CREATE_BOOKMARKS_DELETED_AFTERINSERT_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_bookmarks_deleted_afterinsert_v1_trigger " \
|
|
"AFTER INSERT ON moz_bookmarks_deleted FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT note_sync_change(); " \
|
|
"END")
|
|
|
|
# define CREATE_BOOKMARKS_DELETED_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_bookmarks_deleted_afterdelete_v1_trigger " \
|
|
"AFTER DELETE ON moz_bookmarks_deleted FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"SELECT note_sync_change(); " \
|
|
"END")
|
|
|
|
// This trigger removes orphan search terms when interactions are
|
|
// removed from the metadata table.
|
|
# define CREATE_PLACES_METADATA_AFTERDELETE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_metadata_afterdelete_trigger " \
|
|
"AFTER DELETE ON moz_places_metadata " \
|
|
"FOR EACH ROW " \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_places_metadata_search_queries " \
|
|
"WHERE id = OLD.search_query_id AND NOT EXISTS (" \
|
|
"SELECT id FROM moz_places_metadata " \
|
|
"WHERE search_query_id = OLD.search_query_id " \
|
|
"); " \
|
|
"END")
|
|
|
|
# define CREATE_PLACES_METADATA_AFTERINSERT_TRIGGER( \
|
|
TOTAL_VIEW_TIME, TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES) \
|
|
nsPrintfCString( \
|
|
"CREATE TEMP TRIGGER moz_places_metadata_afterinsert_trigger " \
|
|
"AFTER INSERT ON moz_places_metadata " \
|
|
"FOR EACH ROW " \
|
|
"WHEN NEW.total_view_time >= %d " \
|
|
"OR (NEW.total_view_time >= %d " \
|
|
"AND NEW.key_presses >= %d) " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET recalc_alt_frecency = 1 " \
|
|
"WHERE id = NEW.place_id; " \
|
|
"END", \
|
|
TOTAL_VIEW_TIME, TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES)
|
|
|
|
# define CREATE_PLACES_METADATA_AFTERUPDATE_TRIGGER( \
|
|
TOTAL_VIEW_TIME, TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES) \
|
|
nsPrintfCString( \
|
|
"CREATE TEMP TRIGGER moz_places_metadata_afterupdate_trigger " \
|
|
"AFTER UPDATE ON moz_places_metadata " \
|
|
"FOR EACH ROW " \
|
|
"WHEN " \
|
|
" (OLD.total_view_time < %d AND NEW.total_view_time >= %d) " \
|
|
" OR (OLD.total_view_time < %d AND NEW.total_view_time >= %d " \
|
|
" AND OLD.key_presses >= %d) " \
|
|
" OR (OLD.total_view_time >= %d " \
|
|
" AND OLD.key_presses < %d AND NEW.key_presses >= %d) " \
|
|
" OR (OLD.total_view_time < %d AND NEW.total_view_time >= %d " \
|
|
" AND OLD.key_presses < %d AND NEW.key_presses >= %d) " \
|
|
"BEGIN " \
|
|
"UPDATE moz_places " \
|
|
"SET recalc_alt_frecency = 1 " \
|
|
"WHERE id = NEW.place_id; " \
|
|
"END", \
|
|
TOTAL_VIEW_TIME, TOTAL_VIEW_TIME, TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, \
|
|
TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES, \
|
|
TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES, \
|
|
MANY_KEY_PRESSES, TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, \
|
|
TOTAL_VIEW_TIME_IF_MANY_KEYPRESSES, MANY_KEY_PRESSES, \
|
|
MANY_KEY_PRESSES)
|
|
|
|
// since moz_places_extra is really just storing json, there could be a
|
|
// scenario where we have a valid row but empty json -- we should make sure
|
|
// we have triggers to remove any such rows
|
|
# define CREATE_MOZ_PLACES_EXTRA_AFTERUPDATE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_places_extra_trigger " \
|
|
"AFTER UPDATE ON moz_places_extra FOR EACH ROW " \
|
|
"WHEN (NEW.sync_json = '' OR NEW.sync_json = '{}')" \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_places_extra WHERE place_id = NEW.place_id;" \
|
|
"END")
|
|
|
|
# define CREATE_MOZ_HISTORYVISITS_AFTERUPDATE_TRIGGER \
|
|
nsLiteralCString( \
|
|
"CREATE TEMP TRIGGER moz_historyvisits_extra_trigger " \
|
|
"AFTER UPDATE ON moz_historyvisits_extra FOR EACH ROW " \
|
|
"WHEN (NEW.sync_json = '' OR NEW.sync_json = '{}')" \
|
|
"BEGIN " \
|
|
"DELETE FROM moz_historyvisits_extra WHERE visit_id = NEW.visit_id;" \
|
|
"END")
|
|
|
|
#endif // __nsPlacesTriggers_h__
|