[PATCH 2/8] Revert "Revert "location-importer.in: only import relevant data from AFRINIC, APNIC and RIPE""

["Peter Müller" <peter.mueller@ipfire.org>]

[-- Attachment #1: Type: text/plain, Size: 5385 bytes --]

This reverts commit 13f67f285856e8eabfeff2daf1be3aeaa36a82cc.

Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org>
---
 src/python/location-importer.in | 89 ++++++++++++++++++++++++++++++++-
 1 file changed, 87 insertions(+), 2 deletions(-)

diff --git a/src/python/location-importer.in b/src/python/location-importer.in
index b220eaf..d249a35 100644
--- a/src/python/location-importer.in
+++ b/src/python/location-importer.in
@@ -165,6 +165,7 @@ class CLI(object):
 				-- networks
 				CREATE TABLE IF NOT EXISTS networks(network inet, country 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);
 
 				-- overrides
@@ -365,6 +366,16 @@ class CLI(object):
 				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL)
 					ON COMMIT DROP;
 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
+
+				CREATE TEMPORARY TABLE _rirdata(network inet NOT NULL, country 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("""
+				TRUNCATE TABLE networks;
 			""")
 
 			for source in location.importer.WHOIS_SOURCES:
@@ -372,6 +383,67 @@ class CLI(object):
 					for block in f:
 						self._parse_block(block)
 
+			# 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):
+				smallest = self.db.get("SELECT MIN(masklen(network)) AS prefix FROM _rirdata WHERE family(network) = %s", family)
+
+				self.db.execute("INSERT INTO networks(network, country) \
+					SELECT network, country FROM _rirdata WHERE masklen(network) = %s AND family(network) = %s", 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
+							FROM
+								_rirdata
+							WHERE
+								family(_rirdata.network) = %s
+							AND
+								masklen(_rirdata.network) = %s
+						),
+						filtered AS (
+							SELECT
+								DISTINCT ON (c.network)
+								c.network,
+								c.country,
+								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)
+						SELECT
+							network,
+							country
+						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)
 					SELECT _autnums.number, _organizations.name FROM _autnums
@@ -472,17 +544,30 @@ class CLI(object):
 				inetnum[key] = val.upper()
 
 		# Skip empty objects
-		if not inetnum:
+		if not inetnum or not "country" in inetnum:
 			return
 
 		network = ipaddress.ip_network(inetnum.get("inet6num") or inetnum.get("inetnum"), strict=False)
 
+		# Bail out in case we have processed a network covering the entire IP range, which
+		# is necessary to work around faulty (?) IPv6 network processing
+		if network.prefixlen == 0:
+			logging.warning("Skipping network covering the entire IP adress range: %s" % network)
+			return
+
+		# Bail out in case we have processed a network whose prefix length indicates it is
+		# not globally routable (we have decided not to process them at the moment, as they
+		# significantly enlarge our database without providing very helpful additional information)
+		if (network.prefixlen > 24 and network.version == 4) or (network.prefixlen > 48 and network.version == 6):
+			logging.info("Skipping network too small to be publicly announced: %s" % network)
+			return
+
 		# Bail out in case we have processed a non-public IP network
 		if network.is_private:
 			logging.warning("Skipping non-globally routable network: %s" % network)
 			return
 
-		self.db.execute("INSERT INTO networks(network, country) \
+		self.db.execute("INSERT INTO _rirdata(network, country) \
 			VALUES(%s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country",
 			"%s" % network, inetnum.get("country"),
 		)
-- 
2.20.1