Wrap SQL schemas up so rpki-sql-setup doesn't have to be run in the

build tree (see #8).  This appears to work properly but needs more
testing, leaving ticket open for now.

Also cleaned up a few things that should have moved from scripts/ to
buildtools/ a long time ago.

svn path=/trunk/; revision=4056

7 files changed, 482 insertions, 15 deletions

diff --git a/scripts/graphviz-sql.sh b/buildtools/graphviz-sql.sh
index 21a81146..21a81146 100644
--- a/scripts/graphviz-sql.sh
+++ b/buildtools/graphviz-sql.sh
diff --git a/scripts/make-relaxng.py b/buildtools/make-relaxng.py
index 62decbae..62decbae 100644
--- a/scripts/make-relaxng.py
+++ b/buildtools/make-relaxng.py
diff --git a/buildtools/make-sql-schemas.py b/buildtools/make-sql-schemas.py
new file mode 100644
index 00000000..700d2b9c
--- /dev/null
+++ b/buildtools/make-sql-schemas.py
@@ -0,0 +1,52 @@
+"""
+Script to generate rpki/relaxng.py.
+
+$Id$
+
+Copyright (C) 2009-2011 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
+PERFORMANCE OF THIS SOFTWARE.
+
+Portions copyright (C) 2007--2008  American Registry for Internet Numbers ("ARIN")
+
+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 ARIN DISCLAIMS ALL WARRANTIES WITH
+REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
+AND FITNESS.  IN NO EVENT SHALL ARIN 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.
+"""
+
+schemas = ("rpkid", "irdbd", "pubd")
+
+format_1 = """\
+# Automatically generated, do not edit.
+"""
+
+format_2 = """\
+## @var %(name)s
+## SQL schema %(name)s
+%(name)s = '''%(sql)s'''
+"""
+
+print format_1
+
+for name in schemas:
+  print format_2 % {
+    "name" : name,
+    "sql"  : open(name + ".sql").read() }
diff --git a/scripts/pylint.rc b/buildtools/pylint.rc
index 5e555f45..5e555f45 100644
--- a/scripts/pylint.rc
+++ b/buildtools/pylint.rc
diff --git a/rpkid/Makefile.in b/rpkid/Makefile.in
index ccd7a1f6..ab2f2772 100644
--- a/rpkid/Makefile.in
+++ b/rpkid/Makefile.in
@@ -49,7 +49,7 @@ AUX_SCRIPTS =
 
 SETTINGS = rpki/gui/settings.py rpki/gui/app/settings.py
 
-all:: ${POW_SO} rpki/relaxng.py myrpki.rng ${SCRIPTS} ${AUX_SCRIPTS} ${SETTINGS}
+all:: ${POW_SO} rpki/relaxng.py myrpki.rng rpki/sql_schemas.py ${SCRIPTS} ${AUX_SCRIPTS} ${SETTINGS}
 
 ${POW_SO}: ext/POW.c setup.py
 	${SETUP_PY} build_ext --inplace
@@ -63,8 +63,8 @@ rpm deb:: all
 deb::
 	cd dist; for i in *.rpm; do case $$i in *.src.rpm) :;; *) (set -x; fakeroot alien -v $$i);; esac; done
 
-rpki/relaxng.py: ../scripts/make-relaxng.py left-right-schema.rng up-down-schema.rng publication-schema.rng
-	${PYTHON} ../scripts/make-relaxng.py >$@.tmp
+rpki/relaxng.py: ${abs_top_srcdir}/buildtools/make-relaxng.py left-right-schema.rng up-down-schema.rng publication-schema.rng
+	${PYTHON} ${abs_top_srcdir}/buildtools/make-relaxng.py >$@.tmp
 	mv $@.tmp $@
 
 left-right-schema.rng: left-right-schema.rnc
@@ -79,6 +79,10 @@ publication-schema.rng: publication-schema.rnc
 myrpki.rng: myrpki.rnc
 	trang myrpki.rnc myrpki.rng
 
+rpki/sql_schemas.py: ${abs_top_srcdir}/buildtools/make-sql-schemas.py rpkid.sql irdbd.sql pubd.sql
+	${PYTHON} ${abs_top_srcdir}/buildtools/make-sql-schemas.py >$@.tmp
+	mv $@.tmp $@
+
 clean::
 	find . -type f -name '*.py[co]' -delete
 	cd tests; $(MAKE) $@
