Add rpki-sql-setup --apply-deferred-updates to support migrations

which can't be executed until the daemons are up.  See #671.

svn path=/branches/tk671/; revision=5718

2 files changed, 70 insertions, 17 deletions

diff --git a/rpkid/rpki-sql-setup b/rpkid/rpki-sql-setup
index da94a587..c1535ec8 100755
--- a/rpkid/rpki-sql-setup
+++ b/rpkid/rpki-sql-setup
@@ -21,6 +21,7 @@
 import os
 import sys
 import glob
+import time
 import getpass
 import argparse
 import datetime
@@ -73,10 +74,11 @@ class UserDB(object):
   """
   Class to wrap MySQL access parameters for a particular database.
 
-  NB: The SQL definition 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.
+  NB: The SQL definitions for the upgrade_version and deferred_upgrade
+  tables are 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 these tables, 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
@@ -90,6 +92,15 @@ class UserDB(object):
     ) ENGINE=InnoDB
     """
 
+  deferred_upgrade_table_schema = """
+    CREATE TABLE deferred_upgrade (
+      id        SERIAL NOT NULL,
+      script    TEXT NOT NULL,
+      added     DATETIME NOT NULL,
+      version   TEXT
+    ) ENGINE=InnoDB
+    """
+
   def __init__(self, name):
     self.name = name
     self.database = cfg.get("sql-database", section = name)
@@ -103,9 +114,6 @@ class UserDB(object):
     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()
@@ -125,10 +133,6 @@ class UserDB(object):
       return True
 
   @property
-  def uses_upgrade_version(self):
-    return self.name != "irdbd"
-
-  @property
   def version(self):
     try:
       self.cur.execute("SELECT version FROM upgrade_version")
@@ -146,7 +150,7 @@ class UserDB(object):
     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.commit()
+      self.db.commit()
       log("Updated %s to %s" % (self.name, v))
 
   @property
@@ -158,6 +162,35 @@ class UserDB(object):
         lines.append(line)
     return [statement.strip() for statement in " ".join(lines).rstrip(";").split(";") if statement.strip()]
 
+  def add_deferred_upgrade(self, script, version = None):
+    insert = "INSERT deferred_upgrade (script, added, version) VALUES (%s, %s, %s)"
+    values = (script, datetime.datetime.now(), version)
+    try:
+      self.cur.execute(insert, values)
+    except _mysql_exceptions.ProgrammingError, e:
+      if e.args[0] != ER_NO_SUCH_TABLE:
+        raise
+      log("Creating deferred_upgrade table in %s" % self.name)
+      self.cur.execute(self.deferred_upgrade_table_schema)
+      self.cur.execute(insert, values)
+    self.db.commit()
+
+  @property
+  def deferred_upgrades(self):
+    try:
+      while True:
+        self.cur.execute("SELECT id, script FROM deferred_upgrade ORDER BY id LIMIT 1")
+        row = cur.fetchone()
+        if row is None:
+          break
+        rowid, script = row
+        yield script
+        self.cur.execute("DELETE FROM upgrade_version WHERE id = %s", (rowid,))
+        self.db.commit()
+    except _mysql_exceptions.ProgrammingError, e:
+      if e.args[0] != ER_NO_SUCH_TABLE:
+        raise
+
 
 class Version(object):
   """
@@ -225,8 +258,7 @@ def do_create(name):
     if not statement.upper().startswith("DROP TABLE"):
       log(statement)
       db.cur.execute(statement)
-  if db.uses_upgrade_version:
-    db.version = current_version
+  db.version = current_version
   db.close()
 
 def do_script_drop(name):
@@ -251,17 +283,29 @@ def do_create_if_missing(name):
     do_create(name)
 
 def do_apply_upgrades(name):
-  db = UserDB(name)
-  if db.uses_upgrade_version:
+  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 sorted(Upgrade.load_all(name, args.upgrade_scripts)):
+    for upgrade in upgrades:
       if upgrade.version > db.version:
         upgrade.apply()
         db.version = upgrade.version
     db.version = current_version
     db.close()
 
+def do_apply_deferred_upgrades(name):
+  db = UserDB(name)
+  db.open()
+  for i, script in enumerate(db.deferred_upgrades):
+    if i == 0:
+      log("Pausing to let RPKI daemons start up")
+      time.sleep(10)
+    log("Running deferred upgrade #%i for %s" % (i, name))
+    exec script
+  db.close()
+
 def log(text):
   if args.verbose:
     print "#", text
@@ -301,6 +345,9 @@ group.add_argument("--create-if-missing",
 group.add_argument("--apply-upgrades",
                    action = "store_const", dest = "dispatch", const = do_apply_upgrades,
                    help = "apply upgrade scripts to existing databases")
+group.add_argument("--apply-deferred-upgrades",
+                   action = "store_const", dest = "dispatch", const = do_apply_deferred_upgrades,
+                   help = "apply upgrade scripts that were deferred until daemon startup")
 parser.set_defaults(dispatch = do_create_if_missing)
 args = parser.parse_args()
 
diff --git a/rpkid/rpki-start-servers b/rpkid/rpki-start-servers
index edaffb2e..e1c42e62 100755
--- a/rpkid/rpki-start-servers
+++ b/rpkid/rpki-start-servers
@@ -42,6 +42,8 @@ parser.add_argument("-d", "--debug", action = "store_true",
                     help = "enable debugging")
 parser.add_argument("--logdir", default = ".",
                     help = "where to write write log files when debugging")
+parser.add_argument("--dont-apply-deferred-upgrades", action = "store_true",
+                    help = "don't apply deferred upgrade actions after starting daemons")
 args = parser.parse_args()
 
 cfg = rpki.config.parser(args.config, "myrpki")
@@ -74,3 +76,7 @@ if cfg.getboolean("start_pubd",  cfg.getboolean("run_pubd",  False)):
 
 if cfg.getboolean("start_rootd", cfg.getboolean("run_rootd", False)):
   run("rootd")
+
+if not args.dont_apply_deferred_upgrades:
+  os.execl(os.path.join(rpki.autoconf.sbindir, "rpki-sql-setup"),
+           "--apply-deferred-upgrades")