1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/toolkit/components/places/nsPlacesTriggers.h Wed Dec 31 06:09:35 2014 +0100
1.3 @@ -0,0 +1,178 @@
1.4 +/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*-
1.5 + * vim: sw=2 ts=2 et lcs=trail\:.,tab\:>~ :
1.6 + * This Source Code Form is subject to the terms of the Mozilla Public
1.7 + * License, v. 2.0. If a copy of the MPL was not distributed with this
1.8 + * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
1.9 +
1.10 +#include "nsPlacesTables.h"
1.11 +
1.12 +#ifndef __nsPlacesTriggers_h__
1.13 +#define __nsPlacesTriggers_h__
1.14 +
1.15 +/**
1.16 + * Exclude these visit types:
1.17 + * 0 - invalid
1.18 + * 4 - EMBED
1.19 + * 7 - DOWNLOAD
1.20 + * 7 - FRAMED_LINK
1.21 + **/
1.22 +#define EXCLUDED_VISIT_TYPES "0, 4, 7, 8"
1.23 +
1.24 +/**
1.25 + * This triggers update visit_count and last_visit_date based on historyvisits
1.26 + * table changes.
1.27 + */
1.28 +#define CREATE_HISTORYVISITS_AFTERINSERT_TRIGGER NS_LITERAL_CSTRING( \
1.29 + "CREATE TEMP TRIGGER moz_historyvisits_afterinsert_v2_trigger " \
1.30 + "AFTER INSERT ON moz_historyvisits FOR EACH ROW " \
1.31 + "BEGIN " \
1.32 + "UPDATE moz_places SET " \
1.33 + "visit_count = visit_count + (SELECT NEW.visit_type NOT IN (" EXCLUDED_VISIT_TYPES ")), "\
1.34 + "last_visit_date = MAX(IFNULL(last_visit_date, 0), NEW.visit_date) " \
1.35 + "WHERE id = NEW.place_id;" \
1.36 + "END" \
1.37 +)
1.38 +
1.39 +#define CREATE_HISTORYVISITS_AFTERDELETE_TRIGGER NS_LITERAL_CSTRING( \
1.40 + "CREATE TEMP TRIGGER moz_historyvisits_afterdelete_v2_trigger " \
1.41 + "AFTER DELETE ON moz_historyvisits FOR EACH ROW " \
1.42 + "BEGIN " \
1.43 + "UPDATE moz_places SET " \
1.44 + "visit_count = visit_count - (SELECT OLD.visit_type NOT IN (" EXCLUDED_VISIT_TYPES ")), "\
1.45 + "last_visit_date = (SELECT visit_date FROM moz_historyvisits " \
1.46 + "WHERE place_id = OLD.place_id " \
1.47 + "ORDER BY visit_date DESC LIMIT 1) " \
1.48 + "WHERE id = OLD.place_id;" \
1.49 + "END" \
1.50 +)
1.51 +
1.52 +/**
1.53 + * A predicate matching pages on rev_host, based on a given host value.
1.54 + * 'host' may be either the moz_hosts.host column or an alias representing an
1.55 + * equivalent value.
1.56 + */
1.57 +#define HOST_TO_REVHOST_PREDICATE \
1.58 + "rev_host = get_unreversed_host(host || '.') || '.' " \
1.59 + "OR rev_host = get_unreversed_host(host || '.') || '.www.'"
1.60 +
1.61 +/**
1.62 + * Select the best prefix for a host, based on existing pages registered for it.
1.63 + * Prefixes have a priority, from the top to the bottom, so that secure pages
1.64 + * have higher priority, and more generically "www." prefixed hosts come before
1.65 + * unprefixed ones.
1.66 + * Given a host, examine associated pages and:
1.67 + * - if all of the typed pages start with https://www. return https://www.
1.68 + * - if all of the typed pages start with https:// return https://
1.69 + * - if all of the typed pages start with ftp: return ftp://
1.70 + * - if all of the typed pages start with www. return www.
1.71 + * - otherwise don't use any prefix
1.72 + */
1.73 +#define HOSTS_PREFIX_PRIORITY_FRAGMENT \
1.74 + "SELECT CASE " \
1.75 + "WHEN 1 = ( " \
1.76 + "SELECT min(substr(url,1,12) = 'https://www.') FROM moz_places h " \
1.77 + "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
1.78 + ") THEN 'https://www.' " \
1.79 + "WHEN 1 = ( " \
1.80 + "SELECT min(substr(url,1,8) = 'https://') FROM moz_places h " \
1.81 + "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
1.82 + ") THEN 'https://' " \
1.83 + "WHEN 1 = ( " \
1.84 + "SELECT min(substr(url,1,4) = 'ftp:') FROM moz_places h " \
1.85 + "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
1.86 + ") THEN 'ftp://' " \
1.87 + "WHEN 1 = ( " \
1.88 + "SELECT min(substr(url,1,11) = 'http://www.') FROM moz_places h " \
1.89 + "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
1.90 + ") THEN 'www.' " \
1.91 + "END "
1.92 +
1.93 +/**
1.94 + * These triggers update the hostnames table whenever moz_places changes.
1.95 + */
1.96 +#define CREATE_PLACES_AFTERINSERT_TRIGGER NS_LITERAL_CSTRING( \
1.97 + "CREATE TEMP TRIGGER moz_places_afterinsert_trigger " \
1.98 + "AFTER INSERT ON moz_places FOR EACH ROW " \
1.99 + "WHEN LENGTH(NEW.rev_host) > 1 " \
1.100 + "BEGIN " \
1.101 + "INSERT OR REPLACE INTO moz_hosts (id, host, frecency, typed, prefix) " \
1.102 + "VALUES (" \
1.103 + "(SELECT id FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), " \
1.104 + "fixup_url(get_unreversed_host(NEW.rev_host)), " \
1.105 + "MAX(IFNULL((SELECT frecency FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), -1), NEW.frecency), " \
1.106 + "MAX(IFNULL((SELECT typed FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), 0), NEW.typed), " \
1.107 + "(" HOSTS_PREFIX_PRIORITY_FRAGMENT \
1.108 + "FROM ( " \
1.109 + "SELECT fixup_url(get_unreversed_host(NEW.rev_host)) AS host " \
1.110 + ") AS match " \
1.111 + ") " \
1.112 + "); " \
1.113 + "END" \
1.114 +)
1.115 +
1.116 +#define CREATE_PLACES_AFTERDELETE_TRIGGER NS_LITERAL_CSTRING( \
1.117 + "CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \
1.118 + "AFTER DELETE ON moz_places FOR EACH ROW " \
1.119 + "BEGIN " \
1.120 + "DELETE FROM moz_hosts " \
1.121 + "WHERE host = fixup_url(get_unreversed_host(OLD.rev_host)) " \
1.122 + "AND NOT EXISTS(" \
1.123 + "SELECT 1 FROM moz_places " \
1.124 + "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \
1.125 + "OR rev_host = get_unreversed_host(host || '.') || '.www.' " \
1.126 + "); " \
1.127 + "UPDATE moz_hosts " \
1.128 + "SET prefix = (" HOSTS_PREFIX_PRIORITY_FRAGMENT ") " \
1.129 + "WHERE host = fixup_url(get_unreversed_host(OLD.rev_host)); " \
1.130 + "END" \
1.131 +)
1.132 +
1.133 +// For performance reasons the host frecency is updated only when the page
1.134 +// frecency changes by a meaningful percentage. This is because the frecency
1.135 +// decay algorithm requires to update all the frecencies at once, causing a
1.136 +// too high overhead, while leaving the ordering unchanged.
1.137 +#define CREATE_PLACES_AFTERUPDATE_FRECENCY_TRIGGER NS_LITERAL_CSTRING( \
1.138 + "CREATE TEMP TRIGGER moz_places_afterupdate_frecency_trigger " \
1.139 + "AFTER UPDATE OF frecency ON moz_places FOR EACH ROW " \
1.140 + "WHEN NEW.frecency >= 0 " \
1.141 + "AND ABS(" \
1.142 + "IFNULL((NEW.frecency - OLD.frecency) / CAST(NEW.frecency AS REAL), " \
1.143 + "(NEW.frecency - OLD.frecency))" \
1.144 + ") > .05 " \
1.145 + "BEGIN " \
1.146 + "UPDATE moz_hosts " \
1.147 + "SET frecency = (SELECT MAX(frecency) FROM moz_places " \
1.148 + "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \
1.149 + "OR rev_host = get_unreversed_host(host || '.') || '.www.') " \
1.150 + "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \
1.151 + "END" \
1.152 +)
1.153 +
1.154 +#define CREATE_PLACES_AFTERUPDATE_TYPED_TRIGGER NS_LITERAL_CSTRING( \
1.155 + "CREATE TEMP TRIGGER moz_places_afterupdate_typed_trigger " \
1.156 + "AFTER UPDATE OF typed ON moz_places FOR EACH ROW " \
1.157 + "WHEN NEW.typed = 1 " \
1.158 + "BEGIN " \
1.159 + "UPDATE moz_hosts " \
1.160 + "SET typed = 1 " \
1.161 + "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \
1.162 + "END" \
1.163 +)
1.164 +
1.165 +/**
1.166 + * This trigger removes a row from moz_openpages_temp when open_count reaches 0.
1.167 + *
1.168 + * @note this should be kept up-to-date with the definition in
1.169 + * nsPlacesAutoComplete.js
1.170 + */
1.171 +#define CREATE_REMOVEOPENPAGE_CLEANUP_TRIGGER NS_LITERAL_CSTRING( \
1.172 + "CREATE TEMPORARY TRIGGER moz_openpages_temp_afterupdate_trigger " \
1.173 + "AFTER UPDATE OF open_count ON moz_openpages_temp FOR EACH ROW " \
1.174 + "WHEN NEW.open_count = 0 " \
1.175 + "BEGIN " \
1.176 + "DELETE FROM moz_openpages_temp " \
1.177 + "WHERE url = NEW.url;" \
1.178 + "END" \
1.179 +)
1.180 +
1.181 +#endif // __nsPlacesTriggers_h__
