diff options
Diffstat (limited to '')
| -rw-r--r-- | toolkit/components/places/nsPlacesTriggers.h | 415 |
1 files changed, 415 insertions, 0 deletions
diff --git a/toolkit/components/places/nsPlacesTriggers.h b/toolkit/components/places/nsPlacesTriggers.h new file mode 100644 index 0000000000..78eacdb581 --- /dev/null +++ b/toolkit/components/places/nsPlacesTriggers.h @@ -0,0 +1,415 @@ +/* -*- 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 = (frecency <> 0), " \ + "recalc_alt_frecency = (frecency <> 0), " \ + "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 macro is a helper for the next several triggers. It updates the origin +// frecency stats. Use it as follows. Before changing an origin's frecency, +// call the macro and pass "-" (subtraction) as the argument. That will update +// the stats by deducting the origin's current contribution to them. And then +// after you change the origin's frecency, call the macro again, this time +// passing "+" (addition) as the argument. That will update the stats by adding +// the origin's new contribution to them. +# define UPDATE_ORIGIN_FRECENCY_STATS(op) \ + "INSERT OR REPLACE INTO moz_meta(key, value) " \ + "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_COUNT \ + "', " \ + "IFNULL((SELECT value FROM moz_meta WHERE key = " \ + "'" MOZ_META_KEY_ORIGIN_FRECENCY_COUNT "'), 0) " op \ + " CAST(frecency > 0 AS INT) " \ + "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " \ + "UNION " \ + "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_SUM \ + "', " \ + "IFNULL((SELECT value FROM moz_meta WHERE key = " \ + "'" MOZ_META_KEY_ORIGIN_FRECENCY_SUM "'), 0) " op \ + " MAX(frecency, 0) " \ + "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " \ + "UNION " \ + "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES \ + "', " \ + "IFNULL((SELECT value FROM moz_meta WHERE key = " \ + "'" MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES "'), 0) " op \ + " (MAX(frecency, 0) * MAX(frecency, 0)) " \ + "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " + +// The next several triggers are a workaround for the lack of FOR EACH STATEMENT +// in Sqlite, until bug 871908 can be fixed properly. +// +// While doing inserts or 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 and the origin frecency stats. As a consequence, we also +// do this for updates to moz_places.frecency in order to make sure that changes +// to origins are serialized. +// +// 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 a single +// transaction. Regardless, this is still better than hanging the browser for +// several minutes on a fast machine. + +// This trigger runs on inserts into moz_places. +# 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 OR IGNORE INTO moz_updateoriginsinsert_temp (place_id, " \ + "prefix, " \ + "host, frecency) " \ + "VALUES (NEW.id, get_prefix(NEW.url), get_host_and_port(NEW.url), " \ + "NEW.frecency); " \ + "END") +// This trigger corresponds to the previous trigger. It runs on deletes on +// moz_updateoriginsinsert_temp -- logically, after inserts on moz_places. +# define CREATE_UPDATEORIGINSINSERT_AFTERDELETE_TRIGGER \ + nsLiteralCString( \ + "CREATE TEMP TRIGGER moz_updateoriginsinsert_afterdelete_trigger " \ + "AFTER DELETE ON moz_updateoriginsinsert_temp FOR EACH ROW " \ + "BEGIN " \ + /* Deduct the origin's current contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ + "INSERT INTO moz_origins (prefix, host, frecency, recalc_alt_frecency) " \ + "VALUES (OLD.prefix, OLD.host, MAX(OLD.frecency, 0), 1) " \ + "ON CONFLICT(prefix, host) DO UPDATE " \ + "SET frecency = frecency + OLD.frecency " \ + "WHERE OLD.frecency > 0; " \ + /* Add the origin's new contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ + "UPDATE moz_places SET origin_id = ( " \ + "SELECT id " \ + "FROM moz_origins " \ + "WHERE prefix = OLD.prefix AND host = OLD.host " \ + ") " \ + "WHERE id = OLD.place_id; " \ + "END" \ +) + +// This trigger runs on deletes on moz_places. +# define CREATE_PLACES_AFTERDELETE_TRIGGER \ + nsLiteralCString( \ + "CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \ + "AFTER DELETE ON moz_places FOR EACH ROW " \ + "BEGIN " \ + "INSERT INTO moz_updateoriginsdelete_temp (prefix, host, " \ + "frecency_delta) " \ + "VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url), " \ + "-MAX(OLD.frecency, 0)) " \ + "ON CONFLICT(prefix, host) DO UPDATE " \ + "SET frecency_delta = frecency_delta - OLD.frecency " \ + "WHERE OLD.frecency > 0; " \ + "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 INTO moz_updateoriginsdelete_temp (prefix, host, " \ + "frecency_delta) " \ + "VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url), " \ + "-MAX(OLD.frecency, 0)) " \ + "ON CONFLICT(prefix, host) DO UPDATE " \ + "SET frecency_delta = frecency_delta - OLD.frecency " \ + "WHERE OLD.frecency > 0; " \ + "INSERT OR IGNORE INTO moz_previews_tombstones VALUES " \ + "(md5hex(OLD.url));" \ + "END ") + +// This trigger corresponds to the previous trigger. It runs on deletes on +// moz_updateoriginsdelete_temp -- logically, after deletes on moz_places. +# define CREATE_UPDATEORIGINSDELETE_AFTERDELETE_TRIGGER \ + nsLiteralCString( \ + "CREATE TEMP TRIGGER moz_updateoriginsdelete_afterdelete_trigger " \ + "AFTER DELETE ON moz_updateoriginsdelete_temp FOR EACH ROW " \ + "BEGIN " \ + /* Deduct the origin's current contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ + "UPDATE moz_origins SET frecency = frecency + OLD.frecency_delta, " \ + "recalc_frecency = 0 " \ + "WHERE prefix = OLD.prefix AND host = OLD.host; " \ + "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 " \ + "LIMIT 1 " \ + "); " \ + /* Add the origin's new contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ + "DELETE FROM moz_icons WHERE id IN ( " \ + "SELECT id FROM moz_icons " \ + "WHERE fixed_icon_url_hash = hash(fixup_url(OLD.host || '/favicon.ico')) " \ + "AND fixup_url(icon_url) = fixup_url(OLD.host || '/favicon.ico') " \ + "AND NOT EXISTS (SELECT 1 FROM moz_origins WHERE host = OLD.host " \ + "OR host = fixup_url(OLD.host)) " \ + "EXCEPT " \ + "SELECT icon_id FROM moz_icons_to_pages " \ + "); " \ + "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 " \ + "INSERT INTO moz_updateoriginsupdate_temp (prefix, host, " \ + "frecency_delta) " \ + "VALUES (get_prefix(NEW.url), get_host_and_port(NEW.url), " \ + "MAX(NEW.frecency, 0) - MAX(OLD.frecency, 0)) " \ + "ON CONFLICT(prefix, host) DO UPDATE " \ + "SET frecency_delta = frecency_delta + EXCLUDED.frecency_delta; " \ + "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 ") +// This trigger corresponds to the previous trigger. It runs on deletes on +// moz_updateoriginsupdate_temp -- logically, after updates to +// moz_places.frecency. +# define CREATE_UPDATEORIGINSUPDATE_AFTERDELETE_TRIGGER \ + nsLiteralCString( \ + "CREATE TEMP TRIGGER moz_updateoriginsupdate_afterdelete_trigger " \ + "AFTER DELETE ON moz_updateoriginsupdate_temp FOR EACH ROW " \ + "BEGIN " \ + /* Deduct the origin's current contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ + "UPDATE moz_origins " \ + "SET frecency = frecency + OLD.frecency_delta, " \ + "recalc_frecency = 0 " \ + "WHERE prefix = OLD.prefix AND host = OLD.host; " \ + /* Add the origin's new contribution to frecency stats */ \ + UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ + "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") + +/** + * 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 = 1 WHERE frecency = -1 AND NOT " IS_PLACE_QUERY \ + ";" \ + "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") + +#endif // __nsPlacesTriggers_h__ |