importer: Change country code logic

[location/libloc.git] / src / scripts / location-importer.in

#!/usr/bin/python3
###############################################################################
#                                                                             #
# libloc - A library to determine the location of someone on the Internet     #
#                                                                             #
# Copyright (C) 2020-2024 IPFire Development Team <info@ipfire.org>           #
#                                                                             #
# This library is free software; you can redistribute it and/or               #
# modify it under the terms of the GNU Lesser General Public                  #
# License as published by the Free Software Foundation; either                #
# version 2.1 of the License, or (at your option) any later version.          #
#                                                                             #
# This library is distributed in the hope that it will be useful,             #
# but WITHOUT ANY WARRANTY; without even the implied warranty of              #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU           #
# Lesser General Public License for more details.                             #
#                                                                             #
###############################################################################

import argparse
import concurrent.futures
import csv
import functools
import http.client
import ipaddress
import json
import logging
import math
import re
import socket
import sys
import urllib.error

# Load our location module
import location
import location.database
import location.importer
from location.i18n import _

# Initialise logging
log = logging.getLogger("location.importer")
log.propagate = 1

# Define constants
VALID_ASN_RANGES = (
        (1, 23455),
        (23457, 64495),
        (131072, 4199999999),
)

TRANSLATED_COUNTRIES = {
        # When people say UK, they mean GB
        "UK" : "GB",
}

IGNORED_COUNTRIES = set((
        # Formerly Yugoslavia
        "YU",

        # Some people use ZZ to say "no country" or to hide the country
        "ZZ",
))

# Configure the CSV parser for ARIN
csv.register_dialect("arin", delimiter=",", quoting=csv.QUOTE_ALL, quotechar="\"")

