From patchwork Sun Sep 20 19:19:36 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: =?utf-8?q?Peter_M=C3=BCller?= X-Patchwork-Id: 3475 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4BvcpD2S2Jz3x48 for ; Sun, 20 Sep 2020 19:19:48 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail02.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4BvcpC718zzsQ; Sun, 20 Sep 2020 19:19:47 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4BvcpC3nDFz2xny; Sun, 20 Sep 2020 19:19:47 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Bvcp96jMfz2xBf for ; Sun, 20 Sep 2020 19:19:45 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Bvcp75PRRzQj for ; Sun, 20 Sep 2020 19:19:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1600629585; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=ra7UQSQGEDTyp3d57V6UtY9kSPttrrU43CL7c2U/HbY=; b=eqw0GkSgX0IuG3B0cvONEUs6z849/SV3KU+m2qRA/rQrpjH9Hw8mKLOdrF7YRlvmWJ0B7a 0HwDCNufvok6ZJtsicikni26kX7ndwWrswEbPGorndRWckOK+P9c1aX6QdmfJcU/5gk8jC DGleaEuUfb1+1pN+rmTFy+nrwxazqWDQLf4qiV6OLRe0Jc9zXNFjD9j7fGgfnk2RI9ZDtQ M1R43vtEiaM4oGqyqk+/+yq2MPOO3VNsuUSE9xfyxX29Hv12enUdkhHpL6uT5Ml75051lh diG1wcdX/VVks8ur92PaYeT4ycWdBTQUEG4y2AcJ4A+JExxhvZGzbV2Z9p9Ufg== DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1600629585; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=ra7UQSQGEDTyp3d57V6UtY9kSPttrrU43CL7c2U/HbY=; b=CuchIVI0oexnQrv4+J4EN3bswzpheZs7qGUVUTysKxwAF3AAtsCZILeGGEn2CewFqbRm4W RqNRdGmlUzxClBCw== To: location@lists.ipfire.org From: =?utf-8?q?Peter_M=C3=BCller?= Subject: [PATCH v2 1/3] location-importer.in: avoid violating NOT NULL constraints during JOIN Message-ID: Date: Sun, 20 Sep 2020 19:19:36 +0000 MIME-Version: 1.0 Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Peter Müller --- src/python/location-importer.in | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index f5ae4a9..77952f2 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -360,7 +360,7 @@ class CLI(object): ON COMMIT DROP; CREATE UNIQUE INDEX _autnums_number ON _autnums(number); - CREATE TEMPORARY TABLE _organizations(handle text, name text) + CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL) ON COMMIT DROP; CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle); """) @@ -373,7 +373,7 @@ class CLI(object): self.db.execute(""" INSERT INTO autnums(number, name) SELECT _autnums.number, _organizations.name FROM _autnums - LEFT JOIN _organizations ON _autnums.organization = _organizations.handle + JOIN _organizations ON _autnums.organization = _organizations.handle ON CONFLICT (number) DO UPDATE SET name = excluded.name; """) From patchwork Sun Sep 20 19:20:18 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: =?utf-8?q?Peter_M=C3=BCller?= X-Patchwork-Id: 3476 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Bvcq275CWz3x48 for ; Sun, 20 Sep 2020 19:20:30 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Bvcq24sHwzkH; Sun, 20 Sep 2020 19:20:30 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Bvcq24KJWz2xny; Sun, 20 Sep 2020 19:20:30 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Bvcq113FXz2xny for ; Sun, 20 Sep 2020 19:20:29 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384)) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Bvcpx6hxZzkH; Sun, 20 Sep 2020 19:20:25 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1600629628; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=j5CVB1W7Avpi4AHEJJYpOAbNkO0Dfb8Xj5UI7bNNSjc=; b=/1wSPhxibYILOf9HpSG70DBk8RMpnqWKOcVbuTmaMS3J+ux2vtxcqoXDrmU2f9UYF1P69o 73x9OZznmA7bTJDQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1600629628; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=j5CVB1W7Avpi4AHEJJYpOAbNkO0Dfb8Xj5UI7bNNSjc=; b=Giald05DpZM1gxjRgo7OsxlVW9kHq9FtrxmS0AcvXTNxpr6b6sah1HNTeeMInRcI3OhFKK aNFrMBUjW3RDcjrWqhSeKgLdJg0aybhmwaD6oBVMqWtBm6RbFSsm2QAnnZWrJ2XAhO6xBI cSHQ1ModgwdYMKRdAW2rIrPNDpEqtjFEgqfNqv2ULRvdgpKqDCb9+TkjvtUiCjCLK+jWGy BwnneYnriVRwo77ZoV5X2ivl8a8a9SZUI3+d/GJqxfoXlomEZNENAuwVBPH/1qTSTB/B5Z 7b54Vsll5PB5TjXMDOX+8lYbZagvbb3MKIxzhbXCfTkqK6NBoThlXRS6aXzw5w== Subject: [PATCH v2 2/3] importer: Import raw sources for inetnum's again To: location@lists.ipfire.org References: From: =?utf-8?q?Peter_M=C3=BCller?= Message-ID: <54b93c52-9578-3390-d8d4-e889766dcb84@ipfire.org> Date: Sun, 20 Sep 2020 19:20:18 +0000 MIME-Version: 1.0 In-Reply-To: Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: location-bounces@lists.ipfire.org Sender: "Location" The extended feeds do not have enough detailed information for us, so that we need to import inetnums from RIRs where possible. Filtering private networks is necessary as RIR data may contain 0.0.0.0/0 or similar entries for administrative purposes or due to misfilings. Special thanks goes to Michael for spending numerous hours on this, setting up a testing environment and providing helpful advice while debugging. Partially fixes: #12458 Cc: Michael Tremer Signed-off-by: Peter Müller --- src/python/importer.py | 14 ++++---- src/python/location-importer.in | 63 +++++++++++++++++++++++++++++++++ 2 files changed, 70 insertions(+), 7 deletions(-) diff --git a/src/python/importer.py b/src/python/importer.py index de20f37..f19db4b 100644 --- a/src/python/importer.py +++ b/src/python/importer.py @@ -30,8 +30,8 @@ WHOIS_SOURCES = ( "https://ftp.afrinic.net/pub/pub/dbase/afrinic.db.gz", # Asia Pacific Network Information Centre - #"https://ftp.apnic.net/apnic/whois/apnic.db.inet6num.gz", - #"https://ftp.apnic.net/apnic/whois/apnic.db.inetnum.gz", + "https://ftp.apnic.net/apnic/whois/apnic.db.inet6num.gz", + "https://ftp.apnic.net/apnic/whois/apnic.db.inetnum.gz", #"https://ftp.apnic.net/apnic/whois/apnic.db.route6.gz", #"https://ftp.apnic.net/apnic/whois/apnic.db.route.gz", "https://ftp.apnic.net/apnic/whois/apnic.db.aut-num.gz", @@ -45,8 +45,8 @@ WHOIS_SOURCES = ( # XXX ??? # Réseaux IP Européens - #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.inet6num.gz", - #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.inetnum.gz", + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inet6num.gz", + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inetnum.gz", #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route6.gz", #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route.gz", "https://ftp.ripe.net/ripe/dbase/split/ripe.db.aut-num.gz", @@ -55,10 +55,10 @@ WHOIS_SOURCES = ( EXTENDED_SOURCES = ( # African Network Information Centre - "https://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-extended-latest", + #"https://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-extended-latest", # Asia Pacific Network Information Centre - "https://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-extended-latest", + #"https://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-extended-latest", # American Registry for Internet Numbers "https://ftp.arin.net/pub/stats/arin/delegated-arin-extended-latest", @@ -67,7 +67,7 @@ EXTENDED_SOURCES = ( "http://ftp.lacnic.net/pub/stats/lacnic/delegated-lacnic-extended-latest", # Réseaux IP Européens - "https://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-extended-latest", + #"https://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-extended-latest", ) class Downloader(object): diff --git a/src/python/location-importer.in b/src/python/location-importer.in index 77952f2..e3a07a0 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -393,6 +393,10 @@ class CLI(object): if line.startswith("aut-num:"): return self._parse_autnum_block(block) + # inetnum + if line.startswith("inet6num:") or line.startswith("inetnum:"): + return self._parse_inetnum_block(block) + # organisation elif line.startswith("organisation:"): return self._parse_org_block(block) @@ -422,6 +426,65 @@ class CLI(object): autnum.get("asn"), autnum.get("org"), ) + def _parse_inetnum_block(self, block): + logging.debug("Parsing inetnum block:") + + inetnum = {} + for line in block: + logging.debug(line) + + # Split line + key, val = split_line(line) + + if key == "inetnum": + start_address, delim, end_address = val.partition("-") + + # Strip any excess space + start_address, end_address = start_address.rstrip(), end_address.strip() + + # Convert to IP address + try: + start_address = ipaddress.ip_address(start_address) + end_address = ipaddress.ip_address(end_address) + except ValueError: + logging.warning("Could not parse line: %s" % line) + return + + # Set prefix to default + prefix = 32 + + # Count number of addresses in this subnet + num_addresses = int(end_address) - int(start_address) + if num_addresses: + prefix -= math.log(num_addresses, 2) + + inetnum["inetnum"] = "%s/%.0f" % (start_address, prefix) + + elif key == "inet6num": + inetnum[key] = val + + elif key == "country": + if val == "UNITED STATES": + val = "US" + + inetnum[key] = val.upper() + + # Skip empty objects + if not inetnum: + return + + network = ipaddress.ip_network(inetnum.get("inet6num") or inetnum.get("inetnum"), strict=False) + + # 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) \ + VALUES(%s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country", + "%s" % network, inetnum.get("country"), + ) + def _parse_org_block(self, block): org = {} for line in block: From patchwork Sun Sep 20 19:21:03 2020 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: =?utf-8?q?Peter_M=C3=BCller?= X-Patchwork-Id: 3477 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Bvcqt74hvz3x48 for ; Sun, 20 Sep 2020 19:21:14 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Bvcqt64xbzsQ; Sun, 20 Sep 2020 19:21:14 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Bvcqt4vwwz2xny; Sun, 20 Sep 2020 19:21:14 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Bvcqs42rZz2xny for ; Sun, 20 Sep 2020 19:21:13 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384)) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Bvcqp1nWzzhS; Sun, 20 Sep 2020 19:21:09 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1600629672; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=siey5TjWMGrfUAzGwb32kgi8X8iMLxLtpIeZ2XHJN4g=; b=7uIjqOwt2kfuQNIA/BtCsSYJgMY2ViRlFgEsj2ZSQ/rXpQlx8QkwSR6aJCznVluXP0pzcm CDvwu9qz8SuKZ1Dg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1600629672; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=siey5TjWMGrfUAzGwb32kgi8X8iMLxLtpIeZ2XHJN4g=; b=O+/kkfJdvO7fhsCyYZsCG/hpIDzLo6hENFXRRjRhYug/Jhoyv3fE9CpWMKdm5DEsWiRCJL mgHHCsgxyn5/jpbCoJyO9tU17q15k3GhubDBg+gtn3XPD2WDfeZsIEM0UDo8AlydpNSVL2 RYFeZ3ccSJ4QZgLVSSXl0qdZSga0EQMWo2LBtSdGx15yabH32E5qQcv+LRXL/m3juEcfn/ gtgg6Lybfmo8IuX+5q3cO3RVr0b33eF346lkE7lP6FednC5Was5MUkdWryBcVetzCS4U2p 0AjJ3PYFr9WLr9+HY3QK3vG+p06sQWg5U+3ZU1Ly3tX3hH+jilVwSMUfZ+lxpw== Subject: [PATCH v2 3/3] importer: Purge any redundant entries To: location@lists.ipfire.org References: <54b93c52-9578-3390-d8d4-e889766dcb84@ipfire.org> From: =?utf-8?q?Peter_M=C3=BCller?= Message-ID: <8a59a82a-ba19-d1d2-0a1d-87fc72195f16@ipfire.org> Date: Sun, 20 Sep 2020 19:21:03 +0000 MIME-Version: 1.0 In-Reply-To: <54b93c52-9578-3390-d8d4-e889766dcb84@ipfire.org> Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: location-bounces@lists.ipfire.org Sender: "Location" When importing inetnums, we might import various small networks which are not relevant for us as long as they do not have a different country code than their parent network. Therefore we delete all these entries to keep the database smaller without losing any information. The second version of this patch introduces a SQL statement parallelised across all CPUs available, while the DELETE-statement of the first version literally took ages to complete. However, cleaning up those data still takes about 26 hours (!) on our location02 testing machine, making daily updates of the location database impossible to the current knowledge. real 1521m30.620s user 38m45.521s sys 9m6.027s Special thanks goes to Michael for spending numerous hours on this, setting up a testing environment, doing PostgreSQL magic and providing helpful advice while debugging. Partially fixes: #12458 Cc: Michael Tremer Signed-off-by: Peter Müller --- src/python/location-importer.in | 22 +++++++++++++++++++++- 1 file changed, 21 insertions(+), 1 deletion(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index e3a07a0..1467923 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -374,7 +374,27 @@ class CLI(object): INSERT INTO autnums(number, name) SELECT _autnums.number, _organizations.name FROM _autnums JOIN _organizations ON _autnums.organization = _organizations.handle - ON CONFLICT (number) DO UPDATE SET name = excluded.name; + ON CONFLICT (number) DO UPDATE SET name = excluded.name + """) + + self.db.execute(""" + --- Purge any redundant entries + CREATE TEMPORARY TABLE _garbage ON COMMIT DROP + AS + SELECT network FROM networks candidates + WHERE EXISTS ( + SELECT FROM networks + WHERE + networks.network << candidates.network + AND + networks.country = candidates.country + ); + + CREATE UNIQUE INDEX _garbage_search ON _garbage USING BTREE(network); + + DELETE FROM networks WHERE EXISTS ( + SELECT FROM _garbage WHERE networks.network = _garbage.network + ); """) # Download all extended sources