kea/src/lib/dhcpsrv/tests/schema_mysql_copy.h

// Copyright (C) 2012-2015 Internet Systems Consortium, Inc. ("ISC")
//
// Permission to use, copy, modify, and/or 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.

#ifndef SCHEMA_COPY_H
#define SCHEMA_COPY_H

namespace {

// What follows is a set of statements that creates a copy of the schema
// in the test database.  It is used by the MySQL unit test prior to each
// test.
//
// Each SQL statement is a single string.  The statements are not terminated
// by semicolons, and the strings must end with a comma.  The final line
// statement must be NULL (not in quotes)

// NOTE: This file mirrors the schema in src/lib/dhcpsrv/dhcpdb_create.mysql.
//       If this file is altered, please ensure that any change is compatible
//       with the schema in dhcpdb_create.mysql.

// Deletion of existing tables.

const char* destroy_statement[] = {
    "DROP TABLE lease4",
    "DROP TABLE lease6",
    "DROP TABLE lease6_types",
    "DROP TABLE lease_hwaddr_source",
    "DROP TABLE schema_version",

    // We need to drop ipv6_reservations before hosts, as it has constrains
    // that depend on hosts. Therefore hosts table cannot be deleted while
    // ipv6_reservations exists.
    "DROP TABLE ipv6_reservations",
    "DROP TABLE hosts",
    "DROP TABLE dhcp4_options",
    "DROP TABLE dhcp6_options",
    NULL
};

// Creation of the new tables.

const char* create_statement[] = {

    // Schema initialization to 1.0 starts here.
    "START TRANSACTION",
    "CREATE TABLE lease4 ("
        "address INT UNSIGNED PRIMARY KEY NOT NULL,"
        "hwaddr VARBINARY(20),"
        "client_id VARBINARY(128),"
        "valid_lifetime INT UNSIGNED,"
        "expire TIMESTAMP,"
        "subnet_id INT UNSIGNED,"
        "fqdn_fwd BOOL,"
        "fqdn_rev BOOL,"
        "hostname VARCHAR(255)"
        ") ENGINE = INNODB",

    "CREATE INDEX lease4_by_hwaddr_subnet_id ON lease4 (hwaddr, subnet_id)",

    "CREATE INDEX lease4_by_client_id_subnet_id ON lease4 (client_id, subnet_id)",

    "CREATE TABLE lease6 ("
        "address VARCHAR(39) PRIMARY KEY NOT NULL,"
        "duid VARBINARY(128),"
        "valid_lifetime INT UNSIGNED,"
        "expire TIMESTAMP,"
        "subnet_id INT UNSIGNED,"
        "pref_lifetime INT UNSIGNED,"
        "lease_type TINYINT,"
        "iaid INT UNSIGNED,"
        "prefix_len TINYINT UNSIGNED,"
        "fqdn_fwd BOOL,"
        "fqdn_rev BOOL,"
        "hostname VARCHAR(255)"
        ") ENGINE = INNODB",

    "CREATE INDEX lease6_by_iaid_subnet_id_duid ON lease6 (iaid, subnet_id, duid)",

    "CREATE TABLE lease6_types ("
        "lease_type TINYINT PRIMARY KEY NOT NULL,"
        "name VARCHAR(5)"
        ")",

    "INSERT INTO lease6_types VALUES (0, \"IA_NA\")",
    "INSERT INTO lease6_types VALUES (1, \"IA_TA\")",
    "INSERT INTO lease6_types VALUES (2, \"IA_PD\")",

    "CREATE TABLE schema_version ("
        "version INT PRIMARY KEY NOT NULL,"
        "minor INT"
        ")",

    "INSERT INTO schema_version VALUES (1, 0)",
    "COMMIT",

    // Schema initialization to 1.0 ends here.

    // Schema upgrade to 2.0 starts here.
    "ALTER TABLE lease6 "
        "ADD COLUMN hwaddr varbinary(20),"
        "ADD COLUMN hwtype smallint unsigned,"
        "ADD COLUMN hwaddr_source int unsigned;",

    // Production schema has lease_hwaddr_source table. It is not used by
    // kea code and is simply useful for formulating more human readable
    // queries. Hence no need to create it in tests. The actual SQL
    // code remains here commented out to keep a trace that the omission
    // is intentional.

    /* "CREATE TABLE lease_hwaddr_source ("
    "hwaddr_source INT PRIMARY KEY NOT NULL,"
    "name VARCHAR(40) )",

    "INSERT INTO lease_hwaddr_source VALUES (1, \"HWADDR_SOURCE_RAW\");",
    "INSERT INTO lease_hwaddr_source VALUES (2, \"HWADDR_SOURCE_IPV6_LINK_LOCAL\");",
    "INSERT INTO lease_hwaddr_source VALUES (4, \"HWADDR_SOURCE_DUID\");",
    "INSERT INTO lease_hwaddr_source VALUES (8, \"HWADDR_SOURCE_CLIENT_ADDR_RELAY_OPTION\");",
    "INSERT INTO lease_hwaddr_source VALUES (16, \"HWADDR_SOURCE_REMOTE_ID\");",
    "INSERT INTO lease_hwaddr_source VALUES (32, \"HWADDR_SOURCE_SUBSCRIBER_ID\");",
    "INSERT INTO lease_hwaddr_source VALUES (64, \"HWADDR_SOURCE_DOCSIS\");", */

    "UPDATE schema_version SET version=\"2\", minor=\"0\";",
    // Schema upgrade to 2.0 ends here.

    // Schema upgrade to 3.0 starts here.

    "CREATE TABLE IF NOT EXISTS hosts ("
        "host_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
        "dhcp_identifier VARBINARY(128) NOT NULL,"
        "dhcp_identifier_type TINYINT NOT NULL,"
        "dhcp4_subnet_id INT UNSIGNED NULL,"
        "dhcp6_subnet_id INT UNSIGNED NULL,"
        "ipv4_address INT UNSIGNED NULL,"
        "hostname VARCHAR(255) NULL,"
        "dhcp4_client_classes VARCHAR(255) NULL,"
        "dhcp6_client_classes VARCHAR(255) NULL,"
        "PRIMARY KEY (host_id),"
        "INDEX key_dhcp4_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC),"
        "INDEX key_dhcp6_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC)"
    ")  ENGINE=INNODB",

    "CREATE TABLE IF NOT EXISTS ipv6_reservations ("
        "reservation_id INT NOT NULL AUTO_INCREMENT,"
        "address VARCHAR(39) NOT NULL,"
        "prefix_len TINYINT(3) UNSIGNED NOT NULL DEFAULT 128,"
        "type TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,"
        "dhcp6_iaid INT UNSIGNED NULL,"
        "host_id INT UNSIGNED NOT NULL,"
        "PRIMARY KEY (reservation_id),"
        "INDEX fk_ipv6_reservations_host_idx (host_id ASC),"
        "CONSTRAINT fk_ipv6_reservations_Host FOREIGN KEY (host_id)"
            "REFERENCES hosts (host_id)"
            "ON DELETE NO ACTION ON UPDATE NO ACTION"
    ")  ENGINE=INNODB",

    "CREATE TABLE IF NOT EXISTS dhcp4_options ("
        "option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
        "code TINYINT UNSIGNED NOT NULL,"
        "value BLOB NULL,"
        "formatted_value TEXT NULL,"
        "space VARCHAR(128) NULL,"
        "persistent TINYINT(1) NOT NULL DEFAULT 0,"
        "dhcp_client_class VARCHAR(128) NULL,"
        "dhcp4_subnet_id INT NULL,"
        "host_id INT UNSIGNED NULL,"
        "PRIMARY KEY (option_id),"
        "UNIQUE INDEX option_id_UNIQUE (option_id ASC),"
        "INDEX fk_options_host1_idx (host_id ASC),"
        "CONSTRAINT fk_options_host1 FOREIGN KEY (host_id)"
            "REFERENCES hosts (host_id)"
            "ON DELETE NO ACTION ON UPDATE NO ACTION"
    ")  ENGINE=INNODB",

    "CREATE TABLE IF NOT EXISTS dhcp6_options ("
        "option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
        "code INT UNSIGNED NOT NULL,"
        "value BLOB NULL,"
        "formatted_value TEXT NULL,"
        "space VARCHAR(128) NULL,"
        "persistent TINYINT(1) NOT NULL DEFAULT 0,"
        "dhcp_client_class VARCHAR(128) NULL,"
        "dhcp6_subnet_id INT NULL,"
        "host_id INT UNSIGNED NULL,"
        "PRIMARY KEY (option_id),"
        "UNIQUE INDEX option_id_UNIQUE (option_id ASC),"
        "INDEX fk_options_host1_idx (host_id ASC),"
        "CONSTRAINT fk_options_host10 FOREIGN KEY (host_id)"
            "REFERENCES hosts (host_id)"
            "ON DELETE NO ACTION ON UPDATE NO ACTION"
    ")  ENGINE=INNODB",


    //"DELIMITER $$ ",
    "CREATE TRIGGER host_BDEL BEFORE DELETE ON hosts FOR EACH ROW "
    "BEGIN "
    "DELETE FROM ipv6_reservations WHERE ipv6_reservations.host_id = OLD.host_id; "
    "END ",
    //"$$ ",
    //"DELIMITER ;",

    "UPDATE schema_version SET version = '3', minor = '0';",

    // This line concludes database upgrade to version 3.0.

    // Schema upgrade to 4.0 starts here.
    "ALTER TABLE lease4 "
        "ADD COLUMN state INT UNSIGNED DEFAULT 0",

    "ALTER TABLE lease6 "
        "ADD COLUMN state INT UNSIGNED DEFAULT 0",

    "CREATE INDEX lease4_by_state_expire ON lease4 (state, expire)",
    "CREATE INDEX lease6_by_state_expire ON lease6 (state, expire)",

    // Production schema includes the lease_state table which maps
    // the lease states to their names. This is not used in the unit tests
    // so it is commented out.

    /*"CREATE TABLE IF NOT EXISTS lease_state (",
        "state INT UNSIGNED PRIMARY KEY NOT NULL,"
        "name VARCHAR(64) NOT NULL);",

    "INSERT INTO lease_state VALUES (0, \"default\");",
    "INSERT INTO lease_state VALUES (1, \"declined\");",
    "INSERT INTO lease_state VALUES (2, \"expired-reclaimed\");",*/


    // Schema upgrade to 4.0 ends here.

    NULL
};

};  // Anonymous namespace

#endif // SCHEMA_COPY_H