From patchwork Sun Sep 6 09:49:10 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: 3435 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 4Bkmq73mjzz3x3L for ; Sun, 6 Sep 2020 09:49:55 +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 4Bkmq72282zv8; Sun, 6 Sep 2020 09:49:55 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Bkmq71mXgz2xjj; Sun, 6 Sep 2020 09:49:55 +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 4Bkmq667yCz2xdN for ; Sun, 6 Sep 2020 09:49:54 +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 4Bkmq30STwzv8 for ; Sun, 6 Sep 2020 09:49:50 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1599385792; 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: in-reply-to:in-reply-to:references:references; bh=ETRUWFhCkVdP1aZ/4HZudzs64ar8ZUJBGxLtESm0aAc=; b=yxQlriJz7bPMlUuiEFpRTlHR7Hb6vowwY3j4nziW7aJnsxB/rbeqcOE4piRmjRs8xCbMh/ HVXaXhPg4vuv/nDw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1599385792; 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: in-reply-to:in-reply-to:references:references; bh=ETRUWFhCkVdP1aZ/4HZudzs64ar8ZUJBGxLtESm0aAc=; b=pxfsLQSfK6EaUqAL4N1PvCQg8g29kxrgIf3OTeXt3ych0i5y9DkYqRgo324CCpF89b/51g 0nJI8Wtvi5rDWcrLz/UIAGoWnCPk/r3Nguy6+9Gbi31E27vihYBRBSDOlsVtXXkYyADP9J nT7pLEZU47HaNjew1k+9ghCeyaJLyhL7HTlNtNgwW5sLY8gxf2OPcMXMWEHBv2ikmLoxOP JLYHP3kSjUOzqcyjK+eSLSudnYs3yIpADzR45ILPvG68hYZy8xSM8NGYKZ0bYtpHq1NP75 eSEf5lH3RH6yr4QcDLBQMy83P9C86LcroZbtAOIHuf/ddlkYVEZMcTrz1Jbzjg== Subject: [PATCH 3/3] importer: Purge any redundant entries To: location@lists.ipfire.org References: <3e511a13-07cc-d5a2-1bd4-2bfc7285990d@ipfire.org> <9e67c95c-cbbd-bda6-0d26-befe24dfa70a@ipfire.org> From: =?utf-8?q?Peter_M=C3=BCller?= Message-ID: <42f16516-2006-5d9e-be61-9134a6fb443a@ipfire.org> Date: Sun, 6 Sep 2020 09:49:10 +0000 MIME-Version: 1.0 In-Reply-To: <9e67c95c-cbbd-bda6-0d26-befe24dfa70a@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. Signed-off-by: Peter Müller --- src/python/location-importer.in | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index fbc402f..ea72790 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -375,6 +375,13 @@ class CLI(object): SELECT _autnums.number, _organizations.name FROM _autnums JOIN _organizations ON _autnums.organization = _organizations.handle ON CONFLICT (number) DO UPDATE SET name = excluded.name; + + --- Purge any redundant entries + DELETE FROM networks candidates WHERE EXISTS ( + SELECT FROM networks + WHERE networks.network >> candidates.network + AND networks.country = candidates.country + ); """) # Download all extended sources