Debug rpki-sql-upgrade.

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

4 files changed, 62 insertions, 10 deletions

diff --git a/buildtools/debian-skeleton/rpki-ca.postinst b/buildtools/debian-skeleton/rpki-ca.postinst
index 08af52e0..1500cd4f 100644
--- a/buildtools/debian-skeleton/rpki-ca.postinst
+++ b/buildtools/debian-skeleton/rpki-ca.postinst
@@ -49,6 +49,7 @@ setup_rpki_conf() {
 
 setup_mysql() {
     rpki-sql-setup --mysql-defaults /etc/mysql/debian.cnf
+    rpki-sql-upgrade --verbose
 }
 
 setup_bpki() {
diff --git a/rpkid/rpki-sql-upgrade b/rpkid/rpki-sql-upgrade
index 0ec6b476..19a0155e 100644..100755
--- a/rpkid/rpki-sql-upgrade
+++ b/rpkid/rpki-sql-upgrade
@@ -21,9 +21,7 @@ Run upgrade scripts to drag installed version of RPKI-related SQL
 databases up to the current version.
 
 We ignore the IRDB, on the theory that the Django stuff can take care
-of itself, using South migrations if necessary.  We could use South
-for everything if we were to rewrit the rpkid and pubd databases using
-the Django ORM, but that's a big change.  Perhaps someday.
+of itself, using South migrations if necessary.
 """
 
 import os
@@ -31,10 +29,10 @@ import sys
 import time
 import glob
 import argparse
+import datetime
 import rpki.config
 import rpki.autoconf
 import rpki.version
-import rpki.sundial
 
 from rpki.mysql_import import MySQLdb, _mysql_exceptions
 
@@ -109,7 +107,7 @@ class Database(object):
     if self.enabled:
       cur = self.db.cursor()
       cur.execute("SELECT version FROM upgrade_version")
-      v = self.cur.fetchone()
+      v = cur.fetchone()
       cur.close()
       return Version(None if v is None else v[0])
     else:
@@ -120,7 +118,7 @@ class Database(object):
     if self.enabled and v > self.version:
       cur = self.db.cursor()
       cur.execute("DELETE FROM upgrade_version")
-      cur.execute("INSERT upgrade_version (version, updated) VALUES (%s, %s)", (v, sundial.datetime.now()))
+      cur.execute("INSERT upgrade_version (version, updated) VALUES (%s, %s)", (v, datetime.datetime.now()))
       cur.close()
       self.db.commit()
       if args.verbose:
@@ -129,6 +127,18 @@ class Database(object):
   def close(self):
     self.db.close()
 
+  def execute(self, *sql):
+    cur = self.db.cursor()
+    cur.execute(*sql)
+    cur.close()
+
+  def fetch(self, *sql):
+    cur = self.db.cursor()
+    cur.execute(*sql)
+    for row in cur.fetchall():
+      yield row
+    cur.close()
+
 class Upgrade(object):
   """
   One upgrade script.  Really, just its filename and the Version
@@ -153,7 +163,7 @@ class Upgrade(object):
 
   def apply(self):
     if args.verbose:
-      print "Applying", fn
+      print "Applying", self.fn
     with open(self.fn, "r") as f:
       exec f
 
@@ -176,8 +186,8 @@ cfg = rpki.config.parser(args.config, "myrpki")
 rpkid_db = Database(cfg, "rpkid")
 pubd_db  = Database(cfg, "pubd")
 
-for upgrade in sorted(Upgrade.load_all(args.update_scripts)):
-  if upgrade.version > rpkid_db.version upgrade.version > pubd_db.version:
+for upgrade in sorted(Upgrade.load_all(args.upgrade_scripts)):
+  if upgrade.version > rpkid_db.version or upgrade.version > pubd_db.version:
     upgrade.apply()
     rpkid_db.version = upgrade.version
     pubd_db.version  = upgrade.version
diff --git a/rpkid/setup.py b/rpkid/setup.py
index 39aad552..956babe5 100644
--- a/rpkid/setup.py
+++ b/rpkid/setup.py
@@ -67,6 +67,7 @@ setup(name              = "rpkitoolkit",
                              "rpki-start-servers",
                              "rpki-sql-backup",
                              "rpki-sql-setup",
+                             "rpki-sql-upgrade",
                              "portal-gui/scripts/rpki-manage",
                              "portal-gui/scripts/rpkigui-query-routes",
                              "irbe_cli"]),
@@ -88,4 +89,6 @@ setup(name              = "rpkitoolkit",
                            (autoconf.datarootdir + "/rpki/media/js",
                             glob("rpki/gui/app/static/js/*")),
                            (autoconf.datarootdir + "/rpki/media/img",
-                            glob("rpki/gui/app/static/img/*"))])
+                            glob("rpki/gui/app/static/img/*")),
+                           (autoconf.datarootdir + "/rpki/upgrade-scripts",
+                            glob("upgrade-scripts/*"))])
diff --git a/rpkid/upgrade-scripts/upgrade-to-0.5709.py b/rpkid/upgrade-scripts/upgrade-to-0.5709.py
new file mode 100644
index 00000000..06b6295a
--- /dev/null
+++ b/rpkid/upgrade-scripts/upgrade-to-0.5709.py
@@ -0,0 +1,38 @@
+# $Id$
+#
+# Copyright (C) 2014  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.
+
+"""
+Upgrade RPKI SQL databases to schema expected by 0.5709.
+
+NB: This code is evaluated in the context of rpki-sql-update and has
+access to its global variables.
+"""
+
+rpkid_db.execute("""
+    CREATE TABLE ee_cert (
+            ee_cert_id              SERIAL NOT NULL,
+            ski                     BINARY(20) NOT NULL,
+            cert                    LONGBLOB NOT NULL,
+            published               DATETIME,
+            self_id                 BIGINT UNSIGNED NOT NULL,
+            ca_detail_id            BIGINT UNSIGNED NOT NULL,
+            PRIMARY KEY             (ee_cert_id),
+            CONSTRAINT              ee_cert_self_id
+            FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+            CONSTRAINT              ee_cert_ca_detail_id
+            FOREIGN KEY             (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE
+    ) ENGINE=InnoDB
+""")