Merge tk671 (router certificate support) back to trunk. See #671.

svn path=/trunk/; revision=5753

1 files changed, 148 insertions, 37 deletions

diff --git a/rpkid/rpki-sql-setup b/rpkid/rpki-sql-setup
index 1623d542..40a78532 100755
--- a/rpkid/rpki-sql-setup
+++ b/rpkid/rpki-sql-setup
@@ -2,27 +2,37 @@
 
 # $Id$
 #
-# Copyright (C) 2009--2013  Internet Systems Consortium ("ISC")
-#
+# Copyright (C) 2014  Dragon Research Labs ("DRL")
+# Portions copyright (C) 2009-2013  Internet Systems Consortium ("ISC")
+# 
 # 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 ISC DISCLAIMS ALL WARRANTIES WITH
-# REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
-# AND FITNESS.  IN NO EVENT SHALL ISC 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
+# copyright notices and this permission notice appear in all copies.
+# 
+# THE SOFTWARE IS PROVIDED "AS IS" AND DRL AND ISC DISCLAIM ALL
+# WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
+# WARRANTIES OF MERCHANTABILITY AND FITNESS.  IN NO EVENT SHALL DRL OR
+# ISC 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.
 
+import os
 import sys
+import glob
 import getpass
 import argparse
+import datetime
 import rpki.config
+import rpki.version
+import rpki.autoconf
 import rpki.sql_schemas
 
-from rpki.mysql_import import MySQLdb
+from rpki.mysql_import import MySQLdb, _mysql_exceptions
+
+ER_NO_SUCH_TABLE = 1146                 # See mysqld_ername.h
+
 
 class RootDB(object):
   """
@@ -58,12 +68,30 @@ class RootDB(object):
     if self.initialized:
       self.db.close()
 
+
 class UserDB(object):
   """
   Class to wrap MySQL access parameters for a particular database.
+
+  NB: The SQL definitions for the upgrade_version table is embedded in
+  this class rather than being declared in any of the .sql files.
+  This is deliberate: nothing but the upgrade system should ever touch
+  this table, and it's simpler to keep everything in one place.
+
+  We have to be careful about SQL commits here, because CREATE TABLE
+  implies an automatic commit.  So presence of the magic table per se
+  isn't significant, only its content (or lack thereof).
   """
 
+  upgrade_version_table_schema = """
+    CREATE TABLE upgrade_version (
+      version   TEXT NOT NULL,
+      updated   DATETIME NOT NULL
+    ) ENGINE=InnoDB
+    """
+
   def __init__(self, name):
+    self.name = name
     self.database = cfg.get("sql-database", section = name)
     self.username = cfg.get("sql-username", section = name)
     self.password = cfg.get("sql-password", section = name)
@@ -72,39 +100,104 @@ class UserDB(object):
 
   def open(self):
     self.db = MySQLdb.connect(db = self.database, user = self.username, passwd = self.password)
+    self.db.autocommit(False)
     self.cur = self.db.cursor()
 
-  def commit(self):
-    self.db.commit()
-
   def close(self):
+    if self.cur is not None:
+      self.cur.close()
+      self.cur = None
     if self.db is not None:
+      self.db.commit()
       self.db.close()
       self.db = None
-      self.cur = None
 
   @property
   def exists_and_accessible(self):
     try:
-      db = MySQLdb.connect(db = self.database, user = self.username, passwd = self.password)
-      db.close()
-      return True
+      MySQLdb.connect(db = self.database, user = self.username, passwd = self.password).close()
     except:
       return False
+    else:
+      return True
 
+  @property
+  def version(self):
+    try:
+      self.cur.execute("SELECT version FROM upgrade_version")
+      v = self.cur.fetchone()
+      return Version(None if v is None else v[0])
+    except _mysql_exceptions.ProgrammingError, e:
+      if e.args[0] != ER_NO_SUCH_TABLE:
+        raise
+      log("Creating upgrade_version table in %s" % self.name)
+      self.cur.execute(self.upgrade_version_table_schema)
+      return Version(None)
 
-def read_schema(name):
+  @version.setter
+  def version(self, v):
+    if v > self.version:
+      self.cur.execute("DELETE FROM upgrade_version")
+      self.cur.execute("INSERT upgrade_version (version, updated) VALUES (%s, %s)", (v, datetime.datetime.now()))
+      self.db.commit()
+      log("Updated %s to %s" % (self.name, v))
+
+  @property
+  def schema(self):
+    lines = []
+    for line in getattr(rpki.sql_schemas, self.name, "").splitlines():
+      line = " ".join(line.split())
+      if line and not line.startswith("--"):
+        lines.append(line)
+    return [statement.strip() for statement in " ".join(lines).rstrip(";").split(";") if statement.strip()]
+
+
+class Version(object):
   """
