From patchwork Tue Sep 27 16:48:41 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6017 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 "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4McQZ260T2z3wgT for ; Tue, 27 Sep 2022 16:48:58 +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 "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4McQZ103zjz2RG; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ03vZZz301x; Tue, 27 Sep 2022 16:48:56 +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 "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4McQYy5tZzz2xNS for ; Tue, 27 Sep 2022 16:48: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) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYy4bRwzh6; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=2sHkC8WKbqh7llPBOijIa2DUrZCWrfBsAgRBLXrfY8s=; b=4AoEaJKzWPKruml7XDSKUimcZBwr66qVTlCw001+e0/KascnZ+iP0fY9TfIQk89NM36qXM PnGPPnRmsaE5UgAw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=2sHkC8WKbqh7llPBOijIa2DUrZCWrfBsAgRBLXrfY8s=; b=NyOVclwZN+14qehklORNUI45sSAtuhAHcTSk1KUCCDPyRBlnzXzSHm3ozAukLvfT4tPhPC Etu45gJqmbIbH6Tw+xN4PKKNBSXLqzo4EI+hxzEIr0k4zmbUkxihBCXbaUPjYkdrn4bCKr N2S+Xzrjo6EpxZaiASlSfbV3UFf0kBOL7dFqqhR20bU6a0YcfbUU5rCxH0wUliDfIRQmj/ ElP0tlIj8i9JuQ0EIXgMacx0iR+fhJ2Pe0WATc1qaP4Ka2HibofDZiU4FwXCfH79R3cnz9 UyruQ6eM9iyLRRvhvjyTkesJvKSTJccBMyYA9o07vtL4Xr21dqZk7/HY73fkfA== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 04/10] importer: Sync geofeeds Date: Tue, 27 Sep 2022 16:48:41 +0000 Message-Id: <20220927164847.3409646-4-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@ipfire.org> MIME-Version: 1.0 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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Geofeeds are kept in a separate table to only fetch them once per URL. This needs to be kept in sync which is done before we update any feeds. Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 014f3b6..12035f1 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -1279,6 +1279,38 @@ class CLI(object): 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