path: root/scripts/rp-sqlite

#!/usr/local/bin/python

# $Id$

# Copyright (C) 2013  Dragon Research Labs ("DRL")
#
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND DRL DISCLAIMS ALL WARRANTIES WITH
# REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
# AND FITNESS.  IN NO EVENT SHALL DRL BE LIABLE FOR ANY SPECIAL, DIRECT,
# INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
# LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
# OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
# PERFORMANCE OF THIS SOFTWARE.

# Preliminary script to work out what's involved in building an
# SQLite3 database of RP objects.  We haven't bothered with this until
# now in rcynic, because we mostly just walk the filesystem tree, but
# LTA and some of the ideas Tim is playing with require a lot of
# lookups based on things that are not the URIs we use as filenames,
# so some kind of indexing may become necessary.  Given the complexity
# of building any kind of real index over RFC 3779 resources,
# otherwise fine lightweight tools like the Python shelve library
# probably won't cut it here, and I don't want to add a dependency on
# MySQL on the RP side (yet?), so let's see what we can do with SQLite3.

import os
import sys
import sqlite3
import rpki.POW
import rpki.x509
import rpki.resource_set

fn2map = dict(cer = rpki.x509.X509,
              crl = rpki.x509.CRL,
              mft = rpki.x509.SignedManifest,
              roa = rpki.x509.ROA,
              gbr = rpki.x509.Ghostbuster)

sqlite3.register_adapter(rpki.POW.IPAddress,
                         lambda x: buffer("_" + x.toBytes()))

sqlite3.register_converter("RangeVal",
                           lambda s: long(s) if s.isdigit() else rpki.POW.IPAddress.fromBytes(s[1:]))


def main():
  db, cur = initialize_database()
  test(cur)
  db.close()


def test(cur):
  print "Testing range functions"
  for fn2 in [None] + fn2map.keys():
    if fn2 is not None:
      print
      print "Restricting search to type", fn2
    print
    print "Looking for range that should include adrilankha and psg again"
    for r in find_by_range(cur, "147.28.0.19", "147.28.0.62", fn2):
      print r, r.uris
    print
    print "Looking for range that should include adrilankha"
    for r in find_by_range(cur, "147.28.0.19", "147.28.0.19", fn2):
      print r, r.uris
    print
    print "Looking for range that should include ASN 3130"
    for r in find_by_range(cur, 3130, 3130, fn2):
      print r, r.uris
  print
  print "Moving on to resource sets"
  for expr in ("147.28.0.19-147.28.0.62",
               "3130",
               "2001:418:1::19/128",
               "147.28.0.19-147.28.0.62,198.180.150.50/32",
               "3130,147.28.0.19-147.28.0.62,198.180.150.50/32",
               "2001:418:1::62/128,198.180.150.50/32,2001:418:8006::50/128",
               "147.28.0.19-147.28.0.62,2001:418:1::19/128,2001:418:1::62/128,198.180.150.50/32,2001:418:8006::50/128"):
    print
    print "Trying", expr
    for r in find_by_resource_bag(cur, rpki.resource_set.resource_bag.from_str(expr)):
      print r, r.uris
    for fn2 in fn2map:
      print
      print "Trying", fn2, expr
      for r in find_by_resource_bag(cur, rpki.resource_set.resource_bag.from_str(expr), fn2):
        print r, r.uris
    

def find_by_ski(cur, ski, fn2 = None):
  return find_results(cur, fn2,
                      """
                      SELECT id, fn2, der
                      FROM object
                      WHERE ski = ?
                      """,
                      buffer(ski))


def find_by_aki(cur, aki, fn2 = None):
  return find_results(cur, fn2,
                      """
                      SELECT id, fn2, der
                      FROM object
                      WHERE aki = ?
                      """,
                      buffer(aki))


# It's easiest to understand overlap conditions by understanding
# non-overlap then inverting and and applying De Morgan's law.  Ranges
# A and B do not overlap if either A.min > B.max or A.max < B.min;
# therefore they do overlap if A.min <= B.max and A.max >= B.min.

def find_by_range(cur, range_min, range_max = None, fn2 = None):
  if range_max is None:
    range_max = range_min
  if isinstance(range_min, (str, unicode)):
    range_min = long(range_min) if range_min.isdigit() else rpki.POW.IPAddress(range_min)
  if isinstance(range_max, (str, unicode)):
    range_max = long(range_max) if range_max.isdigit() else rpki.POW.IPAddress(range_max)
  assert isinstance(range_min, (int, long, rpki.POW.IPAddress))
  assert isinstance(range_max, (int, long, rpki.POW.IPAddress))
  return find_results(cur, fn2,
                      """
                      SELECT object.id, fn2, der
                      FROM object, range
                      WHERE ? <= max AND ? >= min AND object.id = range.id
                      """,
                      range_min,
                      range_max)


