Replace sqlite with PostgreSQL

sqlite cannot match any subnets which makes it useless for us.

PostgreSQL has a data type for IP addresses which can be used
to match subnets against each other.

Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>

diff --git a/tools/base.py b/tools/base.py
index 21cbfb399c1f833583e03c1d96a624208a14c79f..d6af1326f6151e822db5212c4c924cc00005d78c 100644 (file)
--- a/tools/base.py
+++ b/tools/base.py
@@ -25,8 +25,8 @@ import ipaddress
 import logging
 import math
 import os.path
+import psycopg2
 import re
-import sqlite3
 
 from . import downloader
 from . import util
@@ -90,27 +90,41 @@ class RIRParser(object):
                self.downloader = downloader.Downloader()
 
                # Create a database to hold temporary data
-               self.db = self._make_database(":memory:")
+               self.db = self._make_database(dbname="location", user="location")
 
                # Start time
                self.start_time = datetime.datetime.utcnow()
 
-       def _make_database(self, filename):
-               db = sqlite3.connect(filename)
+       def _make_database(self, **kwargs):
+               db = psycopg2.connect(**kwargs)
 
-               # Create database layout
-               with db as cursor:
-                       cursor.executescript("""
-                               CREATE TABLE IF NOT EXISTS autnums(asn INTEGER, name TEXT, org TEXT);
-
-                               CREATE TABLE IF NOT EXISTS inetnums(network TEXT, netname TEXT, country TEXT, description TEXT);
+               # Enable autocommit
+               #db.autocommit = True
 
-                               CREATE TABLE IF NOT EXISTS organisations(handle TEXT, name TEXT, country TEXT);
-                               CREATE INDEX IF NOT EXISTS organisations_handle ON organisations(handle);
-
-                               CREATE TABLE IF NOT EXISTS routes(route TEXT, asn INTEGER);
-                               CREATE INDEX IF NOT EXISTS routes_route ON routes(route);
-                       """)
+               # Create database layout
+               with db.cursor() as cursor:
+                       # Autnums
+                       cursor.execute("CREATE TABLE IF NOT EXISTS autnums(asn integer, name text, org text)")
+                       cursor.execute("TRUNCATE TABLE autnums")
+
+                       # Inetnums
+                       cursor.execute("CREATE TABLE IF NOT EXISTS inetnums(network inet, netname text, \
+                               country text, description text)")
+                       cursor.execute("CREATE INDEX IF NOT EXISTS inetnums_family ON inetnums(family(network))")
+                       cursor.execute("TRUNCATE TABLE inetnums")
+
+                       # Organizations
+                       cursor.execute("CREATE TABLE IF NOT EXISTS organisations(handle text, name text, country text)")
+                       cursor.execute("CREATE INDEX IF NOT EXISTS organisations_handle ON organisations(handle)")
+                       cursor.execute("TRUNCATE TABLE organisations")
+
+                       # Routes
+                       cursor.execute("CREATE TABLE IF NOT EXISTS routes(route inet, asn integer)")
+                       cursor.execute("CREATE INDEX IF NOT EXISTS routes_route ON routes(route)")
+                       cursor.execute("CREATE INDEX IF NOT EXISTS routes_family ON routes(family(route))")
+                       cursor.execute("TRUNCATE TABLE routes")
+
+                       db.commit()
 
                return db
 
@@ -127,13 +141,13 @@ class RIRParser(object):
                # Write header
                self._write_header(f)
 
-               with self.db as c:
-                       res = c.execute("""SELECT DISTINCT autnums.asn, autnums.name,
+               with self.db.cursor() as c:
+                       c.execute("""SELECT DISTINCT autnums.asn, autnums.name,
                                organisations.name, organisations.country FROM autnums
                                LEFT JOIN organisations ON autnums.org = organisations.handle
                                WHERE autnums.asn IS NOT NULL ORDER BY autnums.asn""")
 
-                       for row in res:
+                       for row in c:
                                f.write(FMT % ("asnum:", "AS%s" % row[0]))
 
                                if row[1]:
@@ -152,14 +166,18 @@ class RIRParser(object):
                # Write header
                self._write_header(f)
 
-               with self.db as c:
+               with self.db.cursor() as c:
                        # Write all networks
-                       res = c.execute("""SELECT inetnums.network, routes.asn,
-                               inetnums.country, inetnums.netname, inetnums.description
-                               FROM inetnums LEFT JOIN routes ON inetnums.network = routes.route
-                               ORDER BY routes.asn, inetnums.network""")
+                       c.execute("""SELECT DISTINCT ON (routes.route)
+                               inetnums.network, routes.asn, inetnums.country, inetnums.netname, inetnums.description
+                               FROM routes
+                                       LEFT JOIN inetnums
+                                               ON family(routes.route) = family(inetnums.network)
+                                               AND routes.route <<= inetnums.network
+                               ORDER BY routes.route, masklen(routes.route) DESC
+                       """)
 
-                       for row in res:
+                       for row in c:
                                net, asn, country, name, description = row
 
                                f.write(FMT % ("net:", net))
@@ -194,6 +212,8 @@ class RIRParser(object):
                for url in self.rir.database_urls:
                        self.parse_url(url)
 
+                       self.db.commit()
+
        def parse_url(self, url):
                with self.downloader.request(url) as r:
                        for block in r:
@@ -301,7 +321,7 @@ class RIRParser(object):
                if not inetnum:
                        return
 
-               with self.db as c:
+               with self.db.cursor() as c:
                        args = (
                                inetnum.get("inet6num") or inetnum.get("inetnum"),
                                inetnum.get("netname"),
@@ -310,7 +330,7 @@ class RIRParser(object):
                        )
 
                        c.execute("INSERT INTO inetnums(network, netname, country, description) \
-                               VALUES(?, ?, ?, ?)", args)
+                               VALUES(%s, %s, %s, %s)", args)
 
        def _parse_route_block(self, block):
                logging.debug("Parsing route block:")
@@ -335,14 +355,14 @@ class RIRParser(object):
                if not route:
                        return
 
-               with self.db as c:
+               with self.db.cursor() as c:
                        args = (
                                route.get("route6") or route.get("route"),
                                route.get("asn"),
                        )
 
                        c.execute("INSERT INTO routes(route, asn) \
-                               VALUES(?, ?)", args)
+                               VALUES(%s, %s)", args)
 
        def _parse_autnum_block(self, block):
                logging.debug("Parsing autnum block:")
@@ -366,7 +386,7 @@ class RIRParser(object):
                if not autnum:
                        return
 
-               with self.db as c:
+               with self.db.cursor() as c:
                        args = (
                                autnum.get("asn"),
                                autnum.get("as-name"),
@@ -374,7 +394,7 @@ class RIRParser(object):
                        )
 
                        c.execute("INSERT INTO autnums(asn, name, org) \
-                               VALUES(?, ?, ?)", args)
+                               VALUES(%s, %s, %s)", args)
        
        def _parse_org_block(self, block):
                logging.debug("Parsing org block:")
@@ -393,7 +413,7 @@ class RIRParser(object):
                if not org:
                        return
 
-               with self.db as c:
+               with self.db.cursor() as c:
                        args = (
                                org.get("organisation"),
                                org.get("org-name"),
@@ -401,4 +421,4 @@ class RIRParser(object):
                        )
 
                        c.execute("INSERT INTO organisations(handle, name, country) \
-                               VALUES(?, ?, ?)", args)
+                               VALUES(%s, %s, %s)", args)