# $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.

"""
Add SQL tables needed for Ghostbusters support.
Most of the code here lifted from rpki-sql-setup.py
"""

import getopt, sys, rpki.config, warnings

from rpki.mysql_import import MySQLdb

def fix(name, *statements):
  db = MySQLdb.connect(db     = cfg.get("sql-database", section = name),
                       user   = cfg.get("sql-username", section = name),
                       passwd = cfg.get("sql-password", section = name))
  cur = db.cursor()
  for statement in statements:
    cur.execute(statement)
  db.commit()
  db.close()

cfg_file = None

opts, argv = getopt.getopt(sys.argv[1:], "c:h?", ["config=", "help"])
for o, a in opts:
  if o in ("-h", "--help", "-?"):
    print __doc__
    sys.exit(0)
  if o in ("-c", "--config"):
    cfg_file = a

cfg = rpki.config.parser(cfg_file, "myrpki")

fix("irdbd", """
    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;
""")

fix("rpkid", """
    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;
""")