-  Convert an SQL file into a list of SQL statements.
+  A version number.  This is a class in its own right to force the
+  comparision and string I/O behavior we want.
   """
 
-  lines = []
-  for line in getattr(rpki.sql_schemas, name, "").splitlines():
-    line = " ".join(line.split())
-    if line and not line.startswith("--"):
-      lines.append(line)
+  def __init__(self, v):
+    if v is None:
+      v = "0.0"
+    self.v = tuple(v.lower().split("."))
+
+  def __str__(self):
+    return ".".join(self.v)
+
+  def __cmp__(self, other):
+    return cmp(self.v, other.v)
+
+
+class Upgrade(object):
+  """
+  One upgrade script.  Really, just its filename and the Version
+  object we parse from its filename, we don't need to read the script
+  itself except when applying it, but we do need to sort all the
+  available upgrade scripts into version order.
+  """
+
+  @classmethod
+  def load_all(cls, name, dir):
+    g = os.path.join(dir, "upgrade-%s-to-*.py" % name)
+    for fn in glob.iglob(g):
+      yield cls(g, fn)
+
+  def __init__(self, g, fn):
+    head, sep, tail = g.partition("*")
+    self.fn = fn
+    self.version = Version(fn[len(head):-len(tail)])
+
+  def __cmp__(self, other):
+    return cmp(self.version, other.version)
+
+  def apply(self, db):
+    # db is an argument here primarily so the script we exec can get at it
+    log("Applying %s to %s" % (self.fn, db.name))
+    with open(self.fn, "r") as f:
+      exec f
 
-  return [statement.strip() for statement in " ".join(lines).rstrip(";").split(";") if statement.strip()]
 
 def do_drop(name):
   db = UserDB(name)
@@ -122,11 +215,11 @@ def do_create(name):
                    (db.password,))
   root.db.commit()
   db.open()
-  for statement in read_schema(name):
+  for statement in db.schema:
     if not statement.upper().startswith("DROP TABLE"):
       log(statement)
       db.cur.execute(statement)
-  db.commit()
+  db.version = current_version
   db.close()
 
 def do_script_drop(name):
@@ -150,6 +243,19 @@ def do_create_if_missing(name):
   if not db.exists_and_accessible:
     do_create(name)
 
+def do_apply_upgrades(name):
+  upgrades = sorted(Upgrade.load_all(name, args.upgrade_scripts))
+  if upgrades:
+    db = UserDB(name)
+    db.open()
+    log("Current version of %s is %s" % (db.name, db.version))
+    for upgrade in upgrades:
+      if upgrade.version > db.version:
+        upgrade.apply(db)
+        db.version = upgrade.version
+    db.version = current_version
+    db.close()
+
 def log(text):
   if args.verbose:
     print "#", text
@@ -165,6 +271,9 @@ parser.add_argument("-v", "--verbose",  action = "store_true",
                     help = "whistle while you work")
 parser.add_argument("--mysql-defaults",
                     help = "specify MySQL root access credentials via a configuration file")
+parser.add_argument("--upgrade-scripts",
+                    default = os.path.join(rpki.autoconf.datarootdir, "rpki", "upgrade-scripts"),
+                    help = "override default location of upgrade scripts")
 group.add_argument("--create",
                    action = "store_const", dest = "dispatch", const = do_create,
                    help = "create databases and load schemas")
@@ -183,18 +292,20 @@ group.add_argument("--fix-grants",
 group.add_argument("--create-if-missing",
                    action = "store_const", dest = "dispatch", const = do_create_if_missing,
                    help = "create databases and load schemas if they don't exist already")
+group.add_argument("--apply-upgrades",
+                   action = "store_const", dest = "dispatch", const = do_apply_upgrades,
+                   help = "apply upgrade scripts to existing databases")
 parser.set_defaults(dispatch = do_create_if_missing)
 args = parser.parse_args()
 
-cfg  = rpki.config.parser(args.config, "myrpki")
-root = RootDB(args.mysql_defaults)
 try:
-  if cfg.getboolean("start_irdbd", False):
-    args.dispatch("irdbd")
-  if cfg.getboolean("start_rpkid", False):
-    args.dispatch("rpkid")
-  if cfg.getboolean("start_pubd",  False):
-    args.dispatch("pubd")
+  cfg  = rpki.config.parser(args.config, "myrpki")
+  root = RootDB(args.mysql_defaults)
+  current_version = Version(rpki.version.VERSION)
+  for name in ("irdbd", "rpkid", "pubd"):
+    if cfg.getboolean("start_" + name, False):
+      args.dispatch(name)
   root.close()
 except Exception, e:
-  sys.exit(str(e))
+  #sys.exit(str(e))
+  raise