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 NS_LITERAL_CSTRING( \ 28 "CREATE TEMP TRIGGER moz_historyvisits_afterinsert_v2_trigger " \ 29 "AFTER INSERT ON moz_historyvisits FOR EACH ROW " \ 30 "BEGIN " \ 31 "SELECT store_last_inserted_id('moz_historyvisits', NEW.id); " \ 32 "UPDATE moz_places SET " \ 33 "visit_count = visit_count + (SELECT NEW.visit_type NOT IN " \ 34 "(" EXCLUDED_VISIT_TYPES \ 35 ")), " \ 36 "last_visit_date = MAX(IFNULL(last_visit_date, 0), NEW.visit_date) " \ 37 "WHERE id = NEW.place_id;" \ 38 "END") 39 40 #define CREATE_HISTORYVISITS_AFTERDELETE_TRIGGER \ 41 NS_LITERAL_CSTRING( \ 42 "CREATE TEMP TRIGGER moz_historyvisits_afterdelete_v2_trigger " \ 43 "AFTER DELETE ON moz_historyvisits FOR EACH ROW " \ 44 "BEGIN " \ 45 "UPDATE moz_places SET " \ 46 "visit_count = visit_count - (SELECT OLD.visit_type NOT IN " \ 47 "(" EXCLUDED_VISIT_TYPES \ 48 ")), " \ 49 "last_visit_date = (SELECT visit_date FROM moz_historyvisits " \ 50 "WHERE place_id = OLD.place_id " \ 51 "ORDER BY visit_date DESC LIMIT 1) " \ 52 "WHERE id = OLD.place_id;" \ 53 "END") 54 55 /** 56 * A predicate matching pages on rev_host, based on a given host value. 57 * 'host' may be either the moz_hosts.host column or an alias representing an 58 * equivalent value. 59 */ 60 #define HOST_TO_REVHOST_PREDICATE \ 61 "rev_host = get_unreversed_host(host || '.') || '.' " \ 62 "OR rev_host = get_unreversed_host(host || '.') || '.www.'" 63 64 #define OLDHOST_TO_REVHOST_PREDICATE \ 65 "rev_host = get_unreversed_host(OLD.host || '.') || '.' " \ 66 "OR rev_host = get_unreversed_host(OLD.host || '.') || '.www.'" 67 68 /** 69 * Select the best prefix for a host, based on existing pages registered for it. 70 * Prefixes have a priority, from the top to the bottom, so that secure pages 71 * have higher priority, and more generically "www." prefixed hosts come before 72 * unprefixed ones. 73 * Given a host, examine associated pages and: 74 * - if at least half the typed pages start with https://www. return 75 * https://www. 76 * - if at least half the typed pages start with https:// return https:// 77 * - if all of the typed pages start with ftp: return ftp:// 78 * - This is because mostly people will want to visit the http version 79 * of the site. 80 * - if at least half the typed pages start with www. return www. 81 * - otherwise don't use any prefix 82 */ 83 #define HOSTS_PREFIX_PRIORITY_FRAGMENT \ 84 "SELECT CASE " \ 85 "WHEN ( " \ 86 "SELECT round(avg(substr(url,1,12) = 'https://www.')) FROM moz_places h " \ 87 "WHERE (" HOST_TO_REVHOST_PREDICATE \ 88 ") AND +h.typed = 1 " \ 89 ") THEN 'https://www.' " \ 90 "WHEN ( " \ 91 "SELECT round(avg(substr(url,1,8) = 'https://')) FROM moz_places h " \ 92 "WHERE (" HOST_TO_REVHOST_PREDICATE \ 93 ") AND +h.typed = 1 " \ 94 ") THEN 'https://' " \ 95 "WHEN 1 = ( " \ 96 "SELECT min(substr(url,1,4) = 'ftp:') FROM moz_places h " \ 97 "WHERE (" HOST_TO_REVHOST_PREDICATE \ 98 ") AND +h.typed = 1 " \ 99 ") THEN 'ftp://' " \ 100 "WHEN ( " \ 101 "SELECT round(avg(substr(url,1,11) = 'http://www.')) FROM moz_places h " \ 102 "WHERE (" HOST_TO_REVHOST_PREDICATE \ 103 ") AND +h.typed = 1 " \ 104 ") THEN 'www.' " \ 105 "END " 106 107 // The next few triggers are a workaround for the lack of FOR EACH STATEMENT in 108 // Sqlite, until bug 871908 can be fixed properly. 109 // While doing inserts or deletes into moz_places, we accumulate the affected 110 // hosts into a temp table. Afterwards, we delete everything from the temp 111 // table, causing the AFTER DELETE trigger to fire for it, which will then 112 // update the moz_hosts table. 113 // Note this way we lose atomicity, crashing between the 2 queries may break the 114 // hosts table coherency. So it's better to run those DELETE queries in a single 115 // transaction. 116 // Regardless, this is still better than hanging the browser for several minutes 117 // on a fast machine. 118 #define CREATE_PLACES_AFTERINSERT_TRIGGER \ 119 NS_LITERAL_CSTRING( \ 120 "CREATE TEMP TRIGGER moz_places_afterinsert_trigger " \ 121 "AFTER INSERT ON moz_places FOR EACH ROW " \ 122 "BEGIN " \ 123 "SELECT store_last_inserted_id('moz_places', NEW.id); " \ 124 "INSERT OR IGNORE INTO moz_updatehostsinsert_temp (host)" \ 125 "VALUES (fixup_url(get_unreversed_host(NEW.rev_host)));" \ 126 "END") 127 128 // See CREATE_PLACES_AFTERINSERT_TRIGGER. For each delete in moz_places we 129 // add the host to moz_updatehostsdelete_temp - we then delete everything 130 // from moz_updatehostsdelete_temp, allowing us to run a trigger only once 131 // per host. 132 #define CREATE_PLACES_AFTERDELETE_TRIGGER \ 133 NS_LITERAL_CSTRING( \ 134 "CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \ 135 "AFTER DELETE ON moz_places FOR EACH ROW " \ 136 "BEGIN " \ 137 "INSERT OR IGNORE INTO moz_updatehostsdelete_temp (host)" \ 138 "VALUES (fixup_url(get_unreversed_host(OLD.rev_host)));" \ 139 "END") 140 141 // See CREATE_PLACES_AFTERINSERT_TRIGGER. This is the trigger that we want 142 // to ensure gets run for each distinct host that we insert into moz_places. 143 #define CREATE_UPDATEHOSTSINSERT_AFTERDELETE_TRIGGER \ 144 NS_LITERAL_CSTRING( \ 145 "CREATE TEMP TRIGGER moz_updatehostsinsert_afterdelete_trigger " \ 146 "AFTER DELETE ON moz_updatehostsinsert_temp FOR EACH ROW " \ 147 "BEGIN " \ 148 "INSERT OR REPLACE INTO moz_hosts (id, host, frecency, typed, prefix) " \ 149 "SELECT " \ 150 "(SELECT id FROM moz_hosts WHERE host = OLD.host), " \ 151 "OLD.host, " \ 152 "MAX(IFNULL((SELECT frecency FROM moz_hosts WHERE host = OLD.host), " \ 153 "-1), " \ 154 "(SELECT MAX(frecency) FROM moz_places h " \ 155 "WHERE (" OLDHOST_TO_REVHOST_PREDICATE \ 156 "))), " \ 157 "MAX(IFNULL((SELECT typed FROM moz_hosts WHERE host = OLD.host), 0), " \ 158 "(SELECT MAX(typed) FROM moz_places h " \ 159 "WHERE (" OLDHOST_TO_REVHOST_PREDICATE \ 160 "))), " \ 161 "(" HOSTS_PREFIX_PRIORITY_FRAGMENT \ 162 "FROM ( " \ 163 "SELECT OLD.host AS host " \ 164 ")" \ 165 ") " \ 166 " WHERE LENGTH(OLD.host) > 1; " \ 167 "END") 168 169 // See CREATE_PLACES_AFTERINSERT_TRIGGER. This is the trigger that we want 170 // to ensure gets run for each distinct host that we delete from moz_places. 171 #define CREATE_UPDATEHOSTSDELETE_AFTERDELETE_TRIGGER \ 172 NS_LITERAL_CSTRING( \ 173 "CREATE TEMP TRIGGER moz_updatehostsdelete_afterdelete_trigger " \ 174 "AFTER DELETE ON moz_updatehostsdelete_temp FOR EACH ROW " \ 175 "BEGIN " \ 176 "DELETE FROM moz_hosts " \ 177 "WHERE host = OLD.host " \ 178 "AND NOT EXISTS(" \ 179 "SELECT 1 FROM moz_places " \ 180 "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \ 181 "OR rev_host = get_unreversed_host(host || '.') || '.www.' " \ 182 "); " \ 183 "UPDATE moz_hosts " \ 184 "SET prefix = (" HOSTS_PREFIX_PRIORITY_FRAGMENT \ 185 ") " \ 186 "WHERE host = OLD.host; " \ 187 "DELETE FROM moz_icons " \ 188 "WHERE fixed_icon_url_hash = hash(fixup_url(OLD.host || " \ 189 "'/favicon.ico')) " \ 190 "AND fixup_url(icon_url) = fixup_url(OLD.host || '/favicon.ico') " \ 191 "AND NOT EXISTS (SELECT 1 FROM moz_hosts WHERE host = OLD.host " \ 192 "OR host = fixup_url(OLD.host));" \ 193 "END") 194 195 // For performance reasons the host frecency is updated only when the page 196 // frecency changes by a meaningful percentage. This is because the frecency 197 // decay algorithm requires to update all the frecencies at once, causing a 198 // too high overhead, while leaving the ordering unchanged. 199 #define CREATE_PLACES_AFTERUPDATE_FRECENCY_TRIGGER \ 200 NS_LITERAL_CSTRING( \ 201 "CREATE TEMP TRIGGER moz_places_afterupdate_frecency_trigger " \ 202 "AFTER UPDATE OF frecency ON moz_places FOR EACH ROW " \ 203 "WHEN NEW.frecency >= 0 " \ 204 "AND ABS(" \ 205 "IFNULL((NEW.frecency - OLD.frecency) / CAST(NEW.frecency AS REAL), " \ 206 "(NEW.frecency - OLD.frecency))" \ 207 ") > .05 " \ 208 "BEGIN " \ 209 "UPDATE moz_hosts " \ 210 "SET frecency = (SELECT MAX(frecency) FROM moz_places " \ 211 "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \ 212 "OR rev_host = get_unreversed_host(host || '.') || '.www.') " \ 213 "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \ 214 "END") 215 216 #define CREATE_PLACES_AFTERUPDATE_TYPED_TRIGGER \ 217 NS_LITERAL_CSTRING( \ 218 "CREATE TEMP TRIGGER moz_places_afterupdate_typed_trigger " \ 219 "AFTER UPDATE OF typed ON moz_places FOR EACH ROW " \ 220 "WHEN NEW.typed = 1 " \ 221 "BEGIN " \ 222 "UPDATE moz_hosts " \ 223 "SET typed = 1 " \ 224 "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \ 225 "END") 226 227 /** 228 * This trigger removes a row from moz_openpages_temp when open_count reaches 0. 229 * 230 * @note this should be kept up-to-date with the definition in 231 * nsPlacesAutoComplete.js 232 */ 233 #define CREATE_REMOVEOPENPAGE_CLEANUP_TRIGGER \ 234 NS_LITERAL_CSTRING( \ 235 "CREATE TEMPORARY TRIGGER moz_openpages_temp_afterupdate_trigger " \ 236 "AFTER UPDATE OF open_count ON moz_openpages_temp FOR EACH ROW " \ 237 "WHEN NEW.open_count = 0 " \ 238 "BEGIN " \ 239 "DELETE FROM moz_openpages_temp " \ 240 "WHERE url = NEW.url " \ 241 "AND userContextId = NEW.userContextId;" \ 242 "END") 243 244 #define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \ 245 NS_LITERAL_CSTRING( \ 246 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterdelete_trigger " \ 247 "AFTER DELETE ON moz_bookmarks FOR EACH ROW " \ 248 "BEGIN " \ 249 "UPDATE moz_places " \ 250 "SET foreign_count = foreign_count - 1 " \ 251 "WHERE id = OLD.fk;" \ 252 "END") 253 254 #define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \ 255 NS_LITERAL_CSTRING( \ 256 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterinsert_trigger " \ 257 "AFTER INSERT ON moz_bookmarks FOR EACH ROW " \ 258 "BEGIN " \ 259 "SELECT store_last_inserted_id('moz_bookmarks', NEW.id); " \ 260 "UPDATE moz_places " \ 261 "SET foreign_count = foreign_count + 1 " \ 262 "WHERE id = NEW.fk;" \ 263 "END") 264 265 #define CREATE_BOOKMARKS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \ 266 NS_LITERAL_CSTRING( \ 267 "CREATE TEMP TRIGGER moz_bookmarks_foreign_count_afterupdate_trigger " \ 268 "AFTER UPDATE OF fk ON moz_bookmarks FOR EACH ROW " \ 269 "BEGIN " \ 270 "UPDATE moz_places " \ 271 "SET foreign_count = foreign_count + 1 " \ 272 "WHERE id = NEW.fk;" \ 273 "UPDATE moz_places " \ 274 "SET foreign_count = foreign_count - 1 " \ 275 "WHERE id = OLD.fk;" \ 276 "END") 277 278 #define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERDELETE_TRIGGER \ 279 NS_LITERAL_CSTRING( \ 280 "CREATE TEMP TRIGGER moz_keywords_foreign_count_afterdelete_trigger " \ 281 "AFTER DELETE ON moz_keywords FOR EACH ROW " \ 282 "BEGIN " \ 283 "UPDATE moz_places " \ 284 "SET foreign_count = foreign_count - 1 " \ 285 "WHERE id = OLD.place_id;" \ 286 "END") 287 288 #define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERINSERT_TRIGGER \ 289 NS_LITERAL_CSTRING( \ 290 "CREATE TEMP TRIGGER moz_keyords_foreign_count_afterinsert_trigger " \ 291 "AFTER INSERT ON moz_keywords FOR EACH ROW " \ 292 "BEGIN " \ 293 "UPDATE moz_places " \ 294 "SET foreign_count = foreign_count + 1 " \ 295 "WHERE id = NEW.place_id;" \ 296 "END") 297 298 #define CREATE_KEYWORDS_FOREIGNCOUNT_AFTERUPDATE_TRIGGER \ 299 NS_LITERAL_CSTRING( \ 300 "CREATE TEMP TRIGGER moz_keywords_foreign_count_afterupdate_trigger " \ 301 "AFTER UPDATE OF place_id ON moz_keywords FOR EACH ROW " \ 302 "BEGIN " \ 303 "UPDATE moz_places " \ 304 "SET foreign_count = foreign_count + 1 " \ 305 "WHERE id = NEW.place_id; " \ 306 "UPDATE moz_places " \ 307 "SET foreign_count = foreign_count - 1 " \ 308 "WHERE id = OLD.place_id; " \ 309 "END") 310 311 #define CREATE_ICONS_AFTERINSERT_TRIGGER \ 312 NS_LITERAL_CSTRING( \ 313 "CREATE TEMP TRIGGER moz_icons_afterinsert_v1_trigger " \ 314 "AFTER INSERT ON moz_icons FOR EACH ROW " \ 315 "BEGIN " \ 316 "SELECT store_last_inserted_id('moz_icons', NEW.id); " \ 317 "END") 318 319 #endif // __nsPlacesTriggers_h__ 320