@@ -113,21 +117,21 @@ test all-tests parse-test profile yamltest:: all
 	cd tests; $(MAKE) $@
 
 tags: Makefile
-	find . -type f \( -name '*.py' -o -name '*.sql' -o -name '*.rnc' -o -name '*.py.in' \) ! -name relaxng.py ! -name __doc__.py | etags -
+	find . -type f \( -name '*.py' -o -name '*.sql' -o -name '*.rnc' -o -name '*.py.in' \) ! -name relaxng.py ! -name sql_schemas.py ! -name __doc__.py | etags -
 
 lint:
-	pylint --rcfile ../scripts/pylint.rc rpki/[a-z]*.py *d.py rpki-*.py myrpki.py irbe_cli.py tests/*.py
+	pylint --rcfile ${abs_top_srcdir}/buildtools/pylint.rc rpki/[a-z]*.py *d.py rpki-*.py myrpki.py irbe_cli.py tests/*.py
 
 # Documentation
 
 doc/irdbd.dot: irdbd.sql
-	sh ../scripts/graphviz-sql.sh $? >$@
+	sh ${abs_top_srcdir}/buildtools/graphviz-sql.sh $? >$@
 
 doc/pubd.dot: pubd.sql
-	sh ../scripts/graphviz-sql.sh $? >$@
+	sh ${abs_top_srcdir}/buildtools/graphviz-sql.sh $? >$@
 
 doc/rpkid.dot: rpkid.sql
-	sh ../scripts/graphviz-sql.sh $? >$@
+	sh ${abs_top_srcdir}/buildtools/graphviz-sql.sh $? >$@
 
 .SUFFIXES: .dot .png .pdf .eps
 
diff --git a/rpkid/rpki-sql-setup.py b/rpkid/rpki-sql-setup.py
index 7e4abec6..051f6980 100644
--- a/rpkid/rpki-sql-setup.py
+++ b/rpkid/rpki-sql-setup.py
@@ -21,21 +21,19 @@ PERFORMANCE OF THIS SOFTWARE.
 
 import os, getopt, sys, rpki.config, getpass, warnings
 
-from rpki.mysql_import import MySQLdb
+import rpki.sql_schemas
 
-schema_dir = os.path.normpath(sys.path[0])
+from rpki.mysql_import import MySQLdb
 
-def read_schema(filename):
+def read_schema(name):
   """
   Convert an SQL file into a list of SQL statements.
   """
   lines = []
-  f = open(filename)
-  for line in f:
+  for line in getattr(rpki.sql_schemas, name).splitlines():
     line = " ".join(line.split())
     if line and not line.startswith("--"):
       lines.append(line)
-  f.close()
   return [statement.strip() for statement in " ".join(lines).rstrip(";").split(";")]
 
 def sql_setup(name):
@@ -45,7 +43,7 @@ def sql_setup(name):
   database = cfg.get("sql-database", section = name)
   username = cfg.get("sql-username", section = name)
   password = cfg.get("sql-password", section = name)
-  schema = read_schema(os.path.join(schema_dir, "%s.sql" % name))
+  schema = read_schema(name)
 
   print "Creating database", database
   cur = rootdb.cursor()
diff --git a/rpkid/rpki/sql_schemas.py b/rpkid/rpki/sql_schemas.py
new file mode 100644
index 00000000..154ab5c1
--- /dev/null
+++ b/rpkid/rpki/sql_schemas.py
@@ -0,0 +1,413 @@
+# Automatically generated, do not edit.
+
+## @var rpkid
+## SQL schema rpkid
+rpkid = '''-- $Id: rpkid.sql 3745 2011-03-27 00:21:57Z sra $
+
+-- Copyright (C) 2009--2011  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
+-- PERFORMANCE OF THIS SOFTWARE.
+
+-- Copyright (C) 2007--2008  American Registry for Internet Numbers ("ARIN")
+--
+-- 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 ARIN DISCLAIMS ALL WARRANTIES WITH
+-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
+-- AND FITNESS.  IN NO EVENT SHALL ARIN 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.
+
+-- SQL objects needed by the RPKI engine (rpkid.py).
+
+-- DROP TABLE commands must be in correct (reverse dependency) order
+-- to satisfy FOREIGN KEY constraints.
+
+DROP TABLE IF EXISTS ghostbuster;
+DROP TABLE IF EXISTS roa_prefix;
+DROP TABLE IF EXISTS roa;
+DROP TABLE IF EXISTS revoked_cert;
+DROP TABLE IF EXISTS child_cert;
+DROP TABLE IF EXISTS child;
+DROP TABLE IF EXISTS ca_detail;
+DROP TABLE IF EXISTS ca;
+DROP TABLE IF EXISTS parent;
+DROP TABLE IF EXISTS repository;
+DROP TABLE IF EXISTS bsc;
+DROP TABLE IF EXISTS self;
+
+CREATE TABLE self (
+        self_id                 SERIAL NOT NULL,
+        self_handle             VARCHAR(255) NOT NULL,
+        use_hsm                 BOOLEAN NOT NULL DEFAULT FALSE,
+        crl_interval            BIGINT UNSIGNED,
+        regen_margin            BIGINT UNSIGNED,
+        bpki_cert               LONGBLOB,
+        bpki_glue               LONGBLOB,
+        PRIMARY KEY             (self_id),
+        UNIQUE                  (self_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE bsc (
+        bsc_id                  SERIAL NOT NULL,
+        bsc_handle              VARCHAR(255) NOT NULL,
+        private_key_id          LONGBLOB,
+        pkcs10_request          LONGBLOB,
+        hash_alg                ENUM ('sha256'),
+        signing_cert            LONGBLOB,
+        signing_cert_crl        LONGBLOB,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (bsc_id),
+        CONSTRAINT              bsc_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        UNIQUE                  (self_id, bsc_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE repository (
+        repository_id           SERIAL NOT NULL,
+        repository_handle       VARCHAR(255) NOT NULL,
+        peer_contact_uri        TEXT,
+        bpki_cert               LONGBLOB,
+        bpki_glue               LONGBLOB,
+        last_cms_timestamp      DATETIME,
+        bsc_id                  BIGINT UNSIGNED NOT NULL,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (repository_id),
+        CONSTRAINT              repository_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        CONSTRAINT              repository_bsc_id
+        FOREIGN KEY             (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE,
+        UNIQUE                  (self_id, repository_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE parent (
+        parent_id               SERIAL NOT NULL,
+        parent_handle           VARCHAR(255) NOT NULL,
+        bpki_cms_cert           LONGBLOB,
+        bpki_cms_glue           LONGBLOB,
+        peer_contact_uri        TEXT,
+        sia_base                TEXT,
+        sender_name             TEXT,
+        recipient_name          TEXT,
+        last_cms_timestamp      DATETIME,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        bsc_id                  BIGINT UNSIGNED NOT NULL,
+        repository_id           BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (parent_id),
+        CONSTRAINT              parent_repository_id
+        FOREIGN KEY             (repository_id) REFERENCES repository (repository_id) ON DELETE CASCADE,
+        CONSTRAINT              parent_bsc_id
+        FOREIGN KEY             (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE,
+        CONSTRAINT              parent_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        UNIQUE                  (self_id, parent_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE ca (
+        ca_id                   SERIAL NOT NULL,
+        last_crl_sn             BIGINT UNSIGNED NOT NULL,
+        last_manifest_sn        BIGINT UNSIGNED NOT NULL,
+        next_manifest_update    DATETIME,
+        next_crl_update         DATETIME,
+        last_issued_sn          BIGINT UNSIGNED NOT NULL,
+        sia_uri                 TEXT,
+        parent_resource_class   TEXT,
+        parent_id               BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (ca_id),
+        CONSTRAINT              ca_parent_id
+        FOREIGN KEY             (parent_id) REFERENCES parent (parent_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE ca_detail (
+        ca_detail_id            SERIAL NOT NULL,
+        public_key              LONGBLOB,
+        private_key_id          LONGBLOB,
+        latest_crl              LONGBLOB,
+        crl_published           DATETIME,
+        latest_ca_cert          LONGBLOB,
+        manifest_private_key_id LONGBLOB,
+        manifest_public_key     LONGBLOB,
+        latest_manifest_cert    LONGBLOB,
+        latest_manifest         LONGBLOB,
+        manifest_published      DATETIME,
+        state                   ENUM ('pending', 'active', 'deprecated', 'revoked') NOT NULL,
+        ca_cert_uri             TEXT,
+        ca_id                   BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (ca_detail_id),
+        CONSTRAINT              ca_detail_ca_id
+        FOREIGN KEY             (ca_id) REFERENCES ca (ca_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE child (
+        child_id                SERIAL NOT NULL,
+        child_handle            VARCHAR(255) NOT NULL,
+        bpki_cert               LONGBLOB,
+        bpki_glue               LONGBLOB,
+        last_cms_timestamp      DATETIME,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        bsc_id                  BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (child_id),
+        CONSTRAINT              child_bsc_id 
+        FOREIGN KEY             (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE,
+        CONSTRAINT              child_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        UNIQUE                  (self_id, child_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE child_cert (
+        child_cert_id           SERIAL NOT NULL,
+        cert                    LONGBLOB NOT NULL,
+        published               DATETIME,
+        ski                     TINYBLOB NOT NULL,
+        child_id                BIGINT UNSIGNED NOT NULL,
+        ca_detail_id            BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (child_cert_id),
+        CONSTRAINT              child_cert_ca_detail_id
+        FOREIGN KEY             (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE,
+        CONSTRAINT              child_cert_child_id
+        FOREIGN KEY             (child_id) REFERENCES child (child_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE revoked_cert (
+        revoked_cert_id         SERIAL NOT NULL,
+        serial                  BIGINT UNSIGNED NOT NULL,
+        revoked                 DATETIME NOT NULL,
+        expires                 DATETIME NOT NULL,
+        ca_detail_id            BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (revoked_cert_id),
+        CONSTRAINT              revoked_cert_ca_detail_id
+        FOREIGN KEY             (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE roa (
+        roa_id                  SERIAL NOT NULL,
+        asn                     BIGINT UNSIGNED NOT NULL,
+        cert                    LONGBLOB NOT NULL,
+        roa                     LONGBLOB NOT NULL,
+        published               DATETIME,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        ca_detail_id            BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (roa_id),
+        CONSTRAINT              roa_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        CONSTRAINT              roa_ca_detail_id
+        FOREIGN KEY             (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE roa_prefix (
+        prefix                  VARCHAR(40) NOT NULL,
+        prefixlen               TINYINT UNSIGNED NOT NULL,
+        max_prefixlen           TINYINT UNSIGNED NOT NULL,
+        version                 TINYINT UNSIGNED NOT NULL,
+        roa_id                  BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (roa_id, prefix, prefixlen, max_prefixlen),
+        CONSTRAINT              roa_prefix_roa_id
+        FOREIGN KEY             (roa_id) REFERENCES roa (roa_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE ghostbuster (
+        ghostbuster_id          SERIAL NOT NULL,
+        vcard                   LONGBLOB NOT NULL,
+        cert                    LONGBLOB NOT NULL,
+        ghostbuster             LONGBLOB NOT NULL,
+        published               DATETIME,
+        self_id                 BIGINT UNSIGNED NOT NULL,
+        ca_detail_id            BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (ghostbuster_id),
+        CONSTRAINT              ghostbuster_self_id
+        FOREIGN KEY             (self_id) REFERENCES self (self_id) ON DELETE CASCADE,
+        CONSTRAINT              ghostbuster_ca_detail_id
+        FOREIGN KEY             (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+-- Local Variables:
+-- indent-tabs-mode: nil
+-- End:
+'''
+
+## @var irdbd
+## SQL schema irdbd
+irdbd = '''-- $Id: irdbd.sql 3730 2011-03-21 12:42:43Z sra $
+
+-- Copyright (C) 2009--2011  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
+-- PERFORMANCE OF THIS SOFTWARE.
+
+-- Copyright (C) 2007--2008  American Registry for Internet Numbers ("ARIN")
+--
+-- 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 ARIN DISCLAIMS ALL WARRANTIES WITH
+-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
+-- AND FITNESS.  IN NO EVENT SHALL ARIN 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.
+
+-- SQL objects needed by irdbd.py.  You only need this if you're using
+-- irdbd.py as your IRDB; if you have a "real" backend you can do
+-- anything you like so long as you implement the relevant portion of
+-- the left-right protocol.
+
+-- DROP TABLE commands must be in correct (reverse dependency) order
+-- to satisfy FOREIGN KEY constraints.
+
+DROP TABLE IF EXISTS roa_request_prefix;
+DROP TABLE IF EXISTS roa_request;
+DROP TABLE IF EXISTS registrant_net;
+DROP TABLE IF EXISTS registrant_asn;
+DROP TABLE IF EXISTS registrant;
+DROP TABLE IF EXISTS ghostbuster_request;
+
+CREATE TABLE registrant (
+        registrant_id           SERIAL NOT NULL,
+        registrant_handle       VARCHAR(255) NOT NULL,
+        registrant_name         TEXT,
+        registry_handle         VARCHAR(255),
+        valid_until             DATETIME NOT NULL,
+        PRIMARY KEY             (registrant_id),
+        UNIQUE                  (registry_handle, registrant_handle)
+) ENGINE=InnoDB;
+
+CREATE TABLE registrant_asn (
+        registrant_asn_id       SERIAL NOT NULL,
+        start_as                BIGINT UNSIGNED NOT NULL,
+        end_as                  BIGINT UNSIGNED NOT NULL,
+        registrant_id           BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (registrant_asn_id),
+        CONSTRAINT              registrant_asn_registrant_id
+        FOREIGN KEY             (registrant_id) REFERENCES registrant (registrant_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE registrant_net (
+        registrant_net_id       SERIAL NOT NULL,
+        start_ip                VARCHAR(40) NOT NULL,
+        end_ip                  VARCHAR(40) NOT NULL,
+        version                 TINYINT UNSIGNED NOT NULL,
+        registrant_id           BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (registrant_net_id),
+        CONSTRAINT              registrant_net_registrant_id
+        FOREIGN KEY             (registrant_id) REFERENCES registrant (registrant_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE roa_request (
+        roa_request_id          SERIAL NOT NULL,
+        roa_request_handle      VARCHAR(255) NOT NULL,
+        asn                     BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (roa_request_id)
+) ENGINE=InnoDB;
+
+CREATE TABLE roa_request_prefix (
+        prefix                  VARCHAR(40) NOT NULL,
+        prefixlen               TINYINT UNSIGNED NOT NULL,
+        max_prefixlen           TINYINT UNSIGNED NOT NULL,
+        version                 TINYINT UNSIGNED NOT NULL,
+        roa_request_id          BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (roa_request_id, prefix, prefixlen, max_prefixlen),
+        CONSTRAINT              roa_request_prefix_roa_request_id
+        FOREIGN KEY             (roa_request_id) REFERENCES roa_request (roa_request_id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+CREATE TABLE ghostbuster_request (
+        ghostbuster_request_id  SERIAL NOT NULL,
+        self_handle             VARCHAR(40) NOT NULL,
+        parent_handle           VARCHAR(40),
+        vcard                   LONGBLOB NOT NULL,
+        PRIMARY KEY             (ghostbuster_request_id)
+) ENGINE=InnoDB;
+
+-- Local Variables:
+-- indent-tabs-mode: nil
+-- End:
+'''
+
+## @var pubd
+## SQL schema pubd
+pubd = '''-- $Id: pubd.sql 3465 2010-10-07 00:59:39Z sra $
+
+-- Copyright (C) 2009--2010  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
+-- PERFORMANCE OF THIS SOFTWARE.
+
+-- Copyright (C) 2008  American Registry for Internet Numbers ("ARIN")
+--
+-- 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 ARIN DISCLAIMS ALL WARRANTIES WITH
+-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
+-- AND FITNESS.  IN NO EVENT SHALL ARIN 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.
+
+-- SQL objects needed by pubd.py.
+
+-- The config table is weird because we're really only using it
+-- to store one BPKI CRL, but putting this here lets us use a lot of
+-- existing machinery and the alternatives are whacky in other ways.
+
+DROP TABLE IF EXISTS client;
+DROP TABLE IF EXISTS config;
+
+CREATE TABLE config (
+        config_id               SERIAL NOT NULL,
+        bpki_crl                LONGBLOB,
+        PRIMARY KEY             (config_id)
+) ENGINE=InnoDB;
+
+CREATE TABLE client (
+        client_id               SERIAL NOT NULL,
+        client_handle           VARCHAR(255) NOT NULL,
+        base_uri                TEXT,
+        bpki_cert               LONGBLOB,
+        bpki_glue               LONGBLOB,
+        last_cms_timestamp      DATETIME,
+        PRIMARY KEY             (client_id),
+        UNIQUE                  (client_handle)
+) ENGINE=InnoDB;
+
+-- Local Variables:
+-- indent-tabs-mode: nil
+-- End:
+'''
+