def find_by_resource_bag(cur, bag, fn2 = None):
  assert bag.asn or bag.v4 or bag.v6
  qset = []
  aset = []
  for rset in (bag.asn, bag.v4, bag.v6):
    if rset:
      for r in rset:
        qset.append("(? <= max AND ? >= min)")
        aset.append(r.min)
        aset.append(r.max)
  return find_results(*([
    cur, fn2,
    """
    SELECT object.id, fn2, der
    FROM object, range
    WHERE object.id = range.id AND (%s)
    """ % (" OR ".join(qset))
    ] + aset))


def find_results(cur, fn2, query, *args):
  if fn2 is not None:
    assert fn2 in fn2map
    query += " AND fn2 = ?"
    args = args + (fn2,)
  query += " GROUP BY object.id"
  results = []
  cur.execute(query, args)
  selections = cur.fetchall()
  for rowid, fn2, der in selections:
    obj = fn2map[fn2](DER = der)
    cur.execute("SELECT uri FROM uri WHERE id = ?", (rowid,))
    obj.uris = [u[0] for u in cur.fetchall()]
    obj.uri = obj.uris[0] if len(obj.uris) == 1 else None
    results.append(obj)
  return results

def initialize_database(db_name = "rp-sqlite.db",
                        rcynic_root = os.path.expanduser("~/rpki/subvert-rpki.hactrn.net/trunk/"
                                                         "rcynic/rcynic-data/unauthenticated"),
                        delete_old_db = True,
                        spinner = 100):

  # For now just wire in the database name and rcynic root, fix this
  # later if overall approach seems usable.  Might even end up just
  # being an in-memory SQL database, who knows?

  if delete_old_db:
    try:
      os.unlink(db_name)
    except:
      pass

  db = sqlite3.connect(db_name, detect_types = sqlite3.PARSE_DECLTYPES)
  db.text_factory = str

  cur = db.cursor()
  cur.execute("PRAGMA foreign_keys = on")

  cur.execute('''
              CREATE TABLE object (
                id INTEGER PRIMARY KEY NOT NULL,
                der BLOB NOT NULL,
                fn2 TEXT NOT NULL,
                ski BLOB,
                aki BLOB,
                UNIQUE (der))
              ''')

  cur.execute('''
              CREATE TABLE uri (
                id INTEGER NOT NULL,
                uri TEXT NOT NULL,
                UNIQUE (uri),
                FOREIGN KEY (id) REFERENCES object(id)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE)
              ''')

  cur.execute("CREATE INDEX uri_index ON uri(id)")

  cur.execute('''
              CREATE TABLE range (
                id INTEGER NOT NULL,
                min RangeVal NOT NULL,
                max RangeVal NOT NULL,
                UNIQUE (id, min, max),
                FOREIGN KEY (id) REFERENCES object(id)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE)
              ''')

  cur.execute("CREATE INDEX range_index ON range(min, max)")

  nobj = 0

  for root, dirs, files in os.walk(rcynic_root):
    for fn in files:
      fn = os.path.join(root, fn)
      fn2 = os.path.splitext(fn)[1][1:]

      try:
        obj = fn2map[fn2](DER_file = fn)
      except:
        continue

      if spinner and nobj % spinner == 0:
        sys.stderr.write("\r%s %d..." % ("|\\-/"[(nobj/spinner) & 3], nobj))

      nobj += 1

      if fn2 == "crl":
        ski = None
        aki = buffer(obj.get_AKI())
        cer = None

      else:
        if fn2 == "cer":
          cer = obj
        else:
          cer = rpki.x509.X509(POW = obj.get_POW().certs()[0])
        ski = buffer(cer.get_SKI())
        try:
          aki = buffer(cer.get_AKI())
        except:
          aki = None

      der = buffer(obj.get_DER())
      uri = "rsync://" + fn[len(rcynic_root) + 1:]

      try:
        cur.execute("INSERT INTO object (der, fn2, ski, aki) VALUES (?, ?, ?, ?)",
                    (der, fn2, ski, aki))
        rowid = cur.lastrowid

      except sqlite3.IntegrityError:
        cur.execute("SELECT id FROM object WHERE der = ? AND fn2 = ?", (der, fn2))
        rows = cur.fetchall()
        rowid = rows[0][0]
        assert len(rows) == 1

      else:
        if cer is not None:
          bag = cer.get_3779resources()
          for rset in (bag.asn, bag.v4, bag.v6):
            if rset is not None:
              cur.executemany("REPLACE INTO range (id, min, max) VALUES (?, ?, ?)",
                              ((rowid, i.min, i.max) for i in rset))

      cur.execute("INSERT INTO uri (id, uri) VALUES (?, ?)",
                  (rowid, uri))

  if spinner:
    sys.stderr.write("\r= %d objects, committing..." % nobj)

  db.commit()

  if spinner:
    sys.stderr.write("done.\n")

  return db, cur

if __name__ == "__main__":
  main()