1 /* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- 2 * vim: sw=2 ts=2 et lcs=trail\:.,tab\:>~ : 3 * This Source Code Form is subject to the terms of the Mozilla Public 4 * License, v. 2.0. If a copy of the MPL was not distributed with this 5 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */ 6 7 #include "nsPlacesTables.h" 8 9 #ifndef __nsPlacesTriggers_h__ 10 # define __nsPlacesTriggers_h__ 11 12 /** 13 * Exclude these visit types: 14 * 0 - invalid 15 * 4 - EMBED 16 * 7 - DOWNLOAD 17 * 8 - FRAMED_LINK 18 * 9 - RELOAD 19 **/ 20 # define EXCLUDED_VISIT_TYPES "0, 4, 7, 8, 9" 21 22 /** 23 * This triggers update visit_count and last_visit_date based on historyvisits 24 * table changes. 25 */ 26 # define CREATE_HISTORYVISITS_AFTERINSERT_TRIGGER \ 27 nsLiteralCString( \ 28 "CREATE TEMP TRIGGER moz_historyvisits_afterinsert_v2_trigger " \ 29 "AFTER INSERT ON moz_historyvisits FOR EACH ROW " \ 30 "BEGIN " \ 31 "SELECT invalidate_days_of_history();" \ 32 "SELECT store_last_inserted_id('moz_historyvisits', NEW.id); " \ 33 "UPDATE moz_places SET " \ 34 "visit_count = visit_count + (SELECT NEW.visit_type NOT IN " \ 35 "(" EXCLUDED_VISIT_TYPES \ 36 ")), " \ 37 "last_visit_date = MAX(IFNULL(last_visit_date, 0), NEW.visit_date) " \ 38 "WHERE id = NEW.place_id;" \ 39 "END") 40 41 # define CREATE_HISTORYVISITS_AFTERDELETE_TRIGGER \ 42 nsLiteralCString( \ 43 "CREATE TEMP TRIGGER moz_historyvisits_afterdelete_v2_trigger " \ 44 "AFTER DELETE ON moz_historyvisits FOR EACH ROW " \ 45 "BEGIN " \ 46 "SELECT invalidate_days_of_history();" \ 47 "UPDATE moz_places SET " \ 48 "visit_count = visit_count - (SELECT OLD.visit_type NOT IN " \ 49 "(" EXCLUDED_VISIT_TYPES \ 50 ")), " \ 51 "last_visit_date = (SELECT visit_date FROM moz_historyvisits " \ 52 "WHERE place_id = OLD.place_id " \ 53 "ORDER BY visit_date DESC LIMIT 1) " \ 54 "WHERE id = OLD.place_id;" \ 55 "END") 56 57 // This macro is a helper for the next several triggers. It updates the origin 58 // frecency stats. Use it as follows. Before changing an origin's frecency, 59 // call the macro and pass "-" (subtraction) as the argument. That will update 60 // the stats by deducting the origin's current contribution to them. And then 61 // after you change the origin's frecency, call the macro again, this time 62 // passing "+" (addition) as the argument. That will update the stats by adding 63 // the origin's new contribution to them. 64 # define UPDATE_ORIGIN_FRECENCY_STATS(op) \ 65 "INSERT OR REPLACE INTO moz_meta(key, value) " \ 66 "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_COUNT \ 67 "', " \ 68 "IFNULL((SELECT value FROM moz_meta WHERE key = " \ 69 "'" MOZ_META_KEY_ORIGIN_FRECENCY_COUNT "'), 0) " op \ 70 " CAST(frecency > 0 AS INT) " \ 71 "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " \ 72 "UNION " \ 73 "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_SUM \ 74 "', " \ 75 "IFNULL((SELECT value FROM moz_meta WHERE key = " \ 76 "'" MOZ_META_KEY_ORIGIN_FRECENCY_SUM "'), 0) " op \ 77 " MAX(frecency, 0) " \ 78 "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " \ 79 "UNION " \ 80 "SELECT '" MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES \ 81 "', " \ 82 "IFNULL((SELECT value FROM moz_meta WHERE key = " \ 83 "'" MOZ_META_KEY_ORIGIN_FRECENCY_SUM_OF_SQUARES "'), 0) " op \ 84 " (MAX(frecency, 0) * MAX(frecency, 0)) " \ 85 "FROM moz_origins WHERE prefix = OLD.prefix AND host = OLD.host " 86 87 // The next several triggers are a workaround for the lack of FOR EACH STATEMENT 88 // in Sqlite, until bug 871908 can be fixed properly. 89 // 90 // While doing inserts or deletes into moz_places, we accumulate the affected 91 // origins into a temp table. Afterwards, we delete everything from the temp 92 // table, causing the AFTER DELETE trigger to fire for it, which will then 93 // update moz_origins and the origin frecency stats. As a consequence, we also 94 // do this for updates to moz_places.frecency in order to make sure that changes 95 // to origins are serialized. 96 // 97 // Note this way we lose atomicity, crashing between the 2 queries may break the 98 // tables' coherency. So it's better to run those DELETE queries in a single 99 // transaction. Regardless, this is still better than hanging the browser for 100 // several minutes on a fast machine. 101 102 // This trigger runs on inserts into moz_places. 103 # define CREATE_PLACES_AFTERINSERT_TRIGGER \ 104 nsLiteralCString( \ 105 "CREATE TEMP TRIGGER moz_places_afterinsert_trigger " \ 106 "AFTER INSERT ON moz_places FOR EACH ROW " \ 107 "BEGIN " \ 108 "SELECT store_last_inserted_id('moz_places', NEW.id); " \ 109 "INSERT OR IGNORE INTO moz_updateoriginsinsert_temp (place_id, " \ 110 "prefix, " \ 111 "host, frecency) " \ 112 "VALUES (NEW.id, get_prefix(NEW.url), get_host_and_port(NEW.url), " \ 113 "NEW.frecency); " \ 114 "END") 115 // This trigger corresponds to the previous trigger. It runs on deletes on 116 // moz_updateoriginsinsert_temp -- logically, after inserts on moz_places. 117 # define CREATE_UPDATEORIGINSINSERT_AFTERDELETE_TRIGGER \ 118 nsLiteralCString( \ 119 "CREATE TEMP TRIGGER moz_updateoriginsinsert_afterdelete_trigger " \ 120 "AFTER DELETE ON moz_updateoriginsinsert_temp FOR EACH ROW " \ 121 "BEGIN " \ 122 /* Deduct the origin's current contribution to frecency stats */ \ 123 UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ 124 "INSERT INTO moz_origins (prefix, host, frecency) " \ 125 "VALUES (OLD.prefix, OLD.host, MAX(OLD.frecency, 0)) " \ 126 "ON CONFLICT(prefix, host) DO UPDATE " \ 127 "SET frecency = frecency + OLD.frecency " \ 128 "WHERE OLD.frecency > 0; " \ 129 /* Add the origin's new contribution to frecency stats */ \ 130 UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ 131 "UPDATE moz_places SET origin_id = ( " \ 132 "SELECT id " \ 133 "FROM moz_origins " \ 134 "WHERE prefix = OLD.prefix AND host = OLD.host " \ 135 ") " \ 136 "WHERE id = OLD.place_id; " \ 137 "END" \ 138 ) 139 140 // This trigger runs on deletes on moz_places. 141 # define CREATE_PLACES_AFTERDELETE_TRIGGER \ 142 nsLiteralCString( \ 143 "CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \ 144 "AFTER DELETE ON moz_places FOR EACH ROW " \ 145 "BEGIN " \ 146 "INSERT INTO moz_updateoriginsdelete_temp (prefix, host, " \ 147 "frecency_delta) " \ 148 "VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url), " \ 149 "-MAX(OLD.frecency, 0)) " \ 150 "ON CONFLICT(prefix, host) DO UPDATE " \ 151 "SET frecency_delta = frecency_delta - OLD.frecency " \ 152 "WHERE OLD.frecency > 0; " \ 153 "END ") 154 155 // This is an alternate version of CREATE_PLACES_AFTERDELETE_TRIGGER, with 156 // support for previews tombstones. Only one of these should be used at the 157 // same time 158 # define CREATE_PLACES_AFTERDELETE_WPREVIEWS_TRIGGER \ 159 nsLiteralCString( \ 160 "CREATE TEMP TRIGGER moz_places_afterdelete_wpreviews_trigger " \ 161 "AFTER DELETE ON moz_places FOR EACH ROW " \ 162 "BEGIN " \ 163 "INSERT INTO moz_updateoriginsdelete_temp (prefix, host, " \ 164 "frecency_delta) " \ 165 "VALUES (get_prefix(OLD.url), get_host_and_port(OLD.url), " \ 166 "-MAX(OLD.frecency, 0)) " \ 167 "ON CONFLICT(prefix, host) DO UPDATE " \ 168 "SET frecency_delta = frecency_delta - OLD.frecency " \ 169 "WHERE OLD.frecency > 0; " \ 170 "INSERT OR IGNORE INTO moz_previews_tombstones VALUES " \ 171 "(md5hex(OLD.url));" \ 172 "END ") 173 174 // This trigger corresponds to the previous trigger. It runs on deletes on 175 // moz_updateoriginsdelete_temp -- logically, after deletes on moz_places. 176 # define CREATE_UPDATEORIGINSDELETE_AFTERDELETE_TRIGGER \ 177 nsLiteralCString( \ 178 "CREATE TEMP TRIGGER moz_updateoriginsdelete_afterdelete_trigger " \ 179 "AFTER DELETE ON moz_updateoriginsdelete_temp FOR EACH ROW " \ 180 "BEGIN " \ 181 /* Deduct the origin's current contribution to frecency stats */ \ 182 UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ 183 "UPDATE moz_origins SET frecency = frecency + OLD.frecency_delta " \ 184 "WHERE prefix = OLD.prefix AND host = OLD.host; " \ 185 "DELETE FROM moz_origins " \ 186 "WHERE prefix = OLD.prefix AND host = OLD.host AND NOT EXISTS ( " \ 187 "SELECT id FROM moz_places " \ 188 "WHERE origin_id = moz_origins.id " \ 189 "LIMIT 1 " \ 190 "); " \ 191 /* Add the origin's new contribution to frecency stats */ \ 192 UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ 193 "DELETE FROM moz_icons WHERE id IN ( " \ 194 "SELECT id FROM moz_icons " \ 195 "WHERE fixed_icon_url_hash = hash(fixup_url(OLD.host || '/favicon.ico')) " \ 196 "AND fixup_url(icon_url) = fixup_url(OLD.host || '/favicon.ico') " \ 197 "AND NOT EXISTS (SELECT 1 FROM moz_origins WHERE host = OLD.host " \ 198 "OR host = fixup_url(OLD.host)) " \ 199 "EXCEPT " \ 200 "SELECT icon_id FROM moz_icons_to_pages " \ 201 "); " \ 202 "END" \ 203 ) 204 205 // This trigger runs on updates to moz_places.frecency. 206 // 207 // However, we skip this when frecency changes are due to frecency decay since 208 // (1) decay updates all frecencies at once, so this trigger would run for each 209 // moz_place, which would be expensive; and (2) decay does not change the 210 // ordering of frecencies since all frecencies decay by the same percentage. 211 # define CREATE_PLACES_AFTERUPDATE_FRECENCY_TRIGGER \ 212 nsLiteralCString( \ 213 "CREATE TEMP TRIGGER moz_places_afterupdate_frecency_trigger " \ 214 "AFTER UPDATE OF frecency ON moz_places FOR EACH ROW " \ 215 "WHEN NOT is_frecency_decaying() " \ 216 "BEGIN " \ 217 "INSERT INTO moz_updateoriginsupdate_temp (prefix, host, " \ 218 "frecency_delta) " \ 219 "VALUES (get_prefix(NEW.url), get_host_and_port(NEW.url), " \ 220 "MAX(NEW.frecency, 0) - MAX(OLD.frecency, 0)) " \ 221 "ON CONFLICT(prefix, host) DO UPDATE " \ 222 "SET frecency_delta = frecency_delta + EXCLUDED.frecency_delta; " \ 223 "END ") 224 // This trigger corresponds to the previous trigger. It runs on deletes on 225 // moz_updateoriginsupdate_temp -- logically, after updates to 226 // moz_places.frecency. 227 # define CREATE_UPDATEORIGINSUPDATE_AFTERDELETE_TRIGGER \ 228 nsLiteralCString( \ 229 "CREATE TEMP TRIGGER moz_updateoriginsupdate_afterdelete_trigger " \ 230 "AFTER DELETE ON moz_updateoriginsupdate_temp FOR EACH ROW " \ 231 "BEGIN " \ 232 /* Deduct the origin's current contribution to frecency stats */ \ 233 UPDATE_ORIGIN_FRECENCY_STATS("-") "; " \ 234 "UPDATE moz_origins " \ 235 "SET frecency = frecency + OLD.frecency_delta " \ 236 "WHERE prefix = OLD.prefix AND host = OLD.host; " \ 237 /* Add the origin's new contribution to frecency stats */ \ 238 UPDATE_ORIGIN_FRECENCY_STATS("+") "; " \ 239 "END" \ 240 ) 241 242 /** 243 * This trigger removes a row from moz_openpages_temp when open_count reaches 0. 244 * 245 * @note this should be kept up-to-date with the definition in 246 * nsPlacesAutoComplete.js 247 */ 248 # define CREATE_REMOVEOPENPAGE_CLEANUP_TRIGGER \ 249 nsLiteralCString( \ 250 "CREATE TEMPORARY TRIGGER moz_openpages_temp_afterupdate_trigger " \ 251 "AFTER UPDATE OF open_count ON moz_openpages_temp FOR EACH ROW " \ 252 "WHEN NEW.open_count = 0 " \ 253 "BEGIN " \ 254 "DELETE FROM moz_openpages_temp " \ 255 "WHERE url = NEW.url " \ 256 "AND userContextId = NEW.userContextId;" \ 257 "END") 258 259 # define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \ 260 nsLiteralCString( \ 261 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterdelete_trigger " \ 262 "AFTER DELETE ON moz_bookmarks FOR EACH ROW " \ 263 "BEGIN " \ 264 "UPDATE moz_places " \ 265 "SET foreign_count = foreign_count - 1 " \ 266 "WHERE id = OLD.fk;" \ 267 "END") 268 269 # define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \ 270 nsLiteralCString( \ 271 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterinsert_trigger " \ 272 "AFTER INSERT ON moz_bookmarks FOR EACH ROW " \ 273 "BEGIN " \ 274 "SELECT store_last_inserted_id('moz_bookmarks', NEW.id); " \ 275 "SELECT note_sync_change() WHERE NEW.syncChangeCounter > 0; " \ 276 "UPDATE moz_places " \ 277 "SET foreign_count = foreign_count + 1 " \ 278 "WHERE id = NEW.fk;" \ 279 "END") 280 281 # define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \ 282 nsLiteralCString( \ 283 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterupdate_trigger " \ 284 "AFTER UPDATE OF fk, syncChangeCounter ON moz_bookmarks FOR EACH ROW " \ 285 "BEGIN " \ 286 "SELECT note_sync_change() " \ 287 "WHERE NEW.syncChangeCounter <> OLD.syncChangeCounter; " \ 288 "UPDATE moz_places " \ 289 "SET foreign_count = foreign_count + 1 " \ 290 "WHERE OLD.fk <> NEW.fk AND id = NEW.fk;" \ 291 "UPDATE moz_places " \ 292 "SET foreign_count = foreign_count - 1 " \ 293 "WHERE OLD.fk <> NEW.fk AND id = OLD.fk;" \ 294 "END") 295 296 # define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \ 297 nsLiteralCString( \ 298 "CREATE TEMP TRIGGER moz_keywords_foreign_count_afterdelete_trigger " \ 299 "AFTER DELETE ON moz_keywords FOR EACH ROW " \ 300 "BEGIN " \ 301 "UPDATE moz_places " \ 302 "SET foreign_count = foreign_count - 1 " \ 303 "WHERE id = OLD.place_id;" \ 304 "END") 305 306 # define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \ 307 nsLiteralCString( \ 308 "CREATE TEMP TRIGGER moz_keywords_foreign_count_afterinsert_trigger " \ 309 "AFTER INSERT ON moz_keywords FOR EACH ROW " \ 310 "BEGIN " \ 311 "UPDATE moz_places " \ 312 "SET foreign_count = foreign_count + 1 " \ 313 "WHERE id = NEW.place_id;" \ 314 "END") 315 316 # define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \ 317 nsLiteralCString( \ 318 "CREATE TEMP TRIGGER moz_keywords_foreign_count_afterupdate_trigger " \ 319 "AFTER UPDATE OF place_id ON moz_keywords FOR EACH ROW " \ 320 "BEGIN " \ 321 "UPDATE moz_places " \ 322 "SET foreign_count = foreign_count + 1 " \ 323 "WHERE id = NEW.place_id; " \ 324 "UPDATE moz_places " \ 325 "SET foreign_count = foreign_count - 1 " \ 326 "WHERE id = OLD.place_id; " \ 327 "END") 328 329 # define CREATE_ICONS_AFTERINSERT_TRIGGER \ 330 nsLiteralCString( \ 331 "CREATE TEMP TRIGGER moz_icons_afterinsert_v1_trigger " \ 332 "AFTER INSERT ON moz_icons FOR EACH ROW " \ 333 "BEGIN " \ 334 "SELECT store_last_inserted_id('moz_icons', NEW.id); " \ 335 "END") 336 337 # define CREATE_BOOKMARKS_DELETED_AFTERINSERT_TRIGGER \ 338 nsLiteralCString( \ 339 "CREATE TEMP TRIGGER moz_bookmarks_deleted_afterinsert_v1_trigger " \ 340 "AFTER INSERT ON moz_bookmarks_deleted FOR EACH ROW " \ 341 "BEGIN " \ 342 "SELECT note_sync_change(); " \ 343 "END") 344 345 # define CREATE_BOOKMARKS_DELETED_AFTERDELETE_TRIGGER \ 346 nsLiteralCString( \ 347 "CREATE TEMP TRIGGER moz_bookmarks_deleted_afterdelete_v1_trigger " \ 348 "AFTER DELETE ON moz_bookmarks_deleted FOR EACH ROW " \ 349 "BEGIN " \ 350 "SELECT note_sync_change(); " \ 351 "END") 352 353 // This trigger updates last_interaction_at when interactions are created. It 354 // also updates first_interaction_at and document_type in cases where a snapshot 355 // was created before its corresponding interaction. 356 # define CREATE_PLACES_METADATA_AFTERINSERT_TRIGGER \ 357 nsLiteralCString( \ 358 "CREATE TEMP TRIGGER moz_places_metadata_afterinsert_trigger " \ 359 "AFTER INSERT ON moz_places_metadata " \ 360 "FOR EACH ROW " \ 361 "BEGIN " \ 362 "UPDATE moz_places_metadata_snapshots " \ 363 "SET last_interaction_at = NEW.created_at " \ 364 "WHERE place_id = NEW.place_id; " \ 365 "UPDATE moz_places_metadata_snapshots " \ 366 "SET first_interaction_at = NEW.created_at, document_type = " \ 367 "CASE WHEN NEW.document_type <> 0 " \ 368 "THEN NEW.document_type ELSE document_type END " \ 369 "WHERE place_id = NEW.place_id AND first_interaction_at = 0;" \ 370 "END") 371 372 // This trigger removes orphan search terms when interactions are removed from 373 // the metadata table. 374 # define CREATE_PLACES_METADATA_AFTERDELETE_TRIGGER \ 375 nsLiteralCString( \ 376 "CREATE TEMP TRIGGER moz_places_metadata_afterdelete_trigger " \ 377 "AFTER DELETE ON moz_places_metadata " \ 378 "FOR EACH ROW " \ 379 "BEGIN " \ 380 "DELETE FROM moz_places_metadata_search_queries " \ 381 "WHERE id = OLD.search_query_id AND NOT EXISTS (" \ 382 "SELECT id FROM moz_places_metadata " \ 383 "WHERE search_query_id = OLD.search_query_id " \ 384 "); " \ 385 "END") 386 387 // This trigger increments foreign_count when snapshots are created. 388 # define CREATE_PLACES_METADATA_SNAPSHOTS_AFTERINSERT_TRIGGER \ 389 nsLiteralCString( \ 390 "CREATE TEMP TRIGGER " \ 391 "moz_places_metadata_snapshots_afterinsert_trigger " \ 392 "AFTER INSERT ON moz_places_metadata_snapshots " \ 393 "FOR EACH ROW " \ 394 "BEGIN " \ 395 "UPDATE moz_places SET foreign_count = foreign_count + 1 " \ 396 "WHERE id = NEW.place_id; " \ 397 "END") 398 399 // This trigger decrements foreign_count when snapshots are removed. 400 # define CREATE_PLACES_METADATA_SNAPSHOTS_AFTERDELETE_TRIGGER \ 401 nsLiteralCString( \ 402 "CREATE TEMP TRIGGER " \ 403 "moz_places_metadata_snapshots_afterdelete_trigger " \ 404 "AFTER DELETE ON moz_places_metadata_snapshots " \ 405 "FOR EACH ROW " \ 406 "BEGIN " \ 407 "UPDATE moz_places SET foreign_count = foreign_count - 1 " \ 408 "WHERE id = OLD.place_id; " \ 409 "END") 410 411 // This trigger increments foreign_count when sessions are altered. 412 # define CREATE_PLACES_SESSION_TO_PLACE_AFTERINSERT_TRIGGER \ 413 nsLiteralCString( \ 414 "CREATE TEMP TRIGGER " \ 415 "moz_session_to_places_after_insert_trigger " \ 416 "AFTER INSERT ON moz_session_to_places " \ 417 "FOR EACH ROW " \ 418 "BEGIN " \ 419 "UPDATE moz_places SET foreign_count = foreign_count + 1 " \ 420 "WHERE id = NEW.place_id; " \ 421 "END") 422 423 // This trigger decrements foreign_count when sessions are removed. 424 # define CREATE_PLACES_SESSION_TO_PLACE_AFTERDELETE_TRIGGER \ 425 nsLiteralCString( \ 426 "CREATE TEMP TRIGGER " \ 427 "moz_session_to_places_afterdelete_trigger " \ 428 "AFTER DELETE ON moz_session_to_places " \ 429 "FOR EACH ROW " \ 430 "BEGIN " \ 431 "UPDATE moz_places SET foreign_count = foreign_count - 1 " \ 432 "WHERE id = OLD.place_id; " \ 433 "END") 434 435 #endif // __nsPlacesTriggers_h__ 436