class CLI(object):
        def parse_cli(self):
                parser = argparse.ArgumentParser(
                        description=_("Location Importer Command Line Interface"),
                )
                subparsers = parser.add_subparsers()

                # Global configuration flags
                parser.add_argument("--debug", action="store_true",
                        help=_("Enable debug output"))
                parser.add_argument("--quiet", action="store_true",
                        help=_("Enable quiet mode"))

                # version
                parser.add_argument("--version", action="version",
                        version="%(prog)s @VERSION@")

                # Database
                parser.add_argument("--database-host", required=True,
                        help=_("Database Hostname"), metavar=_("HOST"))
                parser.add_argument("--database-name", required=True,
                        help=_("Database Name"), metavar=_("NAME"))
                parser.add_argument("--database-username", required=True,
                        help=_("Database Username"), metavar=_("USERNAME"))
                parser.add_argument("--database-password", required=True,
                        help=_("Database Password"), metavar=_("PASSWORD"))

                # Write Database
                write = subparsers.add_parser("write", help=_("Write database to file"))
                write.set_defaults(func=self.handle_write)
                write.add_argument("file", nargs=1, help=_("Database File"))
                write.add_argument("--signing-key", nargs="?", type=open, help=_("Signing Key"))
                write.add_argument("--backup-signing-key", nargs="?", type=open, help=_("Backup Signing Key"))
                write.add_argument("--vendor", nargs="?", help=_("Sets the vendor"))
                write.add_argument("--description", nargs="?", help=_("Sets a description"))
                write.add_argument("--license", nargs="?", help=_("Sets the license"))
                write.add_argument("--version", type=int, help=_("Database Format Version"))

                # Update WHOIS
                update_whois = subparsers.add_parser("update-whois", help=_("Update WHOIS Information"))
                update_whois.set_defaults(func=self.handle_update_whois)

                # Update announcements
                update_announcements = subparsers.add_parser("update-announcements",
                        help=_("Update BGP Annoucements"))
                update_announcements.set_defaults(func=self.handle_update_announcements)
                update_announcements.add_argument("server", nargs=1,
                        help=_("Route Server to connect to"), metavar=_("SERVER"))

                # Update geofeeds
                update_geofeeds = subparsers.add_parser("update-geofeeds",
                        help=_("Update Geofeeds"))
                update_geofeeds.set_defaults(func=self.handle_update_geofeeds)

                # Update feeds
                update_feeds = subparsers.add_parser("update-feeds",
                        help=_("Update Feeds"))
                update_feeds.add_argument("feeds", nargs="*",
                        help=_("Only update these feeds"))
                update_feeds.set_defaults(func=self.handle_update_feeds)

                # Update overrides
                update_overrides = subparsers.add_parser("update-overrides",
                        help=_("Update overrides"),
                )
                update_overrides.add_argument(
                        "files", nargs="+", help=_("Files to import"),
                )
                update_overrides.set_defaults(func=self.handle_update_overrides)

                # Import countries
                import_countries = subparsers.add_parser("import-countries",
                        help=_("Import countries"),
                )
                import_countries.add_argument("file", nargs=1, type=argparse.FileType("r"),
                        help=_("File to import"))
                import_countries.set_defaults(func=self.handle_import_countries)

                args = parser.parse_args()

                # Configure logging
                if args.debug:
                        location.logger.set_level(logging.DEBUG)
                elif args.quiet:
                        location.logger.set_level(logging.WARNING)

                # Print usage if no action was given
                if not "func" in args:
                        parser.print_usage()
                        sys.exit(2)

                return args

        def run(self):
                # Parse command line arguments
                args = self.parse_cli()

                # Initialise database
                self.db = self._setup_database(args)

                # Call function
                ret = args.func(args)

                # Return with exit code
                if ret:
                        sys.exit(ret)

                # Otherwise just exit
                sys.exit(0)

        def _setup_database(self, ns):
                """
                        Initialise the database
                """
                # Connect to database
                db = location.database.Connection(
                        host=ns.database_host, database=ns.database_name,
                        user=ns.database_username, password=ns.database_password,
                )

                with db.transaction():
                        db.execute("""
                                -- announcements
                                CREATE TABLE IF NOT EXISTS announcements(network inet, autnum bigint,
                                        first_seen_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                                        last_seen_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP);
                                CREATE UNIQUE INDEX IF NOT EXISTS announcements_networks ON announcements(network);
                                CREATE INDEX IF NOT EXISTS announcements_family ON announcements(family(network));
                                CREATE INDEX IF NOT EXISTS announcements_search ON announcements USING GIST(network inet_ops);

                                -- autnums
                                CREATE TABLE IF NOT EXISTS autnums(number bigint, name text NOT NULL);
                                ALTER TABLE autnums ADD COLUMN IF NOT EXISTS source text;
                                CREATE UNIQUE INDEX IF NOT EXISTS autnums_number ON autnums(number);

                                -- countries
                                CREATE TABLE IF NOT EXISTS countries(
                                        country_code text NOT NULL, name text NOT NULL, continent_code text NOT NULL);
                                CREATE UNIQUE INDEX IF NOT EXISTS countries_country_code ON countries(country_code);

                                -- networks
                                CREATE TABLE IF NOT EXISTS networks(network inet, country text);
                                ALTER TABLE networks ADD COLUMN IF NOT EXISTS original_countries text[];
                                ALTER TABLE networks ADD COLUMN IF NOT EXISTS source text;
                                CREATE UNIQUE INDEX IF NOT EXISTS networks_network ON networks(network);
                                CREATE INDEX IF NOT EXISTS networks_family ON networks USING BTREE(family(network));
                                CREATE INDEX IF NOT EXISTS networks_search ON networks USING GIST(network inet_ops);

                                -- geofeeds
                                CREATE TABLE IF NOT EXISTS geofeeds(
                                        id serial primary key,
                                        url text,
                                        status integer default null,
                                        updated_at timestamp without time zone default null
                                );
                                ALTER TABLE geofeeds ADD COLUMN IF NOT EXISTS error text;
                                CREATE UNIQUE INDEX IF NOT EXISTS geofeeds_unique
                                        ON geofeeds(url);
                                CREATE TABLE IF NOT EXISTS geofeed_networks(
                                        geofeed_id integer references geofeeds(id) on delete cascade,
                                        network inet,
                                        country text,
                                        region text,
                                        city text
                                );
                                CREATE INDEX IF NOT EXISTS geofeed_networks_geofeed_id
                                        ON geofeed_networks(geofeed_id);
                                CREATE INDEX IF NOT EXISTS geofeed_networks_search
                                        ON geofeed_networks USING GIST(network inet_ops);
                                CREATE TABLE IF NOT EXISTS network_geofeeds(network inet, url text);
                                CREATE UNIQUE INDEX IF NOT EXISTS network_geofeeds_unique
                                        ON network_geofeeds(network);
                                CREATE INDEX IF NOT EXISTS network_geofeeds_search
                                        ON network_geofeeds USING GIST(network inet_ops);
                                CREATE INDEX IF NOT EXISTS network_geofeeds_url
                                        ON network_geofeeds(url);

                                -- feeds
                                CREATE TABLE IF NOT EXISTS autnum_feeds(
                                        number bigint NOT NULL,
                                        source text NOT NULL,
                                        name text,
                                        country text,
                                        is_anonymous_proxy boolean,
                                        is_satellite_provider boolean,
                                        is_anycast boolean,
                                        is_drop boolean
                                );
                                CREATE UNIQUE INDEX IF NOT EXISTS autnum_feeds_unique
                                        ON autnum_feeds(number, source);

                                CREATE TABLE IF NOT EXISTS network_feeds(
                                        network inet NOT NULL,
                                        source text NOT NULL,
                                        country text,
                                        is_anonymous_proxy boolean,
                                        is_satellite_provider boolean,
                                        is_anycast boolean,
                                        is_drop boolean
                                );
                                CREATE UNIQUE INDEX IF NOT EXISTS network_feeds_unique
                                        ON network_feeds(network, source);
                                CREATE INDEX IF NOT EXISTS network_feeds_search
                                        ON network_feeds USING GIST(network inet_ops);

                                -- overrides
                                CREATE TABLE IF NOT EXISTS autnum_overrides(
                                        number bigint NOT NULL,
                                        name text,
                                        country text,
                                        is_anonymous_proxy boolean,
                                        is_satellite_provider boolean,
                                        is_anycast boolean
                                );
                                CREATE UNIQUE INDEX IF NOT EXISTS autnum_overrides_number
                                        ON autnum_overrides(number);
                                ALTER TABLE autnum_overrides ADD COLUMN IF NOT EXISTS is_drop boolean;
                                ALTER TABLE autnum_overrides DROP COLUMN IF EXISTS source;

                                CREATE TABLE IF NOT EXISTS network_overrides(
                                        network inet NOT NULL,
                                        country text,
                                        is_anonymous_proxy boolean,
                                        is_satellite_provider boolean,
                                        is_anycast boolean
                                );
                                CREATE UNIQUE INDEX IF NOT EXISTS network_overrides_network
                                        ON network_overrides(network);
                                CREATE INDEX IF NOT EXISTS network_overrides_search
                                        ON network_overrides USING GIST(network inet_ops);
                                ALTER TABLE network_overrides ADD COLUMN IF NOT EXISTS is_drop boolean;
                                ALTER TABLE network_overrides DROP COLUMN IF EXISTS source;
                        """)

                return db

        def fetch_countries(self):
                """
                        Returns a list of all countries on the list
                """
                # Fetch all valid country codes to check parsed networks aganist...
                countries = self.db.query("SELECT country_code FROM countries ORDER BY country_code")

                return set((country.country_code for country in countries))

        def handle_write(self, ns):
                """
                        Compiles a database in libloc format out of what is in the database
                """
                # Allocate a writer
                writer = location.Writer(ns.signing_key, ns.backup_signing_key)

                # Set all metadata
                if ns.vendor:
                        writer.vendor = ns.vendor

                if ns.description:
                        writer.description = ns.description

                if ns.license:
                        writer.license = ns.license

                # Add all Autonomous Systems
                log.info("Writing Autonomous Systems...")

                # Select all ASes with a name
                rows = self.db.query("""
                        SELECT
                                autnums.number AS number,
                                COALESCE(
                                        overrides.name,
                                        autnums.name
                                ) AS name
                        FROM
                                autnums
                        LEFT JOIN
                                autnum_overrides overrides ON autnums.number = overrides.number
                        ORDER BY
                                autnums.number
                        """)

                for row in rows:
                        # Skip AS without names
                        if not row.name:
                                continue

                        a = writer.add_as(row.number)
                        a.name = row.name

                # Add all networks
                log.info("Writing networks...")

                # Select all known networks
                rows = self.db.query("""
                        WITH known_networks AS (
                                SELECT network FROM announcements
                                UNION
                                SELECT network FROM networks
                                UNION
                                SELECT network FROM network_feeds
                                UNION
                                SELECT network FROM network_overrides
                                UNION
                                SELECT network FROM geofeed_networks
                        ),

                        ordered_networks AS (
                                SELECT
                                        known_networks.network AS network,
                                        announcements.autnum AS autnum,
                                        networks.country AS country,

                                        -- Must be part of returned values for ORDER BY clause
                                        masklen(announcements.network) AS sort_a,
                                        masklen(networks.network) AS sort_b
                                FROM
                                        known_networks
                                LEFT JOIN
                                        announcements ON known_networks.network <<= announcements.network
                                LEFT JOIN
                                        networks ON known_networks.network <<= networks.network
                                ORDER BY
                                        known_networks.network,
                                        sort_a DESC,
                                        sort_b DESC
                        )

                        -- Return a list of those networks enriched with all
                        -- other information that we store in the database
                        SELECT
                                DISTINCT ON (network)
                                network,
                                autnum,

                                -- Country
                                COALESCE(
                                        (
                                                SELECT
                                                        country
                                                FROM
                                                        network_overrides overrides
                                                WHERE
                                                        networks.network <<= overrides.network
                                                ORDER BY
                                                        masklen(overrides.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        country
                                                FROM
                                                        autnum_overrides overrides
                                                WHERE
                                                        networks.autnum = overrides.number
                                        ),
                                        (
                                                SELECT
                                                        country
                                                FROM
                                                        network_feeds feeds
                                                WHERE
                                                        networks.network <<= feeds.network
                                                ORDER BY
                                                        masklen(feeds.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        country
                                                FROM
                                                        autnum_feeds feeds
                                                WHERE
                                                        networks.autnum = feeds.number
                                                ORDER BY
                                                        source
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        geofeed_networks.country AS country
                                                FROM
                                                        network_geofeeds

                                                -- Join the data from the geofeeds
                                                LEFT JOIN
                                                        geofeeds ON network_geofeeds.url = geofeeds.url
                                                LEFT JOIN
                                                        geofeed_networks ON geofeeds.id = geofeed_networks.geofeed_id

                                                -- Check whether we have a geofeed for this network
                                                WHERE
                                                        networks.network <<= network_geofeeds.network
                                                AND
                                                        networks.network <<= geofeed_networks.network

                                                -- Filter for the best result
                                                ORDER BY
                                                        masklen(geofeed_networks.network) DESC
                                                LIMIT 1
                                        ),
                                        networks.country
                                ) AS country,

                                -- Flags
                                COALESCE(
                                        (
                                                SELECT
                                                        is_anonymous_proxy
                                                FROM
                                                        network_overrides overrides
                                                WHERE
                                                        networks.network <<= overrides.network
                                                ORDER BY
                                                        masklen(overrides.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anonymous_proxy
                                                FROM
                                                        network_feeds feeds
                                                WHERE
                                                        networks.network <<= feeds.network
                                                ORDER BY
                                                        masklen(feeds.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anonymous_proxy
                                                FROM
                                                        autnum_feeds feeds
                                                WHERE
                                                        networks.autnum = feeds.number
                                                ORDER BY
                                                        source
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anonymous_proxy
                                                FROM
                                                        autnum_overrides overrides
                                                WHERE
                                                        networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_anonymous_proxy,
                                COALESCE(
                                        (
                                                SELECT
                                                        is_satellite_provider
                                                FROM
                                                        network_overrides overrides
                                                WHERE
                                                        networks.network <<= overrides.network
                                                ORDER BY
                                                        masklen(overrides.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_satellite_provider
                                                FROM
                                                        network_feeds feeds
                                                WHERE
                                                        networks.network <<= feeds.network
                                                ORDER BY
                                                        masklen(feeds.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_satellite_provider
                                                FROM
                                                        autnum_feeds feeds
                                                WHERE
                                                        networks.autnum = feeds.number
                                                ORDER BY
                                                        source
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_satellite_provider
                                                FROM
                                                        autnum_overrides overrides
                                                WHERE
                                                        networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_satellite_provider,
                                COALESCE(
                                        (
                                                SELECT
                                                        is_anycast
                                                FROM
                                                        network_overrides overrides
                                                WHERE
                                                        networks.network <<= overrides.network
                                                ORDER BY
                                                        masklen(overrides.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anycast
                                                FROM
                                                        network_feeds feeds
                                                WHERE
                                                        networks.network <<= feeds.network
                                                ORDER BY
                                                        masklen(feeds.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anycast
                                                FROM
                                                        autnum_feeds feeds
                                                WHERE
                                                        networks.autnum = feeds.number
                                                ORDER BY
                                                        source
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_anycast
                                                FROM
                                                        autnum_overrides overrides
                                                WHERE
                                                        networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_anycast,
                                COALESCE(
                                        (
                                                SELECT
                                                        is_drop
                                                FROM
                                                        network_overrides overrides
                                                WHERE
                                                        networks.network <<= overrides.network
                                                ORDER BY
                                                        masklen(overrides.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_drop
                                                FROM
                                                        network_feeds feeds
                                                WHERE
                                                        networks.network <<= feeds.network
                                                ORDER BY
                                                        masklen(feeds.network) DESC
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_drop
                                                FROM
                                                        autnum_feeds feeds
                                                WHERE
                                                        networks.autnum = feeds.number
                                                ORDER BY
                                                        source
                                                LIMIT 1
                                        ),
                                        (
                                                SELECT
                                                        is_drop
                                                FROM
                                                        autnum_overrides overrides
                                                WHERE
                                                        networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_drop
                        FROM
                                ordered_networks networks
                """)

                for row in rows:
                        network = writer.add_network(row.network)

                        # Save country
                        if row.country:
                                network.country_code = row.country

                        # Save ASN
                        if row.autnum:
                                network.asn = row.autnum

                        # Set flags
                        if row.is_anonymous_proxy:
                                network.set_flag(location.NETWORK_FLAG_ANONYMOUS_PROXY)

                        if row.is_satellite_provider:
                                network.set_flag(location.NETWORK_FLAG_SATELLITE_PROVIDER)

                        if row.is_anycast:
                                network.set_flag(location.NETWORK_FLAG_ANYCAST)

                        if row.is_drop:
                                network.set_flag(location.NETWORK_FLAG_DROP)

                # Add all countries
                log.info("Writing countries...")
                rows = self.db.query("SELECT * FROM countries ORDER BY country_code")

                for row in rows:
                        c = writer.add_country(row.country_code)
                        c.continent_code = row.continent_code
                        c.name = row.name

                # Write everything to file
                log.info("Writing database to file...")
                for file in ns.file:
                        writer.write(file)

        def handle_update_whois(self, ns):
                downloader = location.importer.Downloader()

                # Did we run successfully?
                error = False

                # Fetch all valid country codes to check parsed networks against
                countries = self.fetch_countries()

                # Check if we have countries
                if not countries:
                        log.error("Please import countries before importing any WHOIS data")
                        return 1

                # Iterate over all potential sources
                for source in sorted(location.importer.SOURCES):
                        with self.db.transaction():
                                # Create some temporary tables to store parsed data
                                self.db.execute("""
                                        CREATE TEMPORARY TABLE _autnums(number integer NOT NULL,
                                                organization text NOT NULL, source text NOT NULL) ON COMMIT DROP;
                                        CREATE UNIQUE INDEX _autnums_number ON _autnums(number);

                                        CREATE TEMPORARY TABLE _organizations(handle text NOT NULL,
                                                name text NOT NULL, source text NOT NULL) ON COMMIT DROP;
                                        CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);

                                        CREATE TEMPORARY TABLE _rirdata(network inet NOT NULL, country text,
                                                original_countries text[] NOT NULL, source text NOT NULL)
                                                ON COMMIT DROP;
                                        CREATE INDEX _rirdata_search ON _rirdata
                                                USING BTREE(family(network), masklen(network));
                                        CREATE UNIQUE INDEX _rirdata_network ON _rirdata(network);
                                """)

                                # Remove all previously imported content
                                self.db.execute("DELETE FROM autnums  WHERE source = %s", source)
                                self.db.execute("DELETE FROM networks WHERE source = %s", source)

                                try:
                                        # Fetch WHOIS sources
                                        for url in location.importer.WHOIS_SOURCES.get(source, []):
                                                for block in downloader.request_blocks(url):
                                                        self._parse_block(block, source, countries)

                                        # Fetch extended sources
                                        for url in location.importer.EXTENDED_SOURCES.get(source, []):
                                                for line in downloader.request_lines(url):
                                                        self._parse_line(line, source, countries)
                                except urllib.error.URLError as e:
                                        log.error("Could not retrieve data from %s: %s" % (source, e))
                                        error = True

                                        # Continue with the next source
                                        continue

                                # Process all parsed networks from every RIR we happen to have access to,
                                # insert the largest network chunks into the networks table immediately...
                                families = self.db.query("""
                                        SELECT DISTINCT
                                                family(network) AS family
                                        FROM
                                                _rirdata
                                        ORDER BY
                                                family(network)
                                        """,
                                )

                                for family in (row.family for row in families):
                                        # Fetch the smallest mask length in our data set
                                        smallest = self.db.get("""
                                                SELECT
                                                        MIN(
                                                                masklen(network)
                                                        ) AS prefix
                                                FROM
                                                        _rirdata
                                                WHERE
                                                        family(network) = %s
                                                """, family,
                                        )

                                        # Copy all networks
                                        self.db.execute("""
                                                INSERT INTO
                                                        networks
                                                (
                                                        network,
                                                        country,
                                                        original_countries,
                                                        source
                                                )
                                                SELECT
                                                        network,
                                                        country,
                                                        original_countries,
                                                        source
                                                FROM
                                                        _rirdata
                                                WHERE
                                                        masklen(network) = %s
                                                AND
                                                        family(network) = %s
                                                ON CONFLICT DO
                                                        NOTHING""",
                                                smallest.prefix,
                                                family,
                                        )

                                        # ... determine any other prefixes for this network family, ...
                                        prefixes = self.db.query("""
                                                SELECT
                                                        DISTINCT masklen(network) AS prefix
                                                FROM
                                                        _rirdata
                                                WHERE
                                                        family(network) = %s
                                                ORDER BY
                                                        masklen(network) ASC
                                                OFFSET 1
                                                """, family,
                                        )

                                        # ... and insert networks with this prefix in case they provide additional
                                        # information (i. e. subnet of a larger chunk with a different country)
                                        for prefix in (row.prefix for row in prefixes):
                                                self.db.execute("""
                                                        WITH candidates AS (
                                                                SELECT
                                                                        _rirdata.network,
                                                                        _rirdata.country,
                                                                        _rirdata.original_countries,
                                                                        _rirdata.source
                                                                FROM
                                                                        _rirdata
                                                                WHERE
                                                                        family(_rirdata.network) = %s
                                                                AND
                                                                        masklen(_rirdata.network) = %s
                                                        ),
                                                        filtered AS (
                                                                SELECT
                                                                        DISTINCT ON (c.network)
                                                                        c.network,
                                                                        c.country,
                                                                        c.original_countries,
                                                                        c.source,
                                                                        masklen(networks.network),
                                                                        networks.country AS parent_country
                                                                FROM
                                                                        candidates c
                                                                LEFT JOIN
                                                                        networks
                                                                ON
                                                                        c.network << networks.network
                                                                ORDER BY
                                                                        c.network,
                                                                        masklen(networks.network) DESC NULLS LAST
                                                        )
                                                        INSERT INTO
                                                                networks(network, country, original_countries, source)
                                                        SELECT
                                                                network,
                                                                country,
                                                                original_countries,
                                                                source
                                                        FROM
                                                                filtered
                                                        WHERE
                                                                parent_country IS NULL
                                                        OR
                                                                country <> parent_country
                                                        ON CONFLICT DO NOTHING
                                                        """, family, prefix,
                                                )

                                self.db.execute("""
                                        INSERT INTO
                                                autnums
                                        (
                                                number,
                                                name,
                                                source
                                        )
                                        SELECT
                                                _autnums.number,
                                                _organizations.name,
                                                _organizations.source
                                        FROM
                                                _autnums
                                        JOIN
                                                _organizations ON _autnums.organization = _organizations.handle
                                        ON CONFLICT
                                        (
                                                number
                                        )
                                        DO UPDATE
                                                SET name = excluded.name
                                        """,
                                )

                # Download and import (technical) AS names from ARIN
                with self.db.transaction():
                        self._import_as_names_from_arin(downloader)

                # Return a non-zero exit code for errors
                return 1 if error else 0

        def _check_parsed_network(self, network):
                """
                        Assistive function to detect and subsequently sort out parsed
                        networks from RIR data (both Whois and so-called "extended sources"),
                        which are or have...

                        (a) not globally routable (RFC 1918 space, et al.)
                        (b) covering a too large chunk of the IP address space (prefix length
                                is < 7 for IPv4 networks, and < 10 for IPv6)
                        (c) "0.0.0.0" or "::" as a network address
                        (d) are too small for being publicly announced (we have decided not to
                                process them at the moment, as they significantly enlarge our
                                database without providing very helpful additional information)

                        This unfortunately is necessary due to brain-dead clutter across
                        various RIR databases, causing mismatches and eventually disruptions.

                        We will return False in case a network is not suitable for adding
                        it to our database, and True otherwise.
                """

                if not network or not (isinstance(network, ipaddress.IPv4Network) or isinstance(network, ipaddress.IPv6Network)):
                        return False

                if not network.is_global:
                        log.debug("Skipping non-globally routable network: %s" % network)
                        return False

                if network.version == 4:
                        if network.prefixlen < 7:
                                log.debug("Skipping too big IP chunk: %s" % network)
                                return False

                        if network.prefixlen > 24:
                                log.debug("Skipping network too small to be publicly announced: %s" % network)
                                return False

                        if str(network.network_address) == "0.0.0.0":
                                log.debug("Skipping network based on 0.0.0.0: %s" % network)
                                return False

                elif network.version == 6:
                        if network.prefixlen < 10:
                                log.debug("Skipping too big IP chunk: %s" % network)
                                return False

                        if network.prefixlen > 48:
                                log.debug("Skipping network too small to be publicly announced: %s" % network)
                                return False

                        if str(network.network_address) == "::":
                                log.debug("Skipping network based on '::': %s" % network)
                                return False

                else:
                        # This should not happen...
                        log.warning("Skipping network of unknown family, this should not happen: %s" % network)
                        return False

                # In case we have made it here, the network is considered to
                # be suitable for libloc consumption...
                return True

        def _check_parsed_asn(self, asn):
                """
                        Assistive function to filter Autonomous System Numbers not being suitable
                        for adding to our database. Returns False in such cases, and True otherwise.
                """

                for start, end in VALID_ASN_RANGES:
                        if start <= asn and end >= asn:
                                return True

                log.info("Supplied ASN %s out of publicly routable ASN ranges" % asn)
                return False

        def _parse_block(self, block, source_key, countries):
                # Get first line to find out what type of block this is
                line = block[0]

                # aut-num
                if line.startswith("aut-num:"):
                        return self._parse_autnum_block(block, source_key)

                # inetnum
                if line.startswith("inet6num:") or line.startswith("inetnum:"):
                        return self._parse_inetnum_block(block, source_key, countries)

                # organisation
                elif line.startswith("organisation:"):
                        return self._parse_org_block(block, source_key)

        def _parse_autnum_block(self, block, source_key):
                autnum = {}
                for line in block:
                        # Split line
                        key, val = split_line(line)

                        if key == "aut-num":
                                m = re.match(r"^(AS|as)(\d+)", val)
                                if m:
                                        autnum["asn"] = m.group(2)

                        elif key == "org":
                                autnum[key] = val.upper()

                        elif key == "descr":
                                # Save the first description line as well...
                                if not key in autnum:
                                        autnum[key] = val

                # Skip empty objects
                if not autnum or not "asn" in autnum:
                        return

                # Insert a dummy organisation handle into our temporary organisations
                # table in case the AS does not have an organisation handle set, but
                # has a description (a quirk often observed in APNIC area), so we can
                # later display at least some string for this AS.
                if not "org" in autnum:
                        if "descr" in autnum:
                                autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")

                                self.db.execute("INSERT INTO _organizations(handle, name, source) \
                                        VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
                                        autnum.get("org"), autnum.get("descr"), source_key,
                                )
                        else:
                                log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
                                                autnum.get("asn"))
                                return

                # Insert into database
                self.db.execute("INSERT INTO _autnums(number, organization, source) \
                        VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
                                organization = excluded.organization",
                        autnum.get("asn"), autnum.get("org"), source_key,
                )

        def _parse_inetnum_block(self, block, source_key, countries):
                log.debug("Parsing inetnum block:")

                inetnum = {}
                for line in block:
                        log.debug(line)

                        # Split line
                        key, val = split_line(line)

                        # Filter any inetnum records which are only referring to IP space
                        # not managed by that specific RIR...
                        if key == "netname":
                                if re.match(r"^(ERX-NETBLOCK|(AFRINIC|ARIN|LACNIC|RIPE)-CIDR-BLOCK|IANA-NETBLOCK-\d{1,3}|NON-RIPE-NCC-MANAGED-ADDRESS-BLOCK|STUB-[\d-]{3,}SLASH\d{1,2})", val.strip()):
                                        log.debug("Skipping record indicating historic/orphaned data: %s" % val.strip())
                                        return

                        if key == "inetnum":
                                start_address, delim, end_address = val.partition("-")

                                # Strip any excess space
                                start_address, end_address = start_address.rstrip(), end_address.strip()

                                # Handle "inetnum" formatting in LACNIC DB (e.g. "24.152.8/22" instead of "24.152.8.0/22")
                                if start_address and not (delim or end_address):
                                        try:
                                                start_address = ipaddress.ip_network(start_address, strict=False)
                                        except ValueError:
                                                start_address = start_address.split("/")
                                                ldigits = start_address[0].count(".")

                                                # How many octets do we need to add?
                                                # (LACNIC does not seem to have a /8 or greater assigned, so the following should suffice.)
                                                if ldigits == 1:
                                                        start_address = start_address[0] + ".0.0/" + start_address[1]
                                                elif ldigits == 2:
                                                        start_address = start_address[0] + ".0/" + start_address[1]
                                                else:
                                                        log.warning("Could not recover IPv4 address from line in LACNIC DB format: %s" % line)
                                                        return

                                                try:
                                                        start_address = ipaddress.ip_network(start_address, strict=False)
                                                except ValueError:
                                                        log.warning("Could not parse line in LACNIC DB format: %s" % line)
                                                        return

                                        # Enumerate first and last IP address of this network
                                        end_address = start_address[-1]
                                        start_address = start_address[0]

                                else:
                                        # Convert to IP address
                                        try:
                                                start_address = ipaddress.ip_address(start_address)
                                                end_address   = ipaddress.ip_address(end_address)
                                        except ValueError:
                                                log.warning("Could not parse line: %s" % line)
                                                return

                                inetnum["inetnum"] = list(ipaddress.summarize_address_range(start_address, end_address))

                        elif key == "inet6num":
                                inetnum[key] = [ipaddress.ip_network(val, strict=False)]

                        elif key == "country":
                                cc = val.upper()

                                # Ignore certain country codes
                                if cc in IGNORED_COUNTRIES:
                                        log.debug("Ignoring country code '%s'" % cc)
                                        continue

                                # Translate country codes
                                try:
                                        cc = TRANSLATED_COUNTRIES[cc]
                                except KeyError:
                                        pass

                                # Do we know this country?
                                if not cc in countries:
                                        log.warning("Skipping invalid country code '%s'" % cc)
                                        continue

                                try:
                                        inetnum[key].append(cc)
                                except KeyError:
                                        inetnum[key] = [cc]

                        # Parse the geofeed attribute
                        elif key == "geofeed":
                                inetnum["geofeed"] = val

                        # Parse geofeed when used as a remark
                        elif key == "remarks":
                                m = re.match(r"^(?:Geofeed)\s+(https://.*)", val)
                                if m:
                                        inetnum["geofeed"] = m.group(1)

                # Skip empty objects
                if not inetnum:
                        return

                # Iterate through all networks enumerated from above, check them for plausibility and insert
                # them into the database, if _check_parsed_network() succeeded
                for single_network in inetnum.get("inet6num") or inetnum.get("inetnum"):
                        if not self._check_parsed_network(single_network):
                                continue

                        # Fetch the countries or use a list with an empty country
                        countries = inetnum.get("country", [None])

                        # Insert the network into the database but only use the first country code
                        for cc in countries:
                                self.db.execute("""
                                        INSERT INTO
                                                _rirdata
                                        (
                                                network,
                                                country,
                                                original_countries,
                                                source
                                        )
                                        VALUES
                                        (
                                                %s, %s, %s, %s
                                        )
                                        ON CONFLICT (network)
                                                DO UPDATE SET country = excluded.country
                                        """, "%s" % single_network, cc, [cc for cc in countries if cc], source_key,
                                )

                                # If there are more than one country, we will only use the first one
                                break

                        # Update any geofeed information
                        geofeed = inetnum.get("geofeed", None)
                        if geofeed:
                                self._parse_geofeed(geofeed, single_network)

                        # Delete any previous geofeeds
                        else:
                                self.db.execute(
                                        "DELETE FROM network_geofeeds WHERE network = %s", "%s" % single_network,
                                )

        def _parse_geofeed(self, url, single_network):
                # Parse the URL
                url = urllib.parse.urlparse(url)

                # Make sure that this is a HTTPS URL
                if not url.scheme == "https":
                        log.debug("Geofeed URL is not using HTTPS: %s" % geofeed)
                        return

                # Put the URL back together normalized
                url = url.geturl()

                # Store/update any geofeeds
                self.db.execute("""
                        INSERT INTO
                                network_geofeeds(
                                        network,
                                        url
                                )
                        VALUES(
                                %s, %s
                        )
                        ON CONFLICT (network) DO
                                UPDATE SET url = excluded.url""",
                        "%s" % single_network, url,
                )

        def _parse_org_block(self, block, source_key):
                org = {}
                for line in block:
                        # Split line
                        key, val = split_line(line)

                        if key == "organisation":
                                org[key] = val.upper()
                        elif key == "org-name":
                                org[key] = val

                # Skip empty objects
                if not org:
                        return

                self.db.execute("INSERT INTO _organizations(handle, name, source) \
                        VALUES(%s, %s, %s) ON CONFLICT (handle) DO \
                        UPDATE SET name = excluded.name",
                        org.get("organisation"), org.get("org-name"), source_key,
                )

        def _parse_line(self, line, source_key, validcountries=None):
                # Skip version line
                if line.startswith("2"):
                        return

                # Skip comments
                if line.startswith("#"):
                        return

                try:
                        registry, country_code, type, line = line.split("|", 3)
                except:
                        log.warning("Could not parse line: %s" % line)
                        return

                # Skip any unknown protocols
                if not type in ("ipv6", "ipv4"):
                        log.warning("Unknown IP protocol '%s'" % type)
                        return

                # Skip any lines that are for stats only or do not have a country
                # code at all (avoids log spam below)
                if not country_code or country_code == '*':
                        return

                # Skip objects with unknown country codes
                if validcountries and country_code not in validcountries:
                        log.warning("Skipping line with bogus country '%s': %s" % \
                                (country_code, line))
                        return

                try:
                        address, prefix, date, status, organization = line.split("|")
                except ValueError:
                        organization = None

                        # Try parsing the line without organization
                        try:
                                address, prefix, date, status = line.split("|")
                        except ValueError:
                                log.warning("Unhandled line format: %s" % line)
                                return

                # Skip anything that isn't properly assigned
                if not status in ("assigned", "allocated"):
                        return

                # Cast prefix into an integer
                try:
                        prefix = int(prefix)
                except:
                        log.warning("Invalid prefix: %s" % prefix)
                        return

                # Fix prefix length for IPv4
                if type == "ipv4":
                        prefix = 32 - int(math.log(prefix, 2))

                # Try to parse the address
                try:
                        network = ipaddress.ip_network("%s/%s" % (address, prefix), strict=False)
                except ValueError:
                        log.warning("Invalid IP address: %s" % address)
                        return

                if not self._check_parsed_network(network):
                        return

                self.db.execute("""
                        INSERT INTO
                                networks
                        (
                                network,
                                country,
                                original_countries,
                                source
                        )
                        VALUES
                        (
                                %s, %s, %s, %s
                        )
                        ON CONFLICT (network)
                                DO UPDATE SET country = excluded.country
                        """, "%s" % network, country_code, [country], source_key,
                )

        def _import_as_names_from_arin(self, downloader):
                # Delete all previously imported content
                self.db.execute("DELETE FROM autnums  WHERE source = %s", "ARIN")

                # Try to retrieve the feed from ftp.arin.net
                feed = downloader.request_lines("https://ftp.arin.net/pub/resource_registry_service/asns.csv")

                # Walk through the file
                for line in csv.DictReader(feed, dialect="arin"):
                        log.debug("Processing object: %s" % line)

                        # Fetch status
                        status = line.get("Status")

                        # We are only interested in anything managed by ARIN
                        if not status == "Full Registry Services":
                                continue

                        # Fetch organization name
                        name = line.get("Org Name")

                        # Extract ASNs
                        first_asn = line.get("Start AS Number")
                        last_asn  = line.get("End AS Number")

                        # Cast to a number
                        try:
                                first_asn = int(first_asn)
                        except TypeError as e:
                                log.warning("Could not parse ASN '%s'" % first_asn)
                                continue

                        try:
                                last_asn = int(last_asn)
                        except TypeError as e:
                                log.warning("Could not parse ASN '%s'" % last_asn)
                                continue

                        # Check if the range is valid
                        if last_asn < first_asn:
                                log.warning("Invalid ASN range %s-%s" % (first_asn, last_asn))

                        # Insert everything into the database
                        for asn in range(first_asn, last_asn + 1):
                                if not self._check_parsed_asn(asn):
                                        log.warning("Skipping invalid ASN %s" % asn)
                                        continue

                                self.db.execute("""
                                        INSERT INTO
                                                autnums
                                        (
                                                number,
                                                name,
                                                source
                                        )
                                        VALUES
                                        (
                                                %s, %s, %s
                                        )
                                        ON CONFLICT
                                        (
                                                number
                                        )
                                        DO NOTHING
                                        """, asn, name, "ARIN",
                                )

        def handle_update_announcements(self, ns):
                server = ns.server[0]

                with self.db.transaction():
                        if server.startswith("/"):
                                self._handle_update_announcements_from_bird(server)

                        # Purge anything we never want here
                        self.db.execute("""
                                -- Delete default routes
                                DELETE FROM announcements WHERE network = '::/0' OR network = '0.0.0.0/0';

                                -- Delete anything that is not global unicast address space
                                DELETE FROM announcements WHERE family(network) = 6 AND NOT network <<= '2000::/3';

                                -- DELETE "current network" address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '0.0.0.0/8';

                                -- DELETE local loopback address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '127.0.0.0/8';

                                -- DELETE RFC 1918 address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '10.0.0.0/8';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '172.16.0.0/12';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '192.168.0.0/16';

                                -- DELETE test, benchmark and documentation address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '192.0.0.0/24';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '192.0.2.0/24';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '198.18.0.0/15';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '198.51.100.0/24';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '203.0.113.0/24';

                                -- DELETE CGNAT address space (RFC 6598)
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '100.64.0.0/10';

                                -- DELETE link local address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '169.254.0.0/16';

                                -- DELETE IPv6 to IPv4 (6to4) address space (RFC 3068)
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '192.88.99.0/24';
                                DELETE FROM announcements WHERE family(network) = 6 AND network <<= '2002::/16';

                                -- DELETE multicast and reserved address space
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '224.0.0.0/4';
                                DELETE FROM announcements WHERE family(network) = 4 AND network <<= '240.0.0.0/4';

                                -- Delete networks that are too small to be in the global routing table
                                DELETE FROM announcements WHERE family(network) = 6 AND masklen(network) > 48;
                                DELETE FROM announcements WHERE family(network) = 4 AND masklen(network) > 24;

                                -- Delete any non-public or reserved ASNs
                                DELETE FROM announcements WHERE NOT (
                                        (autnum >= 1 AND autnum <= 23455)
                                        OR
                                        (autnum >= 23457 AND autnum <= 64495)
                                        OR
                                        (autnum >= 131072 AND autnum <= 4199999999)
                                );

                                -- Delete everything that we have not seen for 14 days
                                DELETE FROM announcements WHERE last_seen_at <= CURRENT_TIMESTAMP - INTERVAL '14 days';
                        """)

        def _handle_update_announcements_from_bird(self, server):
                # Pre-compile the regular expression for faster searching
                route = re.compile(b"^\s(.+?)\s+.+?\[(?:AS(.*?))?.\]$")

                log.info("Requesting routing table from Bird (%s)" % server)

                aggregated_networks = []

                # Send command to list all routes
                for line in self._bird_cmd(server, "show route"):
                        m = route.match(line)
                        if not m:
                                # Skip empty lines
                                if not line:
                                        pass

                                # Ignore any header lines with the name of the routing table
                                elif line.startswith(b"Table"):
                                        pass

                                # Log anything else
                                else:
                                        log.debug("Could not parse line: %s" % line.decode())

                                continue

                        # Fetch the extracted network and ASN
                        network, autnum = m.groups()

                        # Decode into strings
                        if network:
                                network = network.decode()
                        if autnum:
                                autnum = autnum.decode()

                        # Collect all aggregated networks
                        if not autnum:
                                log.debug("%s is an aggregated network" % network)
                                aggregated_networks.append(network)
                                continue

                        # Insert it into the database
                        self.db.execute("INSERT INTO announcements(network, autnum) \
                                VALUES(%s, %s) ON CONFLICT (network) DO \
                                UPDATE SET autnum = excluded.autnum, last_seen_at = CURRENT_TIMESTAMP",
                                network, autnum,
                        )

                # Process any aggregated networks
                for network in aggregated_networks:
                        log.debug("Processing aggregated network %s" % network)

                        # Run "show route all" for each network
                        for line in self._bird_cmd(server, "show route %s all" % network):
                                # Try finding the path
                                m = re.match(b"\s+BGP\.as_path:.* (\d+) {\d+}$", line)
                                if m:
                                        # Select the last AS number in the path
                                        autnum = m.group(1).decode()

                                        # Insert it into the database
                                        self.db.execute("INSERT INTO announcements(network, autnum) \
                                                VALUES(%s, %s) ON CONFLICT (network) DO \
                                                UPDATE SET autnum = excluded.autnum, last_seen_at = CURRENT_TIMESTAMP",
                                                network, autnum,
                                        )

                                        # We don't need to process any more
                                        break

        def _bird_cmd(self, socket_path, command):
                # Connect to the socket
                s = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
                s.connect(socket_path)

                # Allocate some buffer
                buffer = b""

                log.debug("Sending Bird command: %s" % command)

                # Send the command
                s.send(b"%s\n" % command.encode())

                while True:
                        # Fill up the buffer
                        buffer += s.recv(4096)

                        while True:
                                # Search for the next newline
                                pos = buffer.find(b"\n")

                                # If we cannot find one, we go back and read more data
                                if pos <= 0:
                                        break

                                # Cut after the newline character
                                pos += 1

                                # Split the line we want and keep the rest in buffer
                                line, buffer = buffer[:pos], buffer[pos:]

                                # Try parsing any status lines
                                if len(line) > 4 and line[:4].isdigit() and line[4] in (32, 45):
                                        code, delim, line = int(line[:4]), line[4], line[5:]

                                        log.debug("Received response code %s from bird" % code)

                                        # End of output
                                        if code == 0:
                                                return

                                        # Ignore hello line
                                        elif code == 1:
                                                continue

                                # Otherwise return the line
                                yield line

        def handle_update_geofeeds(self, ns):
                # Sync geofeeds
                with self.db.transaction():
                        # Delete all geofeeds which are no longer linked
                        self.db.execute("""
                                DELETE FROM
                                        geofeeds
                                WHERE
                                        NOT EXISTS (
                                                SELECT
                                                        1
                                                FROM
                                                        network_geofeeds
                                                WHERE
                                                        geofeeds.url = network_geofeeds.url
                                        )""",
                        )

                        # Copy all geofeeds
                        self.db.execute("""
                                INSERT INTO
                                        geofeeds(
                                                url
                                        )
                                SELECT
                                        url
                                FROM
                                        network_geofeeds
                                ON CONFLICT (url)
                                        DO NOTHING
                                """,
                        )

                # Fetch all Geofeeds that require an update
                geofeeds = self.db.query("""
                        SELECT
                                id,
                                url
                        FROM
                                geofeeds
                        WHERE
                                updated_at IS NULL
                        OR
                                updated_at <= CURRENT_TIMESTAMP - INTERVAL '1 week'
                        ORDER BY
                                id
                """)

                # Create a downloader
                downloader = location.importer.Downloader()

                # Pass the downloader to the fetch_geofeed function
                fetch_geofeed = functools.partial(self._fetch_geofeed, downloader)

                with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
                        results = executor.map(fetch_geofeed, geofeeds)

                        # Fetch all results to raise any exceptions
                        for result in results:
                                pass

                # Delete data from any feeds that did not update in the last two weeks
                with self.db.transaction():
                        self.db.execute("""
                                DELETE FROM
                                        geofeed_networks
                                WHERE
                                        geofeed_networks.geofeed_id IN (
                                                SELECT
                                                        geofeeds.id
                                                FROM
                                                        geofeeds
                                                WHERE
                                                        updated_at IS NULL
                                                OR
                                                        updated_at <= CURRENT_TIMESTAMP - INTERVAL '2 weeks'
                                        )
                        """)

        def _fetch_geofeed(self, downloader, geofeed):
                log.debug("Fetching Geofeed %s" % geofeed.url)

                with self.db.transaction():
                        # Open the URL
                        try:
                                # Send the request
                                f = downloader.retrieve(geofeed.url, headers={
                                        "User-Agent" : "location/%s" % location.__version__,

                                        # We expect some plain text file in CSV format
                                        "Accept"     : "text/csv, text/plain",
                                })

                                # Remove any previous data
                                self.db.execute("DELETE FROM geofeed_networks \
                                        WHERE geofeed_id = %s", geofeed.id)

                                lineno = 0

                                # Read the output line by line
                                for line in f:
                                        lineno += 1

                                        try:
                                                line = line.decode()

                                        # Ignore any lines we cannot decode
                                        except UnicodeDecodeError:
                                                log.debug("Could not decode line %s in %s" \
                                                        % (lineno, geofeed.url))
                                                continue

                                        # Strip any newline
                                        line = line.rstrip()

                                        # Skip empty lines
                                        if not line:
                                                continue

                                        # Try to parse the line
                                        try:
                                                fields = line.split(",", 5)
                                        except ValueError:
                                                log.debug("Could not parse line: %s" % line)
                                                continue

                                        # Check if we have enough fields
                                        if len(fields) < 4:
                                                log.debug("Not enough fields in line: %s" % line)
                                                continue

                                        # Fetch all fields
                                        network, country, region, city, = fields[:4]

                                        # Try to parse the network
                                        try:
                                                network = ipaddress.ip_network(network, strict=False)
                                        except ValueError:
                                                log.debug("Could not parse network: %s" % network)
                                                continue

                                        # Strip any excess whitespace from country codes
                                        country = country.strip()

                                        # Make the country code uppercase
                                        country = country.upper()

                                        # Check the country code
                                        if not country:
                                                log.debug("Empty country code in Geofeed %s line %s" \
                                                        % (geofeed.url, lineno))
                                                continue

                                        elif not location.country_code_is_valid(country):
                                                log.debug("Invalid country code in Geofeed %s:%s: %s" \
                                                        % (geofeed.url, lineno, country))
                                                continue

                                        # Write this into the database
                                        self.db.execute("""
                                                INSERT INTO
                                                        geofeed_networks (
                                                                geofeed_id,
                                                                network,
                                                                country,
                                                                region,
                                                                city
                                                        )
                                                VALUES (%s, %s, %s, %s, %s)""",
                                                geofeed.id,
                                                "%s" % network,
                                                country,
                                                region,
                                                city,
                                        )

                        # Catch any HTTP errors
                        except urllib.request.HTTPError as e:
                                self.db.execute("UPDATE geofeeds SET status = %s, error = %s \
                                        WHERE id = %s", e.code, "%s" % e, geofeed.id)

                                # Remove any previous data when the feed has been deleted
                                if e.code == 404:
                                        self.db.execute("DELETE FROM geofeed_networks \
                                                WHERE geofeed_id = %s", geofeed.id)

                        # Catch any other errors and connection timeouts
                        except (http.client.InvalidURL, urllib.request.URLError, TimeoutError) as e:
                                log.debug("Could not fetch URL %s: %s" % (geofeed.url, e))

                                self.db.execute("UPDATE geofeeds SET status = %s, error = %s \
                                        WHERE id = %s", 599, "%s" % e, geofeed.id)

                        # Mark the geofeed as updated
                        else:
                                self.db.execute("""
                                        UPDATE
                                                geofeeds
                                        SET
                                                updated_at = CURRENT_TIMESTAMP,
                                                status = NULL,
                                                error = NULL
                                        WHERE
                                                id = %s""",
                                        geofeed.id,
                                )

        def handle_update_overrides(self, ns):
                with self.db.transaction():
                        # Drop any previous content
                        self.db.execute("TRUNCATE TABLE autnum_overrides")
                        self.db.execute("TRUNCATE TABLE network_overrides")

                        for file in ns.files:
                                log.info("Reading %s..." % file)

                                with open(file, "rb") as f:
                                        for type, block in location.importer.read_blocks(f):
                                                if type == "net":
                                                        network = block.get("net")
                                                        # Try to parse and normalise the network
                                                        try:
                                                                network = ipaddress.ip_network(network, strict=False)
                                                        except ValueError as e:
                                                                log.warning("Invalid IP network: %s: %s" % (network, e))
                                                                continue

                                                        # Prevent that we overwrite all networks
                                                        if network.prefixlen == 0:
                                                                log.warning("Skipping %s: You cannot overwrite default" % network)
                                                                continue

                                                        self.db.execute("""
                                                                INSERT INTO
                                                                        network_overrides
                                                                (
                                                                        network,
                                                                        country,
                                                                        is_anonymous_proxy,
                                                                        is_satellite_provider,
                                                                        is_anycast,
                                                                        is_drop
                                                                )
                                                                VALUES
                                                                (
                                                                        %s, %s, %s, %s, %s, %s
                                                                )
                                                                ON CONFLICT (network) DO NOTHING
                                                                """,
                                                                "%s" % network,
                                                                block.get("country"),
                                                                self._parse_bool(block, "is-anonymous-proxy"),
                                                                self._parse_bool(block, "is-satellite-provider"),
                                                                self._parse_bool(block, "is-anycast"),
                                                                self._parse_bool(block, "drop"),
                                                        )

                                                elif type == "aut-num":
                                                        autnum = block.get("aut-num")

                                                        # Check if AS number begins with "AS"
                                                        if not autnum.startswith("AS"):
                                                                log.warning("Invalid AS number: %s" % autnum)
                                                                continue

                                                        # Strip "AS"
                                                        autnum = autnum[2:]

                                                        self.db.execute("""
                                                                INSERT INTO
                                                                        autnum_overrides
                                                                (
                                                                        number,
                                                                        name,
                                                                        country,
                                                                        is_anonymous_proxy,
                                                                        is_satellite_provider,
                                                                        is_anycast,
                                                                        is_drop
                                                                )
                                                                VALUES
                                                                (
                                                                        %s, %s, %s, %s, %s, %s, %s
                                                                )
                                                                ON CONFLICT (number) DO NOTHING
                                                                """,
                                                                autnum,
                                                                block.get("name"),
                                                                block.get("country"),
                                                                self._parse_bool(block, "is-anonymous-proxy"),
                                                                self._parse_bool(block, "is-satellite-provider"),
                                                                self._parse_bool(block, "is-anycast"),
                                                                self._parse_bool(block, "drop"),
                                                        )

                                                else:
                                                        log.warning("Unsupported type: %s" % type)

        def handle_update_feeds(self, ns):
                """
                        Update any third-party feeds
                """
                success = True

                # Create a downloader
                downloader = location.importer.Downloader()

                feeds = (
                        # AWS IP Ranges
                        ("AWS-IP-RANGES", self._import_aws_ip_ranges, "https://ip-ranges.amazonaws.com/ip-ranges.json"),

                        # Spamhaus DROP
                        ("SPAMHAUS-DROP",   self._import_spamhaus_drop, "https://www.spamhaus.org/drop/drop.txt"),
                        ("SPAMHAUS-EDROP",  self._import_spamhaus_drop, "https://www.spamhaus.org/drop/edrop.txt"),
                        ("SPAMHAUS-DROPV6", self._import_spamhaus_drop, "https://www.spamhaus.org/drop/dropv6.txt"),

                        # Spamhaus ASNDROP
                        ("SPAMHAUS-ASNDROP", self._import_spamhaus_asndrop, "https://www.spamhaus.org/drop/asndrop.json"),
                )

                # Drop any data from feeds that we don't support (any more)
                with self.db.transaction():
                        # Fetch the names of all feeds we support
                        sources = [name for name, *rest in feeds]

                        self.db.execute("DELETE FROM autnum_feeds  WHERE NOT source = ANY(%s)", sources)
                        self.db.execute("DELETE FROM network_feeds WHERE NOT source = ANY(%s)", sources)

                # Walk through all feeds
                for name, callback, url, *args in feeds:
                        # Skip any feeds that were not requested on the command line
                        if ns.feeds and not name in ns.feeds:
                                continue

                        try:
                                self._process_feed(downloader, name, callback, url, *args)

                        # Log an error but continue if an exception occurs
                        except Exception as e:
                                log.error("Error processing feed '%s': %s" % (name, e))
                                success = False

                # Return status
                return 0 if success else 1

        def _process_feed(self, downloader, name, callback, url, *args):
                """
                        Processes one feed
                """
                # Open the URL
                f = downloader.retrieve(url)

                with self.db.transaction():
                        # Drop any previous content
                        self.db.execute("DELETE FROM autnum_feeds  WHERE source = %s", name)
                        self.db.execute("DELETE FROM network_feeds WHERE source = %s", name)

                        # Call the callback to process the feed
                        return callback(name, f, *args)

        def _import_aws_ip_ranges(self, name, f):
                # Parse the feed
                feed = json.load(f)

                # Set up a dictionary for mapping a region name to a country. Unfortunately,
                # there seems to be no machine-readable version available of this other than
                # https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html
                # (worse, it seems to be incomplete :-/ ); https://www.cloudping.cloud/endpoints
                # was helpful here as well.
                aws_region_country_map = {
                        # Africa
                        "af-south-1"     : "ZA",

                        # Asia
                        "il-central-1"   : "IL", # Tel Aviv

                        # Asia/Pacific
                        "ap-northeast-1" : "JP",
                        "ap-northeast-2" : "KR",
                        "ap-northeast-3" : "JP",
                        "ap-east-1"      : "HK",
                        "ap-south-1"     : "IN",
                        "ap-south-2"     : "IN",
                        "ap-southeast-1" : "SG",
                        "ap-southeast-2" : "AU",
                        "ap-southeast-3" : "MY",
                        "ap-southeast-4" : "AU",
                        "ap-southeast-5" : "NZ", # Auckland, NZ
                        "ap-southeast-6" : "AP", # XXX: Precise location not documented anywhere

                        # Canada
                        "ca-central-1"   : "CA",
                        "ca-west-1"      : "CA",

                        # Europe
                        "eu-central-1"   : "DE",
                        "eu-central-2"   : "CH",
                        "eu-north-1"     : "SE",
                        "eu-west-1"      : "IE",
                        "eu-west-2"      : "GB",
                        "eu-west-3"      : "FR",
                        "eu-south-1"     : "IT",
                        "eu-south-2"     : "ES",

                        # Middle East
                        "me-central-1"   : "AE",
                        "me-south-1"     : "BH",

                        # South America
                        "sa-east-1"      : "BR",

                        # Undocumented, likely located in Berlin rather than Frankfurt
                        "eusc-de-east-1" : "DE",
                }

                # Collect a list of all networks
                prefixes = feed.get("ipv6_prefixes", []) + feed.get("prefixes", [])

                for prefix in prefixes:
                        # Fetch network
                        network = prefix.get("ipv6_prefix") or prefix.get("ip_prefix")

                        # Parse the network
                        try:
                                network = ipaddress.ip_network(network)
                        except ValuleError as e:
                                log.warning("%s: Unable to parse prefix %s" % (name, network))
                                continue

                        # Sanitize parsed networks...
                        if not self._check_parsed_network(network):
                                continue

                        # Fetch the region
                        region = prefix.get("region")

                        # Set some defaults
                        cc = None
                        is_anycast = False

                        # Fetch the CC from the dictionary
                        try:
                                cc = aws_region_country_map[region]

                        # If we couldn't find anything, let's try something else...
                        except KeyError as e:
                                # Find anycast networks
                                if region == "GLOBAL":
                                        is_anycast = True

                                # Everything that starts with us- is probably in the United States
                                elif region.startswith("us-"):
                                        cc = "US"

                                # Everything that starts with cn- is probably China
                                elif region.startswith("cn-"):
                                        cc = "CN"

                                # Log a warning for anything else
                                else:
                                        log.warning("%s: Could not determine country code for AWS region %s" \
                                                % (name, region))
                                        continue

                        # Write to database
                        self.db.execute("""
                                INSERT INTO
                                        network_feeds
                                (
                                        network,
                                        source,
                                        country,
                                        is_anycast
                                )
                                VALUES
                                (
                                        %s, %s, %s, %s
                                )
                                ON CONFLICT (network, source) DO NOTHING
                                """, "%s" % network, name, cc, is_anycast,
                        )

        def _import_spamhaus_drop(self, name, f):
                """
                        Import Spamhaus DROP IP feeds
                """
                # Count all lines
                lines = 0

                # Walk through all lines
                for line in f:
                        # Decode line
                        line = line.decode("utf-8")

                        # Strip off any comments
                        line, _, comment = line.partition(";")

                        # Ignore empty lines
                        if not line:
                                continue

                        # Strip any excess whitespace
                        line = line.strip()

                        # Increment line counter
                        lines += 1

                        # Parse the network
                        try:
                                network = ipaddress.ip_network(line)
                        except ValueError as e:
                                log.warning("%s: Could not parse network: %s - %s" % (name, line, e))
                                continue

                        # Check network
                        if not self._check_parsed_network(network):
                                log.warning("%s: Skipping bogus network: %s" % (name, network))
                                continue

                        # Insert into the database
                        self.db.execute("""
                                INSERT INTO
                                        network_feeds
                                (
                                        network,
                                        source,
                                        is_drop
                                )
                                VALUES
                                (
                                        %s, %s, %s
                                )""", "%s" % network, name, True,
                        )

                # Raise an exception if we could not import anything
                if not lines:
                        raise RuntimeError("Received bogus feed %s with no data" % name)

        def _import_spamhaus_asndrop(self, name, f):
                """
                        Import Spamhaus ASNDROP feed
                """
                for line in f:
                        # Decode the line
                        line = line.decode("utf-8")

                        # Parse JSON
                        try:
                                line = json.loads(line)
                        except json.JSONDecodeError as e:
                                log.warning("%s: Unable to parse JSON object %s: %s" % (name, line, e))
                                continue

                        # Fetch type
                        type = line.get("type")

                        # Skip any metadata
                        if type == "metadata":
                                continue

                        # Fetch ASN
                        asn  = line.get("asn")

                        # Skip any lines without an ASN
                        if not asn:
                                continue

                        # Filter invalid ASNs
                        if not self._check_parsed_asn(asn):
                                log.warning("%s: Skipping bogus ASN %s" % (name, asn))
                                continue

                        # Write to database
                        self.db.execute("""
                                INSERT INTO
                                        autnum_feeds
                                (
                                        number,
                                        source,
                                        is_drop
                                )
                                VALUES
                                (
                                        %s, %s, %s
                                )""", "%s" % asn, name, True,
                        )

        @staticmethod
        def _parse_bool(block, key):
                val = block.get(key)

                # There is no point to proceed when we got None
                if val is None:
                        return

                # Convert to lowercase
                val = val.lower()

                # True
                if val in ("yes", "1"):
                        return True

                # False
                if val in ("no", "0"):
                        return False

                # Default to None
                return None

        def handle_import_countries(self, ns):
                with self.db.transaction():
                        # Drop all data that we have
                        self.db.execute("TRUNCATE TABLE countries")

                        for file in ns.file:
                                for line in file:
                                        line = line.rstrip()

                                        # Ignore any comments
                                        if line.startswith("#"):
                                                continue

                                        try:
                                                country_code, continent_code, name = line.split(maxsplit=2)
                                        except:
                                                log.warning("Could not parse line: %s" % line)
                                                continue

                                        self.db.execute("INSERT INTO countries(country_code, name, continent_code) \
                                                VALUES(%s, %s, %s) ON CONFLICT DO NOTHING", country_code, name, continent_code)


def split_line(line):
        key, colon, val = line.partition(":")

        # Strip any excess space
        key = key.strip()
        val = val.strip()

        return key, val

def main():
        # Run the command line interface
        c = CLI()
        c.run()

main()