michael@0: /* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*-
michael@0:  * vim: sw=2 ts=2 et lcs=trail\:.,tab\:>~ :
michael@0:  * This Source Code Form is subject to the terms of the Mozilla Public
michael@0:  * License, v. 2.0. If a copy of the MPL was not distributed with this
michael@0:  * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
michael@0: 
michael@0: #include "nsPlacesTables.h"
michael@0: 
michael@0: #ifndef __nsPlacesTriggers_h__
michael@0: #define __nsPlacesTriggers_h__
michael@0: 
michael@0: /**
michael@0:  * Exclude these visit types:
michael@0:  *  0 - invalid
michael@0:  *  4 - EMBED
michael@0:  *  7 - DOWNLOAD
michael@0:  *  7 - FRAMED_LINK
michael@0:  **/
michael@0: #define EXCLUDED_VISIT_TYPES "0, 4, 7, 8"
michael@0: 
michael@0: /**
michael@0:  * This triggers update visit_count and last_visit_date based on historyvisits
michael@0:  * table changes.
michael@0:  */
michael@0: #define CREATE_HISTORYVISITS_AFTERINSERT_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_historyvisits_afterinsert_v2_trigger " \
michael@0:   "AFTER INSERT ON moz_historyvisits FOR EACH ROW " \
michael@0:   "BEGIN " \
michael@0:     "UPDATE moz_places SET " \
michael@0:       "visit_count = visit_count + (SELECT NEW.visit_type NOT IN (" EXCLUDED_VISIT_TYPES ")), "\
michael@0:       "last_visit_date = MAX(IFNULL(last_visit_date, 0), NEW.visit_date) " \
michael@0:     "WHERE id = NEW.place_id;" \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: #define CREATE_HISTORYVISITS_AFTERDELETE_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_historyvisits_afterdelete_v2_trigger " \
michael@0:   "AFTER DELETE ON moz_historyvisits FOR EACH ROW " \
michael@0:   "BEGIN " \
michael@0:     "UPDATE moz_places SET " \
michael@0:       "visit_count = visit_count - (SELECT OLD.visit_type NOT IN (" EXCLUDED_VISIT_TYPES ")), "\
michael@0:       "last_visit_date = (SELECT visit_date FROM moz_historyvisits " \
michael@0:                          "WHERE place_id = OLD.place_id " \
michael@0:                          "ORDER BY visit_date DESC LIMIT 1) " \
michael@0:     "WHERE id = OLD.place_id;" \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: /**
michael@0:  * A predicate matching pages on rev_host, based on a given host value.
michael@0:  * 'host' may be either the moz_hosts.host column or an alias representing an
michael@0:  * equivalent value.
michael@0:  */
michael@0: #define HOST_TO_REVHOST_PREDICATE \
michael@0:   "rev_host = get_unreversed_host(host || '.') || '.' " \
michael@0:   "OR rev_host = get_unreversed_host(host || '.') || '.www.'"
michael@0: 
michael@0: /**
michael@0:  * Select the best prefix for a host, based on existing pages registered for it.
michael@0:  * Prefixes have a priority, from the top to the bottom, so that secure pages
michael@0:  * have higher priority, and more generically "www." prefixed hosts come before
michael@0:  * unprefixed ones.
michael@0:  * Given a host, examine associated pages and:
michael@0:  *  - if all of the typed pages start with https://www. return https://www.
michael@0:  *  - if all of the typed pages start with https:// return https://
michael@0:  *  - if all of the typed pages start with ftp: return ftp://
michael@0:  *  - if all of the typed pages start with www. return www.
michael@0:  *  - otherwise don't use any prefix
michael@0:  */
michael@0: #define HOSTS_PREFIX_PRIORITY_FRAGMENT \
michael@0:   "SELECT CASE " \
michael@0:     "WHEN 1 = ( " \
michael@0:       "SELECT min(substr(url,1,12) = 'https://www.') FROM moz_places h " \
michael@0:       "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
michael@0:     ") THEN 'https://www.' " \
michael@0:     "WHEN 1 = ( " \
michael@0:       "SELECT min(substr(url,1,8) = 'https://') FROM moz_places h " \
michael@0:       "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
michael@0:     ") THEN 'https://' " \
michael@0:     "WHEN 1 = ( " \
michael@0:       "SELECT min(substr(url,1,4) = 'ftp:') FROM moz_places h " \
michael@0:       "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
michael@0:     ") THEN 'ftp://' " \
michael@0:     "WHEN 1 = ( " \
michael@0:       "SELECT min(substr(url,1,11) = 'http://www.') FROM moz_places h " \
michael@0:       "WHERE (" HOST_TO_REVHOST_PREDICATE ") AND +h.typed = 1 " \
michael@0:     ") THEN 'www.' " \
michael@0:   "END "
michael@0: 
michael@0: /**
michael@0:  * These triggers update the hostnames table whenever moz_places changes.
michael@0:  */
michael@0: #define CREATE_PLACES_AFTERINSERT_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_places_afterinsert_trigger " \
michael@0:   "AFTER INSERT ON moz_places FOR EACH ROW " \
michael@0:   "WHEN LENGTH(NEW.rev_host) > 1 " \
michael@0:   "BEGIN " \
michael@0:     "INSERT OR REPLACE INTO moz_hosts (id, host, frecency, typed, prefix) " \
michael@0:     "VALUES (" \
michael@0:       "(SELECT id FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), " \
michael@0:       "fixup_url(get_unreversed_host(NEW.rev_host)), " \
michael@0:       "MAX(IFNULL((SELECT frecency FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), -1), NEW.frecency), " \
michael@0:       "MAX(IFNULL((SELECT typed FROM moz_hosts WHERE host = fixup_url(get_unreversed_host(NEW.rev_host))), 0), NEW.typed), " \
michael@0:       "(" HOSTS_PREFIX_PRIORITY_FRAGMENT \
michael@0:        "FROM ( " \
michael@0:           "SELECT fixup_url(get_unreversed_host(NEW.rev_host)) AS host " \
michael@0:         ") AS match " \
michael@0:       ") " \
michael@0:     "); " \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: #define CREATE_PLACES_AFTERDELETE_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_places_afterdelete_trigger " \
michael@0:   "AFTER DELETE ON moz_places FOR EACH ROW " \
michael@0:   "BEGIN " \
michael@0:     "DELETE FROM moz_hosts " \
michael@0:     "WHERE host = fixup_url(get_unreversed_host(OLD.rev_host)) " \
michael@0:       "AND NOT EXISTS(" \
michael@0:         "SELECT 1 FROM moz_places " \
michael@0:           "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \
michael@0:              "OR rev_host = get_unreversed_host(host || '.') || '.www.' " \
michael@0:       "); " \
michael@0:     "UPDATE moz_hosts " \
michael@0:     "SET prefix = (" HOSTS_PREFIX_PRIORITY_FRAGMENT ") " \
michael@0:     "WHERE host = fixup_url(get_unreversed_host(OLD.rev_host)); " \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: // For performance reasons the host frecency is updated only when the page
michael@0: // frecency changes by a meaningful percentage.  This is because the frecency
michael@0: // decay algorithm requires to update all the frecencies at once, causing a
michael@0: // too high overhead, while leaving the ordering unchanged.
michael@0: #define CREATE_PLACES_AFTERUPDATE_FRECENCY_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_places_afterupdate_frecency_trigger " \
michael@0:   "AFTER UPDATE OF frecency ON moz_places FOR EACH ROW " \
michael@0:   "WHEN NEW.frecency >= 0 " \
michael@0:     "AND ABS(" \
michael@0:       "IFNULL((NEW.frecency - OLD.frecency) / CAST(NEW.frecency AS REAL), " \
michael@0:              "(NEW.frecency - OLD.frecency))" \
michael@0:     ") > .05 " \
michael@0:   "BEGIN " \
michael@0:     "UPDATE moz_hosts " \
michael@0:     "SET frecency = (SELECT MAX(frecency) FROM moz_places " \
michael@0:                     "WHERE rev_host = get_unreversed_host(host || '.') || '.' " \
michael@0:                        "OR rev_host = get_unreversed_host(host || '.') || '.www.') " \
michael@0:     "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: #define CREATE_PLACES_AFTERUPDATE_TYPED_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMP TRIGGER moz_places_afterupdate_typed_trigger " \
michael@0:   "AFTER UPDATE OF typed ON moz_places FOR EACH ROW " \
michael@0:   "WHEN NEW.typed = 1 " \
michael@0:   "BEGIN " \
michael@0:     "UPDATE moz_hosts " \
michael@0:     "SET typed = 1 " \
michael@0:     "WHERE host = fixup_url(get_unreversed_host(NEW.rev_host)); " \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: /**
michael@0:  * This trigger removes a row from moz_openpages_temp when open_count reaches 0.
michael@0:  *
michael@0:  * @note this should be kept up-to-date with the definition in
michael@0:  *       nsPlacesAutoComplete.js
michael@0:  */
michael@0: #define CREATE_REMOVEOPENPAGE_CLEANUP_TRIGGER NS_LITERAL_CSTRING( \
michael@0:   "CREATE TEMPORARY TRIGGER moz_openpages_temp_afterupdate_trigger " \
michael@0:   "AFTER UPDATE OF open_count ON moz_openpages_temp FOR EACH ROW " \
michael@0:   "WHEN NEW.open_count = 0 " \
michael@0:   "BEGIN " \
michael@0:     "DELETE FROM moz_openpages_temp " \
michael@0:     "WHERE url = NEW.url;" \
michael@0:   "END" \
michael@0: )
michael@0: 
michael@0: #endif // __nsPlacesTriggers_h__